Oracle Application: April 2022

Monday 25 April 2022

Oracle EBS - Enable Item as Lot Control PLSQL Script

DECLARE

l_item_tbl_typ ego_item_pub.item_tbl_type;

x_item_tbl_typ ego_item_pub.item_tbl_type;

x_return_status VARCHAR2(100);

x_msg_count NUMBER;

x_message_list error_handler.error_tbl_type;

-- User Variables: Update for your environment ~~!

l_resp_appl_id NUMBER := 401; --FND_PROFILE.VALUE ('RESP_APPL_ID');

l_resp_id NUMBER := 20634; --FND_PROFILE.VALUE ('RESP_ID');

l_user_id NUMBER := -1; --26406 --FND_PROFILE.VALUE ('USER_ID');

BEGIN

fnd_global.apps_initialize(user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_resp_appl_id);

--Calling Enable Item as Lot Control procedure--

FOR i IN (

SELECT

msib.inventory_item_id,

msib.organization_id,

msib.segment1,

msib.lot_control_code,

msib.lot_split_enabled,

msib.lot_merge_enabled,

msib.lot_divisible_flag

FROM

mtl_system_items_b msib

WHERE

msib.organization_id = 85

AND msib.segment1 = '320672'

AND msib.last_update_date >= sysdate - 10

ORDER BY

msib.last_update_date DESC

) LOOP

BEGIN

l_item_tbl_typ(1).transaction_type := ego_item_pub.g_ttype_update;

l_item_tbl_typ(1).inventory_item_id := i.inventory_item_id;

l_item_tbl_typ(1).organization_id := i.organization_id;

l_item_tbl_typ(1).lot_control_code := 2;

l_item_tbl_typ(1).lot_split_enabled := 'Y';

l_item_tbl_typ(1).lot_merge_enabled := 'Y';

-- l_item_tbl_typ(1).auto_lot_alpha_prefix := 'L';

-- l_item_tbl_typ(1).start_auto_lot_number := 1;

ego_item_pub.process_items(p_api_version => 1.0, p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_true,

p_item_tbl => l_item_tbl_typ,

x_item_tbl => x_item_tbl_typ,

p_role_grant_tbl => ego_item_pub.g_miss_role_grant_tbl,

x_return_status => x_return_status,

x_msg_count => x_msg_count);

dbms_output.put_line('x_return_status : ' || x_return_status);

error_handler.get_message_list(x_message_list);

FOR i IN 1..x_message_list.count LOOP

dbms_output.put_line(x_message_list(i).message_text);

END LOOP;

COMMIT;

END xxtlm_enable_item_lot;

END LOOP;

END;

Oracle EBS - Clear Reservation PLSQL Script

DECLARE

p_rsv apps.inv_reservation_global.mtl_reservation_rec_type;

p_dummy_sn apps.inv_reservation_global.serial_number_tbl_type;

l_msg_count NUMBER;

l_msg_data VARCHAR2(240);

l_status VARCHAR2(1);

l_index_out NUMBER;

v_reserv_id NUMBER;

l_err_status VARCHAR2(50);

l_err_mesg VARCHAR2(2000);

-- User Variables: Update for your environment

l_resp_appl_id NUMBER := 401; --FND_PROFILE.VALUE ('RESP_APPL_ID');

l_resp_id NUMBER := 20634; --FND_PROFILE.VALUE ('RESP_ID');

l_user_id NUMBER := -1; --26406 --FND_PROFILE.VALUE ('USER_ID');

-- Item/Organization Variables

-- l_organization_id NUMBER := 2177;

BEGIN

fnd_global.apps_initialize(user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_resp_appl_id);

FOR r_reserve IN (

SELECT

xx.reservation_id,

xx.segment1 item_code,

xx.inventory_item_id,

xx.organization_id,

xx.locator_id

FROM

(

SELECT

msib.segment1,

mp.organization_code,

msib.organization_id,

msib.inventory_item_id,

msib.purchasing_item_flag,

msib.shippable_item_flag,

msib.inventory_item_flag,

lot_control_code,

decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',

'At Receipt',

'At Issue') "Serial Control",

mc.segment1 "PPL",

mc.segment2 "SPL",

mc.segment3 "ITEM CATEGORY",

mr.reservation_quantity reserved_qty,

mr.reservation_id,

mr.locator_id

FROM

mtl_system_items_b msib,

mtl_item_categories mic,

mtl_categories mc,

mtl_parameters mp,

mtl_reservations mr

WHERE

msib.inventory_item_id = mic.inventory_item_id

AND msib.organization_id = mic.organization_id

AND msib.organization_id = mp.organization_id

AND mic.category_id = mc.category_id

AND mic.category_set_id = 1

AND mc.structure_id = 101

AND msib.enabled_flag = 'Y'

AND msib.lot_control_code = 1

-- AND msib.segment1='NTTC90ABE6'

AND nvl(msib.end_date_active, sysdate) >= sysdate

AND upper(mc.segment1) IN ( 'JUNIPER', 'CIENA', 'UTAHSCI',

'WESTELL',

'ADTRAN',

'NOKIA',

'ALCATEL',

'ALCATEL-LUCENT',

'TELLABS',

'FUJI',

'DELTA ELECTRONICS',

'CORNING' )

AND mc.disable_date IS NULL

AND msib.organization_id IN ( 85, 97 )

AND mr.inventory_item_id = msib.inventory_item_id

AND mr.organization_id = msib.organization_id

ORDER BY

mc.segment1

) xx

