Useful SQL Queries to validate FND(Foundation)(Conc. Program, Req Set etc.,) and XDO(Xml Data Objects) Objects(RTF file, Template)

-- Select statement to identify which a concurrent_program

-- Parameters:

--        p_conc_prog - Concurrent Program Short Name

 

SELECT cp.user_concurrent_program_name, cp.concurrent_program_name

  FROM fnd_concurrent_programs_vl cp

 WHERE cp.concurrent_program_name = '&p_conc_prog';

 

-- Select statement to identify which request group is associated to a particular concurrent_program

-- Parameters:

--        p_conc_prog - Concurrent Program Name

--        p_req_group - Request Group Name

 

SELECT cp.user_concurrent_program_name,

       cp.concurrent_program_name,

       rg.request_group_name

  FROM fnd_request_group_units rgu,

       fnd_request_groups rg,

       fnd_concurrent_programs_vl cp

 WHERE     cp.concurrent_program_id = rgu.request_unit_id

       AND rg.request_group_id = rgu.request_group_id

       AND cp.concurrent_program_name = '&p_conc_prog'

       AND rg.request_group_name = '&p_req_group';

 

  -- Select statement to identify Request set and it's corresponding stages and stage links for a particular request set

-- Parameters:

--        p_req_set_short_name - Request Set Name

 

SELECT rs.request_set_name,

       rs.user_request_set_name,

       sv.stage_name,

       sv.request_set_stage_id,

       sv.success_link,

       sv.success_link_name,

       sv.warning_link,

       sv.warning_link_name,

       sv.error_link,

       sv.error_link_name,

       sv.display_sequence,

       sv.user_executable_name,

       sv.user_stage_name

  FROM apps.fnd_request_sets_vl rs, fnd_req_set_stages_form_v sv

 WHERE rs.request_set_name = UPPER('&p_req_set_short_name')

       AND sv.request_set_id = rs.request_set_id;

 

xx_ar_std_register_trans

 

-- Select statement to identify which request group is associated to a particular request set

-- Parameters:

--        p_req_set - Request Set Name

--        p_req_group - Request Group Name

 

SELECT cp.request_set_name, cp.user_request_set_name, rg.request_group_name

  FROM fnd_request_group_units rgu,

       fnd_request_groups rg,

       fnd_request_sets_vl cp

 WHERE     cp.request_set_id = rgu.request_unit_id

       AND rg.request_group_id = rgu.request_group_id

       AND cp.request_set_name = '&p_req_set'

       AND rg.request_group_name = '&p_req_group';

 

  --Select statement to validate RTF Template

  --Parameter

  --     p_template_code - Template Code

 

SELECT xl.lob_type,

       xl.application_short_name,

       xl.lob_code,

       xl.language,

       xl.file_name,

       xl.xdo_file_type,

       xl.file_content_type,

       xl.file_data,

       xl.program,

       xl.territory,

       xt.template_code,

       xt.data_source_code

  FROM xdo_lobs xl, xdo_templates_b xt

 WHERE xt.template_code = xl.lob_code(+)

       AND template_code = '&p_template_code';

 

--Select statement to validate Forms Personalization

-- Parameter

--    p_func_name - Form Function Name

 

SELECT ff.user_function_name,

       ff.user_function_name,

       ff.function_id,

       cr.description,

       cr.trigger_event,

       cr.trigger_object,

       cr.condition,

       cr.sequence,

       cr.form_name

  FROM fnd_form_custom_rules cr, fnd_form_functions_vl ff

 WHERE ff.function_name = cr.function_name

       AND ff.function_name = '&p_func_name';

 

 -- Select statement to validate Custom Profile

-- Parameter:

--       p_prof_name -- Profile Option Name

 

SELECT po.profile_option_name,

       po.user_profile_option_name,

       pov.profile_option_value

  FROM fnd_profile_options_vl po, fnd_profile_option_values pov

 WHERE pov.profile_option_id = po.profile_option_id

       AND po.profile_option_name LIKE UPPER ('&p_prof_name%');

 

-- Select statement to identify association of Form function to a particular Menu as a menu entry

-- Parameters:

