VIEW: APPS.ECE_SPSO_HEADERS_V

Source

View Text - Preformatted

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 ) ) ) )

View Text - HTML Formatted

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 ) ) ) )