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;
/
No comments:
Post a Comment