CREATE OR REPLACE PROCEDURE apps.xxstx_expire_ib_instance
IS
l_instance_rec csi_datastructures_pub.instance_rec;
l_txn_rec csi_datastructures_pub.transaction_rec;
l_instance_id_lst csi_datastructures_pub.id_tbl;
l_cust_account_id NUMBER;
l_party_id NUMBER;
l_line_type_id NUMBER;
l_msg_index_out NUMBER; -- scratch index
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
g_message VARCHAR2 (4000);
l_contract_number NUMBER;
l_inventory_item_id NUMBER;
l_segment1 VARCHAR2 (100);
l_serial_number VARCHAR2 (100);
l_start_date DATE;
l_count NUMBER;
l_count1 NUMBER;
l_instance_id VARCHAR2 (20);
l_msg_data VARCHAR2 (2000);
l_mesg VARCHAR2 (4000);
l_mesg_count NUMBER;
l_item VARCHAR2 (100);
l_instance_number VARCHAR2 (150);
l_inv_master_org_id NUMBER;
l_object_version_number NUMBER;
no_need_expire EXCEPTION;
CURSOR c1
IS
SELECT *
FROM xxstx_bulk_exp_temp where REC_STS IS NULL; --and serial_number = 'DMPJG2NNF18Y'; --Temp Table to store data, Custome table.
BEGIN
-- fnd_global.apps_initialize(1230,22935,542);
-- mo_global.set_policy_context('S',82);
DBMS_OUTPUT.put_line ('IN FIRST BEGIN');
FOR i IN c1
LOOP
DBMS_OUTPUT.put_line ( 'Values got from Cursor = Serial_number:'
|| i.serial_number
|| ',item:'
|| i.item
);
BEGIN
SELECT cii.instance_id, cii.serial_number, cii.inventory_item_id,
cii.instance_number, cii.inv_master_organization_id,
cii.object_version_number
INTO l_instance_id, l_serial_number, l_inventory_item_id,
l_instance_number, l_inv_master_org_id,
l_object_version_number
FROM csi_item_instances cii, mtl_system_items mtl
WHERE cii.serial_number = i.serial_number --'35200400288'
AND mtl.segment1 = i.item --'760B85G4E800N000'
AND mtl.organization_id = 101
AND mtl.inventory_item_id = cii.inventory_item_id
AND cii.instance_number =
NVL (i.instance_number, cii.instance_number);
BEGIN
DBMS_OUTPUT.put_line ( 'Values got from Query :'
|| l_instance_id
|| l_serial_number
|| l_inventory_item_id
|| l_instance_number
|| l_inv_master_org_id
|| l_object_version_number
);
SELECT COUNT (contract_number)
INTO l_count
FROM (SELECT contract_number
FROM okc_k_headers_all_b
WHERE ID IN (SELECT MAX (dnz_chr_id)
FROM okc_k_items
WHERE object1_id1 = l_instance_id)
AND sts_code IN
('ACTIVE', 'QA_HOLD', 'SIGNED', 'ENTERED')
UNION
SELECT contract_number
FROM okc_k_headers_all_b
WHERE ID IN (SELECT MAX (dnz_chr_id)
FROM okc_k_items
WHERE object1_id1 = l_inventory_item_id)
AND sts_code IN
('ACTIVE', 'QA_HOLD', 'SIGNED', 'ENTERED'));
DBMS_OUTPUT.put_line
( 'If statement for serial nas active number of contracts: '
|| l_count
);
IF l_count = 0
THEN
DBMS_OUTPUT.put_line ('If statement if count =0');
l_instance_rec.instance_id := l_instance_id;
l_instance_rec.instance_number := l_instance_number;
l_instance_rec.external_reference := NULL;
l_instance_rec.inventory_item_id := l_inventory_item_id;
l_instance_rec.inv_master_organization_id :=
l_inv_master_org_id;
l_instance_rec.active_end_date := TRUNC (SYSDATE);
l_instance_rec.object_version_number :=
l_object_version_number;
l_txn_rec.transaction_date := TRUNC (SYSDATE);
l_txn_rec.source_transaction_date := TRUNC (SYSDATE);
l_txn_rec.transaction_type_id := 1;
l_txn_rec.object_version_number := 1;
DBMS_OUTPUT.put_line ('EXPIRE INSATCNE');
csi_item_instance_pub.expire_item_instance
(p_api_version => 1.0
-- IN NUMBER
,
p_instance_rec => l_instance_rec
-- IN csi_datastructures_pub.instance_rec
,
p_txn_rec => l_txn_rec
-- IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,
x_instance_id_lst => l_instance_id_lst
-- OUT NOCOPY csi_datastructures_pub.id_tbl
,
x_return_status => x_return_status
-- OUT NOCOPY VARCHAR2
,
x_msg_count => x_msg_count
-- OUT NOCOPY NUMBER
,
x_msg_data => x_msg_data
-- OUT NOCOPY VARCHAR2
);
DBMS_OUTPUT.put_line ('EXPIRE INSATCNE DONE');
DBMS_OUTPUT.put_line ('API Status:=' || x_return_status);
IF x_return_status != apps.fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line
('failed. printing error msg... at owner and LOCATION changes'
);
fnd_msg_pub.get (p_msg_index => apps.fnd_msg_pub.g_last,
p_encoded => apps.fnd_api.g_false,
p_data => x_msg_data,
p_msg_index_out => l_msg_index_out
);
g_message := x_msg_data;
--fnd_file.put_line (fnd_file.LOG,
-- 'Instance # '
---- || l_instance_number || ' could not be expired. Error: ' || g_message
-- );
DBMS_OUTPUT.put_line ( 'Instance # '
|| l_instance_number
|| ' could not be expired. Error: '
|| g_message
);
ROLLBACK;
ELSE
DBMS_OUTPUT.put_line
(' IB instance updated sucessfully at owner and LOCATION changes '
);
DBMS_OUTPUT.put_line ('Step8 : end update API -');
NULL;
END IF;
UPDATE xxstx_bulk_exp_temp
SET rec_sts = 'PROCESSED'
WHERE serial_number = l_serial_number;
DBMS_OUTPUT.put_line ('UPDATED TABLE');
ELSE
UPDATE xxstx_bulk_exp_temp
SET rec_sts = 'ACTIVE CONTRACT'
WHERE serial_number = l_serial_number;
DBMS_OUTPUT.put_line ('l_count is More than 0' || l_count);
END IF;
END;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (' Exception NO DATA FOUND');
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN no_need_expire
THEN
DBMS_OUTPUT.put_line (' no_need_expire NO DATA FOUND');
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error in Main:=' || SQLERRM);
END xxstx_expire_ib_instance;
/
IS
l_instance_rec csi_datastructures_pub.instance_rec;
l_txn_rec csi_datastructures_pub.transaction_rec;
l_instance_id_lst csi_datastructures_pub.id_tbl;
l_cust_account_id NUMBER;
l_party_id NUMBER;
l_line_type_id NUMBER;
l_msg_index_out NUMBER; -- scratch index
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
g_message VARCHAR2 (4000);
l_contract_number NUMBER;
l_inventory_item_id NUMBER;
l_segment1 VARCHAR2 (100);
l_serial_number VARCHAR2 (100);
l_start_date DATE;
l_count NUMBER;
l_count1 NUMBER;
l_instance_id VARCHAR2 (20);
l_msg_data VARCHAR2 (2000);
l_mesg VARCHAR2 (4000);
l_mesg_count NUMBER;
l_item VARCHAR2 (100);
l_instance_number VARCHAR2 (150);
l_inv_master_org_id NUMBER;
l_object_version_number NUMBER;
no_need_expire EXCEPTION;
CURSOR c1
IS
SELECT *
FROM xxstx_bulk_exp_temp where REC_STS IS NULL; --and serial_number = 'DMPJG2NNF18Y'; --Temp Table to store data, Custome table.
BEGIN
-- fnd_global.apps_initialize(1230,22935,542);
-- mo_global.set_policy_context('S',82);
DBMS_OUTPUT.put_line ('IN FIRST BEGIN');
FOR i IN c1
LOOP
DBMS_OUTPUT.put_line ( 'Values got from Cursor = Serial_number:'
|| i.serial_number
|| ',item:'
|| i.item
);
BEGIN
SELECT cii.instance_id, cii.serial_number, cii.inventory_item_id,
cii.instance_number, cii.inv_master_organization_id,
cii.object_version_number
INTO l_instance_id, l_serial_number, l_inventory_item_id,
l_instance_number, l_inv_master_org_id,
l_object_version_number
FROM csi_item_instances cii, mtl_system_items mtl
WHERE cii.serial_number = i.serial_number --'35200400288'
AND mtl.segment1 = i.item --'760B85G4E800N000'
AND mtl.organization_id = 101
AND mtl.inventory_item_id = cii.inventory_item_id
AND cii.instance_number =
NVL (i.instance_number, cii.instance_number);
BEGIN
DBMS_OUTPUT.put_line ( 'Values got from Query :'
|| l_instance_id
|| l_serial_number
|| l_inventory_item_id
|| l_instance_number
|| l_inv_master_org_id
|| l_object_version_number
);
SELECT COUNT (contract_number)
INTO l_count
FROM (SELECT contract_number
FROM okc_k_headers_all_b
WHERE ID IN (SELECT MAX (dnz_chr_id)
FROM okc_k_items
WHERE object1_id1 = l_instance_id)
AND sts_code IN
('ACTIVE', 'QA_HOLD', 'SIGNED', 'ENTERED')
UNION
SELECT contract_number
FROM okc_k_headers_all_b
WHERE ID IN (SELECT MAX (dnz_chr_id)
FROM okc_k_items
WHERE object1_id1 = l_inventory_item_id)
AND sts_code IN
('ACTIVE', 'QA_HOLD', 'SIGNED', 'ENTERED'));
DBMS_OUTPUT.put_line
( 'If statement for serial nas active number of contracts: '
|| l_count
);
IF l_count = 0
THEN
DBMS_OUTPUT.put_line ('If statement if count =0');
l_instance_rec.instance_id := l_instance_id;
l_instance_rec.instance_number := l_instance_number;
l_instance_rec.external_reference := NULL;
l_instance_rec.inventory_item_id := l_inventory_item_id;
l_instance_rec.inv_master_organization_id :=
l_inv_master_org_id;
l_instance_rec.active_end_date := TRUNC (SYSDATE);
l_instance_rec.object_version_number :=
l_object_version_number;
l_txn_rec.transaction_date := TRUNC (SYSDATE);
l_txn_rec.source_transaction_date := TRUNC (SYSDATE);
l_txn_rec.transaction_type_id := 1;
l_txn_rec.object_version_number := 1;
DBMS_OUTPUT.put_line ('EXPIRE INSATCNE');
csi_item_instance_pub.expire_item_instance
(p_api_version => 1.0
-- IN NUMBER
,
p_instance_rec => l_instance_rec
-- IN csi_datastructures_pub.instance_rec
,
p_txn_rec => l_txn_rec
-- IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,
x_instance_id_lst => l_instance_id_lst
-- OUT NOCOPY csi_datastructures_pub.id_tbl
,
x_return_status => x_return_status
-- OUT NOCOPY VARCHAR2
,
x_msg_count => x_msg_count
-- OUT NOCOPY NUMBER
,
x_msg_data => x_msg_data
-- OUT NOCOPY VARCHAR2
);
DBMS_OUTPUT.put_line ('EXPIRE INSATCNE DONE');
DBMS_OUTPUT.put_line ('API Status:=' || x_return_status);
IF x_return_status != apps.fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line
('failed. printing error msg... at owner and LOCATION changes'
);
fnd_msg_pub.get (p_msg_index => apps.fnd_msg_pub.g_last,
p_encoded => apps.fnd_api.g_false,
p_data => x_msg_data,
p_msg_index_out => l_msg_index_out
);
g_message := x_msg_data;
--fnd_file.put_line (fnd_file.LOG,
-- 'Instance # '
---- || l_instance_number || ' could not be expired. Error: ' || g_message
-- );
DBMS_OUTPUT.put_line ( 'Instance # '
|| l_instance_number
|| ' could not be expired. Error: '
|| g_message
);
ROLLBACK;
ELSE
DBMS_OUTPUT.put_line
(' IB instance updated sucessfully at owner and LOCATION changes '
);
DBMS_OUTPUT.put_line ('Step8 : end update API -');
NULL;
END IF;
UPDATE xxstx_bulk_exp_temp
SET rec_sts = 'PROCESSED'
WHERE serial_number = l_serial_number;
DBMS_OUTPUT.put_line ('UPDATED TABLE');
ELSE
UPDATE xxstx_bulk_exp_temp
SET rec_sts = 'ACTIVE CONTRACT'
WHERE serial_number = l_serial_number;
DBMS_OUTPUT.put_line ('l_count is More than 0' || l_count);
END IF;
END;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (' Exception NO DATA FOUND');
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN no_need_expire
THEN
DBMS_OUTPUT.put_line (' no_need_expire NO DATA FOUND');
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error in Main:=' || SQLERRM);
END xxstx_expire_ib_instance;
/
