1 Reply Latest reply: Nov 3, 2010 1:03 PM by veena v RSS

    AR_INVOICE_API_PUB.create_single_invoice

    562924
      Hi, I'm calling the above api to load invoices with, but keep getting an error in the ar_trx_errors_gt table 'Primary salesperson is invalid with current transaction date, even though it is still a valid salesperson id at the given date. Has anyone experienced this and knows how to fix the problem?


      DECLARE
      retcode NUMBER;
      errbuf VARCHAR2(250);

      --l_batch_name                VARCHAR2(250) :=:P_BATCH;
      v_batch_source_name VARCHAR2(250) :='Environmental Svcs';
      l_batch_source_id NUMBER;
      l_vat_id NUMBER;
      l_salesperson VARCHAR2(250);
      l_salescreditID NUMBER;
      l_glCode_id NUMBER;
      l_return_status VARCHAR2(250);
      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_customer_trx_id number;
      cnt number:=0;
      IDX_DIST NUMBER;
      IDX NUMBER;
      IDX_LINE NUMBER;
      l_trx_date DATE:=SYSDATE;

      CURSOR c_inv IS

      select
      xx_int.agreement_no,
      xx_int.line_no,
      xx_int.transaction_type,
      xx_int.transaction_date,
      xx_int.gl_date,
      xx_int.bill_to_number,
      xx_int.salesperson,
      xx_int.description,
      xx_int.quantity,
      xx_int.unit_price,
      xx_int.gl_account,
      xx_int.additional_info_1,
      xx_int.additional_info_2,
      xx_int.additional_info_3,
      xx_int.project_number,
      xx_int.task_number,
      xx_int.exp_type,
      hca.cust_account_id,
      rct.cust_trx_type_id,
      xx_int.tax_code
      from XXJH_AR_INVOICE_LOADER xx_int,
      hz_cust_accounts hca,
      ra_cust_trx_types_all rct
      where xx_int.bill_to_number=hca.account_number
      and xx_int.transaction_type=rct.name
      and hca.account_number='10035950'
      order by bill_to_number, agreement_no, line_no;

      CURSOR c_batch_source_id
      IS
      select rab.batch_source_id
      from ra_batch_sources_all rab
      where rab.name=v_batch_source_name;

      cursor c_vat_id(p_vat_code IN VARCHAR2)
      IS
      select vat_tax_id
      from ar_vat_tax_all_b
      where UPPER(tax_code)=UPPER(p_vat_code);

      CURSOR cBatch
      IS
      select customer_trx_id
      from ra_customer_trx_all
      where batch_id = l_batch_id;

      CURSOR cGLCode_ID (p_glCode IN VARCHAR2)
      IS
      select code_combination_id
      from gl_code_combinations
      where segment1||'-'||segment2||'-'||segment3||'-'||segment4||'-'||segment5=p_glCode;

      CURSOR cSalesrep (p_SalesPerson IN VARCHAR2)
      IS
      select salesrep_id
      from jtf_rs_salesreps
      where salesrep_number is not null
      and salesrep_number not like '-3'
      and UPPER(name)=UPPER(p_Salesperson);

      CURSOR cSalesCreditTypeID(p_SalesPerson IN VARCHAR2)
      IS
      select sales_credit_type_id
      from jtf_rs_salesreps
      where salesrep_number is not null
      and salesrep_number not like '-3'
      and UPPER(name)=UPPER(p_Salesperson);

      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);

      BEGIN



      FOR r_inv IN c_inv LOOP

      OPEN c_batch_source_id;
      FETCH c_batch_source_id INTO l_batch_source_id;
      EXIT WHEN c_batch_source_id%NOTFOUND;
      CLOSE c_batch_source_id;

      OPEN c_vat_id(r_inv.tax_code);
      FETCH c_vat_id INTO l_vat_id;
      EXIT WHEN c_vat_id%NOTFOUND;
      CLOSE c_vat_id;

      OPEN cSalesrep(r_inv.salesperson);
      FETCH cSalesrep INTO l_salesperson;
      EXIT WHEN cSalesrep%NOTFOUND;
      CLOSE cSalesrep;

      OPEN cSalesCreditTypeID(r_inv.salesperson);
      FETCH cSalesCreditTypeID INTO l_salescreditID;
      EXIT WHEN cSalesCreditTypeID%NOTFOUND;
      CLOSE cSalesCreditTypeID;

      OPEN cGLCode_ID(r_inv.gl_account);
      FETCH cGLCode_ID into l_glCode_id;
      EXIT WHEN cGLCode_ID%NOTFOUND;
      CLOSE cGLCode_ID;

      SELECT RA_CUSTOMER_TRX_S.nextval INTO IDX from dual;

      fnd_global.apps_initialize(2883, 51363, 222,0);
      -- Populate header information.
      l_trx_header_tbl(1).trx_header_id := IDX;
      l_trx_header_tbl(1).bill_to_customer_id := r_inv.cust_account_id;
      l_trx_header_tbl(1).trx_currency := 'GBP';
      l_trx_header_tbl(1).cust_trx_type_id := r_inv.cust_trx_type_id;
      l_trx_header_tbl(1).trx_date:= r_inv.transaction_date;
      l_trx_header_tbl(1).gl_date:=r_inv.transaction_date;
      l_trx_header_tbl(1).primary_salesrep_id:=l_salesperson;
      l_trx_header_tbl(1).term_id:=4;
      -- Populate batch source information.
      l_batch_source_rec.batch_source_id := l_batch_source_id;
      --l_batch_source_rec.default_date:= NULL;
      -- Populate line 1 information.

      SELECT RA_CUSTOMER_TRX_LINES_S.nextval INTO IDX_LINE FROM DUAL;

      l_trx_lines_tbl(1).trx_header_id := l_trx_header_tbl(1).trx_header_id;
      l_trx_lines_tbl(1).trx_line_id := idx_line;
      l_trx_lines_tbl(1).line_number := r_inv.line_no;
      l_trx_lines_tbl(1).description := r_inv.description;
      l_trx_lines_tbl(1).quantity_invoiced := r_inv.quantity;
      l_trx_lines_tbl(1).unit_selling_price := r_inv.unit_price;
      --l_trx_lines_tbl(1).attribute11:=r_inv.project_number;
      --l_trx_lines_tbl(1).attribute12:=r_inv.task_number;
      --l_trx_lines_tbl(1).attribute14:=r_inv.exp_type;
      l_trx_lines_tbl(1).vat_tax_id:='10183';
      l_trx_lines_tbl(1).line_type := 'LINE';


      --SELECT RA_CUST_TRX_LINE_GL_DIST_S.nextval INTO IDX_DIST FROM DUAL;
      --Populate distribution lines
      l_trx_dist_tbl(1).trx_dist_id:=IDX_DIST;
      l_trx_dist_tbl(1).trx_header_id:=l_trx_header_tbl(1).trx_header_id;
      l_trx_dist_tbl(1).trx_line_id:=l_trx_lines_tbl(1).trx_line_id;
      l_trx_dist_tbl(1).account_class:='REV';
      l_trx_dist_tbl(1).code_combination_id:=l_glCode_id;
      l_trx_dist_tbl(1).percent:=100;


      --SalesCredit Record
      l_trx_salescredits_tbl(1).trx_salescredit_id :=003;
      l_trx_salescredits_tbl(1).trx_line_id :=l_trx_lines_tbl(1).trx_line_id;
      l_trx_salescredits_tbl(1).salesrep_id :=l_salesperson;
      l_trx_salescredits_tbl(1).sales_credit_type_id:=l_salescreditID;
      l_trx_salescredits_tbl(1).salescredit_percent_split:=100;

      --Call the invoice api to create multiple invoices in a batch.
      AR_INVOICE_API_PUB.create_single_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_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);

      END LOOP;

      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
      -- Check whether any record exist in error table
      SELECT count(*)
      Into cnt
      From ar_trx_errors_gt;
      IF cnt = 0
      THEN
      dbms_output.put_line ( 'Customer Trx id '|| l_customer_trx_id);
      ELSE
      dbms_output.put_line ( 'Transaction not Created, Please check ar_trx_errors_gt table');
      END IF;
      END IF;
      COMMIT;

      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      retcode := 0;
      errbuf := 'Completed Successfully';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      Fnd_File.PUT_LINE (Fnd_File.LOG ,'NO DATA FOUND');
      retcode := 1;
      errbuf := 'No Data';
      ROLLBACK;
      WHEN OTHERS THEN
      retcode := 2;
      errbuf := SQLERRM;
      Fnd_File.PUT_LINE (Fnd_File.LOG ,SQLERRM);
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;
      /