PSP Distribution History by Organization
Description
- Categories
- BI Publisher
- Application
- Labor Distribution
- Source
- PSP: Distribution History by Organization Report (XML)
- Short Name
- PSPLDORG_XML
- DB package
- PSP_PSPLDORG_XMLP_PKG
Imported from BI Publisher
Labor Distribution History by Organization
Labor Distribution History by Organization
SQL 210 lines
SELECT psp_general.get_org_name(paf.organization_id) name, b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(pdl.effective_date)) person_name, a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(pdl.effective_date)) assignment_number, ppl.element_type_id, psp_general.get_element_name(ppl.element_type_id, TRUNC(pdl.effective_date)) element_name, a.gl_code_combination_id gl_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)) project_number, a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)) award_number, a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)) task_number, a.expenditure_type exp_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)) expenditure_organization_name, SUM(DECODE(a.dr_cr_flag, 'C', -1*pdl.distribution_amount, 'D', pdl.distribution_amount)) EARNINGS_AMOUNT, MAX(pdl.effective_date) Effective_date, ptp.period_name, PSP_PSPLDORG_XMLP_PKG.cf_currency_formatformula(b.currency_code) CF_currency_format, PSP_PSPLDORG_XMLP_PKG.cf_currency_codeformula(b.currency_code) CF_currency_code, PSP_PSPLDORG_XMLP_PKG.cf_org_curr_amt_total_dspformu(:CF_currency_format, :CS_ORG_curr_AMT_TOTAL) CF_org_curr_amt_total_dsp, PSP_PSPLDORG_XMLP_PKG.cf_charging_descformula(a.gl_code_combination_id, DECODE ( a.project_id , NULL , NULL , psp_general.get_project_number ( a.project_id ) ), DECODE ( a.task_id , NULL , NULL , psp_general.get_task_number ( a.task_id ) ), DECODE ( a.award_id , NULL , NULL , psp_general.get_award_number ( a.award_id ) ), DECODE ( a.expenditure_organization_id , NULL , NULL , psp_general.get_org_name ( a.expenditure_organization_id ) ), a.expenditure_type) CF_CHARGING_DESC, PSP_PSPLDORG_XMLP_PKG.cf_employee_numberformula(a.person_id, MAX ( pdl.effective_date )) CF_Employee_Number, PSP_PSPLDORG_XMLP_PKG.cf_earnings_amount_dspformula(SUM ( DECODE ( a.dr_cr_flag , 'C' , - 1 * pdl.distribution_amount , 'D' , pdl.distribution_amount ) ), :CF_currency_format) CF_earnings_amount_dsp, PSP_PSPLDORG_XMLP_PKG.cf_time_period_nameformula() CF_Time_Period_Name FROM psp_distribution_lines_history pdl, psp_summary_lines a, per_assignments_f paf, psp_payroll_lines ppl, psp_payroll_sub_lines psl, psp_payroll_controls b, per_time_periods ptp WHERE &P_ORG_ID AND paf.assignment_id = ppl.assignment_id AND paf.business_group_id = :P_BUSINESS_GROUP_ID AND ppl.effective_date between paf.effective_start_date and paf.effective_end_date AND pdl.distribution_date between :P_BEGIN_DATE AND :P_END_DATE AND ppl.payroll_line_id = psl.payroll_line_id AND b.payroll_control_id = ppl.payroll_control_id AND psl.payroll_sub_line_id = pdl.payroll_sub_line_id AND pdl.summary_line_id = a.summary_line_id AND a.business_group_id = :P_BUSINESS_GROUP_ID AND a.set_of_books_id = :P_SET_OF_BOOKS_ID AND PTP.time_period_id = a.time_period_id AND NOT EXISTS (SELECT 1 FROM psp_adjustment_lines_history pdlh WHERE pdlh.orig_line_id = pdl.distribution_line_id AND pdlh.original_line_flag ='Y' AND pdlh.orig_source_type = 'D') GROUP BY psp_general.get_org_name(paf.organization_id), b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(pdl.effective_date)), a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(pdl.effective_date)), ppl.element_type_id, psp_general.get_element_name(ppl.element_type_id, TRUNC(pdl.effective_date)), a.gl_code_combination_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)), a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)), a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)), a.expenditure_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)), ptp.period_name UNION ALL SELECT psp_general.get_org_name(paf.organization_id) name, b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(a.effective_date)) person_name, a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(a.effective_date)) assignment_number, a.element_type_id, psp_general.get_element_name(a.element_type_id, TRUNC(a.effective_date)) element_name, a.gl_code_combination_id gl_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)) project_number, a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)) award_number, a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)) task_number, a.expenditure_type exp_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)) expenditure_organization_name, sum(decode(A.dr_cr_flag,'C',-1*A.distribution_amount,'D',A.distribution_amount)) EARNINGS_AMOUNT, MAX(a.effective_date) Effective_date, PTP.period_name, PSP_PSPLDORG_XMLP_PKG.cf_currency_formatformula(b.currency_code) CF_currency_format, PSP_PSPLDORG_XMLP_PKG.cf_currency_codeformula(b.currency_code) CF_currency_code, PSP_PSPLDORG_XMLP_PKG.cf_org_curr_amt_total_dspformu(:CF_currency_format, :CS_ORG_curr_AMT_TOTAL) CF_org_curr_amt_total_dsp, PSP_PSPLDORG_XMLP_PKG.cf_charging_descformula(a.gl_code_combination_id, DECODE ( a.project_id , NULL , NULL , psp_general.get_project_number ( a.project_id ) ), DECODE ( a.task_id , NULL , NULL , psp_general.get_task_number ( a.task_id ) ), DECODE ( a.award_id , NULL , NULL , psp_general.get_award_number ( a.award_id ) ), DECODE ( a.expenditure_organization_id , NULL , NULL , psp_general.get_org_name ( a.expenditure_organization_id ) ), a.expenditure_type) CF_CHARGING_DESC, PSP_PSPLDORG_XMLP_PKG.cf_employee_numberformula(a.person_id, MAX ( a.effective_date )) CF_Employee_Number, PSP_PSPLDORG_XMLP_PKG.cf_earnings_amount_dspformula(SUM ( DECODE ( a.dr_cr_flag , 'C' , - 1 * a.distribution_amount , 'D' , a.distribution_amount ) ), :CF_currency_format) CF_earnings_amount_dsp, PSP_PSPLDORG_XMLP_PKG.cf_time_period_nameformula() CF_Time_Period_Name FROM PSP_ADJUSTMENT_LINES_HISTORY a, PSP_PAYROLL_CONTROLS b, PER_ASSIGNMENTS_F paf, per_time_periods ptp WHERE a.assignment_id = paf.assignment_id AND &P_ORG_ID AND paf.business_group_id = :P_BUSINESS_GROUP_ID AND distribution_date between :P_BEGIN_DATE AND :P_END_DATE AND distribution_date between paf.effective_start_date and paf.effective_end_date AND a.PAYROLL_CONTROL_ID = b.PAYROLL_CONTROL_ID AND a.business_group_id = :P_BUSINESS_GROUP_ID AND a.set_of_books_id = :P_SET_OF_BOOKS_ID AND NVL(a.original_line_flag,'N') = 'N' AND PTP.time_period_id = a.time_period_id AND NOT EXISTS ( SELECT 1 FROM psp_adjustment_lines_history palh WHERE palh.orig_line_id = a.adjustment_line_id AND palh.original_line_flag = 'Y' AND palh.orig_source_type = 'A') GROUP BY psp_general.get_org_name(paf.organization_id), b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(a.effective_date)), a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(a.effective_date)), a.element_type_id, psp_general.get_element_name(a.element_type_id, TRUNC(a.effective_date)), a.gl_code_combination_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)), a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)), a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)), a.expenditure_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)), ptp.period_name UNION ALL SELECT psp_general.get_org_name(paf.organization_id) name, b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(a.effective_date)) person_name, a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(a.effective_date)) assignment_number, a.element_type_id, psp_general.get_element_name(a.element_type_id, TRUNC(a.effective_date)) element_name, a.gl_code_combination_id gl_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)) project_number, a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)) award_number, a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)) task_number, a.expenditure_type exp_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)) expenditure_organization_name, sum(decode(A.dr_cr_flag,'C',-1*A.distribution_amount,'D',A.distribution_amount)) EARNINGS_AMOUNT, MAX(a.effective_date) Effective_date, ptp.period_name, PSP_PSPLDORG_XMLP_PKG.cf_currency_formatformula(b.currency_code) CF_currency_format, PSP_PSPLDORG_XMLP_PKG.cf_currency_codeformula(b.currency_code) CF_currency_code, PSP_PSPLDORG_XMLP_PKG.cf_org_curr_amt_total_dspformu(:CF_currency_format, :CS_ORG_curr_AMT_TOTAL) CF_org_curr_amt_total_dsp, PSP_PSPLDORG_XMLP_PKG.cf_charging_descformula(a.gl_code_combination_id, DECODE ( a.project_id , NULL , NULL , psp_general.get_project_number ( a.project_id ) ), DECODE ( a.task_id , NULL , NULL , psp_general.get_task_number ( a.task_id ) ), DECODE ( a.award_id , NULL , NULL , psp_general.get_award_number ( a.award_id ) ), DECODE ( a.expenditure_organization_id , NULL , NULL , psp_general.get_org_name ( a.expenditure_organization_id ) ), a.expenditure_type) CF_CHARGING_DESC, PSP_PSPLDORG_XMLP_PKG.cf_employee_numberformula(a.person_id, MAX ( a.effective_date )) CF_Employee_Number, PSP_PSPLDORG_XMLP_PKG.cf_earnings_amount_dspformula(SUM ( DECODE ( a.dr_cr_flag , 'C' , - 1 * a.distribution_amount , 'D' , a.distribution_amount ) ), :CF_currency_format) CF_earnings_amount_dsp, PSP_PSPLDORG_XMLP_PKG.cf_time_period_nameformula() CF_Time_Period_Name FROM PSP_PRE_GEN_DIST_LINES_HISTORY a, PSP_PAYROLL_CONTROLS b, PER_ASSIGNMENTS_F paf, per_time_periods ptp WHERE a.assignment_id = paf.assignment_id AND &P_ORG_ID AND paf.business_group_id = :P_BUSINESS_GROUP_ID AND distribution_date between :P_BEGIN_DATE AND :P_END_DATE AND distribution_date between paf.effective_start_date and paf.effective_end_date AND a.PAYROLL_CONTROL_ID = b.PAYROLL_CONTROL_ID AND a.business_group_id = :P_BUSINESS_GROUP_ID AND a.set_of_books_id = :P_SET_OF_BOOKS_ID AND ptp.time_period_id = a.time_period_id AND NOT EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh WHERE palh.orig_line_id = a.pre_gen_dist_line_id AND palh.original_line_flag = 'Y' AND palh.orig_source_type = 'P') GROUP BY psp_general.get_org_name(paf.organization_id), b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(a.effective_date)), a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(a.effective_date)), a.element_type_id, psp_general.get_element_name(a.element_type_id, TRUNC(a.effective_date)), a.gl_code_combination_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)), a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)), a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)), a.expenditure_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)), ptp.period_name ORDER BY name, currency_code, person_name, assignment_number , element_name |
Parameters 3
| Parameter | Type | List of values SQL |
|---|---|---|
| Organization Set | LOV Oracle | |
| Begin Date | Date | |
| End Date | Date |