[Home] [Help]
PACKAGE BODY: APPS.RCV_BILL_UPDATING_SV
Source
1 PACKAGE BODY RCV_BILL_UPDATING_SV AS
2 /* $Header: RCVBLUPB.pls 120.2 2006/03/21 22:53:02 samanna noship $*/
3
4 /** June 07, 1999, bgu
5 * AP is no longer maintaining base_amount_billed, po will remove all reference
6 * to the field.
7 */
8 PROCEDURE ap_update_po_distributions( X_po_distribution_id IN NUMBER,
9 X_quantity_billed IN NUMBER,
10 X_uom_lookup_code IN VARCHAR2,
11 X_amount_billed IN NUMBER,
12 X_matching_basis IN VARCHAR2) IS
13
14 X_progress VARCHAR2(3) := '000';
15
16 X_po_uom VARCHAR2(25) := '';
17 X_item_id NUMBER := 0;
18 X_po_quantity_billed NUMBER := 0;
19 X_po_amount_billed NUMBER := 0;
20 X_to_po_rate NUMBER := 1;
21
22
23 BEGIN
24
25 SELECT
26 pl.unit_meas_lookup_code,
27 pl.item_id
28 INTO
29 X_po_uom,
30 X_item_id
31 FROM
32 po_distributions pd,
33 po_lines pl
34 WHERE
35 pd.po_distribution_id = X_po_distribution_id AND
36 pl.po_line_id = pd.po_line_id;
37
38 X_progress := '001';
39
40 /* Get UOM conversion rates */
41
42 IF(X_matching_basis = 'QUANTITY') THEN
43 X_to_po_rate := po_uom_s.po_uom_convert(X_uom_lookup_code, X_po_uom, X_item_id);
44 X_progress := '002';
45
46 /* Calculate the quantity with UOM info */
47 X_po_quantity_billed := round( (nvl(X_quantity_billed,0) * X_to_po_rate) ,15);
48 END IF;
49
50 /* Calculate the amount with new info */
51 X_po_amount_billed := nvl(X_amount_billed,0);
52
53 X_progress := '003';
54
55 /* Update PO_DISTRIBUTIONS */
56 --Bug#2602981.Changed the X_po_quantity_billed to round(X_po_quantity_billed,15)
57
58 /* Bug 4305628: For Planned Purchase orders, the quantity/amount billed on the
59 ** Scheduled Release (SR) as well as the backing order (PPO)
60 ** should be updated.
61 */
62 UPDATE po_distributions_all pod
63 SET pod.quantity_billed = nvl(pod.quantity_billed,0) + X_po_quantity_billed,
64 pod.amount_billed = nvl(pod.amount_billed,0) + X_po_amount_billed
65 WHERE po_distribution_id = X_po_distribution_id
66 OR ( pod.distribution_type = 'PLANNED'
67 AND pod.po_distribution_id = ( SELECT pod2.source_distribution_id
68 FROM po_distributions pod2
69 WHERE pod2.distribution_type = 'SCHEDULED'
70 AND pod2.po_distribution_id = X_po_distribution_id)
71 );
72
73 X_progress := '004';
74
75 EXCEPTION
76
77 when others then
78 po_message_s.sql_error('ap_update_po_distributions', X_progress, sqlcode);
79 raise;
80
81 END ap_update_po_distributions;
82
83
84
85 PROCEDURE ap_update_po_line_locations( X_po_line_location_id IN NUMBER,
86 X_quantity_billed IN NUMBER,
87 X_uom_lookup_code IN VARCHAR2,
88 X_amount_billed IN NUMBER,
89 X_matching_basis IN VARCHAR2) IS
90
91 X_progress VARCHAR2(3) := '000';
92
93 X_po_uom VARCHAR2(25) := '';
94 X_item_id NUMBER := 0;
95 X_po_quantity_billed NUMBER := 0;
96 X_po_amount_billed NUMBER := 0;
97 X_to_po_rate NUMBER := 1;
98
99
100 BEGIN
101
102 SELECT
103 pl.unit_meas_lookup_code,
104 pl.item_id
105 INTO
106 X_po_uom,
107 X_item_id
108 FROM
109 po_line_locations ll,
110 po_lines pl
111 WHERE
112 ll.line_location_id = X_po_line_location_id AND
113 pl.po_line_id = ll.po_line_id;
114
115 X_progress := '001';
116
117 /* Get UOM conversion rates */
118
119 IF( x_matching_basis = 'QUANTITY') THEN
120 X_to_po_rate := po_uom_s.po_uom_convert(X_uom_lookup_code, X_po_uom, X_item_id);
121
122 X_progress := '002';
123
124 /* Calculate the quantity with UOM info */
125 /* and amount with new info */
126 X_po_quantity_billed := round((nvl(X_quantity_billed,0) * X_to_po_rate),15);
127 ELSIF X_matching_basis = 'AMOUNT' THEN
128 X_po_amount_billed := nvl(X_amount_billed,0);
129 END IF;
130
131 X_progress := '003';
132
133 /* Update PO_LINE_LOCATIONS */
134 --Bug#2602981. Changed the X_po_quantity_billed to round(X_po_quantity_billed,15)
135
136 /* Bug 4305628: For Planned Purchase orders, the quantity/amount billed on the
137 ** Scheduled Release (SR) as well as the backing order (PPO)
138 ** should be updated.
139 */
140 UPDATE po_line_locations_all pll
141 SET pll.quantity_billed = nvl(pll.quantity_billed,0) + X_po_quantity_billed,
142 pll.amount_billed = nvl(pll.amount_billed,0) + X_po_amount_billed
143 WHERE pll.line_location_id = X_po_line_location_id
144 OR ( pll.shipment_type = 'PLANNED'
145 AND pll.line_location_id = ( SELECT pll2.source_shipment_id
146 FROM po_line_locations pll2
147 WHERE pll2.shipment_type = 'SCHEDULED'
148 AND pll2.line_location_id = X_po_line_location_id)
149 );
150
151 X_progress := '004';
152
153 EXCEPTION
154
155 when others then
156 po_message_s.sql_error('ap_update_po_line_locations', X_progress, sqlcode);
157 raise;
158
159 END ap_update_po_line_locations;
160
161
162
163 PROCEDURE ap_update_rcv_transactions( X_rcv_transaction_id IN NUMBER,
164 X_quantity_billed IN NUMBER,
165 X_uom_lookup_code IN VARCHAR2,
166 X_amount_billed IN NUMBER,
167 X_matching_basis IN VARCHAR2) IS
168 X_progress VARCHAR2(3) := '000';
169
170 X_rcv_uom VARCHAR2(25) := '';
171 X_item_id NUMBER := 0;
172 X_rcv_quantity_billed NUMBER := 0;
173 X_rcv_amount_billed NUMBER := 0;
174 X_to_rcv_rate NUMBER := 1;
175
176
177 BEGIN
178
179 SELECT
180 rt.unit_of_measure,
181 NVL(rt.quantity_billed, 0),
182 NVL(rt.amount_billed, 0),
183 rs.item_id
184 INTO
185 X_rcv_uom,
186 X_rcv_quantity_billed,
187 X_rcv_amount_billed,
188 X_item_id
189 FROM
190 rcv_transactions rt,
191 rcv_shipment_lines rs
192 WHERE
193 rt.transaction_id = X_rcv_transaction_id AND
194 rs.shipment_line_id = rt.shipment_line_id;
195
196 X_progress := '001';
197
198 /* Get UOM conversion rates */
199
200 IF (x_matching_basis = 'QUANTITY') THEN
201 X_to_rcv_rate := po_uom_s.po_uom_convert(X_uom_lookup_code, X_rcv_uom, X_item_id);
202
203 X_progress := '002';
204
205 /* Calculate the quantity with UOM info */
206 X_rcv_quantity_billed := X_rcv_quantity_billed + (nvl(X_quantity_billed,0) * X_to_rcv_rate);
207 END IF;
208
209 /* Update the amount with new info */
210 X_rcv_amount_billed := X_rcv_amount_billed + X_amount_billed;
211
212 X_progress := '003';
213
214 /* Update RCV_TRANSACTIONS */
215 --Bug#2602981.Changed the X_rcv_quantity_billed to round(X_rcv_quantity_billed,15)
216 UPDATE
217 rcv_transactions
218 SET
219 quantity_billed = round(X_rcv_quantity_billed,15),
220 amount_billed = X_rcv_amount_billed
221 WHERE
222 transaction_id = X_rcv_transaction_id;
223
224 X_progress := '004';
225
226 EXCEPTION
227
228 when others then
229 po_message_s.sql_error('ap_update_rcv_transactions', X_progress, sqlcode);
230 raise;
231
232 END ap_update_rcv_transactions;
233
234
235
236 END RCV_BILL_UPDATING_SV;