Total Pageviews

Search This Blog

Wednesday, November 30, 2011

OAF : Package To Print OAF XML Page

Hi All,

Use jdr_utils..printDocument for printing XML page in Oracle apps by specifying page path as a parameter for procedure.


Regards,
Sameer Patil
"Team Work" More We Less Me.

Enable DFF in SelfService Page

Hi All.

Steps to Enable DFF in OAF Page.:

Step 1: Go to Personalize Page
Step 2: Expand All and click on Complete View
Step 3: Search for Your DFF Name(Ex Additional Absence Details )
Step 4: Click on personalize button  Check whether Rendered is set to True If not Set it to True
Step 5: Then Check the issue..

Regards,
Sameer Patil

"Team Work" More We Less Me.

Thursday, March 17, 2011

Oracle Apps AIM Documents

Oracle AIM Documents
Business Process Architecture (BP)
BP.010 - Define Business and Process Strategy
BP.020 - Catalog and Analyze Potential Changes
BP.030 - Determine Data Gathering Requirements
BP.040 - Develop Current Process Model
BP.050 - Review Leading Practices
BP.060 – Develop High-Level Process Vision
BP.070 – Develop High-Level Process Design
BP.080 – Develop Future Process Model
BP.090 – Document Business Procedure
Business Requirements Definition (RD)
RD.010 – Identify Current Financial and Operating Structure
RD.020 – Conduct Current Business Baseline
RD.030 – Establish Process and Mapping Summary
RD.040 – Gather Business Volumes and Metrics
RD.050 – Gather Business Requirements
RD.060 – Determine Audit and Control Requirements
RD.070 – Identify Business Availability Requirements
RD.080 – Identify Reporting and Information Access Requirements
Business Requirements Mapping
BR.010 – Analyze High-Level Gaps
BR.020 – Prepare mapping environment
BR.030 – Map Business requirements
BR.040 – Map Business Data
BR.050 – Conduct Integration Fit Analysis
BR.060 – Create Information Model
BR.070 – Create Reporting Fit Analysis
BR.080 – Test Business Solutions
BR.090 – Confirm Integrated Business Solutions
BR.100 – Define Applications Setup
BR.110 – Define security Profiles
Application and Technical Architecture (TA)
TA.010 – Define Architecture Requirements and Strategy
TA.020 – Identify Current Technical Architecture
TA.030 – Develop Preliminary Conceptual Architecture
TA.040 – Define Application Architecture
TA.050 – Define System Availability Strategy
TA.060 – Define Reporting and Information Access Strategy
TA.070 – Revise Conceptual Architecture
TA.080 – Define Application Security Architecture
TA.090 – Define Application and Database Server Architecture
TA.100 – Define and Propose Architecture Subsystems
TA.110 – Define System Capacity Plan
TA.120 – Define Platform and Network Architecture
TA.130 – Define Application Deployment Plan
TA.140 – Assess Performance Risks
TA.150 – Define System Management Procedures
Module Design and Build (MD)
MD.010 – Define Application Extension Strategy
MD.020 – Define and estimate application extensions
MD.030 – Define design standards
MD.040 – Define Build Standards
MD.050 – Create Application extensions functional design
MD.060 – Design Database extensions
MD.070 – Create Application extensions technical design
MD.080 – Review functional and Technical designs
MD.090 – Prepare Development environment
MD.100 – Create Database extensions
MD.110 – Create Application extension modules
MD.120 – Create Installation routines
Data Conversion (CV)
CV.010 – Define data conversion requirements and strategy
CV.020 – Define Conversion standards
CV.030 – Prepare conversion environment
CV.040 – Perform conversion data mapping
CV.050 – Define manual conversion procedures
CV.060 – Design conversion programs
CV.070 – Prepare conversion test plans
CV.080 – Develop conversion programs
CV.090 – Perform conversion unit tests
CV.100 – Perform conversion business objects
CV.110 – Perform conversion validation tests
CV.120 – Install conversion programs
CV.130 – Convert and verify data
Documentation (DO)
DO.010 – Define documentation requirements and strategy
DO.020 – Define Documentation standards and procedures
DO.030 – Prepare glossary
DO.040 – Prepare documentation environment
DO.050 – Produce documentation prototypes and templates
DO.060 – Publish user reference manual
DO.070 – Publish user guide
DO.080 – Publish technical reference manual
DO.090 – Publish system management guide
Business System Testing (TE)
TE.010 – Define testing requirements and strategy
TE.020 – Develop unit test script
TE.030 – Develop link test script
TE.040 – Develop system test script
TE.050 – Develop systems integration test script
TE.060 – Prepare testing environments
TE.070 – Perform unit test
TE.080 – Perform link test
TE.090 – perform installation test
TE.100 – Prepare key users for testing
TE.110 – Perform system test
TE.120 – Perform systems integration test
TE.130 – Perform Acceptance test
Performance Testing(PT)
PT.010 – Define Performance Testing Strategy
PT.020 – Identify Performance Test Scenarios
PT.030 – Identify Performance Test Transaction
PT.040 – Create Performance Test Scripts
PT.050 – Design Performance Test Transaction Programs
PT.060 – Design Performance Test Data
PT.070 – Design Test Database Load Programs
PT.080 – Create Performance Test Transaction Programs
PT.090 – Create Test Database Load Programs
PT.100 – Construct Performance Test Database
PT.110 – Prepare Performance Test Environment
PT.120 – Execute Performance Test
Adoption and Learning (AP)
AP.010 – Define Executive Project Strategy
AP.020 – Conduct Initial Project Team Orientation
AP.030 – Develop Project Team Learning Plan
AP.040 – Prepare Project Team Learning Environment
AP.050 – Conduct Project Team Learning Events
AP.060 – Develop Business Unit Managers’ Readiness Plan
AP.070 – Develop Project Readiness Roadmap
AP.080 – Develop and Execute Communication Campaign
AP.090 – Develop Managers’ Readiness Plan
AP.100 – Identify Business Process Impact on Organization
AP.110 – Align Human Performance Support Systems
AP.120 – Align Information Technology Groups
AP.130 – Conduct User Learning Needs Analysis
AP.140 – Develop User Learning Plan
AP.150 – Develop User
AP.160 – Prepare User Learning Environment
AP.170 – Conduct User Learning Events
AP.180 – Conduct Effectiveness Assessment
Production Migration (PM)
PM.010 – Define Transition Strategy
PM.020 – Design Production Support Infrastructure
PM.030 – Develop Transition and Contingency Plan
PM.040 – Prepare Production Environment
PM.050 – Set Up Applications
PM.060 – Implement Production Support Infrastructure
PM.070 – Verify Production Readiness
PM.080 – Begin Production
PM.090 – Measure System Performance
PM.100 – Maintain System
PM.110 – Refine Production System
PM.120 – Decommission Former Systems
PM.130 – Propose Future Business Direction
PM.140 – Propose Future Technical Direction

