PSP Distribution History by Organization

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Labor Distribution History by Organization
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 NameSQL textValidation
Organization Set
 
LOV Oracle
Begin Date
 
Date
End Date
 
Date
Download
Related Blitz Reports
PSP Distribution History by Award PSP Distribution History by Project PSP Encumbrance History by Award PSP Distribution Adjustment Register PSP Encumbrance History by Organization PSP Encumbrance process run results PSP Encumbrance Suspense Charge PSP Organization Default Usage Encumbrance CAC Receiving Expense Value (Period-End)
Blitz Report™