PSP Reconciliation between sublines and distribution lines

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Reconciliation between sublines and distribution lines
Application: Labor Distribution
Source: PSP: Reconciliation between sublines and distribution lines (XML)
Short Name: PSPRCSLD_XML
DB package: PSP_PSPRCSLD_XMLP_PKG
Run PSP Reconciliation between sublines and distribution lines and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Source Type 
 
 LOV Oracle
Source Code 
 
 LOV Oracle
Payroll Name 
 
 LOV Oracle
Time Period 
 
 LOV Oracle
Batch Name 
 
 LOV Oracle
Download
Related Blitz Reports
PSP Organization Default Usage Encumbrance PSP Encumbrance Suspense Charge PSP Distribution History by Award PSP Distribution History by Project PSP Distribution History by Organization CAC Recost Cost of Goods Sold PSP Distribution Adjustment Register PSP Encumbrance History by Award PSP Encumbrance History by Organization
Blitz Report™

Blitz Report™ provides multiple benefits: