eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_HORIZONTAL_PLAN_SC dependencies on MSC_SUPPLIES

Line 312: -- MSC_DEMANDS and MSC_SUPPLIES

308: l_plan_type NUMBER := 1;
309:
310: -- --------------------------------------------
311: -- This cursor selects the snapshot activity in
312: -- MSC_DEMANDS and MSC_SUPPLIES
313: -- for the items per organizatio for a plan..
314: -- --------------------------------------------
315: CURSOR mrp_snapshot_activity IS
316: SELECT /*+ INDEX(rec, MSC_SUPPLIES_N1) */

Line 316: SELECT /+ INDEX(rec, MSC_SUPPLIES_N1) /

312: -- MSC_DEMANDS and MSC_SUPPLIES
313: -- for the items per organizatio for a plan..
314: -- --------------------------------------------
315: CURSOR mrp_snapshot_activity IS
316: SELECT /*+ INDEX(rec, MSC_SUPPLIES_N1) */
317: list.number5 item_id,
318: list.number6 org_id,
319: list.number3 inst_id,
320: DECODE(rec.order_type,

Line 370: msc_supplies rec,

366: 0 cost
367: FROM msc_form_query list,
368: msc_trading_partners param,
369: msc_system_items msi,
370: msc_supplies rec,
371: msc_calendar_dates dates
372: WHERE (arg_res_level = 1
373: OR (arg_res_level = 2
374: AND rec.project_id is NULL)

Line 572: from msc_supplies mr

568: l_plan_type = 4 and -- 5086979: IO plan don't show past due demand
569: trunc(mgr.using_assembly_demand_date) >= trunc(l_plan_start_date))
570: AND not exists (
571: select 'cancelled IR'
572: from msc_supplies mr
573: where mgr.origination_type in (30,6)
574: and mgr.disposition_id = mr.transaction_id
575: and mgr.plan_id = mr.plan_id
576: and mgr.sr_instance_id = mr.sr_instance_id

Line 3090: --< bug5449978--if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies

3086: SELECT msc_form_query_s.nextval
3087: INTO l_query_id
3088: FROM dual;
3089:
3090: --< bug5449978--if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies
3091: OPEN plan_start_c;
3092: FETCH plan_start_c INTO l_plan_start_date, l_plan_type, l_optimized_plan, l_enforce_sup_cap_constraints, l_constraints;
3093: CLOSE plan_start_c;
3094:

Line 3095: --if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies

3091: OPEN plan_start_c;
3092: FETCH plan_start_c INTO l_plan_start_date, l_plan_type, l_optimized_plan, l_enforce_sup_cap_constraints, l_constraints;
3093: CLOSE plan_start_c;
3094:
3095: --if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies
3096: --BUG5609299--modified previous fix so that we use msc_supplies for unconstrained DRP or unconstrained ASCP plans.
3097: if (l_plan_type=5 ) or (l_plan_type=1 and l_constraints=0) then
3098: g_use_sup_req:=0; -- do not use msc_supplier_requirements
3099: else

Line 3096: --BUG5609299--modified previous fix so that we use msc_supplies for unconstrained DRP or unconstrained ASCP plans.

3092: FETCH plan_start_c INTO l_plan_start_date, l_plan_type, l_optimized_plan, l_enforce_sup_cap_constraints, l_constraints;
3093: CLOSE plan_start_c;
3094:
3095: --if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies
3096: --BUG5609299--modified previous fix so that we use msc_supplies for unconstrained DRP or unconstrained ASCP plans.
3097: if (l_plan_type=5 ) or (l_plan_type=1 and l_constraints=0) then
3098: g_use_sup_req:=0; -- do not use msc_supplier_requirements
3099: else
3100: g_use_sup_req:=1; -- use msc_supplier_requirements

Line 3274: l_date := ' trunc(ms.new_dock_date) ';-- use msc_supplies

3270: END IF; -- only construct sql for capacity first time through cursor
3271: ELSE -- supplier requirements
3272:
3273: if g_use_sup_req=0 then
3274: l_date := ' trunc(ms.new_dock_date) ';-- use msc_supplies
3275: else
3276: l_date := ' trunc(mr.consumption_date) ';-- use msc_supplier_requirements
3277: end if;
3278:

Line 3281: ' FROM msc_supplies ms,'||

3277: end if;
3278:
3279: sql_stmt_2 := sql_stmt ||
3280: ' ms.transaction_id '||
3281: ' FROM msc_supplies ms,'||
3282: ' msc_supplier_requirements mr '||
3283: ' WHERE ms.plan_id = :p_plan_id' ||
3284: ' AND ms.inventory_item_id = :req_item_id' ||
3285: ' and nvl(ms.source_supplier_id,ms.supplier_id )= :p_supplier_id'||

Line 3454: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3450: sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3451: ' AND order_type =5 ';
3452: ELSIF ((l_isHp = true AND p_rowtype = 81) OR -- WIP
3453: (l_isDrp = true AND p_rowtype = 140)) THEN
3454: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3455: ' AND order_type in (3,7,14,15,27,28) ';
3456: ELSIF p_rowtype = 82 THEN -- Flow schedule
3457: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3458: ' AND order_type = 28 ';

Line 3457: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3453: (l_isDrp = true AND p_rowtype = 140)) THEN
3454: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3455: ' AND order_type in (3,7,14,15,27,28) ';
3456: ELSIF p_rowtype = 82 THEN -- Flow schedule
3457: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3458: ' AND order_type = 28 ';
3459: ELSIF ((l_isHp = true AND p_rowtype = 83) OR -- Purchase Orders
3460: (l_isDrp = true AND p_rowtype = 190)) THEN
3461: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

Line 3461: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3457: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3458: ' AND order_type = 28 ';
3459: ELSIF ((l_isHp = true AND p_rowtype = 83) OR -- Purchase Orders
3460: (l_isDrp = true AND p_rowtype = 190)) THEN
3461: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3462: ' AND order_type = 1 ';
3463:
3464: ELSIF ((l_isHp = true AND p_rowtype = 85) OR -- Requisitions
3465: (l_isDrp = true AND p_rowtype = 240)) THEN

Line 3466: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3462: ' AND order_type = 1 ';
3463:
3464: ELSIF ((l_isHp = true AND p_rowtype = 85) OR -- Requisitions
3465: (l_isDrp = true AND p_rowtype = 240)) THEN
3466: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3467: ' AND order_type in (2,53) ';
3468: ELSIF ((l_isHp = true AND p_rowtype = 87) OR -- In Transit
3469: (l_isDrp = true AND p_rowtype = 230)) THEN
3470: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

Line 3470: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3466: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3467: ' AND order_type in (2,53) ';
3468: ELSIF ((l_isHp = true AND p_rowtype = 87) OR -- In Transit
3469: (l_isDrp = true AND p_rowtype = 230)) THEN
3470: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3471: ' AND order_type = 11 ';
3472: ELSIF ((l_isHp = true AND p_rowtype = 89) OR -- In Receiving
3473: (l_isDrp = true AND p_rowtype = 150)) THEN
3474: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

Line 3474: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3470: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3471: ' AND order_type = 11 ';
3472: ELSIF ((l_isHp = true AND p_rowtype = 89) OR -- In Receiving
3473: (l_isDrp = true AND p_rowtype = 150)) THEN
3474: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3475: ' AND order_type in (8,12) ';
3476:
3477: ELSIF ((l_isHp = true AND p_rowtype = 90) OR
3478: -- Planned Orders: include planned arrival

Line 3480: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3476:
3477: ELSIF ((l_isHp = true AND p_rowtype = 90) OR
3478: -- Planned Orders: include planned arrival
3479: (l_isDrp = true AND p_rowtype = 250)) THEN
3480: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3481: ' AND order_type in (5,17,51,13) ';
3482: ELSIF p_rowtype = 95 THEN
3483: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3484: ' AND order_type = 29 ';

Line 3483: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3479: (l_isDrp = true AND p_rowtype = 250)) THEN
3480: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3481: ' AND order_type in (5,17,51,13) ';
3482: ELSIF p_rowtype = 95 THEN
3483: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3484: ' AND order_type = 29 ';
3485: ELSIF p_rowtype = 97 THEN
3486: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3487: ' AND order_type = 32 ';

Line 3486: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3482: ELSIF p_rowtype = 95 THEN
3483: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3484: ' AND order_type = 29 ';
3485: ELSIF p_rowtype = 97 THEN
3486: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3487: ' AND order_type = 32 ';
3488:
3489: ELSIF ((l_isHp = true AND p_rowtype = 100) OR
3490: -- Total Supply: should not include request arrival

Line 3492: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3488:
3489: ELSIF ((l_isHp = true AND p_rowtype = 100) OR
3490: -- Total Supply: should not include request arrival
3491: (l_isDrp = true AND p_rowtype = 260)) THEN
3492: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3493: ' AND order_type in (1,2,3,5,7,8,11,12,13,14,15,17,27,28,29,32,49,51,53) ';
3494: ELSIF ((l_isHp = true AND p_rowtype = 105) OR -- Beginning on Hand
3495: (l_isDrp = true AND p_rowtype = 130)) THEN
3496: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

Line 3496: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3492: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3493: ' AND order_type in (1,2,3,5,7,8,11,12,13,14,15,17,27,28,29,32,49,51,53) ';
3494: ELSIF ((l_isHp = true AND p_rowtype = 105) OR -- Beginning on Hand
3495: (l_isDrp = true AND p_rowtype = 130)) THEN
3496: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3497: ' AND order_type = 18 ';
3498: ELSIF ((l_isHp = true AND p_rowtype = 140) OR -- Current Schdld Receipts
3499: (l_isDrp = true AND p_rowtype = 270)) THEN
3500: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

Line 3500: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3496: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3497: ' AND order_type = 18 ';
3498: ELSIF ((l_isHp = true AND p_rowtype = 140) OR -- Current Schdld Receipts
3499: (l_isDrp = true AND p_rowtype = 270)) THEN
3500: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3501: ' AND order_type in (1,2,3,7,8,11,12,14,15,27,28,29) ';
3502:
3503: -- ---------------------------
3504: -- New Row Types added for DRP

Line 3529: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3525: sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3526: ' AND order_type in (30,29,49,3,48,16,17,18,19,20,23) ';
3527:
3528: ELSIF (l_isDrp = true AND p_rowtype = 120) THEN -- Internal Supply
3529: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3530: ' AND order_type in (3,5,7,8,12,18) ';
3531: ELSIF (l_isDrp = true AND p_rowtype = 160) THEN -- Planned Make
3532: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3533: ' AND order_type = 5 ';

Line 3532: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3528: ELSIF (l_isDrp = true AND p_rowtype = 120) THEN -- Internal Supply
3529: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3530: ' AND order_type in (3,5,7,8,12,18) ';
3531: ELSIF (l_isDrp = true AND p_rowtype = 160) THEN -- Planned Make
3532: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3533: ' AND order_type = 5 ';
3534: ELSIF (l_isDrp = true AND p_rowtype = 170) THEN -- Ext Supply
3535: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3536: ' AND order_type in (1,2) ';

Line 3535: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3531: ELSIF (l_isDrp = true AND p_rowtype = 160) THEN -- Planned Make
3532: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3533: ' AND order_type = 5 ';
3534: ELSIF (l_isDrp = true AND p_rowtype = 170) THEN -- Ext Supply
3535: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3536: ' AND order_type in (1,2) ';
3537:
3538: ELSIF (l_isDrp = true AND p_rowtype = 220) THEN
3539: -- Arrivals := planned inbound shipment + IR

Line 3540: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3536: ' AND order_type in (1,2) ';
3537:
3538: ELSIF (l_isDrp = true AND p_rowtype = 220) THEN
3539: -- Arrivals := planned inbound shipment + IR
3540: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3541: ' AND order_type in (51,53) ';
3542: ELSIF (l_isDrp = true AND p_rowtype = 380) THEN -- Req. Arrivals
3543: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3544: ' AND order_type = 52 ';

Line 3543: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||

3539: -- Arrivals := planned inbound shipment + IR
3540: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3541: ' AND order_type in (51,53) ';
3542: ELSIF (l_isDrp = true AND p_rowtype = 380) THEN -- Req. Arrivals
3543: sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3544: ' AND order_type = 52 ';
3545: ELSIF (l_isDrp = true AND p_rowtype = 80) THEN
3546: -- Requested Shipments
3547: sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||

Line 3555: sql_stmt := sql_stmt || ' AND ((source_table = ''MSC_SUPPLIES'''||

3551: sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3552: ' AND order_type in (53,54) ';
3553: ELSIF l_isDrp = true AND p_rowtype in (280, 290, 300 ) then -- poh, pab
3554: if p_rowtype = 280 then -- POH
3555: sql_stmt := sql_stmt || ' AND ((source_table = ''MSC_SUPPLIES'''||
3556: ' AND order_type in (1,2,3,7,8,11,12,14,15,17,18,27,28,32,49,53)) ';
3557: else
3558: sql_stmt := sql_stmt || ' AND ((source_table = ''MSC_SUPPLIES'''||
3559: ' AND order_type in (1,2,3,5,7,8,11,12,14,15,17,18,27,28,29,32,49,51,53)) ';

Line 3558: sql_stmt := sql_stmt || ' AND ((source_table = ''MSC_SUPPLIES'''||

3554: if p_rowtype = 280 then -- POH
3555: sql_stmt := sql_stmt || ' AND ((source_table = ''MSC_SUPPLIES'''||
3556: ' AND order_type in (1,2,3,7,8,11,12,14,15,17,18,27,28,32,49,53)) ';
3557: else
3558: sql_stmt := sql_stmt || ' AND ((source_table = ''MSC_SUPPLIES'''||
3559: ' AND order_type in (1,2,3,5,7,8,11,12,14,15,17,18,27,28,29,32,49,51,53)) ';
3560: end if;
3561: sql_stmt := sql_stmt ||
3562: ' OR (source_table = ''MSC_DEMANDS'' AND ';