Oracle WFR scans comma as decimal in LineItem fields

This can not be resolved by using field properties ( Exclude Char) feature since Line Item is of Table type and there is no such properties.

Custom Sub Routine would be way forward, following is a sample -

Private Sub LineItems_ValidateTable(pTable As SCBCdrPROJLib.ISCBCdrTable, pWorkdoc As SCBCdrPROJLib.ISCBCdrWorkdoc, pValid As Boolean)
'======================================
'By Sandeep
'======================================
Dim i As Integer

For i = 0 To pWorkdoc.Fields("LineItems").Table(0).RowCount -1

'Replacing some known characters identified during debug
pWorkdoc.Fields("LineItems").Table(0).CellText("Unit Price", i)=Trim(Replace(CStr(pWorkdoc.Fields("LineItems").Table(0).CellText("Unit Price", i)),".",""))

pWorkdoc.Fields("LineItems").Table(0).CellText("Total", i)= Trim(Replace(CStr(pWorkdoc.Fields("LineItems").Table(0).CellText("Total", i)),".",""))

pWorkdoc.Fields("LineItems").Table(0).CellText("Total", i)= Trim(Replace(CStr(pWorkdoc.Fields("LineItems").Table(0).CellText("Total", i)),".",""))

Next

End Sub


Above is a sample code to replace any char and assign the replaced value back to line Items.
Based on business required we can change the values and assign the calculated values agaain.

If Total field has comma replaced by decimal,  form a logic to check length of post decimal string and decide if decimal is actual decimal or comma. Accordingly replace comma with "" and assign back. 

Oracle WFR : Basics

WFR - Oracle Web Center Form Recognition 

Open Project file (.sdp) in Form Recognition.

Definition Mode

• This will be used to define attributes properties 
• Select user exit and press F12 ( right click >> Show Scripts) to show available userExits 
• Select invoice Oracle F12 ( right click >> Show Scripts) to show sub routines. Select Objects and available related  procedure
• Make the required changes and Save project 
• After successful extraction ( from Run mode) user can  switch to Definition mode > invoice > fields to see each attribute extracted values 

Run Mode 

• Select a File 
• Select Run mode
• Click Extract next record 
• Extraction will be completed 
• Extraction results are displayed 
• Successfully extracted and passed validation will be ticked 
• Crossed attributes are failed some validations  

Verifier Mode ( Text)  

• This will be used to show extraction results for each attributes 
• Move over the mouse on red colored fields and it will show the failed validation 

Verifier Mode ( Design)  can be used to design the layout. 

** UserExits and SubRoutines are written in VB language 

Script to Change FND User Password from DB

DECLARE
  lv_user_name    VARCHAR2(100):= 'SANDEEP';
  lv_owner        VARCHAR2(10) := 'CUST';
  lv_password     VARCHAR2(20) := 'Oracle123';
 ln_user_id         NUMBER;
BEGIN
  /*Create FND User*/
   BEGIN
                          SELECT user_id
                          INTO ln_user_id
                          FROM fnd_user
                          WHERE user_name=lv_user_name;
    EXCEPTION
                        WHEN no_data_found THEN
                        dbms_output.put_line('Did not create user('||lv_user_name||') : '||sqlerrm);
    END;

    fnd_user_pkg.updateuser
                                    ( x_user_name                                                =>lv_user_name,
                                      x_owner                                                        =>lv_owner,
                                      x_unencrypted_password                             =>lv_password
                        );
 
    IF ln_user_id IS NOT NULL THEN
      dbms_output.put_line('Successfully Updated  user password  : '||lv_user_name);
      dbms_output.put_line('user id = '||ln_user_id);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error in  Create user :'||lv_user_name||'>>'||SQLERRM);
END;

Manage OAF Personalization from DB

Utility Name      JDR_UTILS 

Method Names  

• listcustomizations       : Parameter is Document Path ( Full path of Page, CO or VO)
• printdocument             : Parameter , Output of List Customization 
• deletedocument           : Parameter (P_document   => Output of List Customization ) 

