AR Aging Report (Query)

AR Aging Report

select decode(&3,2,'ABC OPERATING UNIT',5,'XYZ OPERATING UNIT') FROM DUAL
/

set head on   
   
select customer_number, customer_name,
  paymt,
          Day_030 ,
          Day_031_060,
        Day_061_090,
 Day_091_120,
 Day_121_150,
 Day_151_180,
 Day_181,
 no_ofTrx
from    
(select pay.org_id,pay.customer_id
--        ,pay.trx_number, pay.trx_date
    --   ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING,to_Date ('asofdate') - trunc(pay.trx_date)
       , sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) <=30   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id)  end ) ) Day_030
         , sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 31  and 60   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_031_060     
       , sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 61  and 90   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_061_090
         , sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 91  and 120  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_091_120     
       , sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 121 and 150  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_121_150
         , sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 151 and 180  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_151_180     
       , sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) >= 181               then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_181
       ,count(*) no_ofTrx
       ,'' 
from       apps.ar_payment_Schedules_all pay
where  pay.class in ('XX','YY','ZZ') and pay.org_id = &3
and pay.gl_date  <= to_Date ('&1')
and pay.GL_DATE_CLOSED >  to_Date ('&1')
and exists   ( select 'x' from  apps.gl_code_combinations gl   
                       ,apps.ra_cust_trx_line_gl_dist_all distgl
              where  (trim (('&2')) ='0' or gl.segment4 in ('&2'))   --gl.segment4 in ('&2')
                and gl.CODE_COMBINATION_ID =  distgl.CODE_COMBINATION_ID
                and distgl.ACCOUNT_CLASS ='REC'
                and distgl.CUSTOMER_TRX_ID  =pay.CUSTOMER_TRX_ID
                )             
group by pay.org_id,pay.customer_id
    --   ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING ,pay.trx_number, pay.trx_date
)  invag ,
(select  pay.org_id,pay.customer_id, sum(receiptL.AMOUNT_APPLIED) paymt
        from  apps.ar_payment_Schedules_all pay
          ,AR_RECEIVABLE_APPLICATIONS_ALL receiptL
    where  pay.class ='PMT' and receiptl.status in ('ACC','UNAPP') and pay.org_id =&3
    and   pay.PAYMENT_SCHEDULE_ID =     receiptL.PAYMENT_SCHEDULE_ID
    and pay.gl_date  <= to_Date ('&1')
    and pay.GL_DATE_CLOSED >  to_Date ('&1')
     group by pay.org_id,pay.customer_id    
) payment,
apps.ra_customers cust
where cust.customer_id =  invag.customer_id (+)
and   cust.customer_id =  payment.customer_id (+) 
and  (invag.org_id is not null or payment.org_id is not null)   
/

AR Reconciliation Report

AR Reconciliation Report

This report is used for reconciling account receivable activities. This report summarizes all customer transaction, receipt and account balances for the period we specify to simply the internal reconciliation process. Following formual to help us reconcile any outstanding receivable amounts.

Difference = Total for period - end of period balance.

AR Reconciliation report collects information from the following report

Adjustment register
Applied receipt register
Aging (begining and ending balance)
Rounding account
Transaction Register
Unapplied Receipts register.
Gain /Loss
Invoice Exception report

Report parameters - Currency, GL Date Low and GL Date High

Defining Request Groups

Defining Request Groups

We should recall that request security group are used to restrict and group the concurrent reports and programs that we can run. To create a request security group, you attach a request group to a responsibility. (We can also use the request groups to customize the SRS processing or reports, which we will learn about in the next section.) In this section, we will explore defining request groups. We define request groups through the navigation path
Security/Responsibility/Request
This will take you to the Request groups form.

To create a request group, we would first enter the group (request group name)and the application. We may include requests from any application in our request group, regardless of the application attached to our request group. The application name is for informational purpose only. The group and application are both required, and must be unique across Oracle Applications. Next, we can optionally assign a code to the request group. This code is used for a custom SRS form so that only programs or reports in this request group could be processed by the custom SRS form. The code together with the application module also must be unique. As a last step , we can enter an optional description.

