Query to find Concurrent Program related information

The following query takes the concurrent program name (for example, "Active Users") and returns its related information (i.e. Reports, Created By, etc.).

Note: Mr. Rajkumar Reddy had pointed out that I could use the "lookup" as opposed to my previous hard-coded "decode" for finding the Execution Method (or program type), which is very efficient. I thank Mr. Raj for sharing this.

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

-- Query to find Concurrent Program related information

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

SELECT cpv.user_concurrent_program_name  "Concurrent Program Name",

       cpv.concurrent_program_name       "Program Short Name",

       efv.application_name              "Application Name",

       cpv.enabled_flag                  "Enabled Flag",

       cpv.output_file_type              "Output Format",

       fu.user_name                      "Created By (userid)",

       (SELECT meaning

          FROM FND_LOOKUP_VALUES_VL flv

         WHERE UPPER (flv.lookup_type) = 'CP_EXECUTION_METHOD_CODE'

           AND flv.Lookup_code = efv.execution_method_code

       )                                 "Execution Method",

       efv.executable_name               "Executable Name",

       efv.execution_file_name           "Execution Filename"

  FROM FND_CONCURRENT_PROGRAMS_VL  cpv,

       FND_EXECUTABLES_FORM_V      efv,

       FND_USER                    fu

 WHERE efv.executable_id  = cpv.executable_id

   AND efv.application_id = cpv.application_id

   AND cpv.created_by     = fu.user_id

   AND cpv.user_concurrent_program_name = 'Active Users' -- // change it

 ORDER BY cpv.user_concurrent_program_name;

Query to find GL to AR Receiving Transactions

The following query finds GL to AR Receiving Transactions. This query is written by Mr. Anil Patil and can also be found in this link:

http://oracle.anilrpatil.com/2009/12/12/drilldown-from-gl-to-receiving-transactions-2/

I have reposted this for sharing purpose only.

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

-- Query to find GL to AR Receiving Transactions

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

SELECT

       b.NAME                        je_batch_name,

       b.description                 je_batch_description,

       b.running_total_accounted_dr  je_batch_total_dr,

       b.running_total_accounted_cr  je_batch_total_cr,

       b.status                      je_batch_status,

       b.default_effective_date      je_batch_effective_date,

       b.default_period_name         je_batch_period_name,

       b.creation_date               je_batch_creation_date,

       u.user_name                   je_batch_created_by,

       h.je_category                 je_header_category,

       h.je_source                   je_header_source,

       h.period_name                 je_header_period_name,

       h.NAME                        je_header_journal_name,

       h.status                      je_header_journal_status,

       h.creation_date               je_header_created_date,

       u1.user_name                  je_header_created_by,

       h.description                 je_header_description,

       h.running_total_accounted_dr  je_header_total_acctd_dr,

       h.running_total_accounted_cr  je_header_total_acctd_cr,

       l.je_line_num                 je_lines_line_number,

       l.ledger_id                   je_lines_ledger_id,

       glcc.concatenated_segments    je_lines_ACCOUNT,

       l.entered_dr                  je_lines_entered_dr,

       l.entered_cr                  je_lines_entered_cr,

       l.accounted_dr                je_lines_accounted_dr,

       l.accounted_cr                je_lines_accounted_cr,

       l.description                 je_lines_description,

       glcc1.concatenated_segments   xla_lines_account,

       xlal.accounting_class_code    xla_lines_acct_class_code,

       xlal.accounted_dr             xla_lines_accounted_dr,

       xlal.accounted_cr             xla_lines_accounted_cr,

       xlal.description              xla_lines_description,

       xlal.accounting_date          xla_lines_accounting_date,

       xlate.entity_code             xla_trx_entity_code,

       xlate.source_id_int_1         xla_trx_source_id_int_1,

       xlate.source_id_int_2         xla_trx_source_id_int_2,

       xlate.source_id_int_3         xla_trx_source_id_int_3,

       xlate.security_id_int_1       xla_trx_security_id_int_1,

       xlate.security_id_int_2       xla_trx_security_id_int_2,

       xlate.transaction_number      xla_trx_transaction_number,

       rcvt.transaction_type         rcv_trx_transaction_type,

       rcvt.transaction_date         rcv_trx_transaction_date,

       rcvt.quantity                 rcv_trx_quantity,

       rcvt.shipment_header_id       rcv_trx_shipment_header_id,

       rcvt.shipment_line_id         rcv_trx_shipment_line_id,

       rcvt.destination_type_code    rcv_trx_destination_type_code,

       rcvt.po_header_id             rcv_trx_po_header_id,

       rcvt.po_line_id               rcv_trx_po_line_id,

       rcvt.po_line_location_id      rcv_trx_po_line_location_id,

       rcvt.po_distribution_id       rcv_trx_po_distribution_id,

       rcvt.vendor_id                rcv_trx_vendor_id,

       rcvt.vendor_site_id           rcv_trx_vendor_site_id

  FROM

       gl_je_batches                 b,

       gl_je_headers                 h,

       gl_je_lines                   l,

       fnd_user                      u,

       fnd_user                      u1,

       gl_code_combinations_kfv      glcc,

       gl_code_combinations_kfv      glcc1,

       gl_import_references          gir,

       xla_ae_lines                  xlal,

       xla_ae_headers                xlah,

       xla_events                    xlae,

       xla.xla_transaction_entities  xlate,

       rcv_transactions              rcvt

 WHERE

       1=1

   AND b.created_by              =  u.user_id

   AND h.created_by              =  u1.user_id

   AND b.je_batch_id             =  h.je_batch_id

   AND h.je_header_id            =  l.je_header_id

   AND l.code_combination_id     =  glcc.code_combination_id

   AND l.je_header_id            =  gir.je_header_id

   AND l.je_line_num             =  gir.je_line_num

   AND gir.gl_sl_link_table      =  xlal.gl_sl_link_table

   AND gir.gl_sl_link_id         =  xlal.gl_sl_link_id

   AND xlal.application_id       =  xlah.application_id

   AND xlal.ae_header_id         =  xlah.ae_header_id

   AND xlal.code_combination_id  =  glcc1.code_combination_id

   AND xlah.application_id       =  xlae.application_id

   AND xlah.event_id             =  xlae.event_id

   AND xlae.application_id       =  xlate.application_id

   AND xlae.entity_id            =  xlate.entity_id

   AND xlate.source_id_int_1     =  rcvt.transaction_id

   AND h.je_category             =  'Receiving'

   AND b.default_period_name     =  'DEC-12'

 ORDER BY h.je_category;

