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.