[Home] [Help]
99: sum(nvl(ms.firm_quantity,ms.new_order_quantity)) quantity,
100: 0 quantity2,
101: 0 sub_inst_id
102: FROM msc_supplies ms,
103: msc_form_query mfq
104: WHERE ms.plan_id = mfq.number4
105: AND ms.inventory_item_id = mfq.number1
106: AND ms.organization_id = mfq.number2
107: AND ms.sr_instance_id = mfq.number3
135: using_requirement_quantity)) quantity,
136: sum(md.unmet_quantity) quantity2,
137: decode(md.customer_id, null, md.source_org_instance_id, 0) sub_inst_id
138: FROM msc_demands md,
139: msc_form_query mfq
140: WHERE md.plan_id = mfq.number4
141: AND md.inventory_item_id = mfq.number1
142: AND md.organization_id = mfq.number2
143: AND md.sr_instance_id = mfq.number3
207: decode(md.customer_id, null, md.source_org_instance_id, 0) sub_inst_id
208: FROM msc_demands md,
209: msc_supplies ms,
210: msc_single_lvl_peg mslp,
211: msc_form_query mfq
212: WHERE ms.organization_id = mfq.number2
213: AND ms.sr_instance_id = mfq.number3
214: AND ms.plan_id = mfq.number4
215: AND ms.inventory_item_id = mfq.number1
265: sum(nvl(ms.firm_quantity,ms.new_order_quantity)) quantity,
266: 0 quantity2,
267: ms.sr_instance_id sub_inst_id
268: FROM msc_supplies ms,
269: msc_form_query mfq
270: WHERE ms.plan_id = mfq.number4
271: AND ms.inventory_item_id = mfq.number1
272: AND ms.source_organization_id = mfq.number2
273: AND ms.source_sr_instance_id = mfq.number3
288: to_date(1, 'J') new_date,
289: 0 quantity,
290: 0 quantity2,
291: 0 sub_inst_id
292: FROM msc_form_query mfq
293: WHERE mfq.query_id = arg_query_id
294: ORDER BY 3,2,7,4;
295:
296: TYPE alloc_activity IS RECORD
850:
851: sql_stmt VARCHAR2(5000);
852: p_org_code varchar2(80);
853: BEGIN
854: sql_stmt := 'INSERT INTO msc_form_query ( '||
855: 'query_id, '||
856: 'last_update_date, '||
857: 'last_updated_by, '||
858: 'creation_date, '||
916: return NUMBER IS
917: p_query_id number;
918: BEGIN
919:
920: SELECT msc_form_query_s.nextval
921: INTO p_query_id
922: FROM dual;
923:
924: if p_sub_type in (1,9) then -- total, reqular org
921: INTO p_query_id
922: FROM dual;
923:
924: if p_sub_type in (1,9) then -- total, reqular org
925: insert into msc_form_query(
926: query_id,
927: last_update_date,
928: last_updated_by,
929: creation_date,
939: mslp.parent_id
940: from msc_supplies ms,
941: msc_supplies parent,
942: msc_single_lvl_peg mslp,
943: msc_form_query mfq
944: where ms.organization_id = mfq.number2
945: AND ms.sr_instance_id = mfq.number3
946: and ms.plan_id = mfq.number4
947: and ms.inventory_item_id = mfq.number1
953: and parent.order_type = 51
954: and ms.new_schedule_date between p_start_date and p_end_date
955: and mfq.query_id = p_item_query_id;
956:
957: insert into msc_form_query(
958: query_id,
959: last_update_date,
960: last_updated_by,
961: creation_date,
972: from msc_supplies ms,
973: msc_supplies parent,
974: msc_demands md,
975: msc_single_lvl_peg mslp,
976: msc_form_query mfq
977: where ms.organization_id = mfq.number2
978: AND ms.sr_instance_id = mfq.number3
979: and ms.plan_id = mfq.number4
980: and ms.inventory_item_id = mfq.number1
990: and mfq.query_id = p_item_query_id;
991: end if; -- if p_sub_type in (1,9) then
992:
993: if p_sub_type <> 9 then -- not a reqular org
994: insert into msc_form_query(
995: query_id,
996: last_update_date,
997: last_updated_by,
998: creation_date,
1008: mslp.parent_id
1009: from msc_supplies ms,
1010: msc_single_lvl_peg mslp,
1011: msc_demands md,
1012: msc_form_query mfq
1013: where ms.organization_id = mfq.number2
1014: AND ms.sr_instance_id = mfq.number3
1015: and ms.plan_id = mfq.number4
1016: and ms.inventory_item_id = mfq.number1