PSP Distribution History by Award

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