[Home] [Help]
1702: , msi.serial_number_control_code
1703: , msi.lot_control_code
1704: , msi.revision_qty_control_code
1705: FROM wms_license_plate_numbers wlpn
1706: , wms_lpn_contents wlc
1707: , mtl_system_items msi
1708: WHERE wlpn.organization_id = p_old_org_id
1709: AND wlpn.outermost_lpn_id = p_outermost_lpn_id
1710: AND wlc.parent_lpn_id = wlpn.lpn_id
2999: mdebug('Got WLC for orgxfer itm='||new_org.inventory_item_id||' oldpuom='||inv_cache.item_rec.primary_uom_code||' newpuom='||new_org.primary_uom_code||
3000: ' sctl='||new_org.serial_number_control_code||' lctl='||new_org.lot_control_code||' rctl='||new_org.revision_qty_control_code, G_INFO);
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)
4253: , wlc.uom_code
4254: , wlc.cost_group_id
4255: , wlc.secondary_quantity --INVCONV kkillams
4256: , wlc.secondary_uom_code --INVCONV kkillams
4257: FROM wms_lpn_contents wlc
4258: WHERE wlc.parent_lpn_id = p_lpn_id
4259: AND wlc.organization_id = p_organization_id
4260: AND wlc.uom_code = p_uom
4261: AND wlc.inventory_item_id = p_content_item_id
4278: , wlc.lot_number
4279: , wlc.serial_summary_entry
4280: , wlc.secondary_quantity --INVCONV kkillams
4281: , wlc.secondary_uom_code --INVCONV kkillams
4282: FROM wms_lpn_contents wlc
4283: WHERE wlc.parent_lpn_id = p_lpn_id
4284: AND wlc.organization_id = p_organization_id
4285: AND wlc.inventory_item_id = p_content_item_id
4286: AND wlc.uom_code = NVL(p_uom_code, wlc.uom_code)
4303: SELECT rowid
4304: , primary_quantity
4305: , quantity
4306: , secondary_quantity --INVCONV kkillams
4307: FROM wms_lpn_contents
4308: WHERE parent_lpn_id = p_lpn_id
4309: AND organization_id = p_organization_id
4310: AND item_description = p_content_item_desc
4311: AND NVL(cost_group_id, G_NULL_NUM) = NVL(p_cost_group_id, G_NULL_NUM)
4647:
4648: -- Update the location information for the packed items
4649: IF ( l_lpn.organization_id <> l_content_lpn.organization_id) THEN
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
4800:
4801: l_progress := 'Updating existing item row in WLC for pack';
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
4812:
4813: -- To calculate wt and volume changes need the difference in 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
4828: IF (l_debug = 1) THEN
4829: mdebug('Inserting new item row into WLC');
4830: END IF;
4831:
4832: INSERT INTO wms_lpn_contents (
4833: last_update_date
4834: , last_updated_by
4835: , creation_date
4836: , created_by
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
4866: , p_content_item_id
4867: , p_content_item_desc
4905: OPEN one_time_item_cursor;
4906: FETCH one_time_item_cursor INTO l_one_time_item_rec;
4907:
4908: IF one_time_item_cursor%NOTFOUND THEN
4909: INSERT INTO wms_lpn_contents (
4910: last_update_date
4911: , last_updated_by
4912: , creation_date
4913: , created_by
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
4945: , p_content_item_id
4946: , p_content_item_desc
4960: , p_sec_quantity --INVCONV kkillams
4961: , p_sec_uom --INVCONV kkillams
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)
5004: l_content_lpn.subinventory_code <> l_subinventory OR
5005: l_content_lpn.locator_id <> l_locator_id ) THEN
5006: -- Update the location information for the packed items
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
5037: BEGIN
5038: SELECT 0 INTO l_lpn_is_empty
5039: FROM dual
5040: WHERE EXISTS (
5041: SELECT 1 FROM wms_lpn_contents
5042: WHERE organization_id = p_organization_id
5043: AND parent_lpn_id = l_lpn.lpn_id )
5044: OR EXISTS (
5045: SELECT 1 FROM wms_license_plate_numbers
5165: l_converted_quantity := l_item_quantity;
5166: END IF;
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
5187:
5188: EXIT;
5189: ELSIF ( round(l_item_quantity, g_precision) >= round(l_temp_record.primary_quantity, g_precision) ) THEN
5190: mdebug('Delete column from content table and decrement total unpack quantity', G_INFO);
5191: DELETE FROM wms_lpn_contents
5192: WHERE rowid = l_temp_record.rowid;
5193:
5194: l_item_quantity := l_item_quantity - l_temp_record.primary_quantity;
5195: EXIT WHEN l_item_quantity <= 0;
5224: BEGIN
5225: SELECT 0 INTO l_lpn_is_empty
5226: FROM dual
5227: WHERE EXISTS (
5228: SELECT 1 FROM wms_lpn_contents
5229: WHERE organization_id = p_organization_id
5230: AND parent_lpn_id = l_lpn.lpn_id )
5231: OR EXISTS (
5232: SELECT 1 FROM wms_license_plate_numbers
5253: FETCH one_time_item_cursor INTO l_one_time_item_rec;
5254:
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)
5261: , quantity = (l_one_time_item_rec.quantity - l_quantity)
5262: , uom_code = p_uom
5263: WHERE rowid = l_one_time_item_rec.rowid;
5264: ELSIF ( l_quantity = l_one_time_item_rec.quantity ) THEN
5265: DELETE FROM wms_lpn_contents
5266: WHERE rowid = l_one_time_item_rec.rowid;
5267: ELSE
5268: IF (l_debug = 1) THEN
5269: mdebug('Not enough of this onetime item to unpack', G_ERROR);
5334: END IF;
5335:
5336: -- Remove the records for the packed items
5337: FORALL bulk_i IN l_tmp_bulk_lpns.lpn_id.first .. l_tmp_bulk_lpns.lpn_id.last
5338: DELETE FROM wms_lpn_contents
5339: WHERE parent_lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
5340:
5341: IF (l_debug = 1) THEN
5342: mdebug('Bulk delete from WLC cnt='||SQL%ROWCOUNT, G_INFO);
5414: SELECT 0 INTO l_lpn_is_empty
5415: FROM dual
5416: WHERE EXISTS (
5417: -- Check to make sure that the parent lpn has no items in it
5418: SELECT 1 FROM wms_lpn_contents
5419: WHERE organization_id = p_organization_id
5420: AND parent_lpn_id = empty_lpn_rec.parent_lpn_id )
5421: OR EXISTS (
5422: -- Check to make sure that the parent lpn has no lpns in it
7538: -- Bug 2375043
7539: --l_current_serial := RPAD(l_prefix, l_padded_length, '0') || l_current_number;
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
7632: SELECT parent_lpn_id, inventory_item_id, item_description,
7633: organization_id, revision, lot_number,
7634: serial_number, quantity, uom_code, cost_group_id,
7635: secondary_quantity, secondary_uom_code --INVCONV kkillams
7636: FROM WMS_LPN_CONTENTS
7637: WHERE parent_lpn_id = l_current_lpn
7638: AND NVL(serial_summary_entry, 2) = 2;
7639:
7640: CURSOR lpn_serial_contents_cursor IS
8176: CURSOR LPN_item_cur (p_parent_lpn_id NUMBER) IS
8177: SELECT inventory_item_id, quantity, uom_code, revision, lot_number, quantity lot_quantity,
8178: cost_group_id, serial_summary_entry
8179: ,secondary_quantity, secondary_uom_code --INVCONV kkillams
8180: FROM wms_lpn_contents
8181: WHERE organization_id = p_organization_id
8182: AND parent_lpn_id = p_parent_lpn_id
8183: ORDER BY inventory_item_id, revision, cost_group_id, lot_number;
8184:
8759: mdebug('Initializing LPN with lpnid='||lpn_rec.lpn_id, G_MESSAGE);
8760: END IF;
8761:
8762: -- Remove all contents from WLC
8763: DELETE FROM wms_lpn_contents
8764: WHERE parent_lpn_id = lpn_rec.lpn_id;
8765:
8766: -- Reset lpn properties to pregenerated
8767: UPDATE wms_license_plate_numbers