Free Oracle Magazine Profit:The Executive's Guide to Oracle Applications

Enter your e-mail address to receive notifications when there are new posts

Profit Magazine: The Executive's Guide to Oracle Applications

Batch Control is not showing Decimal place (Rounding issue)

Posted on May 12th, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

If you are using Receipt batch control, then you might be noticed the form ARXRWMAI does not display the value of decimal place.

Some Customer is really specific to rounding issue for there control.

If customer is asking for rounding issue, then you need to find why this happen, there might be two reason;

(i)Check the precision setting on currency master setup. If this is setup correct, then (ii) check the profile option Currency: Mixed Currency Precision

Currency:Mixed Currency Precision

This profile option specifies how many spaces are available to the right of the decimal point when displaying numbers representing different currencies.

Once you done setup , you can get the correct value in AR Batch Receipt.

This profile not only control only Receipt batch , it is also hookup with some other form in Finance side, but majority of time customer is not using batch control in AR like (receipt batch or Transaction batch), therefore you might not get across this.:)

dgreybarrow Similar Post on Receipt

Posted in Oracle Receivable | No Comments »

Step by Step :Using Receipt API for Miscellaneous Receipt (Scenario 6)

Posted on May 7th, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Previous tutorial

dgreybarrow What kind of senarois you can consider here

  1. You have logistics company pay for goods damaged during shipping?
  2. You are collected the money for cafeteria ?
  3. You are collecting the money for Car parking?
  4. You are selling your computer and other scrap thing in aucation?
  5. Any other revenue that you are generating which is not link with any customer.

These are some senario which is consider as Miscellaneous Receipt.

Read the rest of this entry »

Posted in Oracle Receivable | 2 Comments »

Step by Step :Using Receipt API ->Apply /Unapply Activity Application (Receipt Writeoff API’s) (Scenario 5)

Posted on May 7th, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Previous tutorial

dgreybarrow What is senarios here

The senario which going to discuss here is apply receipt with some receivable application .

Such applications include Short Term Debit (STD) and Receipt Write-off applications.

In this senario's what you need to offset some balance amount of receipt from collection point of view.Such kind of program you can develop and integrate either in real mode or may be used in batch mode depending upon business need.

dgreybarrowWhich API is being used

This routine call Ar_receipt_api_pub.activity_application to do an activity application on a cash receipt.

Read the rest of this entry »

Posted in Oracle Receivable | 3 Comments »

Step by Step :Using Receipt API ->Reverse Receipt (Scenario 4)

Posted on May 3rd, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Previous tutorial

dgreybarrow What is senarios here

The senario which going to discuss here is "Reverse Receipt"

The senarios is very simple as cancel, you basically booked the transaction and then reverse it to make net effect zero.

Typically senarios is when Customer is ready to pay, you created Receipt and submitted a cheque in Bank and then bank inform there is issue with Cheque ,in that case you are simply reversing the transaction and notify the customer.

Read the rest of this entry »

Posted in Oracle Receivable | 3 Comments »

Step by Step :Using Receipt API ->Apply On Account (Scenario 3)

Posted on May 3rd, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Previous tutorial

dgreybarrow What is scenarios here

The scenario which going to discuss here is "Apply On Account"

This debit item could be for the same customer or related customer, or for an unrelated customer, depending on the Allow Payment of Unrelated Transactions system option.

Read the rest of this entry »

Posted in Oracle Receivable | No Comments »

Step by Step :Using Receipt API ->Creating cash and apply (Scenario 2)

Posted on May 2nd, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Previous tutorial

dgreybarrow What is senarios here

The senario for discuss here is "Create And Apply Receipt"

This debit item could be for the same customer or related customer, or for an unrelated customer, depending on the Allow Payment of Unrelated Transactions system option.

dgreybarrowWhich API is being used

By using the following Procedure Ar_receipt_api_pub.Create_and_apply you can create a cash receipt and same time you can apply it to a specified installment of a debit item.

Read the rest of this entry »

Posted in Oracle Receivable | 4 Comments »

Step by Step :Using Receipt API ->Create Cash Receipt (Scenario 1)

Posted on May 2nd, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Long time back , I had a post discussing AR Receipting , API's avaiability and integration with POS based system. This post is sort of tutorial outlines the specifications and the methodology for using the various Receipt APIs.

dgreybarrow Functional Importance of Receipt API's

Oracle receipt APIs provide an extension to existing functionality of creating and manipulating receipts through standard AR Receipts forms and lockboxes.

Most of the public receipt API caters to the following basic functionality via different API calls:

  1. Creating a cash receipt.
  2. Applying a cash receipt to a debit item.
  3. Creating a cash receipt and applying it to a debit item in one pass.
  4. On-account application.
  5. Unapplying the on-account application.
  6. Unapplying the receipt application to a particular transaction.
  7. Reversing the receipt.
  8. Activity application, such as Receipt Write-off.
  9. Creating a miscellaneous receipt.
  10. Other account application, such as Claim Investigation.
  11. Receipt-to-receipt application.
  12. Creating a cash receipt and an on-account application in one pass.

These API potentially used when you are using two major products like AR and Trade managment.

Which API's is not supported

  1. Creating a Batch Receipt

