PSP Distribution History by Organization

âžœ Back to Library
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 
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
Parameter Name SQL text Validation
Organization Set 
 
 LOV Oracle
Begin Date 
 
 Date
End Date 
 
 Date
P_BUSINESS_GROUP_ID 
 
 Number
P_SET_OF_BOOKS_ID 
 
 Number
PSP_REPORT_TYPE 
 
Download
Related Blitz Reports
PSP Distribution History by Award PSP Distribution History by Project PSP Encumbrance History by Organization PSP Encumbrance process run results PSP Encumbrance History by Award PSP Distribution Adjustment Register PSP Encumbrance Suspense Charge PSP Organization Default Usage Encumbrance