Enter requests that are included in the request groups by selecting the type and the request name. The type can be Application, program, Set or stage function. Application means include all concurrent programs and reports within the application. Program and Set mean a particular concurrent request or a particular request set. Function means a particular concurrent request set stage function. Query up the GL concurrent program Group to see the definition of the request group you utilized for our responsibility.

Setting Up Cash Management System Parameters

Setting Up Cash Management System Parameters

To setup system parameters in Oracle Cash Management
Navigation- Setup/System Parameters

First choose a set of books from the list of values. Then enter the begin date: This date must be backdated far enough to cover any transactions that will appear in the bank statement. It is generally safe to calculate that the date of the first statement will not contain entries earlier than its own date for instance, a check may be issued on May 20, but cashed on June 5th. If system begin begin date was dated June 1, Cash Management would recognize it. The system only recognizes the transaction date of the bank record, not our Payable or Receivable transaction date. Next, if you want to see cleared transactions that are available for reconciliation, check show cleared Transactions. If we want to reserve the ability to add lines to a statement that is loaded automatically, not manually, check Add line to Automatic Statements. If Bank statements will be loaded through the interface instead of loaded manually, check Use Open Interface. Large statements should be loaded through the interface to maximize the functionality of the program.

Next go to the General alternative region. Select the matching order for both Payables and Receivables. We have two choices : Transaction or Batch. If our system only requires reconciliation at the batch level, then select Batch otherwise select Transaction. This is how cash management will search the statement lines in order to match them up during Auto Reconciliation. Next enter the tax codes. These codes are used when there are differences in the bank statement associated with VAT. If the transactions are in dollars , select standard ; otherwise select a VAT code to represent the liability and assets for miscellaneous payments and miscellaneous receipts. When we select a Receivables activity for a transaction line, a default General Ledger account is automatically associated with the transaction line. Select an activity in the system parameters from to change the differences between the original amount and the cleared amount. This is usually a clearing account, a bank charges account, or a miscellaneous costs account. The last field in the region is Float Handling. This determines if the transaction's effective date is later than the system date and whether Oracle Cash Management should ignore the transaction or mark it as an error. We have two options Error and Ignore. Cash Management will allow reconciliation of lines with postdated transaction dates for the ignore option.

Once completed the General Alternative region, change the alternative region to Automatic Reconciliation. The settings in this region will not have any effect on manually reconciled statements, only automatic reconciliation. Choose the amount and percentage of variance with Cash Management to match statement lines to available transactions in the tolerances fields. The Amount field is a specific dollar amount of either the statement line or the transacting line. The percentage field also sets the tolerance on either the statement line or the available transactions. Next, enter the tolerance differences fields. In the AP field, select charges or errors. Charges will send the differing amount from the General Ledger to the charge account setup in payables. Errors will not send the differing amount to the General Ledger, but will cause the reconciliation to error out. In the foreign field, we can select gain/loss, charges/errors or No action. Differences will be posted to the General Ledger as a Gain/Loss, Bank Charge, or Bank Error. No action means that this item will not reconcile; it will display a status error. We may select to purge from the interface once the statement is transferred by checking purge. We may also select automatic archiving of the statements from the interface tables by checking archive. We must archive if you purge. The lines per commit field determines how many lines Oracle Cash Management should process before committing. Set the lines per commit field to a low number 5 or less. This prevents the system from stalling while processing large statements during Auto Reconciliation.

Multiple Currencies (MRC)

Multiple Currencies

Revaluing Foreign Currency Balances 

Asset and Liability accounts that are entered in foreign currencies must be revalued every period in accordance with FAS52(Financial Accounting Standards). The Revaluation program adjusts the value of the balance sheet accounts based on current period exchange rates. It then generates revalulaiton journals with adjustments to the defined unrelized gain/loss account. The revaluaiton program genrates adjustements in the Functional Currency. If you have a Reporting Set of Books you must run revalution once for each Primary and Reporting Set of Books. The Revaluation generates a revaluation batch containing a separate journal for each foreign currency. The Revaluation batch automatically has its reversal period set to the next accounting period. When you run revaluation for the next accounting period, you must reverse and post the last accounting periods revaluation batch first.

Navgation - Currency -> Revaluation

Translating Foreign Currency Balances The Translation process translates actual and budget account balances to another currency in accordance FAS52. The translation program uses three different exchange rates: period-average, period end, and historical. The translation program translates asset and liability accounts using the period end exchange rate, ownership/stockholder equity accounts using the historical exchange rate, and revenue and expenses accounts using period average exchange rate. The period average exchange rate is the average rate of all daily rates across the entire accounting period.

