MSC_AP_PO_PO_SUPPLY

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ACCEPTANCE_REQUIRED_FLAG DELIVERY_PRICE FIRM_PLANNED_TYPE ORGANIZATION_ID ORGANIZATION_CODE INVENTORY_ITEM_ID ITEM_NAME POSTPROCESSING_LEAD_TIME SHRINKAGE_RATE NEED_BY_DATE NEW_DOCK_DATE NEW_SCHEDULE_DATE NEW_ORDER_PLACEMENT_DATE PROMISED_DATE PROMISED_SHIP_DATE REQUESTED_SHIP_DATE ORDER_TYPE ORDER_NUMBER PURCH_LINE_NUM SCHEDULE_LINE_NUM NEW_ORDER_QUANTITY PO_DISTRIBUTION_ID PO_LINE_ID DISPOSITION_ID PO_LINE_LOCATION_ID TO_ORG_PRIMARY_QUANTITY REVISION SUBINVENTORY_CODE SUPPLIER_ID SUPPLIER_NAME SUPPLIER_SITE_ID SUPPLIER_SITE_CODE BUYER_MANAGED_TRANSPORT_FLAG DESTINATION_TYPE_CODE DROP_SHIP_DEST_TYPE DROP_SHIP_CUST_ID DROP_SHIP_CUST_NAME DROP_SHIP_CUST_SITE_ID DROP_SHIP_CUST_SITE_CODE CARRIER_ID CARRIER_NAME MODE_OF_TRANSPORT SERVICE_LEVEL ORDER_STATUS SUP_ROW_ID PH_ROW_ID PL_ROW_ID PLL_ROW_ID

Name

ACCEPTANCE_REQUIRED_FLAG

DELIVERY_PRICE

FIRM_PLANNED_TYPE

ORGANIZATION_ID

ORGANIZATION_CODE

INVENTORY_ITEM_ID

ITEM_NAME

POSTPROCESSING_LEAD_TIME

SHRINKAGE_RATE

NEED_BY_DATE

NEW_DOCK_DATE

NEW_SCHEDULE_DATE

NEW_ORDER_PLACEMENT_DATE

PROMISED_DATE

PROMISED_SHIP_DATE

REQUESTED_SHIP_DATE

ORDER_TYPE

ORDER_NUMBER

PURCH_LINE_NUM

SCHEDULE_LINE_NUM

NEW_ORDER_QUANTITY

PO_DISTRIBUTION_ID

PO_LINE_ID

DISPOSITION_ID

PO_LINE_LOCATION_ID

TO_ORG_PRIMARY_QUANTITY

REVISION

SUBINVENTORY_CODE

SUPPLIER_ID

SUPPLIER_NAME

SUPPLIER_SITE_ID

SUPPLIER_SITE_CODE

BUYER_MANAGED_TRANSPORT_FLAG

DESTINATION_TYPE_CODE

DROP_SHIP_DEST_TYPE

DROP_SHIP_CUST_ID

DROP_SHIP_CUST_NAME

DROP_SHIP_CUST_SITE_ID

DROP_SHIP_CUST_SITE_CODE

CARRIER_ID

CARRIER_NAME

MODE_OF_TRANSPORT

SERVICE_LEVEL

ORDER_STATUS

SUP_ROW_ID

PH_ROW_ID

PL_ROW_ID

PLL_ROW_ID

Query

