VIEW: APPS.JTF_BRM_3D_TASK_V

Source

View Text - Preformatted

SELECT 'PRESENT' RECORD_STATUS , 'JTF_BRM_3D_TASK_V' OBJECT_TYPE , TASK_ID OBJECT_ID , TASK_ID , CREATED_BY , CREATION_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN , TASK_NUMBER , TASK_NAME , TASK_TYPE_ID , TASK_TYPE , TASK_STATUS_ID , TASK_STATUS , TASK_PRIORITY_ID , TASK_PRIORITY , OWNER_ID , OWNER , OWNER_TYPE_CODE , OWNER_TYPE , ASSIGNED_BY_ID , ASSIGNED_BY_NAME , CUSTOMER_ID , CUSTOMER_NAME , CUSTOMER_NUMBER , CUST_ACCOUNT_ID , CUST_ACCOUNT_NUMBER , ADDRESS_ID , ADDRESS1 , ADDRESS2 , ADDRESS3 , PLANNED_START_DATE , PLANNED_END_DATE , SCHEDULED_START_DATE , SCHEDULED_END_DATE , ACTUAL_START_DATE , ACTUAL_END_DATE , TIMEZONE_ID , GLOBAL_TIMEZONE_NAME , GMT_DEVIATION_HOURS , SOURCE_OBJECT_TYPE_CODE , SOURCE_OBJECT_TYPE , SOURCE_OBJECT_ID , SOURCE_OBJECT_NAME , DURATION , DURATION_UOM , PLANNED_EFFORT , PLANNED_EFFORT_UOM , ACTUAL_EFFORT , ACTUAL_EFFORT_UOM , PERCENTAGE_COMPLETE , PRIVATE_FLAG , PUBLISH_FLAG , DESCRIPTION , PARENT_TASK_ID , PARENT_TASK_NUMBER FROM jtf_tasks_v UNION ALL SELECT 'HISTORIC' RECORD_STATUS , 'JTF_BRM_3D_TASK_V' OBJECT_TYPE , JTL.TASK_ID OBJECT_ID , JTL.TASK_ID TASK_ID , JTL.CREATED_BY CREATED_BY , JTL.LAST_UPDATE_DATE CREATION_DATE , JTL.LAST_UPDATED_BY LAST_UPDATE_BY , JTL.LAST_UPDATE_DATE LAST_UPDATE_DATE , JTL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , JTAB.TASK_NUMBER TASK_NUMBER , JTA.TASK_NAME TASK_NAME , JTL.OLD_TASK_TYPE_ID TASK_TYPE_ID , JTT.NAME TASK_TYPE , JTL.OLD_TASK_STATUS_ID TASK_STATUS_ID , JTS.NAME TASK_STATUS , JTL.OLD_TASK_PRIORITY_ID TASK_PRIORITY_ID , JTP.NAME TASK_PRIORITY , JTL.OLD_OWNER_ID OWNER_ID , JTF_TASK_UTL.GET_OWNER( JTL.OLD_OWNER_TYPE_CODE , JTL.OLD_OWNER_ID ) OWNER , JTL.OLD_OWNER_TYPE_CODE OWNER_TYPE_CODE , JTO2.NAME OWNER_TYPE , JTL.OLD_ASSIGNED_BY_ID ASSIGNED_BY_ID , FND.USER_NAME ASSIGNED_BY_NAME , JTL.OLD_CUSTOMER_ID CUSTOMER_ID , HZP.PARTY_NAME CUSTOMER_NAME , HZP.PARTY_NUMBER CUSTOMER_NUMBER , JTL.OLD_CUST_ACCOUNT_ID CUSTOMER_ACCOUNT_ID , HZCA.ACCOUNT_NUMBER CUST_ACCOUNT_NUMBER , JTL.OLD_ADDRESS_ID ADDRESS_ID , HZL.ADDRESS1 , HZL.ADDRESS2 || DECODE(HZL.ADDRESS2,'','',DECODE(HZL.ADDRESS3,'','',', ')) || HZL.ADDRESS3 || DECODE(HZL.ADDRESS3,'','',DECODE(HZL.ADDRESS4,'','',', ')) || HZL.ADDRESS4 ADDRESS2 , HZL.CITY || DECODE(HZL.CITY,'','',DECODE(HZL.STATE,'','',', ')) || HZL.STATE || DECODE(HZL.STATE,'','',' ') || HZL.POSTAL_CODE || DECODE(HZL.STATE,'','',', ') || HZL.COUNTRY ADDRESS3 , JTL.OLD_PLANNED_START_DATE PLANNED_START_DATE , JTL.OLD_PLANNED_END_DATE PLANNED_END_DATE , JTL.OLD_SCHEDULED_START_DATE SCHEDULED_START_DATE , JTL.OLD_SCHEDULED_END_DATE SCHEDULED_END_DATE , JTL.OLD_ACTUAL_START_DATE ACTUAL_START_DATE , JTL.OLD_ACTUAL_END_DATE ACTUAL_END_DATE , JTL.OLD_TIMEZONE_ID TIMEZONE_ID , HTI.GLOBAL_TIMEZONE_NAME GLOBAL_TIMEZONE_NAME , HTI.GMT_DEVIATION_HOURS GMT_DEVIATION_HOURS , JTL.OLD_SOURCE_OBJECT_TYPE_CODE SOURCE_OBJECT_TYPE_CODE , JTO.NAME SOURCE_OBJECT_TYPE , JTL.OLD_SOURCE_OBJECT_ID SOURCE_OBJECT_ID , JTL.OLD_SOURCE_OBJECT_NAME SOURCE_OBJECT_NAME , JTL.OLD_DURATION DURATION , JTL.OLD_DURATION_UOM DURATION_UOM , JTL.OLD_PLANNED_EFFORT PLANNED_EFFORT , JTL.OLD_PLANNED_EFFORT_UOM PLANNED_EFFORT_UOM , JTL.OLD_ACTUAL_EFFORT ACTUAL_EFFORT , JTL.OLD_ACTUAL_EFFORT_UOM ACTUAL_EFFORT_UOM , JTL.OLD_PERCENTAGE_COMPLETE PERCENTAGE_COMPLETE , JTL.PRIVATE_CHANGED_FLAG PRIVATE_FLAG , JTL.PUBLISH_CHANGED_FLAG PUBLISH_FLAG , JTLB.OLD_DESCRIPTION DESCRIPTION , JTL.OLD_PARENT_TASK_ID PARENT_TASK_ID , JTA2.TASK_NUMBER PARENT_TASK_NUMBER FROM JTF_TASK_AUDITS_B JTL , JTF_TASK_AUDITS_TL JTLB , JTF_TASKS_TL JTA , JTF_TASKS_B JTAB , JTF_TASKS_B JTA2 , JTF_TASK_TYPES_TL JTT , JTF_TASK_STATUSES_TL JTS , JTF_TASK_PRIORITIES_TL JTP , JTF_OBJECTS_TL JTO , JTF_OBJECTS_TL JTO2 , FND_USER FND , HZ_PARTIES HZP , HZ_CUST_ACCOUNTS HZCA , HZ_PARTY_SITES HZS , HZ_LOCATIONS HZL , HZ_TIMEZONES HTI WHERE JTL.TASK_AUDIT_ID = JTLB.TASK_AUDIT_ID AND JTL.TASK_ID = JTA.TASK_ID AND JTL.TASK_ID = JTAB.TASK_ID AND JTL.OLD_PARENT_TASK_ID = JTA2.TASK_ID (+) AND JTL.OLD_TASK_TYPE_ID = JTT.TASK_TYPE_ID AND JTL.OLD_TASK_STATUS_ID = JTS.TASK_STATUS_ID AND JTL.OLD_TASK_PRIORITY_ID = JTP.TASK_PRIORITY_ID(+) AND JTL.OLD_OWNER_TYPE_CODE = JTO2.OBJECT_CODE (+) AND JTL.OLD_SOURCE_OBJECT_TYPE_CODE = JTO.OBJECT_CODE (+) AND JTL.OLD_ASSIGNED_BY_ID = FND.USER_ID AND JTL.OLD_CUSTOMER_ID = HZP.PARTY_ID (+) AND JTL.OLD_CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID (+) AND JTL.OLD_ADDRESS_ID = HZS.PARTY_SITE_ID (+) AND HZS.LOCATION_ID = HZL.LOCATION_ID (+) AND JTL.OLD_TIMEZONE_ID = HTI.TIMEZONE_ID (+)

