VIEW: APPS.MTH_SRC_WORKDAY_SHIFTS_V

Source

View Text - Preformatted

SELECT SHIFT_DATE, SOURCE_ORG_CODE, USER_ATTR1, USER_ATTR2, USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1, USER_MEASURE2, USER_MEASURE3, USER_MEASURE4, USER_MEASURE5, SHIFT_WORKDAY_PK, SYSTEM_FK, FROM_DATE, SHIFT_NUM, TO_DATE, SHIFT_NAME, SHIFT_TYPE, GRAVEYARD_SHIFT, FROM_TIME, TO_TIME FROM (SELECT bsd.shift_date SHIFT_DATE, mtp.organization_code SOURCE_ORG_CODE, CAST(NULL AS VARCHAR2(150)) USER_ATTR1, CAST(NULL AS VARCHAR2(150)) USER_ATTR2, CAST(NULL AS VARCHAR2(150)) USER_ATTR3, CAST(NULL AS VARCHAR2(150)) USER_ATTR4, CAST(NULL AS VARCHAR2(150)) USER_ATTR5, CAST(NULL AS NUMBER) USER_MEASURE1, CAST(NULL AS NUMBER) USER_MEASURE2, CAST(NULL AS NUMBER) USER_MEASURE3, CAST(NULL AS NUMBER) USER_MEASURE4, CAST(NULL AS NUMBER) USER_MEASURE5, CAST(NULL AS VARCHAR2(150)) SHIFT_WORKDAY_PK, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, (bsd.shift_date + bst.from_time / 86400) FROM_DATE, bsd.shift_num SHIFT_NUM, (bsd.shift_date + DECODE(GREATEST(bst.from_time,bst.to_time),bst.from_time,bst.to_time+(24*3600 ),bst.to_time)/86400 ) TO_DATE, bcs.description SHIFT_NAME, flv.meaning SHIFT_TYPE, CAST(NULL AS VARCHAR2(150)) GRAVEYARD_SHIFT, bst.from_time FROM_TIME, bst.to_time TO_TIME FROM bom_calendar_shifts bcs, mtl_parameters mtp, bom_shift_times bst, bom_shift_dates bsd, fnd_lookup_values flv WHERE mtp.process_enabled_flag IN ('1','N') AND bsd.calendar_code = mtp.calendar_code AND bsd.exception_set_id = mtp.calendar_exception_set_id AND bsd.calendar_code = bst.calendar_code AND bsd.shift_num = bst.shift_num AND bsd.seq_num IS NOT NULL AND bcs.calendar_code = bsd.calendar_code AND bsd.shift_num = bcs.shift_num AND flv.lookup_type ='MTH_SHIFT_TYPE' AND flv.language = USERENV('LANG') AND flv.lookup_code = 'PROD-SHIFT')

View Text - HTML Formatted

SELECT SHIFT_DATE
, SOURCE_ORG_CODE
, USER_ATTR1
, USER_ATTR2
, USER_ATTR3
, USER_ATTR4
, USER_ATTR5
, USER_MEASURE1
, USER_MEASURE2
, USER_MEASURE3
, USER_MEASURE4
, USER_MEASURE5
, SHIFT_WORKDAY_PK
, SYSTEM_FK
, FROM_DATE
, SHIFT_NUM
, TO_DATE
, SHIFT_NAME
, SHIFT_TYPE
, GRAVEYARD_SHIFT
, FROM_TIME
, TO_TIME
FROM (SELECT BSD.SHIFT_DATE SHIFT_DATE
, MTP.ORGANIZATION_CODE SOURCE_ORG_CODE
, CAST(NULL AS VARCHAR2(150)) USER_ATTR1
, CAST(NULL AS VARCHAR2(150)) USER_ATTR2
, CAST(NULL AS VARCHAR2(150)) USER_ATTR3
, CAST(NULL AS VARCHAR2(150)) USER_ATTR4
, CAST(NULL AS VARCHAR2(150)) USER_ATTR5
, CAST(NULL AS NUMBER) USER_MEASURE1
, CAST(NULL AS NUMBER) USER_MEASURE2
, CAST(NULL AS NUMBER) USER_MEASURE3
, CAST(NULL AS NUMBER) USER_MEASURE4
, CAST(NULL AS NUMBER) USER_MEASURE5
, CAST(NULL AS VARCHAR2(150)) SHIFT_WORKDAY_PK
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, (BSD.SHIFT_DATE + BST.FROM_TIME / 86400) FROM_DATE
, BSD.SHIFT_NUM SHIFT_NUM
, (BSD.SHIFT_DATE + DECODE(GREATEST(BST.FROM_TIME
, BST.TO_TIME)
, BST.FROM_TIME
, BST.TO_TIME+(24*3600 )
, BST.TO_TIME)/86400 ) TO_DATE
, BCS.DESCRIPTION SHIFT_NAME
, FLV.MEANING SHIFT_TYPE
, CAST(NULL AS VARCHAR2(150)) GRAVEYARD_SHIFT
, BST.FROM_TIME FROM_TIME
, BST.TO_TIME TO_TIME
FROM BOM_CALENDAR_SHIFTS BCS
, MTL_PARAMETERS MTP
, BOM_SHIFT_TIMES BST
, BOM_SHIFT_DATES BSD
, FND_LOOKUP_VALUES FLV
WHERE MTP.PROCESS_ENABLED_FLAG IN ('1'
, 'N')
AND BSD.CALENDAR_CODE = MTP.CALENDAR_CODE
AND BSD.EXCEPTION_SET_ID = MTP.CALENDAR_EXCEPTION_SET_ID
AND BSD.CALENDAR_CODE = BST.CALENDAR_CODE
AND BSD.SHIFT_NUM = BST.SHIFT_NUM
AND BSD.SEQ_NUM IS NOT NULL
AND BCS.CALENDAR_CODE = BSD.CALENDAR_CODE
AND BSD.SHIFT_NUM = BCS.SHIFT_NUM
AND FLV.LOOKUP_TYPE ='MTH_SHIFT_TYPE'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_CODE = 'PROD-SHIFT')