PSP Employee Labor Schedule
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 ...
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
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 | |
---|---|---|---|
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 |