Read the rest of this entry »

Posted in Oracle Receivable | No Comments »

Where is my Missing Transaction Numbers?

Posted on April 25th, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

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.

dgreybarrow 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.

dgreybarrow 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.

dgreybarrow 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.

dgreybarrow 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;
 

dgreybarrow 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.

dgreybarrow 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.

Posted in Oracle Receivable | No Comments »

R12 AR Month End Close and Reconciliation

Posted on April 1st, 2009 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

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 »

Posted in Oracle Receivable, R12 | No Comments »

AR to GL Transfer : Part II

Posted on December 10th, 2008 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Read This for AR to GL Transfer

AR to GL Transfer

dgreybarrow-2GL Transfer Can Capture the Fingerprints (Link between GL to AR )

TRANSFER Receipt

 

 

 

 

 

 

When you run AR Transfer to GL , GL_INTERFACE table get first populated with reference columns that is then pushed the detailes in GL_JE_LINES table. Here are the details for Reference columns

  • REFERENCE21 :posting_control_id
  • REFERENCE22 :cash_receipt_id||cash_receipt_history_id or cash_receipt_id for MISC
  • REFERENCE23 :line_id
  • REFERENCE24 :receipt_number
  • REFERENCE25 :null for CASH / cash_receipt_history_id for MISC
  • REFERENCE26 :null
  • REFERENCE27 :pay_from_customer
  • REFERENCE28 :MISC / TRADE
  • REFERENCE29 :MISC_source type or TRADE_source_type
  • REFERENCE30 :AR_CASH_RECEIPT_HISTORY

dgreybarrow-2Key Tables

In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down information.

  • ar_cash_receipt_history_all:Receitps accounting
  • ar_distributions_all: Receitps accounting
  • ar_receivable_applications_all: Receipt applications accounting
  • ar_distributions_all:Receipt applications accounting & misc receipts accounting
  • ar_misc_cash_distributions_all:Misc receipts accounting

dgreybarrow-2Query for Subledger Transfer to GL

If you want to get details of different journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code or Journal Type.Here is the query for Transaction.

 
 
SELECT
        gjjlv.period_name               "Period"
      , gjb.name                        "Batch name"
      , gjjlv.header_name               "Journal Entry For"
      , gjjlv.je_source                 "Source"
      , glcc.concatenated_segments      "Accounts"
      , gjjlv.line_entered_dr           "Entered Debit"
      , gjjlv.line_entered_cr           "Entered Credit"
      , gjjlv.line_accounted_dr         "Accounted Debit"
      , gjjlv.line_accounted_cr         "Accounted Credit"
      , gjjlv.currency_code             "Currency"
      , arm.name                        "Payment Method"
      , acra.receipt_number             "Receipt Num"
      , acra.receipt_date               "Receipt Date"
      , RA.CUSTOMER_NAME                "Reference"
      , gjjlv.created_by                "Gl Transfer By"
FROM    apps.gl_je_journal_lines_v gjjlv
      , gl_je_lines gje
      , gl_je_headers gjh
      , gl_je_batches gjb
      , ar_cash_receipts_all acra
      , apps.ra_customers ra
      , apps.gl_code_combinations_kfv glcc
      , ar_receipt_methods arm
WHERE      gjh.period_name IN ('OCT-2007','NOV-2007')
AND        glcc.code_combination_id = gje.code_combination_id
AND        gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND        gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND        gjh.period_name = gjb.default_period_name
AND        gjh.period_name = gje.period_name
AND        gjjlv.period_name = gjh.period_name
AND        gjjlv.je_batch_id = gjh.je_batch_id
AND        gjjlv.je_header_id = gjh.je_header_id
AND        gjjlv.LINE_JE_LINE_NUM  = gje.je_line_num
AND        gjjlv.line_code_combination_id = glcc.code_combination_id
AND        gjjlv.line_reference_4 = acra.receipt_number
AND        ra.customer_id = acra.pay_from_customer
AND        acra.receipt_method_id = arm.receipt_method_id
AND        gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND        gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID
--and        glcc.segment1 ='30D'
 

dgreybarrow-2Run ARGLTP module: General Ledger Transfer Program

AR to GL transfer rECEIPT

If your program is running bit slow , you can run this in debug mode and investigate why performance is done.

Based out of experince , the performance hit would be related to the number of records. If you are trying to determine why records are not imported you could run a smaller range than a week and run that in degbug mode. If you are trying to determine performance issues the debug is likely not going to help.

Here are the steps to run in the debug mode.

1)Go to Run General Ledger Interface Screen:

Interfaces -> General Ledger

2) Activate DEBUG_FLAG parameter:

Menu: Help -> Tools -> Examine
Block: Control
Field: DEBUG_FLAG
Value: Y

3) Run ARGLTP module: General Ledger Transfer Program.

After activating the debug mode the ARGLTP log file will show you more detailed information about this process. In you are unable to debug you can ask Oracle support they will help you in addressing slow performance.

Hope this will be helpful for rootcausing any issue during month end or addressing daily reconciliation.Will back soon for PO and PRJ transfer details.

Posted in Oracle Receivable | 1 Comment »

Page 1 of 512345»

« Previous Entries