CREATE OR REPLACE PACKAGE BODY MOT_J2J_INV_PARTS_USG_CONV_PKG
IS
--/****************************************************************************************************/
--/* */
--/* Header : MOT_J2J_INV_PARTS_USAGE_CONV_PKG.sql */
--/* */
--/* Program Type: PL/SQL Package */
--/* */
--/* Creation : 28-Jul-2011 */
--/* */
--/* Author : TCS */
--/* */
--/* Description : This package is used to perform the Parts Usage Transaction for Repair Items */
--/* */
--/* DEPENDENCIES : The Following Table and Procedures are the Dependencies for this package */
--/* Table Name: */
--/* MOTC.MOTINV_ITEM_USAGE_TBL, MOTC.MOTINV_ITEM_USAGE_BAD_TBL */
--/* API's/Open Interfaces: */
--/* MTL_TRANSACTIONS_INTERFACE */
--/* Process Transaction Program */
--/* Value Sets: MOTINV_REPAIR_ORG , MOTINV_J2J_ACCOUNT_SEGMENTS */
--/* None */
--/* */
--/* Caller : Concurrent Program */
--/* */
--/* Naming Conv : P_ - Parameter variables */
--/* V_ - Global variables */
--/* L_ - local variables */
--/* */
--/* CHANGE HISTORY */
--/* ======================================================================================== */
--/* Date Version Updated By Description */
--/* ======================================================================================== */
--/* 28-Jul-2011 1.0 KNW438 Initial Creation */
--/* */
--/****************************************************************************************************/
-----------------------------------------------------------------------
/********************* Main Procedure *******************************/
-----------------------------------------------------------------------
PROCEDURE main(v_errbuf OUT VARCHAR2,
v_retcode OUT NUMBER,
v_organization_id IN NUMBER,
v_distribution_account IN Number
) IS
v_total_count NUMBER:=0;
v_account_id NUMBER;
V_ACCOUNT_ALIAS VARCHAR2(20) := 'MJSC PART ISSUE ACCT';
V_TRX_TYPE_NAME VARCHAR2(30) := 'Account alias issue';
v_process_flag varchar2(2);
BEGIN
p_errbuf := NULL;
p_retcode := 0;
v_error_flag := 'N';
p_organization_id := v_organization_id;
status_update('','','','','','','','',''); -- Initialization
v_counter := 0;
v_account_id := v_distribution_account;
-- Fetching Distribution Account ID and Disposition ID
--fnd_file.put_line(fnd_file.log,'IN MAIN'||v_process_flag);
BEGIN
SELECT gcc.code_combination_id,
mgd.disposition_id
INTO v_account_id,
v_transaction_source_id
FROM gl.gl_code_combinations gcc,
inv.mtl_generic_dispositions mgd
WHERE
gcc.code_combination_id = v_distribution_account
AND gcc.code_combination_id = mgd.distribution_account
AND mgd.segment1 = V_ACCOUNT_ALIAS
AND mgd.organization_id = v_organization_id;
v_distribution_account_id := v_account_id;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
BEGIN
SELECT COUNT(1)
INTO v_errored_record_count
FROM MOTC.MOTINV_ITEM_USAGE_TBL
WHERE (process_flag IS NULL OR process_flag = 'N');
EXCEPTION
WHEN OTHERS THEN
v_errored_record_count := v_errored_record_count +1;
END;
v_error_message := 'Invalid Distribution Account, which doesnot correspond to the Account Alias Interface';
status_update('','','','','','',v_error_message,'N','');
fnd_file.put_line(fnd_file.log,v_error_message);
END;
-- Fetching Transaction Type ID
BEGIN
SELECT transaction_type_id
INTO v_transaction_type_id
FROM inv.mtl_transaction_types
WHERE transaction_type_name = V_TRX_TYPE_NAME ;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
BEGIN
SELECT COUNT(1)
INTO v_errored_record_count
FROM MOTC.MOTINV_ITEM_USAGE_TBL
WHERE (process_flag IS NULL OR process_flag = 'N');
EXCEPTION
WHEN OTHERS THEN
v_errored_record_count := v_errored_record_count +1;
END;
v_error_message := 'Unable to fetch the Transaction Type ID corresponding to the Transaction Account alias issue';
status_update('','','','','','',v_error_message,'N','');
fnd_file.put_line(fnd_file.log,v_error_message);
END;
IF(v_error_flag = 'N') THEN
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ************************* ACCOUNT ALIAS ISSUE TRANSACTION *************************');
validate_and_insert_to_intf(v_organization_id,v_account_id);
END IF;
IF (v_errored_record_count !=0) THEN
-- Generate Error Record Output if there is any error records.
error_records_out_generate;
END IF;
fnd_file.put_line(fnd_file.log,' ');
v_total_count := v_processed_record_count+ v_errored_record_count;
fnd_file.put_line(fnd_file.log,'Number of Records Successfully Processed : ' || v_processed_record_count );
fnd_file.put_line(fnd_file.log,'Number of Failed Records: ' || v_errored_record_count);
fnd_file.put_line(fnd_file.log,'Total Number of processed Records : '|| v_total_count);
fnd_file.put_line(fnd_file.log,'End of the Process ...' || SYSDATE);
-- To Return the Error Status, if any unexpected programmatical Error occurs
-- v_errbuf := p_errbuf;
-- v_retcode := p_retcode;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Failure in Main Procedure:'||SQLCODE ||' '|| SQLERRM);
-- v_errbuf := SQLERRM;
-- v_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: VALIDATE_AND_INSERT_TO_INTF- To Check for the status of the Records Imported
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE validate_and_insert_to_intf(v_organization_id NUMBER,
v_account_id NUMBER)IS
v_item_number VARCHAR2(20);
v_inventory_item_id NUMBER;
v_subinventory VARCHAR2(10);
v_locator VARCHAR2(12);
v_locator1 VARCHAR2(12);
v_locator2 VARCHAR2(12);
v_locator3 VARCHAR2(12);
v_locator4 VARCHAR2(12);
v_quantity NUMBER;
v_period_name VARCHAR2(10);
v_serial_flag MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE%TYPE;
v_primary_uom_code VARCHAR2(100);
-- v_transaction_type_id NUMBER;
v_transaction_id NUMBER;
--v_transaction_source_id NUMBER := 3770; --Commented as part of J2J-II
-- v_distribution_account_id NUMBER;
v_return_status VARCHAR2(1):='N';
v_location_id NUMBER;
v_onhand_qty NUMBER;
V_RESERVED_QTY NUMBER;
V_AVAILABLE_QTY NUMBER;
v_transaction_date DATE;
v_ir_number VARCHAR2(12);
v_rack VARCHAR2(4);
v_level VARCHAR2(3);
v_bin VARCHAR2(3);
v_qoh NUMBER;
v_att NUMBER;
v_rowid ROWID;
v_process_flag VARCHAR2(2);
CURSOR cur_misc_RECEIPT IS
SELECT item_number,
subinventory,
locator_segment1,
locator_segment2,
locator_segment3,
locator_segment4,
quantity,
transaction_date,
trim(ir_number) ir_number,
process_flag,
rowid --Added for Prod Issue
FROM MOTC.MOTINV_ITEM_USAGE_TBL
WHERE (process_flag IS NULL OR process_flag = 'N');
BEGIN
v_error_flag := 'N';
v_error_message := NULL;
fnd_file.put_line(fnd_file.log, '1. Validation Loop begins to Insert records into Item Interface Table ');
v_distribution_account_id := v_account_id;
-- Fetch Records from the Staging Table
FOR rec_misc_RECEIPT IN cur_misc_RECEIPT
LOOP
v_error_flag := 'N';
v_item_number := rec_misc_RECEIPT.item_number;
v_subinventory := rec_misc_RECEIPT.subinventory;
v_locator1 := rec_misc_RECEIPT.locator_segment1;
v_locator2 := rec_misc_RECEIPT.locator_segment2;
v_locator3 := rec_misc_RECEIPT.locator_segment3;
v_locator4 := rec_misc_RECEIPT.locator_segment4;
v_quantity := rec_misc_RECEIPT.quantity;
v_transaction_date := rec_misc_RECEIPT.transaction_date;
v_ir_number := rec_misc_RECEIPT.ir_number;
v_rowid := rec_misc_RECEIPT.rowid;
v_process_flag := rec_misc_RECEIPT.process_flag;
--Can be commented --
fnd_file.put_line(fnd_file.log, ' ');
fnd_file.put_line(fnd_file.log, ' ');
fnd_file.put_line(fnd_file.log, ' Item Number : '||v_item_number);
fnd_file.put_line(fnd_file.log, ' Subinventory : '||v_subinventory);
fnd_file.put_line(fnd_file.log, ' Locator : '||v_locator1||''||v_locator2||''||v_locator3||''||v_locator4);
fnd_file.put_line(fnd_file.log, ' Process flag : '||v_process_flag);
fnd_file.put_line(fnd_file.log, ' IR NUmber : '||v_ir_number);
--
IF (v_error_flag = 'N') THEN
-- 1st Validation for Item Part Number
BEGIN
SELECT inventory_item_id,
primary_uom_code,
SERIAL_NUMBER_CONTROL_CODE
INTO v_inventory_item_id,
v_primary_uom_code,
v_serial_flag
FROM inv.mtl_system_items_b
WHERE segment1 = v_item_number
AND organization_id = v_organization_id;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Invalid Item Name ' || v_item_number;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- Get Inventory Period Status
IF (v_error_flag = 'N')
THEN
BEGIN
SELECT period_name
INTO v_period_name
FROM org_acct_periods_v
WHERE rec_type = 'ORG_PERIOD'
AND organization_id = v_organization_id
AND (TO_DATE (TRUNC(v_transaction_date))) BETWEEN TRUNC(start_date) AND TRUNC(end_date)
AND status = 'Open';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Inventory Period Is not Open for transaction date ' || v_item_number;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Error Retrieving Inventory Period ' || v_item_number;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
--End Inventory Period Status
END IF;
IF (v_error_flag = 'N') THEN
-- 2nd Validation for Subinventory
BEGIN
SELECT msi.secondary_inventory_name
INTO v_subinventory
FROM inv.mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = v_subinventory
AND organization_id = v_organization_id
AND (disable_date IS NULL OR trunc(disable_date)>trunc(SYSDATE));
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Invalid Subinventory Name ' || v_subinventory;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- 4th Validation, Quantity should not be NULL
IF (v_error_flag = 'N') THEN
IF (v_quantity IS NOT NULL) THEN
v_quantity := v_quantity *(-1);
ELSE
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Quantity Field doesnot have any value';
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message);
END IF;
END IF;
-- 6th fetch Locator ID
IF (v_error_flag = 'N') THEN
BEGIN
Select mil.inventory_location_id
into v_location_id
from mtl_item_locations mil
where organization_id = v_organization_id
and mil.segment1= v_locator1
and mil.segment2= v_locator2
and mil.segment3= v_locator3
and mil.segment4= v_locator4
and enabled_flag='Y';
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Invalid Locator Name ' || v_locator1||'.'||v_locator2||'.'||v_locator3||'.'||v_locator4;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- 7th Onhand quantity validation
-- Transaction quantity
IF (v_error_flag = 'N') THEN
BEGIN
SELECT NVL(SUM(MOQD.TRANSACTION_QUANTITY),0)
INTO v_onhand_qty
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQD
WHERE MOQD.INVENTORY_ITEM_ID=v_inventory_item_id
AND MOQD.ORGANIZATION_ID=v_organization_id
AND MOQD.SUBINVENTORY_CODE =v_subinventory
AND MOQD.LOCATOR_ID = v_location_id;
IF( v_onhand_qty = 0) THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Validation failed for Transaction Quantity ' || v_onhand_qty;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Validation failed for Transaction Quantity ' || v_onhand_qty;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- Reserved Quantity
IF (v_error_flag = 'N') THEN
BEGIN
SELECT NVL(SUM(MR.RESERVATION_QUANTITY),0)
INTO V_RESERVED_QTY
FROM MTL_RESERVATIONS MR
WHERE MR.INVENTORY_ITEM_ID=v_inventory_item_id
AND MR.ORGANIZATION_ID=v_organization_id
AND MR.SUBINVENTORY_CODE = v_subinventory
AND MR.LOCATOR_ID = v_location_id;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Validation failed at Reserved Quantity ' || V_RESERVED_QTY;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- Calculating Available Quantity
IF (v_error_flag = 'N') THEN
BEGIN
V_AVAILABLE_QTY:=(NVL(v_onhand_qty ,0)-NVL(V_RESERVED_QTY,0));
END;
END IF;
IF (v_error_flag = 'N') THEN
BEGIN
IF V_AVAILABLE_QTY<v_quantity THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Insufficient quantity for the item ' || v_item_number;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END IF;
END;
END IF;
-- Fetch Transact ID
IF (v_error_flag = 'N') THEN
BEGIN
SELECT mtl_material_transactions_s.nextval
INTO v_transaction_id
FROM dual;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Unable to generate the sequence number from MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL';
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
IF (v_error_flag = 'N') THEN
BEGIN
fnd_file.put_line(fnd_file.log, 'Before inserting'||v_transaction_id);
-- Account Alias Issue Details Population
INSERT INTO inv.mtl_transactions_interface(transaction_type_id,
transaction_uom,
transaction_date,
organization_id,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_mode,
process_flag,
transaction_source_id,
source_header_id,
source_line_id,
source_code,
lock_flag,
flow_schedule,
scheduled_flag,
transaction_header_id,
inventory_item_id,
transaction_interface_id,
subinventory_code,
distribution_account_id,
transaction_cost,
locator_id,
transaction_reference
)
VALUES (v_transaction_type_id, -- Transaction Type Id
v_primary_uom_code, -- Item Basic UOM
v_transaction_date, -- TransactionDate
v_organization_id, -- Organization id
v_quantity, -- Quantity
SYSDATE, -- Last Update Date
v_user_id, -- Last Update By
SYSDATE, -- Created Date
v_user_id, -- Created By
3, -- Background Processing
1, -- ''1 ready to be processed
v_transaction_source_id, -- Transaction Source ID -- 3770
v_transaction_id, -- Source Header ID
v_transaction_id, -- Source Line ID
'Account alias', --'Inventory' -- Source Code
2, -- Lock Flag
'Y', -- Flow Schedule
2, -- Scheduled Flag
v_transaction_id, -- Transaction Header ID
v_inventory_item_id, -- Inventory Item ID
v_transaction_id, -- Transaction Interface ID
v_subinventory, -- Item Number
v_distribution_account_id, -- Account ID
NULL, -- Transaction Cost
v_location_id, -- Locator ID
v_ir_number --Added due to J2J stock loading
);
--fnd_file.put_line(fnd_file.log, 'After inserting'||v_transaction_id);
/*
if v_serial_flag = 5
then
For rec_misc_RECEIPT_SER in cur_misc_RECEIPT_SER (rec_misc_RECEIPT.item_number, rec_misc_RECEIPT.subinventory)
loop
-- insert data into MTL_SERIAL_ITEMS_INTERFACE
-- for the combination of the item, subinventory, -- org
-- combination
For I in 1 .. rec_misc_RECEIPT.Quantity
loop
Insert into mtl_serial_numbers_interface
(transaction_interface_id,
fm_serial_number,
to_serial_number,
last_update_date,
last_updated_by,
creation_date,
created_by)
values
(v_transaction_id, --transaction interface_id
rec_misc_RECEIPT_SER.serial_number , --from serial number
rec_misc_RECEIPT_SER.serial_number , --to serial number
sysdate, --last update date
0, --last updated by
sysdate, --creation date
0 --created by
);
commit; -- can be removed later on
UPDATE MOTC.MOTINV_ITEM_ONHAND_SERIAL_TBL SET transaction_id = v_transaction_id,
last_update_date = sysdate,
last_updated_by = v_user_id
WHERE item_number = v_item_number
AND subinventory = v_subinventory
and serial_number = rec_misc_RECEIPT_SER.serial_number;
End Loop;
End Loop;
End if;
*/
COMMIT;
v_counter := v_counter + 1;
-- Update the Transaction ID
UPDATE MOTC.MOTINV_ITEM_USAGE_TBL SET transaction_id = v_transaction_id,
last_update_date = sysdate,
last_updated_by = v_user_id,
process_flag='P'--Added for Prod Issue
WHERE
item_number = v_item_number
AND subinventory = v_subinventory
AND LOCATOR_SEGMENT1 = v_locator1
AND LOCATOR_SEGMENT2 = v_locator2
AND LOCATOR_SEGMENT3 = v_locator3
AND LOCATOR_SEGMENT4 = v_locator4
AND transaction_id is null
AND rowid=v_rowid;
COMMIT;
fnd_file.put_line(fnd_file.log, 'After updating transactionid'||v_counter||','||v_transaction_id);
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Unable to insert into the Open Interface Table MTL_MATERIAL_INTERFACE'||SQLCODE ||' '|| SQLERRM;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
END;
END IF;
END LOOP;
IF ( v_counter != 0) THEN
call_interface_program;
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in Procedure VALIDATE_AND_INSERT_TO_INTF :'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: CALL_INTERFACE_PROGRAM - To Launch the Process transaction interface
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE call_interface_program IS
v_i NUMBER;
v_interface_request_id NUMBER:=0;
v_phase VARCHAR2(100);
v_status VARCHAR2(100);
v_dev_phase VARCHAR2(100);
v_dev_status VARCHAR2(100);
v_message VARCHAR2(2000);
v_wait_outcome BOOLEAN;
v_child_intf_request_id NUMBER:=0;
BEGIN
fnd_file.put_line(fnd_file.LOG,' Process transaction interface Program Starts....');
v_interface_request_id:=apps.fnd_request.submit_request(application => 'INV',
program => 'INCTCM',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE);
COMMIT;
fnd_file.put_line(fnd_file.log,'Request ID for Process Transaction Interface Program :'||v_interface_request_id);
IF (v_interface_request_id = 0) THEN
fnd_file.put_line(fnd_file.log, 'Error message' || SQLERRM);
ELSIF (v_interface_request_id IS NULL) THEN
fnd_file.put_line(fnd_file.log,'No Process Transaction Program has been launched');
ELSE
LOOP
--Concurrent Phase Loop Starts
v_wait_outcome := fnd_concurrent.get_request_status(request_id => v_interface_request_id,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
message => v_message);
EXIT WHEN UPPER(v_phase) = 'COMPLETED';
END LOOP; --Concurrent Phase Loop End
-- Get the Inventory Transaction Worker Request and wait for it
BEGIN
SELECT MAX(request_id)
INTO v_child_intf_request_id
FROM fnd_concurrent_requests
WHERE parent_request_id = v_interface_request_id;
EXCEPTION
WHEN OTHERS THEN
v_child_intf_request_id := 0;
END;
IF (v_child_intf_request_id !=0 AND v_child_intf_request_id IS NOT NULL) THEN
LOOP
--Concurrent Phase Loop Starts
v_wait_outcome := fnd_concurrent.get_request_status(request_id => v_child_intf_request_id,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
message => v_message);
EXIT WHEN UPPER(v_phase) = 'COMPLETED';
END LOOP; --Concurrent Phase Loop End
END IF;
END IF;
check_interface_rec_status;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in Procedure CALL_INTERFACE_PROGRAM :'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: CHECK_INTERFACE_REC_STATUS- To Check for the status of the Records Imported
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE check_interface_rec_status IS
CURSOR cur_check_status
IS SELECT miot.item_number,
miot.subinventory,
miot.locator_segment1,
miot.locator_segment2,
miot.locator_segment3,
miot.locator_segment4,
miot.transaction_id,
miot.rowid,
miot.process_flag
FROM MOTC.MOTINV_ITEM_USAGE_TBL miot
WHERE miot.transaction_id IS NOT NULL
AND (miot.process_flag='N' OR miot.process_flag IS NULL OR miot.process_flag='P' );
v_item_number MOTC.MOTINV_ITEM_USAGE_TBL.item_number%TYPE;
v_subinventory MOTC.MOTINV_ITEM_USAGE_TBL.subinventory%TYPE;
v_locator1 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment1%TYPE;
v_locator2 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment2%TYPE;
v_locator3 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment3%TYPE;
v_locator4 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment4%TYPE;
v_transaction_id NUMBER;
v_process_flag MOTC.MOTINV_ITEM_USAGE_TBL.process_flag%TYPE;
v_error_description VARCHAR2(2000);
v_error_message VARCHAR2(2000);
vl_rowid ROWID;--Added for Prod Issue
BEGIN
fnd_file.put_line(fnd_file.LOG,' Interface Error Check Starts ...');
--fnd_file.put_line(fnd_file.log,'AFTER Interface error check starts'|| v_process_flag);
-- To Update the Status of the Failed Records
FOR rec_check_status IN cur_check_status
LOOP
--fnd_file.put_line(fnd_file.log, 'Start of FOR LOOP');
v_item_number := rec_check_status.item_number;
v_subinventory := rec_check_status.subinventory;
v_locator1 := rec_check_status.locator_segment1;
v_locator2 := rec_check_status.locator_segment2;
v_locator3 := rec_check_status.locator_segment3;
v_locator4 := rec_check_status.locator_segment4;
v_transaction_id := rec_check_status.transaction_id;
v_error_description := '';
v_process_flag := rec_check_status.process_flag;
vl_rowid :=rec_check_status.rowid; --Added for Prod Issue
--fnd_file.put_line(fnd_file.log, 'TRANSACTIONID is '||v_transaction_id);
-- fnd_file.put_line(fnd_file.log, 'Middle of FOR LOOP');
-- fnd_file.put_line(fnd_file.log, 'Middle of for loop'||v_process_flag);
BEGIN
fnd_file.put_line(fnd_file.log, 'BEFORE SELECT STMT');
SELECT mti.process_flag,
mti.error_code||' '||mti.error_explanation
INTO v_process_flag,
v_error_description
FROM inv.mtl_transactions_interface mti
WHERE mti.transaction_interface_id = v_transaction_id;
--fnd_file.put_line(fnd_file.log, 'PROCESS_FLAG ===='||v_process_flag);
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_process_flag := 0;
v_error_description := NULL;
WHEN OTHERS THEN
v_process_flag := 0;
v_error_description := NULL;
END;
IF (v_process_flag = 3) THEN -- Successfully Interfaced Records
v_errored_record_count := v_errored_record_count + 1;
v_error_message := v_error_description;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'I',vl_rowid);
ELSIF (v_process_flag = 2 OR v_process_flag=1) THEN -- Successfully Interfaced Records
v_errored_record_count := v_errored_record_count + 1;
v_error_message := 'Record has not been processed from MTL_TRANSACTON_INTERFACE table';
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'I',vl_rowid);
ELSE
v_processed_record_count := v_processed_record_count + 1;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,'','Y',vl_rowid);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in the Procedure CHECK_INTERFACE_REC_STATUS:'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: STATUS_UPDATE - Update the Status to the Staging table
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE status_update(v_item_number VARCHAR2,
v_subinventory VARCHAR2,
v_locator1 VARCHAR2,
v_locator2 VARCHAR2,
v_locator3 VARCHAR2,
v_locator4 VARCHAR2,
v_error_message VARCHAR2,
v_process_flag VARCHAR2,
v_rowid ROWID) IS
BEGIN
--fnd_file.put_line(fnd_file.log,'IN IF LOOP OF STATUS UPDATE'||v_process_flag);
IF ( v_item_number IS NULL AND v_subinventory IS NULL) THEN
UPDATE MOTC.MOTINV_ITEM_USAGE_TBL SET process_flag = v_process_flag,
error_description = v_error_message,
transaction_id ='',
last_update_date = sysdate,
last_updated_by = v_user_id
WHERE process_flag IS NULL OR process_flag='N';
COMMIT;
fnd_file.put_line(fnd_file.log,'IN IF LOOP OF STATUS UPDATE'||v_process_flag);
ELSE
--fnd_file.put_line(fnd_file.log,'IN ELSE LOOP OF STATUS UPDATE'||v_process_flag);
UPDATE MOTC.MOTINV_ITEM_USAGE_TBL SET process_flag = v_process_flag,
error_description = v_error_message,
last_update_date = sysdate,
last_updated_by = v_user_id
WHERE item_number = v_item_number
AND subinventory = v_subinventory
AND LOCATOR_SEGMENT1 = v_locator1
AND LOCATOR_SEGMENT2 = v_locator2
AND LOCATOR_SEGMENT3 = v_locator3
AND LOCATOR_SEGMENT4 = v_locator4
AND ROWID=v_rowid;--Added for Prod Issue
COMMIT;
--fnd_file.put_line(fnd_file.log,'IN ELSE LOOP OF STATUS UPDATE'||v_process_flag);
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in the Procedure STATUS_UPDATE:'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: ERROR_RECORDS_OUT_GENERATE - Generate the output file in the FND Output
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE error_records_out_generate IS
CURSOR cur_err_records
IS SELECT miot.item_number,
miot.subinventory,
miot.locator_segment1,
miot.locator_segment2,
miot.locator_segment3,
miot.locator_segment4,
miot.quantity,
replace(replace(miot.error_description,chr(10),' '),chr(13),' ') error_description
FROM MOTC.MOTINV_ITEM_USAGE_TBL miot
WHERE miot.process_flag = 'N';
v_item_number MOTC.MOTINV_ITEM_USAGE_TBL.item_number%TYPE;
v_subinventory MOTC.MOTINV_ITEM_USAGE_TBL.subinventory%TYPE;
v_locator1 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment1%TYPE;
v_locator2 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment2%TYPE;
v_locator3 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment3%TYPE;
v_locator4 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment4%TYPE;
v_quantity MOTC.MOTINV_ITEM_USAGE_TBL.quantity%TYPE;
v_error_description MOTC.MOTINV_ITEM_USAGE_TBL.error_description%TYPE;
v_output varchar2(5000);
BEGIN
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
************************************************');
fnd_file.put_line(fnd_file.output,'************* TRANSACTION - ERROR RECORDS
*************');
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
*************************************************');
v_output := RPAD('ITEM NUMBER',20,' ')||' '||RPAD('SUBINVENTORY',22,' ')||' '||
RPAD('LOCATOR',20,' ')||' '||RPAD('QUANTITY',20,' ')||' '||'ERROR DESCRIPTION';
fnd_file.put_line(fnd_file.output,v_output);
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
*************************************************');
FOR rec_err_records IN cur_err_records
LOOP
v_item_number := rec_err_records.item_number;
v_subinventory := rec_err_records.subinventory;
v_locator1 := rec_err_records.locator_segment1;
v_locator2 := rec_err_records.locator_segment2;
v_locator3 := rec_err_records.locator_segment3;
v_locator4 := rec_err_records.locator_segment4;
v_quantity := rec_err_records.quantity;
v_error_description := rec_err_records.error_description;
v_output := rpad(nvl(v_item_number,' '),20,' ')||' '||rpad(nvl(v_subinventory,' '),22,' ')||' '||
rpad(nvl(v_locator1,' '),20,' ')||' '||rpad(nvl(v_locator2,' '),20,' ')||' '||
rpad(nvl(v_locator3,' '),20,' ')||' '||rpad(nvl(v_locator4,' '),20,' ')||' '||
rpad(nvl(to_char(v_quantity),' '),20,' ')||' '||v_error_description;
fnd_file.put_line(fnd_file.output,v_output);
END LOOP;
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
***************************************************');
fnd_file.put_line(fnd_file.output,'************ END OF THE REPORT
*************');
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
***************************************************');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in the Procedure ERROR_RECORDS_OUT_GENERATE :'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
END MOT_J2J_INV_PARTS_USG_CONV_PKG;
/
IS
--/****************************************************************************************************/
--/* */
--/* Header : MOT_J2J_INV_PARTS_USAGE_CONV_PKG.sql */
--/* */
--/* Program Type: PL/SQL Package */
--/* */
--/* Creation : 28-Jul-2011 */
--/* */
--/* Author : TCS */
--/* */
--/* Description : This package is used to perform the Parts Usage Transaction for Repair Items */
--/* */
--/* DEPENDENCIES : The Following Table and Procedures are the Dependencies for this package */
--/* Table Name: */
--/* MOTC.MOTINV_ITEM_USAGE_TBL, MOTC.MOTINV_ITEM_USAGE_BAD_TBL */
--/* API's/Open Interfaces: */
--/* MTL_TRANSACTIONS_INTERFACE */
--/* Process Transaction Program */
--/* Value Sets: MOTINV_REPAIR_ORG , MOTINV_J2J_ACCOUNT_SEGMENTS */
--/* None */
--/* */
--/* Caller : Concurrent Program */
--/* */
--/* Naming Conv : P_ - Parameter variables */
--/* V_ - Global variables */
--/* L_ - local variables */
--/* */
--/* CHANGE HISTORY */
--/* ======================================================================================== */
--/* Date Version Updated By Description */
--/* ======================================================================================== */
--/* 28-Jul-2011 1.0 KNW438 Initial Creation */
--/* */
--/****************************************************************************************************/
-----------------------------------------------------------------------
/********************* Main Procedure *******************************/
-----------------------------------------------------------------------
PROCEDURE main(v_errbuf OUT VARCHAR2,
v_retcode OUT NUMBER,
v_organization_id IN NUMBER,
v_distribution_account IN Number
) IS
v_total_count NUMBER:=0;
v_account_id NUMBER;
V_ACCOUNT_ALIAS VARCHAR2(20) := 'MJSC PART ISSUE ACCT';
V_TRX_TYPE_NAME VARCHAR2(30) := 'Account alias issue';
v_process_flag varchar2(2);
BEGIN
p_errbuf := NULL;
p_retcode := 0;
v_error_flag := 'N';
p_organization_id := v_organization_id;
status_update('','','','','','','','',''); -- Initialization
v_counter := 0;
v_account_id := v_distribution_account;
-- Fetching Distribution Account ID and Disposition ID
--fnd_file.put_line(fnd_file.log,'IN MAIN'||v_process_flag);
BEGIN
SELECT gcc.code_combination_id,
mgd.disposition_id
INTO v_account_id,
v_transaction_source_id
FROM gl.gl_code_combinations gcc,
inv.mtl_generic_dispositions mgd
WHERE
gcc.code_combination_id = v_distribution_account
AND gcc.code_combination_id = mgd.distribution_account
AND mgd.segment1 = V_ACCOUNT_ALIAS
AND mgd.organization_id = v_organization_id;
v_distribution_account_id := v_account_id;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
BEGIN
SELECT COUNT(1)
INTO v_errored_record_count
FROM MOTC.MOTINV_ITEM_USAGE_TBL
WHERE (process_flag IS NULL OR process_flag = 'N');
EXCEPTION
WHEN OTHERS THEN
v_errored_record_count := v_errored_record_count +1;
END;
v_error_message := 'Invalid Distribution Account, which doesnot correspond to the Account Alias Interface';
status_update('','','','','','',v_error_message,'N','');
fnd_file.put_line(fnd_file.log,v_error_message);
END;
-- Fetching Transaction Type ID
BEGIN
SELECT transaction_type_id
INTO v_transaction_type_id
FROM inv.mtl_transaction_types
WHERE transaction_type_name = V_TRX_TYPE_NAME ;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
BEGIN
SELECT COUNT(1)
INTO v_errored_record_count
FROM MOTC.MOTINV_ITEM_USAGE_TBL
WHERE (process_flag IS NULL OR process_flag = 'N');
EXCEPTION
WHEN OTHERS THEN
v_errored_record_count := v_errored_record_count +1;
END;
v_error_message := 'Unable to fetch the Transaction Type ID corresponding to the Transaction Account alias issue';
status_update('','','','','','',v_error_message,'N','');
fnd_file.put_line(fnd_file.log,v_error_message);
END;
IF(v_error_flag = 'N') THEN
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ************************* ACCOUNT ALIAS ISSUE TRANSACTION *************************');
validate_and_insert_to_intf(v_organization_id,v_account_id);
END IF;
IF (v_errored_record_count !=0) THEN
-- Generate Error Record Output if there is any error records.
error_records_out_generate;
END IF;
fnd_file.put_line(fnd_file.log,' ');
v_total_count := v_processed_record_count+ v_errored_record_count;
fnd_file.put_line(fnd_file.log,'Number of Records Successfully Processed : ' || v_processed_record_count );
fnd_file.put_line(fnd_file.log,'Number of Failed Records: ' || v_errored_record_count);
fnd_file.put_line(fnd_file.log,'Total Number of processed Records : '|| v_total_count);
fnd_file.put_line(fnd_file.log,'End of the Process ...' || SYSDATE);
-- To Return the Error Status, if any unexpected programmatical Error occurs
-- v_errbuf := p_errbuf;
-- v_retcode := p_retcode;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Failure in Main Procedure:'||SQLCODE ||' '|| SQLERRM);
-- v_errbuf := SQLERRM;
-- v_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: VALIDATE_AND_INSERT_TO_INTF- To Check for the status of the Records Imported
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE validate_and_insert_to_intf(v_organization_id NUMBER,
v_account_id NUMBER)IS
v_item_number VARCHAR2(20);
v_inventory_item_id NUMBER;
v_subinventory VARCHAR2(10);
v_locator VARCHAR2(12);
v_locator1 VARCHAR2(12);
v_locator2 VARCHAR2(12);
v_locator3 VARCHAR2(12);
v_locator4 VARCHAR2(12);
v_quantity NUMBER;
v_period_name VARCHAR2(10);
v_serial_flag MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE%TYPE;
v_primary_uom_code VARCHAR2(100);
-- v_transaction_type_id NUMBER;
v_transaction_id NUMBER;
--v_transaction_source_id NUMBER := 3770; --Commented as part of J2J-II
-- v_distribution_account_id NUMBER;
v_return_status VARCHAR2(1):='N';
v_location_id NUMBER;
v_onhand_qty NUMBER;
V_RESERVED_QTY NUMBER;
V_AVAILABLE_QTY NUMBER;
v_transaction_date DATE;
v_ir_number VARCHAR2(12);
v_rack VARCHAR2(4);
v_level VARCHAR2(3);
v_bin VARCHAR2(3);
v_qoh NUMBER;
v_att NUMBER;
v_rowid ROWID;
v_process_flag VARCHAR2(2);
CURSOR cur_misc_RECEIPT IS
SELECT item_number,
subinventory,
locator_segment1,
locator_segment2,
locator_segment3,
locator_segment4,
quantity,
transaction_date,
trim(ir_number) ir_number,
process_flag,
rowid --Added for Prod Issue
FROM MOTC.MOTINV_ITEM_USAGE_TBL
WHERE (process_flag IS NULL OR process_flag = 'N');
BEGIN
v_error_flag := 'N';
v_error_message := NULL;
fnd_file.put_line(fnd_file.log, '1. Validation Loop begins to Insert records into Item Interface Table ');
v_distribution_account_id := v_account_id;
-- Fetch Records from the Staging Table
FOR rec_misc_RECEIPT IN cur_misc_RECEIPT
LOOP
v_error_flag := 'N';
v_item_number := rec_misc_RECEIPT.item_number;
v_subinventory := rec_misc_RECEIPT.subinventory;
v_locator1 := rec_misc_RECEIPT.locator_segment1;
v_locator2 := rec_misc_RECEIPT.locator_segment2;
v_locator3 := rec_misc_RECEIPT.locator_segment3;
v_locator4 := rec_misc_RECEIPT.locator_segment4;
v_quantity := rec_misc_RECEIPT.quantity;
v_transaction_date := rec_misc_RECEIPT.transaction_date;
v_ir_number := rec_misc_RECEIPT.ir_number;
v_rowid := rec_misc_RECEIPT.rowid;
v_process_flag := rec_misc_RECEIPT.process_flag;
--Can be commented --
fnd_file.put_line(fnd_file.log, ' ');
fnd_file.put_line(fnd_file.log, ' ');
fnd_file.put_line(fnd_file.log, ' Item Number : '||v_item_number);
fnd_file.put_line(fnd_file.log, ' Subinventory : '||v_subinventory);
fnd_file.put_line(fnd_file.log, ' Locator : '||v_locator1||''||v_locator2||''||v_locator3||''||v_locator4);
fnd_file.put_line(fnd_file.log, ' Process flag : '||v_process_flag);
fnd_file.put_line(fnd_file.log, ' IR NUmber : '||v_ir_number);
--
IF (v_error_flag = 'N') THEN
-- 1st Validation for Item Part Number
BEGIN
SELECT inventory_item_id,
primary_uom_code,
SERIAL_NUMBER_CONTROL_CODE
INTO v_inventory_item_id,
v_primary_uom_code,
v_serial_flag
FROM inv.mtl_system_items_b
WHERE segment1 = v_item_number
AND organization_id = v_organization_id;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Invalid Item Name ' || v_item_number;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- Get Inventory Period Status
IF (v_error_flag = 'N')
THEN
BEGIN
SELECT period_name
INTO v_period_name
FROM org_acct_periods_v
WHERE rec_type = 'ORG_PERIOD'
AND organization_id = v_organization_id
AND (TO_DATE (TRUNC(v_transaction_date))) BETWEEN TRUNC(start_date) AND TRUNC(end_date)
AND status = 'Open';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Inventory Period Is not Open for transaction date ' || v_item_number;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Error Retrieving Inventory Period ' || v_item_number;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
--End Inventory Period Status
END IF;
IF (v_error_flag = 'N') THEN
-- 2nd Validation for Subinventory
BEGIN
SELECT msi.secondary_inventory_name
INTO v_subinventory
FROM inv.mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = v_subinventory
AND organization_id = v_organization_id
AND (disable_date IS NULL OR trunc(disable_date)>trunc(SYSDATE));
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Invalid Subinventory Name ' || v_subinventory;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- 4th Validation, Quantity should not be NULL
IF (v_error_flag = 'N') THEN
IF (v_quantity IS NOT NULL) THEN
v_quantity := v_quantity *(-1);
ELSE
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Quantity Field doesnot have any value';
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message);
END IF;
END IF;
-- 6th fetch Locator ID
IF (v_error_flag = 'N') THEN
BEGIN
Select mil.inventory_location_id
into v_location_id
from mtl_item_locations mil
where organization_id = v_organization_id
and mil.segment1= v_locator1
and mil.segment2= v_locator2
and mil.segment3= v_locator3
and mil.segment4= v_locator4
and enabled_flag='Y';
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Invalid Locator Name ' || v_locator1||'.'||v_locator2||'.'||v_locator3||'.'||v_locator4;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- 7th Onhand quantity validation
-- Transaction quantity
IF (v_error_flag = 'N') THEN
BEGIN
SELECT NVL(SUM(MOQD.TRANSACTION_QUANTITY),0)
INTO v_onhand_qty
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQD
WHERE MOQD.INVENTORY_ITEM_ID=v_inventory_item_id
AND MOQD.ORGANIZATION_ID=v_organization_id
AND MOQD.SUBINVENTORY_CODE =v_subinventory
AND MOQD.LOCATOR_ID = v_location_id;
IF( v_onhand_qty = 0) THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Validation failed for Transaction Quantity ' || v_onhand_qty;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Validation failed for Transaction Quantity ' || v_onhand_qty;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- Reserved Quantity
IF (v_error_flag = 'N') THEN
BEGIN
SELECT NVL(SUM(MR.RESERVATION_QUANTITY),0)
INTO V_RESERVED_QTY
FROM MTL_RESERVATIONS MR
WHERE MR.INVENTORY_ITEM_ID=v_inventory_item_id
AND MR.ORGANIZATION_ID=v_organization_id
AND MR.SUBINVENTORY_CODE = v_subinventory
AND MR.LOCATOR_ID = v_location_id;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Validation failed at Reserved Quantity ' || V_RESERVED_QTY;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
-- Calculating Available Quantity
IF (v_error_flag = 'N') THEN
BEGIN
V_AVAILABLE_QTY:=(NVL(v_onhand_qty ,0)-NVL(V_RESERVED_QTY,0));
END;
END IF;
IF (v_error_flag = 'N') THEN
BEGIN
IF V_AVAILABLE_QTY<v_quantity THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Insufficient quantity for the item ' || v_item_number;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END IF;
END;
END IF;
-- Fetch Transact ID
IF (v_error_flag = 'N') THEN
BEGIN
SELECT mtl_material_transactions_s.nextval
INTO v_transaction_id
FROM dual;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Unable to generate the sequence number from MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL';
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
fnd_file.put_line(fnd_file.log,v_error_message||' '||SQLCODE ||' '|| SQLERRM);
END;
END IF;
IF (v_error_flag = 'N') THEN
BEGIN
fnd_file.put_line(fnd_file.log, 'Before inserting'||v_transaction_id);
-- Account Alias Issue Details Population
INSERT INTO inv.mtl_transactions_interface(transaction_type_id,
transaction_uom,
transaction_date,
organization_id,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_mode,
process_flag,
transaction_source_id,
source_header_id,
source_line_id,
source_code,
lock_flag,
flow_schedule,
scheduled_flag,
transaction_header_id,
inventory_item_id,
transaction_interface_id,
subinventory_code,
distribution_account_id,
transaction_cost,
locator_id,
transaction_reference
)
VALUES (v_transaction_type_id, -- Transaction Type Id
v_primary_uom_code, -- Item Basic UOM
v_transaction_date, -- TransactionDate
v_organization_id, -- Organization id
v_quantity, -- Quantity
SYSDATE, -- Last Update Date
v_user_id, -- Last Update By
SYSDATE, -- Created Date
v_user_id, -- Created By
3, -- Background Processing
1, -- ''1 ready to be processed
v_transaction_source_id, -- Transaction Source ID -- 3770
v_transaction_id, -- Source Header ID
v_transaction_id, -- Source Line ID
'Account alias', --'Inventory' -- Source Code
2, -- Lock Flag
'Y', -- Flow Schedule
2, -- Scheduled Flag
v_transaction_id, -- Transaction Header ID
v_inventory_item_id, -- Inventory Item ID
v_transaction_id, -- Transaction Interface ID
v_subinventory, -- Item Number
v_distribution_account_id, -- Account ID
NULL, -- Transaction Cost
v_location_id, -- Locator ID
v_ir_number --Added due to J2J stock loading
);
--fnd_file.put_line(fnd_file.log, 'After inserting'||v_transaction_id);
/*
if v_serial_flag = 5
then
For rec_misc_RECEIPT_SER in cur_misc_RECEIPT_SER (rec_misc_RECEIPT.item_number, rec_misc_RECEIPT.subinventory)
loop
-- insert data into MTL_SERIAL_ITEMS_INTERFACE
-- for the combination of the item, subinventory, -- org
-- combination
For I in 1 .. rec_misc_RECEIPT.Quantity
loop
Insert into mtl_serial_numbers_interface
(transaction_interface_id,
fm_serial_number,
to_serial_number,
last_update_date,
last_updated_by,
creation_date,
created_by)
values
(v_transaction_id, --transaction interface_id
rec_misc_RECEIPT_SER.serial_number , --from serial number
rec_misc_RECEIPT_SER.serial_number , --to serial number
sysdate, --last update date
0, --last updated by
sysdate, --creation date
0 --created by
);
commit; -- can be removed later on
UPDATE MOTC.MOTINV_ITEM_ONHAND_SERIAL_TBL SET transaction_id = v_transaction_id,
last_update_date = sysdate,
last_updated_by = v_user_id
WHERE item_number = v_item_number
AND subinventory = v_subinventory
and serial_number = rec_misc_RECEIPT_SER.serial_number;
End Loop;
End Loop;
End if;
*/
COMMIT;
v_counter := v_counter + 1;
-- Update the Transaction ID
UPDATE MOTC.MOTINV_ITEM_USAGE_TBL SET transaction_id = v_transaction_id,
last_update_date = sysdate,
last_updated_by = v_user_id,
process_flag='P'--Added for Prod Issue
WHERE
item_number = v_item_number
AND subinventory = v_subinventory
AND LOCATOR_SEGMENT1 = v_locator1
AND LOCATOR_SEGMENT2 = v_locator2
AND LOCATOR_SEGMENT3 = v_locator3
AND LOCATOR_SEGMENT4 = v_locator4
AND transaction_id is null
AND rowid=v_rowid;
COMMIT;
fnd_file.put_line(fnd_file.log, 'After updating transactionid'||v_counter||','||v_transaction_id);
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_errored_record_count := v_errored_record_count +1;
v_error_message := 'Unable to insert into the Open Interface Table MTL_MATERIAL_INTERFACE'||SQLCODE ||' '|| SQLERRM;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'N',v_rowid);
END;
END IF;
END LOOP;
IF ( v_counter != 0) THEN
call_interface_program;
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in Procedure VALIDATE_AND_INSERT_TO_INTF :'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: CALL_INTERFACE_PROGRAM - To Launch the Process transaction interface
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE call_interface_program IS
v_i NUMBER;
v_interface_request_id NUMBER:=0;
v_phase VARCHAR2(100);
v_status VARCHAR2(100);
v_dev_phase VARCHAR2(100);
v_dev_status VARCHAR2(100);
v_message VARCHAR2(2000);
v_wait_outcome BOOLEAN;
v_child_intf_request_id NUMBER:=0;
BEGIN
fnd_file.put_line(fnd_file.LOG,' Process transaction interface Program Starts....');
v_interface_request_id:=apps.fnd_request.submit_request(application => 'INV',
program => 'INCTCM',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE);
COMMIT;
fnd_file.put_line(fnd_file.log,'Request ID for Process Transaction Interface Program :'||v_interface_request_id);
IF (v_interface_request_id = 0) THEN
fnd_file.put_line(fnd_file.log, 'Error message' || SQLERRM);
ELSIF (v_interface_request_id IS NULL) THEN
fnd_file.put_line(fnd_file.log,'No Process Transaction Program has been launched');
ELSE
LOOP
--Concurrent Phase Loop Starts
v_wait_outcome := fnd_concurrent.get_request_status(request_id => v_interface_request_id,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
message => v_message);
EXIT WHEN UPPER(v_phase) = 'COMPLETED';
END LOOP; --Concurrent Phase Loop End
-- Get the Inventory Transaction Worker Request and wait for it
BEGIN
SELECT MAX(request_id)
INTO v_child_intf_request_id
FROM fnd_concurrent_requests
WHERE parent_request_id = v_interface_request_id;
EXCEPTION
WHEN OTHERS THEN
v_child_intf_request_id := 0;
END;
IF (v_child_intf_request_id !=0 AND v_child_intf_request_id IS NOT NULL) THEN
LOOP
--Concurrent Phase Loop Starts
v_wait_outcome := fnd_concurrent.get_request_status(request_id => v_child_intf_request_id,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
message => v_message);
EXIT WHEN UPPER(v_phase) = 'COMPLETED';
END LOOP; --Concurrent Phase Loop End
END IF;
END IF;
check_interface_rec_status;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in Procedure CALL_INTERFACE_PROGRAM :'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: CHECK_INTERFACE_REC_STATUS- To Check for the status of the Records Imported
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE check_interface_rec_status IS
CURSOR cur_check_status
IS SELECT miot.item_number,
miot.subinventory,
miot.locator_segment1,
miot.locator_segment2,
miot.locator_segment3,
miot.locator_segment4,
miot.transaction_id,
miot.rowid,
miot.process_flag
FROM MOTC.MOTINV_ITEM_USAGE_TBL miot
WHERE miot.transaction_id IS NOT NULL
AND (miot.process_flag='N' OR miot.process_flag IS NULL OR miot.process_flag='P' );
v_item_number MOTC.MOTINV_ITEM_USAGE_TBL.item_number%TYPE;
v_subinventory MOTC.MOTINV_ITEM_USAGE_TBL.subinventory%TYPE;
v_locator1 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment1%TYPE;
v_locator2 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment2%TYPE;
v_locator3 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment3%TYPE;
v_locator4 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment4%TYPE;
v_transaction_id NUMBER;
v_process_flag MOTC.MOTINV_ITEM_USAGE_TBL.process_flag%TYPE;
v_error_description VARCHAR2(2000);
v_error_message VARCHAR2(2000);
vl_rowid ROWID;--Added for Prod Issue
BEGIN
fnd_file.put_line(fnd_file.LOG,' Interface Error Check Starts ...');
--fnd_file.put_line(fnd_file.log,'AFTER Interface error check starts'|| v_process_flag);
-- To Update the Status of the Failed Records
FOR rec_check_status IN cur_check_status
LOOP
--fnd_file.put_line(fnd_file.log, 'Start of FOR LOOP');
v_item_number := rec_check_status.item_number;
v_subinventory := rec_check_status.subinventory;
v_locator1 := rec_check_status.locator_segment1;
v_locator2 := rec_check_status.locator_segment2;
v_locator3 := rec_check_status.locator_segment3;
v_locator4 := rec_check_status.locator_segment4;
v_transaction_id := rec_check_status.transaction_id;
v_error_description := '';
v_process_flag := rec_check_status.process_flag;
vl_rowid :=rec_check_status.rowid; --Added for Prod Issue
--fnd_file.put_line(fnd_file.log, 'TRANSACTIONID is '||v_transaction_id);
-- fnd_file.put_line(fnd_file.log, 'Middle of FOR LOOP');
-- fnd_file.put_line(fnd_file.log, 'Middle of for loop'||v_process_flag);
BEGIN
fnd_file.put_line(fnd_file.log, 'BEFORE SELECT STMT');
SELECT mti.process_flag,
mti.error_code||' '||mti.error_explanation
INTO v_process_flag,
v_error_description
FROM inv.mtl_transactions_interface mti
WHERE mti.transaction_interface_id = v_transaction_id;
--fnd_file.put_line(fnd_file.log, 'PROCESS_FLAG ===='||v_process_flag);
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_process_flag := 0;
v_error_description := NULL;
WHEN OTHERS THEN
v_process_flag := 0;
v_error_description := NULL;
END;
IF (v_process_flag = 3) THEN -- Successfully Interfaced Records
v_errored_record_count := v_errored_record_count + 1;
v_error_message := v_error_description;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'I',vl_rowid);
ELSIF (v_process_flag = 2 OR v_process_flag=1) THEN -- Successfully Interfaced Records
v_errored_record_count := v_errored_record_count + 1;
v_error_message := 'Record has not been processed from MTL_TRANSACTON_INTERFACE table';
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,v_error_message,'I',vl_rowid);
ELSE
v_processed_record_count := v_processed_record_count + 1;
status_update(v_item_number,v_subinventory,v_locator1,v_locator2,v_locator3,v_locator4,'','Y',vl_rowid);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in the Procedure CHECK_INTERFACE_REC_STATUS:'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: STATUS_UPDATE - Update the Status to the Staging table
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE status_update(v_item_number VARCHAR2,
v_subinventory VARCHAR2,
v_locator1 VARCHAR2,
v_locator2 VARCHAR2,
v_locator3 VARCHAR2,
v_locator4 VARCHAR2,
v_error_message VARCHAR2,
v_process_flag VARCHAR2,
v_rowid ROWID) IS
BEGIN
--fnd_file.put_line(fnd_file.log,'IN IF LOOP OF STATUS UPDATE'||v_process_flag);
IF ( v_item_number IS NULL AND v_subinventory IS NULL) THEN
UPDATE MOTC.MOTINV_ITEM_USAGE_TBL SET process_flag = v_process_flag,
error_description = v_error_message,
transaction_id ='',
last_update_date = sysdate,
last_updated_by = v_user_id
WHERE process_flag IS NULL OR process_flag='N';
COMMIT;
fnd_file.put_line(fnd_file.log,'IN IF LOOP OF STATUS UPDATE'||v_process_flag);
ELSE
--fnd_file.put_line(fnd_file.log,'IN ELSE LOOP OF STATUS UPDATE'||v_process_flag);
UPDATE MOTC.MOTINV_ITEM_USAGE_TBL SET process_flag = v_process_flag,
error_description = v_error_message,
last_update_date = sysdate,
last_updated_by = v_user_id
WHERE item_number = v_item_number
AND subinventory = v_subinventory
AND LOCATOR_SEGMENT1 = v_locator1
AND LOCATOR_SEGMENT2 = v_locator2
AND LOCATOR_SEGMENT3 = v_locator3
AND LOCATOR_SEGMENT4 = v_locator4
AND ROWID=v_rowid;--Added for Prod Issue
COMMIT;
--fnd_file.put_line(fnd_file.log,'IN ELSE LOOP OF STATUS UPDATE'||v_process_flag);
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in the Procedure STATUS_UPDATE:'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
-------------------------------------------------------------------------------------------------------------------------------------
-- Procedure: ERROR_RECORDS_OUT_GENERATE - Generate the output file in the FND Output
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE error_records_out_generate IS
CURSOR cur_err_records
IS SELECT miot.item_number,
miot.subinventory,
miot.locator_segment1,
miot.locator_segment2,
miot.locator_segment3,
miot.locator_segment4,
miot.quantity,
replace(replace(miot.error_description,chr(10),' '),chr(13),' ') error_description
FROM MOTC.MOTINV_ITEM_USAGE_TBL miot
WHERE miot.process_flag = 'N';
v_item_number MOTC.MOTINV_ITEM_USAGE_TBL.item_number%TYPE;
v_subinventory MOTC.MOTINV_ITEM_USAGE_TBL.subinventory%TYPE;
v_locator1 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment1%TYPE;
v_locator2 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment2%TYPE;
v_locator3 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment3%TYPE;
v_locator4 MOTC.MOTINV_ITEM_USAGE_TBL.locator_segment4%TYPE;
v_quantity MOTC.MOTINV_ITEM_USAGE_TBL.quantity%TYPE;
v_error_description MOTC.MOTINV_ITEM_USAGE_TBL.error_description%TYPE;
v_output varchar2(5000);
BEGIN
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
************************************************');
fnd_file.put_line(fnd_file.output,'************* TRANSACTION - ERROR RECORDS
*************');
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
*************************************************');
v_output := RPAD('ITEM NUMBER',20,' ')||' '||RPAD('SUBINVENTORY',22,' ')||' '||
RPAD('LOCATOR',20,' ')||' '||RPAD('QUANTITY',20,' ')||' '||'ERROR DESCRIPTION';
fnd_file.put_line(fnd_file.output,v_output);
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
*************************************************');
FOR rec_err_records IN cur_err_records
LOOP
v_item_number := rec_err_records.item_number;
v_subinventory := rec_err_records.subinventory;
v_locator1 := rec_err_records.locator_segment1;
v_locator2 := rec_err_records.locator_segment2;
v_locator3 := rec_err_records.locator_segment3;
v_locator4 := rec_err_records.locator_segment4;
v_quantity := rec_err_records.quantity;
v_error_description := rec_err_records.error_description;
v_output := rpad(nvl(v_item_number,' '),20,' ')||' '||rpad(nvl(v_subinventory,' '),22,' ')||' '||
rpad(nvl(v_locator1,' '),20,' ')||' '||rpad(nvl(v_locator2,' '),20,' ')||' '||
rpad(nvl(v_locator3,' '),20,' ')||' '||rpad(nvl(v_locator4,' '),20,' ')||' '||
rpad(nvl(to_char(v_quantity),' '),20,' ')||' '||v_error_description;
fnd_file.put_line(fnd_file.output,v_output);
END LOOP;
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
***************************************************');
fnd_file.put_line(fnd_file.output,'************ END OF THE REPORT
*************');
fnd_file.put_line(fnd_file.output,'**************************************************************************************************************************
***************************************************');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Failure in the Procedure ERROR_RECORDS_OUT_GENERATE :'||SQLCODE ||' '|| SQLERRM);
p_errbuf := SQLERRM;
p_retcode := SQLCODE;
END;
END MOT_J2J_INV_PARTS_USG_CONV_PKG;
/
No comments:
Post a Comment