Transaction Reconcile query for Payment,Receipts,Bank Statements QUERY

Transaction Reconcile query for Payment,Receipts,Bank Statements QUERY


SELECT cbe.bank_account_num "BANK_ACCOUNT_NUMBER" /* RECEIPTS QUERY*/
 ,  to_char(acrha.gl_date, 'DD-MON-YYYY') "GL_DATE"
 , 'RECEIPT' "TYPE"
 , hp.party_name "NAME"
 , TO_CHAR(acra.receipt_date, 'DD-MON-YYYY') "TRANSACTION_DATE"
 , TO_CHAR(aps.due_date, 'DD-MON-YYYY') "MATURITY_DATE"
 , acrha.status "STATUS"
 , to_char(acra.receipt_number) "NUMBER"
 , acra.currency_code "CURRENCY"
 , NULL "LINE_NUMBER"
 , acrha.acctd_amount "FUNCTIONAL_AMOUNT"
 , acrha.amount "ENTERED_AMOUNT"
FROM apps.ar_cash_receipts_all acra
 , apps.ar_cash_receipt_history_all acrha
 , apps.hz_cust_accounts hca
 , apps.hz_parties hp
 , apps.ar_payment_schedules_all aps
 , apps.ar_receipt_methods arm
 , apps.ar_receipt_classes arc
 , ce_bank_accounts cbe   
 , ce_bank_acct_uses_all cba
 WHERE acra.cash_receipt_id = acrha.cash_receipt_id
 AND acra.org_id = acrha.org_id
 AND cba.bank_acct_use_id = acra.remit_bank_acct_use_id
 AND cba.bank_account_id = cbe.bank_account_id(+)
 AND EXISTS (select 1 from apps.q_bank_account_reconcile_v where bank_account_number=cbe.bank_account_num and exclude='N')
 AND SYSDATE BETWEEN NVL(cbe.start_date,SYSDATE)  AND NVL(cbe.end_date,SYSDATE)
 AND cba.bank_account_id= NVL (:p_num_bank_account_id, cba.bank_account_id)
 AND trunc(acrha.gl_date) <= trunc(:P_DTE_GL_DATE)
 AND acra.pay_from_customer = hca.cust_account_id(+)
 AND hca.party_id = hp.party_id(+)
 AND acra.cash_receipt_id = aps.cash_receipt_id(+)
 AND acra.receipt_method_id = arm.receipt_method_id
 AND acrha.cash_receipt_history_id =
 (SELECT MAX (cash_receipt_history_id)
 FROM apps.ar_cash_receipt_history_all acrha1
 WHERE acra.cash_receipt_id = acrha1.cash_receipt_id
 AND trunc(acrha1.gl_date) <= trunc(:P_DTE_GL_DATE)
 )
 AND acrha.status NOT IN ('CLEARED', 'REVERSED')
 AND acra.receipt_method_id = arm.receipt_method_id
 AND arm.receipt_class_id = arc.receipt_class_id
 AND arc.notes_receivable <> 'Y'
 UNION ALL
 SELECT distinct cbe.bank_account_num "BANK_ACCOUNT_NUMBER"  /* PAYMENT QUERY*/
 , TO_CHAR (NVL (apc.cleared_date, :P_DTE_GL_DATE), 'DD-MON-YYYY') "GL_DATE"
 ,'PAYMENT' "TYPE"
 , apc.vendor_name "NAME"
 , TO_CHAR(apc.check_date, 'DD-MON-YYYY') "TRANSACTION_DATE"
 , NULL "MATURITY_DATE"
 , apc.status_lookup_code "STATUS"
 , to_char(apc.check_number) "NUMBER"
 , apc.currency_code "CURRENCY"
 , NULL "LINE_NUMBER"
 , NVL (apc.base_amount, apc.amount) "FUNCTIONAL_AMOUNT"
 , apc.amount "ENTERED_AMOUNT"
 FROM ap_checks_all apc
 , FND_LOOKUP_VALUES alc
 , ce_bank_accounts cbe
 , ap_invoice_payments_all aip
 , ce_bank_acct_uses_all cba   
 WHERE 1=1
 AND cba.bank_account_id = NVL (:p_num_bank_account_id, cba.bank_account_id)
 AND apc.status_lookup_code = alc.lookup_code
 and aip.check_id = apc.check_id
 AND alc.lookup_type = 'CHECK STATE'
 AND alc.LANGUAGE = USERENV ('LANG')
 AND alc.VIEW_APPLICATION_ID = 200
 AND cba.bank_acct_use_id = apc.ce_bank_acct_use_id
 AND cba.bank_account_id = cbe.bank_account_id(+)
 AND EXISTS (select 1 from apps.Q_BANK_ACCOUNT_RECONCILE_V where bank_account_number=cbe.bank_account_num and exclude='N')
 AND apc.check_date BETWEEN (SELECT MIN(check_date) FROM apps.ap_checks_all) AND trunc(:P_DTE_GL_DATE)
 UNION ALL
 SELECT cbe.bank_account_num "BANK_ACCOUNT_NUMBER" /* JOURNAL QUERY*/
      ,to_char(:P_DTE_GL_DATE,'DD-MON-YYYY') "GL_DATE"
      ,UPPER(l2.meaning) "TYPE"
      , null "NAME"
      --, TO_CHAR (jel.effective_date, 'DD-MON-YYYY') "TRANSACTION_DATE"
      , TO_CHAR(jel.effective_date, 'DD-MON-YYYY') "TRANSACTION_DATE"
      , NULL "MATURITY_DATE"
      , NULL "STATUS"
      , NULL "NUMBER"
      , jeh.currency_code "CURRENCY"
      , NULL "LINE_NUMBER"
      , DECODE (l2.meaning , 'Debit', (DECODE (NVL (jel.entered_dr , NVL (jel.accounted_dr, 0) ) , 0, jel.accounted_cr , jel.accounted_dr ) ) , 'Credit', -1 *
           (DECODE (NVL (jel.entered_dr, NVL (jel.accounted_dr, 0) ) , 0, jel.accounted_cr , jel.accounted_dr ) ) ) "FUNCTIONAL_AMOUNT"
      , DECODE (l2.meaning , 'Debit', (DECODE (NVL (jel.entered_dr , NVL (jel.accounted_dr, 0) ) , 0, jel.entered_cr , jel.entered_dr ) ) , 'Credit', -1 *
           (DECODE (NVL (jel.entered_dr , NVL (jel.accounted_dr, 0) ) , 0, jel.entered_dr , jel.entered_cr ) ) ) "ENTERED_AMOUNT"
 FROM apps.gl_je_lines jel
    , apps.ce_lookups l2
    , apps.gl_sets_of_books sob
    , apps.ce_system_parameters SYS
    , apps.gl_je_headers jeh
    , apps.ce_statement_reconcils_all cre
    , ce_bank_accounts cbe
 WHERE l2.lookup_type = 'TRX_TYPE'
 AND l2.lookup_code = DECODE (DECODE (NVL (jel.entered_dr, 0) , 0, NVL (jel.accounted_dr, 0) , jel.entered_dr ) , 0, 'JE_CREDIT' , 'JE_DEBIT' )
 AND DECODE (cbe.currency_code , sob.currency_code, jeh.currency_code , cbe.currency_code) = jeh.currency_code
 AND jeh.je_header_id = jel.je_header_id
 AND jeh.je_source NOT IN('Payables', 'Receivables', 'AP Translator', 'AR Translator', 'Treasury')
 AND EXISTS (select 1 from apps.q_bank_account_reconcile_v where bank_account_number=cbe.bank_account_num and exclude='N')
 AND cre.je_header_id(+) = jel.je_header_id
 AND cre.reference_id(+) = jel.je_line_num
 AND cre.reference_type(+) = 'JE_LINE'
 AND NVL (cre.status_flag, 'U') = 'U'
 AND NVL (cre.current_record_flag, 'Y') = 'Y'
 AND '' || jel.status(+) = 'P'
 AND jel.code_combination_id = cbe.asset_code_combination_id
 AND sob.set_of_books_id = SYS.set_of_books_id
 AND jel.ledger_id = SYS.set_of_books_id
 AND jel.effective_date >= SYS.cashbook_begin_date
 AND jeh.je_category <> 'Revaluation'
 AND NOT EXISTS (
 SELECT NULL
 FROM apps.ce_statement_reconcils_all cre2
 , apps.ce_system_parameters sys2
 WHERE jel.je_header_id = cre2.je_header_id
 AND jel.je_line_num = cre2.reference_id
 AND cre2.status_flag = 'M'
 AND NVL (cre2.current_record_flag, 'Y') = 'Y'
 AND sys2.set_of_books_id = jel.ledger_id
 )
 AND cbe.bank_account_id = NVL (:p_num_bank_account_id, cbe.bank_account_id)
 AND trunc(jel.effective_date) <= trunc(:P_DTE_GL_DATE)
