Oracle Interface to Upload Item Stock in Inventory
DECLARE
l_num_inv_org_id NUMBER;
v_num_success_cntr NUMBER := 0;
v_num_failure_cntr NUMBER := 0;
v_num_ou_org NUMBER := fnd_profile.VALUE ('ORG_ID');
v_num_imo_org NUMBER;
v_num_loop_cntr NUMBER := 0;
v_chr_err_flag VARCHAR2 (2) := 'N';
v_chr_uom_code mtl_system_items.primary_uom_code%TYPE;
v_chr_err_code mtl_transactions_interface.ERROR_CODE%TYPE;
v_chr_err_expl mtl_transactions_interface.error_explanation%TYPE;
v_chr_sql_error VARCHAR2 (1000);
v_num_project_id NUMBER;
v_num_task_id NUMBER;
v_num_dist_acct NUMBER;
v_num_locator_cntrl NUMBER;
v_num_qty NUMBER;
v_num_item_id NUMBER;
v_num_loc_operation VARCHAR2 (50);
l_num_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');
l_num_user_id NUMBER := fnd_global.user_id;
l_num_conc_log_id NUMBER := fnd_global.conc_login_id;
l_num_conc_req_id NUMBER := fnd_global.conc_request_id;
--Rev#1: Added
v_trx_qty NUMBER;
v_serial_cntrl_yn VARCHAR2 (10);
v_lot_cntrl_yn VARCHAR2 (10);
--Rev#1: Modified to transaction_reference column
CURSOR c_onhand_qty_temp
IS
SELECT organization_id, item_segment1, subinventory_code,
source_code, source_line_id, source_header_id,
transaction_quantity, transaction_date,
transaction_type_id, transaction_uom,
account_combination,lot_number, error_code,
loc_segment1, loc_segment2, loc_segment3, loc_segment4,
loc_segment19, loc_segment20, process_flag, lock_flag
FROM xx_ONHAND_QTY_TEMP
WHERE process_flag <> 2
AND organization_id = i_org_id
GROUP BY organization_id,item_segment1, subinventory_code,
source_code, source_line_id, source_header_id,transaction_quantity, transaction_date,
transaction_type_id, transaction_uom,
account_combination,lot_number, error_code,
loc_segment1, loc_segment2, loc_segment3, loc_segment4,
loc_segment19, loc_segment20, process_flag, lock_flag
ORDER BY organization_id,item_segment1,lot_number;
CURSOR C_SERIAL_NUMBER(P_ORG_ID NUMBER,P_ITEM VARCHAR2,P_LOT VARCHAR2,P_SUBINV VARCHAR2) IS
SELECT ROWID, organization_id, item_segment1, subinventory_code,
source_code, source_line_id, source_header_id,
transaction_quantity, transaction_date,
transaction_type_id, transaction_uom, transaction_reference,
account_combination,lot_number, fm_serial_number, to_serial_number, error_code,
loc_segment1, loc_segment2, loc_segment3, loc_segment4,
loc_segment19, loc_segment20, process_flag, lock_flag
FROM xx_ONHAND_QTY_TEMP
WHERE process_flag <> 2
AND organization_id = P_ORG_ID
AND item_segment1=P_ITEM
AND lot_number=P_LOT
AND subinventory_code=P_SUBINV
ORDER BY fm_serial_number;
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'----------------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG, 'On Hand Quantity Staging Log');
fnd_file.put_line
(fnd_file.LOG,
'----------------------------------------------------------------------'
);
FOR rec_onhand IN c_onhand_qty_temp
LOOP
--Initializing Variables
v_num_loop_cntr := v_num_loop_cntr + 1;
v_chr_err_flag := 'N';
v_chr_uom_code := '';
v_num_project_id := NULL;
v_num_task_id := NULL;
v_num_dist_acct := NULL;
v_num_locator_cntrl := 0;
v_chr_err_code := '';
v_chr_err_expl := '';
v_num_qty := NULL;
v_num_imo_org := NULL;
v_trx_qty := 0;
v_serial_cntrl_yn := NULL;
fnd_file.put_line (fnd_file.LOG,
'==========================================='
);
fnd_file.put_line (fnd_file.LOG,
'Processing Item: '
|| rec_onhand.item_segment1
|| ' in Organization: '
|| TO_CHAR (v_num_ou_org)
);
fnd_file.put_line (fnd_file.LOG,
'Transaction Qty: '
|| TO_CHAR (rec_onhand.transaction_quantity)
);
fnd_file.put_line (fnd_file.LOG,
'SubInventory : '
|| rec_onhand.subinventory_code
);
-- Get IMO Organizatio ID and Locator Type
BEGIN
SELECT organization_id, locator_type
INTO v_num_imo_org, v_num_locator_cntrl
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = rec_onhand.subinventory_code
AND organization_id = NVL (rec_onhand.organization_id, '0')
AND disable_date IS NULL;
fnd_file.put_line (fnd_file.LOG,
'SubInventory Locator Control '
|| 'Code = '
|| TO_CHAR (v_num_locator_cntrl)
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'Organisation and SubInventory Locator Control Does not exist in the Organization'
);
v_chr_err_code := 'GET_ORG_ID_ERROR';
v_chr_err_expl := v_chr_err_expl || '/Org Id, sub inv loc error';
v_chr_err_flag := 'Y';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'General Error while validating Org Id and SubInventory'
);
fnd_file.put_line (fnd_file.LOG,
'Error code : ' || SQLCODE
);
fnd_file.put_line (fnd_file.LOG,
'Error message : ' || SQLERRM
);
v_chr_err_flag := 'Y';
END;
-- Get CC id using Account Combination
BEGIN
SELECT gcc.code_combination_id
INTO v_num_dist_acct
FROM gl_code_combinations_kfv gcc
WHERE gcc.concatenated_segments =
rec_onhand.account_combination;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_chr_err_code := 'GET_DIST_ACCT_ERROR';
v_chr_err_expl :=
v_chr_err_expl || '/Distribution Account Id error';
v_chr_err_flag := 'Y';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'General Error while validating account_combination'
);
fnd_file.put_line (fnd_file.LOG,
'Error code : ' || SQLCODE
);
fnd_file.put_line (fnd_file.LOG,
'Error message : ' || SQLERRM
);
v_chr_err_flag := 'Y';
END;
--Get Inventory Item id and Primary UOM code.
BEGIN
SELECT inventory_item_id, primary_uom_code
INTO v_num_item_id, v_chr_uom_code
FROM mtl_system_items
WHERE segment1 = rec_onhand.item_segment1
AND organization_id = rec_onhand.organization_id;
fnd_file.put_line (fnd_file.LOG,
'Inventory Item ID: ' || v_num_item_id
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
'Inventory Item id or UOM is not found'
);
v_chr_err_code := 'GET_INV_UOM_ID_ERROR';
v_chr_err_expl := v_chr_err_expl || '/Inv ID, UOM error';
v_chr_err_flag := 'Y';
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'General Error while validating item code'
);
fnd_file.put_line (fnd_file.LOG,
'Error code : ' || SQLCODE
);
fnd_file.put_line (fnd_file.LOG,
'Error message : ' || SQLERRM
);
v_chr_err_flag := 'Y';
END;
--Rev#1: Added to check if trx qty is negative for Misc issues
--Rev#1: 32 is the transaction_type_id for Misc.issue transaction
--Rev#1: 42 is the transaction_type_id for Misc.Receipt transaction
--Rev#2: Modified the code for handling Misc. Receipts
IF (rec_onhand.transaction_type_id = 32) THEN
IF (rec_onhand.transaction_quantity <= 0) THEN
v_trx_qty := rec_onhand.transaction_quantity;
ELSE
v_trx_qty := (rec_onhand.transaction_quantity * -1);
END IF;
ELSE
v_trx_qty := rec_onhand.transaction_quantity;
END IF;
--Rev#1: Added to check if the item is serialized or not
BEGIN
SELECT decode(serial_number_control_code,5,'Y','N'),decode(LOT_CONTROL_CODE,2,'Y','N')
INTO v_serial_cntrl_yn,v_lot_cntrl_yn
FROM mtl_system_items
WHERE inventory_item_id = v_num_item_id
AND organization_id = rec_onhand.organization_id;
fnd_file.put_line(fnd_file.LOG, 'Item is lot enabled (Y/N) = '||v_lot_cntrl_yn);
fnd_file.put_line(fnd_file.LOG, 'Item is Serialized (Y/N) = '||v_serial_cntrl_yn);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error in checking serialized attribute');
fnd_file.put_line (fnd_file.LOG, 'Error message : ' || SUBSTR(SQLERRM, 1, 100));
v_chr_err_flag := 'Y';
END;
IF v_lot_cntrl_yn = 'Y' THEN
IF rec_onhand.lot_number is null then
v_chr_err_flag := 'Y';
v_chr_err_code := 'MISSING_LOT_NO';
v_chr_err_expl := v_chr_err_expl ||'Item is LOT Controlled But missing Lot Number';
end if;
else
IF rec_onhand.lot_number is not null then
v_chr_err_flag := 'Y';
v_chr_err_code := 'UNWANTED_LOT_NO';
v_chr_err_expl := v_chr_err_expl ||'Item is not LOT Controlled Lot Number should be null';
end if;
end if;
--Insert records with error flag 'N'
--Rev#1: Modified the query to include the transaction_reference column
--Rev#1: Modified the query to insert source_code into transaction_source_name
IF v_chr_err_flag = 'N'
THEN
BEGIN
INSERT INTO mtl_transactions_interface
(transaction_interface_id,
organization_id,
inventory_item_id,
item_segment1,
subinventory_code,
source_code,
transaction_source_name,
source_line_id,
source_header_id,
transaction_mode,
transaction_quantity,
transaction_date,
transaction_type_id,
transaction_uom,
transaction_reference,
distribution_account_id,
loc_segment1,
loc_segment2,
loc_segment3,
loc_segment4,
loc_segment19,
loc_segment20,
created_by,
creation_date,
last_updated_by,
last_update_date,
process_flag,
lock_flag
)
VALUES (mtl_material_transactions_s.NEXTVAL,
rec_onhand.organization_id,
v_num_item_id,
rec_onhand.item_segment1,
rec_onhand.subinventory_code,
rec_onhand.source_code,
--Rev#1: Added to pass the source code to transaction_source_name
rec_onhand.source_code,
rec_onhand.source_line_id,
rec_onhand.source_header_id,
3,
--Rev#1: Modified to change the sign of trx qty
v_trx_qty,
rec_onhand.transaction_date,
42,--rec_onhand.transaction_type_id,
rec_onhand.transaction_uom,
--Rev#1: Added to pass the transaction reference
NULL,
v_num_dist_acct,
rec_onhand.loc_segment1,
rec_onhand.loc_segment2,
rec_onhand.loc_segment3,
rec_onhand.loc_segment4,
v_num_project_id,
v_num_task_id,
l_num_user_id,
SYSDATE,
l_num_user_id,
SYSDATE,
1,
2
);
EXCEPTION
WHEN OTHERS
THEN
v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
fnd_file.put_line
(fnd_file.LOG,
'Error in inserting into MTL TRX INTERFACE Table: '
|| v_chr_sql_error
);
v_chr_err_flag := 'Y';
v_chr_err_code:=v_chr_err_code||'Error in inserting into MTL TRX INTERFACE Table: ';
END;
IF (v_lot_cntrl_yn = 'Y' AND rec_onhand.lot_number IS NOT NULL)
THEN
BEGIN
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id,
serial_transaction_temp_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login, lot_number,
transaction_quantity, primary_quantity
)
VALUES (mtl_material_transactions_s.CURRVAL,
mtl_system_items_intf_sets_s.NEXTVAL, SYSDATE, l_num_user_id,
SYSDATE, l_num_user_id, l_num_user_id, rec_onhand.lot_number,
v_trx_qty, v_trx_qty
);
end ;
end if;
FOR V_SERIAL_NUMBER IN C_SERIAL_NUMBER(rec_onhand.organization_id,rec_onhand.item_segment1,rec_onhand.lot_number,rec_onhand.subinventory_code) LOOP
IF v_serial_cntrl_yn = 'Y' THEN
IF V_SERIAL_NUMBER.fm_serial_number IS NULL OR V_SERIAL_NUMBER.to_serial_number IS NULL THEN
--fnd_file.put_line (fnd_file.LOG, 'Item is Serialized but missing FROM or TO serial Nos');
v_chr_err_flag := 'Y';
v_chr_err_code := 'MISSING_FROM_TO_SERIAL_NOS';
v_chr_err_expl := v_chr_err_expl ||'Item is Serialized but missing FROM or TO serial Nos';
END IF;
ELSE
IF V_SERIAL_NUMBER.fm_serial_number IS NOT NULL or V_SERIAL_NUMBER.to_serial_number IS NOT NULL THEN
--fnd_file.put_line (fnd_file.LOG, 'Item is not Serialized, FROM or TO serial nos should be blank');
v_chr_err_flag := 'Y';
v_chr_err_code := 'UNWANTED_FROM_TO_SERIAL_NOS';
v_chr_err_expl := v_chr_err_expl ||'Item is not Serialized FROM or TO serial nos should be blank';
END IF;
END IF;
IF v_chr_err_flag = 'N' THEN
IF (v_serial_cntrl_yn = 'Y' AND V_SERIAL_NUMBER.fm_serial_number IS NOT NULL AND V_SERIAL_NUMBER.to_serial_number IS NOT NULL)
THEN
BEGIN
INSERT INTO mtl_serial_numbers_interface
(transaction_interface_id,
source_code,
source_line_id,
fm_serial_number,
to_serial_number,
creation_date,
last_update_date,
created_by,
last_updated_by,
process_flag
)
VALUES (DECODE (v_lot_cntrl_yn,'Y',mtl_system_items_intf_sets_s.CURRVAL,mtl_material_transactions_s.CURRVAL),
rec_onhand.source_code,
rec_onhand.source_line_id,
V_SERIAL_NUMBER.fm_serial_number,
V_SERIAL_NUMBER.fm_serial_number,
SYSDATE,
SYSDATE,
l_num_user_id,
l_num_user_id,
1
);
EXCEPTION
WHEN OTHERS
THEN
v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
fnd_file.put_line
(fnd_file.LOG,
'Error in inserting into MTL SERIAL NUMBERS INTERFACE Table: '
|| v_chr_sql_error
);
END;
END IF;
END IF;
END LOOP;
END IF;
IF v_chr_err_flag = 'Y'
THEN
fnd_file.put_line (fnd_file.LOG, 'Error in Processing Transaction');
fnd_file.put_line (fnd_file.LOG, v_chr_err_expl);
v_num_failure_cntr := v_num_failure_cntr + 1;
BEGIN
UPDATE xx_ONHAND_QTY_TEMP
SET process_flag = 3,
ERROR_CODE = v_chr_err_code,
error_explanation = v_chr_err_expl,
last_updated_by = l_num_user_id,
last_update_date = SYSDATE,
last_update_login = l_num_conc_log_id,
conc_request_id = l_num_conc_req_id
WHERE organization_id =rec_onhand.organization_id
AND item_segment1=rec_onhand.item_segment1
AND lot_number=rec_onhand.lot_number
AND subinventory_code=rec_onhand.subinventory_code;
EXCEPTION
WHEN OTHERS
THEN
v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
fnd_file.put_line
(fnd_file.LOG,
'Error in inserting into On-Hand Stg Table: '
|| v_chr_sql_error
);
END;
ELSE
fnd_file.put_line (fnd_file.LOG, 'Transaction successfully Processed');
v_num_success_cntr := v_num_success_cntr + 1;
BEGIN
UPDATE xx_ONHAND_QTY_TEMP
SET process_flag = 2,
last_updated_by = l_num_user_id,
last_update_date = SYSDATE,
last_update_login = l_num_conc_log_id,
conc_request_id = l_num_conc_req_id
WHERE organization_id =rec_onhand.organization_id
AND item_segment1=rec_onhand.item_segment1
AND lot_number=rec_onhand.lot_number
AND subinventory_code=rec_onhand.subinventory_code;
EXCEPTION
WHEN OTHERS
THEN
v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
fnd_file.put_line
(fnd_file.LOG,
'Error in inserting into On-Hand Stg Table: '
|| v_chr_sql_error
);
END;
END IF;
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'--------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG,
'--------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG,
'Total Number of Records Processed: '
|| TO_CHAR (v_num_loop_cntr)
);
fnd_file.put_line (fnd_file.LOG,
'Total Number of Records Successfully Processed: '
|| TO_CHAR (v_num_success_cntr)
);
fnd_file.put_line (fnd_file.LOG,
'Total Number of Records Failed Process: '
|| TO_CHAR (v_num_failure_cntr)
);
IF i_delete_pro_flag = 'Y'
THEN
BEGIN
DELETE FROM xx_ONHAND_QTY_TEMP
WHERE process_flag = 2;
EXCEPTION
WHEN OTHERS
THEN
v_chr_sql_error := SUBSTR (SQLERRM, 1, 1000);
fnd_file.put_line
(fnd_file.LOG,
'Error in deleting processed records from custom interface table: '
|| v_chr_sql_error
);
END;
END IF;
END;
