[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 ;
2079: return_value INTEGER;
2080:
2081: --start bug#1861812
2082:
2083: v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2084: v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2085:
2086:
2087: v_header_id oe_order_lines_all.header_id%type ;
2080:
2081: --start bug#1861812
2082:
2083: v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2084: v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2085:
2086:
2087: v_header_id oe_order_lines_all.header_id%type ;
2088: v_config_line_id oe_order_lines_all.line_id%type ;
2083: v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2084: v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2085:
2086:
2087: v_header_id oe_order_lines_all.header_id%type ;
2088: v_config_line_id oe_order_lines_all.line_id%type ;
2089:
2090:
2091: l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
2084: v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2085:
2086:
2087: v_header_id oe_order_lines_all.header_id%type ;
2088: v_config_line_id oe_order_lines_all.line_id%type ;
2089:
2090:
2091: l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
2092: l_hold_release_rec OE_Holds_PVT.Hold_release_REC_type;
2111: --
2112: l_stmt_num := 98;
2113: SELECT item_type_code, ato_line_id , header_id, line_id
2114: INTO v_item_type_code, v_ato_line_id, v_header_id , v_config_line_id
2115: FROM oe_order_lines_all
2116: WHERE line_id = p_itemkey;
2117:
2118:
2119:
2428:
2429: select reservation_id
2430: into lReserveId
2431: from mtl_reservations mr,
2432: oe_order_lines_all oel,
2433: oe_order_headers_all oeh,
2434: oe_transaction_types_all ota,
2435: oe_transaction_types_tl otl,
2436: mtl_sales_orders mso
2516:
2517: select count(*)
2518: into lRsvCount
2519: from mtl_reservations mr,
2520: oe_order_lines_all oel,
2521: oe_order_headers_all oeh,
2522: oe_transaction_types_all ota,
2523: oe_transaction_types_tl otl,
2524: mtl_sales_orders mso
2658: --(oel.ordered_quantity - oel.cancelled_quantity) --bugfix 2017099
2659: oel.ordered_quantity
2660: into l_config_item_id, l_mfg_org_id, l_header_id,
2661: l_qty
2662: from oe_order_lines_all oel
2663: where oel.line_id = to_number(p_itemkey);
2664:
2665: /*------------------------------------+
2666: Check if Config BOM exists.
2798: l_stmt_num := 135;
2799:
2800: select oel.ato_line_id
2801: into l_top_ato_line_id
2802: from oe_order_lines_all oel
2803: where oel.line_id = to_number(p_itemkey);
2804:
2805: IF PG_DEBUG <> 0 THEN
2806: oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'Cost rollup::top_ato_line_id::'||to_char(l_top_ato_line_id));
2935: -- the 11.5.1 workflow activity.
2936:
2937: select oel.ato_line_id
2938: into lAtoLineId
2939: from oe_order_lines_all oel
2940: where line_id = to_number(p_itemkey);
2941:
2942: --wf_engine.SetItemAttrText(p_itemtype, p_itemkey,
2943: -- 'LEAD_TIME_ROLLUP_ORG', p_itemkey);
3026: l_msg_txt varchar2(2000);
3027: l_msg_count number;
3028: l_inventory_item_id Mtl_system_items.inventory_item_id%type;
3029: l_ship_from_org_id Mtl_system_items.organization_id%type;
3030: l_item_type_code Oe_order_lines_all.item_type_code%type;
3031: x_return_status Varchar2(1);
3032: P_source_type Number;
3033: p_sourcing_rule_exists Varchar2(1);
3034:
3068: */
3069: BEGIN
3070: select inventory_item_id, ship_from_org_id,item_type_code, source_type_code
3071: into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code
3072: from oe_order_lines_all
3073: where line_id = to_number(p_itemkey)
3074: and ato_line_id is not null;
3075: IF PG_DEBUG <> 0 THEN
3076: oe_debug_pub.add('check_supply_type_wf: ' || 'Inventory_item_id ='||to_char(l_inventory_item_id),1);
3266: l_stmt_num := 400;
3267:
3268: select NVL(cfm_routing_flag,2)
3269: into l_supply_type
3270: from oe_order_lines_all oel,
3271: bom_operational_routings bor
3272: where oel.line_id = to_number(p_itemkey)
3273: and oel.inventory_item_id = bor.assembly_item_id (+)
3274: and oel.ship_from_org_id = bor.organization_id (+)
3403: l_stmt_num := 110;
3404: --select (oel.ordered_quantity - nvl(oel.cancelled_quantity, 0))
3405: select oel.ordered_quantity --bufix 2017099
3406: into l_quantity
3407: from oe_order_lines_all oel
3408: where oel.line_id = to_number(p_itemkey)
3409: and exists (select '1'
3410: from bom_operational_routings bor
3411: where bor.assembly_item_id = oel.inventory_item_id
3442: -- earlier than today
3443: --
3444: select schedule_ship_date, header_id
3445: into l_sch_ship_date, l_header_id
3446: from oe_order_lines_all oel
3447: where line_id = to_number(p_itemkey);
3448:
3449: if (trunc(l_sch_ship_date) < trunc(sysdate)) then
3450: IF PG_DEBUG <> 0 THEN
3586:
3587: -- select to see if we can acquire lock. If we cannot, it will raise RECORD_LOCKED exception.
3588:
3589: SELECT '1' into l_dummy
3590: FROM oe_order_lines_all
3591: WHERE line_id = p_line_id
3592: FOR UPDATE NOWAIT;
3593:
3594: UPDATE oe_order_lines_all oel
3590: FROM oe_order_lines_all
3591: WHERE line_id = p_line_id
3592: FOR UPDATE NOWAIT;
3593:
3594: UPDATE oe_order_lines_all oel
3595: SET oel.program_id = -99
3596: WHERE oel.line_id = p_line_id
3597: AND nvl(oel.program_id, 0) <> -99;
3598:
3639: BEGIN
3640:
3641: x_result := null;
3642:
3643: UPDATE oe_order_lines_all oel
3644: SET oel.program_id = null
3645: WHERE oel.line_id = p_line_id
3646: AND nvl(oel.program_id, 0) = -99;
3647:
3764: oel.ato_line_id, oel.line_id,oel.top_model_line_id, oel.item_type_code --fix for bug#1874380
3765: into l_quantity, l_mfg_org_id, l_afas_line_id, l_ato_line_id, l_line_id,
3766: l_top_model_line_id,l_item_type_code
3767: from mtl_system_items msi,
3768: oe_order_lines_all oel
3769: where oel.line_id = to_number(p_itemkey)
3770: and (oel.open_flag is null
3771: or oel.open_flag = 'Y')
3772: and oel.ordered_quantity > 0
3786: and msi.base_item_id is not NULL
3787: +-----------------------------------*/
3788: and not exists
3789: (select '1'
3790: from oe_order_lines_all oel2
3791: where oel2.ship_from_org_id = oel.ship_from_org_id
3792: and oel2.header_id = oel.header_id
3793: and oel2.line_id = oel.line_id
3794: and rownum = 1
3907: BEGIN
3908: SELECT build_in_wip_flag
3909: INTO l_build_in_wip
3910: FROM mtl_system_items mtl,
3911: Oe_order_lines_all oel
3912: WHERE oel.line_id = to_number(p_itemkey)
3913: AND oel.inventory_item_id = mtl.inventory_item_id
3914: AND oel.ship_from_org_id = mtl.organization_id;
3915: EXCEPTION
4004: -- An update is needed again in case of retry activity. During the first run of AFAS, if there is
4005: -- an error, the program_id is updated to null. When the AFAS activity is retried, the line
4006: -- doesn't get picked up because the program_id is null.
4007:
4008: UPDATE oe_order_lines_all
4009: SET program_id = -99
4010: WHERE line_id = to_number(p_itemkey)
4011: AND NVL(program_id, 0) <> -99;
4012:
4279: Select line details to make sure the model line is valid.
4280: +------------------------------------------------------------*/
4281: select 1
4282: into l_valid_model_line
4283: from oe_order_lines_all oel,
4284: mtl_system_items msi
4285: where oel.line_id = p_line_id
4286: and msi.organization_id = oel.ship_from_org_id
4287: and msi.inventory_item_id = oel.inventory_item_id
4352: Select line details to make sure the config line is valid.
4353: +------------------------------------------------------------*/
4354: select 1
4355: into l_valid_config_line
4356: from oe_order_lines_all oel,
4357: mtl_system_items msi
4358: where oel.line_id = p_config_line_id
4359: and msi.organization_id = oel.ship_from_org_id
4360: and oel.inventory_item_id = msi.inventory_item_id
4415:
4416: BEGIN
4417: select 1
4418: into l_config_item
4419: from oe_order_lines_all oelM,
4420: oe_order_lines_all oelC
4421: where oelM.line_id = p_model_line_id
4422: and oelC.ato_line_id = oelM.line_id
4423: and oelC.item_type_code = 'CONFIG';
4416: BEGIN
4417: select 1
4418: into l_config_item
4419: from oe_order_lines_all oelM,
4420: oe_order_lines_all oelC
4421: where oelM.line_id = p_model_line_id
4422: and oelC.ato_line_id = oelM.line_id
4423: and oelC.item_type_code = 'CONFIG';
4424:
4491:
4492: select sum(nvl(mrs.reservation_quantity,0))
4493: into x_reserved_quantity
4494: from mtl_system_items msi,
4495: oe_order_lines_all oel,
4496: mtl_reservations mrs
4497: where oel.line_id = p_config_line_id
4498: and oel.open_flag = 'Y'
4499: --and (oel.ordered_quantity - oel.cancelled_quantity) > 0
4542:
4543: select wip_entity_id
4544: into lWipEntityId
4545: from wip_flow_schedules wfs,
4546: oe_order_lines_all oel,
4547: oe_order_headers_all oeh,
4548: oe_transaction_types_all ota,
4549: oe_transaction_types_tl otl,
4550: mtl_sales_orders mso
4607: END IF;
4608:
4609: select ato_line_id
4610: into l_ato_line_id
4611: from oe_order_lines_all
4612: where line_id = p_itemkey;
4613:
4614: if (p_funcmode = 'RUN') then
4615: /*
4789: l_stmt_num NUMBER;
4790:
4791: l_inventory_item_id number;
4792: l_ship_from_org_id number;
4793: l_item_type_code Oe_order_lines_all.item_type_code%type;
4794: l_source_type_code oe_order_lines.source_type_code%type ;
4795:
4796: l_status NUMBER;
4797: -- l_return_status VARCHAR2(1);
4819:
4820: l_stmt_num:=10;
4821: select inventory_item_id, ship_from_org_id,item_type_code, source_type_code,header_id
4822: into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code,l_header_id
4823: from oe_order_lines_all
4824: where line_id = to_number(p_itemkey)
4825: and ato_line_id is not null;
4826: IF PG_DEBUG <> 0 THEN
4827: oe_debug_pub.add('check_supply_creation_wf: ' || 'Inventory_item_id ='||to_char(l_inventory_item_id),1);