Report Manager Publishing issue:

1. You have done setup the profile option BNE Allow Set Parameters = Yes ( System Administrator > Profile>System)
2. Created template from Report Manager(Report Manager>Financial Report Template Editor)
3. Upload the template.
4. Now you are trying to modify the same template and trying again to upload (Report Manager>Financial Report Template Editor)
5. System will not allow as the overwrite existing template not enabled.

You might get this issue, when you are trying to achive this. Therefore if you are using Report Manager to run FSGs, when you created template for upload, you need the paramters like "Enable Drill" and "Overwrite Existing Template" to be checked by default.

These two options might help you in achieve this

Options 1

What you need is to default "Enable Drill" and "Overwrite Existing Template" check box to true.

You can achieve this by simple update from backend directly by running this script from database.

 
UPDATE BNE_PARAM_LIST_ITEMS T
SET T.BOOLEAN_VALUE_FLAG = 'Y',
T.DESC_VALUE = 'Defaulting to True'
WHERE T.APPLICATION_ID = 265
AND T.PARAM_LIST_CODE = 'FRM_EXCEL_UPLOAD'
AND T.PARAM_NAME IN('frm:template_drill_enable','frm:template_overwrite');
 

on successful commit, you need to recreate the template by bouncing the Apache.

Options 2

Another Options is follow as per Oracle metalink Note (#784512.1 ) , this will enable you to default "Enable Drill" and "Overwrite Existing Template" check box to true, you need to edit upload parameters of FSG Template Editor.(Adopted from metalink)

1. Login to application navigate to Desktop Integration responsibility. Open Create Document function.
2. Browser address bar will display an URL like
   http://<host>:<port>/oa_servlets/oracle.apps.bne.webui.BneApplicationService?bne:page=BneCreateDoc change it
   http://host:port/oa_servlets/oracle.apps.bne.webui.BneApplicationService?bne:page=BneParameter and click enter.
3. Uncheck the "Restrict to this application" check box and click next.
4. Click Name LOV. Search for the parameter list code "FRM_EXCEL_UPLOAD" and select it.
5. To Update the parameter "frm:template_overwrite" click the pencil icon next to the parameter.
6. In the resulting Define Parameter page set the default value to True and enter description as "Defaulting to True" and save.
7. Now repeat the same for "frm:template_drill_enable" parameter to default the value.
8. Once you complete click the Save button in the main page ("Define Parameter List" page).
9. You can perform test the changes by clicking test button in the main page.

Lets revisit some Basic things

• Invoice Numbers/Transaction Number : These are Numbers generated and controlled by the Source definition. Oracle Reference column is TRX_NUMBER.
• Document Numbers : This you need to define a Sequence & assign it to the Document Category which is the AR Transaction Type. Oracle Reference column is DOCUMENT_SEQUENCE_VALUE.

Why Transaction Number sequence is so important

As far as I know, some of European Countries(Italy) and some of APAC(Taiwan, Singapore) Countries have to maintain the Transaction number in sequence. Any Jump in the transaction number may be subject to audit issue with auditors. Therefore its very important to alter the underline seeded sequence definition.

Why Oracle gives some jump in number

The reason is cache value which comes with seeded sequence .

If you have "caching" enabled on the sequences. This is a feature that is meant to speed up retrieval of heavily used sequences. If the sequence isn't used for a while the cache "ages out" and those numbers that were in the cache get skipped.

Most of Oracle seeded sequence come with Default 20 values.

Technical note on Oracle Sequence

Based out of Robert Vollman note in his blog. Basically there are 2 potential issues with Oracle sequences

1. The sequence number may "jump", that is 1,2,3,5,6,8,... with number 4 and 7 missing. One reason is if you do a transaction with NEXTVAL and then rollback, the sequence doesn't roll back to where you started.
2. By default, the sequence caches the 20 next values for faster retrieval. Thats the reason why most of seeded Oracle sequence have value 20. If there is a system failure, we will lost the cached values upon system recovery. Thus you end up with a gap of up to 20. One way to avoid this is to use NOCACHE command when creating the sequence. And of course you lose the benefit cache brings as well.

Step to alter the Seeded sequence

1) Query up your invoice source , which user have reported Jump in number.

Navigate>Setup>Invoice>Source

2) Execute the following script using your source as in step 1.

 
SELECT batch_source_id, org_id, name FROM ra_batch_sources_all WHERE name LIKE '<source>';
 

or use this query if you have access from database.

 
SELECT 'alter sequence AR.'||seq.sequence_name||' nocache;' FROM
(SELECT
name bsname, org_id org,
'RA_TRX_NUMBER_'||BATCH_SOURCE_ID||'_'||org_id||'_S' seqname
FROM RA_batch_sources_all) src,
dba_sequences seq
WHERE src.seqname=seq.sequence_name
AND seq.cache_size;
ORDER BY org
/
 

3) Using the batch_source_id from the above query , execute the followingscript:

 
SELECT sequence_owner, sequence_name, cache_size
FROM all_sequences
WHERE sequence_name LIKE 'RA_TRX_NUMBER%1146%'; 
 

4) Execute the following:

 
ALTER sequence SEQUENCE_NAME increment BY 1 nocache;
COMMIT; 
 

Example, if your sequnce is RA_TRX_NUMBER_1146_1166_S

then script would be

 
ALTER sequence RA_TRX_NUMBER_1146_1166_S increment BY 1 nocache;
 

Few Tips

1.When you create a source and attach a sequence, Oracle will create a sequence like RA_TRX_NUMBER_<batch_id>_<org_id>_S
2. Sequence RA_TRX_NUMBER_<batch_id>_<org_id> _S as is owned by AR.

How to handle those missing Number

If you have identified in early stage, and missing numbers are very less in count, than create another source with non-automatic number sequence and tell user to use those number which havn't used by Oracle earlier. Once you used all missing number than end date the source.

As requested by some of reader , Here are steps and checklist for R12 AR Month End Close and Reconciliation.

1. Complete All Transactions for the Period Being Closed

2. Complete and review your unapplied receipts

3. Reconcile Receipts to Bank Statement Activity for the Period

Read the rest of this entry »