Script to update natural account

CREATE OR REPLACE PROCEDURE XX_GL_EXTRACT_ENABLE_ACCOUNT AS

v_set_id    number;
v_position_account_type    number:=3;
v_acc_flex_value_set    varchar2(240):='XXX_COA_ACCOUNT'; /**Pass the COA name/
flex_value_exeception    EXCEPTION;
v_upd_count    number:=0;

BEGIN
 
   SELECT   flex_value_set_id
     INTO   v_set_id
     FROM   fnd_flex_value_sets
    WHERE   flex_value_set_name = v_acc_flex_value_set;


   IF v_set_id IS NULL
   THEN
      RAISE flex_value_exeception;
   END IF;



   IF v_set_id IS NOT NULL
   THEN
     
      SELECT   COUNT (v.flex_value)
        INTO   v_upd_count
        FROM   fnd_flex_hierarchies_tl h,
               fnd_flex_values v,
               fnd_flex_values_tl vt,
               gl_lookups l1,
               gl_lookups l2,
               gl_lookups l5
       WHERE       h.flex_value_set_id(+) = v_set_id
               AND h.hierarchy_id(+) = v.structured_hierarchy_level
               AND h.language(+) = USERENV ('LANG')
               AND v.flex_value_set_id = v_set_id
               AND vt.flex_value_id = v.flex_value_id + 0
               AND vt.language = USERENV ('LANG')
               AND v.enabled_flag = l1.lookup_code
               AND l1.lookup_type = 'YES/NO'
               AND v.summary_flag = l2.lookup_code
               AND l2.lookup_type = 'YES/NO'
               AND NVL (
                     SUBSTRB (
                           fnd_global.newline
                        || v.compiled_value_attributes
                        || fnd_global.newline,
                        INSTRB (
                              fnd_global.newline
                           || v.compiled_value_attributes
                           || fnd_global.newline,
                           fnd_global.newline,
                           1,
                           v_position_account_type
                        )
                        + 1,
                        1
                     ),
                     'A'
                  ) = l5.lookup_code(+)
               AND l5.lookup_type(+) = 'ACCOUNT TYPE'
               AND l5.lookup_code IN ('A', 'L', 'O');

      IF v_upd_count > 1
      THEN
         UPDATE   fnd_flex_values
            SET   attribute1 = 'Y', value_category = 'XXX_COA_ACCOUNT'
          WHERE   flex_value IN
                        (SELECT   v.flex_value
                           FROM   fnd_flex_hierarchies_tl h,
                                  fnd_flex_values v,
                                  fnd_flex_values_tl vt,
                                  gl_lookups l1,
                                  gl_lookups l2,
                                  gl_lookups l5
                          WHERE   h.flex_value_set_id(+) = v_set_id
                                  AND h.hierarchy_id(+) =
                                        v.structured_hierarchy_level
                                  AND h.language(+) = USERENV ('LANG')
                                  AND v.flex_value_set_id = v_set_id
                                  AND vt.flex_value_id = v.flex_value_id + 0
                                  AND vt.language = USERENV ('LANG')
                                  AND v.enabled_flag = l1.lookup_code
                                  AND l1.lookup_type = 'YES/NO'
                                  AND v.summary_flag = l2.lookup_code
                                  AND l2.lookup_type = 'YES/NO'
                                  AND NVL (
                                        SUBSTRB (
                                              fnd_global.newline
                                           || v.compiled_value_attributes
                                           || fnd_global.newline,
                                           INSTRB (
                                                 fnd_global.newline
                                              || v.compiled_value_attributes
                                              || fnd_global.newline,
                                              fnd_global.newline,
                                              1,
                                              v_position_account_type
                                           )
                                           + 1,
                                           1
                                        ),
                                        'A'
                                     ) = l5.lookup_code(+)
                                  AND l5.lookup_type(+) = 'ACCOUNT TYPE'
                                  AND l5.lookup_code IN ('A', 'L', 'O'));
      END IF;
   Commit;
   END IF;

   DBMS_OUTPUT.put_line (
      'Number of Records updated :' || TO_CHAR (v_upd_count)
   );
EXCEPTION
   WHEN flex_value_exeception
   THEN
      DBMS_OUTPUT.put_line('Unable to deriver flex value set id for the value set XXX_COA_ACCOUNT');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error in updating DFF values for Natural account'
      );
END XX_GL_EXTRACT_ENABLE_ACCOUNT;
/

Script to replace junk characters

This script can be used to remove junk characters from a string

CREATE OR REPLACE FUNCTION xx_Replace_Junk_Chars_func (p_string IN VARCHAR2)
   RETURN VARCHAR2
IS
   lv_string   VARCHAR2 (1000);
   i           NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE (p_string);

   FOR i IN 1 .. LENGTH (p_string)
   LOOP
      DBMS_OUTPUT.PUT_LINE (   SUBSTR (p_string, i, 1)
                            || '....'
                            || ASCII (SUBSTR (p_string, i, 1))
                           );

      IF    (ASCII (SUBSTR (p_string, i, 1)) BETWEEN 65 AND 90) OR (ASCII (SUBSTR (p_string, i, 1)) BETWEEN 97 AND 122)
   OR (ASCII (SUBSTR (p_string, i, 1)) BETWEEN 48 AND 57) OR (SUBSTR (p_string, i, 1) IN (' ','.','/','-'))
      THEN
         lv_string := lv_string || SUBSTR (p_string, i, 1);
      ELSE
         lv_string := lv_string || ' ';
      END IF;

      DBMS_OUTPUT.PUT_LINE (lv_string);
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('Final String :' || lv_string);
   RETURN lv_string;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/

Script to Update Preparer/ Requester in Requisitions using API

Get data to be modified from customer and load it into a staging table. Use this table in the below script to update Preparer/ Requester for PO Requisitions.

DECLARE
l_msg_data          VARCHAR2(2000);
     l_msg_count         NUMBER;
     l_return_status     VARCHAR2(1);
     l_update_person     VARCHAR2(200);  
     l_old_personid      NUMBER;  
     l_new_personid      NUMBER;  
     l_document_type     VARCHAR2(200);  
     l_document_no_from  VARCHAR2(200);  
     l_document_no_to    VARCHAR2(200);    
     l_date_from         VARCHAR2(200);        
     l_date_to           VARCHAR2(200);
     l_commit_interval   NUMBER;
     x_date_from         DATE;
     x_date_to           DATE;
   
cursor c1 is
(
select * from  XX_req_prep_chg /*use the table with the loaded data*/

);

