PSP Employee Labor Schedule

Description
Categories: BI Publisher, Human Resources
Application: Labor Distribution
Source: PSP: Employee Labor Schedule Report (XML)
Short Name: PSPLSEMP_XML
DB package: PSP_PSPLSEMP_XMLP_PKG
SELECT	psh.assignment_id sch_assignment_id,
	psl.schedule_hierarchy_id Hierarchy,
	psl.schedule_begin_date,
	psl.schedule_end_date,
	psl.schedule_percent,
	psh.assignment_id Type,
	psh.scheduling_types_code,
	psl.gl_code_combination_id,
	psl.project_id,
	psl.expenditure_organization_id,
	psl.expenditure_type,
	psl.task_id,
	psl.award_id,
	1 row_ind
	,PSP_PSPLSEMP_XMLP_PKG.CF_DESCRIPTIONFORMULA(psh.assignment_id,psh.scheduling_types_code) CF_DESCRIPTION
	,PSP_PSPLSEMP_XMLP_PKG.CF_HIER_DESCFORMULA(psh.scheduling_types_code,PSP_PSPLSEMP_XMLP_PKG.CF_DESCRIPTIONFORMULA(psh.assignment_id,psh.scheduling_types_code)) CF_HIER_DESC
	,PSP_PSPLSEMP_XMLP_PKG.CF_SCH_BEGIN_DATE_DISPFORMULA(psl.schedule_begin_date,:CF_DATE_FORMAT) CF_SCH_BEGIN_DATE_DISP
	,PSP_PSPLSEMP_XMLP_PKG.CF_SCH_END_DATE_DSPFORMULA(psl.schedule_end_date,:CF_DATE_FORMAT) CF_SCH_END_DATE_DISP
	,PSP_PSPLSEMP_XMLP_PKG.CF_GL_DESCRIPTIONFORMULA(psl.gl_code_combination_id) CF_GL_DESCRIPTION
	,PSP_PSPLSEMP_XMLP_PKG.CF_PROJECT_NUMBERFORMULA(psl.project_id) CF_PROJECT_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_TASK_NUMBERFORMULA(psl.task_id) CF_TASK_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_AWARD_NUMBERFORMULA(psl.award_id) CF_AWARD_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_EXP_ORG_NAMEFORMULA(psl.expenditure_organization_id) CF_EXP_ORG_NAME
FROM	psp_schedule_hierarchy psh,
	psp_schedule_lines psl
WHERE	psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
AND	psh.scheduling_types_code = 'A'
AND	psl.schedule_begin_date <= :p_end_date
AND	psl.schedule_end_date >= :p_begin_date
AND	psl.business_group_id = :p_business_group_id
AND	psl.set_of_books_id = :p_set_of_books_id
and psh.assignment_id=:assignment_id
UNION ALL
SELECT	psh.assignment_id sch_assignment_id,
	psl.schedule_hierarchy_id Hierarchy,
	psl.schedule_begin_date,
	psl.schedule_end_date,
	psl.schedule_percent,
	peg.element_group_id Type,
	psh.scheduling_types_code,
	psl.gl_code_combination_id,
	psl.project_id,
	psl.expenditure_organization_id,
	psl.expenditure_type,
	psl.task_id,
	psl.award_id,
	1 row_ind
	,PSP_PSPLSEMP_XMLP_PKG.CF_DESCRIPTIONFORMULA(peg.element_group_id,psh.scheduling_types_code) CF_DESCRIPTION
	,PSP_PSPLSEMP_XMLP_PKG.CF_HIER_DESCFORMULA(psh.scheduling_types_code,PSP_PSPLSEMP_XMLP_PKG.CF_DESCRIPTIONFORMULA(peg.element_group_id,psh.scheduling_types_code)) CF_HIER_DESC
	,PSP_PSPLSEMP_XMLP_PKG.CF_SCH_BEGIN_DATE_DISPFORMULA(psl.schedule_begin_date,:CF_DATE_FORMAT) CF_SCH_BEGIN_DATE_DISP
	,PSP_PSPLSEMP_XMLP_PKG.CF_SCH_END_DATE_DSPFORMULA(psl.schedule_end_date,:CF_DATE_FORMAT) CF_SCH_END_DATE_DISP
	,PSP_PSPLSEMP_XMLP_PKG.CF_GL_DESCRIPTIONFORMULA(psl.gl_code_combination_id) CF_GL_DESCRIPTION
	,PSP_PSPLSEMP_XMLP_PKG.CF_PROJECT_NUMBERFORMULA(psl.project_id) CF_PROJECT_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_TASK_NUMBERFORMULA(psl.task_id) CF_TASK_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_AWARD_NUMBERFORMULA(psl.award_id) CF_AWARD_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_EXP_ORG_NAMEFORMULA(psl.expenditure_organization_id) CF_EXP_ORG_NAME
