[Home] [Help]
133:
134: UPDATE MTL_PARAMETERS
135: SET lpn_starting_number = l_lpn_start_num+p_qty,
136: last_update_date = SYSDATE,
137: last_updated_by = FND_GLOBAL.USER_ID
138: WHERE organization_id = p_org_id;
139:
140: x_curr_seq:=l_lpn_start_num;
141:
763: END IF;
764:
765: l_progress := 'Getting any profile user values';
766:
767: SELECT fnd_global.user_id
768: , FND_PROFILE.value('CONC_REQUEST_ID')
769: INTO l_user_id
770: , l_request_id
771: FROM DUAL;
3001: END IF;
3002:
3003: UPDATE wms_lpn_contents wlc
3004: SET wlc.last_update_date = SYSDATE
3005: , wlc.last_updated_by = fnd_global.user_id
3006: , wlc.organization_id = l_new.organization_id
3007: , wlc.serial_summary_entry = DECODE (new_org.serial_number_control_code,1,2,6,2,wlc.serial_summary_entry)
3008: , wlc.serial_number = DECODE (new_org.serial_number_control_code,1,NULL,6,NULL,wlc.serial_number)
3009: , wlc.lot_number = DECODE (new_org.lot_control_code,1,NULL,wlc.lot_number)
3432: BEGIN
3433: FORALL bulk_i IN l_lpn_bulk_rec.lpn_id.first .. l_lpn_bulk_rec.lpn_id.last
3434: UPDATE wms_license_plate_numbers wlpn
3435: SET last_update_date = SYSDATE
3436: , last_updated_by = fnd_global.user_id
3437: , organization_id = l_lpn_bulk_rec.organization_id(bulk_i)
3438: , license_plate_number = l_lpn_bulk_rec.license_plate_number(bulk_i)
3439: , parent_lpn_id = l_lpn_bulk_rec.parent_lpn_id(bulk_i)
3440: , outermost_lpn_id = l_lpn_bulk_rec.outermost_lpn_id(bulk_i)
3519:
3520: FORALL bulk_i IN l_lpn_bulk_rec.outermost_lpn_id.first .. l_lpn_bulk_rec.outermost_lpn_id.last
3521: UPDATE wms_license_plate_numbers wlpn
3522: SET last_update_date = SYSDATE
3523: , last_updated_by = fnd_global.user_id
3524: , outermost_lpn_id = l_lpn_bulk_rec.outermost_lpn_id(bulk_i)
3525: , organization_id = l_lpn_bulk_rec.organization_id(bulk_i)
3526: , subinventory_code = l_lpn_bulk_rec.subinventory_code(bulk_i)
3527: , locator_id = l_lpn_bulk_rec.locator_id(bulk_i)
4650: FORALL bulk_i IN l_tmp_bulk_lpns.lpn_id.first .. l_tmp_bulk_lpns.lpn_id.last
4651: UPDATE wms_lpn_contents
4652: SET organization_id = l_lpn.organization_id
4653: , last_update_date = SYSDATE
4654: , last_updated_by = fnd_global.user_id
4655: , request_id = l_request_id
4656: WHERE parent_lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
4657:
4658: IF (l_debug = 1) THEN
4666: SET current_organization_id = l_lpn.organization_id
4667: , current_subinventory_code = l_lpn.subinventory_code
4668: , current_locator_id = l_lpn.locator_id
4669: , last_update_date = SYSDATE
4670: , last_updated_by = fnd_global.user_id
4671: WHERE lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
4672:
4673: IF (l_debug = 1) THEN
4674: mdebug('Bulk updated org/sub/loc in MSN cnt='||SQL%ROWCOUNT, G_INFO);
4721: UPDATE mtl_serial_numbers
4722: SET lpn_id = p_lpn_id
4723: , cost_group_id = p_cost_group_id
4724: , last_update_date = SYSDATE
4725: , last_updated_by = fnd_global.user_id
4726: , last_txn_source_type_id = p_source_type_id
4727: , last_txn_source_id = p_source_header_id
4728: , last_txn_source_name = p_source_name
4729: , revision = DECODE(current_status, 3, revision, p_revision)
4802: /* fix for bug 2949825 */
4803: IF ( l_operation_mode = L_CORRECT ) THEN
4804: UPDATE wms_lpn_contents
4805: SET last_update_date = SYSDATE
4806: , last_updated_by = fnd_global.user_id
4807: , request_id = l_request_id
4808: , quantity = l_quantity
4809: , uom_code = p_uom
4810: , primary_quantity = l_primary_quantity
4814: l_item_quantity := l_primary_quantity - l_existing_record_cursor.primary_quantity;
4815: ELSE
4816: UPDATE WMS_LPN_CONTENTS
4817: SET last_update_date = SYSDATE
4818: , last_updated_by = FND_GLOBAL.USER_ID
4819: , request_id = l_request_id
4820: , quantity = quantity + l_quantity
4821: , uom_code = p_uom
4822: , primary_quantity = primary_quantity + l_primary_quantity
4855: , secondary_quantity
4856: , secondary_uom_code)
4857: VALUES (
4858: SYSDATE
4859: , fnd_global.user_id
4860: , SYSDATE
4861: , fnd_global.user_id
4862: , l_request_id
4863: , wms_lpn_contents_s.NEXTVAL
4857: VALUES (
4858: SYSDATE
4859: , fnd_global.user_id
4860: , SYSDATE
4861: , fnd_global.user_id
4862: , l_request_id
4863: , wms_lpn_contents_s.NEXTVAL
4864: , p_lpn_id
4865: , p_organization_id
4934: , secondary_uom_code
4935: )
4936: VALUES (
4937: SYSDATE
4938: , fnd_global.user_id
4939: , SYSDATE
4940: , fnd_global.user_id
4941: , l_request_id
4942: , WMS_LPN_CONTENTS_S.NEXTVAL
4936: VALUES (
4937: SYSDATE
4938: , fnd_global.user_id
4939: , SYSDATE
4940: , fnd_global.user_id
4941: , l_request_id
4942: , WMS_LPN_CONTENTS_S.NEXTVAL
4943: , p_lpn_id
4944: , p_organization_id
4962: );
4963: ELSE
4964: UPDATE wms_lpn_contents
4965: SET last_update_date = SYSDATE
4966: , last_updated_by = fnd_global.user_id
4967: , request_id = l_request_id
4968: , quantity = NVL(l_one_time_item_rec.quantity, 1) + NVL(l_quantity, 1)
4969: , uom_code = p_uom
4970: , source_type_id = p_source_type_id
5007: IF ( l_content_lpn.organization_id <> p_organization_id ) THEN
5008: UPDATE wms_lpn_contents
5009: SET organization_id = p_organization_id
5010: , last_update_date = SYSDATE
5011: , last_updated_by = fnd_global.user_id
5012: , request_id = l_request_id
5013: WHERE organization_id = l_content_lpn.organization_id
5014: AND parent_lpn_id = l_child_lpn.lpn_id;
5015: END IF;
5019: SET current_organization_id = p_organization_id
5020: , current_subinventory_code = l_subinventory
5021: , current_locator_id = l_locator_id
5022: , last_update_date = SYSDATE
5023: , last_updated_by = fnd_global.user_id
5024: WHERE current_organization_id = l_content_lpn.organization_id
5025: AND lpn_id = l_child_lpn.lpn_id;
5026: END IF;
5027: END LOOP;
5088: -- in the serial numbers table
5089: UPDATE mtl_serial_numbers
5090: SET lpn_id = NULL
5091: , last_update_date = SYSDATE
5092: , last_updated_by = fnd_global.user_id
5093: WHERE inventory_item_id = p_content_item_id
5094: AND current_organization_id = p_organization_id
5095: AND length(serial_number) = length(p_from_serial_number)
5096: AND serial_number BETWEEN p_from_serial_number AND NVL(p_to_serial_number, p_from_serial_number);
5167:
5168: -- Decrement unpack quantity from contents and break loop
5169: UPDATE wms_lpn_contents
5170: SET last_update_date = SYSDATE
5171: , last_updated_by = fnd_global.user_id
5172: , request_id = l_request_id
5173: , quantity = quantity - l_converted_quantity
5174: , primary_quantity = primary_quantity - l_item_quantity
5175: , secondary_quantity = CASE WHEN p_sec_uom IS NOT NULL
5255: IF one_time_item_cursor%FOUND THEN
5256: IF ( l_quantity < l_one_time_item_rec.quantity ) THEN
5257: UPDATE wms_lpn_contents
5258: SET last_update_date = SYSDATE
5259: , last_updated_by = fnd_global.user_id
5260: , request_id = l_request_id
5261: , quantity = (l_one_time_item_rec.quantity - l_quantity)
5262: , uom_code = p_uom
5263: WHERE rowid = l_one_time_item_rec.rowid;
5345: -- Update the information for serialized packed items
5346: FORALL bulk_i IN l_tmp_bulk_lpns.lpn_id.first .. l_tmp_bulk_lpns.lpn_id.last
5347: UPDATE mtl_serial_numbers
5348: SET last_update_date = SYSDATE
5349: , last_updated_by = fnd_global.user_id
5350: , lpn_id = NULL
5351: WHERE lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
5352:
5353: IF (l_debug = 1) THEN
5807: , l_lpn.status_id
5808: , l_lpn.sealed_status
5809: , l_operation_mode
5810: , SYSDATE
5811: , fnd_global.user_id
5812: , SYSDATE
5813: , fnd_global.user_id
5814: , p_cost_group_id
5815: , DECODE(l_new.outermost_lpn_id, fnd_api.g_miss_num, NULL, NVL(l_new.outermost_lpn_id, l_lpn.outermost_lpn_id))
5809: , l_operation_mode
5810: , SYSDATE
5811: , fnd_global.user_id
5812: , SYSDATE
5813: , fnd_global.user_id
5814: , p_cost_group_id
5815: , DECODE(l_new.outermost_lpn_id, fnd_api.g_miss_num, NULL, NVL(l_new.outermost_lpn_id, l_lpn.outermost_lpn_id))
5816: , NVL(p_source_type_id, l_lpn.source_type_id)
5817: , NVL(p_source_header_id, l_lpn.source_header_id)
6862: p_pri_qty=> l_primary_quantity,
6863: p_uom=> p_uom,
6864: p_date=> SYSDATE,
6865: p_reason_id=> NULL,
6866: p_user_id=> fnd_global.user_id,
6867: p_frt_code=> NULL,
6868: p_ship_num=> NULL,
6869: p_dist_id=> NULL,
6870: p_way_bill=> NULL,
6898: mdebug('Insert lot entry in MTL_LOT_NUMBER_TEMP lot='|| l_lot_number, G_INFO);
6899: END IF;
6900: x_error_code := inv_trx_util_pub.insert_lot_trx(
6901: p_trx_tmp_id=> l_trx_tmp_id,
6902: p_user_id=> fnd_global.user_id,
6903: p_lot_number=> l_lot_number,
6904: p_trx_qty=> p_quantity,
6905: p_pri_qty=> l_primary_quantity,
6906: p_secondary_qty =>p_sec_quantity, --INVCONV kkillams
6931: END IF;
6932: --Insert serials into MTL_SERIAL_NUMBERS_TEMP
6933: x_error_code := inv_trx_util_pub.insert_ser_trx(
6934: p_trx_tmp_id=> l_trx_tmp_id,
6935: p_user_id=> fnd_global.user_id,
6936: p_fm_ser_num=> l_serial_number_from,
6937: p_to_ser_num=> l_serial_number_to,
6938: x_proc_msg=> x_proc_msg
6939: );
7060: -- Bug5659809: update last_update_date and last_update_by as well
7061: UPDATE wms_license_plate_numbers
7062: SET lpn_context = l_lpn_source
7063: , last_update_date = SYSDATE
7064: , last_updated_by = fnd_global.user_id
7065: WHERE lpn_id = l_lpn_to_pack;
7066: ELSE
7067: FETCH container_lpn INTO l_lpn_to_pack;
7068: EXIT WHEN container_lpn%NOTFOUND;
7142: p_pri_qty=> l_primary_quantity,
7143: p_uom=> l_primary_uom,
7144: p_date=> SYSDATE,
7145: p_reason_id=> NULL,
7146: p_user_id=> fnd_global.user_id,
7147: p_frt_code=> NULL,
7148: p_ship_num=> NULL,
7149: p_dist_id=> NULL,
7150: p_way_bill=> NULL,
7178: mdebug('Insert lot entry in MTL_LOT_NUMBER_TEMP lot='|| l_lot_number, G_INFO);
7179: END IF;
7180: x_error_code := inv_trx_util_pub.insert_lot_trx(
7181: p_trx_tmp_id=> l_trx_tmp_id,
7182: p_user_id=> fnd_global.user_id,
7183: p_lot_number=> l_lot_number,
7184: p_trx_qty=> l_primary_quantity,
7185: p_pri_qty=> l_primary_quantity,
7186: p_secondary_qty => l_sec_pack_quantity, --INVCONV kkillams
7209: mdebug('with l_trx_tmp_id='|| l_trx_tmp_id, G_INFO);
7210: END IF;
7211: x_error_code := inv_trx_util_pub.insert_ser_trx(
7212: p_trx_tmp_id=> l_trx_tmp_id,
7213: p_user_id=> fnd_global.user_id,
7214: p_fm_ser_num=> l_serial_number_from,
7215: p_to_ser_num=> l_serial_number_to,
7216: x_proc_msg=> x_proc_msg
7217: );
7515: -- if serial status is not resides in store (3), update lot and rev
7516: UPDATE mtl_serial_numbers
7517: SET lpn_id = p_lpn_id,
7518: last_update_date = SYSDATE,
7519: last_updated_by = fnd_global.user_id,
7520: last_txn_source_type_id = p_source_type_id,
7521: revision = DECODE(current_status, 3, revision, p_revision),
7522: lot_number = DECODE(current_status, 3, lot_number, p_lot_number)
7523: WHERE inventory_item_id = p_content_item_id
7540: END LOOP;
7541:
7542: UPDATE wms_lpn_contents
7543: SET last_update_date = SYSDATE,
7544: last_updated_by = fnd_global.user_id,
7545: serial_summary_entry = 1,
7546: source_type_id = p_source_type_id
7547: WHERE parent_lpn_id = p_lpn_id
7548: AND organization_id = p_organization_id
7551: AND NVL(lot_number, G_NULL_CHAR) = NVL(p_lot_number, G_NULL_CHAR);
7552:
7553: UPDATE wms_license_plate_numbers
7554: SET last_update_date = SYSDATE,
7555: last_updated_by = fnd_global.user_id,
7556: lpn_context = LPN_CONTEXT_WIP
7557: WHERE lpn_id = p_lpn_id
7558: AND organization_id = p_organization_id;
7559: END IF;
8235: , p_trx_qty => 1
8236: , p_pri_qty => 1
8237: , p_uom => 'Ea'
8238: , p_date => SYSDATE
8239: , p_user_id => fnd_global.user_id
8240: , p_from_lpn_id => Nested_LPN_rec.parent_lpn_id
8241: , p_cnt_lpn_id => Nested_LPN_rec.lpn_id
8242: , x_trx_tmp_id => l_trx_tmp_id
8243: , x_proc_msg => x_msg_data );
8320: END IF;
8321: -- Insert record into MTL_TRANSACTIONS_LOTS_TEMP
8322: l_return_status := INV_TRX_UTIL_PUB.Insert_Lot_Trx(
8323: p_trx_tmp_id => l_trx_tmp_id
8324: , p_user_id => fnd_global.user_id
8325: , p_lot_number => l_crnt_item_rec.lot_number
8326: , p_trx_qty => l_crnt_item_rec.lot_quantity
8327: , p_pri_qty => l_converted_qty
8328: , p_secondary_qty => l_crnt_item_rec.secondary_quantity --INVCONV kkillams
8398: END IF;
8399: -- Range finished or last serial processed, insert serial with API and reset from serial
8400: l_return_status := INV_TRX_UTIL_PUB.Insert_Ser_Trx (
8401: p_trx_tmp_id => l_ser_tmp_id
8402: , p_user_id => fnd_global.user_id
8403: , p_fm_ser_num => l_fm_serial
8404: , p_to_ser_num => l_to_serial
8405: , x_proc_msg => x_msg_data );
8406: IF ( l_return_status <> 0 ) THEN
8467: l_trx_hdr_id
8468: , l_trx_tmp_id
8469: , 'Y'
8470: , SYSDATE
8471: , fnd_global.user_id
8472: , SYSDATE
8473: , fnd_global.user_id
8474: , fnd_global.user_id
8475: , SYSDATE
8469: , 'Y'
8470: , SYSDATE
8471: , fnd_global.user_id
8472: , SYSDATE
8473: , fnd_global.user_id
8474: , fnd_global.user_id
8475: , SYSDATE
8476: , p_organization_id
8477: , Nested_LPN_rec.subinventory_code
8470: , SYSDATE
8471: , fnd_global.user_id
8472: , SYSDATE
8473: , fnd_global.user_id
8474: , fnd_global.user_id
8475: , SYSDATE
8476: , p_organization_id
8477: , Nested_LPN_rec.subinventory_code
8478: , Nested_LPN_rec.locator_id
8638: , p_trx_qty => 1
8639: , p_pri_qty => 1
8640: , p_uom => 'Ea'
8641: , p_date => SYSDATE
8642: , p_user_id => fnd_global.user_id
8643: , p_from_lpn_id => lpn_rec.parent_lpn_id
8644: , p_cnt_lpn_id => lpn_rec.lpn_id
8645: , x_trx_tmp_id => l_trx_tmp_id
8646: , x_proc_msg => x_msg_data );
8774: , content_volume_uom_code = NULL
8775: , gross_weight = tare_weight
8776: , gross_weight_uom_code = tare_weight_uom_code
8777: , last_update_date = SYSDATE
8778: , last_updated_by = fnd_global.user_id
8779: WHERE rowid = lpn_rec.rowid;
8780: END LOOP;
8781:
8782: -- Standard check of p_commit.