, , , , ,

Query to find who and when update an Oracle Application user’s profile

SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date “Change Date”,
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) “Created By”,
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) “Last Update By”
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;
/by
, , , , ,

Query for Sales Order Details

SELECT   ooha.header_id order_header_id, ottt.NAME order_type_name,
         ooha.order_number, ooha.ordered_date,
         ooha.transactional_curr_code order_currency, hp.party_id,
         hp.party_number, hp.party_name customer_name,
         hca.cust_account_id customer_id, hca.account_number customer_number,
         oola.line_id order_line_id, oola.line_number, oola.inventory_item_id,
         msib.segment1 item_number, msib.description item_desc,
         oola.attribute15 superseded_item, oola.order_quantity_uom,
         oola.ordered_quantity, oola.unit_selling_price
    FROM oe_order_headers_all ooha,
         oe_order_lines_all oola,
         oe_transaction_types_tl ottt,
         mtl_system_items_b msib,
         mtl_parameters mp,
         org_organization_definitions ood,
         hz_parties hp,
         hz_cust_accounts hca
   WHERE ooha.header_id = oola.header_id
     AND ottt.transaction_type_id(+) = ooha.order_type_id
     AND ottt.LANGUAGE = USERENV (‘LANG’)
     AND hca.cust_account_id(+) = ooha.sold_to_org_id
     AND hp.party_id = hca.party_id
     AND ooha.org_id = oola.org_id(+)
     AND msib.inventory_item_id = oola.inventory_item_id
     AND msib.organization_id = mp.master_organization_id
     AND mp.organization_id = ood.organization_id
     AND mp.master_organization_id = mp.organization_id
     AND ood.operating_unit = fnd_profile.VALUE (‘ORG_ID’)
     AND ooha.order_number = :sales_order_number
ORDER BY ottt.NAME, ooha.order_number, oola.line_number;
/by
, , , , ,

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
, , , , ,

Query for Supplier Bank Details

SELECT DISTINCT pv.vendor_name vendor_name, pv.segment1 vendor_number,
                pvs.vendor_site_code vendor_site_code,
                aba.bank_account_name bank_account_name,
                aba.bank_account_num bank_account_num,
                aba.currency_code currency_code,
                abau.primary_flag primary_flag, abb.bank_name bank_name,
                abb.bank_number bank_number,
                abb.bank_branch_name bank_branch_name, abb.bank_num bank_num
           FROM ap_bank_account_uses_all abau,
                ap_bank_accounts_all aba,
                ap_bank_branches abb,
                po_vendors pv,
                po_vendor_sites_all pvs
          WHERE abau.external_bank_account_id = aba.bank_account_id
            AND aba.bank_branch_id = abb.bank_branch_id
            AND abau.vendor_id = pv.vendor_id
            AND abau.vendor_id = pvs.vendor_id(+)
            AND abau.vendor_site_id = pvs.vendor_site_id(+)
/by