Oracle Queries

This page details about oracle related queries

Customer Account and Sites - Active BILL TO

select hp.party_name "Customer Name"
      ,hca.account_number "Customer #"
      ,hps.party_site_number "Party Site #"
      ,csu.location      "Bill To Location"
      ,LTRIM(REPLACE(REPLACE(SUBSTR ( NVL2 (UPPER(hl.address1),','  || UPPER(hl.address1), NULL)       ||
                NVL2 (UPPER(hl.address2),     ','  || UPPER(hl.address2), NULL)       ||
                --NVL2 (UPPER(hl.address3),     ','  || UPPER(hl.address3), NULL)       ||
                --NVL2 (UPPER(hl.address4),     ','  || UPPER(hl.address4), NULL)       ||
                NVL2 (UPPER(hl.city),         ','  || UPPER(hl.city), NULL)           ||
                NVL2 (UPPER(hl.county),       ','  || UPPER(hl.county), NULL)         ||
                NVL2 (UPPER(hl.state),        ','  || UPPER(hl.state), NULL)          ||
                NVL2 (UPPER(hl.postal_code),  ' '  || UPPER(hl.postal_code), '0-0')    ||
                NVL2 (UPPER(hl.country),      ' '  || UPPER(hl.country), NULL)
               ,2,1000
               ),'"',''),CHR(9),''),',')  address
           ,csu.org_id
  from hz_cust_accounts hca
      ,hz_party_sites hps
      ,hz_parties hp
      ,hz_cust_acct_sites_all cas
      ,hz_cust_site_uses_all csu
      ,hz_locations hl
 where hp.party_id = hca.party_id
   and hps.party_id  = hp.party_id
   and cas.party_site_id = hps.party_site_id
   and cas.cust_account_id    = hca.cust_account_id
   and cas.cust_acct_site_id = csu.cust_acct_site_id
   and hp.status = 'A'
   and hca.status = 'A'
   and hps.status = 'A'
   and csu.status = 'A'
   and cas.status = 'A'
   AND CSU.SITE_USE_CODE = 'BILL_TO'
   AND hps.location_id = hl.location_id
ORDER BY csu.org_id,hp.party_name;

Query to find concurrent program attached to responsibility

SELECT DISTINCT *
  FROM apps.fnd_responsibility_tl
 WHERE responsibility_id IN (SELECT responsibility_id
                            FROM apps.fnd_responsibility_vl
                              WHERE request_group_id IN (SELECT request_group_id
                                  FROM apps.fnd_request_group_units
                                       WHERE request_unit_id = (SELECT DISTINCT concurrent_program_id
                                                        FROM Apps.fnd_concurrent_programs_tl
                                                     WHERE user_concurrent_program_name = '<&Concurrent Program Name>'
                                                     )
                                          )
                      AND end_date IS NULL
                           )
    AND "LANGUAGE" LIKE 'US'

 ORDER BY responsibility_name;