VIEW: APPS.POABV_EDW_PO_DISTRIBUTIONS_FCV

Source

View Text - Preformatted

SELECT /* ORDERED FULL(INC) USE_NL(POD) USE_NL(PLL) USE_NL(POL) USE_NL(POH) USE_NL(POR) USE_NL(SVNGS) */ POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type, 'User', gsob.currency_code, NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate) / decode(poh.rate_type, 'User', poh.rate, 1), inc.seq_id , 1 , to_char(pod.po_distribution_id) || '-' || eli.instance_code , decode(pol.vendor_product_num, null, 'NA_EDW', decode(pvs.vendor_site_code, null, 'NA_EDW', pov.vendor_name || '-' || pvs.vendor_site_code || '-' || pol.vendor_product_num)) , eli.instance_code , 'NA_EDW' /* POA_CUSTOMIZATION_PKG.purchase_classification_code( pll.line_location_id, 'PO_LINE_LOCATIONS_ALL') || '-' || 'PURCHASE CLASSIFICATION' || '-' || 'PO' */ , decode(nvl(por.agent_id, poh.agent_id), NULL, 'NA_EDW', nvl(por.agent_id, poh.agent_id) || '-' || eli.instance_code || '-' || 'EMPLOYEE'|| '-' || 'PERS') , decode(pod.deliver_to_person_id, NULL, 'NA_EDW', pod.deliver_to_person_id || '-' || eli.instance_code || '-' || 'EMPLOYEE'|| '-' || 'PERS') , decode(NVL(POA_EDW_VARIABLES_PKG.APPROVED_BY(pod.po_header_id), poh.agent_id), NULL, 'NA_EDW', NVL(POA_EDW_VARIABLES_PKG.APPROVED_BY(pod.po_header_id), poh.agent_id) || '-' || eli.instance_code || '-' || 'EMPLOYEE'|| '-' || 'PERS') , EDW_ITEMS_PKG.item_org_fk(pol.item_id, DECODE(pol.item_id, NULL, pod.org_id, fsp.inventory_organization_id), pol.item_description, pol.category_id, eli.instance_code) , decode(poh.vendor_site_id, NULL, 'NA_EDW', poh.vendor_site_id || '-' || poh.org_id || '-' || eli.instance_code || '-SUPPLIER_SITE') , decode(pll.ship_to_organization_id, NULL, 'NA_EDW', pll.ship_to_organization_id || '-' || eli.instance_code) , decode(pod.destination_organization_id, NULL, 'NA_EDW', pod.destination_organization_id || '-' || eli.instance_code) , decode(pod.set_of_books_id, NULL, 'NA_EDW', pod.set_of_books_id || '-' || eli.instance_code) , decode(pll.ship_to_location_id, NULL, 'NA_EDW', decode(hrl1.location_id, null, EDW_GEOGRAPHY_PKG.HZ_Postcode_City_fk(pll.ship_to_location_id), hrl1.town_or_city || '-' || hrl1.postal_code || '-' || hrl1.region_2 || '-' || hrl1.country)) , decode(poh.bill_to_location_id, NULL, 'NA_EDW', hrl2.town_or_city || '-' || hrl2.postal_code || '-' || hrl2.region_2 || '-' || hrl2.country) , decode(pod.deliver_to_location_id, NULL, 'NA_EDW', decode(hrl3.location_id, null, EDW_GEOGRAPHY_PKG.HZ_Postcode_City_fk(pod.deliver_to_location_id), hrl3.town_or_city || '-' || hrl3.postal_code || '-' || hrl3.region_2 || '-' || hrl3.country)) , decode(poh.vendor_site_id, NULL, 'NA_EDW', pvs.city || '-' || pvs.zip || '-' || decode(pvs.state, null, pvs.province, pvs.state) || '-' || pvs.country) , DECODE(pod.task_id, NULL, decode(pod.project_id, NULL, 'NA_EDW', pod.project_id || '-' || eli.instance_code || '-PJ-PRJ'), decode(pod.task_id, NULL, 'NA_EDW', pod.task_id || '-' || eli.instance_code)) , decode(decode(pll.shipment_type, 'STANDARD', poh.creation_date, por.creation_date), NULL, 'NA_EDW', to_char(decode(pll.shipment_type, 'STANDARD', poh.creation_date, por.creation_date), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(decode(pll.shipment_type, 'STANDARD', pol.creation_date, pll.creation_date), NULL, 'NA_EDW', to_char(decode(pll.shipment_type, 'STANDARD', pol.creation_date, pll.creation_date), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(pll.creation_date, NULL, 'NA_EDW', to_char(pll.creation_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(pod.creation_date, NULL, 'NA_EDW', to_char(pod.creation_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(svngs.approved_date, NULL, 'NA_EDW', to_char(svngs.approved_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(decode(pll.shipment_type, 'STANDARD', poh.revised_date, por.revised_date), NULL, 'NA_EDW', to_char(decode(pll.shipment_type, 'STANDARD', poh.revised_date, por.revised_date), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(decode(pll.shipment_type, 'STANDARD', poh.acceptance_due_date, por.acceptance_due_date), NULL, 'NA_EDW', to_char(decode(pll.shipment_type, 'STANDARD', poh.acceptance_due_date, por.acceptance_due_date), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(decode(pll.shipment_type, 'STANDARD', poh.creation_date, por.release_date), NULL, 'NA_EDW', to_char(decode(pll.shipment_type, 'STANDARD', poh.creation_date, por.release_date), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode( NVL(POA_OLTP_GENERIC_PKG.get_approved_date_pll(pod.creation_date, pll.line_location_id), pll.approved_date), NULL, 'NA_EDW', to_char ( NVL(POA_OLTP_GENERIC_PKG.get_approved_date_pll(pod.creation_date, pll.line_location_id), pll.approved_date), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(pll.last_accept_date, NULL, 'NA_EDW', to_char(pll.last_accept_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(pll.need_by_date, NULL, 'NA_EDW', to_char(pll.need_by_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(pll.promised_date, NULL, 'NA_EDW', to_char(pll.promised_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(decode(pll.shipment_type, 'STANDARD', poh.printed_date, por.printed_date), NULL, 'NA_EDW', to_char(decode(pll.shipment_type, 'STANDARD', poh.printed_date, por.printed_date), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(nvl(pod.rate_date, pod.creation_date), NULL, 'NA_EDW', to_char(nvl(pod.rate_date, pod.creation_date), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(prq.creation_date, NULL, 'NA_EDW', to_char(prq.creation_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(POA_EDW_VARIABLES_PKG.get_req_approval_date(pod.req_distribution_id), NULL, 'NA_EDW', to_char(POA_EDW_VARIABLES_PKG.get_req_approval_date(pod.req_distribution_id), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(decode(pod.po_release_id, NULL, POA_EDW_VARIABLES_PKG.get_acceptance_date (pod.po_header_id, 'P'), POA_EDW_VARIABLES_PKG.get_acceptance_date (pod.po_release_id, 'R')), NULL, 'NA_EDW', to_char(decode(pod.po_release_id, NULL, POA_EDW_VARIABLES_PKG.get_acceptance_date (pod.po_header_id, 'P'), POA_EDW_VARIABLES_PKG.get_acceptance_date (pod.po_release_id, 'R')), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(poh.terms_id, NULL, NULL, poh.terms_id || '-' || 'AP' || '-' || eli.instance_code) , decode(pll.shipment_type, NULL, NULL, (upper(pll.shipment_type) || '-' || 'SHIPMENT TYPE' || '-' || 'PO')) , decode(poh.ship_via_lookup_code, NULL, NULL, (upper(poh.ship_via_lookup_code) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG')) , decode(poh.fob_lookup_code, NULL, NULL, (upper(poh.fob_lookup_code) || '-' || 'FOB' || '-' || 'PO')) , decode(poh.freight_terms_lookup_code, NULL, NULL, (upper(poh.freight_terms_lookup_code) || '-' || 'FREIGHT TERMS' || '-' || 'PO')) , decode(pol.transaction_reason_code, NULL, NULL, (upper(pol.transaction_reason_code) || '-' || 'TRANSACTION REASON' || '-' || 'PO')) , decode(pol.price_type_lookup_code, NULL, NULL, (upper(pol.price_type_lookup_code) || '-' || 'PRICE TYPE' || '-' || 'PO')) , decode(pol.price_break_lookup_code, NULL, NULL, (upper(pol.price_break_lookup_code) || '-' || 'PRICE BREAK TYPE' || '-' || 'PO')) , decode(pll.closed_code, NULL, NULL, (upper(pll.closed_code) || '-' || 'DOCUMENT STATE' || '-' || 'PO')) , decode(pod.destination_type_code, NULL, NULL, (upper(pod.destination_type_code) || '-' || 'DESTINATION TYPE' || '-' || 'PO')) , decode(pll.shipment_type, 'STANDARD', decode(pol.contract_id, NULL, ('STANDARD' || '-' || 'AGREEMENT TYPE' || '-' || 'POD'), ('CONTRACT' || '-' || 'AGREEMENT TYPE' || '-' || 'POD')), 'SCHEDULED', ('PLANNED' || '-' || 'AGREEMENT TYPE' || '-' || 'POD'), 'BLANKET', ('BLANKET' || '-' || 'AGREEMENT TYPE' || '-' || 'POD')) , decode(rrh.routing_name, NULL, NULL, upper(rrh.routing_name) || '-' || 'RCV ROUTING' || '-' || 'ROU') , nvl(decode(pll.shipment_type, 'STANDARD', poh.acceptance_required_flag, por.acceptance_required_flag), 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(decode(pll.shipment_type, 'STANDARD', poh.frozen_flag, por.frozen_flag), 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(decode(pll.shipment_type, 'STANDARD', poh.user_hold_flag, por.hold_flag), 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(poh.confirming_order_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pol.negotiated_by_preparer_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pll.allow_substitute_receipts_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pll.approved_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pll.cancel_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pll.inspection_required_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pll.receipt_required_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pod.accrued_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pod.encumbered_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , decode(pod.req_distribution_id, NULL, 'N', 'Y') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pll.taxable_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(poh.edi_processed_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , decode(poh.pcard_id, NULL, 'N', 'Y') || '-' || 'YES_NO' || '-' || 'FND' , POA_EDW_VARIABLES_PKG.get_supplier_approved( pod.po_distribution_id, poh.vendor_id, poh.vendor_site_id, pll.ship_to_organization_id, pol.item_id, pol.category_id ) || '-' || 'YES_NO' || '-' || 'FND' , DECODE(POL.ORDER_TYPE_LOOKUP_CODE ,'QUANTITY' ,EDW_UTIL.GET_EDW_BASE_UOM(MTLU.UOM_CODE, POL.ITEM_ID) ,'NA_EDW') , DECODE(POL.ORDER_TYPE_LOOKUP_CODE ,'QUANTITY' ,EDW_UTIL.GET_EDW_UOM(MTLU.UOM_CODE, POL.ITEM_ID) ,'NA_EDW') , nvl(poh.currency_code,gsob.currency_code) , plt.line_type , decode(inc.check_cut_date, NULL, 'NA_EDW', to_char(inc.check_cut_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(inc.invoice_received_date, NULL, 'NA_EDW', to_char(inc.invoice_received_date, 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(POA_EDW_VARIABLES_PKG.get_invoice_creation_date (pod.po_distribution_id), NULL, 'NA_EDW', to_char(POA_EDW_VARIABLES_PKG.get_invoice_creation_date (pod.po_distribution_id), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(POA_EDW_VARIABLES_PKG.get_goods_received_date (pll.line_location_id), NULL, 'NA_EDW', to_char(POA_EDW_VARIABLES_PKG.get_goods_received_date (pll.line_location_id), 'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , NULL , NULL , NULL , to_number(svngs.approved_date - decode(pll.shipment_type, 'STANDARD', pod.creation_date, por.creation_date)) , decode(pll.consigned_flag, 'Y', null, to_number(inc.check_cut_date - svngs.approved_date)) , case when pll.consigned_flag='Y' or poh.consigned_consumption_flag='Y' or por.consigned_consumption_flag='Y' then null else to_number(inc.check_cut_date - POA_EDW_VARIABLES_PKG.get_goods_received_date (pll.line_location_id)) end , decode(pll.consigned_flag, 'Y', null, to_number(inc.invoice_received_date - POA_EDW_VARIABLES_PKG.get_invoice_creation_date (pod.po_distribution_id))) , decode(pll.consigned_flag, 'Y', null, to_number(inc.check_cut_date - inc.invoice_received_date)) , decode(pll.consigned_flag, 'Y', null, POA_EDW_VARIABLES_PKG.get_ipv(pod.po_distribution_id)/decode(pod.rate,NULL,1,0,1,pod.rate)) , decode(pll.consigned_flag, 'Y', null, (POA_EDW_VARIABLES_PKG.get_ipv(pod.po_distribution_id)/decode(pod.rate,NULL,1,0,1,pod.rate)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, nvl(svngs.purchase_amount/decode(pod.rate, NULL, 1, 0, 1, pod.rate),0)) , decode(pll.consigned_flag, 'Y', null, (nvl(svngs.purchase_amount/decode(pod.rate, NULL, 1, 0, 1, pod.rate),0)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, nvl(svngs.contract_amount/decode(pod.rate, NULL, 1, 0, 1, pod.rate), 0)) , decode(pll.consigned_flag, 'Y', null, (nvl(svngs.contract_amount/decode(pod.rate, NULL, 1, 0, 1, pod.rate), 0)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, nvl(svngs.non_contract_amount/decode(pod.rate, NULL, 1, 0, 1, pod.rate), 0)) , decode(pll.consigned_flag, 'Y', null, (nvl(svngs.non_contract_amount/decode(pod.rate, NULL, 1, 0, 1, pod.rate), 0)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, nvl(svngs.pot_contract_amount/decode(pod.rate, NULL, 1, 0, 1, pod.rate), 0)) , decode(pll.consigned_flag, 'Y', null, (nvl(svngs.pot_contract_amount/decode(pod.rate, NULL, 1, 0, 1, pod.rate), 0)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, nvl(svngs.potential_saving/decode(pod.rate, NULL, 1, 0, 1, pod.rate), 0)) , decode(pll.consigned_flag, 'Y', null, (nvl(svngs.potential_saving/decode(pod.rate, NULL, 1, 0, 1, pod.rate), 0)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, pod.amount_billed) , decode(pll.consigned_flag, 'Y', null, pod.amount_billed * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag ,'Y' ,NULL ,(CASE WHEN PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE (pod.quantity_billed * POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code, pol.item_id)) END)) , CASE WHEN poh.consigned_consumption_flag = 'Y' OR por.consigned_consumption_flag = 'Y' OR plt.order_type_lookup_code <> 'QUANTITY' THEN NULL ELSE pod.quantity_cancelled * POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code, pol.item_id) END , CASE WHEN poh.consigned_consumption_flag = 'Y' OR por.consigned_consumption_flag = 'Y' OR plt.order_type_lookup_code <> 'QUANTITY' THEN NULL ELSE pod.quantity_delivered * POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code, pol.item_id) END , decode(pll.consigned_flag ,'Y' ,NULL ,(CASE WHEN PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE(pod.quantity_ordered * POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code, pol.item_id)) END)) , decode(pll.consigned_flag, 'Y', null, pll.price_override / POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code,pol.item_id)) , decode(pll.consigned_flag, 'Y', null, (pll.price_override / POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code,pol.item_id))* POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, pol.list_price_per_unit / POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code,pol.item_id)) , decode(pll.consigned_flag, 'Y', null, (pol.list_price_per_unit / POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code,pol.item_id)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, pol.market_price / POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code,pol.item_id)) , decode(pll.consigned_flag, 'Y', null, (pol.market_price / POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code,pol.item_id)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , decode(pll.consigned_flag, 'Y', null, pol.not_to_exceed_price / POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code,pol.item_id)) , decode(pll.consigned_flag, 'Y', null, (pol.not_to_exceed_price / POA_EDW_VARIABLES_PKG.get_uom_conv_rate(mtlu.uom_code,pol.item_id)) * POA_EDW_VARIABLES_PKG.get_global_currency_rate (poh.rate_type, decode(poh.rate_type,'User',gsob.currency_code,NVL(poh.currency_code,gsob.currency_code)), NVL(pod.rate_date, pod.creation_date), poh.rate)) , pol.item_id , pol.item_description , decode(pll.shipment_type, 'STANDARD', poh.note_to_vendor, por.note_to_vendor) , pol.note_to_vendor , poh.comments , poh.note_to_receiver , pol.vendor_product_num , pll.cancel_reason , pll.closed_reason , pll.source_shipment_id , pod.po_distribution_id , pod.line_location_id , pod.po_header_id , pod.po_line_id , pod.po_release_id , pod.source_distribution_id , decode(pll.shipment_type, 'STANDARD', poh.revision_num, por.revision_num) , poh.segment1 , decode(ga_poh.global_agreement_flag,'Y',ga_poh.segment1, decode(pll.shipment_type,'STANDARD',poh2.segment1,poh.segment1)) , por.release_num , pod.code_combination_id , greatest(poh.last_update_date, pol.last_update_date, pll.last_update_date, pod.last_update_date) , decode(poa_edw_variables_pkg.get_global_currency_rate(poh.rate_type, decode(poh.rate_type, 'User', gsob.currency_code, nvl(poh.currency_code, gsob.currency_code)), nvl(pod.rate_date, pod.creation_date), poh.rate)/decode(poh.rate_type, 'User', poh.rate, 1), -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY', 'LOCAL READY') , '_DF:PO:PO_HEADERS:poh' , '_DF:PO:PO_LINES:pol' , '_DF:PO:PO_DISTRIBUTIONS:pod' , '_DF:PO:PO_LINE_LOCATIONS:pll' FROM poa_edw_po_dist_inc inc, po_distributions_all pod, po_line_locations_all pll, po_lines_all pol, po_headers_all poh, po_releases_all por, poa_bis_savings svngs, po_req_distributions_all prq, gl_sets_of_books gsob, rcv_routing_headers rrh, mtl_units_of_measure mtlu, financials_system_params_all fsp, po_vendors pov, po_line_types plt, edw_local_instance eli, po_vendor_sites_all pvs, hr_locations_all hrl1, hr_locations_all hrl2, hr_locations_all hrl3, po_headers_all ga_poh, po_headers_all poh2 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID and pll.line_location_id = pod.line_location_id and pll.approved_flag = 'Y' and pod.po_line_id = pol.po_line_id and pod.po_header_id = poh.po_header_id and pod.po_distribution_id = svngs.distribution_transaction_id (+) and pod.po_release_id = por.po_release_id (+) and pod.set_of_books_id = gsob.set_of_books_id and NVL(pod.org_id, -999) = NVL(fsp.org_id, -999) and pod.req_distribution_id = prq.distribution_id (+) and poh.vendor_id = pov.vendor_id (+) and pll.receiving_routing_id = rrh.routing_header_id (+) and pol.line_type_id = plt.line_type_id and pol.unit_meas_lookup_code = mtlu.unit_of_measure(+) and pvs.vendor_id (+)= poh.vendor_id and pvs.vendor_site_id (+)= poh.vendor_site_id and pll.ship_to_location_id = hrl1.location_id (+) and poh.bill_to_location_id = hrl2.location_id (+) and pod.deliver_to_location_id = hrl3.location_id (+) and pol.from_header_id = ga_poh.po_header_id (+) and nvl(pod.distribution_type,'-99') <> 'AGREEMENT' and pol.contract_id =poh2.po_header_id(+)

View Text - HTML Formatted

SELECT /* ORDERED FULL(INC) USE_NL(POD) USE_NL(PLL) USE_NL(POL) USE_NL(POH) USE_NL(POR) USE_NL(SVNGS) */ POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE) / DECODE(POH.RATE_TYPE
, 'USER'
, POH.RATE
, 1)
, INC.SEQ_ID
, 1
, TO_CHAR(POD.PO_DISTRIBUTION_ID) || '-' || ELI.INSTANCE_CODE
, DECODE(POL.VENDOR_PRODUCT_NUM
, NULL
, 'NA_EDW'
, DECODE(PVS.VENDOR_SITE_CODE
, NULL
, 'NA_EDW'
, POV.VENDOR_NAME || '-' || PVS.VENDOR_SITE_CODE || '-' || POL.VENDOR_PRODUCT_NUM))
, ELI.INSTANCE_CODE
, 'NA_EDW' /* POA_CUSTOMIZATION_PKG.PURCHASE_CLASSIFICATION_CODE( PLL.LINE_LOCATION_ID
, 'PO_LINE_LOCATIONS_ALL') || '-' || 'PURCHASE CLASSIFICATION' || '-' || 'PO' */
, DECODE(NVL(POR.AGENT_ID
, POH.AGENT_ID)
, NULL
, 'NA_EDW'
, NVL(POR.AGENT_ID
, POH.AGENT_ID) || '-' || ELI.INSTANCE_CODE || '-' || 'EMPLOYEE'|| '-' || 'PERS')
, DECODE(POD.DELIVER_TO_PERSON_ID
, NULL
, 'NA_EDW'
, POD.DELIVER_TO_PERSON_ID || '-' || ELI.INSTANCE_CODE || '-' || 'EMPLOYEE'|| '-' || 'PERS')
, DECODE(NVL(POA_EDW_VARIABLES_PKG.APPROVED_BY(POD.PO_HEADER_ID)
, POH.AGENT_ID)
, NULL
, 'NA_EDW'
, NVL(POA_EDW_VARIABLES_PKG.APPROVED_BY(POD.PO_HEADER_ID)
, POH.AGENT_ID) || '-' || ELI.INSTANCE_CODE || '-' || 'EMPLOYEE'|| '-' || 'PERS')
, EDW_ITEMS_PKG.ITEM_ORG_FK(POL.ITEM_ID
, DECODE(POL.ITEM_ID
, NULL
, POD.ORG_ID
, FSP.INVENTORY_ORGANIZATION_ID)
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, ELI.INSTANCE_CODE)
, DECODE(POH.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, POH.VENDOR_SITE_ID || '-' || POH.ORG_ID || '-' || ELI.INSTANCE_CODE || '-SUPPLIER_SITE')
, DECODE(PLL.SHIP_TO_ORGANIZATION_ID
, NULL
, 'NA_EDW'
, PLL.SHIP_TO_ORGANIZATION_ID || '-' || ELI.INSTANCE_CODE)
, DECODE(POD.DESTINATION_ORGANIZATION_ID
, NULL
, 'NA_EDW'
, POD.DESTINATION_ORGANIZATION_ID || '-' || ELI.INSTANCE_CODE)
, DECODE(POD.SET_OF_BOOKS_ID
, NULL
, 'NA_EDW'
, POD.SET_OF_BOOKS_ID || '-' || ELI.INSTANCE_CODE)
, DECODE(PLL.SHIP_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL1.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(PLL.SHIP_TO_LOCATION_ID)
, HRL1.TOWN_OR_CITY || '-' || HRL1.POSTAL_CODE || '-' || HRL1.REGION_2 || '-' || HRL1.COUNTRY))
, DECODE(POH.BILL_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, HRL2.TOWN_OR_CITY || '-' || HRL2.POSTAL_CODE || '-' || HRL2.REGION_2 || '-' || HRL2.COUNTRY)
, DECODE(POD.DELIVER_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL3.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(POD.DELIVER_TO_LOCATION_ID)
, HRL3.TOWN_OR_CITY || '-' || HRL3.POSTAL_CODE || '-' || HRL3.REGION_2 || '-' || HRL3.COUNTRY))
, DECODE(POH.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, PVS.CITY || '-' || PVS.ZIP || '-' || DECODE(PVS.STATE
, NULL
, PVS.PROVINCE
, PVS.STATE) || '-' || PVS.COUNTRY)
, DECODE(POD.TASK_ID
, NULL
, DECODE(POD.PROJECT_ID
, NULL
, 'NA_EDW'
, POD.PROJECT_ID || '-' || ELI.INSTANCE_CODE || '-PJ-PRJ')
, DECODE(POD.TASK_ID
, NULL
, 'NA_EDW'
, POD.TASK_ID || '-' || ELI.INSTANCE_CODE))
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.CREATION_DATE
, POR.CREATION_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.CREATION_DATE
, POR.CREATION_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POL.CREATION_DATE
, PLL.CREATION_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POL.CREATION_DATE
, PLL.CREATION_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PLL.CREATION_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PLL.CREATION_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POD.CREATION_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(POD.CREATION_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(SVNGS.APPROVED_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(SVNGS.APPROVED_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.REVISED_DATE
, POR.REVISED_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.REVISED_DATE
, POR.REVISED_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.ACCEPTANCE_DUE_DATE
, POR.ACCEPTANCE_DUE_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.ACCEPTANCE_DUE_DATE
, POR.ACCEPTANCE_DUE_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.CREATION_DATE
, POR.RELEASE_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.CREATION_DATE
, POR.RELEASE_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE( NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_PLL(POD.CREATION_DATE
, PLL.LINE_LOCATION_ID)
, PLL.APPROVED_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR ( NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_PLL(POD.CREATION_DATE
, PLL.LINE_LOCATION_ID)
, PLL.APPROVED_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PLL.LAST_ACCEPT_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PLL.LAST_ACCEPT_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PLL.NEED_BY_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PLL.NEED_BY_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PLL.PROMISED_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PLL.PROMISED_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.PRINTED_DATE
, POR.PRINTED_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.PRINTED_DATE
, POR.PRINTED_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PRQ.CREATION_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PRQ.CREATION_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POA_EDW_VARIABLES_PKG.GET_REQ_APPROVAL_DATE(POD.REQ_DISTRIBUTION_ID)
, NULL
, 'NA_EDW'
, TO_CHAR(POA_EDW_VARIABLES_PKG.GET_REQ_APPROVAL_DATE(POD.REQ_DISTRIBUTION_ID)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(POD.PO_RELEASE_ID
, NULL
, POA_EDW_VARIABLES_PKG.GET_ACCEPTANCE_DATE (POD.PO_HEADER_ID
, 'P')
, POA_EDW_VARIABLES_PKG.GET_ACCEPTANCE_DATE (POD.PO_RELEASE_ID
, 'R'))
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(POD.PO_RELEASE_ID
, NULL
, POA_EDW_VARIABLES_PKG.GET_ACCEPTANCE_DATE (POD.PO_HEADER_ID
, 'P')
, POA_EDW_VARIABLES_PKG.GET_ACCEPTANCE_DATE (POD.PO_RELEASE_ID
, 'R'))
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POH.TERMS_ID
, NULL
, NULL
, POH.TERMS_ID || '-' || 'AP' || '-' || ELI.INSTANCE_CODE)
, DECODE(PLL.SHIPMENT_TYPE
, NULL
, NULL
, (UPPER(PLL.SHIPMENT_TYPE) || '-' || 'SHIPMENT TYPE' || '-' || 'PO'))
, DECODE(POH.SHIP_VIA_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.SHIP_VIA_LOOKUP_CODE) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG'))
, DECODE(POH.FOB_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.FOB_LOOKUP_CODE) || '-' || 'FOB' || '-' || 'PO'))
, DECODE(POH.FREIGHT_TERMS_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.FREIGHT_TERMS_LOOKUP_CODE) || '-' || 'FREIGHT TERMS' || '-' || 'PO'))
, DECODE(POL.TRANSACTION_REASON_CODE
, NULL
, NULL
, (UPPER(POL.TRANSACTION_REASON_CODE) || '-' || 'TRANSACTION REASON' || '-' || 'PO'))
, DECODE(POL.PRICE_TYPE_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POL.PRICE_TYPE_LOOKUP_CODE) || '-' || 'PRICE TYPE' || '-' || 'PO'))
, DECODE(POL.PRICE_BREAK_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POL.PRICE_BREAK_LOOKUP_CODE) || '-' || 'PRICE BREAK TYPE' || '-' || 'PO'))
, DECODE(PLL.CLOSED_CODE
, NULL
, NULL
, (UPPER(PLL.CLOSED_CODE) || '-' || 'DOCUMENT STATE' || '-' || 'PO'))
, DECODE(POD.DESTINATION_TYPE_CODE
, NULL
, NULL
, (UPPER(POD.DESTINATION_TYPE_CODE) || '-' || 'DESTINATION TYPE' || '-' || 'PO'))
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, DECODE(POL.CONTRACT_ID
, NULL
, ('STANDARD' || '-' || 'AGREEMENT TYPE' || '-' || 'POD')
, ('CONTRACT' || '-' || 'AGREEMENT TYPE' || '-' || 'POD'))
, 'SCHEDULED'
, ('PLANNED' || '-' || 'AGREEMENT TYPE' || '-' || 'POD')
, 'BLANKET'
, ('BLANKET' || '-' || 'AGREEMENT TYPE' || '-' || 'POD'))
, DECODE(RRH.ROUTING_NAME
, NULL
, NULL
, UPPER(RRH.ROUTING_NAME) || '-' || 'RCV ROUTING' || '-' || 'ROU')
, NVL(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.ACCEPTANCE_REQUIRED_FLAG
, POR.ACCEPTANCE_REQUIRED_FLAG)
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.FROZEN_FLAG
, POR.FROZEN_FLAG)
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.USER_HOLD_FLAG
, POR.HOLD_FLAG)
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.CONFIRMING_ORDER_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POL.NEGOTIATED_BY_PREPARER_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.APPROVED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.CANCEL_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.INSPECTION_REQUIRED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.RECEIPT_REQUIRED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POD.ACCRUED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POD.ENCUMBERED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, DECODE(POD.REQ_DISTRIBUTION_ID
, NULL
, 'N'
, 'Y') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.TAXABLE_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.EDI_PROCESSED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, DECODE(POH.PCARD_ID
, NULL
, 'N'
, 'Y') || '-' || 'YES_NO' || '-' || 'FND'
, POA_EDW_VARIABLES_PKG.GET_SUPPLIER_APPROVED( POD.PO_DISTRIBUTION_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, PLL.SHIP_TO_ORGANIZATION_ID
, POL.ITEM_ID
, POL.CATEGORY_ID ) || '-' || 'YES_NO' || '-' || 'FND'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, EDW_UTIL.GET_EDW_BASE_UOM(MTLU.UOM_CODE
, POL.ITEM_ID)
, 'NA_EDW')
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, EDW_UTIL.GET_EDW_UOM(MTLU.UOM_CODE
, POL.ITEM_ID)
, 'NA_EDW')
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, PLT.LINE_TYPE
, DECODE(INC.CHECK_CUT_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(INC.CHECK_CUT_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(INC.INVOICE_RECEIVED_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(INC.INVOICE_RECEIVED_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POA_EDW_VARIABLES_PKG.GET_INVOICE_CREATION_DATE (POD.PO_DISTRIBUTION_ID)
, NULL
, 'NA_EDW'
, TO_CHAR(POA_EDW_VARIABLES_PKG.GET_INVOICE_CREATION_DATE (POD.PO_DISTRIBUTION_ID)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POA_EDW_VARIABLES_PKG.GET_GOODS_RECEIVED_DATE (PLL.LINE_LOCATION_ID)
, NULL
, 'NA_EDW'
, TO_CHAR(POA_EDW_VARIABLES_PKG.GET_GOODS_RECEIVED_DATE (PLL.LINE_LOCATION_ID)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, NULL
, NULL
, NULL
, TO_NUMBER(SVNGS.APPROVED_DATE - DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POD.CREATION_DATE
, POR.CREATION_DATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, TO_NUMBER(INC.CHECK_CUT_DATE - SVNGS.APPROVED_DATE))
, CASE WHEN PLL.CONSIGNED_FLAG='Y' OR POH.CONSIGNED_CONSUMPTION_FLAG='Y' OR POR.CONSIGNED_CONSUMPTION_FLAG='Y' THEN NULL ELSE TO_NUMBER(INC.CHECK_CUT_DATE - POA_EDW_VARIABLES_PKG.GET_GOODS_RECEIVED_DATE (PLL.LINE_LOCATION_ID)) END
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, TO_NUMBER(INC.INVOICE_RECEIVED_DATE - POA_EDW_VARIABLES_PKG.GET_INVOICE_CREATION_DATE (POD.PO_DISTRIBUTION_ID)))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, TO_NUMBER(INC.CHECK_CUT_DATE - INC.INVOICE_RECEIVED_DATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POA_EDW_VARIABLES_PKG.GET_IPV(POD.PO_DISTRIBUTION_ID)/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (POA_EDW_VARIABLES_PKG.GET_IPV(POD.PO_DISTRIBUTION_ID)/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.PURCHASE_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.PURCHASE_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.NON_CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.NON_CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.POT_CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.POT_CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.POTENTIAL_SAVING/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.POTENTIAL_SAVING/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POD.AMOUNT_BILLED)
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POD.AMOUNT_BILLED * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (CASE WHEN PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE (POD.QUANTITY_BILLED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) END))
, CASE WHEN POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE POD.QUANTITY_CANCELLED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE POD.QUANTITY_DELIVERED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID) END
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (CASE WHEN PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE(POD.QUANTITY_ORDERED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) END))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, PLL.PRICE_OVERRIDE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (PLL.PRICE_OVERRIDE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))* POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.LIST_PRICE_PER_UNIT / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (POL.LIST_PRICE_PER_UNIT / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.MARKET_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (POL.MARKET_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.NOT_TO_EXCEED_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (POL.NOT_TO_EXCEED_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.NOTE_TO_VENDOR
, POR.NOTE_TO_VENDOR)
, POL.NOTE_TO_VENDOR
, POH.COMMENTS
, POH.NOTE_TO_RECEIVER
, POL.VENDOR_PRODUCT_NUM
, PLL.CANCEL_REASON
, PLL.CLOSED_REASON
, PLL.SOURCE_SHIPMENT_ID
, POD.PO_DISTRIBUTION_ID
, POD.LINE_LOCATION_ID
, POD.PO_HEADER_ID
, POD.PO_LINE_ID
, POD.PO_RELEASE_ID
, POD.SOURCE_DISTRIBUTION_ID
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.REVISION_NUM
, POR.REVISION_NUM)
, POH.SEGMENT1
, DECODE(GA_POH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, GA_POH.SEGMENT1
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH2.SEGMENT1
, POH.SEGMENT1))
, POR.RELEASE_NUM
, POD.CODE_COMBINATION_ID
, GREATEST(POH.LAST_UPDATE_DATE
, POL.LAST_UPDATE_DATE
, PLL.LAST_UPDATE_DATE
, POD.LAST_UPDATE_DATE)
, DECODE(POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE(POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE)/DECODE(POH.RATE_TYPE
, 'USER'
, POH.RATE
, 1)
, -1
, 'RATE NOT AVAILABLE'
, -2
, 'INVALID CURRENCY'
, 'LOCAL READY')
, '_DF:PO:PO_HEADERS:POH'
, '_DF:PO:PO_LINES:POL'
, '_DF:PO:PO_DISTRIBUTIONS:POD'
, '_DF:PO:PO_LINE_LOCATIONS:PLL'
FROM POA_EDW_PO_DIST_INC INC
, PO_DISTRIBUTIONS_ALL POD
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, POA_BIS_SAVINGS SVNGS
, PO_REQ_DISTRIBUTIONS_ALL PRQ
, GL_SETS_OF_BOOKS GSOB
, RCV_ROUTING_HEADERS RRH
, MTL_UNITS_OF_MEASURE MTLU
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_VENDORS POV
, PO_LINE_TYPES PLT
, EDW_LOCAL_INSTANCE ELI
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, HR_LOCATIONS_ALL HRL3
, PO_HEADERS_ALL GA_POH
, PO_HEADERS_ALL POH2
WHERE INC.PRIMARY_KEY = POD.PO_DISTRIBUTION_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND PLL.APPROVED_FLAG = 'Y'
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.PO_DISTRIBUTION_ID = SVNGS.DISTRIBUTION_TRANSACTION_ID (+)
AND POD.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND POD.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND NVL(POD.ORG_ID
, -999) = NVL(FSP.ORG_ID
, -999)
AND POD.REQ_DISTRIBUTION_ID = PRQ.DISTRIBUTION_ID (+)
AND POH.VENDOR_ID = POV.VENDOR_ID (+)
AND PLL.RECEIVING_ROUTING_ID = RRH.ROUTING_HEADER_ID (+)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POL.UNIT_MEAS_LOOKUP_CODE = MTLU.UNIT_OF_MEASURE(+)
AND PVS.VENDOR_ID (+)= POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID (+)= POH.VENDOR_SITE_ID
AND PLL.SHIP_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND POH.BILL_TO_LOCATION_ID = HRL2.LOCATION_ID (+)
AND POD.DELIVER_TO_LOCATION_ID = HRL3.LOCATION_ID (+)
AND POL.FROM_HEADER_ID = GA_POH.PO_HEADER_ID (+)
AND NVL(POD.DISTRIBUTION_TYPE
, '-99') <> 'AGREEMENT'
AND POL.CONTRACT_ID =POH2.PO_HEADER_ID(+)