1. Tables and Views for HCM
  2. PAY_BL_TASK_ACTIONS_VL

PAY_BL_TASK_ACTIONS_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

TASK_ACTION_ID

TASK_ID

ACTION_TYPE

ACTION_DETAILS

TASK_ACTION_NAME

DISPLAY_TASK_ACTION_NAME

LEGISLATIVE_DATA_GROUP_ID

Query

SQL_Statement

SELECT PAY_TASK_ACTIONS.BASE_TASK_ACTION_ID TASK_ACTION_ID,

pay_task_actions.base_task_id task_id,

MAX(DECODE(PAY_TASK_ACTIONS.ACTION_TYPE, 'SERVICE', 'SERVICE_OPERATION', 'TABLE_VIEW', DECODE (PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE, 'PLSQL', 'PLSQL',NULL), PAY_TASK_ACTIONS.ACTION_TYPE)) ACTION_TYPE,

MAX(DECODE(pay_task_properties.task_property_type, 'SERVICE_OPERATION_NAME_SPACE', pay_task_properties.task_property_value, 'VO_FULL_NAME', pay_task_properties.task_property_value, NULL)) action_details,

MAX(DECODE(PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE, 'PLSQL', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE,'SERVICE_OPERATION_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, 'VO_INSTANCE_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, NULL)) TASK_ACTION_NAME,

PAY_TASK_ACTIONS_TL.ACTION_NAME DISPLAY_TASK_ACTION_NAME,

MAX(PAY_TASK_ACTIONS.LEGISLATIVE_DATA_GROUP_ID) LEGISLATIVE_DATA_GROUP_ID

FROM PAY_TASK_ACTIONS,

PAY_TASK_USAGE,

PAY_TASK_PROPERTIES,

PAY_TASK_ACTIONS_TL

WHERE PAY_TASK_ACTIONS.BASE_TASK_ID = PAY_TASK_USAGE.BASE_TASK_ID

AND PAY_TASK_USAGE.USAGE_TYPE = 'BATCH_LOADER'

AND pay_task_properties.base_task_action_id = pay_task_actions.base_task_action_id

AND PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE IN ('PLSQL','VIEW_NAME', 'SERVICE_OPERATION_NAME', 'SERVICE_OPERATION_NAME_SPACE', 'VO_FULL_NAME', 'VO_INSTANCE_NAME')

AND PAY_TASK_ACTIONS_TL.TASK_ACTION_ID = PAY_TASK_ACTIONS.TASK_ACTION_ID

AND pay_task_actions_tl.language = userenv('LANG')

AND PAY_TASK_USAGE.base_task_usage_name NOT IN ('ELEMENT_ENTRY','ELEMENT','ELEMENT_TEMPLATE')

GROUP BY PAY_TASK_ACTIONS.BASE_TASK_ACTION_ID, pay_task_actions.base_task_id, PAY_TASK_ACTIONS_TL.ACTION_NAME

UNION

SELECT pet.element_type_id TASK_ACTION_ID,

PAY_TASK_ACTIONS.BASE_TASK_ID TASK_ID,

MAX(DECODE(PAY_TASK_ACTIONS.ACTION_TYPE, 'SERVICE', 'SERVICE_OPERATION', 'TABLE_VIEW', 'VIEW_OBJECT', PAY_TASK_ACTIONS.ACTION_TYPE)) ACTION_TYPE,

