All About Oracle

~ Lets share, learn & progress!!

All About Oracle

Tag Archives: Oracle

Oracle Forms Trace

02 Monday Mar 2015

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

DEBUG, FORM, Oracle, ORACLE APPS, TKPROF, trace

To debug a form we can use trace files.

1. Log in to the environment that will be traced.

2. Set the profile option “Utilities:Diagnostics“ (internal name DIAGNOSTICS) user-level value to “Yes”.

3. Choose the relevant responsibility for the form and navigate to the form that will be traced.

4. Enable SQL Trace using the menu option : Help > Diagnostics > Trace > Trace with waits. Also select “Unlimited Trace File Size” to ensure an unlimited size for the trace file.

5. The trace file name and location are displayed in a dialog box. Make a note of the trace file name. This trace will be on the database tier.

6. Execute the required actions on the Form.

7. When complete, exit the form and log out.

8. Disable SQL Trace using the menu option : Help > Diagnostics > Trace > No Trace

9. Obtain the raw trace file from the trace file location, using the filename that was displayed above when enabling the trace. Alternatively the trace file identifier (user name), process id or timestamp can be used to identify the trace file.

The initialization parameter user_dump_dest defines the directory to which the trace file is written. Except for 11g, where initialization parameter<diagnostic_dest> (if set) will define this as follows:

<diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace

10. Use the tkprof command to produce a formatted file.

TKPROF Command

The following typical command is recommended for generating TKPROF files:

tkprof <filename1> <filename2> sort= fchela,exeela,prsela

Where <filename1> is the input trace file or a consolidated file of traces produced by the trcsess utility and <filename2> is the file to which TKPROF writes its formatted output.

Advertisements

Level 12 trace TKPROF

09 Friday May 2014

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

Level 12, Oracle, TKPROF, trace

1. To Enable Level 12 Trace:

Responsibility: System Administrator
Navigation: Profile > System

  1. Find the profile option ‘Initialization SQL Statement – Custom’ for User who is submitting the process to trace.

    User: User submitting the process
    Profile: Initialization SQL Statement – Custom

  2. Click on User column – Edit Field and enter

    begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,’TRUE’,’LOG’,’ALTER SESSION SET EVENTS=’||””||’10046 TRACE NAME CONTEXT FOREVER,LEVEL 12’||””); end;

  3. Save

2.  To Enable FND debug:

Responsibility attached to Oracle user

09 Saturday Mar 2013

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

fnd_users, how to find responsibilities attached to a user oracle, Oracle, query, Responsibilities, responsibility

Quick query to find out a particular responsibility attached to which Oracle users:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT fu.user_name, fu.description,furg.end_date
FROM
apps.fnd_user_resp_groups furg,
apps.FND_RESPONSIBILITY fr,
apps.fnd_responsibility_tl frt,
apps.fnd_user fu
WHERE 1=1
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
AND  frt.RESPONSIBILITY_NAME=:responsibility_name
ORDER BY 1

Oracle Receivables Payment Terms

09 Saturday Mar 2013

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

AR, Oracle, Payment Terms, RA_TERMS, Receivables

This article briefly explains how to set up Payment terms:

Navigate to Oracle AR Superuser

Setup–> Transactions–> Payment TermsImage

Table:

select *
from apps.RA_TERMS

Oracle Discoverer report statistics

08 Friday Mar 2013

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

Discoverer, EUL10g, EUL5_QPP_STATS, Oracle, statistics

Here is the script to find out oracle discoverer reports statistics:

