Entries by Oracle ERP Apps Guide

, , , , ,

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_nameFROM fnd_userWHERE user_id = v.created_by) “Created By”,(SELECT user_nameFROM fnd_userWHERE user_id = v.last_updated_by) “Last Update By”FROM fnd_profile_options o,fnd_profile_option_values v,fnd_profile_options_tl tWHERE o.profile_option_id = v.profile_option_idAND o.application_id = v.application_idAND start_date_active <= SYSDATEAND NVL (end_date_active, SYSDATE) >= SYSDATEAND o.profile_option_name = t.profile_option_nameAND level_id = 10001AND t.LANGUAGE IN (SELECT language_codeFROM fnd_languagesWHERE installed_flag = ‘B’UNIONSELECT nls_languageFROM […]

/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     […]

/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’)     […]

/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(+)            […]

/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