Search This Blog

Wednesday, July 5, 2023

Create API for Apps R12 Debit Memo AR

-- b. Declaration section

DECLARE

L_CUSTOMER_TRX_ID  number;  W_TRX_NUMBER  number;  W_STATUS varchar2(2000); W_MESSAGE varchar2(2000);

W_CUSTOMER_TRX_ID  number;

   l_return_status        varchar2(1);

   l_msg_count            number;

   l_msg_data             varchar2(2000);

   l_batch_id             number;

   l_batch_source_rec     ar_invoice_api_pub.batch_source_rec_type;

   l_trx_header_tbl       ar_invoice_api_pub.trx_header_tbl_type;

   l_trx_lines_tbl        ar_invoice_api_pub.trx_line_tbl_type;

   l_trx_dist_tbl         ar_invoice_api_pub.trx_dist_tbl_type;

   l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;

   l_trx_created          number;

   l_cnt                  number;

trx_header_id number;trx_line_id number;trx_dist_id number;

   cursor cbatch IS

   select customer_trx_id

   from ra_customer_trx_all

   where batch_id = l_batch_id;


   cursor list_errors is

   

   SELECT trx_header_id, trx_line_id, trx_salescredit_id, trx_dist_id,

   trx_contingency_id, error_message, invalid_value

   FROM ar_trx_errors_gt;




BEGIN

   -- c. Set the applications context

   mo_global.init('AR');

   mo_global.set_policy_context('S','2352');

   fnd_global.apps_initialize(   user_id      => 2605 

                                ,resp_id      => 52470

                                ,resp_appl_id => 222);


        BEGIN

            SELECT ra_customer_trx_s.NEXTVAL INTO trx_header_id FROM DUAL;

        END;

        BEGIN

            SELECT ra_customer_trx_lines_s.NEXTVAL INTO trx_line_id FROM DUAL;

        END;


        BEGIN

            SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL

              INTO trx_dist_id

              FROM DUAL;

        END;

        

   -- d. Populate batch source information.

   l_batch_source_rec.batch_source_id := -1;


   -- e. Populate header information for first invoice

   l_trx_header_tbl(1).trx_header_id := trx_header_id;

   l_trx_header_tbl(1).bill_to_customer_id := 9741114;--9703695;

   l_trx_header_tbl(1).cust_trx_type_id := 13507;

   l_trx_header_tbl(1).term_id := 5;

--   l_trx_header_tbl(1).AL_DEFAULT_TAX_EXEMPT_FLAG := 'AL_DEFAULT_TAX_EXEMPT_FLAG';


   -- f. Populate lines information for first invoice

   l_trx_lines_tbl(1).trx_header_id := trx_header_id;

   l_trx_lines_tbl(1).trx_line_id := trx_line_id;

   l_trx_lines_tbl(1).line_number := 1;

--   l_trx_lines_tbl(1).description := 'Product Description 1';

   l_trx_lines_tbl(1).MEMO_LINE_ID := 78139;

   l_trx_lines_tbl(1).quantity_invoiced := 1;

   l_trx_lines_tbl(1).unit_selling_price := 1000.00;

   l_trx_lines_tbl(1).line_type := 'LINE';

 -- Populate Distribution Information

l_trx_dist_tbl(1).trx_dist_id := trx_dist_id;

l_trx_dist_tbl(1).trx_header_id := trx_header_id;

l_trx_dist_tbl(1).trx_LINE_ID := trx_line_id;

l_trx_dist_tbl(1).ACCOUNT_CLASS := 'REV';

l_trx_dist_tbl(1).AMOUNT := 1000;

