[Home] [Help]
168: number2,
169: number3,
170: char9,
171: date1
172: from msc_form_query
173: where query_id = x_op_seq_query_id
174: and number1 = x_supply_id
175: /*
176: and number2 not in
275: ms.organization_id, ms.source_organization_id, ms.new_dock_date,
276: ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
277: ms.source_supplier_id) start_date,
278: ms.order_type
279: from msc_form_query mfq,
280: msc_supplies ms,
281: msc_system_items msi
282: where mfq.query_id = x_dem_op_query_id
283: and mfq.number1 = x_supply_id
309: ms.source_supplier_id) start_date,
310: ms.order_type
311: from msc_full_pegging mfp1,
312: msc_full_pegging mfp2,
313: msc_form_query mfq,
314: msc_supplies ms,
315: msc_system_items msi
316: where mfp1.plan_id = x_plan_id
317: and mfp1.sr_instance_id = x_instance_id
324: and ms.sr_instance_id = mfp2.sr_instance_id
325: and ms.transaction_id = mfp2.transaction_id
326: and ms.transaction_id not in (
327: select mfq.number3
328: from msc_form_query mfq
329: where mfq.query_id = x_dem_op_query_id
330: and mfq.number1 = x_supply_id
331: and mfq.number2 is not null
332: )
403: and ms.sr_instance_id = mfp2.sr_instance_id
404: and ms.transaction_id = mfp2.transaction_id
405: and ms.transaction_id not in (
406: select mfq.number3
407: from msc_form_query mfq
408: where mfq.query_id = x_dem_op_query_id
409: and mfq.number1 = x_supply_id
410: and mfq.number2 is not null
411: )
454: from msc_full_pegging mfp1,
455: msc_full_pegging mfp2,
456: msc_supplies ms,
457: msc_demands md,
458: msc_form_query mfq
459: where mfp1.plan_id = x_plan_id
460: and mfp1.transaction_id = x_supply_id
461: and mfp1.sr_instance_id = x_instance_id
462: and mfq.query_id = x_end_peg_query_id
504: --misc
505: cursor c_op_seq_num_cur (p_dem_op_query_id number,
506: p_supply_id number, p_trans_id number) is
507: select number2 --op_seq_num
508: from msc_form_query mfq
509: where mfq.query_id = p_dem_op_query_id
510: and mfq.number1 = p_supply_id
511: and mfq.number3 = p_trans_id;
512:
867:
868: procedure insertOpIntoMFQ(p_instance_id number, p_org_id number, p_supply_id number) is
869: begin
870:
871: insert into msc_form_query
872: (query_id,
873: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
874: number1, number2, number3, char9, date1)
875: select distinct
922:
923: -- supply_id, op_seq, tran_id, inst_id, org_id, critical
924: for i in 1..l_op_seq_num.count
925: loop -- {
926: insert into msc_form_query
927: (query_id,
928: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
929: number1, number2, number3, number4, number5, number6)
930: values
945:
946: begin
947: select distinct mfq.number2, mfq.number2
948: bulk collect into v_op, v_new_op
949: from msc_form_query mfq
950: where mfq.query_id = g_dem_op_query_id
951: and mfq.number1 = p_supply_id
952: and mfq.number2 not in (
953: select mfq_mrr.number2
950: where mfq.query_id = g_dem_op_query_id
951: and mfq.number1 = p_supply_id
952: and mfq.number2 not in (
953: select mfq_mrr.number2
954: from msc_form_query mfq_mrr
955: where mfq_mrr.query_id = g_op_query_id
956: and mfq_mrr.number1 = p_supply_id);
957: for a in 1 .. v_op.count
958: loop -- {
958: loop -- {
959: -- find the closest next op
960: select min(number2)
961: into v_dummy
962: from msc_form_query
963: where query_id = g_op_query_id
964: and number1 = p_supply_id
965: and number2 > v_op(a);
966:
967: if v_dummy is null then
968: -- if not found, find the closest prev op
969: select max(number2)
970: into v_dummy
971: from msc_form_query
972: where query_id = g_op_query_id
973: and number1 = p_supply_id
974: and number2 < v_op(a);
975: end if;
981: end if;
982: end loop; -- }
983:
984: forall a in 1.. v_op.count
985: update msc_form_query
986: set number2= v_new_op(a)
987: where query_id = g_dem_op_query_id
988: and number1 = p_supply_id
989: and number2 = v_op(a);
2661: if ( p_view_type in (RES_ACTIVITIES_VIEW) ) then -- {
2662:
2663: if ( p_node_type = RES_NODE ) then -- {
2664:
2665: insert into msc_form_query
2666: (query_id, last_update_date, last_updated_by, creation_date, created_by,
2667: last_update_login, number1, number2)
2668: select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2669: mfp2.demand_id, mfp2.sr_instance_id
2669: mfp2.demand_id, mfp2.sr_instance_id
2670: from msc_full_pegging mfp2,
2671: msc_full_pegging mfp,
2672: msc_resource_requirements mrr,
2673: msc_form_query mfq
2674: where mfq.query_id = v_mfq_from_query_id
2675: and mrr.plan_id = p_plan_id
2676: and mrr.sr_instance_id = mfq.number2
2677: and mrr.transaction_id = mfq.number1
2681: and mfp2.pegging_id = mfp.end_pegging_id;
2682:
2683: else
2684:
2685: insert into msc_form_query
2686: (query_id, last_update_date, last_updated_by, creation_date, created_by,
2687: last_update_login, number1, number2)
2688: select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2689: mfp2.demand_id, mfp2.sr_instance_id
2689: mfp2.demand_id, mfp2.sr_instance_id
2690: from msc_full_pegging mfp2,
2691: msc_full_pegging mfp,
2692: msc_resource_instance_reqs mrir,
2693: msc_form_query mfq
2694: where mfq.query_id = v_mfq_from_query_id
2695: and mrir.plan_id = p_plan_id
2696: and mrir.sr_instance_id = mfq.number2
2697: and mrir.res_inst_transaction_id = mfq.number1
2711: l_dept_id, l_res_id, l_res_instance_id, l_serial_number);
2712:
2713: if ( p_node_type = RES_NODE ) then -- {
2714:
2715: insert into msc_form_query
2716: (query_id, last_update_date, last_updated_by, creation_date, created_by,
2717: last_update_login, number1, number2)
2718: select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2719: mfp2.demand_id, mfp2.sr_instance_id
2734: and mfp2.plan_id = mfp.plan_id
2735: and mfp2.pegging_id = mfp.end_pegging_id;
2736: else
2737:
2738: insert into msc_form_query
2739: (query_id, last_update_date, last_updated_by, creation_date, created_by,
2740: last_update_login, number1, number2)
2741: select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2742: mfp2.demand_id, mfp2.sr_instance_id
2764: elsif ( p_view_type in (DEMAND_VIEW, ORDER_VIEW) ) then
2765:
2766: if ( p_node_type = RES_NODE ) then -- {
2767:
2768: insert into msc_form_query
2769: (query_id, last_update_date, last_updated_by, creation_date, created_by,
2770: last_update_login, number1, number2)
2771: select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2772: mfp2.demand_id, mfp2.sr_instance_id
2772: mfp2.demand_id, mfp2.sr_instance_id
2773: from msc_full_pegging mfp2,
2774: msc_full_pegging mfp,
2775: msc_resource_requirements mrr,
2776: msc_form_query mfq
2777: where mfq.query_id = v_mfq_from_query_id
2778: and mrr.plan_id = p_plan_id
2779: and mrr.sr_instance_id = mfq.number2
2780: and mrr.transaction_id = mfq.number1
2784: and mfp2.pegging_id = mfp.end_pegging_id;
2785:
2786: else
2787:
2788: insert into msc_form_query
2789: (query_id, last_update_date, last_updated_by, creation_date, created_by,
2790: last_update_login, number1, number2)
2791: select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2792: mfp2.demand_id, mfp2.sr_instance_id
2791: select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2792: mfp2.demand_id, mfp2.sr_instance_id
2793: from msc_full_pegging mfp2,
2794: msc_full_pegging mfp,
2795: msc_form_query mfq
2796: where mfq.query_id = v_mfq_from_query_id
2797: and mfp.plan_id = p_plan_id
2798: and mfp.sr_instance_id = mfq.number2
2799: and mfp.transaction_id = mfq.number1