[Home] [Help]
1484: end if;
1485:
1486: /* Record insertion into Order Lines table */
1487:
1488: insert into oe_order_lines_all
1489: (
1490: line_id,
1491: org_id,
1492: header_id,
3738: (select shipment_schedule_line_id from so_lines_all
3739: where line_id in
3740: (select old_line_id from oe_upgrade_log_v
3741: where new_line_id in
3742: (select line_id from oe_order_lines_all
3743: where line_set_id is null)))
3744: order by header_id, line_id;
3745:
3746: cursor c3 is
3746: cursor c3 is
3747: select
3748: line_id,
3749: line_number
3750: from oe_order_lines_all oola
3751: where line_id in
3752: (select new_line_id from oe_upgrade_log oul, so_lines_all sla
3753: where oul.old_line_id = sla.line_id
3754: and sla.shipment_schedule_line_id = v_line_id)
3769: and parent_line_id is null -- To filter out options (included on Leena's instn.)
3770: and parent_line_id is null -- To filter out options (included on Leena's instn.)
3771: and sla.line_id in
3772: (select lg.old_line_id
3773: from oe_upgrade_log_v lg, oe_order_lines_all ln
3774: where lg.new_line_id = ln.line_id
3775: and ln.item_type_code not in ('INCLUDED','CONFIG')
3776: group by lg.old_line_id
3777: having count(*) > 1);
3858: for c4 in c3 loop
3859:
3860: v_shipment_number := v_shipment_number + 1;
3861:
3862: update oe_order_lines_all
3863: set line_set_id = v_set_id,
3864: line_number = p_line_number,
3865: shipment_number = v_shipment_number
3866: where line_id = c4.line_id;
3941: begin
3942: v_item_type_code := null;
3943: select item_type_code into
3944: v_item_type_code from
3945: oe_order_lines_all
3946: where
3947: line_id = c8.line_id;
3948: exception
3949: when no_data_found then
3954: v_item_type_code = 'KIT' THEN
3955:
3956: v_shipment_number := v_shipment_number + 1;
3957:
3958: update oe_order_lines_all ooal
3959: set line_set_id = v_set_id,
3960: line_number = p_line_number,
3961: shipment_number = v_shipment_number
3962: where ooal.line_id = c8.line_id;
3984:
3985: Procedure Upgrade_Upd_Serv_Ref_line_id
3986: is
3987: cursor c1 is
3988: select header_id, line_id from oe_order_lines_all
3989: where item_type_code = 'SERVICE';
3990: v_new_line_id number;
3991: --
3992: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4006: and sla2.line_id = oul2.old_line_id
4007: and nvl(oul1.old_line_detail_id,0) = nvl(oul2.old_line_detail_id,0)
4008: and nvl(oul1.picking_line_id,0) = nvl(oul2.picking_line_id,0);
4009:
4010: update oe_order_lines_all
4011: set service_reference_line_id = v_new_line_id
4012: where line_Id = c2.line_id;
4013: exception
4014: when no_data_found then
4045: PROCEDURE insert_multiple_models IS
4046:
4047: cursor multiple_cfg_detail(p_ato_line_id IN NUMBER) IS
4048: select line_id,ordered_quantity,shipped_quantity
4049: from oe_order_lines_all
4050: where ato_line_id=p_ato_line_id
4051: and item_type_code = 'CONFIG';
4052:
4053: cursor multiple_cfg_parent IS
4051: and item_type_code = 'CONFIG';
4052:
4053: cursor multiple_cfg_parent IS
4054: select ato_line_id
4055: from oe_order_lines_all
4056: group by ato_line_id,item_type_code
4057: having item_type_code = 'CONFIG'
4058: and count(*) > 1;
4059:
4058: and count(*) > 1;
4059:
4060: cursor model_and_options(p_ato_line_id IN NUMBER) IS
4061: select line_id,upgraded_flag
4062: from oe_order_lines_all
4063: where ato_line_id=p_ato_line_id
4064: and item_type_code <> 'CONFIG'
4065: order by component_code;
4066:
4065: order by component_code;
4066:
4067: cursor service_lines(p_service_reference_line_id IN NUMBER) IS
4068: select line_id,upgraded_flag
4069: from oe_order_lines_all
4070: where service_reference_line_id = p_service_reference_line_id;
4071:
4072: l_cfg_line_id NUMBER;
4073: l_cfg_ordered_quantity NUMBER;
4157: INSERT_ROW(l_new_line_rec, l_line_id,l_upgraded_flag);
4158:
4159: -- Update the config item to point to the new model.
4160:
4161: UPDATE oe_order_lines_all
4162: SET ato_line_id = l_model_rec.line_id,
4163: top_model_line_id = l_model_rec.line_id
4164: WHERE line_id=l_cfg_line_id;
4165:
4162: SET ato_line_id = l_model_rec.line_id,
4163: top_model_line_id = l_model_rec.line_id
4164: WHERE line_id=l_cfg_line_id;
4165:
4166: UPDATE oe_order_lines_all
4167: SET link_to_line_id = l_model_rec.line_id
4168: WHERE line_id=l_cfg_line_id;
4169:
4170: OPEN service_lines(l_line_id);
4529: , MARKETING_SOURCE_CODE_ID
4530: , CALCULATE_PRICE_FLAG
4531: , COMMITMENT_ID
4532: , ORDER_SOURCE_ID -- aksingh
4533: FROM OE_ORDER_LINES_ALL
4534: WHERE LINE_ID = p_line_id ;
4535:
4536:
4537: --
4875: when others then
4876: G_OLD_LINE_ID := 0;
4877: end;
4878:
4879: INSERT INTO OE_ORDER_LINES_ALL
4880: ( ORG_ID
4881: , ACCOUNTING_RULE_ID
4882: , ACTUAL_ARRIVAL_DATE
4883: , ACTUAL_SHIPMENT_DATE
6117: l_line_number := g_Last_Line_Number;
6118: else
6119: begin
6120: Select max(line_number) into l_line_number
6121: from oe_order_lines_all
6122: where header_id = l_line_rec.header_id;
6123:
6124: l_line_number := l_line_number + 1;
6125: exception
6242: l_header_id number;
6243: v_error_code number;
6244: Cursor C1 is
6245: Select l1.line_id,l1.header_id
6246: from oe_order_lines_all l1, oe_order_lines_all l2
6247: where l1.reference_line_id = l2.line_id
6248: and l2.item_type_code in ('MODEL','CLASS','KIT')
6249: and l2.ato_line_id is null
6250: and l1.line_category_code = 'RETURN'
6317: v_received_quantity number;
6318: -- Cursor to select ATO Items
6319: Cursor C1 is
6320: Select l1.line_id,l1.header_id,'RA' item_type,m.received_quantity
6321: from oe_order_lines_all l1, oe_order_lines_all l2,mtl_so_rma_interface m
6322: where l1.reference_line_id = l2.line_id
6323: and l1.line_category_code = 'RETURN'
6324: and decode(m.received_quantity,0,NULL,m.received_quantity) <
6325: l1.ordered_quantity
6365: 0,null,null,null,null,null
6366: );
6367:
6368:
6369: Update oe_order_lines_all
6370: set shipped_quantity = v_received_quantity,
6371: fulfilled_quantity = v_received_quantity,
6372: ordered_quantity = v_received_quantity ,
6373: line_set_id = l_line_set_id