Copying data between development instances

COPY FROM apps/pwd@inst1 TO apps/pwd@inst2 INSERT xx.xxamg_ar_invoice_interface_all USING SELECT * FROM xx.xxamg_ar_invoice_interface_all;


This will not work for 'appsread' user

How to Trace a Concurrent Request And Generate TKPROF File

 Enable Tracing For The Concurrent Manager  Program

    Responsibility: System Administrator
    Navigate: Concurrent > Program > Define
    Query Concurrent Program
    Select the Enable Trace Checkbox

Turn On Tracing

    Responsibility: System Administrator
    Navigate: Profiles > System
    Query Profile Option Concurrent: Allow Debugging
    Set profile to Yes

 Run Concurrent Program With Tracing Turned On

    Logon to the Responsibility that runs the Concurrent Program
     In the Submit Request Screen click on Debug Options (B)
    Select the Checkbox for SQL Trace

 2. Find Trace File Name

  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    FROM
    fnd_concurrent_requests req
    ,v$session ses
    ,v$process proc
    ,v$parameter dest
    ,v$parameter dbnm
    ,fnd_concurrent_programs_vl prog
    ,fnd_executables execname
    WHERE 1=1
    AND req.request_id = &request --Request ID

/opt/oracle/enable/admin/ENBL1/udump  --> trace file location

3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof enbl2_ora_23852_837166_CR1814778.trc /home/vk837166/837166_CR1814778 explain=apps/nobel4pce sort=(exeela,fchela) sys=no
Where: raw_trace_file.trc: Name of trace file

output_file: tkprof out file

explain: This option provides the explain plan for the sql                      statements

sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.

sys=no:Disables sql statements issued by user SYS

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10? long running queries





$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort=’(prsela,exeela,fchela)’ print=10

Enabling FND debug

Set these at the user level

1) FND: Debug Log Level --> set to statement level

2)  FND: Debug Log Enabled --> Yes

3)  FND: Debug Log Module --> %

4) After reproducing the issue
select * from fnd_log_messages
where user_id =
and timestamp > sysdate-3
order by log_sequence desc

Useful SQL queries (FND)

Query for Profile Options (This will work in read only access too)
SELECT fpot.user_profile_option_name AS "USER_PROFILE_OPTION_NAME",
       DECODE (level_id,
               '10001', 'Site',
               '10002', 'Application',
               '10003', 'Responsibility',
               '10004', 'User',
               '10005', 'Server',
               '10007', 'Organization'
              ) AS "LEVEL",
       fpov.profile_option_value AS "Value", fpo.profile_option_name,
       SUBSTR (fat.application_name, 1, 20) AS "Application",
       (SELECT responsibility_name
          FROM fnd_responsibility_tl frt
         WHERE frt.responsibility_id = fpov.level_value
           AND fpov.level_id = 10003) "Responsibility",
       (SELECT user_name
          FROM fnd_user fu
         WHERE user_id = fpov.level_value
           AND fpov.level_id = 10004) "User"
  FROM fnd_profile_options fpo,
       fnd_profile_options_tl fpot,
       fnd_application_tl fat,
       fnd_profile_option_values fpov
WHERE fpo.application_id = fat.application_id
   AND fpov.application_id = fat.application_id
   AND fpov.profile_option_id = fpo.profile_option_id
   AND fpo.profile_option_name = fpot.profile_option_name
   AND fpov.level_id = NVL (:p_level_id, fpov.level_id)
   AND UPPER (fpot.user_profile_option_name) LIKE
          NVL (UPPER ('%&User_Profile_Option_Name%'),
               UPPER (fpot.user_profile_option_name)
              );

------------Calculating time taken to complete execution of the program---------------------------
select  user_concurrent_program_name, actual_start_date,actual_completion_date
 ,trunc(86400*(actual_completion_date - actual_start_date ))-60*(trunc((86400*(actual_completion_date - actual_start_date ))/60)) "Sec"
 ,trunc((86400*(actual_completion_date - actual_start_date ))/60)-60*(trunc(((86400*(actual_completion_date - actual_start_date ))/60)/60)) "Min"
 ,trunc(((86400*(actual_completion_date - actual_start_date ))/60)/60)-24*(trunc((((86400*(actual_completion_date - actual_start_date ))/60)/60)/24)) "Hrs"
 ,r.argument_text,r.request_id,r.parent_request_id   
  from apps.fnd_concurrent_programs_vl p
       ,apps.fnd_executables e
       ,apps.fnd_lookup_values flv
       ,apps.fnd_concurrent_requests r
       ,apps.fnd_responsibility_vl resp
       ,apps.fnd_user fu
 where 1=1
   and p.user_concurrent_program_name like '%CP%' 
   and p.executable_id = e.executable_id
   and r.requested_by = fu.user_id
  and flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
   and flv.lookup_code = e.execution_method_code
   and r.concurrent_program_id = p.concurrent_program_id
   and resp.responsibility_id = r.responsibility_id 
   and r.requested_start_date between '27-AUG-2013' and sysdate
  -- and r.argument1 = 'parameter1'
