View: AMS_APPROVAL_DETAILS_V

Product:	AMS - Marketing
Description:	This view returns Approval detail information with appropriate lookup meanings
Implementation/DBA Data:	APPS.AMS_APPROVAL_DETAILS_V

View Text

SELECT B.ROW_ID ROW_ID
, B.START_DATE_ACTIVE
, B.END_DATE_ACTIVE
, B.APPROVAL_DETAIL_ID
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.OBJECT_VERSION_NUMBER
, B.SECURITY_GROUP_ID
, B.BUSINESS_GROUP_ID
, B.BUSINESS_UNIT_ID
, B.ORGANIZATION_ID
, B.CUSTOM_SETUP_ID
, B.APPROVAL_OBJECT
, B.APPROVAL_OBJECT_TYPE
, B.APPROVAL_TYPE
, B.APPROVAL_PRIORITY
, B.APPROVAL_LIMIT_TO
, B.APPROVAL_LIMIT_FROM
, B.SEEDED_FLAG
, B.ACTIVE_FLAG
, B.CURRENCY_CODE
, B.NAME
, B.DESCRIPTION
, B.USER_COUNTRY_CODE
, BU.NAME
, HR.NAME
, CS.SETUP_NAME
, L1.MEANING
, L2.MEANING
, L3.MEANING
FROM AMS_APPROVAL_DETAILS_VL B
, HR_ORGANIZATION_UNITS BU
, HR_ORGANIZATION_UNITS HR
, AMS_CUSTOM_SETUPS_VL CS
, AMS_LOOKUPS L1
, AMS_LOOKUPS L2
, AMS_LOOKUPS L3
WHERE B.APPROVAL_OBJECT <> 'CLAM'
AND B.ORGANIZATION_ID = HR.ORGANIZATION_ID (+)
AND B.BUSINESS_UNIT_ID = BU.ORGANIZATION_ID (+)
AND BU.TYPE(+) = 'BU'
AND B.CUSTOM_SETUP_ID = CS.CUSTOM_SETUP_ID(+)
AND L1.LOOKUP_CODE(+) = B.APPROVAL_TYPE
AND L1.LOOKUP_TYPE(+) = 'AMS_APPROVAL_TYPE'
AND L2.LOOKUP_CODE(+)= B.APPROVAL_PRIORITY
AND L2.LOOKUP_TYPE(+) = 'AMS_PRIORITY'
AND L3.LOOKUP_CODE(+)= B.APPROVAL_OBJECT
AND L3.LOOKUP_TYPE(+) = 'AMS_APPROVAL_RULE_FOR' UNION ALL SELECT B.ROW_ID ROW_ID
, B.START_DATE_ACTIVE
, B.END_DATE_ACTIVE
, B.APPROVAL_DETAIL_ID
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.OBJECT_VERSION_NUMBER
, B.SECURITY_GROUP_ID
, B.BUSINESS_GROUP_ID
, B.BUSINESS_UNIT_ID
, B.ORGANIZATION_ID
, B.CUSTOM_SETUP_ID
, B.APPROVAL_OBJECT
, B.APPROVAL_OBJECT_TYPE
, B.APPROVAL_TYPE
, B.APPROVAL_PRIORITY
, B.APPROVAL_LIMIT_TO
, B.APPROVAL_LIMIT_FROM
, B.SEEDED_FLAG
, B.ACTIVE_FLAG
, B.CURRENCY_CODE
, B.NAME
, B.DESCRIPTION
, B.USER_COUNTRY_CODE
, BU.NAME
, HR.NAME
, CS.SETUP_NAME
, L1.MEANING
, RC.NAME
, L3.MEANING
FROM AMS_APPROVAL_DETAILS_VL B
, HR_ORGANIZATION_UNITS BU
, HR_ORGANIZATION_UNITS HR
, AMS_CUSTOM_SETUPS_VL CS
, AMS_LOOKUPS L1
, OZF_REASON_CODES_VL RC
, AMS_LOOKUPS L3
WHERE B.APPROVAL_OBJECT = 'CLAM'
AND B.ORGANIZATION_ID = HR.ORGANIZATION_ID (+)
AND B.BUSINESS_UNIT_ID = BU.ORGANIZATION_ID (+)
AND BU.TYPE(+) = 'BU'
AND B.CUSTOM_SETUP_ID = CS.CUSTOM_SETUP_ID(+)
AND L1.LOOKUP_CODE(+) = B.APPROVAL_TYPE
AND L1.LOOKUP_TYPE(+) = 'AMS_APPROVAL_TYPE'
AND B.APPROVAL_PRIORITY = RC.REASON_CODE_ID(+)
AND L3.LOOKUP_CODE(+)= B.APPROVAL_OBJECT
AND L3.LOOKUP_TYPE(+) = 'AMS_APPROVAL_RULE_FOR'

Columns