WHERE

reserved_qty <> 0

) LOOP

dbms_output.put_line('Reservation ID : ' || r_reserve.reservation_id);

p_rsv.reservation_id := r_reserve.reservation_id;

apps.inv_reservation_pub.delete_reservation(p_api_version_number => 1.0,

p_init_msg_lst => fnd_api.g_false,

p_rsv_rec => p_rsv,

p_serial_number => p_dummy_sn,

-- p_validation_flag => fnd_api.g_true,

x_return_status => l_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data);

dbms_output.put_line('Reservation API : ' || l_status);

IF l_status <> 'S' THEN

fnd_msg_pub.get(p_msg_index => l_msg_count, p_data => l_msg_data, p_encoded => 'F',

p_msg_index_out => l_index_out);

l_err_status := 'E';

l_err_mesg := 'Delete Allocations API failed ' || rtrim(l_msg_data);

dbms_output.put_line('API failed ' || l_err_mesg);

ELSE

l_err_status := 'S';

l_err_mesg := NULL;

dbms_output.put_line('API success ' || l_err_mesg);

END IF;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('p_msg_cnt: ');

END;

Oracle EBS - Create Miscellaneous Receipt Transaction PLSQL Script

create or replace PROCEDURE xxtlm_misc_rcpt (

errbuf OUT VARCHAR2,

retcode OUT VARCHAR2

) IS

x_return_status VARCHAR2(1000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(1000);

l_origination_date DATE;

l_segment1 VARCHAR2(150);

l_segment2 VARCHAR2(150);

l_segment3 VARCHAR2(150);

l_segment4 VARCHAR2(150);

l_segment5 VARCHAR2(150);

l_segment6 VARCHAR2(150);

l_segment7 VARCHAR2(150);

l_segment8 VARCHAR2(150);

l_ccd_check NUMBER;

CURSOR main_cur IS

SELECT

xidm.*,

msib.lot_control_code lot_enabled

FROM

xxtlm.xxtlm_item_dtls_mr xidm,

mtl_system_items_b msib

WHERE

1 = 1

AND xidm.item_code IN (

SELECT

item_code

FROM

xxtlm.xxtlm_item_dtls_mi

WHERE

nvl(status_flag, 'N') = 'S'

)

AND xidm.organization_id IN ( 85 --INA

, 97 --CA1

)

AND nvl(xidm.status_flag, 'N') <> 'S'

AND xidm.inventory_item_id = msib.inventory_item_id

AND xidm.organization_id = msib.organization_id

-- AND xidm.item_code = 'FC9681ECD2'

ORDER BY

item_code,

orig_date_received;

BEGIN

fnd_file.put_line(fnd_file.log, '-----------------Start MISC Receipt Process-------------------');

--Session Initialize--

fnd_global.apps_initialize(user_id => fnd_profile.value('USER_ID'), resp_id => fnd_profile.value('RESP_ID'),

resp_appl_id => fnd_profile.value('RESP_APPL_ID'));

-- fnd_global.apps_initialize(user_id => 25665, resp_id => 52895, resp_appl_id => 401);

-- fnd_msg_pub.initialize;

dbms_output.enable(100000000);

FOR rec IN main_cur LOOP

x_return_status := NULL;

x_msg_data := NULL;

IF rec.lot_enabled = 2 THEN

BEGIN

l_segment1 := '10';

l_segment2 := '00';

l_segment3 :=

CASE

WHEN rec.is_consigned = 1 THEN

'3205'

ELSE '6060'

END;

l_segment4 := '10';

l_segment5 := '01';

l_segment6 :=

CASE

WHEN rec.planning_organization_id = 85 THEN

'11'

WHEN rec.planning_organization_id = 97 THEN

'21'

ELSE '00'

END;

l_segment7 := '00';

l_segment8 := '0000';

EXCEPTION

WHEN OTHERS THEN

l_segment1 := NULL;

l_segment2 := NULL;

l_segment3 := NULL;

l_segment4 := NULL;

l_segment5 := NULL;

l_segment6 := NULL;

l_segment7 := NULL;

l_segment8 := NULL;

END;

BEGIN

SELECT

COUNT(1)

INTO l_ccd_check

FROM

gl_code_combinations_kfv

WHERE

1 = 1

AND segment1 = l_segment1

AND segment2 = l_segment2

AND segment3 = l_segment3

AND segment4 = l_segment4

AND segment5 = l_segment5

AND segment6 = l_segment6

AND segment7 = l_segment7

AND segment8 = l_segment8;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error: Invalid Account Code Combination: '

|| rec.item_code

|| '. locator: '

|| rec.locators);

fnd_file.put_line(fnd_file.log,

'Error: Invalid Account Code Combination: '

|| rec.item_code

|| 'Organization Code:'

|| rec.organization_code);

END;

dbms_output.put_line('Code Combination'

|| l_segment1

|| '-'

|| l_segment2

|| '-'

|| l_segment3

|| '-'

|| l_segment4

|| '-'

|| l_segment5

|| '-'

|| l_segment6

|| '-'

|| l_segment7

|| '-'

|| l_segment8);

