CodeAutonetics

API to compile all bodies in Oracle Apps R12 API: Alter package SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER FUNCTION ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM    all_objects a WHERE   a.object_type = 'FUNCTION' AND     a.status      = 'INVALID' AND     a.owner       = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE 14 SET FEEDBACK ON SET VERIFY ON

API to compile all procedure specs in Oracle Apps R12 API: Alter package SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM    all_objects a WHERE   a.object_type = 'PACKAGE' AND     a.status      = 'INVALID' AND     a.owner       = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE 14 SET FEEDBACK ON SET VERIFY ON

API to compile all procedures in Oracle Apps R12 API: Alter package SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM    all_objects a WHERE   a.object_type = 'PROCEDURE' AND     a.status      = 'INVALID' AND     a.owner       = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE 14 SET FEEDBACK ON SET VERIFY ON

API to compile all triggers in Oracle Apps R12 API: Alter package SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM    all_objects a WHERE   a.object_type = 'TRIGGER' AND     a.status      = 'INVALID' AND     a.owner       = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE 14 SET FEEDBACK ON SET VERIFY ON

API to compile all views in Oracle Apps R12 API: Alter package SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM    all_objects a WHERE   a.object_type = 'VIEW' AND     a.status      = 'INVALID' AND     a.owner       = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE 14 SET FEEDBACK ON SET VERIFY ON

API to compile all bodies in Oracle Apps R12 API: Alter package SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE BODY;' FROM    all_objects a WHERE   a.object_type = 'PACKAGE BODY' AND     a.status      = 'INVALID' AND     a.owner       = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE 14 SET FEEDBACK ON SET VERIFY ON

API to call XML report in Oracle Apps R12 API: fnd_request.add_layout DECLARE l_request_id NUMBER; l_user_id NUMBER; l_resp_id NUMBER; l_resp_appl_id NUMBER; l_call_status BOOLEAN; BEGIN SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id INTO l_user_id, l_resp_id, l_resp_appl_id FROM apps.fnd_user fnd, apps.fnd_responsibility_tl fresp WHERE fnd.user_name = 'DEMOUSER' AND fresp.responsibility_name = 'BR Purchasing SME'; apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id); mo_global.set_policy_context ('S', 102); l_call_status := fnd_request.add_layout ('XXCUST', 'XXBRPRPOPRINT_XML', 'en', 'US', 'PDF'); l_request_id := fnd_request.submit_request ('XXCUST', 'XXBRPRPOPRINT_XML', NULL, NULL, FALSE, 'R', --P_report_type NULL, --P_agent_name_num '114259', --P_po_num_from '114259', --P_po_num_to NULL, --P_release_num_from NULL, --P_release_num_to NULL, ...

API to approve workflow notifications using a procedure in Oracle Apps R12 API: wf_notification Oracle Workflow communicates with users by sending notifications . Notifications contain messages that may request users to take some type of action and/or provide users with information. You define the notification activity and the notification message that the notification activity sends in the Workflow Builder. --To auto approve workflow notifications using a procedure. PROCEDURE approve_reject_proc (pAction IN VARCHAR2, --APPROVE/REJECT pComments IN VARCHAR2, -- User Comments -- Optional pNotification_id IN NUMBER, -- Notification ID pStatus OUT VARCHAR2, pMessage OUT VARCHAR2 ) IS l_user_name VARCHAR2(250); BEGIN BEGIN SELECT RECIPIENT_ROLE INTO l_user_name FROM WF_NOTIFICATIONS WHERE notification_id = pNotification_id; EXCEPTION WHEN OTHERS THEN l_user_name := NULL; END; wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction); wf_notification.Respond(pNotif...

API to attach concurrent program to a request group in Oracle Apps R12 API: FND_PROGRAM.add_to_group Request group and data group are used while defining a responsibility in Oracle apps . A request group is attached to a responsibility. It defines the concurrent programs you can execute using the responsibility to which this request group is attached. BEGIN   FND_PROGRAM.add_to_group('XXFINTSTCPDB',      -- Concurrent Program Short Name                 'XXFIN' ,         -- Application Short Name                  'All Reports',        -- Report Group Name                 'SQLAP');          -- Report Group Application   COMMIT; END; /

API to validate key flexfield values for CCCID in Oracle Apps R12 API: FND_FLEX_KEYVAL.VALIDATE_SEGS This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order). Various Operations that can be performed are: ‘FIND_COMBINATION’ – Combination must already exist. ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist. ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction. ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create. ‘DEFAULT_COMBINATION’ – Returns minimal default combination. ‘CHECK_SEGMENTS’ – Validates segments individually. If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.   SET serveroutput ON; DECLARE   l_segme...

