How to terminate 'Concurrent program' in backend

Enable trace for Concurrent Requests

To enable trace for concurrent requests, the main reason for this is to troubleshooting performance problems. Here is a simple guide how to enable oracle session tracing for the request.

1. Login as sysadmin 
2. Go to -> System Administrator -> Concurrent : Program -> Define and Press F11 
3. Enter the Concurrent Program Name (Eg. Journal Import) -> Press Ctrl + F11 
4. Enable the check box "Enable Trace" above "Copy To" button. Click on Save and close the window. 
5. Select Requests -> Run->Submit the concurrent request! 
6. The trace file will be created in the Database Server under the location of "udump" 
sql>select value from v$parameter where name like '%user_dump_dest%';

Supplier Bank Account Details Query

Bank Account can be associated with Supplier at following levels

1. Supplier

2. Supplier site

3. Party Site

4. Party Site + Org

This query can find bank accounts associated with a given supplier at any level.

SELECT 'Bank Account At Supplier Site Level' Bank_Account_Level

     , sup.segment1

     , sup.vendor_name

     , epa.org_id

     , ss.vendor_site_code

     , NULL   Party_Site_Code

     , eba.bank_account_num

     , piu.order_of_preference priority

     , eba.ext_bank_account_id

  FROM ap_suppliers sup

     , ap_supplier_sites_all       ss

     , iby_external_payees_all epa

     , iby_pmt_instr_uses_all  piu

     , iby_ext_bank_accounts   eba

 WHERE sup.vendor_id     = ss.vendor_id

   AND ss.vendor_site_id = epa.supplier_site_id

   AND epa.ext_payee_id  = piu.ext_pmt_party_id      

   AND piu.instrument_id = eba.ext_bank_account_id

   AND sup.segment1      = '702393'

UNION

SELECT 'Bank Account at Supplier Level'

     , sup.segment1

     , sup.vendor_name

     , epa.org_id

     , NULL

     , NULL

     , eba.bank_account_num

     , piu.order_of_preference priority

     , eba.ext_bank_account_id

  FROM ap_suppliers sup

     , iby_external_payees_all epa

     , iby_pmt_instr_uses_all  piu

     , iby_ext_bank_accounts   eba

 WHERE sup.party_id        = epa.payee_party_id

   AND epa.ext_payee_id    = piu.ext_pmt_party_id      

   AND piu.instrument_id   = eba.ext_bank_account_id

   AND sup.segment1        = '702393'

   AND supplier_site_id    IS NULL

   AND party_site_id       IS NULL

UNION

SELECT 'Bank Account at Address + Opearting Unit Level'

     , sup.segment1

     , sup.vendor_name

     , epa.org_id

     , NULL

     , psite.party_site_name

     , eba.bank_account_num

     , piu.order_of_preference priority

     , eba.ext_bank_account_id

  FROM ap_suppliers            sup

     , hz_party_sites          psite

     , iby_external_payees_all epa

     , iby_pmt_instr_uses_all  piu

     , iby_ext_bank_accounts   eba

 WHERE sup.party_id        = psite.party_id

   AND psite.party_site_id = epa.party_site_id

   AND epa.ext_payee_id    = piu.ext_pmt_party_id      

   AND piu.instrument_id   = eba.ext_bank_account_id

   AND sup.segment1        = '702393'

   AND supplier_site_id    IS NULL

   AND epa.org_id          IS NOT NULL

UNION

SELECT 'Bank Account at Address Level'

     , sup.segment1

     , sup.vendor_name

     , epa.org_id

     , NULL

     , psite.party_site_name

     , eba.bank_account_num

     , piu.order_of_preference priority

     , eba.ext_bank_account_id

  FROM ap_suppliers            sup

     , hz_party_sites          psite

     , iby_external_payees_all epa

     , iby_pmt_instr_uses_all  piu

     , iby_ext_bank_accounts   eba

 WHERE sup.party_id        = psite.party_id

   AND psite.party_site_id = epa.party_site_id

   AND epa.ext_payee_id    = piu.ext_pmt_party_id      

   AND piu.instrument_id   = eba.ext_bank_account_id

   AND sup.segment1        = '702393'

   AND supplier_site_id    IS NULL

   AND epa.org_id          IS NULL

 ORDER BY bank_account_num

COALESCE function in Oracle

Syntax

The syntax for the COALESCE function is:

COALESCE( expr1, expr2, ... expr_n )

The COALESCE function can be used in Oracle/PLSQL.

You could use the coalesce function in a SQL statement as follows:

SELECT COALESCE( address1, address2, address3 ) result

FROM suppliers;

The above COALESCE function is equivalent to the following IF-THEN-ELSE statement:

IF address1 is not null THEN

   result := address1;

ELSIF address2 is not null THEN

   result := address2;

ELSIF address3 is not null THEN

   result := address3;

ELSE

   result := null;

END IF;

How to check the Clone date of an Oracle instance

SELECT resetlogs_time FROM v$database;

Get DDL for view

select text from ALL_VIEWS where upper(view_name) like upper('<View name>');

Query to list customers and their sites information

/**********************************************************
*PURPOSE: To list customers and their sites information *
*AUTHOR: RAG *
**********************************************************/
SELECT
  ---------------------- --Customer Information ----------------------
  hp.party_id,
  hp.party_name "CUSTOMER_NAME",
  hca.cust_account_id,
  hca.account_number,
  hcas.org_id, 
  --------------------------- --Customer Site Information ---------------------------
  hcas.cust_acct_site_id,
  hps.party_site_number,
  hcsu.site_use_code, 
  ----------------------- --Customer Site Address -----------------------
  hl.address1,
  hl.address2,
  hl.address3,
  hl.address4,
  hl.city,
  hl.postal_code,
  hl.state,
  hl.province,
  hl.county,
  hl.country,
  hl.address_style
FROM hz_parties hp,
  hz_party_sites hps,
  hz_cust_accounts_all hca,
  hz_cust_acct_sites_all hcas,
  hz_cust_site_uses_all hcsu,
  hz_locations hl
WHERE 1                    =1
AND hp.party_id            = hca.party_id
AND hca.cust_account_id    = hcas.cust_account_id(+)
AND hps.party_site_id(+)   = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
  --
AND hps.location_id = hl.location_id(+)
  --
AND hp.party_type = 'ORGANIZATION' -- only ORGANIZATION Party types
AND hp.STATUS     = 'A'            -- only Active Parties/Customers
ORDER BY to_number(hp.party_number),
  hp.party_name,
  hca.account_number;