VIEW: APPS.ICX_CHV_SCHEDULE_ITEMS_V

Source

View Text - Preformatted

SELECT CSI.ROWID , CSI.SCHEDULE_ID , CSI.SCHEDULE_ITEM_ID , SCHEDULE_REVISION , CSI.ORGANIZATION_ID , MTP.ORGANIZATION_CODE , CSI.ITEM_ID , MSIK.CONCATENATED_SEGMENTS , MSI.DESCRIPTION , MSI.FULL_LEAD_TIME , MSI.MINIMUM_ORDER_QUANTITY , MSI.MAXIMUM_ORDER_QUANTITY , MSI.FIXED_LOT_MULTIPLIER , CSI.ITEM_PLANNING_METHOD , PAA.ENABLE_AUTHORIZATIONS_FLAG , PAA.SCHEDULER_ID , PPF1.FULL_NAME , MSI.BUYER_ID , PPF2.FULL_NAME , CSI.PO_HEADER_ID , POH.SEGMENT1 , POH.END_DATE , CSI.PO_LINE_ID , POL.LINE_NUM , CSI.REBUILD_FLAG , CSI.ITEM_CONFIRM_STATUS , PLC.DISPLAYED_FIELD , CSI.STARTING_CUM_QUANTITY , CSI.STARTING_AUTH_QUANTITY , CSI.STARTING_CUM_QTY_PRIMARY , CSI.STARTING_AUTH_QTY_PRIMARY , CSI.PURCHASING_UNIT_OF_MEASURE , MSI.PRIMARY_UNIT_OF_MEASURE , CSI.LAST_RECEIPT_TRANSACTION_ID , RCV.TRANSACTION_DATE , RCV.QUANTITY , RSH.RECEIPT_NUM , CSI.ATTRIBUTE_CATEGORY , CSI.ATTRIBUTE1 , CSI.ATTRIBUTE2 , CSI.ATTRIBUTE3 , CSI.ATTRIBUTE4 , CSI.ATTRIBUTE5 , CSI.ATTRIBUTE6 , CSI.ATTRIBUTE7 , CSI.ATTRIBUTE8 , CSI.ATTRIBUTE9 , CSI.ATTRIBUTE10 , CSI.ATTRIBUTE11 , CSI.ATTRIBUTE12 , CSI.ATTRIBUTE13 , CSI.ATTRIBUTE14 , CSI.ATTRIBUTE15 , CSI.LAST_UPDATE_DATE , CSI.LAST_UPDATED_BY , CSI.CREATION_DATE , CSI.CREATED_BY , CSI.LAST_UPDATE_LOGIN , CSI.REQUEST_ID , CSI.PROGRAM_APPLICATION_ID , CSI.PROGRAM_ID , CSI.PROGRAM_UPDATE_DATE , CSI.ITEM_PURGE_STATUS , plc2.displayed_field , pol.vendor_product_num , rcv.shipment_header_id FROM CHV_SCHEDULE_ITEMS CSI ,CHV_SCHEDULE_HEADERS CSH ,PO_ASL_ATTRIBUTES PAA ,MTL_PARAMETERS MTP ,MTL_SYSTEM_ITEMS MSI ,MTL_SYSTEM_ITEMS_KFV MSIK ,PO_LOOKUP_CODES PLC ,po_lookup_codes plc2 ,PO_HEADERS POH ,PO_LINES POL ,RCV_SHIPMENT_HEADERS RSH ,RCV_TRANSACTIONS RCV ,PER_PEOPLE_F PPF1 ,PER_PEOPLE_F PPF2 WHERE CSI.SCHEDULE_ID = CSH.SCHEDULE_ID AND CSI.ORGANIZATION_ID = MTP.ORGANIZATION_ID AND CSI.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND CSI.ORGANIZATION_ID = MSIK.ORGANIZATION_ID AND CSI.ITEM_ID = MSIK.INVENTORY_ITEM_ID AND CSI.ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.BUYER_ID = PPF2.PERSON_ID(+) AND SYSDATE BETWEEN NVL(PPF2.EFFECTIVE_START_DATE, SYSDATE -1) AND NVL(PPF2.EFFECTIVE_END_DATE, SYSDATE + 1) AND PAA.SCHEDULER_ID = PPF1.PERSON_ID(+) AND SYSDATE BETWEEN NVL(PPF1.EFFECTIVE_START_DATE, SYSDATE -1) AND NVL(PPF1.EFFECTIVE_END_DATE, SYSDATE +1) AND CSI.ITEM_CONFIRM_STATUS = PLC.LOOKUP_CODE(+) AND ('SCHEDULE_STATUS') = PLC.LOOKUP_TYPE AND CSI.PO_HEADER_ID = POH.PO_HEADER_ID(+) AND CSI.PO_LINE_ID = POL.PO_LINE_ID(+) AND CSH.VENDOR_ID = PAA.VENDOR_ID AND CSH.VENDOR_SITE_ID = PAA.VENDOR_SITE_ID AND CSI.ITEM_ID = PAA.ITEM_ID AND PAA.USING_ORGANIZATION_ID = CHV_INQ_SV.GET_ASL_ORG(CSI.ORGANIZATION_ID, CSH.VENDOR_ID, CSH.VENDOR_SITE_ID, CSI.ITEM_ID) AND NVL( CSI.ITEM_PURGE_STATUS, 'ACTIVE') != 'PURGED' AND CSI.LAST_RECEIPT_TRANSACTION_ID = RCV.TRANSACTION_ID (+) AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+) AND poh.type_lookup_code = plc2.lookup_code (+) AND plc2.lookup_type (+) = ( 'PO TYPE' )

