Soft Link or Symbolic Link

S Link ( soft link or symbolic link) is required to read a shell script (.prog file) through a concurrent program.

We create a concurrent program of executable type as HOST. The host file extension is .prog. We must have to create a soft link to access this host file.

Host File Location – It is available at bin folder on custom top. ($XXXXX_TOP/bin)

Soft Link Creation –  ln -s  $FND_TOP/bin/fndcpesr  sourcefile

 ln -s  $FND_TOP/bin/fndcpesr  $v_custom_top/bin/XXXXX_TEST_SOFTLINK

The above command will create the soft link for XXXXX_TEST_SOFTLINK.prog file. The new link will look as below – 

Known Issues – Sometime concurrent program could not read the soft link, though it is available.  Below is the error message can be found in log file

FND-CP-ESP: Child: exec:: No such file or directory

/oracle/…/…/xxxxx/12.0.0/bin/XXXXX_TEST_SOFTLINK

Program exited with status 1

Root Cause – This issue appear if there is any control M character in prog file, which is the source for soft link.

Check for control M Char -$ ./XXXXX_TEST_SOFTLINK.prog

if output is file contents => No control M character available

if output has control M Char => 

Resolutions – Remove the control M character and re create the soft link.

1)      Delete the existing soft link   >>$  rm    XXXXX_TEST_SOFTLINK  

2)      Remove the control M character >>$ cat    XXXXX_TEST_SOFTLINK.prog | col -b >  XXXXX_TEST_SOFTLINK1.prog

3)   Delete the existing prog file  >> rm XXXXX_TEST_SOFTLINK.prog
4)   Rename the new prog file to old  >>$  mv     XXXXX_TEST_SOFTLINK1.prog  XXXXX_TEST_SOFTLINK.prog

4)      Re create the soft link for XXXXX_TEST_SOFTLINK.prog

**PS – Do not copy prog file in  binary mode or zipped mode to server. It may include control M character to prog file.

FNDLOAD - Core Oracle Apps Objects Migration

FNDLOAD is very useful oracle provided utility to migrate core oracle apps objects (Flex fields,Concurrent program,responsibility,user,profiles,messages,request group.......forms personalization etc) from one instance to other.This post is all about FNDLOAD utility. 

Oracle provided all setup objects being stored in FND tables and shared  across the application.The name FNDLOAD - to load/download objects to/from FND Tables.

1) Upload objects - 

 FNDLOAD apps_user_name/apps_password 0 Y UPLOAD $Control_file $Object_name.ldt  

2) Download objects -

FNDLOAD apps_user_name/apps_password 0 Y DOWNLOAD $Control_file $Object_name.ldt [Parameters]

Parameters - 

$Object_TYPE [$Object_TYPE_Name="Your Object Name"] APPLICATION_SHORT_NAME=$'XXXXX' [$SubObject_TYPE] [$SubObject_TYPE_Name="Your sub Object Name"]

$Control_file => Control file is .lct file specific to object.Give the complete patch of file or download file at same location.most of the cases it is available at $FND_TOP/patch/import

$Object_name.ldt  => Downloaded file will be saved with this name

$Object_TYPE => downloaded object type (REQUEST_GROUP,PROGRAM etc)

Sub Object Type is required in case object is holding many objects and requirement is to download single sub object. Ex - Request Group contains many concurrent program entry, to download request group holding only your conc prog, use sub object type as program and give your program name. 

Below is the FNDLOAD command for different objects -

========================================================================= 
FNDLOAD example for few frequently used objects
========================================================================= 

XML Data Definition and Template

FNDLOAD apps/appspwd 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct DATADEFTEMPLATE.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXXXX' DATA_SOURCE_CODE='Your Data Definition Code'

Concurrent Program

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"

Request Set

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"

Request Group 

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"

Single Program from Request Group

FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct DATALOADERFILE.ldt REQUEST_GROUP REQUEST_GROUP_NAME="Your Request Group name" APPLICATION_SHORT_NAME="Your Request Group Application short name" REQUEST_GROUP_UNIT UNIT_NAME="Your concurrent program short name"

Single Message

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct YOURLDTFILENAME.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXXXX' MESSAGE_NAME='YOUR MESSAGE NAME'

Message

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct YOURLDTFILENAME.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXXXX'

Responsibility

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

Value set

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

Value set with values

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

 

Menu

FNDLOAD apps/appspwd  0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

Profiles

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="XXXXX"

KFF - Key Flex Fields

FNDLOAD apps/appspwd 0 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="XXXXX" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

DFF - Descriptive Flex Fields

FNDLOAD apps/appspwd 0 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="xxxxx" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

 Lookup

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="xxxxx" LOOKUP_TYPE="lookup name"

Printer Style 

FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

Defects Manager - Oracle Reports

1) ORA - 24324 : Service handle not initiated ==> SELECT

Resolution - Check the Database connection and re connect. This error is because of  Database disconnection.

Table walkthrough - HRMS

This post to make us familiar with basic HRMS tables. Since for each employee user creation is a normal process, I am mentioning about user details table too.

--User Details

select * from FND_USER where user_name = :name

-- Employee Associated with User

select * from PER_ALL_PEOPLE_F where person_id in (select employee_id from fnd_user where user_name = :name)

-- Assignment for Employee/User

select * from PER_ALL_ASSIGNMENTS_F where person_id in (select employee_id from fnd_user where user_name = :name)

-- Position for Employee/User

select a.name,a.* from PER_ALL_POSITIONS a where position_id in  (select position_id  from per_all_assignments_f where person_id in (select employee_id from fnd_user where user_name = :name))

-- Position Hirerachy for Employee/User

SELECT  ppse.subordinate_position_id,ppse.parent_position_id,pap.name "Subordinate Position"  FROM

PER_POS_STRUCTURE_ELEMENTS ppse, per_all_positions pap WHERE ppse.subordinate_position_id=pap.position_id

START WITH   ppse.subordinate_position_id in  (select position_id  from per_all_assignments_f where person_id in (select employee_id from fnd_user where user_name = :name))

CONNECT BY PRIOR  ppse.parent_position_id = ppse.subordinate_position_id;         

PS – Post intention is to be familiar with HRMS Tables name, below written script can be used by adding additional filter conditions to filter inactive records.

TRUNC(effective_start_date)   <= TRUNC(SYSDATE)

                        AND      TRUNC(effective_end_date)     > TRUNC(SYSDATE)

File Handling using PL/SQL - UTL_FILE

Intention of this post is to list out different exceptions being raised while using UTL_FILE.

First create DBA Directory

File Handling Program -

DECLARE

   p_file_path     VARCHAR2 (200);

   p_file_handle   UTL_FILE.FILE_TYPE;

   p_data_file     VARCHAR2 (200);

BEGIN

   p_data_file := 'SANDEEP_UTLFILE_TEST.txt'; 

   p_file_path := 'SANDEEP_DIR'; 

  
  DBMS_OUTPUT.PUT_LINE('File Path     >>' ||p_file_path );
  DBMS_OUTPUT.PUT_LINE('File Name   >>' ||p_data_file ); 

   p_file_handle := UTL_FILE.FOPEN (p_file_path,p_data_file,'W',32767);// OPEN the FILE

 /********************************************************************************** 

   p_file_path                    >> Location where data file being placed

   p_data_file                    >> Data file name

  ‘W’                               >> Operation Mode  
                                             'W' indicates write mode , 

                                              'R' indicates read mode , 
                                              'A' indicates Append mode

   32767                           >> Length of the string. 32767 is the max length.

 **********************************************************************************/

   UTL_FILE.PUT_LINE (p_file_handle, 'Sandeep is working on UTL File Demo'); 
   // WRITE into FILE
-- UTL_FILE.FFLUSH(p_file_handle); 
-- FFLUSH will write all buffer contents, useful if contents are larger in size.

  UTL_FILE.FCLOSE(p_file_handle);// CLOSE the FILE

EXCEPTION

          WHEN  UTL_FILE.invalid_mode  THEN

            DBMS_OUTPUT.PUT_LINE('20051, Invalid Option in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_path THEN

            DBMS_OUTPUT.PUT_LINE('20052, Invalid Path in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_filehandle THEN

            DBMS_OUTPUT.PUT_LINE('20053, Invalid Filehandle in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_operation THEN

            DBMS_OUTPUT.PUT_LINE('20054, Invalid Operation in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.read_error THEN

            DBMS_OUTPUT.PUT_LINE('20055, Read Error in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.write_error THEN

            DBMS_OUTPUT.PUT_LINE('20056, Write Error in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.internal_error THEN

            DBMS_OUTPUT.PUT_LINE('20057, Internal Error in UTL File le >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('Unexpected Error occured during report generation >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

END;

OUTPUT – File Generated 

               

Oracle PIM - Oracle Product Information Management

Introduction
Product Information Management is an oracle product family.Oracle had launched PLM (Product Life Management) in 11, which has been upgraded as PIM with few enhanced features like document management etc in release R12. 

1- Product Information Management is another product family of Oracle Application to store iproduct (items) details
2- Its similar to Item Master of SCM family with some additional enhancements.
3- It’s self service application
5- This is EGO_TOP

Basic terminology for product information management -

Item catalog Category - ICC
- To segregate items having similar characteristic,we classify those into categories. ICC is a name to different categories created under Item catalog to grouped similar items.
- Item catalog categories provide a logical way to classify items that have similar characteristics in Item Catalog
- It is suggested that every Item should be created under an ICC

Attribute Group - AG
- Oracle provides operational attributes to define items.
- Customer may need some more information about items and that can be achieved using Attribute group.
- Create custom attribute groups and attributes under this group to support custom need.
- Assign this AG to your ICC
- Associate this AG to any custom Page 

Custom Page
- PIM Supports user defined AG and to display these UDAs on UI, It provides functionality to create custom Pages.
- Pages can be created only under any ICC
- Associate your UDAs to custom Pages

Functions
- To avoid manual entry and use data with specific pattern, functions are available in PIM
- Customer can create own functions and return value based on his logic.
- PIM supports below fields to be used as function generated: 
       1) Item Number
       2) Item Description
       3) Actions
 

Do we kNOw ????

Through this post, we highlight few points being used in oracle & application very often but still unknown....

1) ORA-0000 : normal,successful completion - For every successful transaction oracle returns ORA-0000.

2)Concurrent Programs use auto commit - If invoking same procedure from database do commit at end, to avoid different behavior as concurrent program.  

3)Change profile using FA resp - sometime few profiles need cache clear to reflect the change value. Changing  profile value using Functional Administrator responsibility does not require any cache clear.

4)Dummy Oracle Apps Link - mentioned url will launch the oracle application, create your login and do  practice oracle apps at home
  http://vis1200.solutionbeacon.net/OA_HTML/AppsLogin

DBA Activities

below are some basic and usefull dba activities for oracle application environment -

1) To bounce the OA Core service
Solution -   cd   $ADMIN_SCRIPTS_HOME  or
                   cd   $INST_TOP/admin/scripts
stop and start script adoacorectl.sh to bounce and restore oa core. run status to check the oa core status-

2) Audit Sign On
Solution - There is a system level profile "Sign-On:Audit Level". Set this profile with following values -

                 1) None

                 2) User

                 3) Responsibility

                 4) Forms

After enabling this profile table "FND_LOGIN_RESP_FORMS" will capture the log in details

3) Hide password in unix

    echo "Write down Your Name"

    stty -echo

    read v_user_name

   stty echo

   echo " "use of stty will hide the user inputon screen.

Defects Manager - XML Publisher

Using troubleshoot page, I shall summarize the list of problems faced during xml report creation and it's resolution.

1) Leading zero issue - Invoice number can have leading zeros ("000INV1").Reporting any invoice number field in excel format can not preserve the leading zeros and display only "INV1".

Solutions -  use inbuilt function fo-bidi
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?INVOICE_NO_TAG?></fo:bidi-override>
or
Open the template and put some space before invoice number tag field.
==========================================================================

2) RTF formatting issue – Any changes to border and shading is reverting back if we close the rtf and re open it.

Solution – While changing border and shading apply it to TABLE level not COLUMN level. Changes at TABLE level will be preserved. refer below screenshots - 

                         







==========================================================================
3) OPP service failure - This issue occurs during concurrent program execution and most frequently while CP is generating any xml report and program will end in warning.
Solution -  Check with DBA and ask to up the OPP service if down or check your template. there will be few garbage character available in template, which is causing the issue.(For more details check - OPP Service post)




                                             

To be continued.......

Functional Administrator Responsibility - Advantages

Oracle provides so many features in core ( oracle forms) to ease the application use in real time. Fox example lookups, profiles, functions and menus.

Below lines explain how to do all these without opening oracle forms. Oracle provides Functional Administrator responsibility (self service application) to achieve most of the core features available in oracle forms. Add this responsibility to your user.

Click on Functional Administrator Responsibility –

Lookup – Core services >> Lookups : search for a lookup. Also create a new lookup type here

FND messages – Core Services>>Messages : Search existing messages or create a new message here

Profile Categories – Core Services>>Profile Categories : Search existing profile category or create a new profile category here

Profile  – Core Services>>Profiles : Search existing profile and change values or create a new profiles here

Function – Core Services>>Function : Search existing functions or create a new function here

Menus – Core Services>>Menus : Search existing menus or create a new menu here

Cache Clear – Core Services>>Caching Framework>>Global Configuration>>

Clear All Statistics or Clear Cache

By adding Functional administrator responsibility, user does not require to open old forms application any more :) while working on self service pages.