View: ECE_SPSO_HEADERS_V

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

View Text

SELECT NVL(CSH.COMMUNICATION_CODE
, 'NONE') COMMUNICATION_METHOD
, ETPD.TEST_FLAG TEST_FLAG
, 'SPSO' DOCUMENT_ID
, 'SPS' DOCUMENT_TYPE
, CSH.SCHEDULE_NUM DOCUMENT_CODE
, ETPD.TRANSLATOR_CODE TP_TRANSLATOR_CODE
, POVS.ECE_TP_LOCATION_CODE TP_LOCATION_CODE_EXT
, ETPH.TP_DESCRIPTION TP_DESCRIPTION
, ETPH.TP_REFERENCE_EXT1 TP_REFERENCE_EXT1
, ETPH.TP_REFERENCE_EXT2 TP_REFERENCE_EXT2
, ETPH.ATTRIBUTE_CATEGORY TPH_ATTRIBUTE_CATEGORY
, ETPH.ATTRIBUTE1 TPH_ATTRIBUTE1
, ETPH.ATTRIBUTE2 TPH_ATTRIBUTE2
, ETPH.ATTRIBUTE3 TPH_ATTRIBUTE3
, ETPH.ATTRIBUTE4 TPH_ATTRIBUTE4
, ETPH.ATTRIBUTE5 TPH_ATTRIBUTE5
, ETPH.ATTRIBUTE6 TPH_ATTRIBUTE6
, ETPH.ATTRIBUTE7 TPH_ATTRIBUTE7
, ETPH.ATTRIBUTE8 TPH_ATTRIBUTE8
, ETPH.ATTRIBUTE9 TPH_ATTRIBUTE9
, ETPH.ATTRIBUTE10 TPH_ATTRIBUTE10
, ETPH.ATTRIBUTE11 TPH_ATTRIBUTE11
, ETPH.ATTRIBUTE12 TPH_ATTRIBUTE12
, ETPH.ATTRIBUTE13 TPH_ATTRIBUTE13
, ETPH.ATTRIBUTE14 TPH_ATTRIBUTE14
, ETPH.ATTRIBUTE15 TPH_ATTRIBUTE15
, ETPD.ATTRIBUTE_CATEGORY TPD_ATTRIBUTE_CATEGORY
, ETPD.ATTRIBUTE1 TPD_ATTRIBUTE1
, ETPD.ATTRIBUTE2 TPD_ATTRIBUTE2
, ETPD.ATTRIBUTE3 TPD_ATTRIBUTE3
, ETPD.ATTRIBUTE4 TPD_ATTRIBUTE4
, ETPD.ATTRIBUTE5 TPD_ATTRIBUTE5
, CSH.SCHEDULE_HORIZON_START SCHEDULE_HORIZON_START
, CSH.SCHEDULE_HORIZON_END SCHEDULE_HORIZON_END
, CSH.CREATION_DATE CREATION_DATE
, CSH.ORGANIZATION_ID ORGANIZATION_ID
, CSH.VENDOR_ID VENDOR_ID
, CSH.VENDOR_SITE_ID VENDOR_SITE_ID
, CSH.SCHEDULE_ID SCHEDULE_ID
, CSH.SCHEDULE_TYPE SCHEDULE_TYPE
, CSH.SCHEDULE_SUBTYPE SCHEDULE_SUBTYPE
, CSH.SCHEDULE_STATUS SCHEDULE_STATUS
, RTRIM(CSH.SCHEDULE_NUM)|| '-' ||TO_CHAR(NVL(CSH.SCHEDULE_REVISION
, 0)) SCHEDULE_NUMBER_REVISION
, POV.VENDOR_NAME SF_NAME
, POV.SEGMENT1 SF_NUMBER
, POVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, POVS.ADDRESS_LINE1 SF_ADDRESS_LINE1
, POVS.ADDRESS_LINE2 SF_ADDRESS_LINE2
, POVS.ADDRESS_LINE3 SF_ADDRESS_LINE3
, POVS.CITY SF_CITY
, POVS.STATE SF_STATE
, POVS.ZIP SF_ZIP
, POVS.COUNTRY SF_COUNTRY
, POVS.PROVINCE SF_PROVINCE
, POVS.AREA_CODE SF_AREA_CODE
, POVS.PHONE SF_PHONE
, HROUTL.NAME ST_NAME
, MTLP.ORGANIZATION_CODE ST_ORG_CODE
, DECODE(CSH.SCHEDULE_TYPE
, 'SHIP_SCHEDULE'
, 'N'
, DECODE(NVL(MTLP.ORGANIZATION_CODE
, '-1')
, '-1'
, 'Y'
, 'N')) MULTIPLE_SHIP_TO_ORG_FLAG
, 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_2 ST_STATE
, HRL.REGION_1 ST_COUNTY
, HRL.POSTAL_CODE ST_POSTAL_CODE
, HRL.COUNTRY ST_COUNTRY
, HRL.REGION_3 ST_REGION3
, HRL.TELEPHONE_NUMBER_1 ST_TELEPHONE
, COO.ENABLE_CUM_FLAG ENABLE_CUM_FLAG
, CCP.CUM_PERIOD_START_DATE CUM_PERIOD_START_DATE
, CSH.ATTRIBUTE_CATEGORY SCH_HEADER_ATTRIBUTE_CATEGORY
, CSH.ATTRIBUTE1 SCH_HEADER_ATTRIBUTE1
, CSH.ATTRIBUTE2 SCH_HEADER_ATTRIBUTE2
, CSH.ATTRIBUTE3 SCH_HEADER_ATTRIBUTE3
, CSH.ATTRIBUTE4 SCH_HEADER_ATTRIBUTE4
, CSH.ATTRIBUTE5 SCH_HEADER_ATTRIBUTE5
, CSH.ATTRIBUTE6 SCH_HEADER_ATTRIBUTE6
, CSH.ATTRIBUTE7 SCH_HEADER_ATTRIBUTE7
, CSH.ATTRIBUTE8 SCH_HEADER_ATTRIBUTE8
, CSH.ATTRIBUTE9 SCH_HEADER_ATTRIBUTE9
, CSH.ATTRIBUTE10 SCH_HEADER_ATTRIBUTE10
, CSH.ATTRIBUTE11 SCH_HEADER_ATTRIBUTE11
, CSH.ATTRIBUTE12 SCH_HEADER_ATTRIBUTE12
, CSH.ATTRIBUTE13 SCH_HEADER_ATTRIBUTE13
, CSH.ATTRIBUTE14 SCH_HEADER_ATTRIBUTE14
, CSH.ATTRIBUTE15 SCH_HEADER_ATTRIBUTE15
, POV.ATTRIBUTE_CATEGORY VENDOR_ATTRIBUTE_CATEGORY
, POV.ATTRIBUTE1 VENDOR_ATTRIBUTE1
, POV.ATTRIBUTE2 VENDOR_ATTRIBUTE2
, POV.ATTRIBUTE3 VENDOR_ATTRIBUTE3
, POV.ATTRIBUTE4 VENDOR_ATTRIBUTE4
, POV.ATTRIBUTE5 VENDOR_ATTRIBUTE5
, POV.ATTRIBUTE6 VENDOR_ATTRIBUTE6
, POV.ATTRIBUTE7 VENDOR_ATTRIBUTE7
, POV.ATTRIBUTE8 VENDOR_ATTRIBUTE8
, POV.ATTRIBUTE9 VENDOR_ATTRIBUTE9
, POV.ATTRIBUTE10 VENDOR_ATTRIBUTE10
, POV.ATTRIBUTE11 VENDOR_ATTRIBUTE11
, POV.ATTRIBUTE12 VENDOR_ATTRIBUTE12
, POV.ATTRIBUTE13 VENDOR_ATTRIBUTE13
, POV.ATTRIBUTE14 VENDOR_ATTRIBUTE14
, POV.ATTRIBUTE15 VENDOR_ATTRIBUTE15
, POVS.ATTRIBUTE_CATEGORY VNS_ATTRIBUTE_CATEGORY
, POVS.ATTRIBUTE1 VNS_ATTRIBUTE1
, POVS.ATTRIBUTE2 VNS_ATTRIBUTE2
, POVS.ATTRIBUTE3 VNS_ATTRIBUTE3
, POVS.ATTRIBUTE4 VNS_ATTRIBUTE4
, POVS.ATTRIBUTE5 VNS_ATTRIBUTE5
, POVS.ATTRIBUTE6 VNS_ATTRIBUTE6
, POVS.ATTRIBUTE7 VNS_ATTRIBUTE7
, POVS.ATTRIBUTE8 VNS_ATTRIBUTE8
, POVS.ATTRIBUTE9 VNS_ATTRIBUTE9
, POVS.ATTRIBUTE10 VNS_ATTRIBUTE10
, POVS.ATTRIBUTE11 VNS_ATTRIBUTE11
, POVS.ATTRIBUTE12 VNS_ATTRIBUTE12
, POVS.ATTRIBUTE13 VNS_ATTRIBUTE13
, POVS.ATTRIBUTE14 VNS_ATTRIBUTE14
, POVS.ATTRIBUTE15 VNS_ATTRIBUTE15
, HRL.ATTRIBUTE_CATEGORY SHIP_ORG_ATTRIBUTE_CATEGORY
, HRL.ATTRIBUTE1 SHIP_ORG_ATTRIBUTE1
, HRL.ATTRIBUTE2 SHIP_ORG_ATTRIBUTE2
, HRL.ATTRIBUTE3 SHIP_ORG_ATTRIBUTE3
, HRL.ATTRIBUTE4 SHIP_ORG_ATTRIBUTE4
, HRL.ATTRIBUTE5 SHIP_ORG_ATTRIBUTE5
, HRL.ATTRIBUTE6 SHIP_ORG_ATTRIBUTE6
, HRL.ATTRIBUTE7 SHIP_ORG_ATTRIBUTE7
, HRL.ATTRIBUTE8 SHIP_ORG_ATTRIBUTE8
, HRL.ATTRIBUTE9 SHIP_ORG_ATTRIBUTE9
, HRL.ATTRIBUTE10 SHIP_ORG_ATTRIBUTE10
, HRL.ATTRIBUTE11 SHIP_ORG_ATTRIBUTE11
, HRL.ATTRIBUTE12 SHIP_ORG_ATTRIBUTE12
, HRL.ATTRIBUTE13 SHIP_ORG_ATTRIBUTE13
, HRL.ATTRIBUTE14 SHIP_ORG_ATTRIBUTE14
, HRL.ATTRIBUTE15 SHIP_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
, SYSDATE TRANSACTION_DATE
, TO_NUMBER(NULL) RUN_ID
, ETPD.DOCUMENT_STANDARD DOCUMENT_STANDARD
, ETPD.MAP_ID MAP_ID
, CSH.BATCH_ID BATCH_ID
FROM HR_ALL_ORGANIZATION_UNITS HROU
, HR_ALL_ORGANIZATION_UNITS_TL HROUTL
, MTL_PARAMETERS MTLP
, HR_LOCATIONS HRL
, CHV_ORG_OPTIONS COO
, CHV_CUM_PERIODS CCP
, ECE_TP_DETAILS ETPD
, ECE_TP_HEADERS ETPH
, PO_VENDOR_SITES POVS
, PO_VENDORS POV
, CHV_SCHEDULE_HEADERS CSH
WHERE CSH.SCHEDULE_STATUS = 'CONFIRMED'
AND DECODE(CSH.SCHEDULE_TYPE
, 'SHIP_SCHEDULE'
, 'SSSO'
, 'SPSO') = 'SPSO'
AND CSH.VENDOR_ID = POV.VENDOR_ID
AND CSH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND CSH.ORGANIZATION_ID = COO.ORGANIZATION_ID (+)
AND CSH.ORGANIZATION_ID = CCP.ORGANIZATION_ID (+)
AND CSH.ORGANIZATION_ID = HROU.ORGANIZATION_ID (+)
AND HROU.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'
, HROU.ORGANIZATION_ID (+) )) = 'TRUE'
AND CSH.ORGANIZATION_ID = MTLP.ORGANIZATION_ID (+)
AND EXISTS (SELECT 1
FROM CHV_ITEM_ORDERS CIO
WHERE CIO.SCHEDULE_ID = CSH.SCHEDULE_ID)
AND HROU.LOCATION_ID = HRL.LOCATION_ID (+)
AND POVS.VENDOR_ID = POV.VENDOR_ID
AND POVS.TP_HEADER_ID = ETPH.TP_HEADER_ID
AND POVS.TP_HEADER_ID = ETPD.TP_HEADER_ID
AND ETPH.TP_HEADER_ID = ETPD.TP_HEADER_ID
AND ETPD.DOCUMENT_ID = 'SPSO'
AND ETPD.EDI_FLAG = 'Y'
AND NVL(CSH.COMMUNICATION_CODE
, 'NONE') IN ('BOTH'
, 'EDI')
AND ( ( NVL(COO.ENABLE_CUM_FLAG
, 'N') = '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 ) ) ) )

Columns