DECLARE
CURSOR c_main
IS
SELECT doc_name
FROM EUL10g.EUL5_DOCUMENTS
WHERE doc_batch = 0;
CURSOR c_detail (l_doc_name VARCHAR2)
IS
SELECT QS_DOC_NAME, QS_DOC_OWNER, MAX (last_run_date) last_used
FROM (SELECT doc_name QS_DOC_NAME,
CASE
WHEN INSTR (docs.doc_created_by, ‘#’) = 0
THEN
docs.doc_created_by
WHEN INSTR (docs.doc_created_by, ‘#’) > 0
THEN
(SELECT fu.user_name
FROM apps.fnd_user fu
WHERE     1 = 1
AND fu.user_id =
SUBSTR (docs.doc_created_by, 2, 5))
END
QS_DOC_OWNER,
NULL last_run_date
FROM EUL10g.EUL5_DOCUMENTS docs
WHERE doc_name = l_doc_name —
UNION
SELECT QS_DOC_NAME,
QS_DOC_OWNER,
MAX (QS_CREATED_DATE) last_run_date
FROM EUL10g.EUL5_QPP_STATS
WHERE qs_doc_name = l_doc_name –
GROUP BY QS_DOC_NAME, QS_DOC_OWNER) a
GROUP BY QS_DOC_NAME, QS_DOC_OWNER;
BEGIN
DBMS_OUTPUT.put_line (‘QS_DOC_NAME’ || ‘QS_DOC_OWNER’ || ‘LAST_USED’);
FOR i IN c_main
LOOP
FOR j IN c_detail (i.doc_name)
LOOP
DBMS_OUTPUT.put_line (
j.QS_DOC_NAME || ‘,’ || j.QS_DOC_OWNER || ‘,’ || j.LAST_USED);
END LOOP;
END LOOP;
END;

How to create credit card in Oracle Applications

07 Thursday Mar 2013

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

Credit card API, Oracle, Oracle Applications

DECLARE
  l_return_status    varchar2(1000);
  l_msg_count        number;
  l_msg_data         varchar2(4000);
  l_card_id          number;
  l_response         IBY_FNDCPT_COMMON_PUB.Result_rec_type;
  l_card_instrument  IBY_FNDCPT_SETUP_PUB.CreditCard_rec_type;
BEGIN
  l_card_instrument.Owner_Id              := 4429;
  l_card_instrument.Card_Holder_Name      := ‘Business World’;
  l_card_instrument.Billing_Address_Id    := 3643;
  l_card_instrument.Card_Number           := ‘4012888888881881’;
  l_card_instrument.Expiration_Date       := ’21-OCT-2011′;
  l_card_instrument.Instrument_Type       := ‘CREDITCARD’;
  l_card_instrument.PurchaseCard_Flag     := ‘N’;
  l_card_instrument.Card_Issuer           := ‘VISA’;
  l_card_instrument.Single_Use_Flag       := ‘N’;
  l_card_instrument.Info_Only_Flag        := ‘N’;
  l_card_instrument.Card_Purpose          := ‘N’;
  l_card_instrument.Card_Description      := ‘Card for BW Corporate Purchases’;
  l_card_instrument.Active_Flag           := ‘Y’;
  IBY_FNDCPT_SETUP_PUB.Create_Card (
            p_api_version     => 1.0,
            x_return_status   => l_return_status,
            x_msg_count       => l_msg_count,
            x_msg_data        => l_msg_data,
            p_card_instrument  => l_card_instrument,
            x_card_id          => l_card_id ,
            x_response         => l_response,
            p_init_msg_list    => FND_API.G_TRUE
   );
   dbms_output.put_line(‘l_return_status = ‘ || l_return_status);
   dbms_output.put_line(‘l_msg_count = ‘ || l_msg_count);
   dbms_output.put_line(‘l_card_id = ‘ || l_card_id);
   dbms_output.put_line(‘l_response.Result_Code = ‘ || l_response.Result_Code);
   dbms_output.put_line(‘l_response.Result_Category = ‘ || l_response.Result_Category) ;
   dbms_output.put_line(‘l_response.Result_Message = ‘ || l_response.Result_Message) ;
END;

Procedure to delete Bank statements from Cash Management

05 Tuesday Mar 2013

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

Bank statements deletion, Cash Management, Oracle

 

Step 1: Un-Reconcile all the bank statements which have been loaded incorrectly.

Please run the “Unreconcile Bank Statement” for each BANK_ACCOUNT_NUMBER and STATEMENT NUMBER combination.
Navigation:
• Corresponding Super User Responsibilities
• View
• Requests
• Submit concurrent program “Unreconcile Bank Statement”

Step2: Delete the Bank statements those have been incorrectly loaded into CM. We will make use of the Custom Purge program that is already available in CM super user responsibility.
Run “PHS Purge/Archive Bank Statements” to purge Unreconciled statements. Pass Parameters as Below.

Note: 1. The program need to be run for every bank branch in Bank Statement.

2. Statement Date parameter value should be same as Statement Date in the Data File.

3. Statement Number Parameter value should be the same as the STATEMENT_NUMBER value present in CE_STATEMENT_HEADERS_ALL table.

Step 3: Delete the statement lines and headers open interface tables for the statements
Step 3.1: Unwanted records for statements which are to be re-loaded need to be archived and deleted from CE_STATEMENT_HEADERS_INT_ALL, CE_STATEMENT_LINES_INTERFACE interface tables by running the Standard Oracle Concurrent program: Archive/Purge Bank Statements

This will need to be executed on all the Org IDs depending if they have data on the interface table that no longer needs to be loaded.
Navigation:

• Corresponding Super User Responsibility
• View
• Requests
• Submit Request
• Archive/Purge Bank Statements

On the parameters:

• Archive/Purge Objects: Interface
• Statement Type: Both
• Interface Statement Status: All
• Archive/Purge Option: Archive/Purge
• Archive Option: Append
• Bank Branch Name: This depends on the data you want to remove on the interface table
• Bank Account Number: This depends on the data you want to remove on the interface table
• Statement Date From: The minimum date of the statement period you want to archive and delete from the interface tables
• Statement Date To: The maximum date of the statement period you want to archive and delete from the interface tables

Below is a snapshot:

Step 3.2: Perform manual deletion from CE_STATEMENT_HEADERS_INT_ALL and from CE_STATEMENT_LINES_INTERFACE for all the data of the same statement numbers that was not removed from Step 3.1
The deletion will be performed via the back-end process using the following scripts.
DELETE FROM
CE.CE_STATEMENT_HEADERS_INT_ALL WHERE
statement_number =

DELETE FROM
CE.CE_STATEMENT_LINES_INTERFACE
WHERE statement_number =

Step 3.3: Clean-up the CE.CE_STMT_INT_TMP table before performing a new CM run.

DELETE FROM
CE.CE_STMT_INT_TMP;

Bank Statement Interface (Cash Management)

05 Tuesday Mar 2013

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

Bank statements, Cash Management, Oracle, Reconciliation

Cash Management
Bank Statement Interface

Bank Reconciliation is required for auditing purposes. Reconciliation is the process of determining the difference between two balances, one on you bank account statements and one on the bank accounts in oracle financials.
Bank reconciliation is a process that explains the difference between the bank balance shown in an organization’s bank statement, as supplied by the bank, and the corresponding amount shown in the organization’s own accounting records at a particular point in time.
Such differences may occur, for example, because

a cheque issued by the organization has not been presented to the bank,
a banking transaction, such as a credit received, or a charge made by the bank, has not yet been recorded in the organization’s books
either the bank or the organization itself has made an error

Bank statement Import Process Flow

Image

Set Up required for using Bank statement Import & Reconciliation:

Step 1: Bank Account

Create the bank account and bank branch in Accounts payables.
Step 2: Transaction Code

Transaction code for the bank should be set up corresponding to all the codes that might possibly appear in the data file. The codes have a type (Payment, receipt, stopped, rejected, miscellaneous payment etc). These differ from bank to bank, and thus are client specific.

For example:

Code Description    Source
CQ    Check           AP Payment
DD    Direct debit   AR Receipt

Step 3: Bank statement mapping
Step 4: Creating the data file
Data file should be of following structure

Record 01 – Mandatory

———————

This should be your first record in the file.  It should be of the following format:

01,<bank originator ID>,<bank customer ID>,<file creation date>,<file creation time>,<file identification number>,<physical record length>,<block size>,<version number>/

01,Wellfargo,,20111028,0720,01,080,,2

Record 02 – Mandatory

———————

This should be the second record in the file.  It should be of the following format:

02,<bank customer ID>,<bank originator ID>,<group status>,<as of date>,<as of time>,<currency code>,<as of date modifier>/

02,PRJ,201110280720-WS,1,20111028,,USD,

The group status can be 1 for “update”, 2 for “deletion”, 3 for “correction” and 4 for “test only”.

Record 03 – Mandatory

———————

This should be the third record in the file.  It should be of the following format:

03,<bank account number>,<currency code>,<type code>,<sign><amount>,<item count>,<funds type>/

03,811089812

Record 16 – Mandatory

———————

The record type 16 will likely have many records, which should be the fourth and subsequent records.  Each will be of the following format:

16,<transaction code>,<amount>,<funds type>,<bank ref #>,<customer ref #>,<text>

16,CQ,2406,,CHECK,CHECK,CHECK,,20021235,
16,CQ,4704,,CHECK,CHECK,CHECK,,20021290,
16,CQ,7733,,CHECK,CHECK,CHECK,,20020803,
16,CQ,9705,,CHECK,CHECK,CHECK,,20020567,
16,CQ,11958,,CHECK,CHECK,CHECK,,20019597,
16,CQ,13201,,CHECK,CHECK,CHECK,,20019014,
16,CQ,18507,,CHECK,CHECK,CHECK,,20020272,
16,CQ,101015,,CHECK,CHECK,CHECK,,20021322,
16,CQ,191189,,CHECK,CHECK,CHECK,,20020459,

Record 49 – Mandatory

———————

This record should follow the last 16 record for the given bank account. It should be of the following format:

49,<account control total>,<number of records for account>/

49,360418,11

Record 99 – Mandatory

———————

This will be the last record in your file. It should be of the following format:

99,<file control total>,<number of groups>,<number of records>/

Concurrent programs to Run

————————–

You have the option of running the concurrent programs in a variety of ways.When testing, it is recommended to run each program one at a time, so that you

can see where in the process you are in case an error occurs.  Once you have the setup completed, you can run all of the programs in one step.

1. Bank Statement Loader

The required parameters to this job are the following:

Process Option – Choose “Load”.

Mapping Name – Pick the one you created in the Bank Statement Mapping section,or if you used a standard one, pick that.

Data File Name – This is whatever you named your data file.  Typical convention is to use the .dat extension.  Example:  bofa123101.dat

Directory Path – If you placed your data file in the $CE_TOP/bin directory, leave this parameter blank.  Otherwise, you must provide the entire directory path to your data file.  Do NOT use any environment variables in your pathname.

Bad example:  $CE_TOP/out/bofa123101.dat

Good example:  /amer/oracle/crmus01/crmus01appl/ce/11.5.0/out/ bofa123101.dat

Display Debug – Defaults to “N”, but it is recommended to set it to “Y” to aid in debugging issues. This job will kick off three additional concurrent programs:

Run SQL*Loader- <format name> – This program takes the data from your data file and loads it into the CE_STMT_INT_TMP table.  This program has no output, but you can see on the last page of the log file how many records got loaded and how many got rejected.

Load Bank Statement Data – This program takes the data from the CE_STMT_INT_TMP table and loads it into the CE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACE tables.  There is no output for this program, and the log file is useless in debugging.

Bank Statement Loader Execution Report – This program provides some information about what the previous programs did.  It has an output report, which has minimal value.  Occasionally, you will get a good error message or warning, but typically it reports everything is fine even if there was an issue.  The log file is not helpful in debugging issues.

2. Bank Statement Import

The required parameters to this job are the following:

Bank Branch Name – Provide the bank branch name that you setup in the Bank Account Setup section.

GL Date – Although this parameter is not marked as required, the import will often fail if you do not provide a value for this parameter.  The date must in an open period in both AP and AR.

Note:  If you have multiple files loaded, but only want to import one of them, use the Statement Date or Statement Number range parameters to limit the import job.

If successful, this program moves records from the CE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACE tables into the CE_STATEMENT_HEADERS and CE_STATEMENT_LINES tables.

This concurrent program will launch one other concurrent program:

AutoReconciliation Execution Report – Although misleadingly named, this report is useful.  It will show exceptions which may have occurred during the import.

3. AutoReconciliation

The required parameters to this job are the following:

Bank Branch Name – Provide the bank branch name that you set up in the Bank Account section.

GL Date – Although this parameter is not marked as required, the import will often fail if you do not provide a value for this parameter.  The date must in an open period in both AP and AR. Note:  If you have multiple files loaded, but only want to import one of them, use the Statement Date or Statement Number range parameters to limit the import job.

If successful, this program will reconcile the imported bank statement lines to outstanding AP and AR transactions.  If unsuccessful, it will mark the bank statement line with an error and allow you to manually reconcile the transaction.

This concurrent program will launch one other concurrent program: AutoReconciliation Execution Report – This time this same report shows exceptions in matching up the imported bank statement transactions with the existing AP and AR transactions in the system.  It gives descriptive reasons why the line was not able to be reconciled automatically.

Oracle concurrent Programs

05 Tuesday Mar 2013

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

Concurrent requests, Oracle, phase_code

Here is the query to fetch concurrent program run and the various status:

SELECT request_id,
program,
requestor,
request_date,
actual_start_date,
actual_completion_date,
ROUND ( (actual_completion_date – actual_start_date) * 24 * 60, 2)
total_duration_mins,
DECODE (phase_code,
‘C’, ‘Completed’,
‘I’, ‘Inactive’,
‘P’, ‘Pending’,
‘R’, ‘Running’)
phase_code,
DECODE (status_code,
‘A’, ‘Waiting’,
‘B’, ‘Resuming’,
‘C’, ‘Normal’,
‘D’, ‘Cancelled’,
‘E’, ‘ Error’,
‘F’, ‘Scheduled’,
‘G’, ‘Warning’,
‘H’, ‘On Hold’,
‘I’, ‘Normal’,
‘M’, ‘No Manager’,
‘Q’, ‘Standby’,
‘R’, ‘ Normal’,
‘S’, ‘Suspended’,
‘T’, ‘Terminating’,
‘U’, ‘Disabled’,
‘W’, ‘Paused’,
‘X’, ‘Terminated’,
‘Z’, ‘Waiting’)
status_code,
argument_text,
program_short_name
FROM apps.FND_CONC_REQ_SUMMARY_V
WHERE TRUNC (actual_start_date) = TRUNC (SYSDATE)
ORDER BY status_code;

Killing a scheduled workbook Oracle Discoverer

05 Tuesday Mar 2013

Posted by Pooh in Oracle Applications

≈ Leave a comment

Tags

command, Discoverer, Kill Session, Oracle

Login to Oracle and run following sql

select sid,serial#,module,program,username,to_char(logon_time,’hh24:mi:ss’) from v$session where module like ‘%Disc%’ ;

Ask DBA to run following statement:

alter system kill session ‘<sid>,<serial#>’;

← Older posts

Recent Posts

  • ORA-22337: the type of accessed object has been evolved
  • Launch a URL dynamically in Oracle forms personalization using Profile option
  • Submitting Request Set from backend
  • Oracle Forms Trace
  • Introduction to Inventory Orgnizations

Categories

  • Oracle Applications
  • Oracle SOA
Advertisements

Blog Stats

  • 28,953 hits

Blog at WordPress.com.

Cancel