PAY Gross To Net Summary Report (GB)

            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