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...