IF l_ccd_check <> 0 THEN

BEGIN

xxtlm_pkmtl_transaction(p_item_id => rec.inventory_item_id, p_organization_id => rec.planning_organization_id,

p_owning_organization_id => rec.owning_organization_id,

p_owning_tp_type => rec.owning_tp_type,

p_planning_tp_type => rec.planning_tp_type,

p_planning_organization_id => rec.planning_organization_id,

p_subinv => rec.subinventory_code,

p_loc => rec.locators,

p_lot_no => 'C-'

|| rec.organization_code

|| '-'

|| rec.inventory_item_id

|| to_char(rec.orig_date_received, 'MMDDYYMISS-')

|| '1',

p_origination_date => rec.orig_date_received,

p_uom_code => rec.primary_uom_code,

p_quantity => rec.onhand_quantity,

p_transaction_type => 'Miscellaneous receipt',

p_trans_type_ref => rec.organization_code

|| ' MISC RCPT '

|| trunc(sysdate),

p_primary_qty => NULL,

p_clei_code => rec.item_code,

p_segment1 => l_segment1,

p_segment2 => l_segment2,

p_segment3 => l_segment3,

p_segment4 => l_segment4,

p_segment5 => l_segment5,

p_segment6 => l_segment6,

p_segment7 => l_segment7,

p_segment8 => l_segment8,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data);

dbms_output.put_line('X_RETURN_STATUS->'

|| x_return_status

|| ' X_MSG_COUNT->'

|| x_msg_count

|| ' X_MSG_DATA->'

|| x_msg_data);

END;

ELSE

dbms_output.put_line('Error: Invalid Account Code Combination: '

|| rec.item_code

|| '. locator: '

|| rec.locators);

fnd_file.put_line(fnd_file.log,

'Error: Invalid Account Code Combination: '

|| rec.item_code

|| 'Organization Code:'

|| rec.organization_code);

x_return_status := 'E';

x_msg_data := 'Error Invalid Account Code Combination';

END IF;

BEGIN

UPDATE xxtlm.xxtlm_item_dtls_mr

SET

status_flag = x_return_status,

error_msg = x_msg_data

WHERE

organization_id = rec.organization_id

AND inventory_item_id = rec.inventory_item_id

AND subinventory_code = rec.subinventory_code

AND locators = rec.locators

AND onhand_quantity = rec.onhand_quantity;

COMMIT;

fnd_file.put_line(fnd_file.log, ' Organization Code: '

|| rec.organization_code

|| ' ,item: '

|| rec.item_code

|| ' ,status flag: '

|| x_return_status);

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error while updating status for item: '

|| rec.item_code

|| '. locator: '

|| rec.locators);

fnd_file.put_line(fnd_file.log,

'Error while updating status for item:'

|| rec.item_code

|| 'Organization Code:'

|| rec.organization_code);

END;

ELSE

UPDATE xxtlm.xxtlm_item_dtls_mr

SET

status_flag = 'E',

error_msg = 'Item is not Lot Enabled'

WHERE

organization_id = rec.organization_id

AND inventory_item_id = rec.inventory_item_id

AND subinventory_code = rec.subinventory_code

AND locators = rec.locators

AND onhand_quantity = rec.onhand_quantity;

COMMIT;

END IF;

END LOOP;

fnd_file.put_line(fnd_file.log, '-----------------End MISC Receipt Process-------------------');

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Unexpected Error in Main block: ' || sqlerrm);

fnd_file.put_line(fnd_file.log, 'Error: ' || sqlerrm);

END xxtlm_misc_rcpt;

/***--Dependent Procedure---***/

create or replace PROCEDURE xxtlm_pkmtl_transaction (

p_item_id IN VARCHAR2,

p_organization_id IN NUMBER,

p_owning_organization_id IN mtl_onhand_quantities_detail.owning_organization_id%TYPE,

p_owning_tp_type IN mtl_onhand_quantities_detail.owning_tp_type%TYPE,

p_planning_tp_type IN mtl_onhand_quantities_detail.planning_tp_type%TYPE,

p_planning_organization_id IN mtl_onhand_quantities_detail.planning_organization_id%TYPE,

p_subinv IN VARCHAR2,

p_loc IN VARCHAR2,

p_lot_no IN VARCHAR2,

p_origination_date IN DATE,

p_uom_code IN VARCHAR2,

p_quantity IN NUMBER,

p_transaction_type IN VARCHAR2,

p_trans_type_ref IN VARCHAR2,

p_primary_qty IN NUMBER,

p_clei_code IN VARCHAR2,

p_segment1 IN VARCHAR2,

p_segment2 IN VARCHAR2,

p_segment3 IN VARCHAR2,

p_segment4 IN VARCHAR2,

p_segment5 IN VARCHAR2,

p_segment6 IN VARCHAR2,

p_segment7 IN VARCHAR2,

p_segment8 IN VARCHAR2,

x_return_status OUT VARCHAR2,

x_msg_count OUT NUMBER,

x_msg_data OUT VARCHAR2

) IS

l_transaction_id NUMBER;

l_loc_id NUMBER;

