PAY Gross To Net Summary Report (GB)
Description
Categories: BI Publisher
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
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
Run
PAY Gross To Net Summary Report (GB) and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Payroll |
|
LOV Oracle | |
Period |
|
LOV Oracle | |
Consolidation Set |
|
LOV Oracle |