[Home] [Help]
88: End if ;
89:
90:
91: IF p_group_id IS NULL THEN
92: select wip_indicators_temp_s.nextval into x_main_group_id
93: from sys.dual ;
94: ELSE
95: x_main_group_id := p_group_id ;
96: END IF;
105: p_resource_id) LOOP
106:
107: -- Generate the new Sequence for this
108: begin
109: select wip_indicators_temp_s.nextval into x_temp_group_id
110: from sys.dual ;
111: exception
112: when others then
113: x_phase := 'IV';
205: End if ;
206:
207:
208: IF p_group_id IS NULL THEN
209: select wip_indicators_temp_s.nextval into x_main_group_id
210: from sys.dual ;
211: ELSE
212: x_main_group_id := p_group_id ;
213: END IF;
223: p_resource_id) LOOP
224:
225: -- Generate the new Sequence for this
226: begin
227: select wip_indicators_temp_s.nextval into x_temp_group_id
228: from sys.dual ;
229: exception
230: when others then
231: x_phase := 'IV';
326:
327:
328:
329: IF p_group_id IS NULL THEN
330: select wip_indicators_temp_s.nextval into x_main_group_id
331: from sys.dual ;
332: ELSE
333: x_main_group_id := p_group_id ;
334: END IF;
343: p_resource_id) LOOP
344:
345: -- Generate the new Sequence for this
346: begin
347: select wip_indicators_temp_s.nextval into x_temp_group_id
348: from sys.dual ;
349: exception
350: when others then
351: x_phase := 'IV';
455: ***********************************************/
456:
457:
458: IF p_group_id IS NULL THEN
459: select wip_indicators_temp_s.nextval into x_main_group_id
460: from sys.dual ;
461: ELSE
462: x_main_group_id := p_group_id ;
463: END IF;
472: p_resource_id) LOOP
473:
474: -- Generate the new Sequence for this
475: begin
476: select wip_indicators_temp_s.nextval into x_temp_group_id
477: from sys.dual ;
478: exception
479: when others then
480: x_phase := 'IV';
540: p_main_group_id NUMBER) IS
541:
542: Begin
543:
544: UPDATE Wip_Indicators_Temp
545: SET group_id = p_main_group_id
546: WHERE
547: group_id = p_temp_group_id ;
548:
854: * a new group id from the sequence
855: ***********************************************/
856:
857: IF p_group_id IS NULL THEN
858: select wip_indicators_temp_s.nextval into x_group_id
859: from sys.dual ;
860: ELSE
861: x_group_id := p_group_id ;
862: END IF;
913: -- of this procedure (that is a hack, you can do a join in this
914: -- sql statement and actually perform the whole intelligent insert
915: -- in this statement itself).
916:
917: insert into wip_indicators_temp(
918: group_id,
919: line_id,
920: transaction_date,
921: required_quantity,
954: -- Insert tha line availability for all the days when the line
955: -- is available, but there is no load on the line. I.e., there
956: -- are no records in WFS.
957:
958: -- insert into wip_indicators_temp(
959: -- group_id,
960: -- line_id,
961: -- transaction_date,
962: -- required_quantity,
993: --and bcd.calendar_date between p_date_from and p_date_to
994: --and bcd.seq_num is not null
995: --and bcd.calendar_date not in
996: -- ( Select distinct transaction_date
997: -- from wip_indicators_temp
998: -- where group_id = x_group_id
999: -- and indicator_type = WIP_LINE_LOAD
1000: -- and process_phase = WIP_LINE_LOAD_PHASE_ONE
1001: -- ) ;
1011: INTO x_org_id
1012: FROM wip_lines
1013: WHERE line_id = p_line_id;
1014: flm_timezone.init_timezone(x_org_id);
1015: UPDATE wip_indicators_temp wit
1016: SET wit.available_quantity = 0
1017: WHERE wit.group_id = x_group_id
1018: and flm_timezone.client_to_calendar(wit.transaction_date) NOT IN (
1019: SELECT bcd.calendar_date
1303: * a new group id from the sequence
1304: ***********************************************/
1305:
1306: IF p_group_id IS NULL THEN
1307: select wip_indicators_temp_s.nextval into x_group_id
1308: from sys.dual ;
1309: ELSE
1310: x_group_id := p_group_id ;
1311: END IF;
1453: -- This will insert a unique row for each one of shift in each day
1454: -- for which the resource was loaded. The left over resource load
1455: -- will be equally allocated across each of these unique rows.
1456:
1457: insert into wip_indicators_temp (
1458: group_id,
1459: wip_entity_id,
1460: organization_id,
1461: resource_id,
1549: -- Summarize the information inserted in the previous statement
1550: -- across the various days, as we do not have show the resource
1551: -- load by day, but we aggregate the information across the days
1552: -- for each resource.
1553: insert into wip_indicators_temp (
1554: group_id,
1555: organization_id,
1556: resource_id,
1557: resource_code,
1582: wit.creation_date,
1583: wit.created_by,
1584: wit.program_application_id
1585: from
1586: wip_indicators_temp wit
1587: where
1588: wit.group_id = x_group_id
1589: and wit.indicator_type = WIP_LINE_RL
1590: and wit.process_phase = WIP_LINE_RL_PHASE_ONE
1613:
1614:
1615: -- Delete the non-aggregated resource load information that
1616: -- was inserted with the process phase = 1.
1617: delete from wip_indicators_temp
1618: where group_id = x_group_id
1619: and indicator_type = WIP_LINE_RL
1620: and process_phase = WIP_LINE_RL_PHASE_ONE ;
1621:
1630: -- Update the gross availaibility for
1631: -- the various resources in the line operation
1632: -- across the various days that falls in the range that
1633: -- are specified as the parameters.
1634: UPDATE wip_indicators_temp wit
1635: SET wit.available_units = (
1636: select
1637: nvl(sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units),0)
1638: from