l_trn_type_id NUMBER;

l_segment1 VARCHAR2(150);

l_segment2 VARCHAR2(150);

l_segment3 VARCHAR2(150);

l_segment4 VARCHAR2(150);

l_segment5 VARCHAR2(150);

l_segment6 VARCHAR2(150);

l_segment7 VARCHAR2(150);

l_segment8 VARCHAR2(150);

l_segment9 VARCHAR2(150);

l_segment10 VARCHAR2(150);

l_segment11 VARCHAR2(150);

l_expiration_date DATE;

l_trans_count NUMBER;

lc_result NUMBER;

l_subinv_code mtl_item_locations_kfv.subinventory_code%TYPE;

r_mtl_trns_rec mtl_transactions_interface%rowtype;

r_mtl_lot_rec mtl_transaction_lots_interface%rowtype;

g_l_transaction_id NUMBER;

g_l_lpn_id NUMBER := 0;

CURSOR cur_tns_rec IS

SELECT

mtt.*

FROM

mtl_transaction_types mtt

WHERE

mtt.transaction_type_name = p_transaction_type;

r_tns_rec cur_tns_rec%rowtype;

PRAGMA autonomous_transaction;

BEGIN

x_return_status := NULL;

x_msg_data := NULL;

dbms_output.put_line('================================================================================');

BEGIN

SELECT

mtl_material_transactions_s.NEXTVAL

INTO l_transaction_id

FROM

dual;

EXCEPTION

WHEN OTHERS THEN

l_transaction_id := NULL;

END;

OPEN cur_tns_rec;

FETCH cur_tns_rec INTO r_tns_rec;

CLOSE cur_tns_rec;

BEGIN

-- l_segment1 := '10';

-- l_segment2 := '00';

-- l_segment3 := '3205';

-- l_segment4 := '10';

-- l_segment5 := '01';

-- l_segment6 := '00';

-- l_segment7 := '00';

-- l_segment8 := '0000';

l_segment1 := p_segment1;

l_segment2 := p_segment2;

l_segment3 := p_segment3;

l_segment4 := p_segment4;

l_segment5 := p_segment5;

l_segment6 := p_segment6;

l_segment7 := p_segment7;

l_segment8 := p_segment8;

EXCEPTION

WHEN OTHERS THEN

l_segment1 := NULL;

l_segment2 := NULL;

l_segment6 := NULL;

l_segment3 := NULL;

l_segment4 := NULL;

l_segment5 := NULL;

l_segment7 := NULL;

l_segment8 := NULL;

l_segment9 := NULL;

l_segment10 := NULL;

END;

BEGIN

SELECT

inventory_location_id,

subinventory_code

INTO

l_loc_id,

l_subinv_code

FROM

mtl_item_locations_kfv

WHERE

concatenated_segments = p_loc

AND organization_id = p_organization_id;

EXCEPTION

WHEN OTHERS THEN

l_loc_id := NULL;

END;

dbms_output.put_line('p_loc: ' || p_loc);

dbms_output.put_line('l_loc_id: ' || l_loc_id);

dbms_output.put_line('p_lot_no: ' || p_lot_no);

dbms_output.put_line('Transaction_type: ' || r_tns_rec.transaction_type_name);

dbms_output.put_line('p_quantity: ' || p_quantity);

BEGIN

r_mtl_trns_rec := NULL;

g_l_transaction_id := NULL;

r_mtl_trns_rec.transaction_uom := p_uom_code;

r_mtl_trns_rec.transaction_date := sysdate;

r_mtl_trns_rec.source_code := p_transaction_type;

r_mtl_trns_rec.source_line_id := l_transaction_id; -- 422170

r_mtl_trns_rec.source_header_id := l_transaction_id; -- 167091

r_mtl_trns_rec.process_flag := 1;

r_mtl_trns_rec.transaction_mode := 3;

r_mtl_trns_rec.lock_flag := 2;

r_mtl_trns_rec.locator_id := l_loc_id;

r_mtl_trns_rec.last_update_date := sysdate;

r_mtl_trns_rec.last_updated_by := fnd_global.user_id;

r_mtl_trns_rec.creation_date := sysdate;

r_mtl_trns_rec.created_by := fnd_global.user_id;

r_mtl_trns_rec.inventory_item_id := p_item_id;

r_mtl_trns_rec.subinventory_code := nvl(p_subinv, l_subinv_code);

r_mtl_trns_rec.organization_id := p_organization_id;

r_mtl_trns_rec.transaction_quantity := p_quantity;

r_mtl_trns_rec.primary_quantity := p_primary_qty;

r_mtl_trns_rec.transaction_type_id := r_tns_rec.transaction_type_id;

r_mtl_trns_rec.transaction_source_type_id := r_tns_rec.transaction_source_type_id;

r_mtl_trns_rec.transaction_action_id := r_tns_rec.transaction_action_id;

--Added by Venky

r_mtl_trns_rec.owning_organization_id := p_owning_organization_id; --3091

r_mtl_trns_rec.owning_tp_type := p_owning_tp_type; --1

r_mtl_trns_rec.planning_organization_id := p_planning_organization_id;

r_mtl_trns_rec.planning_tp_type := p_planning_tp_type; --2

