PSP Employee Labor Schedule

➜ Back to Library
Description
Categories: BI Publisher
Columns: Sch Assignment Id, Hierarchy, Schedule Begin Date, Schedule End Date, Schedule Percent, Type, Scheduling Types Code, Gl Code Combination Id, Project Id, Expenditure Organization Id ...
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 NameSQL textValidation
Organization Set
 
LOV Oracle
Report Type
 
LOV Oracle
Begin Date
 
Date
End Date
 
Date
P_BUSINESS_GROUP_ID
 
Number
P_SET_OF_BOOKS_ID
 
Number
PSP_REPORT_TYPE
 
Download
Related Blitz Reports
PSP Encumbrance Suspense Charge PSP Organization Default Usage Encumbrance PSP Encumbrance History by Organization PSP Encumbrance process run results PSP Encumbrance History by Award PSP Reconciliation between sublines and distribution lines PSP Organization Level Default Labor Schedule PSP Distribution Adjustment Register PSP Distribution History by Award
Blitz Report In Action
Blitz Report™