PSP Distribution History by Project

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Labor Distribution History by Project
Application: Labor Distribution
Source: PSP: Distribution History by Project Report (XML)
Short Name: PSPLDPRJ_XML
DB package: PSP_PSPLDPRJ_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Download
 
Related Blitz Reports
PSP Distribution History by Award 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™

Blitz Report™ provides multiple benefits: