Oracle Apps R12 AR Invoice Transaction API (With Cursor) Multiple

CREATE OR REPLACE PROCEDURE APPS.xxcreate_invoice_ar (
P_VEHICLE_NO VARCHAR2,
P_DATE DATE,
P_CUSTOMER_CODE VARCHAR2,
P_RESULT OUT VARCHAR2,
P_INVOICE_ID OUT NUMBER,
P_INVOICE_NUM OUT VARCHAR2
) AS

   v_customer_id             NUMBER;--:= 8980620;
   L_VEHICLE_NO        varchar2(200) := P_VEHICLE_NO;
   v_user_id                 NUMBER := 2605;
   v_resp_id                 NUMBER := 52328;
   v_org_id                  NUMBER := 1930;
   v_date                    DATE := P_DATE;
   v_trx_number           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_contingencies_tbl   ar_invoice_api_pub.trx_contingencies_tbl_type;
   trx_header_id_v           NUMBER;
   trx_line_id_v             NUMBER;
   trx_dist_id_v             NUMBER;
   v_line                    number := 1;
   v_line_count              number :=0;
 
   CURSOR cbatch
   IS
      SELECT customer_trx_id
        FROM ra_customer_trx_all
       WHERE batch_id = l_batch_id;
 
   CURSOR cvalidtxn
   IS
      SELECT trx_header_id
        FROM ar_trx_header_gt
       WHERE trx_header_id NOT IN (SELECT trx_header_id
                                     FROM ar_trx_errors_gt);

  CURSOR line_items is

 SELECT MEMO_LINE_ID,NAME,PRICE,QTY
 ,(SELECT GL_ID_REV FROM AR_MEMO_LINES_ALL_VL WHERE MEMO_LINE_ID =XXF.MEMO_LINE_ID AND ATTRIBUTE1 IS NOT NULL )GL_ID
 FROM XXF_SERVICES_D XXF
 WHERE VEHICLE_NO = P_VEHICLE_NO;--20180701141;

 
BEGIN

     SELECT CUSTOMER_ID
      INTO v_customer_id
      FROM AR_CUSTOMERS
      WHERE CUSTOMER_NUMBER = P_CUSTOMER_CODE;

     SELECT xx_invoice_header.NEXTVAL
     INTO trx_header_id_v
     FROM DUAL;
   /*SELECT xx_invoice_line.NEXTVAL
     INTO trx_line_id_v
     FROM DUAL;
   SELECT xx_invoice_dist.NEXTVAL
     INTO trx_dist_id_v
     FROM DUAL;*/
   fnd_global.apps_initialize (v_user_id, v_resp_id, 222);
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', v_org_id);
   xla_security_pkg.set_security_context (222);
   l_batch_source_rec.batch_source_id := 4048;
   l_trx_header_tbl (1).trx_header_id := trx_header_id_v;
   l_trx_header_tbl (1).bill_to_customer_id := v_customer_id;           --1042
   l_trx_header_tbl (1).cust_trx_type_id := 1420;
   l_trx_header_tbl (1).trx_date := v_date;   --'10-AUG-2006';--'30-MAY-2006';
   l_trx_header_tbl (1).trx_currency := 'AED';
   l_trx_header_tbl (1).term_id := 5;
   --l_trx_header_tbl (1).default_tax_exempt_flag := 'E';
   l_trx_header_tbl (1).legal_entity_id := 27278;
   l_trx_header_tbl (1).finance_charges := NULL;
   l_trx_header_tbl (1).status_trx := 'OP';
   l_trx_header_tbl (1).printing_option := 'PRI';
   --l_trx_header_tbl (1).set_of_books_id := 12345;
   --l_trx_header_tbl (1).complete_flag := 'Y';
 
   for i in line_items loop
   v_line_count := v_line_count + v_line;
   l_trx_lines_tbl (v_line_count).trx_header_id := trx_header_id_v;
   l_trx_lines_tbl (v_line_count).trx_line_id := xx_invoice_line.NEXTVAL;--trx_line_id_v;
   l_trx_lines_tbl (v_line_count).line_number := v_line_count;
   l_trx_lines_tbl (v_line_count).description := i.name;
   l_trx_lines_tbl (v_line_count).quantity_invoiced := i.qty;
   l_trx_lines_tbl (v_line_count).unit_selling_price := i.price;
   l_trx_lines_tbl (v_line_count).line_type := 'LINE';
   --l_trx_lines_tbl (1).tax_exempt_flag := 'E';
   --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE := 'E';
   --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE_MEANING := 'E';
   l_trx_lines_tbl (v_line_count).memo_line_id := i.memo_line_id;
--   l_trx_lines_tbl (1).TAX_PRECEDENCE := 10;
--   l_trx_lines_tbl (1).TAX_RATE := 10;
   l_trx_dist_tbl (v_line_count).trx_dist_id := xx_invoice_dist.NEXTVAL;
   l_trx_dist_tbl (v_line_count).trx_line_id := xx_invoice_line.CURRVAL;
   l_trx_dist_tbl (v_line_count).account_class := 'REV';
   l_trx_dist_tbl (v_line_count).PERCENT := 100;
   l_trx_dist_tbl (v_line_count).code_combination_id := i.gl_id;
   end loop;
   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
                            );
   DBMS_OUTPUT.put_line (l_return_status);
   DBMS_OUTPUT.put_line (l_msg_data);
   P_RESULT:= l_return_status||'-'||l_msg_data;
   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 ('unexpected errors found!');
   ELSE
      FOR cvalidtxnrec IN cvalidtxn
      LOOP
         IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
         THEN
            DBMS_OUTPUT.put_line ('Invoice(s) suceessfully created!');
            DBMS_OUTPUT.put_line (   'Batch ID: '
                                  || ar_invoice_api_pub.g_api_outputs.batch_id
                                 );
            l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
            FOR cbatchrec IN cbatch
            LOOP
               DBMS_OUTPUT.put_line (   'Cust Trx Id '
                                     || cbatchrec.customer_trx_id
                                    );
               v_trx_number := cbatchrec.customer_trx_id;
               DBMS_OUTPUT.PUT_LINE('INVOICE_ID->'||v_trx_number||'-INVOICE_NUMBER->'||trx_header_id_v);
               P_INVOICE_ID := v_trx_number;
               P_INVOICE_NUM := trx_header_id_v;
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line ('Errors found!');
           
         END IF;
      END LOOP;
   END IF;
   COMMIT;
END;
/