MAX(DECODE(PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE, 'SERVICE_OPERATION_NAME_SPACE', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, 'VO_FULL_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, NULL)) ACTION_DETAILS,

MAX(DECODE(PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE, 'SERVICE_OPERATION_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, 'VO_INSTANCE_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, NULL)) TASK_ACTION_NAME,

PAY_TASK_ACTIONS_TL.ACTION_NAME

||' - '

||DECODE(ldg.name,NULL,'',ldg.name

||' - ')

||pet.element_name DISPLAY_TASK_ACTION_NAME,

MAX(PAY_TASK_ACTIONS.LEGISLATIVE_DATA_GROUP_ID) LEGISLATIVE_DATA_GROUP_ID

FROM PAY_TASK_ACTIONS,

PAY_TASK_USAGE,

PAY_TASK_PROPERTIES,

pay_task_actions_tl,

pay_element_types_vl pet,

per_legislative_data_groups_vl ldg

WHERE PAY_TASK_ACTIONS.BASE_TASK_ID = PAY_TASK_USAGE.BASE_TASK_ID

AND PAY_TASK_USAGE.USAGE_TYPE = 'BATCH_LOADER'

AND PAY_TASK_PROPERTIES.BASE_TASK_ACTION_ID = PAY_TASK_ACTIONS.BASE_TASK_ACTION_ID

AND PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE IN ('VIEW_NAME', 'SERVICE_OPERATION_NAME', 'SERVICE_OPERATION_NAME_SPACE', 'VO_FULL_NAME', 'VO_INSTANCE_NAME')

AND PAY_TASK_ACTIONS_TL.TASK_ACTION_ID = PAY_TASK_ACTIONS.TASK_ACTION_ID

AND PAY_TASK_ACTIONS_TL.LANGUAGE = USERENV('LANG')

AND pay_task_usage.base_task_usage_name = 'ELEMENT_ENTRY'

AND pet.LEGISLATIVE_DATA_GROUP_ID = ldg.LEGISLATIVE_DATA_GROUP_ID (+)

GROUP BY pet.element_type_id,

PAY_TASK_ACTIONS.BASE_TASK_ID,

PAY_TASK_ACTIONS_TL.ACTION_NAME

||' - '

||DECODE(ldg.name,NULL,'',ldg.name

||' - ')

||pet.element_name

UNION

SELECT pet.template_id*-1 TASK_ACTION_ID,

PAY_TASK_ACTIONS.BASE_TASK_ID TASK_ID,

MAX(DECODE(PAY_TASK_ACTIONS.ACTION_TYPE, 'SERVICE', 'SERVICE_OPERATION', 'TABLE_VIEW', 'VIEW_OBJECT', PAY_TASK_ACTIONS.ACTION_TYPE)) ACTION_TYPE,

MAX(DECODE(PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE, 'SERVICE_OPERATION_NAME_SPACE', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, 'VO_FULL_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, NULL)) ACTION_DETAILS,

MAX(DECODE(PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE, 'SERVICE_OPERATION_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, 'VO_INSTANCE_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, NULL)) TASK_ACTION_NAME,

PAY_TASK_ACTIONS_TL.ACTION_NAME

||' - '

||pet.template_name DISPLAY_TASK_ACTION_NAME,

MAX(PAY_TASK_ACTIONS.LEGISLATIVE_DATA_GROUP_ID) LEGISLATIVE_DATA_GROUP_ID

FROM PAY_TASK_ACTIONS,

PAY_TASK_USAGE,

PAY_TASK_PROPERTIES,

PAY_TASK_ACTIONS_TL,

pay_templates pet

WHERE PAY_TASK_ACTIONS.BASE_TASK_ID = PAY_TASK_USAGE.BASE_TASK_ID

AND PAY_TASK_USAGE.USAGE_TYPE = 'BATCH_LOADER'

AND PAY_TASK_PROPERTIES.BASE_TASK_ACTION_ID = PAY_TASK_ACTIONS.BASE_TASK_ACTION_ID

AND PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE IN ('VIEW_NAME', 'SERVICE_OPERATION_NAME', 'SERVICE_OPERATION_NAME_SPACE', 'VO_FULL_NAME', 'VO_INSTANCE_NAME')

AND PAY_TASK_ACTIONS_TL.TASK_ACTION_ID = PAY_TASK_ACTIONS.TASK_ACTION_ID

AND PAY_TASK_ACTIONS_TL.LANGUAGE = USERENV('LANG')

AND PAY_TASK_USAGE.base_task_usage_name = 'ELEMENT'

AND pet.template_type ='T'

AND pet.template_name IN ('Basic Element Template','Global Earnings','Global Pre-Statutory Deduction','Global Voluntary

Deduction','401K Deduction Template')

GROUP BY pet.template_id,

PAY_TASK_ACTIONS.BASE_TASK_ID,

PAY_TASK_ACTIONS_TL.ACTION_NAME

||' - '

||pet.template_name

UNION

SELECT pet.template_id TASK_ACTION_ID,

PAY_TASK_ACTIONS.BASE_TASK_ID TASK_ID,

MAX(DECODE(PAY_TASK_ACTIONS.ACTION_TYPE, 'SERVICE', 'SERVICE_OPERATION', 'TABLE_VIEW', 'VIEW_OBJECT', PAY_TASK_ACTIONS.ACTION_TYPE)) ACTION_TYPE,

MAX(DECODE(PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE, 'SERVICE_OPERATION_NAME_SPACE', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, 'VO_FULL_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, NULL)) ACTION_DETAILS,

MAX(DECODE(PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE, 'SERVICE_OPERATION_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, 'VO_INSTANCE_NAME', PAY_TASK_PROPERTIES.TASK_PROPERTY_VALUE, NULL)) TASK_ACTION_NAME,

PAY_TASK_ACTIONS_TL.ACTION_NAME

||' - '

||pet.template_name DISPLAY_TASK_ACTION_NAME,

MAX(PAY_TASK_ACTIONS.LEGISLATIVE_DATA_GROUP_ID) LEGISLATIVE_DATA_GROUP_ID

FROM PAY_TASK_ACTIONS,

PAY_TASK_USAGE,

PAY_TASK_PROPERTIES,

PAY_TASK_ACTIONS_TL,

pay_templates pet

WHERE PAY_TASK_ACTIONS.BASE_TASK_ID = PAY_TASK_USAGE.BASE_TASK_ID

AND PAY_TASK_USAGE.USAGE_TYPE = 'BATCH_LOADER'

AND PAY_TASK_PROPERTIES.BASE_TASK_ACTION_ID = PAY_TASK_ACTIONS.BASE_TASK_ACTION_ID

AND PAY_TASK_PROPERTIES.TASK_PROPERTY_TYPE IN ('VIEW_NAME', 'SERVICE_OPERATION_NAME', 'SERVICE_OPERATION_NAME_SPACE', 'VO_FULL_NAME', 'VO_INSTANCE_NAME')

AND PAY_TASK_ACTIONS_TL.TASK_ACTION_ID = PAY_TASK_ACTIONS.TASK_ACTION_ID

AND PAY_TASK_ACTIONS_TL.LANGUAGE = USERENV('LANG')

AND PAY_TASK_USAGE.base_task_usage_name = 'ELEMENT_TEMPLATE'

AND pet.template_type ='T'

AND pet.template_name NOT IN ('Create Legislation')

GROUP BY pet.template_id,

PAY_TASK_ACTIONS.BASE_TASK_ID,

PAY_TASK_ACTIONS_TL.ACTION_NAME

||' - '

||pet.template_name