PAY Gross To Net Summary Report (GB)

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: Gross To Net Summary Report (GB) (XML)
Short Name: PAYGBGTN_XML
DB package: PAY_PAYGBGTN_XMLP_PKG
            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, 21) 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,21), :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)
            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, 21)
            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, 21) 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,21), :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)
            order by 1, 3, 5, 4, 6
Parameter Name SQL text Validation
Employer Charges ID
 
Number
Direct Payment ID
 
Number
Total Deductions ID
 
Number
Gross Pay ID
 
Number
Business Group Id
 
Number
Consolidation Set
 
LOV Oracle
Period
 
LOV Oracle
Payroll
 
LOV Oracle