View Text - HTML Formatted

SELECT CSI.ROWID
, CSI.SCHEDULE_ID
, CSI.SCHEDULE_ITEM_ID
, SCHEDULE_REVISION
, CSI.ORGANIZATION_ID
, MTP.ORGANIZATION_CODE
, CSI.ITEM_ID
, MSIK.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.FULL_LEAD_TIME
, MSI.MINIMUM_ORDER_QUANTITY
, MSI.MAXIMUM_ORDER_QUANTITY
, MSI.FIXED_LOT_MULTIPLIER
, CSI.ITEM_PLANNING_METHOD
, PAA.ENABLE_AUTHORIZATIONS_FLAG
, PAA.SCHEDULER_ID
, PPF1.FULL_NAME
, MSI.BUYER_ID
, PPF2.FULL_NAME
, CSI.PO_HEADER_ID
, POH.SEGMENT1
, POH.END_DATE
, CSI.PO_LINE_ID
, POL.LINE_NUM
, CSI.REBUILD_FLAG
, CSI.ITEM_CONFIRM_STATUS
, PLC.DISPLAYED_FIELD
, CSI.STARTING_CUM_QUANTITY
, CSI.STARTING_AUTH_QUANTITY
, CSI.STARTING_CUM_QTY_PRIMARY
, CSI.STARTING_AUTH_QTY_PRIMARY
, CSI.PURCHASING_UNIT_OF_MEASURE
, MSI.PRIMARY_UNIT_OF_MEASURE
, CSI.LAST_RECEIPT_TRANSACTION_ID
, RCV.TRANSACTION_DATE
, RCV.QUANTITY
, RSH.RECEIPT_NUM
, CSI.ATTRIBUTE_CATEGORY
, CSI.ATTRIBUTE1
, CSI.ATTRIBUTE2
, CSI.ATTRIBUTE3
, CSI.ATTRIBUTE4
, CSI.ATTRIBUTE5
, CSI.ATTRIBUTE6
, CSI.ATTRIBUTE7
, CSI.ATTRIBUTE8
, CSI.ATTRIBUTE9
, CSI.ATTRIBUTE10
, CSI.ATTRIBUTE11
, CSI.ATTRIBUTE12
, CSI.ATTRIBUTE13
, CSI.ATTRIBUTE14
, CSI.ATTRIBUTE15
, CSI.LAST_UPDATE_DATE
, CSI.LAST_UPDATED_BY
, CSI.CREATION_DATE
, CSI.CREATED_BY
, CSI.LAST_UPDATE_LOGIN
, CSI.REQUEST_ID
, CSI.PROGRAM_APPLICATION_ID
, CSI.PROGRAM_ID
, CSI.PROGRAM_UPDATE_DATE
, CSI.ITEM_PURGE_STATUS
, PLC2.DISPLAYED_FIELD
, POL.VENDOR_PRODUCT_NUM
, RCV.SHIPMENT_HEADER_ID
FROM CHV_SCHEDULE_ITEMS CSI
, CHV_SCHEDULE_HEADERS CSH
, PO_ASL_ATTRIBUTES PAA
, MTL_PARAMETERS MTP
, MTL_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS_KFV MSIK
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC2
, PO_HEADERS POH
, PO_LINES POL
, RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS RCV
, PER_PEOPLE_F PPF1
, PER_PEOPLE_F PPF2
WHERE CSI.SCHEDULE_ID = CSH.SCHEDULE_ID
AND CSI.ORGANIZATION_ID = MTP.ORGANIZATION_ID
AND CSI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND CSI.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND CSI.ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND CSI.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.BUYER_ID = PPF2.PERSON_ID(+)
AND SYSDATE BETWEEN NVL(PPF2.EFFECTIVE_START_DATE
, SYSDATE -1)
AND NVL(PPF2.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PAA.SCHEDULER_ID = PPF1.PERSON_ID(+)
AND SYSDATE BETWEEN NVL(PPF1.EFFECTIVE_START_DATE
, SYSDATE -1)
AND NVL(PPF1.EFFECTIVE_END_DATE
, SYSDATE +1)
AND CSI.ITEM_CONFIRM_STATUS = PLC.LOOKUP_CODE(+)
AND ('SCHEDULE_STATUS') = PLC.LOOKUP_TYPE
AND CSI.PO_HEADER_ID = POH.PO_HEADER_ID(+)
AND CSI.PO_LINE_ID = POL.PO_LINE_ID(+)
AND CSH.VENDOR_ID = PAA.VENDOR_ID
AND CSH.VENDOR_SITE_ID = PAA.VENDOR_SITE_ID
AND CSI.ITEM_ID = PAA.ITEM_ID
AND PAA.USING_ORGANIZATION_ID = CHV_INQ_SV.GET_ASL_ORG(CSI.ORGANIZATION_ID
, CSH.VENDOR_ID
, CSH.VENDOR_SITE_ID
, CSI.ITEM_ID)
AND NVL( CSI.ITEM_PURGE_STATUS
, 'ACTIVE') != 'PURGED'
AND CSI.LAST_RECEIPT_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND POH.TYPE_LOOKUP_CODE = PLC2.LOOKUP_CODE (+)
AND PLC2.LOOKUP_TYPE (+) = ( 'PO TYPE' )