PSP Distribution History by Award

➜ Back to Library
Description
Categories: BI Publisher
Columns: Person Id, Assignment Id, Element Type Id, Time Period Id, Earnings Amount, Award Id, Expenditure Type, Currency Code, Project Id, Task Id ...
Application: Labor Distribution
Source: PSP: Distribution History by Award Report (XML)
Short Name: PSPLDAWD_XML
DB package: PSP_PSPLDAWD_XMLP_PKG
Select     		a.PERSON_ID,
   		a.ASSIGNMENT_ID,
    		b.ELEMENT_TYPE_ID,
    		a.TIME_PERIOD_ID,
    		SUM(decode(a.dr_cr_flag,'C',(-1)*d.DISTRIBUTION_AMOUNT,'D',d.DISTRIBUTION_AMOUNT)) EARNINGS_AMOUNT,
    		a.AWARD_ID,
    		a.EXPENDITURE_TYPE,
    		ppc.currency_code,
                                a.project_id,
                                a.task_id ,
                                a.expenditure_organization_id,
	PSP_PSPLDAWD_XMLP_PKG.cf_award_nameformula(a.AWARD_ID) CF_AWARD_NAME,
	PSP_PSPLDAWD_XMLP_PKG.cf_currency_formatformula(ppc.currency_code) CF_currency_format,
	PSP_PSPLDAWD_XMLP_PKG.cf_sum_currency_codeformula(ppc.currency_code) CF_sum_currency_code,
	PSP_PSPLDAWD_XMLP_PKG.cf_curr_awd_amt_totalformula(:CF_currency_format, :CS_curr_AWD_AMT_TOTAL) CF_curr_awd_amt_total_dsp,
	PSP_PSPLDAWD_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID, a.PERSON_ID) CF_Assignment_Number,
	PSP_PSPLDAWD_XMLP_PKG.cf_element_nameformula(b.ELEMENT_TYPE_ID) CF_Element_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_charging_instructionsformul(a.expenditure_organization_id, a.task_id, a.project_id, a.AWARD_ID, a.EXPENDITURE_TYPE) CF_charging_instructions,
	PSP_PSPLDAWD_XMLP_PKG.cf_employee_numberformula() CF_Employee_Number,
	PSP_PSPLDAWD_XMLP_PKG.cf_earnings_amountformula(:CF_currency_format, SUM ( decode ( a.dr_cr_flag , 'C' , ( - 1 ) * d.DISTRIBUTION_AMOUNT , 'D' , d.DISTRIBUTION_AMOUNT ) )) CF_earnings_amount_dsp
FROM
psp_summary_lines a,
PSP_PAYROLL_LINES b,
PSP_PAYROLL_SUB_LINES c,
PSP_DISTRIBUTION_LINES_HISTORY d,
PSP_PAYROLL_CONTROLS ppc,
per_assignments_f paf
WHERE B.PAYROLL_LINE_ID = C.PAYROLL_LINE_ID AND C.PAYROLL_SUB_LINE_ID = d.payroll_sub_line_id
&P_Award_ID
and  a.award_id is not null
AND d.summary_line_id = a.summary_line_id  AND ppc.payroll_control_id = b.payroll_control_id
AND d.gl_project_flag = 'P'
AND NOT EXISTS( SELECT 1 FROM psp_adjustment_lines_history pdlh WHERE pdlh.orig_line_id =d.distribution_line_id
AND pdlh.original_line_flag ='Y' AND pdlh.orig_source_type = 'D' )
and d.business_group_id = :P_BUSINESS_GROUP_ID and d.set_of_books_id = :P_SET_OF_BOOKS_ID
and d.distribution_date BETWEEN :p_start_date AND :p_end_date
and paf.business_group_id =:P_BUSINESS_GROUP_ID and paf.assignment_id = b.assignment_id
and b.effective_date between paf.effective_start_date and paf.effective_end_date
group by 		a.AWARD_ID,
    		ppc.currency_code,
   	 	a.EXPENDITURE_TYPE,
    		a.PERSON_ID,
    		a.ASSIGNMENT_ID,
    		b.ELEMENT_TYPE_ID,
    		a.TIME_PERIOD_ID,
                                a.project_id,
                                a.task_id ,
                                a.expenditure_organization_id
UNION ALL
Select     		a.PERSON_ID,
    		a.ASSIGNMENT_ID,
    		a.ELEMENT_TYPE_ID,
    		a.TIME_PERIOD_ID,
    		SUM(decode(a.dr_cr_flag,'C',(-1)*a.DISTRIBUTION_AMOUNT,'D',a.DISTRIBUTION_AMOUNT )) EARNINGS_AMOUNT,
    		a.AWARD_ID,
    		a.EXPENDITURE_TYPE,
		ppc.CURRENCY_CODE,
                                a.project_id,
                                a.task_id ,
                                a.expenditure_organization_id,
	PSP_PSPLDAWD_XMLP_PKG.cf_award_nameformula(a.AWARD_ID) CF_AWARD_NAME,
	PSP_PSPLDAWD_XMLP_PKG.cf_currency_formatformula(ppc.currency_code) CF_currency_format,
	PSP_PSPLDAWD_XMLP_PKG.cf_sum_currency_codeformula(ppc.currency_code) CF_sum_currency_code,
	PSP_PSPLDAWD_XMLP_PKG.cf_curr_awd_amt_totalformula(:CF_currency_format, :CS_curr_AWD_AMT_TOTAL) CF_curr_awd_amt_total_dsp,
	PSP_PSPLDAWD_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID, a.PERSON_ID) CF_Assignment_Number,
	PSP_PSPLDAWD_XMLP_PKG.cf_element_nameformula(a.ELEMENT_TYPE_ID) CF_Element_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_charging_instructionsformul(a.expenditure_organization_id, a.task_id, a.project_id, a.AWARD_ID, a.EXPENDITURE_TYPE) CF_charging_instructions,
	PSP_PSPLDAWD_XMLP_PKG.cf_employee_numberformula() CF_Employee_Number,
	PSP_PSPLDAWD_XMLP_PKG.cf_earnings_amountformula(:CF_currency_format, SUM ( decode ( a.dr_cr_flag , 'C' , ( - 1 ) * a.DISTRIBUTION_AMOUNT , 'D' , a.DISTRIBUTION_AMOUNT ) )) CF_earnings_amount_dsp
