Important Queries for oracle receivable, Payables

Below are the few important queries which are used in oracle

Important Queries

1.Below query will fetch all the responsibilities attached to a user

Select   fu.user_name, 
            fr.responsibility_name, 
            furg.START_DATE, furg.END_DATE
from   fnd_user_resp_groups_direct furg, fnd_user fu,                                  fnd_responsibility_tl fr
where  fu.user_name = :user_name
and      furg.user_id = fu.user_id
and      furg.responsibility_id = fr.responsibility_id
and      fr.language = userenv('LANG')

2.Query to find all the users which get updated in last one month 

select vs.name,a.*
from(
SELECT
     fuser.description
    ,fuser.USER_NAME USER_NAME
    , fuser.employee_id
    ,fuser.start_date
    ,fuser.end_date
    ,papf.full_name
    --,frt.RESPONSIBILITY_ID RESPONSIBILITY_ID
    --,frt.RESPONSIBILITY_NAME RESPONSIBILITY
    ,fuser.creation_date creation_date
    ,fuser.last_update_date last_updated
    ,(select count(1) from FND_USER_RESP_GROUPS furg, FND_RESPONSIBILITY_TL frt where frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID and fuser.USER_ID = furg.USER_ID AND frt.LANGUAGE = 'US') resp_count
    ,(select count(1) from wf_user_role_assignments_v furg, FND_RESPONSIBILITY_TL frt where 'FND_RESP'||frt.application_id||':'||frt.RESPONSIBILITY_ID = furg.ROLE_name AND frt.LANGUAGE = 'US' and fuser.USER_name = furg.USER_name and furg.creation_date>=sysdate-30) resp_added_in_last_month_count
FROM
    FND_USER fuser
    --, FND_USER_RESP_GROUPS furg
    --, FND_RESPONSIBILITY_TL frt
    , (select full_name,person_id from per_all_people_f papf1 where sysdate between papf1.effective_start_date and papf1.effective_end_date) papf
    --,wf_user_role_assignments_v wura
WHERE
    1=1
    --and wura.user_name=fuser.user_name
    --fuser.USER_ID = furg.USER_ID
    --AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
    --AND frt.LANGUAGE = 'US'
    --and fuser.last_update_date>=sysdate-30
    and fuser.last_update_date>=sysdate-30
    and papf.person_id = fuser.employee_id(+)
    -- AND frt.LANGUAGE = 'US'   
) a, v$database vs
where a.resp_added_in_last_month_count <> 0 or a.end_date is not null
order by a.user_name

3.Query to find the count( number of ) invoices created between particular date and time 

select trunc(creation_date) start_date,
       to_char(creation_date, 'hh24:mm') START_TIME,
       count(*) total,
       sum(case when nvl(attribute15,'PAPER') = 'EDI'  and trx_number not like '%CR' then 1 else 0 end) EDI_INVOICES,
       sum(case when nvl(attribute15,'PAPER') = 'PAPER' and trx_number not like '%CR' then 1 else 0 end) PAPER_INVOICES,
       sum(case when trx_number like '%CR' then 1 else 0 end) CREDIT_MEMO
from ra_customer_trx_all 
where creation_date between to_date('2016-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2016-05-02 23:59:59','YYYY-MM-DD HH24:MI:SS')
group by creation_date,to_char(creation_date, 'hh24:mm:ss')
order by start_time

How to setup Transaction Types In Oracle Cloud Receivables

In this blog we will see what is a transaction types in oracle and how to setup transaction types in oracle receivables.

Transaction Types setup in Oracle receivables:

Transaction types in oracle are used to default tax, freight, creation sign , receivables information etc on the transaction.

Transaction types in oracle receivables also checks whether your transaction entries will update your customers' balances and whether Receivables will posts these transactions to your general ledger or not.

In oracle you can associate transaction types with the  invoice sources in the Transaction Sources window . This will speed data entry while creating transaction and credit transaction.

There are two predefined transaction types in oracle receivables:

1. Invoice

2. Credit Memo.

Detailed Steps to create Transaction Types in oracle receivables

1.      Navigate to the Receivables Responsibility >Setup >Transactions >Transaction Types

2.      In operating Unit field by default will have current operating unit.

3.      Enter the unique Name and Description of the transaction type.

     

4.      Select a Class for the transaction type.

How to setup Transaction Types In Oracle Receivables

Below are the seeded classes available in oracle

1.   Invoice

2.   Chargeback

3.   Credit Memo

4.   Debit Memo

5.   Deposit

6.   Guarantee

For the case of credit memo 

5. Set Open Receivable is to Yes,

Customer balances will be updated each time when we create a debit memo, credit memo, chargeback, or on-account credit with this transaction type in receivables. these will also be included in the standard aging and collection processes in receivables.

6.Select the Post to GL checkbox