--        p_menu_name - Menu Name

--        p_function_name - Function Name

 

SELECT mv.menu_name,

       ff.function_name,

       ff.user_function_name,

       me.prompt,

       me.description Entry_Description

  FROM fnd_menus_vl mv, fnd_menu_entries_vl me, fnd_form_functions_vl ff

 WHERE     mv.menu_id = me.menu_id

       AND mv.menu_name = '&p_menu_name'

       AND ff.function_id = me.function_id

       AND ff.function_name = '&p_function_name';

How to select user and responsibility details associated to an Application(FND) user

-- Query to derive responsibility/responsibilities associated to an Application(FND) user/users

Parameters:-

1. Application(FND) User Name

2. Responsibility Name
 

  SELECT u.user_name,

              u.start_date user_start_date,

              u.end_date user_end_date,

              u.email_address user_email_address,

              r.responsibility_name,

              g.start_date resp_assign_start_date,

              g.end_date resp_assign_end_date,

              g.user_id,

              r.responsibility_id

    FROM apps.fnd_user u,

             apps.fnd_user_resp_groups g,

             apps.fnd_responsibility_tl r

   WHERE     1 = 1

        AND r.responsibility_id = g.responsibility_id

        AND g.user_id = u.user_id

        AND u.user_name = NVL(:p_user_name, u.user_name)

        AND r.responsibility_name = NVL(:p_responsibility_name, r.responsibility_name)

ORDER BY u.user_name, r.responsibility_name DESC;

How to fetch Aging Bucket line details for a delinquent Customer account

Following Select statement will fetch Aging bucket line details for a particular Customer to send dunning letter:-

Parameters:

•   Dunning Plan Name
•   Customer Account Number

SELECT l.aging_bucket_line_id,
                l.type Aging_Bucket_Line_Type,
                l.days_start,
                l.days_to
  FROM ar_aging_bucket_lines l,
              ar_aging_buckets b,
              iex_dunning_plans_vl d
 WHERE 1 = 1
   AND d.name = :p_dunning_plan_name   
   AND d.aging_bucket_id = b.aging_bucket_id
   AND b.aging_bucket_id = l.aging_bucket_id
   AND (SELECT MAX (TRUNC (SYSDATE) - TRUNC (ar.due_date)) days
                 FROM iex_delinquencies del,
                             ar_payment_schedules ar,
                             hz_cust_accounts hca
             WHERE 1 = 1
             AND hca.cust_account_id = del.cust_account_id
             AND hca.account_number = :p_account_number
            AND del.payment_schedule_id = ar.payment_schedule_id
            AND del.status IN ('DELINQUENT', 'PREDELINQUENT'))
            BETWEEN l.days_start AND l.days_to
            AND EXISTS                    (SELECT 1
                     FROM iex_ag_dn_xref x
                     WHERE 1=1
                      AND d.dunning_plan_id = x.dunning_plan_id
                      AND d.aging_bucket_id = x.aging_bucket_id
                      AND x.aging_bucket_line_id = l.aging_bucket_line_id);

How to delete a Concurrent Program and Concurrent Executable

Concurrent program cannot be deleted from Oracle Applications front-end, it can only be disabled.

Try to delete a concurrent program from Oracle Applications front-end:-

 

 

 

 

 

 

 

                                                                                             

 

Execute the following database script using fnd_program.delete_program to delete Concurrent program programmatically:-
 

 

  BEGIN
       fnd_program.delete_program (program_short_name => 'XX_CP', -- Conc. Executable Short Name
                                                  application => 'AR' -- Program Application Short Name
                                                           );
    COMMIT;
END;

 

After the program deletion script is executed, try to query the Concurrent Program from Oracle Applications front-end:-

Steps:- F11, Enter Program: XX Concurrent Program, Ctrl + F11

Note: - The concurrent program is deleted

 

The Concurrent executable can be deleted from the Oracle Applications front-end after the associated Concurrent Program is deleted or it can be deleted by using a database script:-

Press on “Delete” icon and save to delete Concurrent executable:-

 

 

 

 

 

 

                                                                                                                                                                      

Following database script using fnd_program.delete_executable can be used to delete Concurrent executable programmatically:-

