VIEW: APPS.PAYBV_PAYROLL_ACT_RR_ENET_DET

Source

View Text - Preformatted

SELECT pbg.name Business_group_name ,pbg.business_group_id Business_group_id ,ppf.person_id Person_id ,ppf.full_name Employee_name ,ppf.first_name First_name ,ppf.last_name Last_name ,ppf.middle_names Middle_name ,ppf.employee_number Employee_number ,paf.assignment_id Assignment_id ,paf.assignment_number Assignment_number ,pg.name Grade_name ,pp.name Position_name ,pj.name Job_name ,ppof.payroll_name Payroll_name ,ppof.payroll_id Payroll_id ,hou.name HR_organization_name ,paa.assignment_action_id Assignment_action_id ,ppa.payroll_action_id Payroll_action_id ,ppa.date_earned Date_earned ,ppa.effective_date Effective_date ,nvl(prt.run_type_name,prt1.run_type_name) Run_type ,prt1.run_type_name Run_component ,pec.classification_name Classification_name_code ,pectl.classification_name Classification_name ,to_char(pec.default_priority) Class_default_priority ,pettl.element_name Element_name ,nvl(pettl.reporting_name,pettl.element_name) Reporting_name ,pivtl.name Input_value_name ,piv.uom Unit_of_measure ,prrv.result_value Result_Value ,pbg.legislation_code Legislation_code ,CASE WHEN ppa.business_group_id = nvl(hr_bis.get_sec_profile_bg_id,ppa.business_group_id) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE FROM per_assignments_f paf ,per_people_f ppf ,pay_element_types_f pet ,pay_element_types_f_tl pettl ,pay_input_values_f piv ,pay_input_values_f_tl pivtl ,pay_assignment_actions paa ,pay_run_results prr ,pay_run_result_values prrv ,pay_run_types_f prt ,pay_run_types_f prt1 ,pay_payrolls_f ppof ,per_periods_of_service ppos ,pay_element_classifications pec ,pay_element_classifications_tl pectl ,pay_payroll_actions ppa ,per_business_groups pbg ,hr_organization_units hou ,per_grades pg ,per_jobs pj ,per_positions pp WHERE pbg.business_group_id = ppf.business_group_id AND hou.organization_id = paf.organization_id AND ppf.person_id = paf.person_id and pec.classification_id = pectl.classification_id and pectl.language = userenv('LANG') AND ppa.effective_date between ppof.effective_start_date and ppof.effective_end_date AND ppa.effective_date between paf.effective_start_date and paf.effective_end_date AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date AND nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date and pet.effective_end_date AND nvl(ppa.date_earned,ppa.effective_date) between piv.effective_start_date and piv.effective_end_date AND ppa.effective_date between nvl(prt.effective_start_date,ppa.effective_date) and nvl(prt.effective_end_date,ppa.effective_date) AND ppa.effective_date between nvl(prt1.effective_start_date,ppa.effective_date) and nvl(prt1.effective_end_date,ppa.effective_date) AND paf.period_of_service_id = ppos.period_of_service_id AND ppf.person_id = ppos.person_id AND pg.grade_id (+)= paf.grade_id AND pp.position_id (+)= paf.position_id AND pj.job_id (+)= paf.job_id AND (pet.business_group_id = paf.business_group_id or pet.legislation_code = pbg.legislation_code or (pet.business_group_id is null and pet.legislation_code is null)) AND pec.classification_id = pet.classification_id AND ppof.payroll_id = paf.payroll_id AND piv.element_type_id = pet.element_type_id AND pettl.language = userenv('LANG') AND pettl.element_type_id = pet.element_type_id AND paf.assignment_id = paa.assignment_id AND prr.assignment_action_id = paa.assignment_action_id AND ppa.payroll_action_id = paa.payroll_action_id AND prr.element_type_id = pet.element_type_id AND prr.status IN ('P', 'PA') AND ppa.action_type IN ('Q','R','B','V') AND prr.run_result_id = prrv.run_result_id AND prrv.input_value_id = piv.input_value_id AND prt.run_type_id (+) = ppa.run_type_id AND prt1.run_type_id (+)= paa.run_type_id AND pivtl.language = userenv('LANG') AND pivtl.input_value_id = piv.input_value_id UNION ALL SELECT pbg.name Business_group_name ,pbg.business_group_id Business_group_id ,ppf.person_id Person_id ,ppf.full_name Employee_name ,ppf.first_name First_name ,ppf.last_name Last_name ,ppf.middle_names Middle_name ,ppf.employee_number Employee_number ,paf.assignment_id Assignment_id ,paf.assignment_number Assignment_number ,pg.name Grade_name ,pp.name Position_name ,pj.name Job_name ,ppof.payroll_name Payroll_name ,ppof.payroll_id Payroll_id ,hou.name HR_organization_name ,paa.assignment_action_id Assignment_action_id ,ppa.payroll_action_id Payroll_action_id ,ppa.date_earned Date_earned ,ppa.effective_date Effective_date ,nvl(prt.run_type_name,prt1.run_type_name) Run_type ,prt1.run_type_name Run_component ,pbt.BALANCE_NAME_CODE Classification_name_code ,pbt.BALANCE_NAME Classification_name ,to_char(50000) Class_default_priority ,pbt.BALANCE_NAME Element_name ,pbt.BALANCE_NAME Reporting_name ,hr_general.decode_lookup('NAME_TRANSLATIONS','PAY VALUE') Input_value_name ,'M' Unit_of_measure ,to_char(pay_in_tax_utils.get_balance_value( paa.assignment_action_id ,pbt.BALANCE_NAME_CODE ,'_ASG_RUN' ,'NULL' ,'NULL' )) Result_Value ,pbg.legislation_code Legislation_code ,CASE WHEN ppa.business_group_id = nvl(hr_bis.get_sec_profile_bg_id,ppa.business_group_id) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE FROM per_assignments_f paf ,per_people_f ppf ,pay_assignment_actions paa ,pay_assignment_actions paa2 ,pay_payrolls_f ppof ,per_periods_of_service ppos ,pay_payroll_actions ppa ,pay_run_types_f prt ,pay_run_types_f prt1 ,per_business_groups pbg ,hr_organization_units hou ,per_grades pg ,per_jobs pj ,per_positions pp ,(select pbt.BALANCE_NAME BALANCE_NAME_CODE,pbtl.BALANCE_NAME,pbt.LEGISLATION_CODE from pay_balance_types pbt, pay_balance_types_tl pbtl where pbt.ASSIGNMENT_REMUNERATION_FLAG = 'Y' and pbtl.BALANCE_TYPE_ID = pbt.BALANCE_TYPE_ID and pbtl.language = userenv('LANG')) pbt WHERE pbg.business_group_id = ppf.business_group_id AND hou.organization_id = paf.organization_id AND ppf.person_id = paf.person_id and pbt.legislation_code = pbg.legislation_code AND ppa.effective_date between ppof.effective_start_date and ppof.effective_end_date AND ppa.effective_date between paf.effective_start_date and paf.effective_end_date AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date AND ppa.effective_date between nvl(prt.effective_start_date,ppa.effective_date) and nvl(prt.effective_end_date,ppa.effective_date) AND ppa.effective_date between nvl(prt1.effective_start_date,ppa.effective_date) and nvl(prt1.effective_end_date,ppa.effective_date) AND paf.period_of_service_id = ppos.period_of_service_id AND ppf.person_id = ppos.person_id AND pg.grade_id (+)= paf.grade_id AND pp.position_id (+)= paf.position_id AND pj.job_id (+)= paf.job_id AND ppof.payroll_id = paf.payroll_id AND paf.assignment_id = paa.assignment_id AND ppa.payroll_action_id = paa.payroll_action_id AND ppa.action_type IN ('Q','R','B','V') AND prt.run_type_id (+) = ppa.run_type_id AND prt1.run_type_id (+)= paa.run_type_id AND paa.source_action_id = paa2.assignment_action_id and paa2.source_action_id IS NULL WITH READ ONLY