-- End Added by Venky

r_mtl_trns_rec.dst_segment1 := l_segment1;

r_mtl_trns_rec.dst_segment2 := l_segment2;

r_mtl_trns_rec.dst_segment3 := l_segment3;

r_mtl_trns_rec.dst_segment4 := l_segment4;

r_mtl_trns_rec.dst_segment5 := l_segment5;

r_mtl_trns_rec.dst_segment6 := l_segment6;

r_mtl_trns_rec.dst_segment7 := l_segment7;

r_mtl_trns_rec.dst_segment8 := l_segment8;

r_mtl_trns_rec.dst_segment9 := l_segment9;

r_mtl_trns_rec.dst_segment10 := l_segment10;

r_mtl_trns_rec.transaction_interface_id := l_transaction_id;

r_mtl_trns_rec.transaction_header_id := l_transaction_id;

r_mtl_trns_rec.transaction_reference := p_trans_type_ref;

IF g_l_lpn_id > 0 THEN

r_mtl_trns_rec.transfer_lpn_id := g_l_lpn_id;

dbms_output.put_line('g_l_lpn_id: ' || g_l_lpn_id);

END IF;

-- Insert MTI for inv open transactions.

INSERT INTO mtl_transactions_interface VALUES r_mtl_trns_rec;

dbms_output.put_line('Record inserted into mtl_transactions_interface with transaction_header_id: ' || l_transaction_id);

g_l_transaction_id := l_transaction_id;

--dbms_output.put_line('Inserted a record'||l_transaction_id);

x_return_status := fnd_api.g_ret_sts_success;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.output,

'in exception ---- inserting data into MTL_TRANSACTIONS_INTERFACE : ' || sqlerrm);

dbms_output.put_line('Error' || sqlerrm);

x_return_status := fnd_api.g_ret_sts_error;

x_msg_count := x_msg_count + 1;

x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :' || sqlerrm;

END;

dbms_output.put_line('STEP1');

IF x_return_status != fnd_api.g_ret_sts_error THEN --Successful return of first insert

IF p_lot_no IS NOT NULL THEN

--Derive lot expiration date for the lot number

BEGIN

SELECT

expiration_date

INTO l_expiration_date

FROM

mtl_lot_numbers

WHERE

lot_number = p_lot_no

AND inventory_item_id = p_item_id

AND organization_id = p_organization_id;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Exception in fetching lot expiration_date: ' || sqlerrm);

l_expiration_date := NULL;

dbms_output.put_line('STEP2');

END;

dbms_output.put_line('STEP3');

dbms_output.put_line('Expiration_date: ' || l_expiration_date);

dbms_output.put_line('STEP3.1');

BEGIN

dbms_output.put_line('STEP4');

--Insert lot number interface if the item is lot controlled.

r_mtl_lot_rec := NULL;

g_l_transaction_id := l_transaction_id;

r_mtl_lot_rec.transaction_interface_id := l_transaction_id;

r_mtl_lot_rec.source_code := p_transaction_type;

r_mtl_lot_rec.source_line_id := l_transaction_id;

r_mtl_lot_rec.last_update_date := sysdate;

r_mtl_lot_rec.last_updated_by := fnd_global.user_id;

r_mtl_lot_rec.creation_date := sysdate;

r_mtl_lot_rec.created_by := fnd_global.user_id;

r_mtl_lot_rec.last_update_login := fnd_global.login_id;

r_mtl_lot_rec.lot_number := p_lot_no;

r_mtl_lot_rec.origination_date := p_origination_date;

r_mtl_lot_rec.lot_expiration_date := l_expiration_date;

r_mtl_lot_rec.transaction_quantity := p_quantity;

r_mtl_lot_rec.primary_quantity := p_quantity;

r_mtl_lot_rec.serial_transaction_temp_id := l_transaction_id;

--New CLEI Code--

r_mtl_lot_rec.lot_attribute_category := '102';

r_mtl_lot_rec.c_attribute1 := p_clei_code;

--New CLEI Code--

dbms_output.put_line('STEP5');

INSERT INTO mtl_transaction_lots_interface VALUES r_mtl_lot_rec;

dbms_output.put_line('STEP6');

EXCEPTION

WHEN OTHERS THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_count := x_msg_count + 1;

x_msg_data := 'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :' || sqlerrm;

dbms_output.put_line('STEP7');

END;

END IF;

dbms_output.put_line('STEP8');

COMMIT;

--call Inventory open transaction api to perform transfer of material from source locator to destination locator.

dbms_output.put_line('STEP9');

x_return_status := NULL;

x_msg_count := 0;

x_msg_data := NULL;

dbms_output.put_line('Before Calling process_transactions' || l_transaction_id);

lc_result := inv_txn_manager_pub.process_transactions(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_true,

p_validation_level => fnd_api.g_valid_level_full,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_trans_count => l_trans_count,

p_table => 1,

p_header_id => l_transaction_id);

dbms_output.put_line('STEP10');

dbms_output.put_line('Return Status' || x_return_status);

IF nvl(x_return_status, 'N') <> fnd_api.g_ret_sts_success THEN

x_return_status := fnd_api.g_ret_sts_error;

dbms_output.put_line('Return Status: ' || x_return_status);