SQL_Statement
SELECT ph.acceptance_required_flag, pll.price_override * (CASE WHEN ph.currency_code is not null AND gsb.currency_code is not null AND ph.currency_code <> gsb.currency_code THEN greatest(1, GL_CURRENCY_API.get_rate_sql(ph.currency_code, gsb.currency_code, ph.RATE_DATE, ph.RATE_TYPE)) ELSE 1 END) DELIVERY_PRICE , DECODE(DECODE(DECODE(SIGN(NVL(ph.firm_date,sysdate+1)-sysdate),1, NVL(ph.firm_status_lookup_code,'N'), 'Y'), 'N', DECODE(SIGN(NVL(pll.firm_date,sysdate+1)-sysdate), 1, NVL(pll.firm_status_lookup_code,'N'),'Y'), 'Y' ), 'Y',1, 2) FIRM_PLANNED_TYPE, sup.to_organization_id organization_id, esi.organization_code, sup.item_id inventory_item_id, esi.item_number item_name, esi.postprocessing_lead_time, esi.Shrinkage_Rate, pll.need_by_date, sup.need_by_date new_dock_date, NVL(sup.expected_delivery_date, sup.need_by_date) new_schedule_date, ph.creation_date new_order_placement_date, pll.promised_date, pll.promised_ship_date, pll.requested_ship_date, 1 order_type, ph.segment1 order_number, pl.line_num purch_line_num, pll.shipment_num schedule_line_num, sup.to_org_primary_quantity new_order_quantity, sup.po_distribution_id, pl.po_line_id, PH.po_header_id disposition_id, sup.po_line_location_id, NVL(sup.to_org_primary_quantity,0) to_org_primary_quantity, pl.item_revision revision, ( SELECT dist.destination_subinventory FROM po_distributions_all dist WHERE dist.po_header_id = sup.po_header_id AND dist.po_line_id = sup.po_line_id AND dist.line_location_id = sup.po_line_location_id AND ROWNUM = 1 ) subinventory_code, ph.vendor_id supplier_id, supHZP.partner_name supplier_name, ph.vendor_site_id supplier_site_id, pvsa.vendor_site_code supplier_site_code, ph.buyer_managed_transport_flag, sup.destination_type_code, DECODE(UPPER(NVL(sup.DESTINATION_TYPE_CODE,'-1')),'DROP SHIP','1','INVENTORY','2','-1') drop_ship_dest_type, pll.ship_to_cust_id drop_ship_cust_id, custHZP.party_number drop_ship_cust_name, hps.party_site_id drop_ship_cust_site_id, hps.party_site_number drop_ship_cust_site_code, pll.carrier_id, HZP.Partner_name carrier_name, FNLMOD.meaning mode_of_transport, FNLSRL.meaning service_level, ( CASE WHEN NVL(pll.quantity_shipped, 0) > 0 OR NVL(pll.quantity_received, 0) > 0 THEN 1 WHEN NVL(pll.amount_billed, 0) > 0 THEN 2 ELSE NULL END ) order_status, sup.rowid sup_row_id, ph.rowid ph_row_id, pl.rowid pl_row_id, pll.rowid pll_row_id FROM INV_SUPPLY SUP, MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi, PO_HEADERS_ALL PH, PO_LINES_ALL PL, PO_LINE_LOCATIONS_ALL PLL, GL_SETS_OF_BOOKS gsb, AP_SYSTEM_PARAMETERS_ALL aspa, MSC_AP_CARRIERS_V HZP, MSC_AP_SUPPLIERS_V supHzp, POZ_SUPPLIER_SITES_ALL_M pvsa, HZ_PARTIES custHZP, HZ_LOCATIONS LOC, HZ_PARTY_SITES hps, FND_LOOKUP_VALUES_VL FNLMOD, FND_LOOKUP_VALUES_VL FNLSRL WHERE sup.supply_type_code = 'PO' AND sup.destination_type_code IN ('INVENTORY','DROP SHIP') AND esi.Organization_ID = sup.To_Organization_ID AND esi.Inventory_Item_ID = sup.Item_ID AND nvl(esi.OUTSIDE_PROCESS_SERVICE_FLAG, 2) <> 1 AND ph.po_header_id = sup.po_header_id AND pl.po_line_id = sup.po_line_id AND pll.line_location_id = sup.po_line_location_id AND sup.PO_LINE_ID IS NOT NULL AND pl.LINE_NUM IS NOT NULL AND aspa.org_id(+) = ph.req_bu_id AND gsb.set_of_books_id(+) = aspa.set_of_books_id AND HZP.SR_TP_ID(+) = PLL.CARRIER_ID AND supHZP.sr_tp_id(+) = ph.vendor_id AND pvsa.vendor_site_id(+) = ph.vendor_site_id AND CUSTHZP.PARTY_ID(+) = PLL.SHIP_TO_CUST_ID AND LOC.LOCATION_ID(+) = PLL.SHIP_TO_CUST_LOCATION_ID AND hps.LOCATION_ID(+) = PLL.SHIP_TO_CUST_LOCATION_ID AND hps.PARTY_ID(+) = PLL.SHIP_TO_CUST_ID AND hps.START_DATE_ACTIVE(+) <= SYSDATE AND NVL(hps.END_DATE_ACTIVE(+),SYSDATE+1) > SYSDATE AND FNLMOD.LOOKUP_CODE(+) = pll.MODE_OF_TRANSPORT AND FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT' AND FNLMOD.VIEW_APPLICATION_ID(+) = 0 AND FNLMOD.SET_ID(+) = 0 AND FNLSRL.LOOKUP_CODE(+) = pll.SERVICE_LEVEL AND FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS' AND FNLSRL.VIEW_APPLICATION_ID(+) = 0 AND FNLSRL.SET_ID(+) = 0 AND NVL(sup.EXPECTED_DELIVERY_DATE,sup.need_by_date) IS NOT NULL AND sup.PO_LINE_LOCATION_ID IS NOT NULL

