API for Manual allocation of lot number when shipping lot
hello this API use for control lot number when you ship. normally oracle select lot number as FIFO based so if we want to ship lot which based on order number then we need to manual allocation of that lot number
here we suppose manufacturing unit is maxico and shipping unit means supplier is USA
step 1. insert in a table
CREATE OR REPLACE procedure APPS.inst_lot_int_tab (p_po_header_id in number,p_org_id in number ,p_pack_list in varchar2)
is
CURSOR get_po_line_det
IS
SELECT poh.vendor_id, pol.item_id, pol.attribute13 order_line_id,
pol.item_id inventory_item_id, pll.*
FROM po_headers_all poh, po_lines_all pol, po_line_locations_all pll
WHERE 1 = 1
AND poh.po_header_id = pol.po_header_id
AND pol.po_header_id = pll.po_header_id
AND pol.po_line_id = pll.po_line_id
AND poh.po_header_id = p_po_header_id
AND poh.org_id = p_org_id;
CURSOR cur_lot_det
IS
SELECT DISTINCT fraa.roll_number, wdd.shipped_quantity
FROM ems_packing_list emsh,
fil_roll_assgn_attb fraa,
wsh_delivery_details wdd,
xxuflx_trd_ship_lines_all xtl
WHERE NVL (fraa.diverted_header_id, fraa.header_id) =
emsh.order_header_id
AND wdd.source_header_id =
NVL (fraa.diverted_header_id, fraa.header_id)
AND xtl.receive_flag = 'Y'
AND xtl.ship_quantity IS NULL
AND xtl.packing_list_number = p_pack_list --- packing list number of mafacuring site
AND xtl.roll_number = fraa.roll_number
AND xtl.packing_list_number = emsh.packing_list_number
AND xtl.item_code = fraa.item_code
AND xtl.roll_number = wdd.lot_number;
BEGIN
FOR line_rec IN get_po_line_det
LOOP
--
FOR lot_rec IN cur_lot_det
LOOP
--
DBMS_OUTPUT.put_line ('insert shipment lot rec');
--
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id, source_code, product_code,
product_transaction_id,
last_update_date,
last_updated_by,
creation_date, created_by,
lot_number,
transaction_quantity, primary_quantity, process_flag
)
VALUES (mtl_material_transactions_s.NEXTVAL
--transaction_interface_id
, 'PO', 'RCV', -- product_code
rcv_transactions_interface_s.CURRVAL,
SYSDATE -- last_update_date
,
6055 -- last_updated_by
,
SYSDATE -- creation_date
,6055 -- created_by
,
lot_rec.roll_number --LOT_NUMBER
,
lot_rec.shipped_quantity
--TRANSACTION_QUANTITY
, lot_rec.shipped_quantity
--primary_quantity,
, 1
);
END LOOP;
--
END LOOP;
END;
/
now you check new packing list number in ems_packing_list table
3. custom table xxuflex_fil_roll_assgn_attb
CREATE GLOBAL TEMPORARY TABLE APPS.XXUFLEX_FIL_ROLL_ASSGN_ATTB
(
ROLL_NUMBER VARCHAR2(64 BYTE),
HEADER_ID NUMBER,
ORG_ID NUMBER,
LINE_ID NUMBER,
ITEM_CODE VARCHAR2(30 BYTE),
ACTUAL_LENGTH NUMBER(10),
OPT_DENS NUMBER(5,2),
TRTMNT_IN VARCHAR2(3 BYTE),
TRTMNT_OUT VARCHAR2(3 BYTE),
CORE_ID NUMBER(5),
ROLL_WIDTH NUMBER(6),
GROSS_ROLL_WT NUMBER(6,1),
NET_ROLL_WT NUMBER(6,1),
ROLL_GRADE VARCHAR2(5 BYTE),
BATCH_ID NUMBER(10),
BATCH_NO VARCHAR2(32 BYTE),
LOT_NO VARCHAR2(32 BYTE),
SUBLOT_NO VARCHAR2(32 BYTE),
GRADE_REASON VARCHAR2(30 BYTE),
NUMBER_OF_JOINTS NUMBER,
FORMULA_CLASS VARCHAR2(8 BYTE),
LENGTH NUMBER(5),
OUT_DIA NUMBER(7,2),
ORDER_NUMBER NUMBER,
DIVERTED_ORDER_NUMBER NUMBER,
DIVERTED_HEADER_ID NUMBER,
DIVERTED_LINE_ID NUMBER,
WAREHOUSE VARCHAR2(4 BYTE),
LINE_SHIPMENT_NUMBER VARCHAR2(10 BYTE),
JUMBO_LOT_NO VARCHAR2(1000 BYTE),
JUMBO_SUBLOT_NO VARCHAR2(1000 BYTE),
JUMBO_WT VARCHAR2(1000 BYTE),
JUMBO_WIDTH VARCHAR2(1000 BYTE),
DIVERTED_ORDER_DATE DATE,
ROLL_ASSGN_DATE DATE,
NET_WT_REMAINING NUMBER,
JUMBO_ONHAND_QTY VARCHAR2(1000 BYTE),
JUMBO_ONHAND_LENGTH VARCHAR2(1000 BYTE),
LOT_ID NUMBER,
ITEM_ID NUMBER NOT NULL,
CREATED_BY NUMBER(15),
CREATION_DATE DATE,
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_LOGIN NUMBER(15),
CORE_DETAIL VARCHAR2(100 BYTE),
CORE_WT NUMBER(30),
PRINT_FLAG CHAR(1 BYTE),
ROLL_LOCATION VARCHAR2(50 BYTE),
PK VARCHAR2(1 BYTE),
CHK_BOX VARCHAR2(3 BYTE)
)
ON COMMIT PRESERVE ROWS
NOCACHE;
4.CONTROL_LOT
CREATE OR REPLACE PROCEDURE APPS.control_lot_pk (
p_packing_list_number IN VARCHAR,
p_us_packing_list_number IN VARCHAR,
x_return_message OUT VARCHAR2,
x_return_status OUT VARCHAR2
)
IS
CURSOR cur_rec
IS
SELECT DISTINCT NVL (fraa.diverted_header_id, fraa.header_id)
header_id,
NVL (fraa.diverted_line_id, fraa.line_id) line_id
FROM ems_packing_list emsh,
ems_pallet_number emsl,
ems_assign_lot eal,
xxuflex_fil_roll_assgn_attb fraa
WHERE emsh.header_id = emsl.header_id
AND emsl.line_id = eal.line_id
AND packing_list_number = p_packing_list_number
-- AND NVL (fraa.diverted_header_id, fraa.header_id) = emsh.order_header_id
AND roll_number = lot_sublot_number
r;
CURSOR get_delivery_det (
p_source_header_id IN NUMBER,
p_source_line_id IN NUMBER
)
IS
SELECT wdd.*
FROM wsh_delivery_details wdd --, oe_order_lines_all ola
WHERE source_header_id = p_source_header_id
AND wdd.source_line_id = get_line_id_pk(p_source_line_id)
AND move_order_line_id IS NULL
AND released_status IN ('R', 'B')
;
--
l_return_status VARCHAR2 (10) := 'S';
l_msg_count NUMBER (15);
l_msg_data VARCHAR2 (4000);
--
--
p_new_batch_id NUMBER;
l_rule_id NUMBER;
l_rule_name VARCHAR2 (2000);
l_batch_prefix VARCHAR2 (2000);
l_batch_info_rec wsh_picking_batches_pub.batch_info_rec;
l_request_id NUMBER;
--
lv_org_id NUMBER;
lv_order_number NUMBER;
--
lv_process VARCHAR2 (100);
lv_detail_exists VARCHAR2 (1) := 'N';
lv_order_header_id NUMBER;
l_release_batch VARCHAR2 (1) := 'N';
--
BEGIN
--
dbms_output.put_line ('pick release started ' || p_packing_list_number);
--
--
BEGIN
SELECT order_header_id
INTO lv_order_header_id
FROM
(SELECT order_header_id from ems_packing_list
WHERE packing_list_number = p_packing_list_number
AND org_id IN (406,82)
;
---= source_org_id;
EXCEPTION
WHEN OTHERS
THEN
lv_order_header_id := 0;
END;
--
dbms_output.put_line ('Source Header ID ' || lv_order_header_id);
dbms_output.put_line ('Insert data into fil roll assgn attbt');
DELETE FROM xxuflex_fil_roll_assgn_attb;
INSERT INTO xxuflex_fil_roll_assgn_attb
(roll_number,
header_id,
org_id,
line_id,
item_code,
actual_length,
opt_dens,
trtmnt_in,
trtmnt_out,
core_id,
roll_width,
gross_roll_wt,
net_roll_wt,
roll_grade,
batch_id,
batch_no,
lot_no,
sublot_no,
grade_reason,
number_of_joints,
formula_class,
LENGTH,
out_dia,
order_number,
item_id,
line_shipment_number
)
SELECT distinct roll_number,
oohus.header_id,
oohus.org_id,
oolus.line_id,
item_code,
actual_length,
opt_dens,
trtmnt_in,
trtmnt_out,
core_id,
roll_width,
gross_roll_wt,
net_roll_wt,
roll_grade,
NULL batch_id,
NULL batch_no,
lot_no,
sublot_no,
grade_reason,
number_of_joints,
formula_class,
LENGTH,
out_dia,
oohus.order_number,
oolus.inventory_item_id,
(oolus.line_number || '.' || oolus.shipment_number
)
FROM fil_roll_assgn_attb fraa,
oe_order_headers_all oohn,
oe_order_lines_all ooln,
oe_order_headers_all oohus,
oe_order_lines_all oolus,
XXULX_TRADE_ORDER_LINES xtl
WHERE 1 = 1
AND NVL (fraa.diverted_header_id, fraa.header_id) = oohn.header_id
and NVL (fraa.diverted_header_id, fraa.header_id) = xtl.MGF_OE_HEADER_ID
AND NVL (fraa.diverted_line_id, fraa.line_id) = ooln.line_id
AND oohn.header_id = ooln.header_id
AND xtl.mgf_oe_header_id=oohn.header_id
and xtl.mgf_oe_order_number=oohn.order_number
and xtl.oe_header_id=oohus.HEADER_ID
and xtl.org_id=oohus.org_id
and xtl.mgf_org_id=oohn.org_id
AND oohus.HEADER_ID=oolus.header_id
AND fraa.org_id IN (406,82)---= source_org_id
AND NVL (fraa.diverted_header_id, fraa.header_id) =lv_order_header_id
and fraa.roll_number in (select LOT_SUBLOT_NUMBER from ems_assign_lot where header_id=
(select HEADER_ID from ems_packing_list where packing_list_number=p_us_packing_list_number))
union
SELECT roll_number,
oohus.header_id,
oohus.org_id,
oolus.line_id,
item_code,
actual_length,
opt_dens,
trtmnt_in,
trtmnt_out,
core_id,
roll_width,
gross_roll_wt,
net_roll_wt,
roll_grade,
NULL batch_id,
NULL batch_no,
lot_no,
sublot_no,
grade_reason,
number_of_joints,
formula_class,
LENGTH,
out_dia,
oohus.order_number,
fraa.item_id,
(oolus.line_number || '.' || oolus.shipment_number)
FROM fil_roll_assgn_attb fraa,
oe_order_headers_all oohm,
oe_order_lines_all oolm,
oe_order_headers_all oohus,
oe_order_lines_all oolus,
XXULX_TRADE_ORDER_LINES xtl
WHERE 1 = 1
AND NVL (fraa.diverted_header_id, fraa.header_id) = oohm.header_id
and NVL (fraa.diverted_header_id, fraa.header_id) = xtl.MGF_OE_HEADER_ID
AND NVL (fraa.diverted_line_id, fraa.line_id) = oolm.line_id
AND oohm.header_id = oolm.header_id
AND xtl.mgf_oe_header_id=oohm.header_id
and xtl.mgf_oe_order_number=oohm.order_number
and xtl.oe_header_id=oohus.HEADER_ID
and xtl.org_id=oohus.org_id
and xtl.mgf_org_id=oohm.org_id
AND oohus.HEADER_ID=oolus.header_id
AND fraa.org_id IN (406,241)---= source_org_id
AND NVL (fraa.diverted_header_id, fraa.header_id) =lv_order_header_id
and roll_number in (select LOT_SUBLOT_NUMBER from ems_assign_lot where header_id=
(select HEADER_ID from ems_packing_list where packing_list_number=p_us_packing_list_number));
--
dbms_output.put_line ('Total rolls inserted ' || SQL%ROWCOUNT);
dbms_output.put_line ('Start pick release');
FOR l_rec IN cur_rec
LOOP
FOR l_get_delivery_det IN get_delivery_det (l_rec.header_id,
l_rec.line_id
)
LOOP
l_batch_info_rec.backorders_only_flag := 'I';
l_batch_info_rec.delivery_detail_id := -1;
l_batch_prefix := NULL;
--
lv_process := 'Pick Release';
dbms_output.put_line (lv_process||'start');
wsh_picking_batches_pub.create_batch
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rule_id => l_rule_id,
p_rule_name => l_rule_name,
p_batch_rec => l_batch_info_rec,
p_batch_prefix => l_batch_prefix,
x_batch_id => p_new_batch_id
);
lv_detail_exists := 'Y';
IF l_get_delivery_det.batch_id IS NULL
THEN
UPDATE wsh_delivery_details
SET batch_id = p_new_batch_id
WHERE delivery_detail_id =
l_get_delivery_det.delivery_detail_id;
INSERT INTO xxfil_batch_gt
VALUES (p_new_batch_id
);
l_release_batch := 'Y';
ELSE
INSERT INTO xxfil_batch_gt
VALUES (l_get_delivery_det.batch_id
);
END IF;
IF lv_detail_exists = 'Y' AND l_release_batch = 'Y'
THEN
lv_process := 'Release Batch';
dbms_output.put_line (lv_process || p_new_batch_id);
-- Release the batch Created Above
wsh_picking_batches_pub.release_batch
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_id => p_new_batch_id,
p_batch_name => NULL,
p_log_level => 1,
p_release_mode => 'ONLINE',
-- (ONLINE or CONCURRENT)
x_request_id => l_request_id
);
dbms_output.put_line ('Batch Released Status ' || l_return_status);
IF l_return_status != 'E'
THEN
NULL;
ELSE
fnd_file.put_line (fnd_file.LOG,
'Message count ' || l_msg_count
);
wsh_util_core.get_messages ('Y',
l_msg_data,
l_msg_data,
l_msg_count
);
ROLLBACK;
END IF;
ELSE
l_return_status := 'S';
l_msg_data := NULL;
END IF;
IF l_return_status <> 'E'
THEN
lv_process := 'Update Lots';
l_return_status := NULL;
l_msg_data := NULL;
dbms_output.put_line (lv_process);
BEGIN
SELECT oeh.org_id,
oeh.order_number
INTO lv_org_id,
lv_order_number
FROM ems_packing_list ems, oe_order_headers_all oeh
WHERE packing_list_number =p_us_packing_list_number
AND ems.order_number = oeh.order_number;
EXCEPTION
WHEN OTHERS
THEN
lv_org_id := 0;
lv_order_number := 0;
END;
dbms_output.put_line ('Generate Lots ' || lv_order_number);
/* control_lot_num (lv_org_id,
lv_order_number,
p_us_packing_list_number,
l_msg_data,
l_return_status
);*/
END IF;
dbms_output.put_line ('After Generate Lots ' || l_return_status || l_msg_data);
DELETE FROM mtl_reservations
WHERE inventory_item_id =
l_get_delivery_det.inventory_item_id
AND lot_number IS NULL;
END LOOP;
END LOOP;
x_return_status := l_return_status;
x_return_message := l_msg_data;
EXCEPTION
WHEN OTHERS
THEN
x_return_message := lv_process || SQLERRM;
x_return_status := 'E';
END ;
/
5.CONTROL_LOT_NUM
CREATE OR REPLACE PROCEDURE APPS.control_lot_num (
p_org_id NUMBER, ---241
p_so_number NUMBER, ---311111180605
p_packing_list_number IN VARCHAR2,
p_move_order_id in number, ---311111180605-002
x_return_message OUT VARCHAR2,
x_return_status OUT VARCHAR2
)
AS
--
--
l_error_flag VARCHAR2 (5) := 'N';
l_return_status VARCHAR2 (500);
l_count NUMBER := 0;
l_proc_msg VARCHAR2 (4000);
l_serial_transaction_temp_id NUMBER;
l_insert_alloc NUMBER;
l_user_id NUMBER := fnd_global.user_id;
ctr NUMBER := 0;
x_trx_qty NUMBER := 0;
x_trx_id NUMBER := 0;
x_trx_id_p NUMBER := 0;
--
CURSOR cur_alloc
IS
SELECT inventory_item_id,
organization_id,
demand_source_line_id,
primary_uom_code,
trx_qty,
lot_number,
transaction_temp_id,
move_order_line_id,
locator_id
FROM (SELECT distinct oola.inventory_item_id,
oola.ship_from_org_id organization_id,
oola.line_id demand_source_line_id,
oola.order_quantity_uom primary_uom_code,
fraa.net_roll_wt trx_qty,
fraa.roll_number lot_number,
mmtt.transaction_temp_id,
move_order_line_id,
moq.locator_id
FROM xxuflex_fil_roll_assgn_attb fraa,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_material_transactions_temp mmtt,
mtl_onhand_quantities moq,
fil_so_attb fsa
--WHERE SUBSTR(FRAA.line_shipment_number,1,INSTR(FRAA.line_shipment_number,'.')-1) = OOLA.line_number
--WHERE FRAA.line_id = NVL (OOLA.split_from_line_id, OOLA.line_id)
WHERE fsa.line_number = oola.line_number
------AND NVL (FRAA.diverted_header_id,FRAA.header_id) = OOLA.header_id --FRAA.header_id = OOLA.header_id
AND fraa.header_id = oola.header_id
AND fraa.diverted_header_id IS NULL
AND fraa.roll_number = moq.lot_number
-----AND FSA.header_id = NVL (FRAA.diverted_header_id, FRAA.header_id)
AND fsa.header_id = ooha.header_id
AND fsa.roll_width = fraa.roll_width
AND fraa.item_id = oola.inventory_item_id
AND fraa.item_id = moq.inventory_item_id
and mmtt.move_order_line_id=p_move_order_id--(select move_order_line_id from wsh_delivery_details where SOURCE_HEADER_NUMBER= p_so_number)
and fraa.line_id=mmtt.TRX_SOURCE_LINE_ID);
/*
AND ooha.order_number = 311111180609
AND NVL (mmtt.transaction_quantity, 0) != 0
-- AND moq.organization_id = oola.ship_from_org_id
-- AND moq.locator_id = mmtt.locator_id
AND oola.line_id = mmtt.demand_source_line
AND SUBSTR (fraa.line_shipment_number,
1,
INSTR (fraa.line_shipment_number, '.') - 1
) = oola.line_number
AND ooha.org_id = 241
-----------AND NVL (FRAA.diverted_order_number, OOHA.order_number)= p_so_number
AND fraa.roll_number NOT IN (
SELECT lot_number
FROM wsh_delivery_details
WHERE org_id = 241
AND lot_number IS NOT NULL
AND source_header_id IN (
SELECT header_id
FROM oe_order_headers_all
WHERE order_number =
311111180609))
AND EXISTS (
SELECT 'Y'
FROM ems_packing_list emsh,
ems_pallet_number emsl,
ems_assign_lot eal
WHERE emsh.header_id = emsl.header_id
AND emsl.line_id = eal.line_id
AND packing_list_number =
'311111180609-001' --p_packing_list_number
AND emsh.order_header_id = fraa.header_id
AND lot_sublot_number = fraa.roll_number)
UNION
-----It is for diverted roll----------------------------------------------
SELECT oola.inventory_item_id,
oola.ship_from_org_id organization_id,
oola.line_id demand_source_line_id,
oola.order_quantity_uom primary_uom_code,
fraa.net_roll_wt trx_qty,
fraa.roll_number lot_number,
mmtt.transaction_temp_id,
move_order_line_id,
moq.locator_id
FROM oe_order_lines_all oola,
oe_order_headers_all ooha,
fil_so_attb fsa,
mtl_material_transactions_temp mmtt,
xxuflex_fil_roll_assgn_attb fraa,
mtl_onhand_quantities moq
WHERE 1 = 1
AND oola.header_id = ooha.header_id
AND fsa.header_id = ooha.header_id
AND fsa.line_id = oola.line_id
AND oola.line_id = mmtt.trx_source_line_id
--------AND FRAA.diverted_order_number = ooha.order_number
AND fraa.diverted_header_id = ooha.header_id
AND fraa.diverted_line_id = oola.line_id
AND fraa.diverted_header_id IS NOT NULL
-----------------------------------------------
AND fraa.roll_number = moq.lot_number
AND fraa.item_id = moq.inventory_item_id
AND NVL (mmtt.transaction_quantity, 0) != 0
-- AND moq.organization_id = oola.ship_from_org_id
-- AND moq.locator_id = mmtt.locator_id
-----------------------------------------------
AND ooha.order_number = 311111180609
AND ooha.org_id = 241
AND fraa.roll_number NOT IN (
SELECT lot_number
FROM wsh_delivery_details
WHERE org_id = 241
AND lot_number IS NOT NULL
AND source_header_id IN (
SELECT header_id
FROM oe_order_headers_all
WHERE order_number =
311111180609))
AND EXISTS (
SELECT 'Y'
FROM ems_packing_list emsh,
ems_pallet_number emsl,
ems_assign_lot eal
WHERE emsh.header_id = emsl.header_id
AND emsl.line_id = eal.line_id
AND packing_list_number =
'311111180609-001'
AND emsh.order_header_id =
fraa.diverted_header_id
AND lot_sublot_number = fraa.roll_number))
ORDER BY organization_id, transaction_temp_id, inventory_item_id;
--
*/
CURSOR cur_dealloc
IS
SELECT DISTINCT oola.inventory_item_id,
oola.ship_from_org_id organization_id,
oola.line_id,
mmtt.transaction_temp_id
FROM xxuflex_fil_roll_assgn_attb fraa,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_material_transactions_temp mmtt
--WHERE FRAA.line_id = OOLA.line_id
WHERE NVL (fraa.diverted_header_id, fraa.header_id) =
oola.header_id
--FRAA.header_id = OOLA.header_id
AND fraa.item_id = oola.inventory_item_id
AND oola.header_id = ooha.header_id
AND oola.line_id = mmtt.demand_source_line
AND ooha.org_id = p_org_id
AND NVL (FRAA.diverted_order_number, OOHA.order_number)=p_so_number
AND ooha.order_number = p_so_number
and mmtt.move_order_line_id=p_move_order_id--(select move_order_line_id from wsh_delivery_details where SOURCE_HEADER_NUMBER= p_so_number)
AND NVL (mmtt.transaction_quantity, 0) != 0
;
CURSOR cur_dealloc_mmtl
IS
SELECT DISTINCT mmtt.transaction_temp_id
FROM xxuflex_fil_roll_assgn_attb fraa,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_material_transactions_temp mmtt
--WHERE FRAA.line_id = OOLA.line_id
WHERE NVL (fraa.diverted_header_id, fraa.header_id) =
oola.header_id
--FRAA.header_id = OOLA.header_id
AND fraa.item_id = oola.inventory_item_id
AND oola.header_id = ooha.header_id
AND oola.line_id = mmtt.demand_source_line
AND NVL (transaction_quantity, 0) = 0
--AND ooha.org_id = p_org_id
---AND NVL (FRAA.diverted_order_number, OOHA.order_number)= p_so_number
AND ooha.order_number = p_so_number;
--
BEGIN
--
fnd_file.put_line
(fnd_file.LOG,
'======================================================='
);
fnd_file.put_line (fnd_file.LOG, 'Calling API for De Allocation');
--
FOR rec_dealloc IN cur_dealloc
LOOP
dbms_output.put_line('mtl_material_transactions_temp');
UPDATE mtl_material_transactions_temp mmtt
SET transaction_quantity = 0,
primary_quantity = 0
WHERE transaction_temp_id = rec_dealloc.transaction_temp_id;
--
/*
BEGIN
dbms_output.put_line('delete_lot_ser_trx');
--
inv_trx_util_pub.delete_lot_ser_trx
(p_trx_tmp_id => rec_dealloc.transaction_temp_id,
p_org_id => rec_dealloc.organization_id,
p_item_id => rec_dealloc.inventory_item_id,
p_lotctrl => 2,
p_serctrl => 1,
x_return_status => l_return_status
);
--
fnd_file.put_line
(fnd_file.LOG,
'De Allocation done for Sales Order Line ID: '
|| rec_dealloc.line_id
);
--
EXCEPTION
--
WHEN OTHERS
THEN
dbms_output.put_line('exception in delete');
l_error_flag := 'Y';
fnd_file.put_line
(fnd_file.LOG,
'Error in De Allocation for Sales Order Line ID: '
|| rec_dealloc.line_id
|| ' : '
|| SQLERRM
);
x_return_message :=
'Error in De Allocation for Sales Order Line ID: '
|| rec_dealloc.line_id
|| ' : '
|| SQLERRM;
EXIT;
END;
--
*/
END LOOP;
--
fnd_file.put_line
(fnd_file.LOG,
'======================================================='
);
fnd_file.put_line (fnd_file.LOG, 'Calling API for Manual Allocation');
--
x_trx_id_p := 0;
ctr := 1;
dbms_output.put_line('ctr='||ctr);
dbms_output.put_line('error flag'||l_error_flag);
IF l_error_flag = 'N'
THEN
FOR rec_alloc IN cur_alloc
LOOP
IF ctr = 1
THEN
dbms_output.put_line('ctr = 1');
x_trx_id := rec_alloc.transaction_temp_id;
---X_TRX_ID := REC_ALLOC.transaction_temp_id ;
ctr := ctr + 1;
x_trx_qty := 0;
ELSE
IF x_trx_id <> rec_alloc.transaction_temp_id
THEN
dbms_output.put_line('x_trx_id <> rec_alloc.transaction_temp_id');
x_trx_id := rec_alloc.transaction_temp_id;
x_trx_qty := 0;
END IF;
END IF;
----------------------------------------------
--
BEGIN
--
dbms_output.put_line('insert_lot_trx');
l_insert_alloc :=
inv_trx_util_pub.insert_lot_trx
(p_trx_tmp_id => rec_alloc.transaction_temp_id,
p_user_id => l_user_id,
p_lot_number => rec_alloc.lot_number,
p_trx_qty => rec_alloc.trx_qty,
p_pri_qty => rec_alloc.trx_qty,
x_ser_trx_id => l_serial_transaction_temp_id,
x_proc_msg => l_proc_msg
);
--
fnd_file.put_line (fnd_file.LOG,
'Manual Allocation done for Lot Number: '
|| rec_alloc.lot_number
);
--
dbms_output.put_line('Manual Allocation done for Lot Number: '|| rec_alloc.lot_number);
l_count := l_count + 1;
--
x_trx_qty := x_trx_qty + rec_alloc.trx_qty;
----- DBMS_OUTPUT.PUT_LINE('UPDATED ..... '||X_TRX_ID ||' '|| X_TRX_QTY);
---X_TRX_ID := REC_ALLOC.transaction_temp_id ;
--
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('l_error_flag=Y');
l_error_flag := 'Y';
fnd_file.put_line (fnd_file.LOG,
'ERROR OTHERS ..... ' || x_trx_id
);
fnd_file.put_line
(fnd_file.LOG,
'Error in Manual Allocation for Lot Number: '
|| rec_alloc.lot_number
|| ' : '
|| SQLERRM
);
x_return_message :=
'Error in Manual Allocation for Lot Number: '
|| rec_alloc.lot_number
|| ' : '
|| SQLERRM;
EXIT;
END;
IF l_error_flag = 'N'
THEN
dbms_output.put_line('UPDATE mtl_material_transactions_temp');
----- ADDEDED BY SANDEEP ON 31-OCT-2012 --------------
UPDATE mtl_material_transactions_temp mmtt
SET transaction_quantity = x_trx_qty,
primary_quantity = x_trx_qty,
transfer_to_location = locator_id
WHERE transaction_temp_id = x_trx_id;
-- FND_FILE.put_line(FND_FILE.LOG,'UPDATED ..... '||X_TRX_ID||' FINAL '||X_TRX_QTY || ' l_error_flag: '||l_error_flag) ;
----COMMIT;
ELSE
fnd_file.put_line (fnd_file.LOG,
'NOT UPDATED ..... ' || x_trx_id
);
----ROLLBACK;
END IF;
--
END LOOP;
END IF;
FOR rec_dealloc_mmtl IN cur_dealloc_mmtl
LOOP
dbms_output.put_line('DELETE FROM mtl_material_transactions_temp');
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id =
rec_dealloc_mmtl.transaction_temp_id;
END LOOP;
-- /*
IF l_error_flag = 'N'
THEN
x_return_status := 'S';
ELSE
x_return_status := 'E';
ROLLBACK;
END IF;
dbms_output.put_line(x_return_status);
--- */
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('error');
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END ;
/
here we suppose manufacturing unit is maxico and shipping unit means supplier is USA
step 1. insert in a table
CREATE OR REPLACE procedure APPS.inst_lot_int_tab (p_po_header_id in number,p_org_id in number ,p_pack_list in varchar2)
is
CURSOR get_po_line_det
IS
SELECT poh.vendor_id, pol.item_id, pol.attribute13 order_line_id,
pol.item_id inventory_item_id, pll.*
FROM po_headers_all poh, po_lines_all pol, po_line_locations_all pll
WHERE 1 = 1
AND poh.po_header_id = pol.po_header_id
AND pol.po_header_id = pll.po_header_id
AND pol.po_line_id = pll.po_line_id
AND poh.po_header_id = p_po_header_id
AND poh.org_id = p_org_id;
CURSOR cur_lot_det
IS
SELECT DISTINCT fraa.roll_number, wdd.shipped_quantity
FROM ems_packing_list emsh,
fil_roll_assgn_attb fraa,
wsh_delivery_details wdd,
xxuflx_trd_ship_lines_all xtl
WHERE NVL (fraa.diverted_header_id, fraa.header_id) =
emsh.order_header_id
AND wdd.source_header_id =
NVL (fraa.diverted_header_id, fraa.header_id)
AND xtl.receive_flag = 'Y'
AND xtl.ship_quantity IS NULL
AND xtl.packing_list_number = p_pack_list --- packing list number of mafacuring site
AND xtl.roll_number = fraa.roll_number
AND xtl.packing_list_number = emsh.packing_list_number
AND xtl.item_code = fraa.item_code
AND xtl.roll_number = wdd.lot_number;
BEGIN
FOR line_rec IN get_po_line_det
LOOP
--
FOR lot_rec IN cur_lot_det
LOOP
--
DBMS_OUTPUT.put_line ('insert shipment lot rec');
--
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id, source_code, product_code,
product_transaction_id,
last_update_date,
last_updated_by,
creation_date, created_by,
lot_number,
transaction_quantity, primary_quantity, process_flag
)
VALUES (mtl_material_transactions_s.NEXTVAL
--transaction_interface_id
, 'PO', 'RCV', -- product_code
rcv_transactions_interface_s.CURRVAL,
SYSDATE -- last_update_date
,
6055 -- last_updated_by
,
SYSDATE -- creation_date
,6055 -- created_by
,
lot_rec.roll_number --LOT_NUMBER
,
lot_rec.shipped_quantity
--TRANSACTION_QUANTITY
, lot_rec.shipped_quantity
--primary_quantity,
, 1
);
END LOOP;
--
END LOOP;
END;
/
step 2. create packing list in usa
CREATE OR REPLACE PROCEDURE APPS.create_packing_list_pk (
p_packing_list IN VARCHAR2,
x_return_status OUT VARCHAR2,
x_return_msg OUT VARCHAR2,
p_us_packing_list OUT VARCHAR2
)
IS
lv_order_number VARCHAR2 (100);
lv_str VARCHAR2 (100);
lv_header_id NUMBER;
lv_order_number_new VARCHAR2 (100);
-- lv_error_message VARCHAR2 (200);
lv_packing_list_roll VARCHAR2 (200);
lv_commercial_category VARCHAR2 (200);
lv_line_number VARCHAR2 (200);
lv_width VARCHAR2 (200);
lv_location VARCHAR2 (200);
lv_count1 NUMBER;
lv_old_header_id NUMBER;
lv_error_message VARCHAR2 (200);
lv_order_header_id NUMBER;
lv_mex_pack_qty NUMBER;
lv_us_rcvd_qty NUMBER;
CURSOR cur_pallet_number (
p_header_id IN NUMBER,
p_source_header_id IN NUMBER
)
IS
SELECT header_id,
line_id,
pallet_number,
SUBSTR (pallet_number, -4) str,
pallet_weight,
description,
pallet_type,
pallet_roll
FROM ems_pallet_number epn
WHERE header_id = (select header_id from ems_packing_listwhere packing_list_number=p_packing_list)
AND org_id IN (406,82)---= source_org_id
AND EXISTS (
SELECT 'y'
FROM ems_assign_lot eat
WHERE header_id = epn.header_id
AND line_id = epn.line_id
AND org_id = epn.org_id
AND EXISTS (
SELECT 'y'
FROM wsh_delivery_details
WHERE source_header_id = (select order_header_id from ems_packing_list
where packing_list_number=p_packing_list)
AND lot_number IS NOT NULL
AND lot_number = eat.lot_sublot_number))
;
CURSOR cur_lot_number (
p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_source_header_id IN NUMBER
)
IS
SELECT lot_sublot_number,
SUM (shipped_quantity) roll_weight,
description,
fitment
FROM ems_assign_lot eat, wsh_delivery_details wdd
WHERE header_id = (select header_id from ems_packing_list where packing_list_number=p_packing_list)
AND line_id = p_line_id
AND eat.org_id IN (406,82)---= source_org_id
AND source_header_id = (select order_header_id from ems_packing_list
where packing_list_number=p_packing_list)
AND lot_number IS NOT NULL
AND lot_number = eat.lot_sublot_number
GROUP BY lot_sublot_number, description, fitment
union
SELECT lot_sublot_number,
SUM (shipped_quantity) roll_weight,
description,
fitment
FROM ems_assign_lot eat, wsh_delivery_details wdd
WHERE header_id = (select header_id from ems_packing_list where packing_list_number=p_packing_list)
AND line_id = p_line_id
AND eat.org_id IN (406,241)---= source_org_id
AND source_header_id = (select order_header_id from ems_packing_list where packing_list_number=p_packing_list)
AND lot_number IS NOT NULL
AND lot_number = eat.lot_sublot_number
GROUP BY lot_sublot_number, description, fitment;
BEGIN
--
dbms_output.put_line('Inserting Packing List ' || p_packing_list);
--
BEGIN
select header_id,
order_number,
packing_list_number,
packing_list_roll,
commercial_category,
line_number,
width,
LOCATION,
order_header_id
INTO
lv_old_header_id,
lv_order_number,
lv_str,
lv_packing_list_roll,
lv_commercial_category,
lv_line_number,
lv_width,
lv_location,
lv_order_header_id
from
( SELECT header_id,
order_number,
SUBSTR (packing_list_number, -4) packing_list_number,
packing_list_roll,
commercial_category,
line_number,
width,
LOCATION,
order_header_id
from ems_packing_list
WHERE packing_list_number =p_packing_list
AND org_id IN (406,82)
union
SELECT header_id,
order_number,
SUBSTR (packing_list_number, -4) packing_list_number,
packing_list_roll,
commercial_category,
line_number,
width,
LOCATION,
order_header_id
FROM ems_packing_list
WHERE packing_list_number =p_packing_list
AND org_id IN (406,241) ) ;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Invalid Packing List');
lv_order_number := NULL;
x_return_status := 'E';
x_return_msg := 'Invalid packing list ' || p_packing_list;
END;
--
-- Added by Abhishek on 06 May-2014
BEGIN
SELECT SUM (wdd.shipped_quantity) shipped_quantity
FROM ems_packing_list emsh,
ems_pallet_number emsl,
ems_assign_lot eal,
fil_roll_assgn_attb fraa,
wsh_delivery_details wdd,
oe_order_lines_all ola
WHERE emsh.header_id = emsl.header_id
AND ola.header_id = wdd.source_header_id
AND ola.line_id = wdd.source_line_id
AND emsl.line_id = eal.line_id
AND NVL (fraa.diverted_header_id, fraa.header_id) =
emsh.order_header_id
AND roll_number = lot_sublot_number
AND wdd.source_header_id =
NVL (fraa.diverted_header_id, fraa.header_id)
-- AND wdd.source_line_id = NVL(fraa.diverted_line_id, fraa.line_id)
AND wdd.org_id IN (406,241)--= source_org_id
AND wdd.released_status = 'C'
AND wdd.lot_number = fraa.roll_number
AND packing_list_number = p_packing_list) ;
--
EXCEPTION
WHEN OTHERS
THEN
lv_mex_pack_qty := 0;
END;
dbms_output.put_line('lv_mex_pack_qty: ' || lv_mex_pack_qty);
--
-- Added by Abhishek on 06 May-2014
BEGIN
SELECT SUM (quantity_received) rcvd_qty
INTO lv_us_rcvd_qty
FROM rcv_shipment_headers rcv, rcv_shipment_lines rsl
WHERE rcv.attribute15 = p_packing_list
AND rcv.shipment_header_id = rsl.shipment_header_id;
--
EXCEPTION
WHEN OTHERS
THEN
lv_us_rcvd_qty := 0;
END;
dbms_output.put_line('lv_us_rcvd_qty: ' || lv_us_rcvd_qty);
--
IF lv_mex_pack_qty = lv_us_rcvd_qty
THEN
-- Added by Abhishek on 06 May-2014
IF lv_order_number IS NOT NULL
THEN
--
dbms_output.put_line('get order number');
--
BEGIN
SELECT ooh.header_id,
ooh.order_number
INTO lv_header_id,
lv_order_number_new
FROM oe_order_headers_all ooh
WHERE order_number = (select distinct xxth.trd_oe_order_number from XXUFLX_TRD_SHIP_HEADERS_ALL xxth,XXUFLX_TRD_SHIP_LINES_ALL xxtl
where xxtl.trd_ship_header_id=xxth.TRD_SHIP_HEADER_ID
and xxtl.packing_list_number=p_packing_list);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Order Number is not created in USA');
lv_order_number_new := NULL;
x_return_status := 'E';
x_return_msg := 'Order Number is not created in US';
END;
IF lv_order_number_new IS NOT NULL
THEN
-- check if packing list exists
SELECT COUNT (*)
INTO lv_count1
FROM ems_packing_list
WHERE packing_list_number = lv_order_number_new || lv_str
AND org_id = 241;
--
dbms_output.put_line('packing list count ' || lv_count1);
dbms_output.put_line('New packing list' || lv_order_number_new || lv_str);
--
IF lv_count1 = 0
THEN
--
BEGIN
--
INSERT INTO ems_packing_list
(header_id,
order_number,
packing_list_number,
packing_list_roll,
commercial_category,
line_number,
width,
description,
created_by,
creation_date,
last_updated_by,
last_update_date,
order_header_id,
LOCATION,
org_id
)
VALUES (header_seq.NEXTVAL,
lv_order_number_new,
lv_order_number_new || lv_str,
lv_packing_list_roll,
lv_commercial_category,
lv_line_number,
lv_width,
'CLOSED',
6055,
SYSDATE,
6055,
SYSDATE,
lv_header_id,
lv_location,
241
);
--
dbms_output.put_line('header inserted');
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := SUBSTR (SQLERRM, 1, 200);
dbms_output.put_line
( 'Oracle Error when crearting packing list:- '
|| lv_error_message
);
x_return_status := 'E';
x_return_msg :=
'error while inserting packing list header '
|| SQLERRM;
END;
IF NVL (x_return_status, 'S') <> 'E'
THEN
--
FOR rec_pallet_number IN
cur_pallet_number (lv_old_header_id,
lv_order_header_id
)
LOOP
--
BEGIN
--
INSERT INTO ems_pallet_number
(header_id,
line_id,
pallet_number,
pallet_roll,
pallet_weight,
description,
created_by,
creation_date,
last_updated_by,
last_update_date,
org_id,
pallet_type
)
VALUES (header_seq.CURRVAL,
line_seq.NEXTVAL,
lv_order_number_new
|| rec_pallet_number.str,
rec_pallet_number.pallet_roll,
rec_pallet_number.pallet_weight,
rec_pallet_number.description,
6055,
SYSDATE,
6055,
SYSDATE,
241,
rec_pallet_number.pallet_type
);
--
dbms_output.put_line('packing list line entered');
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := SUBSTR (SQLERRM, 1, 200);
dbms_output.put_line
( 'Oracle Error when crearting packing list:- '
|| lv_error_message
);
x_return_status := 'E';
x_return_msg :=
'error while inserting packing list lines '
|| SQLERRM;
END;
IF NVL (x_return_status, 'S') <> 'E'
THEN
FOR rec_lot_number IN
cur_lot_number (rec_pallet_number.header_id,
rec_pallet_number.line_id,
lv_order_header_id
)
LOOP
BEGIN
INSERT INTO ems_assign_lot
(header_id,
line_id,
assign_lot_id,
lot_sublot_number,
roll_weight,
description,
org_id,
created_by,
creation_date,
last_update_date,
last_updated_by,
fitment
)
VALUES (header_seq.CURRVAL,
line_seq.CURRVAL,
shipment_seq.NEXTVAL,
rec_lot_number.lot_sublot_number,
rec_lot_number.roll_weight,
rec_lot_number.description,
241,
6055,
SYSDATE,
SYSDATE,
6055,
rec_lot_number.fitment
);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line
( 'Oracle Error when crearting packing list Lot Number:- '
|| rec_lot_number.lot_sublot_number
);
lv_error_message :=
SUBSTR (SQLERRM, 1, 200);
dbms_output.put_line
( 'Oracle Error when crearting packing list:- '
|| lv_error_message
);
x_return_status := 'E';
x_return_msg :=
'error while inserting packing list lots '
|| SQLERRM;
END;
END LOOP;
END IF;
END LOOP;
END IF;
x_return_msg :=
'Packing list successfully created. Packing List# is '
|| lv_order_number_new
|| lv_str;
ELSE
x_return_status := 'S';
x_return_msg := 'Packing list already exists in USA';
END IF;
--
p_us_packing_list := lv_order_number_new || lv_str;
--
END IF;
END IF;
ELSE
x_return_status := 'E';
x_return_msg :=
'Quantity Mismatch in US Received Quantity ('
|| lv_us_rcvd_qty
|| ') and Mexico Packing List Quantity ('
|| lv_mex_pack_qty
|| ')';
END IF;
---
IF x_return_status = 'E'
THEN
ROLLBACK;
ELSE
x_return_status := 'S';
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := SUBSTR (SQLERRM, 1, 200);
dbms_output.put_line('Oracle Error :- ' || lv_error_message);
x_return_status := 'E';
x_return_msg := 'Exception while creating packing list ' || SQLERRM;
END ;
--shipping
/
now you check new packing list number in ems_packing_list table
3. custom table xxuflex_fil_roll_assgn_attb
CREATE GLOBAL TEMPORARY TABLE APPS.XXUFLEX_FIL_ROLL_ASSGN_ATTB
(
ROLL_NUMBER VARCHAR2(64 BYTE),
HEADER_ID NUMBER,
ORG_ID NUMBER,
LINE_ID NUMBER,
ITEM_CODE VARCHAR2(30 BYTE),
ACTUAL_LENGTH NUMBER(10),
OPT_DENS NUMBER(5,2),
TRTMNT_IN VARCHAR2(3 BYTE),
TRTMNT_OUT VARCHAR2(3 BYTE),
CORE_ID NUMBER(5),
ROLL_WIDTH NUMBER(6),
GROSS_ROLL_WT NUMBER(6,1),
NET_ROLL_WT NUMBER(6,1),
ROLL_GRADE VARCHAR2(5 BYTE),
BATCH_ID NUMBER(10),
BATCH_NO VARCHAR2(32 BYTE),
LOT_NO VARCHAR2(32 BYTE),
SUBLOT_NO VARCHAR2(32 BYTE),
GRADE_REASON VARCHAR2(30 BYTE),
NUMBER_OF_JOINTS NUMBER,
FORMULA_CLASS VARCHAR2(8 BYTE),
LENGTH NUMBER(5),
OUT_DIA NUMBER(7,2),
ORDER_NUMBER NUMBER,
DIVERTED_ORDER_NUMBER NUMBER,
DIVERTED_HEADER_ID NUMBER,
DIVERTED_LINE_ID NUMBER,
WAREHOUSE VARCHAR2(4 BYTE),
LINE_SHIPMENT_NUMBER VARCHAR2(10 BYTE),
JUMBO_LOT_NO VARCHAR2(1000 BYTE),
JUMBO_SUBLOT_NO VARCHAR2(1000 BYTE),
JUMBO_WT VARCHAR2(1000 BYTE),
JUMBO_WIDTH VARCHAR2(1000 BYTE),
DIVERTED_ORDER_DATE DATE,
ROLL_ASSGN_DATE DATE,
NET_WT_REMAINING NUMBER,
JUMBO_ONHAND_QTY VARCHAR2(1000 BYTE),
JUMBO_ONHAND_LENGTH VARCHAR2(1000 BYTE),
LOT_ID NUMBER,
ITEM_ID NUMBER NOT NULL,
CREATED_BY NUMBER(15),
CREATION_DATE DATE,
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_LOGIN NUMBER(15),
CORE_DETAIL VARCHAR2(100 BYTE),
CORE_WT NUMBER(30),
PRINT_FLAG CHAR(1 BYTE),
ROLL_LOCATION VARCHAR2(50 BYTE),
PK VARCHAR2(1 BYTE),
CHK_BOX VARCHAR2(3 BYTE)
)
ON COMMIT PRESERVE ROWS
NOCACHE;
4.CONTROL_LOT
CREATE OR REPLACE PROCEDURE APPS.control_lot_pk (
p_packing_list_number IN VARCHAR,
p_us_packing_list_number IN VARCHAR,
x_return_message OUT VARCHAR2,
x_return_status OUT VARCHAR2
)
IS
CURSOR cur_rec
IS
SELECT DISTINCT NVL (fraa.diverted_header_id, fraa.header_id)
header_id,
NVL (fraa.diverted_line_id, fraa.line_id) line_id
FROM ems_packing_list emsh,
ems_pallet_number emsl,
ems_assign_lot eal,
xxuflex_fil_roll_assgn_attb fraa
WHERE emsh.header_id = emsl.header_id
AND emsl.line_id = eal.line_id
AND packing_list_number = p_packing_list_number
-- AND NVL (fraa.diverted_header_id, fraa.header_id) = emsh.order_header_id
AND roll_number = lot_sublot_number
r;
CURSOR get_delivery_det (
p_source_header_id IN NUMBER,
p_source_line_id IN NUMBER
)
IS
SELECT wdd.*
FROM wsh_delivery_details wdd --, oe_order_lines_all ola
WHERE source_header_id = p_source_header_id
AND wdd.source_line_id = get_line_id_pk(p_source_line_id)
AND move_order_line_id IS NULL
AND released_status IN ('R', 'B')
;
--
l_return_status VARCHAR2 (10) := 'S';
l_msg_count NUMBER (15);
l_msg_data VARCHAR2 (4000);
--
--
p_new_batch_id NUMBER;
l_rule_id NUMBER;
l_rule_name VARCHAR2 (2000);
l_batch_prefix VARCHAR2 (2000);
l_batch_info_rec wsh_picking_batches_pub.batch_info_rec;
l_request_id NUMBER;
--
lv_org_id NUMBER;
lv_order_number NUMBER;
--
lv_process VARCHAR2 (100);
lv_detail_exists VARCHAR2 (1) := 'N';
lv_order_header_id NUMBER;
l_release_batch VARCHAR2 (1) := 'N';
--
BEGIN
--
dbms_output.put_line ('pick release started ' || p_packing_list_number);
--
--
BEGIN
SELECT order_header_id
INTO lv_order_header_id
FROM
(SELECT order_header_id from ems_packing_list
WHERE packing_list_number = p_packing_list_number
AND org_id IN (406,82)
;
---= source_org_id;
EXCEPTION
WHEN OTHERS
THEN
lv_order_header_id := 0;
END;
--
dbms_output.put_line ('Source Header ID ' || lv_order_header_id);
dbms_output.put_line ('Insert data into fil roll assgn attbt');
DELETE FROM xxuflex_fil_roll_assgn_attb;
INSERT INTO xxuflex_fil_roll_assgn_attb
(roll_number,
header_id,
org_id,
line_id,
item_code,
actual_length,
opt_dens,
trtmnt_in,
trtmnt_out,
core_id,
roll_width,
gross_roll_wt,
net_roll_wt,
roll_grade,
batch_id,
batch_no,
lot_no,
sublot_no,
grade_reason,
number_of_joints,
formula_class,
LENGTH,
out_dia,
order_number,
item_id,
line_shipment_number
)
SELECT distinct roll_number,
oohus.header_id,
oohus.org_id,
oolus.line_id,
item_code,
actual_length,
opt_dens,
trtmnt_in,
trtmnt_out,
core_id,
roll_width,
gross_roll_wt,
net_roll_wt,
roll_grade,
NULL batch_id,
NULL batch_no,
lot_no,
sublot_no,
grade_reason,
number_of_joints,
formula_class,
LENGTH,
out_dia,
oohus.order_number,
oolus.inventory_item_id,
(oolus.line_number || '.' || oolus.shipment_number
)
FROM fil_roll_assgn_attb fraa,
oe_order_headers_all oohn,
oe_order_lines_all ooln,
oe_order_headers_all oohus,
oe_order_lines_all oolus,
XXULX_TRADE_ORDER_LINES xtl
WHERE 1 = 1
AND NVL (fraa.diverted_header_id, fraa.header_id) = oohn.header_id
and NVL (fraa.diverted_header_id, fraa.header_id) = xtl.MGF_OE_HEADER_ID
AND NVL (fraa.diverted_line_id, fraa.line_id) = ooln.line_id
AND oohn.header_id = ooln.header_id
AND xtl.mgf_oe_header_id=oohn.header_id
and xtl.mgf_oe_order_number=oohn.order_number
and xtl.oe_header_id=oohus.HEADER_ID
and xtl.org_id=oohus.org_id
and xtl.mgf_org_id=oohn.org_id
AND oohus.HEADER_ID=oolus.header_id
AND fraa.org_id IN (406,82)---= source_org_id
AND NVL (fraa.diverted_header_id, fraa.header_id) =lv_order_header_id
and fraa.roll_number in (select LOT_SUBLOT_NUMBER from ems_assign_lot where header_id=
(select HEADER_ID from ems_packing_list where packing_list_number=p_us_packing_list_number))
union
SELECT roll_number,
oohus.header_id,
oohus.org_id,
oolus.line_id,
item_code,
actual_length,
opt_dens,
trtmnt_in,
trtmnt_out,
core_id,
roll_width,
gross_roll_wt,
net_roll_wt,
roll_grade,
NULL batch_id,
NULL batch_no,
lot_no,
sublot_no,
grade_reason,
number_of_joints,
formula_class,
LENGTH,
out_dia,
oohus.order_number,
fraa.item_id,
(oolus.line_number || '.' || oolus.shipment_number)
FROM fil_roll_assgn_attb fraa,
oe_order_headers_all oohm,
oe_order_lines_all oolm,
oe_order_headers_all oohus,
oe_order_lines_all oolus,
XXULX_TRADE_ORDER_LINES xtl
WHERE 1 = 1
AND NVL (fraa.diverted_header_id, fraa.header_id) = oohm.header_id
and NVL (fraa.diverted_header_id, fraa.header_id) = xtl.MGF_OE_HEADER_ID
AND NVL (fraa.diverted_line_id, fraa.line_id) = oolm.line_id
AND oohm.header_id = oolm.header_id
AND xtl.mgf_oe_header_id=oohm.header_id
and xtl.mgf_oe_order_number=oohm.order_number
and xtl.oe_header_id=oohus.HEADER_ID
and xtl.org_id=oohus.org_id
and xtl.mgf_org_id=oohm.org_id
AND oohus.HEADER_ID=oolus.header_id
AND fraa.org_id IN (406,241)---= source_org_id
AND NVL (fraa.diverted_header_id, fraa.header_id) =lv_order_header_id
and roll_number in (select LOT_SUBLOT_NUMBER from ems_assign_lot where header_id=
(select HEADER_ID from ems_packing_list where packing_list_number=p_us_packing_list_number));
--
dbms_output.put_line ('Total rolls inserted ' || SQL%ROWCOUNT);
dbms_output.put_line ('Start pick release');
FOR l_rec IN cur_rec
LOOP
FOR l_get_delivery_det IN get_delivery_det (l_rec.header_id,
l_rec.line_id
)
LOOP
l_batch_info_rec.backorders_only_flag := 'I';
l_batch_info_rec.delivery_detail_id := -1;
l_batch_prefix := NULL;
--
lv_process := 'Pick Release';
dbms_output.put_line (lv_process||'start');
wsh_picking_batches_pub.create_batch
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rule_id => l_rule_id,
p_rule_name => l_rule_name,
p_batch_rec => l_batch_info_rec,
p_batch_prefix => l_batch_prefix,
x_batch_id => p_new_batch_id
);
lv_detail_exists := 'Y';
IF l_get_delivery_det.batch_id IS NULL
THEN
UPDATE wsh_delivery_details
SET batch_id = p_new_batch_id
WHERE delivery_detail_id =
l_get_delivery_det.delivery_detail_id;
INSERT INTO xxfil_batch_gt
VALUES (p_new_batch_id
);
l_release_batch := 'Y';
ELSE
INSERT INTO xxfil_batch_gt
VALUES (l_get_delivery_det.batch_id
);
END IF;
IF lv_detail_exists = 'Y' AND l_release_batch = 'Y'
THEN
lv_process := 'Release Batch';
dbms_output.put_line (lv_process || p_new_batch_id);
-- Release the batch Created Above
wsh_picking_batches_pub.release_batch
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_id => p_new_batch_id,
p_batch_name => NULL,
p_log_level => 1,
p_release_mode => 'ONLINE',
-- (ONLINE or CONCURRENT)
x_request_id => l_request_id
);
dbms_output.put_line ('Batch Released Status ' || l_return_status);
IF l_return_status != 'E'
THEN
NULL;
ELSE
fnd_file.put_line (fnd_file.LOG,
'Message count ' || l_msg_count
);
wsh_util_core.get_messages ('Y',
l_msg_data,
l_msg_data,
l_msg_count
);
ROLLBACK;
END IF;
ELSE
l_return_status := 'S';
l_msg_data := NULL;
END IF;
IF l_return_status <> 'E'
THEN
lv_process := 'Update Lots';
l_return_status := NULL;
l_msg_data := NULL;
dbms_output.put_line (lv_process);
BEGIN
SELECT oeh.org_id,
oeh.order_number
INTO lv_org_id,
lv_order_number
FROM ems_packing_list ems, oe_order_headers_all oeh
WHERE packing_list_number =p_us_packing_list_number
AND ems.order_number = oeh.order_number;
EXCEPTION
WHEN OTHERS
THEN
lv_org_id := 0;
lv_order_number := 0;
END;
dbms_output.put_line ('Generate Lots ' || lv_order_number);
/* control_lot_num (lv_org_id,
lv_order_number,
p_us_packing_list_number,
l_msg_data,
l_return_status
);*/
END IF;
dbms_output.put_line ('After Generate Lots ' || l_return_status || l_msg_data);
DELETE FROM mtl_reservations
WHERE inventory_item_id =
l_get_delivery_det.inventory_item_id
AND lot_number IS NULL;
END LOOP;
END LOOP;
x_return_status := l_return_status;
x_return_message := l_msg_data;
EXCEPTION
WHEN OTHERS
THEN
x_return_message := lv_process || SQLERRM;
x_return_status := 'E';
END ;
/
5.CONTROL_LOT_NUM
CREATE OR REPLACE PROCEDURE APPS.control_lot_num (
p_org_id NUMBER, ---241
p_so_number NUMBER, ---311111180605
p_packing_list_number IN VARCHAR2,
p_move_order_id in number, ---311111180605-002
x_return_message OUT VARCHAR2,
x_return_status OUT VARCHAR2
)
AS
--
--
l_error_flag VARCHAR2 (5) := 'N';
l_return_status VARCHAR2 (500);
l_count NUMBER := 0;
l_proc_msg VARCHAR2 (4000);
l_serial_transaction_temp_id NUMBER;
l_insert_alloc NUMBER;
l_user_id NUMBER := fnd_global.user_id;
ctr NUMBER := 0;
x_trx_qty NUMBER := 0;
x_trx_id NUMBER := 0;
x_trx_id_p NUMBER := 0;
--
CURSOR cur_alloc
IS
SELECT inventory_item_id,
organization_id,
demand_source_line_id,
primary_uom_code,
trx_qty,
lot_number,
transaction_temp_id,
move_order_line_id,
locator_id
FROM (SELECT distinct oola.inventory_item_id,
oola.ship_from_org_id organization_id,
oola.line_id demand_source_line_id,
oola.order_quantity_uom primary_uom_code,
fraa.net_roll_wt trx_qty,
fraa.roll_number lot_number,
mmtt.transaction_temp_id,
move_order_line_id,
moq.locator_id
FROM xxuflex_fil_roll_assgn_attb fraa,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_material_transactions_temp mmtt,
mtl_onhand_quantities moq,
fil_so_attb fsa
--WHERE SUBSTR(FRAA.line_shipment_number,1,INSTR(FRAA.line_shipment_number,'.')-1) = OOLA.line_number
--WHERE FRAA.line_id = NVL (OOLA.split_from_line_id, OOLA.line_id)
WHERE fsa.line_number = oola.line_number
------AND NVL (FRAA.diverted_header_id,FRAA.header_id) = OOLA.header_id --FRAA.header_id = OOLA.header_id
AND fraa.header_id = oola.header_id
AND fraa.diverted_header_id IS NULL
AND fraa.roll_number = moq.lot_number
-----AND FSA.header_id = NVL (FRAA.diverted_header_id, FRAA.header_id)
AND fsa.header_id = ooha.header_id
AND fsa.roll_width = fraa.roll_width
AND fraa.item_id = oola.inventory_item_id
AND fraa.item_id = moq.inventory_item_id
and mmtt.move_order_line_id=p_move_order_id--(select move_order_line_id from wsh_delivery_details where SOURCE_HEADER_NUMBER= p_so_number)
and fraa.line_id=mmtt.TRX_SOURCE_LINE_ID);
/*
AND ooha.order_number = 311111180609
AND NVL (mmtt.transaction_quantity, 0) != 0
-- AND moq.organization_id = oola.ship_from_org_id
-- AND moq.locator_id = mmtt.locator_id
AND oola.line_id = mmtt.demand_source_line
AND SUBSTR (fraa.line_shipment_number,
1,
INSTR (fraa.line_shipment_number, '.') - 1
) = oola.line_number
AND ooha.org_id = 241
-----------AND NVL (FRAA.diverted_order_number, OOHA.order_number)= p_so_number
AND fraa.roll_number NOT IN (
SELECT lot_number
FROM wsh_delivery_details
WHERE org_id = 241
AND lot_number IS NOT NULL
AND source_header_id IN (
SELECT header_id
FROM oe_order_headers_all
WHERE order_number =
311111180609))
AND EXISTS (
SELECT 'Y'
FROM ems_packing_list emsh,
ems_pallet_number emsl,
ems_assign_lot eal
WHERE emsh.header_id = emsl.header_id
AND emsl.line_id = eal.line_id
AND packing_list_number =
'311111180609-001' --p_packing_list_number
AND emsh.order_header_id = fraa.header_id
AND lot_sublot_number = fraa.roll_number)
UNION
-----It is for diverted roll----------------------------------------------
SELECT oola.inventory_item_id,
oola.ship_from_org_id organization_id,
oola.line_id demand_source_line_id,
oola.order_quantity_uom primary_uom_code,
fraa.net_roll_wt trx_qty,
fraa.roll_number lot_number,
mmtt.transaction_temp_id,
move_order_line_id,
moq.locator_id
FROM oe_order_lines_all oola,
oe_order_headers_all ooha,
fil_so_attb fsa,
mtl_material_transactions_temp mmtt,
xxuflex_fil_roll_assgn_attb fraa,
mtl_onhand_quantities moq
WHERE 1 = 1
AND oola.header_id = ooha.header_id
AND fsa.header_id = ooha.header_id
AND fsa.line_id = oola.line_id
AND oola.line_id = mmtt.trx_source_line_id
--------AND FRAA.diverted_order_number = ooha.order_number
AND fraa.diverted_header_id = ooha.header_id
AND fraa.diverted_line_id = oola.line_id
AND fraa.diverted_header_id IS NOT NULL
-----------------------------------------------
AND fraa.roll_number = moq.lot_number
AND fraa.item_id = moq.inventory_item_id
AND NVL (mmtt.transaction_quantity, 0) != 0
-- AND moq.organization_id = oola.ship_from_org_id
-- AND moq.locator_id = mmtt.locator_id
-----------------------------------------------
AND ooha.order_number = 311111180609
AND ooha.org_id = 241
AND fraa.roll_number NOT IN (
SELECT lot_number
FROM wsh_delivery_details
WHERE org_id = 241
AND lot_number IS NOT NULL
AND source_header_id IN (
SELECT header_id
FROM oe_order_headers_all
WHERE order_number =
311111180609))
AND EXISTS (
SELECT 'Y'
FROM ems_packing_list emsh,
ems_pallet_number emsl,
ems_assign_lot eal
WHERE emsh.header_id = emsl.header_id
AND emsl.line_id = eal.line_id
AND packing_list_number =
'311111180609-001'
AND emsh.order_header_id =
fraa.diverted_header_id
AND lot_sublot_number = fraa.roll_number))
ORDER BY organization_id, transaction_temp_id, inventory_item_id;
--
*/
CURSOR cur_dealloc
IS
SELECT DISTINCT oola.inventory_item_id,
oola.ship_from_org_id organization_id,
oola.line_id,
mmtt.transaction_temp_id
FROM xxuflex_fil_roll_assgn_attb fraa,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_material_transactions_temp mmtt
--WHERE FRAA.line_id = OOLA.line_id
WHERE NVL (fraa.diverted_header_id, fraa.header_id) =
oola.header_id
--FRAA.header_id = OOLA.header_id
AND fraa.item_id = oola.inventory_item_id
AND oola.header_id = ooha.header_id
AND oola.line_id = mmtt.demand_source_line
AND ooha.org_id = p_org_id
AND NVL (FRAA.diverted_order_number, OOHA.order_number)=p_so_number
AND ooha.order_number = p_so_number
and mmtt.move_order_line_id=p_move_order_id--(select move_order_line_id from wsh_delivery_details where SOURCE_HEADER_NUMBER= p_so_number)
AND NVL (mmtt.transaction_quantity, 0) != 0
;
CURSOR cur_dealloc_mmtl
IS
SELECT DISTINCT mmtt.transaction_temp_id
FROM xxuflex_fil_roll_assgn_attb fraa,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_material_transactions_temp mmtt
--WHERE FRAA.line_id = OOLA.line_id
WHERE NVL (fraa.diverted_header_id, fraa.header_id) =
oola.header_id
--FRAA.header_id = OOLA.header_id
AND fraa.item_id = oola.inventory_item_id
AND oola.header_id = ooha.header_id
AND oola.line_id = mmtt.demand_source_line
AND NVL (transaction_quantity, 0) = 0
--AND ooha.org_id = p_org_id
---AND NVL (FRAA.diverted_order_number, OOHA.order_number)= p_so_number
AND ooha.order_number = p_so_number;
--
BEGIN
--
fnd_file.put_line
(fnd_file.LOG,
'======================================================='
);
fnd_file.put_line (fnd_file.LOG, 'Calling API for De Allocation');
--
FOR rec_dealloc IN cur_dealloc
LOOP
dbms_output.put_line('mtl_material_transactions_temp');
UPDATE mtl_material_transactions_temp mmtt
SET transaction_quantity = 0,
primary_quantity = 0
WHERE transaction_temp_id = rec_dealloc.transaction_temp_id;
--
/*
BEGIN
dbms_output.put_line('delete_lot_ser_trx');
--
inv_trx_util_pub.delete_lot_ser_trx
(p_trx_tmp_id => rec_dealloc.transaction_temp_id,
p_org_id => rec_dealloc.organization_id,
p_item_id => rec_dealloc.inventory_item_id,
p_lotctrl => 2,
p_serctrl => 1,
x_return_status => l_return_status
);
--
fnd_file.put_line
(fnd_file.LOG,
'De Allocation done for Sales Order Line ID: '
|| rec_dealloc.line_id
);
--
EXCEPTION
--
WHEN OTHERS
THEN
dbms_output.put_line('exception in delete');
l_error_flag := 'Y';
fnd_file.put_line
(fnd_file.LOG,
'Error in De Allocation for Sales Order Line ID: '
|| rec_dealloc.line_id
|| ' : '
|| SQLERRM
);
x_return_message :=
'Error in De Allocation for Sales Order Line ID: '
|| rec_dealloc.line_id
|| ' : '
|| SQLERRM;
EXIT;
END;
--
*/
END LOOP;
--
fnd_file.put_line
(fnd_file.LOG,
'======================================================='
);
fnd_file.put_line (fnd_file.LOG, 'Calling API for Manual Allocation');
--
x_trx_id_p := 0;
ctr := 1;
dbms_output.put_line('ctr='||ctr);
dbms_output.put_line('error flag'||l_error_flag);
IF l_error_flag = 'N'
THEN
FOR rec_alloc IN cur_alloc
LOOP
IF ctr = 1
THEN
dbms_output.put_line('ctr = 1');
x_trx_id := rec_alloc.transaction_temp_id;
---X_TRX_ID := REC_ALLOC.transaction_temp_id ;
ctr := ctr + 1;
x_trx_qty := 0;
ELSE
IF x_trx_id <> rec_alloc.transaction_temp_id
THEN
dbms_output.put_line('x_trx_id <> rec_alloc.transaction_temp_id');
x_trx_id := rec_alloc.transaction_temp_id;
x_trx_qty := 0;
END IF;
END IF;
----------------------------------------------
--
BEGIN
--
dbms_output.put_line('insert_lot_trx');
l_insert_alloc :=
inv_trx_util_pub.insert_lot_trx
(p_trx_tmp_id => rec_alloc.transaction_temp_id,
p_user_id => l_user_id,
p_lot_number => rec_alloc.lot_number,
p_trx_qty => rec_alloc.trx_qty,
p_pri_qty => rec_alloc.trx_qty,
x_ser_trx_id => l_serial_transaction_temp_id,
x_proc_msg => l_proc_msg
);
--
fnd_file.put_line (fnd_file.LOG,
'Manual Allocation done for Lot Number: '
|| rec_alloc.lot_number
);
--
dbms_output.put_line('Manual Allocation done for Lot Number: '|| rec_alloc.lot_number);
l_count := l_count + 1;
--
x_trx_qty := x_trx_qty + rec_alloc.trx_qty;
----- DBMS_OUTPUT.PUT_LINE('UPDATED ..... '||X_TRX_ID ||' '|| X_TRX_QTY);
---X_TRX_ID := REC_ALLOC.transaction_temp_id ;
--
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('l_error_flag=Y');
l_error_flag := 'Y';
fnd_file.put_line (fnd_file.LOG,
'ERROR OTHERS ..... ' || x_trx_id
);
fnd_file.put_line
(fnd_file.LOG,
'Error in Manual Allocation for Lot Number: '
|| rec_alloc.lot_number
|| ' : '
|| SQLERRM
);
x_return_message :=
'Error in Manual Allocation for Lot Number: '
|| rec_alloc.lot_number
|| ' : '
|| SQLERRM;
EXIT;
END;
IF l_error_flag = 'N'
THEN
dbms_output.put_line('UPDATE mtl_material_transactions_temp');
----- ADDEDED BY SANDEEP ON 31-OCT-2012 --------------
UPDATE mtl_material_transactions_temp mmtt
SET transaction_quantity = x_trx_qty,
primary_quantity = x_trx_qty,
transfer_to_location = locator_id
WHERE transaction_temp_id = x_trx_id;
-- FND_FILE.put_line(FND_FILE.LOG,'UPDATED ..... '||X_TRX_ID||' FINAL '||X_TRX_QTY || ' l_error_flag: '||l_error_flag) ;
----COMMIT;
ELSE
fnd_file.put_line (fnd_file.LOG,
'NOT UPDATED ..... ' || x_trx_id
);
----ROLLBACK;
END IF;
--
END LOOP;
END IF;
FOR rec_dealloc_mmtl IN cur_dealloc_mmtl
LOOP
dbms_output.put_line('DELETE FROM mtl_material_transactions_temp');
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id =
rec_dealloc_mmtl.transaction_temp_id;
END LOOP;
-- /*
IF l_error_flag = 'N'
THEN
x_return_status := 'S';
ELSE
x_return_status := 'E';
ROLLBACK;
END IF;
dbms_output.put_line(x_return_status);
--- */
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('error');
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END ;
/
now you will check your lot number against you order number in mtl_transaction_lots_temp table
by following query
you fing move_order_line_id in wsh_dilivery_detail table against order_number
SELECT transaction_temp_id FROM mtl_material_transactions_temp
WHERE move_order_line_id = 1850473;
select *
from mtl_transaction_lots_temp where transaction_temp_id =53372002;;
Comments
Post a Comment