“Team Work” More We Less Me.|| Sameer Patil

Tuesday, March 15, 2011

Standard and Default Reports in Oracle HRMS

Predefined and User Defined Reports in Oracle HRMS

Thursday, March 10, 2011

Creating a View Without Table in SQL

Hi Friends,

We can create a View without Table.
Solution is using FORCE key word in syntax.

Example : 
                CREATE OR REPLACE FORCE VIEW SAM_VIEW AS SELECT * FROM SAM_TAB;
While running this in Toad it will through an Error like ORA-00942: table or view does not exist.
But when you run below query you will come to know View created or Not.
                  DESC SAM_VIEW;
Now you can create you own table..
But still view is in invalid state.You need alter view.
                 ALTER VIEW SAM_VIEW COMPILE;

"Team Work" More We Less Me.
             Sameer Patil

ORA-20001: FLEX-SSV EXCEPTION

ORA-20001: FLEX-SSV EXCEPTION

Hi All,
This Error Occurs in three case:
1)Data Passing not matches with Value set data.
2)(Some Case)Getting data in cursor without using TRIM.
3)Passing value in place of ID..

Example : For some field a value set is attached ..which is of TABLE Type.
                There value will be Data has to be match and ID Has to be pass in API.

"Team Work" More We Less Me.
Sameer Patil