BEGIN
  fnd_program.delete_executable (executable_short_name => 'XX_EXEC', -- Conc. Executable Short Name
                                                 application => 'AR' -- Program Application Short Name
                                                    );
   COMMIT;
END;

 

 

After the executable deletion script is executed in the database, try to query the Concurrent Program from Oracle Applications front-end:-

Steps:- F11, Enter Executable: XX_EXEC, Ctrl + F11

Note: - The concurrent executable is deleted

SQL Query to display Current Statement of Active Session/Program

Following SQL query will fetch "Current Statement" for the active session passed as parameter:-

 Parameters:-

     p_inst_id -- Instance Id
     p_sid      -- Session Id
 
     SELECT s.sid,
       s.client_info,
       s.machine,
       s.program,
       s.type,
       s.logon_time,
       s.osuser,
       sq.sorts,
       sq.disk_reads,
       sq.buffer_gets,
       sq.rows_processed,
       sq.sqltype,
       sq.sql_text
  FROM gv$session s,
               gv$sql sq
 WHERE s.sql_hash_value = sq.hash_value
   AND s.inst_id = :p_inst_id           -- replace with ID from above
   AND s.sid = :p_sid                   -- replace with instID from above
   AND sq.inst_id = s.inst_id;

 

 -- Select statement which provides information on sessions and queries which cause maximum disk reads and waits

 

  SELECT SUBSTR (ss.username, 1, 8) username,
         ss.osuser "USER",
         ar.module || ' @ ' || ss.machine client,
         ss.process pid,
         TO_CHAR (ar.last_load_time, 'DD-Mon HH24:MM:SS') load_time,
         ar.disk_reads disk_reads,
         ar.buffer_gets buffer_gets,
         SUBSTR (ss.lockwait, 1, 10) lockwait,
         w.event event,
         ss.status,
         ar.sql_fulltext sql
    FROM v$session_wait w,
         v$sqlarea ar,
         v$session ss,
         v$timer t
   WHERE     ss.sql_address = ar.address
         AND ss.sql_hash_value = ar.hash_value
         AND ss.sid = w.sid(+)
         AND ss.status = 'ACTIVE'
         AND w.event != 'client message'
ORDER BY ss.lockwait ASC, ss.username, ar.disk_reads DESC;

Select statement with Bill-To & Ship-To address for AR Open Transactions

-- Select Statement which details the AR Open Transactions' Bill-To, Ship-To and Paying Site details along with accounting combinations

SELECT sob.name sob_name,
         hou.name ou_name,
         t.trx_number,
         hp.party_name,
         acct.account_number,
         rctt.name Transaction_type,
         t.bill_to_site_use_id,
            hl.address1
         || ','
         || hl.address2
         || ','
         || hl.city
         || ','
         || hl.county
         || ','
         || NVL (hl.state, hl.province)
            bill_to_address,
            hl1.address1
         || ','
         || hl1.address2
         || ','
         || hl1.city
         || ','
         || hl1.county
         || ','
         || NVL (hl1.state, hl.province)
            ship_to_address,
            hl1.address1
         || ','
         || hl1.address2
         || ','
         || hl1.city
         || ','
         || hl1.county
         || ','
         || NVL (hl1.state, hl.province)
            paying_cust_address,
         l.customer_trx_line_id,
         t.customer_trx_id,
         l.line_number,
         l.line_type,
         l.reason_code,
         l.quantity_invoiced,
         l.inventory_item_id,
         l.description,
         l.uom_code,
         l.unit_selling_price,
         (d.amount) extended_amount,
         p.amount_due_remaining balance_due,
         gcc.segment1,
         gcc.segment2,
         gcc.segment3,
         gcc.segment4,
         gcc.segment5,
         gcc.segment6,
         gcc.segment7,
         gcc.segment8,
         d.account_class,
         d.gl_date,
         d.acctd_amount,
         sp.salesrep_number,
         s.revenue_percent_split,
         l.link_to_cust_trx_line_id
