Query to find AP Pay Group

Below is the SQL query to find the Pay Groups in Oracle Accounts Payables (AP). 

Navigation:

Payables Manager -> Setup -> Lookups -> Purchasing -> Search for 'PAY GROUP' in Type field

SELECT lc.lookup_type               "Lookup Type",
       lc.lookup_code               "Lookup Code",
       lc.displayed_field           "Displayed Field",
       lc.description               "Description",
       TRUNC (lc.creation_date)     "Creation Date",
       usr_cr.user_name             "Created By",
       TRUNC (lc.last_update_date)  "Last Update Date",
       usr_up.user_name             "Last Updated By"
  FROM PO_LOOKUP_CODES  lc,
       FND_USER         usr_cr,
       FND_USER         usr_up
 WHERE lc.created_by = usr_cr.user_id
   AND lc.last_updated_by = usr_up.user_id
   AND lc.lookup_type = 'PAY GROUP'
   AND lc.enabled_flag = 'Y'
   AND SYSDATE <= NVL (lc.inactive_date, SYSDATE)
 ORDER BY lc.lookup_type, lc.lookup_code;

Query to find State and State Code in Oracle Applications

 Query to find State and State Code

SELECT hg.geography_code     state_code,

       hgi.identifier_value  state_name

  FROM (SELECT gi.geography_id,

               gi.identifier_value,

               ROW_NUMBER() OVER (

                  PARTITION BY gi.geography_id 

                  ORDER BY gi.last_update_date DESC) rn

          FROM HZ_GEOGRAPHY_IDENTIFIERS  gi

         WHERE gi.identifier_type = 'NAME'

           AND gi.identifier_subtype = 'STANDARD_NAME'

           AND gi.geography_type = 'STATE'

           AND gi.language_code = 'US'

           AND gi.primary_flag = 'N') hgi

  JOIN HZ_GEOGRAPHIES  hg

    ON hg.geography_id = hgi.geography_id

 WHERE hg.geography_type = 'STATE'

   AND hgi.rn = 1

 ORDER BY hgi.identifier_value;

Query to find Check Number Setups

SELECT pd.payment_document_name               "Payment Document Name",
       pd.first_available_document_num        "Check Number Begin",
       pd.last_available_document_number      "Check Number End",
       (pd.last_available_document_number -
        pd.first_available_document_num) + 1  "Num of Total Checks",
       pd.last_issued_document_number         "Last Issued Check Number",
       (pd.last_issued_document_number -
        pd.first_available_document_num) + 1  "Num Of Checks Used",
       (pd.last_available_document_number -
        pd.last_issued_document_number)       "Num Of Checks Remaining",
       pd.format_code                         "Format Code",
       ba.bank_account_name                   "Bank Account Name"
  FROM CE_PAYMENT_DOCUMENTS  pd,
       CE_BANK_ACCOUNTS      ba
 WHERE TRUNC (NVL (pd.inactive_date, SYSDATE)) >= TRUNC (SYSDATE)  -- active ones
   AND pd.internal_bank_account_id = ba.bank_account_id
 ORDER BY pd.payment_document_name;

Add System Admin responsibility to a user

BEGIN
   FND_USER_PKG.ADDRESP(
      USERNAME        =>  UPPER('AMOHSIN'),  -- username
      RESP_APP        =>  'SYSADMIN',
      RESP_KEY        =>  'SYSTEM_ADMINISTRATOR',
      SECURITY_GROUP  =>  'STANDARD',
      DESCRIPTION     =>  'DESCRIPTION',
      START_DATE      =>  SYSDATE,
      END_DATE        =>  NULL);

   COMMIT;

   DBMS_OUTPUT.PUT_LINE('Responsibility Added Successfully');

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Responsibility is not added due to' ||
      SQLCODE || SUBSTR(SQLERRM, 1, 100));
      
      ROLLBACK;
END;

Query to find AP Supplier Remittance Email Addresses

This following query will display all the active vendors' email addresses by their associated sites. This will work only in Oracle R12.

SELECT

       -- s.vendor_id,

       -- st.vendor_site_id,

       -- s.party_id,

       -- st.party_site_id,

       s.vendor_name              "Vendor Name",

       s.segment1                 "Vendor Number",

       s.vendor_type_lookup_code  "Vendor Type",

       st.vendor_site_code        "Vendor Site Code",

       ou.name                    "Operating Unit",

       --

       iepa.remit_advice_delivery_method  "Remittance Delivery Method",

       iepa.remit_advice_email            "Remittance Advice Email"

  FROM

       ap_suppliers              s,

       ap_supplier_sites_all     st,

       hr_operating_units        ou,

       iby_external_payees_all   iepa

 WHERE

       1=1

   --

   -- AND s.vendor_type_lookup_code = 'EMPLOYEE'

   --

   AND TRUNC (SYSDATE) BETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL (s.end_date_active, SYSDATE+1))

   AND s.enabled_flag = 'Y'

   --

   AND iepa.supplier_site_id = st.vendor_site_id

   AND iepa.payee_party_id = s.party_id

   --

   AND st.org_id = ou.organization_id

   AND st.vendor_id = s.vendor_id

   --

 ORDER BY s.vendor_name, st.vendor_site_code;

  

Query to find Form Personalization

I found this query at http://ravivarma1985.blogspot.com/2012/05/sql-query-to-get-list-of.html. Thought I would re-post it for my own personal reference. Might come in handy.