UNION ALL
 SELECT cbe.bank_account_num "BANK_ACCOUNT_NUMBER",
       TO_CHAR (csha.gl_date, 'DD-MON-YYYY') "GL_DATE",
       DECODE (csl.trx_type,
               'CREDIT', 'BANK RECEIPT',
               'DEBIT', 'BANK PAYMENT',
               csl.trx_type)
          "TYPE",
       csha.statement_number "NAME",
       TO_CHAR (csl.trx_date, 'DD-MON-YYYY') "TRANSACTION_DATE",
       NULL "MATURITY_DATE",
       DECODE(csl.status,'RECONCILED','PARTIAL RECONCILED',csl.status) "STATUS",
       TO_CHAR (csl.bank_trx_number) "NUMBER",
       csl.currency_code "CURRENCY",
       csl.line_number "LINE_NUMBER",
       DECODE(csl.status,'RECONCILED',(select csl.amount-SUM(cr.amount) from ce_statement_reconcils_all cr where csl.STATEMENT_LINE_ID = cr.STATEMENT_LINE_ID),csl.amount) "FUNCTIONAL_AMOUNT",
      DECODE(csl.status,'RECONCILED',(select csl.amount-SUM(cr.amount) from ce_statement_reconcils_all cr where csl.STATEMENT_LINE_ID = cr.STATEMENT_LINE_ID),csl.amount)  "ENTERED_AMOUNT"
     -- csl.amount "ENTERED_AMOUNT"
  FROM apps.CE_STATEMENT_HEADERS csha,
       apps.ce_statement_lines csl,
       ce_bank_accounts cbe
 WHERE     csha.bank_account_id = cbe.bank_account_id
    AND csha.bank_account_id =
              NVL ( :p_num_bank_account_id, csha.bank_account_id)
       AND TRUNC (csha.statement_date) <= TRUNC ( :P_DTE_GL_DATE)
       AND csha.statement_header_id = csl.statement_header_id
       AND EXISTS
              (SELECT 1
                 FROM apps.q_bank_account_reconcile_v
                WHERE     bank_account_number = cbe.bank_account_num
                      AND exclude = 'N')
       AND NOT EXISTS
                  (SELECT 1
                     FROM ce_statement_lines csl1,
                          ce_statement_reconcils_all csr
                    WHERE     csl1.STATEMENT_HEADER_ID =
                                 csl.STATEMENT_HEADER_ID
                          AND csl1.STATEMENT_LINE_ID = csr.STATEMENT_LINE_ID
                          AND csl1.STATEMENT_LINE_ID = csl.STATEMENT_LINE_ID
                          AND csl1.amount = csr.amount
                          AND csr.status_flag = 'M'
                          AND csr.current_record_flag = 'Y')
       AND UPPER (csha.statement_number) NOT LIKE '%DUMMY ST%'
       AND SYSDATE BETWEEN NVL (cbe.start_date, SYSDATE)
                       AND NVL (cbe.end_date, SYSDATE)

