[Home] [Help]
290:
291: l_active_activity varchar2(30);
292: lMatchProfile varchar2(10);
293:
294: v_source_type_code oe_order_lines_all.source_type_code%type ;
295: x_oper_unit_list cto_auto_procure_pk.oper_unit_tbl;
296: l_batch_no Number;
297:
298:
361:
362: -- The exception block for the following select statement is added by Renga Kannan
363: -- on 12/14/00. This part of the bug fix # 1381938
364: -- When the start Model workflow is called and it is having configuration item
365: -- in Oe_order_lines_all, it can be becasue of two things. One is the Auto create config
366: -- batch program is running and the other is due to link_item
367: -- In the case of Auto create config process we will get the program_id value in bcol table.
368: -- But in the case of link item the data may not be there in bcol table at all. So in the
369: -- case of link_item in the when_no_data_found exception we will set the l_program_id value to 0.
395:
396: l_stmt_num := 103;
397: select line_id
398: into l_config_line_id
399: from oe_order_lines_all
400: where ato_line_id = to_number(p_itemkey)
401: and item_type_code = 'CONFIG';
402:
403: /* ATO Line Workflow will not have individual activities to be bypassed
522: into lTopAtoLineId
523: , l_reservation_uom_code , l_quantity_to_reserve
524: , l_schedule_ship_date
525: , v_source_type_code
526: from oe_order_lines_all
527: where line_id = to_number(p_itemkey);
528:
529: IF PG_DEBUG <> 0 THEN
530: oe_debug_pub.add('create_config_item_wf: ' || 'lTopATOLineId::'||to_char(lTopAtoLineId));
547: select oeh.order_number , msi.segment1, oel.line_number || '.' || oel.shipment_number
548: , msi.inventory_item_id , msi.organization_id
549: into v_order_number, v_top_model_name, v_top_model_line_num
550: , v_top_model_id, v_ship_from_org_id
551: from oe_order_headers_all oeh , oe_order_lines_all oel , mtl_system_items msi
552: where oeh.header_id = oel.header_id
553: and oel.line_id = lTopAtoLineId
554: and oel.inventory_item_id = msi.inventory_item_id
555: and oel.ship_from_org_id = msi.organization_id ;
575:
576: /*--------------------------------------------------------+
577: Call API to create config item. The API will create config
578: items for all ATO models. The top model config item will be
579: linked in oe_order_lines_all AFTER BOM creation.
580: Depending on the "Match" profile, it will create new items
581: or match to existing items.
582: +---------------------------------------------------------*/
583:
701: oe_debug_pub.add( 'DROPPED component count > 0 ' , 1 ) ;
702:
703: select oeh.order_number , msi.segment1, oel.line_number || '.' || oel.shipment_number
704: into v_order_number, v_top_model_name, v_top_model_line_num
705: from oe_order_headers_all oeh , oe_order_lines_all oel , mtl_system_items msi
706: where oeh.header_id = oel.header_id
707: and oel.line_id = lTopAtoLineId
708: and oel.inventory_item_id = msi.inventory_item_id
709: and oel.ship_from_org_id = msi.organization_id ;
818:
819:
820:
821: --
822: -- Link the top level config item in oe_order_lines_all
823: --
824:
825: l_stmt_num := 150;
826: IF PG_DEBUG <> 0 THEN
878: l_stmt_num := 152;
879:
880: select line_id, header_id, inventory_item_id
881: into l_config_line_id, l_header_id, l_config_item_id
882: from oe_order_lines_all
883: where ato_line_id = to_number(p_itemkey)
884: and item_type_code = 'CONFIG';
885:
886: IF PG_DEBUG <> 0 THEN
919:
920: select msi.segment1, oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' )
921: || '.' || nvl(component_number , '' )
922: into v_top_config_name, v_top_config_line_num
923: from oe_order_lines_all oel , mtl_system_items msi
924: where oel.ato_line_id = lTopAtoLineId
925: and item_type_code = 'CONFIG'
926: and oel.inventory_item_id = msi.inventory_item_id
927: and oel.ship_from_org_id = msi.organization_id ;
956:
957: select msi.segment1, oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' )
958: || '.' || nvl(component_number , '' )
959: into v_top_config_name, v_top_config_line_num
960: from oe_order_lines_all oel , mtl_system_items msi
961: where oel.ato_line_id = lTopAtoLineId
962: and item_type_code = 'CONFIG'
963: and oel.inventory_item_id = msi.inventory_item_id
964: and oel.ship_from_org_id = msi.organization_id ;
2050: return_value INTEGER;
2051:
2052: --start bug#1861812
2053:
2054: v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2055: v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2056:
2057:
2058: v_header_id oe_order_lines_all.header_id%type ;
2051:
2052: --start bug#1861812
2053:
2054: v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2055: v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2056:
2057:
2058: v_header_id oe_order_lines_all.header_id%type ;
2059: v_config_line_id oe_order_lines_all.line_id%type ;
2054: v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2055: v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2056:
2057:
2058: v_header_id oe_order_lines_all.header_id%type ;
2059: v_config_line_id oe_order_lines_all.line_id%type ;
2060:
2061:
2062: l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
2055: v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2056:
2057:
2058: v_header_id oe_order_lines_all.header_id%type ;
2059: v_config_line_id oe_order_lines_all.line_id%type ;
2060:
2061:
2062: l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
2063: l_hold_release_rec OE_Holds_PVT.Hold_release_REC_type;
2082: --
2083: l_stmt_num := 98;
2084: SELECT item_type_code, ato_line_id , header_id, line_id
2085: INTO v_item_type_code, v_ato_line_id, v_header_id , v_config_line_id
2086: FROM oe_order_lines_all
2087: WHERE line_id = p_itemkey;
2088:
2089:
2090:
2395:
2396: select reservation_id
2397: into lReserveId
2398: from mtl_reservations mr,
2399: oe_order_lines_all oel,
2400: oe_order_headers_all oeh,
2401: oe_transaction_types_all ota,
2402: oe_transaction_types_tl otl,
2403: mtl_sales_orders mso
2483:
2484: select count(*)
2485: into lRsvCount
2486: from mtl_reservations mr,
2487: oe_order_lines_all oel,
2488: oe_order_headers_all oeh,
2489: oe_transaction_types_all ota,
2490: oe_transaction_types_tl otl,
2491: mtl_sales_orders mso
2625: --(oel.ordered_quantity - oel.cancelled_quantity) --bugfix 2017099
2626: oel.ordered_quantity
2627: into l_config_item_id, l_mfg_org_id, l_header_id,
2628: l_qty
2629: from oe_order_lines_all oel
2630: where oel.line_id = to_number(p_itemkey);
2631:
2632: /*------------------------------------+
2633: Check if Config BOM exists.
2765: l_stmt_num := 135;
2766:
2767: select oel.ato_line_id
2768: into l_top_ato_line_id
2769: from oe_order_lines_all oel
2770: where oel.line_id = to_number(p_itemkey);
2771:
2772: IF PG_DEBUG <> 0 THEN
2773: oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'Cost rollup::top_ato_line_id::'||to_char(l_top_ato_line_id));
2902: -- the 11.5.1 workflow activity.
2903:
2904: select oel.ato_line_id
2905: into lAtoLineId
2906: from oe_order_lines_all oel
2907: where line_id = to_number(p_itemkey);
2908:
2909: --wf_engine.SetItemAttrText(p_itemtype, p_itemkey,
2910: -- 'LEAD_TIME_ROLLUP_ORG', p_itemkey);
2993: l_msg_txt varchar2(2000);
2994: l_msg_count number;
2995: l_inventory_item_id Mtl_system_items.inventory_item_id%type;
2996: l_ship_from_org_id Mtl_system_items.organization_id%type;
2997: l_item_type_code Oe_order_lines_all.item_type_code%type;
2998: x_return_status Varchar2(1);
2999: P_source_type Number;
3000: p_sourcing_rule_exists Varchar2(1);
3001:
3035: */
3036: BEGIN
3037: select inventory_item_id, ship_from_org_id,item_type_code, source_type_code
3038: into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code
3039: from oe_order_lines_all
3040: where line_id = to_number(p_itemkey)
3041: and ato_line_id is not null;
3042: IF PG_DEBUG <> 0 THEN
3043: oe_debug_pub.add('check_supply_type_wf: ' || 'Inventory_item_id ='||to_char(l_inventory_item_id),1);
3233: l_stmt_num := 400;
3234:
3235: select NVL(cfm_routing_flag,2)
3236: into l_supply_type
3237: from oe_order_lines_all oel,
3238: bom_operational_routings bor
3239: where oel.line_id = to_number(p_itemkey)
3240: and oel.inventory_item_id = bor.assembly_item_id (+)
3241: and oel.ship_from_org_id = bor.organization_id (+)
3370: l_stmt_num := 110;
3371: --select (oel.ordered_quantity - nvl(oel.cancelled_quantity, 0))
3372: select oel.ordered_quantity --bufix 2017099
3373: into l_quantity
3374: from oe_order_lines_all oel
3375: where oel.line_id = to_number(p_itemkey)
3376: and exists (select '1'
3377: from bom_operational_routings bor
3378: where bor.assembly_item_id = oel.inventory_item_id
3409: -- earlier than today
3410: --
3411: select schedule_ship_date, header_id
3412: into l_sch_ship_date, l_header_id
3413: from oe_order_lines_all oel
3414: where line_id = to_number(p_itemkey);
3415:
3416: if (trunc(l_sch_ship_date) < trunc(sysdate)) then
3417: IF PG_DEBUG <> 0 THEN
3553:
3554: -- select to see if we can acquire lock. If we cannot, it will raise RECORD_LOCKED exception.
3555:
3556: SELECT '1' into l_dummy
3557: FROM oe_order_lines_all
3558: WHERE line_id = p_line_id
3559: FOR UPDATE NOWAIT;
3560:
3561: UPDATE oe_order_lines_all oel
3557: FROM oe_order_lines_all
3558: WHERE line_id = p_line_id
3559: FOR UPDATE NOWAIT;
3560:
3561: UPDATE oe_order_lines_all oel
3562: SET oel.program_id = -99
3563: WHERE oel.line_id = p_line_id
3564: AND nvl(oel.program_id, 0) <> -99;
3565:
3606: BEGIN
3607:
3608: x_result := null;
3609:
3610: UPDATE oe_order_lines_all oel
3611: SET oel.program_id = null
3612: WHERE oel.line_id = p_line_id
3613: AND nvl(oel.program_id, 0) = -99;
3614:
3731: oel.ato_line_id, oel.line_id,oel.top_model_line_id, oel.item_type_code --fix for bug#1874380
3732: into l_quantity, l_mfg_org_id, l_afas_line_id, l_ato_line_id, l_line_id,
3733: l_top_model_line_id,l_item_type_code
3734: from mtl_system_items msi,
3735: oe_order_lines_all oel
3736: where oel.line_id = to_number(p_itemkey)
3737: and (oel.open_flag is null
3738: or oel.open_flag = 'Y')
3739: and oel.ordered_quantity > 0
3753: and msi.base_item_id is not NULL
3754: +-----------------------------------*/
3755: and not exists
3756: (select '1'
3757: from oe_order_lines_all oel2
3758: where oel2.ship_from_org_id = oel.ship_from_org_id
3759: and oel2.header_id = oel.header_id
3760: and oel2.line_id = oel.line_id
3761: and rownum = 1
3874: BEGIN
3875: SELECT build_in_wip_flag
3876: INTO l_build_in_wip
3877: FROM mtl_system_items mtl,
3878: Oe_order_lines_all oel
3879: WHERE oel.line_id = to_number(p_itemkey)
3880: AND oel.inventory_item_id = mtl.inventory_item_id
3881: AND oel.ship_from_org_id = mtl.organization_id;
3882: EXCEPTION
4193: Select line details to make sure the model line is valid.
4194: +------------------------------------------------------------*/
4195: select 1
4196: into l_valid_model_line
4197: from oe_order_lines_all oel,
4198: mtl_system_items msi
4199: where oel.line_id = p_line_id
4200: and msi.organization_id = oel.ship_from_org_id
4201: and msi.inventory_item_id = oel.inventory_item_id
4260: Select line details to make sure the config line is valid.
4261: +------------------------------------------------------------*/
4262: select 1
4263: into l_valid_config_line
4264: from oe_order_lines_all oel,
4265: mtl_system_items msi
4266: where oel.line_id = p_config_line_id
4267: and msi.organization_id = oel.ship_from_org_id
4268: and oel.inventory_item_id = msi.inventory_item_id
4316:
4317: BEGIN
4318: select 1
4319: into l_config_item
4320: from oe_order_lines_all oelM,
4321: oe_order_lines_all oelC
4322: where oelM.line_id = p_model_line_id
4323: and oelC.ato_line_id = oelM.line_id
4324: and oelC.item_type_code = 'CONFIG';
4317: BEGIN
4318: select 1
4319: into l_config_item
4320: from oe_order_lines_all oelM,
4321: oe_order_lines_all oelC
4322: where oelM.line_id = p_model_line_id
4323: and oelC.ato_line_id = oelM.line_id
4324: and oelC.item_type_code = 'CONFIG';
4325:
4392:
4393: select sum(nvl(mrs.reservation_quantity,0))
4394: into x_reserved_quantity
4395: from mtl_system_items msi,
4396: oe_order_lines_all oel,
4397: mtl_reservations mrs
4398: where oel.line_id = p_config_line_id
4399: and oel.open_flag = 'Y'
4400: --and (oel.ordered_quantity - oel.cancelled_quantity) > 0
4443:
4444: select wip_entity_id
4445: into lWipEntityId
4446: from wip_flow_schedules wfs,
4447: oe_order_lines_all oel,
4448: oe_order_headers_all oeh,
4449: oe_transaction_types_all ota,
4450: oe_transaction_types_tl otl,
4451: mtl_sales_orders mso
4508: END IF;
4509:
4510: select ato_line_id
4511: into l_ato_line_id
4512: from oe_order_lines_all
4513: where line_id = p_itemkey;
4514:
4515: if (p_funcmode = 'RUN') then
4516: /*
4690: l_stmt_num NUMBER;
4691:
4692: l_inventory_item_id number;
4693: l_ship_from_org_id number;
4694: l_item_type_code Oe_order_lines_all.item_type_code%type;
4695: l_source_type_code oe_order_lines.source_type_code%type ;
4696:
4697: l_status NUMBER;
4698: -- l_return_status VARCHAR2(1);
4720:
4721: l_stmt_num:=10;
4722: select inventory_item_id, ship_from_org_id,item_type_code, source_type_code,header_id
4723: into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code,l_header_id
4724: from oe_order_lines_all
4725: where line_id = to_number(p_itemkey)
4726: and ato_line_id is not null;
4727: IF PG_DEBUG <> 0 THEN
4728: oe_debug_pub.add('check_supply_creation_wf: ' || 'Inventory_item_id ='||to_char(l_inventory_item_id),1);