[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_SPEND_PKG
Source
1 PACKAGE BODY POA_EDW_SPEND_PKG AS
2 /* $Header: POASPNDB.pls 120.0 2005/06/01 23:32:09 appldev noship $ */
3
4
5 FUNCTION CONTRACT_AMT_RELEASED(p_contract_id IN NUMBER,
6 p_org_id IN NUMBER,
7 p_contract_type in VARCHAR2) RETURN NUMBER
8 IS
9 v_amt_released NUMBER := 0;
10 v_stdpo_amt NUMBER := 0;
11 BEGIN
12
13 IF (p_contract_type = 'BLANKET') THEN
14 IF (poa_ga_util_pkg.is_global_agreement(p_contract_id) = 'Y') THEN
15 SELECT SUM(decode( pol.matching_basis
16 , 'AMOUNT'
17 , (decode( pll.closed_code
18 , 'FINALLY_CLOSED'
19 , ( decode( sign( nvl(pod.amount_delivered,0)
20 - nvl(pod.amount_billed,0))
21 , 1
22 , nvl(pod.amount_delivered,0)
23 , nvl(pod.amount_billed,0)))
24 , ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
25 , (decode( pll.closed_code
26 , 'FINALLY_CLOSED'
27 , ( decode( sign( nvl(pod.quantity_delivered,0)
28 - nvl(pod.quantity_billed,0))
29 , 1
30 , nvl(pod.quantity_delivered,0)
31 , nvl(pod.quantity_billed,0)))
32 * nvl(pll.price_override,0)
33 , ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
34 * nvl(pll.price_override,0) ))
35 )
36 )
37 INTO v_stdpo_amt
38 FROM po_headers_all poh,
39 po_lines_all pol,
40 po_line_locations_all pll,
41 po_distributions_all pod
42 WHERE pod.po_header_id = poh.po_header_id
43 and pod.line_location_id = pll.line_location_id
44 and pol.po_header_id = poh.po_header_id
45 and pll.po_line_id = pol.po_line_id
46 and pll.from_header_id = p_contract_id
47 and nvl(pll.approved_flag, 'N') = 'Y'
48 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
49 END IF;
50 SELECT SUM(decode( pol.matching_basis
51 , 'AMOUNT'
52 , (decode( pll.closed_code
53 , 'FINALLY_CLOSED'
54 , ( decode( sign( nvl(pod.amount_delivered,0)
55 - nvl(pod.amount_billed,0))
56 , 1
57 , nvl(pod.amount_delivered,0)
58 , nvl(pod.amount_billed,0)))
59 , ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
60 , (decode( pll.closed_code
61 , 'FINALLY_CLOSED'
62 , ( decode( sign( nvl(pod.quantity_delivered,0)
63 - nvl(pod.quantity_billed,0))
64 , 1
65 , nvl(pod.quantity_delivered,0)
66 , nvl(pod.quantity_billed,0)))
67 * nvl(pll.price_override,0)
68 , ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
69 * nvl(pll.price_override,0) ))
70 )
71 )
72 INTO v_amt_released
73 FROM po_releases_all por,
74 po_headers_all poh,
75 po_line_locations_all pll,
76 po_distributions_all pod,
77 po_lines_all pol
78 WHERE pod.po_release_id = por.po_release_id
79 and pod.po_header_id = poh.po_header_id
80 and pod.po_line_id = pol.po_line_id
81 and pod.line_location_id = pll.line_location_id
82 and poh.po_header_id = p_contract_id
83 and pod.org_id = p_org_id
84 and nvl(pll.approved_flag,
85 'N') = 'Y'
86 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
87
88 v_amt_released := nvl(v_amt_released, 0) + nvl(v_stdpo_amt, 0);
89
90 ELSE
91 SELECT SUM(decode( plc.matching_basis
92 , 'AMOUNT'
93 , (decode( pll.closed_code
94 , 'FINALLY_CLOSED'
95 , (decode( sign( nvl(pod.amount_delivered,0)
96 - nvl(pod.amount_billed,0))
97 , 1
98 , nvl(pod.amount_delivered,0)
99 , nvl(pod.amount_billed,0)))
100 , ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
101 , (decode( pll.closed_code
102 , 'FINALLY_CLOSED'
103 , (decode( sign( nvl(pod.quantity_delivered,0)
104 - nvl(pod.quantity_billed,0))
105 , 1
106 , nvl(pod.quantity_delivered,0)
107 , nvl(pod.quantity_billed,0)))
108 * nvl(pll.price_override,0)
109 , ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
110 * nvl(pll.price_override,0) ))
111 )
112 )
113 INTO v_amt_released
114 FROM po_headers_all poh,
115 po_headers_all poh2,
116 po_lines_all plc,
117 po_line_locations_all pll,
118 po_distributions_all pod
119 WHERE pod.po_header_id = poh.po_header_id
120 and pod.po_line_id = plc.po_line_id
121 and pod.line_location_id = pll.line_location_id
122 and plc.contract_id = poh2.po_header_id
123 and poh2.po_header_id = p_contract_id
124 and pod.org_id = p_org_id
125 and nvl(pll.approved_flag,
126 'N') = 'Y'
127 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
128 END IF;
129
130 RETURN nvl(v_amt_released,0);
131
132 END CONTRACT_AMT_RELEASED;
133
134 FUNCTION LINE_AMT_RELEASED(p_contract_id IN NUMBER,
135 p_org_id IN NUMBER,
136 p_line_id IN NUMBER) RETURN NUMBER
137 IS
138 v_amt_released NUMBER := 0;
139 v_stdpo_amt NUMBER := 0;
140 BEGIN
141
142 IF (poa_ga_util_pkg.is_global_agreement(p_contract_id) = 'Y') THEN
143 SELECT SUM(decode( pol.matching_basis
144 , 'AMOUNT'
145 , (decode( pll.closed_code
146 , 'FINALLY_CLOSED'
147 , (decode( sign( nvl(pod.amount_delivered,0)
148 - nvl(pod.amount_billed,0))
149 , 1
150 , nvl(pod.amount_delivered,0)
151 , nvl(pod.amount_billed,0)))
152 , ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
153 , (decode( pll.closed_code
154 , 'FINALLY_CLOSED'
155 , (decode( sign( nvl(pod.quantity_delivered,0)
156 - nvl(pod.quantity_billed,0))
157 , 1
158 , nvl(pod.quantity_delivered,0)
159 , nvl(pod.quantity_billed,0)))
160 * nvl(pll.price_override,0)
161 , ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
162 * nvl(pll.price_override,0) ))
163 )
164 )
165 INTO v_stdpo_amt
166 FROM po_headers_all poh,
167 po_lines_all pol,
168 po_line_locations_all pll,
169 po_distributions_all pod
170 WHERE pod.po_header_id = poh.po_header_id
171 and pod.line_location_id = pll.line_location_id
172 and pol.po_header_id = poh.po_header_id
173 and pll.po_line_id = pol.po_line_id
174 and pol.from_header_id = p_contract_id
175 and pol.from_line_id = p_line_id
176 and nvl(pll.approved_flag, 'N') = 'Y'
177 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
178 END IF;
179
180 SELECT SUM(decode( pol.matching_basis
181 , 'AMOUNT'
182 , (decode( pll.closed_code
183 , 'FINALLY_CLOSED'
184 , (decode( sign( nvl(pod.amount_delivered,0)
185 - nvl(pod.amount_billed,0))
186 , 1
187 , nvl(pod.amount_delivered,0),nvl(pod.amount_billed,0)))
188 , ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
189 , (decode( pll.closed_code
190 , 'FINALLY_CLOSED'
191 , (decode( sign( nvl(pod.quantity_delivered,0)
192 - nvl(pod.quantity_billed,0))
193 , 1
194 , nvl(pod.quantity_delivered,0),nvl(pod.quantity_billed,0)))
195 * nvl(pll.price_override,0)
196 , ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
197 * nvl(pll.price_override,0) ))
198 )
199 )
200 INTO v_amt_released
201 FROM po_distributions_all pod,
202 po_line_locations_all pll,
203 po_headers_all poh,
204 po_releases_all por,
205 po_lines_all pol
206 WHERE pod.po_release_id = por.po_release_id
207 and pod.po_header_id = poh.po_header_id
208 and pod.po_line_id = pol.po_line_id
209 and pod.line_location_id = pll.line_location_id
210 and poh.po_header_id = p_contract_id
211 and pod.org_id = p_org_id
212 and pod.po_line_id = p_line_id
213 and nvl(pll.approved_flag,
214 'N') = 'Y'
215 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
216
217 RETURN nvl(v_stdpo_amt, 0) + nvl(v_amt_released,0);
218
219 END LINE_AMT_RELEASED;
220
221
222 FUNCTION LINE_QTY_RELEASED(p_contract_id IN NUMBER,
223 p_org_id IN NUMBER,
224 p_line_id IN NUMBER) RETURN NUMBER
225 IS
226 v_qty_released NUMBER := 0;
227 v_stdpo_qty NUMBER := 0;
228 BEGIN
229
230 IF (poa_ga_util_pkg.is_global_agreement(p_contract_id) = 'Y') THEN
231 SELECT SUM(decode(pll.closed_code, 'FINALLY_CLOSED',
232 (decode(sign(nvl(pod.quantity_delivered,0)
233 - nvl(pod.quantity_billed,0)),
234 1, nvl(pod.quantity_delivered,0),nvl(pod.quantity_billed,0)))
235 ,
236 (nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
237 ))
238 INTO v_stdpo_qty
239 FROM po_headers_all poh,
240 po_lines_all pol,
241 po_line_locations_all pll,
242 po_distributions_all pod
243 WHERE pod.po_header_id = poh.po_header_id
244 and pod.line_location_id = pll.line_location_id
245 and pol.po_header_id = poh.po_header_id
246 and pll.po_line_id = pol.po_line_id
247 and pll.from_header_id = p_contract_id
248 and pll.from_line_id = p_line_id
249 and nvl(pll.approved_flag, 'N') = 'Y'
250 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
251 END IF;
252
253 SELECT SUM(decode(pll.closed_code, 'FINALLY_CLOSED',
254 (decode(sign(nvl(pod.quantity_delivered,0)
255 - nvl(pod.quantity_billed,0)),
256 1, nvl(pod.quantity_delivered,0), nvl(pod.quantity_billed,0))),
257 (nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))))
258 INTO v_qty_released
259 FROM po_distributions_all pod,
260 po_line_locations_all pll,
261 po_headers_all poh,
262 po_releases_all por
263 WHERE pod.po_release_id = por.po_release_id
264 and pod.po_header_id = poh.po_header_id
265 and pod.line_location_id = pll.line_location_id
266 and poh.po_header_id = p_contract_id
267 and pod.org_id = p_org_id
268 and pod.po_line_id = p_line_id
269 and nvl(pll.approved_flag,
270 'N') = 'Y'
271 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
272
273 RETURN nvl(v_stdpo_qty, 0) + nvl(v_qty_released,0);
274
275 END LINE_QTY_RELEASED;
276
277
278 FUNCTION APPROVED_BY(p_po_header_id IN NUMBER)
279 RETURN NUMBER
280 IS
281 v_employee_id NUMBER := 0;
282 v_sequence_num NUMBER := 0;
283
284 BEGIN
285
286 SELECT max(pah.sequence_num)
287 INTO v_sequence_num
288 FROM po_action_history pah
289 WHERE object_id = p_po_header_id
290 and object_type_code in ('PO', 'PA')
291 and action_code = 'APPROVE';
292
293 SELECT pah.employee_id
294 INTO v_employee_id
295 FROM po_action_history pah
296 WHERE pah.sequence_num = v_sequence_num
297 and pah.object_id = p_po_header_id
298 and pah.object_type_code in ('PO', 'PA')
299 and pah.action_code = 'APPROVE'
300 and rownum < 2;
301
302
303 RETURN v_employee_id;
304
305 END APPROVED_BY;
306
307
308 FUNCTION GET_ACCEPTANCE_DATE(p_doc_id IN NUMBER,
309 p_type IN VARCHAR2)
310 RETURN DATE
311 IS
312 v_accept_date DATE := to_date(NULL);
313 BEGIN
314
315 IF (p_type = 'P') THEN
316 SELECT max(pac.action_date)
317 INTO v_accept_date
318 FROM po_acceptances pac
319 WHERE pac.po_header_id = p_doc_id
320 and pac.accepted_flag = 'Y';
321 ELSE
322 SELECT max(pac.action_date)
323 INTO v_accept_date
324 FROM po_acceptances pac
325 WHERE pac.po_release_id = p_doc_id
326 and pac.accepted_flag = 'Y';
327
328 END IF;
329
330 RETURN v_accept_date;
331
332 EXCEPTION when others then
333 v_accept_date := to_date(NULL);
334 RETURN v_accept_date;
335
336
337 END GET_ACCEPTANCE_DATE;
338
339 FUNCTION GET_REQ_APPROVAL_DATE(p_req_dist_id IN NUMBER)
340 RETURN DATE
341 IS
342 v_approval_date DATE := to_date(NULL);
343 v_req_header_id NUMBER := 0;
344
345 BEGIN
346
347 SELECT prh.requisition_header_id
348 INTO v_req_header_id
349 FROM po_requisition_headers_all prh,
350 po_requisition_lines_all prl,
351 po_req_distributions_all prd
352 WHERE prd.distribution_id = p_req_dist_id
353 and prl.requisition_line_id = prd.requisition_line_id
354 and prh.requisition_header_id = prl.requisition_header_id;
355
356
357 SELECT max(pah.action_date)
358 INTO v_approval_date
359 FROM po_action_history pah
360 WHERE pah.object_id = v_req_header_id
361 and pah.object_type_code = 'REQUISITION'
362 and pah.object_sub_type_code = 'PURCHASE'
363 and pah.action_code = 'APPROVE';
364
365
366 RETURN v_approval_date;
367
368 EXCEPTION when others then
369 v_approval_date := to_date(NULL);
370 RETURN v_approval_date;
371
372
373 END GET_REQ_APPROVAL_DATE;
374
375 FUNCTION GET_SUPPLIER_APPROVED(p_po_dist_id IN NUMBER)
376 RETURN VARCHAR2
377 IS
378 v_supp_approved VARCHAR2(3) := NULL;
379 BEGIN
380
381
382 SELECT decode(max('Y'), 'Y', 'Y', 'N')
383 INTO v_supp_approved
384 FROM po_distributions_all pod,
385 po_line_locations_all pll,
386 po_lines_all pol,
387 po_headers_all poh,
388 po_asl_status_rules pasr,
389 po_asl_statuses pas,
390 po_approved_supplier_list pasl
391 WHERE pod.po_header_id = poh.po_header_id
392 and pod.po_line_id = pol.po_line_id
393 and pod.line_location_id = pll.line_location_id
394 and poh.vendor_id = pasl.vendor_id
395 and (poh.vendor_site_id = pasl.vendor_site_id
396 OR
397 pasl.vendor_site_id is null)
398 and ((pll.ship_to_organization_id = pasl.using_organization_id)
399 OR
400 (pasl.using_organization_id = -1
401 and not exists
402 (SELECT 'local exists with global record'
403 FROM po_line_locations_all pll2,
404 po_lines_all pol2,
405 po_headers_all poh2,
406 po_approved_supplier_list pasl2
407 WHERE pll2.ship_to_organization_id =
408 pasl.using_organization_id
409 and pll2.po_header_id = poh2.po_header_id
410 and pol2.po_header_id = poh2.po_header_id
411 and poh2.vendor_id = pasl2.vendor_id
412 and ((pol2.item_id is not null
413 and pol2.item_id = pasl2.item_id)
414 OR
415 (pol2.item_id is null
416 and pol2.category_id = pasl2.category_id)))))
417 and ((pol.item_id is not null
418 and pol.item_id = pasl.item_id)
419 OR
420 (pol.item_id is null
421 and pol.category_id = pasl.category_id))
422 and pasl.asl_status_id = pas.status_id
423 and pasr.status_id = pas.status_id
424 and pasr.business_rule = '1_PO_APPROVAL'
425 and pasr.allow_action_flag = 'Y'
426 and pod.po_distribution_id = p_po_dist_id
427 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
428
429 RETURN v_supp_approved;
430
431 EXCEPTION when others THEN
432 v_supp_approved := 'N';
433 RETURN v_supp_approved;
434
435 END GET_SUPPLIER_APPROVED;
436
437 FUNCTION GET_SUPPLIER_APPROVED(p_po_dist_id IN NUMBER,
438 p_vendor_id IN NUMBER,
439 p_vendor_site_id IN NUMBER,
440 p_ship_to_org_id IN NUMBER,
441 p_item_id IN NUMBER,
442 p_category_id IN NUMBER)
443 RETURN VARCHAR2
444 IS
445 v_supp_approved VARCHAR2(3) := NULL;
446 BEGIN
447
448 select decode(max('Y'), 'Y', 'Y', 'N')
449 into v_supp_approved
450 from po_asl_status_rules pasr,
451 po_asl_statuses pas,
452 po_approved_supplier_list pasl
453 where pasl.vendor_id = p_vendor_id
454 and (pasl.vendor_site_id is null or
455 pasl.vendor_site_id = p_vendor_site_id)
456 and pasl.using_organization_id in (-1,p_ship_to_org_id)
457 and ((p_item_id is not null and pasl.item_id = p_item_id) or
458 (p_item_id is null and pasl.category_id = p_category_id))
459 and pasl.asl_status_id = pas.status_id
460 and pasr.status_id = pas.status_id
461 and pasr.business_rule = '1_PO_APPROVAL'
462 having count(pasr.allow_action_flag)
463 = count(decode(pasr.allow_action_flag,'Y','Y',null));
464
465 return v_supp_approved;
466
467 EXCEPTION when others THEN
468 v_supp_approved := 'N';
469 RETURN v_supp_approved;
470
471 END GET_SUPPLIER_APPROVED;
472
473 -- get_check_cut_date
474 -- Gets min check_cut date from ap_invoices for a given distribution ID
475
476 FUNCTION get_check_cut_date(p_po_dist_id NUMBER)
477 RETURN DATE
478 IS
479 cc_date DATE := NULL;
480
481 BEGIN
482
483 select min(ack.check_date)
484 into cc_date
485 from ap_checks_all ack,
486 ap_invoice_payments_all aip,
487 ap_invoice_distributions_all aid
488 where aip.check_id = ack.check_id
489 and aip.invoice_id = aid.invoice_id
490 and aid.po_distribution_id = p_po_dist_id;
491
492 RETURN(cc_date);
493
494 EXCEPTION
495 WHEN NO_DATA_FOUND THEN
496 RETURN (cc_date);
497 WHEN OTHERS THEN
498 RAISE;
499
500 END get_check_cut_date;
501
502 -- get_invoice_received_date
503 -- Gets min invoice_received_date from ap_invoices for a given distribution_id
504
505 FUNCTION get_invoice_received_date(p_po_dist_id NUMBER)
506 RETURN DATE
507 IS
508
509 inv_rec_date DATE := NULL;
510
511 BEGIN
512
513 select min(ain.invoice_received_date)
514 into inv_rec_date
515 from ap_invoices_all ain,
516 ap_invoice_distributions_all aid
517 where ain.invoice_id = aid.invoice_id
518 and aid.po_distribution_id = p_po_dist_id;
519
520 RETURN(inv_rec_date);
521
522 EXCEPTION
523 WHEN NO_DATA_FOUND THEN
524 RETURN(inv_rec_date);
525 WHEN OTHERS THEN
526 RAISE;
527
528 END get_invoice_received_date;
529
530 -- get_invoice_creation_date
531 -- Gets min invoice_creation_date from ap_invoice_creation_all
532 -- for a given distribution_id
533
534 FUNCTION get_invoice_creation_date(p_po_dist_id NUMBER)
535 RETURN DATE
536 IS
537
538 inv_creation_date DATE := NULL;
539
540 BEGIN
541
542 select min(aid.creation_date)
543 into inv_creation_date
544 from ap_invoice_distributions_all aid
545 where aid.po_distribution_id = p_po_dist_id;
546
547 RETURN(inv_creation_date);
548
549 EXCEPTION
550
551 WHEN NO_DATA_FOUND THEN
552 RETURN(inv_creation_date);
553 WHEN OTHERS THEN
554 RAISE;
555
556 END get_invoice_creation_date;
557
558 -- get_goods_received_date
559 -- gets min good_received_date from rcv_transactions for given line_location_id
560 -- and transaction_type is RECEIVE
561
562 FUNCTION get_goods_received_date(p_po_line_loc_id NUMBER)
563 RETURN DATE
564 IS
565
566 goods_rcvd_date DATE := NULL;
567
568 BEGIN
569
570 select trunc(min(transaction_date))
571 into goods_rcvd_date
572 from rcv_transactions rct
573 where transaction_type = 'RECEIVE'
574 and rct. po_line_location_id = p_po_line_loc_id;
575
576 RETURN(goods_rcvd_date);
577
578 EXCEPTION
579 WHEN NO_DATA_FOUND THEN
580 RETURN(goods_rcvd_date);
581 WHEN OTHERS THEN
582 RAISE;
583
584 END get_goods_received_date;
585
586 -- get_ipv
587 -- get the sum ipv for given distribution ID
588
589 FUNCTION get_ipv(p_po_dist_id NUMBER)
590 RETURN NUMBER
591 IS
592 v_ipv NUMBER := 0;
593
594 BEGIN
595
596 select SUM(base_invoice_price_variance) into v_ipv
597 from ap_invoice_distributions_all
598 where po_distribution_id = p_po_dist_id;
599
600 RETURN(v_ipv);
601
602 EXCEPTION
603 WHEN NO_DATA_FOUND THEN
604 RETURN(0);
605 WHEN OTHERS THEN
606 RAISE;
607
608 END get_ipv;
609
610 END POA_EDW_SPEND_PKG;