PSP Distribution Adjustment Register

SELECT	pact.adjustment_batch_name Batch,
	TO_CHAR(pact.distribution_start_date,'DD-MON-YYYY') ||' To '||TO_CHAR(pact.distribution_end_date,'DD-MON-YYYY') "Adjustment From",
	psp_general.get_person_name(fu1.employee_id, TRUNC(SYSDATE)) "Submitted By",
	psp_general.get_person_name(fu2.employee_id, TRUNC(SYSDATE)) "Approved By",
	TO_CHAR(pact.approval_date,'DD-MON-YYYY') "Approval Date",
	psp_general.get_person_name(pact.person_id, TRUNC(SYSDATE)) "Full Name",
	paf.assignment_number Assignment,
	pj.name Job,
	psp_general.get_payroll_name(ppc.payroll_id, TRUNC(SYSDATE)) "Payroll Name",
	psp_general.get_element_name(palh.element_type_id, TRUNC(SYSDATE)) "Earnings Element",
	peg.element_group_name,
	pact.comments  Comments,
	DECODE(palh.original_line_flag,'Y',palh.gl_code_combination_id,'N',NULL) glccid_old,
	DECODE(palh.original_line_flag,'Y',palh.project_id, 'N',NULL) project_id_old,
	DECODE(palh.original_line_flag,'Y',palh.award_id, 'N',NULL) award_id_old,
	DECODE(palh.original_line_flag,'Y', palh.task_id,'N',NULL) task_id_old,
	DECODE(palh.original_line_flag,'Y', palh.expenditure_type,'N',NULL) expenditure_type_old,
	DECODE(palh.original_line_flag,'Y', palh.expenditure_organization_id,'N',NULL) exp_org_id_old,
	DECODE(palh.original_line_flag, 'Y',(SUM(DECODE(palh.dr_cr_flag,'D',palh.distribution_amount, -palh.distribution_amount))),'N',NULL) "Original Amount",
	DECODE(palh.original_line_flag,'N',palh.gl_code_combination_id,'Y',NULL) glccid_new,
	DECODE(palh.original_line_flag,'N',palh.project_id, 'Y',NULL) project_id_new,
	DECODE(palh.original_line_flag,'N',palh.award_id, 'Y',NULL) award_id_new,
	DECODE(palh.original_line_flag,'N', palh.task_id,'Y',NULL) task_id_new,
	DECODE(palh.original_line_flag,'N', palh.expenditure_type,'Y',NULL) expenditure_type_new,
	DECODE(palh.original_line_flag,'N', palh.expenditure_organization_id,'Y',NULL) exp_org_id_new,
	DECODE(palh.original_line_flag,'N', (SUM(DECODE(palh.dr_cr_flag,'D',palh.distribution_amount, -palh.distribution_amount))),'Y',NULL) "Adjusted Amount"
,
DECODE(pact.adjust_by, NULL, 'E', pact.adjust_by) "Adjustment By",
	pact.currency_code, 
	PSP_PSPDAREG_XMLP_PKG.cf_adjustment_byformula(DECODE ( pact.adjust_by , NULL , 'E' , pact.adjust_by )) CF_adjustment_by, 
	PSP_PSPDAREG_XMLP_PKG.cf_sum_currencyformula(pact.currency_code) CF_sum_currency, 
	PSP_PSPDAREG_XMLP_PKG.cf_currency_formatformula(pact.currency_code) CF_currency_format, 
	PSP_PSPDAREG_XMLP_PKG.cf_orig_amount_total_dspformul(:CF_currency_format, :CS_Orig_Amount_Total) CF_orig_amount_total_dsp, 
	PSP_PSPDAREG_XMLP_PKG.cf_adjusted_amount_total_dspfo(:CF_currency_format, :CS_Adjusted_Amount_Total) CF_adjusted_amount_total_dsp, 
	PSP_PSPDAREG_XMLP_PKG.cf_original_amount_dspformula(:CF_currency_format, DECODE ( palh.original_line_flag , 'Y' , ( SUM ( DECODE ( palh.dr_cr_flag , 'D' , palh.distribution_amount , - palh.distribution_amount ) ) ) , 'N' , NULL )) CF_original_amount_dsp, 
	PSP_PSPDAREG_XMLP_PKG.cf_adjusted_amount_dspformula(:CF_currency_format, DECODE ( palh.original_line_flag , 'N' , ( SUM ( DECODE ( palh.dr_cr_flag , 'D' , palh.distribution_amount , - palh.distribution_amount ) ) ) , 'Y' , NULL )) CF_adjusted_amount_dsp, 
	PSP_PSPDAREG_XMLP_PKG.cf_new_ciformula(DECODE ( palh.original_line_flag , 'N' , palh.gl_code_combination_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.project_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.task_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.award_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.expenditure_organization_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.expenditure_type , 'Y' , NULL )) CF_New_CI, 
	PSP_PSPDAREG_XMLP_PKG.cf_old_ciformula(DECODE ( palh.original_line_flag , 'Y' , palh.gl_code_combination_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.project_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.task_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.award_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.expenditure_organization_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.expenditure_type , 'N' , NULL )) CF_Old_CI
