API TO CREATE AND UPDATE SALE ORDER PRICE LIST IN ORACLE APPS
In this post , I will share you the 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.
API TO CREATE AND UPDATE SALE ORDER PRICE 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_ROHIT
(
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_rohit_plist
IS
CURSOR plist_info_stg
IS
SELECT *
FROM xxprice_rohit
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_rohit
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.
API TO CREATE AND UPDATE SALE ORDER PRICE LIST IN ORACLE APPS
0 comments:
Post a Comment