View Text - HTML Formatted

SELECT 'PRESENT' RECORD_STATUS
, 'JTF_BRM_3D_TASK_V' OBJECT_TYPE
, TASK_ID OBJECT_ID
, TASK_ID
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, TASK_NUMBER
, TASK_NAME
, TASK_TYPE_ID
, TASK_TYPE
, TASK_STATUS_ID
, TASK_STATUS
, TASK_PRIORITY_ID
, TASK_PRIORITY
, OWNER_ID
, OWNER
, OWNER_TYPE_CODE
, OWNER_TYPE
, ASSIGNED_BY_ID
, ASSIGNED_BY_NAME
, CUSTOMER_ID
, CUSTOMER_NAME
, CUSTOMER_NUMBER
, CUST_ACCOUNT_ID
, CUST_ACCOUNT_NUMBER
, ADDRESS_ID
, ADDRESS1
, ADDRESS2
, ADDRESS3
, PLANNED_START_DATE
, PLANNED_END_DATE
, SCHEDULED_START_DATE
, SCHEDULED_END_DATE
, ACTUAL_START_DATE
, ACTUAL_END_DATE
, TIMEZONE_ID
, GLOBAL_TIMEZONE_NAME
, GMT_DEVIATION_HOURS
, SOURCE_OBJECT_TYPE_CODE
, SOURCE_OBJECT_TYPE
, SOURCE_OBJECT_ID
, SOURCE_OBJECT_NAME
, DURATION
, DURATION_UOM
, PLANNED_EFFORT
, PLANNED_EFFORT_UOM
, ACTUAL_EFFORT
, ACTUAL_EFFORT_UOM
, PERCENTAGE_COMPLETE
, PRIVATE_FLAG
, PUBLISH_FLAG
, DESCRIPTION
, PARENT_TASK_ID
, PARENT_TASK_NUMBER
FROM JTF_TASKS_V UNION ALL SELECT 'HISTORIC' RECORD_STATUS
, 'JTF_BRM_3D_TASK_V' OBJECT_TYPE
, JTL.TASK_ID OBJECT_ID
, JTL.TASK_ID TASK_ID
, JTL.CREATED_BY CREATED_BY
, JTL.LAST_UPDATE_DATE CREATION_DATE
, JTL.LAST_UPDATED_BY LAST_UPDATE_BY
, JTL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JTL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, JTAB.TASK_NUMBER TASK_NUMBER
, JTA.TASK_NAME TASK_NAME
, JTL.OLD_TASK_TYPE_ID TASK_TYPE_ID
, JTT.NAME TASK_TYPE
, JTL.OLD_TASK_STATUS_ID TASK_STATUS_ID
, JTS.NAME TASK_STATUS
, JTL.OLD_TASK_PRIORITY_ID TASK_PRIORITY_ID
, JTP.NAME TASK_PRIORITY
, JTL.OLD_OWNER_ID OWNER_ID
, JTF_TASK_UTL.GET_OWNER( JTL.OLD_OWNER_TYPE_CODE
, JTL.OLD_OWNER_ID ) OWNER
, JTL.OLD_OWNER_TYPE_CODE OWNER_TYPE_CODE
, JTO2.NAME OWNER_TYPE
, JTL.OLD_ASSIGNED_BY_ID ASSIGNED_BY_ID
, FND.USER_NAME ASSIGNED_BY_NAME
, JTL.OLD_CUSTOMER_ID CUSTOMER_ID
, HZP.PARTY_NAME CUSTOMER_NAME
, HZP.PARTY_NUMBER CUSTOMER_NUMBER
, JTL.OLD_CUST_ACCOUNT_ID CUSTOMER_ACCOUNT_ID
, HZCA.ACCOUNT_NUMBER CUST_ACCOUNT_NUMBER
, JTL.OLD_ADDRESS_ID ADDRESS_ID
, HZL.ADDRESS1
, HZL.ADDRESS2 || DECODE(HZL.ADDRESS2
, ''
, ''
, DECODE(HZL.ADDRESS3
, ''
, ''
, '
, ')) || HZL.ADDRESS3 || DECODE(HZL.ADDRESS3
, ''
, ''
, DECODE(HZL.ADDRESS4
, ''
, ''
, '
, ')) || HZL.ADDRESS4 ADDRESS2
, HZL.CITY || DECODE(HZL.CITY
, ''
, ''
, DECODE(HZL.STATE
, ''
, ''
, '
, ')) || HZL.STATE || DECODE(HZL.STATE
, ''
, ''
, ' ') || HZL.POSTAL_CODE || DECODE(HZL.STATE
, ''
, ''
, '
, ') || HZL.COUNTRY ADDRESS3
, JTL.OLD_PLANNED_START_DATE PLANNED_START_DATE
, JTL.OLD_PLANNED_END_DATE PLANNED_END_DATE
, JTL.OLD_SCHEDULED_START_DATE SCHEDULED_START_DATE
, JTL.OLD_SCHEDULED_END_DATE SCHEDULED_END_DATE
, JTL.OLD_ACTUAL_START_DATE ACTUAL_START_DATE
, JTL.OLD_ACTUAL_END_DATE ACTUAL_END_DATE
, JTL.OLD_TIMEZONE_ID TIMEZONE_ID
, HTI.GLOBAL_TIMEZONE_NAME GLOBAL_TIMEZONE_NAME
, HTI.GMT_DEVIATION_HOURS GMT_DEVIATION_HOURS
, JTL.OLD_SOURCE_OBJECT_TYPE_CODE SOURCE_OBJECT_TYPE_CODE
, JTO.NAME SOURCE_OBJECT_TYPE
, JTL.OLD_SOURCE_OBJECT_ID SOURCE_OBJECT_ID
, JTL.OLD_SOURCE_OBJECT_NAME SOURCE_OBJECT_NAME
, JTL.OLD_DURATION DURATION
, JTL.OLD_DURATION_UOM DURATION_UOM
, JTL.OLD_PLANNED_EFFORT PLANNED_EFFORT
, JTL.OLD_PLANNED_EFFORT_UOM PLANNED_EFFORT_UOM
, JTL.OLD_ACTUAL_EFFORT ACTUAL_EFFORT
, JTL.OLD_ACTUAL_EFFORT_UOM ACTUAL_EFFORT_UOM
, JTL.OLD_PERCENTAGE_COMPLETE PERCENTAGE_COMPLETE
, JTL.PRIVATE_CHANGED_FLAG PRIVATE_FLAG
, JTL.PUBLISH_CHANGED_FLAG PUBLISH_FLAG
, JTLB.OLD_DESCRIPTION DESCRIPTION
, JTL.OLD_PARENT_TASK_ID PARENT_TASK_ID
, JTA2.TASK_NUMBER PARENT_TASK_NUMBER
FROM JTF_TASK_AUDITS_B JTL
, JTF_TASK_AUDITS_TL JTLB
, JTF_TASKS_TL JTA
, JTF_TASKS_B JTAB
, JTF_TASKS_B JTA2
, JTF_TASK_TYPES_TL JTT
, JTF_TASK_STATUSES_TL JTS
, JTF_TASK_PRIORITIES_TL JTP
, JTF_OBJECTS_TL JTO
, JTF_OBJECTS_TL JTO2
, FND_USER FND
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTY_SITES HZS
, HZ_LOCATIONS HZL
, HZ_TIMEZONES HTI
WHERE JTL.TASK_AUDIT_ID = JTLB.TASK_AUDIT_ID
AND JTL.TASK_ID = JTA.TASK_ID
AND JTL.TASK_ID = JTAB.TASK_ID
AND JTL.OLD_PARENT_TASK_ID = JTA2.TASK_ID (+)
AND JTL.OLD_TASK_TYPE_ID = JTT.TASK_TYPE_ID
AND JTL.OLD_TASK_STATUS_ID = JTS.TASK_STATUS_ID
AND JTL.OLD_TASK_PRIORITY_ID = JTP.TASK_PRIORITY_ID(+)
AND JTL.OLD_OWNER_TYPE_CODE = JTO2.OBJECT_CODE (+)
AND JTL.OLD_SOURCE_OBJECT_TYPE_CODE = JTO.OBJECT_CODE (+)
AND JTL.OLD_ASSIGNED_BY_ID = FND.USER_ID
AND JTL.OLD_CUSTOMER_ID = HZP.PARTY_ID (+)
AND JTL.OLD_CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID (+)
AND JTL.OLD_ADDRESS_ID = HZS.PARTY_SITE_ID (+)
AND HZS.LOCATION_ID = HZL.LOCATION_ID (+)
AND JTL.OLD_TIMEZONE_ID = HTI.TIMEZONE_ID (+)