Convert Number into Time hours Minutes seconds in Oracle SQL

select to_char(trunc(70000/3600)) || to_char(to_date(mod(70000, 86400),'sssss'),':mi:ss') as time
from dual; 

Menu Changes and Profile Options in Oracle Apps Sysadmin

function not available to this responsibility change responsibilities or contact System Administrator


the Menu then run the program called "Compile Security" and Parameter is "Y".

Help -> Examine -> failed

Profile Option: "Hide Diagnostics menu entry"  set to No

Profile Option : "FND: Diagnostics" set to  ->  "Yes"

Profile Option: "Utilities:Diagnostics" set to "Yes"

Cost Manager in Inventory Oracle Application

The Inventory Cost Manager will kick off "Actual Cost Worker" Concurrent Program.
You can see any errors or warning in this concurrent requests.

Basically The Transactions not costed issues are:
1) Cost Cutoff date is past dated for that organization.
2) Cost manager is not Running
3) Tables Space issue
4) Resource Overhead cost issue.

-- Cost Cutoff date
select organization_code, COST_CUTOFF_DATE from apps.mtl_parameters
where organization_id = 114

-- Wip transactions errors
select * from apps.WIP_COST_TXN_INTERFACE
where Process_Status = 3


Select mp.organization_code,transaction_id, mmt.Organization_Id, Inventory_Item_Id, Cost_Group_Id, error_explanation, mmt.*
From Inv.Mtl_Material_Transactions mmt, apps.mtl_parameters mp
 Where Costed_Flag = 'E'
 and mp.organization_id = mmt.organization_id
 order by 1 ;


-- Cost manager Concurrent Request
SELECT request_id requestid, request_date requestdt, phase_code phase, status_code status
  FROM apps.fnd_concurrent_requests fcr, apps.fnd_concurrent_programs fcp
 WHERE fcp.application_id = 702 AND fcp.concurrent_program_name = 'CMCTCM'
 AND fcr.concurrent_program_id = fcp.concurrent_program_id AND fcr.program_application_id = 702 AND fcr.phase_code <> 'C'

Item Cost Details Query in Oracle Applications

SELECT msi.segment1 "ITEM_NAME", msi.inventory_item_id, cic.item_cost, mp.organization_code, mp.organization_id, cct.cost_type, cct.description, cic.tl_material, cic.tl_material_overhead, cic.material_cost, cic.material_overhead_cost, cic.tl_item_cost,
       cic.unburdened_cost, cic.burden_cost, pl_resource recorce_cost, pl_overhead overhead_cost
  FROM apps.cst_cost_types cct, apps.cst_item_costs cic, apps.mtl_system_items_b msi, apps.mtl_parameters mp
 WHERE cct.cost_type_id = cic.cost_type_id
   AND cic.inventory_item_id = msi.inventory_item_id
   AND cic.organization_id = msi.organization_id
   AND msi.organization_id = mp.organization_id
   AND msi.segment1 = '70002402'
   AND mp.organization_code = 'SHO'
   AND cct.cost_type = 'Average';

PO XLA GL and RCV XLA GL queries in Oracle Applications

/* Formatted on 2017/08/08 14:31 (Formatter Plus v4.8.8) */
SELECT poh.org_id, poh.segment1 po_num, pol.line_num, poll.shipment_num, por.release_num, pod.distribution_num, NVL (poll.price_override, pol.unit_price) unit_price, pod.quantity_ordered, pod.quantity_delivered, pod.quantity_billed,
       pod.quantity_cancelled, xdl.accounting_line_code, xdl.line_definition_code, xdl.event_class_code, xal.accounted_dr, xal.accounted_cr, jeh.ledger_id, jeb.NAME, jeb.creation_date, jeh.je_source, jeh.je_category, jel.je_line_num,
       NVL (jel.accounted_dr, 0) accounted_dr, NVL (jel.accounted_cr, 0) accounted_cr, glcc.segment1, glcc.segment2, glcc.segment3, glcc.segment4, glcc.segment5, glcc.segment6
  FROM apps.po_headers_all poh,
       apps.po_lines_all pol,
       apps.po_line_locations_all poll,
       apps.po_distributions_all pod,
       apps.po_releases_all por,
       apps.xla_distribution_links xdl,
       apps.xla_ae_lines xal,
       apps.gl_import_references gif,
       apps.gl_je_lines jel,
       apps.gl_je_headers jeh,
       apps.gl_je_batches jeb,
       apps.gl_code_combinations glcc
 WHERE 1 = 1
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.ae_header_id = xal.ae_header_id
   AND xal.gl_sl_link_table = gif.gl_sl_link_table
   AND xal.gl_sl_link_id = gif.gl_sl_link_id
   AND gif.je_header_id = jel.je_header_id
   AND gif.je_line_num = jel.je_line_num
   AND jel.code_combination_id = glcc.code_combination_id
   AND jel.je_header_id = jeh.je_header_id
   AND jeh.je_batch_id = jeb.je_batch_id
   AND xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
   AND xdl.application_id = 201
   AND pod.po_distribution_id = xdl.source_distribution_id_num_1
   AND pod.line_location_id = poll.line_location_id
   AND poll.po_line_id = pol.po_line_id
   AND pol.po_header_id = poh.po_header_id
   AND pod.po_release_id = por.po_release_id(+)

