/*
REM +======================================================================+
REM | |
REM | |
REM | All rights reserved. |
REM +======================================================================+ */
/*================================================================================================================*/
/*
DESCRIPTION:
Deliverable Name: Data_Fix_219386_1.sql
Description: This ticket has been created for WR#219386 Fix
Created By:
USAGE:
Run this script from the APPS schema.
CHANGE HISTORY:
Date Name Description
---------- -------------------------------------- ---------------------------------
20/Feb/2015 Siddarth Created
*/
SET SERVEROUTPUT ON
DECLARE
CURSOR c1_itm IS SELECT * FROM apps.mtl_system_items where organization_id = 5204
and inventory_item_id = 93805;
CURSOR c2_org IS SELECT * FROM apps.org_organization_definitions WHERE operating_unit IN (5758, 6359, 5203);
l_count NUMBER := 0;
l_ignore VARCHAR2(10) := 'N';
l_segment1 VARCHAR2(240);
BEGIN
dbms_output.enable(20000);
FOR r1_itm IN c1_itm LOOP
FOR r2_org IN c2_org LOOP
l_ignore := 'N';
BEGIN
SELECT segment1
INTO l_segment1
FROM mtl_system_items
WHERE segment1 = r1_itm.segment1
AND organization_id = r2_org.organization_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_ignore := 'Y';
WHEN OTHERS THEN
l_ignore := 'N';
END;
DBMS_OUTPUT.PUT_LINE('Item Name - Org: ' || r1_itm.segment1 || ' - '|| r2_org.organization_code);
IF l_ignore = 'N' THEN
INSERT INTO mtl_system_items_interface
(PROCESS_FLAG
,SEGMENT1
,DESCRIPTION
,SET_PROCESS_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE)
VALUES
(1
,r1_itm.segment1
,'Configured Assy, PCIe Quad Port - 2x4GbFC/2x1GbE Combo HBA, QEM3472, SUN'
,11143000
,r2_org.organization_id
,'UPDATE');
l_count := l_count + SQL%ROWCOUNT;
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Records inserted : ' || l_count);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error when inserting into MSII ' || SQLERRM);
END;
/
