PSP Reconciliation between distribution lines and summary

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Reconciliation between distribution lines and summary lines
Application: Labor Distribution
Source: PSP: Reconciliation between distribution lines and summary (XML)
Short Name: PSPRCDLS_XML
DB package: PSP_PSPRCDLS_XMLP_PKG
Run PSP Reconciliation between distribution lines and summary and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT  	person_id,
               	assignment_id,
               	gl_code_combination_id,
               	project_id,
               	task_id,
              	 award_id,
               	expenditure_type,
               	expenditure_organization_id,
               	sum(decode(dr_cr_flag, 'C', summary_amount, 0)) sl_credit_amount,
         	sum(decode(dr_cr_flag, 'D', summary_amount, 0)) sl_debit_amount,
	currency_code,
	PSP_PSPRCDLS_XMLP_PKG.cf_currency_formatformula(currency_code) CF_currency_format,
	PSP_PSPRCDLS_XMLP_PKG.cf_currency_codeformula(currency_code) CF_currency_code,
	/*PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_d_total_dspformula(:CS_SUM_SL_D_TOTAL, :CF_currency_format) CF_sum_sl_d_total_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_c_total_dspformula(:CS_SUM_SL_C_TOTAL, :CF_currency_format) CF_sum_sl_c_total_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_d_total_dspformula(:CS_SUM_DL_D_TOTAL, :CF_currency_format) CF_sum_dl_d_total_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_c_total_dspformula(:CS_SUM_DL_C_TOTAL, :CF_currency_format) CF_sum_dl_c_total_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_mismatch_total_dspformula(:CS_sum_dl_d_total, :CS_sum_sl_d_total, :CS_sum_dl_c_total, :CS_sum_sl_c_total) CF_mismatch_total_dsp,*/
	PSP_PSPRCDLS_XMLP_PKG.cf_person_nameformula(person_id) CF_person_name,
	/*PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_d_person_dspformula(:SUM_SL_D_PERSON, :CF_currency_format) CF_sum_sl_d_person_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_c_person_dspformula(:SUM_SL_C_PERSON, :CF_currency_format) CF_sum_sl_c_person_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_d_person_dspformula(:SUM_DL_D_PERSON, :CF_currency_format) CF_sum_dl_d_person_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_c_person_dspformula(:SUM_DL_C_PERSON, :CF_currency_format) CF_sum_dl_c_person_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_mismatch_personformula(:sum_dl_d_person, :sum_sl_d_person, :sum_dl_c_person, :sum_sl_c_person) CF_mismatch_person, */
	PSP_PSPRCDLS_XMLP_PKG.cf_assignment_numberformula(assignment_id) CF_assignment_number,
	/*PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_d_assg_dspformula(:SUM_SL_D_ASSG, :CF_currency_format) CF_sum_sl_d_assg_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_sl_c_assg_dspformula(:SUM_SL_C_ASSG, :CF_currency_format) CF_sum_sl_c_assg_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_d_assg_dspformula(:SUM_DL_D_ASSG, :CF_currency_format) CF_sum_dl_d_assg_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_sum_dl_c_assg_dspformula(:SUM_DL_C_ASSG, :CF_currency_format, :sum_sl_c_assg) CF_sum_dl_c_assg_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_mismatch_assgformula(:sum_dl_d_assg, :sum_sl_d_assg, :sum_dl_c_assg, :sum_sl_c_assg) CF_mismatch_assg,*/
	/*PSP_PSPRCDLS_XMLP_PKG.cf_sl_debit_amount_dspformula(sum ( decode ( dr_cr_flag , 'D' , summary_amount , 0 ) ), :CF_currency_format) CF_sl_debit_amount_dsp, */
	PSP_PSPRCDLS_XMLP_PKG.cf_amt_sl_cformula(gl_code_combination_id, sum ( decode ( dr_cr_flag , 'C' , summary_amount , 0 ) )) CF_AMT_SL_C,
	--PSP_PSPRCDLS_XMLP_PKG.cf_amt_sl_c_dspformula(:CF_amt_sl_c, :CF_currency_format) CF_amt_sl_c_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_charging_instructionsformul(gl_code_combination_id, project_id, task_id, award_id, expenditure_organization_id, expenditure_type) CF_Charging_Instructions,
	PSP_PSPRCDLS_XMLP_PKG.cf_credit_amount_dl_dspformula(:CF_currency_format) CF_credit_amount_dl_dsp,
	PSP_PSPRCDLS_XMLP_PKG.cf_1formula(currency_code, person_id, assignment_id, gl_code_combination_id, project_id, task_id, award_id, expenditure_type, expenditure_organization_id) CF_1,
	--PSP_PSPRCDLS_XMLP_PKG.cf_1_dspformula(:CF_1, :CF_currency_format) CF_1_dsp,
	/*PSP_PSPRCDLS_XMLP_PKG.cf_mismatch_lineformula(:CF_1, sum ( decode ( dr_cr_flag , 'D' , summary_amount , 0 ) ), :CF_amt_sl_c) CF_Mismatch_Line,*/
	PSP_PSPRCDLS_XMLP_PKG.CP_credit_amount_dl_p CP_credit_amount_dl,
	PSP_PSPRCDLS_XMLP_PKG.CP_suspense_p CP_suspense
FROM	psp_summary_lines psl,
	psp_payroll_controls ppc
WHERE	psl.status_code 	= 'A'
AND 	psl.payroll_control_id =ppc.payroll_control_id
AND           ppc.source_type    = :p_source_type
AND           payroll_source_code    = :p_source_code
AND           ppc.time_period_id    = :p_time_period_id
AND           (batch_name = :p_batch_name or batch_name IS NULL)
GROUP BY
	currency_code,
	person_id,
	assignment_id,
	gl_code_combination_id,
	project_id,
	task_id,
	award_id,
	expenditure_type,
	expenditure_organization_id
	ORDER BY 11 ASC,1 ASC,2 ASC
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
Blitz Report™