l_trx_dist_tbl(1).CODE_COMBINATION_ID := 3398536;--3374927;

   -- k. Call the invoice api to create multiple invoices in a batch.

   /*AR_INVOICE_API_PUB.create_invoice(

       p_api_version          => 1.0,

       p_batch_source_rec     => l_batch_source_rec,

       p_trx_header_tbl       => l_trx_header_tbl,

       p_trx_lines_tbl        => l_trx_lines_tbl,

       p_trx_dist_tbl         => l_trx_dist_tbl,

       p_trx_salescredits_tbl => l_trx_salescredits_tbl,

       x_return_status        => l_return_status,

       x_msg_count            => l_msg_count,

       x_msg_data             => l_msg_data);


   -- l. check for errors

   IF l_return_status = fnd_api.g_ret_sts_error OR

      l_return_status = fnd_api.g_ret_sts_unexp_error THEN

      dbms_output.put_line('FAILURE: Unexpected errors were raised!');

   ELSE

      -- m. check batch/invoices created

      select distinct batch_id

      into l_batch_id

      from ar_trx_header_gt;


      IF l_batch_id IS NOT NULL THEN

         dbms_output.put_line('SUCCESS: Created batch_id = ' || l_batch_id || ' containing the following customer_trx_id:');


         for c in cBatch loop

             dbms_output.put_line (' ' || c.customer_trx_id );

         end loop;

      END IF;

    END IF;


    -- n. Within the batch, check if some invoices raised errors

    SELECT count(*)

    INTO l_cnt

    FROM ar_trx_errors_gt;


    IF l_cnt > 0 THEN

      dbms_output.put_line('FAILURE: Errors encountered, see list below:');

      FOR i in list_errors LOOP

         dbms_output.put_line('----------------------------------------------------');

         dbms_output.put_line('Header ID = ' || to_char(i.trx_header_id));

--         dbms_output.put_line('Line ID = ' || to_char(i.trx_line_id));

         dbms_output.put_line('Sales Credit ID = ' || to_char(i.trx_salescredit_id));

         dbms_output.put_line('Dist Id = ' || to_char(i.trx_dist_id));

         dbms_output.put_line('Contingency ID = ' || to_char(i.trx_contingency_id));

         dbms_output.put_line('Message = ' || substr(i.error_message,1,80));

         dbms_output.put_line('Invalid Value = ' || substr(i.invalid_value,1,80));

         dbms_output.put_line('----------------------------------------------------');

      END LOOP;

   END IF;*/

   

    AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE (

            P_API_VERSION            => 1.0,

            P_INIT_MSG_LIST          => FND_API.G_FALSE,

            P_COMMIT                 => FND_API.G_FALSE,

            P_BATCH_SOURCE_REC       => L_BATCH_SOURCE_REC,

            P_TRX_HEADER_TBL         => L_TRX_HEADER_TBL,

            P_TRX_LINES_TBL          => L_TRX_LINES_TBL,

            P_TRX_DIST_TBL           => L_TRX_DIST_TBL,

            P_TRX_SALESCREDITS_TBL   => L_TRX_SALESCREDITS_TBL,

            X_CUSTOMER_TRX_ID        => L_CUSTOMER_TRX_ID,

            X_RETURN_STATUS          => L_RETURN_STATUS,

            X_MSG_COUNT              => L_MSG_COUNT,

            X_MSG_DATA               => L_MSG_DATA);

             

            

            

        COMMIT;

        DBMS_OUTPUT.PUT_LINE ('l_return_status-' || L_RETURN_STATUS);

        DBMS_OUTPUT.PUT_LINE ('l_customer_trx_id-' || L_CUSTOMER_TRX_ID);

        DBMS_OUTPUT.PUT_LINE ('l_msg_count -' || L_MSG_COUNT);


        IF L_CUSTOMER_TRX_ID > 0

        THEN

            BEGIN

            W_customer_trx_id := L_CUSTOMER_TRX_ID;

                SELECT TRX_NUMBER

                  INTO W_TRX_NUMBER

                  FROM RA_CUSTOMER_TRX

                 WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID;

            END;


            DBMS_OUTPUT.PUT_LINE (

                'Generated Oracle Txn Number-->' || W_TRX_NUMBER);

            W_STATUS := 'Success';

            W_MESSAGE := 'Invoice is Created';

            DBMS_OUTPUT.PUT_LINE (W_STATUS);

            ELSE

             W_STATUS := 'E';

        END IF;

   

END;


COMMIT;


--DELETE 


--select * FROM ar_trx_errors_gt;


--SELECT * FROM RA_CUSTOMER_TRX_ALL WHERE CREATION_DATE LIKE SYSDATE;

No comments:

Post a Comment