Begin

jdr_utils.listcustomizations('/oracle/apps/per/selfservice/termination/webui/TerminationPG');

END;

Begin

jdr_utils.printdocument('/oracle/apps/per/selfservice/termination/webui/customizations/function/FUNCTIONNAME/TerminationPG');

END;

Begin

jdr_utils.deletedocument(p_document => '/oracle/apps/per/selfservice/termination/webui/customizations/function/FUNCTIONNAME/TerminationPG');

END;

Unix Command to send Email

uuencode <ATTACHMENT_FILE> <ATTACHMENT_FILE> | mailx -s "Subject" <TO_EMAIL> -c <CC_EMAIL>

mutt -s "SUBJECT" <TO_EMAIL> -b <CC_EMAIL> < <ATTACHMENT_FILE>

Sample Code 
-------------------------------------------------------------------------------------
CONTENT="$XXX_TOP/bin/sample.txt"
SUBJECT="Email TEST"       
EMAIL_ADDRESS="Sandeep19rm@gmail.com"
CC_ADDRESS="Sandeep19rm@gmail.com"
ATTACHMENT="-a $FILE_HEADER -a $FILE_DETAIL"

mutt -s "$SUBJECT" $EMAIL_ADDRESS -b $CC_EMAIL < $CONTENT $ATTACHMENT

-------------------------------------------------------------------------------------

(echo "Hi,

 Please find the attached log file for review.

Thanks & Regards

OA-Learning"; 

uuencode  test.zip test.zip) | mailx -s "TEST Result" 'Sandeep19rm@gmail.com'

-------------------------------------------------------------------------------------

HCM Extract Migration

HCM Extract provides export and import options. For any new development work, migration approach should be export/import.

Export Extract Definition
My Workforce >> Data Exchange >> Manage Extract/ Extract Definition >> Search Extract  and Click export icon ( 3rd one having up arrow key symbol)  

This will download the XML version of Extract definition. 

Import Extract Definition 

My Workforce >> Data Exchange >> Manage Extract/ Extract Definition >> Click on Import Icon ( opposite of export and available at top left corner , beside +) 

It will prompt to Give Extract Name and file Import. 

Limitation - Extract Name must be unique during Import. This is why this method doesn't really helpful if change is in existing extract. 

It also doesn't allow to delete extract  if it did ran in past. So you can't delete and reuse same name. 

For Operational Support, all changes to existing extract need to be done manually. 

Execution of HCM Extract

Extract Submission 
Option 1 
My Workforce >> Data Exchange >> Submit a Process or Report >> Submit Extracts

Option 2 
My Workforce >> Data Exchange >> Submit Extract  >> Search Extract  >> Submit

Option 3 
My Workforce >> Data Exchange >> Manage Extract/ Extract Definition >> Search Extract  and Click on Submit Extract Icon ( Last in list, a box with arrow key) 

Option 4
My Workforce >> Data Exchange >> Submit a Process or Report >> Search for Recently Completed Runs >> Open Action List >> Submit Similar 



Extract Scheduling 
Option 1 
My Workforce >> Data Exchange >> Submit a Process or Report >> Schedule



Extract Rollback
Option 1 
My Workforce >> Data Exchange >> Submit a Process or Report >> Search for Recently Completed Runs >> Open Action List >> Roll Back >. This will submit roll back request and results will disappear from original run

Context in Fast Formula

Fast Formula in Cloud can use context setting to derive values at different context.

For example if there is a requirement to display Current Salary and First Salary for given assignment. An Extract can be setup by creating 3 attributes with Assignment  user entity.

Attribute1 - CurrentSal - Type (Database Item Group)  - ASSIGNMENT SALARY
Attribute2  - DATE_FROM  Type (Database Item Group) - ASSIGNMENT START DATE

Attribute3 - FirstSal      - Type (Rule)

Fast Formula To derive Salary on different date.
/**************************************************************/
DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT IS 0

