PSP Reconciliation between sublines and distribution lines

Description
Categories: BI Publisher, Human Resources
Application: Labor Distribution
Source: PSP: Reconciliation between sublines and distribution lines (XML)
Short Name: PSPRCSLD_XML
DB package: PSP_PSPRCSLD_XMLP_PKG
SELECT  
sum(DECODE(ppl.dr_cr_flag, 'C', pdl.distribution_amount, 0)) DL_Credit_Amount,
		sum(DECODE(ppl.dr_cr_flag, 'D', pdl.distribution_amount, 0)) DL_Debit_Amount,
		to_char(min(pdl.distribution_date), 'DD-MON-YYYY') CI_Begin_date,
		to_char(max(pdl.distribution_date),'DD-MON-YYYY') CI_End_Date,
		DECODE(pdl.gl_project_flag, 'G', DECODE(pdl.suspense_org_Account_id, NULL,
						nvl(pdl.auto_gl_code_combination_id,
                                                                                                nvl(pdl.cap_excess_glccid,
						nvl(psl.gl_code_combination_id,
              					nvl(poa.gl_code_combination_id,
              					nvl(peta.gl_code_combination_id,
                  			              pdls.gl_code_combination_id))))),
                                                                          nvl(pdl.suspense_auto_glccid, poa2.gl_code_combination_id)),
					NULL) glccid,
	DECODE(pdl.gl_project_flag, 'P', DECODE(pdl.suspense_org_account_id, NULL,
                                                                               nvl(pdl.cap_excess_project_id,
              				     	nvl(psl.project_id,
              					nvl(poa.project_id,
              					nvl(peta.project_id,
                  				pdls.project_id)))),poa2.project_id),
					NULL) project_id,
	DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
                                                                                nvl(pdl.cap_excess_task_id,
              					nvl(psl.task_id,
              					nvl(poa.task_id,
              					nvl(peta.task_id,
                  				pdls.task_id)))),poa2.task_id),
					NULL) task_id,
	DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
                                                                                nvl(pdl.cap_excess_award_id,
              					nvl(psl.award_id,
              					nvl(poa.award_id,
             					nvl(peta.award_id,
                  				pdls.award_id)))),poa2.award_id),
					NULL) award_id,
	DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
						nvl(pdl.auto_expenditure_type,
                                                                                nvl(pdl.cap_excess_exp_type,
						nvl(psl.expenditure_type,
                    				nvl(poa.expenditure_type,
                        				nvl(peta.expenditure_type,
                              			pdls.expenditure_type))))), 
                                                                nvl(pdl.suspense_auto_exp_type,poa2.expenditure_type)),
					NULL) expenditure_type,
	DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
                                                                                nvl(pdl.cap_excess_exp_org_id,
              					nvl(psl.expenditure_organization_id,
                                                                                nvl(poa.expenditure_organization_id,
              					nvl(peta.expenditure_organization_id,
                  				pdls.expenditure_organization_id)))),poa2.expenditure_organization_id),
					NULL) exp_org_id,
	pdl.suspense_reason_code suspense_reason_code,
	ppsl.payroll_sub_line_id, 
	PSP_PSPRCSLD_XMLP_PKG.cf_dl_credit_amount_dspformula(sum ( DECODE ( ppl.dr_cr_flag , 'C' , pdl.distribution_amount , 0 ) ), :CF_currency_format) CF_DL_Credit_Amount_dsp, 
	PSP_PSPRCSLD_XMLP_PKG.cf_dl_debit_amount_dspformula(sum ( DECODE ( ppl.dr_cr_flag , 'D' , pdl.distribution_amount , 0 ) ), :CF_currency_format) CF_DL_Debit_Amount_dsp, 
	PSP_PSPRCSLD_XMLP_PKG.cf_charging_instructionsformul(DECODE ( pdl.gl_project_flag , 'G' , DECODE ( pdl.suspense_org_Account_id , NULL , nvl ( pdl.auto_gl_code_combination_id , nvl ( pdl.cap_excess_glccid , nvl ( psl.gl_code_combination_id , nvl ( poa.gl_code_combination_id , nvl ( peta.gl_code_combination_id , pdls.gl_code_combination_id ) ) ) ) ) , nvl ( pdl.suspense_auto_glccid , poa2.gl_code_combination_id ) ) , NULL ), DECODE ( pdl.gl_project_flag , 'P' , DECODE ( pdl.suspense_org_account_id , NULL , nvl ( pdl.cap_excess_project_id , nvl ( psl.project_id , nvl ( poa.project_id , nvl ( peta.project_id , pdls.project_id ) ) ) ) , poa2.project_id ) , NULL ), DECODE ( pdl.gl_project_flag , 'P' , decode ( pdl.suspense_org_account_id , NULL , nvl ( pdl.cap_excess_task_id , nvl ( psl.task_id , nvl ( poa.task_id , nvl ( peta.task_id , pdls.task_id ) ) ) ) , poa2.task_id ) , NULL ), DECODE ( pdl.gl_project_flag , 'P' , decode ( pdl.suspense_org_account_id , NULL , nvl ( pdl.cap_excess_award_id , nvl ( psl.award_id , nvl ( poa.award_id , nvl ( peta.award_id , pdls.award_id ) ) ) ) , poa2.award_id ) , NULL ), DECODE ( pdl.gl_project_flag , 'P' , decode ( pdl.suspense_org_account_id , NULL , nvl ( pdl.cap_excess_exp_org_id , nvl ( psl.expenditure_organization_id , nvl ( poa.expenditure_organization_id , nvl ( peta.expenditure_organization_id , pdls.expenditure_organization_id ) ) ) ) , poa2.expenditure_organization_id ) , NULL ), DECODE ( pdl.gl_project_flag , 'P' , decode ( pdl.suspense_org_account_id , NULL , nvl ( pdl.auto_expenditure_type , nvl ( pdl.cap_excess_exp_type , nvl ( psl.expenditure_type , nvl ( poa.expenditure_type , nvl ( peta.expenditure_type , pdls.expenditure_type ) ) ) ) ) , nvl ( pdl.suspense_auto_exp_type , poa2.expenditure_type ) ) , NULL )) CF_Charging_Instructions
