[Home] [Help]
184: ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
185: mdr.department_code,
186: mdr.resource_code
187: from msc_resource_requirements mrr,
188: msc_supplies ms,
189: msc_system_items msi,
190: msc_department_resources mdr,
191: msc_routing_operations mro,
192: msc_trading_partners mtp
359: 17, COPROD_NODE,
360: 28, COPROD_NODE,
361: JOB_NODE) nodetype
362: from msc_form_query mfq,
363: msc_supplies ms
364: where mfq.query_id = p_mfq_query_id
365: and mfq.NUMBER2 = ms.sr_instance_id
366: and mfq.NUMBER1 = ms.transaction_id
367: and ms.plan_id = p_plan_id
987: mrr.cummulative_quantity, ms.new_order_quantity
988: into l_mtq,l_cumm_quan, l_order_quan
989: from msc_routing_operations mro,
990: msc_resource_requirements mrr,
991: msc_supplies ms
992: where mrr.plan_id = p_plan_id
993: and mrr.sr_instance_id = p_instance_id
994: and mrr.transaction_id = p_transaction_id
995: and mro.routing_sequence_id = mrr.routing_sequence_id
1144: msi.planning_time_fence_date
1145: into l_prev_start_date, l_timefence_date
1146: from msc_system_items msi,
1147: msc_resource_requirements mrr,
1148: msc_supplies ms
1149: where mrr.plan_id = p_plan_id
1150: and mrr.transaction_id = p_transaction_id
1151: and mrr.sr_instance_id = p_instance_id
1152: and ms.plan_id = mrr.plan_id
2458: g_gantt_rh_toler_days_early,
2459: g_gantt_rh_toler_days_late) display_type
2460: from msc_gantt_query mgq,
2461: msc_resource_requirements mrr,
2462: msc_supplies ms
2463: where mgq.query_id = p_query_id
2464: and mgq.row_flag = SYS_YES
2465: and mgq.is_fetched = SYS_NO
2466: and mgq.res_instance_id = MBP_NULL_VALUE
2512: g_gantt_rh_toler_days_late) display_type
2513: from msc_gantt_query mgq,
2514: msc_resource_requirements mrr,
2515: msc_resource_requirements mrr1,
2516: msc_supplies ms
2517: where mgq.query_id = p_query_id
2518: and mgq.row_flag = SYS_YES
2519: and mgq.is_fetched = SYS_NO
2520: and mgq.res_instance_id = MBP_NULL_VALUE
2579: g_gantt_rh_toler_days_late) display_type
2580: from msc_gantt_query mgq,
2581: msc_resource_requirements mrr,
2582: msc_resource_instance_reqs mrir,
2583: msc_supplies ms
2584: where mgq.query_id = p_query_id
2585: and mgq.row_flag = SYS_YES
2586: and mgq.is_fetched = SYS_NO
2587: and mgq.res_instance_id <> MBP_NULL_VALUE
2650: from msc_gantt_query mgq,
2651: msc_resource_requirements mrr,
2652: msc_resource_requirements mrr1,
2653: msc_resource_instance_reqs mrir,
2654: msc_supplies ms
2655: where mgq.query_id = p_query_id
2656: and mgq.row_flag = SYS_YES
2657: and mgq.is_fetched = SYS_NO
2658: and mgq.res_instance_id <> MBP_NULL_VALUE
2734: g_gantt_rh_toler_days_late)) display_type
2735: from msc_gantt_query mgq,
2736: msc_resource_requirements mrr,
2737: msc_resource_batches mrb,
2738: msc_supplies ms
2739: where mgq.query_id = p_query_id
2740: and mgq.row_flag = SYS_YES
2741: and mgq.is_fetched = SYS_NO
2742: and mgq.res_instance_id = MBP_NULL_VALUE
2812: from msc_gantt_query mgq,
2813: msc_resource_requirements mrr,
2814: msc_resource_requirements mrr1,
2815: msc_resource_batches mrb,
2816: msc_supplies ms
2817: where mgq.query_id = p_query_id
2818: and mgq.row_flag = SYS_YES
2819: and mgq.is_fetched = SYS_NO
2820: and mgq.res_instance_id = MBP_NULL_VALUE
2903: from msc_gantt_query mgq,
2904: msc_resource_requirements mrr,
2905: msc_resource_instance_reqs mrir,
2906: msc_resource_batches mrb,
2907: msc_supplies ms
2908: where mgq.query_id = p_query_id
2909: and mgq.row_flag = SYS_YES
2910: and mgq.is_fetched = SYS_NO
2911: and mgq.res_instance_id <> MBP_NULL_VALUE
2998: msc_resource_requirements mrr,
2999: msc_resource_requirements mrr1,
3000: msc_resource_batches mrb,
3001: msc_resource_instance_reqs mrir,
3002: msc_supplies ms
3003: where mgq.query_id = p_query_id
3004: and mgq.row_flag = SYS_YES
3005: and mgq.is_fetched = SYS_NO
3006: and mgq.res_instance_id <> MBP_NULL_VALUE
3268: mrr.organization_id, ms.inventory_item_id,
3269: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3270: from msc_resource_requirements mrr,
3271: msc_department_resources mdr,
3272: msc_supplies ms,
3273: msc_items mi,
3274: msc_items mi2,
3275: msc_gantt_query mgq,
3276: msc_plan_organizations mpo,
3389: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3390: from msc_resource_instance_reqs mrir,
3391: msc_resource_requirements mrr,
3392: msc_department_resources mdr,
3393: msc_supplies ms,
3394: msc_items mi,
3395: msc_items mi2,
3396: msc_gantt_query mgq
3397: where mgq.query_id = p_query_id
3467: to_char(sum(ms.new_order_quantity)),
3468: to_char(mrr.batch_number),
3469: to_char(sum(mrr.capacity_consumed_ratio))) bar_text
3470: from msc_resource_requirements mrr,
3471: msc_supplies ms,
3472: msc_gantt_query mgq,
3473: msc_plan_organizations mpo,
3474: msc_department_resources mdr,
3475: msc_plans mp
3550: to_char(mrir.batch_number),
3551: to_char(sum(mrir.capacity_consumed_ratio))) bar_text
3552: from msc_resource_instance_reqs mrir,
3553: msc_resource_requirements mrr,
3554: msc_supplies ms,
3555: msc_gantt_query mgq
3556: where mgq.query_id = p_query_id
3557: and mgq.row_flag = SYS_YES
3558: and mgq.is_fetched = SYS_NO
4766: orders_where_stat := 'select sr_instance_id, transaction_id '
4767: ||' from msc_orders_v '
4768: ||' where plan_id = :1 '
4769: ||' and category_set_id = '|| g_category_set_id
4770: ||' and source_table = ''MSC_SUPPLIES'' '
4771: ||' and 1=1 '||p_where;
4772:
4773: elsif (p_filter_type = FILTER_TYPE_AMONG) then
4774: null; --pabram need to check..
4889: p_where;
4890: elsif p_from_block = 'EXCEPTION' then
4891: sql_stat := 'SELECT mrr.sr_instance_id, '||
4892: ' mrr.transaction_id '||
4893: ' FROM msc_supplies mrr, '||
4894: ' msc_exception_details med '||
4895: ' WHERE mrr.plan_id = '||p_plan_id ||
4896: ' and mrr.plan_id = med.plan_id '||
4897: ' and ( (mrr.transaction_id = med.number1 and '||
4914:
4915: else
4916: sql_stat := 'SELECT mrr.sr_instance_id, '||
4917: ' mrr.transaction_id '||
4918: 'FROM msc_supplies mrr '||
4919: ' WHERE mrr.plan_id = '||p_plan_id ||p_where;
4920: end if;
4921:
4922: open the_cursor for sql_stat;
5142: mrr.assigned_units,
5143: mrr.firm_flag
5144: from msc_resource_requirements mrr,
5145: msc_operation_resource_seqs mors,
5146: msc_supplies ms
5147: where mrr.plan_id = p_plan
5148: and mrr.transaction_id = p_trx
5149: and mrr.sr_instance_id = p_inst
5150: and mors.plan_id = mrr.plan_id
5167: mrr.assigned_units,
5168: mrr.firm_flag
5169: from msc_resource_requirements mrr,
5170: msc_job_op_resources mjor,
5171: msc_supplies ms
5172: where mrr.plan_id = p_plan
5173: and mrr.transaction_id = p_trx
5174: and mrr.sr_instance_id = p_inst
5175: and mrr.routing_sequence_id is null
5294: -- 4561112 bugfix
5295: cursor assembly_qty (p_plan number, p_supply number) is
5296: select nvl(assembly_quantity,1) assembly_quantity
5297: from msc_boms mb,
5298: msc_supplies ms,
5299: msc_process_effectivity mpe,
5300: msc_resource_requirements mrr
5301: where ms.plan_id = p_plan
5302: and ms.transaction_id = p_supply
5732: and sr_instance_id = p_inst_id
5733: and batch_number = p_batch_number;
5734:
5735: -- just update MS...we may need to lock in future..
5736: update msc_supplies
5737: set status = 0,
5738: applied = 2
5739: where plan_id = p_plan_id
5740: and sr_instance_id = p_inst_id
5811: and sr_instance_id = p_inst_id
5812: and batch_number = p_batch_number;
5813:
5814: -- just update MS...we may need to lock in future..
5815: update msc_supplies
5816: set status = 0,
5817: applied = 2
5818: where plan_id = p_plan_id
5819: and sr_instance_id = p_inst_id
5907: end loop;
5908: close c_firm_seq_req_row;
5909:
5910: -- just update MS...we may need to lock in future..
5911: update msc_supplies
5912: set status = 0,
5913: applied = 2
5914: where plan_id = p_plan_id
5915: and sr_instance_id = p_inst_id
6338: p_firm_type number default null,
6339: p_firm_date date default null,
6340: p_firm_qty number default null) is
6341: begin
6342: update msc_supplies
6343: set status = 0,
6344: applied = 2,
6345: firm_planned_type = decode(p_update_type,
6346: FIRM_SUPPLY, p_firm_type,
6370: decode(ms.order_type, 27, 1, ms.firm_planned_type) firm_planned_type,
6371: sysdate theDate,
6372: getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time
6373: from msc_resource_requirements mrr,
6374: msc_supplies ms
6375: where mrr.plan_id = p_plan_id
6376: and mrr.sr_instance_id = p_instance_id
6377: and mrr.transaction_id = p_transaction_id
6378: and ms.plan_id = mrr.plan_id
6488: sysdate theDate,
6489: getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time
6490: from msc_resource_requirements mrr,
6491: msc_resource_instance_reqs mrir,
6492: msc_supplies ms
6493: where mrir.plan_id = p_plan_id
6494: and mrir.sr_instance_id = p_instance_id
6495: and mrir.res_inst_transaction_id = p_transaction_id
6496: and mrir.plan_id = mrr.plan_id
7804:
7805: begin
7806: select nvl(new_order_quantity,0)
7807: into l_quan
7808: from msc_supplies
7809: where plan_id = p_plan_id
7810: and transaction_id = p_supply_id
7811: for update of firm_date nowait;
7812: exception
8183: nvl(to_char(ms.UEPSD,format_mask),null_space) UEPSD,
8184: nvl(to_char(ms.UEPCD,format_mask),null_space) UEPCD,
8185: nvl(to_char(ms.ULPSD,format_mask),null_space) ULPSD,
8186: nvl(to_char(ms.ULPCD,format_mask),null_space) ULPCD
8187: from msc_supplies ms,
8188: msc_system_items msi
8189: where ms.plan_id = p_plan_id
8190: and ms.transaction_id = p_transaction_id
8191: and ms.sr_instance_id = p_instance_id
8226: nvl(to_char(mrr.UEPCD,format_mask),null_space) UEPCD,
8227: nvl(to_char(mrr.ULPSD,format_mask),null_space) ULPSD,
8228: nvl(to_char(mrr.ULPCD,format_mask),null_space) ULPCD
8229: from msc_resource_requirements mrr,
8230: msc_supplies ms,
8231: msc_department_resources mdr
8232: where mrr.plan_id = p_plan_id
8233: and mrr.transaction_id = p_transaction_id
8234: and mrr.sr_instance_id = p_instance_id
8383: nvl(to_char(mrr.UEPSD,format_mask),null_space) UEPSD,
8384: nvl(to_char(mrr.UEPCD,format_mask),null_space) UEPCD,
8385: nvl(to_char(mrr.ULPSD,format_mask),null_space) ULPSD,
8386: nvl(to_char(mrr.ULPCD,format_mask),null_space) ULPCD
8387: from msc_supplies ms,
8388: msc_resource_requirements mrr,
8389: msc_department_resources mdr,
8390: msc_system_items msi,
8391: msc_process_effectivity pe
10136: msi.description,
10137: msc_get_name.lookup_meaning(''WIP_JOB_STATUS'', ms.wip_status_code) wip_status_text,
10138: ms.new_schedule_date,
10139: ms.actual_start_date
10140: from msc_supplies ms,
10141: msc_system_items msi,
10142: msc_item_categories mic,
10143: msc_trading_partners mtp,
10144: mfg_lookups mfg,
10357: mrr.actual_start_date,
10358: mgq.critical_flag
10359: from msc_resource_requirements mrr,
10360: msc_department_resources mdr,
10361: msc_supplies ms,
10362: msc_items mi,
10363: msc_items mi2,
10364: msc_trading_partners mtp,
10365: msc_gantt_query mgq