from       		PSP_ADJUSTMENT_LINES_HISTORY a,
		PSP_PAYROLL_CONTROLS ppc
where      		 a.distribution_date BETWEEN :p_start_date AND :p_end_date
&P_Award_ID
and a.award_id is not null
and 		a.business_group_id = :P_BUSINESS_GROUP_ID
and 		a.set_of_books_id = :P_SET_OF_BOOKS_ID
AND		ppc.PAYROLL_CONTROL_ID =  a.PAYROLL_CONTROL_ID
AND 		NVL(a.original_line_flag,'N') = 'N'
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 		a.AWARD_ID,
		ppc.CURRENCY_CODE,
  		a.EXPENDITURE_TYPE,
    		a.PERSON_ID,
    		a.ASSIGNMENT_ID,
    		a.ELEMENT_TYPE_ID,
    		a.TIME_PERIOD_ID,
                                a.project_id,
                                a.task_id ,
                                a.expenditure_organization_id
UNION ALL
Select
			a.PERSON_ID,
    		a.ASSIGNMENT_ID,
    		a.ELEMENT_TYPE_ID,
    		a.TIME_PERIOD_ID,
    		SUM(DECODE(a.dr_cr_flag, 'C', -1 * a.DISTRIBUTION_AMOUNT, 'D', a.DISTRIBUTION_AMOUNT)) EARNINGS_AMOUNT,
   		   	a.AWARD_ID,
    		a.EXPENDITURE_TYPE,
			ppc.CURRENCY_CODE,
                                a.project_id,
                                a.task_id ,
                                a.expenditure_organization_id,
	PSP_PSPLDAWD_XMLP_PKG.cf_award_nameformula(a.AWARD_ID) CF_AWARD_NAME,
	PSP_PSPLDAWD_XMLP_PKG.cf_currency_formatformula(ppc.currency_code) CF_currency_format,
	PSP_PSPLDAWD_XMLP_PKG.cf_sum_currency_codeformula(ppc.currency_code) CF_sum_currency_code,
	PSP_PSPLDAWD_XMLP_PKG.cf_curr_awd_amt_totalformula(:CF_currency_format, :CS_curr_AWD_AMT_TOTAL) CF_curr_awd_amt_total_dsp,
	PSP_PSPLDAWD_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID, a.PERSON_ID) CF_Assignment_Number,
	PSP_PSPLDAWD_XMLP_PKG.cf_element_nameformula(a.ELEMENT_TYPE_ID) CF_Element_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name,
	PSP_PSPLDAWD_XMLP_PKG.cf_charging_instructionsformul(a.expenditure_organization_id, a.task_id, a.project_id, a.AWARD_ID, a.EXPENDITURE_TYPE) CF_charging_instructions,
	PSP_PSPLDAWD_XMLP_PKG.cf_employee_numberformula() CF_Employee_Number,
	PSP_PSPLDAWD_XMLP_PKG.cf_earnings_amountformula(:CF_currency_format, SUM ( decode ( a.dr_cr_flag , 'C' , ( - 1 ) * a.DISTRIBUTION_AMOUNT , 'D' , a.DISTRIBUTION_AMOUNT ) )) CF_earnings_amount_dsp
from       		PSP_PRE_GEN_DIST_LINES_HISTORY a,
		PSP_PAYROLL_CONTROLS ppc
where    		a.distribution_date BETWEEN :p_start_date AND :p_end_date
 &P_Award_ID
and   a.award_id is not null
and 		a.business_group_id = :P_BUSINESS_GROUP_ID
and 		a.set_of_books_id = :P_SET_OF_BOOKS_ID
AND		ppc.PAYROLL_CONTROL_ID =  a.PAYROLL_CONTROL_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     	a.AWARD_ID,
                       	 ppc.CURRENCY_CODE,
		a.EXPENDITURE_TYPE,
    		a.PERSON_ID,
    		a.ASSIGNMENT_ID,
    		a.ELEMENT_TYPE_ID,
    		a.TIME_PERIOD_ID,
                                a.project_id,
                                a.task_id ,
                                a.expenditure_organization_id
--ORDER BY 12,8,16,17,18,19,20,9,10,7,11,3,4,5
ORDER BY 8 ASC,  6
Parameter NameSQL textValidation
Award 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 Project PSP Distribution History by Organization PSP Distribution Adjustment Register PSP Encumbrance process run results PSP Encumbrance Suspense Charge PSP Organization Default Usage Encumbrance PSP Encumbrance History by Organization PSP Encumbrance History by Award PO Printed Requisitions
Blitz Report In Action
Blitz Report™