GL Application Foundation (FND) Tables
Accounting Flexfield - “FND_ID_FLEX_STRUCTURES” & “FND_ID_FLEX_SEGMENTS”
Value Sets - “FND_FLEX_VALUE_SETS”
Segment Values - “FND_FLEX_VALUES” & “FND_FLEX_VALUES_TL”
FND_CURRENCIES (_TL)
GL Application - Setup Tables
Set of Books - “GL_SETS_OF_BOOKS”
Code Combinations - “GL_CODE_COMBINATIONS”
Calendar - “GL_PERIODS”, “GL_PERIOD_STATUSES”
Budget Definitions - “GL_BUDGETS”, “GL_BUDGET_ENTITIES”, “GL_BUDGET_VERSIONS” , "GL_BUDGET_ASSIGNMENT_RANGES"
Daily Rates - "GL_DAILY_RATES"
GL Application - Open Interface Tables
Actuals Interface - “GL_INTERFACE”
Budget Interface - “GL_BUDGET_INTERFACE”
Daily Foreign Currency Rates - “GL_DAILY_RATES_INTERFACE”
GL Application - Transaction & Balance Data Tables
Journal Entries (Transactions) - “GL_JE_BATCHES”,
“GL_JE_HEADERS”,
“GL_JE_LINES”
Balances - “GL_BALANCES”
GL Lookup Values - “GL_LOOKUPS”
GL Application Archive, Log, & History Tables
History - “GL_ALLOC_HISTORY”, “GL_ARCHIVE_HISTORY”, “GL_INTERFACE_HISTORY”
Archive & Purge - “GL_ARCHIVE_BATCHES”
Wednesday, July 6, 2011
Order to Cash Tables and Data Flow
--ORDER MAIN TABLES
select flow_status_code, booked_flag, a.* from oe_order_headers_all a where order_number = '4025794';
select flow_status_code, a.* from oe_order_lines_all a where header_id = 795291;
select * from oe_price_adjustments where header_id = 795291 and line_id = 2440080;
select * from oe_order_price_attribs where header_id = 795291 and line_id = 2440080;
select * from oe_order_holds_all where header_id = 795291 and line_id = 2440080;
--DELIVERY TABLES
select * from wsh_delivery_details where source_header_id = 795291 and source_line_id = 2440080;
select * from wsh_delivery_assignments where delivery_detail_id = 4537129;
--PICK RELEASE (from SHIPPING tab)
--Concurrent Program "Pick Selection List Generation" kicks off after releasing order from SHIPPING.
--This in turn kicks off "Pick Slip Report" and "Auto Ship Confirm Report (Auto Ship Confirm Report)"
select * from wsh_new_deliveries where delivery_id = 653581;
select * from wsh_picking_batches where name = '653581';
select * from wsh_serial_numbers where delivery_detail_id = 4537129;
--MOVE ORDER
select * from mtl_txn_request_headers where request_number = '689455';
select * from mtl_txn_request_lines where header_id = 690521;
--SHIP CONFIRM
--Shipping Transaction for after entering serial numbers.
--Kicks off "Interface Trip Stop" very imp program that affects Inventory.
select * from mtl_material_transactions where source_code = 'ORDER ENTRY' and transaction_reference = 795291 and trx_source_line_id = 2440080;
select * from mtl_unit_transactions where inventory_item_id = 379603 and serial_number in ('SUMIT00','SUMIT01');
select * from mtl_serial_numbers where inventory_item_id = 379603 and upper(serial_number) in ('SUMIT00','SUMIT01');
--Run "Workflow Background Process" for "OM Order Line"
select * from ra_interface_lines_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_salescredits_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_distributions_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
--Run "Auto Invoice Master Program" and that will kick "Auto Invoice Import" program that will create invoice for the order
-- "Prepayment Matching Program (Prepayments Matching Program)"
select * from ra_customer_trx_all where interface_header_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_customer_trx_lines_all where interface_line_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_cust_trx_line_salesreps_all where customer_trx_line_id = 5758780;
select * from ra_cust_trx_line_gl_dist_all where customer_trx_line_id = 5758780;
select * from ar_payment_schedules_all where trx_number = '50133102';
--Receipt against invoice
select * from ar_cash_receipts_all where receipt_number like '12345%' and creation_date >= trunc(sysdate);
select * from ar_receivable_applications where cash_receipt_id in (130168,130169);
--Run "General Ledge Transfer Program"
--This kicks off "Revenue Recognition" and "Revenue Contingency Analyzer" and "Update Posting Control" program
select * from gl_interface where date_created >= '10-JUN-2011' and user_je_category_name = 'Sales Invoices' and user_je_source_name = 'Receivables'
and reference10 like '%50133102%' and reference24 = '50133102' and reference23 = '42596305';
--Run "Journal Import"
select * from gl_je_batches where creation_date >= '13-JUN-2011' order by je_batch_id desc;
select * from gl_je_headers where je_source = 'Receivables' and je_category = 'Sales Invoices' and je_batch_id = 1592931;
select * from gl_je_lines where je_header_id = 1750124;
--Post Journals "Posting"
select * from gl_balances where last_update_date >= '13-JUN-2011' ;
select flow_status_code, booked_flag, a.* from oe_order_headers_all a where order_number = '4025794';
select flow_status_code, a.* from oe_order_lines_all a where header_id = 795291;
select * from oe_price_adjustments where header_id = 795291 and line_id = 2440080;
select * from oe_order_price_attribs where header_id = 795291 and line_id = 2440080;
select * from oe_order_holds_all where header_id = 795291 and line_id = 2440080;
--DELIVERY TABLES
select * from wsh_delivery_details where source_header_id = 795291 and source_line_id = 2440080;
select * from wsh_delivery_assignments where delivery_detail_id = 4537129;
--PICK RELEASE (from SHIPPING tab)
--Concurrent Program "Pick Selection List Generation" kicks off after releasing order from SHIPPING.
--This in turn kicks off "Pick Slip Report" and "Auto Ship Confirm Report (Auto Ship Confirm Report)"
select * from wsh_new_deliveries where delivery_id = 653581;
select * from wsh_picking_batches where name = '653581';
select * from wsh_serial_numbers where delivery_detail_id = 4537129;
--MOVE ORDER
select * from mtl_txn_request_headers where request_number = '689455';
select * from mtl_txn_request_lines where header_id = 690521;
--SHIP CONFIRM
--Shipping Transaction for after entering serial numbers.
--Kicks off "Interface Trip Stop" very imp program that affects Inventory.
select * from mtl_material_transactions where source_code = 'ORDER ENTRY' and transaction_reference = 795291 and trx_source_line_id = 2440080;
select * from mtl_unit_transactions where inventory_item_id = 379603 and serial_number in ('SUMIT00','SUMIT01');
select * from mtl_serial_numbers where inventory_item_id = 379603 and upper(serial_number) in ('SUMIT00','SUMIT01');
--Run "Workflow Background Process" for "OM Order Line"
select * from ra_interface_lines_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_salescredits_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_distributions_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
--Run "Auto Invoice Master Program" and that will kick "Auto Invoice Import" program that will create invoice for the order
-- "Prepayment Matching Program (Prepayments Matching Program)"
select * from ra_customer_trx_all where interface_header_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_customer_trx_lines_all where interface_line_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_cust_trx_line_salesreps_all where customer_trx_line_id = 5758780;
select * from ra_cust_trx_line_gl_dist_all where customer_trx_line_id = 5758780;
select * from ar_payment_schedules_all where trx_number = '50133102';
--Receipt against invoice
select * from ar_cash_receipts_all where receipt_number like '12345%' and creation_date >= trunc(sysdate);
select * from ar_receivable_applications where cash_receipt_id in (130168,130169);
--Run "General Ledge Transfer Program"
--This kicks off "Revenue Recognition" and "Revenue Contingency Analyzer" and "Update Posting Control" program
select * from gl_interface where date_created >= '10-JUN-2011' and user_je_category_name = 'Sales Invoices' and user_je_source_name = 'Receivables'
and reference10 like '%50133102%' and reference24 = '50133102' and reference23 = '42596305';
--Run "Journal Import"
select * from gl_je_batches where creation_date >= '13-JUN-2011' order by je_batch_id desc;
select * from gl_je_headers where je_source = 'Receivables' and je_category = 'Sales Invoices' and je_batch_id = 1592931;
select * from gl_je_lines where je_header_id = 1750124;
--Post Journals "Posting"
select * from gl_balances where last_update_date >= '13-JUN-2011' ;
Procure to Pay Tables and Data Flow
--Vendors
SELECT * FROM ap_vendors_v WHERE vendor_name = 'XX_VENDOR';
select * from po_vendors where vendor_name = 'XX_VENDOR';
--Requisitions
select segment1, type_lookup_code,a.* from po_requisition_headers_all a where requisition_header_id = 431593;
select * from po_requisition_lines_all where requisition_header_id = 431593 and requisition_line_id = 632840;
select * from po_req_distributions_all where requisition_line_id = 632840;
select * from po_document_types where document_subtype = 'PURCHASE';--(po_requisition_headers_all .type_lookup_code)
select * from po_lookup_codes where lookup_type = 'AUTHORIZATION STATUS';
--PO Headers
select * from po_distributions_all where req_distribution_id = po_req_distributions_all.distribution_id
select * from po_lines_all where po_line_id = 1152395;
select * From po_headers_all where po_header_id = 686518;
select * from po_line_locations_all where po_header_id = 686518;
--Receiving
select * from rcv_transactions where po_header_id = 147163 and po_distribution_id = 353861;
select * from rcv_shipment_headers where shipment_header_id = rcv_transaction.shipment_header_id;
select * from rcv_shipment_lines where where shipment_header_id = rcv_transaction.shipment_header_id;
select * from rcv_serial_transactions where transaction_id = rcv.transactions.transaction_id;
--Receiving transaction Processor import
--AP Invoices and Payments
select * from ap_invoice_distributions_all where po_distribution_id = 353861;
select * from ap_invoices_all where invoice_id = 730583;
select * from ap_terms where term_id = 10020;
select * from ap_invoice_payments_all where invoice_id = 730583;
select * from ap_payment_schedules_all where invoice_id = 730583;
select * from ap_payment_distributions_all where invoice_payment_id in (select invoice_payment_id from ap_invoice_payments_all where invoice_id = 730583);
select * from ap_checks_all where check_id = 285590;
--Accounting Entries
select * from ap_ae_lines_all where source_table = 'AP_INVOICES' and source_id = 730583; -- (ap_invoices_all.invoice_id)
select * from ap_ae_headers_all where ae_header_id = 988223;
--Reconciling Bank Accounts in Cash Management Module
select * from ce_statement_headers_all where statement_header_id =75826
select * from ce_statement_lines where statement_header_id in (select statement_header_id from ce_statement_headers_all where statement_header_id =75826)
--Concurrent Program "Payables Transfer to General Ledger"
select * from gl_interface;
--Import Journal
select * from gl_je_batches;
select * from gl_je_headers;
select * from gl_je_lines;
--Post Journals "Posting"
select * from gl_balances;
--Requisition Screen Header
select * from PO_REQUISITION_HEADERS_INQ_V where requisition_num = '19106668';
select * from PO_REQUISITION_LINES_INQ_V where requisition_header_id = 431593;
select * from PO_REQUISITION_HEADERS_V where requisition_number = '19106668';
select * from PO_REQUISITION_LINES_V where requisition_header_id = 431593;
select * from PO_REQ_DISTRIBUTIONS_V where requisition_line_id in (select requisition_line_id from PO_REQUISITION_LINES_V where requisition_header_id = 431593);
SELECT * FROM ap_vendors_v WHERE vendor_name = 'XX_VENDOR';
select * from po_vendors where vendor_name = 'XX_VENDOR';
--Requisitions
select segment1, type_lookup_code,a.* from po_requisition_headers_all a where requisition_header_id = 431593;
select * from po_requisition_lines_all where requisition_header_id = 431593 and requisition_line_id = 632840;
select * from po_req_distributions_all where requisition_line_id = 632840;
select * from po_document_types where document_subtype = 'PURCHASE';--(po_requisition_headers_all .type_lookup_code)
select * from po_lookup_codes where lookup_type = 'AUTHORIZATION STATUS';
--PO Headers
select * from po_distributions_all where req_distribution_id = po_req_distributions_all.distribution_id
select * from po_lines_all where po_line_id = 1152395;
select * From po_headers_all where po_header_id = 686518;
select * from po_line_locations_all where po_header_id = 686518;
--Receiving
select * from rcv_transactions where po_header_id = 147163 and po_distribution_id = 353861;
select * from rcv_shipment_headers where shipment_header_id = rcv_transaction.shipment_header_id;
select * from rcv_shipment_lines where where shipment_header_id = rcv_transaction.shipment_header_id;
select * from rcv_serial_transactions where transaction_id = rcv.transactions.transaction_id;
--Receiving transaction Processor import
--AP Invoices and Payments
select * from ap_invoice_distributions_all where po_distribution_id = 353861;
select * from ap_invoices_all where invoice_id = 730583;
select * from ap_terms where term_id = 10020;
select * from ap_invoice_payments_all where invoice_id = 730583;
select * from ap_payment_schedules_all where invoice_id = 730583;
select * from ap_payment_distributions_all where invoice_payment_id in (select invoice_payment_id from ap_invoice_payments_all where invoice_id = 730583);
select * from ap_checks_all where check_id = 285590;
--Accounting Entries
select * from ap_ae_lines_all where source_table = 'AP_INVOICES' and source_id = 730583; -- (ap_invoices_all.invoice_id)
select * from ap_ae_headers_all where ae_header_id = 988223;
--Reconciling Bank Accounts in Cash Management Module
select * from ce_statement_headers_all where statement_header_id =75826
select * from ce_statement_lines where statement_header_id in (select statement_header_id from ce_statement_headers_all where statement_header_id =75826)
--Concurrent Program "Payables Transfer to General Ledger"
select * from gl_interface;
--Import Journal
select * from gl_je_batches;
select * from gl_je_headers;
select * from gl_je_lines;
--Post Journals "Posting"
select * from gl_balances;
--Requisition Screen Header
select * from PO_REQUISITION_HEADERS_INQ_V where requisition_num = '19106668';
select * from PO_REQUISITION_LINES_INQ_V where requisition_header_id = 431593;
select * from PO_REQUISITION_HEADERS_V where requisition_number = '19106668';
select * from PO_REQUISITION_LINES_V where requisition_header_id = 431593;
select * from PO_REQ_DISTRIBUTIONS_V where requisition_line_id in (select requisition_line_id from PO_REQUISITION_LINES_V where requisition_header_id = 431593);
Important Workflow Tables
--Workflow Users/Roles
SELECT * FROM WF_USERS where name = 'XXUSER';
SELECT * FROM WF_ROLES where name = 'XXUSER';
SELECT * FROM WF_USER_ROLES where user_name = 'XXUSER';
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS where user_name = 'XXUSER';
--Workflow Item Name and Attributes
SELECT * FROM WF_ITEM_TYPES where name = 'OKCAUKAP'; --PO Approval (POAPPRV) ; PO Requisition Approval (REQAPPRV);
--OM Order Header (OEOH); OM Order Line (OEOL)
SELECT * FROM WF_ITEM_TYPES_VL where name = 'OKCAUKAP';
SELECT * FROM WF_ITEM_TYPES_VL where display_name like '%Approval';
SELECT * FROM WF_ITEM_TYPES_VL where description like '%Order%';
SELECT * FROM WF_ITEM_ATTRIBUTES where item_type = 'OKCAUKAP';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL;
--Workflow Functions
SELECT * FROM WF_ACTIVITIES where item_type = 'OKCAUKAP' and version = 1;
SELECT * FROM WF_ACTIVITIES_TL where item_type = 'OKCAUKAP';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES where activity_name = 'CREATE_SOA';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL;
SELECT * FROM WF_ACTIVITY_TRANSITIONS;
--Workflow Lookups
SELECT * FROM WF_LOOKUPS_TL where lookup_type = 'OKC_DB_RETRY';
--Workflow Messages
SELECT * FROM WF_MESSAGES where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGES_TL where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';
--Workflow Particular item run and values
SELECT * FROM WF_ITEMS where item_type = 'OKCAUKAP' order by begin_date desc;
--USER_KEY/ITEM_KEY => select * from okc_k_headers_b where contract_number = '20683312';
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES where item_type = 'OKCAUKAP' and item_key='20683312';
SELECT * FROM WF_PROCESS_ACTIVITIES where process_item_type = 'OKCAUKAP' and process_version = 1;
SELECT * FROM WF_NOTIFICATIONS WHERE MESSAGE_TYPE = 'OKCAUKAP' AND MESSAGE_NAME = 'APPROVE_CONTRACT';
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES where notification_id =;
SELECT * FROM WF_DEFERRED;
SELECT * FROM WF_USERS where name = 'XXUSER';
SELECT * FROM WF_ROLES where name = 'XXUSER';
SELECT * FROM WF_USER_ROLES where user_name = 'XXUSER';
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS where user_name = 'XXUSER';
--Workflow Item Name and Attributes
SELECT * FROM WF_ITEM_TYPES where name = 'OKCAUKAP'; --PO Approval (POAPPRV) ; PO Requisition Approval (REQAPPRV);
--OM Order Header (OEOH); OM Order Line (OEOL)
SELECT * FROM WF_ITEM_TYPES_VL where name = 'OKCAUKAP';
SELECT * FROM WF_ITEM_TYPES_VL where display_name like '%Approval';
SELECT * FROM WF_ITEM_TYPES_VL where description like '%Order%';
SELECT * FROM WF_ITEM_ATTRIBUTES where item_type = 'OKCAUKAP';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL;
--Workflow Functions
SELECT * FROM WF_ACTIVITIES where item_type = 'OKCAUKAP' and version = 1;
SELECT * FROM WF_ACTIVITIES_TL where item_type = 'OKCAUKAP';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES where activity_name = 'CREATE_SOA';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL;
SELECT * FROM WF_ACTIVITY_TRANSITIONS;
--Workflow Lookups
SELECT * FROM WF_LOOKUPS_TL where lookup_type = 'OKC_DB_RETRY';
--Workflow Messages
SELECT * FROM WF_MESSAGES where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGES_TL where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';
--Workflow Particular item run and values
SELECT * FROM WF_ITEMS where item_type = 'OKCAUKAP' order by begin_date desc;
--USER_KEY/ITEM_KEY => select * from okc_k_headers_b where contract_number = '20683312';
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES where item_type = 'OKCAUKAP' and item_key='20683312';
SELECT * FROM WF_PROCESS_ACTIVITIES where process_item_type = 'OKCAUKAP' and process_version = 1;
SELECT * FROM WF_NOTIFICATIONS WHERE MESSAGE_TYPE = 'OKCAUKAP' AND MESSAGE_NAME = 'APPROVE_CONTRACT';
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES where notification_id =
SELECT * FROM WF_DEFERRED;
OA Framework Code Logging
To write Log Statements in OAF Code:
Add the below piece of code in your OA page
--CONTROLLER
if (pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT))
pageContext.writeDiagnostics(this, "your log statement", OAFwkConstants.STATEMENT);
--OC4J
if (OADBTranasctionImpl.isLoggingEnabled(OAFwkConstants.PROCEDURE))
OADBTransactionImpl.writeDiagnostics(this, "your message", OAFwkConstants.PROCEDURE);
Enable FND Logging Profile:
a. FND: Debug Log Enabled -- Set to Yes
b. FND: Debug Log Level -- Set to Statement level
To See Log Statements:
a. To see log statement on browser append below string to browser url and click on enter &aflog_level=statement.
b. query FND_LOG_MESSAGES in the database for all the debugging messages.
c. Select Diagnostics button from any page and in the Diagnostics page select Show Log on Screen option
Add the below piece of code in your OA page
--CONTROLLER
if (pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT))
pageContext.writeDiagnostics(this, "your log statement", OAFwkConstants.STATEMENT);
--OC4J
if (OADBTranasctionImpl.isLoggingEnabled(OAFwkConstants.PROCEDURE))
OADBTransactionImpl.writeDiagnostics(this, "your message", OAFwkConstants.PROCEDURE);
Enable FND Logging Profile:
a. FND: Debug Log Enabled -- Set to Yes
b. FND: Debug Log Level -- Set to Statement level
To See Log Statements:
a. To see log statement on browser append below string to browser url and click on enter &aflog_level=statement.
b. query FND_LOG_MESSAGES in the database for all the debugging messages.
c. Select Diagnostics button from any page and in the Diagnostics page select Show Log on Screen option
Debugging in Oracle Apps
FND Debugging
Good Metalink Notes on Debugging
Metalink: Note Id 248920.1 ==> How to generate Debug Log Files for Oracle Quoting 11i?
Metalink: Note Id 265330.1 ==> How to generate Debug Log Files for Oracle Quoting R12?
Metalink: Note Id 207262.1 ==> How To Generate Debug Log Files for Oracle iStore 11i?
Metalink: Note Id 763489.1 ==> How To Generate Debug Log Files for Oracle iStore R12?
Metalink: Note Id 452830.1 ==> How to generate Debug Log Files for Oracle Payments R12?
Metalink: Note Id 265330.1 ==> How to generate Debug Log Files for Oracle iPayment 11i?
Metalink: Note Id 239627.1 ==> How To Obtain the CSE/CSI Log and Debug Files?
Good Metalink Notes on Debugging
Metalink: Note Id 248920.1 ==> How to generate Debug Log Files for Oracle Quoting 11i?
Metalink: Note Id 265330.1 ==> How to generate Debug Log Files for Oracle Quoting R12?
Metalink: Note Id 207262.1 ==> How To Generate Debug Log Files for Oracle iStore 11i?
Metalink: Note Id 763489.1 ==> How To Generate Debug Log Files for Oracle iStore R12?
Metalink: Note Id 452830.1 ==> How to generate Debug Log Files for Oracle Payments R12?
Metalink: Note Id 265330.1 ==> How to generate Debug Log Files for Oracle iPayment 11i?
Metalink: Note Id 239627.1 ==> How To Obtain the CSE/CSI Log and Debug Files?
Trace Files and TKPROF in Oracle Apps
How to enable Trace:
When you enable SQL trace in a session for the first time, the Oracle server process handling that session will create a trace file in the directory on the database server designated by the user_dump_dest initialization parameter. As the server is called by the application to perform database operations, the server process will append to the trace file.
select name,value from V$PARAMETER where name = 'user_dump_dest';
Using TKPROF
TKPROF Example#1
----------------------
If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:
TKPROF ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10
TKPROF Example#2
----------------------
This example runs TKPROF, accepts a trace file named dlsun12_jane_fg_sqlplus_007.trc, and writes a formatted output file named outputa.prf:
TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)
For more details, users should visit following link:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/sqltrace.htm
- Enable the SQL Trace facility for the session by using one of the following DBMS_SESSION.SET_SQL_TRACE procedure
- Diagnostics Link > "Set Trace Level" > Go button > Choose the Trace Level.
- In situations where you cannot invoke an ALTER SESSION command from the session you wish to trace—as with prepackaged applications, for example—you can connect to the database as a DBA user and invoke the dbms_system built-in package in order to turn on or off SQL trace in another session. You do this by querying v$session to find the SID and serial number of the session you wish to trace and then invoking the dbms_system package with a command of the form:EXECUTE SYS.dbms_system.set_sql_trace_in_session (
,
When you enable SQL trace in a session for the first time, the Oracle server process handling that session will create a trace file in the directory on the database server designated by the user_dump_dest initialization parameter. As the server is called by the application to perform database operations, the server process will append to the trace file.
select name,value from V$PARAMETER where name = 'user_dump_dest';
Using TKPROF
TKPROF Example#1
----------------------
If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:
TKPROF ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10
TKPROF Example#2
----------------------
This example runs TKPROF, accepts a trace file named dlsun12_jane_fg_sqlplus_007.trc, and writes a formatted output file named outputa.prf:
TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)
For more details, users should visit following link:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/sqltrace.htm
How to Bounce Apache in Oracle Apps
R12
$INST_TOP/admin/scripts/adapccctl.sh stop
$INST_TOP/admin/scripts/adoacorectl.sh stop
$INST_TOP/admin/scripts/adapccctl.sh start
$INST_TOP/admin/scripts/adoacorectl.sh start
11i
$COMMON_TOP/admin/scripts//adapcctl.sh stop
$COMMON_TOP/admin/scripts//adapcctl.sh start
$INST_TOP/admin/scripts/adapccctl.sh stop
$INST_TOP/admin/scripts/adoacorectl.sh stop
$INST_TOP/admin/scripts/adapccctl.sh start
$INST_TOP/admin/scripts/adoacorectl.sh start
11i
$COMMON_TOP/admin/scripts/
$COMMON_TOP/admin/scripts/
How to clear middle-tier cache using Functional Adminstrator
Navigate to Functional Administrator responsibility
- Core Services
- Caching Framework
- Global Configuration
- Clear cache
Subscribe to:
Posts (Atom)