DECLARE
CURSOR cur_com
IS
select * from BOM_INVENTORY_COMPONENTS_V bic,bom_bill_of_materials bom
where bom.assembly_item_id = 33003
and bic.bill_sequence_id = bom.bill_sequence_id
and bic.operation_seq_num = 10
and bom.organization_id = 81;
CURSOR cur_lot_num(p_item_id NUMBER,p_org_id NUMBER,p_subinv_code VARCHAR2)
IS
select lot_number,primary_transaction_quantity quantity from mtl_onhand_quantities_detail
where inventory_item_id = p_item_id
and organization_id = p_org_id
and subinventory_code = p_subinv_code
order by orig_date_received asc;
retval number;
ln_interface_id number;
l_return_status VARCHAR2(10);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2(500);
l_trans_count VARCHAR2(100);
v_header_id number;
ln_out NUMBER;
ln_quantity NUMBER;
ln_trn_type_id NUMBER;
ln_item_id NUMBER;
lv_uom_code VARCHAR2(10);
lv_lot_number VARCHAR2(25);
BEGIN
BEGIN
SELECT mtl_material_transactions_s.NEXTVAL
INTO ln_interface_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
ln_interface_id := 0;
END;
BEGIN
SELECT transaction_type_id
INTO ln_trn_type_id
FROM mtl_transaction_types
WHERE transaction_type_name = 'WIP Negative Issue';
EXCEPTION
WHEN OTHERS THEN
ln_trn_type_id := 0;
END;
BEGIN
SELECT msi.inventory_item_id,msi.primary_uom_code,(msi.inventory_item_id||msi.start_auto_lot_number) lot_number
INTO ln_item_id, lv_uom_code,lv_lot_number
FROM bom_operational_routings_v bor,wip_discrete_jobs wdj,mtl_system_items_b msi
WHERE bor.routing_sequence_id = wdj.common_routing_sequence_id
AND bor.attribute5 = msi.segment1
AND bor.organization_id = msi.organization_id
AND wdj.wip_entity_id = 132107;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('Exception in lot number');
-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'FORM-09 UNDER RULE 107 OF FACTORIES RULES 1969.,,,,,,,,,,,,,,,,,');
END;
ln_quantity := 3;
BEGIN
INSERT INTO mtl_transactions_interface
(source_code,
source_line_id,
source_header_id,
process_flag,
transaction_mode,
inventory_item_id,
--revision,
organization_id,
subinventory_code,
-- locator_id,
transaction_quantity,
transaction_uom,
transaction_date,
transaction_type_id,
-- transfer_subinventory,
lock_flag,
last_update_date,
last_updated_by,
created_by,
creation_date,
--attribute1, transaction_source_id, attribute2,
--attribute3, attribute4,
transaction_source_id,
-- attribute9,
transaction_interface_id,
transfer_locator,
wip_entity_type,
operation_seq_num
)
VALUES ('1',
99,
99,
1,
3,
1001,--rec_sl.inventory_item_id,
--rec_sl.revision,
81,--rec_sl.organization_id,
'ASSY_IN',--From Subinventory
-- ln_to_loc_id,
ln_quantity,--transaction_quantity
'Kg',
SYSDATE,
ln_trn_type_id,--Transaction Type Id
-- 'MRB',--To subinventory
2,
SYSDATE,
0,
0,
SYSDATE,
--rec_sl.attribute1, ln_wip_id, rec_sl.header_id,
--rec_sl.attribute2, rec_sl.attribute4,
132107,--transaction_source_id
ln_interface_id,--transaction_interface_id
NULL,--transfer_locator
2,--wip_entity_type
10--operation_seq_num
);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while inserting into MTL_TRANSACTION_INTERFACE');
END;
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id,
lot_number,
lot_expiration_date,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by
--, source_code,
--source_line_id, process_flag, attribute9
)
VALUES (ln_interface_id, --transaction interface id
lv_lot_number,-- rec_mtl_lot.lot_number,
--Lot number
SYSDATE + 100, --Lot expiration date
ln_quantity,-- ln_qty, --transaction quantity
SYSDATE, --last update date
0, --last updated by
SYSDATE, --creation date
0
--,'Subinventory Transfer',
--rec_sl.line_id, 1, ln_dcs_no
);
BEGIN
SELECT mtl_material_transactions_s.NEXTVAL
INTO v_header_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
v_header_id := 0;
END;
BEGIN
UPDATE mtl_transactions_interface
SET transaction_header_id = v_header_id,
--TRANSACTION_INTERFACE_ID =v_header_id,
transaction_date = SYSDATE
WHERE source_code = '1' AND transaction_header_id IS NULL;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while updating MTL_TRANSACTIONS_INTERFACE');
END;
-- COMMIT;
retval :=
apps.inv_txn_manager_pub.process_transactions
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id => v_header_id
);
dbms_output.put_line('l_msg_data:'||l_msg_data);
dbms_output.put_line('l_return_status:'||l_return_status);
dbms_output.put_line('v_header_id:'||v_header_id);
dbms_output.put_line('retval:'||v_header_id);
END;
CURSOR cur_com
IS
select * from BOM_INVENTORY_COMPONENTS_V bic,bom_bill_of_materials bom
where bom.assembly_item_id = 33003
and bic.bill_sequence_id = bom.bill_sequence_id
and bic.operation_seq_num = 10
and bom.organization_id = 81;
CURSOR cur_lot_num(p_item_id NUMBER,p_org_id NUMBER,p_subinv_code VARCHAR2)
IS
select lot_number,primary_transaction_quantity quantity from mtl_onhand_quantities_detail
where inventory_item_id = p_item_id
and organization_id = p_org_id
and subinventory_code = p_subinv_code
order by orig_date_received asc;
retval number;
ln_interface_id number;
l_return_status VARCHAR2(10);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2(500);
l_trans_count VARCHAR2(100);
v_header_id number;
ln_out NUMBER;
ln_quantity NUMBER;
ln_trn_type_id NUMBER;
ln_item_id NUMBER;
lv_uom_code VARCHAR2(10);
lv_lot_number VARCHAR2(25);
BEGIN
BEGIN
SELECT mtl_material_transactions_s.NEXTVAL
INTO ln_interface_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
ln_interface_id := 0;
END;
BEGIN
SELECT transaction_type_id
INTO ln_trn_type_id
FROM mtl_transaction_types
WHERE transaction_type_name = 'WIP Negative Issue';
EXCEPTION
WHEN OTHERS THEN
ln_trn_type_id := 0;
END;
BEGIN
SELECT msi.inventory_item_id,msi.primary_uom_code,(msi.inventory_item_id||msi.start_auto_lot_number) lot_number
INTO ln_item_id, lv_uom_code,lv_lot_number
FROM bom_operational_routings_v bor,wip_discrete_jobs wdj,mtl_system_items_b msi
WHERE bor.routing_sequence_id = wdj.common_routing_sequence_id
AND bor.attribute5 = msi.segment1
AND bor.organization_id = msi.organization_id
AND wdj.wip_entity_id = 132107;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('Exception in lot number');
-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'FORM-09 UNDER RULE 107 OF FACTORIES RULES 1969.,,,,,,,,,,,,,,,,,');
END;
ln_quantity := 3;
BEGIN
INSERT INTO mtl_transactions_interface
(source_code,
source_line_id,
source_header_id,
process_flag,
transaction_mode,
inventory_item_id,
--revision,
organization_id,
subinventory_code,
-- locator_id,
transaction_quantity,
transaction_uom,
transaction_date,
transaction_type_id,
-- transfer_subinventory,
lock_flag,
last_update_date,
last_updated_by,
created_by,
creation_date,
--attribute1, transaction_source_id, attribute2,
--attribute3, attribute4,
transaction_source_id,
-- attribute9,
transaction_interface_id,
transfer_locator,
wip_entity_type,
operation_seq_num
)
VALUES ('1',
99,
99,
1,
3,
1001,--rec_sl.inventory_item_id,
--rec_sl.revision,
81,--rec_sl.organization_id,
'ASSY_IN',--From Subinventory
-- ln_to_loc_id,
ln_quantity,--transaction_quantity
'Kg',
SYSDATE,
ln_trn_type_id,--Transaction Type Id
-- 'MRB',--To subinventory
2,
SYSDATE,
0,
0,
SYSDATE,
--rec_sl.attribute1, ln_wip_id, rec_sl.header_id,
--rec_sl.attribute2, rec_sl.attribute4,
132107,--transaction_source_id
ln_interface_id,--transaction_interface_id
NULL,--transfer_locator
2,--wip_entity_type
10--operation_seq_num
);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while inserting into MTL_TRANSACTION_INTERFACE');
END;
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id,
lot_number,
lot_expiration_date,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by
--, source_code,
--source_line_id, process_flag, attribute9
)
VALUES (ln_interface_id, --transaction interface id
lv_lot_number,-- rec_mtl_lot.lot_number,
--Lot number
SYSDATE + 100, --Lot expiration date
ln_quantity,-- ln_qty, --transaction quantity
SYSDATE, --last update date
0, --last updated by
SYSDATE, --creation date
0
--,'Subinventory Transfer',
--rec_sl.line_id, 1, ln_dcs_no
);
BEGIN
SELECT mtl_material_transactions_s.NEXTVAL
INTO v_header_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
v_header_id := 0;
END;
BEGIN
UPDATE mtl_transactions_interface
SET transaction_header_id = v_header_id,
--TRANSACTION_INTERFACE_ID =v_header_id,
transaction_date = SYSDATE
WHERE source_code = '1' AND transaction_header_id IS NULL;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while updating MTL_TRANSACTIONS_INTERFACE');
END;
-- COMMIT;
retval :=
apps.inv_txn_manager_pub.process_transactions
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id => v_header_id
);
dbms_output.put_line('l_msg_data:'||l_msg_data);
dbms_output.put_line('l_return_status:'||l_return_status);
dbms_output.put_line('v_header_id:'||v_header_id);
dbms_output.put_line('retval:'||v_header_id);
END;
