MSC_AP_PO_RCV_SUPPLY

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
DELIVERY_PRICE FIRM_PLANNED_TYPE INVENTORY_ITEM_ID ITEM_NAME NEW_DOCK_DATE NEW_ORDER_PLACEMENT_DATE NEW_ORDER_QUANTITY NEW_SCHEDULE_DATE ORDER_NUMBER ORDER_TYPE ORGANIZATION_ID ORGANIZATION_CODE DISPOSITION_ID PO_DISTRIBUTION_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 SUP_ROW_ID PH_ROW_ID PL_ROW_ID PLL_ROW_ID RT_ROW_ID

Name

DELIVERY_PRICE

FIRM_PLANNED_TYPE

INVENTORY_ITEM_ID

ITEM_NAME

NEW_DOCK_DATE

NEW_ORDER_PLACEMENT_DATE

NEW_ORDER_QUANTITY

NEW_SCHEDULE_DATE

ORDER_NUMBER

ORDER_TYPE

ORGANIZATION_ID

ORGANIZATION_CODE

DISPOSITION_ID

PO_DISTRIBUTION_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

SUP_ROW_ID

PH_ROW_ID

PL_ROW_ID

PLL_ROW_ID

RT_ROW_ID

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, sup.ITEM_ID INVENTORY_ITEM_ID, esi.ITEM_NUMBER ITEM_NAME, RT.TRANSACTION_DATE NEW_DOCK_DATE, ph.creation_date NEW_ORDER_PLACEMENT_DATE, sup.TO_ORG_PRIMARY_QUANTITY NEW_ORDER_QUANTITY, sup.EXPECTED_DELIVERY_DATE NEW_SCHEDULE_DATE, PH.SEGMENT1 ORDER_NUMBER, 8 ORDER_TYPE, sup.TO_ORGANIZATION_ID ORGANIZATION_ID, esi.ORGANIZATION_CODE, sup.PO_HEADER_ID DISPOSITION_ID, sup.po_distribution_id, sup.PO_LINE_ID, sup.PO_LINE_LOCATION_ID, PL.LINE_NUM PURCH_LINE_NUM, NVL(SUP.TO_ORG_PRIMARY_QUANTITY ,0) TO_ORG_PRIMARY_QUANTITY, esi.Shrinkage_Rate SHRINKAGE_RATE, sup.ITEM_REVISION REVISION, TO_CHAR(sup.SUPPLY_SOURCE_ID) SCHEDULE_LINE_NUM, (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 , sup.rowid sup_row_id, PH.rowid ph_row_id, PL.rowid pl_row_id, PLL.rowid pll_row_id , RT.rowid rt_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, RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS RT, GL_SETS_OF_BOOKS gsb, AP_SYSTEM_PARAMETERS_ALL aspa, MSC_AP_SUPPLIERS_V supHzp, POZ_SUPPLIER_SITES_ALL_M pvsa WHERE sup.SUPPLY_TYPE_CODE = 'RECEIVING' 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 RSH.SHIPMENT_HEADER_ID = sup.SHIPMENT_HEADER_ID AND RT.TRANSACTION_ID = sup.RCV_TRANSACTION_ID AND PL.LINE_NUM IS NOT NULL AND NVL(PH.req_bu_id,-99) = NVL(ASPA.ORG_ID(+),-99) 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

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,

sup.ITEM_ID INVENTORY_ITEM_ID,

esi.ITEM_NUMBER ITEM_NAME,

RT.TRANSACTION_DATE NEW_DOCK_DATE,

ph.creation_date NEW_ORDER_PLACEMENT_DATE,

sup.TO_ORG_PRIMARY_QUANTITY NEW_ORDER_QUANTITY,

sup.EXPECTED_DELIVERY_DATE NEW_SCHEDULE_DATE,

PH.SEGMENT1 ORDER_NUMBER,

8 ORDER_TYPE,

sup.TO_ORGANIZATION_ID ORGANIZATION_ID,

esi.ORGANIZATION_CODE,

sup.PO_HEADER_ID DISPOSITION_ID,

sup.po_distribution_id,

sup.PO_LINE_ID,

sup.PO_LINE_LOCATION_ID,

PL.LINE_NUM PURCH_LINE_NUM,

NVL(SUP.TO_ORG_PRIMARY_QUANTITY ,0) TO_ORG_PRIMARY_QUANTITY,

esi.Shrinkage_Rate SHRINKAGE_RATE,

sup.ITEM_REVISION REVISION,

TO_CHAR(sup.SUPPLY_SOURCE_ID) SCHEDULE_LINE_NUM,

(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 ,

sup.rowid sup_row_id,

PH.rowid ph_row_id,

PL.rowid pl_row_id,

PLL.rowid pll_row_id ,

RT.rowid rt_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,

RCV_SHIPMENT_HEADERS RSH,

RCV_TRANSACTIONS RT,

GL_SETS_OF_BOOKS gsb,

AP_SYSTEM_PARAMETERS_ALL aspa,

MSC_AP_SUPPLIERS_V supHzp,

POZ_SUPPLIER_SITES_ALL_M pvsa

WHERE sup.SUPPLY_TYPE_CODE = 'RECEIVING'

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 RSH.SHIPMENT_HEADER_ID = sup.SHIPMENT_HEADER_ID

AND RT.TRANSACTION_ID = sup.RCV_TRANSACTION_ID

AND PL.LINE_NUM IS NOT NULL

AND NVL(PH.req_bu_id,-99) = NVL(ASPA.ORG_ID(+),-99)

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