[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure to insert into WMS_PACKING_MATERIAL_GTEMP
*******************************************/
PROCEDURE insert_material_rec(
p_material_rec IN WMS_PACKING_MATERIAL_GTEMP%ROWTYPE) IS
BEGIN
INSERT INTO WMS_PACKING_MATERIAL_GTEMP(
MOVE_ORDER_HEADER_ID
, MOVE_ORDER_LINE_ID
, REFERENCE
, REFERENCE_ID
, TXN_SOURCE_ID
, DELIVERY_DETAIL_ID
, ORGANIZATION_ID
, ORGANIZATION_CODE
, SUBINVENTORY
, LOCATOR_ID
, LOCATOR
, PROJECT_ID
, PROJECT
, TASK_ID
, TASK_NUMBER
, TASK_NAME
, INVENTORY_ITEM_ID
, ITEM
, ITEM_DESCRIPTION
, LPN_ID
, LPN
, PARENT_LPN_ID
, PARENT_LPN
, OUTERMOST_LPN_ID
, OUTERMOST_LPN
, REVISION
, UOM
, LOT_NUMBER
, QUANTITY
, DELIVERY_ID
, DELIVERY
, DELIVERY_COMPLETED
, TRIP_ID
, TRIP
, CARRIER_ID
, CARRIER
, ORDER_HEADER_ID
, ORDER_NUMBER
, ORDER_LINE_ID
, ORDER_LINE_NUM
, PACKING_INSTRUCTION
, CUSTOMER_ID
, CUSTOMER_NUMBER
, CUSTOMER_NAME
, SHIP_TO_LOCATION_ID
, SHIP_TO_LOCATION
, RECEIPT_NUM
, DOCUMENT_TYPE
, DOCUMENT_ID
, DOCUMENT_NUMBER
, DOCUMENT_LINE_ID
, DOCUMENT_LINE_NUM
, VENDOR_ID
, SOURCE_ORG_ID
, TRADING_PARTNER
, RECEIVING_LOCATION_ID
, RECEIVING_LOCATION
, PTO_FLAG
, SELECTED_FLAG
, SHIP_SET_ID
, SHIP_SET
--INVCONV KKILLAMS
, SECONDARY_UOM_CODE
, SECONDARY_QUANTITY
, GRADE_CODE
--INVCONV KKILLAMS
)
VALUES(
p_material_rec.MOVE_ORDER_HEADER_ID
, p_material_rec.MOVE_ORDER_LINE_ID
, p_material_rec.REFERENCE
, p_material_rec.REFERENCE_ID
, p_material_rec.TXN_SOURCE_ID
, p_material_rec.DELIVERY_DETAIL_ID
, p_material_rec.ORGANIZATION_ID
, p_material_rec.ORGANIZATION_CODE
, p_material_rec.SUBINVENTORY
, p_material_rec.LOCATOR_ID
, p_material_rec.LOCATOR
, p_material_rec.PROJECT_ID
, p_material_rec.PROJECT
, p_material_rec.TASK_ID
, p_material_rec.TASK_NUMBER
, p_material_rec.TASK_NAME
, p_material_rec.INVENTORY_ITEM_ID
, p_material_rec.ITEM
, p_material_rec.ITEM_DESCRIPTION
, p_material_rec.LPN_ID
, p_material_rec.LPN
, p_material_rec.PARENT_LPN_ID
, p_material_rec.PARENT_LPN
, p_material_rec.OUTERMOST_LPN_ID
, p_material_rec.OUTERMOST_LPN
, p_material_rec.REVISION
, p_material_rec.UOM
, p_material_rec.LOT_NUMBER
, p_material_rec.QUANTITY
, p_material_rec.DELIVERY_ID
, p_material_rec.DELIVERY
, p_material_rec.DELIVERY_COMPLETED
, p_material_rec.TRIP_ID
, p_material_rec.TRIP
, p_material_rec.CARRIER_ID
, p_material_rec.CARRIER
, p_material_rec.ORDER_HEADER_ID
, p_material_rec.ORDER_NUMBER
, p_material_rec.ORDER_LINE_ID
, p_material_rec.ORDER_LINE_NUM
, p_material_rec.PACKING_INSTRUCTION
, p_material_rec.CUSTOMER_ID
, p_material_rec.CUSTOMER_NUMBER
, p_material_rec.CUSTOMER_NAME
, p_material_rec.SHIP_TO_LOCATION_ID
, p_material_rec.SHIP_TO_LOCATION
, p_material_rec.RECEIPT_NUM
, p_material_rec.DOCUMENT_TYPE
, p_material_rec.DOCUMENT_ID
, p_material_rec.DOCUMENT_NUMBER
, p_material_rec.DOCUMENT_LINE_ID
, p_material_rec.DOCUMENT_LINE_NUM
, p_material_rec.VENDOR_ID
, p_material_rec.SOURCE_ORG_ID
, p_material_rec.TRADING_PARTNER
, p_material_rec.RECEIVING_LOCATION_ID
, p_material_rec.RECEIVING_LOCATION
, p_material_rec.PTO_FLAG
, nvl(p_material_rec.SELECTED_FLAG,'N')
, p_material_rec.SHIP_SET_ID
, p_material_rec.SHIP_SET
--INVCONV kkillams
, p_material_rec.SECONDARY_UOM_CODE
, p_material_rec.SECONDARY_QUANTITY
, p_material_rec.GRADE_CODE
--INVCONV kkillams
);
trace('Error in insert_material_rec()');
END insert_material_rec;
l_select_str VARCHAR2(2000);
l_select_str := 'SELECT DISTINCT';
l_select_str := l_select_str || ' rsh.receipt_num,';
l_select_str := l_select_str || ' rs.location_id,';
l_select_str := l_select_str || ' rsh.vendor_id,';
l_select_str := l_select_str || ' rsl.from_organization_id,';
l_select_str := l_select_str || ' rs.rcv_transaction_id';
l_query_sql := l_select_str || l_from_str || l_where_str ;
select distinct inventory_item_id
from wms_packing_material_gtemp
where lpn_id is null --for loose item
and project_id is not NULL; --taks id can be loose
l_select_str VARCHAR2(2000) :=
'SELECT mol.header_id mol_header_id, mol.line_id mol_line_id, mol.reference, mol.reference_id, mol.txn_source_id, '||
'mol.organization_id organization_id, :org_code organization_code, nvl(lpn.subinventory_code,mol.from_subinventory_code) subinventory, '||
'nvl(lpn.locator_id,mol.from_locator_id) locator_id, :locator locator, mol.project_id project_id, :project project, mol.task_id task_id, :task task, '||
'mol.inventory_item_id inventory_item_id, :item item, mol.lpn_id lpn_id, mol.revision revision, mol.uom_code uom, mol.quantity-nvl(mol.quantity_delivered,0) quantity, mol.lot_number, '||
'mol.secondary_quantity -NVL(mol.secondary_quantity_delivered,0) secondary_quantity, mol.secondary_uom_code, mol.grade_code'; --INCONV kkillams
' (mol.lpn_id IS NOT NULL AND (NOT exists (select 1 from wms_dispatched_tasks wdt where wdt.transfer_lpn_id = mol.lpn_id)) '||
' AND (NOT exists (select 1 from wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt where wdt.transaction_temp_id = mmtt.transaction_temp_id and mmtt.lpn_id = mol.lpn_id)))) ';
l_select_str := l_select_str||', NULL rcv_location_id';
l_select_str := l_select_str||', NULL rcv_location';
l_select_str := l_select_str||', NULL vendor_id';
l_select_str := l_select_str||', NULL src_org_id';
l_select_str := l_select_str||', NULL parnter_name';
l_select_str := l_select_str||', NULL doc_type';
l_select_str := l_select_str||', NULL doc_num_id';
l_select_str := l_select_str||', NULL document_number';
l_select_str := l_select_str||', NULL receipt_num';
l_select_str := l_select_str||', NULL doc_line_id';
l_select_str := l_select_str||', NULL doc_line_num';
l_where_str := l_where_str||' IN (SELECT rsl.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_where_str := l_where_str||' IN (SELECT rs.item_id';
l_query_sql := l_select_str || l_from_str || l_where_str ;
delete from wms_packing_material_gtemp;
SELECT lpn.license_plate_number, lpn.parent_lpn_id, pLpn.license_plate_number,
lpn.outermost_lpn_id, oLpn.license_plate_number
INTO l_material_rec.lpn,
l_material_rec.parent_lpn_id,
l_material_rec.parent_lpn,
l_material_rec.outermost_lpn_id, l_material_rec.outermost_lpn
FROM wms_license_plate_numbers lpn, wms_license_plate_numbers pLpn, wms_license_plate_numbers oLpn
WHERE lpn.lpn_id = l_material_rec.lpn_id
AND pLpn.lpn_id(+) = lpn.parent_lpn_id
AND oLpn.lpn_id(+) = lpn.outermost_lpn_id;
SELECT
inv_project.get_locsegs(l_material_rec.locator_id,l_material_rec.organization_id) /*bug344642 concatenated_segments*/ INTO l_material_rec.locator
FROM mtl_item_locations_kfv
WHERE organization_id = l_material_rec.organization_id
AND subinventory_code = l_material_rec.subinventory
AND inventory_location_id = l_material_rec.locator_id;
SELECT name INTO l_material_rec.project
FROM pa_projects WHERE project_id = l_material_rec.project_id;
SELECT task_number,task_name INTO l_material_rec.task_number, l_material_rec.task_name
FROM pa_tasks
WHERE project_id = l_material_rec.project_id
AND task_id = l_material_rec.task_id;
SELECT concatenated_segments,description INTO l_material_rec.item, l_material_rec.item_description
FROM mtl_system_items_kfv
WHERE organization_id = l_material_rec.organization_id
AND inventory_item_id = l_material_rec.inventory_item_id;
SELECT hrl.location_code
INTO l_material_rec.receiving_location
FROM hr_locations_all hrl
WHERE hrl.location_id = l_material_rec.receiving_location_id;
SELECT rsh.shipment_header_id, rsl.shipment_line_id, rsl.requisition_line_id, rsh.shipment_num, rsh.asn_type, rsh.receipt_source_code, rsh.ship_to_org_id, rsl.line_num, rsh.receipt_num, rsh.vendor_id, rsl.from_organization_id
INTO l_shipment_header_id, l_shipment_line_id, l_req_line_id, l_shipment_num, l_asn_type, l_receipt_source_code, l_ship_to_org_id, l_line_num, l_receipt_num, l_vendor_id, l_src_org_id
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = l_material_rec.reference_id;
SELECT rsh.shipment_header_id, rsh.shipment_num, rsh.receipt_num,rsh.vendor_id
,rsl.shipment_line_id, rsl.line_num,rsl.from_organization_id
INTO l_material_rec.document_id, l_material_rec.document_number, l_receipt_num, l_vendor_id,l_shipment_line_id, l_line_num, l_src_org_id
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = l_material_rec.reference_id;
SELECT rsl.shipment_line_id, rsl.line_num, rsl.from_organization_id
INTO l_material_rec.document_line_id, l_material_rec.document_line_num, l_src_org_id
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = l_material_rec.reference_id;
SELECT rsh.vendor_id INTO l_material_rec.vendor_id
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = l_material_rec.reference_id;
SELECT vendor_name INTO l_material_rec.trading_partner
FROM po_vendors
WHERE vendor_id = l_material_rec.vendor_id;
SELECT rsl.from_organization_id INTO l_material_rec.source_org_id
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = l_material_rec.reference_id;
SELECT organization_code ||'-'||organization_name
INTO l_material_rec.trading_partner
FROM org_organization_definitions
WHERE organization_id = l_material_rec.source_org_id;
SELECT poh.po_header_id, poh.segment1, pol.po_line_id, pol.line_num
INTO l_material_rec.document_id, l_material_rec.document_number
, l_material_rec.document_line_id, l_material_rec.document_line_num
FROM po_headers_all poh, po_lines_all pol, po_line_locations_all poll
WHERE poll.line_location_id = l_material_rec.reference_id
AND poh.po_header_id = poll.po_header_id
AND pol.po_line_id = poll.po_line_id;
SELECT pol.po_line_id, pol.line_num
INTO l_material_rec.document_line_id, l_material_rec.document_line_num
FROM po_lines_all pol, po_line_locations_all poll
WHERE poll.line_location_id = l_material_rec.reference_id
AND pol.po_line_id = poll.po_line_id;
SELECT vendor_name INTO l_material_rec.trading_partner
FROM po_vendors
WHERE vendor_id = l_material_rec.vendor_id;
SELECT prh.requisition_header_id, prh.segment1, prl.requisition_line_id, prl.line_num
INTO l_material_rec.document_id, l_material_rec.document_number
, l_material_rec.document_line_id, l_material_rec.document_line_num
FROM po_requisition_headers_all prh, po_requisition_lines_all prl, rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = l_material_rec.reference_id
AND prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = rsl.requisition_line_id;
SELECT prl.requisition_line_id, prl.line_num
INTO l_material_rec.document_line_id, l_material_rec.document_line_num
FROM po_requisition_lines_all prl, rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = l_material_rec.reference_id
AND prl.requisition_line_id = rsl.requisition_line_id;
SELECT organization_code ||'-'||organization_name
INTO l_material_rec.trading_partner
FROM org_organization_definitions
WHERE organization_id = l_material_rec.source_org_id;
SELECT oeoh.header_id, to_char(oeoh.order_number), oeol.line_id, oeol.line_number
INTO l_material_rec.document_id, l_material_rec.document_number
, l_material_rec.document_line_id, l_material_rec.document_line_num
FROM oe_order_headers_all oeoh, oe_order_lines_all oeol
WHERE oeol.line_id = l_material_rec.reference_id
AND oeoh.header_id = oeol.header_id;
SELECT oeol.line_id, oeol.line_number
INTO l_material_rec.document_line_id, l_material_rec.document_line_num
FROM oe_order_lines_all oeol
WHERE oeol.line_id = l_material_rec.reference_id;
SELECT vendor_name INTO l_material_rec.trading_partner
FROM po_vendors
WHERE vendor_id = l_material_rec.vendor_id;
insert_material_rec(l_material_rec);
select count(1) INTO l_item_cnt
FROM (select distinct project_id, task_id
from wms_packing_material_gtemp
WHERE lpn_id is NULL --loose items only
and inventory_item_id = l_get_proj_task_rec.inventory_item_id
AND project_id IS NOT NULL) wpmg;
insert into wms_packing_material_temp value (select * from wms_packing_material_gtemp);
g_lot_ser_attr.delete;
SELECT wnd.delivery_id, wnd.name,
nvl(p_delivery_state, wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id))
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.organization_id = p_organization_id
AND wnd.delivery_id = p_delivery_id
AND ((p_trip_id IS NULL) OR
(p_trip_id IS NOT NULL AND wnd.delivery_id IN
(select wdl.delivery_id from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
where wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = p_trip_id)))
AND ((p_delivery_state IS NULL) OR
(p_delivery_state IS NOT NULL AND
wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id) = p_delivery_state));
SELECT wnd.delivery_id, wnd.name,
nvl(p_delivery_state, wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id))
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.organization_id = p_organization_id
AND wnd.delivery_id IN
(select wdl.delivery_id from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
where wdl.pick_up_stop_id = wts.stop_id
and wts.trip_id = p_trip_id)
AND ((p_delivery_state IS NULL) OR
(p_delivery_state IS NOT NULL AND
wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id) = p_delivery_state));
SELECT wnd.delivery_id, wnd.name,
nvl(p_delivery_state, wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id))
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.organization_id = p_organization_id
AND ((p_delivery_state IS NULL) OR
(p_delivery_state IS NOT NULL AND
wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id) = p_delivery_state));
SELECT wdd1.organization_id
,wdd1.subinventory
,wdd1.locator_id
,wdd1.project_id
,wdd1.task_id
,wdd1.inventory_item_id
,wdd1.revision
,wdd1.lot_number
,wdd1.requested_quantity_uom uom
,sum(wdd1.requested_quantity) quantity
,wdd1.requested_quantity_uom2 uom2 --INVCONV KKILLAMS
,sum(wdd1.requested_quantity2) quantity2 --INVCONV KKILLAMS
,wdd2.lpn_id
,wda.delivery_id
-- Bug 5121507, Get carrier in the order of Trip->Delivery->Delivery Detail
--,nvl(wdd1.carrier_id, wnd.carrier_id) carrier_id
,nvl(wt.carrier_id, nvl(wnd.carrier_id, wdd1.carrier_id)) carrier_id
,wdd1.source_header_id
,wdd1.source_header_number
,wdd1.source_line_id
,wdd1.source_line_number
,nvl(wdd1.customer_id, wnd.customer_id)
,wdd1.ship_to_location_id
,wdd1.ship_set_id
,wdd1.top_model_line_id
FROM wsh_delivery_details_ob_grp_v wdd1, wsh_delivery_details_ob_grp_v wdd2
,wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
-- Bug 5121507
, wsh_delivery_legs wdl
, wsh_trip_stops wts
, wsh_trips wt
WHERE wda.delivery_detail_id = wdd1.delivery_detail_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wnd.delivery_id (+) = wda.delivery_id
AND wdd1.released_status = 'Y'
AND wdd2.lpn_id IS NOT NULL
AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd2.lpn_id IN
(select lpn_id from wms_license_plate_numbers
where organization_id = p_organization_id
and lpn_context = 11)
-- restriction from find window
AND wdd1.organization_id = p_organization_id
AND wdd1.subinventory = nvl(p_subinventory_code, wdd1.subinventory)
AND nvl(wdd1.locator_id, -999) = nvl(p_locator_id, nvl(wdd1.locator_id, -999))
AND ((wdd2.lpn_id = nvl(p_from_lpn_id, wdd2.lpn_id)) OR
(wdd2.lpn_id IN (select lpn_id from wms_license_plate_numbers where outermost_lpn_id = p_from_lpn_id)))
AND wdd1.inventory_item_id = nvl(p_inventory_item_id, wdd1.inventory_item_id )
AND nvl(wdd1.project_id, -9999) = nvl(p_project_id,nvl(wdd1.project_id, -9999))
AND nvl(wdd1.task_id, -9999) = nvl(p_task_id,nvl(wdd1.task_id, -9999))
AND ((pl_delivery_id IS NULL) OR
(pl_delivery_id IS NOT NULL AND wda.delivery_id = pl_delivery_id))
AND wdd1.source_header_number = nvl(p_order_number, wdd1.source_header_number)
AND wdd1.source_header_type_name = nvl(p_order_type, wdd1.source_header_type_name)
-- Bug 5121507
--AND nvl(nvl(wdd1.carrier_id, wnd.carrier_id), -9999) = nvl(p_carrier_id, nvl(nvl(wdd1.carrier_id, wnd.carrier_id), -9999))
AND nvl(nvl(wt.carrier_id, nvl(wnd.carrier_id,wdd1.carrier_id)), -9999) = nvl(p_carrier_id, nvl(nvl(wt.carrier_id, nvl(wnd.carrier_id,wdd1.carrier_id)), -9999))
AND wdd1.customer_id = nvl(p_customer_id, wdd1.customer_id)
-- Bug 5121507
AND wnd.delivery_id = wdl.delivery_id(+)
AND wdl.pick_up_stop_id = wts.stop_id (+)
AND wts.trip_id = wt.trip_id (+)
GROUP BY wdd1.organization_id
,wdd1.subinventory
,wdd1.locator_id
,wdd1.project_id
,wdd1.task_id
,wdd1.inventory_item_id
,wdd1.revision
,wdd1.lot_number
,wdd1.requested_quantity_uom
,wdd1.requested_quantity_uom2 --INVCONV KKILLAMS
,wdd2.lpn_id
,wda.delivery_id
-- Bug 5121507
--,nvl(wdd1.carrier_id, wnd.carrier_id)
,nvl(wt.carrier_id, nvl(wnd.carrier_id,wdd1.carrier_id))
,wdd1.source_header_id
,wdd1.source_header_number
,wdd1.source_line_id
,wdd1.source_line_number
,nvl(wdd1.customer_id, wnd.customer_id)
,wdd1.ship_to_location_id
,wdd1.ship_set_id
,wdd1.top_model_line_id;
delete from wms_packing_material_gtemp;
SELECT
inv_project.get_locsegs(l_material_rec.locator_id,l_material_rec.organization_id) /*bug344642 concatenated_segments*/ INTO l_material_rec.locator
FROM mtl_item_locations_kfv
WHERE organization_id = l_material_rec.organization_id
AND subinventory_code = l_material_rec.subinventory
AND inventory_location_id = l_material_rec.locator_id;
SELECT name INTO l_material_rec.project
FROM pa_projects WHERE project_id = l_material_rec.project_id;
SELECT task_number,task_name INTO l_material_rec.task_number, l_material_rec.task_name
FROM pa_tasks
WHERE project_id = l_material_rec.project_id
AND task_id = l_material_rec.task_id;
SELECT concatenated_segments,description INTO l_material_rec.item, l_material_rec.item_description
FROM mtl_system_items_kfv
WHERE organization_id = l_material_rec.organization_id
AND inventory_item_id = l_material_rec.inventory_item_id;
SELECT lpn.license_plate_number, lpn.parent_lpn_id, pLpn.license_plate_number,
lpn.outermost_lpn_id, oLpn.license_plate_number
INTO l_material_rec.lpn, l_material_rec.parent_lpn_id, l_material_rec.parent_lpn,
l_material_rec.outermost_lpn_id, l_material_rec.outermost_lpn
FROM wms_license_plate_numbers lpn, wms_license_plate_numbers pLpn, wms_license_plate_numbers oLpn
WHERE lpn.lpn_id = l_material_rec.lpn_id
AND pLpn.lpn_id(+) = lpn.parent_lpn_id
AND oLpn.lpn_id(+) = lpn.outermost_lpn_id;
SELECT name INTO l_material_rec.delivery
FROM wsh_new_deliveries
WHERE delivery_id = l_material_rec.delivery_id;
SELECT t.trip INTO l_material_rec.trip
FROM(
SELECT distinct wt.name trip
FROM wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt
WHERE wdl.delivery_id = l_material_rec.delivery_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wt.trip_id = wts.trip_id) t
WHERE rownum <2;
SELECT carrier_name INTO l_material_rec.carrier
FROM wsh_carriers_v
WHERE carrier_id = l_material_rec.carrier_id;
SELECT nvl(oeol.packing_instructions, oeoh.packing_instructions)
INTO l_material_rec.packing_instruction
FROM oe_order_headers_all oeoh, oe_order_lines_all oeol
WHERE oeoh.header_id = oeol.header_id
AND oeol.line_id = l_material_rec.order_line_id;
SELECT party.party_number, party.party_name
INTO l_material_rec.customer_number, l_material_rec.customer_name
FROM hz_parties party --, hz_cust_accounts cust_acct
WHERE party.party_id = l_material_rec.customer_id;
SELECT location_code INTO l_material_rec.ship_to_location
FROM hr_locations_all
WHERE location_id = l_material_rec.ship_to_location_id;
SELECT nvl(city, address1)||':'||to_char(location_id)
INTO l_material_rec.ship_to_location
FROM hz_locations
WHERE location_id = l_material_rec.ship_to_location_id;
SELECT 'Y' INTO l_material_rec.pto_flag
FROM dual
WHERE exists (
select 1 from oe_order_lines_all oel, oe_order_lines_all oel1
where oel.inventory_item_id = l_material_rec.inventory_item_id
and oel.top_model_line_id = l_top_model_line_id
and oel1. inventory_item_id = oel.inventory_item_id
and oel1.top_model_line_id = oel.top_model_line_id
and (((oel.shippable_flag = 'Y' or oel.line_id = oel.top_model_line_id)
AND (oel.ato_line_id <> oel.TOP_MODEL_LINE_ID OR oel.ato_line_id IS NULL))
OR (oel1.ato_line_id is not null and oel1.line_id = oel1.top_model_line_id))
);
SELECT set_name
INTO l_material_rec.ship_set
FROM oe_sets
WHERE set_id = l_material_rec.ship_set_id;
insert_material_rec(l_material_rec);
SELECT distinct kit_item_id, top_model_line_id, 'Y','N' identified_flag
/*is_kit_identified(kit_item_id) identified_flag*/
FROM wms_packing_kitting_gtemp
WHERE nvl(completed_flag,'N') <> 'Y'
ORDER BY identified_flag desc;
SELECT oel.INVENTORY_ITEM_id, oel.top_model_line_id, 'N', 'N'
FROM oe_order_lines_all oel
WHERE oel.line_id = oel.top_model_line_id
AND oel.ato_line_id IS NULL
AND oel.top_model_line_id in (
select oel1.top_model_line_id
from oe_order_lines_all oel1,oe_order_lines_all oel2
where oel1.inventory_item_id = p_item_id
and oel2. inventory_item_id = oel1.inventory_item_id
and oel2.top_model_line_id = oel1.top_model_line_id
AND oel1.line_id = oel2.line_id --bug 3458361
and (((oel1.shippable_flag = 'Y') AND (oel1.line_id <> oel1.TOP_MODEL_LINE_ID) and (oel1.ato_line_id is null))
OR (oel2.ato_line_id is not null and oel2.line_id = oel2.top_model_line_id))
)
AND exists (
select 1 from WMS_PACKING_MATERIAL_GTEMP wpmg, oe_order_lines_all oel1
where WPMG.order_header_id = oel1.header_id
AND wpmg.order_line_id = oel1.line_id
AND wpmg.inventory_item_id = p_item_id
AND wpmg.inventory_item_id = oel1.inventory_item_id
AND oel.top_model_line_id = oel1.top_model_line_id
AND oel.header_id = oel1.header_id);
l_kit_list.DELETE;
select 1 INTO l_item_in_existing_kit FROM dual
WHERE exists
(SELECT 1 from WMS_PACKING_KITTING_GTEMP WPKG
where WPKG.top_model_line_id = l_kit_rec.top_model_line_id);
PROCEDURE insert_kit_info(
p_kit_item_id IN NUMBER
, p_component_item_id IN NUMBER
, p_top_model_line_id IN NUMBER
, p_packed_qty IN NUMBER
, p_disp_packed_qty IN VARCHAR2
) IS
CURSOR kit_component_cur IS
SELECT msi.concatenated_segments ITEM,
msi.inventory_item_id ITEM_ID,
--round(oel.ordered_quantity/oel1.ordered_quantity) bom_qty,
oel.ordered_quantity ORD_QTY,
OEL.top_model_LINE_ID
FROM oe_order_lines_all oel, mtl_system_items_kfv msi, oe_order_lines_all oel1
WHERE oel.inventory_item_id = msi.inventory_item_id
AND oel.ship_from_org_id = msi.organization_id
AND oel1.inventory_item_id = msi.inventory_item_id
AND oel1.ship_from_org_id = msi.organization_id
AND oel.top_model_line_id = p_top_model_line_id
AND oel1.top_model_line_id = oel.top_model_line_id
AND oel1.line_id = oel.line_id --bug 3458361
AND (((oel.shippable_flag = 'Y' or oel.line_id = oel.top_model_line_id)
AND (oel.ato_line_id <> oel.TOP_MODEL_LINE_ID OR oel.ato_line_id IS NULL))
OR (oel1.ato_line_id is not null and oel1.line_id = oel1.top_model_line_id))
ORDER BY oel.top_model_line_id,oel.shippable_flag;
trace('In insert_kit_info, p_kit_item_id='||p_kit_item_id||',p_component_item_id='||p_component_item_id);
INSERT INTO wms_packing_kitting_gtemp
( ITEM
, kit_item_id
, component_item_id
, top_model_line_id
, BOM_QTY
, ORDER_QTY
, PACKED_QTY
, packed_qty_disp
, completed_flag) VALUES
(l_kit_rec.item
,l_kit_rec.kit_item_id
,l_kit_rec.component_item_id
,l_kit_rec.top_model_line_id
,1
,l_kit_rec.ORDER_QTY
,l_kit_rec.PACKED_QTY
,l_kit_rec.packed_qty_disp
,l_kit_rec.completed_flag);
trace('Inserted kit info for kit_item_id '||l_kit_rec.kit_item_id);
INSERT INTO wms_packing_kitting_gtemp
( ITEM
, kit_item_id
, component_item_id
, top_model_line_id
, BOM_QTY
, ORDER_QTY
, PACKED_QTY
, packed_qty_disp
, completed_flag) VALUES
(l_kit_rec.item
,l_kit_rec.kit_item_id
,l_kit_rec.component_item_id
,l_kit_rec.top_model_line_id
,(l_kit_rec.order_qty/l_kit_oqty) -- Cmp BOM = Cmp_Order_Qty/Kit_Order_Qty
,l_kit_rec.ORDER_QTY
,l_kit_rec.PACKED_QTY
,l_kit_rec.packed_qty_disp
,l_kit_rec.completed_flag);
trace('Inserted component info for component_item_id '||l_kit_rec.component_item_id);
END insert_kit_info;
SELECT kit_item_id
,component_item_id
,packed_qty
,packed_qty_disp
FROM wms_packing_kitting_gtemp
WHERE kit_item_id = p_kit_item_id
AND top_model_line_id = p_top_model_line_id
AND component_item_id IS NOT NULL
AND component_item_id <> p_exclude_item_id;
SELECT wpmg.move_order_line_id
, wpmg.txn_source_id
, wpmg.project_id
, wpmg.task_id
, inv_convert.inv_um_convert(wpmg.inventory_item_id,null,least(mol.quantity,wpmg.quantity), mol.uom_code,p_primary_uom,null,null)
, least(mol.quantity,wpmg.quantity)
, mol.uom_code
--, decode(wpmg.uom, p_transaction_uom, 0, 1) uom_match
, least(mol.secondary_quantity,wpmg.secondary_quantity)
, mol.secondary_uom_code --INVCONV kkillams
, wpmg.grade_code --INVCONV kkillams
, mol.lot_number
, mol.inspection_status
FROM wms_packing_material_gtemp wpmg, mtl_txn_request_lines mol
WHERE wpmg.move_order_line_id = mol.line_id
AND wpmg.organization_id = p_organization_id
AND nvl(wpmg.subinventory,'#$%') = nvl(p_subinventory_code, nvl(wpmg.subinventory,'#$%'))
AND nvl(wpmg.locator_id, -9999) = nvl(p_locator_id, nvl(wpmg.locator_id, -9999))
AND wpmg.inventory_item_id = p_inventory_item_id
AND ((p_revision IS NULL) OR
(p_revision IS NOT NULL and wpmg.revision = p_revision))
AND ((wpmg.lot_number IS NULL) OR
(wpmg.lot_number IS NOT NULL and wpmg.lot_number = p_lot_number))
AND ((p_from_lpn_id IS NULL AND wpmg.lpn_id IS NULL) OR
(p_from_lpn_id IS NOT NULL and wpmg.lpn_id = p_from_lpn_id))
AND ((p_project_id = -1 and p_task_id = -1) OR
(wpmg.project_id IS NULL and p_project_id IS NULL and
wpmg.task_id IS NULL and p_task_id IS NULL) OR
(wpmg.project_id = p_project_id AND wpmg.task_id = p_task_id))
AND ((mol.wms_process_flag = 2 and wpmg.selected_flag='Y') OR
(mol.wms_process_flag <> 2))
order by decode(wpmg.uom, p_transaction_uom, 0, 1) asc, mol.creation_date asc;
SELECT * FROM wms_packing_material_gtemp
WHERE inventory_item_id = p_inventory_item_id
AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
AND subinventory = p_subinventory_code
AND locator_id = p_locator_id
AND lpn_id = p_from_lpn_id;
l_update_qty NUMBER := 0; --Bug 6028098
l_insert NUMBER;
SELECT 1, mmtt.move_order_line_id, mmtt.inventory_item_id, mmtt.revision
, mmtt.transaction_quantity, mmtt.transaction_uom, mtlt.lot_number, mtlt.serial_transaction_temp_id
, mmtt.secondary_transaction_quantity, mmtt.secondary_uom_code --INVCONV kkillams
INTO l_mmtt_exists, l_cur_rec.move_order_line_id, l_cur_rec.inventory_item_id
, l_cur_rec.revision, l_cur_rec.transaction_quantity, l_cur_rec.transaction_uom
, l_cur_rec.lot_number, l_cur_rec.serial_transaction_temp_id
, l_cur_rec.secondary_transaction_quantity, l_cur_rec.secondary_uom_code --INVCONV kkillams
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = p_transaction_temp_id
AND mmtt.inventory_item_id <> -1
AND mmtt.content_lpn_id IS NULL;
l_insert := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id => null
,p_item_id => null
,p_org_id => p_organization_id
,p_trx_action_id => l_txn_action_id
,p_trx_type_id => l_txn_type_id
,p_trx_src_type_id => 13
,p_trx_qty => 0
,p_pri_qty => 0
,p_uom => nvl(p_transaction_uom, ' ')
,p_subinv_code => p_subinventory_code
,p_tosubinv_code => p_to_subinventory
,p_locator_id => p_locator_id
,p_tolocator_id => l_new_tolocator_id
,p_from_lpn_id => p_from_lpn_id
,p_cnt_lpn_id => p_content_lpn_id
,p_xfr_lpn_id => p_to_lpn_id
,p_posting_flag => 'N' -- Set this so that locator capacity calculation will not consider this
,p_move_order_line_id => null
,p_process_flag => 'N' -- Set process_flag to 'N' so that INV TM will not process this MMTT
,p_user_id => fnd_global.user_id
,x_trx_tmp_id => l_txn_tmp_id
,x_proc_msg => l_proc_msg
,p_secondary_trx_qty => CASE WHEN p_secondary_uom IS NOT NULL THEN 0 ELSE NULL END --INVCONV kkillams
,p_secondary_uom => nvl(p_secondary_uom, ' ') --INVCONV kkillams
);
trace('done with inserting , l_insert ='||l_insert);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MMTT for content lpn ID '|| p_content_lpn_id|| 'err is '||l_proc_msg);
trace('MMTT inserted, tmp_id='||l_txn_tmp_id);
delete from wms_packing_material_gtemp where outermost_lpn_id = p_content_lpn_id;
l_mol_list.delete;
select current_status
, lot_number
, inspection_status
INTO l_current_status
, l_ser_lot_number
, l_ser_inspection_status
from mtl_serial_numbers a
where serial_number = p_fm_serial_number
and current_organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and rownum<2;
trace('Calling inv_trx_util_pub.insert_line_trx() to insert MMTT with ');
l_insert := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id => l_txn_hdr_id
,p_item_id => p_inventory_item_id
,p_revision => p_revision
,p_org_id => p_organization_id
,p_trx_action_id => l_txn_action_id
,p_trx_type_id => l_txn_type_id
,p_trx_src_type_id => 13
,p_trx_qty => l_mol_list(i).transaction_quantity
,p_pri_qty => l_mol_list(i).primary_quantity
,p_uom => l_mol_list(i).transaction_uom
,p_secondary_trx_qty => l_mol_list(i).secondary_transaction_quantity --INVCONV kkillams
,p_secondary_uom => l_mol_list(i).secondary_uom_code --INVCONV kkillams
,p_subinv_code => p_subinventory_code
,p_tosubinv_code => p_to_subinventory
,p_locator_id => p_locator_id
,p_tolocator_id => l_new_tolocator_id --p_to_locator_id
,p_from_lpn_id => p_from_lpn_id
,p_xfr_lpn_id => p_to_lpn_id
,p_posting_flag => 'N' -- Set this so that locator capacity calculation will not consider this
,p_process_flag => 'N' -- Set process_flag to 'N' so that INV TM will not process this MMTT record
,p_move_order_line_id => l_mol_list(i).move_order_line_id
,p_user_id => fnd_global.user_id
,x_trx_tmp_id => l_txn_tmp_id
,x_proc_msg => l_proc_msg
);
trace('done with inserting , l_insert ='||l_insert||',mol='||l_mol_list(i).move_order_line_id);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MMTT for move order line id:'||l_mol_list(i).move_order_line_id || 'err is '||l_proc_msg);
trace('MMTT inserted, tmp_id='||l_txn_tmp_id||', hdr_id='||l_txn_hdr_id);
SELECT 1 INTO l_new_lot
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
g_lot_ser_attr.delete;
trace('Calling insert_lot_trx with ');
l_insert := inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id => l_txn_tmp_id
, p_user_id => fnd_global.user_id
, p_lot_number => p_lot_number
, p_exp_date => p_lot_expiration_date
, p_trx_qty => l_mol_list(i).transaction_quantity
, p_pri_qty => l_mol_list(i).primary_quantity
, p_secondary_qty => l_mol_list(i).secondary_transaction_quantity --INVCONV kkillams
, p_grade_code => p_grade_code --INVCONV kkillams
, x_ser_trx_id => l_ser_txn_id
, x_proc_msg => l_proc_msg
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MTLT for lot:'||p_lot_number||',l_proc_msg='||l_proc_msg);
trace('MTLT record inserted for lot(no attr):'||p_lot_number||',ser_txn_id='||l_ser_txn_id);
trace('Calling insert_lot_trx with ');
l_insert := inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id => l_txn_tmp_id
, p_user_id => fnd_global.user_id
, p_lot_number => p_lot_number
, p_exp_date => p_lot_expiration_date
, p_trx_qty => l_mol_list(i).transaction_quantity
, p_pri_qty => l_mol_list(i).primary_quantity
, p_secondary_qty => l_mol_list(i).secondary_transaction_quantity --INVCONV kkillams
, p_grade_code => l_mol_list(i).grade_code --INVCONV kkillams
, x_ser_trx_id => l_ser_txn_id
, x_proc_msg => l_proc_msg
, p_age =>to_number(get_column_default_value('AGE'))
, p_best_by_date =>to_date(get_column_default_value('BEST_BY_DATE'),G_DATE_MASK)
, p_change_date =>to_date(get_column_default_value('CHANGE_DATE'),G_DATE_MASK)
, p_color =>get_column_default_value('COLOR')
, p_curl_wrinkle_fold =>get_column_default_value('CURL_WRINKLE_FOLD')
, p_date_code =>get_column_default_value('DATE_CODE')
, p_description =>get_column_default_value('DESCRIPTION')
, p_item_size =>to_number(get_column_default_value('ITEM_SIZE'))
, p_length =>to_number(get_column_default_value('LENGTH'))
, p_length_uom =>get_column_default_value('LENGTH_UOM')
, p_maturity_date =>to_date(get_column_default_value('MATURITY_DATE'),G_DATE_MASK)
, p_origination_date =>to_date(get_column_default_value('ORIGINATION_DATE'),G_DATE_MASK)
, p_place_of_origin =>get_column_default_value('PLACE_OF_ORIGIN')
, p_recycled_content =>to_number(get_column_default_value('RECYCLED_CONTENT'))
, p_retest_date =>to_date(get_column_default_value('RETEST_DATE'),G_DATE_MASK)
, p_supplier_lot_number =>get_column_default_value('SUPPLIER_LOT_NUMBER')
, p_territory_code =>get_column_default_value('TERRITORY_CODE')
, p_thickness =>to_number(get_column_default_value('THICKNESS'))
, p_thickness_uom =>get_column_default_value('THICKNESS_UOM')
, p_vendor_id =>get_column_default_value('VENDOR_ID')
, p_volume =>to_number(get_column_default_value('VOLUME'))
, p_volume_uom =>get_column_default_value('VOLUME_UOM')
, p_width =>to_number(get_column_default_value('WIDTH'))
, p_width_uom =>to_number(get_column_default_value('WIDTH_UOM'))
, p_lot_attribute_category=>get_column_default_value('LOT_ATTRIBUTE_CATEGORY')
, p_c_attribute1 =>get_column_default_value('C_ATTRIBUTE1')
, p_c_attribute2 =>get_column_default_value('C_ATTRIBUTE2')
, p_c_attribute3 =>get_column_default_value('C_ATTRIBUTE3')
, p_c_attribute4 =>get_column_default_value('C_ATTRIBUTE4')
, p_c_attribute5 =>get_column_default_value('C_ATTRIBUTE5')
, p_c_attribute6 =>get_column_default_value('C_ATTRIBUTE6')
, p_c_attribute7 =>get_column_default_value('C_ATTRIBUTE7')
, p_c_attribute8 =>get_column_default_value('C_ATTRIBUTE8')
, p_c_attribute9 =>get_column_default_value('C_ATTRIBUTE9')
, p_c_attribute10 =>get_column_default_value('C_ATTRIBUTE10')
, p_c_attribute11 =>get_column_default_value('C_ATTRIBUTE11')
, p_c_attribute12 =>get_column_default_value('C_ATTRIBUTE12')
, p_c_attribute13 =>get_column_default_value('C_ATTRIBUTE13')
, p_c_attribute14 =>get_column_default_value('C_ATTRIBUTE14')
, p_c_attribute15 =>get_column_default_value('C_ATTRIBUTE15')
, p_c_attribute16 =>get_column_default_value('C_ATTRIBUTE16')
, p_c_attribute17 =>get_column_default_value('C_ATTRIBUTE17')
, p_c_attribute18 =>get_column_default_value('C_ATTRIBUTE18')
, p_c_attribute19 =>get_column_default_value('C_ATTRIBUTE19')
, p_c_attribute20 =>get_column_default_value('C_ATTRIBUTE20')
, p_d_attribute1 =>to_date(get_column_default_value('D_ATTRIBUTE1'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute2 =>to_date(get_column_default_value('D_ATTRIBUTE2'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute3 =>to_date(get_column_default_value('D_ATTRIBUTE3'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute4 =>to_date(get_column_default_value('D_ATTRIBUTE4'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute5 =>to_date(get_column_default_value('D_ATTRIBUTE5'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute6 =>to_date(get_column_default_value('D_ATTRIBUTE6'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute7 =>to_date(get_column_default_value('D_ATTRIBUTE7'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute8 =>to_date(get_column_default_value('D_ATTRIBUTE8'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute9 =>to_date(get_column_default_value('D_ATTRIBUTE9'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute10 =>to_date(get_column_default_value('D_ATTRIBUTE10'),G_DATE_MASK)
, p_n_attribute1 =>to_number(get_column_default_value('N_ATTRIBUTE1'))
, p_n_attribute2 =>to_number(get_column_default_value('N_ATTRIBUTE2'))
, p_n_attribute3 =>to_number(get_column_default_value('N_ATTRIBUTE3'))
, p_n_attribute4 =>to_number(get_column_default_value('N_ATTRIBUTE4'))
, p_n_attribute5 =>to_number(get_column_default_value('N_ATTRIBUTE5'))
, p_n_attribute6 =>to_number(get_column_default_value('N_ATTRIBUTE6'))
, p_n_attribute7 =>to_number(get_column_default_value('N_ATTRIBUTE7'))
, p_n_attribute8 =>to_number(get_column_default_value('N_ATTRIBUTE8'))
, p_n_attribute9 =>to_number(get_column_default_value('N_ATTRIBUTE9'))
, p_n_attribute10 =>to_number(get_column_default_value('N_ATTRIBUTE10'))
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MTLT for lot:'||p_lot_number||',l_proc_msg='||l_proc_msg);
trace('MTLT record inserted for lot(with attr):'||p_lot_number||',ser_txn_id='||l_ser_txn_id);
SELECT 1 INTO l_new_serial
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND serial_number = p_fm_serial_number;
g_lot_ser_attr.delete;
l_insert := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => nvl(l_ser_txn_id,l_txn_tmp_id)
, p_user_id => fnd_global.user_id
, p_fm_ser_num => p_fm_serial_number
, p_to_ser_num => p_fm_serial_number
, x_proc_msg => l_proc_msg
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MSNT for serial(no attr):'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
trace('MSNT record inserted for serial(no attr):'||p_fm_serial_number||',ser_txn_id='||nvl(l_ser_txn_id,l_txn_tmp_id));
l_insert := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => nvl(l_ser_txn_id,l_txn_tmp_id)
, p_user_id => fnd_global.user_id
, p_fm_ser_num => p_fm_serial_number
, p_to_ser_num => p_fm_serial_number
, x_proc_msg => l_proc_msg
, p_time_since_new =>to_number(get_column_default_value('TIME_SINCE_NEW'))
, p_cycles_since_new =>to_number(get_column_default_value('CYCLES_SINCE_NEW'))
, p_time_since_overhaul =>to_number(get_column_default_value('TIME_SINCE_OVERHAUL'))
, p_cycles_since_overhaul=>to_number(get_column_default_value('CYCLES_SINCE_OVERHAUL'))
, p_time_since_repair =>to_number(get_column_default_value('TIME_SINCE_REPAIR'))
, p_cycles_since_repair =>to_number(get_column_default_value('CYCLES_SINCE_REPAIR'))
, p_time_since_visit =>to_number(get_column_default_value('TIME_SINCE_VISIT'))
, p_cycles_since_visit =>to_number(get_column_default_value('CYCLES_SINCE_VISIT'))
, p_time_since_mark =>to_number(get_column_default_value('TIME_SINCE_MARK'))
, p_cycles_since_mark =>to_number(get_column_default_value('CYCLES_SINCE_MARK'))
, p_number_of_repairs =>to_number(get_column_default_value('NUMBER_OF_REPAIRS'))
, p_territory_code =>to_number(get_column_default_value('TERRITORY_CODE'))
, p_orgination_date =>to_date(get_column_default_value('ORIGINATION_DATE'),G_DATE_MASK)
, p_serial_attribute_category =>get_column_default_value('SERIAL_ATTRIBUTE_CATEGORY')
, p_c_attribute1 =>get_column_default_value('C_ATTRIBUTE1')
, p_c_attribute2 =>get_column_default_value('C_ATTRIBUTE2')
, p_c_attribute3 =>get_column_default_value('C_ATTRIBUTE3')
, p_c_attribute4 =>get_column_default_value('C_ATTRIBUTE4')
, p_c_attribute5 =>get_column_default_value('C_ATTRIBUTE5')
, p_c_attribute6 =>get_column_default_value('C_ATTRIBUTE6')
, p_c_attribute7 =>get_column_default_value('C_ATTRIBUTE7')
, p_c_attribute8 =>get_column_default_value('C_ATTRIBUTE8')
, p_c_attribute9 =>get_column_default_value('C_ATTRIBUTE9')
, p_c_attribute10 =>get_column_default_value('C_ATTRIBUTE10')
, p_c_attribute11 =>get_column_default_value('C_ATTRIBUTE11')
, p_c_attribute12 =>get_column_default_value('C_ATTRIBUTE12')
, p_c_attribute13 =>get_column_default_value('C_ATTRIBUTE13')
, p_c_attribute14 =>get_column_default_value('C_ATTRIBUTE14')
, p_c_attribute15 =>get_column_default_value('C_ATTRIBUTE15')
, p_c_attribute16 =>get_column_default_value('C_ATTRIBUTE16')
, p_c_attribute17 =>get_column_default_value('C_ATTRIBUTE17')
, p_c_attribute18 =>get_column_default_value('C_ATTRIBUTE18')
, p_c_attribute19 =>get_column_default_value('C_ATTRIBUTE19')
, p_c_attribute20 =>get_column_default_value('C_ATTRIBUTE20')
, p_d_attribute1 =>to_date(get_column_default_value('D_ATTRIBUTE1'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute2 =>to_date(get_column_default_value('D_ATTRIBUTE2'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute3 =>to_date(get_column_default_value('D_ATTRIBUTE3'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute4 =>to_date(get_column_default_value('D_ATTRIBUTE4'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute5 =>to_date(get_column_default_value('D_ATTRIBUTE5'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute6 =>to_date(get_column_default_value('D_ATTRIBUTE6'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute7 =>to_date(get_column_default_value('D_ATTRIBUTE7'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute8 =>to_date(get_column_default_value('D_ATTRIBUTE8'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute9 =>to_date(get_column_default_value('D_ATTRIBUTE9'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute10 =>to_date(get_column_default_value('D_ATTRIBUTE10'),'YYYY/MM/DD:HH24:MI:SS')
, p_n_attribute1 =>to_number(get_column_default_value('N_ATTRIBUTE1'))
, p_n_attribute2 =>to_number(get_column_default_value('N_ATTRIBUTE2'))
, p_n_attribute3 =>to_number(get_column_default_value('N_ATTRIBUTE3'))
, p_n_attribute4 =>to_number(get_column_default_value('N_ATTRIBUTE4'))
, p_n_attribute5 =>to_number(get_column_default_value('N_ATTRIBUTE5'))
, p_n_attribute6 =>to_number(get_column_default_value('N_ATTRIBUTE6'))
, p_n_attribute7 =>to_number(get_column_default_value('N_ATTRIBUTE7'))
, p_n_attribute8 =>to_number(get_column_default_value('N_ATTRIBUTE8'))
, p_n_attribute9 =>to_number(get_column_default_value('N_ATTRIBUTE9'))
, p_n_attribute10 =>to_number(get_column_default_value('N_ATTRIBUTE10'))
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MSNT for serial:'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
trace('MSNT record inserted for serial(with attr):'||p_fm_serial_number||',ser_txn_id='||nvl(l_ser_txn_id,l_txn_tmp_id));
UPDATE mtl_serial_numbers
SET GROUP_MARK_ID = l_txn_tmp_id
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
--AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND serial_number = p_fm_serial_number;
trace(SQL%ROWCOUNT||' records updated for serial number '||p_fm_serial_number||' for group_mark_id as '||l_txn_tmp_id);
trace('Error when update MSN with group_mark_id='||l_txn_tmp_id||',SN='||p_fm_serial_number);
UPDATE mtl_txn_request_lines
SET WMS_PROCESS_FLAG = 2
WHERE line_id = l_mol_list(i).move_order_line_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity + l_mol_list(i).transaction_quantity,
primary_quantity = primary_quantity + l_mol_list(i).primary_quantity,
secondary_transaction_quantity = CASE WHEN l_mol_list(i).secondary_transaction_quantity IS NOT NULL
THEN l_mol_list(i).secondary_transaction_quantity + secondary_transaction_quantity
ELSE secondary_transaction_quantity
END --INVCONV kkillams
WHERE transaction_temp_id = p_transaction_temp_id;
trace('MMTT updated for tmp_id '||p_transaction_temp_id);
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity + l_mol_list(i).transaction_quantity,
primary_quantity = primary_quantity + l_mol_list(i).primary_quantity,
secondary_quantity = CASE WHEN l_mol_list(i).secondary_transaction_quantity IS NOT NULL
THEN l_mol_list(i).secondary_transaction_quantity + secondary_quantity
ELSE secondary_quantity
END --INVCONV kkillams
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = p_lot_number;
trace('MTLT updated for tmp_id '||p_transaction_temp_id);
SELECT 1 INTO l_new_serial
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND serial_number = p_fm_serial_number;
g_lot_ser_attr.delete;
l_insert := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id)
, p_user_id => fnd_global.user_id
, p_fm_ser_num => p_fm_serial_number
, p_to_ser_num => p_fm_serial_number
, x_proc_msg => l_proc_msg
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MSNT for serial(no attr):'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
trace('MSNT record inserted for serial(no attr):'||p_fm_serial_number||',ser_txn_id='||nvl(l_ser_txn_id,l_txn_tmp_id));
l_insert := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id)
, p_user_id => fnd_global.user_id
, p_fm_ser_num => p_fm_serial_number
, p_to_ser_num => p_fm_serial_number
, x_proc_msg => l_proc_msg
, p_time_since_new =>to_number(get_column_default_value('TIME_SINCE_NEW'))
, p_cycles_since_new =>to_number(get_column_default_value('CYCLES_SINCE_NEW'))
, p_time_since_overhaul =>to_number(get_column_default_value('TIME_SINCE_OVERHAUL'))
, p_cycles_since_overhaul=>to_number(get_column_default_value('CYCLES_SINCE_OVERHAUL'))
, p_time_since_repair =>to_number(get_column_default_value('TIME_SINCE_REPAIR'))
, p_cycles_since_repair =>to_number(get_column_default_value('CYCLES_SINCE_REPAIR'))
, p_time_since_visit =>to_number(get_column_default_value('TIME_SINCE_VISIT'))
, p_cycles_since_visit =>to_number(get_column_default_value('CYCLES_SINCE_VISIT'))
, p_time_since_mark =>to_number(get_column_default_value('TIME_SINCE_MARK'))
, p_cycles_since_mark =>to_number(get_column_default_value('CYCLES_SINCE_MARK'))
, p_number_of_repairs =>to_number(get_column_default_value('NUMBER_OF_REPAIRS'))
, p_territory_code =>to_number(get_column_default_value('TERRITORY_CODE'))
, p_orgination_date =>to_date(get_column_default_value('ORIGINATION_DATE'),G_DATE_MASK)
, p_serial_attribute_category =>get_column_default_value('SERIAL_ATTRIBUTE_CATEGORY')
, p_c_attribute1 =>get_column_default_value('C_ATTRIBUTE1')
, p_c_attribute2 =>get_column_default_value('C_ATTRIBUTE2')
, p_c_attribute3 =>get_column_default_value('C_ATTRIBUTE3')
, p_c_attribute4 =>get_column_default_value('C_ATTRIBUTE4')
, p_c_attribute5 =>get_column_default_value('C_ATTRIBUTE5')
, p_c_attribute6 =>get_column_default_value('C_ATTRIBUTE6')
, p_c_attribute7 =>get_column_default_value('C_ATTRIBUTE7')
, p_c_attribute8 =>get_column_default_value('C_ATTRIBUTE8')
, p_c_attribute9 =>get_column_default_value('C_ATTRIBUTE9')
, p_c_attribute10 =>get_column_default_value('C_ATTRIBUTE10')
, p_c_attribute11 =>get_column_default_value('C_ATTRIBUTE11')
, p_c_attribute12 =>get_column_default_value('C_ATTRIBUTE12')
, p_c_attribute13 =>get_column_default_value('C_ATTRIBUTE13')
, p_c_attribute14 =>get_column_default_value('C_ATTRIBUTE14')
, p_c_attribute15 =>get_column_default_value('C_ATTRIBUTE15')
, p_c_attribute16 =>get_column_default_value('C_ATTRIBUTE16')
, p_c_attribute17 =>get_column_default_value('C_ATTRIBUTE17')
, p_c_attribute18 =>get_column_default_value('C_ATTRIBUTE18')
, p_c_attribute19 =>get_column_default_value('C_ATTRIBUTE19')
, p_c_attribute20 =>get_column_default_value('C_ATTRIBUTE20')
, p_d_attribute1 =>to_date(get_column_default_value('D_ATTRIBUTE1'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute2 =>to_date(get_column_default_value('D_ATTRIBUTE2'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute3 =>to_date(get_column_default_value('D_ATTRIBUTE3'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute4 =>to_date(get_column_default_value('D_ATTRIBUTE4'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute5 =>to_date(get_column_default_value('D_ATTRIBUTE5'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute6 =>to_date(get_column_default_value('D_ATTRIBUTE6'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute7 =>to_date(get_column_default_value('D_ATTRIBUTE7'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute8 =>to_date(get_column_default_value('D_ATTRIBUTE8'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute9 =>to_date(get_column_default_value('D_ATTRIBUTE9'),'YYYY/MM/DD:HH24:MI:SS')
, p_d_attribute10 =>to_date(get_column_default_value('D_ATTRIBUTE10'),'YYYY/MM/DD:HH24:MI:SS')
, p_n_attribute1 =>to_number(get_column_default_value('N_ATTRIBUTE1'))
, p_n_attribute2 =>to_number(get_column_default_value('N_ATTRIBUTE2'))
, p_n_attribute3 =>to_number(get_column_default_value('N_ATTRIBUTE3'))
, p_n_attribute4 =>to_number(get_column_default_value('N_ATTRIBUTE4'))
, p_n_attribute5 =>to_number(get_column_default_value('N_ATTRIBUTE5'))
, p_n_attribute6 =>to_number(get_column_default_value('N_ATTRIBUTE6'))
, p_n_attribute7 =>to_number(get_column_default_value('N_ATTRIBUTE7'))
, p_n_attribute8 =>to_number(get_column_default_value('N_ATTRIBUTE8'))
, p_n_attribute9 =>to_number(get_column_default_value('N_ATTRIBUTE9'))
, p_n_attribute10 =>to_number(get_column_default_value('N_ATTRIBUTE10'))
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MSNT for serial(with attr):'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
trace('MSNT record inserted for serial(with attr):'||p_fm_serial_number||',ser_txn_id='||nvl(l_ser_txn_id,l_txn_tmp_id));
/* l_insert := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id)
, p_user_id => fnd_global.user_id
, p_fm_ser_num => p_fm_serial_number
, p_to_ser_num => p_fm_serial_number
, x_proc_msg => l_proc_msg
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MSNT for serial:'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
trace('MSNT record inserted for serial:'||p_fm_serial_number||',ser_txn_id='||
nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id));
UPDATE mtl_serial_numbers
SET GROUP_MARK_ID = p_transaction_temp_id
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
--AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND serial_number = p_fm_serial_number;
trace(SQL%ROWCOUNT||' records updated for serial number '||p_fm_serial_number||' for group_mark_id as '||p_transaction_temp_id);
trace('Error when update MSN with group_mark_id='||l_txn_tmp_id||',SN='||p_fm_serial_number);
END IF; -- End Create new MMTT or update MMTT
UPDATE wms_packing_material_gtemp
SET selected_flag = 'Y',
quantity = quantity - inv_convert.inv_um_convert(
inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,uom,null,null),
secondary_quantity = CASE WHEN l_mol_list(i).secondary_transaction_quantity IS NOT NULL
THEN secondary_quantity - l_mol_list(i).secondary_transaction_quantity
ELSE secondary_quantity
END --INVCONV kkillams
WHERE move_order_line_id = l_mol_list(i).move_order_line_id;
SELECT 1
INTO l_mmtt_exists
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id
AND mmtt.transaction_temp_id = p_transaction_temp_id
AND mmtt.content_lpn_id IS NULL
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.transaction_uom = p_transaction_uom
AND mmtt.lpn_id = p_from_lpn_id
AND nvl(mmtt.secondary_uom_code, '@#$') = nvl(p_secondary_uom,nvl(mmtt.secondary_uom_code, '@#$'));
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_hdr_id
FROM dual;
trace('Calling inv_trx_util_pub.insert_line_trx() to insert MMTT with ');
l_insert := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id => l_txn_hdr_id
,p_item_id => null
,p_org_id => p_organization_id
,p_trx_action_id => l_txn_action_id
,p_trx_type_id => l_txn_type_id
,p_trx_src_type_id => 13
,p_trx_qty => 0
,p_pri_qty => 0
,p_uom => nvl(p_transaction_uom, ' ')
,p_secondary_trx_qty => CASE WHEN p_secondary_uom IS NOT NULL THEN 0 ELSE NULL END --INVCONV kkillams
,p_secondary_uom => nvl(p_secondary_uom, ' ') --INVCONV kkillams
,p_subinv_code => p_subinventory_code
,p_tosubinv_code => l_to_sub
,p_xfr_org_id => p_organization_id
,p_locator_id => p_locator_id
,p_tolocator_id => l_to_loc_id
,p_from_lpn_id => p_from_lpn_id
,p_cnt_lpn_id => p_content_lpn_id
,p_xfr_lpn_id => p_to_lpn_id
,p_user_id => fnd_global.user_id
,x_trx_tmp_id => l_txn_tmp_id
,x_proc_msg => l_proc_msg);
trace('done with inserting , l_insert ='||l_insert);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MMTT for content lpn ID:'||p_content_lpn_id|| 'err is '||l_proc_msg);
trace('MMTT inserted, hdr_id='||l_txn_hdr_id|| ',tmp_id='||l_txn_tmp_id);
UPDATE wms_packing_material_gtemp
SET selected_flag = 'D' -- Deleted
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);
UPDATE wms_packing_material_gtemp
SET selected_flag = 'D'
WHERE outermost_lpn_id = p_content_lpn_id;
trace('Calling inv_trx_util_pub.insert_line_trx() to insert MMTT with ');
l_insert := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id => l_txn_hdr_id
,p_item_id => p_inventory_item_id
,p_revision => p_revision
,p_org_id => p_organization_id
,p_trx_action_id => l_txn_action_id
,p_trx_type_id => l_txn_type_id
,p_trx_src_type_id => 13
,p_trx_qty => p_transaction_qty
,p_pri_qty => p_primary_qty
,p_uom => p_transaction_uom
,p_subinv_code => p_subinventory_code
,p_tosubinv_code => l_to_sub
,p_xfr_org_id => p_organization_id
,p_locator_id => p_locator_id
,p_tolocator_id => l_to_loc_id
,p_from_lpn_id => p_from_lpn_id
,p_xfr_lpn_id => p_to_lpn_id
,p_user_id => fnd_global.user_id
,p_secondary_trx_qty => p_secondary_qty
,p_secondary_uom => p_secondary_uom
,x_trx_tmp_id => l_txn_tmp_id
,x_proc_msg => l_proc_msg);
trace('done with inserting , l_insert ='||l_insert);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MMTT for item id:'||p_inventory_item_id|| 'err is '||l_proc_msg);
trace('MMTT inserted, tmp_id='||l_txn_tmp_id);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity + p_transaction_qty,
primary_quantity = primary_quantity + p_primary_qty,
secondary_transaction_quantity =
decode(secondary_transaction_quantity, NULL, NULL, secondary_transaction_quantity+p_secondary_qty)
WHERE transaction_temp_id = p_transaction_temp_id;
trace('MMTT updated for tmp_id '||p_transaction_temp_id);
l_insert := inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id => l_txn_tmp_id
, p_user_id => fnd_global.user_id
, p_lot_number => p_lot_number
, p_trx_qty => p_transaction_qty
, p_pri_qty => p_primary_qty
, p_secondary_qty => p_secondary_qty
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code --INVCONV kkillams
, x_ser_trx_id => l_ser_txn_id
, x_proc_msg => l_proc_msg
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MTLT for lot:'||p_lot_number||',l_proc_msg='||l_proc_msg);
trace('MTLT record inserted for lot:'||p_lot_number||',ser_txn_id='||l_ser_txn_id);
l_insert := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => l_ser_txn_id
, p_user_id => fnd_global.user_id
, p_fm_ser_num => p_fm_serial_number
, p_to_ser_num => nvl(p_to_serial_number, p_fm_serial_number)
, x_proc_msg => l_proc_msg
);
IF l_insert <> 0 THEN
IF l_debug = 1 THEN
trace('Error when inserting MSNT for fm_serial:'||p_fm_serial_number||',to_serial:'||p_to_serial_number||',l_proc_msg='||l_proc_msg);
trace('MSNT record inserted for fm_serial:'||p_fm_serial_number||',to_serial:'||p_to_serial_number||',ser_txn_id='||l_ser_txn_id);
SELECT sum(quantity)
INTO l_sum_qty
FROM wms_packing_material_gtemp
WHERE inventory_item_id = p_inventory_item_id
AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
AND subinventory = p_subinventory_code
AND locator_id = p_locator_id
AND lpn_id = p_from_lpn_id;
UPDATE wms_packing_material_gtemp
SET selected_flag = 'Y'
,quantity = 0
WHERE inventory_item_id = p_inventory_item_id
AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
AND subinventory = p_subinventory_code
AND locator_id = p_locator_id
AND lpn_id = p_from_lpn_id;
ELSE -- quantities are not the same, so you must loop and update the records you can.
-- Loop thru records for that part and reduce qty by correct amount.
FOR c1 IN get_gtemp LOOP
IF l_process_qty <= 0 THEN
exit;
l_update_qty := c1.quantity;
l_update_qty := l_process_qty;
trace('l_update_qty ' || l_update_qty);
UPDATE wms_packing_material_gtemp
SET selected_flag = 'Y'
,quantity = quantity - l_update_qty
WHERE inventory_item_id = p_inventory_item_id
AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
AND subinventory = p_subinventory_code
AND locator_id = p_locator_id
AND lpn_id = p_from_lpn_id
AND order_line_num = c1.order_line_num;
UPDATE wms_packing_material_gtemp
SET selected_flag = 'Y'
,quantity = quantity - inv_convert.inv_um_convert(
inventory_item_id,null,p_transaction_qty,p_transaction_uom,uom,null,null)
WHERE inventory_item_id = p_inventory_item_id
AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
AND subinventory = p_subinventory_code
AND locator_id = p_locator_id
AND lpn_id = p_from_lpn_id
AND rownum<2;
trace('Error when updating wms_packing_material_gtemp for item, only one record should be updated');
trace('wms_packing_material_gtemp updated, row_count='||l_row_count);
* Procedure to delete MMTT/MTLT/MSNT record
* For a pack/split/unpack transaction
* This is used when user choose to do a UNDO
*******************************************/
PROCEDURE delete_txn(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_transaction_header_id IN NUMBER
, p_transaction_temp_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_serial_number IN VARCHAR2
, p_quantity IN NUMBER DEFAULT NULL
, p_uom IN VARCHAR2 DEFAULT NULL
) IS
l_txn_hdr_id NUMBER;
trace('In wms_packing_workbench_pvt.delete_txn');
SELECT content_lpn_id, inventory_item_id, primary_quantity, transaction_uom, secondary_uom_code
INTO l_cont_lpn_id, l_item_id, l_mmtt_qty, l_txn_uom
, l_sec_uom --INCONV kkillams
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id = p_transaction_temp_id;
SELECT mtlt_row_id, mtlt_qty, msnt_row_id
INTO l_mtlt_row_id, l_mtlt_qty, l_msnt_row_id
FROM
(SELECT mtlt.rowid mtlt_row_id, mtlt.primary_quantity mtlt_qty, msnt.rowid msnt_row_id
FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.transaction_temp_id = p_transaction_temp_id
AND mtlt.lot_number = p_lot_number
AND msnt.fm_serial_number = p_serial_number
order by msnt.creation_date desc) t
WHERE rownum < 2;
SELECT t.mtlt_row_id, t.mtlt_qty
INTO l_mtlt_row_id, l_mtlt_qty
FROM
(SELECT rowid mtlt_row_id, primary_quantity mtlt_qty
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = p_lot_number
AND primary_quantity = p_quantity
order by creation_date desc) t
WHERE rownum < 2;
SELECT t.msnt_row_id
INTO l_msnt_row_id
FROM
(SELECT rowid msnt_row_id
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND fm_serial_number = p_serial_number
order by creation_date desc) t
WHERE rownum < 2;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
trace(l_row_count||' rows of MMTT deleted with tmp_id '||p_transaction_temp_id);
DELETE mtl_serial_numbers_temp
WHERE rowid = l_msnt_row_id;
trace(l_row_count||' rows of MSNT deleted with row_id '||l_msnt_row_id);
DELETE mtl_transaction_lots_temp
WHERE rowid = l_mtlt_row_id;
trace(l_row_count||' rows of MTLT deleted with row_id '||l_mtlt_row_id);
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity - abs(p_quantity)
,transaction_quantity = inv_convert.inv_um_convert(
l_item_id,null,primary_quantity - abs(p_quantity),p_uom,l_txn_uom,null,null)
WHERE rowid = l_mtlt_row_id;
trace(l_row_count||' rows of MTLT updated with row_id '||l_mtlt_row_id);
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
trace(l_row_count||' rows of MMTT deleted with tmp_id '||p_transaction_temp_id);
UPDATE mtl_material_transactions_temp
SET primary_quantity = primary_quantity - abs(p_quantity)
,transaction_quantity = inv_convert.inv_um_convert(
l_item_id,null,primary_quantity - abs(p_quantity),p_uom,l_txn_uom,null,null)
--INVCONV kkillams
,secondary_transaction_quantity = CASE WHEN secondary_uom_code IS NOT NULL THEN
inv_convert.inv_um_convert(l_item_id,
null,
primary_quantity - abs(p_quantity),
p_uom,
l_sec_uom,null,null)
ELSE NULL END
WHERE transaction_temp_id = p_transaction_temp_id;
trace(l_row_count||' rows of MMTT updated with tmp_id '||p_transaction_temp_id);
trace('Error in delete_txn(), progress='||l_progress);
END delete_txn;
PROCEDURE update_kit_model_info
( p_kit_item_id IN NUMBER
, p_component_item_id IN NUMBER
, p_top_model_line_id IN NUMBER
) IS
CURSOR c_existing_kits_for_component IS
SELECT DISTINCT top_model_line_id, kit_item_id
FROM wms_packing_kitting_gtemp
WHERE component_item_id = p_component_item_id;
SELECT min(floor(decode(packed_qty_disp, '*',0,NULL,0,to_number(packed_qty_disp))/bom_qty)) kit_qty
INTO l_kit_packed_qty
FROM wms_packing_kitting_gtemp
WHERE kit_item_id = p_kit_item_id
AND top_model_line_id = p_top_model_line_id
AND component_item_id IS NOT NULL;
SELECT order_qty INTO l_kit_order_qty
FROM wms_packing_kitting_gtemp
WHERE kit_item_id = p_kit_item_id
AND top_model_line_id = p_top_model_line_id
AND component_item_id IS NULL;
UPDATE wms_packing_kitting_gtemp
SET packed_qty = l_kit_packed_qty
, packed_qty_disp = decode(l_kit_packed_qty,0,NULL,to_char(l_kit_packed_qty))
, completed_flag = l_completed_flag
WHERE kit_item_id = p_kit_item_id
AND top_model_line_id = p_top_model_line_id
AND component_item_id IS NULL;
trace(SQL%ROWCOUNT||' rows of wms_packing_kitting_gtemp updated');
UPDATE wms_packing_kitting_gtemp
SET completed_flag = l_completed_flag
WHERE kit_item_id = p_kit_item_id
AND top_model_line_id = p_top_model_line_id;
trace(SQL%ROWCOUNT||' rows of wms_packing_kitting_gtemp updated for completed_flag of '||l_completed_flag);
SELECT min(floor(decode(packed_qty_disp, '*',0,NULL,0,to_number(packed_qty_disp))/bom_qty)) kit_qty
INTO l_kit_packed_qty
FROM wms_packing_kitting_gtemp
WHERE kit_item_id = l_existing_kits_for_component.kit_item_id
AND top_model_line_id = l_existing_kits_for_component.top_model_line_id
AND component_item_id IS NOT NULL;
SELECT order_qty INTO l_kit_order_qty
FROM wms_packing_kitting_gtemp
WHERE kit_item_id = l_existing_kits_for_component.kit_item_id
AND top_model_line_id = l_existing_kits_for_component.top_model_line_id
AND component_item_id IS NULL;
UPDATE wms_packing_kitting_gtemp
SET packed_qty = l_kit_packed_qty
, packed_qty_disp = decode(l_kit_packed_qty,0,NULL,to_char(l_kit_packed_qty))
, completed_flag = l_completed_flag
WHERE kit_item_id = l_existing_kits_for_component.kit_item_id
AND top_model_line_id = l_existing_kits_for_component.top_model_line_id
AND component_item_id IS NULL;
trace(SQL%ROWCOUNT||' rows of wms_packing_kitting_gtemp updated');
UPDATE wms_packing_kitting_gtemp
SET completed_flag = l_completed_flag
WHERE kit_item_id = l_existing_kits_for_component.kit_item_id
AND top_model_line_id = l_existing_kits_for_component.top_model_line_id;
trace(SQL%ROWCOUNT||' rows of wms_packing_kitting_gtemp updated for completed_flag of '||l_completed_flag);
END update_kit_model_info;
* Update the kit temp table
* p_packed_qty: Given packed_qty
When p_action = 'A'(Add): Add the p_packed_qty to existing p_packed_qty
p_action = 'U'(Update): Update the gtemp.packed_qty as p_packed_qty
p_disp_packed_qty:
When is '*', update gtemp.packed_qty_disp as '*'
When is NULL, update gtemp.packed_qty_disp as NULL
When not null and not '*', update gtemp.packed_qty_disp as gtemp.packed_qty
****************************************************/
PROCEDURE update_kit_info
( p_kit_item_id IN NUMBER
, p_component_item_id IN NUMBER
, p_top_model_line_id IN NUMBER
, p_packed_qty IN NUMBER DEFAULT NULL
, p_disp_packed_qty IN VARCHAR2 DEFAULT NULL
, p_action IN VARCHAR2
) IS
l_packed_qty NUMBER;
CURSOR c_update_QTY_common_comp IS
SELECT packed_qty,order_qty,kit_item_id,component_item_id,top_model_line_id FROM wms_packing_kitting_gtemp
WHERE component_item_id = p_component_item_id
AND ((packed_qty <> order_qty AND packed_qty IS NOT NULL) OR
packed_qty IS NULL);
SELECT
packed_qty,order_qty,kit_item_id,component_item_id,top_model_line_id,packed_qty_disp
FROM wms_packing_kitting_gtemp;
l_update_qty_common_comp c_update_qty_common_comp%ROWTYPE;
trace('In update_kit_info');
UPDATE wms_packing_kitting_gtemp
SET packed_qty = least(order_qty,decode(p_action, 'A', nvl(packed_qty,0) + p_packed_qty, p_packed_qty))
, packed_qty_disp = Decode(p_disp_packed_qty, '*', '*', NULL, NULL,
to_char(least(order_qty,decode(p_action, 'A', nvl(packed_qty,0) + p_packed_qty, p_packed_qty))))
WHERE kit_item_id = p_kit_item_id
AND component_item_id = p_component_item_id
AND top_model_line_id = p_top_model_line_id;
trace('updated kit '|| p_kit_item_id||' and component '||p_component_item_id||' top_model_line_id='||p_top_model_line_id);
trace('NUMBER OF ROWS UPDATE :'||SQL%rowcount);
OPEN c_update_QTY_common_comp;
FETCH c_update_QTY_common_comp INTO l_update_qty_common_comp;
IF c_update_QTY_common_comp%notfound THEN
CLOSE c_update_qty_common_comp;
trace('Inside the loop to update the qty recursively in WPKG');
IF l_update_qty_common_comp.packed_qty IS NULL THEN
l_update_qty_common_comp.packed_qty := 0;
l_surplus_qty := (l_remaining_qty_to_pack + l_update_qty_common_comp.packed_qty) - l_update_qty_common_comp.order_qty;
trace('l_update_qty_common_comp.packed_qt :' ||l_update_qty_common_comp.packed_qty);
trace('l_update_qty_common_comp.order_qty :'|| l_update_qty_common_comp.order_qty);
UPDATE wms_packing_kitting_gtemp
SET packed_qty = (l_remaining_qty_to_pack+l_update_qty_common_comp.packed_qty)
--, packed_qty_disp = '*'
WHERE kit_item_id = l_update_qty_common_comp.kit_item_id
AND component_item_id = l_update_qty_common_comp.component_item_id
AND top_model_line_id = l_update_qty_common_comp.top_model_line_id;
CLOSE c_update_qty_common_comp;
(l_update_qty_common_comp.order_qty - l_update_qty_common_comp.packed_qty);
UPDATE wms_packing_kitting_gtemp
SET packed_qty = l_update_qty_common_comp.order_qty
--, packed_qty_disp = '*'
WHERE kit_item_id = l_update_qty_common_comp.kit_item_id
AND component_item_id = l_update_qty_common_comp.component_item_id
AND top_model_line_id = l_update_qty_common_comp.top_model_line_id;
UPDATE wms_packing_kitting_gtemp
SET packed_qty_disp = '*'
WHERE component_item_id = p_component_item_id;
update_kit_model_info
( p_kit_item_id => p_kit_item_id
, p_component_item_id => p_component_item_id
, p_top_model_line_id => p_top_model_line_id);
END update_kit_info;
CURSOR c_update_disp_qty_common_comp IS
SELECT packed_qty,kit_item_id,component_item_id,top_model_line_id FROM wms_packing_kitting_gtemp
WHERE component_item_id = p_inventory_item_id
AND packed_qty = order_qty
AND packed_qty IS NOT NULL;
l_update_disp_qty_common_comp c_update_disp_qty_common_comp%ROWTYPE;
l_new_inserted_kit_cnt NUMBER := 0;
l_new_inserted_kit_cnt := 0;
insert_kit_info
( p_kit_item_id =>l_kit_list(1).kit_item_id
, p_component_item_id =>p_inventory_item_id
, p_top_model_line_id => l_kit_list(1).top_model_line_id
, p_packed_qty => p_quantity
, p_disp_packed_qty => p_quantity
);
trace('Kit 1 is a new kit, inserted information for kit ID '||l_kit_list(1).kit_item_id);
update_kit_info
( p_kit_item_id =>l_kit_list(1).kit_item_id
, p_component_item_id =>p_inventory_item_id
, p_top_model_line_id => l_kit_list(1).top_model_line_id
, p_packed_qty => p_quantity
, p_disp_packed_qty => to_char(p_quantity)
, p_action => 'A' -- Add
);
trace('Kit 1 exists already, updated kit information for kit ID '||l_kit_list(1).kit_item_id);
l_kit_component_list.DELETE;
update_kit_info
(p_kit_item_id => l_kit_list(1).kit_item_id
, p_component_item_id =>
l_kit_component_list(i).component_item_id
, p_top_model_line_id => l_kit_list(1).top_model_line_id
, p_packed_qty => l_kit_component_list(i).packed_qty
, p_disp_packed_qty => to_char(l_kit_component_list(i).packed_qty)
, p_action => 'U'); -- Update
trace('updated kit info for item '|| l_kit_component_list(i).component_item_id
||' in kit '||l_kit_list(1).kit_item_id);
l_other_kit_list.DELETE;
update_kit_info
(p_kit_item_id => l_other_kit_list(j).kit_item_id
, p_component_item_id =>l_kit_component_list(i).component_item_id
, p_top_model_line_id => l_kit_list(j).top_model_line_id
, p_packed_qty => NULL
, p_disp_packed_qty => NULL
, p_action => 'U'); -- Update
trace('Updated the packedQty and dispQty as NULL for kit '||l_other_kit_list(j).kit_item_id||', component '||l_kit_component_list(i).component_item_id);
END IF; -- End if to update kit info
update_kit_info
(p_kit_item_id=>l_kit_list(i).kit_item_id
,p_component_item_id=> p_inventory_item_id
,p_top_model_line_id => l_kit_list(i).top_model_line_id
,p_packed_qty=> 0
,p_disp_packed_qty => '*'
,p_action => 'A'); -- Add
update_kit_info
(p_kit_item_id=>l_kit_list(i).kit_item_id
,p_component_item_id=> p_inventory_item_id
,p_top_model_line_id => l_kit_list(i).top_model_line_id
,p_packed_qty=>p_quantity
,p_disp_packed_qty => to_char(p_quantity)
,p_action => 'A'); -- Add
update_kit_info
(p_kit_item_id=>NULL--l_kit_list(i).kit_item_id
,p_component_item_id=> p_inventory_item_id
,p_top_model_line_id => NULL--l_kit_list(i).top_model_line_id
,p_packed_qty=> p_quantity
,p_disp_packed_qty => '*'
,p_action => 'A');
update_kit_info
(p_kit_item_id=>l_kit_list(i).kit_item_id
,p_component_item_id=> p_inventory_item_id
,p_top_model_line_id => l_kit_list(i).top_model_line_id
,p_packed_qty=> 0
,p_disp_packed_qty => '*'
,p_action => 'A');
trace(' Kit '||i||' is new unique kit for the item, insert_kit_info with packed_qty ');
insert_kit_info
(p_kit_item_id =>l_kit_list(i).kit_item_id
, p_component_item_id =>p_inventory_item_id
, p_top_model_line_id => l_kit_list(i).top_model_line_id
, p_packed_qty => p_quantity
, p_disp_packed_qty => p_quantity);
trace(' Kit '||i||' is new kit, insert_kit_info with packed_qty and *');
insert_kit_info
(p_kit_item_id =>l_kit_list(i).kit_item_id
, p_component_item_id =>p_inventory_item_id
, p_top_model_line_id => l_kit_list(i).top_model_line_id
, p_packed_qty => NULL
, p_disp_packed_qty => '*'
);
update_kit_info
(p_kit_item_id=>NULL--l_kit_list(i).kit_item_id
,p_component_item_id=> p_inventory_item_id
,p_top_model_line_id => NULL--l_kit_list(i).top_model_line_id
,p_packed_qty=> p_quantity
,p_disp_packed_qty => '*'
,p_action => 'A');
SELECT SUM(packed_qty), SUM(order_qty) INTO l_pack_comp_qty_total,l_ord_comp_qty_total
FROM wms_packing_kitting_gtemp
WHERE component_item_id = p_inventory_item_id
GROUP BY component_item_id;
SELECT 1 INTO l_common_qty_filled FROM dual WHERE exists
(SELECT 1
FROM wms_packing_kitting_gtemp
WHERE component_item_id = p_inventory_item_id
AND ((packed_qty <> order_qty AND packed_qty IS NOT
NULL) OR (packed_qty IS NULL) ));
OPEN c_update_disp_qty_common_comp;
FETCH c_update_disp_qty_common_comp INTO l_update_disp_qty_common_comp;
IF c_update_disp_qty_common_comp%notfound THEN
CLOSE c_update_disp_qty_common_comp;
UPDATE wms_packing_kitting_gtemp
SET packed_qty_disp = l_update_disp_qty_common_comp.packed_qty
WHERE kit_item_id = l_update_disp_qty_common_comp.kit_item_id
AND component_item_id = l_update_disp_qty_common_comp.component_item_id
AND top_model_line_id = l_update_disp_qty_common_comp.top_model_line_id;
update_kit_model_info
( p_kit_item_id => NULL
, p_component_item_id => p_inventory_item_id
, p_top_model_line_id => NULL);
SELECT 1 INTO l_exist
FROM dual
WHERE exists(
SELECT 1 FROM wms_packing_kitting_gtemp
WHERE kit_item_id = p_kit_id
AND component_item_id IS NOT NULL
AND packed_qty IS NOT NULL
AND packed_qty_disp = '*');
SELECT COUNT(1) INTO l_cnt FROM wms_packing_kitting_gtemp
WHERE component_item_id = p_component_id;