[Home] [Help]
95: bind variables, use table joins. Also added rownum in the subquery */
96:
97: CURSOR c_repLines IS
98: select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
99: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
100: where wro.wip_entity_id = p_wip_entity_id
101: and wro.repetitive_schedule_id = p_repetitive_schedule_id
102: and mtrl.txn_source_id = wro.wip_entity_id
103: and mtrl.reference_id = wro.repetitive_schedule_id
116: /* Bugfix 4211265: Cursor c_discLines is doing an FTS on mtrl. We will join with wip_entities to get the organization_id and join with mtrl thru wro so that index MTL_TXN_REQUEST_LINES_N1 can be used. */
117:
118: CURSOR c_discLines IS
119: select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
120: from mtl_txn_request_lines mtrl, wip_requirement_operations wro, wip_entities we /* bug 4211266 */
121: where we.wip_entity_id = p_wip_entity_id
122: and wro.organization_id = we.organization_id
123: and wro.wip_entity_id = we.wip_entity_id
124: and mtrl.organization_id = wro.organization_id
211: end if;
212:
213: --finally update all backordered quantity to 0
214: if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
215: update wip_requirement_operations
216: set quantity_backordered = 0
217: where wip_entity_id = p_wip_entity_id;
218: elsif(p_wip_entity_type = wip_constants.repetitive) then
219: update wip_requirement_operations
215: update wip_requirement_operations
216: set quantity_backordered = 0
217: where wip_entity_id = p_wip_entity_id;
218: elsif(p_wip_entity_type = wip_constants.repetitive) then
219: update wip_requirement_operations
220: set quantity_backordered = 0
221: where wip_entity_id = p_wip_entity_id
222: and repetitive_schedule_id = p_repetitive_schedule_id;
223: end if;
256: l_dummy VARCHAR2(1);
257:
258: CURSOR c_repLines IS
259: select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
260: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
261: where mtrl.txn_source_id = p_wip_entity_id
262: and mtrl.reference_id = p_repetitive_schedule_id
263: and wro.wip_entity_id = p_wip_entity_id
264: and wro.repetitive_schedule_id = p_repetitive_schedule_id
276: for update of wro.quantity_allocated, quantity_backordered nowait;
277:
278: CURSOR c_discLines IS
279: select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
280: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
281: where mtrl.txn_source_id = p_wip_entity_id
282: and wro.wip_entity_id = p_wip_entity_id
283: and wro.operation_seq_num = p_operation_seq_num
284: and mtrl.txn_source_line_id = wro.operation_seq_num
350: end if;
351:
352: --finally update backordered quantity to 0
353: if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
354: update wip_requirement_operations
355: set quantity_backordered = 0
356: where wip_entity_id = p_wip_entity_id
357: and operation_seq_num = p_operation_seq_num
358: and inventory_item_id = p_inventory_item_id;
356: where wip_entity_id = p_wip_entity_id
357: and operation_seq_num = p_operation_seq_num
358: and inventory_item_id = p_inventory_item_id;
359: elsif(p_wip_entity_type = wip_constants.repetitive) then
360: update wip_requirement_operations
361: set quantity_backordered = 0
362: where wip_entity_id = p_wip_entity_id
363: and repetitive_schedule_id = p_repetitive_schedule_id
364: and operation_seq_num = p_operation_seq_num
391:
392: l_msgCount NUMBER;
393:
394: BEGIN
395: update wip_requirement_operations
396: set quantity_allocated = nvl(quantity_allocated,0) - p_openQty,
397: last_update_date = sysdate,
398: last_updated_by = fnd_global.user_id,
399: last_update_login = fnd_global.login_id
419: l_reductionQty NUMBER;
420:
421: CURSOR c_repLines(v_wip_entity_id NUMBER, v_repetitive_schedule_id NUMBER, v_operation_seq_num NUMBER, v_inventory_item_id NUMBER) IS
422: select line_id, nvl(mtrl.required_quantity,(nvl(quantity_detailed,0) - nvl(quantity_delivered,0))) open_quantity, wro.rowid
423: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
424: where mtrl.txn_source_id = v_wip_entity_id
425: and mtrl.reference_id = v_repetitive_schedule_id
426: and wro.wip_entity_id = v_wip_entity_id
427: and wro.repetitive_schedule_id = v_repetitive_schedule_id
441: for update of wro.quantity_allocated, quantity_backordered nowait;
442:
443: CURSOR c_discLines(v_wip_entity_id NUMBER, v_operation_seq_num NUMBER, v_inventory_item_id NUMBER) IS
444: select line_id, nvl(mtrl.required_quantity,(nvl(quantity_detailed,0) - nvl(quantity_delivered,0))) open_quantity, wro.rowid
445: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
446: where mtrl.txn_source_id = v_wip_entity_id
447: and wro.wip_entity_id = v_wip_entity_id
448: and wro.operation_seq_num = v_operation_seq_num
449: and mtrl.txn_source_line_id = wro.operation_seq_num
524: end if;
525:
526: /*finally update backordered quantity and allocated quantity for this component*/
527: if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
528: update wip_requirement_operations
529: set quantity_backordered = greatest((quantity_backordered - p_comp_tbl(i).requested_quantity), 0),
530: quantity_allocated = quantity_allocated - p_comp_tbl(i).requested_quantity
531: where wip_entity_id = p_comp_tbl(i).wip_entity_id
532: and operation_seq_num = p_comp_tbl(i).operation_seq_num
531: where wip_entity_id = p_comp_tbl(i).wip_entity_id
532: and operation_seq_num = p_comp_tbl(i).operation_seq_num
533: and inventory_item_id = p_comp_tbl(i).inventory_item_id;
534: elsif(p_wip_entity_type = wip_constants.repetitive) then
535: update wip_requirement_operations
536: set quantity_backordered = greatest(quantity_backordered - p_comp_tbl(i).requested_quantity, 0),
537: quantity_allocated = quantity_allocated - p_comp_tbl(i).requested_quantity
538: where wip_entity_id = p_comp_tbl(i).wip_entity_id
539: and repetitive_schedule_id = p_comp_tbl(i).repetitive_schedule_id
567: x_return_status OUT NOCOPY VARCHAR2,
568: x_msg_data OUT NOCOPY VARCHAR2) IS
569: CURSOR c_repLines IS
570: select line_id
571: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
572: where mtrl.txn_source_id = p_wip_entity_id
573: and mtrl.txn_source_line_id = p_repetitive_schedule_id
574: and wro.wip_entity_id = p_wip_entity_id
575: and wro.repetitive_schedule_id = p_repetitive_schedule_id
586: for update of wro.quantity_allocated, wro.quantity_backordered nowait; --make sure no orders are transacted in the meantime
587:
588: CURSOR c_discLines IS
589: select line_id
590: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
591: where mtrl.txn_source_id = p_wip_entity_id
592: and wro.wip_entity_id = p_wip_entity_id
593: and wro.operation_seq_num = 1
594: and mtrl.txn_source_line_id = wro.operation_seq_num
912: wro.wip_supply_type,
913: wro.date_required,
914: msi.revision_qty_control_code
915:
916: from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
917: where wro.wip_entity_id = v_wip_entity_id
918: and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
919: and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
920: and (
958: wro.wip_supply_type,
959: wro.date_required,
960: msi.revision_qty_control_code
961:
962: from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
963: where wro.wip_entity_id = v_wip_entity_id
964: and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
965: and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
966: /*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
998: wro.wip_supply_type,
999: wro.date_required,
1000: msi.revision_qty_control_code
1001:
1002: from wip_requirement_operations wro, wip_operations wo, mtl_system_items_b msi
1003: where wro.wip_entity_id = v_wip_entity_id
1004: and wro.wip_entity_id = wo.wip_entity_id
1005: and wro.operation_seq_num = wo.operation_seq_num
1006: and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
1045: wro.wip_supply_type,
1046: wro.date_required,
1047: msi.revision_qty_control_code
1048:
1049: from wip_requirement_operations wro,
1050: wip_repetitive_schedules wrs,
1051: mtl_system_items_b msi,
1052: wip_operations wo
1053: where wro.repetitive_schedule_id = v_rep_sched_id
1702:
1703: /* moved to pre_allocate_material in wippckpb.pls per bug#2642679
1704: if(p_wip_entity_type in(wip_constants.repetitive,wip_constants.discrete,wip_constants.eam,wip_constants.lotbased)) then
1705: if(p_use_pickset_flag = 'N') then
1706: update wip_requirement_operations
1707: set quantity_backordered = p_open_qty,
1708: last_update_date = l_sysDate,
1709: last_updated_by = l_userId,
1710: last_update_login = l_loginId
1850: l_supplyType,
1851: l_reqDate,
1852: l_revControlCode
1853:
1854: from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
1855: where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1856: and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
1857: and (
1858: ( 'Y' = (select allocate_backflush_components
1894: l_supplyType,
1895: l_reqDate,
1896: l_revControlCode
1897:
1898: from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
1899: where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1900: and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
1901: and wro.required_quantity > (wro.quantity_issued +
1902: nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1930: l_supplyType,
1931: l_reqDate,
1932: l_revControlCode
1933:
1934: from wip_requirement_operations wro, wip_operations wo, mtl_system_items_b msi
1935: where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1936: and wro.wip_entity_id = wo.wip_entity_id
1937: and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
1938: and wro.operation_seq_num = wo.operation_seq_num
1976: l_supplyType,
1977: l_reqDate,
1978: l_revControlCode
1979:
1980: from wip_requirement_operations wro,
1981: wip_repetitive_schedules wrs,
1982: mtl_system_items_b msi,
1983: wip_operations wo
1984: where wro.repetitive_schedule_id = p_alloc_comp_tbl(i).repetitive_schedule_id
2168: wdj.project_id,
2169: wdj.task_id
2170: from wip_entities we,
2171: wip_discrete_jobs wdj,
2172: wip_requirement_operations wro,
2173: wip_parameters wp,
2174: wip_operations wo
2175: where we.wip_entity_id = wdj.wip_entity_id
2176: and we.entity_type = 1
2197: wdj.project_id,
2198: wdj.task_id
2199: from wip_entities we,
2200: wip_discrete_jobs wdj,
2201: wip_requirement_operations wro,
2202: wip_parameters wp,
2203: wip_operations wo
2204: where wo.wip_entity_id = we.wip_entity_id
2205: and wo.operation_seq_num = wro.operation_seq_num
2234: from wip_entities we,
2235: mtl_system_items_kfv msik,
2236: wip_repetitive_schedules wrs,
2237: wip_repetitive_items wri,
2238: wip_requirement_operations wro,
2239: wip_parameters wp,
2240: wip_lines wl,
2241: wip_operations wo
2242: where we.wip_entity_id = wrs.wip_entity_id
2602: l_logLevel number;
2603:
2604: CURSOR c_disc_operations(v_wip_entity_id NUMBER, v_organization_id NUMBER) IS
2605: select unique operation_seq_num
2606: from wip_requirement_operations
2607: where wip_entity_id = v_wip_entity_id
2608: and organization_id = v_organization_id
2609: and wip_supply_type in (wip_constants.op_pull, wip_constants.assy_pull);
2610:
2610:
2611: CURSOR c_rep_operations(v_wip_entity_id NUMBER, v_repetitive_schedule_id NUMBER,
2612: v_organization_id NUMBER) IS
2613: select unique operation_seq_num
2614: from wip_requirement_operations
2615: where wip_entity_id = v_wip_entity_id
2616: and organization_id = v_organization_id
2617: and repetitive_schedule_id = v_repetitive_schedule_id
2618: and wip_supply_type in (wip_constants.op_pull, wip_constants.assy_pull);