PSP Reconciliation between distribution lines and summary

Description
Categories: BI Publisher
Columns: Person Id, Assignment Id, Gl Code Combination Id, Project Id, Task Id, Award Id, Expenditure Type, Expenditure Organization Id, Sl Credit Amount, Sl Debit Amount ...
Application: Labor Distribution
Source: PSP: Reconciliation between distribution lines and summary (XML)
Short Name: PSPRCDLS_XML
DB package: PSP_PSPRCDLS_XMLP_PKG
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
P_SET_OF_BOOKS_ID
 
Number