2 Replies Latest reply: Dec 4, 2012 4:16 AM by 976976 RSS

    Ar Invoice With Std Tax Loading by Using Api

    976976
      Hi Experts ,

      I am trying to load AR Invoice With Std Tax , Only Invoice Getting load , but tax not reflecting in distribution , I mean Std tax (EBs Tax) , can any one help me Over this,

      My code is :

      /* Formatted on 11/29/2012 5:23:32 PM (QP5 v5.114.809.3010) */
      DECLARE
      -- Initializing Block
      l_return_status VARCHAR2 (1);
      l_msg_count NUMBER;
      l_msg_data VARCHAR2 (2000);
      p_header_id number := 1;
      p_trx_header_id number := 2;
      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_cust_trx_id NUMBER;
      l_tbl_count NUMBER;
      l_tbl_count_t number;
      l_header_s NUMBER;
      l_lines_s NUMBER;
      l_vat_tax_id NUMBER;
      l_lines_s_tax number; -- added by shagul
      l_org_header_id NUMBER;
      l_org_order_num VARCHAR2 (50);
      l_org_order_type VARCHAR2 (120);
      l_return_code VARCHAR2 (1);
      l_error_msg VARCHAR2 (2000);
      l_batch_source_id NUMBER;
      l_count NUMBER;
      l_trx_number VARCHAR2 (50);
      l_trx_date DATE;

      -- Initializing Cursor Block
      CURSOR c_header (p_header_id NUMBER)
      IS
      SELECT ooh.header_id,
      ooh.order_number,
      ooh.source_document_id,
      ooh.transactional_curr_code curr_code,
      ooh.salesrep_id,
      ooh.sold_to_org_id,
      ooh.invoice_to_org_id,
      ooh.payment_term_id,
      ooh.ordered_date,
      'PPGPL SHIP ONLY ORDER' order_type
      FROM oe_order_headers_all ooh
      WHERE HEADER_ID = 140061;

      CURSOR c_lines (p_header_id NUMBER)
      IS
      SELECT ool.line_number,
      ool.line_id,
      ool.inventory_item_id,
      ool.ordered_quantity,
      ool.order_quantity_uom,
      ool.unit_selling_price,
      ool.tax_code,
      ool.ship_from_org_id
      FROM oe_order_lines_all ool
      WHERE HEADER_ID = 140061;

      CURSOR c_errors (p_trx_header_id NUMBER)
      IS
      SELECT error_message FROM ar_trx_errors_gt;
      BEGIN
      -- Application Initializing
      -- fnd_global.apps_initialize (fnd_global.user_id,
      -- fnd_global.resp_id,
      -- fnd_global.resp_appl_id);
      -- arp_global.init_global;
      -- arp_standard.init_standard;
      BEGIN
      mo_global.init ('AR');
      -- MO_GLOBAL.SET_POLICY_CONTEXT ('S', 321);
      END;
      fnd_global.apps_initialize (-1, 20678, 222);

      -- Header Loop
      FOR l_header IN c_header (p_header_id)
      LOOP
      SELECT 0 f INTO l_count FROM DUAL;

      -- WHERE interface_line_attribute1 = '12939'
      -- AND interface_line_attribute2 = 'PPGPL SHIP ONLY ORDER';

      BEGIN
      SELECT 140061 header_id,
      '12948' order_number,
      'PPGPL SHIP ONLY ORDER' order_type
      INTO l_org_header_id, l_org_order_num, l_org_order_type
      FROM DUAL;
      EXCEPTION
      WHEN OTHERS
      THEN
      NULL;
      END;

      IF l_count = 0
      THEN
      BEGIN
      -- Get the Header sequence Number
      SELECT ppgpl_ar_trx_headers_s.NEXTVAL INTO l_header_s FROM DUAL;
      EXCEPTION
      WHEN OTHERS
      THEN
      NULL;
      END;

      -- Get the Batch Source id
      BEGIN
      SELECT batch_source_id
      INTO l_batch_source_id
      FROM ra_batch_sources_all
      WHERE NAME = 'PPGPL SHIP ONLY';
      EXCEPTION
      WHEN OTHERS
      THEN
      l_batch_source_id := NULL;
      END;

      -- Get the Original Bill Only Invoice Number
      BEGIN
      SELECT '12948- Copy', SYSDATE trx_date
      INTO l_trx_number, l_trx_date
      FROM DUAL;
      EXCEPTION
      WHEN OTHERS
      THEN
      l_trx_number := NULL;
      END;

      -- If the order is ship only and have the original order
      IF l_org_header_id IS NOT NULL AND l_trx_number IS NOT NULL
      THEN
      -- Assinging values to header records
      l_batch_source_rec.batch_source_id := l_batch_source_id;
      l_tbl_count := 0;
      l_tbl_count_t := 0;
      l_trx_header_tbl (1).trx_header_id := l_header_s;
      l_trx_header_tbl (1).trx_number := l_trx_number || 'D';
      l_trx_header_tbl (1).trx_date := l_trx_date;
      l_trx_header_tbl (1).gl_date := '15-OCT-2012';
      l_trx_header_tbl (1).trx_currency := l_header.curr_code;
      l_trx_header_tbl (1).cust_trx_type_id := 1;
      l_trx_header_tbl (1).primary_salesrep_id := l_header.salesrep_id;
      l_trx_header_tbl (1).bill_to_customer_id :=
      l_header.sold_to_org_id;
      l_trx_header_tbl (1).bill_to_site_use_id :=
      l_header.invoice_to_org_id;
      l_trx_header_tbl (1).term_id := l_header.payment_term_id;
      l_trx_header_tbl (1).finance_charges := 'N';
      l_trx_header_tbl (1).status_trx := 'OP';

      -- Lines Loop Start
      FOR l_lines IN c_lines (p_header_id)
      LOOP
      l_tbl_count := l_tbl_count + 1;
      l_tbl_count_t := l_tbl_count_t + 1;
      -- Get Sequence and Vat tax id
      BEGIN
      SELECT ppgpl_ar_trx_lines_s.NEXTVAL
      INTO l_lines_s
      FROM DUAL;

      SELECT ppgpl_ar_trx_lines_s.NEXTVAL
      INTO l_lines_s_tax --- added by shagul
      FROM DUAL;

      IF l_lines.tax_code IS NOT NULL
      THEN
      SELECT vat_tax_id
      INTO l_vat_tax_id
      FROM ar_vat_tax_all_b
      WHERE tax_code = 'VAT (15%)';
      ELSE
      l_vat_tax_id := NULL;
      END IF;
      EXCEPTION
      WHEN OTHERS
      THEN
      l_vat_tax_id := NULL;
      END;

      -- Assigning Values to lines Record
      l_trx_lines_tbl (l_tbl_count).trx_header_id := l_header_s;
      l_trx_lines_tbl (l_tbl_count).trx_line_id := l_lines_s;
      l_trx_lines_tbl (l_tbl_count).line_number :=
      l_lines.line_number;
      l_trx_lines_tbl (l_tbl_count).inventory_item_id :=
      l_lines.inventory_item_id;
      l_trx_lines_tbl (l_tbl_count).uom_code :=
      l_lines.order_quantity_uom;
      l_trx_lines_tbl (l_tbl_count).quantity_invoiced :=
      l_lines.ordered_quantity;
      l_trx_lines_tbl (l_tbl_count).quantity_ordered :=
      l_lines.ordered_quantity;
      l_trx_lines_tbl (l_tbl_count).vat_tax_id := l_vat_tax_id;
      -- l_trx_lines_tbl (l_tbl_count).TAXABLE_FLAG := 'Y'; -- newly added
      l_trx_lines_tbl (l_tbl_count).unit_selling_price :=
      l_lines.unit_selling_price;
      l_trx_lines_tbl (l_tbl_count).line_type := 'LINE';
      l_trx_lines_tbl (l_tbl_count).warehouse_id :=
      l_lines.ship_from_org_id;
      l_trx_lines_tbl (l_tbl_count).sales_order :=
      l_header.order_number;
      l_trx_lines_tbl (l_tbl_count).sales_order_line :=
      l_lines.line_number;
      l_trx_lines_tbl (l_tbl_count).sales_order_date :=
      l_header.ordered_date;
      l_trx_lines_tbl (l_tbl_count).interface_line_context :=
      'ORDER ENTRY';
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute1 :=
      l_header.order_number;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute2 :=
      l_header.order_type;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute3 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute4 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute5 :=
      l_lines.line_id;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute6 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute7 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute8 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute9 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute10 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute11 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute12 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute13 := 0;
      l_trx_lines_tbl (l_tbl_count).interface_line_attribute14 := 0;


      END LOOP;
      END IF;
      END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('Sucess');

      -- If any records are processed then call the API
      IF l_tbl_count > 0
      THEN
      BEGIN
      ar_invoice_api_pub.create_single_invoice (
      p_api_version => 1.0,
      -- p_commit => fnd_api.g_true,
      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_cust_trx_id,
      x_return_status => l_return_status,
      x_msg_count => l_msg_count,
      x_msg_data => l_msg_data
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      DBMS_OUTPUT.put_line ('Failed' || SQLERRM);
      END;

      -- Checking the return status
      IF l_return_status = fnd_api.g_ret_sts_error
      OR l_return_status = fnd_api.g_ret_sts_unexp_error
      THEN
      fnd_file.put_line (fnd_file.LOG,
      'Unexpected Errors Found!1 - ' || l_msg_data);
      DBMS_OUTPUT.put_line ('eror' || SQLERRM || l_msg_data);
      ELSE
      IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
      THEN
      fnd_file.put_line (fnd_file.LOG, 'Invoice suceessfully created!');
      DBMS_OUTPUT.put_line ('Invoice suceessfully created!');
      ELSE
      fnd_file.put_line (fnd_file.LOG, 'Errors found! ' || l_msg_data);
      DBMS_OUTPUT.put_line (
      'Unexpected Errors Found12! - ' || SQLERRM || l_errors.error_message
      );

      FOR l_errors IN c_errors (l_header_s)
      LOOP
      fnd_file.put_line (fnd_file.LOG, l_errors.error_message);
      END LOOP;
      END IF;
      END IF;
      ELSE
      fnd_file.put_line (fnd_file.LOG,
      'There are no records to process the transaction');
      END IF;


      COMMIT;
      EXCEPTION
      WHEN OTHERS
      THEN
      fnd_file.put_line (fnd_file.LOG,
      'Unexpected Errors Found! - ' || SQLERRM);
      DBMS_OUTPUT.put_line( 'Unexpected Errors Found!!100 - '
      || SQLERRM
      || 'CHECK'
      || l_msg_data);
      END;


      Thanks in Advance
      shagul

      Edited by: 973973 on Nov 29, 2012 5:42 PM

      Edited by: 973973 on Nov 29, 2012 6:43 PM