[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
681:
682:
683: CURSOR get_proj_task_rec IS
684: select distinct inventory_item_id
685: from wms_packing_material_gtemp
686: where lpn_id is null --for loose item
687: and project_id is not NULL; --taks id can be loose
688:
689: l_get_proj_task_rec get_proj_task_rec%ROWTYPE;
779: l_receipt_num VARCHAR2(30);
780: l_doc_line_id NUMBER;
781: l_doc_line_num NUMBER;
782:
783: l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
784: l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
785: l_rec_count NUMBER;
786: l_exists NUMBER;
787:
780: l_doc_line_id NUMBER;
781: l_doc_line_num NUMBER;
782:
783: l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
784: l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
785: l_rec_count NUMBER;
786: l_exists NUMBER;
787:
788: l_shipment_header_id NUMBER;
1098: IF l_debug = 1 THEN
1099: trace('Executed query');
1100: END IF;
1101:
1102: delete from wms_packing_material_gtemp;
1103: --delete from wms_packing_material_temp;
1104:
1105: l_rec_count := 0;
1106: LOOP
1704: END IF;
1705:
1706: select count(1) INTO l_item_cnt
1707: FROM (select distinct project_id, task_id
1708: from wms_packing_material_gtemp
1709: WHERE lpn_id is NULL --loose items only
1710: and inventory_item_id = l_get_proj_task_rec.inventory_item_id
1711: AND project_id IS NOT NULL) wpmg;
1712:
1730: END IF;--p_is_pjm_enabled_org
1731:
1732: /*
1733: if fnd_global.user_id = 1005653 THEN --SATKUMAR
1734: insert into wms_packing_material_temp value (select * from wms_packing_material_gtemp);
1735: commit;
1736: end if;
1737: */
1738:
2066: ,wdd1.top_model_line_id;
2067:
2068: l_progress VARCHAR2(10);
2069:
2070: l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2071: l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2072: l_del_count NUMBER;
2073: l_rec_count NUMBER;
2074:
2067:
2068: l_progress VARCHAR2(10);
2069:
2070: l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2071: l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2072: l_del_count NUMBER;
2073: l_rec_count NUMBER;
2074:
2075: l_delivery_id NUMBER;
2102: x_return_status := fnd_api.G_RET_STS_SUCCESS;
2103: l_del_count := 0;
2104: l_rec_count := 0;
2105:
2106: delete from wms_packing_material_gtemp;
2107: --delete from wms_packing_material_temp;
2108:
2109: l_progress := '010';
2110: IF (p_delivery_id IS NOT NULL) OR (p_trip_id IS NOT NULL) OR (p_delivery_state IS NOT NULL) THEN
2521: IF l_delivery_cur_exp%ISOPEN THEN
2522: CLOSE l_delivery_cur_exp;
2523: END IF;
2524:
2525: --insert into wms_packing_material_temp value (select * from wms_packing_material_gtemp);
2526: --commit;
2527:
2528:
2529: EXCEPTION
2571: and (((oel1.shippable_flag = 'Y') AND (oel1.line_id <> oel1.TOP_MODEL_LINE_ID) and (oel1.ato_line_id is null))
2572: OR (oel2.ato_line_id is not null and oel2.line_id = oel2.top_model_line_id))
2573: )
2574: AND exists (
2575: select 1 from WMS_PACKING_MATERIAL_GTEMP wpmg, oe_order_lines_all oel1
2576: where WPMG.order_header_id = oel1.header_id
2577: AND wpmg.order_line_id = oel1.line_id
2578: AND wpmg.inventory_item_id = p_item_id
2579: AND wpmg.inventory_item_id = oel1.inventory_item_id
2849: Procedure to query the eligible material for pack/split/unpack transactions
2850: For inbound, it queries move order lines
2851: For outbound, it queries delivery detail lines
2852: After it finds results, it populates global temp table
2853: WMS_PACKING_MATERIAL_GTEMP to display on the spreadtable on packing workbench form
2854:
2855: Input Parameter:
2856: p_source_id: 1=>Inbound, 2=>Outbound
2857:
3048: , mol.secondary_uom_code --INVCONV kkillams
3049: , wpmg.grade_code --INVCONV kkillams
3050: , mol.lot_number
3051: , mol.inspection_status
3052: FROM wms_packing_material_gtemp wpmg, mtl_txn_request_lines mol
3053: WHERE wpmg.move_order_line_id = mol.line_id
3054: AND wpmg.organization_id = p_organization_id
3055: AND nvl(wpmg.subinventory,'#$%') = nvl(p_subinventory_code, nvl(wpmg.subinventory,'#$%'))
3056: AND nvl(wpmg.locator_id, -9999) = nvl(p_locator_id, nvl(wpmg.locator_id, -9999))
3070: order by decode(wpmg.uom, p_transaction_uom, 0, 1) asc, mol.creation_date asc;
3071:
3072: --Bug 6028098
3073: CURSOR get_gtemp IS
3074: SELECT * FROM wms_packing_material_gtemp
3075: WHERE inventory_item_id = p_inventory_item_id
3076: AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
3077: AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
3078: AND subinventory = p_subinventory_code
3333: x_transaction_temp_id := l_txn_tmp_id;
3334: x_serial_transaction_temp_id := null;
3335:
3336: -- Delete record from eligible material temp table
3337: delete from wms_packing_material_gtemp where outermost_lpn_id = p_content_lpn_id;
3338: --commit;
3339: END IF;
3340:
3341: ELSIF p_inventory_item_id IS NOT NULL THEN
4124: x_transaction_temp_id := p_transaction_temp_id;
4125:
4126: END IF; -- End Create new MMTT or update MMTT
4127:
4128: -- Update WMS_PACKING_MATERIAL_GTEMP
4129: IF l_debug = 1 THEN
4130: trace('Updating wpmg to decrease qty '||inv_convert.inv_um_convert(
4131: p_inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,'Ea',null,null));
4132: END IF;
4130: trace('Updating wpmg to decrease qty '||inv_convert.inv_um_convert(
4131: p_inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,'Ea',null,null));
4132: END IF;
4133: BEGIN
4134: UPDATE wms_packing_material_gtemp
4135: SET selected_flag = 'Y',
4136: quantity = quantity - inv_convert.inv_um_convert(
4137: inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,uom,null,null),
4138: secondary_quantity = CASE WHEN l_mol_list(i).secondary_transaction_quantity IS NOT NULL
4142: WHERE move_order_line_id = l_mol_list(i).move_order_line_id;
4143: EXCEPTION
4144: WHEN others THEN
4145: IF l_debug = 1 THEN
4146: trace('Error when updating wms_packing_material_gtemp for mol:'||l_mol_list(i).move_order_line_id);
4147: END IF;
4148: raise fnd_api.g_exc_error;
4149: END;
4150:
4298: IF l_debug = 1 THEN
4299: trace('MMTT inserted, hdr_id='||l_txn_hdr_id|| ',tmp_id='||l_txn_tmp_id);
4300: END IF;
4301:
4302: -- Update wms_packing_material_gtemp
4303: BEGIN
4304: IF p_from_lpn_id IS NOT NULL THEN
4305: -- Split, Unpack
4306: UPDATE wms_packing_material_gtemp
4302: -- Update wms_packing_material_gtemp
4303: BEGIN
4304: IF p_from_lpn_id IS NOT NULL THEN
4305: -- Split, Unpack
4306: UPDATE wms_packing_material_gtemp
4307: SET selected_flag = 'D' -- Deleted
4308: 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);
4309: ELSE
4310: -- Pack
4307: SET selected_flag = 'D' -- Deleted
4308: 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);
4309: ELSE
4310: -- Pack
4311: UPDATE wms_packing_material_gtemp
4312: SET selected_flag = 'D'
4313: WHERE outermost_lpn_id = p_content_lpn_id;
4314: END IF;
4315: EXCEPTION
4314: END IF;
4315: EXCEPTION
4316: WHEN others THEN
4317: IF l_debug = 1 THEN
4318: trace('Error when deleting from wms_packing_material_gtemp for content lpn '||p_content_lpn_id);
4319: END IF;
4320: x_proc_msg := l_proc_msg;
4321: raise fnd_api.g_exc_error;
4322: END;
4469: --Start Bug 6028098
4470: BEGIN
4471: SELECT sum(quantity)
4472: INTO l_sum_qty
4473: FROM wms_packing_material_gtemp
4474: WHERE inventory_item_id = p_inventory_item_id
4475: AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4476: AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
4477: AND subinventory = p_subinventory_code
4491:
4492: -- If the summed qty = process_qty, then no loop needs to be done.
4493: -- Simply perform an update for all records for that part and set the qty to 0.
4494: IF l_process_qty = l_sum_qty THEN
4495: UPDATE wms_packing_material_gtemp
4496: SET selected_flag = 'Y'
4497: ,quantity = 0
4498: WHERE inventory_item_id = p_inventory_item_id
4499: AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4528: IF l_debug = 1 THEN
4529: trace('l_update_qty ' || l_update_qty);
4530: END IF;
4531:
4532: UPDATE wms_packing_material_gtemp
4533: SET selected_flag = 'Y'
4534: ,quantity = quantity - l_update_qty
4535: WHERE inventory_item_id = p_inventory_item_id
4536: AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4547: --End Bug 6028098
4548:
4549: --Start Commented out Bug 6028098
4550: /*
4551: -- Update wms_packing_material_gtemp
4552: BEGIN
4553: UPDATE wms_packing_material_gtemp
4554: SET selected_flag = 'Y'
4555: ,quantity = quantity - inv_convert.inv_um_convert(
4549: --Start Commented out Bug 6028098
4550: /*
4551: -- Update wms_packing_material_gtemp
4552: BEGIN
4553: UPDATE wms_packing_material_gtemp
4554: SET selected_flag = 'Y'
4555: ,quantity = quantity - inv_convert.inv_um_convert(
4556: inventory_item_id,null,p_transaction_qty,p_transaction_uom,uom,null,null)
4557: WHERE inventory_item_id = p_inventory_item_id
4563: AND rownum<2;
4564: l_row_count := SQL%ROWCOUNT;
4565: IF l_row_count > 1 THEN
4566: IF l_debug = 1 THEN
4567: trace('Error when updating wms_packing_material_gtemp for item, only one record should be updated');
4568: END IF;
4569: fnd_message.set_name('INV','INV_FAILED');
4570: fnd_msg_pub.add;
4571: raise fnd_api.g_exc_error;
4570: fnd_msg_pub.add;
4571: raise fnd_api.g_exc_error;
4572: ELSE
4573: IF l_debug = 1 THEN
4574: trace('wms_packing_material_gtemp updated, row_count='||l_row_count);
4575: END IF;
4576: --END IF;
4577: EXCEPTION
4578: WHEN others THEN
4576: --END IF;
4577: EXCEPTION
4578: WHEN others THEN
4579: IF l_debug = 1 THEN
4580: trace('Error when updating wms_packing_material_gtemp for item '||p_inventory_item_id);
4581: END IF;
4582: fnd_message.set_name('INV','INV_FAILED');
4583: fnd_msg_pub.add;
4584: raise fnd_api.g_exc_error;