PSP Employee Labor Schedule

➜ Back to Library
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
Download
Related Blitz Reports
PSP Organization Default Usage Encumbrance
PSP Encumbrance Suspense Charge
PSP Encumbrance History by Award
PSP Reconciliation between sublines and distribution lines
PSP Encumbrance process run results
PSP Encumbrance History by Organization
PSP Organization Level Default Labor Schedule
PSP Distribution Adjustment Register
PSP Distribution Default Account
Blitz Report In Action