VIEW: APPS.POABV_EDW_SUPPLIER_PERFORM_FCV

Source

View Text - Preformatted

SELECT inc.seq_id , 1 , to_char(pll.line_location_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)) , EDW_LOOKUP_PKG.lookup_code_fk('PO', 'PURCHASE CLASSIFICATION', POA_CUSTOMIZATION_PKG.purchase_classification_code( pll.line_location_id, 'PO_LINE_LOCATIONS_ALL')) , DECODE(POA_EDW_SUPPERF.get_invoice_date(pll.line_location_id), NULL, 'NA_EDW', to_char(POA_EDW_SUPPERF.get_invoice_date(pll.line_location_id),'DD-MM-YYYY') || '-'|| gsob.period_set_name || '-'|| gsob.accounted_period_type || '-'|| eli.instance_code || '-CD') , decode(pol.order_type_lookup_code,'QUANTITY', edw_util.get_edw_base_uom(muom.uom_code, pol.item_id), 'NA_EDW') , decode(pol.order_type_lookup_code,'QUANTITY', edw_util.get_edw_uom(muom.uom_code, pol.item_id), 'NA_EDW') , eli.instance_code , EDW_TRD_PARTNER_PKG.supplier_site_fk (poh.vendor_site_id, poh.org_id, eli.instance_code) , EDW_ORGANIZATION_PKG.int_organization_fk (pll.ship_to_organization_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.vendor_site_id, NULL, 'NA_EDW', pvs.city || '-' || pvs.zip || '-' || decode(pvs.state, null, pvs.province, pvs.state) || '-' || pvs.country) , EDW_ITEMS_PKG.item_org_fk(pol.item_id, DECODE(pol.item_id, NULL, pll.org_id, fsp.inventory_organization_id), pol.item_description, pol.category_id, eli.instance_code) , EDW_HR_PERSON_PKG.regular_employee_fk( NVL(por.agent_id, poh.agent_id), eli.instance_code) , DECODE(NVL(pll.promised_date, NVL(pll.need_by_date, pll.creation_date)), NULL, 'NA_EDW', to_char(NVL(pll.promised_date, NVL(pll.need_by_date, pll.creation_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(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.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(poa_edw_supperf.get_first_receipt_date(pll.line_location_id), NULL, 'NA_EDW', to_char(poa_edw_supperf.get_first_receipt_date(pll.line_location_id),'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(NVL(POA_OLTP_GENERIC_PKG.get_approved_date_pll(pll.creation_date, pll.line_location_id), pll.approved_date), NULL, 'NA_EDW', to_char(NVL(POA_OLTP_GENERIC_PKG.get_approved_date_pll(pll.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(poh.terms_id, NULL, NULL, poh.terms_id || '-' || 'AP' || '-' || eli.instance_code) , NVL(poh.currency_code, gsob.currency_code) , DECODE(pol.price_type_lookup_code, NULL, NULL, upper(pol.price_type_lookup_code) || '-' || 'PRICE TYPE' || '-' || 'PO') , DECODE(pll.closed_code, NULL, NULL, pll.closed_code || '-' || 'DOCUMENT STATE' || '-' || 'PO') , plt.line_type , POA_EDW_SUPPERF.get_ipv(pll.line_location_id) , POA_EDW_SUPPERF.get_ipv(pll.line_location_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(poh.rate_date, pll.creation_date), poh.rate) , decode(pll.consigned_flag,'Y',null, pll.price_override / poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code,pol.item_id)) , decode(pll.consigned_flag,'Y',null, pll.price_override / poa_edw_variables_pkg.get_uom_conv_rate(muom.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(poh.rate_date, pll.creation_date), poh.rate)) , decode(pll.consigned_flag,'Y',null, pol.market_price / poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code,pol.item_id)) , decode(pll.consigned_flag,'Y',null, pol.market_price / poa_edw_variables_pkg.get_uom_conv_rate(muom.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(poh.rate_date, pll.creation_date), poh.rate)) , decode(pll.consigned_flag,'Y',null, pol.list_price_per_unit / poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code,pol.item_id)) , decode(pll.consigned_flag,'Y',null, pol.list_price_per_unit / poa_edw_variables_pkg.get_uom_conv_rate(muom.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(poh.rate_date, pll.creation_date), poh.rate)) , to_number(NULL) , decode(pll.consigned_flag,'Y',null,poa_customization_pkg.get_target_price(pll.line_location_id) / poa_edw_variables_pkg.get_uom_conv_rate(muom.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(poh.rate_date, pll.creation_date), poh.rate)) , decode(pll.consigned_flag,'Y',null, pll.price_override * (pll.quantity - pll.quantity_cancelled) * 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(poh.rate_date, pll.creation_date), poh.rate)) , decode(pll.consigned_flag,'Y',null,pll.price_override * (pll.quantity - pll.quantity_cancelled)) , decode(pll.consigned_flag ,'Y' ,NULL ,decode(pol.order_type_lookup_code ,'AMOUNT' ,null ,pll.quantity * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id))) , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE pll.quantity_cancelled * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code,pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE poa_edw_supperf.get_qty_shipped(pll.line_location_id ,pol.unit_meas_lookup_code) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE poa_edw_supperf.get_qty_received('ALL' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE poa_edw_supperf.get_rcv_txn_qty(pll.line_location_id,'REJECT') * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE poa_edw_supperf.get_rcv_txn_qty(pll.line_location_id,'ACCEPT') * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE poa_edw_supperf.get_qty_delivered(pll.line_location_id) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE poa_edw_supperf.get_num_receipts('ALL' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' then 0 ELSE poa_edw_supperf.get_num_receipts('EARLY' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' then 0 ELSE poa_edw_supperf.get_num_receipts('LATE' , pll.line_location_id , NVL(pll.promised_date, pll.need_by_date) , pll.days_early_receipt_allowed , pll.days_late_receipt_allowed) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' THEN 0 ELSE poa_edw_supperf.get_num_receipts('EARLYWINDOW' , pll.line_location_id , NVL(pll.promised_date, pll.need_by_date) , pll.days_early_receipt_allowed , pll.days_late_receipt_allowed) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' THEN 0 ELSE poa_edw_supperf.get_num_receipts('LATEWINDOW' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' THEN poa_edw_supperf.get_num_receipts('ONDATE' ,pll.line_location_id ,NULL ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) ELSE poa_edw_supperf.get_num_receipts('ONDATE' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE poa_edw_supperf.get_num_receipts('SUBSTITUTE' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' then 0 ELSE poa_edw_supperf.get_qty_received('EARLY' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' then 0 ELSE poa_edw_supperf.get_qty_received('LATE' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' then 0 ELSE poa_edw_supperf.get_qty_pastdue(pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' then 0 ELSE poa_edw_supperf.get_qty_received('EARLYWINDOW' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' then 0 ELSE poa_edw_supperf.get_qty_received('LATEWINDOW' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL WHEN poh.shipping_control='BUYER' THEN poa_edw_supperf.get_qty_received('ONDATE' ,pll.line_location_id ,NULL ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) ELSE poa_edw_supperf.get_qty_received('ONDATE' ,pll.line_location_id ,NVL(pll.promised_date,pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , CASE WHEN por.consigned_consumption_flag = 'Y' OR poh.consigned_consumption_flag = 'Y' OR pol.order_type_lookup_code = 'AMOUNT' THEN NULL ELSE poa_edw_supperf.get_qty_received('SUBSTITUTE' ,pll.line_location_id ,NVL(pll.promised_date, pll.need_by_date) ,pll.days_early_receipt_allowed ,pll.days_late_receipt_allowed) * poa_edw_variables_pkg.get_uom_conv_rate(muom.uom_code, pol.item_id) END , case when por.consigned_consumption_flag='Y' or poh.consigned_consumption_flag='Y' then null else pll.days_early_receipt_allowed end , case when por.consigned_consumption_flag='Y' or poh.consigned_consumption_flag='Y' then null else pll.days_late_receipt_allowed end , case when por.consigned_consumption_flag='Y' or poh.consigned_consumption_flag='Y' then null else pll.qty_rcv_tolerance end , decode(pll.consigned_flag,'Y',null, POA_EDW_SUPPERF.get_days_to_invoice( pll.line_location_id)) , pll.receive_close_tolerance , GREATEST(gsob.last_update_date, plt.last_update_date, NVL(pov.last_update_date,TO_DATE('02-01-1000','dd-mm-yyyy')), muom.last_update_date, fsp.last_update_date, pll.last_update_date, pol.last_update_date, poh.last_update_date, NVL(poa_edw_supperf.get_last_rcv_trx_date( pll.line_location_id), TO_DATE('02-01-1000', 'dd-mm-yyyy'))) , poh.segment1 , decode(ga.global_agreement_flag,'Y',ga.segment1, DECODE(pll.shipment_type, 'STANDARD', poh2.segment1, poh.segment1)) , por.release_num , NULL , NULL , NULL , NULL , decode(edw_currency.get_rate( decode(poh.rate_type, 'User', gsob.currency_code, nvl(poh.currency_code, gsob.currency_code)), NVL(poh.rate_date, pll.creation_date), NULL), -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY', 'LOCAL READY') collection_status , '_DF:PO:PO_HEADERS:poh' , '_DF:PO:PO_LINES:pol' , '_DF:PO:PO_LINE_LOCATIONS:pll' FROM poa_edw_sup_perf_inc inc, edw_local_instance eli, gl_sets_of_books gsob, po_line_types plt, po_vendors pov, mtl_units_of_measure muom, financials_system_params_all fsp, po_releases_all por, po_lines_all pol, po_headers_all poh, po_line_locations_all pll, po_vendor_sites_all pvs, hr_locations_all hrl1, po_headers_all ga, po_headers_all poh2, po_doc_style_headers style WHERE inc.primary_key = pll.line_location_id AND pll.approved_flag = 'Y' AND pll.shipment_type in ('BLANKET', 'SCHEDULED', 'STANDARD') AND pll.po_header_id = poh.po_header_id AND pll.po_line_id = pol.po_line_id AND pol.from_header_id = ga.po_header_id (+) AND pol.line_type_id = plt.line_type_id AND pol.unit_meas_lookup_code = muom.unit_of_measure AND por.po_release_id (+)= pll.po_release_id AND NVL(fsp.org_id, -999) = NVL(pll.org_id, -999) AND gsob.set_of_books_id = fsp.set_of_books_id AND pov.vendor_id (+)= poh.vendor_id 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 PLL.MATCHING_BASIS = 'QUANTITY' and poh.style_id = style.style_id and nvl(style.progress_payment_flag,'N') = 'N' AND POL.CONTRACT_ID =POH2.PO_HEADER_ID(+)

View Text - HTML Formatted

SELECT INC.SEQ_ID
, 1
, TO_CHAR(PLL.LINE_LOCATION_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))
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('PO'
, 'PURCHASE CLASSIFICATION'
, POA_CUSTOMIZATION_PKG.PURCHASE_CLASSIFICATION_CODE( PLL.LINE_LOCATION_ID
, 'PO_LINE_LOCATIONS_ALL'))
, DECODE(POA_EDW_SUPPERF.GET_INVOICE_DATE(PLL.LINE_LOCATION_ID)
, NULL
, 'NA_EDW'
, TO_CHAR(POA_EDW_SUPPERF.GET_INVOICE_DATE(PLL.LINE_LOCATION_ID)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, EDW_UTIL.GET_EDW_BASE_UOM(MUOM.UOM_CODE
, POL.ITEM_ID)
, 'NA_EDW')
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, EDW_UTIL.GET_EDW_UOM(MUOM.UOM_CODE
, POL.ITEM_ID)
, 'NA_EDW')
, ELI.INSTANCE_CODE
, EDW_TRD_PARTNER_PKG.SUPPLIER_SITE_FK (POH.VENDOR_SITE_ID
, POH.ORG_ID
, ELI.INSTANCE_CODE)
, EDW_ORGANIZATION_PKG.INT_ORGANIZATION_FK (PLL.SHIP_TO_ORGANIZATION_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.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, PVS.CITY || '-' || PVS.ZIP || '-' || DECODE(PVS.STATE
, NULL
, PVS.PROVINCE
, PVS.STATE) || '-' || PVS.COUNTRY)
, EDW_ITEMS_PKG.ITEM_ORG_FK(POL.ITEM_ID
, DECODE(POL.ITEM_ID
, NULL
, PLL.ORG_ID
, FSP.INVENTORY_ORGANIZATION_ID)
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, ELI.INSTANCE_CODE)
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK( NVL(POR.AGENT_ID
, POH.AGENT_ID)
, ELI.INSTANCE_CODE)
, DECODE(NVL(PLL.PROMISED_DATE
, NVL(PLL.NEED_BY_DATE
, PLL.CREATION_DATE))
, NULL
, 'NA_EDW'
, TO_CHAR(NVL(PLL.PROMISED_DATE
, NVL(PLL.NEED_BY_DATE
, PLL.CREATION_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(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.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(POA_EDW_SUPPERF.GET_FIRST_RECEIPT_DATE(PLL.LINE_LOCATION_ID)
, NULL
, 'NA_EDW'
, TO_CHAR(POA_EDW_SUPPERF.GET_FIRST_RECEIPT_DATE(PLL.LINE_LOCATION_ID)
, '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(NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_PLL(PLL.CREATION_DATE
, PLL.LINE_LOCATION_ID)
, PLL.APPROVED_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_PLL(PLL.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(POH.TERMS_ID
, NULL
, NULL
, POH.TERMS_ID || '-' || 'AP' || '-' || ELI.INSTANCE_CODE)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, DECODE(POL.PRICE_TYPE_LOOKUP_CODE
, NULL
, NULL
, UPPER(POL.PRICE_TYPE_LOOKUP_CODE) || '-' || 'PRICE TYPE' || '-' || 'PO')
, DECODE(PLL.CLOSED_CODE
, NULL
, NULL
, PLL.CLOSED_CODE || '-' || 'DOCUMENT STATE' || '-' || 'PO')
, PLT.LINE_TYPE
, POA_EDW_SUPPERF.GET_IPV(PLL.LINE_LOCATION_ID)
, POA_EDW_SUPPERF.GET_IPV(PLL.LINE_LOCATION_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(POH.RATE_DATE
, PLL.CREATION_DATE)
, POH.RATE)
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, PLL.PRICE_OVERRIDE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, PLL.PRICE_OVERRIDE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.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(POH.RATE_DATE
, PLL.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.MARKET_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.MARKET_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.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(POH.RATE_DATE
, PLL.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.LIST_PRICE_PER_UNIT / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.LIST_PRICE_PER_UNIT / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.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(POH.RATE_DATE
, PLL.CREATION_DATE)
, POH.RATE))
, TO_NUMBER(NULL)
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POA_CUSTOMIZATION_PKG.GET_TARGET_PRICE(PLL.LINE_LOCATION_ID) / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.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(POH.RATE_DATE
, PLL.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, PLL.PRICE_OVERRIDE * (PLL.QUANTITY - PLL.QUANTITY_CANCELLED) * 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(POH.RATE_DATE
, PLL.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, PLL.PRICE_OVERRIDE * (PLL.QUANTITY - PLL.QUANTITY_CANCELLED))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, PLL.QUANTITY * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID)))
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE PLL.QUANTITY_CANCELLED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE POA_EDW_SUPPERF.GET_QTY_SHIPPED(PLL.LINE_LOCATION_ID
, POL.UNIT_MEAS_LOOKUP_CODE) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE POA_EDW_SUPPERF.GET_QTY_RECEIVED('ALL'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE POA_EDW_SUPPERF.GET_RCV_TXN_QTY(PLL.LINE_LOCATION_ID
, 'REJECT') * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE POA_EDW_SUPPERF.GET_RCV_TXN_QTY(PLL.LINE_LOCATION_ID
, 'ACCEPT') * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE POA_EDW_SUPPERF.GET_QTY_DELIVERED(PLL.LINE_LOCATION_ID) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE POA_EDW_SUPPERF.GET_NUM_RECEIPTS('ALL'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_NUM_RECEIPTS('EARLY'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_NUM_RECEIPTS('LATE'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_NUM_RECEIPTS('EARLYWINDOW'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_NUM_RECEIPTS('LATEWINDOW'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN POA_EDW_SUPPERF.GET_NUM_RECEIPTS('ONDATE'
, PLL.LINE_LOCATION_ID
, NULL
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) ELSE POA_EDW_SUPPERF.GET_NUM_RECEIPTS('ONDATE'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE POA_EDW_SUPPERF.GET_NUM_RECEIPTS('SUBSTITUTE'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_QTY_RECEIVED('EARLY'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_QTY_RECEIVED('LATE'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_QTY_PASTDUE(PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_QTY_RECEIVED('EARLYWINDOW'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN 0 ELSE POA_EDW_SUPPERF.GET_QTY_RECEIVED('LATEWINDOW'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL WHEN POH.SHIPPING_CONTROL='BUYER' THEN POA_EDW_SUPPERF.GET_QTY_RECEIVED('ONDATE'
, PLL.LINE_LOCATION_ID
, NULL
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) ELSE POA_EDW_SUPPERF.GET_QTY_RECEIVED('ONDATE'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POL.ORDER_TYPE_LOOKUP_CODE = 'AMOUNT' THEN NULL ELSE POA_EDW_SUPPERF.GET_QTY_RECEIVED('SUBSTITUTE'
, PLL.LINE_LOCATION_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED) * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MUOM.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG='Y' OR POH.CONSIGNED_CONSUMPTION_FLAG='Y' THEN NULL ELSE PLL.DAYS_EARLY_RECEIPT_ALLOWED END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG='Y' OR POH.CONSIGNED_CONSUMPTION_FLAG='Y' THEN NULL ELSE PLL.DAYS_LATE_RECEIPT_ALLOWED END
, CASE WHEN POR.CONSIGNED_CONSUMPTION_FLAG='Y' OR POH.CONSIGNED_CONSUMPTION_FLAG='Y' THEN NULL ELSE PLL.QTY_RCV_TOLERANCE END
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POA_EDW_SUPPERF.GET_DAYS_TO_INVOICE( PLL.LINE_LOCATION_ID))
, PLL.RECEIVE_CLOSE_TOLERANCE
, GREATEST(GSOB.LAST_UPDATE_DATE
, PLT.LAST_UPDATE_DATE
, NVL(POV.LAST_UPDATE_DATE
, TO_DATE('02-01-1000'
, 'DD-MM-YYYY'))
, MUOM.LAST_UPDATE_DATE
, FSP.LAST_UPDATE_DATE
, PLL.LAST_UPDATE_DATE
, POL.LAST_UPDATE_DATE
, POH.LAST_UPDATE_DATE
, NVL(POA_EDW_SUPPERF.GET_LAST_RCV_TRX_DATE( PLL.LINE_LOCATION_ID)
, TO_DATE('02-01-1000'
, 'DD-MM-YYYY')))
, POH.SEGMENT1
, DECODE(GA.GLOBAL_AGREEMENT_FLAG
, 'Y'
, GA.SEGMENT1
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH2.SEGMENT1
, POH.SEGMENT1))
, POR.RELEASE_NUM
, NULL
, NULL
, NULL
, NULL
, DECODE(EDW_CURRENCY.GET_RATE( DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POH.RATE_DATE
, PLL.CREATION_DATE)
, NULL)
, -1
, 'RATE NOT AVAILABLE'
, -2
, 'INVALID CURRENCY'
, 'LOCAL READY') COLLECTION_STATUS
, '_DF:PO:PO_HEADERS:POH'
, '_DF:PO:PO_LINES:POL'
, '_DF:PO:PO_LINE_LOCATIONS:PLL'
FROM POA_EDW_SUP_PERF_INC INC
, EDW_LOCAL_INSTANCE ELI
, GL_SETS_OF_BOOKS GSOB
, PO_LINE_TYPES PLT
, PO_VENDORS POV
, MTL_UNITS_OF_MEASURE MUOM
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_RELEASES_ALL POR
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_ALL PLL
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL HRL1
, PO_HEADERS_ALL GA
, PO_HEADERS_ALL POH2
, PO_DOC_STYLE_HEADERS STYLE
WHERE INC.PRIMARY_KEY = PLL.LINE_LOCATION_ID
AND PLL.APPROVED_FLAG = 'Y'
AND PLL.SHIPMENT_TYPE IN ('BLANKET'
, 'SCHEDULED'
, 'STANDARD')
AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.FROM_HEADER_ID = GA.PO_HEADER_ID (+)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POL.UNIT_MEAS_LOOKUP_CODE = MUOM.UNIT_OF_MEASURE
AND POR.PO_RELEASE_ID (+)= PLL.PO_RELEASE_ID
AND NVL(FSP.ORG_ID
, -999) = NVL(PLL.ORG_ID
, -999)
AND GSOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND POV.VENDOR_ID (+)= POH.VENDOR_ID
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 PLL.MATCHING_BASIS = 'QUANTITY'
AND POH.STYLE_ID = STYLE.STYLE_ID
AND NVL(STYLE.PROGRESS_PAYMENT_FLAG
, 'N') = 'N'
AND POL.CONTRACT_ID =POH2.PO_HEADER_ID(+)