Upgrading the EBS system from INDIA LOCALIZATION to GST

Apply the GST patch as per below order,

GST : Path to GST Implementation - Patch Details (Doc ID 2268465.1) 
India GST - Latest Patches Released (RUP 11 onwards) (Doc ID 2342288.1) 

<Read pre-requisites or instructions from DOC ID 2268465.1 >

1) India GST functional patch

General Ledger Patch : Patch 19283503:R12.GL.C
Revised Co-Existence Patch : Patch 26153906:R12.JAI.C

2) TDS migration patch (This patch will migrate the TDS setup to the new system) -

Note: TDS Migration Steps Document(OFI-GST- TDS Migration.pdf)

Patch 26194366:R12.JAI.C 
Patch 26273448:R12.JAI.C 

IMPORTANT : These Patches can be applied with the Co -Existence patch

Note: All the OPEN transactions (Invoice / Purchase Order / Receipts) need to close before proceeding to the below steps.

3) GST Enablement  patch

GST Enablement patch Patch 26003443:R12.JAI.C 

4) TDS Enablement patch + Concurrent Program for Migrating the TDS Transactions

TDS Enablement patch Patch 26171921:R12.JAI.C  

5) RUP Patches

Apply RUP 11 patches
Step 1. Apply Patch 27421320:R12.JAI.C
Step 2. Apply Patch 27435589:R12.JAI.C (Inclusion Document)
Note : This patch will be pre-requisite for all patches which will be released in future.
Step 3. Apply Patch 22698083:R12.AP.C

Please read pre-requisites or instructions from DOC ID 2342288.1 >

Apply SEP Consolidation patches
Apply Patch 28656394:R12.JAI.C 
pre-requisites :
Patch 27421320:R12.JAI.C 
Patch 22698083:R12.AP.C 
Patch 27538659:R12.BOM.D

Patching cycle needs to apply in the correct listed order as a separate ADOP cycle. Don't consolidate all the patches in a single cycle because certain Techno-Functional activity needs to carried out in-between the patching implementation


At end of patch completion the Invoice workbench form will prevent the invoice creation due to package AP_INVOICES_UTILITY_PKG invalidation. Try the below steps which we have already posted to overcome this.

https://oracleapplications90.blogspot.com/2019/06/apinvoicesutilitypkg-package-body-is.html

iProcurement : Unable To Lock The Record Cause: The Record Is Being Modified By Another User

Reason for this issue is system saved requisitions are not purged.

A system saved requisition when a user starts creating a requisition and then aborts the process; these records can cause the locking error.

SOLUTION

To implement the solution, execute the request : Purge System Saved Requisition

1. Go into the responsibility: Purchasing

2. Navigate to Reports > Run

3. Search for the Purge System Saved Requisition request

-- Users can schedule the request from this screen to run on an interval.

-- It is suggested to run this on a daily basis to ensure all system saved requisitions are purged.

4. Retest the issue.

5. Migrate the solution as appropriate to other environments.

Unable To Lock The Record Cause: The Record Is Being Modified By Another User (Doc ID 1636766.1)

AP_INVOICES_UTILITY_PKG Package Body is Invalid Status Due to PLS-00302: component 'AP_CHECK_GDF_VALID' must be declared

This is part of GST Patch implementation work.

GST : Path to GST Implementation - Patch Details (Doc ID 2268465.1)


During the patching cycle the package AP_INVOICES_UTILITY_PKG will get INVALID due to Wrong Number of Argument change in AP_CHECK_GDF_VALID section.

To fix this issue try apply the patch as per Oracle Suggestion

For Release 12.2.x:   

fv    patch/115/sql    FVGTSUTB.pls    120.1.12020000.48
ap    patch/115/sql    apinvutb.pls    120.79.12020000.13

Please Refer Below Doc from Oracle,

R12 AP: AP_INVOICES_UTILITY_PKG Package Body is Invalid Due to PLS-00302: component 'AP_CHECK_GDF_VALID' must be declared (Doc ID 1934300.1)

Hope it Helps !!

Not Able To Do Third Party Registration Setup Of Supplier At Null Site - TDS Setup

When attempting to third party registration for TDS Regime 
the following error occurs.

ERROR
-----------------------
APP-JA-460353:You must create a records for null site first

Solution : 

To implement the solution, please execute the following steps:

1. Go into the responsibility: Oracle Financial for India 