Wednesday, March 2, 2011

Audit Trail In Oracle Apps

Audit Trail is a way of keeping track of changes made to important data
in Oracle Application tables. Audit Trail  keeps a history of the Changes.

To setup Audit Trail in Oracle Applications perform the
following steps:

Step 1: Go to System Administrator Responsibility

Step 2: Make sure APPLSYS  has select privileges on SYS.DBA_TABLES.

For example, if you are auditing the PER_AUDIT_ALL table,
use the following SQL statement to verify this:

select TABLE_NAME
from SYS.DBA_TABLES
where TABLE_NAME like 'PER_AUDIT_ALL';

Step 3:  Define audit groups: It is required to group the tables that you
need to audit since auditing is enabled on an audit groups basis.

Navigation: Security -> Audit Trail -> Groups
Step 4:. Define audit installations: In order to be able to audit across
application installations, you must specify the Oracle ID's whose
tables you wish to audit. At this point, you will need to specify
the list of columns from the table you want audited.

Navigation: Security -> Audit Trail -> Install

Step 5:  Run the "Audit Trail Update Tables" report from the submit request
form to enable auditing.

Navigation: Requests -> Run -> select a single request


"Team Work" More We Less Me.
   Sameer Patil

Wednesday, February 9, 2011

Oracle Apps Error Codes

Oracle Apps Error Codes:

[APP-51000] Invalid position This position does not exist at all, or is not completely within the assignment's date range.

[APP-51001] This vacancy does not exist

[APP-51002] Cannot submit report Possible causes could be that the report does not exist, The concurrent request manager is not working.

[APP-51003] Assignment must have a valid pay period at actual termination date

[APP-51005] Person type must be a type of EMP at actual termination date

[APP-51006] Payroll must have a valid pay period at actual termination date

[APP-51007] Actual termination date must exist before setting final process date

[APP-51008] Person type must be a type of EX_EMP at final process date

[APP-51009] This position is not in the same business group as the assignment.

[APP-51010] Formula return &RET_NAME exceeds size limit of &LIM_SIZE characters.

[APP-51011] Person does not exist at specified date

[APP-51012] Cannot rehire a person who is not a type of EX_EMP

[APP-51013] This person does not have a previous period of service

[APP-51014] Cannot rehire a person unless a final processing date exists for their previous period of service

[APP-51015] The rehire date must be later than the final processing date of the previous period of service

[APP-51017] Assignment Number must be null for applicant assignments

[APP-51018] Hierarchy Level must be greater than 0. The Hierarchy level specified must be greater than 0.

[APP-51019] Primary Key Constraint Error Combination of Entity Name and Pattern Purpose is not unique.

[APP-51020] Unique Key Constraint Error. Combination of Entity Name and Pattern Purpose is not unique.

[APP-51021] Unique Key Constraint Error. Combination of Pattern Purpose and Hierarchy Level is not unique.

[APP-51022] Invalid primary key specified. The Primary key specified is invalid.

[APP-51023] The Pattern Bit Specified is not unique. Pattern Bit specifed is not unique.

[APP-51024] Multiplier must be positive whole number. Multiplier must be greater than 0.

[APP-51026] This record is locked by another user. The Object specified is currently locked. Try again later.

[APP-51027] API Error. Invalid Object Version Number. Invalid Object Version Number. Re-query and try again.

[APP-51028] Employment category is not valid

[APP-51029] Choose a date on which the assignment includes a payroll

[APP-51030] There is a gap in the continuity of Primary Addresses.

[APP-51030] There is a gap in the continuity of Primary/Main Addresses.

[APP-51031] Employee is a supervisor, changing hire will invalidate this.

[APP-51032] Employees previous Employment,has not been closed for more than a day.

[APP-51033] Future dated salary administration records exist

"Team Work" More We Less Me.
          Sameer Patil

Thursday, February 3, 2011

Steps For Creating User Hook