Inventory Material Transaction Accounting query in Oracle Apps

SELECT msi.segment1,ACCOUNTING_LINE_TYPE, mtt.TRANSACTION_TYPE_NAME,(select MEANING from apps.FND_LOOKUP_VALUES
WHERE LOOKUP_CODE =A.ACCOUNTING_LINE_TYPE
AND VIEW_APPLICATION_ID = 700
AND LANGUAGE = 'US'
AND LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE') ACCOUNT_LINE_TYPE, base_transaction_value, ABS(base_transaction_value) ORb, MMT.TRANSACTION_QUANTITY, MMT.ACTUAL_COST
  FROM apps.mtl_transaction_accounts a,
       apps.gl_code_combinations_kfv gcc,
       apps.mtl_system_items_b       msi,
       apps.mtl_material_transactions mmt,
       APPS.MTL_TRANSACTION_TYPES MTT
 WHERE 1 = 1
   AND mmt.transaction_date BETWEEN to_date('01-JUL-2017', 'DD-MON-YYYY') AND
       to_date('31-JUL-2017 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
   AND A.ACCOUNTING_LINE_TYPE  = 36
   AND msi.organization_id = a.organization_id
   AND msi.inventory_item_id = a.inventory_item_id
   AND a.organization_id = 125
   AND gcc.code_combination_id = a.reference_account
   AND gcc.segment4 IN ('501010', '501020')
   AND gcc.segment1 IN ('421', '422')
--   and msi.segment1 = '000000000031022' --'000000000031019' --'000000000018045'
   and mmt.transaction_id= a.transaction_id
   and msi.organization_id = mmt.organization_id
    AND msi.inventory_item_id = mmt.inventory_item_id
    and MTT.TRANSACTION_TYPE_ID=MMT.TRANSACTION_TYPE_ID

SLA Query and Information in Oracle Applications EBS

SELECT apl.application_name, xah.je_category_name,xev.EVENT_TYPE_CODE, xte.*
  FROM xla.xla_transaction_entities xte,
       xla.xla_events xev,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       apps.fnd_application_vl apl
 WHERE xev.entity_id = xte.entity_id
   AND xah.entity_id = xte.entity_id
   AND xah.event_id = xev.event_id
   AND xah.ae_header_id = xal.ae_header_id
   AND xte.application_id = apl.application_id
   AND xah.gl_transfer_status_code = 'Y'
   AND xah.accounting_date > TO_DATE ('25-JUL-2017', 'DD-MON-YYYY')
   AND xah.accounting_date < TO_DATE ('30-JUL-2017', 'DD-MON-YYYY')
   AND xah.application_id = apl.application_id
   AND xev.application_id = apl.application_id
   AND xah.je_category_name IN ('Inventory', 'WIP', 'Receiving')
   AND xah.ledger_id = 3
   AND xte.source_id_int_2 = 229
   AND apl.application_id = 707;

SELECT   SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount,
         invoice_num
    FROM ap_invoices_all aia,
         xla.xla_transaction_entities xte,
         xla_ae_headers xah,
         xla_ae_lines xal,
         gl.gl_import_references gir,
         gl_je_lines gjl,
         gl_je_headers gjh
   WHERE 1 = 1
     AND aia.invoice_id = NVL ("SOURCE_ID_INT_1", (-99))
     AND xte.entity_code = 'AP_INVOICES'
     AND xte.application_id = 200
     AND xte.entity_id = xah.entity_id
     AND xah.ae_header_id = xal.ae_header_id
     AND xal.gl_sl_link_id = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjl.je_header_id = gjh.je_header_id
     AND aia.invoice_num = 'INVNO4567'   --Invoice Num
GROUP BY aia.invoice_num, aia.invoice_amount;


SELECT b.NAME batch_name, 
                 b.description batch_description,
                 h.je_category, 
                 h.je_source,
                 h.period_name je_period_name, 
                 h.NAME journal_name,
                 h.status journal_status, 
                 h.description je_description,
                 l.je_line_num line_number    
FROM gl_je_batches b,
             gl_je_headers h,
             gl_je_lines l,
             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,
             ra_customer_trx_all rct,
WHERE 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 h.je_source = 'Receivables'
AND h.period_name = '2009-10'
AND rct.trx_number = xlate.transaction_number
AND xlate.transaction_number =':P_TRX_NUMBER

Subledger Accounting In Detail

Subledger Accounting

About SLA:

1.SLA is an intermediate step between subledger products and the Oracle General Ledger

2.Journal entries are created in Subledger Accounting and then transferred to Oracle General Ledger

Tables that are involved in this process are

XLA_TRANSACTION_ENTITIES

XLA_EVENTS

XLA_AE_HEADERS

XLA_AE_LINES

GL_IMPORT_REFERENCES

GL_JE_LINES

GL_JE_HEADERS

GL_BALANCES

NOTE: For every transaction, it will create an entity_id in xla_transaction_entities table. This column we use to make link between transaction entities and xla ae headers table. By default at first time for an entity, one event will be there. Suppose for the same transaction (means for same entity), if we create any adjustment or some other thing, that we treat it as an event. So for one entity there will be more events.

Links Between these tables

GL_JE_LINES (JE_HEADER_ID, JE_LINE_NUM)   

GL_IMPORT_REFERENCES (JE_HEADER_ID, JE_LINE_NUM)

GL_IMPORT_REFERENCES (GL_SL_LINK_TABLE, GL_SL_LINK_ID

XLA_AE_LINES (GL_SL_LINK_TABLE, GL_SL_LINK_ID)

XLA_AE_LINES (APPLICAITON_ID, AE_HEADER_ID)

XLA_AE_HEADERS (APPLICATION_ID, AE_HEADER_ID)

XLA_AE_HEADERS (APPLICATION_ID, EVENT_ID)

XLA_EVENTS (APPLICATION_ID, EVENT_ID)

XLA_EVENTS (APPLICATION_ID, ENTITY_ID)

XLA_TRANSACTION_ENTITIES (APPLICATION_ID, ENTITY_ID)

Draft : Draft will create journal entries, which are not final, which means they are not ready to be transferred to GL.

You can create accounting on this transaction again and again, which will delete the old journal entries and create new ones. You can’t transfer these journal entries to GL.

xla_events.process_status_code = D

xla_events.event_status_code = U

xla_ae_headers.accounting_entry_status_code = D

Final : Final will create journal entries, which can be transferred to GL. Once it is finally accounted you can’t run create accounting on the particular transaction (specifically on that event).

Run Transfer Journal Entries to GL program

xla_events.process_status_code = P

xla_events.event_status_code = P

xla_ae_headers.accounting_entry_status_code = F

Final Post: Final Post will create journal entries in final mode, transfer them to GL and post them.

xla_ae_headers.accounting_entry_status_code = F

xla_ae_headers.transfer_status_code = Y

xla_events.process_status_code = P

xla_events.event_status_code = P

============================================================

In R12 we can't find any references columns. Where as in R11 we have

Invoice numbers, Suppliers/Customer details in GL tables as references

columns.

So in R12 new module XLA introduced , XLA tables don't store any direct

references.

First we have to check the transactions in xla_transaction_entities, where

source_id_int_1 stores the ID value of transactions. So based on the

transaction we have to map the appropriate modules

See the below examples .

AP Invoices

where xla_transaction_entities.source_id_int_1 = ap_invoices_all.invoice_id

and entity_code = 'AP_INVOICES'

AP Payments

where xla_transaction_entities.source_id_int_1 = ap_checks_All.check_id

and xte.entity_code = 'AP_PAYMENTS'

AR Invoice

where xla_transaction_entities.source_id_int_1 =

ra_customer_trx_all.customer_trx_id

and xte.entity_code = 'TRANSACTIONS'

AR Receipt

where xla_transaction_entities.source_id_int_1

=ar_cash_receipts_all.CASH_RECEIPT_ID

This is the way we have to map all modules based on entity_code.

==============================================================

Technical details of Drilldown from GL to Sub Modules

February 13, 2014   //    No Comment

This article explains you the technical reference for journal import and drilldown functionalities, which will help in understanding the SLA architecture and helps you in troubleshooting of Support issues.

Drilldown from General Ledger takes us to the SLA Journal details.

The technical flow for this is as follows:

Drilldown is performed from Journal lines in General Ledger. This data is stored in the table GL_JE_LINES. The link between SLA data and data in GL is through the table GL_IMPORT_REFERENCES.

Note that data is populated in GL_IMPORT_REFERENCES only if  ‘Import References’ option is selected in the Journal source definition. The data can be mapped from GL_JE_LINES to GL_IMPORT_REFERENCES table using the columns je_header_id and je_lines_num.

The SLA Journal line data is stored in the table XLA_AE_LINES and header details are stored in XLA_AE_HEADERS.

The data in GL_IMPORT_REFERENCES can be mapped to XLA_AE_LINES using the columns gl_sl_link_id and gl_sl_link_table.

GL to Submodules Drilldown

Important points to note:

Data can be transferred from SLA to GL in either Summary or Detail mode. This option is defined for the Event Class, which is attached to the Journal Line Type. Journal Line Type is then attached to the Journal Line Definition, which is finally attached to the Subledger Accounting Method. Please note that if the transfer is done in Summary mode, then the Reference columns will not be populated in either GL_IMPORT_REFERENCES or GL_JE_LINES.

Data from SLA, which comes to GL_INTERFACE, can then be imported in either Summary or Detail Mode. This option is set while defining the Ledger in the Accounting Setup Manager.

Please note that irrespective of the Mode of Transfer (Detail or Summary), Drilldown will work from GL to all the Subledgers that are using the SLA Engine. The subledger reference information required to drilldown is taken from the SLA tables XLA_AE_HEADERS and XLA_AE_LINES, and not in GL_JE_LINES or GL_IMPORT_REFERENCES.

Due to patch# 7512923:R12.XLA.A, the reference columns REFERENCE5 to REFERENCE10 are populated with entity_id, event_id, ae_header_id, ae_line_num, accounted_cr and accounted_dr. You need to apply this patch if you want to see Line References on Account Inquiry -> Journal Detail.

To identify the mode of Transfer:

Column gl_transfer_mode_code in XLA_AE_LINES indicates the transfer mode from SLA to GL. For this column, value ‘S’ indicates that the transfer is done in Summary mode while ‘D’ indicates that the transfer is in Detail mode.

For the Journal Import mode, the values for specific applications is stored in the table XLA_LEDGER_OPTIONS. This table stores the setup for various applications for the Ledgers defined. The column indicating the Journal Import mode, is transfer_to_gl_mode_code. The values are:

P : Summarized by Period

A : Summarized by Accounting Date

D : No Summarization i.e Detail mode

How to find the process sid from concurrent request id in Oracle Applications or Oracle EBS

/* Formatted on 2017/07/17 16:22 (Formatter Plus v4.8.8) */
SELECT d.username,d.status, d.MACHINE, a.request_id, d.SID, d.serial#, d.osuser, d.process, c.spid, d.inst_id
  FROM apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_processes b,
       gv$process c,
       gv$session d
 WHERE a.controlling_manager = b.concurrent_process_id
   AND c.pid = b.oracle_process_id
   AND b.session_id = d.audsid
   AND a.request_id = 95410755  
   AND a.phase_code = 'R';

OAF Standard Page Debugging Process

Enable Profile option at user level

FND: Debug Log Enabled = Yes
FND: Debug Log Filename for Middle-Tier = NULL (Leave The field BLANK)
FND: Debug Log Level = Statement
FND: Debug Log Module = ICX% (Set the short name of the module you wish to debug

Run the page and see the data in table by below query.


SELECT   *
    FROM fnd_log_messages
   WHERE user_id = :p_user_id AND TIMESTAMP > SYSDATE - .3
ORDER BY log_sequence DESC;

Another option you can see the log messages on the screen also:
In the Home page Diagnostics -> Show log or Set Trace Level or Show log on screen or Show Pool Monitor.

If you select Show Log on Screen  and select the Log Level then it will show on screen only.

OAF Personalizations Query

SELECT PATH.PATH_DOCID PERZ_DOC_ID,
jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH
FROM JDR_PATHS PATH
WHERE PATH.PATH_DOCID IN
(SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS
WHERE COMP_SEQ = 0 AND COMP_ELEMENT = 'customization'
AND COMP_ID IS NULL)
ORDER BY PERZ_DOC_PATH;

How to call SQL Loader from Command Prompt

sqlldr username@server/password control=loader.ctl
sqlldr username/password@server control=loader.ctl

GL Period Status Query in Oracle Application

SELECT   sob.NAME "Set of Books", fnd.product_code "Product Code",
         ps.period_name "Period Name", ps.start_date "Period Start Date",
         ps.end_date "Period End Date",
         DECODE (ps.closing_status,
                 'O', 'O - Open',
                 'N', 'N - Never Opened',
                 'F', 'F - Future Enterable',
                 'C', 'C - Closed',
                 'Unknown'
                ) "Period Status"
    FROM gl_period_statuses ps, gl_sets_of_books sob, fnd_application_vl fnd
   WHERE ps.application_id IN (101, 201)                            -- GL & PO
     AND sob.set_of_books_id = ps.set_of_books_id
     AND fnd.application_id = ps.application_id
     AND ps.adjustment_period_flag = 'N'
     AND (:p_date BETWEEN TRUNC (ps.start_date) AND TRUNC (ps.end_date))
ORDER BY ps.set_of_books_id, fnd.product_code, ps.start_date;

To increase Oracle EBS Performance

We can run the program "Program - Optimizer" for each module.

Then Run the "Gather Schema Statistics" Program for Each module too to improve performance.

Query has exceeded 200 rows in OAF

For this error set the Profile option "FND: View Object Max Fetch Size" to 999 at site level.

Error looging on Table in PLSQL Oracle 11g Feature

CREATE TABLE TEST(

  id           NUMBER(10)    NOT NULL,

  code         VARCHAR2(10)  NOT NULL,

  description  VARCHAR2(50),

  CONSTRAINT dest_pk PRIMARY KEY (id)

);

-- Create the error logging table.

BEGIN

  DBMS_ERRLOG.create_error_log (dml_table_name => 'TEST');

END;

It will create ERR$_TEST new table

INSERT INTO TEST

SELECT *

FROM   source

LOG ERRORS INTO err$_test ('INSERT') REJECT LIMIT UNLIMITED;

SELECT ora_err_number$, ora_err_mesg$

FROM   err$_test

WHERE  ora_err_tag$ = 'INSERT';

Automatic Reversal query for General Ledger Entries in Oracle Applications

SELECT led.name,
   jb.name batch_name,
   jh.name journal_name,
   jh.je_header_id,
   jc.user_je_category_name,
   jh.status, jh.period_name, jh.accrual_rev_status,jh.accrual_rev_period_name
FROM gl.gl_je_headers jh,
   gl.gl_je_batches jb,
   gl.gl_autoreverse_options aro,
   gl.gl_ledgers led,
   gl.gl_je_categories_tl jc
WHERE aro.je_category_name = jh.je_category
AND jh.ledger_id = led.ledger_id
AND led.criteria_set_id = aro.criteria_set_id
AND jh.je_batch_id = jb.je_batch_id
AND aro.autoreverse_flag = 'Y'
AND jh.accrual_rev_period_name IS NULL
AND jh.accrual_rev_status IS  NULL
AND led.ledger_id = jh.ledger_id
AND jc.je_category_name = jh.je_category
and jh.period_name like '%2017'
order by jh.period_name;

Query to find GL XLA AP Link invoices in Oracle Applications

SELECT   SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount,
         invoice_num
    FROM ap_invoices_all aia,
         xla.xla_transaction_entities xte,
         xla_ae_headers xah,
         xla_ae_lines xal,
         gl.gl_import_references gir,
         gl_je_lines gjl,
         gl_je_headers gjh
   WHERE 1 = 1
     AND aia.invoice_id = NVL ("SOURCE_ID_INT_1", (-99))
     AND xte.entity_code = 'AP_INVOICES'
     AND xte.application_id = 200
     AND xte.entity_id = xah.entity_id
     AND xah.ae_header_id = xal.ae_header_id
     AND xal.gl_sl_link_id = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjl.je_header_id = gjh.je_header_id
     AND aia.invoice_num = 'XXXXXX'                           --Invoice Number
GROUP BY aia.invoice_num, aia.invoice_amount

Supplier Accrual Balance Query in Oracle Applications

SELECT   fv.flex_value_meaning company, fv.description company_description,
         cc.segment4 ACCOUNT,
         NVL (sp.vendor_name, gs.user_je_source_name) supplier,
         sp.segment1 supplier_number,
         sp.vendor_type_lookup_code supplier_type, pt.NAME payment_terms,
         SUM (  NVL (NVL (xal.accounted_dr, gjl.accounted_dr), 0)
              - NVL (NVL (xal.accounted_cr, gjl.accounted_cr), 0)
             ) net
    FROM xla.xla_transaction_entities xte,
         xla.xla_ae_headers xah,
         xla.xla_ae_lines xal,
         gl.gl_import_references gir,
         gl.gl_je_lines gjl,
         gl.gl_je_headers gjh,
         gl.gl_code_combinations cc,
         gl.gl_je_batches gb,
         apps.gl_je_sources_vl gs,
         apps.ap_suppliers sp,
         apps.fnd_flex_value_sets vs,
         apps.fnd_flex_values_vl fv,
         apps.ap_terms_vl pt,
         apps.gl_je_categories_vl gc,
         gl.gl_periods gp
   WHERE 1 = 1
     -- AND xte.entity_code = 'AP_INVOICES'         --'AP_INVOICES' --'AP_PAYMENTS'
     AND NVL (xte.application_id, 200) = 200
     AND xte.entity_id(+) = xah.entity_id
     AND xah.ae_header_id(+) = xal.ae_header_id
     AND xal.gl_sl_link_id(+) = gir.gl_sl_link_id
     AND xal.gl_sl_link_table(+) = gir.gl_sl_link_table
     AND sp.vendor_id(+) = xal.party_id
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjl.je_header_id = gjh.je_header_id
     AND gjh.je_source = gs.je_source_name
     AND gjh.period_name = gp.period_name
     AND gp.start_date >= '01-JAN-2015'
     AND gp.end_date < '01-MAR-2017'
--            AND  gp.period_name = 'FEB 2017'
--            and gp.period_set_name = 'USG CALENDAR'
     AND gjh.je_category = gc.je_category_name
     AND gc.je_category_name IN ('Treasury', 'Purchase Invoices', 'Payments')
--   AND xal.accounting_date(+) < TO_DATE ('01-APR-2017', 'DD-MON-YYYY')
--   AND xal.accounting_date(+) >= TO_DATE ('01-JAN-2017', 'DD-MON-YYYY')
--   AND xal.code_combination_id(+) = cc.code_combination_id
--   AND xal.ledger_id(+) = gjh.ledger_id
     AND cc.chart_of_accounts_id = 50241
     AND cc.segment4 = '201001'
     AND cc.segment1 = '101'
     AND gjh.je_batch_id = gb.je_batch_id
     AND gjl.code_combination_id = cc.code_combination_id
     AND vs.flex_value_set_name = 'XXX_CO'
     AND fv.flex_value_set_id = vs.flex_value_set_id
     AND fv.flex_value = cc.segment1
     AND pt.term_id(+) = sp.terms_id
GROUP BY fv.flex_value_meaning,
         fv.description,
         NVL (sp.vendor_name, gs.user_je_source_name),
         sp.segment1,
         cc.segment4,
         sp.vendor_type_lookup_code,
         pt.NAME
ORDER BY 2, 3, 4;

GL Balance Query in Oracle Applications

SELECT   gcc.concatenated_segments code_combination,
         SUM (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
             ) beginning_bal,
         SUM (  NVL (gb.begin_balance_dr, 0)
              - NVL (gb.begin_balance_cr, 0)
              + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
             ) end_bal
    FROM gl_balances gb, gl_code_combinations_kfv gcc
   WHERE gb.code_combination_id = gcc.code_combination_id
     AND gcc.concatenated_segments = '101-000-01010-201001-000-0000-0000'
                                                           -- Enter GL Account
     AND gb.ledger_id = 1                                  -- Enter the Ledger
     AND gb.actual_flag = 'A'
     AND gb.period_name = 'JAN-2015'                          --Enter the Period
     AND gb.currency_code = (SELECT currency_code
                               FROM gl_ledgers
                              WHERE ledger_id = gb.ledger_id)
GROUP BY gcc.concatenated_segments

template builder installer failed unexpected error for word bi publisher desktop

Go to -> C: -> Users -> XXXX-> AppData -> Local -> Temp

Find the file "TemplateBuilderInstaller.log" and open in note pad. You will see the error like:



Word process check start
Error No.: 339
Description: Component 'MSCOMCTL.OCX' or one of its dependencies not correctly registered: a file is missing or invalid

Download MSCOMCTL.OCX and copy to C:/Windows/system32 folder and C:/Windows/SysWOW64 folders.

Follow below Steps:
Look for MSCOMNCTL.OCX in the following:   C:\Windows\SysWOW64 directory
Right click on Command Prompt and select Run As Administrator
Type the following
CD C:\Windows\SysWOW64 directory
Press Enter
Next Type
Regsvr32 MSCOMCTL.OCX
Press Enter
You should get a message "DllRegisterServer in mscomctl.ocx succeeded"
Proceed with the installation


Then Install again BI Publisher Desktop.

Consult the OPP service log for details in Oracle Applications

-- Consult the OPP service log for details


--opp file name for given request id

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
 WHERE fcpp.processor_id = fcp.concurrent_process_id
   AND fcpp.action_type = 6
   AND fcpp.concurrent_request_id = &request_id;

Payment Details Query in Oracle Payables or Payments in Oracle EBS

SELECT sup.vendor_name, assa.vendor_site_code, pp.payment_profile_name,
p.payment_id, p.payment_method_code, p.payment_status, p.process_type,
p.payment_amount, p.payment_currency_code, p.ext_payee_id
FROM apps.iby_payments_all p,
apps.iby_payment_profiles pp,
apps.hr_operating_units op,
apps.ap_supplier_sites_all assa,
apps.ap_suppliers sup,
apps.iby_external_payees_all iepa
WHERE 1 = 1 -- payment_instruction_id=213190
AND p.payment_profile_id = pp.payment_profile_id
AND pp.processing_type = 'ELECTRONIC'
AND p.org_id = op.organization_id
AND op.business_group_id = 0
AND iepa.ext_payee_id = p.ext_payee_id
AND sup.vendor_id = assa.vendor_id
AND assa.pay_site_flag = 'Y'
AND assa.vendor_site_id = iepa.supplier_site_id
AND p.payment_instruction_id = 213190;

Payment Method Preference for Supplier Query in Oracle Payments

SELECT sup.vendor_name, assa.vendor_site_code, ieppm.payment_method_code,


iepa.remit_advice_delivery_method, iepa.remit_advice_email,


iepa.remit_advice_fax

FROM apps.ap_supplier_sites_all assa,


apps.ap_suppliers sup,


apps.iby_external_payees_all iepa,


apps.iby_ext_party_pmt_mthds ieppm,


apps.hr_operating_units op

WHERE sup.vendor_id = assa.vendor_id

AND assa.pay_site_flag = 'Y'


AND assa.vendor_site_id = iepa.supplier_site_id

AND iepa.ext_payee_id = ieppm.ext_pmt_party_id

AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE))


-- AND assa.vendor_site_id = :p_vendor_site_id


AND ieppm.primary_flag = 'Y'


AND assa.org_id = op.organization_id

AND op.business_group_id = 0