SQL_Statement

SELECT

ph.acceptance_required_flag,

pll.price_override * (CASE

WHEN ph.currency_code is not null

AND gsb.currency_code is not null

AND ph.currency_code <> gsb.currency_code

THEN greatest(1, GL_CURRENCY_API.get_rate_sql(ph.currency_code, gsb.currency_code, ph.RATE_DATE, ph.RATE_TYPE))

ELSE 1

END) DELIVERY_PRICE ,

DECODE(DECODE(DECODE(SIGN(NVL(ph.firm_date,sysdate+1)-sysdate),1, NVL(ph.firm_status_lookup_code,'N'), 'Y'), 'N',

DECODE(SIGN(NVL(pll.firm_date,sysdate+1)-sysdate), 1, NVL(pll.firm_status_lookup_code,'N'),'Y'), 'Y'

), 'Y',1, 2) FIRM_PLANNED_TYPE,

sup.to_organization_id organization_id,

esi.organization_code,

sup.item_id inventory_item_id,

esi.item_number item_name,

esi.postprocessing_lead_time,

esi.Shrinkage_Rate,

pll.need_by_date,

sup.need_by_date new_dock_date,

NVL(sup.expected_delivery_date, sup.need_by_date) new_schedule_date,

ph.creation_date new_order_placement_date,

pll.promised_date,

pll.promised_ship_date,

pll.requested_ship_date,

1 order_type,

ph.segment1 order_number,

pl.line_num purch_line_num,

pll.shipment_num schedule_line_num,

sup.to_org_primary_quantity new_order_quantity,

sup.po_distribution_id,

pl.po_line_id,

PH.po_header_id disposition_id,

sup.po_line_location_id,

NVL(sup.to_org_primary_quantity,0) to_org_primary_quantity,

pl.item_revision revision,

(

SELECT dist.destination_subinventory

FROM po_distributions_all dist

WHERE dist.po_header_id = sup.po_header_id

AND dist.po_line_id = sup.po_line_id

AND dist.line_location_id = sup.po_line_location_id

AND ROWNUM = 1

) subinventory_code,

ph.vendor_id supplier_id,

supHZP.partner_name supplier_name,

ph.vendor_site_id supplier_site_id,

pvsa.vendor_site_code supplier_site_code,

ph.buyer_managed_transport_flag,

sup.destination_type_code,