API to create Accounting Key Flexfield Concatenated Segment description in Oracle Apps R12 API: GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION Update an existing Oracle User in R12 Oracle Apps using API -- FND_USER_PKG.UPDATEUSER We will try to update the email address for an existing user ‘DEMOUSER’ using the API.  There was no email_address populated to the user ‘DEMOUSER’ prior to running the API. DECLARE    v_user_name     VARCHAR2 (100)    := 'WIPUSER';    v_email_address  VARCHAR2 (100) := 'test@WIPRO.com'; BEGIN    fnd_user_pkg.updateuser                            (x_user_name                    => v_user_name,          ...

API to Reset Oracle Apps Password  in Oracle Apps R12 API: fnd_user_pkg.ChangePassword API accepts two parameters one is USER NAME and other is New Password.   declare v_user_name varchar2(30):=upper('&Enter_User_Name'); v_new_password varchar2(30):='&Enter_New_Password'; v_status boolean; begin  v_status:= fnd_user_pkg.ChangePassword (     username => v_user_name,     newpassword => v_new_password   );   if v_status =true then   dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);   commit;   else   DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));   rollback;   END if; end;

API to register table in Oracle Apps R12 API: ad_dd.register_table You register your custom application tables using a PL/SQL routine in the AD_DD package. Flexfields and Oracle Alert are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert. You can also use the AD_DD API to delete the registrations of tables and columns. You should delete the column registration first, then the table registration. To alter a registration you should first delete the registration, then re-register the table or column.  The AD_DD API does NOT check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need NOT register views. You should include call...

API to fetch combination_id for given set of key flexfield segment values in Oracle Apps R12    API: FND_FLEX_EXT.GET_COMBINATION_ID This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed. It creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist.  It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued. It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package. Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are out...

API to create Accounting Key Flexfield Concatenated Segment description in Oracle Apps R12 API: GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION A key flexfield is a field you can customize to enter multi-segment values such as part numbers, account numbers, and so on. A descriptive flexfield is a field you customize to enter additional information for which your Oracle Applications product has not already provided a field. SELECT gcc.concatenated_segments,        gl_flexfields_pkg.get_concat_description( gcc.chart_of_accounts_id,                                                  gcc.code_combination_id                    ...

API to delete Responsibility to user FND_RESPONSIBILITITY A responsibility is a level of authority in Oracle Applications that lets users access only those Oracle Applications functions and data appropriate to their roles in an organization. Each responsibility allows access to: A specific application or applications , such as Oracle General Ledger or Oracle Planning.   Deleting a Responsibility to an Oracle User in R12 Oracle Apps using API -- FND_USER_PKG.DELRESP to Delete responsibility ‘System Administrator’ for the user 'USERNAME' using the API. DECLARE    v_user_name            VARCHAR2 (100) := 'USERNAME';    v_responsibility_name  VARCHAR2 (100) := 'System Administrator';    v_application_name     VARCHAR2 (100) := NULL;    v_responsibility_key   VARCHAR2 (100) := NULL;    v_security_group     ...

API to delete JTF_NOTES JTF_NOTES_B JTF_NOTES_B stores all non–translated information about Notes. Notes may be created for any object mapped for object usage in JTF_OBJECTS; the JTF_OBJECT that the note is linked to is stored as the source_object_code and source_object_id. The actual text of the note, which is stored in the JTF_NOTES_TL, is split into two parts under columns NOTES and NOTE_DETAILS. Typically, the NOTES should hold the short form or the first part of the note. NOTE_DETAILS for added details, or whenever the note is longer than the 2000 character limit for the NOTES column. The NOTE_STATUS flags the note as being personal (P), Internal (I), or Publish(E).   set serveroutput on DECLARE   CURSOR c_notes IS     SELECT jtf_note_id       FROM jtf.jtf_notes_b     where jtf_note_id = 94492027     ;   l_ret_status VARCHAR2(3);   l_msg_data   VARCHAR2(...

API to create JTF_NOTES JTF_NOTES_B JTF_NOTES_B stores all non–translated information about Notes. Notes may be created for any object mapped for object usage in JTF_OBJECTS; the JTF_OBJECT that the note is linked to is stored as the source_object_code and source_object_id. The actual text of the note, which is stored in the JTF_NOTES_TL, is split into two parts under columns NOTES and NOTE_DETAILS. Typically, the NOTES should hold the short form or the first part of the note. NOTE_DETAILS for added details, or whenever the note is longer than the 2000 character limit for the NOTES column. The NOTE_STATUS flags the note as being personal (P), Internal (I), or Publish(E).   DECLARE    x_return_status   varchar2 (1);    x_msg_count       number;    x_msg_data        varchar2 (200);    x_jtf_note_id     number;    x_msg_...

To Check whether a given table is used in any of the oracle form Using unix command : Login to you host using Putty/ SSH tectia, after connection cd < your fmb location, this is AU or Custom AU Path > grep -Hrn MTL_SYSTEM_ITEMS_B  -H causes the filename to be printed (implied when multiple files are searched) -r does a recursive search -n causes the line number to be printed mtl_system_items_b table name to search path/to/files can be . to search in the current directory Further options that I find very useful: -I ignore binary files (complement: -a treat all files as text) -F treat search term as a literal, not a regular expression -i do a case-insensitive search --color=always to force colors even when piping through less . To make less support colors, you need to use the -r option: grep -Hrn search . | less -r --exclude-dir=dir useful for excluding directories like .svn and .git . Result [yourUser@host US]$ cd /apps/db...