QP_PRICE_LIST_PUB example
To Create a New Price List:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
DECLARE 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; –apps initilization variables x_user_id NUMBER :=Fnd_Profile.VALUE(‘USER_ID’); x_resp_id NUMBER :=fnd_global.resp_id; x_appl_id NUMBER :=fnd_global.resp_appl_id; x_org_id NUMBER :=fnd_global.org_id; x_login_id NUMBER :=Fnd_Profile.VALUE(‘LOGIN_ID’); BEGIN fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id); DBMS_OUTPUT.PUT_LINE(‘API Execution Started’); FND_MSG_PUB.INITIALIZE; –Header Rec v_price_list_rec.list_header_id := qp_list_headers_b_s.nextval; v_price_list_rec.list_type_code := ‘PRL’; v_price_list_rec.operation := qp_globals.g_opr_create; v_price_list_rec.name := ‘Example1’; v_price_list_rec.description := ‘Creating sample price list’; v_price_list_rec.currency_code :=’USD’; –Line Record Values v_price_list_line_tbl(1).list_header_id := qp_list_headers_b_s.currval; v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval; 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 := 100; v_price_list_line_tbl(1).arithmetic_operator:= ‘UNIT_PRICE’; v_price_list_line_tbl(1).modifier_level_code :=’LINE’; –Attribute Record Values v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval; v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval; 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 := ‘209955’; v_pricing_attr_tbl(1).product_uom_code := ‘Ea’; 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 Enter Item Into 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 ); COMMIT; IF (v_return_Status =’S’) THEN DBMS_OUTPUT.PUT_LINE(‘API Executed Successfully’); DBMS_OUTPUT.PUT_LINE(v_return_status); DBMS_OUTPUT.PUT_LINE(v_msg_count); DBMS_OUTPUT.PUT_LINE(v_msg_data); ELSE IF v_msg_count > 0 THEN FOR l_index in 1..v_msg_count LOOP dbms_output.put_line(l_index || ‘.’ || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255)); END LOOP; END IF; DBMS_OUTPUT.PUT_LINE(‘API Not Executed Successfully ‘); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘ERROR=’||sqlerrm); END; |
To update the existing price list with a new price list line:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
DECLARE 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; –apps initilization variables x_user_id NUMBER :=Fnd_Profile.VALUE(‘USER_ID’); x_resp_id NUMBER :=fnd_global.resp_id; x_appl_id NUMBER :=fnd_global.resp_appl_id; x_org_id NUMBER :=fnd_global.org_id; x_login_id NUMBER :=Fnd_Profile.VALUE(‘LOGIN_ID’); BEGIN fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id); DBMS_OUTPUT.PUT_LINE(‘API Execution Started’); FND_MSG_PUB.INITIALIZE; –Header Rec v_price_list_rec.list_header_id := 230566; v_price_list_rec.list_type_code := ‘PRL’; v_price_list_rec.operation := qp_globals.g_opr_update; –Line Record Values v_price_list_line_tbl(1).list_header_id := 230566; v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval; 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 := 100; v_price_list_line_tbl(1).arithmetic_operator:= ‘UNIT_PRICE’; v_price_list_line_tbl(1).modifier_level_code :=’LINE’; –Attribute Record Values v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval; v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval; 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 := ‘209957’; v_pricing_attr_tbl(1).product_uom_code := ‘Ea’; 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 Enter Item Into 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 ); COMMIT; IF (v_return_Status =’S’) THEN DBMS_OUTPUT.PUT_LINE(‘API Executed Successfully’); DBMS_OUTPUT.PUT_LINE(v_return_status); ELSE IF v_msg_count > 0 THEN FOR l_index in 1..v_msg_count LOOP dbms_output.put_line(l_index || ‘.’ || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255)); END LOOP; END IF; DBMS_OUTPUT.PUT_LINE(‘API Not Executed Successfully ‘); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘ERROR=’||sqlerrm); END; |