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;

SQL - Transaction Control Flag Validation at Project/Task Level

FUNCTION OAL_TXN_CONTROL(p_project_id NUMBER,p_task_id NUMBER, P_EXPENDITURE_TYPE VARCHAR2,P_EXPENDITURE_CATEGORY VARCHAR2,P_EXPENDITURE_ITEM_DATE DATE)
RETURN VARCHAR2
AS
    lv_txn_flag VARCHAR2(10);
    ln_count    NUMBER;
    LD_DATE DATE;
    LV_CHRGEFLAG VARCHAR2(10);
    LN_ACTIVE    NUMBER;
    lv_exp varchar2(100);
lv_txn_level VARCHAR2(10);
  BEGIN
     
   
SELECT limit_to_txn_controls_flag
      INTO lv_txn_flag
      FROM PA_TASKS A
      WHERE PROJECT_ID = P_PROJECT_ID
      AND task_id      = P_task_id ;

      IF LV_TXN_FLAG   = 'Y' THEN -- IF 100
   
        SELECT COUNT(1)
                INTO ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists -- IF 110
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN  -- IF 120
            RETURN 'TRUE';
          ELSE
            RETURN 'T';
          END IF;   -- IF 120

        ELSE -- not exists 110
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE TASK_ID = P_task_id
              AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
 IF LN_COUNT <> 0 THEN -- IF 120
             RETURN 'TRUE';
          ELSE
             RETURN 'T';
          END IF; -- 120
        END IF; -- 110
      ELSE
-- check if any record exists in control table else call PA level vaidation
SELECT COUNT(1)
                INTO ln_count        
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id;

IF ln_count <> 0 THEN
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;

        IF LN_COUNT      <> 0 THEN -- IF type exists -- 210
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <> 0 THEN  -- 220
            RETURN 'T';
          ELSE
            RETURN 'TRUE';
          END IF;  -- 220
   
ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE TASK_ID = P_task_id
          AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
          IF ln_count <> 0 THEN  -- 220
            RETURN 'T';
          ELSE
            RETURN 'TRUE';
          END IF;  -- 220

        END IF; --210
ELSE -- Project Level Validations
SELECT limit_to_txn_controls_flag
 INTO lv_txn_flag
 FROM PA_PROJECTS_ALL A
 WHERE PROJECT_ID = P_PROJECT_ID;
 IF LV_TXN_FLAG   = 'Y' THEN
   
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN
            RETURN 'TRUE';
          ELSE
            RETURN 'P';
          END IF;

        ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE project_id = P_project_id
AND task_id is NULL
              AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         

          IF ln_count <>0 THEN
            RETURN 'TRUE';
          ELSE
            RETURN 'P';
          END IF;

        END IF;

      ELSE
   
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
       WHERE project_id = P_project_id
AND task_id is NULL
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN
            RETURN 'P';
          ELSE
            RETURN 'TRUE';
          END IF;

        ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
 IF ln_count <>0 THEN
            RETURN 'P';
          ELSE
            RETURN 'TRUE';
          END IF;

        END IF;
    END IF;
END IF;
END IF;  

END ;


Return Values - 

• TRUE - No Failure
• P - Failed at Project Level transaction control 
• T - Failed at Task Level transaction control 

SQL - 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 '%Program Name%'
 ;

The XML page cannot be displayed

Error –  Program completed in error and output shows below

Resolution -  Please check your data definition XML file (data template). Some issue with XML tags.

Correct data template and check again!!

VO Extension without JDeveloper

VO Extension is tool (JDeveloper) dependent but can be done manually by using below steps.

Steps to extend VO:

Create new VO  xxxxx_StandardVO.xml by renaming the standard VO StandardVO.xml (from server).

Edit below sections of VO according your customization need.

<ViewObject>

Name = New VO Name

Extends = original VO Name with complete path

ComponentClass = NewVO associate file with complete path

<SQLQuery>

Update the SQL with new changes.

<ViewAttribute> - Not required If change is in where condition or existing SQL logic

If adding a new sql field,we need to add ViewAttributes for new field.

Example -

<ViewObject

   Name=" xxxxx_StandardVO "

   Extends="oracle.apps.application.subapplication.common.server. StandardVO"

   BindingStyle="OracleName"

   CustomQuery="true"

   ComponentClass="xxxxx.oracle.apps.application.subapplication.common.server.xxxxx_StandardVOImpl"

   UseGlueCode="false">

<ViewAttribute

      Name="NEWFIELD"

      IsUpdateable="false"

      IsPersistent="false"

      Precision="30"

      Type="java.lang.String"

      ColumnType="VARCHAR2"

      AliasName=" NEWFIELD "

      Expression=" NEWFIELD "

      SQLType="VARCHAR" >

      <DesignTime>

         <Attr Name="_OverrideAttr" Value="true" />

         <Attr Name="_DisplaySize" Value="30" />

      </DesignTime>

   </ViewAttribute>

Create associated java file xxxxx_StandardVOImpl.java

package xxxxx.oracle.apps.application.subapplication.common.server;

import oracle.apps.application.subapplication.common.server. StandardVOImpl;

public class xxxxx_StandardVOImpl extends StandardVOImpl {

    public xxxxx_StandardVOImpl() {

    }  }

Compile and generate class file (xxxxx_StandardVOImpl.class

Create a OAProject.jpx file

<?xml version='1.0' encoding='windows-1252' ?>

<!DOCTYPE JboProject SYSTEM "jbo_03_01.dtd">

<JboProject

   Name="OAProject"

   SeparateXMLFiles="true"

   PackageName="" >

   <DesignTime>

      <Attr Name="_ejbPackage" Value="false" />

      <Attr Name="_NamedConnection" Value="DBConnection1" />

      <Attr Name="_version" Value="10.1.3.41.57" />

      <Attr Name="_jprName" Value="OAProject.jpr" />

   </DesignTime>

   <Containee

      Name="server"

      FullName="oracle.apps.application.subapplication.common.server"

      ObjectType="JboPackage" >

   </Containee>

   <Containee

      Name="server"

      FullName="xxxxx.oracle.apps.application.subapplication.common.server"

      ObjectType="JboPackage" >

   </Containee>

   <Substitutes>

      <Substitute OldName ="oracle.apps.application.subapplication.common.server.StandardVO" NewName ="xxxxx.oracle.apps.application.subapplication.common.server.xxxxx_StandardVO" />

   </Substitutes>

</JboProject>

Create server.xml file

<?xml version='1.0' encoding='windows-1252' ?>

<!DOCTYPE JboPackage SYSTEM "jbo_03_01.dtd">

<JboPackage

   Name="server"

   SeparateXMLFiles="true"

   PackageName="xxxxx.oracle.apps.application.subapplication.common.server" >

   <DesignTime>

      <Attr Name="_ejbPackage" Value="false" />

      <Attr Name="_version" Value="10.1.3.41.57" />

   </DesignTime>

   <Containee

      Name="xxxxx_StandardVO"

      FullName="xxxxx.oracle.apps.application.subapplication.common.server.xxxxx_StandardVO"

      ObjectType="ViewObject" >

   </Containee>

</JboPackage>

Place all files at respective locations and run JPX Importer