[Home] [Help]
188: from
189: po_lines_all pol,
190: po_line_locations_all pll,
191: po_headers_all poh,
192: po_distributions_all pod,
193: gl_sets_of_books poa_gl
194: where pod.line_location_id = pll.line_location_id
195: and pod.po_line_id = pol.po_line_id
196: and pod.po_header_id = poh.po_header_id
216: po_lines_all pol,
217: po_line_locations_all pll,
218: po_headers_all poh,
219: po_releases_all por,
220: po_distributions_all pod,
221: gl_sets_of_books poa_gl
222: where pod.line_location_id = pll.line_location_id
223: and pod.po_release_id = por.po_release_id
224: and pod.po_line_id = pol.po_line_id
267: from
268: po_lines_all pol,
269: po_line_locations_all pll,
270: po_headers_all poh,
271: po_distributions_all pod,
272: gl_sets_of_books poa_gl
273: where pod.line_location_id = pll.line_location_id
274: and pod.po_line_id = pol.po_line_id
275: and pod.po_header_id = poh.po_header_id
287: trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
288: from
289: po_line_locations_all pll,
290: po_headers_all poh,
291: po_distributions_all pod,
292: gl_sets_of_books poa_gl
293: where pod.line_location_id = pll.line_location_id
294: and pod.po_header_id = poh.po_header_id
295: and pll.shipment_type in ('STANDARD','PREPAYMENT')
306: trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
307: from
308: po_line_locations_all pll,
309: po_headers_all poh,
310: po_distributions_all pod,
311: gl_sets_of_books poa_gl
312: where pod.line_location_id = pll.line_location_id
313: and pod.po_header_id = poh.po_header_id
314: and pll.shipment_type in ('STANDARD','PREPAYMENT')
325: trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
326: from
327: po_line_locations_all pll,
328: po_headers_all poh,
329: po_distributions_all pod,
330: gl_sets_of_books poa_gl
331: where pod.line_location_id = pll.line_location_id
332: and pod.po_header_id = poh.po_header_id
333: and pll.shipment_type in ('STANDARD','PREPAYMENT')
347: from
348: po_lines_all pol,
349: po_line_locations_all pll,
350: po_headers_all poh,
351: po_distributions_all pod,
352: gl_sets_of_books poa_gl
353: where pod.line_location_id = pll.line_location_id
354: and pod.po_line_id = pol.po_line_id
355: and pod.po_header_id = poh.po_header_id
367: trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
368: from
369: po_line_locations_all pll,
370: po_headers_all poh,
371: po_distributions_all pod,
372: gl_sets_of_books poa_gl
373: where pod.line_location_id = pll.line_location_id
374: and pod.po_header_id = poh.po_header_id
375: and pll.shipment_type in ('BLANKET', 'SCHEDULED')
386: trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
387: from
388: po_line_locations_all pll,
389: po_headers_all poh,
390: po_distributions_all pod,
391: gl_sets_of_books poa_gl
392: where pod.line_location_id = pll.line_location_id
393: and pod.po_header_id = poh.po_header_id
394: and pll.shipment_type in ('BLANKET', 'SCHEDULED')
405: trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
406: from
407: po_line_locations_all pll,
408: po_headers_all poh,
409: po_distributions_all pod,
410: gl_sets_of_books poa_gl
411: where pod.line_location_id = pll.line_location_id
412: and pod.po_header_id = poh.po_header_id
413: and pll.shipment_type in ('BLANKET', 'SCHEDULED')
425: from
426: po_line_locations_all pll,
427: po_headers_all poh,
428: po_releases_all por,
429: po_distributions_all pod,
430: gl_sets_of_books poa_gl
431: where pod.line_location_id = pll.line_location_id
432: and pod.po_release_id = por.po_release_id
433: and pod.po_header_id = poh.po_header_id
524: distinct ponh.currency_code txn_cur_code,
525: pgl.currency_code func_cur_code,
526: nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
527: from poa_dbi_pod_inc inc,
528: po_distributions_all pod,
529: po_lines_all pol,
530: pon_bid_headers ponbh,
531: pon_auction_headers_all ponh,
532: financials_system_params_all pfsp,
544: distinct ponh.currency_code txn_cur_code,
545: pgl.currency_code func_cur_code,
546: nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
547: from poa_dbi_pod_inc inc,
548: po_distributions_all pod,
549: po_lines_all pol,
550: pon_bid_headers ponbh,
551: pon_auction_headers_all ponh,
552: financials_system_params_all pfsp,
565: pgl.currency_code func_cur_code,
566: nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
567: from
568: poa_dbi_pod_inc inc,
569: po_distributions_all pod,
570: po_lines_all pol,
571: pon_bid_headers ponbh,
572: pon_auction_headers_all ponh,
573: financials_system_params_all pfsp,
617: distinct ponh.currency_code txn_cur_code,
618: pgl.currency_code func_cur_code,
619: nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
620: from poa_dbi_pod_inc inc,
621: po_distributions_all pod,
622: po_lines_all pol,
623: pon_bid_headers ponbh,
624: pon_auction_headers_all ponh,
625: financials_system_params_all pfsp,
637: distinct ponh.currency_code txn_cur_code,
638: pgl.currency_code func_cur_code,
639: nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
640: from poa_dbi_pod_inc inc,
641: po_distributions_all pod,
642: po_lines_all pol,
643: pon_bid_headers ponbh,
644: pon_auction_headers_all ponh,
645: financials_system_params_all pfsp,
658: pgl.currency_code func_cur_code,
659: nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
660: from
661: poa_dbi_pod_inc inc,
662: po_distributions_all pod,
663: po_lines_all pol,
664: pon_bid_headers ponbh,
665: pon_auction_headers_all ponh,
666: financials_system_params_all pfsp,
733: )
734: ) neg_base_uom_conv_rate
735: from
736: poa_dbi_pod_inc inc,
737: po_distributions_all pod,
738: po_line_locations_all pll,
739: po_lines_all pol,
740: pon_bid_item_prices ponbip,
741: pon_bid_headers ponbh,
808: )
809: ) neg_base_uom_conv_rate
810: from
811: poa_dbi_pod_inc inc,
812: po_distributions_all pod,
813: po_line_locations_all pll,
814: po_lines_all pol,
815: po_headers_all poh_orig,
816: po_lines_all pol_orig,
888: )
889: ) neg_base_uom_conv_rate
890: from
891: poa_dbi_pod_inc inc,
892: po_distributions_all pod,
893: po_line_locations_all pll,
894: po_lines_all pol,
895: po_headers_all poh_orig,
896: pon_bid_item_prices ponbip,
979: )
980: ) neg_base_uom_conv_rate
981: from
982: poa_dbi_pod_inc inc,
983: po_distributions_all pod,
984: po_line_locations_all pll,
985: po_lines_all pol,
986: pon_bid_item_prices ponbip,
987: pon_bid_headers ponbh,
1054: )
1055: ) neg_base_uom_conv_rate
1056: from
1057: poa_dbi_pod_inc inc,
1058: po_distributions_all pod,
1059: po_line_locations_all pll,
1060: po_lines_all pol,
1061: po_headers_all poh_orig,
1062: po_lines_all pol_orig,
1134: )
1135: ) neg_base_uom_conv_rate
1136: from
1137: poa_dbi_pod_inc inc,
1138: po_distributions_all pod,
1139: po_line_locations_all pll,
1140: po_lines_all pol,
1141: po_headers_all poh_orig,
1142: pon_bid_item_prices ponbip,
1254: pol.category_id,
1255: pll.ship_to_organization_id
1256: FROM
1257: poa_dbi_pod_inc inc,
1258: po_distributions_all pod,
1259: po_line_locations_all pll,
1260: po_lines_all pol,
1261: po_headers_all poh,
1262: po_doc_style_headers style,
1267: distinct
1268: pod.po_distribution_id,
1269: hro.ship_to_ou_id
1270: FROM
1271: po_distributions_all pod,
1272: po_line_locations_all psc,
1273: po_lines_all plc,
1274: poa_dbi_pod_inc inc,
1275: po_headers_all ga,
1467: ,sum(case when rll.approved_flag='Y' and rd.creation_date < b.creation_date then
1468: nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0) else 0 end) line_all_qty
1469: from bb b
1470: ,po_line_locations_all rll
1471: ,po_distributions_all rd
1472: where b.std_id = rll.po_line_id(+)
1473: and rll.line_location_id = rd.line_location_id(+)
1474: and nvl(rd.distribution_type,'-99') <> 'AGREEMENT'
1475: and (rll.shipment_type is null or rll.shipment_type in ('BLANKET', 'STANDARD', 'PRICE BREAK'))
1483: sum(case when rd.creation_date < b.creation_date then
1484: nvl(rll.price_override,0) * (nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0)) else 0 END) blanket_amt
1485: from bb b
1486: ,po_line_locations_all rll
1487: ,po_distributions_all rd
1488: where b.std_id = rll.po_line_id(+)
1489: and rll.line_location_id = rd.line_location_id(+)
1490: and nvl(rd.distribution_type,'-99') <> 'AGREEMENT'
1491: and (rll.shipment_type is null or rll.shipment_type in ('BLANKET', 'STANDARD', 'PRICE BREAK'))
1898: pod.amount_ordered,
1899: pod.amount_cancelled
1900: from
1901: po_line_locations_archive_all a,
1902: po_distributions_all pod
1903: where pod.line_location_id = a.line_location_id(+)
1904: and a.approved_date(+) >= pod.creation_date
1905: and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
1906: group by
2156: poa_dbi_pod_inc inc,
2157: poa_dbi_neg_details negd,
2158: poa_dbi_pod_rates rat,
2159: gl_sets_of_books poa_gl,
2160: po_distributions_all pod,
2161: po_line_locations_all pll,
2162: po_lines_all pol,
2163: po_headers_all poh,
2164: po_requisition_lines_all prl,
2173: pon_auction_headers_all ponh,
2174: ( SELECT /*+ cardinality(inc,1) */
2175: distinct pod.po_distribution_id, hro.ship_to_ou_id
2176: FROM
2177: po_distributions_all pod,
2178: po_line_locations_all psc,
2179: po_lines_all plc,
2180: poa_dbi_pod_inc inc,
2181: po_headers_all ga,
2537: bis_collection_utilities.log('EAD batch no='||v_batch_no || ' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 1);
2538: update poa_dbi_pod_f f
2539: set (approved_date, po_approval_cycle_time) = (SELECT min(approved_date), MIN(approved_date - pod.creation_date)
2540: from po_line_locations_archive_all pll
2541: ,po_distributions_all pod
2542: where pod.po_distribution_id = f.po_distribution_id
2543: and pll.line_location_id = pod.line_location_id
2544: and pll.approved_date >= pod.creation_date)
2545: where f.po_distribution_id in (select primary_key from poa_dbi_pod_inc