The profile option GL: Owners equity transaction rule effects how ownership stockholders equity account balances are translated. If the profile option is set to PTD, the net activity accounts of the accounting period are translated, then added to the corresponding prior period balances. If the profile set to YTD the balances, not the activities are translated and the translated amounts are the YTD Balances. There is no need to add the balances to previous balances.

Navigation
Currency-> Translation 

Multiple Reporting Currencies 
Multiple Reporting Currencies allows you to maintain accounting transactions in more than one functional currency. MRC is set up by creating one or more Reporting Set of Books.

MRC need if Your company is located a member state of EMU and would like to report financial data, including transaction level data, in both your functional currency and the Euro.
You must regularly report financial data including transaction level data in more than one currency because your company is multinational.

MRC Works for following modules
Assets, CashManagment, Cost Management , General Ledger, Payables, Projects, Purchasing, Receivables.

MRC works differently depdning on whether you enter the transaction inn Oracle Subledger or Oracle GL. When you enter transaction in subledgers the accounting transactions are translated into the Reporting set of books as you enter the transaction. When you transfer to Oracle GL, you must transfer to both the primary set of books and the reporting set of books you should post the transferred subledgers accounting transactions to the primary set of books as well as to all reporting set of books.

When you enter transactions in GL, the journal entries entered in the Primary Set of Books are not translated immediately into any reporting Set of Books, when the Journal entries are posted In the Primary Set of Books it will transferred to reporting Set of Books. In the reporting Set of Books you can post, revalue , translate and consolidate.

HOW TABLES ARE EFFECTED

GL Lession

Maintenance and Relevant ReportsCalendar Maintenance:
GL Can't be started without defining the first open period from the Open/Close Calendar option. One has to be very careful when selecting the first period as once opened a period prior to that can't be opened. Once opened, on an ongoing basis periods have to be opened and closed. New Years whould be required to be appended to the existing calendars.

Account Values Maintenance:
On a regular basis lot of values would have to be opened, disabled etc.
Use Mass Maintenance to move balances by period from one account to another or merge balances by period from multiple accounts into a single account. The moved/merged balances are added to the existing balances in your target accounts. To change a move/merge, one can reverse it and restore account balances to their previous amounts.
During a move/merge operation the financial integrity between GL and its sub ledgers is maintained.


Document Sequencing:Every time the validity of the Document sequence is over, define and assign new sequence.
Currency Rate Maintenance

Standard Reports:
Account Analysis, Trial Balance (Detailed, Summary, Expanded), Budget Reports, Chart of Accounts Reports and
Listings, Currency Listings Reports, FSG Reports on Row/Column Set Details, GL Report, Journal Reports and
Execution Reports.


Closing Procedure:
Set the status of the first accounting period in the new fiscal year to Future Entry.

If the business rules require reversing entries at the beginning of every period, generate and post accruals from the prior period. If prior period reversals were not generated and posted at the beginning of this period, then generate reversals

Transfer data from all of sub ledgers and feeder systems to the GL_INTERFACE table. Review and Post the imported journal entries. Close the period for each sub ledger. This prevents future sub ledger transactions from being posted to GL in the same period. Perform reconciliations of subsidiary ledgers by reviewing and correcting balances.

Generate all recurring journals and step–down allocations.

Revalue balances to update foreign currency journals to functional currency equivalents.
Post all journal entries, including: manual, recurring, step–down allocations, and reversals.
Update any unpostable journal entries and then post them again.

Run GL reports, such as the Trial Balance reports, Account Analysis reports, and Journal reports.

Translate balances to any defined currency if report in foreign currencies is required.
Consolidate subsidiary SOBs in case of multiple companies.

If using a calendar with an adjusting period that represent the last day of the fiscal year, close the current period and open the adjusting period. Create and post adjusting entries and accruals in the adjusting period.

Run Trial Balance reports and other GL Reports in the adjusting period after adjustments are made.

If it is required to have an actual closing journal entry that shows the closing of income statement accounts to retained earnings, submit the Create Income Statements Closing Journals program. This program creates an auditable closing journal entry. The income statement will reflect zero balances on posting

