[Home] [Help]
PACKAGE BODY: APPS.RCV_BILL_UPDATING_SV
Source
1 PACKAGE BODY RCV_BILL_UPDATING_SV AS
2 /* $Header: RCVBLUPB.pls 120.3 2011/08/24 05:48:34 ksivasa ship $*/
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 --Bug 12886371. Added the updation of who columns
63 UPDATE po_distributions_all pod
64 SET pod.quantity_billed = nvl(pod.quantity_billed,0) + X_po_quantity_billed,
65 pod.amount_billed = nvl(pod.amount_billed,0) + X_po_amount_billed,
66 pod.last_updated_by = fnd_global.user_id,
67 pod.last_update_date=sysdate,
68 pod.last_update_login=fnd_global.login_id
69 WHERE po_distribution_id = X_po_distribution_id
70 OR ( pod.distribution_type = 'PLANNED'
71 AND pod.po_distribution_id = ( SELECT pod2.source_distribution_id
72 FROM po_distributions pod2
73 WHERE pod2.distribution_type = 'SCHEDULED'
74 AND pod2.po_distribution_id = X_po_distribution_id)
75 );
76
77 X_progress := '004';
78
79 EXCEPTION
80
81 when others then
82 po_message_s.sql_error('ap_update_po_distributions', X_progress, sqlcode);
83 raise;
84
85 END ap_update_po_distributions;
86
87
88
89 PROCEDURE ap_update_po_line_locations( X_po_line_location_id IN NUMBER,
90 X_quantity_billed IN NUMBER,
91 X_uom_lookup_code IN VARCHAR2,
92 X_amount_billed IN NUMBER,
93 X_matching_basis IN VARCHAR2) IS
94
95 X_progress VARCHAR2(3) := '000';
96
97 X_po_uom VARCHAR2(25) := '';
98 X_item_id NUMBER := 0;
99 X_po_quantity_billed NUMBER := 0;
100 X_po_amount_billed NUMBER := 0;
101 X_to_po_rate NUMBER := 1;
102
103
104 BEGIN
105
106 SELECT
107 pl.unit_meas_lookup_code,
108 pl.item_id
109 INTO
110 X_po_uom,
111 X_item_id
112 FROM
113 po_line_locations ll,
114 po_lines pl
115 WHERE
116 ll.line_location_id = X_po_line_location_id AND
117 pl.po_line_id = ll.po_line_id;
118
119 X_progress := '001';
120
121 /* Get UOM conversion rates */
122
123 IF( x_matching_basis = 'QUANTITY') THEN
124 X_to_po_rate := po_uom_s.po_uom_convert(X_uom_lookup_code, X_po_uom, X_item_id);
125
126 X_progress := '002';
127
128 /* Calculate the quantity with UOM info */
129 /* and amount with new info */
130 X_po_quantity_billed := round((nvl(X_quantity_billed,0) * X_to_po_rate),15);
131 ELSIF X_matching_basis = 'AMOUNT' THEN
132 X_po_amount_billed := nvl(X_amount_billed,0);
133 END IF;
134
135 X_progress := '003';
136
137 /* Update PO_LINE_LOCATIONS */
138 --Bug#2602981. Changed the X_po_quantity_billed to round(X_po_quantity_billed,15)
139
140 /* Bug 4305628: For Planned Purchase orders, the quantity/amount billed on the
141 ** Scheduled Release (SR) as well as the backing order (PPO)
142 ** should be updated.
143 */
144 --Bug 12886371. Added the updation of who columns
145 UPDATE po_line_locations_all pll
146 SET pll.quantity_billed = nvl(pll.quantity_billed,0) + X_po_quantity_billed,
147 pll.amount_billed = nvl(pll.amount_billed,0) + X_po_amount_billed,
148 pll.last_updated_by = fnd_global.user_id,
149 pll.last_update_date=sysdate,
150 pll.last_update_login=fnd_global.login_id
151 WHERE pll.line_location_id = X_po_line_location_id
152 OR ( pll.shipment_type = 'PLANNED'
153 AND pll.line_location_id = ( SELECT pll2.source_shipment_id
154 FROM po_line_locations pll2
155 WHERE pll2.shipment_type = 'SCHEDULED'
156 AND pll2.line_location_id = X_po_line_location_id)
157 );
158
159 X_progress := '004';
160
161 EXCEPTION
162
163 when others then
164 po_message_s.sql_error('ap_update_po_line_locations', X_progress, sqlcode);
165 raise;
166
167 END ap_update_po_line_locations;
168
169
170
171 PROCEDURE ap_update_rcv_transactions( X_rcv_transaction_id IN NUMBER,
172 X_quantity_billed IN NUMBER,
173 X_uom_lookup_code IN VARCHAR2,
174 X_amount_billed IN NUMBER,
175 X_matching_basis IN VARCHAR2) IS
176 X_progress VARCHAR2(3) := '000';
177
178 X_rcv_uom VARCHAR2(25) := '';
179 X_item_id NUMBER := 0;
180 X_rcv_quantity_billed NUMBER := 0;
181 X_rcv_amount_billed NUMBER := 0;
182 X_to_rcv_rate NUMBER := 1;
183
184
185 BEGIN
186
187 SELECT
188 rt.unit_of_measure,
189 NVL(rt.quantity_billed, 0),
190 NVL(rt.amount_billed, 0),
191 rs.item_id
192 INTO
193 X_rcv_uom,
194 X_rcv_quantity_billed,
195 X_rcv_amount_billed,
196 X_item_id
197 FROM
198 rcv_transactions rt,
199 rcv_shipment_lines rs
200 WHERE
201 rt.transaction_id = X_rcv_transaction_id AND
202 rs.shipment_line_id = rt.shipment_line_id;
203
204 X_progress := '001';
205
206 /* Get UOM conversion rates */
207
208 IF (x_matching_basis = 'QUANTITY') THEN
209 X_to_rcv_rate := po_uom_s.po_uom_convert(X_uom_lookup_code, X_rcv_uom, X_item_id);
210
211 X_progress := '002';
212
213 /* Calculate the quantity with UOM info */
214 X_rcv_quantity_billed := X_rcv_quantity_billed + (nvl(X_quantity_billed,0) * X_to_rcv_rate);
215 END IF;
216
217 /* Update the amount with new info */
218 X_rcv_amount_billed := X_rcv_amount_billed + X_amount_billed;
219
220 X_progress := '003';
221
222 /* Update RCV_TRANSACTIONS */
223 --Bug#2602981.Changed the X_rcv_quantity_billed to round(X_rcv_quantity_billed,15)
224 --Bug 12886371. Added the updation of who columns
225 UPDATE
226 rcv_transactions
227 SET
228 quantity_billed = round(X_rcv_quantity_billed,15),
229 amount_billed = X_rcv_amount_billed,
230 last_updated_by = fnd_global.user_id,
231 last_update_date=sysdate,
232 last_update_login=fnd_global.login_id
233 WHERE
234 transaction_id = X_rcv_transaction_id;
235
236 X_progress := '004';
237
238 EXCEPTION
239
240 when others then
241 po_message_s.sql_error('ap_update_rcv_transactions', X_progress, sqlcode);
242 raise;
243
244 END ap_update_rcv_transactions;
245
246
247
248 END RCV_BILL_UPDATING_SV;