[Home] [Help]
114: |19-Nov-2003 : Kiran Konada
115: | bugfix 2885568
116: | There was a full table scan on wip_discrete_jobs
117: | unique index present on wip_enity_id and organization_id
118: | hece, joined oe_order_lines_all and got the ship_from _org_id
119: |
120: | original query
121: | select dj.wip_entity_id, we.wip_entity_name
122: | into l_wip_entity_id, l_job_name
172:
173: BEGIN
174:
175:
176: UPDATE oe_order_lines_all oel
177: SET oel.program_id = null
178: WHERE oel.line_id = p_line_id
179: AND oel.program_id = -99;
180:
400: ' nvl(oel.org_id,-1) org_id, ' ||
401: ' oel.ato_line_id ato_line_id, '||
402: ' oel.inventory_item_id inventory_item_id '||
403: 'from '||
404: ' oe_order_lines_all OEL, '||
405: ' wf_item_activity_statuses WAS, '||
406: ' wf_process_activities WPA, '||
407: ' mtl_system_items MSI ';
408: else
423: ' oel.inventory_item_id inventory_item_id '||
424: 'from '||
425: ' wf_process_activities WPA, '||
426: ' wf_item_activity_statuses WAS, '||
427: ' oe_order_lines_all OEL, '||
428: ' mtl_system_items MSI ';
429: end if;
430:
431:
510: if (l_line_id is NOT NULL) then
511: sql_stmt := sql_stmt ||
512: 'and oel.line_id in '||
513: '(select oelc.line_id '||
514: ' from oe_order_lines_all oelc '||
515: ' where (oelc.ato_line_id = :l_line_id '||--5108885
516: 'and oelc.item_type_code = ''CONFIG'') '||
517: 'or (oelc.line_id = :l_line_id '||
518: 'and oelc.item_type_code = ''STANDARD'' ' ||
889:
890: begin
891: SELECT ship_from_org_id
892: INTO l_ship_from_org_id
893: FROM OE_ORDER_LINES_ALL
894: WHERE line_id = WorkOrder_Rec.ato_line_id --- bug fix 5207010 . We should lock based on ATO line id
895: and (GET_NOTINV_QTY(WorkOrder_Rec.line_id) - GET_RESERVED_QTY(WorkOrder_Rec.line_id)) > 0
896: FOR UPDATE NOWAIT; -- bugfix 2388802: lock the row which is really eligible
897: l_inventory_item_id := WorkOrder_Rec.inventory_item_id;
1074: l_stmt_num := 6;
1075:
1076: select nvl(oel.dep_plan_required_flag,'N')
1077: into l_dep_plan_flag
1078: from oe_order_lines_all oel
1079: where oel.line_id = WorkOrder_Rec.line_id;
1080:
1081: IF PG_DEBUG <> 0 THEN
1082: OE_DEBUG_PUB.add('get_order_lines: ' || 'l_dep_plan_flag::'||l_dep_plan_flag, 2);
1145: -- Flow ER 14595064
1146: -- Checking whether the routings are flow or discrete.
1147: select NVL(cfm_routing_flag,2)
1148: into l_supply_type
1149: from oe_order_lines_all oel,
1150: bom_operational_routings bor
1151: where oel.line_id = to_number(WorkOrder_Rec.line_id)
1152: and oel.inventory_item_id = bor.assembly_item_id (+)
1153: and oel.ship_from_org_id = bor.organization_id (+)
1164: l_stmt_num := 9;
1165:
1166: select schedule_ship_date
1167: into l_schedule_ship_date
1168: from oe_order_lines_all
1169: where line_id = WorkOrder_Rec.line_id;
1170:
1171: if (trunc(l_schedule_ship_date) < trunc(sysdate)) then
1172: IF PG_DEBUG <> 0 THEN
1349:
1350: select dj.wip_entity_id, we.wip_entity_name
1351: into l_wip_entity_id, l_job_name
1352: from wip_discrete_jobs dj, wip_entities we,
1353: oe_order_lines_all oel
1354: where dj.wip_entity_id = we.wip_entity_id
1355: and dj.source_line_id = l_line_id
1356: and dj.source_code = 'WICDOL'
1357: --bugfix 2885568 to remove full table scan on wip_discrete_jobs
1434:
1435: /*****************************************************************************
1436: Procedure: reserve_work_order
1437: Parameters: p_model_line_id - line id of the configuration item in
1438: oe_order_lines_all
1439: p_wip_seq - group id to be used in interface table
1440: x_error_message - error message if insert fails
1441: x_message_name - name of error message if insert
1442: fails
1522: -- 2620282: Selecting bom revision info
1523: decode( nvl(msi.revision_qty_control_code , 1 ) , 1, NULL , wei.bom_revision) bom_revision,
1524: oel.org_id -- bugfix 3014000
1525: from wip_job_schedule_interface wei,
1526: oe_order_lines_all oel,
1527: mtl_sales_orders mso,
1528: oe_order_headers_all oeh,
1529: --oe_order_types_v oet
1530: oe_transaction_types_tl oet,
1800:
1801: end if; /* reservation status not successful */
1802:
1803: /* Bugfix 2105156 : Release the manual lock */
1804: UPDATE oe_order_lines_all
1805: SET program_id = null
1806: WHERE line_id = WorkOrder_Rec.line_id
1807: AND program_id = -99;
1808:
1811: -- begin bugfix 3136206
1812: if resv_counter = 0 then
1813: oe_debug_pub.add('Warning: No reservations made. Check for errors in WJSI');
1814: -- unlock the lines. Update the program_id to null.
1815: update oe_order_lines_all
1816: set program_id = null
1817: where program_id = -99
1818: and line_id in (select wei.source_line_id
1819: from wip_job_schedule_interface wei
1898: null,
1899: null)),0)
1900: from mtl_reservations mr,
1901: oe_order_headers_all oeh,
1902: oe_order_lines_all oel,
1903: mtl_system_items msi --bugfix 2074290: added msi
1904: where oel.line_id = pLineId
1905: and oel.header_id = oeh.header_id
1906: and mr.demand_source_line_id = oel.line_id
1918:
1919: cursor c_wjsi_qty is
1920: select nvl(sum(wjs.start_quantity), 0)
1921: from wip_job_schedule_interface wjs,
1922: oe_order_lines_all oel
1923: where oel.line_id = pLineId
1924: and wjs.source_line_id = oel.line_id
1925: and (wjs.process_status = WIP_CONSTANTS.PENDING
1926: or wjs.process_status = WIP_CONSTANTS.RUNNING
2043: null)),
2044: 'Y', nvl(sum(wdd.requested_quantity), 0) )
2045: into l_quantity
2046: from WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
2047: oe_order_lines_all oel,
2048: mtl_system_items msi
2049: where oel.line_id = pLineId
2050: and oel.inventory_item_id = msi.inventory_item_id
2051: and oel.ship_from_org_id = msi.organization_id