dbms_output.put_line('Message: ' || x_msg_data);

dbms_output.put_line('STEP11');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('In Exception' || sqlerrm);

fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);

dbms_output.put_line('STEP12');

ROLLBACK;

END xxtlm_pkmtl_transaction;

Oracle EBS - Create Miscellaneous Issue Transaction PLSQL Script

--SET SERVEROUTPUT ON

DECLARE

x_return_status VARCHAR2(1000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(1000);

l_segment1 VARCHAR2(150);

l_segment2 VARCHAR2(150);

l_segment3 VARCHAR2(150);

l_segment4 VARCHAR2(150);

l_segment5 VARCHAR2(150);

l_segment6 VARCHAR2(150);

l_segment7 VARCHAR2(150);

l_segment8 VARCHAR2(150);

CURSOR main_cur IS

SELECT

FROM

xxtlm.xxtlm_item_dtls_mi

WHERE

1 = 1

-- AND item_code IN ('CBB050E')

AND organization_id IN ( 85 --INA

, 97 --CA1

)

AND nvl(status_flag, 'N') <> 'S';

BEGIN

dbms_output.enable(100000000);

FOR rec IN main_cur LOOP

BEGIN

l_segment1 := '10';

l_segment2 := '00';

l_segment3 := CASE WHEN rec.is_consigned = 1 THEN '3205' ELSE '6060' END;

l_segment4 := '10';

l_segment5 := '01';

l_segment6 := CASE WHEN rec.planning_organization_id = 85 THEN '11' WHEN rec.planning_organization_id = 97 THEN '21' ELSE '00' END;

l_segment7 := '00';

l_segment8 := '0000';

xxtlm_pkmtl_transaction(p_item_id => rec.inventory_item_id, p_organization_id => rec.planning_organization_id,

p_owning_organization_id => NULL,

p_owning_tp_type => NULL,

p_planning_tp_type => NULL,

p_planning_organization_id => NULL,

p_subinv => rec.subinventory_code,

p_loc => rec.locators,

p_lot_no => NULL,

p_origination_date => NULL,

p_uom_code => rec.primary_uom_code,

p_quantity => rec.onhand_quantity * - 1,

p_transaction_type => 'Miscellaneous issue',

p_trans_type_ref => rec.organization_code

|| ' MISC ISSUE '

|| trunc(sysdate),

p_primary_qty => NULL,

p_clei_code => NULL, ---need to change logic

p_segment1 => l_segment1,

p_segment2 => l_segment2,

p_segment3 => l_segment3,

p_segment4 => l_segment4,

p_segment5 => l_segment5,

p_segment6 => l_segment6,

p_segment7 => l_segment7,

p_segment8 => l_segment8,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data);

dbms_output.put_line('X_RETURN_STATUS->'

|| x_return_status

|| ' X_MSG_COUNT->'

|| x_msg_count

|| ' X_MSG_DATA->'

|| x_msg_data);

END;

BEGIN

UPDATE xxtlm.xxtlm_item_dtls_mi

SET

status_flag = x_return_status,

ERROR_MSG=x_msg_data

WHERE

organization_id = rec.organization_id

AND inventory_item_id = rec.inventory_item_id

AND subinventory_code = rec.subinventory_code

AND locators = rec.locators

AND onhand_quantity = rec.onhand_quantity;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error while updating status for item: '

|| rec.item_code

|| '. locator: '

|| rec.locators);

END;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Unexpected Error in Main block: ' || sqlerrm);

END;

/***--Dependent Procedure---***/

create or replace PROCEDURE xxtlm_pkmtl_transaction (

p_item_id IN VARCHAR2,

p_organization_id IN NUMBER,

p_owning_organization_id IN mtl_onhand_quantities_detail.owning_organization_id%TYPE,

p_owning_tp_type IN mtl_onhand_quantities_detail.owning_tp_type%TYPE,

p_planning_tp_type IN mtl_onhand_quantities_detail.planning_tp_type%TYPE,

p_planning_organization_id IN mtl_onhand_quantities_detail.planning_organization_id%TYPE,

p_subinv IN VARCHAR2,

p_loc IN VARCHAR2,

p_lot_no IN VARCHAR2,

p_origination_date IN DATE,

p_uom_code IN VARCHAR2,

p_quantity IN NUMBER,

p_transaction_type IN VARCHAR2,

p_trans_type_ref IN VARCHAR2,

p_primary_qty IN NUMBER,

p_clei_code IN VARCHAR2,

p_segment1 IN VARCHAR2,

p_segment2 IN VARCHAR2,

p_segment3 IN VARCHAR2,

p_segment4 IN VARCHAR2,

p_segment5 IN VARCHAR2,

p_segment6 IN VARCHAR2,

p_segment7 IN VARCHAR2,

p_segment8 IN VARCHAR2,

x_return_status OUT VARCHAR2,

x_msg_count OUT NUMBER,

x_msg_data OUT VARCHAR2

) IS

l_transaction_id NUMBER;

l_loc_id NUMBER;

l_trn_type_id NUMBER;

l_segment1 VARCHAR2(150);

l_segment2 VARCHAR2(150);

l_segment3 VARCHAR2(150);

l_segment4 VARCHAR2(150);

l_segment5 VARCHAR2(150);

