PAY Gross To Net Summary Report (GB)

➜ Back to Library
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
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
Download
Related Blitz Reports
PAY Employee Run Results Report (CA) PAY Employee Run Results PAY Payroll Reconciliation Detail PAY Payroll Reconciliation (New Zealand) RKD Employee Element History PAY Employee Payroll History SSP ShPP Adoption Element Results SSP ShPP Birth Element Results PAY List Payroll Actions and Results By Person
Blitz Report™

Blitz Report™ provides multiple benefits: