PSP Reconciliation between pre-generated distribution lines and summary lines
Description
- Categories
- BI Publisher
- Application
- Labor Distribution
- Source
- PSP: Reconciliation between pre-generated distribution lines and summary lines (XML)
- Short Name
- PSPRCPGD_XML
- DB package
- PSP_PSPRCPGD_XMLP_PKG
Imported from BI Publisher
Reconciliation between pre-generated distribution lines and summary lines
Reconciliation between pre-generated distribution lines and summary lines
SQL 73 lines
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_PSPRCPGD_XMLP_PKG.cf_currency_codeformula(currency_code) CF_currency_code, PSP_PSPRCPGD_XMLP_PKG.cf_currency_formatformula(currency_code) CF_currency_format, PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_d_total_dspformula(:CS_sum_sl_d_total, :CF_currency_format) CF_sum_sl_d_total_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_c_total_dspformula(:CS_sum_sl_c_total, :CF_currency_format) CF_sum_sl_c_total_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_d_total_dspformula(:CS_sum_dl_d_total, :CF_currency_format) CF_sum_dl_d_total_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_c_total_dspformula(:CS_sum_dl_c_total, :CF_currency_format) CF_sum_dl_c_total_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_mismatch_currency_totalform(:CS_sum_dl_d_total, :CS_sum_sl_d_total, :CS_sum_dl_c_total, :CS_sum_sl_c_total) CF_mismatch_currency_total, PSP_PSPRCPGD_XMLP_PKG.cf_person_nameformula(person_id) CF_person_name, PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_d_person_dspformula(:sum_sl_d_person, :CF_currency_format) CF_sum_sl_d_person_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_c_person_dspformula(:sum_sl_c_person, :CF_currency_format) CF_sum_sl_c_person_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_d_person_dspformula(:sum_dl_d_person, :CF_currency_format) CF_sum_dl_d_person_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_c_person_dspformula(:sum_dl_c_person, :CF_currency_format) CF_sum_dl_c_person_dsp, PSP_PSPRCPGD_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_PSPRCPGD_XMLP_PKG.cf_assignment_numberformula(assignment_id) CF_assignment_number, PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_d_assg_dspformula(:sum_sl_d_assg, :CF_currency_format) CF_sum_sl_d_assg_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_sl_c_assg_dspformula(:sum_sl_c_assg, :CF_currency_format) CF_sum_sl_c_assg_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_d_assg_dspformula(:sum_dl_d_assg, :CF_currency_format) CF_sum_dl_d_assg_dsp, PSP_PSPRCPGD_XMLP_PKG.cf_sum_dl_c_assg_dspformula(:sum_dl_c_assg, :CF_currency_format) CF_sum_dl_c_assg_dsp, PSP_PSPRCPGD_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_PSPRCPGD_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_PSPRCPGD_XMLP_PKG.cf_amt_sl_cformula(gl_code_combination_id, sum ( decode ( dr_cr_flag , 'C' , summary_amount , 0 ) )) CF_amt_sl_c, PSP_PSPRCPGD_XMLP_PKG.cf_amt_sl_c_dspformula(:CF_amt_sl_c, :CF_currency_format) CF_amt_sl_c_dsp, -- PSP_PSPRCPGD_XMLP_PKG.cf_amt_dl_dformula(gl_code_combination_id) CF_amt_dl_d, PSP_PSPRCPGD_XMLP_PKG.cf_amt_dl_dformula(person_id,assignment_id, gl_code_combination_id, project_id, task_id, award_id, expenditure_type, expenditure_organization_id ) CF_amt_dl_d, PSP_PSPRCPGD_XMLP_PKG.cf_amt_dl_d_dspformula(:CF_amt_dl_d, :CF_currency_format) CF_amt_dl_d_dsp, -- PSP_PSPRCPGD_XMLP_PKG.cf_mismatch_eltformula(:CF_amt_dl_d, sum ( decode ( dr_cr_flag , 'D' , summary_amount , 0 ) ), :CF_amt_sl_c) CF_mismatch_elt, PSP_PSPRCPGD_XMLP_PKG.cf_mismatch_eltformula(PSP_PSPRCPGD_XMLP_PKG.cf_amt_dl_dformula(person_id,assignment_id, gl_code_combination_id, project_id, task_id, award_id, expenditure_type, expenditure_organization_id ), sum ( decode ( dr_cr_flag , 'D' , summary_amount , 0 ) ), PSP_PSPRCPGD_XMLP_PKG.cf_amt_sl_cformula(gl_code_combination_id, sum ( decode ( dr_cr_flag , 'C' , summary_amount , 0 ) ))) CF_mismatch_elt, -- PSP_PSPRCPGD_XMLP_PKG.cf_charging_instructionsformu(project_id, task_id, award_id, expenditure_organization_id, gl_code_combination_id) CF_Charging _Instructions, PSP_PSPRCPGD_XMLP_PKG.cf_charging_instructionsformu(project_id, task_id, award_id, expenditure_organization_id, gl_code_combination_id, expenditure_type) CF_Charging_Instructions, PSP_PSPRCPGD_XMLP_PKG.cf_credit_amount_pgdl_dspformu(:CF_currency_format) CF_credit_amount_pgdl_dsp, PSP_PSPRCPGD_XMLP_PKG.CP_suspense_p CP_suspense, PSP_PSPRCPGD_XMLP_PKG.CP_credit_amount_pgdl_p CP_credit_amount_pgdl 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; expenditure_organization_id --added order by currency_code, person_id, assignment_id, gl_code_combination_id, project_id, task_id, award_id, expenditure_type, expenditure_organization_id |
Parameters 5
| Parameter | Type | List of values SQL |
|---|---|---|
| Source Type | LOV Oracle | |
| Source Code | LOV Oracle | |
| Payroll Name | LOV Oracle | |
| Time Period | LOV Oracle | |
| Batch Name | LOV Oracle |