View: ECE_SPSO_ITEMS_V

Product:	EC - e-Commerce Gateway
Description:	This view extracts item informationfor the outbound Planning Schedule (830/DELFOR) transaction.@rep:scope private@rep:product CHV@rep:lifecycle active@rep:displayname Planning Schedule Item View@rep:category BUSINESS_ENTITY CHV_PLANNING_SCH
Implementation/DBA Data:	APPS.ECE_SPSO_ITEMS_V

View Text

SELECT CSI.SCHEDULE_ID SCHEDULE_ID
, CSI.SCHEDULE_ITEM_ID SCHEDULE_ITEM_ID
, CSI.ITEM_ID ITEM_ID
, MIF.ITEM_NUMBER ITEM_NUMBER
, MIF.DESCRIPTION DESCRIPTION
, MUM.UOM_CODE PO_UOM
, POH.SEGMENT1 SUPPLY_AGREEMENT_NUMBER
, POH.TYPE_LOOKUP_CODE SUPPLY_AGREEMENT_TYPE
, POL.LINE_NUM SUPPLY_AGREEMENT_LINE_NUMBER
, COO.ENABLE_CUM_FLAG ENABLE_CUM_FLAG
, CCP.CUM_PERIOD_START_DATE CUM_PERIOD_START_DATE
, HROUTL.NAME ST_NAME
, MTP.ORGANIZATION_CODE ST_ORG_CODE
, HRL.LOCATION_CODE ST_LOCATION_CODE
, HRL.ADDRESS_LINE_1 ST_ADDRESS_LINE1
, HRL.ADDRESS_LINE_2 ST_ADDRESS_LINE2
, HRL.ADDRESS_LINE_3 ST_ADDRESS_LINE3
, HRL.TOWN_OR_CITY ST_CITY
, HRL.REGION_1 ST_COUNTY
, HRL.REGION_2 ST_STATE
, HRL.REGION_3 ST_REGION3
, HRL.COUNTRY ST_COUNTRY
, HRL.POSTAL_CODE ST_POSTAL_CODE
, HRL.TELEPHONE_NUMBER_1 ST_TELEPHONE
, PPF1.LAST_NAME BUYER_LAST_NAME
, PPF1.FIRST_NAME BUYER_FIRST_NAME
, PP.PHONE_NUMBER BUYER_TELEPHONE
, PHC.HAZARD_CLASS HAZARD_CLASS
, PHC.DESCRIPTION HAZARD_CLASS_DESCRIPTION
, PUN.UN_NUMBER UN_NUMBER
, CSI.STARTING_AUTH_QUANTITY STARTING_AUTH_QUANTITY
, CSI.STARTING_CUM_QUANTITY STARTING_CUM_QUANTITY
, CSI.STARTING_AUTH_QTY_PRIMARY STARTING_AUTH_QTY_PRIMARY
, CSI.STARTING_CUM_QTY_PRIMARY STARTING_CUM_QTY_PRIMARY
, CSI.LAST_RECEIPT_TRANSACTION_ID LAST_RECEIPT_TRANSACTION_ID
, CSI.ATTRIBUTE_CATEGORY SCH_ITEM_ATTRIBUTE_CATEGORY
, CSI.ATTRIBUTE1 SCH_ITEM_ATTRIBUTE1
, CSI.ATTRIBUTE2 SCH_ITEM_ATTRIBUTE2
, CSI.ATTRIBUTE3 SCH_ITEM_ATTRIBUTE3
, CSI.ATTRIBUTE4 SCH_ITEM_ATTRIBUTE4
, CSI.ATTRIBUTE5 SCH_ITEM_ATTRIBUTE5
, CSI.ATTRIBUTE6 SCH_ITEM_ATTRIBUTE6
, CSI.ATTRIBUTE7 SCH_ITEM_ATTRIBUTE7
, CSI.ATTRIBUTE8 SCH_ITEM_ATTRIBUTE8
, CSI.ATTRIBUTE9 SCH_ITEM_ATTRIBUTE9
, CSI.ATTRIBUTE10 SCH_ITEM_ATTRIBUTE10
, CSI.ATTRIBUTE11 SCH_ITEM_ATTRIBUTE11
, CSI.ATTRIBUTE12 SCH_ITEM_ATTRIBUTE12
, CSI.ATTRIBUTE13 SCH_ITEM_ATTRIBUTE13
, CSI.ATTRIBUTE14 SCH_ITEM_ATTRIBUTE14
, CSI.ATTRIBUTE15 SCH_ITEM_ATTRIBUTE15
, MIF.ATTRIBUTE_CATEGORY ITEM_ATTRIBUTE_CATEGORY
, MIF.ATTRIBUTE1 ITEM_ATTRIBUTE1
, MIF.ATTRIBUTE2 ITEM_ATTRIBUTE2
, MIF.ATTRIBUTE3 ITEM_ATTRIBUTE3
, MIF.ATTRIBUTE4 ITEM_ATTRIBUTE4
, MIF.ATTRIBUTE5 ITEM_ATTRIBUTE5
, MIF.ATTRIBUTE6 ITEM_ATTRIBUTE6
, MIF.ATTRIBUTE7 ITEM_ATTRIBUTE7
, MIF.ATTRIBUTE8 ITEM_ATTRIBUTE8
, MIF.ATTRIBUTE9 ITEM_ATTRIBUTE9
, MIF.ATTRIBUTE10 ITEM_ATTRIBUTE10
, MIF.ATTRIBUTE11 ITEM_ATTRIBUTE11
, MIF.ATTRIBUTE12 ITEM_ATTRIBUTE12
, MIF.ATTRIBUTE13 ITEM_ATTRIBUTE13
, MIF.ATTRIBUTE14 ITEM_ATTRIBUTE14
, MIF.ATTRIBUTE15 ITEM_ATTRIBUTE15
, HRL.ATTRIBUTE_CATEGORY SHIP_TO_ORG_ATTRIBUTE_CATEGORY
, HRL.ATTRIBUTE1 SHIP_TO_ORG_ATTRIBUTE1
, HRL.ATTRIBUTE2 SHIP_TO_ORG_ATTRIBUTE2
, HRL.ATTRIBUTE3 SHIP_TO_ORG_ATTRIBUTE3
, HRL.ATTRIBUTE4 SHIP_TO_ORG_ATTRIBUTE4
, HRL.ATTRIBUTE5 SHIP_TO_ORG_ATTRIBUTE5
, HRL.ATTRIBUTE6 SHIP_TO_ORG_ATTRIBUTE6
, HRL.ATTRIBUTE7 SHIP_TO_ORG_ATTRIBUTE7
, HRL.ATTRIBUTE8 SHIP_TO_ORG_ATTRIBUTE8
, HRL.ATTRIBUTE9 SHIP_TO_ORG_ATTRIBUTE9
, HRL.ATTRIBUTE10 SHIP_TO_ORG_ATTRIBUTE10
, HRL.ATTRIBUTE11 SHIP_TO_ORG_ATTRIBUTE11
, HRL.ATTRIBUTE12 SHIP_TO_ORG_ATTRIBUTE12
, HRL.ATTRIBUTE13 SHIP_TO_ORG_ATTRIBUTE13
, HRL.ATTRIBUTE14 SHIP_TO_ORG_ATTRIBUTE14
, HRL.ATTRIBUTE15 SHIP_TO_ORG_ATTRIBUTE15
, COO.ATTRIBUTE_CATEGORY ORG_OPT_ATTRIBUTE_CATEGORY
, COO.ATTRIBUTE1 ORG_OPT_ATTRIBUTE1
, COO.ATTRIBUTE2 ORG_OPT_ATTRIBUTE2
, COO.ATTRIBUTE3 ORG_OPT_ATTRIBUTE3
, COO.ATTRIBUTE4 ORG_OPT_ATTRIBUTE4
, COO.ATTRIBUTE5 ORG_OPT_ATTRIBUTE5
, COO.ATTRIBUTE6 ORG_OPT_ATTRIBUTE6
, COO.ATTRIBUTE7 ORG_OPT_ATTRIBUTE7
, COO.ATTRIBUTE8 ORG_OPT_ATTRIBUTE8
, COO.ATTRIBUTE9 ORG_OPT_ATTRIBUTE9
, COO.ATTRIBUTE10 ORG_OPT_ATTRIBUTE10
, COO.ATTRIBUTE11 ORG_OPT_ATTRIBUTE11
, COO.ATTRIBUTE12 ORG_OPT_ATTRIBUTE12
, COO.ATTRIBUTE13 ORG_OPT_ATTRIBUTE13
, COO.ATTRIBUTE14 ORG_OPT_ATTRIBUTE14
, COO.ATTRIBUTE15 ORG_OPT_ATTRIBUTE15
, TO_NUMBER(NULL) RUN_ID
FROM PO_HEADERS POH
, PO_LINES POL
, CHV_ORG_OPTIONS COO
, CHV_CUM_PERIODS CCP
, MTL_PARAMETERS MTP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS_TL HROUTL
, HR_LOCATIONS HRL
, MTL_ITEM_FLEXFIELDS MIF
, MTL_UNITS_OF_MEASURE MUM
, PO_HAZARD_CLASSES_TL PHC
, PO_HAZARD_CLASSES_B PHCB
, PO_UN_NUMBERS_TL PUN
, PO_UN_NUMBERS_B PUNB
, PER_ALL_PEOPLE_F PPF1
, CHV_SCHEDULE_HEADERS CSH
, CHV_SCHEDULE_ITEMS CSI
, PER_PHONES PP
WHERE CSI.SCHEDULE_ID = CSH.SCHEDULE_ID
AND CSI.PO_HEADER_ID = POH.PO_HEADER_ID(+)
AND CSI.PO_LINE_ID = POL.PO_LINE_ID(+)
AND CSI.ORGANIZATION_ID = HOU.ORGANIZATION_ID (+)
AND HOU.ORGANIZATION_ID = HROUTL.ORGANIZATION_ID (+)
AND HROUTL.LANGUAGE (+) = USERENV('LANG')
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS'
, HOU.ORGANIZATION_ID (+) )) = 'TRUE'
AND HOU.LOCATION_ID = HRL.LOCATION_ID(+)
AND CSI.ORGANIZATION_ID = COO.ORGANIZATION_ID
AND CSI.ORGANIZATION_ID = MTP.ORGANIZATION_ID
AND CSI.ORGANIZATION_ID = MIF.ORGANIZATION_ID
AND CSI.ITEM_ID = MIF.ITEM_ID
AND MIF.BUYER_ID = PPF1.PERSON_ID(+)
AND PPF1.EFFECTIVE_START_DATE (+) <= TRUNC(SYSDATE)
AND NVL(PPF1.EFFECTIVE_END_DATE(+)
, SYSDATE) >= TRUNC(SYSDATE)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, PPF1.PERSON_ID (+)
, PPF1.PERSON_TYPE_ID (+)
, PPF1.EMPLOYEE_NUMBER (+)
, PPF1.APPLICANT_NUMBER (+) )) = 'TRUE'
AND CSI.PURCHASING_UNIT_OF_MEASURE = MUM.UNIT_OF_MEASURE(+)
AND MIF.HAZARD_CLASS_ID = PHCB.HAZARD_CLASS_ID(+)
AND PHCB.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND PHC.LANGUAGE (+) = USERENV('LANG')
AND PUNB.UN_NUMBER_ID(+)=MIF.UN_NUMBER_ID
AND PUNB.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+)
AND PUN.LANGUAGE (+) = USERENV('LANG')
AND CSI.ORGANIZATION_ID = CCP.ORGANIZATION_ID(+)
AND ( ( COO.ENABLE_CUM_FLAG = 'N' ) OR ( ( COO.ENABLE_CUM_FLAG = 'Y' ) AND ( ( CCP.CUM_PERIOD_END_DATE IS NULL
AND CSH.SCHEDULE_HORIZON_START >= CCP.CUM_PERIOD_START_DATE ) OR ( CSH.SCHEDULE_HORIZON_START BETWEEN CCP.CUM_PERIOD_START_DATE AND CCP.CUM_PERIOD_END_DATE ) ) ) )
AND EXISTS (SELECT 1
FROM CHV_ITEM_ORDERS CIO
WHERE CIO.SCHEDULE_ID = CSH.SCHEDULE_ID)
AND PPF1.PERSON_ID=PP.PARENT_ID (+)
AND NVL(PP.PARENT_TABLE
, 'PER_ALL_PEOPLE_F')='PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+)='W1'
AND TRUNC(SYSDATE) >= PP.DATE_FROM(+)
AND TRUNC(SYSDATE) <= NVL(PP.DATE_TO(+)
, SYSDATE)

Columns