[Home] [Help]
578: x_msg_data OUT NOCOPY VARCHAR2
579: ) IS
580:
581: CURSOR l_dept_id_csr(p_dept_name IN VARCHAR2, p_org_name IN VARCHAR2) IS
582: SELECT department_id FROM bom_departments dept, hr_all_organization_units org
583: WHERE org.name = p_org_name AND
584: dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
585: dept.description = p_dept_name;
586:
584: dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
585: dept.description = p_dept_name;
586:
587: CURSOR l_validate_dept_csr(l_dept_id IN NUMBER) IS
588: SELECT 'x' FROM bom_departments WHERE
589: department_id = l_dept_id;
590:
591: CURSOR l_validate_plan_csr(l_plan_id IN NUMBER) IS
592: SELECT 'x' FROM ahl_simulation_plans_b WHERE
853: -- Added department name as Select field
854:
855: CURSOR l_dept_id_csr(p_dept_name IN VARCHAR2, p_org_name IN VARCHAR2) IS
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)
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
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;
905: l_required_quantity NUMBER;
906: l_unused_capacity NUMBER;
907: l_dept_id NUMBER := NULL;
908: l_org_id NUMBER := NULL;
909: l_department_name bom_departments.description%type;
910: l_period_start_time DATE;
911: l_period_end_time DATE;
912:
913: l_idx NUMBER := 0;
925: l_name HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
926: l_deprt_id NUMBER;
927: l_orga_id NUMBER;
928: l_junk VARCHAR2(1);
929: l_dept_name bom_departments.description%type;
930:
931: BEGIN
932: -- Standard start of API savepoint
933: SAVEPOINT Get_Rsrc_Req_By_Period_pvt;
1549: to add the exists condition to retrieve only department with shifts
1550: */
1551:
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:
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
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
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;
2586:
2587: --
2588: CURSOR l_get_dept_desc_cur (p_bom_department_id IN NUMBER) IS
2589: SELECT department_code,description
2590: FROM bom_departments
2591: WHERE department_id = p_bom_department_id;
2592: --
2593: l_shift_num NUMBER;
2594: l_shift_capacity NUMBER;
3397:
3398: CURSOR l_dept_name_csr (c_org_id IN NUMBER,
3399: c_dept_id IN NUMBER)
3400: IS
3401: SELECT description FROM bom_departments
3402: WHERE organization_id = c_org_id
3403: AND department_id = c_dept_id;
3404:
3405: l_api_name CONSTANT VARCHAR2(30) := 'Create_Time_Periods';
3747: CURSOR l_get_dept_cur (c_dept_name IN VARCHAR2,
3748: c_org_name IN VARCHAR2)
3749: IS
3750: SELECT B.department_id,A.organization_id FROM
3751: HR_ALL_ORGANIZATION_UNITS A, BOM_DEPARTMENTS B
3752: WHERE A.organization_id = B.organization_id
3753: AND a.name = c_org_name
3754: AND b.description = c_dept_name;
3755: --
4074: ORDER BY EXCEPTION_DATE;
4075: --
4076: CURSOR l_dept_name_csr(c_dept_id IN NUMBER)
4077: IS
4078: SELECT description FROM bom_departments
4079: WHERE department_id = c_dept_id;
4080:
4081: l_visit_start_date DATE := TRUNC(p_visit_start_date);
4082: l_temp_date DATE;