VIEW: APPS.EGO_IMPORT_BATCH_DETAILS_V

Source

View Text - Preformatted

SELECT BATCH.BATCH_ID AS BATCH_ID, BATCH.ORGANIZATION_ID AS ORGANIZATION_ID, BATCH.SOURCE_SYSTEM_ID AS SOURCE_SYSTEM_ID, (SELECT ORIG_SYSTEM_NAME FROM HZ_ORIG_SYSTEMS_TL WHERE ORIG_SYSTEM_ID = BATCH.SOURCE_SYSTEM_ID AND LANGUAGE = USERENV('LANG') ) AS SOURCE_SYSTEM_NAME , BATCH.BATCH_TYPE AS BATCH_TYPE, ( SELECT DISPLAY_NAME FROM FND_OBJECTS_VL WHERE OBJ_NAME = BATCH.BATCH_TYPE ) AS BATCH_TYPE_MEANING , BATCH.ASSIGNEE AS ASSIGNEE, EGO_ITEM_UTIL_PVT.GET_USER_NAME(BATCH.ASSIGNEE) AS ASSIGNEE_NAME , BATCH.BATCH_STATUS AS BATCH_STATUS, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_BATCH_STATUS', BATCH.BATCH_STATUS) AS BATCH_STATUS_MEANING, BATCH.NAME AS BATCH_NAME, BATCH.DESCRIPTION AS BATCH_DESCRIPTION, BATCH.LAST_MATCH_REQUEST_ID AS LAST_MATCH_REQUEST_ID, BATCH.LAST_IMPORT_REQUEST_ID AS LAST_IMPORT_REQUEST_ID, BATCH.CREATED_BY AS CREATED_BY, EGO_ITEM_UTIL_PVT.GET_USER_NAME(BATCH.CREATED_BY) AS CREATOR_NAME , BATCH.CREATION_DATE AS CREATION_DATE, BATCH.LAST_UPDATED_BY AS LAST_UPDATED_BY, BATCH.LAST_UPDATE_DATE AS LAST_UPDATE_DATE, BATCH.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN, OPT.DEF_MATCH_RULE_CUST_APP_ID AS DEF_MATCH_RULE_CUST_APP_ID, OPT.DEF_MATCH_RULE_CUST_CODE AS DEF_MATCH_RULE_CUST_CODE, OPT.DEF_MATCH_RULE_RN_APP_ID AS DEF_MATCH_RULE_RN_APP_ID, OPT.DEF_MATCH_RULE_RN_CODE AS DEF_MATCH_RULE_RN_CODE, (SELECT ACL.NAME FROM AK_CUSTOMIZATIONS_TL ACL WHERE ACL.CUSTOMIZATION_APPLICATION_ID = OPT.DEF_MATCH_RULE_CUST_APP_ID AND ACL.CUSTOMIZATION_CODE = OPT.DEF_MATCH_RULE_CUST_CODE AND ACL.REGION_APPLICATION_ID = OPT.DEF_MATCH_RULE_RN_APP_ID AND ACL.REGION_CODE = OPT.DEF_MATCH_RULE_RN_CODE AND ACL.LANGUAGE = USERENV('LANG') ) AS DEFAULT_MATCH_RULE_MEANING, OPT.OPTION_SET_ID AS OPTION_SET_ID, OPT.APPLY_DEF_MATCH_RULE_ALL AS APPLY_DEF_MATCH_RULE_ALL, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO', OPT.APPLY_DEF_MATCH_RULE_ALL) AS APPLY_DEFMATCHRULEALL_MEANING, OPT.MATCH_ON_DATA_LOAD AS MATCH_ON_DATA_LOAD, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO', OPT.MATCH_ON_DATA_LOAD) AS MATCH_ON_DATA_LOAD_MEANING, OPT.CONFIRM_SINGLE_MATCH AS CONFIRM_SINGLE_MATCH, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO', OPT.CONFIRM_SINGLE_MATCH) AS CONFIRM_SINGLE_MATCH_MEANING, OPT.IMPORT_ON_DATA_LOAD AS IMPORT_ON_DATA_LOAD, ( CASE WHEN CP.REQUEST_ID IS NOT NULL AND CP.REQUESTED_START_DATE <= SYSDATE AND OPT.IMPORT_ON_DATA_LOAD = 'S' THEN EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_AUTO_IMPORT_OPTIONS', 'N') ELSE EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_AUTO_IMPORT_OPTIONS', OPT.IMPORT_ON_DATA_LOAD) END ) AS IMPORT_ON_DATA_LOAD_MEANING, OPT.REVISION_IMPORT_POLICY AS REVISION_IMPORT_POLICY, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_REV_IMPORT_POLICY', OPT.REVISION_IMPORT_POLICY) AS REVISION_IMPORT_POLICY_MEANING, OPT.IMPORT_XREF_ONLY AS IMPORT_XREF_ONLY, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_CROSS_REFERENCES', OPT.IMPORT_XREF_ONLY) AS IMPORT_XREF_ONLY_MEANING, OPT.STRUCTURE_TYPE_ID AS STRUCTURE_TYPE_ID, (SELECT DISPLAY_NAME FROM BOM_STRUCTURE_TYPES_TL WHERE STRUCTURE_TYPE_ID = OPT.STRUCTURE_TYPE_ID AND LANGUAGE = USERENV('LANG') ) AS STRUCTURE_TYPE_DISPLAY_NAME , OPT.STRUCTURE_NAME AS STRUCTURE_NAME, (DECODE(OPT.STRUCTURE_NAME, BOM_Globals.GET_PRIMARY_UI(), bom_globals.retrieve_message('BOM', 'BOM_PRIMARY'), null, null, (SELECT DISPLAY_NAME FROM BOM_ALTERNATE_DESIGNATORS_TL ALT_VL WHERE ALT_VL.ALTERNATE_DESIGNATOR_CODE = OPT.STRUCTURE_NAME AND LANGUAGE = USERENV('LANG') AND ORGANIZATION_ID = BATCH.ORGANIZATION_ID ) ) ) AS STRUCTURE_DISPLAY_NAME, OPT.STRUCTURE_EFFECTIVITY_TYPE AS STRUCTURE_EFFECTIVITY_TYPE, (SELECT MEANING FROM MFG_LOOKUPS WHERE LOOKUP_TYPE = 'BOM_EFFECTIVITY_CONTROL' AND LOOKUP_CODE = OPT.STRUCTURE_EFFECTIVITY_TYPE ) AS STRUCTURE_EFF_TYPE_MEANING , OPT.EFFECTIVITY_DATE AS EFFECTIVITY_DATE, OPT.FROM_END_ITEM_UNIT_NUMBER AS FROM_END_ITEM_UNIT_NUMBER, OPT.STRUCTURE_CONTENT AS STRUCTURE_CONTENT, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_STRUCTURE_CONTENT', OPT.STRUCTURE_CONTENT) AS STRUCTURE_CONTENT_MEANING, OPT.CHANGE_ORDER_CREATION AS CHANGE_ORDER_CREATION, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_CHG_ORDER_CREATION', OPT.CHANGE_ORDER_CREATION) AS CHANGE_ORDER_CREATION_MEANING, OPT.ADD_ALL_TO_CHANGE_FLAG AS ADD_ALL_TO_CHANGE_FLAG, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO', OPT.ADD_ALL_TO_CHANGE_FLAG) AS ADD_ALL_TO_CHANGE_FLAG_MEANING, OPT.CHANGE_MGMT_TYPE_CODE AS CHANGE_MGMT_TYPE_CODE, (SELECT TYPE_NAME FROM ENG_CHANGE_ORDER_TYPES_TL TL, ENG_CHANGE_ORDER_TYPES B WHERE B.BASE_CHANGE_MGMT_TYPE_CODE = 'CHANGE_ORDER' AND B.TYPE_CLASSIFICATION = 'CATEGORY' AND B.CHANGE_MGMT_TYPE_CODE = OPT.CHANGE_MGMT_TYPE_CODE AND B.CHANGE_ORDER_TYPE_ID = TL.CHANGE_ORDER_TYPE_ID AND TL.LANGUAGE = USERENV('LANG') ) AS CHANGE_MGMT_TYPE_MEANING , OPT.CHANGE_TYPE_ID AS CHANGE_TYPE_ID, (SELECT TYPE_NAME FROM ENG_CHANGE_ORDER_TYPES_TL TL WHERE CHANGE_ORDER_TYPE_ID = OPT.CHANGE_TYPE_ID AND TL.LANGUAGE = USERENV('LANG') ) AS CHANGE_TYPE_NAME , OPT.CHANGE_NOTICE AS CHANGE_NOTICE, OPT.CHANGE_NAME AS CHANGE_NAME, OPT.CHANGE_DESCRIPTION AS CHANGE_DESCRIPTION, BATCH.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER, OPT.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER1, OPT.NIR_OPTION AS NIR_OPTION, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_NIR_OPTION', OPT.NIR_OPTION) AS NIR_OPTION_MEANING, ( CASE WHEN CP.PHASE_CODE = 'P' AND CP.REQUESTED_START_DATE > SYSDATE THEN CP.REQUEST_ID ELSE NULL END ) AS SCHEDULED_REQUEST_ID, ( CASE WHEN CP.PHASE_CODE = 'P' AND CP.REQUESTED_START_DATE > SYSDATE THEN CP.REQUESTED_START_DATE ELSE NULL END ) AS IMPORT_SCHEDULE_DATE, Nvl(OPT.ENABLED_FOR_DATA_POOL, 'N') AS ENABLED_FOR_DATA_POOL, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO', Nvl(OPT.ENABLED_FOR_DATA_POOL, 'N')) AS ENABLED_FOR_DATA_POOL_MEANING FROM EGO_IMPORT_BATCHES_VL BATCH, EGO_IMPORT_OPTION_SETS OPT, FND_CONCURRENT_REQUESTS CP WHERE BATCH.BATCH_ID = OPT.BATCH_ID AND BATCH.LAST_IMPORT_REQUEST_ID = CP.REQUEST_ID(+)

