Query : user Password

 --Package Specification

CREATE OR REPLACE PACKAGE get_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_pwd;

/

--Package Body

CREATE OR REPLACE PACKAGE BODY get_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2

   AS

      LANGUAGE JAVA

      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

END get_pwd;

/

--Query to execute

SELECT usr.user_name,

       get_pwd.decrypt

          ((SELECT (SELECT get_pwd.decrypt

                              (fnd_web_sec.get_guest_username_pwd,

                               usertable.encrypted_foundation_password

                              )

                      FROM DUAL) AS apps_password

              FROM fnd_user usertable

             WHERE usertable.user_name =

                      (SELECT SUBSTR

                                  (fnd_web_sec.get_guest_username_pwd,

                                   1,

                                     INSTR

                                          (fnd_web_sec.get_guest_username_pwd,

                                           '/'

                                          )

                                   - 1

                                  )

                         FROM DUAL)),

           usr.encrypted_user_password

          ) PASSWORD

  FROM fnd_user usr

 WHERE usr.user_name = ':USER_NAME';

EBS: Customer SITE USE API : How to update Customer Site Use using API : Oracle APPS

EBS: Customer SITE USE API : How to update Customer Site Use using API

DECLARE

   lv_cust_site_use_rec        hz_cust_account_site_v2pub.cust_site_use_rec_type;

   p_customer_profile_rec      hz_customer_profile_v2pub.customer_profile_rec_type;

   x_site_use_id               NUMBER;

   x_return_status             VARCHAR2 (2000);

   x_msg_count                 NUMBER;

   x_msg_data                  VARCHAR2 (2000);

   l_salesrep_id               NUMBER;

   l_bill_site_use_id          NUMBER;

   l_p_object_version_number   NUMBER;

   l_site_use_id               NUMBER;

   l_cust_account_id           NUMBER;

   v_org_id                    NUMBER                                  := null;

   v_price_list                VARCHAR2 (30)                     DEFAULT NULL;

   CURSOR cur_contr

   IS

      SELECT DISTINCT hca.account_number, hca.account_name,

                      hps.party_site_number, hcas.attribute1 duns_number,

                      hcas.cust_acct_site_id, hcas.status, hcas.org_id,

                      hcsu.site_use_code, hcsu.site_use_id,

                      hcsu.object_version_number, rt.NAME, rt.description,

                      hcsu.fob_point, hl.country

                 FROM hz_cust_accounts_all hca,

                      hz_cust_acct_sites_all hcas,

                      hz_party_sites hps,

                      hz_cust_site_uses_all hcsu,

                      ra_terms rt,

                      hz_locations hl

                WHERE hca.cust_account_id = hcas.cust_account_id

                  AND hcas.party_site_id = hps.party_site_id

                  AND hca.account_number = hca.account_number

                  AND hcas.org_id IN (103)

                  AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id

                  AND hcsu.payment_term_id = rt.term_id

                  AND hps.location_id = hl.location_id                 

                  AND hca.account_number IN

                         (

                          '5175852'

                         );

BEGIN

  

   FOR rec_cur IN cur_contr

   LOOP

      mo_global.init ('AR');

      mo_global.set_policy_context ('S', rec_cur.org_id);

      --

      lv_cust_site_use_rec.cust_acct_site_id := rec_cur.cust_acct_site_id;

      lv_cust_site_use_rec.site_use_id := rec_cur.site_use_id;

      lv_cust_site_use_rec.payment_term_id := 100;

      ---

      

      hz_cust_account_site_v2pub.update_cust_site_use

                        (p_init_msg_list              => 'T',

                         lv_cust_site_use_rec         => lv_cust_site_use_rec,

                         p_object_version_number      => rec_cur.object_version_number,

                         x_return_status              => x_return_status,

                         x_msg_count                  => x_msg_count,

                         x_msg_data                   => x_msg_data

                        );

      IF x_return_status = fnd_api.g_ret_sts_success

      THEN

         COMMIT;

         DBMS_OUTPUT.put_line (   'Status  : '

                               || x_return_status

                               || '  Error Msg :  '

                               || x_msg_data

                              );

         DBMS_OUTPUT.put_line (   'Updated Customer Site '

                               || rec_cur.cust_acct_site_id

                               || '- '

                               || rec_cur.site_use_id

                              );

      ELSE

         DBMS_OUTPUT.put_line (   'Updation of Customer Account got failed:'

                               || x_msg_data

                              );

         ROLLBACK;

         FOR i IN 1 .. x_msg_count

         LOOP

            x_msg_data :=

                         fnd_msg_pub.get (p_msg_index      => i,

                                          p_encoded        => 'F');

            DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);

         END LOOP;

      END IF;

   END LOOP;

END;

EBS : Forms Personalization : Special Trigger Notes

 EBS : Forms Personalization : Special Trigger Notes

You can enable SPECIAL1-15 or MENU1-15, to create new menu entries under the Tool menu. The above two setups have instantiated the new Menu Items SPECIAL16-30 will create entries under Reports menu SPECIAL31-45 will create entries under Actions menu