FROM	psp_schedule_hierarchy psh,
	psp_schedule_lines psl,
	psp_element_groups peg
WHERE	psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
AND	psh.element_group_id = peg.element_group_id
AND	psh.scheduling_types_code = 'EG'
AND	psl.schedule_begin_date <= :p_end_date
AND	psl.schedule_end_date >= :p_begin_date
AND	psl.business_group_id = :p_business_group_id
AND	psl.set_of_books_id = :p_set_of_books_id
AND	peg.business_group_id = :p_business_group_id
AND	peg.set_of_books_id = :p_set_of_books_id
and psh.assignment_id=:assignment_id
UNION ALL
SELECT	psh.assignment_id sch_assignment_id,
	psl.schedule_hierarchy_id Hierarchy,
	psl.schedule_begin_date,
	psl.schedule_end_date,
	psl.schedule_percent,
	pet.element_type_id Type,
	psh.scheduling_types_code,
	psl.gl_code_combination_id,
	psl.project_id,
	psl.expenditure_organization_id,
	psl.expenditure_type,
	psl.task_id,
	psl.award_id,
	1 row_ind
	,PSP_PSPLSEMP_XMLP_PKG.CF_DESCRIPTIONFORMULA(pet.element_type_id,psh.scheduling_types_code) CF_DESCRIPTION
	,PSP_PSPLSEMP_XMLP_PKG.CF_HIER_DESCFORMULA(psh.scheduling_types_code,PSP_PSPLSEMP_XMLP_PKG.CF_DESCRIPTIONFORMULA(pet.element_type_id,psh.scheduling_types_code)) CF_HIER_DESC
	,PSP_PSPLSEMP_XMLP_PKG.CF_SCH_BEGIN_DATE_DISPFORMULA(psl.schedule_begin_date,:CF_DATE_FORMAT) CF_SCH_BEGIN_DATE_DISP
	,PSP_PSPLSEMP_XMLP_PKG.CF_SCH_END_DATE_DSPFORMULA(psl.schedule_end_date,:CF_DATE_FORMAT) CF_SCH_END_DATE_DISP
	,PSP_PSPLSEMP_XMLP_PKG.CF_GL_DESCRIPTIONFORMULA(psl.gl_code_combination_id) CF_GL_DESCRIPTION
	,PSP_PSPLSEMP_XMLP_PKG.CF_PROJECT_NUMBERFORMULA(psl.project_id) CF_PROJECT_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_TASK_NUMBERFORMULA(psl.task_id) CF_TASK_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_AWARD_NUMBERFORMULA(psl.award_id) CF_AWARD_NUMBER
	,PSP_PSPLSEMP_XMLP_PKG.CF_EXP_ORG_NAMEFORMULA(psl.expenditure_organization_id) CF_EXP_ORG_NAME
FROM	psp_schedule_hierarchy psh,
	psp_schedule_lines psl,
	psp_element_types_v pet
WHERE	psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
AND	psh.element_type_id = pet.element_type_id
AND	psh.scheduling_types_code = 'ET'
AND	psl.schedule_begin_date <= :p_end_date
AND	psl.schedule_end_date >= :p_begin_date
AND	psl.business_group_id = :p_business_group_id
AND	psl.set_of_books_id = :p_set_of_books_id
AND	pet.business_group_id = :p_business_group_id
AND	pet.set_of_books_id = :p_set_of_books_id
 and psh.assignment_id=:assignment_id
--ORDER BY 1, 2, 3, 4
ORDER BY 1 ASC,6 ASC,2 ASC,7 ASC , 1 , 2 , 3 , 4
Parameter Name SQL text Validation
PSP_REPORT_TYPE
 
P_SET_OF_BOOKS_ID
 
Number
P_BUSINESS_GROUP_ID
 
Number
End Date
 
Date
Begin Date
 
Date
Report Type
 
LOV Oracle
Organization Set
 
LOV Oracle