Oracle Discoverer report statistics

Tags

, , , ,

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;

Advertisements

Submit Process LockBox concurrent program

Tags

, ,

Code to submit Process Lockbox
================================================
================================================/* Formatted on 3/6/2013 2:32:19 PM (QP5 v5.215.12089.38647) */
DECLARE
l_req_id   NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (0, 50268, 222);
l_req_id :=
FND_REQUEST.submit_request (
application   => ‘AR’,
program       => ‘ARLPLB’,
description   => NULL,
start_time    => NULL,
argument1     => ‘Y’                             — CODE_NEW_TRANS_YN                             ,
argument2     => NULL                              — TRANSMISSION_ID                              ,
argument3     => NULL                      — TRANSMISSION_REQUEST_ID                              ,
argument4     => ‘AR01268073’                    — TRANSMISSION_NAME                                      ,
argument5     => ‘Y’                            — CODE_RUN_IMPORT_YN                             ,
argument6     => ‘/inbox/AR013118_268073.dat’            — FILE_NAME                                                      ,
argument7     => ‘phs_wf_lb’                          — CONTROL_FILE                                     ,
argument8     => 1000                       — TRANSMISSION_FORMAT_ID                              ,
argument9     => ‘Y’                        — CODE_RUN_VALIDATION_YN                             ,
argument10    => ‘N’                — CODE_PAY_UNRELATED_INVOICES_YN                             ,
argument11    => 1189                                   — LOCKBOX_ID                              ,
argument12    => TO_CHAR (SYSDATE, ‘YYYY/MM/DD HH24:MI:SS’) — GL_DATE                                                                    ,
argument13    => ‘A’                            — CODE_REPORT_FORMAT                             ,
argument14    => ‘N’                      — CODE_COMPLETE_BATCHES_YN                             ,
argument15    => ‘Y’ –‘Y’                    — CODE_RUN_APPL_YN(Post Quick Cash)                             ,
argument16    => ‘N’                        — IGNORE_INVALID_TXN_NUM                             ,
argument17    => ‘N’                        — USSGL_Transaction_code                             ,
argument18    => NULL,
argument19    => TO_CHAR (104)  ,                            — ORG_ID
argument20    => ‘L’,
argument21    => NULL);
COMMIT;
DBMS_OUTPUT.put_line (‘ERROR’ || l_req_id);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘ERROR’ || SQLERRM);
END;

How to create credit card in Oracle Applications

Tags

, ,

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

Tags

, ,

 

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)

Tags

, , ,

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

Tags

, ,

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;

Full Table Scan on GL_CODE_COMBINATIONS

Tags

, , ,

We recently had bad performance due to the full table scan on the gl_code_combinations table. Metalink Note: [ID 858725.1]

Indexes for GL_CODE_COMBINATIONS

To optimize the GL_CODE_COMBINATIONS performance add a non-unique concatenated index on just the active segments of the GL_CODE_COMBINATIONS table. This is a required index, per development, no matter where the performance problem is, as it improves performance for many other GL functionalities as well.
If one such index already exists make sure that the segments with the highest number of values still come first as the spread of values may change over time.
The order of the columns, in the CREATE INDEX command, is decisive – the more sensitive or selective an index is the better the performance. To achieve that, the segments with more occurrences in the table must be placed first.

To find what indexes already exist, use this sql:

Select aic.table_name, aic.index_name,
ai.uniqueness, aic.column_name, aic.column_position
From all_ind_columns aic,
all_indexes ai
where ai.index_name = aic.index_name
and ai.table_name = ‘GL_CODE_COMBINATIONS’
order by aic.index_name, aic.column_position;

To determine the order of the segments in the concatenated index execute the following select statement, which counts the number of distinct values for each segment used in the GL_CODE_COMBINATIONS table (as the index is for all Charts Of Accounts we do not distinguish between COA’s):

set serveroutput on

DECLARE
v_SegNum VARCHAR2(9);
v_FreqCount NUMBER;
BEGIN
FOR i IN 1..30
LOOP
EXECUTE IMMEDIATE
‘SELECT COUNT(DISTINCT SEGMENT’||TO_CHAR(i)||’)
FROM gl_code_combinations’
INTO v_FreqCount ;
IF v_freqCount <> 0 THEN
DBMS_OUTPUT.PUT_LINE(
‘SEGMENT’||TO_CHAR(i)||’ Frequency = ‘||TO_CHAR(v_FreqCount));
END IF;
END LOOP;
END;

For example, consider that this select statement has returned the values:

SEGMENT1 Frequency = 4
SEGMENT2 Frequency = 37
SEGMENT3 Frequency = 76
SEGMENT4 Frequency = 3221
SEGMENT5 Frequency = 21
SEGMENT6 Frequency = 22