--   and r.request_id = 7344623
   --and r.parent_request_id IN ('7329248','7313636','7312292')  --'7314309',
  order by r.requested_start_date desc;

-------User Responsibilities----------
select u.user_name, resp.responsibility_name
  from apps.fnd_user_resp_groups g,
       apps.fnd_responsibility_vl resp
       ,apps.fnd_user u
 where u.user_name = '837166'
   and u.user_id = g.user_id
   and g.responsibility_id = resp.responsibility_id
   order by 2;


-- Query to find request group and application name for a concurrent program
-------------------------------------------------------------------------------
SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
  FROM fnd_request_groups          rg,
       fnd_request_group_units     rgu,
       fnd_concurrent_programs     cp,
       fnd_concurrent_programs_tl  cpt,
       fnd_application             fa,
       fnd_application_tl          fat
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = 'CP';

-----Getting submitted concurrent requests information---------
select r.request_id,p.user_concurrent_program_name
        , e.executable_name, flv.meaning, execution_file_name
       ,r.requested_start_date,r.actual_start_date,r.actual_completion_date
       ,fu.user_name
       , r.phase_code, r.status_code, r.parent_request_id,
       resp.responsibility_name
       , r.argument_text, r.argument1, r.argument2, r.argument3,r.argument4,r.argument5,r.argument6,r.argument7,r.argument8,
       r.argument9,r.argument10,r.argument11,r.argument12,r.argument13,r.argument14,r.argument15,r.argument16,r.argument17      
  from apps.fnd_concurrent_programs_vl p
       ,apps.fnd_executables e
       ,apps.fnd_lookup_values flv
       ,apps.fnd_concurrent_requests r
       ,apps.fnd_responsibility_vl resp
       ,apps.fnd_user fu
 where 1=1
   and p.user_concurrent_program_name like 'Statement Generation Program%' 
   and p.executable_id = e.executable_id
   and r.requested_by = fu.user_id
   --and r.phase_code ='E'
  -- and r.request_id in (7284898,7284934)
    --(7284901,7284935)
   and flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
   and flv.lookup_code = e.execution_method_code
   and r.concurrent_program_id = p.concurrent_program_id
   and resp.responsibility_id = r.responsibility_id 
 --  and r.requested_start_date between '21-AUG-2011' and sysdate  
   --and argument1 like 'APAC%'
   --and r.phase_code != 'C'
  order by r.requested_start_date desc;


-----to find session ID for a particular concurrent request-----------

SELECT ses.sid, 
            ses.serial# 
       FROM v$session ses, 
            v$process pro
           WHERE ses.paddr = pro.addr
                AND pro.spid IN (SELECT oracle_process_id 
                                           FROM fnd_concurrent_requests 
                                        WHERE request_id = &request_id);

-----Form Functions and responisbilities------
SELECT     LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
           LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
           menu_entry.grant_flag grant_flag,
           DECODE (menu_entry.sub_menu_id,
                   NULL, 'FUNCTION',
                   DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
                  ) TYPE,
           menu2.user_menu_name, func2.user_function_name
      FROM fnd_menu_entries_vl menu_entry,
           fnd_menus_tl menu,
           fnd_form_functions_tl func,
           fnd_form_functions_tl func2,
           fnd_menus_tl menu2
     WHERE menu_entry.sub_menu_id = menu.menu_id(+)
       AND menu_entry.function_id = func.function_id(+)
       AND menu_entry.sub_menu_id = menu2.menu_id(+)
       AND menu_entry.function_id = func2.function_id(+)
       AND grant_flag = 'Y'
       and func2.user_function_name like '%Customer Hold%'
START WITH menu_entry.menu_id =
                     (SELECT menu_id
                        FROM fnd_menus_tl menu2
                       WHERE menu2.user_menu_name = :MENU_NAME)
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
  ORDER SIBLINGS BY menu_entry.entry_sequence;

-----Checking Patch Levels-------
select * from apps.ad_bugs
where bug_number = '4602427';

select * from apps.ad_applied_patches
where patch_name = '4602427';

Setting up Advanced Collections Dunning plans (Both Standard and Custom)

Dunning Requirement was to have 4 letters sent to customer Accounts. All 4 had custom templates and in the initial phase were sent to 3rd Party for printing and mailing. First 2 letters were setup via Dunning plans and the remaining 2 were manually identified and sent to SR via XML transmission. Here I will be covering the first 2 letters only as they involve the standard IEX setups to be done in the system.

Steps:

1)    Create Custom Aging bucket as per the business requirements.

AR Super User/ Receivables Manager --> Setup --> Collections --> Aging Buckets

2)    Use this Aging bucket in the Collections module
Collections Administrator --> Setup Checklist --> Operations Setup (OAF Page) --> general collections Information

Set up the email host and the From address in the correspondence Tab.

Collections Method Setup --> Create Dunning Plans

Use the Standard Account Scoring engine

Add the Aging Bucket Line by selecting the custom aging bucket lines that we created as the initial step

If you want to create custom scoring components to suit the business requirements, you need to use the Create Scoring Components (Need to have Advanced collections Full license)

