[Home] [Help]
PACKAGE BODY: APPS.POS_COMPLEX_WORK_PVT
Source
1 PACKAGE BODY POS_COMPLEX_WORK_PVT AS
2 /* $Header: POSVCWOB.pls 120.10 2006/09/12 13:48:07 jbalakri noship $ */
3
4
5 Procedure Get_Po_Amounts (
6 p_api_version IN NUMBER,
7 p_Init_Msg_List IN VARCHAR2,
8 p_po_header_id IN NUMBER,
9 x_amt_approved OUT NOCOPY NUMBER,
10 X_amt_billed OUT NOCOPY NUMBER,
11 X_amt_financed OUT NOCOPY NUMBER,
12 X_adv_billed OUT NOCOPY NUMBER,
13 X_progress_pmt OUT NOCOPY NUMBER,
14 X_amt_recouped OUT NOCOPY NUMBER,
15 X_amt_retained OUT NOCOPY NUMBER,
16 X_amt_delivered OUT NOCOPY NUMBER )
17 IS
18
19 l_api_name CONSTANT VARCHAR2(30) := 'GET_PO_AMOUNTS';
20 l_api_version CONSTANT NUMBER := 1.0;
21
22 l_amt_approved NUMBER := 0;
23 l_amt_billed NUMBER := 0;
24 l_amt_financed NUMBER := 0;
25 l_adv_billed NUMBER := 0;
26 l_progress_pmt NUMBER := 0;
27 l_amt_recouped NUMBER := 0;
28 l_amt_retained NUMBER := 0;
29 l_amt_delivered NUMBER := 0;
30
31 CURSOR l_amt_approved_csr IS
32 select SUM(DECODE(PLL.matching_basis,
33 'AMOUNT', NVL(PLL.amount_received, 0),
34 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)))
35 from PO_LINE_LOCATIONS_ALL PLL
36 where PLL.po_header_id = p_po_header_id
37 and PLL.payment_type in ('MILESTONE', 'RATE', 'LUMPSUM');
38
39
40 CURSOR l_amt_billed_csr IS
41 select SUM(DECODE(PLL.matching_basis,
42 'AMOUNT', NVL(PLL.amount_billed, 0),
43 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)))
44 from PO_LINE_LOCATIONS_ALL PLL
45 where PLL.po_header_id = p_po_header_id
46 and PLL.payment_type <> 'ADVANCE'
47 and PLL.shipment_type = 'STANDARD';
48
49 CURSOR l_adv_billed_csr IS
50 select sum(NVL(PLL.amount_financed, 0))
51 from PO_LINE_LOCATIONS_ALL PLL
52 where PLL.po_header_id = p_po_header_id
53 and PLL.payment_type = 'ADVANCE'
54 and PLL.shipment_type = 'PREPAYMENT';
55
56 CURSOR l_amt_financed_csr IS
57 select SUM(DECODE(PLL.matching_basis,
58 'AMOUNT', NVL(PLL.amount_financed,0),
59 'QUANTITY', NVL(PLL.quantity_financed, 0)*NVL(PLL.price_override, 0)))
60 from PO_LINE_LOCATIONS_ALL PLL
61 where PLL.po_header_id = p_po_header_id
62 and PLL.payment_type in ('MILESTONE', 'RATE', 'LUMPSUM')
63 and PLL.shipment_type = 'PREPAYMENT';
64
65 CURSOR l_progress_pmt_csr IS
66 select SUM(DECODE(PLL.shipment_type,
67 'STANDARD', DECODE(PLL.matching_basis,
68 'AMOUNT', NVL(PLL.amount_billed, 0),
69 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)),
70 'PREPAYMENT', DECODE(PLL.matching_basis,
71 'AMOUNT', NVL(PLL.amount_financed, 0),
72 'QUANTITY', NVL(PLL.quantity_financed, 0)*NVL(PLL.price_override, 0))))
73 from PO_LINE_LOCATIONS_ALL PLL
74 where PLL.po_header_id = p_po_header_id
75 and PLL.payment_type in ('MILESTONE', 'RATE', 'LUMPSUM')
76 and PLL.shipment_type in('STANDARD', 'PREPAYMENT'); --???
77
78 CURSOR l_amt_recouped_csr IS
79 select SUM(DECODE(PLL.matching_basis,
80 'AMOUNT', NVL(PLL.amount_recouped, 0),
81 'QUANTITY', NVL(PLL.quantity_recouped, 0)*NVL(PLL.price_override, 0)))
82 from PO_LINE_LOCATIONS_ALL PLL
83 where PLL.po_header_id = p_po_header_id
84 and PLL.shipment_type = 'PREPAYMENT'; -- could be advance or financing pp
85
86 CURSOR l_amt_retained_csr IS
87 select sum(NVL(PLL.retainage_withheld_amount,0) - NVL(retainage_released_amount,0))
88 from PO_LINE_LOCATIONS_ALL PLL
89 where PLL.po_header_id = p_po_header_id
90 and PLL.payment_type in ('MILESTONE', 'RATE', 'LUMPSUM')
91 and PLL.shipment_type = 'STANDARD';
92
93
94 CURSOR l_amt_delivered_csr IS
95 select sum(NVL(RSL.amount, NVL(RSL.quantity_shipped,0)*NVL(PLL.price_override,0)))
96 from RCV_SHIPMENT_LINES RSL,
97 PO_LINE_LOCATIONS_ALL PLL
98 where PLL.po_header_id = p_po_header_id
99 and PLL.shipment_type = 'STANDARD'
100 and PLL.payment_type = 'DELIVERY'
101 and RSL.PO_line_location_id = PLL.line_location_id;
102 --and RSL.approval_status in ('APPROVED', 'PROCESSED');
103
104 BEGIN
105 -- Amount Approved
106 OPEN l_amt_approved_csr;
107 LOOP
108 FETCH l_amt_approved_csr INTO l_amt_approved;
109 EXIT WHEN l_amt_approved_csr%NOTFOUND;
110 END LOOP;
111 CLOSE l_amt_approved_csr;
112
113 X_amt_approved := NVL(l_amt_approved, 0);
114
115 -- Amount Billed
116 OPEN l_amt_billed_csr;
117 LOOP
118 FETCH l_amt_billed_csr INTO l_amt_billed;
119 EXIT WHEN l_amt_billed_csr%NOTFOUND;
120 END LOOP;
121 CLOSE l_amt_billed_csr;
122
123 x_amt_billed := nvl(l_amt_billed, 0);
124
125 -- Advance Billed, matching_basis can only be amount
126 OPEN l_adv_billed_csr;
127 LOOP
128 FETCH l_adv_billed_csr INTO l_adv_billed;
129 EXIT WHEN l_adv_billed_csr%NOTFOUND;
130 END LOOP;
131 CLOSE l_adv_billed_csr;
132
133 x_adv_billed := nvl(l_adv_billed, 0);
134
135 -- Amount Financed: pay item amount financed + advance amount
136 OPEN l_amt_financed_csr;
137 LOOP
138 FETCH l_amt_financed_csr INTO l_amt_financed;
139 EXIT WHEN l_amt_financed_csr%NOTFOUND;
140 END LOOP;
141 CLOSE l_amt_financed_csr;
142
143 X_amt_financed := nvl(l_amt_financed, 0) + x_adv_billed;
144
145 -- Progress Payment : pay item amount billed or financed
146 -- Actual pay item PO, Get total pay item amount billed
147 OPEN l_progress_pmt_csr;
148 LOOP
149 FETCH l_progress_pmt_csr INTO l_progress_pmt;
150 EXIT WHEN l_progress_pmt_csr%NOTFOUND;
151 END LOOP;
152 CLOSE l_progress_pmt_csr;
153
154 x_progress_pmt := nvl(l_progress_pmt, 0);
155
156 -- Amount Recouped
157 -- From Advances or financing pay items. For Advances, matching basis is AMOUNT
158 OPEN l_amt_recouped_csr;
159 LOOP
160 FETCH l_amt_recouped_csr INTO l_amt_recouped;
161 EXIT WHEN l_amt_recouped_csr%NOTFOUND;
162 END LOOP;
163 CLOSE l_amt_recouped_csr;
164
165 x_amt_recouped := nvl(l_amt_recouped, 0);
166
167 -- Amount Retained, should exclude delivery shipment???
168 OPEN l_amt_retained_csr;
169 LOOP
170 FETCH l_amt_retained_csr INTO l_amt_retained;
171 EXIT WHEN l_amt_retained_csr%NOTFOUND;
172 END LOOP;
173 CLOSE l_amt_retained_csr;
174
175 x_amt_retained := nvl(l_amt_retained, 0);
176
177 -- Amount Delivered: approved amount for actual delivery shipment for financing PO
178 OPEN l_amt_delivered_csr;
179 LOOP
180 FETCH l_amt_delivered_csr INTO l_amt_delivered;
181 EXIT WHEN l_amt_delivered_csr%NOTFOUND;
182 END LOOP;
183 CLOSE l_amt_delivered_csr;
184
185 x_amt_delivered := nvl(l_amt_delivered, 0);
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 x_amt_approved := 0;
190 X_amt_billed := 0;
191 X_amt_financed := 0;
192 X_adv_billed := 0;
193 X_progress_pmt := 0;
194 X_amt_recouped := 0;
195 X_amt_retained := 0;
196 X_amt_delivered := 0;
197
198 RAISE;
199
200 END Get_Po_Amounts;
201
202
203 Procedure Get_Po_Line_Amounts (
204 p_api_version IN NUMBER,
205 p_Init_Msg_List IN VARCHAR2,
206 p_po_line_id IN NUMBER,
207 X_amt_delivered OUT NOCOPY NUMBER,
208 X_amt_billed OUT NOCOPY NUMBER,
209 X_advance_amt OUT NOCOPY NUMBER,
210 X_adv_billed OUT NOCOPY NUMBER,
211 X_amt_recouped OUT NOCOPY NUMBER )
212 IS
213
214 l_api_name CONSTANT VARCHAR2(30) := 'GET_PO_LINE_AMOUNTS';
215 l_api_version CONSTANT NUMBER := 1.0;
216
217 l_amt_delivered NUMBER := 0;
218 l_amt_billed NUMBER := 0;
219 l_advance_amt NUMBER := 0;
220 l_adv_billed NUMBER := 0;
221 l_amt_recouped NUMBER := 0;
222
223 CURSOR l_amt_delivered_csr IS
224 select sum(NVL(
225 NVL(RSL.amount,RSL.REQUESTED_AMOUNT), --5488052
226 NVL(RSL.quantity_shipped,0)*NVL(PLL.price_override,0)
227 )
228 )
229 from RCV_SHIPMENT_LINES RSL,
230 PO_LINE_LOCATIONS_ALL PLL
231 where PLL.po_line_id = p_po_line_id
232 and PLL.shipment_type = 'STANDARD'
233 --5488052 and PLL.payment_type = 'DELIVERY'
234 and RSL.PO_line_location_id = PLL.line_location_id
235 and RSL.approval_status in ('APPROVED', 'PROCESSED');
236
237
238 CURSOR l_amt_billed_csr IS
239 select SUM(DECODE(PLL.matching_basis,
240 'AMOUNT', NVL(PLL.amount_billed, 0),
241 'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)))
242 from PO_LINE_LOCATIONS_ALL PLL
243 where PLL.po_line_id = p_po_line_id
244 --5488052 and PLL.payment_type = 'DELIVERY'
245 and PLL.shipment_type = 'STANDARD';
246
247
248 CURSOR l_advance_amt_csr IS
249 select PLL.amount
250 from PO_LINE_LOCATIONS_ALL PLL
251 where PLL.po_line_id = p_po_line_id
252 and PLL.payment_type = 'ADVANCE'
253 and PLL.shipment_type = 'PREPAYMENT';
254
255
256 CURSOR l_adv_billed_csr IS
257 select PLL.amount_financed
258 from PO_LINE_LOCATIONS_ALL PLL
259 where PLL.po_line_id = p_po_line_id
260 and PLL.payment_type = 'ADVANCE'
261 and PLL.shipment_type = 'PREPAYMENT';
262
263
264 CURSOR l_amt_recouped_csr IS
265 select SUM(DECODE(PLL.matching_basis,
266 'AMOUNT', NVL(PLL.amount_recouped, 0),
267 'QUANTITY', NVL(PLL.quantity_recouped, 0)*NVL(PLL.price_override, 0)))
268 from PO_LINE_LOCATIONS_ALL PLL
269 where PLL.po_line_id = p_po_line_id
270 and PLL.shipment_type = 'PREPAYMENT'; -- could be advance or financ pp
271
272
273 BEGIN
274
275 -- Amount Delivered
276 OPEN l_amt_delivered_csr;
277 LOOP
278 FETCH l_amt_delivered_csr INTO l_amt_delivered;
279 EXIT WHEN l_amt_delivered_csr%NOTFOUND;
280 END LOOP;
281 CLOSE l_amt_delivered_csr;
282
283 x_amt_delivered := nvl(l_amt_delivered, 0);
284
285 -- Amount Billed
286 OPEN l_amt_billed_csr;
287 LOOP
288 FETCH l_amt_billed_csr INTO l_amt_billed;
289 EXIT WHEN l_amt_billed_csr%NOTFOUND;
290 END LOOP;
291 CLOSE l_amt_billed_csr;
292
293 x_amt_billed := nvl(l_amt_billed, 0);
294
295
296 -- Advance Amount, should have only one
297 OPEN l_advance_amt_csr;
298 LOOP
299 FETCH l_advance_amt_csr INTO l_advance_amt;
300 EXIT WHEN l_advance_amt_csr%NOTFOUND;
301 END LOOP;
302 CLOSE l_advance_amt_csr;
303
304 x_advance_amt := nvl(l_advance_amt, 0);
305
306
307 -- Advance Billed, matching basis is Amount, only one
308 OPEN l_adv_billed_csr;
309 LOOP
310 FETCH l_adv_billed_csr INTO l_adv_billed;
311 EXIT WHEN l_adv_billed_csr%NOTFOUND;
312 END LOOP;
313 CLOSE l_adv_billed_csr;
314
315 x_adv_billed := nvl(l_adv_billed, 0);
316
317
318 -- Amount Recouped, from Advances or financing pay items.
319 OPEN l_amt_recouped_csr;
320 LOOP
321 FETCH l_amt_recouped_csr INTO l_amt_recouped;
322 EXIT WHEN l_amt_recouped_csr%NOTFOUND;
323 END LOOP;
324 CLOSE l_amt_recouped_csr;
325
326 x_amt_recouped := nvl(l_amt_recouped, 0);
327
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 X_amt_delivered := 0;
332 X_amt_billed := 0;
333 X_advance_amt := 0;
334 X_adv_billed := 0;
335 X_amt_recouped := 0;
336
337 -- RAISE;
338
339 END Get_Po_line_Amounts;
340
341
342
343
344 Procedure Get_po_ship_amounts (
345 p_api_version IN NUMBER,
346 p_Init_Msg_List IN VARCHAR2,
347 p_po_line_location_id IN NUMBER,
348 X_value_percent OUT NOCOPY NUMBER,
349 X_amt_approved OUT NOCOPY NUMBER )
350 IS
351
352 l_value_percent NUMBER := 0;
353 l_amt_approved NUMBER := 0;
354
355 CURSOR l_value_percent_csr IS
356 select ROUND(DECODE(PLL.matching_basis,
357 'AMOUNT', (NVL(PLL.amount, 0)/POL.amount)*100,
358 'QUANTITY', (NVL(PLL.price_override, 0)/POL.unit_price)*100))
359 from PO_LINE_LOCATIONS_ARCHIVE_ALL PLL,
360 PO_LINES_ARCHIVE_ALL POL
361 where PLL.po_line_id = POL.po_line_id
362 and PLL.line_location_id = p_po_line_location_id
363 and PLL.payment_type = 'MILESTONE'
364 and PLL.latest_external_flag ='Y'
365 and POL.latest_external_flag ='Y';
366
367 CURSOR l_amt_approved_csr IS
368 select DECODE(PLL.matching_basis,
369 'AMOUNT', NVL(PLL.amount_received, 0),
370 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0))
371 from PO_LINE_LOCATIONS_ALL PLL
372 where PLL.line_location_id = p_po_line_location_id;
373
374
375 BEGIN
376
377 -- Value Percent, only valid for milestone pay items.
378 OPEN l_value_percent_csr;
379 LOOP
380 FETCH l_value_percent_csr INTO l_value_percent;
381 EXIT WHEN l_value_percent_csr%NOTFOUND;
382 END LOOP;
383 CLOSE l_value_percent_csr;
384
385 x_value_percent := nvl(l_value_percent, 0);
386
387
388 -- Amount Approved
389 OPEN l_amt_approved_csr;
390 LOOP
391 FETCH l_amt_approved_csr INTO l_amt_approved;
392 EXIT WHEN l_amt_approved_csr%NOTFOUND;
393 END LOOP;
394 CLOSE l_amt_approved_csr;
395
396 X_amt_approved := NVL(l_amt_approved, 0);
397
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 X_value_percent := 0;
402 X_amt_approved := 0;
403
404 -- RAISE;
405
406 END Get_Po_Ship_Amounts;
407
408
409 END POS_COMPLEX_WORK_PVT;