MSC_AP_PO_SHIP_SUPPLY

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
DELIVERY_PRICE FIRM_PLANNED_TYPE INVENTORY_ITEM_ID ITEM_NAME NEW_ORDER_QUANTITY NEW_SCHEDULE_DATE ORDER_NUMBER ORDER_TYPE ORGANIZATION_ID ORGANIZATION_CODE PO_DISTRIBUTION_ID DISPOSITION_ID PO_LINE_ID PO_LINE_LOCATION_ID PURCH_LINE_NUM TO_ORG_PRIMARY_QUANTITY SHRINKAGE_RATE REVISION SCHEDULE_LINE_NUM SUBINVENTORY_CODE SUPPLIER_ID SUPPLIER_NAME SUPPLIER_SITE_ID SUPPLIER_SITE_CODE NEW_DOCK_DATE MS_ROW_ID RSH_ROW_ID RSL_ROW_ID PLL_ROW_ID PH_ROW_ID PL_ROW_ID SHIPMENT_HEADER_NUM

Name

DELIVERY_PRICE

FIRM_PLANNED_TYPE

INVENTORY_ITEM_ID

ITEM_NAME

NEW_ORDER_QUANTITY

NEW_SCHEDULE_DATE

ORDER_NUMBER

ORDER_TYPE

ORGANIZATION_ID

ORGANIZATION_CODE

PO_DISTRIBUTION_ID

DISPOSITION_ID

PO_LINE_ID

PO_LINE_LOCATION_ID

PURCH_LINE_NUM

TO_ORG_PRIMARY_QUANTITY

SHRINKAGE_RATE

REVISION

SCHEDULE_LINE_NUM

SUBINVENTORY_CODE

SUPPLIER_ID

SUPPLIER_NAME

SUPPLIER_SITE_ID

SUPPLIER_SITE_CODE

NEW_DOCK_DATE

MS_ROW_ID

RSH_ROW_ID

RSL_ROW_ID

PLL_ROW_ID

PH_ROW_ID

PL_ROW_ID

SHIPMENT_HEADER_NUM

Query