FROM ra_customer_trx_all t,
         ar_payment_schedules_all p,
         gl_sets_of_books sob,
         hr_operating_units hou,
         ra_customer_trx_lines_all l,
         ra_cust_trx_line_gl_dist_all d,
         gl_code_combinations gcc,
         ra_cust_trx_line_salesreps_all s,
         jtf_rs_salesreps sp,
         ra_cust_trx_types_all rctt,
         ra_terms rt,
         hz_parties hp,
         hz_cust_site_uses_all hcsu,
         hz_cust_acct_sites_all hcas,
         hz_cust_accounts acct,
         hz_party_sites hps,
         hz_locations hl,
         hz_cust_site_uses_all hcsu1,
         hz_cust_acct_sites_all hcas1,
         hz_party_sites hps1,
         hz_locations hl1,
         hz_cust_accounts acct_pay,
         hz_cust_site_uses_all hcsu2,
         hz_cust_acct_sites_all hcas2,
         hz_party_sites hps2,
         hz_locations hl2
   WHERE     t.customer_trx_id = p.customer_trx_id
         AND p.status = 'OP'
         AND rctt.org_id = t.org_id
         AND t.set_of_books_id = sob.set_of_books_id
         AND t.org_id = hou.organization_id
         AND t.customer_trx_id = l.customer_trx_id
         AND t.cust_trx_type_id = rctt.cust_trx_type_id
         AND d.customer_trx_id = l.customer_trx_id
         AND d.customer_trx_line_id = l.customer_trx_line_id
         AND d.code_combination_id = gcc.code_combination_id
         AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
         AND s.customer_trx_id(+) = t.customer_trx_id
         AND s.salesrep_id = sp.salesrep_id(+)
         AND s.org_id = sp.org_id(+)
         AND t.term_id = rt.term_id(+)
         AND acct.party_id = hp.party_id
         AND t.bill_to_customer_id = acct.cust_account_id
         AND t.bill_to_site_use_id = hcsu.site_use_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND acct.cust_account_id = hcas.cust_account_id
         AND hcsu.site_use_code = 'BILL_TO'
         AND hcas.party_site_id = hps.party_site_id
         AND hps.location_id = hl.location_id
         AND t.ship_to_site_use_id = hcsu1.site_use_id(+)
         AND hcas1.cust_acct_site_id(+) = hcsu1.cust_acct_site_id
         AND p.class IN ('INV', 'DM')
         AND hcsu1.site_use_code(+) = 'SHIP_TO'
         AND hcas1.party_site_id = hps1.party_site_id(+)
         AND hps1.location_id = hl1.location_id(+)
         AND t.paying_customer_id = acct_pay.cust_account_id(+)
         AND t.paying_site_use_id = hcsu2.site_use_id(+)
         AND hcas2.cust_acct_site_id(+) = hcsu2.cust_acct_site_id
         AND hcas2.party_site_id = hps2.party_site_id(+)
         AND NVL (hcsu2.site_use_code, 'BILL_TO') = 'BILL_TO'
         AND hps2.location_id = hl2.location_id(+)
         AND EXISTS
                (SELECT 1
                   FROM ra_customer_trx_all t,
                        ar_payment_schedules_all p1,
                        gl_sets_of_books sob,
                        hr_operating_units hou,
                        hz_cust_accounts acct_bill_to,
                        hz_cust_accounts acct_pay,
                        jtf_rs_salesreps sp,
                        ra_terms rt,
                        ra_cust_trx_types_all rctt
                  WHERE     t.customer_trx_id = p1.customer_trx_id
                        AND p1.status = 'OP'
                        AND p1.customer_trx_id = p.customer_trx_id
                        AND t.set_of_books_id = sob.set_of_books_id
                        AND t.org_id = hou.organization_id
                        AND t.bill_to_customer_id =
                               acct_bill_to.cust_account_id
                        AND t.paying_customer_id = acct_pay.cust_account_id(+)
                        AND t.primary_salesrep_id = sp.salesrep_id(+)
                        AND t.org_id = sp.org_id(+)
                        AND t.term_id = rt.term_id(+)
                        AND t.cust_trx_type_id = rctt.cust_trx_type_id
                        AND t.org_id = rctt.org_id)
ORDER BY trx_number, customer_trx_line_id;