PSP Reconciliation between sublines and distribution lines
Description
- Categories
- BI Publisher
- Application
- Labor Distribution
- Source
- PSP: Reconciliation between sublines and distribution lines (XML)
- Short Name
- PSPRCSLD_XML
- DB package
- PSP_PSPRCSLD_XMLP_PKG
Imported from BI Publisher
Reconciliation between sublines and distribution lines
Reconciliation between sublines and distribution lines
SQL 130 lines
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) |
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 |