How to set org context in Oracle apps R12

The SQL command to set the ORG_ID prior to running a script is:

SQL> exec mo_global.init('AR');
exec mo_global.set_policy_context('S','&org_id');
Enter the org_id when prompted.


The procedure - mo_global.set_policy_context has two parameters
p_access_mode & p_org_id


p_access_mode          Description
S     In case you want your current session to work against Single ORG_ID
M     In case you want your current session to work against multiple ORG_IDs


p_org_id: Only applicable if p_access_mode is passed value of "S"


If using Toad
Begin
mo_global.set_policy_context(‘S’, &org_id);
End;

kill a session which is locked in Oracle apps

This Article is used to explain how to kill a session which is locked

Check if the Package or table are locked using the below query

SELECT b.object_name,
       a.session_id,
       a.oracle_username,
       a.os_user_name,
       a.process,
       a.locked_mode
  FROM v$locked_object a,
       all_objects b
 WHERE a.object_id = b.object_id


b.    Get the serial number for the session based on the session id got from above qyery.


SELECT SID,
       serial#,
       ownerid,
       status,
       server,
       username,
       osuser,
       process,
       machine
FROM v$session
WHERE SID = ‘Session id from above query’

c.    Command to kill the session

ALTER SYSTEM KILL SESSION 'Sid from query, Serial# from Query 2'

FNDLOAD Download and Upload objects

FNDLOAD is used to migrate objects between instances.

DOWNLOAD

1 – Printer Styles
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 – Lookups
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND"
LOOKUP_TYPE="lookup name"

3 – Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ONE:SEG_ALL APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

4 – Key Flexfield Structures
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 – Concurrent Programs
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"

6 – Value Sets
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

7 – Value Sets with values
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

8 – Profile Options
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"

8 – Request Groups
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"

10 – Request Sets
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"

11 – Responsibilities
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

12 – Menus
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

13 – Forms Personalization
FNDLOAD apps/$APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME

14 - XML Data Definition and Associated Template Definition ***Doesn't download attachments.
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct file_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='FND' DATA_SOURCE_CODE='DATA SOURCE CODE' TMPL_APP_SHORT_NAME='FND' TEMPLATE_CODE='TEMPLATE_CODE'

15 - Workflow Download

WFLOAD apps/$APPS_PWD 0 Y DOWNLOAD file_name.ldt WF_INTERNAL_NAME



UPLOAD 

UPLOAD command is same for all except replacing the .lct and passing any extra parameters if you want to
pass 

FNDLOAD apps/$APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/corresponding.lct upload_file.ldt

Use the condition below to override the existing.

FNDLOAD apps/$APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/corresponding.lct upload_file.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE 

Bursting in BI publisher.

Nice article on bursting,it really helps to understand bursting concept.

http://garethroberts.blogspot.in/2008/03/bi-publisher-ebs-bursting-101.html

Changing Password for an Oracle User in R12 Oracle Apps using API

This article helps in changing the password for Oracle user using API FND_USER_PKG.CHANGEPASSWORD.

This API is tested in R12.1.3

DECLARE
   v_user_name          VARCHAR2 (100) := 'TEST_USER';
   v_new_password   VARCHAR2 (100) := :NEWPASSWORD;
   v_status                   BOOLEAN              := NULL;
BEGIN
   v_status := fnd_user_pkg.changepassword (v_user_name, v_new_password);

  COMMIT;
   DBMS_OUTPUT.put_line (   'Password is changed successfully for the user '
                         || v_user_name
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while setting new password to the user and the error is '
          || SQLERRM
         );
END;

Autonomous Transactions

The easiest way to understand autonomous transactions is to see them in action.

CREATE TABLE at_test (
      id               NUMBER            NOT NULL,
     description  VARCHAR2(50)  NOT NULL
   );

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.


Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.


As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.

ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. 

The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session.

Enabling/disabling concurrent program parameters dynamically--Nice Artice

 http://apps2fusion.com/at/45-as/241-enablingdisabling-concurrent-program-parameters