Query to find GL Period-wise Transaction Summary

Query to find GL Period-wise Transaction Summary.

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

-- Query to find GL Period-wise Transaction Summary

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

SELECT

       b.name                        batch_name,

       b.description                 batch_description,

       b.running_total_accounted_dr  batch_total_dr,

       b.running_total_accounted_cr  batch_total_cr,

       b.status                      batch_status,

       b.default_effective_date      effective_date,

       b.default_period_name         batch_period_name,

       b.creation_date,

       u.user_name                   batch_created_by,

       h.je_category,

       h.je_source,

       h.period_name                 je_period_name,

       h.name                        journal_name,

       h.status                      journal_status,

       h.creation_date               je_created_date,

       u1.user_name                  je_created_by,

       h.description                 je_description,

       h.running_total_accounted_dr  je_total_dr,

       h.running_total_accounted_cr  je_total_cr,

       l.je_line_num                 line_number,

       l.ledger_id,

       glcc.concatenated_segments    account,

       l.entered_dr,

       l.entered_cr,

       l.accounted_dr,

       l.accounted_cr,

       xlal.unrounded_accounted_dr   xla_unrounded_accounted_dr,

       xlal.unrounded_accounted_cr   xla_unrounded_accounted_cr,

       l.description,

       xlal.code_combination_id,

       xlal.accounting_class_code,

       xlal.accounted_dr             xlal_accounted_dr,

       xlal.accounted_cr             xlal_accounted_cr,

       xlal.description              xlal_description,

       xlal.accounting_date          xlal_accounting_date,

       xlate.entity_code             xlate_entity_code,

       xlate.source_id_int_1         xlate_source_id_int_1,

       xlate.source_id_int_2         xlate_source_id_int_2,

       xlate.source_id_int_3         xlate_source_id_int_3,

       xlate.security_id_int_1       xlate_security_id_int_1,

       xlate.security_id_int_2       xlate_security_id_int_2,

       xlate.transaction_number      xlate_transaction_number

  FROM

       gl_je_batches                 b,

       gl_je_headers                 h,

       gl_je_lines                   l,

       fnd_user                      u,

       fnd_user                      u1,

       gl_code_combinations_kfv      glcc,

       gl_import_references          gir,

       xla_ae_lines                  xlal,

       xla_ae_headers                xlah,

       xla_events                    xlae,

       xla.xla_transaction_entities  xlate

 WHERE 
       1=1

   AND b.created_by              =  u.user_id

   AND h.created_by              =  u1.user_id

   AND b.je_batch_id             =  h.je_batch_id

   AND h.je_header_id            =  l.je_header_id

   AND xlal.code_combination_id  =  glcc.code_combination_id

   AND l.je_header_id            =  gir.je_header_id

   AND l.je_line_num             =  gir.je_line_num

   AND gir.gl_sl_link_table      =  xlal.gl_sl_link_table

   AND gir.gl_sl_link_id         =  xlal.gl_sl_link_id

   AND xlal.ae_header_id         =  xlah.ae_header_id

   AND xlah.event_id             =  xlae.event_id

   AND xlae.entity_id            =  xlate.entity_id

   AND xlae.application_id       =  xlate.application_id

      -- AND XLATE.SOURCE_ID_INT_1 = RCVT.TRANSACTION_ID

   AND h.je_source               =  'Receivables'

   AND h.period_name             =  'NOV-12' --'&PERIOD_NAME'

   