Steps For Creating User Hook :Oracle HRMS
Step 1: Find the API for which HOOK has to write.
            There are mainly two tables:
             1)HR_API_HOOKS
             2)HR_API_MODULES
Note:For understanding types of hook search in oracle metalink.

Step 2: Create A PL/SQL  Procedure which fits requirements.

Step 3: Now Register created procedure into Required Hook:
             API for Registering User Hook:
                   . HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL
                   . HR_API_HOOK_CALL_API.UPDATE_API_HOOK_CALL
                   . HR_API_HOOK_CALL_API.DELETE_API_HOOK_CALL

Step 4: Once Hook registered. We need to run the Pre Processors which will taken care by DBA.

Step 5: Once done with all above steps verify the status of user hook in the table called:
             HR_API_HOOK_CALLS


Sunday, January 23, 2011

Check the patch for Applied Patch.

Check the patch for Applied Patch.

AD_APPLIED_PATCHES_S

AD_APPLIED_PATCHES

AD_APPLIED_PATCHES_N1

AD_APPLIED_PATCHES_N2

AD_APPLIED_PATCHES_U1

AD_APPLIED_PATCHES_U2

AD_APPLIED_PATCHES_S

AD_APPLIED_PATCHES


"Team Work" More We Less Me.
 Thanks & Regards
  Sameer Patil

Thursday, January 20, 2011

Date Tracking Problems in API:Oracle Apps

Date Tracking Problems in API

 if ((TRUNC (a.eff_date) < TRUNC (b.effective_start_date)) and
             (TRUNC (b.effective_end_date)!= TO_DATE ('31-DEC-4712'))) then
                   if (TRUNC (b.effective_end_date) > TRUNC (a.eff_date)) then
                   lc_  date_mode := 'CORRECTION';
                   lc_date := b.effective_start_date;
                    v_status := 'S';
                  DBMS_OUTPUT.put_line ('Condition 1');
               else
                   v_status := 'E';
                   v_error := 'No need to update';
                    DBMS_OUTPUT.put_line ('Condition 2');
               end if;
           
          elsif ((TRUNC (b.effective_start_date)  < TRUNC (a.eff_date))
              and (TRUNC (b.effective_end_date)!= TO_DATE ('31-DEC-4712'))) then
              
               if (TRUNC (b.effective_end_date) > TRUNC (a.eff_date)) then
                   lc_date_mode := 'UPDATE_CHANGE_INSERT';
                   lc_date := a.eff_date;
                    v_status := 'S';
                  DBMS_OUTPUT.put_line ('Condition 3');
               else
                   v_status := 'E';
                  v_error := 'No need to update';
                    DBMS_OUTPUT.put_line ('Condition 4');
               end if;
          
        
         else
             IF TRUNC (b.effective_start_date) = TRUNC (a.eff_date)
             THEN
                lc_date_mode := 'CORRECTION';
                lc_date := a.eff_date;
                v_status := 'S';
                DBMS_OUTPUT.put_line ('Condition 5');
             ELSE
                lc_date := a.eff_date;
                lc_date_mode := 'UPDATE';
                v_status := 'S';
                DBMS_OUTPUT.put_line ('Condition 6');
             END IF;
         end if;
       

         IF lc_date_mode = 'UPDATE' AND v_status = 'S'
         THEN
            IF b.effective_end_date != TO_DATE ('31-DEC-4712')
            -- need to chnage
            THEN
               lc_date_mode := 'UPDATE_CHANGE_INSERT';
               lc_date := a.eff_date;
               DBMS_OUTPUT.put_line ('Condition 7');
            elsif ((b.effective_end_date = TO_DATE ('31-DEC-4712')) and
                  (b.effective_start_date >= a.eff_date)) then
               lc_date_mode := 'CORRECTION';
               lc_date := b.effective_start_date;
               DBMS_OUTPUT.put_line ('Condition 8');
            elsif ((b.effective_end_date = TO_DATE ('31-DEC-4712')) and
                  (b.effective_start_date < a.eff_date)) then
              lc_date := a.eff_date;
                lc_date_mode := 'UPDATE';
                DBMS_OUTPUT.put_line ('Condition 9');
            END IF;
         END IF;

         DBMS_OUTPUT.put_line ('**** Before Calling API ******');
         DBMS_OUTPUT.put_line ('a.eff_date : ' || a.eff_date);
         DBMS_OUTPUT.put_line (   ' b.effective_start_date : '
                               || b.effective_start_date
                              );
         DBMS_OUTPUT.put_line ('lc_date_mode : ' || lc_date_mode);
         DBMS_OUTPUT.put_line (' lc_date : ' || lc_date);
 


