Install Base creation for all the Sales Order for which it is missing

Install Base creation for all the Sales Order for which it is missing:-
------------------------------------------------------------------------------

You can using the following package to create the IB (Install base) for the sales Order It has missed.

Normally, we enable the Install base option in the "Master Item" form for the Items for which we want to track. If we enable this option, then when we create the sales order with the Item for which Install base option is check then, you would see the IB got created.

CREATE OR REPLACE PACKAGE alloracletech_ib_pkg
IS
PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER);
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2);
END alloracletech_ib_pkg;
/

CREATE OR REPLACE PACKAGE BODY alloracletech_ib_pkg
IS

PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER)
IS
/************************************************************************
Purpose : This procedure creates IB for all the order which are missed.
*************************************************************************/
l_header_id NUMBER;
l_mtl_txn_id NUMBER;
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;

CURSOR c_ib
IS
SELECT -- oeh.order_number,
oel1.line_id
-- oeh.creation_date,
-- msib1.inventory_item_id,
-- msib1.segment1
FROM oe_order_lines_all oel1,
mtl_system_items_b msib1,
oe_order_headers_all oeh
WHERE oel1.ordered_item = msib1.segment1
AND msib1.comms_nl_trackable_flag = 'Y'
AND msib1.shippable_item_flag = 'Y'
AND msib1.organization_id = 22
AND oeh.header_id = oel1.header_id
AND oel1.flow_status_code = 'CLOSED'
AND oel1.line_category_code = 'ORDER'
AND oeh.order_type_id = 1008
-- AND oel1.line_id IN (5599900, 5742086)
-- AND oeh.order_number IN ( )
AND oeh.shipping_method_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM csi.csi_item_instances cii
WHERE cii.last_oe_order_line_id = oel1.line_id);
BEGIN
FOR i IN c_ib
LOOP
BEGIN
SELECT transaction_id
INTO l_mtl_txn_id
FROM mtl_material_transactions
WHERE trx_source_line_id = i.line_id
AND transaction_type_id = 33;

-- dbms_output.put_line(i.order_number||' '||i.line_id);
csi_inv_txn_hook_pkg.posttransaction
(p_header_id => l_header_id,
p_transaction_id => l_mtl_txn_id,
x_return_status => l_return_status
);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
WRITE ('L', 'No Transaction ID for the Line ID : ' || i.line_id );
WHEN TOO_MANY_ROWS THEN
WRITE ('L','More then one Transaction ID for the Line ID : ' || i.line_id );
WHEN OTHERS THEN
WRITE ('L',
'Error in LineID :' || i.line_id || CHR (10) || SQLCODE || ' : ' || SQLERRM
);
END;
END LOOP;
END create_install_base;

/************************************************************************/
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : This procedure writes to the output file or log file depending
on the parameter p_type passed.
*************************************************************************/
BEGIN
IF p_type = 'L'
THEN
fnd_file.put_line (fnd_file.LOG, p_message);
ELSIF p_type = 'O'
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;
END WRITE;
END alloracletech_ib_pkg;
/

Use the following to Execute the Procedure.

begin
alloracletech_ib_pkg.create_install_base;
end;
/

The following query can be used to find out whether Install Base (IB) created or not for the Order line.

select * from csi.csi_item_instances
where last_oe_order_line_id IN (Your Order Line ID);

Example:-

select * from csi.csi_item_instances
where last_oe_order_line_id IN (6912858, 6912859, 6912860);

I hope you find the above information help full.

Note:- There are few things which are hard-corded like order_type_id = 1008 etc, these are specific to my instance setup. It may vary for your Instance setup. Change the values accordingly.

Note:- I have tested above script in the 11i Instances.