PAY IR8S Summary
Description
- Categories
- BI Publisher
- Application
- Payroll
- Source
- IR8S Summary Report
- Short Name
- PYSGIR8S_XML
DB package:
SQL 95 lines
select paa.assignment_id, ff1.value employee_name, to_char(fnd_date.canonical_to_date(ac3.context),'MON') month, sum(nvl(decode(fue.user_entity_name, 'X_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_400, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_401, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_402, sum(nvl(decode(fue.user_entity_name, 'X_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_404, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_405, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_406, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) CPF_LIAB, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) VOL_CPF_LIAB, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) CPF_WITH, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) VOL_CPF_WITH from ff_user_entities fue, ff_archive_items ff, ff_user_entities fue1, ff_archive_items ff1, pay_assignment_actions paa, per_all_assignments_f paaf, ff_archive_item_contexts ac3 where fue.user_Entity_id= ff.user_entity_id and fue.user_entity_name in ('X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH', 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH', 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH', 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH', 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH', 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH', 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH', 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH', 'X_ORDINARY_EARNINGS_PER_LE_MONTH', 'X_ADDITIONAL_EARNINGS_PER_LE_MONTH') and ff.context1=paa.assignment_action_id and fue1.user_entity_id=ff1.user_entity_id and fue1.user_entity_name = 'X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME' and ff1.context1=paa.assignment_action_id and ff.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3 and paa.payroll_action_id=:p_archive_action_id and paa.assignment_id = paaf.assignment_id and paa.assignment_id in ( select assignment_id from (select paa.assignment_id, ff1.value employee_name, sum(nvl(decode(fue.user_entity_name, 'X_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_400, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_401_1, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_401_2, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_401, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_402_1, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_402_2, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_402, sum(nvl(decode(fue.user_entity_name, 'X_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_404, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_405_1, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_405_2, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_405, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_406_1, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_406_2, sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_406, sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_349_PER_LE_YTD',ff.value),0)) MOA_408, sum(nvl(decode(fue.user_entity_name, 'X_IR8S_MOA_409_PER_LE_YTD',ff.value),0)) MOA_409, sum(nvl(decode(fue.user_entity_name, 'X_MOA410',ff.value),0)) MOA_410, sum(nvl(decode(fue.user_entity_name, 'X_MOA411',ff.value),0)) MOA_411, sum(nvl(decode(fue.user_entity_name, 'X_MOA412',ff.value),0)) MOA_412, sum(nvl(decode(fue.user_entity_name, 'X_MOA413',ff.value),0)) MOA_413, sum(nvl(decode(fue.user_entity_name, 'X_MOA414',ff.value),0)) MOA_414 from ff_user_entities fue, ff_archive_items ff, ff_user_entities fue1, ff_archive_items ff1, ff_user_entities fue2, ff_archive_items ff2, pay_assignment_actions paa where fue.user_Entity_id= ff.user_entity_id and fue.user_entity_name in ('X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD', 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD', 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD', 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD', 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD', 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD', 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD', 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD', 'X_ORDINARY_EARNINGS_PER_LE_YTD', 'X_ADDITIONAL_EARNINGS_PER_LE_YTD', 'X_IR8A_MOA_349_PER_LE_YTD', 'X_IR8S_MOA_409_PER_LE_YTD', 'X_MOA410','X_MOA411','X_MOA412','X_MOA413','X_MOA414' ) and ff.context1=paa.assignment_action_id and fue1.user_entity_id=ff1.user_entity_id and fue1.user_entity_name = 'X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME' and ff1.context1=paa.assignment_action_id and fue2.user_entity_id=ff2.user_entity_id and fue2.user_entity_name = 'X_HR_IR8A_INDICATORS_SG_PER_IR8S_APPLICABLE' and ff2.context1=paa.assignment_action_id and paa.payroll_action_id=:p_archive_action_id and decode(:p_assignment_set_id,null,'Y',decode(hr_assignment_set.ASSIGNMENT_IN_SET(:p_assignment_set_id,paa.assignment_id),'Y','Y','N')) = 'Y' and paa.assignment_id like decode(:p_assignment_id,null,'%',:p_assignment_id) and nvl(ff2.value,'N')='Y' group by paa.assignment_id, ff1.value) where (moa_400<>0 or moa_401<>0 or moa_402<>0 or moa_404<>0 or moa_405<>0 or moa_406<>0 or moa_408<>0 or moa_409<>0 or moa_410<>0)) group by paa.assignment_id, paaf.person_id, ff1.value, ac3.context order by paaf.person_id |