l_segment6 VARCHAR2(150);

l_segment7 VARCHAR2(150);

l_segment8 VARCHAR2(150);

l_segment9 VARCHAR2(150);

l_segment10 VARCHAR2(150);

l_segment11 VARCHAR2(150);

l_expiration_date DATE;

l_trans_count NUMBER;

lc_result NUMBER;

l_subinv_code mtl_item_locations_kfv.subinventory_code%TYPE;

r_mtl_trns_rec mtl_transactions_interface%rowtype;

r_mtl_lot_rec mtl_transaction_lots_interface%rowtype;

g_l_transaction_id NUMBER;

g_l_lpn_id NUMBER := 0;

CURSOR cur_tns_rec IS

SELECT

mtt.*

FROM

mtl_transaction_types mtt

WHERE

mtt.transaction_type_name = p_transaction_type;

r_tns_rec cur_tns_rec%rowtype;

PRAGMA autonomous_transaction;

BEGIN

x_return_status := NULL;

x_msg_data := NULL;

dbms_output.put_line('================================================================================');

BEGIN

SELECT

mtl_material_transactions_s.NEXTVAL

INTO l_transaction_id

FROM

dual;

EXCEPTION

WHEN OTHERS THEN

l_transaction_id := NULL;

END;

OPEN cur_tns_rec;

FETCH cur_tns_rec INTO r_tns_rec;

CLOSE cur_tns_rec;

BEGIN

-- l_segment1 := '10';

-- l_segment2 := '00';

-- l_segment3 := '3205';

-- l_segment4 := '10';

-- l_segment5 := '01';

-- l_segment6 := '00';

-- l_segment7 := '00';

-- l_segment8 := '0000';

l_segment1 := p_segment1;

l_segment2 := p_segment2;

l_segment3 := p_segment3;

l_segment4 := p_segment4;

l_segment5 := p_segment5;

l_segment6 := p_segment6;

l_segment7 := p_segment7;

l_segment8 := p_segment8;

EXCEPTION

WHEN OTHERS THEN

l_segment1 := NULL;

l_segment2 := NULL;

l_segment6 := NULL;

l_segment3 := NULL;

l_segment4 := NULL;

l_segment5 := NULL;

l_segment7 := NULL;

l_segment8 := NULL;

l_segment9 := NULL;

l_segment10 := NULL;

END;

BEGIN

SELECT

inventory_location_id,

subinventory_code

INTO

l_loc_id,

l_subinv_code

FROM

mtl_item_locations_kfv

WHERE

concatenated_segments = p_loc

AND organization_id = p_organization_id;

EXCEPTION

WHEN OTHERS THEN

l_loc_id := NULL;

END;

dbms_output.put_line('p_loc: ' || p_loc);

dbms_output.put_line('l_loc_id: ' || l_loc_id);

dbms_output.put_line('p_lot_no: ' || p_lot_no);

dbms_output.put_line('Transaction_type: ' || r_tns_rec.transaction_type_name);

dbms_output.put_line('p_quantity: ' || p_quantity);

BEGIN

r_mtl_trns_rec := NULL;

g_l_transaction_id := NULL;

r_mtl_trns_rec.transaction_uom := p_uom_code;

r_mtl_trns_rec.transaction_date := sysdate;

r_mtl_trns_rec.source_code := p_transaction_type;

r_mtl_trns_rec.source_line_id := l_transaction_id; -- 422170

r_mtl_trns_rec.source_header_id := l_transaction_id; -- 167091

r_mtl_trns_rec.process_flag := 1;

r_mtl_trns_rec.transaction_mode := 3;

r_mtl_trns_rec.lock_flag := 2;

r_mtl_trns_rec.locator_id := l_loc_id;

r_mtl_trns_rec.last_update_date := sysdate;

r_mtl_trns_rec.last_updated_by := fnd_global.user_id;

r_mtl_trns_rec.creation_date := sysdate;

r_mtl_trns_rec.created_by := fnd_global.user_id;

r_mtl_trns_rec.inventory_item_id := p_item_id;

r_mtl_trns_rec.subinventory_code := nvl(p_subinv, l_subinv_code);

r_mtl_trns_rec.organization_id := p_organization_id;

r_mtl_trns_rec.transaction_quantity := p_quantity;

r_mtl_trns_rec.primary_quantity := p_primary_qty;

r_mtl_trns_rec.transaction_type_id := r_tns_rec.transaction_type_id;

r_mtl_trns_rec.transaction_source_type_id := r_tns_rec.transaction_source_type_id;

r_mtl_trns_rec.transaction_action_id := r_tns_rec.transaction_action_id;

--Added by Venky

r_mtl_trns_rec.owning_organization_id := p_owning_organization_id; --3091

r_mtl_trns_rec.owning_tp_type := p_owning_tp_type; --1

r_mtl_trns_rec.planning_organization_id := p_planning_organization_id;

r_mtl_trns_rec.planning_tp_type := p_planning_tp_type; --2

-- End Added by Venky

r_mtl_trns_rec.dst_segment1 := l_segment1;

r_mtl_trns_rec.dst_segment2 := l_segment2;

r_mtl_trns_rec.dst_segment3 := l_segment3;

