[Home] [Help]
136:
137: UPDATE MTL_PARAMETERS
138: SET lpn_starting_number = l_lpn_start_num+p_qty,
139: last_update_date = SYSDATE,
140: last_updated_by = FND_GLOBAL.USER_ID
141: WHERE organization_id = p_org_id;
142:
143: x_curr_seq:=l_lpn_start_num;
144:
818: END IF;
819:
820: l_progress := 'Getting any profile user values';
821:
822: SELECT fnd_global.user_id
823: , FND_PROFILE.value('CONC_REQUEST_ID')
824: INTO l_user_id
825: , l_request_id
826: FROM DUAL;
1231:
1232: UPDATE mtl_client_parameters
1233: SET lpn_starting_number = l_lpn_start_num+p_qty,
1234: last_update_date = SYSDATE,
1235: last_updated_by = FND_GLOBAL.USER_ID
1236: WHERE client_id = p_client_id;
1237:
1238: x_curr_seq:=l_lpn_start_num;
1239:
3301: END IF;
3302:
3303: UPDATE wms_lpn_contents wlc
3304: SET wlc.last_update_date = SYSDATE
3305: , wlc.last_updated_by = fnd_global.user_id
3306: , wlc.organization_id = l_new.organization_id
3307: , wlc.serial_summary_entry = DECODE (new_org.serial_number_control_code,1,2,6,2,wlc.serial_summary_entry)
3308: , wlc.serial_number = DECODE (new_org.serial_number_control_code,1,NULL,6,NULL,wlc.serial_number)
3309: , wlc.lot_number = DECODE (new_org.lot_control_code,1,NULL,wlc.lot_number)
3732: BEGIN
3733: FORALL bulk_i IN l_lpn_bulk_rec.lpn_id.first .. l_lpn_bulk_rec.lpn_id.last
3734: UPDATE wms_license_plate_numbers wlpn
3735: SET last_update_date = SYSDATE
3736: , last_updated_by = fnd_global.user_id
3737: , organization_id = l_lpn_bulk_rec.organization_id(bulk_i)
3738: , license_plate_number = l_lpn_bulk_rec.license_plate_number(bulk_i)
3739: , parent_lpn_id = l_lpn_bulk_rec.parent_lpn_id(bulk_i)
3740: , outermost_lpn_id = l_lpn_bulk_rec.outermost_lpn_id(bulk_i)
3819:
3820: FORALL bulk_i IN l_lpn_bulk_rec.outermost_lpn_id.first .. l_lpn_bulk_rec.outermost_lpn_id.last
3821: UPDATE wms_license_plate_numbers wlpn
3822: SET last_update_date = SYSDATE
3823: , last_updated_by = fnd_global.user_id
3824: , outermost_lpn_id = l_lpn_bulk_rec.outermost_lpn_id(bulk_i)
3825: , organization_id = l_lpn_bulk_rec.organization_id(bulk_i)
3826: , subinventory_code = l_lpn_bulk_rec.subinventory_code(bulk_i)
3827: , locator_id = l_lpn_bulk_rec.locator_id(bulk_i)
5118: FORALL bulk_i IN l_tmp_bulk_lpns.lpn_id.first .. l_tmp_bulk_lpns.lpn_id.last
5119: UPDATE wms_lpn_contents
5120: SET organization_id = l_lpn.organization_id
5121: , last_update_date = SYSDATE
5122: , last_updated_by = fnd_global.user_id
5123: , request_id = l_request_id
5124: WHERE parent_lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
5125:
5126: IF (l_debug = 1) THEN
5134: SET current_organization_id = l_lpn.organization_id
5135: , current_subinventory_code = l_lpn.subinventory_code
5136: , current_locator_id = l_lpn.locator_id
5137: , last_update_date = SYSDATE
5138: , last_updated_by = fnd_global.user_id
5139: WHERE lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
5140:
5141: IF (l_debug = 1) THEN
5142: mdebug('Bulk updated org/sub/loc in MSN cnt='||SQL%ROWCOUNT, G_INFO);
5189: UPDATE mtl_serial_numbers
5190: SET lpn_id = p_lpn_id
5191: , cost_group_id = p_cost_group_id
5192: , last_update_date = SYSDATE
5193: , last_updated_by = fnd_global.user_id
5194: , last_txn_source_type_id = DECODE(last_txn_source_type_id, 5, 5, p_source_type_id)--8687722
5195: , last_txn_source_id = p_source_header_id
5196: , last_txn_source_name = p_source_name
5197: , revision = DECODE(current_status, 3, revision, p_revision)
5270: /* fix for bug 2949825 */
5271: IF ( l_operation_mode = L_CORRECT ) THEN
5272: UPDATE wms_lpn_contents
5273: SET last_update_date = SYSDATE
5274: , last_updated_by = fnd_global.user_id
5275: , request_id = l_request_id
5276: , quantity = l_quantity
5277: , uom_code = p_uom
5278: , primary_quantity = l_primary_quantity
5282: l_item_quantity := l_primary_quantity - l_existing_record_cursor.primary_quantity;
5283: ELSE
5284: UPDATE WMS_LPN_CONTENTS
5285: SET last_update_date = SYSDATE
5286: , last_updated_by = FND_GLOBAL.USER_ID
5287: , request_id = l_request_id
5288: , quantity = quantity + l_quantity
5289: , uom_code = p_uom
5290: , primary_quantity = primary_quantity + round(l_primary_quantity,5)
5328: , secondary_quantity
5329: , secondary_uom_code)
5330: VALUES (
5331: SYSDATE
5332: , fnd_global.user_id
5333: , SYSDATE
5334: , fnd_global.user_id
5335: , l_request_id
5336: , wms_lpn_contents_s.NEXTVAL
5330: VALUES (
5331: SYSDATE
5332: , fnd_global.user_id
5333: , SYSDATE
5334: , fnd_global.user_id
5335: , l_request_id
5336: , wms_lpn_contents_s.NEXTVAL
5337: , p_lpn_id
5338: , p_organization_id
5407: , secondary_uom_code
5408: )
5409: VALUES (
5410: SYSDATE
5411: , fnd_global.user_id
5412: , SYSDATE
5413: , fnd_global.user_id
5414: , l_request_id
5415: , WMS_LPN_CONTENTS_S.NEXTVAL
5409: VALUES (
5410: SYSDATE
5411: , fnd_global.user_id
5412: , SYSDATE
5413: , fnd_global.user_id
5414: , l_request_id
5415: , WMS_LPN_CONTENTS_S.NEXTVAL
5416: , p_lpn_id
5417: , p_organization_id
5435: );
5436: ELSE
5437: UPDATE wms_lpn_contents
5438: SET last_update_date = SYSDATE
5439: , last_updated_by = fnd_global.user_id
5440: , request_id = l_request_id
5441: , quantity = NVL(l_one_time_item_rec.quantity, 1) + NVL(l_quantity, 1)
5442: , uom_code = p_uom
5443: , source_type_id = p_source_type_id
5480: IF ( l_content_lpn.organization_id <> p_organization_id ) THEN
5481: UPDATE wms_lpn_contents
5482: SET organization_id = p_organization_id
5483: , last_update_date = SYSDATE
5484: , last_updated_by = fnd_global.user_id
5485: , request_id = l_request_id
5486: WHERE organization_id = l_content_lpn.organization_id
5487: AND parent_lpn_id = l_child_lpn.lpn_id;
5488: END IF;
5492: SET current_organization_id = p_organization_id
5493: , current_subinventory_code = l_subinventory
5494: , current_locator_id = l_locator_id
5495: , last_update_date = SYSDATE
5496: , last_updated_by = fnd_global.user_id
5497: WHERE current_organization_id = l_content_lpn.organization_id
5498: AND lpn_id = l_child_lpn.lpn_id;
5499: END IF;
5500: END LOOP;
5561: -- in the serial numbers table
5562: UPDATE mtl_serial_numbers
5563: SET lpn_id = NULL
5564: , last_update_date = SYSDATE
5565: , last_updated_by = fnd_global.user_id
5566: WHERE inventory_item_id = p_content_item_id
5567: AND current_organization_id = p_organization_id
5568: AND length(serial_number) = length(p_from_serial_number)
5569: AND serial_number BETWEEN p_from_serial_number AND NVL(p_to_serial_number, p_from_serial_number);
5653: IF inv_cache.item_rec.tracking_quantity_ind = 'PS' THEN -- 16466243
5654:
5655: UPDATE wms_lpn_contents
5656: SET last_update_date = SYSDATE
5657: , last_updated_by = fnd_global.user_id
5658: , request_id = l_request_id
5659: , quantity = (CASE WHEN uom_code = inv_cache.item_rec.primary_uom_code
5660: THEN Round((primary_quantity - l_item_quantity), 5)
5661: ELSE
5692: --Bug#8526734,no need to use std conversion everytime.Also added lot_number in case p_sec_quantity is null
5693: -- Decrement unpack quantity from contents and break loop
5694: UPDATE wms_lpn_contents
5695: SET last_update_date = SYSDATE
5696: , last_updated_by = fnd_global.user_id
5697: , request_id = l_request_id
5698: , quantity = quantity - l_converted_quantity
5699: , primary_quantity = primary_quantity - l_item_quantity
5700: , secondary_quantity = CASE WHEN p_sec_uom IS NOT NULL
5776: IF one_time_item_cursor%FOUND THEN
5777: IF ( l_quantity < l_one_time_item_rec.quantity ) THEN
5778: UPDATE wms_lpn_contents
5779: SET last_update_date = SYSDATE
5780: , last_updated_by = fnd_global.user_id
5781: , request_id = l_request_id
5782: , quantity = (l_one_time_item_rec.quantity - l_quantity)
5783: , uom_code = p_uom
5784: WHERE rowid = l_one_time_item_rec.rowid;
5866: -- Update the information for serialized packed items
5867: FORALL bulk_i IN l_tmp_bulk_lpns.lpn_id.first .. l_tmp_bulk_lpns.lpn_id.last
5868: UPDATE mtl_serial_numbers
5869: SET last_update_date = SYSDATE
5870: , last_updated_by = fnd_global.user_id
5871: , lpn_id = NULL
5872: WHERE lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
5873:
5874: IF (l_debug = 1) THEN
6367: , l_lpn.status_id
6368: , l_lpn.sealed_status
6369: , l_operation_mode
6370: , SYSDATE
6371: , fnd_global.user_id
6372: , SYSDATE
6373: , fnd_global.user_id
6374: , p_cost_group_id
6375: , DECODE(l_new.outermost_lpn_id, fnd_api.g_miss_num, NULL, NVL(l_new.outermost_lpn_id, l_lpn.outermost_lpn_id))
6369: , l_operation_mode
6370: , SYSDATE
6371: , fnd_global.user_id
6372: , SYSDATE
6373: , fnd_global.user_id
6374: , p_cost_group_id
6375: , DECODE(l_new.outermost_lpn_id, fnd_api.g_miss_num, NULL, NVL(l_new.outermost_lpn_id, l_lpn.outermost_lpn_id))
6376: , NVL(p_source_type_id, l_lpn.source_type_id)
6377: , NVL(p_source_header_id, l_lpn.source_header_id)
7422: p_pri_qty=> l_primary_quantity,
7423: p_uom=> p_uom,
7424: p_date=> SYSDATE,
7425: p_reason_id=> NULL,
7426: p_user_id=> fnd_global.user_id,
7427: p_frt_code=> NULL,
7428: p_ship_num=> NULL,
7429: p_dist_id=> NULL,
7430: p_way_bill=> NULL,
7458: mdebug('Insert lot entry in MTL_LOT_NUMBER_TEMP lot='|| l_lot_number, G_INFO);
7459: END IF;
7460: x_error_code := inv_trx_util_pub.insert_lot_trx(
7461: p_trx_tmp_id=> l_trx_tmp_id,
7462: p_user_id=> fnd_global.user_id,
7463: p_lot_number=> l_lot_number,
7464: p_trx_qty=> p_quantity,
7465: p_pri_qty=> l_primary_quantity,
7466: p_secondary_qty =>p_sec_quantity, --INVCONV kkillams
7491: END IF;
7492: --Insert serials into MTL_SERIAL_NUMBERS_TEMP
7493: x_error_code := inv_trx_util_pub.insert_ser_trx(
7494: p_trx_tmp_id=> l_trx_tmp_id,
7495: p_user_id=> fnd_global.user_id,
7496: p_fm_ser_num=> l_serial_number_from,
7497: p_to_ser_num=> l_serial_number_to,
7498: x_proc_msg=> x_proc_msg
7499: );
7620: -- Bug5659809: update last_update_date and last_update_by as well
7621: UPDATE wms_license_plate_numbers
7622: SET lpn_context = l_lpn_source
7623: , last_update_date = SYSDATE
7624: , last_updated_by = fnd_global.user_id
7625: WHERE lpn_id = l_lpn_to_pack;
7626: ELSE
7627: FETCH container_lpn INTO l_lpn_to_pack;
7628: EXIT WHEN container_lpn%NOTFOUND;
7702: p_pri_qty=> l_primary_quantity,
7703: p_uom=> l_primary_uom,
7704: p_date=> SYSDATE,
7705: p_reason_id=> NULL,
7706: p_user_id=> fnd_global.user_id,
7707: p_frt_code=> NULL,
7708: p_ship_num=> NULL,
7709: p_dist_id=> NULL,
7710: p_way_bill=> NULL,
7738: mdebug('Insert lot entry in MTL_LOT_NUMBER_TEMP lot='|| l_lot_number, G_INFO);
7739: END IF;
7740: x_error_code := inv_trx_util_pub.insert_lot_trx(
7741: p_trx_tmp_id=> l_trx_tmp_id,
7742: p_user_id=> fnd_global.user_id,
7743: p_lot_number=> l_lot_number,
7744: p_trx_qty=> l_primary_quantity,
7745: p_pri_qty=> l_primary_quantity,
7746: p_secondary_qty => l_sec_pack_quantity, --INVCONV kkillams
7769: mdebug('with l_trx_tmp_id='|| l_trx_tmp_id, G_INFO);
7770: END IF;
7771: x_error_code := inv_trx_util_pub.insert_ser_trx(
7772: p_trx_tmp_id=> l_trx_tmp_id,
7773: p_user_id=> fnd_global.user_id,
7774: p_fm_ser_num=> l_serial_number_from,
7775: p_to_ser_num=> l_serial_number_to,
7776: x_proc_msg=> x_proc_msg
7777: );
8075: -- if serial status is not resides in store (3), update lot and rev
8076: UPDATE mtl_serial_numbers
8077: SET lpn_id = p_lpn_id,
8078: last_update_date = SYSDATE,
8079: last_updated_by = fnd_global.user_id,
8080: last_txn_source_type_id = p_source_type_id,
8081: revision = DECODE(current_status, 3, revision, p_revision),
8082: lot_number = DECODE(current_status, 3, lot_number, p_lot_number)
8083: WHERE inventory_item_id = p_content_item_id
8100: END LOOP;
8101:
8102: UPDATE wms_lpn_contents
8103: SET last_update_date = SYSDATE,
8104: last_updated_by = fnd_global.user_id,
8105: serial_summary_entry = 1,
8106: source_type_id = p_source_type_id
8107: WHERE parent_lpn_id = p_lpn_id
8108: AND organization_id = p_organization_id
8111: AND NVL(lot_number, G_NULL_CHAR) = NVL(p_lot_number, G_NULL_CHAR);
8112:
8113: UPDATE wms_license_plate_numbers
8114: SET last_update_date = SYSDATE,
8115: last_updated_by = fnd_global.user_id,
8116: lpn_context = LPN_CONTEXT_WIP
8117: WHERE lpn_id = p_lpn_id
8118: AND organization_id = p_organization_id;
8119: END IF;
8806: , p_trx_qty => 1
8807: , p_pri_qty => 1
8808: , p_uom => 'Ea'
8809: , p_date => SYSDATE
8810: , p_user_id => fnd_global.user_id
8811: , p_from_lpn_id => Nested_LPN_rec.parent_lpn_id
8812: , p_cnt_lpn_id => Nested_LPN_rec.lpn_id
8813: , x_trx_tmp_id => l_trx_tmp_id
8814: , x_proc_msg => x_msg_data );
8891: END IF;
8892: -- Insert record into MTL_TRANSACTIONS_LOTS_TEMP
8893: l_return_status := INV_TRX_UTIL_PUB.Insert_Lot_Trx(
8894: p_trx_tmp_id => l_trx_tmp_id
8895: , p_user_id => fnd_global.user_id
8896: , p_lot_number => l_crnt_item_rec.lot_number
8897: , p_trx_qty => l_crnt_item_rec.lot_quantity
8898: , p_pri_qty => l_converted_qty
8899: , p_secondary_qty => l_crnt_item_rec.secondary_quantity --INVCONV kkillams
8969: END IF;
8970: -- Range finished or last serial processed, insert serial with API and reset from serial
8971: l_return_status := INV_TRX_UTIL_PUB.Insert_Ser_Trx (
8972: p_trx_tmp_id => l_ser_tmp_id
8973: , p_user_id => fnd_global.user_id
8974: , p_fm_ser_num => l_fm_serial
8975: , p_to_ser_num => l_to_serial
8976: , x_proc_msg => x_msg_data );
8977: IF ( l_return_status <> 0 ) THEN
9040: , l_trx_tmp_id
9041: , 'Y'
9042: ,'Y' --Added for the bug 15900513
9043: , SYSDATE
9044: , fnd_global.user_id
9045: , SYSDATE
9046: , fnd_global.user_id
9047: , fnd_global.user_id
9048: , SYSDATE
9042: ,'Y' --Added for the bug 15900513
9043: , SYSDATE
9044: , fnd_global.user_id
9045: , SYSDATE
9046: , fnd_global.user_id
9047: , fnd_global.user_id
9048: , SYSDATE
9049: , p_organization_id
9050: , Nested_LPN_rec.subinventory_code
9043: , SYSDATE
9044: , fnd_global.user_id
9045: , SYSDATE
9046: , fnd_global.user_id
9047: , fnd_global.user_id
9048: , SYSDATE
9049: , p_organization_id
9050: , Nested_LPN_rec.subinventory_code
9051: , Nested_LPN_rec.locator_id
9211: , p_trx_qty => 1
9212: , p_pri_qty => 1
9213: , p_uom => 'Ea'
9214: , p_date => SYSDATE
9215: , p_user_id => fnd_global.user_id
9216: , p_from_lpn_id => lpn_rec.parent_lpn_id
9217: , p_cnt_lpn_id => lpn_rec.lpn_id
9218: , x_trx_tmp_id => l_trx_tmp_id
9219: , x_proc_msg => x_msg_data );
9347: , content_volume_uom_code = NULL
9348: , gross_weight = tare_weight
9349: , gross_weight_uom_code = tare_weight_uom_code
9350: , last_update_date = SYSDATE
9351: , last_updated_by = fnd_global.user_id
9352: WHERE rowid = lpn_rec.rowid;
9353: END LOOP;
9354:
9355: -- Standard check of p_commit.