VIEW: APPS.PAY_GB_NICABLE_V

Source

View Text - Preformatted

SELECT papf.first_name, papf.last_name, papf.employee_number, prrv1.result_value Benefit_Description, prrv2.result_value Amount, paa.assignment_action_id, paa.assignment_id, ppa.payroll_id, ppa.effective_date from pay_run_results prr, pay_run_result_values prrv1, pay_run_result_values prrv2, pay_element_types_f petf, pay_input_values_f pivf1, pay_input_values_f pivf2, per_all_people_f papf, per_all_assignments_f paaf, pay_assignment_actions paa, pay_payroll_actions ppa, pay_all_payrolls_f papayf, fnd_sessions fs Where fs.session_id = userenv('sessionid') and papayf.payroll_id = ppa.payroll_id and fs.effective_date between papayf.effective_start_date and papayf.effective_end_date and ppa.action_type IN ( 'Q','R','B','I') and ppa.payroll_action_id = paa.payroll_action_id and paa.action_status = 'C' and NOT EXISTS( SELECT '1' FROM pay_action_interlocks pai, pay_assignment_actions paa2, pay_payroll_actions pact3 WHERE pai.locked_action_id = paa.assignment_action_id AND pai.locking_action_id = paa2.assignment_action_id AND pact3.payroll_action_id = paa2.payroll_action_id AND pact3.action_type = 'V' AND paa2.action_status = 'C') and paa.assignment_id = paaf.assignment_id and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date and paaf.person_id = papf.person_id and ppa.effective_date between papf.effective_start_date and papf.effective_end_date and petf.element_name = 'NIC able Benefits' and fs.effective_date between petf.effective_start_date and petf.effective_end_date and petf.element_type_id = prr.element_type_id and paa.assignment_action_id = prr.assignment_action_id and prr.status IN ('P', 'PA') and pivf1.name = 'Benefit Description' and pivf1.element_type_id = petf.element_type_id and fs.effective_date between pivf1.effective_start_date and pivf1.effective_end_date and pivf2.name = 'Amount' and pivf2.element_type_id = petf.element_type_id and fs.effective_date between pivf2.effective_start_date and pivf2.effective_end_date and prrv1.input_value_id = pivf1.input_value_id and prrv1.run_result_id = prr.run_result_id and prrv2.input_value_id = pivf2.input_value_id and prrv2.run_result_id = prr.run_result_id

View Text - HTML Formatted

SELECT PAPF.FIRST_NAME
, PAPF.LAST_NAME
, PAPF.EMPLOYEE_NUMBER
, PRRV1.RESULT_VALUE BENEFIT_DESCRIPTION
, PRRV2.RESULT_VALUE AMOUNT
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.PAYROLL_ID
, PPA.EFFECTIVE_DATE
FROM PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV1
, PAY_RUN_RESULT_VALUES PRRV2
, PAY_ELEMENT_TYPES_F PETF
, PAY_INPUT_VALUES_F PIVF1
, PAY_INPUT_VALUES_F PIVF2
, PER_ALL_PEOPLE_F PAPF
, PER_ALL_ASSIGNMENTS_F PAAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ALL_PAYROLLS_F PAPAYF
, FND_SESSIONS FS
WHERE FS.SESSION_ID = USERENV('SESSIONID')
AND PAPAYF.PAYROLL_ID = PPA.PAYROLL_ID
AND FS.EFFECTIVE_DATE BETWEEN PAPAYF.EFFECTIVE_START_DATE
AND PAPAYF.EFFECTIVE_END_DATE
AND PPA.ACTION_TYPE IN ( 'Q'
, 'R'
, 'B'
, 'I')
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND NOT EXISTS( SELECT '1'
FROM PAY_ACTION_INTERLOCKS PAI
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PACT3
WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.LOCKING_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID
AND PACT3.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PACT3.ACTION_TYPE = 'V'
AND PAA2.ACTION_STATUS = 'C')
AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND PETF.ELEMENT_NAME = 'NIC ABLE BENEFITS'
AND FS.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PIVF1.NAME = 'BENEFIT DESCRIPTION'
AND PIVF1.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND FS.EFFECTIVE_DATE BETWEEN PIVF1.EFFECTIVE_START_DATE
AND PIVF1.EFFECTIVE_END_DATE
AND PIVF2.NAME = 'AMOUNT'
AND PIVF2.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND FS.EFFECTIVE_DATE BETWEEN PIVF2.EFFECTIVE_START_DATE
AND PIVF2.EFFECTIVE_END_DATE
AND PRRV1.INPUT_VALUE_ID = PIVF1.INPUT_VALUE_ID
AND PRRV1.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRRV2.INPUT_VALUE_ID = PIVF2.INPUT_VALUE_ID
AND PRRV2.RUN_RESULT_ID = PRR.RUN_RESULT_ID