Backend Queries to get Frontend Details

-
a)Query to get executable_name ,execution_method_code from concurrent program name

SELECT fcpt.user_concurrent_program_name,
       fcp.concurrent_program_name,
       fe.execution_file_name,
       DECODE (fe.execution_method_code,
                 'A', 'Spawned',
                 'B', 'Request Set Stage Function',
                 'E', 'Perl Concurrent Program',
                 'H', 'Host',
                 'I', 'PL/SQL Stored Procedure',
                 'J', 'Java Stored Procedure',
                 'K', 'Java Concurrent Program',
                 'L', 'SQL*Loader',
                 'M', 'Multi Language Function',
                 'P', 'Oracle Reports',
                 'Q', 'SQL*Plus',
                 'S', 'Immediate',
                 'Other') as execution_method_code
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_programs fcp,
       fnd_executables fe
 WHERE     fcpt.concurrent_program_id = fcp.concurrent_program_id
       AND fcp.executable_id = fe.executable_id
       AND UPPER (user_concurrent_program_name) LIKE
              '%<CONCURRENT PROGRAM NAME IN UPPER CASE>%'

b)Query to get responsibility for a concurrent program

  SELECT frgu.REQUEST_UNIT_ID,
         frt.responsibility_name,
         frg.request_group_name,
         frgu.request_unit_type,
         frgu.request_unit_id,
         fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr,
         fnd_responsibility_tl frt,
         fnd_request_groups frg,
         fnd_request_group_units frgu,
         fnd_concurrent_programs_tl fcpt
   WHERE     frt.responsibility_id = fr.responsibility_id
         AND frg.request_group_id = fr.request_group_id
         AND frgu.request_group_id = frg.request_group_id
         AND fcpt.concurrent_program_id = frgu.request_unit_id
         AND fcpt.user_concurrent_program_name = 'CP_NAME'
ORDER BY 1,
         2,
         3,
         4  

Comments

Post a Comment

Popular posts from this blog

HOW TO auto approve workflow WF notification procedure

-
API to approve workflow notifications using a procedure in Oracle Apps R12 API: wf_notification Oracle Workflow communicates with users by sending notifications . Notifications contain messages that may request users to take some type of action and/or provide users with information. You define the notification activity and the notification message that the notification activity sends in the Workflow Builder. --To auto approve workflow notifications using a procedure. PROCEDURE approve_reject_proc (pAction IN VARCHAR2, --APPROVE/REJECT pComments IN VARCHAR2, -- User Comments -- Optional pNotification_id IN NUMBER, -- Notification ID pStatus OUT VARCHAR2, pMessage OUT VARCHAR2 ) IS l_user_name VARCHAR2(250); BEGIN BEGIN SELECT RECIPIENT_ROLE INTO l_user_name FROM WF_NOTIFICATIONS WHERE notification_id = pNotification_id; EXCEPTION WHEN OTHERS THEN l_user_name := NULL; END; wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction); wf_notification.Respond(pNotif...
Read more

HOW TO register table in oracle apps

-
API to register table in Oracle Apps R12 API: ad_dd.register_table You register your custom application tables using a PL/SQL routine in the AD_DD package. Flexfields and Oracle Alert are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert. You can also use the AD_DD API to delete the registrations of tables and columns. You should delete the column registration first, then the table registration. To alter a registration you should first delete the registration, then re-register the table or column.  The AD_DD API does NOT check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need NOT register views. You should include call...
Read more

HOW TO update an existing user in oracle apps

-
API to create Accounting Key Flexfield Concatenated Segment description in Oracle Apps R12 API: GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION Update an existing Oracle User in R12 Oracle Apps using API -- FND_USER_PKG.UPDATEUSER We will try to update the email address for an existing user ‘DEMOUSER’ using the API.  There was no email_address populated to the user ‘DEMOUSER’ prior to running the API. DECLARE    v_user_name     VARCHAR2 (100)    := 'WIPUSER';    v_email_address  VARCHAR2 (100) := 'test@WIPRO.com'; BEGIN    fnd_user_pkg.updateuser                            (x_user_name                    => v_user_name,          ...
Read more