SQL_Statement
SELECT 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, 1 FIRM_PLANNED_TYPE, MS.ITEM_ID INVENTORY_ITEM_ID, msi.ITEM_NUMBER ITEM_NAME, MS.TO_ORG_PRIMARY_QUANTITY NEW_ORDER_QUANTITY, nvl(nvl(MS.EXPECTED_DELIVERY_DATE,MS.RECEIPT_DATE), RSH.SHIPPED_DATE) NEW_SCHEDULE_DATE, decode( PH.SEGMENT1, null, RSH.SHIPMENT_NUM, PH.SEGMENT1) ORDER_NUMBER, 11 ORDER_TYPE, MS.TO_ORGANIZATION_ID ORGANIZATION_ID, msi.ORGANIZATION_CODE, MS.po_distribution_id, PH.po_header_id disposition_id, MS.PO_LINE_ID , MS.PO_LINE_LOCATION_ID, RSL.LINE_NUM PURCH_LINE_NUM, NVL(MS.TO_ORG_PRIMARY_QUANTITY ,0) TO_ORG_PRIMARY_QUANTITY, msi.Shrinkage_Rate SHRINKAGE_RATE, MS.ITEM_REVISION REVISION, TO_CHAR(MS.SUPPLY_SOURCE_ID) SCHEDULE_LINE_NUM, NVL(MS.to_subinventory,(select dist.destination_subinventory from po_distributions_all dist where dist.po_header_id = MS.po_header_id and dist.po_line_id = MS.po_line_id and dist.line_location_id = MS.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 , MS.RECEIPT_DATE NEW_DOCK_DATE, MS.rowid ms_row_id, RSH.rowid rsh_row_id, RSL.rowid rsl_row_id, PLL.rowid pll_row_id, PH.rowid ph_row_id, pl.rowid pl_row_id, rsh.shipment_num SHIPMENT_HEADER_NUM FROM MSC_PLANNED_EGP_SYSTEM_ITEMS_V msi, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, GL_SETS_OF_BOOKS gsb, AP_SYSTEM_PARAMETERS_ALL aspa, PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL pl, INV_SUPPLY MS, PO_HEADERS_ALL PH, MSC_AP_SUPPLIERS_V supHzp, POZ_SUPPLIER_SITES_ALL_M pvsa WHERE MS.SUPPLY_TYPE_CODE = 'SHIPMENT' and msi.Organization_ID= ms.To_Organization_ID and msi.Inventory_Item_ID= ms.Item_ID AND nvl(msi.OUTSIDE_PROCESS_SERVICE_FLAG, 2) <> 1 and pll.line_location_id(+) = ms.po_line_location_id and pl.po_line_id(+) = ms.po_line_id AND MS.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND MS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND MS.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND RSL.LINE_NUM IS NOT NULL AND MS.SHIPMENT_LINE_ID IS NOT NULL AND ph.req_bu_id = aspa.org_id (+) AND aspa.set_of_books_id = gsb.set_of_books_id(+) AND supHZP.sr_tp_id(+)=ph.vendor_id AND pvsa.vendor_site_id(+) = ph.vendor_site_id AND MS.Transfer_Order_Line_Id IS NULL AND MS.PO_LINE_LOCATION_ID IS NOT NULL

SQL_Statement

SELECT

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,

1 FIRM_PLANNED_TYPE,

MS.ITEM_ID INVENTORY_ITEM_ID,

msi.ITEM_NUMBER ITEM_NAME,

MS.TO_ORG_PRIMARY_QUANTITY NEW_ORDER_QUANTITY,

nvl(nvl(MS.EXPECTED_DELIVERY_DATE,MS.RECEIPT_DATE), RSH.SHIPPED_DATE) NEW_SCHEDULE_DATE,

decode( PH.SEGMENT1, null, RSH.SHIPMENT_NUM, PH.SEGMENT1) ORDER_NUMBER,

11 ORDER_TYPE,

MS.TO_ORGANIZATION_ID ORGANIZATION_ID,

msi.ORGANIZATION_CODE,

MS.po_distribution_id,

PH.po_header_id disposition_id,

MS.PO_LINE_ID ,

MS.PO_LINE_LOCATION_ID,

RSL.LINE_NUM PURCH_LINE_NUM,

NVL(MS.TO_ORG_PRIMARY_QUANTITY ,0) TO_ORG_PRIMARY_QUANTITY,

msi.Shrinkage_Rate SHRINKAGE_RATE,

MS.ITEM_REVISION REVISION,

TO_CHAR(MS.SUPPLY_SOURCE_ID) SCHEDULE_LINE_NUM,

NVL(MS.to_subinventory,(select dist.destination_subinventory

from po_distributions_all dist

where dist.po_header_id = MS.po_header_id

and dist.po_line_id = MS.po_line_id

and dist.line_location_id = MS.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 ,

MS.RECEIPT_DATE NEW_DOCK_DATE,

MS.rowid ms_row_id,

RSH.rowid rsh_row_id,

RSL.rowid rsl_row_id,

PLL.rowid pll_row_id,

PH.rowid ph_row_id,

pl.rowid pl_row_id,

rsh.shipment_num SHIPMENT_HEADER_NUM

FROM

MSC_PLANNED_EGP_SYSTEM_ITEMS_V msi,

RCV_SHIPMENT_HEADERS RSH,

RCV_SHIPMENT_LINES RSL,

GL_SETS_OF_BOOKS gsb,

AP_SYSTEM_PARAMETERS_ALL aspa,

PO_LINE_LOCATIONS_ALL PLL,

PO_LINES_ALL pl,

INV_SUPPLY MS,

PO_HEADERS_ALL PH,

MSC_AP_SUPPLIERS_V supHzp,

POZ_SUPPLIER_SITES_ALL_M pvsa

WHERE MS.SUPPLY_TYPE_CODE = 'SHIPMENT'

and msi.Organization_ID= ms.To_Organization_ID

and msi.Inventory_Item_ID= ms.Item_ID

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

and pll.line_location_id(+) = ms.po_line_location_id

and pl.po_line_id(+) = ms.po_line_id

AND MS.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

AND MS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID

AND MS.PO_HEADER_ID = PH.PO_HEADER_ID(+)

AND RSL.LINE_NUM IS NOT NULL

AND MS.SHIPMENT_LINE_ID IS NOT NULL

AND ph.req_bu_id = aspa.org_id (+)

AND aspa.set_of_books_id = gsb.set_of_books_id(+)

AND supHZP.sr_tp_id(+)=ph.vendor_id

AND pvsa.vendor_site_id(+) = ph.vendor_site_id

AND MS.Transfer_Order_Line_Id IS NULL

AND MS.PO_LINE_LOCATION_ID IS NOT NULL