PSP Distribution Suspense Account

Description
Categories: BI Publisher
Columns: Payroll Id, Time Period Id, Person Id, Assignment Id, Element Type Id, Distribution Amount, Suspense Reason Code, Organization Id, Actual Project Id, Actual Exp Org Id ...
Application: Labor Distribution
Source: PSP: Distribution Suspense Account Report (XML)
Short Name: PSPLDSSA_XML
DB package: PSP_PSPLDSSA_XMLP_PKG
SELECT	a.PAYROLL_ID,
		a.TIME_PERIOD_ID,
		a.PERSON_ID,
		a.ASSIGNMENT_ID,
		a.ELEMENT_TYPE_ID,
		SUM(a.DISTRIBUTION_AMOUNT) DISTRIBUTION_AMOUNT,
		a.SUSPENSE_REASON_CODE,
		a.ORGANIZATION_ID,
		a.ACTUAL_PROJECT_ID,
		a.ACTUAL_EXP_ORG_ID,
		a.ACTUAL_EXPENDITURE_TYPE,
		a.ACTUAL_TASK_ID,
		a.ACTUAL_AWARD_ID,
		a.ACTUAL_GL_CCID,
		a.DISTRIBUTION_DATE,
		a.PROJECT_ID,
		a.EXPENDITURE_ORGANIZATION_ID,
		a.EXPENDITURE_TYPE,
		a.TASK_ID,
		a.AWARD_ID,
		a.GL_CODE_COMBINATION_ID,
		a.currency_code,
		PSP_PSPLDSSA_XMLP_PKG.cf_person_nameformula(a.PERSON_ID) CF_PERSON_NAME,
		--PSP_PSPLDSSA_XMLP_PKG.cf_employee_numberformula(a.PERSON_ID) CF_EMPLOYEE_NUMBER,
		PSP_PSPLDSSA_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID) CF_ASSIGNMENT_NUMBER,
		PSP_PSPLDSSA_XMLP_PKG.cf_element_nameformula(a.ELEMENT_TYPE_ID) CF_ELEMENT_NAME,
        PSP_PSPLDSSA_XMLP_PKG.cf_gl_code_combination_idformu(a.GL_CODE_COMBINATION_ID,a.PROJECT_ID,a.TASK_ID,a.AWARD_ID,a.EXPENDITURE_ORGANIZATION_ID,a.EXPENDITURE_TYPE) CF_GL_CODE_COMBINATION_ID,
		PSP_PSPLDSSA_XMLP_PKG.CF_PERIOD_NAMEFORMULA(a.TIME_PERIOD_ID,a.PAYROLL_ID) CF_PERIOD_NAME,
		PSP_PSPLDSSA_XMLP_PKG.CF_ORGANIZATION_NAMEFORMULA(a.ORGANIZATION_ID) CF_ORGANIZATION_NAME,
		PSP_PSPLDSSA_XMLP_PKG.CF_ACTUAL_GL_CCIDFORMULA(a.ACTUAL_GL_CCID,a.ACTUAL_PROJECT_ID,a.ACTUAL_TASK_ID,a.ACTUAL_AWARD_ID,a.ACTUAL_EXP_ORG_ID,a.ACTUAL_EXPENDITURE_TYPE) CF_ACTUAL_GL_CCID
		,PSP_PSPLDSSA_XMLP_PKG.CF_sum_currencyFormula(a.currency_code) CF_SUM_CURRENCY
		,PSP_PSPLDSSA_XMLP_PKG.CF_CURRENCY_FORMATFORMULA(a.currency_code) CF_CURRENCY_FORMAT
FROM		PSP_REP_SSP_CHG1_V a
WHERE		(a.DISTRIBUTION_DATE between :P_TIME_PERIOD_START and :P_TIME_PERIOD_END) 
 &P_ORGANIZATION_ID
AND		a.business_group_id = :P_BUSINESS_GROUP_ID
AND		a.set_of_books_id = :P_SET_OF_BOOKS_ID
GROUP BY 	a.ORGANIZATION_ID, 
		a.PAYROLL_ID,
		a.TIME_PERIOD_ID,
		a.ACTUAL_PROJECT_ID,
		a.ACTUAL_EXP_ORG_ID,
		a.ACTUAL_EXPENDITURE_TYPE,
		a.ACTUAL_TASK_ID,
		a.ACTUAL_AWARD_ID,
		a.ACTUAL_GL_CCID,
		a.PERSON_ID,
		a.ASSIGNMENT_ID,
		a.ELEMENT_TYPE_ID,
		a.PROJECT_ID,
		a.EXPENDITURE_ORGANIZATION_ID,
		a.EXPENDITURE_TYPE,
		a.TASK_ID,
		a.AWARD_ID,
		a.GL_CODE_COMBINATION_ID,	
		a.DISTRIBUTION_DATE,
		a.SUSPENSE_REASON_CODE,
		a.currency_code
		--ORDER BY 22 ASC,7 ASC,16 ASC,17 
  --ASC,18 ASC,19 ASC,20 ASC,21 ASC
  ORDER BY CF_ORGANIZATION_NAME,a.currency_code,CF_ACTUAL_GL_CCID,CF_PERIOD_NAME,CF_ASSIGNMENT_NUMBER,CF_ELEMENT_NAME,
CF_PERIOD_NAME,CF_GL_CODE_COMBINATION_ID,a.SUSPENSE_REASON_CODE,a.PROJECT_ID,a.EXPENDITURE_ORGANIZATION_ID,
a.EXPENDITURE_TYPE,	a.TASK_ID,a.AWARD_ID,a.GL_CODE_COMBINATION_ID,SUM(a.DISTRIBUTION_AMOUNT),a.DISTRIBUTION_DATE
Parameter Name SQL text Validation
Organization Set
 
LOV Oracle
Begin Date
 
Date
End Date
 
Date
P_BUSINESS_GROUP_ID
 
Number
P_SET_OF_BOOKS_ID
 
Number
PSP_REPORT_TYPE