DECODE(UPPER(NVL(sup.DESTINATION_TYPE_CODE,'-1')),'DROP SHIP','1','INVENTORY','2','-1') drop_ship_dest_type,

pll.ship_to_cust_id drop_ship_cust_id,

custHZP.party_number drop_ship_cust_name,

hps.party_site_id drop_ship_cust_site_id,

hps.party_site_number drop_ship_cust_site_code,

pll.carrier_id,

HZP.Partner_name carrier_name,

FNLMOD.meaning mode_of_transport,

FNLSRL.meaning service_level,

(

CASE

WHEN NVL(pll.quantity_shipped, 0) > 0 OR NVL(pll.quantity_received, 0) > 0 THEN 1

WHEN NVL(pll.amount_billed, 0) > 0 THEN 2

ELSE NULL

END

) order_status,

sup.rowid sup_row_id,

ph.rowid ph_row_id,

pl.rowid pl_row_id,

pll.rowid pll_row_id

FROM

INV_SUPPLY SUP,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi,

PO_HEADERS_ALL PH,

PO_LINES_ALL PL,

PO_LINE_LOCATIONS_ALL PLL,

GL_SETS_OF_BOOKS gsb,

AP_SYSTEM_PARAMETERS_ALL aspa,

MSC_AP_CARRIERS_V HZP,

MSC_AP_SUPPLIERS_V supHzp,

POZ_SUPPLIER_SITES_ALL_M pvsa,

HZ_PARTIES custHZP,

HZ_LOCATIONS LOC,

HZ_PARTY_SITES hps,

FND_LOOKUP_VALUES_VL FNLMOD,

FND_LOOKUP_VALUES_VL FNLSRL

WHERE sup.supply_type_code = 'PO'

AND sup.destination_type_code IN ('INVENTORY','DROP SHIP')

AND esi.Organization_ID = sup.To_Organization_ID

AND esi.Inventory_Item_ID = sup.Item_ID

AND nvl(esi.OUTSIDE_PROCESS_SERVICE_FLAG, 2) <> 1

AND ph.po_header_id = sup.po_header_id

AND pl.po_line_id = sup.po_line_id

AND pll.line_location_id = sup.po_line_location_id

AND sup.PO_LINE_ID IS NOT NULL

AND pl.LINE_NUM IS NOT NULL

AND aspa.org_id(+) = ph.req_bu_id

AND gsb.set_of_books_id(+) = aspa.set_of_books_id

AND HZP.SR_TP_ID(+) = PLL.CARRIER_ID

AND supHZP.sr_tp_id(+) = ph.vendor_id

AND pvsa.vendor_site_id(+) = ph.vendor_site_id

AND CUSTHZP.PARTY_ID(+) = PLL.SHIP_TO_CUST_ID

AND LOC.LOCATION_ID(+) = PLL.SHIP_TO_CUST_LOCATION_ID

AND hps.LOCATION_ID(+) = PLL.SHIP_TO_CUST_LOCATION_ID

AND hps.PARTY_ID(+) = PLL.SHIP_TO_CUST_ID

AND hps.START_DATE_ACTIVE(+) <= SYSDATE

AND NVL(hps.END_DATE_ACTIVE(+),SYSDATE+1) > SYSDATE

AND FNLMOD.LOOKUP_CODE(+) = pll.MODE_OF_TRANSPORT

AND FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT'

AND FNLMOD.VIEW_APPLICATION_ID(+) = 0

AND FNLMOD.SET_ID(+) = 0

AND FNLSRL.LOOKUP_CODE(+) = pll.SERVICE_LEVEL

AND FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS'

AND FNLSRL.VIEW_APPLICATION_ID(+) = 0

AND FNLSRL.SET_ID(+) = 0

AND NVL(sup.EXPECTED_DELIVERY_DATE,sup.need_by_date) IS NOT NULL

AND sup.PO_LINE_LOCATION_ID IS NOT NULL