VIEW: APPS.PAY_JP_SOE_ASSACTS_V

Source

View Text - Preformatted

SELECT paa.assignment_id, paa.assignment_action_id, paa.object_version_number, paa.action_status, hrl1.meaning, decode(paa.action_status,'M','Y','N'), paa.action_sequence, paa.payroll_action_id, ppa.request_id, ppa.start_date, ppa.effective_date, ppa.date_earned, ppa.action_type, hrl2.meaning, ppa.payroll_id, pp.payroll_name, ppa.consolidation_set_id, pcs.consolidation_set_name, ppa.element_set_id, pes.base_element_set_name, pes.element_set_name, ppa.backpay_set_id, pbs.backpay_set_name, ppa.assignment_set_id, has.assignment_set_name, ppa.org_payment_method_id, opmtl.org_payment_method_name, ppa.legislative_parameters, substrb(pay_jp_soe_pkg.messages_exist_flag(paa.assignment_action_id,'A'),1,1) from pay_org_payment_methods_f_tl opmtl, pay_payrolls_f pp, hr_assignment_sets has, pay_backpay_sets pbs, pay_element_sets_vl pes, pay_consolidation_sets pcs, pay_payroll_actions ppa, pay_assignment_actions paa, hr_lookups hrl2, hr_lookups hrl1 where hrl1.lookup_type = 'ACTION_STATUS' and hrl2.lookup_type = 'ACTION_TYPE' and hrl1.lookup_code = paa.action_status and ppa.payroll_action_id = paa.payroll_action_id and hrl2.lookup_code = ppa.action_type and pcs.consolidation_set_id(+) = ppa.consolidation_set_id and pes.element_set_id(+) = ppa.element_set_id and pbs.backpay_set_id(+) = ppa.backpay_set_id and has.assignment_set_id(+) = ppa.assignment_set_id and pp.payroll_id(+) = ppa.payroll_id and ppa.effective_date between pp.effective_start_date(+) and pp.effective_end_date(+) and opmtl.org_payment_method_id(+) = ppa.org_payment_method_id and opmtl.language(+) = userenv('LANG')

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.OBJECT_VERSION_NUMBER
, PAA.ACTION_STATUS
, HRL1.MEANING
, DECODE(PAA.ACTION_STATUS
, 'M'
, 'Y'
, 'N')
, PAA.ACTION_SEQUENCE
, PAA.PAYROLL_ACTION_ID
, PPA.REQUEST_ID
, PPA.START_DATE
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, PPA.ACTION_TYPE
, HRL2.MEANING
, PPA.PAYROLL_ID
, PP.PAYROLL_NAME
, PPA.CONSOLIDATION_SET_ID
, PCS.CONSOLIDATION_SET_NAME
, PPA.ELEMENT_SET_ID
, PES.BASE_ELEMENT_SET_NAME
, PES.ELEMENT_SET_NAME
, PPA.BACKPAY_SET_ID
, PBS.BACKPAY_SET_NAME
, PPA.ASSIGNMENT_SET_ID
, HAS.ASSIGNMENT_SET_NAME
, PPA.ORG_PAYMENT_METHOD_ID
, OPMTL.ORG_PAYMENT_METHOD_NAME
, PPA.LEGISLATIVE_PARAMETERS
, SUBSTRB(PAY_JP_SOE_PKG.MESSAGES_EXIST_FLAG(PAA.ASSIGNMENT_ACTION_ID
, 'A')
, 1
, 1)
FROM PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_PAYROLLS_F PP
, HR_ASSIGNMENT_SETS HAS
, PAY_BACKPAY_SETS PBS
, PAY_ELEMENT_SETS_VL PES
, PAY_CONSOLIDATION_SETS PCS
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, HR_LOOKUPS HRL2
, HR_LOOKUPS HRL1
WHERE HRL1.LOOKUP_TYPE = 'ACTION_STATUS'
AND HRL2.LOOKUP_TYPE = 'ACTION_TYPE'
AND HRL1.LOOKUP_CODE = PAA.ACTION_STATUS
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND HRL2.LOOKUP_CODE = PPA.ACTION_TYPE
AND PCS.CONSOLIDATION_SET_ID(+) = PPA.CONSOLIDATION_SET_ID
AND PES.ELEMENT_SET_ID(+) = PPA.ELEMENT_SET_ID
AND PBS.BACKPAY_SET_ID(+) = PPA.BACKPAY_SET_ID
AND HAS.ASSIGNMENT_SET_ID(+) = PPA.ASSIGNMENT_SET_ID
AND PP.PAYROLL_ID(+) = PPA.PAYROLL_ID
AND PPA.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE(+)
AND PP.EFFECTIVE_END_DATE(+)
AND OPMTL.ORG_PAYMENT_METHOD_ID(+) = PPA.ORG_PAYMENT_METHOD_ID
AND OPMTL.LANGUAGE(+) = USERENV('LANG')