FROM	psp_adjustment_lines_history palh,
	fnd_user fu1,
	fnd_user fu2,
	per_assignments_f paf,
	per_jobs pj,
	psp_payroll_controls ppc,
	psp_adjustment_control_table pact,
	psp_group_element_list pgel,
	psp_element_groups peg
WHERE	pact.assignment_id = paf.assignment_id
AND          paf.effective_start_date = (select max(pt.effective_start_date) from 
                                per_assignments_f pt where pt.assignment_id = paf.assignment_id 
                                and pt.assignment_type = 'E') 
AND	pact.created_by = fu1.user_id
AND	pact.approver_id = fu2.user_id
AND	pj.job_id = paf.job_id
AND	ppc.payroll_control_id	= palh.payroll_control_id
AND	pact.adjustment_batch_name = palh.batch_name
AND	pact.business_group_id = :p_business_group_id
AND	pact.set_of_books_id = :p_set_of_books_id
AND         peg.business_group_id = pact.business_group_id 
AND         peg.set_of_books_id = pact.set_of_books_id
AND	paf.organization_id = NVL(:p_assignment_organization_id, paf.organization_id)
AND	pact.approver_id = NVL(:p_approved_by, pact.approver_id)
AND	pact.created_by = NVL(:p_submitted_by, pact.created_by)
AND	pact.adjustment_batch_name = NVL(:p_batch_name, pact.adjustment_batch_name)
AND	trunc(pact.creation_date) BETWEEN trunc(:p_begin_date) AND TRUNC(:p_end_date)+0.99999
AND	pgel.element_type_id (+) = palh.element_type_id
AND	peg.element_group_id  = nvl(pgel.element_group_id,peg.element_group_id)
AND	(:P_PRIMARY_ORGANIZATION_ID IS NULL
                 OR  (:P_PRIMARY_ORGANIZATION_ID IS NOT NULL
	AND	EXISTS	(SELECT	1
			FROM	per_all_assignments_f paf2
			WHERE	paf2.organization_id = :P_PRIMARY_ORGANIZATION_ID
			AND	paf2.person_id = pact.person_id
			AND	paf2.primary_flag = 'Y')))
GROUP BY	pact.adjustment_batch_name,
		peg.element_group_name,
		psp_general.get_element_name(palh.element_type_id, TRUNC(SYSDATE)),
		palh.original_line_flag,
		palh.gl_code_combination_id,
		palh.project_id,
		palh.task_id,
		palh.award_id,
		palh.expenditure_type,
		palh.expenditure_organization_id,
		palh.original_line_flag,
		pact.distribution_start_date,
		pact.distribution_end_date,
		pact.comments,
		psp_general.get_person_name(fu1.employee_id, TRUNC(SYSDATE)),
		psp_general.get_person_name(fu2.employee_id, TRUNC(SYSDATE)),
		pact.approval_date,
		psp_general.get_person_name(pact.person_id, TRUNC(SYSDATE)),
		paf.assignment_number,
		pj.name,
		psp_general.get_payroll_name(ppc.payroll_id, TRUNC(SYSDATE)),
		palh.dr_cr_flag,
		pact.adjust_by,
		pact.currency_code
/*ORDER BY	pact.adjustment_batch_name asc,
		peg.element_group_name asc,
		10 asc,
		palh.original_line_flag desc*/
		ORDER BY 1 ASC,27 ASC,28 ASC,11 ASC,10 ASC , pact.adjustment_batch_name asc , peg.element_group_name asc , 10 asc , palh.original_line_flag desc