[Home] [Help]
801: res_passed := 'DONOT_RETRIEVE_CALENDAR' ;
802:
803: /* B13866449 Rajesh Patangya */
804: PRG_EXECUTING := 'MSC';
805: log_message('REGENERATE THE CALENDAR insert_gmp_resource_avail call = ' || p_db_link );
806: log_message('Calendar_no= ' || cursor_rec.calendar_no ||' and org_id= '|| cursor_rec.organization_id || 'RSRC= '|| res_passed);
807:
808: sql_regen :=
809: ' BEGIN apps.gmp_calendar_pkg.insert_gmp_resource_avail'||p_db_link||'(:p1, :p2, :p3, :p4, :p5, :p6); END ;' ;
805: log_message('REGENERATE THE CALENDAR insert_gmp_resource_avail call = ' || p_db_link );
806: log_message('Calendar_no= ' || cursor_rec.calendar_no ||' and org_id= '|| cursor_rec.organization_id || 'RSRC= '|| res_passed);
807:
808: sql_regen :=
809: ' BEGIN apps.gmp_calendar_pkg.insert_gmp_resource_avail'||p_db_link||'(:p1, :p2, :p3, :p4, :p5, :p6); END ;' ;
810:
811: EXECUTE IMMEDIATE sql_regen USING
812: OUT v_errbuf , OUT v_retcode,
813: in cursor_rec.organization_id ,
813: in cursor_rec.organization_id ,
814: in res_passed, in l_dummy,
815: in cursor_rec.calendar_no ;
816:
817: log_message('After insert_gmp_resource_avail call = ' || v_errbuf || ' REG '|| v_retcode );
818: time_stamp;
819: PRG_EXECUTING := 'MSC';
820:
821: END IF ;
891: || ' gra.to_time, '
892: || ' gra.resource_units, '
893: || ' 2 '
894: || ' FROM '
895: || ' gmp_resource_avail'||p_db_link||' gra, '
896: || ' mtl_parameters'||p_db_link||' m, '
897: || ' cr_rsrc_dtl'||p_db_link||' crd '
898: || ' WHERE nvl(gra.resource_instance_id,0) = 0 '
899: || ' AND nvl(crd.calendar_code,m.calendar_code) = gra.calendar_code '
940: || ' ((gra.organization_id*2)+1) department_id, '/* encoded */
941: || ' :instance_id '
942: || ' FROM '
943: || ' gmp_resource_instances'||p_db_link||' gri, '
944: || ' gmp_resource_avail'||p_db_link||' gra, '
945: || ' mtl_parameters'||p_db_link||' m, '
946: || ' cr_rsrc_dtl'||p_db_link||' crd '
947: || ' WHERE gri.resource_id = gra.resource_id '
948: || ' AND gri.instance_id = gra.resource_instance_id '
2039:
2040: IF (p_usage = 'WPS') THEN /* Usage WPS */
2041: log_message('p_usage = WPS from net_rsrc_insert');
2042: FORALL i IN first_in..last_in
2043: INSERT INTO gmp_resource_avail
2044: (
2045: instance_id, organization_id, resource_id,
2046: calendar_code, resource_instance_id, shift_num,
2047: shift_date, from_time, to_time,
2108:
2109: /*
2110: REM+==========================================================================+
2111: REM| PROCEDURE NAME |
2112: REM| insert_gmp_resource_avail |
2113: REM| |
2114: REM| Type |
2115: REM| public |
2116: REM| |
2130: REM| B4724360 - 12-DEC-2005 Modified code to TO ADD TIME OR A SHIFT TO A |
2131: REM| PLANT RESOURCE |
2132: REM+==========================================================================+
2133: */
2134: PROCEDURE insert_gmp_resource_avail(errbuf OUT NOCOPY varchar2,
2135: retcode OUT NOCOPY number ,
2136: p_org_id IN PLS_INTEGER ,
2137: p_from_rsrc IN varchar2 ,
2138: p_to_rsrc IN varchar2 ,
2176: SOURCE_DBNAME varchar2(16) ;
2177: delete_msg VARCHAR2(500) ;
2178:
2179: BEGIN
2180: proc_name := 'insert_gmp_resource_avail'; /* B13866449 Rajesh Patangya */
2181: SOURCE_DBNAME := null; /* B13866449 Rajesh Patangya */
2182: cal_index := 1 ;
2183: i := 1 ;
2184: stmt_no := 0 ;
2216: IF (NVL(P_FROM_RSRC,'RETRIEVE') <> 'DONOT_RETRIEVE_CALENDAR') THEN
2217: COMMIT;
2218: END IF; */
2219:
2220: log_message(' P_FROM_RSRC from insert_gmp_resource_avail: '||P_FROM_RSRC || ' PRG_EXECUTING = ' || PRG_EXECUTING );
2221: log_message(' P_FROM_RSRC ELSE and Called_from value: '||Called_from );
2222:
2223: END IF;
2224:
2226: select name into SOURCE_DBNAME from V$database ;
2227: log_message(' SOURCE_DBNAME = ' || SOURCE_DBNAME) ;
2228: END;
2229:
2230: log_message('Entered insert_gmp_resource_avail PARAMETERS - ' ||p_org_id||'**'|| p_calendar_code
2231: || ' V_FROM_RSRC: '||V_FROM_RSRC||'** V_TO_RSRC: '||V_TO_RSRC||'** res_count: '||res_count ||'bom_result -'||bom_result);
2232:
2233: IF (Called_from = V_APS) THEN
2234: log_message(' Called_from = V_APS IF Condition' );
2232:
2233: IF (Called_from = V_APS) THEN
2234: log_message(' Called_from = V_APS IF Condition' );
2235: sql_1 := ' SELECT count(*) from '
2236: || ' ( select min(LAST_UPDATE_DATE) lud from gmp_resource_avail '
2237: || ' where CALENDAR_CODE = :cal_code '
2238: || ' and ORGANIZATION_ID = :orgn_id ) gmp, '
2239: || ' (select min(LAST_UPDATE_DATE) lud from bom_shift_dates '
2240: || ' where CALENDAR_CODE = :cal_code ) bom '
2272: AND NVL(c.CALENDAR_CODE,m.CALENDAR_CODE) = p_calendar_code
2273: AND c.resource_id = b.resource_id
2274: GROUP BY c.RESOURCES , c.RESOURCE_ID
2275: ) crd ,
2276: ( SELECT MIN(LAST_UPDATE_DATE) lud , resource_id FROM gmp_resource_avail
2277: WHERE CALENDAR_CODE = p_calendar_code
2278: AND ORGANIZATION_ID = p_org_id
2279: GROUP BY resource_id
2280: ) gmp
2284: FROM cr_rsrc_dtl c , mtl_parameters m
2285: WHERE c.ORGANIZATION_ID = p_org_id
2286: AND c.ORGANIZATION_ID = m.organization_id
2287: AND NVL(c.CALENDAR_CODE,m.CALENDAR_CODE) = p_calendar_code
2288: AND NOT EXISTS ( SELECT 1 FROM gmp_resource_avail gmp
2289: WHERE gmp.CALENDAR_CODE = p_calendar_code
2290: AND gmp.ORGANIZATION_ID = p_org_id
2291: AND c.resource_id = gmp.resource_id )
2292: ) ;
2390: FOR i in 1..cal_shift_rec.COUNT
2391: LOOP
2392:
2393: stmt_no := 1 ;
2394: delete_stmt := 'DELETE FROM gmp_resource_avail '||
2395: ' WHERE CALENDAR_CODE = :cal_code ' ||
2396: ' AND organization_id = :org_id1 ';
2397:
2398: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2490: WHEN NO_DATA_FOUND THEN
2491: log_message('Manufacturing Calendar is not assigned to '|| p_orgn_code);
2492: retcode := 1 ;
2493: WHEN OTHERS THEN
2494: log_message('insert_gmp_resource_avail ' || sqlerrm);
2495: retcode := 1 ;
2496:
2497: END insert_gmp_resource_avail;
2498:
2493: WHEN OTHERS THEN
2494: log_message('insert_gmp_resource_avail ' || sqlerrm);
2495: retcode := 1 ;
2496:
2497: END insert_gmp_resource_avail;
2498:
2499: /*
2500: REM+==========================================================================+
2501: REM| PROCEDURE NAME |
2951: last_in := last_index ;
2952: END IF;
2953:
2954: /*sowsubra - calendar convergence ME changes - organization_id is used in place
2955: of organization code in gmp_resource_avail*/
2956:
2957: FOR j IN first_index..end_index LOOP
2958:
2959: FORALL i IN first_in..last_in
2956:
2957: FOR j IN first_index..end_index LOOP
2958:
2959: FORALL i IN first_in..last_in
2960: INSERT INTO gmp_resource_avail
2961: (
2962: instance_id, organization_id, resource_id,
2963: calendar_code, resource_instance_id, shift_num,
2964: shift_date, from_time, to_time,
3032: || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_tt, '
3033: || ' lead(resource_units,1) over(order by resource_id, '
3034: || ' resource_instance_id, '
3035: || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_rc '
3036: || ' FROM gmp_resource_avail'
3037: || ' WHERE organization_id = :orgn_id1 '
3038: || ' AND calendar_code = :cal_code ' ;
3039:
3040: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
3072: LOOP
3073: FETCH ri_assembly INTO interval_record;
3074: EXIT WHEN ri_assembly%NOTFOUND;
3075:
3076: sql_del := 'DELETE FROM gmp_resource_avail'
3077: || ' WHERE organization_id = :org_id1 '
3078: || ' AND calendar_code = :cal_code '
3079: || ' AND resource_id = :prid '
3080: || ' AND resource_instance_id = :piid '
3114: CLOSE ri_assembly;
3115:
3116: stmt_no := 69;
3117:
3118: log_message('UPDATE gmp_resource_avail calender= ' || p_calendar_code || ' org_id= ' || p_org_id);
3119: -- Bug: 8916018 Vpedarla commented the below dynamic sql.
3120: -- Bug: 12557020 vkinduri/rpatangy modified the query for the correctness and performance
3121: sqlupt := 'UPDATE gmp_resource_avail'
3122: ||' SET to_time = 86400 '
3117:
3118: log_message('UPDATE gmp_resource_avail calender= ' || p_calendar_code || ' org_id= ' || p_org_id);
3119: -- Bug: 8916018 Vpedarla commented the below dynamic sql.
3120: -- Bug: 12557020 vkinduri/rpatangy modified the query for the correctness and performance
3121: sqlupt := 'UPDATE gmp_resource_avail'
3122: ||' SET to_time = 86400 '
3123: ||' WHERE to_time = 86399 '
3124: ||' AND shift_num >= 99999 '
3125: ||' AND calendar_code = :t_calendar_code '
3127: ||' AND resource_id <> 0' ;
3128:
3129: EXECUTE immediate sqlupt USING p_calendar_code, p_org_id ;
3130: sqlupt := null ;
3131: sqlupt := 'UPDATE gmp_resource_avail'
3132: ||' SET shift_num = (shift_num - 99999) '
3133: ||' WHERE shift_num >= 99999 '
3134: ||' AND calendar_code = :t_calendar_code '
3135: ||' AND organization_id = :t_org_id '
3136: ||' AND resource_id <> 0' ;
3137:
3138: EXECUTE immediate sqlupt USING p_calendar_code, p_org_id ;
3139:
3140: /* UPDATE gmp_resource_avail
3141: SET to_time = 86400 , shift_num = (shift_num - 99999)
3142: WHERE shift_num >= 99999
3143: AND calendar_code = p_calendar_code
3144: AND organization_id = p_org_id
3171: REM| Type |
3172: REM| public |
3173: REM| |
3174: REM| DESCRIPTION |
3175: REM| The following procedure inserts rows into gmp_resource_avail |
3176: REM| |
3177: REM| Input Parameters |
3178: REM| p_instance_id - Instance Id |
3179: REM| p_orgn_code - Plant Code |
3210: proc_name := 'net_rsrc_avail_insert';
3211: IF nvl(p_from_time,0) = 0 AND nvl(p_to_time,0) = 0 THEN
3212: NULL ;
3213: ELSE
3214: INSERT INTO gmp_resource_avail (
3215: instance_id, plant_code, resource_id,
3216: calendar_code, resource_instance_id, shift_num,
3217: shift_date, from_time, to_time,
3218: resource_units, creation_date, created_by,