DECLARE
-- pragma autonomous_transaction;
p_api_version_number NUMBER;
p_init_msg_list VARCHAR2 (32767);
p_commit VARCHAR2 (32767);
p_control_rec aso_quote_pub.control_rec_type;
p_qte_header_rec aso_quote_pub.qte_header_rec_type;
p_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
p_hd_payment_tbl aso_quote_pub.payment_tbl_type;
p_hd_shipment_rec aso_quote_pub.shipment_rec_type;
p_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
p_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
p_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
p_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
p_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
p_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
p_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
p_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
p_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
p_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
p_ln_payment_tbl aso_quote_pub.payment_tbl_type;
p_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
p_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
p_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
x_qte_header_rec aso_quote_pub.qte_header_rec_type;
x_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
x_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
x_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
x_hd_payment_tbl aso_quote_pub.payment_tbl_type;
x_hd_shipment_rec aso_quote_pub.shipment_rec_type;
x_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
x_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
x_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
x_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
x_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
x_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
x_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
x_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
x_ln_payment_tbl aso_quote_pub.payment_tbl_type;
x_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
x_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
x_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
v_party_id NUMBER;
v_account_id NUMBER;
v_user_id NUMBER;
v_org_id NUMBER;
v_party_site_id NUMBER;
v_order_type_id NUMBER;
v_quote_name VARCHAR2 (200);
v_currency VARCHAR2 (10);
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_cost_quote_header_id VARCHAR2 (1000);
p_quote_header_id NUMBER := &p_quote_hdr_id;
v_pricelist_id NUMBER;
v_item_num VARCHAR2 (30) := &p_item_num;
v_inv_org_code VARCHAR2 (50) := &p_invorg_code;
v_item_id NUMBER;
v_inv_org_id NUMBER;
v_resp_id NUMBER := fnd_profile.value('RESP_ID');
v_appl_id NUMBER := fnd_profile.value('APPL_ID');
BEGIN
fnd_global.APPS_INITIALIZE (v_user_id, v_resp_id, v_appl_id);
p_api_version_number := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_false;
-- P_CONTROL_REC := NULL; Modify the code to initialize this parameter
SELECT cust_party_id,
order_type_id,
SUBSTR (quote_name, 1, 200),
org_id,
created_by,
currency_code,
quote_source_code,
resource_id,
resource_grp_id
INTO v_party_id,
v_order_type_id,
v_quote_name,
v_org_id,
v_user_id,
v_currency,
p_qte_header_rec.quote_source_code,
p_qte_header_rec.resource_id,
p_qte_header_rec.resource_grp_id
FROM aso_quote_headers_all
WHERE quote_header_id = p_quote_header_id;
DBMS_OUTPUT.put_line ('after party id' || v_party_id);
-- Find the cust account primary bill site's party site id
BEGIN
SELECT hca.cust_account_id, cas.party_site_id
INTO v_account_id, v_party_site_id
FROM hz_parties party,
hz_cust_accounts hca,
hz_cust_acct_sites_all cas,
hz_cust_site_uses_all csu
WHERE party.party_id = v_party_id
AND hca.party_id = party.party_id
AND hca.cust_account_id = v_account_id
AND cas.cust_account_id = hca.cust_account_id
AND cas.status = 'A'
AND cas.cust_acct_site_id = csu.cust_acct_site_id
AND csu.site_use_code = 'BILL_TO'
AND csu.primary_flag = 'Y'
AND csu.status = 'A'
AND csu.org_id = v_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
BEGIN
SELECT ps.party_site_id
INTO v_party_site_id
FROM hz_parties party,
hz_party_sites ps,
hz_party_site_uses psu
WHERE party.party_id = v_party_id
AND party.party_id = ps.party_id
AND ps.party_site_id = psu.party_site_id
AND ps.status = 'A'
AND psu.site_use_type = 'BILL_TO'
AND psu.primary_per_type = 'Y'
AND psu.status = 'A';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
SELECT ps.party_site_id
INTO v_party_site_id
FROM hz_parties party, hz_party_sites ps
WHERE party.party_id = v_party_id
AND party.party_id = ps.party_id
AND ps.status = 'A'
AND ROWNUM < 2;
END;
END;
-- p_qte_header_rec := NULL; Modify the code to initialize this parameter
-----------------------------------------------------
-- build the header and shipment rec to pass to Oracle's API
-----------------------------------------------------
v_user_id := fnd_global.user_id;
v_org_id := fnd_global.org_id;
p_qte_header_rec.created_by := v_user_id;
p_qte_header_rec.last_updated_by := v_user_id;
p_qte_header_rec.org_id := v_org_id;
p_qte_header_rec.quote_source_code := 'Order Capture Quotes';
p_qte_header_rec.cust_party_id := v_party_id;
p_qte_header_rec.cust_account_id := v_account_id;
p_qte_header_rec.invoice_to_cust_account_id := v_account_id;
p_qte_header_rec.invoice_to_party_site_id := v_party_site_id;
p_qte_header_rec.invoice_to_cust_party_id := v_party_id;
p_qte_header_rec.quote_name := SUBSTR ('Test Quote', 1, 80);
p_qte_header_rec.currency_code := v_currency;
p_qte_header_rec.order_type_id := v_order_type_id;
--p_qte_header_rec.quote_status_id;
--COST ESTIMATION
SELECT quote_status_id
INTO p_qte_header_rec.quote_status_id
FROM aso_quote_statuses_vl
WHERE status_code = 'DRAFT';
--p_qte_header_rec.price_list_id ;
--cost price list
SELECT list_header_id
INTO p_qte_header_rec.price_list_id
FROM qp_list_headers_vl
WHERE list_header_id = v_pricelist_id;
--AND list_type_code = 'PRL';
p_hd_shipment_rec.operation_code := 'CREATE';
p_hd_shipment_rec.ship_to_party_site_id := v_party_site_id;
SELECT inventory_item_id, organization_id
INTO v_item_id, v_inv_org_id
FROM mtl_system_items_b msi, mtl_parameters mp
WHERE msi.segment1 = v_item_num
AND msi.organization_id = mp.organization_id
AND mp.organization_code = v_inv_org_code;
----------------- LINES ---------------------------------
P_Qte_Line_Tbl (1).organization_id := v_inv_org_id;
P_Qte_Line_Tbl (1).operation_code := 'CREATE';
P_Qte_Line_Tbl (1).inventory_item_id := v_item_id;
P_Qte_Line_Tbl (1).quantity := 1;
P_Qte_Line_Tbl (1).uom_code := 'EA';
P_Qte_Line_Tbl (1).line_category_code := 'ORDER';
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
DBMS_OUTPUT.put_line ('before creating quote');
apps.aso_quote_pub.create_quote (p_api_version_number,
p_init_msg_list,
p_commit,
p_control_rec,
p_qte_header_rec,
p_hd_price_attributes_tbl,
p_hd_payment_tbl,
p_hd_shipment_rec,
p_hd_freight_charge_tbl,
p_hd_tax_detail_tbl,
p_qte_line_tbl,
p_qte_line_dtl_tbl,
p_line_attr_ext_tbl,
p_line_rltship_tbl,
p_price_adjustment_tbl,
p_price_adj_attr_tbl,
p_price_adj_rltship_tbl,
p_ln_price_attributes_tbl,
p_ln_payment_tbl,
p_ln_shipment_tbl,
p_ln_freight_charge_tbl,
p_ln_tax_detail_tbl,
x_qte_header_rec,
x_qte_line_tbl,
x_qte_line_dtl_tbl,
x_hd_price_attributes_tbl,
x_hd_payment_tbl,
x_hd_shipment_rec,
x_hd_freight_charge_tbl,
x_hd_tax_detail_tbl,
x_line_attr_ext_tbl,
x_line_rltship_tbl,
x_price_adjustment_tbl,
x_price_adj_attr_tbl,
x_price_adj_rltship_tbl,
x_ln_price_attributes_tbl,
x_ln_payment_tbl,
x_ln_shipment_tbl,
x_ln_freight_charge_tbl,
x_ln_tax_detail_tbl,
x_return_status,
x_msg_count,
x_msg_data);
DBMS_OUTPUT.put_line ('x_qte_header_rec.quote_header_id = '
|| x_qte_header_rec.quote_header_id);
DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);
IF x_qte_header_rec.quote_header_id IS NOT NULL
THEN
x_cost_quote_header_id := x_qte_header_rec.quote_header_id;
END IF;
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line ('Error ' || fnd_msg_pub.get (i, 'F'));
END LOOP;
DBMS_OUTPUT.put_line ('end cost api');
END;
/
No comments:
Post a Comment