View Text - HTML Formatted

SELECT PBG.NAME BUSINESS_GROUP_NAME
, PBG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPF.PERSON_ID PERSON_ID
, PPF.FULL_NAME EMPLOYEE_NAME
, PPF.FIRST_NAME FIRST_NAME
, PPF.LAST_NAME LAST_NAME
, PPF.MIDDLE_NAMES MIDDLE_NAME
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PG.NAME GRADE_NAME
, PP.NAME POSITION_NAME
, PJ.NAME JOB_NAME
, PPOF.PAYROLL_NAME PAYROLL_NAME
, PPOF.PAYROLL_ID PAYROLL_ID
, HOU.NAME HR_ORGANIZATION_NAME
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PPA.DATE_EARNED DATE_EARNED
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, NVL(PRT.RUN_TYPE_NAME
, PRT1.RUN_TYPE_NAME) RUN_TYPE
, PRT1.RUN_TYPE_NAME RUN_COMPONENT
, PEC.CLASSIFICATION_NAME CLASSIFICATION_NAME_CODE
, PECTL.CLASSIFICATION_NAME CLASSIFICATION_NAME
, TO_CHAR(PEC.DEFAULT_PRIORITY) CLASS_DEFAULT_PRIORITY
, PETTL.ELEMENT_NAME ELEMENT_NAME
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORTING_NAME
, PIVTL.NAME INPUT_VALUE_NAME
, PIV.UOM UNIT_OF_MEASURE
, PRRV.RESULT_VALUE RESULT_VALUE
, PBG.LEGISLATION_CODE LEGISLATION_CODE
, CASE WHEN PPA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PPA.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPF
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_INPUT_VALUES_F PIV
, PAY_INPUT_VALUES_F_TL PIVTL
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_TYPES_F PRT
, PAY_RUN_TYPES_F PRT1
, PAY_PAYROLLS_F PPOF
, PER_PERIODS_OF_SERVICE PPOS
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_PAYROLL_ACTIONS PPA
, PER_BUSINESS_GROUPS PBG
, HR_ORGANIZATION_UNITS HOU
, PER_GRADES PG
, PER_JOBS PJ
, PER_POSITIONS PP
WHERE PBG.BUSINESS_GROUP_ID = PPF.BUSINESS_GROUP_ID
AND HOU.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND PPF.PERSON_ID = PAF.PERSON_ID
AND PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV('LANG')
AND PPA.EFFECTIVE_DATE BETWEEN PPOF.EFFECTIVE_START_DATE
AND PPOF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN NVL(PRT.EFFECTIVE_START_DATE
, PPA.EFFECTIVE_DATE)
AND NVL(PRT.EFFECTIVE_END_DATE
, PPA.EFFECTIVE_DATE)
AND PPA.EFFECTIVE_DATE BETWEEN NVL(PRT1.EFFECTIVE_START_DATE
, PPA.EFFECTIVE_DATE)
AND NVL(PRT1.EFFECTIVE_END_DATE
, PPA.EFFECTIVE_DATE)
AND PAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PPF.PERSON_ID = PPOS.PERSON_ID
AND PG.GRADE_ID (+)= PAF.GRADE_ID
AND PP.POSITION_ID (+)= PAF.POSITION_ID
AND PJ.JOB_ID (+)= PAF.JOB_ID
AND (PET.BUSINESS_GROUP_ID = PAF.BUSINESS_GROUP_ID OR PET.LEGISLATION_CODE = PBG.LEGISLATION_CODE OR (PET.BUSINESS_GROUP_ID IS NULL
AND PET.LEGISLATION_CODE IS NULL))
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PPOF.PAYROLL_ID = PAF.PAYROLL_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PETTL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'B'
, 'V')
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRT.RUN_TYPE_ID (+) = PPA.RUN_TYPE_ID
AND PRT1.RUN_TYPE_ID (+)= PAA.RUN_TYPE_ID
AND PIVTL.LANGUAGE = USERENV('LANG')
AND PIVTL.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID UNION ALL SELECT PBG.NAME BUSINESS_GROUP_NAME
, PBG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPF.PERSON_ID PERSON_ID
, PPF.FULL_NAME EMPLOYEE_NAME
, PPF.FIRST_NAME FIRST_NAME
, PPF.LAST_NAME LAST_NAME
, PPF.MIDDLE_NAMES MIDDLE_NAME
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PG.NAME GRADE_NAME
, PP.NAME POSITION_NAME
, PJ.NAME JOB_NAME
, PPOF.PAYROLL_NAME PAYROLL_NAME
, PPOF.PAYROLL_ID PAYROLL_ID
, HOU.NAME HR_ORGANIZATION_NAME
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PPA.DATE_EARNED DATE_EARNED
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, NVL(PRT.RUN_TYPE_NAME
, PRT1.RUN_TYPE_NAME) RUN_TYPE
, PRT1.RUN_TYPE_NAME RUN_COMPONENT
, PBT.BALANCE_NAME_CODE CLASSIFICATION_NAME_CODE
, PBT.BALANCE_NAME CLASSIFICATION_NAME
, TO_CHAR(50000) CLASS_DEFAULT_PRIORITY
, PBT.BALANCE_NAME ELEMENT_NAME
, PBT.BALANCE_NAME REPORTING_NAME
, HR_GENERAL.DECODE_LOOKUP('NAME_TRANSLATIONS'
, 'PAY VALUE') INPUT_VALUE_NAME
, 'M' UNIT_OF_MEASURE
, TO_CHAR(PAY_IN_TAX_UTILS.GET_BALANCE_VALUE( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_NAME_CODE
, '_ASG_RUN'
, 'NULL'
, 'NULL' )) RESULT_VALUE
, PBG.LEGISLATION_CODE LEGISLATION_CODE
, CASE WHEN PPA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PPA.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLLS_F PPOF
, PER_PERIODS_OF_SERVICE PPOS
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_TYPES_F PRT
, PAY_RUN_TYPES_F PRT1
, PER_BUSINESS_GROUPS PBG
, HR_ORGANIZATION_UNITS HOU
, PER_GRADES PG
, PER_JOBS PJ
, PER_POSITIONS PP
, (SELECT PBT.BALANCE_NAME BALANCE_NAME_CODE
, PBTL.BALANCE_NAME
, PBT.LEGISLATION_CODE
FROM PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTL
WHERE PBT.ASSIGNMENT_REMUNERATION_FLAG = 'Y'
AND PBTL.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PBTL.LANGUAGE = USERENV('LANG')) PBT
WHERE PBG.BUSINESS_GROUP_ID = PPF.BUSINESS_GROUP_ID
AND HOU.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND PPF.PERSON_ID = PAF.PERSON_ID
AND PBT.LEGISLATION_CODE = PBG.LEGISLATION_CODE
AND PPA.EFFECTIVE_DATE BETWEEN PPOF.EFFECTIVE_START_DATE
AND PPOF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN NVL(PRT.EFFECTIVE_START_DATE
, PPA.EFFECTIVE_DATE)
AND NVL(PRT.EFFECTIVE_END_DATE
, PPA.EFFECTIVE_DATE)
AND PPA.EFFECTIVE_DATE BETWEEN NVL(PRT1.EFFECTIVE_START_DATE
, PPA.EFFECTIVE_DATE)
AND NVL(PRT1.EFFECTIVE_END_DATE
, PPA.EFFECTIVE_DATE)
AND PAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PPF.PERSON_ID = PPOS.PERSON_ID
AND PG.GRADE_ID (+)= PAF.GRADE_ID
AND PP.POSITION_ID (+)= PAF.POSITION_ID
AND PJ.JOB_ID (+)= PAF.JOB_ID
AND PPOF.PAYROLL_ID = PAF.PAYROLL_ID
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'B'
, 'V')
AND PRT.RUN_TYPE_ID (+) = PPA.RUN_TYPE_ID
AND PRT1.RUN_TYPE_ID (+)= PAA.RUN_TYPE_ID
AND PAA.SOURCE_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID
AND PAA2.SOURCE_ACTION_ID IS NULL WITH READ ONLY