PSP Distribution History by Project

Description
Categories: BI Publisher
Columns: Person Id, Assignment Id, Element Type Id, Time Period Id, Earnings Amount, Project Id, Expenditure Type, Currency Code, Award Id, Task Id ...
Application: Labor Distribution
Source: PSP: Distribution History by Project Report (XML)
Short Name: PSPLDPRJ_XML
DB package: PSP_PSPLDPRJ_XMLP_PKG
Select 	a.PERSON_ID,
	a.ASSIGNMENT_ID,
	b.ELEMENT_TYPE_ID,
	a.TIME_PERIOD_ID,
  	SUM(decode(a.dr_cr_flag,'C',d.DISTRIBUTION_AMOUNT*(-1),'D',d.DISTRIBUTION_AMOUNT))        EARNINGS_AMOUNT,
		a.PROJECT_ID,
	a.EXPENDITURE_TYPE,
   	 ppc.CURRENCY_CODE,
                a.award_id,
                 a.task_id ,
                 a.expenditure_organization_id,
	PSP_PSPLDPRJ_XMLP_PKG.cf_project_nameformula(a.PROJECT_ID) CF_PROJECT_NAME,
	PSP_PSPLDPRJ_XMLP_PKG.cf_currency_formatformula(ppc.CURRENCY_CODE) CF_currency_format,
	PSP_PSPLDPRJ_XMLP_PKG.cf_sum_currency_codeformula(ppc.CURRENCY_CODE) CF_sum_currency_code,
--	PSP_PSPLDPRJ_XMLP_PKG.cf_curr_proj_amt_totalformula(:CF_currency_format, :CS_curr_PROJ_AMT_TOTAL) CF_curr_proj_amt_total_dsp,
	PSP_PSPLDPRJ_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_element_nameformula(b.ELEMENT_TYPE_ID) CF_Element_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_charging_instructionformula(a.expenditure_organization_id, a.task_id, a.award_id, PSP_PSPLDPRJ_XMLP_PKG.cf_project_nameformula(a.PROJECT_ID), a.EXPENDITURE_TYPE) CF_charging_instruction,
	PSP_PSPLDPRJ_XMLP_PKG.cf_earnings_amountformula(PSP_PSPLDPRJ_XMLP_PKG.cf_currency_formatformula(ppc.CURRENCY_CODE) , SUM ( decode ( a.dr_cr_flag , 'C' , d.DISTRIBUTION_AMOUNT * ( - 1 ) , 'D' , d.DISTRIBUTION_AMOUNT ) )) CF_earnings_amount_dsp,
	PSP_PSPLDPRJ_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID) CF_Assignment_Number
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_Project_ID
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
and a.project_id is not null
group by 	a.PROJECT_ID,
  	ppc.currency_code,
	a.EXPENDITURE_TYPE,
	a.PERSON_ID,
	a.ASSIGNMENT_ID,
 	b.ELEMENT_TYPE_ID,
                a.TIME_PERIOD_ID,
	a.award_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',a.DISTRIBUTION_AMOUNT*(-1),'D',a.DISTRIBUTION_AMOUNT))       EARNINGS_AMOUNT,
   	a.PROJECT_ID,
   	 a.EXPENDITURE_TYPE,
   	 ppc.CURRENCY_CODE,
                 a.award_id,
                a.task_id ,
                a.expenditure_organization_id,
	PSP_PSPLDPRJ_XMLP_PKG.cf_project_nameformula(a.PROJECT_ID) CF_PROJECT_NAME,
	PSP_PSPLDPRJ_XMLP_PKG.cf_currency_formatformula(ppc.CURRENCY_CODE) CF_currency_format,
	PSP_PSPLDPRJ_XMLP_PKG.cf_sum_currency_codeformula(ppc.CURRENCY_CODE) CF_sum_currency_code,
