VIEW: APPS.HXC_APPROVAL_COMPS_V

Source

View Text - Preformatted

SELECT hac.ROWID row_id, hac.approval_comp_id, hac.approval_style_id, has.NAME, hac.time_recipient_id, htr.NAME, hac.approval_mechanism, hrl.meaning, hac.approval_mechanism_id, TO_CHAR (NULL), hac.approval_order, hac.start_date , DECODE (hac.end_date, hr_general.end_of_time, TO_DATE (NULL), hac.end_date) , hac.wf_item_type, hac.wf_name, hac.object_version_number , hac.created_by, hac.creation_date, hac.last_updated_by, hac.last_update_date, hac.last_update_login, htc.time_category_name, hac.time_category_id, hac.parent_comp_id, hac.parent_comp_ovn, hac.run_recipient_extensions FROM hxc_time_recipients htr , hxc_approval_styles has , hr_standard_lookups hrl , hxc_approval_comps hac , hxc_time_categories htc WHERE hac.approval_style_id = has.approval_style_id AND hac.time_recipient_id = htr.time_recipient_id(+) AND hrl.lookup_type(+) = 'HXC_APPROVAL_MECHANISM' AND hrl.lookup_code(+) = hac.approval_mechanism AND hrl.application_id(+) = 809 AND hac.approval_mechanism IN ('HR_SUPERVISOR', 'AUTO_APPROVE', 'ORG_TIMEKEEPER','ENTRY_LEVEL_APPROVAL','PROJECT_MANAGER') AND hac.approval_mechanism_id IS NULL AND (htc.time_category_id(+) = hac.time_category_id) UNION SELECT hac.ROWID row_id, hac.approval_comp_id, hac.approval_style_id, has.NAME, hac.time_recipient_id, htr.NAME, hac.approval_mechanism, hrl.meaning, hac.approval_mechanism_id, ppx.full_name, hac.approval_order, hac.start_date, DECODE (hac.end_date, hr_general.end_of_time, TO_DATE (NULL), hac.end_date), hac.wf_item_type, hac.wf_name, hac.object_version_number, hac.created_by, hac.creation_date, hac.last_updated_by, hac.last_update_date, hac.last_update_login, htc.time_category_name, hac.time_category_id, hac.parent_comp_id, hac.parent_comp_ovn, hac.run_recipient_extensions FROM hxc_time_recipients htr , hxc_approval_styles has , hr_standard_lookups hrl , per_people_x ppx , hxc_approval_comps hac , hxc_time_categories htc WHERE hac.approval_style_id = has.approval_style_id AND hac.time_recipient_id = htr.time_recipient_id(+) AND hrl.lookup_type(+) = 'HXC_APPROVAL_MECHANISM' AND hrl.lookup_code(+) = hac.approval_mechanism AND hrl.application_id(+) = 809 AND hac.approval_mechanism = 'PERSON' AND hac.approval_mechanism_id = ppx.person_id AND (htc.time_category_id(+) = hac.time_category_id) UNION SELECT hac.ROWID row_id, hac.approval_comp_id, hac.approval_style_id, has.NAME, hac.time_recipient_id, htr.NAME, hac.approval_mechanism, hrl.meaning, hac.approval_mechanism_id, wfa.display_name, hac.approval_order, hac.start_date, DECODE (hac.end_date, hr_general.end_of_time, TO_DATE (NULL), hac.end_date), hac.wf_item_type, hac.wf_name, hac.object_version_number, hac.created_by, hac.creation_date, hac.last_updated_by, hac.last_update_date, hac.last_update_login, htc.time_category_name, hac.time_category_id, hac.parent_comp_id, hac.parent_comp_ovn, hac.run_recipient_extensions FROM hxc_time_recipients htr , hxc_approval_styles has , hr_standard_lookups hrl , wf_activities_vl wfa , hxc_approval_comps hac , hxc_time_categories htc WHERE hac.approval_style_id = has.approval_style_id AND hac.time_recipient_id = htr.time_recipient_id(+) AND hrl.lookup_type(+) = 'HXC_APPROVAL_MECHANISM' AND hrl.lookup_code(+) = hac.approval_mechanism AND hrl.application_id(+) = 809 AND hac.approval_mechanism = 'WORKFLOW' AND wfa.NAME LIKE 'OTC_APPROVAL%' AND wfa.TYPE = 'PROCESS' AND wfa.version = (SELECT MAX (version) FROM wf_activities_vl wf WHERE wf.item_type = wfa.item_type AND wf.NAME = wfa.NAME) AND hac.wf_item_type = wfa.item_type AND hac.wf_name = wfa.NAME AND (htc.time_category_id(+) = hac.time_category_id) UNION SELECT hac.ROWID row_id, hac.approval_comp_id, hac.approval_style_id, has.NAME, hac.time_recipient_id, htr.NAME, hac.approval_mechanism, hrl.meaning, hac.approval_mechanism_id, ff.description, hac.approval_order, hac.start_date, DECODE (hac.end_date, hr_general.end_of_time, TO_DATE (NULL), hac.end_date), hac.wf_item_type, hac.wf_name, hac.object_version_number, hac.created_by, hac.creation_date, hac.last_updated_by, hac.last_update_date, hac.last_update_login, htc.time_category_name, hac.time_category_id, hac.parent_comp_id, hac.parent_comp_ovn, hac.run_recipient_extensions FROM hxc_time_recipients htr , hxc_approval_styles has , hr_standard_lookups hrl , ff_formulas_f ff , ff_formula_types fft , hxc_approval_comps hac , hxc_time_categories htc WHERE hac.approval_style_id = has.approval_style_id AND hac.time_recipient_id = htr.time_recipient_id(+) AND hrl.lookup_type(+) = 'HXC_APPROVAL_MECHANISM' AND hrl.lookup_code(+) = hac.approval_mechanism AND hrl.application_id(+) = 809 AND hac.approval_mechanism IN ('FORMULA_MECHANISM', 'FORMULA_WORKFLOW') AND ff.formula_type_id = fft.formula_type_id AND LOWER (fft.formula_type_name) LIKE 'otl%' AND hac.approval_mechanism_id = ff.formula_id AND (htc.time_category_id(+) = hac.time_category_id)

