[Home] [Help]
PACKAGE BODY: APPS.POA_SAVINGS_CON
Source
1 PACKAGE BODY poa_savings_con AS
2 /* $Header: poasvp2b.pls 120.3 2006/02/13 01:21:59 sdiwakar noship $ */
3
4 /*
5 NAME
6 populate_contract -
7 DESCRIPTION
8 main function for populating poa_savings fact table
9 for Oracle Purchasing with contract purchases information
10 */
11 --
12 PROCEDURE populate_contract (p_start_date IN DATE,
13 p_end_date IN DATE,
14 p_start_time IN DATE,
15 p_batch_no IN NUMBER)
16 IS
17 --
18 x_progress VARCHAR2(3) := NULL;
19 v_buf VARCHAR2(240) := NULL;
20 --
21 BEGIN
22
23 POA_LOG.debug_line('Populate_contract: entered');
24
25 /* Insert all releases created against blankets and planned
26 * agreements
27 */
28
29 x_progress := '010';
30 INSERT INTO poa_bis_savings
31 ( purchase_amount
32 , contract_amount
33 , non_contract_amount
34 , pot_contract_amount
35 , potential_saving
36 , total_purchase_qty
37 , distribution_transaction_id
38 , document_type_code
39 , purchase_creation_date
40 , item_id
41 , item_description
42 , category_id
43 , supplier_site_id
44 , supplier_id
45 , requestor_id
46 , ship_to_location_id
47 , ship_to_organization_id
48 , operating_unit_id
49 , buyer_id
50 , project_id
51 , task_id
52 , currency_code
53 , rate_type
54 , rate_date
55 , cost_center_id
56 , account_id
57 , company_id
58 , rate
59 , approved_date
60 , Currency_Conv_Rate
61 , created_by
62 , creation_date
63 , last_updated_by
64 , last_update_date
65 , last_update_login
66 , request_id
67 , program_application_id
68 , program_id
69 , program_update_date)
70 (SELECT decode(psc.consigned_flag
71 ,'Y'
72 ,null
73 ,decode(psc.matching_basis
74 ,'AMOUNT'
75 ,(decode(psc.closed_code
76 ,'FINALLY_CLOSED'
77 ,(decode(sign(nvl(pod.amount_delivered,0)
78 -nvl(pod.amount_billed,0))
79 ,1
80 ,nvl(pod.amount_delivered,0)
81 ,nvl(pod.amount_billed,0)))
82 *nvl(pod.rate,1)
83 ,(nvl(pod.amount_ordered,0)
84 -nvl(pod.amount_cancelled,0))
85 *nvl(pod.rate,1)))
86 ,(decode(psc.closed_code
87 ,'FINALLY_CLOSED'
88 ,(decode(sign(nvl(pod.quantity_delivered,0)
89 -nvl(pod.quantity_billed,0))
90 ,1
91 ,nvl(pod.quantity_delivered,0)
92 ,nvl(pod.quantity_billed,0)))
93 *nvl(psc.price_override,0)
94 *nvl(pod.rate,1)
95 ,(nvl(pod.quantity_ordered,0)
96 -nvl(pod.quantity_cancelled,0))
97 *nvl(psc.price_override,0)
98 *nvl(pod.rate,1)))
99 )
100 )
101 , decode(psc.consigned_flag
102 ,'Y'
103 ,null
104 ,decode(psc.matching_basis
105 ,'AMOUNT'
106 ,(decode(psc.closed_code
107 ,'FINALLY_CLOSED'
108 ,(decode(sign(nvl(pod.amount_delivered,0)
109 -nvl(pod.amount_billed,0))
110 ,1
111 ,nvl(pod.amount_delivered,0)
112 ,nvl(pod.amount_billed,0)))
113 *nvl(pod.rate,1)
114 ,(nvl(pod.amount_ordered,0)
115 -nvl(pod.amount_cancelled,0))
116 *nvl(pod.rate,1)))
117 ,(decode(psc.closed_code
118 ,'FINALLY_CLOSED'
119 ,(decode(sign(nvl(pod.quantity_delivered,0)
120 -nvl(pod.quantity_billed,0))
121 ,1
122 ,nvl(pod.quantity_delivered,0)
123 ,nvl(pod.quantity_billed,0)))
124 *nvl(psc.price_override,0)
125 *nvl(pod.rate,1)
126 ,(nvl(pod.quantity_ordered,0)
127 -nvl(pod.quantity_cancelled,0))
128 *nvl(psc.price_override,0)
129 *nvl(pod.rate,1)))
130 )
131 )
132 , Decode(psc.consigned_flag, 'Y', NULL, 0)
133 , Decode(psc.consigned_flag, 'Y', NULL, 0)
134 , Decode(psc.consigned_flag, 'Y', NULL, 0)
135 , decode(psc.consigned_flag
136 ,'Y'
137 ,null
138 ,decode(psc.value_basis
139 ,'QUANTITY'
140 ,pod.quantity_ordered
141 ,null
142 )
143 )
144 , pod.po_distribution_id
145 , phc.type_lookup_code
146 , pod.creation_date
147 , plc.item_id
148 , plc.item_description
149 , plc.category_id
150 , phc.vendor_site_id
151 , phc.vendor_id
152 , pod.deliver_to_person_id
153 , psc.ship_to_location_id
154 , psc.ship_to_organization_id
155 , psc.org_id
156 , por.agent_id
157 , pod.project_id
158 , pod.task_id
159 , gl.currency_code
160 , phc.rate_type
161 , nvl(phc.rate_date, pod.creation_date)
162 , pod.code_combination_id
163 , NULL
164 , NULL
165 , pod.rate
166 , NVL(POA_OLTP_GENERIC_PKG.get_approved_date_por(pod.creation_date, por.po_release_id),
167 por.approved_date)
168 , POA_CURRENCY_PKG.get_global_currency_rate (phc.rate_type,
169 decode(phc.rate_type, 'User', gl.currency_code,
170 NVL(phc.currency_code,gl.currency_code)),
171 NVL(pod.rate_date, pod.creation_date), phc.rate)
172 , fnd_global.user_id
173 , p_start_time
174 , fnd_global.user_id
175 , p_start_time
176 , fnd_global.login_id
177 , fnd_global.conc_request_id
178 , fnd_global.prog_appl_id
179 , fnd_global.conc_program_id
180 , p_start_time
181 FROM poa_edw_po_dist_inc inc,
182 gl_sets_of_books gl
183 , po_distributions_all pod
184 , po_line_locations_all psc
185 , po_lines_all plc
186 , po_headers_all phc
187 , po_releases_all por
188 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID
189 and phc.po_header_id = plc.po_header_id
190 and plc.po_line_id = psc.po_line_id
191 and psc.line_location_id = pod.line_location_id
192 and pod.po_release_id = por.po_release_id
193 and gl.set_of_books_id = pod.set_of_books_id
194 and psc.shipment_type in ('BLANKET', 'SCHEDULED')
195 and psc.approved_flag = 'Y'
196 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
197 and pod.creation_date is not null
198 and inc.batch_id = p_batch_no);
199
200 /* Insert standard POs created against contracts */
201
202 x_progress := '020';
203 INSERT into poa_bis_savings
204 ( purchase_amount
205 , contract_amount
206 , non_contract_amount
207 , pot_contract_amount
208 , potential_saving
209 , total_purchase_qty
210 , distribution_transaction_id
211 , document_type_code
212 , purchase_creation_date
213 , item_id
214 , item_description
215 , category_id
216 , supplier_site_id
217 , supplier_id
218 , requestor_id
219 , ship_to_location_id
220 , ship_to_organization_id
221 , operating_unit_id
222 , buyer_id
223 , project_id
224 , task_id
225 , currency_code
226 , rate_type
227 , rate_date
228 , cost_center_id
229 , account_id
230 , company_id
231 , rate
232 , approved_date
233 , Currency_Conv_Rate
234 , created_by
235 , creation_date
236 , last_updated_by
237 , last_update_date
238 , last_update_login
239 , request_id
240 , program_application_id
241 , program_id
242 , program_update_date)
243 (SELECT decode(psc.consigned_flag
244 ,'Y'
245 ,null
246 ,decode(psc.matching_basis
247 ,'AMOUNT'
248 ,(decode(psc.closed_code
249 ,'FINALLY_CLOSED'
250 ,(decode(sign(nvl(pod.amount_delivered,0)
251 -nvl(pod.amount_billed,0))
252 ,1
253 ,nvl(pod.amount_delivered,0)
254 ,nvl(pod.amount_billed,0)))
255 *nvl(pod.rate,1)
256 ,(nvl(pod.amount_ordered,0)
257 -nvl(pod.amount_cancelled,0))
258 *nvl(pod.rate,1)))
259 ,(decode(psc.closed_code
260 ,'FINALLY_CLOSED'
261 ,(decode(sign(nvl(pod.quantity_delivered,0)
262 -nvl(pod.quantity_billed,0))
263 ,1
264 ,nvl(pod.quantity_delivered,0)
265 ,nvl(pod.quantity_billed,0)))
266 *nvl(psc.price_override,0)
267 *nvl(pod.rate,1)
268 ,(nvl(pod.quantity_ordered,0)
269 -nvl(pod.quantity_cancelled,0))
270 *nvl(psc.price_override,0)
271 *nvl(pod.rate,1)))
272 )
273 )
274 , decode(psc.consigned_flag
275 ,'Y'
276 ,null
277 ,decode(psc.matching_basis
278 ,'AMOUNT'
279 ,(decode(psc.closed_code
280 ,'FINALLY_CLOSED'
281 ,(decode(sign(nvl(pod.amount_delivered,0)
282 -nvl(pod.amount_billed,0))
283 ,1
284 ,nvl(pod.amount_delivered,0)
285 ,nvl(pod.amount_billed,0)))
286 *nvl(pod.rate,1)
287 ,(nvl(pod.amount_ordered,0)
288 -nvl(pod.amount_cancelled,0))
289 *nvl(pod.rate,1)))
290 ,(decode(psc.closed_code
291 ,'FINALLY_CLOSED'
292 ,(decode(sign(nvl(pod.quantity_delivered,0)
293 -nvl(pod.quantity_billed,0))
294 ,1
295 ,nvl(pod.quantity_delivered,0)
296 ,nvl(pod.quantity_billed,0)))
297 *nvl(psc.price_override,0)
298 *nvl(pod.rate,1)
299 ,(nvl(pod.quantity_ordered,0)
300 -nvl(pod.quantity_cancelled,0))
301 *nvl(psc.price_override,0)
302 *nvl(pod.rate,1)))
303 )
304 )
305 , Decode(psc.consigned_flag, 'Y', NULL, 0)
306 , Decode(psc.consigned_flag, 'Y', NULL, 0)
307 , Decode(psc.consigned_flag, 'Y', NULL, 0)
308 , decode(psc.consigned_flag
309 ,'Y'
310 ,null
311 ,decode(psc.value_basis
312 ,'QUANTITY'
313 ,pod.quantity_ordered
314 ,null
315 )
316 )
317 , pod.po_distribution_id
318 , phc.type_lookup_code
319 , pod.creation_date
320 , plc.item_id
321 , plc.item_description
322 , plc.category_id
323 , phc.vendor_site_id
324 , phc.vendor_id
325 , pod.deliver_to_person_id
326 , psc.ship_to_location_id
327 , psc.ship_to_organization_id
328 , psc.org_id
329 , phc.agent_id
330 , pod.project_id
331 , pod.task_id
332 , gl.currency_code
333 , phc.rate_type
334 , nvl(phc.rate_date, pod.creation_date)
335 , pod.code_combination_id
336 , NULL
337 , NULL
338 , pod.rate
339 , NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pod.creation_date, phc.po_header_id),
340 phc.approved_date)
341 , POA_CURRENCY_PKG.get_global_currency_rate (phc.rate_type,
342 decode(phc.rate_type, 'User', gl.currency_code,
343 NVL(phc.currency_code,gl.currency_code)),
344 NVL(pod.rate_date, pod.creation_date), phc.rate)
345 , fnd_global.user_id
346 , p_start_time
347 , fnd_global.user_id
348 , p_start_time
349 , fnd_global.login_id
350 , fnd_global.conc_request_id
351 , fnd_global.prog_appl_id
352 , fnd_global.conc_program_id
353 , p_start_time
354 FROM poa_edw_po_dist_inc inc,
355 gl_sets_of_books gl
356 , po_distributions_all pod
357 , po_line_locations_all psc
358 , po_lines_all plc
359 , po_headers_all phc
360 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID
361 and phc.po_header_id = plc.po_header_id
362 and plc.po_line_id = psc.po_line_id
363 and psc.line_location_id = pod.line_location_id
364 and gl.set_of_books_id = pod.set_of_books_id
365 and psc.shipment_type = 'STANDARD'
366 and plc.contract_id is not null
367 and psc.approved_flag = 'Y'
368 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
369 and pod.creation_date is not null
370 and inc.batch_id = p_batch_no);
371
372 /* Insert standard POs created against global agreements */
373
374 x_progress := '020';
375 INSERT into poa_bis_savings
376 ( purchase_amount
377 , contract_amount
378 , non_contract_amount
379 , pot_contract_amount
380 , potential_saving
381 , total_purchase_qty
382 , distribution_transaction_id
383 , document_type_code
384 , purchase_creation_date
385 , item_id
386 , item_description
387 , category_id
388 , supplier_site_id
389 , supplier_id
390 , requestor_id
391 , ship_to_location_id
392 , ship_to_organization_id
393 , operating_unit_id
394 , buyer_id
395 , project_id
396 , task_id
397 , currency_code
398 , rate_type
399 , rate_date
400 , cost_center_id
401 , account_id
402 , company_id
403 , rate
404 , approved_date
405 , Currency_Conv_Rate
406 , created_by
407 , creation_date
408 , last_updated_by
409 , last_update_date
410 , last_update_login
411 , request_id
412 , program_application_id
413 , program_id
414 , program_update_date)
415 (SELECT decode(psc.consigned_flag
416 ,'Y'
417 ,null
418 ,decode(psc.matching_basis
419 ,'AMOUNT'
420 ,(decode(psc.closed_code
421 ,'FINALLY_CLOSED'
422 ,(decode(sign(nvl(pod.amount_delivered,0)
423 -nvl(pod.amount_billed,0))
424 ,1
425 ,nvl(pod.amount_delivered,0)
426 ,nvl(pod.amount_billed,0)))
427 *nvl(pod.rate,1)
428 ,(nvl(pod.amount_ordered,0)
429 -nvl(pod.amount_cancelled,0))
430 *nvl(pod.rate,1)))
431 ,(decode(psc.closed_code
432 ,'FINALLY_CLOSED'
433 ,(decode(sign(nvl(pod.quantity_delivered,0)
434 -nvl(pod.quantity_billed,0))
435 ,1
436 ,nvl(pod.quantity_delivered,0)
437 ,nvl(pod.quantity_billed,0)))
438 *nvl(psc.price_override,0)
439 *nvl(pod.rate,1)
440 ,(nvl(pod.quantity_ordered,0)
441 -nvl(pod.quantity_cancelled,0))
442 *nvl(psc.price_override,0) * nvl(pod.rate,1)))
443 )
444 )
445 , decode(psc. consigned_flag
446 ,'Y'
447 ,null
448 ,decode(psc.matching_basis
449 ,'AMOUNT'
450 ,(decode(psc.closed_code
451 ,'FINALLY_CLOSED'
452 ,(decode(sign(nvl(pod.amount_delivered,0)
453 -nvl(pod.amount_billed,0))
454 ,1
455 ,nvl(pod.amount_delivered,0)
456 ,nvl(pod.amount_billed,0)))
457 *nvl(pod.rate,1)
458 ,(nvl(pod.amount_ordered,0)
459 -nvl(pod.amount_cancelled,0))
460 * nvl(pod.rate,1)))
461 ,(decode(psc.closed_code
462 ,'FINALLY_CLOSED'
463 ,(decode(sign(nvl(pod.quantity_delivered,0)
464 -nvl(pod.quantity_billed,0))
465 ,1
466 ,nvl(pod.quantity_delivered,0)
467 ,nvl(pod.quantity_billed,0)))
468 *nvl(psc.price_override,0)
469 *nvl(pod.rate,1)
470 ,(nvl(pod.quantity_ordered,0)
471 -nvl(pod.quantity_cancelled,0))
472 *nvl(psc.price_override,0)
473 *nvl(pod.rate,1)))
474 )
475 )
476 , Decode(psc.consigned_flag, 'Y', NULL, 0)
477 , Decode(psc.consigned_flag, 'Y', NULL, 0)
478 , Decode(psc.consigned_flag, 'Y', NULL, 0)
479 , decode(psc. consigned_flag
480 ,'Y'
481 ,null
482 ,decode(psc.value_basis
483 ,'QUANTITY'
484 ,pod.quantity_ordered
485 ,null
486 )
487 )
488 , pod.po_distribution_id
489 , phc.type_lookup_code
490 , pod.creation_date
491 , plc.item_id
492 , plc.item_description
493 , plc.category_id
494 , phc.vendor_site_id
495 , phc.vendor_id
496 , pod.deliver_to_person_id
497 , psc.ship_to_location_id
498 , psc.ship_to_organization_id
499 , psc.org_id
500 , phc.agent_id
501 , pod.project_id
502 , pod.task_id
503 , gl.currency_code
504 , phc.rate_type
505 , nvl(phc.rate_date, pod.creation_date)
506 , pod.code_combination_id
507 , NULL
508 , NULL
509 , pod.rate
510 , NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pod.creation_date, phc.po_header_id),
511 phc.approved_date)
512 , POA_CURRENCY_PKG.get_global_currency_rate (phc.rate_type,
513 decode(phc.rate_type, 'User', gl.currency_code,
514 NVL(phc.currency_code,gl.currency_code)),
515 NVL(pod.rate_date, pod.creation_date), phc.rate)
516 , fnd_global.user_id
517 , p_start_time
518 , fnd_global.user_id
519 , p_start_time
520 , fnd_global.login_id
521 , fnd_global.conc_request_id
522 , fnd_global.prog_appl_id
523 , fnd_global.conc_program_id
524 , p_start_time
525 FROM poa_edw_po_dist_inc inc,
526 gl_sets_of_books gl
527 , po_distributions_all pod
528 , po_line_locations_all psc
529 , po_lines_all plc
530 , po_headers_all phc
531 , po_headers_all ga
532 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID
533 and phc.po_header_id = plc.po_header_id
534 and plc.po_line_id = psc.po_line_id
535 and psc.line_location_id = pod.line_location_id
536 and gl.set_of_books_id = pod.set_of_books_id
537 and psc.shipment_type = 'STANDARD'
538 and plc.from_header_id = ga.po_header_id
539 and ga.global_agreement_flag = 'Y'
540 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
541 AND plc.contract_id IS NULL -- in case we have both cpa and ga reference
542 and psc.approved_flag = 'Y'
543 and pod.creation_date is not null
544 and inc.batch_id = p_batch_no);
545
546 /* Insert standard POs created for Complex work procurement (R12) */
547 x_progress := '020';
548
549 insert into poa_bis_savings
550 (
551 purchase_amount,
552 contract_amount,
553 non_contract_amount,
554 pot_contract_amount,
555 potential_saving,
556 total_purchase_qty,
557 distribution_transaction_id,
558 document_type_code,
559 purchase_creation_date,
560 item_id,
561 item_description,
562 category_id,
563 supplier_site_id,
564 supplier_id,
565 requestor_id,
566 ship_to_location_id,
567 ship_to_organization_id,
568 operating_unit_id,
569 buyer_id,
570 project_id,
571 task_id,
572 currency_code,
573 rate_type,
574 rate_date,
575 cost_center_id,
576 account_id,
577 company_id,
578 rate,
579 approved_date,
580 Currency_Conv_Rate,
581 created_by,
582 creation_date,
583 last_updated_by,
584 last_update_date,
585 last_update_login,
586 request_id,
587 program_application_id,
588 program_id,
589 program_update_date
590 )
591 (
592 select /*+ cardinality(inc, 1) */ decode(psc.consigned_flag
593 ,'Y'
594 ,null
595 ,decode(psc.matching_basis
596 ,'AMOUNT'
597 ,(decode(psc.closed_code
598 ,'FINALLY_CLOSED'
599 ,(decode(sign(nvl(pod.amount_delivered,0)
600 -nvl(pod.amount_billed,0))
601 ,1
602 ,nvl(pod.amount_delivered,0)
603 ,nvl(pod.amount_billed,0)))
604 *nvl(pod.rate,1)
605 ,(nvl(pod.amount_ordered,0)
606 -nvl(pod.amount_cancelled,0))
607 *nvl(pod.rate,1)))
608 ,(decode(psc.closed_code
609 ,'FINALLY_CLOSED'
610 ,(decode(sign(nvl(pod.quantity_delivered,0)
611 -nvl(pod.quantity_billed,0))
612 ,1
613 ,nvl(pod.quantity_delivered,0)
614 ,nvl(pod.quantity_billed,0)))
615 *nvl(psc.price_override,0)
616 *nvl(pod.rate,1)
617 ,(nvl(pod.quantity_ordered,0)
618 -nvl(pod.quantity_cancelled,0))
619 *nvl(psc.price_override,0) * nvl(pod.rate,1)))
620 )
621 )
622 , decode(psc. consigned_flag
623 ,'Y'
624 ,null
625 ,decode(psc.matching_basis
626 ,'AMOUNT'
627 ,(decode(psc.closed_code
628 ,'FINALLY_CLOSED'
629 ,(decode(sign(nvl(pod.amount_delivered,0)
630 -nvl(pod.amount_billed,0))
631 ,1
632 ,nvl(pod.amount_delivered,0)
633 ,nvl(pod.amount_billed,0)))
634 *nvl(pod.rate,1)
635 ,(nvl(pod.amount_ordered,0)
636 -nvl(pod.amount_cancelled,0))
637 * nvl(pod.rate,1)))
638 ,(decode(psc.closed_code
639 ,'FINALLY_CLOSED'
640 ,(decode(sign(nvl(pod.quantity_delivered,0)
641 -nvl(pod.quantity_billed,0))
642 ,1
643 ,nvl(pod.quantity_delivered,0)
644 ,nvl(pod.quantity_billed,0)))
645 *nvl(psc.price_override,0)
646 *nvl(pod.rate,1)
647 ,(nvl(pod.quantity_ordered,0)
648 -nvl(pod.quantity_cancelled,0))
649 *nvl(psc.price_override,0)
650 *nvl(pod.rate,1)))
651 )
652 )
653 , Decode(psc.consigned_flag, 'Y', NULL, 0)
654 , Decode(psc.consigned_flag, 'Y', NULL, 0)
655 , Decode(psc.consigned_flag, 'Y', NULL, 0)
656 , decode(psc. consigned_flag
657 ,'Y'
658 ,null
659 ,decode(psc.value_basis
660 ,'QUANTITY'
661 ,pod.quantity_ordered
662 ,null
663 )
664 )
665 , pod.po_distribution_id
666 , phc.type_lookup_code
667 , pod.creation_date
668 , plc.item_id
669 , plc.item_description
670 , plc.category_id
671 , phc.vendor_site_id
672 , phc.vendor_id
673 , pod.deliver_to_person_id
674 , psc.ship_to_location_id
675 , psc.ship_to_organization_id
676 , psc.org_id
677 , phc.agent_id
678 , pod.project_id
679 , pod.task_id
680 , gl.currency_code
681 , phc.rate_type
682 , nvl(phc.rate_date, pod.creation_date)
683 , pod.code_combination_id
684 , NULL
685 , NULL
686 , pod.rate
687 , NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pod.creation_date, phc.po_header_id),
688 phc.approved_date)
689 , POA_CURRENCY_PKG.get_global_currency_rate (phc.rate_type,
690 decode(phc.rate_type, 'User', gl.currency_code,
691 NVL(phc.currency_code,gl.currency_code)),
692 NVL(pod.rate_date, pod.creation_date), phc.rate)
693 , fnd_global.user_id
694 , p_start_time
695 , fnd_global.user_id
696 , p_start_time
697 , fnd_global.login_id
698 , fnd_global.conc_request_id
699 , fnd_global.prog_appl_id
700 , fnd_global.conc_program_id
701 , p_start_time
702 from poa_edw_po_dist_inc inc,
703 gl_sets_of_books gl
704 , po_distributions_all pod
705 , po_line_locations_all psc
706 , po_lines_all plc
707 , po_headers_all phc
708 , po_doc_style_headers style
709 where inc.primary_key = pod.po_distribution_id
710 and phc.po_header_id = plc.po_header_id
711 and plc.po_line_id = psc.po_line_id
712 and psc.line_location_id = pod.line_location_id
713 and gl.set_of_books_id = pod.set_of_books_id
714 and phc.style_id = style.style_id
715 and nvl(style.progress_payment_flag,'N') = 'Y'
716 and psc.shipment_type = 'STANDARD'
717 and plc.from_header_id is null
718 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
719 AND plc.contract_id IS NULL -- in case we have both cpa reference
720 and psc.approved_flag = 'Y'
721 and pod.creation_date is not null
722 and inc.batch_id = p_batch_no);
723
724 POA_LOG.debug_line('Populate_contract exit');
725 --
726 EXCEPTION
727 WHEN others THEN
728 v_buf := 'Contract function: ' || sqlcode || ': ' || sqlerrm || ': ' || x_progress;
729 ROLLBACK;
730
731 POA_LOG.put_line(v_buf);
732 POA_LOG.put_line(' ');
733 RAISE;
734 --
735 END populate_contract;
736 --
737 END poa_savings_con;