--	PSP_PSPLDPRJ_XMLP_PKG.cf_curr_proj_amt_totalformula(:CF_currency_format, :CS_curr_PROJ_AMT_TOTAL) CF_curr_proj_amt_total_dsp,
	PSP_PSPLDPRJ_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_element_nameformula(a.ELEMENT_TYPE_ID) CF_Element_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_charging_instructionformula(a.expenditure_organization_id, a.task_id, a.award_id, PSP_PSPLDPRJ_XMLP_PKG.cf_project_nameformula(a.PROJECT_ID), a.EXPENDITURE_TYPE) CF_charging_instruction,
	PSP_PSPLDPRJ_XMLP_PKG.cf_earnings_amountformula(PSP_PSPLDPRJ_XMLP_PKG.cf_currency_formatformula(ppc.CURRENCY_CODE) ,  SUM(decode(a.dr_cr_flag,'C',a.DISTRIBUTION_AMOUNT*(-1),'D',a.DISTRIBUTION_AMOUNT))) CF_earnings_amount_dsp,
	PSP_PSPLDPRJ_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID) CF_Assignment_Number
from      	 PSP_ADJUSTMENT_LINES_HISTORY a,
            	PSP_PAYROLL_CONTROLS PPC
where      	a.distribution_date BETWEEN :p_start_date AND :p_end_date
&P_Project_ID
and           a.project_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	a.payroll_control_id = ppc.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.PROJECT_ID,
   	 ppc.CURRENCY_CODE,
    	a.EXPENDITURE_TYPE,
   	a.PERSON_ID,
   	a.ASSIGNMENT_ID,
    	a.ELEMENT_TYPE_ID,
   	a.TIME_PERIOD_ID,
                a.award_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.PROJECT_ID,
    	a.EXPENDITURE_TYPE,
	ppc.CURRENCY_CODE,
               a.award_id,
               a.task_id ,
              a.expenditure_organization_id,
	PSP_PSPLDPRJ_XMLP_PKG.cf_project_nameformula(a.PROJECT_ID) CF_PROJECT_NAME,
	PSP_PSPLDPRJ_XMLP_PKG.cf_currency_formatformula(ppc.CURRENCY_CODE) CF_currency_format,
	PSP_PSPLDPRJ_XMLP_PKG.cf_sum_currency_codeformula(ppc.CURRENCY_CODE) CF_sum_currency_code,
--	PSP_PSPLDPRJ_XMLP_PKG.cf_curr_proj_amt_totalformula(:CF_currency_format, :CS_curr_PROJ_AMT_TOTAL) CF_curr_proj_amt_total_dsp,
	PSP_PSPLDPRJ_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_element_nameformula(a.ELEMENT_TYPE_ID) CF_Element_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name,
	PSP_PSPLDPRJ_XMLP_PKG.cf_charging_instructionformula(a.expenditure_organization_id, a.task_id, a.award_id, PSP_PSPLDPRJ_XMLP_PKG.cf_project_nameformula(a.PROJECT_ID), a.EXPENDITURE_TYPE) CF_charging_instruction,
	PSP_PSPLDPRJ_XMLP_PKG.cf_earnings_amountformula(PSP_PSPLDPRJ_XMLP_PKG.cf_currency_formatformula(ppc.CURRENCY_CODE) , SUM(DECODE(a.DR_CR_FLAG, 'C', -1 * a.DISTRIBUTION_AMOUNT, 'D', a.DISTRIBUTION_AMOUNT))) CF_earnings_amount_dsp,
	PSP_PSPLDPRJ_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID) CF_Assignment_Number
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_Project_ID
and a.project_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 	a.payroll_control_id = ppc.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.PROJECT_ID,
   	 ppc.CURRENCY_CODE,
    	a.EXPENDITURE_TYPE,
    	a.PERSON_ID,
   	 a.ASSIGNMENT_ID,
   	 a.ELEMENT_TYPE_ID,
    	a.TIME_PERIOD_ID,
               a.award_id,
               a.task_id ,
              a.expenditure_organization_id
ORDER BY 12,8,CF_Employee_Name,CF_Element_Name,CF_Time_Period_Name,CF_charging_instruction,1,9,11,10,7,2,ELEMENT_TYPE_ID,TIME_PERIOD_ID
Parameter Name SQL text Validation
Project Set
 
LOV Oracle
Begin Date
 
Date
End Date
 
Date
P_BUSINESS_GROUP_ID
 
Number
P_SET_OF_BOOKS_ID
 
Number
PSP_REPORT_TYPE