Oracle Apps Scripts
-- Query to generate numbers 0 to N with single SQL Statement
SELECT ROWNUM-1
FROM ( SELECT 1 FROM dual GROUP BY CUBE (1,1,1,1,1,1))
WHERE ROWNUM< :maxlimit+2
-- Query to find Number of Business days between two dates
SELECT COUNT (*)
FROM (SELECT ROWNUM - 1 rn
FROM all_objects
WHERE ROWNUM <= TO_DATE (:new_date, 'DD-MON-YY')
- TO_DATE (:old_date, 'DD-MON-YY')
+ 1)
WHERE TO_CHAR (TO_DATE (:old_date,'DD- MON-YY') + rn, 'DY')
NOT IN('SAT', 'SUN');
--UTL File Script
CREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number)
AS
v_line VARCHAR2(2000); -- Data line read from input file
v_file UTL_FILE.FILE_TYPE; -- Data file handle
v_dir VARCHAR2(250); -- Directory containing the data file
v_filename VARCHAR2(50); -- Data filename
v_1st_Comma number;
v_2nd_Comma number;
v_3rd_Comma number;
v_4th_Comma number;
v_5th_Comma number;
v_empno sample_emp.empno%type;
v_ename sample_emp.ename%type;
v_job sample_emp.job%type;
v_mgr sample_emp.mgr%type;
v_hiredate sample_emp.hiredate%type;
--v_sal sample_emp.sal%type;
BEGIN
v_dir := '/usr/tmp';
v_filename := 'sample.dat';
v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');
-- --------------------------------------------------------
-- Loop over the file, reading in each line. GET_LINE will
-- raise NO_DATA_FOUND when it is done, so we use that as
-- the exit condition for the loop.
-- --------------------------------------------------------
LOOP
BEGIN
UTL_FILE.GET_LINE(v_file, v_line);
EXCEPTION
WHEN no_data_found THEN
exit;
END;
-- ----------------------------------------------------------
-- Each field in the input record is delimited by commas. We
-- need to find the locations of the two commas in the line,
-- and use these locations to get the fields from v_line.
-- ----------------------------------------------------------
v_1st_Comma := INSTR(v_line, ',' ,1 , 1);
v_2nd_Comma := INSTR(v_line, ',' ,1 , 2);
v_3rd_Comma := INSTR(v_line, ',' ,1 , 3);
v_4th_Comma := INSTR(v_line, ',' ,1 , 4);
v_5th_Comma := INSTR(v_line, ',' ,1 , 5);
v_empno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1));
v_ename := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);
v_job := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1);
v_mgr := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1));
v_hiredate := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),'DD-MON-YYYY');
-- v_sal := to_number(SUBSTR(v_line, v_5th_comma+1),'99999');
DBMS_OUTPUT.PUT_LINE(v_empno ||' '|| v_ename || ' ' || v_job || ' ' || v_mgr ||' ' || v_hiredate);
-- ------------------------------------------
-- Insert the new record into the DEPT table.
-- ------------------------------------------
INSERT INTO sample_emp
VALUES (v_empno,v_ename,v_job,v_mgr,v_hiredate);
END LOOP;
UTL_FILE.FCLOSE(v_file);
COMMIT;
END;
-- Parent Child Query / Recurssive query
-- This query gives all function names under a menu considering submenus's also.
SELECT DISTINCT
fmep.menu_id,
DECODE(fmep.function_id,NULL, DECODE(fmec.function_id, NULL,DECODE(fmec1.function_id,NULL,
'No Func',fmec1.function_id ),
fmec.function_id),
fmep.function_id) funcID,
fff.user_function_name,
fff.description
FROM fnd_form_functions_tl fff,
fnd_menu_entries fmec1,
fnd_menu_entries fmec,
fnd_menu_entries fmep
WHERE fmep.menu_id = (SELECT menu_id
FROM fnd_menus
WHERE menu_name='INV_NAVIGATE' --Change the menu according to your requirement
AND ROWNUM=1)
AND fmep.sub_menu_id = fmec .menu_id(+)
AND fmec.sub_menu_id = fmec1 .menu_id (+)
AND fff.function_id = DECODE (fmep.function_id,NULL,DECODE(fmec.function_id, NULL,
DECODE(fmec1.function_id,NULL,-999,fmec1.function_id),
fmec.function_id),
fmep.function_id)
ORDER BY DECODE(fmep .function_id,NULL, DECODE(fmec.function_id, NULL,
DECODE(fmec1.function_id,NULL, 'No Func',fmec1.function_id ),
fmec.function_id),
fmep.function_id)
--Send an email though PL/SQL Procedure
CREATE OR REPLACE PROCEDURE erpschools_send_email
IS
mailhost varchar2 (30) := 'smtp.erpschools.com';
crlf varchar2(2) := CHR (13) || CHR (10);
v_email varchar2(16000);
message_1 LONG;
mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (mail_conn, mailhost);
utl_smtp.mail (mail_conn, 'sender@erpschools.com');
utl_smtp.rcpt (mail_conn, 'receiver@erpschools.com');
message_1 := 'From: Mr Sender < sender@erpschools.com >' || crlf ||
'Subject: Testing Hyperlink' || crlf ||
'To: Mrs Receiver <Receiver@erpschools.com>' || crlf ||
'<a href="http://www.erpschools.com" target="new" www.erpschools.com</a>' || crlf || crlf ;
utl_smtp.data(mail_conn, 'MIME-Version: 1.0' ||CHR(13)||CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||message_1);
utl_smtp.quit (mail_conn);
END;
--to find from which responsibility a concurrent program can be run
select distinct
a.application_id,
a.concurrent_program_id,
a.user_concurrent_program_name,
a.description,
b.request_group_id,
request_group_name,
e.responsibility_name
from fnd_concurrent_programs_tl a,
fnd_request_groups b,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
where a.concurrent_program_id = c.request_unit_id
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
-- and a.user_concurrent_program_name like 'XX%'
and a.concurrent_program_id = 45220
-- find from which responsibility user has ran the concurrent program
select distinct
user_concurrent_program_name,
responsibility_name,
user_name,
request_date,
argument_text,
request_id,
requested_by,
phase_code,
status_code,
a.concurrent_program_id,
a.responsibility_id,
logfile_name,
outfile_name
from fnd_concurrent_requests a,
fnd_concurrent_programs_tl b,
fnd_responsibility_tl c,
fnd_user d
where a.CONCURRENT_PROGRAM_ID = b.concurrent_program_id
and a.responsibility_id = c.responsibility_id
and a.requested_by = d.user_id
and user_name like 'PRUDHVIA'
-- Query to get customer site info using customer id
SELECT hcsua.site_use_id,hl.*,hps.*,hp.*
FROM apps.hz_cust_accounts hca,
apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_cust_site_uses_all hcsua
WHERE hca.cust_account_id = hcasa.cust_account_id
AND hca.party_id = hp.party_id
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcasa.party_site_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hca.cust_account_id = :cust_account_id /*i.e Sold_to_org_id from oe_order_headers_all*/
-- Initializations (user, responsibility, application, org)
BEGIN
FND_GLOBAL.APPS_INITIALIZE(:USER_ID,:RESPONSIBILITY_ID,:APPLICATION_ID);
END;
/fnd_client_info.set_org_context('ORG_ID');
dbms_application_info.SET_CLIENT_INFO('Org_id');