API to create and update sale order price list in oracle apps

API to create and update sale order price list in oracle apps

In this post , we are going to write a PLSQL code and oracle API to Create and update sale order price list in oracle apps. Using this Below script you are Able to Create and Update Sale Orders Prices List in Oracle Apps.

CREATE OR REPLACE PROCEDURE APPS.xx_pricelist ( p_list_header_id IN NUMBER, p_operand IN NUMBER, p_product_attr_value IN VARCHAR2, p_uom IN VARCHAR2 ) IS v_return_status VARCHAR2 (1) := NULL; v_msg_count NUMBER := 0; v_msg_data VARCHAR2 (2000); v_price_list_rec qp_price_list_pub.price_list_rec_type; v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type; v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type; v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type; v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type; v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type; ppr_price_list_rec qp_price_list_pub.price_list_rec_type; ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type; ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type; ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type; ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type; ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type; ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type; ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type; BEGIN v_price_list_rec.list_header_id := p_list_header_id; v_price_list_rec.list_type_code := 'PRL'; v_price_list_rec.operation := qp_globals.g_opr_update; v_price_list_line_tbl (1).list_header_id := p_list_header_id; v_price_list_line_tbl (1).list_line_id := fnd_api.g_miss_num; v_price_list_line_tbl (1).list_line_type_code := 'PLL'; v_price_list_line_tbl (1).operation := qp_globals.g_opr_create; v_price_list_line_tbl (1).operand := p_operand; v_price_list_line_tbl (1).arithmetic_operator := 'UNIT_PRICE'; -- v_price_list_line_tbl (1).start_date_active := '05-OCT-2011'; -- v_price_list_line_tbl (1).start_date_active := '05-OCT-2011'; -- v_price_list_line_tbl (1).organization_id := NULL; v_pricing_attr_tbl (1).pricing_attribute_id := fnd_api.g_miss_num; v_pricing_attr_tbl (1).list_line_id := fnd_api.g_miss_num; ---- v_pricing_attr_tbl (1).product_attribute_context := 'Item'; -- v_pricing_attr_tbl (1).product_attribute := 'Item Number'; v_pricing_attr_tbl (1).product_attribute_context := 'ITEM'; v_pricing_attr_tbl (1).product_attribute := 'PRICING_ATTRIBUTE1'; v_pricing_attr_tbl (1).product_attr_value := p_product_attr_value; v_pricing_attr_tbl (1).product_uom_code := p_uom; v_pricing_attr_tbl (1).excluder_flag := 'N'; v_pricing_attr_tbl (1).attribute_grouping_no := 1; v_pricing_attr_tbl (1).price_list_line_index := 1; v_pricing_attr_tbl (1).operation := qp_globals.g_opr_create; DBMS_OUTPUT.put_line ('Calling API to insert Price List'); qp_price_list_pub.process_price_list (p_api_version_number => 1, p_init_msg_list => fnd_api.g_true, p_return_values => fnd_api.g_false, p_commit => fnd_api.g_false, x_return_status => v_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data, p_price_list_rec => v_price_list_rec, p_price_list_line_tbl => v_price_list_line_tbl, p_pricing_attr_tbl => v_pricing_attr_tbl, x_price_list_rec => ppr_price_list_rec, x_price_list_val_rec => ppr_price_list_val_rec, x_price_list_line_tbl => ppr_price_list_line_tbl, x_qualifiers_tbl => ppr_qualifiers_tbl, x_qualifiers_val_tbl => ppr_qualifiers_val_tbl, x_pricing_attr_tbl => ppr_pricing_attr_tbl, x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl, x_price_list_line_val_tbl => ppr_price_list_line_val_tbl ); IF v_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull'); ELSE DBMS_OUTPUT.put_line ('The Item loading into the price list Failed'); ROLLBACK; FOR i IN 1 .. v_msg_count LOOP v_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F'); DBMS_OUTPUT.put_line (i || ') ' || v_msg_data); END LOOP; END IF; END; /

After this you need to create one custom table

CREATE TABLE APPS.XXPRICE_LST_TBL ( ORG_ID NUMBER, PRICE_LIST VARCHAR2(400 BYTE), ITEM_CODE VARCHAR2(400 BYTE), ATTR_VALUE NUMBER, STATUS VARCHAR2(40 BYTE), ERR_MSG VARCHAR2(400 BYTE), ENTRY_ID NUMBER )

This table will store all information of Sale order item and which price list will be assigned and what rate will be provided to this item. So upload all the price list data in this staging table.

Then you need to create this final procedure to load the price list data from staging table to oracle standard price list base tables.

CREATE OR REPLACE PROCEDURE APPS.xx_plist_proc IS CURSOR plist_info_stg IS SELECT * FROM XXPRICE_LST_TBL WHERE NVL (status, 'N') <> 'P'; v_status VARCHAR2 (1); v_err_msg VARCHAR2 (100); v_item_id NUMBER; v_primary_uom_code VARCHAR2 (3); v_list_header_id NUMBER; BEGIN FOR z1 IN plist_info_stg LOOP v_status:= 'P'; v_err_msg := null; BEGIN SELECT inventory_item_id, primary_uom_code INTO v_item_id, v_primary_uom_code FROM mtl_system_items_b msi WHERE segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5 = z1.item_code AND msi.organization_id = z1.org_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_status := 'E'; v_err_msg := 'Inventory Item ID not found'; END; BEGIN SELECT list_header_id INTO v_list_header_id FROM qp_list_headers_tl WHERE NAME = z1.price_list; EXCEPTION WHEN NO_DATA_FOUND THEN v_status := 'E'; v_err_msg := 'Price list ID not found'; END; IF v_item_id IS NOT NULL AND v_primary_uom_code IS NOT NULL AND v_list_header_id IS NOT NULL THEN xx_pricelist (v_list_header_id, to_number(z1.attr_value), v_item_id, v_primary_uom_code ); END IF; UPDATE XXPRICE_LST_TBL SET status= v_status, err_msg = v_err_msg WHERE entry_id = z1.entry_id; COMMIT; END LOOP; END; /

After this just execute this above procedure and all the data will be uploaded in Oracle.