Oracle PLSQL : Bulk Collect

 Oracle PLSQL : Bulk Collect Example

declare

Cursor cur_gl_stg is

Select  je_header_id,name,description from gl_je_headers where trunc(creation_date) >= '01-APR-2023';

 

TYPE cur_gl_stg_type IS TABLE OF cur_gl_stg%rowtype INDEX BY PLS_INTEGER;

v_gl_stg_type    cur_gl_stg_type;

l_index_cnt Number:= 0;

 

Begin

 OPEN cur_gl_stg; ---opening cursor

 loop --loop start

   l_index_cnt := l_index_cnt+1;

   FETCH cur_gl_stg

   BULK COLLECT INTO v_gl_stg_type LIMIT 30;

   EXIT WHEN v_gl_stg_type.COUNT = 0;

   dbms_output.put_line('l_index_cnt:'||l_index_cnt||','||v_gl_stg_type.count);

  

    FORALL indx IN 1 .. v_gl_stg_type.COUNT

        UPDATE gl_je_headers

      Set description = description||'BulkCollect'

      where je_header_id =v_gl_stg_type(indx).je_header_id ;

   

       dbms_output.put_line('Rowcount:'||SQL%ROWCOUNT||','||v_gl_stg_type.count);

     

 end loop;

 

    dbms_output.put_line('Total count:'||v_gl_stg_type.count);

 /*for i in 1..v_gl_stg_type.count loop

  dbms_output.put_line('Name :'||v_gl_stg_type(i).name);

 end loop;*/

 

exception when others then

   dbms_output.put_line('Error main :'||SQLERRM);

null;

End;      

EBS : OAF Personalization : How to add Custom Fields in Supplier Page Purchasing TAB (Oracle APPS R12)

How to add Custom Fields in Supplier Page Purchasing TAB using OAF Personalization in R12

Step1: Go to Functional Administrator-> click on Personalization TAB

Step2: Enter Document Path as “/oracle/apps/pos/supplier/webui/ByrPurchPG”  and click on GO Button. Click on Personalize Page “Pencil Icon”.

Step3: Select “Complete” and click on “Expand All”.

Step4: Search for “Table: Purchasing“ and Click on “Create Item” as highlighted below

Step5: Enter Following details as mentioned in below table as shown in below screenshot

Property	Value
*ID	XX_Custom_fields
Prompt	XX_Custom_fields
View Attribute	Attribute4
View Instance	SitesVO

Step6: And click on Apply to save the records.

EBS : Oracle Apps GL Important Query

 Oracle APPS GL Query

• Query to get GL Daily rates

select * from apps.gl_daily_rates

where trunc(conversion_date) ='22-MAR-2023'

and from_currency='EUR' and TO_CURRENCY ='USD'

and conversion_type ='1002'

order by 3 desc;

EBS : Oracle PLSQL : File Handling using UTL

 File Handling using UTL in Oracle PLSQL

• How to check file avilabilty in the directory

UTL_FILE.fgetattr(v_dir_path, v_file_name, v_exists, v_f_len, v_bsize);

• remove the existing file from the directory 

 UTL_FILE.fremove (v_location,  v_file_name )

ORACLE PLSQL : Nested Block Example

 PLSQL : Nested Block Example

declare

l_main_block varchar2(100) := 'MAIN_BLOCK';

begin

    dbms_output.put_line('Main Block:'||l_main_block);

    dbms_output.put_line('Nested Block inside min Block:'||l_nested_block);

 

  ----------------------------Nested block---------------

 

  declare

  l_nested_block varchar2(100) := 'NESTED_BLOCK';

  begin

      dbms_output.put_line('nested Block:'||l_nested_block); --to write another business logic

      dbms_output.put_line('Main Block inside nested Block:'||l_main_block);

  end;   

  ---------------------------end -Nested block---------------

 

exception when others then

 dbms_output.put_line('Error:'||SQLERRM);

end;

EBS: API to get GL Account Code Description : GL

  API to get GL Account Code Description

APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  2,
                  GCC.SEGMENT2
               )

EBS :Query to find Concurrent Program Responsibility name

 ---------Query to find Conccurrent Program Responsibility name—

SELECT fav.APPLICATION_NAME,frq.REQUEST_GROUP_NAME,request_unit_id,frq.*

  FROM fnd_request_group_units frqu, fnd_request_groups frq, fnd_application_vl fav, fnd_concurrent_programs

WHERE 1=1--request_unit_id = 183400  ---conc prog id/request setid

and frqu.request_group_id=frq.request_group_id

and request_unit_id =CONCURRENT_PROGRAM_ID

and CONCURRENT_PROGRAM_NAME ='STATMENT_EMAIL'

and frq.application_id = fav.APPLICATION_ID

EBS : How to remove leading zero in Excel Output report (Oracle BI Publisher Report)

EBS : How to remove leading zero in Excel Output report (Oracle BI Publisher Report)

Step1: Go to RTF Template of that particular fields

Step2: Add below code in that fields

"<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?ITEM_CODE?></fo:bidi-override>"