[Home] [Help]
21: END trace;
22:
23:
24: /********************************************
25: Procedure to insert into WMS_PACKING_MATERIAL_GTEMP
26: *******************************************/
27: PROCEDURE insert_material_rec(
28: p_material_rec IN WMS_PACKING_MATERIAL_GTEMP%ROWTYPE) IS
29:
24: /********************************************
25: Procedure to insert into WMS_PACKING_MATERIAL_GTEMP
26: *******************************************/
27: PROCEDURE insert_material_rec(
28: p_material_rec IN WMS_PACKING_MATERIAL_GTEMP%ROWTYPE) IS
29:
30: BEGIN
31: INSERT INTO WMS_PACKING_MATERIAL_GTEMP(
32: MOVE_ORDER_HEADER_ID
27: PROCEDURE insert_material_rec(
28: p_material_rec IN WMS_PACKING_MATERIAL_GTEMP%ROWTYPE) IS
29:
30: BEGIN
31: INSERT INTO WMS_PACKING_MATERIAL_GTEMP(
32: MOVE_ORDER_HEADER_ID
33: , MOVE_ORDER_LINE_ID
34: , REFERENCE
35: , REFERENCE_ID
174: END IF;
175: END insert_material_rec;
176:
177: PROCEDURE check_against_rcv
178: ( p_mol_rec IN WMS_PACKING_MATERIAL_GTEMP%ROWTYPE
179: , p_document_type IN VARCHAR2 DEFAULT NULL
180: , p_document_id IN NUMBER DEFAULT NULL
181: , p_document_line_id IN NUMBER DEFAULT NULL
182: , p_receipt_num IN VARCHAR2 DEFAULT NULL
738:
739:
740: CURSOR get_proj_task_rec IS
741: select distinct inventory_item_id
742: from wms_packing_material_gtemp
743: where lpn_id is null --for loose item
744: and project_id is not NULL; --taks id can be loose
745:
746: l_get_proj_task_rec get_proj_task_rec%ROWTYPE;
836: l_receipt_num VARCHAR2(30);
837: l_doc_line_id NUMBER;
838: l_doc_line_num VARCHAR2(10);--CLM Changes, Line number to be alphanumeric
839:
840: l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
841: l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
842: l_rec_count NUMBER;
843: l_exists NUMBER;
844:
837: l_doc_line_id NUMBER;
838: l_doc_line_num VARCHAR2(10);--CLM Changes, Line number to be alphanumeric
839:
840: l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
841: l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
842: l_rec_count NUMBER;
843: l_exists NUMBER;
844:
845: l_shipment_header_id NUMBER;
1159: IF l_debug = 1 THEN
1160: trace('Executed query');
1161: END IF;
1162:
1163: delete from wms_packing_material_gtemp;
1164: --delete from wms_packing_material_temp;
1165:
1166: l_rec_count := 0;
1167: LOOP
1775: END IF;
1776:
1777: select count(1) INTO l_item_cnt
1778: FROM (select distinct project_id, task_id
1779: from wms_packing_material_gtemp
1780: WHERE lpn_id is NULL --loose items only
1781: and inventory_item_id = l_get_proj_task_rec.inventory_item_id
1782: AND project_id IS NOT NULL) wpmg;
1783:
1801: END IF;--p_is_pjm_enabled_org
1802:
1803: /*
1804: if fnd_global.user_id = 1005653 THEN --SATKUMAR
1805: insert into wms_packing_material_temp value (select * from wms_packing_material_gtemp);
1806: commit;
1807: end if;
1808: */
1809:
2139: ,wdd1.top_model_line_id;
2140:
2141: l_progress VARCHAR2(10);
2142:
2143: l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2144: l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2145: l_del_count NUMBER;
2146: l_rec_count NUMBER;
2147:
2140:
2141: l_progress VARCHAR2(10);
2142:
2143: l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2144: l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2145: l_del_count NUMBER;
2146: l_rec_count NUMBER;
2147:
2148: l_delivery_id NUMBER;
2175: x_return_status := fnd_api.G_RET_STS_SUCCESS;
2176: l_del_count := 0;
2177: l_rec_count := 0;
2178:
2179: delete from wms_packing_material_gtemp;
2180: --delete from wms_packing_material_temp;
2181:
2182: l_progress := '010';
2183: IF (p_delivery_id IS NOT NULL) OR (p_trip_id IS NOT NULL) OR (p_delivery_state IS NOT NULL) THEN
2594: IF l_delivery_cur_exp%ISOPEN THEN
2595: CLOSE l_delivery_cur_exp;
2596: END IF;
2597:
2598: --insert into wms_packing_material_temp value (select * from wms_packing_material_gtemp);
2599: --commit;
2600:
2601:
2602: EXCEPTION
2644: and (((oel1.shippable_flag = 'Y') AND (oel1.line_id <> oel1.TOP_MODEL_LINE_ID) and (oel1.ato_line_id is null))
2645: OR (oel2.ato_line_id is not null and oel2.line_id = oel2.top_model_line_id))
2646: )
2647: AND exists (
2648: select 1 from WMS_PACKING_MATERIAL_GTEMP wpmg, oe_order_lines_all oel1
2649: where WPMG.order_header_id = oel1.header_id
2650: AND wpmg.order_line_id = oel1.line_id
2651: AND wpmg.inventory_item_id = p_item_id
2652: AND wpmg.inventory_item_id = oel1.inventory_item_id
2922: Procedure to query the eligible material for pack/split/unpack transactions
2923: For inbound, it queries move order lines
2924: For outbound, it queries delivery detail lines
2925: After it finds results, it populates global temp table
2926: WMS_PACKING_MATERIAL_GTEMP to display on the spreadtable on packing workbench form
2927:
2928: Input Parameter:
2929: p_source_id: 1=>Inbound, 2=>Outbound
2930:
3121: , mol.secondary_uom_code --INVCONV kkillams
3122: , wpmg.grade_code --INVCONV kkillams
3123: , mol.lot_number
3124: , mol.inspection_status
3125: FROM wms_packing_material_gtemp wpmg, mtl_txn_request_lines mol
3126: WHERE wpmg.move_order_line_id = mol.line_id
3127: AND wpmg.organization_id = p_organization_id
3128: AND nvl(wpmg.subinventory,'#$%') = nvl(p_subinventory_code, nvl(wpmg.subinventory,'#$%'))
3129: AND nvl(wpmg.locator_id, -9999) = nvl(p_locator_id, nvl(wpmg.locator_id, -9999))
3157: , mol.secondary_uom_code --INVCONV kkillams
3158: , wpmg.grade_code --INVCONV kkillams
3159: , mol.lot_number
3160: , mol.inspection_status
3161: FROM wms_packing_material_gtemp wpmg, mtl_txn_request_lines mol
3162: WHERE wpmg.move_order_line_id = mol.line_id
3163: AND wpmg.organization_id = p_organization_id
3164: AND nvl(wpmg.subinventory,'#$%') = nvl(p_subinventory_code, nvl(wpmg.subinventory,'#$%'))
3165: AND nvl(wpmg.locator_id, -9999) = nvl(p_locator_id, nvl(wpmg.locator_id, -9999))
3199: , mol.secondary_uom_code --INVCONV kkillams
3200: , wpmg.grade_code --INVCONV kkillams
3201: , mol.lot_number
3202: , mol.inspection_status
3203: FROM wms_packing_material_gtemp wpmg, mtl_txn_request_lines mol
3204: WHERE wpmg.move_order_line_id = mol.line_id
3205: AND wpmg.organization_id = p_organization_id
3206: AND nvl(wpmg.subinventory,'#$%') = nvl(p_subinventory_code, nvl(wpmg.subinventory,'#$%'))
3207: AND nvl(wpmg.locator_id, -9999) = nvl(p_locator_id, nvl(wpmg.locator_id, -9999))
3236: --order by decode(wpmg.uom, p_transaction_uom, 0, 1) asc, mol.creation_date asc;
3237:
3238: --Bug 6028098
3239: CURSOR get_gtemp IS
3240: SELECT * FROM wms_packing_material_gtemp
3241: WHERE inventory_item_id = p_inventory_item_id
3242: AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
3243: AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
3244: AND subinventory = p_subinventory_code
3499: x_transaction_temp_id := l_txn_tmp_id;
3500: x_serial_transaction_temp_id := null;
3501:
3502: -- Delete record from eligible material temp table
3503: delete from wms_packing_material_gtemp where outermost_lpn_id = p_content_lpn_id;
3504: --commit;
3505: END IF;
3506:
3507: ELSIF p_inventory_item_id IS NOT NULL THEN
4290: x_transaction_temp_id := p_transaction_temp_id;
4291:
4292: END IF; -- End Create new MMTT or update MMTT
4293:
4294: -- Update WMS_PACKING_MATERIAL_GTEMP
4295: IF l_debug = 1 THEN
4296: trace('Updating wpmg to decrease qty '||inv_convert.inv_um_convert(
4297: p_inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,'Ea',null,null));
4298: END IF;
4296: trace('Updating wpmg to decrease qty '||inv_convert.inv_um_convert(
4297: p_inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,'Ea',null,null));
4298: END IF;
4299: BEGIN
4300: UPDATE wms_packing_material_gtemp
4301: SET selected_flag = 'Y',
4302: quantity = quantity - inv_convert.inv_um_convert(
4303: inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,uom,null,null),
4304: secondary_quantity = CASE WHEN l_mol_list(i).secondary_transaction_quantity IS NOT NULL
4308: WHERE move_order_line_id = l_mol_list(i).move_order_line_id;
4309: EXCEPTION
4310: WHEN others THEN
4311: IF l_debug = 1 THEN
4312: trace('Error when updating wms_packing_material_gtemp for mol:'||l_mol_list(i).move_order_line_id);
4313: END IF;
4314: raise fnd_api.g_exc_error;
4315: END;
4316:
4464: IF l_debug = 1 THEN
4465: trace('MMTT inserted, hdr_id='||l_txn_hdr_id|| ',tmp_id='||l_txn_tmp_id);
4466: END IF;
4467:
4468: -- Update wms_packing_material_gtemp
4469: BEGIN
4470: IF p_from_lpn_id IS NOT NULL THEN
4471: -- Split, Unpack
4472: UPDATE wms_packing_material_gtemp
4468: -- Update wms_packing_material_gtemp
4469: BEGIN
4470: IF p_from_lpn_id IS NOT NULL THEN
4471: -- Split, Unpack
4472: UPDATE wms_packing_material_gtemp
4473: SET selected_flag = 'D' -- Deleted
4474: WHERE (lpn_id = p_content_lpn_id AND parent_lpn_id = p_from_lpn_id) OR (parent_lpn_id = p_content_lpn_id AND parent_lpn_id <> outermost_lpn_id);
4475: ELSE
4476: -- Pack
4473: SET selected_flag = 'D' -- Deleted
4474: WHERE (lpn_id = p_content_lpn_id AND parent_lpn_id = p_from_lpn_id) OR (parent_lpn_id = p_content_lpn_id AND parent_lpn_id <> outermost_lpn_id);
4475: ELSE
4476: -- Pack
4477: UPDATE wms_packing_material_gtemp
4478: SET selected_flag = 'D'
4479: WHERE outermost_lpn_id = p_content_lpn_id;
4480: END IF;
4481: EXCEPTION
4480: END IF;
4481: EXCEPTION
4482: WHEN others THEN
4483: IF l_debug = 1 THEN
4484: trace('Error when deleting from wms_packing_material_gtemp for content lpn '||p_content_lpn_id);
4485: END IF;
4486: x_proc_msg := l_proc_msg;
4487: raise fnd_api.g_exc_error;
4488: END;
4635: --Start Bug 6028098
4636: BEGIN
4637: SELECT sum(quantity)
4638: INTO l_sum_qty
4639: FROM wms_packing_material_gtemp
4640: WHERE inventory_item_id = p_inventory_item_id
4641: AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4642: AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
4643: AND subinventory = p_subinventory_code
4657:
4658: -- If the summed qty = process_qty, then no loop needs to be done.
4659: -- Simply perform an update for all records for that part and set the qty to 0.
4660: IF l_process_qty = l_sum_qty THEN
4661: UPDATE wms_packing_material_gtemp
4662: SET selected_flag = 'Y'
4663: ,quantity = 0
4664: WHERE inventory_item_id = p_inventory_item_id
4665: AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4694: IF l_debug = 1 THEN
4695: trace('l_update_qty ' || l_update_qty);
4696: END IF;
4697:
4698: UPDATE wms_packing_material_gtemp
4699: SET selected_flag = 'Y'
4700: ,quantity = quantity - l_update_qty
4701: WHERE inventory_item_id = p_inventory_item_id
4702: AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4713: --End Bug 6028098
4714:
4715: --Start Commented out Bug 6028098
4716: /*
4717: -- Update wms_packing_material_gtemp
4718: BEGIN
4719: UPDATE wms_packing_material_gtemp
4720: SET selected_flag = 'Y'
4721: ,quantity = quantity - inv_convert.inv_um_convert(
4715: --Start Commented out Bug 6028098
4716: /*
4717: -- Update wms_packing_material_gtemp
4718: BEGIN
4719: UPDATE wms_packing_material_gtemp
4720: SET selected_flag = 'Y'
4721: ,quantity = quantity - inv_convert.inv_um_convert(
4722: inventory_item_id,null,p_transaction_qty,p_transaction_uom,uom,null,null)
4723: WHERE inventory_item_id = p_inventory_item_id
4729: AND rownum<2;
4730: l_row_count := SQL%ROWCOUNT;
4731: IF l_row_count > 1 THEN
4732: IF l_debug = 1 THEN
4733: trace('Error when updating wms_packing_material_gtemp for item, only one record should be updated');
4734: END IF;
4735: fnd_message.set_name('INV','INV_FAILED');
4736: fnd_msg_pub.add;
4737: raise fnd_api.g_exc_error;
4736: fnd_msg_pub.add;
4737: raise fnd_api.g_exc_error;
4738: ELSE
4739: IF l_debug = 1 THEN
4740: trace('wms_packing_material_gtemp updated, row_count='||l_row_count);
4741: END IF;
4742: --END IF;
4743: EXCEPTION
4744: WHEN others THEN
4742: --END IF;
4743: EXCEPTION
4744: WHEN others THEN
4745: IF l_debug = 1 THEN
4746: trace('Error when updating wms_packing_material_gtemp for item '||p_inventory_item_id);
4747: END IF;
4748: fnd_message.set_name('INV','INV_FAILED');
4749: fnd_msg_pub.add;
4750: raise fnd_api.g_exc_error;