QUERY TO LINK BETWEEN FA , AP AND RCV SHIPMENTS

SELECT DISTINCT gcc.segment1 bc,
         gcc.segment2 rc,
         gcc.segment3 department_code,
         fad.asset_number asset_number,
         fad.attribute_category_code asset_category,
         fad.description asset_desc,
         fab.cost cost,
         fai.po_number,
         fai.invoice_number,
         CONVERT (fai.description, 'AL32UTF8') line_description,
         fai.fixed_assets_cost line_amount,
         aps.segment1 supplier_number,
         aps.vendor_name supplier_name,
         to_char(fab.date_placed_in_service,'DD-MON-YY') date_in_service
         ,fth.transaction_type_code transaction_type
         ,ftv.period_effective effective_period
         ,ftv.period_entered
         ,aia.doc_sequence_value voucher_num
         ,aia.invoice_currency_code invoice_currency
         ,rsh.receipt_num receipt_number
         ,to_char(rsh.creation_date,'DD-MON-YY') receipt_date
    FROM fa_additions_v fad,
         fa_books fab,
         fa_distribution_history fdh,
         gl_code_combinations gcc,
         fa_asset_invoices fai,
         ap_suppliers aps,
         ap_invoices_all aia,
         fa_book_controls bc,
         ap_invoice_distributions_all aida
         ,fa_transaction_headers fth
         ,fa_transaction_history_trx_v ftv
         ,fa_lookups_tl lotl,
         fa_lookups_b flb
         ,rcv_transactions rt
         ,rcv_shipment_headers rsh
   WHERE     fad.asset_id = fdh.asset_id
         AND fab.book_type_code = :p_book_type
         AND fab.Date_ineffective IS NULL
         AND fai.invoice_id = aia.invoice_id
         AND aida.invoice_id = aia.invoice_id
         AND bc.book_type_code = fab.book_type_code
         AND fdh.transaction_header_id_in = fth.transaction_header_id
         AND flb.lookup_code = lotl.lookup_code
         AND flb.lookup_code = fth.transaction_type_code
         AND rt.transaction_id = aida.rcv_transaction_id
         AND rt.shipment_header_id = rsh.shipment_header_id
         AND ftv.transaction_header_id = fth.transaction_header_id
         AND fdh.date_ineffective IS NULL   
         AND(( (:p_from_period = :p_to_period) AND
                    fdh.date_effective < (NVL((SELECT dp.period_close_date
                   FROM fa_deprn_periods dp
                  WHERE dp.book_type_code = :p_book_type
                        AND dp.period_name = :p_to_period),SYSDATE)))
                OR ( fdh.date_effective between
                (SELECT dp.period_open_date
                   FROM fa_deprn_periods dp
                  WHERE dp.book_type_code = :p_book_type
                        AND dp.period_name = :p_from_period)
            AND NVL((SELECT dp.period_close_date
                   FROM fa_deprn_periods dp
                  WHERE dp.book_type_code = :p_book_type
                        AND dp.period_name = :p_to_period),SYSDATE)) )     
         AND fab.book_type_code = fdh.book_type_code
         AND fab.asset_id = fdh.asset_id
         AND fdh.code_combination_id = gcc.code_combination_id