2. Navigate to Party Registration > Define Third Party Registration 

3. To Create a null site level setup of Supplier , Do not pass any value for Operating unit 

4. In Find Third Party Registration Form ,Pass only Party type as "supplier " and Click on New 

5. Not Pass the all details to do the supplier setup at null site level 

6. Migrate the solution as appropriate to other environments.

********************************************************************************************

ORA-20002: [WF_NO_ROLE] NAME=FND_RESP|ICX|XXICX_SSP_IPROC|82 ORIG_SYSTEM=FND_RESP ORIG_SYSTEM_ID=51285 has been detected in FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT

Issue: Trying to assign a responsibility to a user using "fnd_user_pkg.addresp" or from "Users" Screen in "System Administrator" responsibility and you receive the error below:

"ORA-20002: [WF_NO_ROLE] NAME=FND_RESP|ICX|XXICX_SSP_IPROC|82 ORIG_SYSTEM=FND_RESP ORIG_SYSTEM_ID=51285 has been detected in FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT"


As its shown; this is the custom exception raised by Oracle API.

Let see how to get it resolve:

1) Submit Program "Workflow Directory Services User/Role Validation" with parameters like

"100000, USERNAME, , Y, Y, N, "

• Parameters
  • Batch Size: Number of records that will be processed per commit cycle
  • Fix dangling user/roles: Removes any user/roles where the user and/or the role does not exist in WF_LOCAL_ROLES
  • Add missing user/role assignments: Adds any user/roles that are present in WF_LOCAL_USER_ROLES but missing in WF_USER_ROLE_ASSIGNMENTS
  • Update WHO columns in WF tables: Controls the updating of who columns when corrupt records are corrected

2) Set the Enable Security Groups profile option to Yes at application level for the application relevant to the responsibility

3) Submit Program "CREATE FND_RESP WF ROLES"

Hope it helps !!

MOAC initialization in EBS

MOAC - Multi-Org Access Control

This feature in R12 version enables users to access secured data in multiple operating units from a single responsibility. End-Users can access data within several operating units based on a Security Profile attached to the responsibility. 

For Single Operating Unit:

begin
mo_global.set_policy_context('S',12345);
end;

MO_GLOBAL.SET_POLICY_CONTEXT ('S', 12345);

S => SINGLE
12345 => Org ID

For Application (More than one Org):

exec apps.mo_global.init('PO');

PO => Purchasing Application

APPS initialization:

For Package Level  Usage:

User ID :  FND_GLOBAL.USER_ID;
Responsibility ID : FND_GLOBAL.RESP_ID;
Responsibility Application ID:  FND_GLOBAL.RESP_APPL_ID;


     FND_GLOBAL.APPS_INITIALIZE( lv_user_id,
       lv_resp_id,
                                                               lv_resp_app_id);

COMMIT;

For Anonymous Package Usage:

select user_id user_id from fnd_user where user_name = '<User_Name>';


select responsibility_id resp_id, application_id resp_app_id from fnd_responsibility_tl where responsibility_name = '<Responsibility_Name>';

     FND_GLOBAL.APPS_INITIALIZE( user_id,
                                                        resp_id,
                                                               resp_app_id);

R12.2.X ADOP Patching Cycle

ADOP / Online Patching: 
****************************

Main Feature => ***  Minimal definable Downtime ***

FS1 – (RUN_EDITION) Production file system that is used by online users when system is being patched.

FS2 – (PATCH_EDITION) Exact copy of production(fs1) used by the patching tools. Changes do not affect the running application(RUN_EDITION).

When patching is completed and we do CUTOVER, then patching tool swaps the FS1 and FS2 file systems. It means, FS2  file system will become FS1 , which will be access by the users.

FS-NE (Non-Edition) – This is a non-edition file system which stores data that is stored in a file system like log file, report file and patch top files.

There are below 5 Phases of ADOP(online patching)

1) PREPARE
2) APPLY
3) FINALIZE
4) CUTOVER
5) CLEANUP

$ adop phase=prepare

check the status - $ adop -status

$adop phase=apply patches=<Patch Number>

$adop phase=finalize

$adop phase=cutover

$adop phase=cleanup

If there are some reason either the prepare or apply phase failed or any problems then you can abort this patching cycle.

$adop phase=abort

After running abort, you must always run a full cleanup as:

$adop phase=cleanup cleanup_mode=full