BEGIN
 
fnd_global.apps_initialize (/*Pass the values*/);
mo_global.set_policy_context ('S', /*Pass the value*/);
 
for r in c1
loop

   DBMS_OUTPUT.put_line ('Calling API');
 
   l_update_person      := 'ALL';  
    l_old_personid       := /*Assign the value*/;  
    l_new_personid       := /*Assign the value*/;  
    l_document_type      := NULL;  
    l_document_no_from   := r.requisition;
    l_document_no_to     := r.requisition;
    l_commit_interval    := 100;
   
   
     
             
        PO_Mass_Update_Req_GRP.Update_Persons(p_update_person    => l_update_person,
                              p_old_personid     => l_old_personid,
                                      p_new_personid     => l_new_personid,
                          p_document_type    => l_document_type,
                                      p_document_no_from => l_document_no_from,
                              p_document_no_to   => l_document_no_to,
                                      p_date_from        => x_date_from,
                              p_date_to          => x_date_to,
                                      p_commit_interval  => l_commit_interval,
                                      p_msg_data         => l_msg_data,
                                  p_msg_count        => l_msg_count,
                          p_return_status    => l_return_status);


   DBMS_OUTPUT.put_line ('API Return status: ' || l_return_status);
 
   DBMS_OUTPUT.put_line ('API p_msg_count: ' || l_msg_count);
 
   DBMS_OUTPUT.put_line ('Requisition ' || r.requisition);

commit;
 
end loop;


exception
when others then
DBMS_OUTPUT.put_line ('Inside Exception');
 
END;

Upload Catalogue item Images in iProcurement

Upload catalogue item Images in iProcurement  

                      

  Oracle iProcurement module is widely used by oracle users to raise catalog and non-catalog Purchase Requisitions. iProcurment module is very user friendly screen to raise requisitions and hence it is most used module. 

                         “Picture Speaks Louder than Words”, as famous English idiom says a picture provides more information instead of providing just letters or description of an item. Hence the user will be able to understand and choose his right product from the catalogue in iProcurement.

                        This document describes the Steps to add images in the iprocurement to show them while performing catalogue requisitions.

Benefit of this Feature:

·         Helps the user

o   to identify the appropriate product

o   to clearly identify the product

o   to avoid ordering wrong product  

Step1: Set the required profile option

                We have to set  below profile options at site level to start the process. 

1.       POR: Hosted images Directory:

Set the value to the directory where you will be storing the image files tobe used by iProcurement. Mostly the directory would be /OA_MEDIA/. Set the real path of this directory as the profile option value.

POR: Show Thumbnail image  set to Yes 

Step2: Store the images in the directory:

Keep the images in .Jpeg or .png format and store the images in “$JAVA_TOP / Oracle / apps / Media” folder. You may have to work with DBA team to store the images If you don’t have access to database.

In our example we are storing below image in the images folder

Step3: Create BPA and Map the image file name in BPA line

                Now create Blanket purchase agreement with the required item and map the image against the required item.

Enter BPA Header DetailsàClick on Line to enter Line details

Enter Line DetailsàClick on Pencil icon to enter image details

Enter image file name in Thumbnail image field as shown below àClick on apply button once the data is entered

You will get confirmation message à Submit the BPA for approval

Below confirmation message appears and ensure that the BPA is approved.

Now, BPA is in Approved Status.

Go to iProcurement Shopping pageà Enter the item Description in the Search WindowàClick Go to get see the item:

You will be able to see the item with Picture as above in the iProcurement Screen.