Global Temporary Tables in PL/SQL

Example of Global Temporary Tables in PL/SQL

declare
type mycur is ref cursor;
l_gtname varchar2(30) default 'Global_table_' || userenv('sessionid');
l_cursor mycur;
l_ename varchar2(50);
l_cnt number := 0;
begin
execute immediate 'create global temporary table ' ||
l_gtname || ' on commit delete rows
as
select * from scott.emp where 1=0 ';
execute immediate 'insert into ' || l_gtname ||
' select * from scott.emp where rownum = 1';
open l_cursor for
'select ename from ' || l_gtname || ' order by ename';
loop
l_cnt := l_cnt+1;
fetch l_cursor into l_ename;
exit when l_cnt = 10; --l_cursor%notfound;
dbms_output.put_line( l_cnt||'-'||l_ename );
end loop;
close l_cursor;
dbms_output.put_line(l_gtname);
execute immediate 'drop table ' || l_gtname;
end;

Query to find the available responsibilities for given operating_unit

SELECT fr.responsibility_name
FROM fnd_responsibility_vl fr,
FND_APPLICATION_TL fat,
fnd_profile_options_vl fpo,
FND_PROFILE_OPTION_VALUES fpov
WHERE fat.application_id = fr.application_id
AND fpov.level_value = fr.responsibility_id(+)
AND fpov. profile_option_value =:p_operating_unit_id
AND fpov.profile_option_id = fpo.profile_option_id
AND fpo.user_profile_option_name like 'MO: Operating Unit';

Creating Locations Using API Oracle Apps

Using API creating Location in Oracle Apps from backend

API :hr_location_api.create_location

declare
l_validate boolean := false
;l_effective_date date := to_date('01-JAN-2008','DD-MON-YYYY')
;l_location_code varchar2(20) := '1234'
;l_address_line_1 varchar2(50) := '#1234, XXXXX'
;l_address_line_2 varchar2(50) := 'Adarsh Nagar'
;l_address_line_3 varchar2(50) := 'Hyderabad'
;l_location_id number
;l_business_group_id number := null
;l_object_version_number number;
begin
hr_location_api.create_location
(p_validate => l_validate
,p_effective_date => l_effective_date
,p_location_code => l_location_code
,p_address_line_1 => l_address_line_1
,p_address_line_2 => l_address_line_2
,p_address_line_3 => l_address_line_3
,p_location_id => l_location_id
,p_business_group_id => l_business_group_id
,p_object_version_number => l_object_version_number
,p_description => 'Loaded by API'
,p_inactive_date => '01-JAN-2010'
,p_style =>'US_GLB'
);
dbms_output.put_line(l_location_id);
END;

How to convert from Rows to column using sql

Converting Rows to Column Using SQL

declare
TYPE c_refcur IS REF CURSOR;
l_str VARCHAR2(4000);
l_col VARCHAR2(4000);
c_ref c_refcur;
l_num number;
l_query varchar2(4000) :='select column_name from all_tab_columns where table_name like ''MTL_SYSTEM_ITEMS_B'' and rownum < 6 order by 1 desc' ;
l_dlmtr varchar2(10) := ',';
BEGIN

OPEN c_ref FOR l_query;
LOOP
FETCH c_ref INTO l_col;

l_str := l_str || l_dlmtr || l_col;

EXIT WHEN c_ref%NOTFOUND;

END LOOP;
CLOSE c_ref;
dbms_output.put_line( 'select '||SUBSTR(l_str,2)|| ' from MTL_SYSTEM_ITEMS_B');
EXCEPTION
WHEN OTHERS THEN
l_str := SQLERRM;
IF c_ref%ISOPEN THEN
CLOSE c_ref;
END IF;

END;
/

OUTPUT: select SECONDARY_UOM_CODE,EAM_ACT_SHUTDOWN_STATUS,EAM_ACT_NOTIFICATION_FLAG,DUAL_UOM_DEVIATION_HIGH,DUAL_UOM_CONTROL,DUAL_UOM_CONTROL from MTL_SYSTEM_ITEMS_B