You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Only one row is being inserted in voucher stg tbls

2 replies [Last post]
Guest's picture
Guest

I am trying to load the follow staging tables with invoice information but it only loads the first row then it posts as completed successfully.

PS_VCHR_HDR_STG;
PS_VCHR_LINE_STG;
PS_VCHR_DIST_STG;
PS_VCHR_VNDR_STG;

I have the initial invoice info in Table CUST_INVOICE_INFO info includes PO information.
I start with DO SELECT - selecting all fields in the CUST_INVOICE_INFO table
then I go to PeopleCode action

Local Record &APHeader = CreateRecord(Record.VCHR_HDR_STG);
&APHeader.BUSINESS_UNIT.Value = "US001";
&APHeader.VCHR_BLD_KEY_C1.Value = "DORBU";
&APHeader.VCHR_BLD_KEY_C2.Value = CUST_INV_AET.INVOICE_ID;
&APHeader.VCHR_BLD_KEY_N1.Value = 0;
&APHeader.VCHR_BLD_KEY_N2.Value = 0;
&APHeader.VCHR_SRC.Value = "CUST";
&APHeader.VOUCHER_STYLE.Value = "REG";
....ETC
&APHeader.Insert();

I do the same for each record, but the PS_VCHR_LINE_STG and PS_VCHR_DIST_STG are not populated correctly.

Lepa's picture
User offline. Last seen 2 years 2 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Only one row is being inserted in voucher stg tbls

I don't see any thing wrong with the code you posted. Hard to say without knowing how the flow in your program looks like. But, there is a more efficient way to insert data from one table to another table in just one SQL action instead of Do Select and then PeopleCode. Below is a quick example.

INSERT INTO PS_VCHR_HDR_STG (BUSINESS_UNIT,
                             VCHR_BLD_KEY_C1,
                             VCHR_BLD_KEY_C2,
                             VCHR_BLD_KEY_N1,
                             VCHR_BLD_KEY_N2,
                             VOUCHER_ID,
                             VOUCHER_STYLE,
                             INVOICE_ID,
                             INVOICE_DT,
                             VENDOR_SETID,
                             VENDOR_ID
                             .....
                             .....
                             .....)
   SELECT 'US001',
          'DORBU',
          %BIND(INVOICE_ID),
          0,
          0,
          VOUCHER_ID,
          VOUCHER_STYLE,
          INVOICE_ID,
          INVOICE_DT,
          VENDOR_SETID,
          VENDOR_ID,
          ....
          ....
          ....
     FROM PS_ CUST_INVOICE_INFO A
     WHERE <ADD UR WHERE HERE>

n/a
Guest's picture
Guest (not verified)
Re: Only one row is being inserted in voucher stg tbls

Hi Lepa, thank you for your suggestion. Much appreciated.