"Team Work" More We Less Me.
            Sameer Patil

Wednesday, January 19, 2011

FND_PROFILE

1. Put :This can be used to put a value to the specified user profile option.

Usage:
 * FND_Profile.Put('PROFILE_NAME', 'New_Value')

    * FND_Profile.Put(’USERNAME’, Usr_Name)

    * FND_Profile.Put(’RESP_ID’, Resp_ID)

    * FND_Profile.Put(’RESP_APPL_ID’, Resp_App_ID)

    * FND_Profile.Put(’USER_ID’, User_ID)


2.DEFINED : this is function returns TRUE if a value has been assigned to the specified profile option.

Usage:
 * SELECT fnd_profile.defined(’ACCOUNT_GENERATOR:DEBUG_MODE’) ACC_GEN_DEBUG_SESSION_MODE FROM DUAL;

3.GET :This is used to retrieve the current value of the specified user profile option

Usage :Different type of options can be retrieved like


    * FND_Profile.Get('PROFILENAME', Profile_name);

    * FND_Profile.Get(’CONC_LOGIN_ID’, Conc_login_id);

    * FND_Profile.Get(’LOGIN_ID’, loginid);


4.VALUE : This is function which returns a character string. Used to retrieve the current value of the specified user profile option.

Usage:


    * fnd_profile.value('PROFILEOPTION')

    * fnd_profile.value(’MFG_ORGANIZATION_ID’)

    * fnd_profile.value(’login_ID’)

    * fnd_profile.value(’USER_ID’)

    * fnd_profile.value(’USERNAME’)

    * fnd_profile.value(’CONCURRENT_REQUEST_ID’)

    * fnd_profile.value(’GL_SET_OF_BKS_ID’)

    * fnd_profile.value(’ORG_ID’)

    * fnd_profile.value(’SO_ORGANIZATION_ID’)

    * fnd_profile.value(’APPL_SHRT_NAME’)

    * fnd_profile.value(’RESP_NAME’)

    * fnd_profile.value(’RESP_ID’)


5.VALUE_WNPS: This is a function, returns a character string. This is Used to retrieve the current value of the specified user profile option without caching it.

6.SAVE_USER :This is function used to save a value for a profile option permanently to the database, for the current user level. It is necessary to explicitly issue a commit when using this function. Returns TRUE if profile option is successfully saved, otherwise FALSE.

7.SAVE :This is function used to save a value for a profile option permanently to the database, for a specified level. It is necessary to explicitly issue a commit when using this function. Returns TRUE if profile option is successfully saved, otherwise FALSE.

Usage


    * fnd_profile.save(’GUEST_USER_PWD’, ‘GUEST/ORACLE’, ‘SITE’);


8.INITIALIZE :This is used by internal Applications Object Library to initialize the internal profile information at the level context.
The cache is first cleared of all database options.

Usage:


    * fnd_profile.initialize(user_id);


9.PUTMULTIPLE :This is used by internal Applications Object Library to set multiple pairs of profile options and values. 
"Team Work" More We Less Me.
           Sameer Patil

Tuesday, January 18, 2011

Loading Image in Oracle HRMS

