Query to Extract Employee Contact Information

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type

Script to Print the Oracle Apps Version Number

SELECT substr(a.application_short_name, 1, 5) code,
       substr(t.application_name, 1, 50) application_name,
       p.product_version version
  FROM fnd_application a,
       fnd_application_tl t,
       fnd_product_installations p
 WHERE a.application_id = p.application_id
   AND a.application_id = t.application_id
   AND t.language = USERENV('LANG')

Query to Print Calendar for the year

SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH,
"Sun", "Mon","Tue", "Wed", "Thu", "Fri", "Sat"

FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,

MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Sun",

MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Mon",

MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Tue",

MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Wed",

MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Thu",

MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Fri",

MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2))) "Sat"

FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt

FROM all_objects

WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y'))
GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))

ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

Link between purchase Order and Requisition

SELECT prh.segment1 req_number,
       prh.authorization_status,
       prl.line_num req_line_num,
       prl.item_description req_item_description,
       prl.unit_price req_unit_price,
       prl.quantity req_quantity,
       pd.req_header_reference_num,
       pd.req_line_reference_num,
       pl.line_num,
       pl.item_description,
       pl.quantity,
       pl.amount,
       ph.segment1 po_number,
       prd.distribution_id,
       pd.req_distribution_id
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all   prl,
       po_req_distributions_all   prd,
       po_distributions_all       pd,
       po_line_locations_all      pll,
       po_lines_all               pl,
       po_headers_all             ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)

Database and Application Information

1) Get Product Version

SELECT produc ,version, status
FROM   product_component_version

The other way to get this information is by using following query


select * from v$version;


2)Check if the application is setup for Multi-Org

SELECT multi_org_flag
FROM   fnd_product_groups

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;           

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(+)