If this check box is selected you will be able to post transactions with this type to general ledger.This box should always be checked if the class is Deposit or Guarantee.

7.Select Print. 

     

This can overridden while entering transactions in transaction window in receivables.

8. Select Transaction Status of From the LOV. The values available are open, Closed, Pending, or Void. 

9.Select the Allow Freight box.

This will allow freight to be entered for transactions having transaction type

10.Select the Tax Calculation box.

Tax calculation will be performed only for the transactions which will have this transaction type and this check box is selected. If this checkbox is not selected then receivables will not calculate the tax for the transaction with this transaction type.

11.Select Creation Sign.

The default values selected in this is Positive Sign for transaction types having class of either Guarantee or Deposit. If we use Cash Basis accounting in that case Transaction's creation sign should be Positive Sign, Negative Sign, or Any Sign. This filed can not be updated once selected for any transaction types.

        

12.Check the Natural  Application Only box. This cannot updated once saved this transaction type.

13.Enter the account values like receivable account , freight account etc .these account will be defaulted when we create any transaction for this transaction type.

How to setup Types In Oracle|Transaction Types setup in oracle|Transaction Types In Oracle Receivable

What is Collectors in oracle receivables 

In this blog we will see what is collectors in oracle receivables and how to create collectors in oracle receivables

What is Collectors in oracle:

Receivables allows you to create collectors and you can them to a specific profile class or to a customer's credit profile class. After you assign a collector a to profile class, that collector becomes the collector for customers which are assigned with that profile class. 

Collector in oracle is that the one who is chargeable for collection of amounts. The Collectors are going to be appointed to a profile class or to a customer's credit profile class. Once you assign a collector to a profile class, that collector becomes the collector for all customers appointed that profile category. 

 Steps to create collectors in oracle receivables:

1. Go to the Collectors window.

2. Enter the Name and Description of the collector. 

3. Enter the other details as well like telephone number, correspondence address . If you want you can use this information on your dunning letters as well.

4. Save your work.

Navigation for Collector in Oracle Receivables

Go to Navigator>Setup>Collections>Collector

2. The collection window will open up

Collectors in oracle receivable | Defining Collectors in oracle receivable 

How to create Chargeback in oracle receivables

In this post we will see what is chargeback in oracle receivables and how to create chargeback in oracle receivables 

Chargeback in receivables is created when you want to close original invoice which customer has paid and crate a new invoice with left amount that customer has to pay yet.

Lets understand chargeback with an example in oracle receivables

Suppose a customer has purchase an item of INR 500 and he has paid INR 475 then we will  create chargeback of INR 25 in receivables for that customer and close the original invoice

Chargeback Cycle in oracle receivables:

• First we will Create Invoice for the items that the customer has bought from us.

•  After receiving the payment from customer, create will create a receipt

•  This receipt will be applied to the invoice

•  Now we will Create a Chargeback for the remaining amount which customer has to pay.

•  Now when  the customer pays the remaining amount ( at alter point of time), we will create a receipt and  apply it to the chargeback.

Lets see how to create chargeback in oracle receivables step wise

1. Invoice Creation: As Assumed customer bought a camera worth INR 500 from us. We will create invoice for INR 500 for that customer in account receivabels.

Nvigation : Receivables responsibilty>Transaction>transaction

2. Receipt creation: Now customer has sent payment of INR 475 instead of INR 500. Once we receive the payment, we will create a receipt for INR 475 in receivables.

Navigation for creating a receipt: Receivables responsibility > Receipts > Receipts

Enter the information as below:

Receipt Method: Choose one of the method form the drop down lists. In this case we have selected manual.

Receipt Number: Here enter the receipt number. This should be a unique number.

Receipt amount: This will be the amount paid by customer

Save it. A receipt will be created in the system.

How to create chargeback in oracle receivables

3.  Application : Apply the receipt to the receivables invoice against which we have received the payment.

To apply the receipt to a invoice, click on Apply button on the lower right hand side of the receipt.

Enter the invoice number on which you want to apply the receipt in Apply to column and save it. (Apply check box is checked before saving the work)

After applying the receipt to the invoice, the balance on the invoice will remain INR 25.

4. Create Chargeback in oracle receivables: 

Now the original invoice is closed and the customer has remaining balance as 25INR. We will have to create a chargeback in receivables for the customer.

Navigate to Receipts and query our receipt which we created earlier and click on Apply button

Now click on the Chargebacks button available

Enter the details like transaction Type and the Amount for this chargeback. By default the chargeback amount is the remaining amount of the transaction.

Save the work. On saving, the chargeback number will be generated automatically.

You can Check the balance on invoice as well, it will be INR 0

How to create chargeback in oracle receivables

Now try to query the Chargeback. You will see there is  balance of INR25, which the customer has to pay.

Chargeback in oracle receivables | How to create Chargeback in oracle receivables