PSP Distribution History by Organization

Description
Categories: BI Publisher, Human Resources
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
PSP_REPORT_TYPE
 
P_SET_OF_BOOKS_ID
 
Number
P_BUSINESS_GROUP_ID
 
Number
End Date
 
Date
Begin Date
 
Date
Organization Set
 
LOV Oracle