ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4713751A9E05362FB09050512Y select /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_FK8, paa PAY_ASSIGNMENT_ACTIONS_N50, prr PAY_RUN_RESULTS_N50, piv PAY_INPUT_VALUES_F_UK2) USE_NL(ppa, paa, prr, piv, pet, pec, pcb, pbt) */ decode (pbt.BALANCE_TYPE_ID, :P_GROSS_PAY_ID, 1, :P_TOTAL_DEDUCTIONS_ID, 2, :P_DIRECT_PAYMENT_ID, 3, :P_EMPLOYER_CHARGES_ID, 4, :P_SMP_RECOVERY_AMT, 5, :P_SMP_NIC_COMP, 6, :P_SAP_RECOVERY_AMT, 7, :P_SAP_NIC_COMP, 8, :P_SPP_BIRTH_RECOVER, 9, :P_SPP_BIRTH_NIC_COMP, 10, :P_SPP_ADOP_RECOVER_AMT, 11, :P_SPP_ADOP_NIC_COMP, 12, :P_ASPP_BIRTH_REC_AMT, 13, :P_ASPP_BIRTH_NIC_COMP, 14, :P_ASPP_ADOPT_RECV_AMT, 15, :P_ASPP_ADOPT_NIC_COMP, 16, :P_SHPP_ADOPT_NIC_COMP, 17, :P_SHPP_ADOPT_RECV_AMT, 18, :P_SHPP_BIRTH_NIC_COMP, 19, :P_SHPP_BIRTH_RECV_AMT, 20, :P_SPBP_NIC_COMP, 21, :P_SPBP_RECOVERY_AMT, 22,23) Balance_Order ,pbt_tl.BALANCE_NAME Balance_Name ,pec_tl.CLASSIFICATION_NAME Classification ,nvl(substr(pet_tl.REPORTING_NAME,1,30),pet_tl.ELEMENT_NAME) Element_Name ,nvl(substr(pet_tl.REPORTING_NAME,1,30),substr(pet_tl.ELEMENT_NAME,1,40)) || rpad(' ', 39-length(nvl(substr(pet_tl.REPORTING_NAME,1,30),pet_tl.ELEMENT_NAME)), ' ') || rpad(' ',2,' ') || to_char(sum(to_number(prrv.RESULT_VALUE)),'99999999990.00') Element_Name_And_Value ,sum(to_number(prrv.RESULT_VALUE)) Sum_Values, PAY_PAYGBGTN_XMLP_PKG.cf_calculate_totals_formula(decode ( pbt.BALANCE_TYPE_ID , :P_GROSS_PAY_ID , 1 , :P_TOTAL_DEDUCTIONS_ID , 2 , :P_DIRECT_PAYMENT_ID , 3 , :P_EMPLOYER_CHARGES_ID , 4 , :P_SMP_RECOVERY_AMT, 5, :P_SMP_NIC_COMP, 6,:P_SAP_RECOVERY_AMT, 7,:P_SAP_NIC_COMP, 8,:P_SPP_BIRTH_RECOVER, 9,:P_SPP_BIRTH_NIC_COMP, 10,:P_SPP_ADOP_RECOVER_AMT, 11,:P_SPP_ADOP_NIC_COMP, 12,:P_ASPP_BIRTH_REC_AMT, 13,:P_ASPP_BIRTH_NIC_COMP, 14,:P_ASPP_ADOPT_RECV_AMT, 15,:P_ASPP_ADOPT_NIC_COMP, 16,:P_SHPP_ADOPT_NIC_COMP, 17,:P_SHPP_ADOPT_RECV_AMT, 18,:P_SHPP_BIRTH_NIC_COMP, 19,:P_SHPP_BIRTH_RECV_AMT, 20,:P_SPBP_NIC_COMP, 21,:P_SPBP_RECOVERY_AMT, 22, 23), :CS_Balance_Total) CF_Calculate_Totals from PAY_PAYROLL_ACTIONS ppa ,PAY_ASSIGNMENT_ACTIONS paa ,PAY_RUN_RESULTS prr ,PAY_INPUT_VALUES_F piv ,PAY_ELEMENT_TYPES_F pet ,PAY_ELEMENT_CLASSIFICATIONS pec ,PAY_BALANCE_CLASSIFICATIONS pcb ,PAY_BALANCE_TYPES pbt ,PAY_BALANCE_TYPES_TL pbt_tl ,PAY_ELEMENT_CLASSIFICATIONS_TL pec_tl ,PAY_ELEMENT_TYPES_F_TL pet_tl ,PAY_RUN_RESULT_VALUES prrv where paa.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID &P_CONSOLIDATION_SET_LINE and pbt.BALANCE_TYPE_ID in (:P_GROSS_PAY_ID, :P_TOTAL_DEDUCTIONS_ID, :P_DIRECT_PAYMENT_ID, :P_EMPLOYER_CHARGES_ID , :P_SMP_RECOVERY_AMT, :P_SMP_NIC_COMP,:P_SAP_RECOVERY_AMT,:P_SAP_NIC_COMP,:P_SPP_BIRTH_RECOVER,:P_SPP_BIRTH_NIC_COMP, :P_SPP_ADOP_RECOVER_AMT,:P_SPP_ADOP_NIC_COMP,:P_ASPP_BIRTH_REC_AMT,:P_ASPP_BIRTH_NIC_COMP,:P_ASPP_ADOPT_RECV_AMT,:P_ASPP_ADOPT_NIC_COMP,:P_SHPP_ADOPT_NIC_COMP,:P_SHPP_ADOPT_RECV_AMT,:P_SHPP_BIRTH_NIC_COMP,:P_SHPP_BIRTH_RECV_AMT,:P_SPBP_NIC_COMP,:P_SPBP_RECOVERY_AMT) and ppa.ACTION_STATUS = 'C' and ppa.ACTION_TYPE in ('Q', 'R','V') and ppa.TIME_PERIOD_ID = :P_TIME_PERIOD_ID and ppa.PAYROLL_ID = :P_PAYROLL_ID and ppa.BUSINESS_GROUP_ID+0 = :P_BUSINESS_GROUP_ID and pec.CLASSIFICATION_ID = pet.CLASSIFICATION_ID and pet.element_type_id = pet_tl.element_type_id and pet_tl.language = userenv('LANG') and pbt.balance_type_id = pbt_tl.balance_type_id and pbt_tl.language = userenv('LANG') and pbt.BALANCE_TYPE_ID = pcb.BALANCE_TYPE_ID and pcb.CLASSIFICATION_ID = pec.CLASSIFICATION_ID and pec.classification_id = pec_tl.classification_id and pec_tl.language = userenv('LANG') and pbt.LEGISLATION_CODE = :P_LEGISLATION_CODE and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID and prr.ELEMENT_TYPE_ID = piv.ELEMENT_TYPE_ID and prr.STATUS in ('P', 'PA') and piv.NAME = 'Pay Value' and piv.UOM = 'M' and ppa.EFFECTIVE_DATE between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE and ppa.EFFECTIVE_DATE between piv.EFFECTIVE_START_DATE and piv.EFFECTIVE_END_DATE and piv.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID and prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID and prrv.RESULT_VALUE is not NULL and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID group by pbt_tl.BALANCE_NAME ,pec_tl.CLASSIFICATION_NAME ,nvl(substr(pet_tl.REPORTING_NAME,1,30),pet_tl.ELEMENT_NAME) ,nvl(substr(pet_tl.REPORTING_NAME,1,30),substr(pet_tl.ELEMENT_NAME,1,40)) || rpad(' ', 39-length(nvl(substr(pet_tl.REPORTING_NAME,1,30),pet_tl.ELEMENT_NAME)), ' ') , decode (pbt.BALANCE_TYPE_ID, :P_GROSS_PAY_ID, 1, :P_TOTAL_DEDUCTIONS_ID, 2, :P_DIRECT_PAYMENT_ID, 3, :P_EMPLOYER_CHARGES_ID, 4, :P_SMP_RECOVERY_AMT, 5, :P_SMP_NIC_COMP, 6, :P_SAP_RECOVERY_AMT, 7, :P_SAP_NIC_COMP, 8, :P_SPP_BIRTH_RECOVER, 9, :P_SPP_BIRTH_NIC_COMP, 10, :P_SPP_ADOP_RECOVER_AMT, 11, :P_SPP_ADOP_NIC_COMP, 12, :P_ASPP_BIRTH_REC_AMT, 13, :P_ASPP_BIRTH_NIC_COMP, 14, :P_ASPP_ADOPT_RECV_AMT, 15, :P_ASPP_ADOPT_NIC_COMP, 16, :P_SHPP_ADOPT_NIC_COMP, 17, :P_SHPP_ADOPT_RECV_AMT, 18, :P_SHPP_BIRTH_NIC_COMP, 19, :P_SHPP_BIRTH_RECV_AMT, 20, :P_SPBP_NIC_COMP, 21, :P_SPBP_RECOVERY_AMT, 22,23) union select decode (pbt.BALANCE_TYPE_ID, :P_GROSS_PAY_ID, 1, :P_TOTAL_DEDUCTIONS_ID, 2, :P_DIRECT_PAYMENT_ID, 3, :P_EMPLOYER_CHARGES_ID, 4, :P_SMP_RECOVERY_AMT, 5, :P_SMP_NIC_COMP, 6, :P_SAP_RECOVERY_AMT, 7, :P_SAP_NIC_COMP, 8, :P_SPP_BIRTH_RECOVER, 9, :P_SPP_BIRTH_NIC_COMP, 10, :P_SPP_ADOP_RECOVER_AMT, 11, :P_SPP_ADOP_NIC_COMP, 12, :P_ASPP_BIRTH_REC_AMT, 13, :P_ASPP_BIRTH_NIC_COMP, 14, :P_ASPP_ADOPT_RECV_AMT, 15, :P_ASPP_ADOPT_NIC_COMP, 16, :P_SHPP_ADOPT_NIC_COMP, 17, :P_SHPP_ADOPT_RECV_AMT, 18, :P_SHPP_BIRTH_NIC_COMP, 19, :P_SHPP_BIRTH_RECV_AMT, 20, :P_SPBP_NIC_COMP, 21, :P_SPBP_RECOVERY_AMT, 22,23) Balance_Order ,pbt.BALANCE_NAME Balance_Name ,NULL ,NULL ,NULL ,0 Sum_Values, PAY_PAYGBGTN_XMLP_PKG.cf_calculate_totals_formula(decode ( pbt.BALANCE_TYPE_ID , :P_GROSS_PAY_ID , 1 , :P_TOTAL_DEDUCTIONS_ID , 2 , :P_DIRECT_PAYMENT_ID , 3 , :P_EMPLOYER_CHARGES_ID , 4 , :P_SMP_RECOVERY_AMT, 5, :P_SMP_NIC_COMP, 6,:P_SAP_RECOVERY_AMT, 7,:P_SAP_NIC_COMP, 8,:P_SPP_BIRTH_RECOVER, 9,:P_SPP_BIRTH_NIC_COMP, 10,:P_SPP_ADOP_RECOVER_AMT, 11,:P_SPP_ADOP_NIC_COMP, 12,:P_ASPP_BIRTH_REC_AMT, 13,:P_ASPP_BIRTH_NIC_COMP, 14,:P_ASPP_ADOPT_RECV_AMT, 15,:P_ASPP_ADOPT_NIC_COMP, 16,:P_SHPP_ADOPT_NIC_COMP, 17,:P_SHPP_ADOPT_RECV_AMT, 18,:P_SHPP_BIRTH_NIC_COMP, 19,:P_SHPP_BIRTH_RECV_AMT, 20,:P_SPBP_NIC_COMP, 21,:P_SPBP_RECOVERY_AMT, 22, 23), :CS_Balance_Total) CF_Calculate_Totals from pay_balance_types pbt where pbt.balance_type_id in (:P_GROSS_PAY_ID, :P_TOTAL_DEDUCTIONS_ID, :P_DIRECT_PAYMENT_ID, :P_EMPLOYER_CHARGES_ID , :P_SMP_RECOVERY_AMT, :P_SMP_NIC_COMP,:P_SAP_RECOVERY_AMT,:P_SAP_NIC_COMP,:P_SPP_BIRTH_RECOVER,:P_SPP_BIRTH_NIC_COMP, :P_SPP_ADOP_RECOVER_AMT,:P_SPP_ADOP_NIC_COMP,:P_ASPP_BIRTH_REC_AMT,:P_ASPP_BIRTH_NIC_COMP,:P_ASPP_ADOPT_RECV_AMT,:P_ASPP_ADOPT_NIC_COMP,:P_SHPP_ADOPT_NIC_COMP,:P_SHPP_ADOPT_RECV_AMT,:P_SHPP_BIRTH_NIC_COMP,:P_SHPP_BIRTH_RECV_AMT,:P_SPBP_NIC_COMP,:P_SPBP_RECOVERY_AMT) order by 1, 3, 5, 4, 6 PAY PAYGBGTN_XML US PAY Gross To Net Summary Report (GB) Imported from BI Publisher Description: Gross To Net Summary Report (GB) Application: Payroll Source: Gross To Net Summary Report (GB) (XML) Short Name: PAYGBGTN_XML DB package: PAY_PAYGBGTN_XMLP_PKG BI Publisher &p_consolidation_set_line :cp_business_group_name :cp_consolidation_set_name :cp_payroll_name :cp_time_period_time :cs_balance_total :p_aspp_adopt_nic_comp :p_aspp_adopt_recv_amt :p_aspp_birth_nic_comp :p_aspp_birth_rec_amt :p_business_group :p_business_group_id :p_conc_request_id :p_consolidation_set_id :p_consolidation_set_line :p_direct_payment_id :p_employer_charges_id :p_gross_pay_id :p_legislation_code :p_payroll_id :p_sap_nic_comp :p_sap_recovery_amt :p_session_date :p_shpp_adopt_nic_comp :p_shpp_adopt_recv_amt :p_shpp_birth_nic_comp :p_shpp_birth_recv_amt :p_smp_nic_comp :p_smp_recovery_amt :p_spbp_nic_comp :p_spbp_recovery_amt :p_spp_adop_nic_comp :p_spp_adop_recover_amt :p_spp_birth_nic_comp :p_spp_birth_recover :p_time_period_id :p_total_deductions_id 1 -60 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Id Business Group ID 2 -50 :p_gross_pay_id Number select balance_type_id from pay_balance_types where balance_name = 'Gross Pay' and legislation_code = 'GB' US Gross Pay ID Gross Pay ID 3 -40 :p_total_deductions_id Number select balance_type_id from pay_balance_types where balance_name = 'Total Deductions' and legislation_code = 'GB' US Total Deductions ID Total Deductions ID 4 -30 :p_direct_payment_id Number select balance_type_id from pay_balance_types where balance_name = 'Total Direct Payments' and legislation_code = 'GB' US Direct Payment ID Direct Payment ID 5 -20 :p_employer_charges_id Number select balance_type_id from pay_balance_types where balance_name = 'Total Employer Charges' and legislation_code = 'GB' US Employer Charges ID Total Employer Charges ID 6 10 :p_payroll_id LOV Oracle PY_SRS_PAYROLL Y select pay.payroll_id id, pay.payroll_name value, null description from pay_payrolls_f pay,fnd_sessions fnd where pay.business_group_id= fnd_global.per_business_group_id and fnd.session_id=userenv('sessionid') and fnd.effective_date between pay.effective_start_date and pay.effective_end_date order by pay.payroll_name Y US Payroll Payroll Name 7 20 :p_time_period_id LOV Oracle PY_SRS_PAY_PERIOD_ID Y select time_period_id id, period_name value, null description from per_time_periods p,fnd_sessions f where p.payroll_id=:$flex$.py_srs_payroll and p.start_date<=f.effective_date and f.session_id= userenv('sessionid') order by p.start_date desc select p.period_name from per_time_periods p,fnd_sessions f where p.payroll_id = :$flex$.payroll and f.session_id = userenv('sessionid') and f.effective_date between p.start_date and p.end_date Y US Period Payroll Period Name 8 30 :p_consolidation_set_id LOV Oracle PY_SRS_CONSOLIDATION_SET_PAYROLL Y select con.consolidation_set_id id, con.consolidation_set_name value, null description from pay_consolidation_sets con where con.business_group_id =fnd_global.per_business_group_id and exists ( select 1 from pay_payroll_actions pact, per_time_periods ptp where pact.consolidation_set_id=con.consolidation_set_id and ptp.time_period_id=:$flex$.py_srs_pay_period_id and pact.payroll_id=:$flex$.py_srs_payroll and pact.effective_date between ptp.start_date and ptp.end_date and pact.action_type in ('R','Q','P','U','V') ) order by con.consolidation_set_name US Consolidation Set Consolidation Set Name