SELECT ffv.form_id          "Form ID",

       ffv.form_name        "Form Name",

       ffv.user_form_name   "User Form Name",

       ffv.description      "Form Description",

       ffcr.sequence        "Sequence",

       ffcr.description     "Personalization Rule Name"

  FROM fnd_form_vl             ffv,

       fnd_form_custom_rules   ffcr

 WHERE ffv.form_name = ffcr.form_name

 ORDER BY ffv.form_name, ffcr.sequence;

Query to find Legal Entity, Organization, Company Code

SELECT

       xep.legal_entity_id        "Legal Entity ID",

       xep.name                   "Legal Entity",

       hr_outl.name               "Organization Name",

       hr_outl.organization_id    "Organization ID",

       hr_loc.location_id         "Location ID",

       hr_loc.country             "Country Code",

       hr_loc.location_code       "Location Code",

       glev.flex_segment_value    "Company Code"

  FROM

       xle_entity_profiles            xep,

       xle_registrations              reg,

       --

       hr_operating_units             hou,

       -- hr_all_organization_units      hr_ou,

       hr_all_organization_units_tl   hr_outl,

       hr_locations_all               hr_loc,

       --

       gl_legal_entities_bsvs         glev

 WHERE

       1=1

   AND xep.transacting_entity_flag   =  'Y'

   AND xep.legal_entity_id           =  reg.source_id

   AND reg.source_table              =  'XLE_ENTITY_PROFILES'

   AND reg.identifying_flag          =  'Y'

   AND xep.legal_entity_id           =  hou.default_legal_context_id

   AND reg.location_id               =  hr_loc.location_id

   AND xep.legal_entity_id           =  glev.legal_entity_id

   --

   -- AND hr_ou.organization_id         =  hou.business_group_id

   AND hr_outl.organization_id       =  hou.organization_id

 ORDER BY hr_outl.name

Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information". In the following example, I am trying to get all the information for "US" context code.

The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

-----------------------------------------------------------------------------

-- Query to find DFF information

-----------------------------------------------------------------------------

SELECT

       fdf.title                             "DFF Title",

       fdf.application_table_name            "Application Table",

       fdf.context_column_name               "Context Column Name",

       --

       fdfcu.descriptive_flex_context_code   "DFF Context Code",

       fdfcu.column_seq_num                  "Sequence",

       fdfcu.end_user_column_name            "Segment Name",

       fdfcu.application_column_name         "Column Name",

       --

       ffv.flex_value_set_name               "Value Set Name"

  FROM

       fnd_descr_flex_col_usage_vl   fdfcu,

       fnd_descriptive_flexs_vl      fdf,

       fnd_flex_value_sets           ffv

 WHERE

       1 = 1

   --

   AND fdf.title = 'Further Job Information'        -- <change it>

   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>

   AND fdfcu.enabled_flag = 'Y'

   --

   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id

   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name

   AND fdfcu.application_id = fdf.application_id

   --

 ORDER BY

       fdfcu.descriptive_flexfield_name,

       fdfcu.descriptive_flex_context_code,

       fdfcu.column_seq_num;

LEFT OUTER JOIN: Oracle HR Employee Phones

If you ever worked with PER_PHONES table in Oracle EBS to get the employee phones, you must have seen that PER_PHONES table contains multiple lines for different phone types for an employee.

For example, if I run the following query for an employee whose PERSON_ID is 1444, I get three records:

SELECT *

  FROM PER_PHONES p

 WHERE p.parent_id = 1444;

You can run the following query to get the meaning of the phone types:

SELECT lookup_type,

       lookup_code,

       meaning

  FROM HR_LOOKUPS

 WHERE enabled_flag = 'Y'

   AND lookup_type  = 'PHONE_TYPE';

Now the problem is, when you connect this PER_PHONES (parent_id) table with PER_ALL_PEOPLE_F (person_id) table , you end up with multiple rows. In the above example, it will return three rows.

Now of course, the client will never want to see more than one row per employee. In my case, the client specifically asked for a condition which goes like: pick up Mobile Phone of the employee first; if Mobile Phone is NULL, then pick up Home Phone; if Home Phone is NULL, then pick up Work Phone, and so on.

I assumed that there would already be a view for this from Oracle. Since I did not find any, I had to come up with a query (to create a custom view) to avoid multiple rows per employee, using LEFT OUTER JOIN function.

SELECT

       pp.parent_id      parent_id,

       pp.parent_table   parent_table,

       --

       ppm.phone_number  mobile_phone,

       pph.phone_number  home_phone,

       ppw.phone_number  work_phone,

       ppo.phone_number  other_phone

  FROM

       (SELECT DISTINCT

               parent_id,

               parent_table

          FROM per_phones

       ) pp

  -- mobile phone

  LEFT OUTER JOIN per_phones  ppm

    ON (     ppm.phone_type   = 'M'

         AND ppm.parent_id    = pp.parent_id

         AND ppm.parent_table = pp.parent_table

       )

  -- home phone

  LEFT OUTER JOIN per_phones  pph

    ON (     pph.phone_type   = 'H1'

         AND pph.parent_id    = pp.parent_id

         AND pph.parent_table = pp.parent_table

       )

  -- work phone

  LEFT OUTER JOIN per_phones  ppw

    ON (     ppw.phone_type   = 'W1'

         AND ppw.parent_id    = pp.parent_id

         AND ppw.parent_table = pp.parent_table

       )

  -- other phone

  LEFT OUTER JOIN per_phones  ppo

    ON (     ppo.phone_type   = 'O'

         AND ppo.parent_id    = pp.parent_id

         AND ppo.parent_table = pp.parent_table

       )

 WHERE 1 = 1;

Hopefully, this is helpful. However, if there is any other query which may be useful, I would certainly love to hear or know about it.