Oracle Apps Essentials

What is PRAGMA? In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime. The 5 types of Pragma directives available in Oracle are listed below: PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction. PRAGMA SERIALLY_REUSABLE: This directive tels Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory. PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required. PRAGMA EXCEPTION_INIT: ...

Why personalization? Oracle Supports personalization unlike customization Personalization are stored in tables rather than files Will not have a bigger impact when you upgrade or apply patches to the environment Can be moved easily through FNDLOAD from one instance to other Can be restricted at site/responsibility/user level Easy to disable/enable with click of a button. Personalization will store who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability. Can be applied to new responsibilities/users easily. Can be restricted to function or form. What can be done through personalization? Zoom from one form to another Pass data from one form to another through global variables Change LOV values dynamically Enable/Disable/Hide fields dynamically Display user friendly messages when required Launch URL directly from oracle form Execute PL/SQL programs through FORM_DDL package Call custom libraries dyn...

There are five ways to recompile invalid objects in schema. DBMS_DDL DBMS_UTILITY UTL_RECOMP UTLRP.SQL Manually Recompile > Best Approach 1. DBMS_DDL This procedure is equivalent to the following SQL statement: ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY] Syntax Exec dbms_ddl.alter_compile ( type , schema, name); Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER. Schema : Database Username Name : Objects name Example SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST'); PL/SQL procedure successfully completed. 2. DBMS_UTILITY This procedure compiles all procedures, functions, packages, and triggers in the specified schema. Syntax Exec dbms_utility.compile_schema ( schema,compile all) Schema : Database Username Compile All : Object type ( procedure, function, packages,trigger) Example SQL> exec dbms_utility.compile_schema('SCOTT'); PL/SQL procedure successfully co...

How to use Attachment APIs 1. Create Directory - Optional CREATE DIRECTORY test as '/home/oracle/' Grant all on directory test to public   2. Upload file to the location / Identify the location of the Document to be attached   3. DECLARE l_rowid ROWID ; l_attached_document_id NUMBER ; l_document_id NUMBER ; l_media_id NUMBER ; l_category_id NUMBER ; l_pk1_value fnd_attached_documents.pk1_value% TYPE := 747085 ; ----<Primary Key information that uniquely identifies the product (such as the product_ID)>; l_description fnd_documents_tl.description% TYPE := 'Test Attachment' ; l_filename VARCHAR2 ( 240 ) := 'FNDWRR.pdf' ; --'cost allocation code.txt'; --'<File Name>'; l_file_path varchar2 ( 240 ) := 'test' ; ---'SALE_INVOICE_PATH'; --Server Directory Path for upload files l_seq_num NUMBER ; l_blob_data BLOB ; l_blob BLOB ; l_bfile BFILE ; l_byte NUMBER ; l_fnd_user_id NUMBER ; l_short_d...

Command Line Export / Import Instructions java -jar eulbuilder.jar -connect apps/password@$Database -import <FILE_NAME>.eex -keep_format_properties -preserve_workbook_owner -auto_refresh -log <LOG_FILENAME> java -jar eulbuilder.jar -identifier -export <FILE_NAME>.eex -connect apps/password@$Database -audit_info -created_by ORACLE_APPS -set_updated_by ORACLE_APPS -cmdfile export.txt -log <LOG_FILENAME>

Script uses API to assign WF User Roles for Worklist access set serveroutput on  declare  l_userTable WF_DIRECTORY.UserTable;  l_roleName varchar2(240):='<EBS USERNAME>';  l_roleDispName varchar2(240):='<EBS USERNAME>';  l_userName varchar2(240):='<EBS USERNAME>';  l_userDispName varchar2(240):='<EBS USERNAME>';  begin -- wf_directory.CreateAdHocUser(name=>l_userName, -- display_name=>l_userDispName,language=>'AMERICAN', territory=>'UNITED KINGDOM'); --dbms_output.put_line('After User '|| l_userName);  l_userTable(0) := l_userName;  wf_directory.CreateAdHocRole2(role_name=>l_roleName,  role_display_name=>l_roleDispName,  role_users=>l_userTable,language=>'AMERICAN', territory=>'UNITED KINGDOM');  dbms_output.put_line('After Role '|| l_roleName);  commit;  end; Now run the following sqls to verify the wfds tables select * from wf_...

How to find the External Table referred in the Query? SELECT doc_name, ba_name,   folder_name,   user_name,   ext_table,   EUL FROM   (SELECT d.DOC_name,     b.ba_name ba_name,     o.obj_name folder_name,     o.sobj_ext_table ext_table,     e.EXP_name item_name,     UPPER (USR1.user_name) user_name,     'EUL4_US' EUL   FROM eul4_us.eul5_objs o   LEFT JOIN eul4_us.eul5_expressions e   ON o.obj_id = e.it_obj_id   LEFT JOIN eul4_us.eul5_elem_xrefs x   ON e.exp_id = x.ex_to_id   LEFT JOIN eul4_us.eul5_documents d   ON d.doc_id = x.ex_from_id   LEFT JOIN eul4_us.EUL5_ba_obj_links l   ON l.bol_obj_id = o.obj_id   LEFT JOIN eul4_us.EUL5_bas b   ON b.ba_id = l.bol_ba_id   LEFT JOIN eul4_us.eul5_eul_users EU1   ON d.DOC_EU_ID + 0 = EU1.eu_id   LEFT JOIN applsys.fnd_user USR1   ...