INPUTS ARE DATA_ELEMENTS (TEXT_TEXT)

l_effective_date = TO_DATE(SUBSTR(TRANSLATE(DATA_ELEMENTS['DATE_FROM'],'01234567890:.-TZ', '01234567890:.- '), 1, 19), 'YYYY-MM-DD HH24:MI:SS')

CHANGE_CONTEXTS(EFFECTIVE_DATE= l_effective_date)

RULE_VALUE = TO_CHAR(CMP_ASSIGNMENT_SALARY_AMOUNT)

RULE_VALUE = TRIM(RULE_VALUE)

RETURN RULE_VALUE
/**************************************************************/

. Read the Extract Attribute - DATE FROM
. Set The Context for Effective Date
. Get the Database Item Value ( as on set date)
. Return this value 

Create Custom Fast Formula in HCM Cloud

1) Go to Setup and Maintenance >> Search Task Fast Formulas
2) Create a new fast formula
3) Give Name , Type and Effective Start Date

4) Don't assign Legislative Data Group Name ( Otherwise you can't refer these Fast Formula)
5) Effective Start Date should be 01/01/0001

Use SELECT Query In Fast Formula

SQL can be used in fast formula with help of TABLE type value set

Steps:

1) Go to Setup and Maintenance >> Search Manage Value Sets for Global Human Resource task >> Create a new value Set
>> Validation Type = TABLE

Module                       -  Module Name  ( Extract etc)
From Clause               -  TABLE Name ( cmp_salary)
Value Column Name  -  ColumnName ( Salary_amount )
WHERE Clause          - Conditions

2) Use Parameter in WHERE Clause if required. ( Parameter will be passed from fast Formula only).
Example
Assignment_id = :{PARAMETER.ASSIGNMENT_ID}

ASSIGNMENT_ID is parameter Name, which need to be passed from Fast Formula.

3) Create Fast Formula and fetch value set value using following method  GET_VALUE_SET('ValueSetName','BindVariable')

ValueSetName - Mandatory - Pass ValueSet Name
BindVariable   - Optional  -  Value for Parameters

