[Home] [Help]
203: mgq2.row_index to_index,
204: mon.dependency_type
205: from msc_operation_networks mon,
206: msc_resource_requirements mrr,
207: msc_gantt_query mgq1,
208: msc_gantt_query mgq2
209: where mrr.plan_id = x_plan_id
210: and mrr.sr_instance_id = x_inst_id
211: and mrr.supply_id = x_supply_id
204: mon.dependency_type
205: from msc_operation_networks mon,
206: msc_resource_requirements mrr,
207: msc_gantt_query mgq1,
208: msc_gantt_query mgq2
209: where mrr.plan_id = x_plan_id
210: and mrr.sr_instance_id = x_inst_id
211: and mrr.supply_id = x_supply_id
212: and mrr.end_date is not null
434: and ms.sr_instance_id = x_instance_id
435: and ms.disposition_id = x_supply_id
436: -- and ms.order_type in (14,15,16,17,28)
437: and ms.transaction_id not in (select mgq.transaction_id
438: from msc_gantt_query mgq
439: where mgq.query_id = g_order_query_id
440: and mgq.transaction_id = ms.transaction_id)
441: order by ms.new_wip_start_date;
442:
512:
513: cursor c_op_row_index_cur (p_qid number,
514: p_supply_id number, p_op_seq_num number)is
515: select mgq.row_index
516: from msc_gantt_query mgq
517: where mgq.query_id = p_qid
518: and mgq.transaction_id = p_supply_id
519: and mgq.op_seq_num = p_op_seq_num;
520:
520:
521: cursor check_job_row_cur (p_query number,
522: p_plan number, p_inst number, p_trx number) is
523: select count(*)
524: from msc_gantt_query mgq
525: where mgq.query_id = p_query
526: --and mgq.plan_id = p_plan
527: and mgq.sr_instance_id = p_inst
528: and mgq.transaction_id = p_trx
529: and rownum = 1;
530:
531: cursor c_dmd_row_index (p_query number, p_inst number, p_trx number) is
532: select row_index
533: from msc_gantt_query mgq
534: where mgq.query_id = p_query
535: and mgq.sr_instance_id = p_inst
536: and mgq.transaction_id = p_trx;
537:
538: cursor check_supp_row_cur (p_query number,
539: p_plan number, p_inst number, p_org number, p_item number,
540: p_supp number, p_supp_site number) is
541: select count(*)
542: from msc_gantt_query mgq
543: where mgq.query_id = p_query
544: and mgq.plan_id = p_plan
545: and mgq.sr_instance_id = p_inst
546: and mgq.organization_id = p_org
550: and rownum = 1;
551:
552: cursor c_check_row_type (p_query number, p_index number) is
553: select node_type
554: from msc_gantt_query
555: where query_id = p_query
556: and row_index = p_index;
557:
558: cursor c_parent_link (p_query number, p_index number) is
556: and row_index = p_index;
557:
558: cursor c_parent_link (p_query number, p_index number) is
559: select parent_link
560: from msc_gantt_query
561: where query_id = p_query
562: and row_index = p_index;
563:
564: -- cursors -
590: instr(p_list,'(',1,i)+1,instr(p_list,')',1,i)-instr(p_list,'(',1,i)-1);
591: l_trx_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
592: end if; -- }
593:
594: update msc_gantt_query
595: set child_query_id = p_child_query_id
596: where query_id = p_parent_query_id
597: and transaction_id = l_trx_id ;
598:
608: put_line(' updateParentLinkforCoprod '
609: ||' p_row_index '|| p_row_index
610: ||' p_parent_link '|| p_parent_link);
611:
612: update msc_gantt_query
613: set parent_link = decode(parent_link, null,p_parent_link,
614: parent_link|| COMMA_SEPARATOR ||p_parent_link),
615: dependency_type = p_dependency_type
616: where query_id = p_query_id
627: put_line(' updateOpParentLink '
628: ||' p_row_index '|| p_row_index
629: ||' p_parent_link '|| p_parent_link);
630:
631: update msc_gantt_query
632: set parent_link = decode(parent_link,
633: null, p_parent_link || FIELD_SEPERATOR || nvl(to_char(p_dependency_type), null_space),
634: parent_link || FIELD_SEPERATOR || p_parent_link || FIELD_SEPERATOR || nvl(to_char(p_dependency_type), null_space)),
635: dependency_type = nvl(dependency_type,0)+1
642: p_parent_link varchar2, p_dependency_type number default null) is
643:
644: l_parent_row_type number;
645: l_child_row_type number;
646: l_child_parent_link msc_gantt_query.parent_link%type;
647: begin
648: if ( p_parent_link is null ) then -- {
649: return;
650: end if; -- }
671: if ( instr(l_child_parent_link,p_parent_link) <> 0 ) then
672: return;
673: end if;
674:
675: update msc_gantt_query
676: set parent_link = decode(parent_link, null,p_parent_link,
677: parent_link|| COMMA_SEPARATOR ||p_parent_link),
678: dependency_type = p_dependency_type
679: where query_id = p_query_id
994:
995: function getParentOpLink(p_supply_id1 number, p_supply_id2 number) return varchar2 is
996:
997: l_op_seq_num number;
998: l_parent_link msc_gantt_query.parent_link%type;
999: begin
1000: if ( g_peg_type = PEG_DOWN ) then -- {
1001: open c_op_seq_num_cur(g_dem_op_query_id, p_supply_id1, p_supply_id2);
1002: fetch c_op_seq_num_cur into l_op_seq_num;
1088:
1089: procedure addPegUpNodes(p_parent_index number,
1090: p_instance_id number, p_org_id number, p_supply_id number) is
1091:
1092: l_parent_link msc_gantt_query.parent_link%type;
1093: l_first_op number;
1094:
1095: l_inst_id msc_gantt_utils.number_arr;
1096: l_org_id msc_gantt_utils.number_arr;
1161: function isDupInMGQ(p_query number, p_inst number, p_trx number) return number is
1162:
1163: cursor c_dup is
1164: select row_index
1165: from msc_gantt_query
1166: where query_id = p_query
1167: and sr_instance_id = p_inst
1168: and transaction_id = p_trx;
1169:
1182: procedure addPegDownJobNodes(p_parent_index number,
1183: p_instance_id number, p_org_id number,
1184: p_supply_id number, p_fetch_type number) is
1185:
1186: l_parent_link msc_gantt_query.parent_link%type;
1187:
1188: l_inst_id msc_gantt_utils.number_arr;
1189: l_org_id msc_gantt_utils.number_arr;
1190: l_trans_id msc_gantt_utils.number_arr;
1301:
1302: cursor c_next_row (p_query_id number, p_row_index number) is
1303: select transaction_id, organization_id, sr_instance_id, op_seq_id,
1304: node_type, node_path
1305: from msc_gantt_query
1306: where query_id = p_query_id
1307: and row_index = p_row_index;
1308:
1309: cursor c_next_row_index (p_query_id number,
1308:
1309: cursor c_next_row_index (p_query_id number,
1310: p_row_index number, p_peg_dir number) is
1311: select row_index, '1' dummy_sort
1312: from msc_gantt_query
1313: where query_id = p_query_id
1314: and (node_type = JOB_NODE
1315: or (node_type = COPROD_NODE and p_peg_dir = PEG_UP)
1316: or (node_type = COPROD_NODE and p_peg_dir = PEG_DOWN)
1327: select decode(p_peg_dir,
1328: PEG_UP, node_level + PEG_UP,
1329: PEG_DOWN, node_level + PEG_DOWN,
1330: PEG_ORDERS, node_level + 1 )
1331: from msc_gantt_query
1332: where query_id = p_query_id
1333: and row_index = p_row_index;
1334:
1335: BEGIN
1491: l_min_node_level number;
1492:
1493: cursor c_min_level is
1494: select abs(min(node_level))
1495: from msc_gantt_query
1496: where query_id = p_query_id;
1497:
1498: begin
1499: open c_min_level;
1499: open c_min_level;
1500: fetch c_min_level into l_min_node_level;
1501: close c_min_level;
1502:
1503: update msc_gantt_query
1504: set node_level = l_min_node_level + node_level + 1
1505: where query_id = p_query_id;
1506: end updateNodeLevels;
1507:
1507:
1508: function getNodesCount(p_query_id number) return varchar2 is
1509: cursor c_node_count_cur is
1510: select node_type, count(*)
1511: from msc_gantt_query
1512: where query_id = p_query_id
1513: group by node_type;
1514:
1515: cursor c_first_row_cur is
1513: group by node_type;
1514:
1515: cursor c_first_row_cur is
1516: select min(row_index)
1517: from msc_gantt_query
1518: where query_id = p_query_id;
1519:
1520: l_node_type number;
1521: l_count number;
1556: p_refresh boolean default false) return number is
1557:
1558: cursor c_max_index_cur (l_query number) is
1559: select max(row_index)
1560: from msc_gantt_query
1561: where query_id = l_query ;
1562: BEGIN
1563: put_line('orderView in');
1564: g_node_index := 0;
1686: l_row_count number;
1687: begin
1688: select nvl(max(row_index),0)
1689: into l_row_index
1690: from msc_gantt_query
1691: where query_id = p_query_id;
1692:
1693: l_one_record := substr(p_node_list,instr(p_node_list,'(',1,1)+1,
1694: instr(p_node_list,')',1,1)-instr(p_node_list,'(',1,1)-1);
1725: p_from_index number, p_critical number) return number is
1726:
1727: cursor c_node_type_cur is
1728: select node_type, node_path
1729: from msc_gantt_query
1730: where query_id = p_from_query_id
1731: and row_index = p_from_index;
1732:
1733: l_node_type number;
1753: l_row_index := 1;
1754: else
1755: select nvl(max(row_index),0)
1756: into l_row_index
1757: from msc_gantt_query
1758: where query_id = p_to_query_id;
1759: checkRow := true;
1760: end if; -- }
1761:
1761:
1762: if (p_critical = CRITICAL_PATH ) then -- {
1763: select distinct sr_instance_id, organization_id, department_id, resource_id
1764: bulk collect into v_inst_id, v_org_id, v_dept_id, v_res_id
1765: from msc_gantt_query mgq
1766: where query_id = p_from_query_id
1767: and node_type = RES_NODE
1768: and ((p_from_index = 0) or ( (l_node_type in (JOB_NODE, OP_NODE) and node_path like l_node_path||':%')
1769: or (l_node_type = RES_NODE and row_index = p_from_index) ))
1770: and nvl(critical_flag, mbp_null_value) > 0;
1771: else
1772: select distinct sr_instance_id, organization_id, department_id, resource_id
1773: bulk collect into v_inst_id, v_org_id, v_dept_id, v_res_id
1774: from msc_gantt_query mgq
1775: where query_id = p_from_query_id
1776: and node_type = RES_NODE
1777: and ((p_from_index = 0) or ( (l_node_type in (JOB_NODE, OP_NODE) and node_path like l_node_path||':%')
1778: or (l_node_type = RES_NODE and row_index = p_from_index) ));
1800: p_from_index number) return number is
1801:
1802: cursor c_node_type_cur is
1803: select node_type, node_path
1804: from msc_gantt_query
1805: where query_id = p_from_query_id
1806: and row_index = p_from_index;
1807:
1808: l_node_type number;
1832: l_row_index := 1;
1833: else
1834: select nvl(max(row_index),0) + 1
1835: into l_row_index
1836: from msc_gantt_query
1837: where query_id = p_to_query_id;
1838: checkRow := true;
1839: end if; -- }
1840:
1844: ms.inventory_item_id,
1845: nvl(ms.supplier_id, mbp_null_value),
1846: nvl(ms.supplier_site_id, mbp_null_value)
1847: bulk collect into v_inst_id, v_org_id, v_item_id, v_supp_id, v_supp_site_id
1848: from msc_gantt_query mgq,
1849: msc_supplies ms
1850: where mgq.query_id = p_from_query_id
1851: and mgq.row_index = p_from_index
1852: and ms.plan_id = g_plan_id
1890: l_row_type number;
1891:
1892: cursor c_rowtype (ll_query number, ll_index number)is
1893: select mgq.res_instance_id
1894: from msc_gantt_query mgq
1895: where mgq.query_id = ll_query
1896: and mgq.row_index = ll_index;
1897:
1898: begin
1903: l_row_index := 1;
1904: else
1905: select nvl(max(row_index),0) + 1
1906: into l_row_index
1907: from msc_gantt_query
1908: where query_id = p_to_query_id;
1909: checkRow := true;
1910: g_order_query_id := p_to_query_id;
1911: end if; -- }
1916: if ( p_from_view_type = RES_ACTIVITIES_VIEW ) then -- {
1917: if (l_row_type = mbp_null_value) then
1918: select distinct mrr.sr_instance_id, mrr.organization_id, mrr.supply_id
1919: bulk collect into v_inst_id, v_org_id, v_trx_id
1920: from msc_gantt_query mgq,
1921: msc_gantt_dtl_query mgdq,
1922: msc_resource_requirements mrr
1923: where mgq.query_id = p_from_query_id
1924: and mgq.row_index = p_from_index
1934: and mrr.parent_id = 2;
1935: else
1936: select distinct mrir.sr_instance_id, mrir.organization_id, mrir.supply_id
1937: bulk collect into v_inst_id, v_org_id, v_trx_id
1938: from msc_gantt_query mgq,
1939: msc_gantt_dtl_query mgdq,
1940: msc_resource_instance_reqs mrir
1941: where mgq.query_id = p_from_query_id
1942: and mgq.row_index = p_from_index
1957: elsif ( p_from_view_type = RES_HOURS_VIEW ) then
1958:
1959: select distinct mrr.sr_instance_id, mrr.organization_id, mrr.supply_id
1960: bulk collect into v_inst_id, v_org_id, v_trx_id
1961: from msc_gantt_query mgq,
1962: msc_gantt_dtl_query mgdq,
1963: msc_resource_requirements mrr
1964: where mgq.query_id = p_from_query_id
1965: and mgq.row_index = p_from_index
1982: return 0; -- not supported for this view..
1983: elsif ( p_from_view_type = SUPPLIER_VIEW ) then
1984: select distinct msr.sr_instance_id, msr.organization_id, msr.supply_id
1985: bulk collect into v_inst_id, v_org_id, v_trx_id
1986: from msc_gantt_query mgq,
1987: msc_supplier_requirements msr
1988: where mgq.query_id = p_from_query_id
1989: and mgq.row_index = p_from_index
1990: and msr.plan_id = g_plan_id
2034: --
2035: function getGanttRowCount(p_query_id number) return number is
2036: cursor c_row is
2037: select count(*)
2038: from msc_gantt_query
2039: where query_id = p_query_id;
2040: l_count number;
2041: begin
2042: open c_row;
2070: p_filter_type, p_view_type, p_folder_id);
2071:
2072: elsif ( p_refresh ) then
2073:
2074: update msc_gantt_query
2075: set is_fetched = SYS_NO,
2076: row_flag = SYS_NO
2077: where query_id = p_query_id;
2078:
2079: delete from msc_gantt_dtl_query
2080: where query_id = p_query_id;
2081:
2082: else
2083: update msc_gantt_query
2084: set is_fetched = SYS_NO,
2085: row_flag = SYS_NO
2086: where query_id = p_query_id;
2087:
2124:
2125: end if; -- }
2126:
2127: -- set the is_fetched to SYS_YES
2128: update msc_gantt_query
2129: set is_fetched = SYS_YES
2130: where query_id = p_query_id
2131: and row_flag = SYS_YES ;
2132:
2154: msc_gantt_utils.populateListIntoGantt(p_query_id,
2155: p_plan_id, p_list, p_filter_type, p_view_type);
2156:
2157: elsif ( p_refresh ) then
2158: update msc_gantt_query
2159: set is_fetched = SYS_NO,
2160: row_flag = SYS_NO
2161: where query_id = p_query_id;
2162:
2162:
2163: delete from msc_gantt_dtl_query
2164: where query_id = p_query_id;
2165: else
2166: update msc_gantt_query
2167: set is_fetched = SYS_NO,
2168: row_flag = SYS_NO
2169: where query_id = p_query_id;
2170:
2179: msc_gantt_utils.prepareSupplierGantt(p_query_id, p_plan_id, g_plan_start_date, g_cutoff_date);
2180: msc_gantt_utils.sendSupplierGantt(p_query_id, p_supp_data);
2181:
2182: -- set the is_fetched to SYS_YES
2183: update msc_gantt_query
2184: set is_fetched = SYS_YES
2185: where query_id = p_query_id
2186: and row_flag = SYS_YES ;
2187: