Concurrent Program for UNIX scripts

Steps to run a shell script through Concurrent Program to load data into oracle table:

1) Create a DB table
eg:
CREATE TABLE DATA_LOAD(NAME VARCHAR2(300),AGE NUMBER,DATE_OF_BIRTH DATE);

2) Create a control file to do the upload operation
eg: data_load.ctl

LOAD DATA
INTO TABLE "DATA_LOAD"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
NAME,
AGE,
DATE_OF_BIRTH DATE "DD/MM/YYYY"
)

3) Create a data file
eg: data_load.dat
Tom|26|27-03-1983


Steps to Register Shell Script as a concurrent program:

Step #1
Place the .prog script under the bin directory for your
applications top directory.

For example, call the script XX_LOAD.prog and place it under
$CUSTOM_TOP/bin

Step #2
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called XX_LOAD.prog use this:

ln -s $FND_TOP/bin/fndcpesr XX_LOAD

This link should be named the same as your script without the
.prog extension.

Put the link for your script in the same directory where the
script is located.

Step #3
Register the concurrent program, using an execution method of
'Host'. Use the name of your script without the .prog extension
as the name of the executable.

For the example above:
Use XX_LOAD

Step #4
Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd
$2 = userid(apps)
$3 = username
$4 = request_id

Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.

Sample Shell Script to load data into oracle (XX_LOAD.prog)
-------------------------------------------------------------------------------------
#!/bin/sh

echo ' '
echo 'Parameters:'
echo '---------- '
echo ' File Path: '$5
echo ' Control File Name: '$6
echo ' Data File Name: '$7
echo ' '

sqlldr $1@$TWO_TASK control=$5/$6.ctl data=$5/$7.dat
log=$5/$6.log bad=$5/$6.bad discard=$5/$6.dsc

exit 0
-------------------------------------------------------------------------------------

OTL Layouts

Seeded timecard layouts are located in $HXC_TOP/patch/115/import/US.

Unix command to display seeded ldt files of Timecard Layouts.

ls $path | xargs grep -i "DISPLAY_LAYOUT_NAME = "

Output will look like below

hxczzhxclayt0000.ldt: DISPLAY_LAYOUT_NAME = "Payroll Timecard Layout"
hxczzhxclayt0015.ldt: DISPLAY_LAYOUT_NAME = "Projects and Payroll Timecard Layout"
hxczzhxclayt0030.ldt: DISPLAY_LAYOUT_NAME = "Exception Layout - Timecard Entry Page"
hxczzhxclayt0058.ldt: DISPLAY_LAYOUT_NAME = "Projects Fragmented Timecard View"
hxczzhxclayt0074.ldt: DISPLAY_LAYOUT_NAME = "US Projects and Payroll Review Layout"


FND Command to upload custom layout:
FNDLOAD [db username]/[db password]@[db instance] 0 Y UPLOAD $HXC_TOP/patch/115/import/hxclaytlayoutsld.lct XXCustom_Proj_Pay_Timecard_Layout.ldt

Most Commonly Used FNDLOAD Commands:

1 - Alerts
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct file_name.ldt ALR_ALERTS APPLICATION_SHORT_NAME="PER" ALERT_NAME="[Alert Name]"

2 - Lookups
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="PER" LOOKUP_TYPE="[lookup type code] "

3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/[pwd]@[DB] 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="PER" DESCRIPTIVE_FLEXFIELD_NAME="[descriptive flex field name]" P_CONTEXT_CODE="[Context code]"

4 - Key Flexfield Structures
FNDLOAD apps/[pwd]@[DB] 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="PER" ID_FLEX_CODE="[code]" P_STRUCTURE_CODE="[structure code] "

5 - Concurrent Programs
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="PER" CONCURRENT_PROGRAM_NAME="[concurrent program short name]"

6 - Value Sets
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="[value set name]"

7 - Profile Options
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="PER_BUSINESS_GROUP_ID"

8 - Request Groups
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="[Request group Name]" APPLICATION_SHORT_NAME="PER"

9 - Request Sets
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="PER" REQUEST_SET_NAME="[request set name]"

10 – Responsibilities
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="[Responsibility key]"

11 - Menus
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="[menu name]"


12 – Balances
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $PAY_TOP/patch/115/import/paydefbalance.lct file_name.ldt DEFINED_BALANCES BALANCE_NAME="[balance name]" BALANCE_DIMENSION=”[bal dimention]” LEGISLATION_CODE=US

13-Messages
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct file_name.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=" PER" MESSAGE_NAME="[Message Name]"

14- WEB ADI integrator
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneint.lct file_name.ldt BNE_INTEGRATORS INTEGRATOR_ASN= “PER” INTEGRATOR_CODE="[Integrator code]"

15-Web ADI contents
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct file_name.ldt BNE_CONTENTS CONTENT_ASN=” PER” CONTENT_CODE="[Content code]"

16-Web ADI mappings
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct file_name.ldt BNE_MAPPINGS MAPPING_ASN=” PER” MAPPING_CODE="[mapping code]"

17-Web ADI layout
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct file_name.ldt BNE_LAYOUTS LAYOUT_ASN=” PER” LAYOUT_CODE = "[layout code]"

18- Web ADI parameter list
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneparamlist.lct file_name.ldt BNE_PARAM_LISTS PARAM_LIST_ASN=” PER” PARAM_LIST_CODE="[parameterlist code]"

19-WEB ADI component
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecomp.lct file_name.ldt BNE_COMPONENTS COMPONENT_ASN=” PER” COMPONENT_CODE=”[Component code]”

20-Functions
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME="[Function code]"

21- Printer Styles
FNDLOAD apps/[pwd]@[DB] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”

22 - USER
FNDLOAD apps/apps@ktplr12 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct usr.ldt FND_USER USER_NAME='XXHR'

23 - XML Publisher Data Definition and Templates
FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct template.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=PER DATA_SOURCE_CODE=[DATADEFINITIONCODE]

The Above command downloads all the templates defined for this Particular Data Definition. For a particular template to be downloaded use

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XMLPData.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=[CUSTOM] DATA_SOURCE_CODE=[DATADEFSHORTCODE] TMPL_APP_SHORT_NAME=[CUSTOM] TEMPLATE_CODE= [TEMPLATECODE]
24 – AME Entities($AME_TOP /patch/115/import)

AME's Entity .lct File Name
Transaction Types amescvar.lct
Attributes amesmatt.lct
Conditions amesconk.lct
Action Type Configurations amesaatc.lct
Approver Groups amesappg.lct
Rules amesrulk.lct
Rule Action amesactu.lct
Attribute Usage amesmatr.lct
Approver Group Configurations amesaagc.lct

25- Work Flows

Download :
WFLOAD [apps/pwd] 0 Y DOWNLOAD [filepath] [[item type]]

Upload
WFLOAD [apps/pwd] 0 Y UPLOAD [filepath]

Upload Command

FNDLOAD apps/ [apps_pwd]@[db_sid] 0 Y UPLOAD [.lct file path] [.ldt file name]

Eg: FNDLOAD apps/ [apps_pwd]@[db_sid] 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct messages.ldt

Enable Entitlement balance in Employee Self Service Responsibility:

Navigation and implementation steps to achieve this:
Step 1: Create a Element Set: XXHR_LOA

Step 2: Add the elements which has Earned Leave Plan and Sick Leave Plan, In Classification Rules select Absence.

Step 3: Navigate to System Administrator Responsibility and choose system profile and query for
HR: Accrual Plan Element Set Displayed to User, in Site level value choose XXHR_LOA element set that you have created.

Step 4: Now you are done with the settings, to see the changes navigate to
Employee Self Service --> Leave Of Absence --> Entitlement Balances.

Oracle Time and Labor Errors/Solutions

Errors/Solutions:

 Error 1:  An error has been generated because worker information has not been found.

Solution: While debugging this error I found that my user (FND_USER) did not have any employee attached to this. Once I attached a employee for the user this error got resolved. There are other reasons for this error.

Please refer Oracle Metalink Doc ID : 338340.1 for further details on this error.

 

Error 2:  20001 ORA-20001: HR_6153_ALL_PROCEDURE_FAIL: N, PROCEDURE, pay_hr_otc_retrieval_interface.extract_data_from_attr_tbl, N, STEP, tbb mismatch

This error occurs when we tried to transfer OTL to BEE where the concurrent program completes successfully but BEE will not been created.

Solution: Apply patch to resolve this error.Refer Oracle Metalink Bug No. 4180521 and Bug No. 5850046

 Before applying the patch: In seeded package hxt_interface_utilities the code should look like this

 TYPE flex_value_table IS TABLE OF flex_value_rec

      INDEX BY BINARY_INTEGER;

 

After applying the patch: In seeded package hxt_interface_utilities the code should look like this

TYPE flex_value_table IS TABLE OF flex_value_rec

      INDEX BY VARCHAR2(255);

 After applying patch I tried to transfer Time card from OTL to BEE, it got transferred successfully.