[Home] [Help]
1895: , msi.serial_number_control_code
1896: , msi.lot_control_code
1897: , msi.revision_qty_control_code
1898: FROM wms_license_plate_numbers wlpn
1899: , wms_lpn_contents wlc
1900: , mtl_system_items msi
1901: WHERE wlpn.organization_id = p_old_org_id
1902: AND wlpn.outermost_lpn_id = p_outermost_lpn_id
1903: AND wlc.parent_lpn_id = wlpn.lpn_id
3299: 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||
3300: ' sctl='||new_org.serial_number_control_code||' lctl='||new_org.lot_control_code||' rctl='||new_org.revision_qty_control_code, G_INFO);
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)
4695: , wlc.uom_code
4696: , wlc.cost_group_id
4697: , wlc.secondary_quantity --INVCONV kkillams
4698: , wlc.secondary_uom_code --INVCONV kkillams
4699: FROM wms_lpn_contents wlc
4700: WHERE wlc.parent_lpn_id = p_lpn_id
4701: AND wlc.organization_id = p_organization_id
4702: AND wlc.uom_code = p_uom
4703: AND wlc.inventory_item_id = p_content_item_id
4720: , wlc.lot_number
4721: , wlc.serial_summary_entry
4722: , wlc.secondary_quantity --INVCONV kkillams
4723: , wlc.secondary_uom_code --INVCONV kkillams
4724: FROM wms_lpn_contents wlc
4725: WHERE wlc.parent_lpn_id = p_lpn_id
4726: AND wlc.organization_id = p_organization_id
4727: AND wlc.inventory_item_id = p_content_item_id
4728: AND wlc.uom_code = NVL(p_uom_code, wlc.uom_code)
4745: SELECT rowid
4746: , primary_quantity
4747: , quantity
4748: , secondary_quantity --INVCONV kkillams
4749: FROM wms_lpn_contents
4750: WHERE parent_lpn_id = p_lpn_id
4751: AND organization_id = p_organization_id
4752: AND item_description = p_content_item_desc
4753: AND NVL(cost_group_id, G_NULL_NUM) = NVL(p_cost_group_id, G_NULL_NUM)
5115:
5116: -- Update the location information for the packed items
5117: IF ( l_lpn.organization_id <> l_content_lpn.organization_id) THEN
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
5268:
5269: l_progress := 'Updating existing item row in WLC for pack';
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
5280:
5281: -- To calculate wt and volume changes need the difference in 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
5301: -- Quantity columns should be rounded to 5 decimals to avoid rounding errors
5302: l_quantity := round(l_quantity,g_precision);
5303: l_primary_quantity := round(l_primary_quantity,g_precision);
5304:
5305: INSERT INTO wms_lpn_contents (
5306: last_update_date
5307: , last_updated_by
5308: , creation_date
5309: , created_by
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
5339: , p_content_item_id
5340: , p_content_item_desc
5378: OPEN one_time_item_cursor;
5379: FETCH one_time_item_cursor INTO l_one_time_item_rec;
5380:
5381: IF one_time_item_cursor%NOTFOUND THEN
5382: INSERT INTO wms_lpn_contents (
5383: last_update_date
5384: , last_updated_by
5385: , creation_date
5386: , created_by
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
5418: , p_content_item_id
5419: , p_content_item_desc
5433: , p_sec_quantity --INVCONV kkillams
5434: , p_sec_uom --INVCONV kkillams
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)
5477: l_content_lpn.subinventory_code <> l_subinventory OR
5478: l_content_lpn.locator_id <> l_locator_id ) THEN
5479: -- Update the location information for the packed items
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
5510: BEGIN
5511: SELECT 0 INTO l_lpn_is_empty
5512: FROM dual
5513: WHERE EXISTS (
5514: SELECT 1 FROM wms_lpn_contents
5515: WHERE organization_id = p_organization_id
5516: AND parent_lpn_id = l_lpn.lpn_id )
5517: OR EXISTS (
5518: SELECT 1 FROM wms_license_plate_numbers
5651:
5652: IF ( round(l_item_quantity, g_precision) < round(l_temp_record.primary_quantity, g_precision) ) THEN
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
5690: mdebug('l_converted_quantity ='||l_converted_quantity, G_INFO);
5691: END IF;
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
5706:
5707: EXIT;
5708: ELSIF ( round(l_item_quantity, g_precision) >= round(l_temp_record.primary_quantity, g_precision) ) THEN
5709: mdebug('Delete column from content table and decrement total unpack quantity', G_INFO);
5710: DELETE FROM wms_lpn_contents
5711: WHERE rowid = l_temp_record.rowid;
5712:
5713: l_item_quantity := l_item_quantity - l_temp_record.primary_quantity;
5714: l_item_quantity := round(l_item_quantity,g_precision);-- round the quantity to 5 decimal places to avoid errors, bug 9695128
5745: BEGIN
5746: SELECT 0 INTO l_lpn_is_empty
5747: FROM dual
5748: WHERE EXISTS (
5749: SELECT 1 FROM wms_lpn_contents
5750: WHERE organization_id = p_organization_id
5751: AND parent_lpn_id = l_lpn.lpn_id )
5752: OR EXISTS (
5753: SELECT 1 FROM wms_license_plate_numbers
5774: FETCH one_time_item_cursor INTO l_one_time_item_rec;
5775:
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)
5782: , quantity = (l_one_time_item_rec.quantity - l_quantity)
5783: , uom_code = p_uom
5784: WHERE rowid = l_one_time_item_rec.rowid;
5785: ELSIF ( l_quantity = l_one_time_item_rec.quantity ) THEN
5786: DELETE FROM wms_lpn_contents
5787: WHERE rowid = l_one_time_item_rec.rowid;
5788: ELSE
5789: IF (l_debug = 1) THEN
5790: mdebug('Not enough of this onetime item to unpack', G_ERROR);
5855: END IF;
5856:
5857: -- Remove the records for the packed items
5858: FORALL bulk_i IN l_tmp_bulk_lpns.lpn_id.first .. l_tmp_bulk_lpns.lpn_id.last
5859: DELETE FROM wms_lpn_contents
5860: WHERE parent_lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
5861:
5862: IF (l_debug = 1) THEN
5863: mdebug('Bulk delete from WLC cnt='||SQL%ROWCOUNT, G_INFO);
5939: SELECT 0 INTO l_lpn_is_empty
5940: FROM dual
5941: WHERE EXISTS (
5942: -- Check to make sure that the parent lpn has no items in it
5943: SELECT 1 FROM wms_lpn_contents
5944: WHERE organization_id = p_organization_id
5945: AND parent_lpn_id = empty_lpn_rec.parent_lpn_id )
5946: OR EXISTS (
5947: -- Check to make sure that the parent lpn has no lpns in it
8098: -- Bug 2375043
8099: --l_current_serial := RPAD(l_prefix, l_padded_length, '0') || l_current_number;
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
8193: organization_id, revision, lot_number,
8194: serial_number, quantity, uom_code, cost_group_id,
8195: secondary_quantity, secondary_uom_code --INVCONV kkillams
8196: , primary_quantity --16445970
8197: FROM WMS_LPN_CONTENTS
8198: WHERE parent_lpn_id = l_current_lpn
8199: AND NVL(serial_summary_entry, 2) = 2;
8200:
8201: CURSOR lpn_serial_contents_cursor IS
8747: CURSOR LPN_item_cur (p_parent_lpn_id NUMBER) IS
8748: SELECT inventory_item_id, quantity, uom_code, revision, lot_number, quantity lot_quantity,
8749: cost_group_id, serial_summary_entry
8750: ,secondary_quantity, secondary_uom_code --INVCONV kkillams
8751: FROM wms_lpn_contents
8752: WHERE organization_id = p_organization_id
8753: AND parent_lpn_id = p_parent_lpn_id
8754: ORDER BY inventory_item_id, revision, cost_group_id, lot_number;
8755:
9332: mdebug('Initializing LPN with lpnid='||lpn_rec.lpn_id, G_MESSAGE);
9333: END IF;
9334:
9335: -- Remove all contents from WLC
9336: DELETE FROM wms_lpn_contents
9337: WHERE parent_lpn_id = lpn_rec.lpn_id;
9338:
9339: -- Reset lpn properties to pregenerated
9340: UPDATE wms_license_plate_numbers
9506: AND rownum = 1;
9507: ELSE
9508: SELECT msikfv.concatenated_segments
9509: INTO l_concatenated_segments
9510: FROM WMS_LPN_CONTENTS wlc,
9511: mtl_system_items_b_kfv msikfv
9512: WHERE wlc.parent_lpn_id IN
9513: (SELECT lpn_id
9514: FROM wms_license_plate_numbers