Landed Cost Management R12

Attached is the database schema of LCM and its integration with purchasing, receiving, inventory and costing.

View password of application user in Oracle E-Business Suite R12

Login to Apps user

Step 1:

--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

Step 2:
--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;
/

Step 3:
Query to get password for apps user.

SELECT
(SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) FROM DUAL)), usertable.encrypted_foundation_password) FROM DUAL) AS apps_password
 FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD') ,1 , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1 ) FROM DUAL))
Step 4:
 --Query for finding any application user
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';




---REFERENCE
http://muthunagaraj.blogspot.com/2012/07/steps-for-finding-password-of-user-in.html

How to run concurrent program in procedure/ trigger

req_id NUMBER;

RESULT BOOLEAN;

RESULT := APPS.fnd_request.set_mode (TRUE);

                req_id := APPS.FND_REQUEST.SUBMIT_REQUEST('FND',

                                         'FNDSCURS',

                                         '',

                                         sysdate,

                                         FALSE,

                                         :NEW.BATCH_ID);

               commit;

  

--this is sample to run the active users standard report having no parameter, you can explore the parameters by reviewing the FND_REQUEST.SUBMIT body. 

Error while ruuning OAF Hello World OAException: Message Name: SECURITY_APPL_SERVER_ID

Solution: This may occur due to the dbc file no correctly being parsed. Replace the dbc file from application server into jdeveloper required path.

Server Path: /opt/oracle/inst/apps/<SID>_<apps_node>/appl/fnd/12.0.0/secure/<SID>.dbc
Jdeveloper Path: D:\jdevhome\jdev\dbc_files\secure

Gather schema errors out after upgrade from R12.1.1 to R12.1.3

This is a known bug and solution is in the given mentioned oracle support note

11i - 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)

How to run a Concurrent Program from ZOOM button in R12

Brief Introduction to Custom Library: The CUSTOM.pll library is a standard Oracle Forms PL/SQL library that is supplied by Oracle with the Oracle Applications. This is Oracle’s built-in feature that allows the customer to enhance the standard functionality of the Applications by implementing site-specific business rules. Every Oracle Forms -based eBusiness screen, and any custom form developed using the Oracle Application development standards, will access the CUSTOM library. This makes an ideal point of creating business rules that effect the entire organization. This is the only method of forms enhancement whose functionality is supported by Oracle World Wide Support. Although any enhancements coded by the customer are not directly supported by Oracle World Wide Support.

Solution:

To run a report we have to make changes in CUSTOM.pll library which resides in $AU_TOP resource folder. To make customizations in CUSTOM.pll we have to open the CUSTOM.pll in form builder.

Then to make code enable for ZOOM button is to be written in CUSTOM.pll to write the code library is to be opened in Forms builder.

After compiling the library place it on the server and compile it again with the following code

Source the environment variable.

. frmcmp_batch.sh module=/opt/oracle/apps/apps_st/appl/au/12.0.0/resource/CUSTOM.pll userid=apps/apps output_file=/opt/oracle/apps/apps_st/appl/au/12.0.0/resource/CUSTOM.plx module_type=library compile_all=special

Note: You have to re login to see the changes.

Code to Run the Concurrent Program:

To run the reports we will have to write the code in event procedure of CUSTOM.pll the code is as following:

procedure event(event_name varchar2) is

form_name            varchar2(30) := name_in('system.current_form');

  block_name          varchar2(30) := name_in('system.cursor_block');

  lv_param_p1         varchar2(30);

  lv_param_p2         varchar2(30);

  request_id          NUMBER;

  timer_id            timer;

  parm_request_id     number;

  progress_no         number;

  v_layout            boolean;

  l_wait                            boolean;

  x_Phase             Varchar2(100);

  x_Status            Varchar2(100);

  x_Dev_Phase         Varchar2(100);

  x_Dev_Status        Varchar2(100);

  x_Message           Varchar2(100); 

  begin

   

    if (event_name = 'ZOOM') then

    if (form_name = 'ARXTWMAI' and block_name = 'TGW_HEADER') THEN

   

    lv_param_p1 := name_in('tgw_header.TRX_NUMBER_MIR'); --- This is the parameter that is from standard form into the report parameter

   

    v_layout :=FND_REQUEST.add_layout('AR','USA_AR_INVOICE_VOUCHER','en','US','PDF',NULL); -- This is to set the layout

   

    request_id :=  fnd_request.submit_request('AR','USA_AR_INVOICE_VOUCHER','Cust Export Sale  Voucher',SYSDATE,FALSE,'','','',lv_param_p1,lv_param_p1,'','','','','');  --- This will submit the concurrent request to execute the report. Parameters in sequence to execute the report.

   

    COMMIT; --- commit must be used when ever a concurrent parogram is run customized

    clear_message;

     If request_id Is Null Or

       request_id = 0 Then

      Return;

     End If;

    

    

       fnd_message.set_string('Request Submitted :' || request_id ||'. Please Wait ..... ');

       fnd_message.show;

        SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'BUSY');

        APP_WINDOW.PROGRESS(0);

       

        l_wait := Fnd_Concurrent.Wait_For_Request(request_id,1,0,x_Phase,x_Status,x_Dev_Phase,x_Dev_Status,x_Message);

       

          If x_Dev_Phase = 'COMPLETE' THEN --- And x_Dev_Status = 'NORMAL' Then

            

             --    message(x_Dev_Phase);

            

Editor_Pkg.Report(request_id,'Y'); --- This resides in FNDCON.pll library you will have to add this library to CUSTIOM.pll so the compile the library otherwise it will not compile. Shown in figure at the end.

           Else

                              Fnd_File.Put_Line(1,Rpad('x_phase',20,' ') || '=' || x_Phase);

                              Fnd_File.Put_Line(1,Rpad('x_status=',20,' ') || '=' || x_Status);

                              Fnd_File.Put_Line(1,Rpad('x_dev_phase=',20,' ') || '=' || x_Dev_Phase);

                              Fnd_File.Put_Line(1,Rpad('x_dev_status=',20,' ') || '=' || x_Dev_Status);

                              Fnd_File.Put_Line(1,Rpad('x_message=',20,' ') || '=' || x_Message);

                              Fnd_File.Put_Line(1,'Warning : Starting print report failure! It did not running in 120 seconds, its request_id is ' ||

                                                To_Char(request_id) || ' please check it.');

    End If;

     

        SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT'); 

        APP_WINDOW.PROGRESS(1);

       

       

                

        end if;   

 

    end if;   

 

  end event;

This will execute your required report but report will be complete in warning and report must be added to that request group.

Press Yes in above figure.

Difference between ad_bugs and ad_applied_patches

Bug Definition: A software bug is an error, flaw, failure, or fault in a computer program or system that produces an incorrect or unexpected result, or causes it to behave in unintended ways. 

In oracle e-business suite when some standard functionality is not working correctly or as per mentioned in documentation there is a bug and for bug oracle support recommends patch. One single patch can fix a single or multiple bugs.

AD_BUGS
All the bugs whose fixes has been applied before that release i-e patches applied in Oracle Applications Installation. Upon successful installation many entries exists in this table.

AD_APPLIED_PATCHES
It holds the information of patches applied after installation. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.

So whenever a patch is applied, entries occur in both tables.