[Home] [Help]
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
197: and pll.shipment_type in ('STANDARD','PREPAYMENT')
198: and pll.approved_flag = 'Y'
199: and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
200: and poa_gl.set_of_books_id = pod.set_of_books_id
201: and pod.creation_date >= d_glob_date
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
276: and pll.shipment_type in ('STANDARD','PREPAYMENT')
277: and pll.approved_flag = 'Y'
278: and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
279: and poa_gl.set_of_books_id = pod.set_of_books_id
280: and pod.creation_date >= d_glob_date
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')
296: and pll.approved_flag = 'Y'
297: and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
298: and poa_gl.set_of_books_id = pod.set_of_books_id
299: and pod.creation_date >= d_glob_date
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')
315: and pll.approved_flag = 'Y'
316: and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
317: and poa_gl.set_of_books_id = pod.set_of_books_id
318: and pod.creation_date >= d_glob_date
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')
334: and pll.approved_flag = 'Y'
335: and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
336: and poa_gl.set_of_books_id = pod.set_of_books_id
337: and pod.creation_date >= d_glob_date
534: where pod.po_distribution_id = inc.primary_key
535: and pod.po_line_id = pol.po_line_id
536: and pol.auction_header_id = ponbh.auction_header_id
537: and ponbh.auction_header_id = ponh.auction_header_id
538: and ponh.contract_type = 'STANDARD'
539: and ponh.org_id = pfsp.org_id
540: and pfsp.set_of_books_id = pgl.set_of_books_id
541: and ponh.creation_date >= d_glob_date
542: union
627: where pod.po_distribution_id = inc.primary_key
628: and pod.po_line_id = pol.po_line_id
629: and pol.auction_header_id = ponbh.auction_header_id
630: and ponbh.auction_header_id = ponh.auction_header_id
631: and ponh.contract_type = 'STANDARD'
632: and ponh.org_id = pfsp.org_id
633: and pfsp.set_of_books_id = pgl.set_of_books_id
634: and ponh.creation_date >= d_glob_date
635: union
762: and ponip.auction_header_id = ponh.auction_header_id
763: and ponbh.bid_number = ponbip.bid_number
764: and ponbip.auction_line_number = ponip.line_number
765: and ponh.org_id = pfsp.org_id
766: and ponbh.contract_type in ('STANDARD','BLANKET')
767: and pfsp.set_of_books_id = pgl.set_of_books_id
768: and pfsp.inventory_organization_id = ppar.organization_id
769: and ponip.item_id = pitem.inventory_item_id(+)
770: and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
1008: and ponip.auction_header_id = ponh.auction_header_id
1009: and ponbh.bid_number = ponbip.bid_number
1010: and ponbip.auction_line_number = ponip.line_number
1011: and ponh.org_id = pfsp.org_id
1012: and ponbh.contract_type in ('STANDARD','BLANKET')
1013: and pfsp.set_of_books_id = pgl.set_of_books_id
1014: and pfsp.inventory_organization_id = ppar.organization_id
1015: and ponip.item_id = pitem.inventory_item_id(+)
1016: and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
1301: where enabled_flag = 'Y'
1302: ) pgoa
1303: WHERE plc.po_line_id = psc.po_line_id
1304: and psc.line_location_id = pod.line_location_id
1305: and psc.shipment_type = 'STANDARD'
1306: and plc.from_header_id = ga.po_header_id(+)
1307: and nvl(ga.global_agreement_flag, 'N') = 'N'
1308: and psc.approved_flag = 'Y'
1309: and plc.item_id is not null
1444: pb.creation_date,
1445: blanket.po_header_id,
1446: blanket.vendor_id
1447: , nvl(blanket.bl_rate, 1) * decode(blanket.bl_func_cur_code, ptmp3.func_cur_code, 1, poa_ga_util_pkg.get_ga_conversion_rate(blanket.bl_func_cur_code, ptmp3.func_cur_code, ptmp3.rate_date)) cur_conversion_rate
1448: -- convert to blanket functional currency and then to standard PO functional currency
1449: ,(case when pb.ship_to_location_id = ptmp3.ship_to_location_id
1450: and ( pb.quantity is null or ( price_break_lookup_code = 'NON CUMULATIVE' and ptmp3.quantity >= pb.quantity)
1451: or (price_break_lookup_code = 'CUMULATIVE' and ptmp3.quantity + blanket.line_qty >= pb.quantity))
1452: then pb.price_override else null end) price1
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'))
1476: GROUP by b.po_distribution_id, b.po_line_id, b.po_header_id, b.creation_date, b.vendor_id, b.global_agreement_flag,
1477: b.b_item_id, b.bl_min_release_amount, b.b_unit_meas_lookup_code, b.cancel_flag,
1478: b.expiration_date, b.price_break_lookup_code, b.unit_price,b.amount_limit, b.b_min_release_amount, b.bl_creation_date, b.bl_func_cur_code, b.bl_rate
1479: ) blanket,
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'))
1492: GROUP by b.po_distribution_id, b.po_header_id
1493: ) bblanket,
1494: poa_dbi_pod_match_temp ptmp3,
1495: po_line_locations_all pb
1755: )
1756: else 0 end
1757: ) non_contract_amount,
1758: (CASE WHEN (pol.item_id IS NOT NULL
1759: AND pll.shipment_type = 'STANDARD'
1760: AND (nvl(ref.global_agreement_flag, 'N') = 'N')
1761: AND match.po_distribution_id IS NOT null
1762: and nvl(pol.negotiated_by_preparer_flag,'N')='N')
1763: THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)) * Nvl(pll.price_override,0))
1762: and nvl(pol.negotiated_by_preparer_flag,'N')='N')
1763: THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)) * Nvl(pll.price_override,0))
1764: ELSE 0 END) pot_contract_amount,
1765: (CASE WHEN (pol.item_id IS NOT NULL
1766: AND pll.shipment_type = 'STANDARD'
1767: AND (nvl(ref.global_agreement_flag, 'N') = 'N')
1768: AND match.po_distribution_id IS NOT null
1769: AND nvl(pol.negotiated_by_preparer_flag,'N') = 'N')
1770: THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0))
2018: else 0
2019: end
2020: ) non_contract_amount,
2021: (CASE WHEN (pol.item_id IS NOT NULL
2022: AND pll.shipment_type = 'STANDARD'
2023: AND (nvl(ref.global_agreement_flag, 'N') = 'N')
2024: AND match.po_distribution_id IS NOT null
2025: and nvl(pol.negotiated_by_preparer_flag,'N') = 'N')
2026: THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)) * Nvl(pll.price_override,0))
2025: and nvl(pol.negotiated_by_preparer_flag,'N') = 'N')
2026: THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)) * Nvl(pll.price_override,0))
2027: ELSE 0 END) pot_contract_amount,
2028: (CASE WHEN (pol.item_id IS NOT NULL
2029: AND pll.shipment_type = 'STANDARD'
2030: AND (nvl(ref.global_agreement_flag, 'N') = 'N')
2031: AND match.po_distribution_id IS NOT null
2032: and nvl(pol.negotiated_by_preparer_flag,'N') = 'N')
2033: THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0))
2040: pol.item_id,
2041: pol.item_revision,
2042: pol.category_id,
2043: pll.ship_to_location_id,
2044: poa_gl.currency_code, -- standard PO functional currency
2045: nvl(pod.rate_date, pod.creation_date), -- rate date
2046: match.ship_to_ou_id, -- ship to OU
2047: pll.ship_to_organization_id,
2048: pod.po_distribution_id,
2077: (case when pll.value_basis = 'QUANTITY'
2078: then pod.quantity_cancelled
2079: else null
2080: end) quantity_cancelled,
2081: (case when (pol.item_id is not null and pll.shipment_type = 'STANDARD' and match.po_distribution_id is not null)
2082: then poa_dbi_savings_pkg.get_lowest_possible_price(pod.creation_date,
2083: poh.org_id,
2084: pll.need_by_date,
2085: pll.quantity, --shipment quantity
2088: pol.item_id,
2089: pol.item_revision,
2090: pol.category_id,
2091: pll.ship_to_location_id,
2092: poa_gl.currency_code, -- standard PO functional currency
2093: nvl(pod.rate_date, pod.creation_date), -- rate date
2094: match.ship_to_ou_id, -- ship to OU
2095: pll.ship_to_organization_id,
2096: pod.po_distribution_id,
2211: and psc.line_location_id = pod.line_location_id
2212: and phc.po_header_id = plc.po_header_id
2213: and phc.style_id = style.style_id
2214: and nvl(style.progress_payment_flag,'N') = 'N'
2215: and psc.shipment_type = 'STANDARD'
2216: and plc.from_header_id = ga.po_header_id (+)
2217: and nvl(ga.global_agreement_flag, 'N') = 'N'
2218: and psc.approved_flag = 'Y'
2219: and plc.item_id is not null