[Home] [Help]
97: bind variables, use table joins. Also added rownum in the subquery */
98:
99: CURSOR c_repLines IS
100: select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
101: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
102: where wro.wip_entity_id = p_wip_entity_id
103: and wro.repetitive_schedule_id = p_repetitive_schedule_id
104: and mtrl.txn_source_id = wro.wip_entity_id
105: and mtrl.reference_id = wro.repetitive_schedule_id
118: /* 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. */
119:
120: CURSOR c_discLines IS
121: select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
122: from mtl_txn_request_lines mtrl, wip_requirement_operations wro, wip_entities we /* bug 4211266 */
123: where we.wip_entity_id = p_wip_entity_id
124: and wro.organization_id = we.organization_id
125: and wro.wip_entity_id = we.wip_entity_id
126: and mtrl.organization_id = wro.organization_id
217: --Fix bug 15874569
218: SAVEPOINT updateBackorder;
219: LOOP
220: BEGIN
221: update wip_requirement_operations
222: set quantity_backordered = 0
223: where wip_entity_id = p_wip_entity_id;
224: EXIT;
225: EXCEPTION
229: END;
230: END LOOP;
231: --End of Fix bug 15874569
232: elsif(p_wip_entity_type = wip_constants.repetitive) then
233: update wip_requirement_operations
234: set quantity_backordered = 0
235: where wip_entity_id = p_wip_entity_id
236: and repetitive_schedule_id = p_repetitive_schedule_id;
237: end if;
270: l_dummy VARCHAR2(1);
271:
272: CURSOR c_repLines IS
273: select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
274: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
275: where mtrl.txn_source_id = p_wip_entity_id
276: and mtrl.reference_id = p_repetitive_schedule_id
277: and wro.wip_entity_id = p_wip_entity_id
278: and wro.repetitive_schedule_id = p_repetitive_schedule_id
290: for update of wro.quantity_allocated, quantity_backordered nowait;
291:
292: CURSOR c_discLines IS
293: select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
294: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
295: where mtrl.txn_source_id = p_wip_entity_id
296: and wro.wip_entity_id = p_wip_entity_id
297: and wro.operation_seq_num = p_operation_seq_num
298: and mtrl.txn_source_line_id = wro.operation_seq_num
364: end if;
365:
366: --finally update backordered quantity to 0
367: if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
368: update wip_requirement_operations
369: set quantity_backordered = 0
370: where wip_entity_id = p_wip_entity_id
371: and operation_seq_num = p_operation_seq_num
372: and inventory_item_id = p_inventory_item_id;
370: where wip_entity_id = p_wip_entity_id
371: and operation_seq_num = p_operation_seq_num
372: and inventory_item_id = p_inventory_item_id;
373: elsif(p_wip_entity_type = wip_constants.repetitive) then
374: update wip_requirement_operations
375: set quantity_backordered = 0
376: where wip_entity_id = p_wip_entity_id
377: and repetitive_schedule_id = p_repetitive_schedule_id
378: and operation_seq_num = p_operation_seq_num
405:
406: l_msgCount NUMBER;
407:
408: BEGIN
409: update wip_requirement_operations
410: set quantity_allocated = nvl(quantity_allocated,0) - p_openQty,
411: last_update_date = sysdate,
412: last_updated_by = fnd_global.user_id,
413: last_update_login = fnd_global.login_id
433: l_reductionQty NUMBER;
434:
435: CURSOR c_repLines(v_wip_entity_id NUMBER, v_repetitive_schedule_id NUMBER, v_operation_seq_num NUMBER, v_inventory_item_id NUMBER) IS
436: select line_id, nvl(mtrl.required_quantity,(nvl(quantity_detailed,0) - nvl(quantity_delivered,0))) open_quantity, wro.rowid
437: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
438: where mtrl.txn_source_id = v_wip_entity_id
439: and mtrl.reference_id = v_repetitive_schedule_id
440: and wro.wip_entity_id = v_wip_entity_id
441: and wro.repetitive_schedule_id = v_repetitive_schedule_id
455: for update of wro.quantity_allocated, quantity_backordered nowait;
456:
457: CURSOR c_discLines(v_wip_entity_id NUMBER, v_operation_seq_num NUMBER, v_inventory_item_id NUMBER) IS
458: select line_id, nvl(mtrl.required_quantity,(nvl(quantity_detailed,0) - nvl(quantity_delivered,0))) open_quantity, wro.rowid
459: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
460: where mtrl.txn_source_id = v_wip_entity_id
461: and wro.wip_entity_id = v_wip_entity_id
462: and wro.operation_seq_num = v_operation_seq_num
463: and mtrl.txn_source_line_id = wro.operation_seq_num
538: end if;
539:
540: /*finally update backordered quantity and allocated quantity for this component*/
541: if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
542: update wip_requirement_operations
543: set quantity_backordered = greatest((quantity_backordered - p_comp_tbl(i).requested_quantity), 0),
544: quantity_allocated = quantity_allocated - p_comp_tbl(i).requested_quantity
545: where wip_entity_id = p_comp_tbl(i).wip_entity_id
546: and operation_seq_num = p_comp_tbl(i).operation_seq_num
545: where wip_entity_id = p_comp_tbl(i).wip_entity_id
546: and operation_seq_num = p_comp_tbl(i).operation_seq_num
547: and inventory_item_id = p_comp_tbl(i).inventory_item_id;
548: elsif(p_wip_entity_type = wip_constants.repetitive) then
549: update wip_requirement_operations
550: set quantity_backordered = greatest(quantity_backordered - p_comp_tbl(i).requested_quantity, 0),
551: quantity_allocated = quantity_allocated - p_comp_tbl(i).requested_quantity
552: where wip_entity_id = p_comp_tbl(i).wip_entity_id
553: and repetitive_schedule_id = p_comp_tbl(i).repetitive_schedule_id
581: x_return_status OUT NOCOPY VARCHAR2,
582: x_msg_data OUT NOCOPY VARCHAR2) IS
583: CURSOR c_repLines IS
584: select line_id
585: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
586: where mtrl.txn_source_id = p_wip_entity_id
587: and mtrl.txn_source_line_id = p_repetitive_schedule_id
588: and wro.wip_entity_id = p_wip_entity_id
589: and wro.repetitive_schedule_id = p_repetitive_schedule_id
605: -- Also, the source_type should not be WIP. In case of backflush transfer, the source type is INV.
606:
607: CURSOR c_discLines IS
608: select line_id
609: from mtl_txn_request_lines mtrl, wip_requirement_operations wro
610: where mtrl.txn_source_id = p_wip_entity_id
611: and wro.wip_entity_id = p_wip_entity_id
612: and wro.operation_seq_num = 1
613: and mtrl.txn_source_line_id = wro.operation_seq_num
934: wro.wip_supply_type,
935: wro.date_required,
936: msi.revision_qty_control_code
937:
938: from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo,wip_discrete_jobs wdj /*Bug 14050050 */
939: where wro.wip_entity_id = v_wip_entity_id
940: and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
941: and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
942: and (
983: wro.wip_supply_type,
984: wro.date_required,
985: msi.revision_qty_control_code
986:
987: from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
988: where wro.wip_entity_id = v_wip_entity_id
989: and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
990: and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
991: /*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
1023: wro.wip_supply_type,
1024: wro.date_required,
1025: msi.revision_qty_control_code
1026:
1027: from wip_requirement_operations wro, wip_operations wo, mtl_system_items_b msi
1028: where wro.wip_entity_id = v_wip_entity_id
1029: and wro.wip_entity_id = wo.wip_entity_id
1030: and wro.operation_seq_num = wo.operation_seq_num
1031: and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
1070: wro.wip_supply_type,
1071: wro.date_required,
1072: msi.revision_qty_control_code
1073:
1074: from wip_requirement_operations wro,
1075: wip_repetitive_schedules wrs,
1076: mtl_system_items_b msi,
1077: wip_operations wo
1078: where wro.repetitive_schedule_id = v_rep_sched_id
1727:
1728: /* moved to pre_allocate_material in wippckpb.pls per bug#2642679
1729: if(p_wip_entity_type in(wip_constants.repetitive,wip_constants.discrete,wip_constants.eam,wip_constants.lotbased)) then
1730: if(p_use_pickset_flag = 'N') then
1731: update wip_requirement_operations
1732: set quantity_backordered = p_open_qty,
1733: last_update_date = l_sysDate,
1734: last_updated_by = l_userId,
1735: last_update_login = l_loginId
1888: l_supplyType,
1889: l_reqDate,
1890: l_revControlCode
1891:
1892: from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
1893: where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1894: and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
1895: and (
1896: ( 'Y' = (select allocate_backflush_components
1932: l_supplyType,
1933: l_reqDate,
1934: l_revControlCode
1935:
1936: from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
1937: where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1938: and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
1939: and wro.required_quantity > (wro.quantity_issued +
1940: nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1968: l_supplyType,
1969: l_reqDate,
1970: l_revControlCode
1971:
1972: from wip_requirement_operations wro, wip_operations wo, mtl_system_items_b msi
1973: where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1974: and wro.wip_entity_id = wo.wip_entity_id
1975: and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
1976: and wro.operation_seq_num = wo.operation_seq_num
2014: l_supplyType,
2015: l_reqDate,
2016: l_revControlCode
2017:
2018: from wip_requirement_operations wro,
2019: wip_repetitive_schedules wrs,
2020: mtl_system_items_b msi,
2021: wip_operations wo
2022: where wro.repetitive_schedule_id = p_alloc_comp_tbl(i).repetitive_schedule_id
2217: and wdj.job_type = decode(p_job_type, 4, wdj.job_type, p_job_type) /*Bug 5932126 (FP of 5880558): Added to check job type*/
2218: and we.organization_id = p_organization_id
2219: and exists(
2220: select 1
2221: from wip_requirement_operations wro,
2222: wip_operations wo
2223: where we.wip_entity_id = wro.wip_entity_id
2224: and we.organization_id = wro.organization_id
2225: and wro.wip_entity_id = wo.wip_entity_id(+)
2239: wdj.project_id,
2240: wdj.task_id
2241: from wip_entities we,
2242: wip_discrete_jobs wdj,
2243: wip_requirement_operations wro,
2244: wip_parameters wp,
2245: wip_operations wo
2246: where wo.wip_entity_id = we.wip_entity_id
2247: and wo.operation_seq_num = wro.operation_seq_num
2282: min(wro.date_required) min_date_required
2283: from wip_entities we,
2284: wip_discrete_jobs wdj,
2285: wip_parameters wp,
2286: wip_requirement_operations wro,
2287: wip_operations wo
2288: where we.wip_entity_id = wdj.wip_entity_id
2289: and we.organization_id = wdj.organization_id
2290: and wp.organization_id = wdj.organization_id
2298: and wdj.MATERIAL_ISSUE_BY_MO LIKE 'Y' --only for workorders that have Enable Material Request as Yes
2299: and we.organization_id = p_organization_id
2300: and exists(
2301: select 1
2302: from wip_requirement_operations wro,
2303: wip_operations wo
2304: where we.wip_entity_id = wro.wip_entity_id
2305: and we.organization_id = wro.organization_id
2306: and wro.wip_entity_id = wo.wip_entity_id(+)
2325: from wip_entities we,
2326: mtl_system_items_kfv msik,
2327: wip_repetitive_schedules wrs,
2328: wip_repetitive_items wri,
2329: wip_requirement_operations wro,
2330: wip_parameters wp,
2331: wip_lines wl,
2332: wip_operations wo
2333: where we.wip_entity_id = wrs.wip_entity_id
2707: l_logLevel number;
2708:
2709: CURSOR c_disc_operations(v_wip_entity_id NUMBER, v_organization_id NUMBER) IS
2710: select unique operation_seq_num
2711: from wip_requirement_operations
2712: where wip_entity_id = v_wip_entity_id
2713: and organization_id = v_organization_id
2714: and wip_supply_type in (wip_constants.op_pull, wip_constants.assy_pull);
2715:
2715:
2716: CURSOR c_rep_operations(v_wip_entity_id NUMBER, v_repetitive_schedule_id NUMBER,
2717: v_organization_id NUMBER) IS
2718: select unique operation_seq_num
2719: from wip_requirement_operations
2720: where wip_entity_id = v_wip_entity_id
2721: and organization_id = v_organization_id
2722: and repetitive_schedule_id = v_repetitive_schedule_id
2723: and wip_supply_type in (wip_constants.op_pull, wip_constants.assy_pull);