, , , , ,

Query to get Request Group Details Responsibility wise

SELECT   frg.request_group_name, fat1.application_name, frg.description,
         DECODE (frgu.request_unit_type,
                 ‘P’, ‘Program’,
                 ‘S’, ‘Set’,
                 ‘A’, ‘Application’,
                 frgu.request_unit_type
                ) TYPE,
         DECODE (frgu.request_unit_type,
                 ‘P’, fcpt.user_concurrent_program_name,
                 ‘S’, frst.user_request_set_name,
                 ‘A’, fat3.application_name,
                 frgu.request_unit_type
                ) NAME,
         fat2.application_name
    FROM fnd_request_groups frg,
         fnd_request_group_units frgu,
         fnd_concurrent_programs_tl fcpt,
         fnd_application_tl fat1,
         fnd_application_tl fat2,
         fnd_application_tl fat3,
         fnd_request_sets_tl frst
   WHERE frg.request_group_id = frgu.request_group_id
     AND frgu.request_unit_id = fcpt.concurrent_program_id(+)
     AND fcpt.LANGUAGE(+) = USERENV (‘LANG’)
     AND frg.application_id = fat1.application_id
     AND fat1.LANGUAGE(+) = USERENV (‘LANG’)
     AND frgu.unit_application_id = fat2.application_id
     AND fat2.LANGUAGE(+) = USERENV (‘LANG’)
     AND frgu.unit_application_id = fcpt.application_id(+)
     AND frgu.request_unit_id = frst.request_set_id(+)
     AND frst.LANGUAGE(+) = USERENV (‘LANG’)
     AND frgu.request_unit_id = fat3.application_id(+)
     AND fat3.LANGUAGE(+) = USERENV (‘LANG’)
     AND frgu.unit_application_id = frst.application_id(+)
     AND upper(fat1.application_name) = upper(:application_name)
ORDER BY request_group_name, frgu.request_unit_type, frgu.request_unit_id;

/by
, , , , ,

Query for Customer Address Details

SELECT DISTINCT hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
           FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
          WHERE hp.party_id = hps.party_id(+)
            AND hp.party_id = hca.party_id(+)
            AND hcasa1.party_site_id(+) = hps.party_site_id
            AND hcasa2.party_site_id(+) = hps.party_site_id
            AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
            AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
            AND hcsua1.site_use_code(+) = ‘bill_to’
            AND hcsua2.site_use_code(+) = ‘ship_to’
            AND hcasa1.org_id(+) = fnd_profile.VALUE (‘org_id’)
            AND hcasa2.org_id(+) = fnd_profile.VALUE (‘org_id’)
            AND hps.location_id = hl.location_id
            AND hl.country = ftt.territory_code
            AND ftt.LANGUAGE = USERENV (‘lang’)
       ORDER BY customer_number;
/by