If local accounting rules require balance sheet to be closed, submit the Create Balance Sheet Closing Journals program. Balance sheet will now reflect zero balances on posting.

Close the last period of the fiscal year and Open the first period of the new fiscal year to launch a concurrent process to update account balances. Opening the first period of a new year
automatically closes income statement and posts the difference to retained earnings account specified in the SOB form.

Perform Year–End Encumbrance Procedures (if applicable).

Run FSG reports for the last period of the year.

If balance sheet is closed at year–end, reverse the Balance Sheet Closing Journals to repopulate balances of balance sheet accounts for the new year.

Customer Tables Understanding

Customer Tables Understanding

Hello everyone,

This is a just how customer informations are stored in apps tables. This post gives you clear explanation of how customers information are getting stored.

Customer Created Name: TESTCUST_1

Customer Accounts Created under the customer TESTCUST_1:

1.TESTCUST_1_1

2.TESTCUST_1_2

For Customer:

SELECT * FROM HZ_PARTIES

WHERE PARTY_NAME LIKE 'TESTCUST_1'

Party Id: 307957

For Customer Accounts:

SELECT * FROM HZ_CUST_ACCOUNTS hca

WHERE hca.ACCOUNT_NAME IN ('TESTCUST_1_1','TESTCUST_1_2')

Account Number: 5391,5392

Account Id: 96675,96677

Customer Accounts Sites:

Under the Customer Account:

1.TESTCUST_1_1

Sites Name:

1.TESTCUSTS1_1

2.TESTCUSTS1_2

/*2.TESTCUST_1_2

Site Name:

1.TESTCUSTS2_1

2.TESTCUSTS2_1*/

SELECT * FROM HZ_CUST_ACCT_SITES_ALL hcsa

where hcsa.CUST_ACCOUNT_ID IN (96675) -- Customer Account Id of TESTCUST_1_1 ---Sites - TESTCUSTS1_1,TESTCUSTS1_2

Customer Account Site Id: 10554 - TESTCUSTS_1_2

10558 - TESTCUSTS_1_1

SELECT * FROM HZ_PARTY_SITES

WHERE PARTY_ID = 307957 -- Check Party Site Name : TESTCUSTS1_1,TESTCUSTS1_2

Party site Id: 179402 - TESTCUSTS_1_2

179404 - TESTCUSTS_1_1

For the Site TESTCUSTS1_1:

Contact Creation:

1.Contact Name: TESTS_1_1

2.Telephone - created

3.Email

There are two Id need to get, one is subject id and other is object id

1.Subject Id is related to Organization that is customer Party id here the party id is

SELECT * FROM HZ_PARTIES

WHERE PARTY_NAME LIKE 'TESTCUST_1'

Party Id: 307957 - subject Id

2.Object Id is related to the contact name created that is Party Id of the person(Contact)

SELECT * FROM HZ_PARTIES

WHERE PARTY_NAME LIKE 'TESTS_1_1'

Party Id : 307959 - Object id

Now Check in the Relationship table using subject id and object id

SELECT * FROM HZ_RELATIONSHIPS hr

WHERE hr.SUBJECT_ID = 307957

and hr.OBJECT_ID = 307959

and the reverse entry will also be there

SELECT * FROM HZ_RELATIONSHIPS hr

WHERE hr.SUBJECT_ID = 307959

and hr.OBJECT_ID = 307957

Relatonship Id:137025

Party Id:307960

SELECT * FROM HZ_PARTIES hp

WHERE hp.PARTY_ID = 307960 -- Party_type will be PARTY_RELATIONSHIP

SELECT * FROM HZ_ORG_CONTACTS hoc

where hoc.PARTY_RELATIONSHIP_ID = 137025 -- Relatioship Id

Org Contact Id: 118071

SELECT * FROM HZ_CONTACT_POINTS hp --HZ_CUST_CONTACT_POINTS hcp

WHERE hp.OWNER_TABLE_ID = 179404 --Party Site Id of the Contact - TESTCUSTS_1_1

SELECT * FROM HZ_CUST_ACCOUNT_ROLES hcr

WHERE hcr.CUST_ACCT_SITE_ID = 10558 --Customer Account Role created when creating contacts --This is the Customer Site Id of TESTCUSTS_1_1