[Home] [Help]
411: Ahl_Debug_Pub.enable_debug;
412:
413: -- ASSUMPTIONS:
414: -- A Task can appear only once for a given visit (ahl_visit_tasks_b)
415: -- A Department can have only one shift (ahl_department_shifts)
416: -- Shift_Num is unique in bom_shift_times
417:
418: -- Validate Visit Id
419: IF (p_visit_id IS NULL) THEN
856: SELECT department_id, org.organization_id, dept.description dept_name
857: FROM bom_departments dept, hr_all_organization_units org
858: WHERE org.name = p_org_name AND
859: dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
860: EXISTS ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = dept.DEPARTMENT_ID) AND
861: ( (p_dept_name is not null and dept.description = p_dept_name)
862: or p_dept_name is null ) ;
863:
864: -- yazhou 24Aug2005 ends
865:
866: /* Added the exists condition to retrieve only departments with shifts */
867: CURSOR l_validate_dept_csr(l_dept_id IN NUMBER) IS
868: SELECT description FROM bom_departments WHERE
869: department_id = l_dept_id and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = bom_departments.DEPARTMENT_ID);
870: --
871: CURSOR l_validate_org_csr(l_org_name IN VARCHAR2) IS
872: SELECT name FROM hr_all_organization_units WHERE
873: name = l_org_name;
1552: CURSOR l_dept_id_csr(p_dept_name IN VARCHAR2, p_org_name IN VARCHAR2) IS
1553: SELECT department_id,org.organization_id FROM bom_departments dept, hr_all_organization_units org
1554: WHERE org.name = p_org_name AND
1555: dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
1556: dept.description = p_dept_name and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = dept.DEPARTMENT_ID);
1557:
1558: /* Added the exists condition to retrieve only departments with shifts */
1559: CURSOR l_validate_dept_csr(l_dept_id IN NUMBER) IS
1560: SELECT 'x' FROM bom_departments WHERE
1557:
1558: /* Added the exists condition to retrieve only departments with shifts */
1559: CURSOR l_validate_dept_csr(l_dept_id IN NUMBER) IS
1560: SELECT 'x' FROM bom_departments WHERE
1561: department_id = l_dept_id and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = bom_departments.DEPARTMENT_ID);
1562:
1563: CURSOR l_validate_plan_csr(l_plan_id IN NUMBER) IS
1564: SELECT 'x' FROM ahl_simulation_plans_b WHERE
1565: simulation_plan_id = l_plan_id;
3377: x_per_rsrc_tbl OUT NOCOPY Period_Rsrc_Req_Tbl_Type
3378: ) IS
3379:
3380: CURSOR l_dept_shift_csr(p_department_id IN NUMBER) IS
3381: SELECT calendar_code, shift_num FROM ahl_department_shifts
3382: WHERE department_id = p_department_id;
3383:
3384: CURSOR l_shift_times_csr(p_calendar_code IN VARCHAR2, p_shift_num IN NUMBER) IS
3385: SELECT FROM_TIME, TO_TIME FROM bom_shift_times
4049: p_department_id IN NUMBER
4050: ) IS
4051:
4052: CURSOR l_dept_shift_csr(p_department_id IN NUMBER) IS
4053: SELECT calendar_code, shift_num FROM ahl_department_shifts
4054: WHERE department_id = p_department_id;
4055:
4056: CURSOR l_shift_times_csr(p_calendar_code IN VARCHAR2, p_shift_num IN NUMBER) IS
4057: SELECT FROM_TIME, TO_TIME FROM bom_shift_times
4319: FUNCTION Not_A_Holiday(p_curr_date DATE, p_dept_id NUMBER) RETURN BOOLEAN
4320: IS
4321: l_junk VARCHAR2(1);
4322: BEGIN
4323: SELECT 'x' INTO l_junk FROM AHL_DEPARTMENT_SHIFTS ADS, BOM_CALENDAR_EXCEPTIONS BCE
4324: WHERE ADS.DEPARTMENT_ID = p_dept_id AND
4325: BCE.CALENDAR_CODE = ADS.CALENDAR_CODE AND
4326: BCE.EXCEPTION_TYPE = G_HOLIDAY_TYPE AND
4327: TRUNC(BCE.EXCEPTION_DATE) = TRUNC(p_curr_date);
4673: FROM bom_resource_shifts
4674: WHERE resource_id = p_bom_resource_id
4675: AND department_id = p_bom_department_id
4676: AND SHIFT_NUM = ( select shift_num
4677: FROM AHL_DEPARTMENT_SHIFTS
4678: WHERE department_id = p_bom_department_id);
4679:
4680: -- l_res_type NUMBER;
4681: -- l_bom_org_id NUMBER;