Tuesday, October 30, 2018

API: QP_PRICE_LIST_PUB Example To create and update Price Lists

API: QP_PRICE_LIST_PUB Example To create and update Price Lists


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;
-

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...