[Home] [Help]
1052: end if ;
1053: --dbms_output.put_line('Before Stage 3 Phase I');
1054:
1055: -- Populate the fresh resource availability data into the
1056: -- mrp_net_resource_avail table.
1057: -- Calculate_Resource_Avail will call an MRP API to do this job
1058: -- per org.
1059: -- Note that in calc_resource_avail, we pass in NULL as the
1060: -- argument to the arg_simulation_set paramter to the MRP API.
1104: end if ;
1105: --dbms_output.put_line('Before Stage 3 Phase II');
1106:
1107: -- Pre-aggregate all the information needed from
1108: -- mrp_net_resource_avail and group the data by the attributes
1109: -- needed by the rest of the program. MNRA is a large table, so
1110: -- do this once, and then use this temp table to get values from.
1111: -- We want to pick only the rows that are referenced by the
1112: -- simulation_set = NULL condition, because this is what we specified
1139: nvl(sum(((decode(sign(to_time - from_time),
1140: -1, ( 86400 - from_time ) + to_time,
1141: 1, ( to_time - from_time ) ,
1142: 0 ))/3600)*capacity_units),0))
1143: FROM mrp_net_resource_avail
1144: WHERE simulation_set IS NULL
1145: and shift_date BETWEEN x_date_from AND (x_date_to + 0.99999)
1146: GROUP BY
1147: trunc (shift_date),
1176: nvl(sum(((decode(sign(to_time - from_time),
1177: -1, ( 86400 - from_time ) + to_time,
1178: 1, ( to_time - from_time ) ,
1179: 0 ))/3600)*capacity_units),0))
1180: FROM mrp_net_resource_avail mrp_outer
1181: WHERE simulation_set IS NULL
1182: and mrp_outer.shift_date BETWEEN x_date_from AND (x_date_to + 0.99999) --3779182
1183: AND mrp_outer.resource_id = nvl(p_resource_id, mrp_outer.resource_id)
1184: AND mrp_outer.department_id = nvl(p_department_id, mrp_outer.department_id)
3292:
3293:
3294: /* Move_Utz_Info
3295: Move the utilization information that doesnot have
3296: job and op seq reference from mrp_net_resource_avail
3297: into wip_bis_prod_indicators
3298: */
3299: /* PROCEDURE Move_Utz_Info( --comment for bug 3662056
3300: p_group_id IN NUMBER,
3537: trunc(mnra.shift_date) shift_date
3538: FROM
3539: bom_resources br,
3540: bom_departments bd,
3541: mrp_net_resource_avail mnra,
3542: mtl_units_of_measure muom
3543: where
3544: trunc(mnra.shift_date) between trunc(g_date_from) and trunc(g_date_to)
3545: and trunc(mnra.shift_date) >= trunc(br.creation_date)
4024: BEGIN
4025:
4026: SELECT count(*)
4027: INTO x_from_count
4028: FROM mrp_net_resource_avail
4029: WHERE organization_id = p_organization_id
4030: AND shift_date BETWEEN p_date_from AND p_date_from + 0.99999
4031: AND simulation_set is null ;
4032:
4038:
4039: BEGIN
4040: SELECT count(*)
4041: INTO x_to_count
4042: FROM mrp_net_resource_avail
4043: WHERE organization_id = p_organization_id
4044: AND shift_date BETWEEN p_date_to AND p_date_to +0.99999
4045: AND simulation_set is null ;
4046:
4395: -1, ( 86400 - from_time ) + to_time,
4396: 1, ( to_time - from_time ) ,
4397: 0 ))/3600)*capacity_units),0))
4398: from
4399: mrp_net_resource_avail mnra
4400: where
4401: mnra.organization_id = wit.organization_id
4402: and mnra.department_id = wit.department_id
4403: and mnra.resource_id = wit.resource_id
4460: g_applicationid
4461: from
4462: bom_resources br,
4463: bom_departments bd,
4464: mrp_net_resource_avail mnra,
4465: mtl_units_of_measure muom
4466: where
4467: mnra.shift_date between trunc(x_date_from) and
4468: trunc(x_date_to) + 0.99999
4799: sysdate,
4800: g_userid,
4801: g_applicationid
4802: from
4803: mrp_net_resource_avail mnra,
4804: bom_departments bd,
4805: bom_department_resources bdr,
4806: wip_operations_v wo,
4807: wip_operation_resources_v wor,
4878: -1, ( 86400 - from_time ) + to_time,
4879: 1, ( to_time - from_time ) ,
4880: 0 ))/3600)*capacity_units),0))
4881: from
4882: mrp_net_resource_avail mnra
4883: where
4884: mnra.organization_id = wit.organization_id
4885: and mnra.department_id = wit.department_id
4886: and mnra.resource_id = wit.resource_id
4943: g_applicationid
4944: from
4945: bom_resources br,
4946: bom_departments bd,
4947: mrp_net_resource_avail mnra,
4948: mtl_units_of_measure muom
4949: where
4950: mnra.shift_date between trunc(x_date_from) and
4951: trunc(x_date_to) + 0.99999
5039: p_date_from IN DATE,
5040: p_date_to IN DATE
5041: ) IS
5042: select distinct organization_id, department_id, resource_id
5043: from mrp_net_resource_avail
5044: where organization_id = nvl(p_organization_id, organization_id)
5045: and department_id = nvl(p_department_id, department_id)
5046: and resource_id = nvl(p_resource_id, resource_id)
5047: and trunc(shift_date) between trunc(p_date_from)
5120: g_applicationid
5121: from
5122: bom_resources br,
5123: bom_departments bd,
5124: mrp_net_resource_avail mnra,
5125: mtl_units_of_measure muom
5126: where
5127: mnra.shift_date between trunc(p_date_from) and
5128: trunc(p_date_to) + 0.99999
5217: nvl(count(*),0)
5218: into
5219: x_no_of_day_shifts
5220: from
5221: mrp_net_resource_avail
5222: where resource_id = p_resource_id
5223: and organization_id = p_organization_id
5224: and simulation_set is null
5225: and shift_date = p_transaction_date ;
5236: nvl(count(distinct shift_date),0)
5237: into
5238: x_total_days
5239: from
5240: mrp_net_resource_avail
5241: where resource_id = p_resource_id
5242: and organization_id = p_organization_id
5243: and simulation_set is null
5244: and shift_date between p_start_date and p_completion_date ;