Oracle OAF Profile Option Overview

Oracle OAF has some important profile options which is useful in extension or personalization.

1. FND_Diagnostics

Setting this profile option to YES, will add anew link "Diagnostics" at top right on page, that allow developer to trace logs.

To add log when coding use the following code in Controller or Application Module
In Controller write this code:-
pageContext.writeDiagnostics(this, "Phrase will be added to logs", 1);

In Application Module write this code
getOADBTransaction().writeDiagnostics(this, "Phrase will be added to logs", 1);

2. Personalize Self-Service Defn

 Set this profile to Yes to allow personalization.  

3. FND: Personalization Region Link Enabled :

Set this profile to  Yes  show "Personalize  Region" links above each  region in a page. 

4. Disable Self-Service Personalization

Yes will disable all personalization at any level. 

5. FND: Personalization Document Root Path

Set this profile option to a directory at application server machine which will contain import/export personalization files

Triggering Mail from Unix Box

For Single Person Mail Box:

echo "Hi, This is Test Mail" | mailx -s "Test Mail For SMTP" someone@company.com

For group of Mails (#Group-Member-Mails)

echo "Hi, This is Test Mail" | mailx -s "Test Mail For SMTP" #somegroup@company.com

Instead of mailx, command mail also will work for initiating mails

mail -S smtp=<relay server name> -s "Space Alert For $(hostname): Disk space $usep%" someone@company.com

Note: Relay Sever Name can be derived under the System Profile : IEX: SMTP Host

How to create Soft link for file in Unix

Syntax : ln -s <file to be softlinked> <Name of file to be>

Example : ln -s r12data.sh r12data_soft.sh

How to refresh or clear page in OAF

//Clear button code
       if(pageContext.getParameter("Clear")!=null)
         {
            pageContext.forwardImmediatelyToCurrentPage(null, false, null); 
         
         }


Note : "Clear" refers to the Button ID in page.

Find | DBC File Path in Oracle Applications.

Navigation :

  Click on About This Page in homepage ==> Under Tabs, click on Java System Properties ==> Search for keyword DBCFILE.

RTF Tag to Print String in Right Aligned Format

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?format-number:<column name>;'###,###,##0.00'?></fo:bidi-override>


Example :

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?TAG_NAME?></fo:bidi-override>

Script to Add Employee as Buyer under Purchasing

DECLARE
   l_agent_id          NUMBER;
   l_employee_number   VARCHAR2 (20) := 120273;
   l_buyer_rowid       VARCHAR2 (100);
BEGIN
   BEGIN
      SELECT person_id
        INTO l_agent_id
        FROM apps.per_all_people_f f
       WHERE employee_number = l_employee_number;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (   'Unable to find Agent ID for '
                               || l_employee_number
                               || SQLERRM
                              );
   END;

   apps.po_agents_pkg.insert_row (x_rowid                    => l_buyer_rowid,
                                  x_agent_id                 => l_agent_id,
                                  x_last_update_date         => SYSDATE,
                                  x_last_updated_by          => 0,
                                  x_last_update_login        => 0,
                                  x_creation_date            => SYSDATE,
                                  x_created_by               => 0,
                                  x_location_id              => NULL,
                                  x_category_id              => NULL,
                                  x_authorization_limit      => NULL,
                                  x_start_date_active        => SYSDATE,
                                  x_end_date_active          => NULL,
                                  x_attribute_category       => NULL,
                                  x_attribute1               => NULL,
                                  x_attribute2               => NULL,
                                  x_attribute3               => NULL,
                                  x_attribute4               => NULL,
                                  x_attribute5               => NULL,
                                  x_attribute6               => NULL,
                                  x_attribute7               => NULL,
                                  x_attribute8               => NULL,
                                  x_attribute9               => NULL,
                                  x_attribute10              => NULL,
                                  x_attribute11              => NULL,
                                  x_attribute12              => NULL,
                                  x_attribute13              => NULL,
                                  x_attribute14              => NULL,
                                  x_attribute15              => NULL
                                 );
   COMMIT;
   DBMS_OUTPUT.put_line ('Setup as a buyer ' || l_employee_number);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error in Setting up as Buyer ' || SQLERRM);
END;

Script To Reset User Application Password

DECLARE
      L_Ret_Val Boolean;
      L_User_Name   Varchar2(50) := 'IMSPROC';
      l_new_pwd     varchar2(20) := 'welcome123';
BEGIN
      l_ret_val :=
           fnd_user_pkg.changepassword(username=> l_user_name
                                      ,Newpassword => L_New_Pwd);
     --DBMS_OUTPUT.PUT_LINE(l_ret_val);                               
     IF l_ret_val
     THEN
           DBMS_OUTPUT.PUT_LINE('The password is successfully reset to '||  l_new_pwd);
           COMMIT;
     Else
           DBMS_OUTPUT.PUT_LINE('The password reset has failed ');
     End If;
end;

Script to update Workflow Mail Address as NULL in TEST Instances to Avoid Triggering Mail to users.

update fnd_user set email_address=' ';

update per_people_f set email_address=' ';

update hz_parties set email_address = null where email_address is not null;

update hz_contact_points set email_address = null where email_address is not null;

update ap_supplier_sites_all   set email_address = null, remittance_email=null;

update ap_supplier_contacts set email_address = null;

update iby_ext_bank_accounts set contact_email = null;

update iby_external_payers_all set debit_advice_email = null;

update iby_external_payees_all set remit_advice_email = null;

update po_headers_all      set email_address = null;

update pon_bid_headers set email  = null;

update pon_bidding_parties set additional_contact_email  = null;

update per_all_people_f set email_address=null, attribute3=trim(attribute3), attribute4=trim(attribute4), attribute5=trim(attribute5), attribute6=trim(attribute6), attribute7=trim(attribute7), attribute8=trim(attribute8), attribute9=trim(attribute9), attribute10=trim(attribute10), attribute11=trim(attribute11), attribute12=trim(attribute12), attribute13=trim(attribute13),attribute14=trim(attribute14),attribute15=trim(attribute15);

update WF_LOCAL_USERS set email_address = null where email_address is not null ;

update WF_LOCAL_ROLES set email_address = null where email_address is not null;

update wf_notifications set mail_status='SENT' where mail_status in ('MAIL', 'ERROR', 'FAILED'); (under apps schema);

update wf_notifications set status='CLOSED', mail_status='SENT', end_date=sysdate where status='OPEN' and MESSAGE_TYPE='WFERROR';

update fnd_user set user_guid=null';

OAF Deployment | Oracle R12.1.X

Below are the steps to be carried out for OAF Deployment in Oracle R12.1.X

Step 1 : LOG INTO APP TIER ENVIRONMENT as Superuser

Step 2 : GOTO $JAVA_TOP                               ==> cd $JAVA_TOP

Step 3 : GOTO FOLLOWING CUSTOM PATH    ==> cd XX/oracle/apps/../..

Step 4 : MOVE ALL OBJECTS TO CORREPONDING DIR.

1.      Directory server - JAVA & XML Files of AM,EO,VO .

2.      Directory webui  - JAVA & XML Files of CO,PG.

Step 5 : COMPILE ALL JAVA FILES IN RESPECTIVE DIRECTORY.

1.      javac file_name.java (To compile one file)

2.      javac *.java (To compile all files) 

Step 6 : IMPORT XML PAGE INTO DATABASE 

                         java oracle.jrad.tools.xml.importer.XMLImporter <Page_name>.xml -username apps -password <pwd> -dbconnection "(DESCRIPTION =(ADDRESS=(PROTOCOL = tcp)(HOST =<DB Host Address>)(PORT =<Host Port no>))(CONNECT_DATA =(SID =<SID name>)))" -rootdir $JAVA_TOP

                       

Step 7 : BOUNCE APACHE/OACORE SERVER.

1.      $INST_TOP/admin/scripts/adoacorectl.sh stop

2.     $INST_TOP/admin/scripts/adoacorectl.sh start

Note : Execute Below Command To change user as Superuser

Sudo su – applmgr or pbrun -u applmgr runshell

Workflow Notification Mail is in Unreadable Format in Outlook

All the workflow notification mails triggered from Oracle EBS are in unreadable format for user who view mails in outlook or mail box.

Issue : Users are not able to take any actions since mails are in unreadable format as shown below.

Solution : Execute the following steps:

1. Update System Profile option FND: Enable Rich Table Interactions
• Responsibility: System Administrator
• Navigation: Profiles > System
• Query and set the profile option FND: Enable Rich Table Interactions
• True at site level
• False at sysadmin user level (sysadmin is the default mailer user)
2. Clear the cache Using Functional Administrator.