How To Load BLOB Data Into the Database via PL/SQL

   1.  Create a table TEST_BLOB.

      DROP TABLE test_blob; 

      CREATE TABLE test_blob ( 
        id   NUMBER, 
        data BLOB 
      ) 
      /

   2. Create a procedure INSERT_BLOB.

      CREATE OR REPLACE PROCEDURE insert_blob (in_id IN INTEGER, in_data IN BLOB)
      AS
        -- Max Buffer Size = 32767
        buffersize CONSTANT INTEGER := 32767;

        v_data         BLOB;
        v_data_offset  BINARY_INTEGER := 1;
        v_data_remain  INTEGER;
        v_data_buffer  RAW(32767);
        v_data_amount  INTEGER;
         
      BEGIN
        
        INSERT INTO test_blob VALUES (in_id, EMPTY_BLOB())
          RETURNING data INTO v_data;
        
        v_data_remain := DBMS_LOB.GETLENGTH(in_data);
        DBMS_OUTPUT.PUT_LINE('Size of BLOB: ' || v_data_remain || ' bytes');
        
        WHILE v_data_remain > 0 LOOP

          -- Calculate the size of the buffer    
          v_data_amount := LEAST(buffersize, v_data_remain);

          -- Read a chunk of the BLOB passed in
          DBMS_LOB.READ(in_data, v_data_amount, v_data_offset, v_data_buffer);

          -- Write the chunk to the interal BLOB inside the database
          DBMS_LOB.WRITE(v_data, v_data_amount, v_data_offset, v_data_buffer);

          -- Calculate remaining BLOB data
          v_data_remain := v_data_remain - v_data_amount;

          -- Calculate next BLOB offset
          v_data_offset := v_data_offset + v_data_amount;

          DBMS_OUTPUT.PUT_LINE('Amount Read/Written: ' || (v_data_offset - 1) || ' bytes');    

        END LOOP;  

      END;
      /

   3. Create a large BLOB and call procedure INSERT_BLOB.

      SET serveroutput ON

      DECLARE

        test_blob   BLOB;
        test_data   RAW(100) := UTL_RAW.CAST_TO_RAW('ABCDEFGHIJ1234567890abcdefghij');
        chunk_size  BINARY_INTEGER;

      BEGIN 

        chunk_size := UTL_RAW.LENGTH(test_data); 
        DBMS_OUTPUT.PUT_LINE('Size of Chunk: ' || chunk_size || ' bytes');
        
        -- Create a Temporary BLOB
        DBMS_LOB.CREATETEMPORARY(test_blob, false);

        -- Fill the BLOB with 50,000 test_data sized chunks
        FOR i IN 1..50000 LOOP
          DBMS_LOB.WRITE(test_blob, chunk_size, ((i-1)*chunk_size)+1, test_data);
        END LOOP;

        -- Report the length of the BLOB
        DBMS_OUTPUT.PUT_LINE('Length of TEST_BLOB: ' || DBMS_LOB.GETLENGTH(test_blob) || ' bytes');

        -- Insert the BLOB into the database
        DBMS_OUTPUT.PUT_LINE('Calling INSERT_BLOB...');
        insert_blob(1, test_blob);

      END;
      /

   4. Review the OUTPUT below.

      Size of Chunk: 30 bytes
      Length of TEST_BLOB: 1500000 bytes

      Calling INSERT_BLOB...

      Size of BLOB: 1500000 bytes

      Amount Read/Written: 32767 bytes
      Amount Read/Written: 65534 bytes
      Amount Read/Written: 98301 bytes
      Amount Read/Written: 131068 bytes
 

      PL/SQL procedure successfully completed.

   5. Verify that the BLOB was successfully inserted.

      SQL> SELECT DBMS_LOB.GETLENGTH(data) FROM test_blob;

      DBMS_LOB.GETLENGTH(DATA)
      ------------------------
                       1500000

      SQL>

Additional Notes

    * You can use procedure INSERT_BLOB to also load CLOBs into the database.  That is because Binary data is a superset of Character data.  Hence all character data can be represented by binary code points.

    * You cannot use procedure INSERT_CLOB to load BLOBs into the database.  Binary data is not a subset of Character data.  All binary data cannot be represented in character code points.

    * When creating a test BLOB you must use the UTL_RAW.LENGTH function rather than the standard LENGTH function.  UTL_RAW.LENGTH returns the size in BYTES of a RAW datatype.  The LENGTH function returns the size in CHARACTERS of character datatypes.
"Team Work" More We Less Me.
         Sameer Patil