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

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

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

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

Migration Steps in Oracle Apps:

Migration Steps in Oracle Apps:

Step 1:

Get the data file(Excel......)


Step 2:

Create A table which have a columns as in Data file(Excel).
This Table is called "Staging Table".

Step 3:

Delete the Header of the Data file Exmaple:Name,State etc........
and Store it as Filename.CSV


Step 4:

Create a Control File

Step 5:

Load(Import) Data Using SQL * Loader Option in Toad or Using FileZilla and Concurrent Program.


Step 6:

Find The API For which your Requirement is.And find out mandatory parameter

Step 7:

Create A procedure or Package or Script to get required data.and pass it to the API variables.
Note:Try with "P_Validate=>TRUE" if it works then go for "P_Validate=>FALSE".



     Sameer Patil

"Team Work" More We Less Me.

Batch Element Entry (BEE)

What is Batch Element Entry (BEE)?

Batch Element Entry (BEE) enables payroll to enter batches of element entries, validate that the entries are correct and then transfer the entries to the employee’s element entries.

Batch Element Entry also provides the following functionality:

*  Batch Header identifies the batch and determines what happens if any of the batch lines duplicate existing element entries.

*  Batch Lines enable payroll to create batches by Element with the relevant Input Values for processing in payroll.  Defaults can be initiated for faster data entry.

*  BEE provides the ability to validate batches prior to submitting.  This feature enables payroll to identify incorrect employee assignment numbers, elements that are not eligible for employees, etc.

*  Provides the ability to use Control Totals to help validate the batch

*  Messages that indicate if batches are out of balance with control totals, in valid employee assignment numbers, etc.

*  BEE Spreadsheet Interface - Enables the loading of payroll data from various sources into BEE using the delivered BEE Spreadsheet Interface tool.  Please refer to the Spreadsheet Interface section for more information on using the Spreadsheet Interface.

"Team Work" More We Less Me.

           Sameer Patil

Procedure to Create Competency.

Procedure to Create Competency.

1. Select the New button in the Find window.

2. Enter a name and description for the competence.

3. Enter the dates between which the competence is valid. You must enter a From date but you can leave the To date blank. If you enter a To date, ensure that it is wide enough to cover any competence elements you may need to create.

4. If you want to use a general rating scale to measure the competence against, go to Entering a Rating Scale. If you want to measure the competence against specific proficiency levels, go to Entering Proficiency Levels.

Entering a Rating Scale

5. Click the Scale radio button to use a rating scale to measure the competence against.

6. If you set up a default rating scale, it displays automatically for this competence, otherwise select one from the list. You can delete the defaulted rating scale and select another one, if required.

7. Select the Levels box if you want to check the proficiency levels for that rating scale, otherwise go straight to Entering Competence Details.

Entering Proficiency Levels

8. Click the Levels radio button to enter proficiency levels and behavioral indicators for the competence.

9. Select the Levels button and enter the first proficiency level and description. For example, enter 0 in the Level field and No Skill in the Name field.

10. Enter the behavioral indicator for that level, for example, 'No knowledge/expertise of scaffolding'. You can enter up to 2000 characters for each behavioral indicator.

11. Continue to enter proficiency levels and behavioral indicators, then go to Entering Competence Details.

Entering Competence Details

12. Select the main method of evaluating the competence, for example, written examination or observation.

13. Enter how often you need to evaluate the competence. For example, enter 5 if you have to evaluate the competence every 5 years.

14. Select the measure to identify how often you need to evaluate the competence. For example, select Year if you evaluate the competence every 5 years.

15. Click the Certification Required box if you need a certificate to confirm possession of the competence, for example, if you need proof of a driving license.

If you click this box, when you or another user later indicates that a person possesses this competence (using the Competence Profile), you must enter the certification method and date.

16. Save your changes.

Querying a Competence Using the Find Competencies Window

When you navigate to the Competencies window, the Find Competencies window automatically displays in front of it.

To query a competence using the Find Competence window:

1. Do one or both of the following:

• Enter a full or partial query on the competence name.

2. Choose the Find button.

Oracle Human Resources displays the competence for you to check.

"Team Work" More We Less Me.
           Sameer Patil

Object Version Number (OVN) in API

 Object Version Number (OVN) is a column in a database table. It is used to track multiple changes to a record. When a new row is inserted, the OVN is set to 1. As updates are performed on the row through the Forms, the OVN is incremented. The OVN is transferred with a queried row. If an update is attempted when the change is saved to the table, the current OVN is compared with the value being passed back from the transaction. If the two are the same, the update can be committed to the table. If the two are different, the update is rejected and flagged with an error. The transaction must be re-queried, and the update made again to the more current version of the row.

APIs and Forms use OVNs similarly. Every published API has the p_object version_number control parameter.

For Create APIs, this parameter is defined as an OUT parameter: the API assigns the object version number to 1 for row inserts. For Update APIs, the parameter is defined as IN OUT, and is mandatory in the API call.

The current value must be passed in the API call and it is compared to the version on the row in the table. If the versions are different, the error HR_7155_OBJECT_LOCKED is raised. The information can be re-queried for a more current version.




You can use the following query to determine the OVN to pass in your API call:

select max(ovn) from (table name)  where person_id/assignment_id etc=(some value)

Thanks and Regards

Sameer S Patil

"Team Work" More We Less Me.