[Home] [Help]
267: SELECT '1'
268: INTO l_dummy
269: FROM dual
270: WHERE exists (SELECT '1'
271: FROM mtl_txn_request_lines mtrl
272: WHERE mtrl.lpn_id = p_lpn_id
273: AND mtrl.organization_id = p_org_id
274: AND (Nvl(mtrl.project_id,-1) <> Nvl(p_project_id,-1)
275: OR Nvl(mtrl.task_id,-1) <> Nvl(p_task_id,-1))
3859: , mtrl.reference_id
3860: , mtrl.quantity txn_qty
3861: , mtrl.quantity_detailed quantity_detailed
3862: , mtrl.quantity_delivered quantity_delivered
3863: FROM mtl_txn_request_lines mtrl
3864: , mtl_material_transactions_temp mmtt
3865: WHERE mtrl.organization_id = p_org_id
3866: AND nvl(mtrl.from_subinventory_code,'@$#_') = nvl(p_from_sub,'@$#_')
3867: AND nvl(mtrl.from_locator_id,-1) = nvl(p_from_locator_id,-1)
3930: , mtrl.reference_id
3931: , mtrl.quantity txn_qty
3932: , mtrl.quantity_detailed quantity_detailed
3933: , mtrl.quantity_delivered quantity_delivered
3934: FROM mtl_txn_request_lines mtrl
3935: , mtl_material_transactions_temp mmtt
3936: WHERE mtrl.organization_id = p_org_id
3937: AND nvl(mtrl.from_subinventory_code,'@$#_') = nvl(p_from_sub,'@$#_')
3938: AND nvl(mtrl.from_locator_id,-1) = nvl(p_from_locator_id,-1)
3980: ,2) quantity_ordering
3981: ,mtrl.reference reference
3982: ,mtrl.reference_type_code reference_type_code
3983: ,mtrl.reference_id reference_id
3984: FROM mtl_txn_request_lines mtrl
3985: WHERE mtrl.organization_id = p_org_id
3986: AND mtrl.inventory_item_id = p_item
3987: AND mtrl.uom_code = p_uom_code
3988: AND Nvl(mtrl.revision, Nvl(p_rev, '@@@')) = Nvl(p_rev, '@@@')
4975:
4976: --
4977: -- UPDATE THE MOVE ORDER LINE HERE
4978: --
4979: update mtl_txn_request_lines
4980: set reference = l_reference
4981: ,reference_type_code = l_reference_type_code
4982: ,reference_id = l_reference_id
4983: ,quantity = quantity + l_mol_res_out(l_loop_index).quantity
5102: --update mol for the sub and loc
5103: --{{
5104: --Receive into a RCV sub/loc. Check MOL has these columns
5105: --stamped afterwards}}
5106: UPDATE mtl_txn_request_lines
5107: SET from_subinventory_code = Nvl(p_subinventory,from_subinventory_code)
5108: , from_locator_id = Nvl(p_locator_id, from_locator_id)
5109: , backorder_delivery_detail_id = l_mol_res_out(l_loop_index).backorder_delivery_detail_id
5110: , crossdock_type = l_mol_res_out(l_loop_index).crossdock_type
5585: print_debug('MAINTAIN_MO_CON - l_tmp_qty_dlvd:'||l_tmp_qty_dlvd,4);
5586: print_debug('MAINTAIN_MO_CON - l_tmp_uom_code:'||l_tmp_uom_code,4);
5587: END IF;
5588:
5589: UPDATE mtl_txn_request_lines
5590: SET inspection_status = Decode(l_transaction_type,'ACCEPT',2,'REJECT',3,inspection_status)
5591: , lpn_id = p_transfer_lpn_id
5592: , quantity = l_tmp_qty
5593: , quantity_detailed = Decode(l_transaction_type,'TRANSFER',l_tmp_qty_dtld,NULL)
5623: IF (l_debug = 1) THEN
5624: print_debug('MAINTAIN_MO_CON - before updating mol update or close = C ',1);
5625: END IF;
5626:
5627: UPDATE mtl_txn_request_lines
5628: SET line_status = inv_globals.G_TO_STATUS_CLOSED
5629: , quantity = Nvl(quantity_delivered,0)
5630: , quantity_detailed = quantity_delivered
5631: , secondary_quantity_detailed = SECONDARY_QUANTITY_DELIVERED
5671: IF (l_debug = 1) THEN
5672: print_debug('MAINTAIN_MO_CON - before updating mol update or clode <> C ',1);
5673: END IF;
5674:
5675: UPDATE mtl_txn_request_lines
5676: SET inspection_status = Decode(l_transaction_type,'ACCEPT',2,'REJECT',3,inspection_status)
5677: , lpn_id = p_transfer_lpn_id
5678: , quantity = l_tmp_qty
5679: , quantity_detailed = Decode(l_transaction_type,'TRANSFER',l_tmp_qty_dtld,NULL)
5796: ,(mtrl.secondary_quantity - Nvl(mtrl.secondary_quantity_delivered, 0))
5797: into l_backorder_delivery_detail_id
5798: ,l_split_qty
5799: ,l_split_sec_qty
5800: from MTL_TXN_REQUEST_LINES mtrl
5801: where mtrl.line_id = l_mo_split_tb(1).line_id;
5802:
5803: l_progress := 'WMSINB-45115';
5804: IF (l_debug = 1) THEN
5855:
5856: --Update quantity with the quantity converted to the UOM in the RTI. This is changed so
5857: --that the we honor the UOM entered by the user during
5858: --Transfer
5859: UPDATE mtl_txn_request_lines
5860: SET inspection_status = Decode(l_transaction_type,'ACCEPT',2,'REJECT',3,inspection_status)
5861: , lpn_id = p_transfer_lpn_id
5862: , quantity = Decode(uom_code
5863: ,p_uom_code
5909: --same consolidation criteria. Otherwise it should be
5910: --updated just like the crossdock mols.
5911:
5912: IF (l_update_or_close = 'C') THEN
5913: UPDATE mtl_txn_request_lines
5914: SET line_status = inv_globals.G_TO_STATUS_CLOSED
5915: , quantity = quantity_delivered
5916: , quantity_detailed = quantity_delivered
5917: , secondary_quantity_detailed = SECONDARY_QUANTITY_DELIVERED
5919: , backorder_delivery_detail_id = l_backorder_delivery_detail_id
5920: , crossdock_type = Decode(l_backorder_delivery_detail_id,NULL,NULL,crossdock_type)
5921: WHERE line_id = l_mo_split_tb(1).line_id;
5922: ELSE
5923: UPDATE mtl_txn_request_lines
5924: SET inspection_status = Decode(l_transaction_type,'ACCEPT',2,'REJECT',3,inspection_status)
5925: , lpn_id = p_transfer_lpn_id
5926: , quantity = Decode(uom_code
5927: ,p_uom_code
5973: * to update the wms_process_flag MOL with 4 the first time
5974: * time you reach here. You should only update it when the
5975: * qty being processing match the MOL qty (which will done
5976: * when maintain_mo_con is called with qty 1 */
5977: --UPDATE mtl_txn_request_lines
5978: --SET wms_process_flag = 1
5979: --WHERE line_id = l_mol_rec.line_id;
5980:
5981: l_remaining_primary_quantity := 0;
6102: ELSE
6103: l_qty_detailed := 0;
6104: END IF;
6105:
6106: update mtl_txn_request_lines
6107: set reference = l_reference
6108: ,reference_type_code = l_reference_type_code
6109: ,reference_id = l_reference_id
6110: ,quantity = quantity + l_mol_res_in(1).quantity
6189: -- RAISE fnd_api.g_exc_error;
6190: -- END IF;
6191: --
6192: -- --update mol for the sub and loc
6193: -- UPDATE mtl_txn_request_lines
6194: -- SET from_subinventory_code = Nvl(p_subinventory,from_subinventory_code)
6195: -- , from_locator_id = Nvl(p_locator_id, from_locator_id)
6196: -- WHERE header_id = l_move_order_header_id
6197: -- and organization_id = p_org_id
6570: l_progress := 'WMSINB-45053';
6571:
6572:
6573: --Close line
6574: UPDATE mtl_txn_request_lines
6575: SET quantity_delivered = Decode(p_mmtt_temp_id,NULL,
6576: Nvl(quantity_delivered,0)+l_quantity_delivered,quantity_delivered)
6577: ,secondary_quantity_delivered = Decode(p_mmtt_temp_id,NULL,
6578: Nvl(secondary_quantity_delivered,0)+l_sec_quantity_delivered,quantity_delivered)
6643: END IF;
6644:
6645: l_progress := 'WMSINB-45053';
6646:
6647: UPDATE mtl_txn_request_lines
6648: SET quantity_delivered = Decode(p_mmtt_temp_id,NULL,quantity,quantity_delivered)
6649: -- OPMConvergence
6650: , secondary_quantity_delivered = Decode(p_mmtt_temp_id,NULL,secondary_quantity,secondary_quantity_delivered)
6651: -- OPMConvergence
6745:
6746: IF (l_transaction_type = 'CORRECT') THEN
6747: --We need to update the wms_process_flag on mol for all the lines for the given lpn as
6748: --we update all the mols for the given lpn to 2 in mark_returns
6749: UPDATE mtl_txn_request_lines
6750: SET wms_process_flag = 1
6751: WHERE (lpn_id = p_lpn_id
6752: OR lpn_id = p_transfer_lpn_id);
6753: END IF;
7128:
7129: --
7130: -- UPDATE THE MOVE ORDER LINE HERE
7131: --
7132: update mtl_txn_request_lines
7133: set reference = l_reference
7134: ,reference_type_code = l_reference_type_code
7135: ,reference_id = l_reference_id
7136: ,quantity = quantity + ABS(l_mol_res_out(l_loop_index).quantity)
7246: RAISE fnd_api.g_exc_error;
7247: END IF;
7248:
7249: --update mol for the sub and loc
7250: UPDATE mtl_txn_request_lines
7251: SET from_subinventory_code = Nvl(l_transfer_sub,from_subinventory_code)
7252: , from_locator_id = Nvl(l_transfer_locator_id, from_locator_id)
7253: WHERE header_id = l_move_order_header_id
7254: and line_id = l_move_order_line_id
7285:
7286: --We need to update the wms_process_flag on mol for all the lines for the given lpn as
7287: --we update all the mols for the given lpn to 2 in mark_returns
7288:
7289: UPDATE mtl_txn_request_lines
7290: SET wms_process_flag = 1
7291: WHERE (lpn_id = p_lpn_id
7292: OR lpn_id = p_transfer_lpn_id);
7293:
7343: should have been performed in the logic above. Note that this
7344: assumes that the UI have marked the correct MOL for processing
7345:
7346: --Update the wms_process_flag for all MOLs for the fiven lpns
7347: UPDATE mtl_txn_request_lines
7348: SET wms_process_flag = 1
7349: WHERE lpn_id = p_lpn_id;
7350:
7351: UPDATE mtl_txn_request_lines
7347: UPDATE mtl_txn_request_lines
7348: SET wms_process_flag = 1
7349: WHERE lpn_id = p_lpn_id;
7350:
7351: UPDATE mtl_txn_request_lines
7352: SET wms_process_flag = 1
7353: WHERE lpn_id = p_transfer_lpn_id;
7354: */
7355: EXCEPTION
11183: , Nvl(mmtt.transaction_quantity,mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) txn_qty
11184: , Nvl(mmtt.transaction_uom,mtrl.uom_code) txn_uom_code
11185: , Nvl(mmtt.secondary_transaction_quantity
11186: , mtrl.secondary_quantity-Nvl(mtrl.secondary_quantity_delivered,0)) sec_qty --OPM Convergence
11187: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
11188: WHERE mmtt.move_order_line_id (+)= mtrl.line_id
11189: AND mtrl.lpn_id = v_lpn_id
11190: AND mtrl.line_status = 7
11191: AND mtrl.quantity-Nvl(mtrl.quantity_delivered,0) > 0
13839: -- INSPECTED MATERIAL EXISTS IN FLPN FAIL THE TXN
13840: -- Begin
13841: -- select 1
13842: -- into l_insp_mat_exists
13843: -- from mtl_txn_request_lines mol
13844: -- where mol.lpn_id = l_lpn_rec.lpn_id
13845: -- and nvl(mol.inspection_status,2) = 1
13846: -- and rownum = 1;
13847: -- if l_insp_mat_exists = 1 then