AND fad.asset_type = DECODE(:p_asset_type,'ALL', fad.asset_type,:p_asset_type)
         AND gcc.segment1 = DECODE (:p_chr_bc, 'ALL', gcc.segment1, :p_chr_bc)
         AND gcc.segment2 = DECODE (:p_chr_rc, 'ALL', gcc.segment2, :p_chr_rc)
         AND fdh.asset_id = fai.asset_id(+)
         AND fai.date_ineffective IS NULL
         AND aps.vendor_id(+) = fai.po_vendor_id
ORDER BY fad.asset_number

ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

SELECT fu.user_name user_name
      ,fu.description user_description
      ,fu.email_address user_email
      ,ppf.full_name employee_name
      ,hou.name business_group
      ,fr.responsibility_name resp_name
FROM apps.fnd_user fu
    ,apps.per_all_people_f ppf
    ,apps.hr_all_organization_units hou
    ,apps.fnd_user_resp_groups_all fur
    ,apps.fnd_responsibility_tl fr
WHERE ppf.person_id = fu.employee_id
AND hou.organization_id = ppf.business_group_id
AND fu.user_id = fur.user_id
AND NVL (fur.end_date, SYSDATE + 1) > SYSDATE
AND fur.responsibility_id = fr.responsibility_id
--AND fr.responsibility_name LIKE 'Shipping User'
AND fr.LANGUAGE = 'US'
ORDER BY fu.user_name
        ,fr.responsibility_name