SELECT NVL(DECODE(hca.CUSTOMER_CLASS_CODE,'STRATEGIC',20,'ACUTE',40,'AMB',60,'FEDERAL',80),100)

    FROM hz_cust_accounts hca

  WHERE  hca.cust_account_id = :cust_account_id

   AND EXISTS (SELECT 1 FROM iex_delinquencies del

                 WHERE del.cust_account_id = hca.cust_account_id

                 AND del.creation_date >= SYSDATE - 365

              )

Create a new Scoring engine to utilize the custom scoring component:

Use the same component details as the standard Account scoring. For custom scoring component set up your range

Use the IEX_F_ACCOUNTS_V standard segment. You can test the segment (It should return count of all the accounts in your system that have delinquent transactions)

Create a new Dunning plan

3)    Setting up custom RTF templates

XML Publisher Administrator --> Home --> Data Definitions

This should be setup in Collections Application and not the custom XX application

Create a Template to be associated with the data definition

Repeat these steps for all the custom RTF templates that are needed for the business

4)    Creating the custom query to populate the custom RTF’s

Collections Administrator --> Administration --> Manage Queries

SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') currsysdate,

DECODE((per.person_first_name || per.person_last_name), null, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('RESPONSIBILITY', 'APS'), per.person_first_name) first_name,

per.person_last_name last_name,

org.party_name org_name,

loc.address1 address1,

loc.address2 address2,

loc.city city,

loc.state state,

loc.postal_code postal_code,

NVL(per.person_first_name,'Valued Client') first_name1,

(SELECT SUM(aps.amount_due_remaining)

FROM

iex_delinquencies_all dd,

ar_payment_schedules_all aps

WHERE

dd.payment_schedule_id = aps.payment_schedule_id AND

dd.party_cust_id = org.party_id AND

dd.cust_account_id = :ACCOUNT_ID) total_amount_due_remaining,

TO_CHAR(SYSDATE+14, 'MM/DD/YYYY') required_pay_date,

rs.source_name collector_name,

rs.source_job_title collector_title,

rs.source_phone collector_phone,

rs.source_mgr_name collector_supervisor,

(SELECT account_number FROM hz_cust_accounts

WHERE cust_account_id = :ACCOUNT_ID) ACCT_NUM,

CURSOR

(SELECT

 NVL(SUM(  (CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN -99999 AND 0 THEN AMOUNT_DUE_REMAINING END)),0) inv_current

, NVL(SUM(  (CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN 1 AND 30 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_1_to_30

, NVL(SUM((CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN 31 AND 60 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_31_to_60

, NVL(SUM((CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN 61 AND 90 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_61_to_90

, NVL(SUM((CASE WHEN ROUND(SYSDATE-ps.due_date) BETWEEN 91 AND 120 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_91_to_120

, NVL(SUM((CASE WHEN ROUND(SYSDATE-ps.due_date) > 120 THEN AMOUNT_DUE_REMAINING END)),0) inv_cur_days_120_days_plus

FROM

 ar_payment_schedules_all ps

, hz_cust_accounts hca

, ra_cust_trx_line_gl_dist_all hcatlda

, gl_code_combinations_kfv gcc

, ra_customer_trx_all hcata

, hz_cust_accounts hca2

WHERE

 ps.status = 'OP'

AND ps.CUSTOMER_ID=hca.cust_account_id

AND ps.CUSTOMER_TRX_ID=hcatlda.CUSTOMER_TRX_ID

AND hcatlda.ACCOUNT_CLASS='REC'

AND hcatlda.latest_rec_flag = 'Y'

AND hcatlda.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID

AND ps.CUSTOMER_TRX_ID=hcata.CUSTOMER_TRX_ID

AND NVL(hcata.ATTRIBUTE5,ps.CUSTOMER_ID)=hca2.cust_account_ID

AND ps.CUSTOMER_ID= :ACCOUNT_ID

ORDER BY hca.account_name

) AS payment_history

FROM

HZ_LOCATIONS loc

,hz_parties org

,hz_parties per

,jtf_rs_resource_extns rs

WHERE

loc.location_id = :LOCATION_ID

AND org.party_id= :PARTY_ID

AND per.party_id = NVL(:CONTACT_ID, org.party_id)

AND rs.RESOURCE_ID = :RESOURCE_ID

5)    Associating the custom RTF’s with the custom collections query

Collections Administrator --> Administration --> Manage Templates Query

Select the custom template and the custom dunning query and click on Apply

6)    After all these setups have been completed, we need to submit the IEX: Scoring Engine Harness with the standard Account scoring

If we are using the custom scoring engine and components that were created earlier then use that particular custom scoring engine instead of the standard Account scoring

The scoring engine harness scores the Account using the standard or custom components and this score can be seen on the collections screen in the Accounts tab.

7)    Submit the IEX: Send Dunnings for Delinquent Customers to send the dunning letters

You can also use the custom scoring component dunning plan also

8)    Sample custom RTF attached

https://docs.google.com/document/d/1Ha3XWSB_OMR_4ckRLXGRCuXO_GCvqJMFJmkf_ZF5j8k/edit?usp=sharing