1 Reply Latest reply: Jan 18, 2013 5:23 AM by L S P.. RSS

    Tax Calculation issue using ASO_QUOTE_PUB.CREATE_QUOTE

    765417
      Hi,

      We are using ASO_QUOTE_PUB.CREATE_ORDER API to create quotes. Quotes are created correctly w/o tax. We checked all the required profiles wrt to Order Capture (ASO). But still not able to get the tax calculations on the quote.

      Surprisingly when we create Order from the same quote it brings taxes correctly.

      Attached is the code we use for the same:
      CREATE OR REPLACE PACKAGE BODY snrdr_create_quote_pkg
      IS
      PROCEDURE create_quote (
      errbuff OUT VARCHAR2,
      retcode OUT NUMBER,
      p_requisition_id IN NUMBER
      )
      IS
      l_control_rec aso_quote_pub.control_rec_type;
      l_qte_header_rec aso_quote_pub.qte_header_rec_type;
      l_qte_line_rec aso_quote_pub.qte_line_rec_type;
      l_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
      l_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
      l_hd_price_attr_tbl aso_quote_pub.price_attributes_tbl_type;
      l_hd_payment_tbl aso_quote_pub.payment_tbl_type;
      l_payment_rec aso_quote_pub.payment_rec_type;
      l_hd_shipment_rec aso_quote_pub.shipment_rec_type;
      l_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
      l_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
      l_tax_detail_rec aso_quote_pub.tax_detail_rec_type;
      l_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
      l_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
      l_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
      l_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
      l_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
      l_ln_price_attr_tbl aso_quote_pub.price_attributes_tbl_type;
      l_ln_payment_tbl aso_quote_pub.payment_tbl_type;
      l_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
      l_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
      l_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
      lx_qte_header_rec aso_quote_pub.qte_header_rec_type;
      lx_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
      lx_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
      lx_hd_price_attr_tbl aso_quote_pub.price_attributes_tbl_type;
      lx_hd_payment_tbl aso_quote_pub.payment_tbl_type;
      lx_hd_shipment_rec aso_quote_pub.shipment_rec_type;
      lx_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
      lx_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
      lx_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
      lx_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
      lx_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
      lx_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
      lx_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
      lx_ln_price_attr_tbl aso_quote_pub.price_attributes_tbl_type;
      lx_ln_payment_tbl aso_quote_pub.payment_tbl_type;
      lx_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
      lx_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
      lx_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
      lx_return_status VARCHAR2 (1);
      lx_msg_count NUMBER;
      lx_msg_data VARCHAR2 (2000);
      l_user_id fnd_user.user_id%TYPE;
      l_appl_id fnd_application.application_id%TYPE;
      l_resp_id fnd_responsibility.responsibility_id%TYPE;
      l_org_id hr_operating_units.organization_id%TYPE;
      l_sold_to_org_id hz_cust_accounts.cust_account_id%TYPE;
      l_ship_to_org_id hz_cust_site_uses_all.site_use_id%TYPE;
      l_cust_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
      l_party_id hz_parties.party_id%TYPE;
      l_price_list_id qp_list_headers_b.list_header_id%TYPE;
      l_primary_salesrep_id ra_salesreps.salesrep_id%TYPE;
      l_order_type_id oe_transaction_types_all.transaction_type_id%TYPE;
      l_order_line_type_id oe_transaction_types_all.transaction_type_id%TYPE;
      l_order_category_code oe_transaction_types_all.order_category_code%TYPE;
      l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
      l_currency_code qp_list_headers.currency_code%TYPE;
      l_party_site_id hz_party_sites.party_site_id%TYPE;
      l_quote_header_id NUMBER;
      l_quote_line_id NUMBER;
      l_quote_status_id aso_quote_statuses_b.quote_status_id%TYPE;
      l_data1 VARCHAR2 (1000);
      l_msg_index NUMBER;
      l_message VARCHAR2 (2000) := NULL;
      l_header_cnt NUMBER := 0;
      l_line_cnt NUMBER := 0;
      l_cur_user_id NUMBER;
      snrdr_exception EXCEPTION;
      l_hdr_continue_flag BOOLEAN := TRUE;
      l_line_continue_flag BOOLEAN := TRUE;
      l_hdr_error_message VARCHAR2 (2000) := NULL;
      l_line_error_message VARCHAR2 (2000) := NULL;
      l_failed_line_cnt NUMBER := 0;
      l_failed_hdr_cnt NUMBER := 0;
      l_error_message VARCHAR2(2000):=NULL;
      l_api_error_msg VARCHAR2(2000):=NULL;

      CURSOR quote_header_cur
      IS
      SELECT ssrh.*
      FROM snrdr_sim_requisition_header ssrh
      WHERE 1 = 1
      AND ssrh.process_flag = 'I'
      AND ssrh.header_id = NVL (p_requisition_id, ssrh.header_id)
      FOR UPDATE OF process_flag,
      last_update_by,
      last_update_date,
      quote_header_id,
      error_message;

      CURSOR quote_lines_cur (p_req_id NUMBER)
      IS
      SELECT ssrl.*
      FROM snrdr_sim_requisition_line ssrl
      WHERE 1 = 1
      AND ssrl.process_flag = 'I'
      AND ssrl.header_id = p_req_id
      FOR UPDATE OF process_flag,
      last_update_by,
      last_update_date,
      error_message;
      BEGIN
      l_user_id := fnd_profile.VALUE ('USER_ID');
      l_resp_id := fnd_profile.VALUE ('RESP_ID');
      l_appl_id := fnd_profile.VALUE ('RESP_APPL_ID');
      l_org_id := fnd_profile.VALUE ('ORG_ID');
      fnd_file.put_line (fnd_file.LOG, 'User ID = ' || l_user_id);
      fnd_file.put_line (fnd_file.LOG, 'Resp ID = ' || l_resp_id);
      fnd_file.put_line (fnd_file.LOG, 'Appl ID = ' || l_appl_id);
      fnd_file.put_line (fnd_file.LOG, 'ORG ID = ' || l_org_id);
      mo_global.init ('ONT');
      DBMS_APPLICATION_INFO.set_client_info (l_org_id);
      mo_global.set_policy_context ('S', l_org_id);
      fnd_file.put_line (fnd_file.LOG, 'All Set');

      FOR quote_header_rec IN quote_header_cur
      LOOP
      l_hdr_continue_flag := TRUE;
      l_hdr_error_message := NULL;
      l_control_rec.pricing_request_type := 'ASO';
      l_control_rec.header_pricing_event := 'LINE';
      l_control_rec.line_pricing_event := 'LINE';
      l_control_rec.calculate_tax_flag := 'Y';
      l_control_rec.calculate_freight_charge_flag := 'Y';
      l_control_rec.last_update_date := SYSDATE;

      -- Get Quote Header ID
      SELECT aso_quote_headers_s.NEXTVAL
      INTO l_quote_header_id
      FROM DUAL;

      fnd_file.put_line (fnd_file.LOG,
      'l_quote_header_id = ' || l_quote_header_id
      );
      l_qte_header_rec.quote_source_code := 'Order Capture Quotes';
      l_qte_header_rec.quote_header_id := l_quote_header_id;

      -- Get Quote Status ID
      BEGIN
      SELECT quote_status_id
      INTO l_quote_status_id
      FROM aso_quote_statuses_b
      WHERE status_code = 'DRAFT';

      fnd_file.put_line (fnd_file.LOG,
      'Quote Status ID : ' || l_quote_status_id
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      fnd_file.put_line (fnd_file.LOG,'Error getting Quote Status ID for Quote Status = DRAFT');
      l_hdr_error_message :=
      'Error getting Quote Status ID for Quote Status = DRAFT';
      l_hdr_continue_flag := FALSE;
      END;

      l_qte_header_rec.quote_status_id := l_quote_status_id;

      -- Get CUST_ACCOUNT_ID and PARTY_ID values
      BEGIN
      SELECT hca.cust_account_id sold_to_org_id,
      --hcsua.site_use_id ship_to_org_id,
      hcasa.cust_acct_site_id, hp.party_id, hps.party_site_id
      INTO l_sold_to_org_id,
      --l_ship_to_org_id,
      l_cust_acct_site_id, l_party_id, l_party_site_id
      FROM hz_cust_site_uses_all hcsua,
      hz_cust_acct_sites_all hcasa,
      hz_cust_accounts hca,
      hz_party_sites hps,
      hz_parties hp,
      hz_locations hl
      WHERE 1 = 1
      AND hcsua.site_use_code = 'SHIP_TO'
      AND hcsua.status = 'A'
      AND hcasa.org_id = l_org_id
      --AND hcasa.ship_to_flag = 'Y'
      AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
      AND hcasa.cust_account_id = hca.cust_account_id
      AND hcasa.party_site_id = hps.party_site_id
      AND hp.party_id = hps.party_id
      AND hcsua.primary_flag = 'Y'
      AND hl.location_id = hps.location_id
      AND hca.account_number = TO_CHAR (quote_header_rec.customer_id);

      fnd_file.put_line (fnd_file.LOG,
      'l_sold_to_org_id = ' || l_sold_to_org_id
      );
      fnd_file.put_line (fnd_file.LOG,
      'l_cust_acct_site_id = ' || l_cust_acct_site_id
      );
      fnd_file.put_line (fnd_file.LOG, 'l_party_id = ' || l_party_id);
      EXCEPTION
      WHEN OTHERS
      THEN
      fnd_file.put_line (fnd_file.LOG,'Error getting Customer Details');
      l_hdr_error_message :=
      l_error_message || 'Error getting Customer Details';
      l_hdr_continue_flag := FALSE;
      END;

      l_qte_header_rec.party_id := l_party_id;
      l_qte_header_rec.cust_account_id := l_sold_to_org_id;

      -- Get Price List ID, Primary SalesRep, Order Type ID and Order Category Code
      BEGIN
      SELECT hcsua.price_list_id, hcsua.primary_salesrep_id,
      hcsua.order_type_id, otta.order_category_code,
      otta.default_outbound_line_type_id, qlh.currency_code
      INTO l_price_list_id, l_primary_salesrep_id,
      l_order_type_id, l_order_category_code,
      l_order_line_type_id, l_currency_code
      FROM hz_cust_site_uses_all hcsua,
      oe_transaction_types_all otta,
      qp_list_headers qlh
      WHERE 1 = 1
      AND hcsua.site_use_code = 'BILL_TO'
      AND hcsua.order_type_id = otta.transaction_type_id
      AND otta.org_id = l_org_id
      AND hcsua.price_list_id = qlh.list_header_id
      AND qlh.active_flag = 'Y'
      AND hcsua.cust_acct_site_id = l_cust_acct_site_id;

      fnd_file.put_line (fnd_file.LOG,
      'l_price_list_id = ' || l_price_list_id
      );
      fnd_file.put_line (fnd_file.LOG,
      'l_primary_salesrep_id = '
      || l_primary_salesrep_id
      );
      fnd_file.put_line (fnd_file.LOG,
      'l_order_type_id = ' || l_order_type_id
      );
      fnd_file.put_line (fnd_file.LOG,
      'l_order_category_code = '
      || l_order_category_code
      );
      fnd_file.put_line (fnd_file.LOG,
      'l_order_line_type_id = '
      || l_order_line_type_id
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      fnd_file.put_line (fnd_file.LOG, 'Error getting Price List/Pri.Sales Rep./Order Type/Order Category Code');
      l_hdr_error_message :=
      l_error_message
      || 'Error getting Price List/Pri.Sales Rep./Order Type/Order Category Code';
      l_hdr_continue_flag := FALSE;
      --RAISE snrdr_exception;
      END;

      l_qte_header_rec.currency_code := l_currency_code; --required
      l_qte_header_rec.order_type_id := l_order_type_id;
      l_qte_header_rec.price_list_id := l_price_list_id;
      l_qte_header_rec.employee_person_id := 692; --l_primary_salesrep_id;
      l_qte_header_rec.quote_category_code := l_order_category_code;
      l_qte_header_rec.last_update_date := SYSDATE;
      l_qte_header_rec.invoice_to_cust_account_id := l_sold_to_org_id;
      l_qte_header_rec.invoice_to_party_site_id := l_party_site_id;
      l_qte_header_rec.sold_to_party_site_id := l_party_site_id;

      IF l_hdr_continue_flag
      THEN
      l_line_cnt := 0;
      l_failed_line_cnt := 0;

      FOR quote_lines_rec IN
      quote_lines_cur (quote_header_rec.header_id)
      LOOP
      l_line_cnt := l_line_cnt + 1;

      SELECT aso_quote_lines_s.NEXTVAL
      INTO l_quote_line_id
      FROM DUAL;

      fnd_file.put_line (fnd_file.LOG,
      'l_quote_line_id = ' || l_quote_line_id
      );
      l_qte_line_tbl (l_line_cnt).operation_code := 'CREATE';
      l_qte_line_tbl (l_line_cnt).quote_header_id :=
      l_quote_header_id;
      l_qte_line_tbl (l_line_cnt).quote_line_id := l_quote_line_id;

      BEGIN
      SELECT DISTINCT (inventory_item_id)
      INTO l_inventory_item_id
      FROM mtl_system_items_b
      WHERE segment1 = quote_lines_rec.item_number;

      fnd_file.put_line (fnd_file.LOG,
      'l_inventory_item_id = '
      || l_inventory_item_id
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      fnd_file.put_line (fnd_file.LOG,'Item ' || quote_lines_rec.item_number
      || ' not Setup');
      l_line_error_message :=
      'Item ' || quote_lines_rec.item_number
      || ' not Setup';
      l_line_continue_flag := FALSE;
      END;

      l_qte_line_tbl (l_line_cnt).inventory_item_id :=
      l_inventory_item_id;
      l_qte_line_tbl (l_line_cnt).quantity :=
      quote_lines_rec.qty_requested;
      l_qte_line_tbl (l_line_cnt).line_category_code := 'ORDER';
      l_qte_line_tbl (l_line_cnt).line_number := l_line_cnt;
      --l_qte_line_tbl (l_line_cnt).order_line_type_id :=
      -- l_order_line_type_id;
      l_qte_line_tbl (l_line_cnt).organization_id := l_org_id;
      l_qte_line_tbl (l_line_cnt).recalculate_flag := 'Y';

      IF NOT l_line_continue_flag
      THEN
      UPDATE snrdr_sim_requisition_line
      SET process_flag = 'E',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message = l_line_error_message
      WHERE CURRENT OF quote_lines_cur;

      l_failed_line_cnt := l_failed_line_cnt + 1;
      ELSE
      UPDATE snrdr_sim_requisition_line
      SET process_flag = 'S',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message = NULL
      WHERE CURRENT OF quote_lines_cur;
      END IF;
      END LOOP;
      if l_line_cnt = 0 then
      UPDATE snrdr_sim_requisition_header
      SET process_flag = 'E',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message = 'Requisition Lines Data Missing'
      WHERE CURRENT OF quote_header_cur;
      l_line_continue_flag:=FALSE;
      l_failed_hdr_cnt := l_failed_hdr_cnt + 1;
      fnd_file.put_line (fnd_file.LOG,
      'Requisition Lines Data for SIM Requisition ' || quote_header_rec.header_id ||' is Missing'
      );

      end if;
      if l_failed_line_cnt > 0 then
      UPDATE snrdr_sim_requisition_header
      SET process_flag = 'E',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message = 'Requisition Line Failed Validation'
      WHERE CURRENT OF quote_header_cur;
      end if;
      ELSE
      UPDATE snrdr_sim_requisition_header
      SET process_flag = 'E',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message = l_hdr_error_message
      WHERE CURRENT OF quote_header_cur;

      l_failed_hdr_cnt := l_failed_hdr_cnt + 1;
      END IF;

      IF ( l_hdr_continue_flag
      AND l_line_continue_flag
      AND l_failed_line_cnt = 0
      AND l_line_cnt > 0
      )
      THEN
      fnd_file.put_line (fnd_file.LOG,
      'API aso_quote_pub.create_quote STARTED...'
      );

      BEGIN
      aso_quote_pub.create_quote
      (p_api_version_number => 2.0,
      p_init_msg_list => fnd_api.g_false,
      p_commit => fnd_api.g_false,
      p_control_rec => l_control_rec,
      p_qte_header_rec => l_qte_header_rec,
      p_qte_line_tbl => l_qte_line_tbl,
      p_hd_payment_tbl => l_hd_payment_tbl,
      p_hd_shipment_rec => l_hd_shipment_rec,
      p_hd_tax_detail_tbl => l_hd_tax_detail_tbl,
      x_qte_header_rec => lx_qte_header_rec,
      x_qte_line_tbl => lx_qte_line_tbl,
      x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
      x_hd_price_attributes_tbl => lx_hd_price_attr_tbl,
      x_hd_payment_tbl => lx_hd_payment_tbl,
      x_hd_shipment_rec => lx_hd_shipment_rec,
      x_hd_freight_charge_tbl => lx_hd_freight_charge_tbl,
      x_hd_tax_detail_tbl => lx_hd_tax_detail_tbl,
      x_line_attr_ext_tbl => lx_line_attr_ext_tbl,
      x_line_rltship_tbl => lx_line_rltship_tbl,
      x_price_adjustment_tbl => lx_price_adjustment_tbl,
      x_price_adj_attr_tbl => lx_price_adj_attr_tbl,
      x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl,
      x_ln_price_attributes_tbl => lx_ln_price_attr_tbl,
      x_ln_payment_tbl => lx_ln_payment_tbl,
      x_ln_shipment_tbl => lx_ln_shipment_tbl,
      x_ln_freight_charge_tbl => lx_ln_freight_charge_tbl,
      x_ln_tax_detail_tbl => lx_ln_tax_detail_tbl,
      x_return_status => lx_return_status,
      x_msg_count => lx_msg_count,
      x_msg_data => lx_msg_data
      );

      IF lx_return_status = fnd_api.g_ret_sts_success
      THEN
      fnd_file.put_line (fnd_file.LOG,
      'Quote Number '
      || lx_qte_header_rec.quote_number
      || ' Created Successfully'
      );
      fnd_file.put_line (fnd_file.LOG,
      'Quote Header ID '
      || lx_qte_header_rec.quote_header_id
      || ' Created Successfully'
      );

      UPDATE snrdr_sim_requisition_header
      SET process_flag = 'S',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      quote_header_id = lx_qte_header_rec.quote_header_id
      WHERE CURRENT OF quote_header_cur;

      UPDATE snrdr_sim_requisition_line
      SET process_flag = 'S',
      last_update_by = l_user_id,
      last_update_date = SYSDATE
      WHERE header_id = quote_header_rec.header_id;
      ELSE
      l_api_error_msg:=NULL;
      fnd_file.put_line (fnd_file.LOG,
      'Error Creating Quote for '
      || quote_header_rec.header_id
      );
      fnd_file.put_line (fnd_file.LOG, 'Error Messages');
      l_message := fnd_msg_pub.get (p_encoded => fnd_api.g_false);

      WHILE (l_message IS NOT NULL)
      LOOP
      fnd_file.put_line (fnd_file.LOG, l_message);
      l_message :=
      fnd_msg_pub.get (p_encoded => fnd_api.g_false);
      l_api_error_msg:= l_api_error_msg || l_message;
      END LOOP;

      UPDATE snrdr_sim_requisition_header
      SET process_flag = 'E',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message = l_api_error_msg
      WHERE CURRENT OF quote_header_cur;

      l_failed_hdr_cnt := l_failed_hdr_cnt + 1;

      UPDATE snrdr_sim_requisition_line
      SET process_flag = 'E',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message = l_api_error_msg
      WHERE header_id = quote_header_rec.header_id;
      END IF;
      EXCEPTION
      WHEN OTHERS
      THEN
      fnd_file.put_line (fnd_file.LOG, 'API Unexpected Error');
      fnd_file.put_line (fnd_file.LOG, SUBSTR (SQLERRM, 1, 1500));
      l_api_error_msg:= SQLCODE || SUBSTR (SQLERRM, 1, 1500);
      UPDATE snrdr_sim_requisition_header
      SET process_flag = 'E',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message = l_api_error_msg
      WHERE CURRENT OF quote_header_cur;

      UPDATE snrdr_sim_requisition_line
      SET process_flag = 'E',
      last_update_by = l_user_id,
      last_update_date = SYSDATE,
      error_message=l_api_error_msg
      WHERE header_id = quote_header_rec.header_id;
      END;

      l_header_cnt := l_header_cnt + 1;
      END IF;


      END LOOP;

      IF l_header_cnt = 0 and l_failed_hdr_cnt <> 0 and l_failed_line_cnt <> 0
      THEN
      fnd_file.put_line (fnd_file.LOG,
      'There are no records for process.');
      END IF;

      IF l_failed_hdr_cnt > 0 OR l_failed_line_cnt > 0
      THEN
      fnd_file.put_line (fnd_file.LOG, 'There are error records .');
      retcode := 2;
      errbuff := 'There are error records in process';
      END IF;

      COMMIT;
      EXCEPTION
      WHEN OTHERS
      THEN
      fnd_file.put_line
      (fnd_file.LOG,
      'Error in snrdr_create_quote_pkg.create_quote procedure '
      || SUBSTR (SQLERRM, 1, 1500)
      );
      retcode := 2;
      END create_quote;
      END snrdr_create_quote_pkg;
      /




      Any idea whats going wrong here?

      Thanks and Regards.