Syntex for Bind variable -
'|=PARAMNAME1='''''||Value1||'|PARAMNAME2=''''||Value2||''''


** This method might work only for payroll flow. I could not get return while using from Extract

List of Programs in Request Set

--SQL – List of Request Set Programs
SELECT USER_REQUEST_SET_NAME,
  a.REQUEST_SET_ID,
  Sequence,
  b.CONCURRENT_PROGRAM_ID,
  c.USER_CONCURRENT_PROGRAM_NAME
FROM FND_REQUEST_SETS_VL a,
  FND_REQUEST_SET_PROGRAMS b,
  FND_CONCURRENT_PROGRAMS_VL c
WHERE 1=1
--AND USER_REQUEST_SET_NAME LIKE '%RequestSetname%'
AND a.REQUEST_SET_ID        = B.REQUEST_SET_ID
AND b.concurrent_program_id = c.concurrent_program_id
ORDER BY USER_REQUEST_SET_NAME,sequence ;

Logical and Physical Query in OTBI

OTBI reporting is based on pre-configured subject areas.  Oracle provide multiple SAs for respective area which can be used to create an OTBI analysis by drag drop and calculations.

Based on selected SA and columns system generates a logical query, which can be viewed from Advanced  => SQL.


Physical query is SQL used to fetch logical query from Oracle DB. Physical query can be view in nqquery.log [Doc ID 1469168.1 - How to generate nqquery.log]

Steps to view physical query 
1) Get the logical query
2) Go to Administraton >> SQL Issues >> Paste following line followed by logical query
SET VARIABLE DISABLE_CACHE_HIT=1,DISABLE_PLAN_CACHE_HIT=1;
<<logicalQuery>>
3) Click on view log 

Script to find Form Personlizations

SELECT FORM_NAME,
  FUNCTION_NAME,
  Description,
  SEQUENCE,
  CONDITION
FROM FND_FORM_CUSTOM_RULES
WHERE ENABLED = 'Y';

SQL to find OAF personalizations

SELECT JDR_MDS_INTERNAL.GETDOCUMENTNAME(PATH_DOCID) DOCUMENT_ID,
  jdr_mds_internal.exportDocumentAsXML(jdr_mds_internal.getDocumentName(path_docid)) personalization_xml
FROM jdr_paths,
  jdr_attributes
WHERE PATH_DOCID = ATT_COMP_DOCID
AND path_name    = SUBSTR(att_value,INSTR(att_value, '/', -1, 1)+1)
AND att_comp_seq = 0
AND ATT_NAME     = 'customizes'
AND ATT_VALUE    = ATT_VALUE
AND jdr_mds_internal.getDocumentName(path_docid) LIKE '%/responsibility/53621%'


This would return all OAF personalization for responsibility id 53621.

Regular Expression for Date Format DD-MON-YYYY HH24:MI:SS

Use below regular expression to validate if given string is in specific date format.

select COUNT(1)  from DUAL where regexp_like('12-JUN-2016 23:34:59','^(\d){2}-[A-Z]{3}-(\d){4}\s(\d){2}:(\d){2}:(\d){2}$')
1

select COUNT(1)  from DUAL where regexp_like('121-JUN-2016 23:34:59','^(\d){2}-[A-Z]{3}-(\d){4}\s(\d){2}:(\d){2}:(\d){2}$')
0


=======================

^          : Starting String
$          : Ending String
{n}      : Execute last rule n time
[A-Z]   : All CAP Alphabets check
\d         :  digit check
\s          : Space

Script to find Template and associated JSP file

SELECT 

ida.access_name,
ida.file_name,
DECODE(ida.site_id, 1, 'All', s.msite_name) site_name,
ida.default_msite,
ida.language_code,
ida.default_language
FROM IBE_DSP_ATTACHMENTS_V ida, ibe_msites_tl s
WHERE m.site_id = s.msite_id and file_name = ‘ibeSeededPage.jsp’; 

Once identify the target JSP page use above SQL to find the associated program access name.

Create a custom version of seeded JSP (i.e. xxskpibeSeededPage.jsp)

Login to iStore Administrator Responsibility >> Advanced >> Template Manager 

Search the program access name with the out of above SQL 

Update >> Add new file >> provide custom file name for all countries and US language. 

Now place the custom file at OA_HTML file path and compile JSP (Click here for compilation and migrations)

SQL - Menu and Functions assigned to Responsibilities

SELECT a.seq,
  a.ENTRY_SEQUENCE,
  (SELECT menu_name FROM fnd_menus WHERE menu_id = a.menu_id
  ) menu,
  (SELECT menu_name FROM fnd_menus WHERE menu_id = a.sub_menu_id
  ) sub_menu,
  (SELECT FUNCTION_NAME
  FROM fnd_form_functions
  WHERE FUNCTION_ID = a.FUNCTION_ID
  ) function_name ,
  prompt,
  description
FROM
  (SELECT c.*
  FROM
    (SELECT level seq,
      ENTRY_SEQUENCE,
      x.menu_id,
      sub_menu_id,
      function_id,
      prompt,
      description
    FROM fnd_menu_entries_vl x
      START WITH x.menu_id =
      (SELECT menu_id
      FROM fnd_menus
      WHERE menu_id=
        (SELECT menu_id
        FROM fnd_responsibility_vl
        WHERE responsibility_name=:Responsibility_Name
        )
      )
      CONNECT BY prior sub_menu_id = x.menu_id
    ) c
  WHERE NOT EXISTS
    (SELECT 1
    FROM FND_RESP_FUNCTIONS
    WHERE responsibility_id =
      (SELECT responsibility_id
      FROM fnd_responsibility_tl
      WHERE responsibility_name=:Responsibility_Name
      )
    AND action_id = c.menu_id
    AND rule_type ='M'
    )
  AND NOT EXISTS
    (SELECT 1
    FROM FND_RESP_FUNCTIONS
    WHERE responsibility_id =
      (SELECT responsibility_id
      FROM fnd_responsibility_tl
      WHERE responsibility_name=:Responsibility_Name
      )
    AND action_id = c.function_id
    AND rule_type ='F'
    )
  ) A
ORDER BY 1,2,3

SQL - Concurrent Programs

Concurrent Program Executable Details- 
SELECT application_name,
  user_concurrent_program_name,
  meaning Program_Type,
  output_file_type output_format,
  --  concurrent_program_id,
  --  cp.application_id,--ce.*,
  executable_name,
  execution_file_name
FROM FND_CONCURRENT_PROGRAMS_VL cp ,
  FND_EXECUTABLES_FORM_V ce ,
  fnd_lookup_values flv
WHERE cp.executable_id       = ce.executable_id
AND ce.execution_method_code = lookup_code
AND LOOKUP_TYPE              = 'CP_EXECUTION_METHOD_CODE'
AND user_concurrent_program_name LIKE '%ProgramNAME%'

List of Concurrent Program  assigned to Responsibilities -

SELECT DISTINCT USER_CONCURRENT_PROGRAM_NAME,

  CONCURRENT_PROGRAM_NAME,
  z.responsibility_name,
  REQUEST_GROUP_NAME,
  application_name,
  REQUEST_GROUP_CODE
FROM FND_REQUEST_GROUP_UNITS x ,
  FND_CONCURRENT_PROGRAMS_VL y ,
  FND_RESPONSIBILITY_VL Z,
  FND_REQUEST_GROUPS FRG,
  FND_APPLICATION_VL fa
WHERE request_unit_id    = y.concurrent_program_id
AND unit_application_id  = y.application_id
AND X.REQUEST_GROUP_ID   = Z.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID   = Z.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = Z.REQUEST_GROUP_ID
AND fa.application_id    = frg.application_id
AND Y.USER_CONCURRENT_PROGRAM_NAME LIKE '%ProgramName%'
  -- AND responsibility_name LIKE '%'
ORDER BY 1,2;

SQL - Find Last accessed date of responsibility by USER

SELECT FU.USER_NAME,

  FRT.RESPONSIBILITY_NAME,

  MAX(FL.START_TIME),

  MAX(fl.login_id)

FROM FND_LOGINS FL,

  FND_LOGIN_RESPONSIBILITIES FLR,

  FND_USER FU,

  FND_RESPONSIBILITY_TL FRT

WHERE FL.LOGIN_ID         = FLR.LOGIN_ID

AND FL.USER_ID            = FU.USER_ID

AND FU.USER_NAME          ='OA_LEARNINIG'

AND FRT.RESPONSIBILITY_ID = FLR.RESPONSIBILITY_ID

GROUP BY FU.USER_NAME,

  FRT.RESPONSIBILITY_NAME

ORDER BY 3 DESC ;

PN –  System captures login date only from core forms. 

Additional Parameter Operating Unit in R12

You must have observed additional parameter (Operating Unit) option while running the concurrent program in R12. It's due to MOAC feature to run the program for selective OU only.

This additional parameter is controlled from System Administrator responsibility Self service version.

This post is to help how to pass value to this parameter if we are triggering such programs from database.

DECLARE
l_request_id  NUMBER;
BEGIN
           dbms_output.put_line('OAL - START::');
           FND_REQUEST.SET_ORG_ID(82); 
          -- This is required to set the value for additional Operating Unit Parameter

         l_request_id :=
         fnd_request.submit_request( 'APPLICATION_SHORT_NAME' ,'CPSHORTNAME' ,NULL ,NULL ,TRUE,Arg1,Arg2......);


           dbms_output.put_line('OAL - END::'|| l_request_id );

EXCEPTION
WHEN OTHERS THEN
      dbms_output.put_line('OAL - Exception ::'||SQLERRM);
END;