FROM
	Psp_distribution_lines pdl,
	psp_payroll_sub_lines ppsl,
	psp_payroll_lines ppl,
	psp_schedule_lines psl,
	psp_organization_accounts poa,
	psp_element_type_accounts peta,
	psp_default_labor_Schedules pdls,
	psp_organization_accounts poa2
WHERE
			ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
AND		ppsl.payroll_line_id 	= ppl.payroll_line_id
AND    	pdl.schedule_line_id = psl.schedule_line_id(+)
AND    	pdl.default_org_account_id = poa.organization_account_id(+)
AND    	pdl.element_account_id = peta.element_account_id(+)
AND    	pdl.org_schedule_id = pdls.org_schedule_id(+)
AND    	pdl.suspense_org_account_id = poa2.organization_account_id(+)
AND 	(pdl.reversal_entry_flag = 'N' OR pdl.reversal_entry_flag IS NULL)
AND		pdl.status_code = 'N' 
 and ppsl.payroll_sub_line_id=:payroll_sub_line_id1
GROUP BY
       ppl.dr_cr_flag,
	DECODE(pdl.gl_project_flag, 'G', DECODE(pdl.suspense_org_Account_id, NULL,
						nvl(pdl.auto_gl_code_combination_id,
                                                                     nvl(pdl.cap_excess_glccid,
						nvl(psl.gl_code_combination_id,
              					nvl(poa.gl_code_combination_id,
              					nvl(peta.gl_code_combination_id,
                  			pdls.gl_code_combination_id))))),
                                                                     nvl(pdl.suspense_auto_glccid,poa2.gl_code_combination_id)),
					NULL),
	DECODE(pdl.gl_project_flag, 'P', DECODE(pdl.suspense_org_account_id, NULL,
                                                                  nvl(pdl.cap_excess_project_id,
              				     	nvl(psl.project_id,
              					nvl(poa.project_id,
              					nvl(peta.project_id,
                  				pdls.project_id)))),poa2.project_id),
					NULL),
	DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
                                                                                     nvl(pdl.cap_Excess_task_id,
              					nvl(psl.task_id,
              					nvl(poa.task_id,
              					nvl(peta.task_id,
                  				pdls.task_id)))),poa2.task_id),
					NULL),
	DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
                                                          nvl(pdl.cap_excess_award_id,
              					nvl(psl.award_id,
              					nvl(poa.award_id,
             					nvl(peta.award_id,
                  				pdls.award_id)))),poa2.award_id),
					NULL),
	DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
						nvl(pdl.auto_expenditure_type,
                                                                   nvl(pdl.cap_Excess_exp_type,
						nvl(psl.expenditure_type,
                    				nvl(poa.expenditure_type,
                        				nvl(peta.expenditure_type,
                              			pdls.expenditure_type))))), 
                                                               nvl(pdl.suspense_auto_exp_type,poa2.expenditure_type)),
					NULL),
	DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
                                                                                         nvl(pdl.cap_excess_exp_org_id,
              					nvl(psl.expenditure_organization_id,
              					nvl(poa.expenditure_organization_id,
              					nvl(peta.expenditure_organization_id,
                  				pdls.expenditure_organization_id)))),poa2.expenditure_organization_id),
					NULL),
	pdl.suspense_reason_code,
	ppsl.payroll_sub_line_id
 ORDER BY 
min(pdl.distribution_date),
          	max(pdl.distribution_date)
Parameter Name SQL text Validation
P_SET_OF_BOOKS_ID
 
Number
Batch Name
 
LOV Oracle
Time Period
 
LOV Oracle
Payroll Name
 
LOV Oracle
Source Code
 
LOV Oracle
Source Type
 
LOV Oracle