Pages

Recently Viewed

Monday, August 24, 2015

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. 




Thursday, May 21, 2015

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;

Wednesday, May 20, 2015

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 


Wednesday, April 22, 2015

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%'
 ;

Friday, February 27, 2015

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!!


Saturday, January 17, 2015

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  

Friday, January 2, 2015

Print parametr in XML report without having XML tags

Oracle provides option to print input parameter directly in RTF without generating a XML tag for this. This can be done in below 2 steps,

Define the parameter reference: this can be done in two different ways
<?param@begin:P_PARAM?>
<xsl:param name="P_PARAM" xdofo:ctx="begin"/>
Print the report parameter value:
<?$P_PARAM?>


Example
CP Parameter Name
START_TIME
END_TIME

Invoke parameters on rtf
<?param@begin:START_TIME?>
<?param@begin:END_TIME?>

Create 2 form field (Placeholder) in RTF  with below values
<?$START_TIME?>
<?$END_TIME?>