CHAPTER 3: Oracle Apps Order to cash(O2C) Cycle

CHAPTER 3: Oracle Apps Order to cash(O2C) Cycle:--


AR Invoice:--


-------


After confirm to ship flow from shipping screen,


run the workflow back ground process to create the automatic AR invoice from respective responsibility(order management).


Pass parameter as


Item type=OM Order line


process deferred : yes


process timeout : yes


process stuck : yes


Navigation:--


--------------


order management super user vision operations-->view-->request-->submit a new request


choose workflow back ground process


parameters:


-----------------


item type : om order line


process deferred : yes


process timeout : yes


process stuck : yes


click ok


it will launch the following concurrent program.


1.)auto invoice master program, 2.)Auto invoice import program

it will launch auto invoice master program and auto invoice import program to create invoices actually.


to see the invoice number after completion of all the programs ,we will switch over to account receivables responsibility


receivables transactions-->transactions


f11-->enter the order number under reference field.


it will give us invoice number.


here invoice number means transaction number only.

Or from order management window


Identify invoice number from front-end:----


Go to order managementorder entry query the so numberline item actionadditional detailsgo to invoices/credit memo tab, get the invoice number from the sales order window


click on invoice details, it will redirect you to receivables responsibility, transactions-->transactions


base tables:--- Transaction windows tables:--ar invoice


receivables>transactions>transactions


------------


1.ra_customer_trx_all


2.ra_customer_trx_lines_all


3.ar_payment_schedules_all


SELECT interface_header_attribute1, interface_header_attribute2,


interface_header_attribute3, interface_header_attribute10


FROM ra_customer_trx_all


WHERE trx_number = '10047320'—ar invoice number


interface_header_attribute1-- sales order number


interface_header_attribute2- sales order type


interface_header_attribute3- delivery id


interface_header_attribute10-- ware house


select * from ra_customer_Trx_lines_all


where customer_trx_id='1207195' and line_type='LINE';


select due_date,amount_due_original,amount_due_remaining from ar_payment_schedules_all where customer_trx_id='1207195'


link between om and AR :---


---------------------------------------------------------


header level join(header to header)


SELECT ooh.order_number, rcta.trx_number


FROM oe_order_headers_all ooh,


ra_customer_trx_all rcta


WHERE TO_CHAR (ooh.order_number) = rcta.interface_header_attribute1


and


trx_number = '11048329';


Note: interface_header_attribute1 contains the sales order number column having character data type that’s why to_char()used for order number.


Line level join of transaction(header to line):--


SELECT ooh.order_number,rcta.sales_order


FROM oe_order_headers_all ooh,


ra_customer_trx_lines_all rcta


WHERE TO_CHAR (ooh.order_number) = rcta.sales_order


and customer_trx_id='1237199';


note:if we will not put correct join then query will give the Cartesian product results.


Line to line link:--


SELECT rctla.sales_order


FROM oe_order_lines_all oola,


ra_customer_trx_lines_all rctla


WHERE TO_CHAR (oola.line_id) = rctla.interface_line_attribute6


AND customer_trx_id = '12071957'


query to display incomplete invoices or AR Open Invoices:--


SELECT


hca.account_number acct_nbr


, ctx.trx_number trx_nbr


, TO_CHAR(ctx.trx_date,'DD-MM-YYYY') trx_date


, ctt.NAME tran_type


, ctx.customer_trx_id


, ctx.bill_to_customer_id


, ctx.cust_trx_type_id


FROM apps.ra_cust_trx_types_all ctt


, apps.hz_cust_accounts hca


, apps.ra_customer_trx_all ctx


WHERE NVL(ctx.complete_flag,'N')='N'


AND ctx.bill_to_customer_id = hca.cust_account_id


AND ctx.cust_trx_type_id = ctt.cust_trx_type_id


or in simple sentence like below


SELECT * FROM Ra_customer_trx_all where complete_flag='N' and org_id=101


How to show the name and number of the bill-to customer:--


select hzp.party_name,


hcac.account_number


from ra_customer_trx_all rct,


hz_parties hzp,


hz_cust_accounts hcac


where hzp.party_id=hcac.party_id


and hcac.cust_account_id=rct.bill_to_customer_id


and rct.TRX_NUMBER='12347339';


How to show the bill to customer number and ship to customer name:--


select hzp.party_name,


hcac.account_number


from ra_customer_trx_all rct,


hz_parties hzp,


hz_cust_accounts hcac


where hzp.party_id=hcac.party_id


and hcac.cust_account_id=rct.ship_to_customer_id


and rct.TRX_NUMBER='198543289';





how to derive credit memo ?


credit memo meaning– negative amount


use the self join


select rct.TRX_NUMBER invoice_number,


credit.TRX_NUMBER credit_memo_number


from


ra_customer_trx_all rct,


ra_customer_trx_all credit


where rct.CUSTOMER_TRX_ID=credit.PREVIOUS_CUSTOMER_TRX_ID


and rct.TRX_NUMBER='118956790'


-- self join


Credit Memo: A negative sum to lower the invoice's balance.


An illustration of a situation where a credit memo is created is:


Let's say you have prepared and delivered a $5,000 AR invoice to ABC, your customer. After the goods arrived, the buyer discovered one of them


worth $1000 is broken, and I had to send it back. Although we have previously sent them a $5,000 invoice, we should only charge them $4000 as a result of the $1000 reimbursement for the defective item.


In this case, we provide a $1000 credit memo, which we apply to the $5000 invoice to reduce its balance to $4000.


To inform the consumer that they just need to pay us $4000, we send the credit memo message to them once more.


To create credit memo : go to shipping>transaction>query the sales order Number, now, go to actions- credit memo


Or, if above navigation not worked,try below one


Go to order management>order entry> query the so number>line item >action>additional details then go to invoices/credit memo tab,


get the invoice number from the sales order window


click on invoice details, it will redirect you to receivables responsibility, transactions-->transactions


1.)Standard invoice/transaction


Nav:receivables>Transactions>Transactions


We will give the source = Videojet invoice source, it will pick up the type and type will pick up the class automatically.


Now we will give the customer name and tab out it will fill up the remaining fields in ship to and bill to section and save. Transaction number will generate, now give the details in line item and if required give details in freight and tax(coming from e-business tax) window.


Transaction number=1001

Now Click on complete button for validating the fields then it will show the values under Balance due section and complete checkbox will be checked.

When you will click on icon besides the transaction Number field it will give the pdf print of invoice as shown below