View Text - HTML Formatted

SELECT HAC.ROWID ROW_ID
, HAC.APPROVAL_COMP_ID
, HAC.APPROVAL_STYLE_ID
, HAS.NAME
, HAC.TIME_RECIPIENT_ID
, HTR.NAME
, HAC.APPROVAL_MECHANISM
, HRL.MEANING
, HAC.APPROVAL_MECHANISM_ID
, TO_CHAR (NULL)
, HAC.APPROVAL_ORDER
, HAC.START_DATE
, DECODE (HAC.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE (NULL)
, HAC.END_DATE)
, HAC.WF_ITEM_TYPE
, HAC.WF_NAME
, HAC.OBJECT_VERSION_NUMBER
, HAC.CREATED_BY
, HAC.CREATION_DATE
, HAC.LAST_UPDATED_BY
, HAC.LAST_UPDATE_DATE
, HAC.LAST_UPDATE_LOGIN
, HTC.TIME_CATEGORY_NAME
, HAC.TIME_CATEGORY_ID
, HAC.PARENT_COMP_ID
, HAC.PARENT_COMP_OVN
, HAC.RUN_RECIPIENT_EXTENSIONS
FROM HXC_TIME_RECIPIENTS HTR
, HXC_APPROVAL_STYLES HAS
, HR_STANDARD_LOOKUPS HRL
, HXC_APPROVAL_COMPS HAC
, HXC_TIME_CATEGORIES HTC
WHERE HAC.APPROVAL_STYLE_ID = HAS.APPROVAL_STYLE_ID
AND HAC.TIME_RECIPIENT_ID = HTR.TIME_RECIPIENT_ID(+)
AND HRL.LOOKUP_TYPE(+) = 'HXC_APPROVAL_MECHANISM'
AND HRL.LOOKUP_CODE(+) = HAC.APPROVAL_MECHANISM
AND HRL.APPLICATION_ID(+) = 809
AND HAC.APPROVAL_MECHANISM IN ('HR_SUPERVISOR'
, 'AUTO_APPROVE'
, 'ORG_TIMEKEEPER'
, 'ENTRY_LEVEL_APPROVAL'
, 'PROJECT_MANAGER')
AND HAC.APPROVAL_MECHANISM_ID IS NULL
AND (HTC.TIME_CATEGORY_ID(+) = HAC.TIME_CATEGORY_ID) UNION SELECT HAC.ROWID ROW_ID
, HAC.APPROVAL_COMP_ID
, HAC.APPROVAL_STYLE_ID
, HAS.NAME
, HAC.TIME_RECIPIENT_ID
, HTR.NAME
, HAC.APPROVAL_MECHANISM
, HRL.MEANING
, HAC.APPROVAL_MECHANISM_ID
, PPX.FULL_NAME
, HAC.APPROVAL_ORDER
, HAC.START_DATE
, DECODE (HAC.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE (NULL)
, HAC.END_DATE)
, HAC.WF_ITEM_TYPE
, HAC.WF_NAME
, HAC.OBJECT_VERSION_NUMBER
, HAC.CREATED_BY
, HAC.CREATION_DATE
, HAC.LAST_UPDATED_BY
, HAC.LAST_UPDATE_DATE
, HAC.LAST_UPDATE_LOGIN
, HTC.TIME_CATEGORY_NAME
, HAC.TIME_CATEGORY_ID
, HAC.PARENT_COMP_ID
, HAC.PARENT_COMP_OVN
, HAC.RUN_RECIPIENT_EXTENSIONS
FROM HXC_TIME_RECIPIENTS HTR
, HXC_APPROVAL_STYLES HAS
, HR_STANDARD_LOOKUPS HRL
, PER_PEOPLE_X PPX
, HXC_APPROVAL_COMPS HAC
, HXC_TIME_CATEGORIES HTC
WHERE HAC.APPROVAL_STYLE_ID = HAS.APPROVAL_STYLE_ID
AND HAC.TIME_RECIPIENT_ID = HTR.TIME_RECIPIENT_ID(+)
AND HRL.LOOKUP_TYPE(+) = 'HXC_APPROVAL_MECHANISM'
AND HRL.LOOKUP_CODE(+) = HAC.APPROVAL_MECHANISM
AND HRL.APPLICATION_ID(+) = 809
AND HAC.APPROVAL_MECHANISM = 'PERSON'
AND HAC.APPROVAL_MECHANISM_ID = PPX.PERSON_ID
AND (HTC.TIME_CATEGORY_ID(+) = HAC.TIME_CATEGORY_ID) UNION SELECT HAC.ROWID ROW_ID
, HAC.APPROVAL_COMP_ID
, HAC.APPROVAL_STYLE_ID
, HAS.NAME
, HAC.TIME_RECIPIENT_ID
, HTR.NAME
, HAC.APPROVAL_MECHANISM
, HRL.MEANING
, HAC.APPROVAL_MECHANISM_ID
, WFA.DISPLAY_NAME
, HAC.APPROVAL_ORDER
, HAC.START_DATE
, DECODE (HAC.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE (NULL)
, HAC.END_DATE)
, HAC.WF_ITEM_TYPE
, HAC.WF_NAME
, HAC.OBJECT_VERSION_NUMBER
, HAC.CREATED_BY
, HAC.CREATION_DATE
, HAC.LAST_UPDATED_BY
, HAC.LAST_UPDATE_DATE
, HAC.LAST_UPDATE_LOGIN
, HTC.TIME_CATEGORY_NAME
, HAC.TIME_CATEGORY_ID
, HAC.PARENT_COMP_ID
, HAC.PARENT_COMP_OVN
, HAC.RUN_RECIPIENT_EXTENSIONS
FROM HXC_TIME_RECIPIENTS HTR
, HXC_APPROVAL_STYLES HAS
, HR_STANDARD_LOOKUPS HRL
, WF_ACTIVITIES_VL WFA
, HXC_APPROVAL_COMPS HAC
, HXC_TIME_CATEGORIES HTC
WHERE HAC.APPROVAL_STYLE_ID = HAS.APPROVAL_STYLE_ID
AND HAC.TIME_RECIPIENT_ID = HTR.TIME_RECIPIENT_ID(+)
AND HRL.LOOKUP_TYPE(+) = 'HXC_APPROVAL_MECHANISM'
AND HRL.LOOKUP_CODE(+) = HAC.APPROVAL_MECHANISM
AND HRL.APPLICATION_ID(+) = 809
AND HAC.APPROVAL_MECHANISM = 'WORKFLOW'
AND WFA.NAME LIKE 'OTC_APPROVAL%'
AND WFA.TYPE = 'PROCESS'
AND WFA.VERSION = (SELECT MAX (VERSION)
FROM WF_ACTIVITIES_VL WF
WHERE WF.ITEM_TYPE = WFA.ITEM_TYPE
AND WF.NAME = WFA.NAME)
AND HAC.WF_ITEM_TYPE = WFA.ITEM_TYPE
AND HAC.WF_NAME = WFA.NAME
AND (HTC.TIME_CATEGORY_ID(+) = HAC.TIME_CATEGORY_ID) UNION SELECT HAC.ROWID ROW_ID
, HAC.APPROVAL_COMP_ID
, HAC.APPROVAL_STYLE_ID
, HAS.NAME
, HAC.TIME_RECIPIENT_ID
, HTR.NAME
, HAC.APPROVAL_MECHANISM
, HRL.MEANING
, HAC.APPROVAL_MECHANISM_ID
, FF.DESCRIPTION
, HAC.APPROVAL_ORDER
, HAC.START_DATE
, DECODE (HAC.END_DATE
, HR_GENERAL.END_OF_TIME
, TO_DATE (NULL)
, HAC.END_DATE)
, HAC.WF_ITEM_TYPE
, HAC.WF_NAME
, HAC.OBJECT_VERSION_NUMBER
, HAC.CREATED_BY
, HAC.CREATION_DATE
, HAC.LAST_UPDATED_BY
, HAC.LAST_UPDATE_DATE
, HAC.LAST_UPDATE_LOGIN
, HTC.TIME_CATEGORY_NAME
, HAC.TIME_CATEGORY_ID
, HAC.PARENT_COMP_ID
, HAC.PARENT_COMP_OVN
, HAC.RUN_RECIPIENT_EXTENSIONS
FROM HXC_TIME_RECIPIENTS HTR
, HXC_APPROVAL_STYLES HAS
, HR_STANDARD_LOOKUPS HRL
, FF_FORMULAS_F FF
, FF_FORMULA_TYPES FFT
, HXC_APPROVAL_COMPS HAC
, HXC_TIME_CATEGORIES HTC
WHERE HAC.APPROVAL_STYLE_ID = HAS.APPROVAL_STYLE_ID
AND HAC.TIME_RECIPIENT_ID = HTR.TIME_RECIPIENT_ID(+)
AND HRL.LOOKUP_TYPE(+) = 'HXC_APPROVAL_MECHANISM'
AND HRL.LOOKUP_CODE(+) = HAC.APPROVAL_MECHANISM
AND HRL.APPLICATION_ID(+) = 809
AND HAC.APPROVAL_MECHANISM IN ('FORMULA_MECHANISM'
, 'FORMULA_WORKFLOW')
AND FF.FORMULA_TYPE_ID = FFT.FORMULA_TYPE_ID
AND LOWER (FFT.FORMULA_TYPE_NAME) LIKE 'OTL%'
AND HAC.APPROVAL_MECHANISM_ID = FF.FORMULA_ID
AND (HTC.TIME_CATEGORY_ID(+) = HAC.TIME_CATEGORY_ID)