SET SERVEROUTPUT ON SIZE 1000000
SET ECHO OFF
SET VERIFY OFF
SET DEFINE "~"
SET ESCAPE ON
DECLARE
XROW ROWID; --You cant directly give Row Id to the x_rowid parameter,
ln_rowid1 ROWID;
l_rowid VARCHAR2(100):= 0;
l_rec number:= 0;
CURSOR c
IS
select * -- LOOKUP_CODE, xgl.MEANING MEANING,DESCRIPTION,NEW_TAG,LOOKUP_TYPE
--select LOOKUP_CODE, SUBSTR(MEANING,1,79) MEANING,DESCRIPTION,NEW_TAG,LOOKUP_TYPE
from XXOGL_LOOKUP_VALUES_STG xgl
Where rownum <=3500
AND NVL(STATUS,'N') <> 'S'
--AND xgl.LOOKUP_CODE ='203011001'
AND NOT EXISTS
(Select 1 from FND_LOOKUP_VALUES flv
where flv.LOOKUP_TYPE ='OGL_IGAAP_PROFIT_LOSS_MAP'
AND flv.LOOKUP_CODE = xgl.LOOKUP_CODE ); --My Custom Query to have fetch in my custom table
--cursor c1 is
--select distinct LOOKUP_TYPE
--from xxcsb.xxcsb_gl_lookup_values_stg;
BEGIN
/*
for i in c1 -- For loop for Inserting lookup types
loop
fnd_lookup_types_pkg.insert_row (x_rowid => XROW,
x_lookup_type => i.lookup_type, --–cursor values
x_security_group_id => 0,
x_view_application_id => 101,
x_application_id => 101,
x_customization_level => 'U',
x_meaning => i.lookup_type,
x_description => i.lookup_type,
x_creation_date => SYSDATE,
x_created_by => 1133,
x_last_update_date => SYSDATE,
x_last_updated_by => 0,
x_last_update_login => 83810
);
DBMS_OUTPUT.put_line (XROW);
end loop; --–Loop ends here
commit;*/
DBMS_OUTPUT.put_line ('Loop');
for i in c -- For loop for Inserting lookup values
loop
l_rowid := NULL;
--DBMS_OUTPUT.put_line ('inLoop');
BEGIN
fnd_lookup_values_pkg.insert_row (x_rowid => l_rowid, --ln_rowid1,
x_lookup_type => I.LOOKUP_TYPE,
x_security_group_id => 0,
x_view_application_id => 3,
x_lookup_code => I.LOOKUP_CODE,
x_tag => NULL, --I.NEW_TAG,
x_attribute_category => I.LOOKUP_TYPE, --ATTRIBUTE_CATEGORY,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_enabled_flag => 'Y',
x_start_date_active => NULL, --TO_DATE ('01-JAN-1950','DD-MON-YYYY'),
x_end_date_active => NULL,
x_territory_code => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => I.BS_CODE,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_meaning => I.MEANING,
x_description => I.DESCRIPTION,
x_creation_date => SYSDATE,
x_created_by => 1131,
x_last_update_date => SYSDATE,
x_last_updated_by => 1131,
x_last_update_login => 80533
);
l_rec := l_rec+1;
--I := I+1;
DBMS_OUTPUT.put_line (l_rec ||'-'|| I.LOOKUP_CODE);
DBMS_OUTPUT.put_line (ln_rowid1);
UPdate xxcsb.XXJG_GL_CC_LOB_LOOKUP_STG SET STATUS ='S'
WHERE LOOKUP_CODE = I.LOOKUP_CODE;
Exception
when others then dbms_output.put_line('Exception Occured: '||SQLERRM);
END;
Commit;
end loop;
Exception
when others then dbms_output.put_line('Exception Occured: '||SQLERRM);
END;
/