The indexes for individual segments with a small number of distinct values should be disabled in the Segments Definition form (deselect the index column – please see the paragraph C.1 above).
In general the best concatenated index would include ALL segments, ordered by decreasing number of distinct values. From data above, the best order to make the most selective index is:

  1. SEGMENT4,
  2. SEGMENT3,
  3. SEGMENT2,
  4. SEGMENT6,
  5. SEGMENT5,
  6. SEGMENT1

In this example we recommend to deselect the index flag on SEGMENT1, as there is no benefit to index a segment with a small number of different values used in the code combinations.

As it is recommended not to have 2 indexes starting with the same column, you would need to uncheck also the index flag on SEGMENT4 in the Key Segments form, because the concatenated index will be used instead (in this example segment4 will be the first column in the concatenated index). If you do not, then the index on segment4 will be recreated  the next time the GL Optimizer is executed and you would end up with 2 indexes starting with segment4.

Sign on to SQL*Plus as APPS to create manually the new concatenated index GL_CODE_COMBINATIONS_CAT. In our example you should  execute the following:

CREATE INDEX GL_CODE_COMBINATIONS_CAT ON gl_code_combinations
(segment4,
segment3,
segment2,
segment6,
segment5,
segment1)
PCTFREE 0
INITRANS 2
MAXTRANS 255
TABLESPACE user_index
STORAGE (INITIAL 1048576
NEXT 16384
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 20);

When this index is created you must run the GL Optimizer with Yes for the options to Maintain Indexes, to create/drop the required indexes for the segments.
Be aware that if the concatenated index uses the name of the standard segment index, it may be replaced/deleted by the GL Optimizer. When this happens the concatenated index must be recreated manually.

How do we post AR transactions that were previously posted and reversed

Tags

, , , , ,

How do we post AR transactions that was previously posted and reversed

One Way:

Run the concurrent program ARGCGP to unpost (reset the posted status) for the affected AR transactions.

1. Verify the following before running ARGCGP.

– The AR period affected is open
– The correct posting_control_id is identified. Otherwise, running
the program will make the problem worse by allowing data to be
posted twice.
– The transactions cannot be found in the GL_INTERFACE table

2. To run the program, it should be setup as a concurrent request.
– System Admin Responsibility: Concurrent > Program > Define
– Query ARGCGP (as the short name)
– Click ‘Copy To’ Give it a new program name and short name
For example: ARGCGP-Reverse AR Posting

Important: The new program short name must start with ‘ARGCGP’, example ARGCGP_CUS.
– Click on Use in SRS
– Save
– Click on Parameters
– Enter these parameters and save:

Seq Paramater Valueset Default Value Required Display
4 Set of Books id AR_SRS_SET_OF_BOOKS_ID Constant yes yes
5 Posting_control_id 20 Characters yes yes
6 Reset_gl_pst_date 6 Characters Constant yes yes
7 del_gl_interface 6 Characters Constant yes yes
8 debug_flag 6 Characters Constant yes yes

Description of Parameters

– set_of_books_id = The ID for the book posted from AR
– posting_control_id = The unique id for the original posting run from AR
to GL. Can be identified by querying the
AR_POSTING_CONTROL table if value is not known.
– reset_gl_pst_date = (Y)es or (N)o flag to indicate if records in AR
marked as posted should be reset so that they are
postable again
– del_gl_interface = (Y)es or (N)o flag to indicate if records for this
posting_control_id should be deleted from
gl_interface table
– debug_flag = (Y)es or (N)o flag to put additional debug
information in the log file

3. Add the new program to Revenue Recognition Request Group.
– Security > Responsibility > Request
– Query up Group Revenue Recognition, Application Oracle Receivables
– Add a new line
– Report and from LOV choose ARGCGP-SRS
– Save

4. Change the user’s responsiblity to Receivables User and run the process.
5. Rerun the GL Interface.
***************************************************************************************************************
* WARNING: Before updating any tables in SQL*Plus, perform a BACKUP of the affected tables.
***************************************************************************************************************

How to reverse a Journal Batch

Tags

, , , , ,

Reverse a journal in one step using Enter Journals screen only.
To reverse a journal entry that does not have a defined reversal period, effective date (average balances enabled), and reversal method:

  1. Navigate to the Enter Journal Window (Navigation Path: Journal / Enter)
  2. Query the batch and journal within the batch that you want to reverse.
  3. Choose Review Journal.
  4. Choose More Actions or More Details.
  5. Choose Reverse Journal.
  6. Select Reversal Period. If average balances are enabled, you must also select the Reversal Effective Date.
  7. Choose OK, then select a reversal method for your journal. General Ledger will display your concurrent request ID.
  8. Post the reversing journal batch.