View Text - HTML Formatted

SELECT BATCH.BATCH_ID AS BATCH_ID
, BATCH.ORGANIZATION_ID AS ORGANIZATION_ID
, BATCH.SOURCE_SYSTEM_ID AS SOURCE_SYSTEM_ID
, (SELECT ORIG_SYSTEM_NAME
FROM HZ_ORIG_SYSTEMS_TL
WHERE ORIG_SYSTEM_ID = BATCH.SOURCE_SYSTEM_ID
AND LANGUAGE = USERENV('LANG') ) AS SOURCE_SYSTEM_NAME
, BATCH.BATCH_TYPE AS BATCH_TYPE
, ( SELECT DISPLAY_NAME
FROM FND_OBJECTS_VL
WHERE OBJ_NAME = BATCH.BATCH_TYPE ) AS BATCH_TYPE_MEANING
, BATCH.ASSIGNEE AS ASSIGNEE
, EGO_ITEM_UTIL_PVT.GET_USER_NAME(BATCH.ASSIGNEE) AS ASSIGNEE_NAME
, BATCH.BATCH_STATUS AS BATCH_STATUS
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_BATCH_STATUS'
, BATCH.BATCH_STATUS) AS BATCH_STATUS_MEANING
, BATCH.NAME AS BATCH_NAME
, BATCH.DESCRIPTION AS BATCH_DESCRIPTION
, BATCH.LAST_MATCH_REQUEST_ID AS LAST_MATCH_REQUEST_ID
, BATCH.LAST_IMPORT_REQUEST_ID AS LAST_IMPORT_REQUEST_ID
, BATCH.CREATED_BY AS CREATED_BY
, EGO_ITEM_UTIL_PVT.GET_USER_NAME(BATCH.CREATED_BY) AS CREATOR_NAME
, BATCH.CREATION_DATE AS CREATION_DATE
, BATCH.LAST_UPDATED_BY AS LAST_UPDATED_BY
, BATCH.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, BATCH.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, OPT.DEF_MATCH_RULE_CUST_APP_ID AS DEF_MATCH_RULE_CUST_APP_ID
, OPT.DEF_MATCH_RULE_CUST_CODE AS DEF_MATCH_RULE_CUST_CODE
, OPT.DEF_MATCH_RULE_RN_APP_ID AS DEF_MATCH_RULE_RN_APP_ID
, OPT.DEF_MATCH_RULE_RN_CODE AS DEF_MATCH_RULE_RN_CODE
, (SELECT ACL.NAME
FROM AK_CUSTOMIZATIONS_TL ACL
WHERE ACL.CUSTOMIZATION_APPLICATION_ID = OPT.DEF_MATCH_RULE_CUST_APP_ID
AND ACL.CUSTOMIZATION_CODE = OPT.DEF_MATCH_RULE_CUST_CODE
AND ACL.REGION_APPLICATION_ID = OPT.DEF_MATCH_RULE_RN_APP_ID
AND ACL.REGION_CODE = OPT.DEF_MATCH_RULE_RN_CODE
AND ACL.LANGUAGE = USERENV('LANG') ) AS DEFAULT_MATCH_RULE_MEANING
, OPT.OPTION_SET_ID AS OPTION_SET_ID
, OPT.APPLY_DEF_MATCH_RULE_ALL AS APPLY_DEF_MATCH_RULE_ALL
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO'
, OPT.APPLY_DEF_MATCH_RULE_ALL) AS APPLY_DEFMATCHRULEALL_MEANING
, OPT.MATCH_ON_DATA_LOAD AS MATCH_ON_DATA_LOAD
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO'
, OPT.MATCH_ON_DATA_LOAD) AS MATCH_ON_DATA_LOAD_MEANING
, OPT.CONFIRM_SINGLE_MATCH AS CONFIRM_SINGLE_MATCH
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO'
, OPT.CONFIRM_SINGLE_MATCH) AS CONFIRM_SINGLE_MATCH_MEANING
, OPT.IMPORT_ON_DATA_LOAD AS IMPORT_ON_DATA_LOAD
, ( CASE WHEN CP.REQUEST_ID IS NOT NULL
AND CP.REQUESTED_START_DATE <= SYSDATE
AND OPT.IMPORT_ON_DATA_LOAD = 'S' THEN EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_AUTO_IMPORT_OPTIONS'
, 'N') ELSE EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_AUTO_IMPORT_OPTIONS'
, OPT.IMPORT_ON_DATA_LOAD) END ) AS IMPORT_ON_DATA_LOAD_MEANING
, OPT.REVISION_IMPORT_POLICY AS REVISION_IMPORT_POLICY
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_REV_IMPORT_POLICY'
, OPT.REVISION_IMPORT_POLICY) AS REVISION_IMPORT_POLICY_MEANING
, OPT.IMPORT_XREF_ONLY AS IMPORT_XREF_ONLY
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_CROSS_REFERENCES'
, OPT.IMPORT_XREF_ONLY) AS IMPORT_XREF_ONLY_MEANING
, OPT.STRUCTURE_TYPE_ID AS STRUCTURE_TYPE_ID
, (SELECT DISPLAY_NAME
FROM BOM_STRUCTURE_TYPES_TL
WHERE STRUCTURE_TYPE_ID = OPT.STRUCTURE_TYPE_ID
AND LANGUAGE = USERENV('LANG') ) AS STRUCTURE_TYPE_DISPLAY_NAME
, OPT.STRUCTURE_NAME AS STRUCTURE_NAME
, (DECODE(OPT.STRUCTURE_NAME
, BOM_GLOBALS.GET_PRIMARY_UI()
, BOM_GLOBALS.RETRIEVE_MESSAGE('BOM'
, 'BOM_PRIMARY')
, NULL
, NULL
, (SELECT DISPLAY_NAME
FROM BOM_ALTERNATE_DESIGNATORS_TL ALT_VL
WHERE ALT_VL.ALTERNATE_DESIGNATOR_CODE = OPT.STRUCTURE_NAME
AND LANGUAGE = USERENV('LANG')
AND ORGANIZATION_ID = BATCH.ORGANIZATION_ID ) ) ) AS STRUCTURE_DISPLAY_NAME
, OPT.STRUCTURE_EFFECTIVITY_TYPE AS STRUCTURE_EFFECTIVITY_TYPE
, (SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'BOM_EFFECTIVITY_CONTROL'
AND LOOKUP_CODE = OPT.STRUCTURE_EFFECTIVITY_TYPE ) AS STRUCTURE_EFF_TYPE_MEANING
, OPT.EFFECTIVITY_DATE AS EFFECTIVITY_DATE
, OPT.FROM_END_ITEM_UNIT_NUMBER AS FROM_END_ITEM_UNIT_NUMBER
, OPT.STRUCTURE_CONTENT AS STRUCTURE_CONTENT
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_STRUCTURE_CONTENT'
, OPT.STRUCTURE_CONTENT) AS STRUCTURE_CONTENT_MEANING
, OPT.CHANGE_ORDER_CREATION AS CHANGE_ORDER_CREATION
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_CHG_ORDER_CREATION'
, OPT.CHANGE_ORDER_CREATION) AS CHANGE_ORDER_CREATION_MEANING
, OPT.ADD_ALL_TO_CHANGE_FLAG AS ADD_ALL_TO_CHANGE_FLAG
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO'
, OPT.ADD_ALL_TO_CHANGE_FLAG) AS ADD_ALL_TO_CHANGE_FLAG_MEANING
, OPT.CHANGE_MGMT_TYPE_CODE AS CHANGE_MGMT_TYPE_CODE
, (SELECT TYPE_NAME
FROM ENG_CHANGE_ORDER_TYPES_TL TL
, ENG_CHANGE_ORDER_TYPES B
WHERE B.BASE_CHANGE_MGMT_TYPE_CODE = 'CHANGE_ORDER'
AND B.TYPE_CLASSIFICATION = 'CATEGORY'
AND B.CHANGE_MGMT_TYPE_CODE = OPT.CHANGE_MGMT_TYPE_CODE
AND B.CHANGE_ORDER_TYPE_ID = TL.CHANGE_ORDER_TYPE_ID
AND TL.LANGUAGE = USERENV('LANG') ) AS CHANGE_MGMT_TYPE_MEANING
, OPT.CHANGE_TYPE_ID AS CHANGE_TYPE_ID
, (SELECT TYPE_NAME
FROM ENG_CHANGE_ORDER_TYPES_TL TL
WHERE CHANGE_ORDER_TYPE_ID = OPT.CHANGE_TYPE_ID
AND TL.LANGUAGE = USERENV('LANG') ) AS CHANGE_TYPE_NAME
, OPT.CHANGE_NOTICE AS CHANGE_NOTICE
, OPT.CHANGE_NAME AS CHANGE_NAME
, OPT.CHANGE_DESCRIPTION AS CHANGE_DESCRIPTION
, BATCH.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER
, OPT.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER1
, OPT.NIR_OPTION AS NIR_OPTION
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_IMPORT_NIR_OPTION'
, OPT.NIR_OPTION) AS NIR_OPTION_MEANING
, ( CASE WHEN CP.PHASE_CODE = 'P'
AND CP.REQUESTED_START_DATE > SYSDATE THEN CP.REQUEST_ID ELSE NULL END ) AS SCHEDULED_REQUEST_ID
, ( CASE WHEN CP.PHASE_CODE = 'P'
AND CP.REQUESTED_START_DATE > SYSDATE THEN CP.REQUESTED_START_DATE ELSE NULL END ) AS IMPORT_SCHEDULE_DATE
, NVL(OPT.ENABLED_FOR_DATA_POOL
, 'N') AS ENABLED_FOR_DATA_POOL
, EGO_ITEM_UTIL_PVT.GET_LOOKUP_MEANING('EGO_YES_NO'
, NVL(OPT.ENABLED_FOR_DATA_POOL
, 'N')) AS ENABLED_FOR_DATA_POOL_MEANING
FROM EGO_IMPORT_BATCHES_VL BATCH
, EGO_IMPORT_OPTION_SETS OPT
, FND_CONCURRENT_REQUESTS CP
WHERE BATCH.BATCH_ID = OPT.BATCH_ID
AND BATCH.LAST_IMPORT_REQUEST_ID = CP.REQUEST_ID(+)