Oracle Cloud Fusion: Supplier Bank Accounts Query

SELECT (SELECT NAME
        FROM   hr_operating_units
        WHERE  organization_id = pssm.prc_bu_id) BU_name,
   ps.segment1 supplier_number,
       hzp.party_name supplier_name,
       pssm.vendor_site_code supplier_site,
       bank.party_name bank_name,
       branch.party_name branch_name,
       ieb.bank_account_name,
       ieb.bank_account_num,
       ieb.currency_code,
       ieb.IBAN
FROM   poz_suppliers ps,
       poz_supplier_sites_all_m pssm,
       iby_external_payees_all payee,
       iby_pmt_instr_uses_all uses,
       iby_ext_bank_accounts ieb,
       hz_parties bank,
       hz_parties branch,
       hz_parties HZP
WHERE  ps.vendor_id = pssm.vendor_id
       AND ps.party_id = payee.payee_party_id
       AND payee.supplier_site_id = pssm.vendor_site_id
       AND uses.instrument_type = 'BANKACCOUNT'
       AND payee.ext_payee_id = uses.ext_pmt_party_id
       AND uses.payment_function = 'PAYABLES_DISB'
       AND uses.instrument_id = ieb.ext_bank_account_id
       AND ieb.bank_id = bank.party_id(+)
       AND ieb.branch_id = branch.party_id(+)
       AND hzp.party_id = ps.party_id
       AND SYSDATE BETWEEN Nvl(uses.start_date, SYSDATE) AND
                           Nvl(uses.end_date, SYSDATE)
       AND SYSDATE BETWEEN Nvl(ieb.start_date, SYSDATE) AND
                           Nvl(ieb.end_date, SYSDATE)
       AND Nvl(ps.end_date_active, SYSDATE + 1) > Trunc (SYSDATE)
       AND Nvl(pssm.inactive_date, SYSDATE + 1) > Trunc (SYSDATE) 

Oracle Cloud Fusion: Query to get the Supplier/Site Attachment Details

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

-- Supplier Attachment

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

SELECT psv.vendor_name supplier_name,

       fad.category_name,

       fdt.dm_type,

       fdt.file_name,

       fdt.title,

       fdt.description

FROM   poz_suppliers_v psv,

       fnd_attached_documents fad,

       fnd_documents_tl fdt

WHERE  1 = 1

       AND To_char (psv.vendor_id) = fad.pk1_value

       AND fad.document_id = fdt.document_id

       AND fad.entity_name = 'POZ_SUPPLIERS'

       AND fdt.language = Userenv ('LANG') 

   

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

-- Supplier Site Attachment

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

SELECT psv.vendor_name supplier_name,

       (SELECT NAME

        FROM   hr_operating_units

        WHERE  organization_id = pssv.prc_bu_id) Procurement_BU,

       pssv.vendor_site_code supplier_site,

       fad.category_name,

       fdt.dm_type,

       fdt.file_name,

       fdt.title,

       fdt.description

FROM   poz_suppliers_v psv,

       poz_supplier_sites_v pssv,

       fnd_attached_documents fad,

       fnd_documents_tl fdt

WHERE  1 = 1

       AND psv.vendor_id = pssv.vendor_id

       AND To_char (pssv.vendor_site_id) = fad.pk1_value

       AND fad.document_id = fdt.document_id

       AND fad.entity_name = 'POZ_SUPPLIER_SITES_ALL_M'

       AND fdt.language = Userenv ('LANG')    

AP Invoice Details Query (Till GL)

SELECT aida.period_name,
       aia.invoice_num ap_inv_numer,
       aia.invoice_date ap_inv_date,
       aia.invoice_amount ap_inv_amount,
       aia.amount_paid ap_inv_amt_paid,
       aia.accts_pay_code_combination_id ap_inv_accts_pay_ccid,
       aida.amount ap_inv_dist_amount,
       aida.dist_code_combination_id ap_inv_dist_ccid,
          gcc1.segment1
       || '.'
       || gcc1.segment2
       || '.'
       || gcc1.segment3
       || '.'
       || gcc1.segment4
       || '.'
       || gcc1.segment5
       || '.'
       || gcc1.segment6
       || '.'
       || gcc1.segment7
       || '.'
       || gcc1.segment8 ap_inv_dist_code_combination,
       gjh.NAME gl_je_header_name,
       gjh.description gl_je_header_desc,
       gjl.je_line_num,
       gjl.code_combination_id gl_je_line_ccid
  FROM ap.ap_invoices_all aia,
       ap.ap_invoice_distributions_all aida,
       ap.ap_invoice_payments_all aipa,
       gl.gl_code_combinations gcc1,
       gl.gl_ledgers gl,
       xla.xla_transaction_entities xte,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       xla.xla_distribution_links xdl,
       gl.gl_import_references gir,
       gl.gl_je_headers gjh,
       gl.gl_je_lines gjl
 WHERE 1 = 1
   AND aia.org_id = :P_ORG_ID
   AND aia.SOURCE = :P_INVOICE_SOURCE
   AND aida.period_name = :P_PERIOD_NAME
   AND aia.invoice_id = :P_INVOICE_ID
   AND aida.invoice_id = aia.invoice_id
   AND gcc1.code_combination_id = aida.dist_code_combination_id
   AND aipa.invoice_id = aia.invoice_id
   AND gl.short_name = :P_LEDGER_SHORT_NAME
   AND gl.chart_of_accounts_id = gcc1.chart_of_accounts_id
   AND xte.ledger_id = gl.ledger_id
   AND xte.entity_code = 'AP_INVOICES'
   AND xte.source_id_int_1 = aia.invoice_id
   AND xah.entity_id = xte.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
   AND xdl.applied_to_entity_code = 'AP_INVOICES'
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gjh.je_header_id = gir.je_header_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_line_num = gir.je_line_num;
 