Active Employees and Their Jobs in oracle apps

Active Employees and Their Jobs

SELECT papf.full_name emp_name,
      papf.current_employee_flag current_emp_flag,
      papf.employee_number emp_number,
      b.d_job_id job_title,
      papf.email_address email

FROM  apps.PER_ALL_PEOPLE_F       papf,
      apps.PER_ALL_ASSIGNMENTS_F asg,
      apps.PER_ASSIGNMENTS_V7    b,
      apps.PER_JOBS              pjb
   
WHERE papf.person_id       = asg.person_id(+)
AND asg.person_id          = b.person_id
AND b.effective_start_date =
  (SELECT MAX (b2.effective_start_date)
  FROM apps.per_assignments_v7 b2
  WHERE b2.person_id = b.person_id
  )
AND NVL (papf.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (asg.effective_end_date, SYSDATE  + 1) > SYSDATE
AND b.job_id                                   = pjb.job_id(+)
ORDER BY papf.full_name

Employee supervisor Oracle R12 SQL Query

# Oracle EBS SQL Query that will list all employees in the hr.per_all_assignments_f 
# table and their supervisor, in readable format.

SELECT DISTINCT papf1.full_name leve1_full_name
     , papf1.employee_number level1_empno
     , papf2.full_name leve2_full_name
     , papf2.employee_number level2_empno
  FROM hr.per_all_people_f papf1
     , hr.per_all_assignments_f paaf1
     , hr.per_all_assignments_f paaf2
     , hr.per_all_people_f papf2
  WHERE  papf1.person_id = paaf1.person_id
  AND paaf1.supervisor_id = papf2.person_id(+)
  AND papf2.person_id = paaf2.person_id

  ORDER BY leve1_full_name;

Open PO's to display the information regarding the PO in oracle apps R12

Open PO's to display the information regarding the PO like Amount Billed,Amount Ordered in Shipment Level.

select   
(l.quantity*l.unit_price) "Amount Ordered", 
(select sum(aid.amount) 
 from ap_invoices_all aia,
      ap_invoice_distributions_all aid
where aia.invoice_id = aid.invoice_id
and   aid.po_distribution_id = d.po_distribution_id)"Amount Billed",
(select sum(aip.amount )
from AP_INVOICE_PAYMENTS_all aip ,
     ap_invoice_distributions_all adi
where aip.invoice_id=adi.invoice_id
and   adi.po_distribution_id= d.po_distribution_id)"Amount Received",

gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 "Charge account",
ll.ship_to_location_id "Ship to Location",
l.quantity            "quantity",
ll.quantity          "Ordered Quantity",
ll.quantity_Billed     "Billed Quantity",
ll.quantity_received  "Received Quantity",
h.segment1,
d.po_distribution_id
from   
po.po_headers_all h,   
po.po_lines_all l,   
po.po_line_locations_all ll,   
po.po_distributions_all d,
gl_code_combinations   gcc
where h.po_header_id = l.po_header_id   
and  gcc.code_combination_id=d.code_combination_id
and ll.po_line_id = l.po_Line_id   
and ll.line_location_id = d.line_location_id  
and h.segment1='1505'
and h.closed_date is null ;

PROCEDURE TO PRINT LOG INFORMATION FROM BACKEND AND FRONT END IN ORACLE APPS

PROCEDURE TO PRINT THE LOG INFORMATION FROM BACKEND AND FRONT END

PROCEDURE print_log(p_message IN VARCHAR2)
IS

BEGIN
    fnd_file.put_line(fnd_file.LOG,p_message);
    dbms_output.put_line(p_message);
END print_log;

query we can find the custom top path in oracle apps

query we can find the custom top path.

select distinct variable_name, value

from fnd_env_context

where variable_name like '%CUS%_TOP' --escape '\'

order by variable_name;

link between order table,customer table and shipping tables in oracle apps r12

link between OE ,HZ AND WSH Tables in oracle apps r12

select DISTINCT wnd.name shipper_no,
       wnd.confirm_date,
       hp.party_name,
       hcsua.location "Customer address",
       hl.address1,
       hl.city,
       hl.state,
       hl.postal_code,
       hl.country,
       ool.header_id,
       msi.segment1 L_L_Item_No,
       wlp.license_plate_number,
       ool.ordered_item "Customer_Part_No",
       wda.delivery_id,
       msi.inventory_item_id,
     ool.ordered_item "CUST_1",
  msi.segment1 "ITEM_1"
  from wsh_new_deliveries wnd,
       hz_cust_accounts_all hca,
       hz_parties hp,
       hz_locations hl,
       wsh_delivery_assignments wda,
       wsh_delivery_details wdd,
       oe_order_lines_all ool,
       mtl_system_items_b msi,
       mtl_parameters mp,
       wsh_delivery_details wdd_p,
       wms_license_plate_numbers wlp,
        hz_cust_site_uses_all hcsua
 where wnd.customer_id = hca.cust_account_id
   and hca.party_id = hp.party_id
   and wnd.delivery_id = wda.delivery_id
   and wda.delivery_detail_id = wdd.delivery_detail_id
   and hl.location_id = wnd.ultimate_dropoff_location_id
   and wdd.source_line_id = ool.line_id
   and wdd.source_header_id = ool.header_id
   and ool.inventory_item_id = elss.inventory_item_id
   and ool.inventory_item_id = msi.inventory_item_id
   and ool.ship_from_org_id = msi.organization_id
   and mp.organization_code='000'
   and wda.parent_delivery_detail_id = wdd_p.delivery_detail_id
   and wdd_p.lpn_id = wlp.lpn_id
   and ool.ship_to_org_id = hcsua.site_use_id(+)
   and wnd.name='7453179';

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;

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.ap_suppliers              s,

       ap.ap_supplier_sites_all     st,

       hr_operating_units           ou,

       iby.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 Custom Oracle Alert

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

SELECT alr.application_id,

       alr.alert_id,

       alr.alert_name,

       alr.start_date_active,

       alr.description,

       alr.sql_statement_text

  FROM alr.alr_alerts alr

 WHERE 1=1

   AND alr.created_by <> 1      -- show only custom alerts

   AND alr.enabled_flag = 'Y';  -- show only enabled alerts

Query to find Parameters and Value Sets associated with a Concurrent Program

SELECT fcpl.user_concurrent_program_name  "Concurrent Program Name",

       fcp.concurrent_program_name        "Program Short Name",

       fdfcuv.column_seq_num              "Column Seq #",

       fdfcuv.end_user_column_name        "Parameter Name",

       fdfcuv.form_left_prompt            "Prompt Name",

       fdfcuv.enabled_flag                "Enabled Flag",

       fdfcuv.required_flag               "Required Flag",

       fdfcuv.display_flag                "Display Flag",

       fdfcuv.flex_value_set_id           "Value Set ID",

       ffvs.flex_value_set_name           "Value Set Name",

       flv.meaning                        "Default Type",

       fdfcuv.default_value               "Default Value"

  FROM fnd_concurrent_programs      fcp,

       fnd_concurrent_programs_tl   fcpl,

       fnd_descr_flex_col_usage_vl  fdfcuv,

       fnd_flex_value_sets          ffvs,

       fnd_lookup_values            flv

 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id

   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name

   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id

   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'

   AND flv.lookup_code(+)                 =  fdfcuv.default_type

   AND fcpl.LANGUAGE                      =  USERENV('LANG')

   AND flv.LANGUAGE(+)                    =  USERENV('LANG')

   AND fdfcuv.enabled_flag                =  'Y'

   AND fcpl.user_concurrent_program_name  =  'XX AR Conversion Program'  -- <change it>

 ORDER BY fdfcuv.column_seq_num;