Query to find Bank information

Bank, Bank Account, and Bank Branches information from R12.

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

-- Query to find Bank, Bank Account, and Bank Branches information

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

SELECT cba.bank_account_name            "Bank Account Name",

       cba.bank_account_num             "Bank Account Number",

       cba.multi_currency_allowed_flag  "Multi Currency Flag",

       cba.zero_amount_allowed          "Zero Amount Flag",

       cba.account_classification       "Account Classification",

       bb.bank_name                     "Bank Name",

       bb.bank_branch_type              "Bank Branch Type",

       bb.bank_branch_name              "Bank Branch Name",

       bb.bank_branch_number            "Bank Branch Number",

       bb.eft_swift_code                "Swift Code",

       -- bb.description                   "Description",

       ou.name                          "Operating Unit",

       gcf.concatenated_segments        "GL Code Combination"

  FROM ce_bank_accounts          cba,

       ce_bank_acct_uses_all     bau,

       cefv_bank_branches        bb,

       hr_operating_units        ou,

       gl_code_combinations_kfv  gcf

 WHERE cba.bank_account_id = bau.bank_account_id

   AND cba.bank_branch_id  = bb.bank_branch_id

   AND ou.organization_id  = bau.org_id

   AND cba.asset_code_combination_id = gcf.code_combination_id

   AND (cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE))

 ORDER BY TO_NUMBER(cba.bank_account_num);

Query to find Service Contract, Line, Subline, Item, Order Information

The following query lists all the service contract related information.

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

-- Query to find Service Contract, Line, Subline, Item, Order Information

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

SELECT okh.contract_number         "Contract Number",

       okh.scs_code                "Code",

       ooh.order_number            "Order Number",

       TO_NUMBER(sl.line_number)   "SL Line",

       sl.lse_id                   "SL LSE ID",

       TO_NUMBER(cl.line_number)   "CL Line",

       cl.lse_id                   "CL LSE ID",

       TO_CHAR(cl.id)              "CL ID",

       msi.inventory_item_id,

       msi.segment1                "Item Number",

       cii.serial_number           "Serial Number",

       okh.start_date              "Contract Start Date",

       sl.start_date               "Line Start Date",

       cl.start_date               "Subline Start Date"

  FROM okc_k_headers_all_b   okh,

       okc_k_lines_b         sl,

       okc_k_lines_b         cl,

       okc_k_items           oki,

       okc_k_rel_objs        rel,

       csi_item_instances    cii,

       mtl_system_items_b    msi,

       oe_order_headers_all  ooh

 WHERE 1=1

   AND okh.id                   =  sl.chr_id

   AND cl.cle_id                =  sl.id

   AND sl.cle_id IS NULL

   AND cl.id                    =  oki.cle_id(+)

   AND oki.object1_id1          =  cii.instance_id

   AND cii.inventory_item_id    =  msi.inventory_item_id

   AND okh.inv_organization_id  =  msi.organization_id

   AND rel.chr_id               =  okh.id

   AND rel.jtot_object1_code    =  'OKX_ORDERHEAD'

   AND rel.object1_id1          =  ooh.header_id

   AND oki.object1_id2          =  '#'

   ----

   -- AND okh.cust_po_number       =  ooh.cust_po_number

   -- AND okh.ship_to_site_use_id  =  ooh.ship_to_org_id

   --

   /* if subline start date is earlier than line start date */

   -- AND sl.start_date > cl.start_date

   ----

   -- following conditions are for test purpose only

   ----

   -- AND okh.contract_number = '12796'

   -- AND sl.line_number      = 1

 ORDER BY okh.contract_number,

       TO_NUMBER(sl.line_number),

       TO_NUMBER(cl.line_number);