Pass / Comment based on available values.

AR Invoice Details Query (Till GL)

SELECT trx.trx_number,
       trx.cust_trx_type_id,
       trx.bill_to_customer_id,
       trx_dist.amount ar_inv_dist_amount,
       trx_dist.code_combination_id ar_inv_dist_ccid,
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
       || '.'
       || gcc.segment6
       || '.'
       || gcc.segment7
       || '.'
       || gcc.segment8
          ar_inv_dist_code_combination,
       gjh.name gl_je_header_name,
       gjh.description gl_je_header_desc,
       gjl.je_line_num,
       gjl.code_combination_id gl_je_line_ccid
  FROM ar.ra_customer_trx_all trx,
       ar.ra_cust_trx_line_gl_dist_all trx_dist,
       gl.gl_code_combinations gcc,
       gl.gl_ledgers gl,
       xla.xla_transaction_entities xte,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       xla.xla_distribution_links xdl,
       gl.gl_import_references gir,
       gl.gl_je_headers gjh,
       gl.gl_je_lines gjl
 WHERE 1 = 1
   -- AND trx.org_id = :P_ORG_ID
   -- AND trx.batch_source_id = :P_BATCH_SOURCE_ID
   AND trx.customer_trx_id = :P_CUSTOMER_TRX_ID
   AND trx.customer_trx_id = trx_dist.customer_trx_id
   AND gcc.code_combination_id = trx_dist.code_combination_id
   --  AND gl.short_name = :P_LEDGER_SHORT_NAME
   AND gl.chart_of_accounts_id = gcc.chart_of_accounts_id
   AND xte.ledger_id = gl.ledger_id
   AND xte.entity_code = 'TRANSACTIONS'
   AND xte.application_id = 222
   AND xte.source_id_int_1 = trx.customer_trx_id
   AND xah.entity_id = xte.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.accounting_class_code = 'REVENUE'
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.source_distribution_id_num_1 = trx_dist.cust_trx_line_gl_dist_id
   AND xdl.application_id = 222
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gjh.je_header_id = gir.je_header_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_line_num = gir.je_line_num;

Pass / Comment based on available values.

How to find the locked tables in Oracle?

Execute this Query

SELECT   owner,
         object_name,
         oracle_username || ' (' || vs.status || ')' ora_user,
         os_user_name os_user,
         machine SYSTEM,
         vlo.process unix,
         '''' || vs.SID || ',' || vs.serial# || '''' sid_serial,
         vr.NAME roll_name,
         TO_CHAR (vs.logon_time, 'YYYY/MM/DD HH24:MI:SS') TIME
    FROM v$locked_object vlo,
         dba_objects doj,
         v$session vs,
         v$transaction vt,
         v$rollname vr
   WHERE vlo.object_id = doj.object_id
     AND vs.SID = vlo.session_id
     AND vs.taddr = vt.addr
     AND vt.xidusn = vr.usn
ORDER BY os_user,
         sid_serial,
         object_name

* Pass / Add condition for Object (Table) Name If required.

How to kill a particular session?

ALTER SYSTEM KILL SESSION 'v$session.sid,v$session.serial';

Reports / Executables attached to Responsibilities List

Run this Query

  SELECT frt.responsibility_name,
         frt.description
    FROM fnd_executables fe,
         fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt,
         fnd_request_group_units frgu,
         fnd_request_groups frg,
         fnd_responsibility fr,
         fnd_responsibility_tl frt
   WHERE (fcpt.user_concurrent_program_name = 'USER_CONCURRENT_PROGRAM_NAME'
       OR  fe.execution_file_name = 'EXECUTION_FILE_NAME'
       OR  fe.executable_name = 'EXECUTABLE_NAME')
     AND fcpt.concurrent_program_id = fcp.concurrent_program_id
     AND fcpt.language = USERENV ('Lang')
     AND fcp.executable_id = fe.executable_id
     AND fcp.enabled_flag = 'Y'
     AND fcp.concurrent_program_id = frgu.request_unit_id
     AND frg.request_group_id = frgu.request_group_id
     AND fr.request_group_id = frg.request_group_id
     AND frt.responsibility_id = fr.responsibility_id
     AND frt.language = USERENV ('Lang')
ORDER BY frt.responsibility_name,
         frt.description;

* Pass the Executable Name / Executable File Name / User Concurrent Program Name

RANK(), DENSE_RANK(), PARTITION BY ...

1. How to get the N th Max value?
    (Everybody knows this question in another form - query for the 2nd max salary)

If N = 2

SELECT MAX (sal)
FROM emp
WHERE sal < (SELECT MAX (sal)
FROM emp);
 
If N = 4 / 5 or n ...
It’s an easily achievable using by DENSE_RANK function.
 
SELECT empno, ename, sal
FROM (SELECT empno, ename, sal, DENSE_RANK () OVER (ORDER BY sal DESC NULLS LAST) top_most
FROM emp)

WHERE top_most = 4;

2. What is the difference between RANK and DENSE_RANK function?

       (For better understanding; check the below queries and outputs.)

SELECT * FROM emp;

SELECT empno, ename, sal, RANK () OVER (ORDER BY sal DESC NULLS LAST) RANK, DENSE_RANK () OVER (ORDER BY sal DESC NULLS LAST) DENSE_RANK

FROM emp;

3. Example for PARTITION BY

SELECT ename, sal, RANK () OVER (ORDER BY sal DESC NULLS LAST) RANK, DENSE_RANK () OVER (ORDER BY sal DESC NULLS LAST) DENSE_RANK, deptno, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) deptno_rank
FROM emp
ORDER BY deptno, deptno_rank