r_mtl_trns_rec.dst_segment4 := l_segment4;

r_mtl_trns_rec.dst_segment5 := l_segment5;

r_mtl_trns_rec.dst_segment6 := l_segment6;

r_mtl_trns_rec.dst_segment7 := l_segment7;

r_mtl_trns_rec.dst_segment8 := l_segment8;

r_mtl_trns_rec.dst_segment9 := l_segment9;

r_mtl_trns_rec.dst_segment10 := l_segment10;

r_mtl_trns_rec.transaction_interface_id := l_transaction_id;

r_mtl_trns_rec.transaction_header_id := l_transaction_id;

r_mtl_trns_rec.transaction_reference := p_trans_type_ref;

IF g_l_lpn_id > 0 THEN

r_mtl_trns_rec.transfer_lpn_id := g_l_lpn_id;

dbms_output.put_line('g_l_lpn_id: ' || g_l_lpn_id);

END IF;

-- Insert MTI for inv open transactions.

INSERT INTO mtl_transactions_interface VALUES r_mtl_trns_rec;

dbms_output.put_line('Record inserted into mtl_transactions_interface with transaction_header_id: ' || l_transaction_id);

g_l_transaction_id := l_transaction_id;

--dbms_output.put_line('Inserted a record'||l_transaction_id);

x_return_status := fnd_api.g_ret_sts_success;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.output,

'in exception ---- inserting data into MTL_TRANSACTIONS_INTERFACE : ' || sqlerrm);

dbms_output.put_line('Error' || sqlerrm);

x_return_status := fnd_api.g_ret_sts_error;

x_msg_count := x_msg_count + 1;

x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :' || sqlerrm;

END;

dbms_output.put_line('STEP1');

IF x_return_status != fnd_api.g_ret_sts_error THEN --Successful return of first insert

IF p_lot_no IS NOT NULL THEN

--Derive lot expiration date for the lot number

BEGIN

SELECT

expiration_date

INTO l_expiration_date

FROM

mtl_lot_numbers

WHERE

lot_number = p_lot_no

AND inventory_item_id = p_item_id

AND organization_id = p_organization_id;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Exception in fetching lot expiration_date: ' || sqlerrm);

l_expiration_date := NULL;

dbms_output.put_line('STEP2');

END;

dbms_output.put_line('STEP3');

dbms_output.put_line('Expiration_date: ' || l_expiration_date);

dbms_output.put_line('STEP3.1');

BEGIN

dbms_output.put_line('STEP4');

--Insert lot number interface if the item is lot controlled.

r_mtl_lot_rec := NULL;

g_l_transaction_id := l_transaction_id;

r_mtl_lot_rec.transaction_interface_id := l_transaction_id;

r_mtl_lot_rec.source_code := p_transaction_type;

r_mtl_lot_rec.source_line_id := l_transaction_id;

r_mtl_lot_rec.last_update_date := sysdate;

r_mtl_lot_rec.last_updated_by := fnd_global.user_id;

r_mtl_lot_rec.creation_date := sysdate;

r_mtl_lot_rec.created_by := fnd_global.user_id;

r_mtl_lot_rec.last_update_login := fnd_global.login_id;

r_mtl_lot_rec.lot_number := p_lot_no;

r_mtl_lot_rec.origination_date := p_origination_date;

r_mtl_lot_rec.lot_expiration_date := l_expiration_date;

r_mtl_lot_rec.transaction_quantity := p_quantity;

r_mtl_lot_rec.primary_quantity := p_quantity;

r_mtl_lot_rec.serial_transaction_temp_id := l_transaction_id;

--New CLEI Code--

r_mtl_lot_rec.lot_attribute_category := '102';

r_mtl_lot_rec.c_attribute1 := p_clei_code;

--New CLEI Code--

dbms_output.put_line('STEP5');

INSERT INTO mtl_transaction_lots_interface VALUES r_mtl_lot_rec;

dbms_output.put_line('STEP6');

EXCEPTION

WHEN OTHERS THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_count := x_msg_count + 1;

x_msg_data := 'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :' || sqlerrm;

dbms_output.put_line('STEP7');

END;

END IF;

dbms_output.put_line('STEP8');

COMMIT;

--call Inventory open transaction api to perform transfer of material from source locator to destination locator.

dbms_output.put_line('STEP9');

x_return_status := NULL;

x_msg_count := 0;

x_msg_data := NULL;

dbms_output.put_line('Before Calling process_transactions' || l_transaction_id);

lc_result := inv_txn_manager_pub.process_transactions(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_true,

p_validation_level => fnd_api.g_valid_level_full,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_trans_count => l_trans_count,

p_table => 1,

p_header_id => l_transaction_id);

dbms_output.put_line('STEP10');

dbms_output.put_line('Return Status' || x_return_status);

IF nvl(x_return_status, 'N') <> fnd_api.g_ret_sts_success THEN

x_return_status := fnd_api.g_ret_sts_error;

dbms_output.put_line('Return Status: ' || x_return_status);

dbms_output.put_line('Message: ' || x_msg_data);

dbms_output.put_line('STEP11');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('In Exception' || sqlerrm);

fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);

dbms_output.put_line('STEP12');

ROLLBACK;

END xxtlm_pkmtl_transaction;