AR Functional Questions

SOURCE 

There has been a lot of requests to post sample questions for other financials modules also. Here i am posting a small sample of questions on Oracle Receivables.
1. Provide an overview of AR invoice processing.
2. Provide an overview of AR cash receipt processing.
3. How do you adjust receivables?
4. Do you receive deposits and/or prepayments?
5. Explain Customer Creation and Maintenance Process. Provide samples of customer master records with Payment Terms.
6. Do you track and record relationships between customers e.g. franchises, subsidiaries etc.?
7. Do you create reciprocal customer relationships whenever you create a relationship between customers for tracking purposes?
8. Do you have multiple "remit to’s"?How are multiple "remit to’s" determined for each customer?
9. What credit information is tracked for each customer?
10. How do you group your customers for credit limits, credit checks?
11. Do you do miscellaneous invoicing? Please explain with examples.
12. Do you send customers statements? If so please describe the process and highlight any exceptions.
13. How are the revenue, receivable, freight and tax accounts configured for each invoice type?14. Explain Debit Memo & Credit Memo Process.
15. Do you bill customers in a foreign currency?How many invoice types do you have? Provide invoice samples. (Credit Memo, Debit Memo etc.)
16. How do you number your invoices?
17. Where are the invoices printed? How many printers and where are they? Pre-printed forms?
18. How do you calculate your tax charges? What are the rates? Explain Tax Structure.
19. Do you validate the tax rate against a Customer’s Ship To address when preparing invoices? If so please describe how.
20. Do you charge finance charges? If so - how do you charge finance charges?
21. Explain your dunning and Collection Process. Please provide dunning statement formats.
22. Do collectors actively call customers on delinquent items?
23. What sort of buckets do you use for cash requirements forecasting? (i.e. 30, 60, 90...)
24. Do you write off receivable? Is there a specific dollar amount or time frame used by your Company for write-offs?
25. Do you have Chargeback? Explain.
26. Explain Revenue Recognition Process and explain the scenarious.
27. Explain Bills Receivable?
28. Do you associate Revenue to Sales Person.
29. How is Sales Territory Information for tracking purposes derived? (i.e. customer, salesperson etc.)
30. How do your customers generally pay invoices? single check—single invoice/single check—multiple invoices/multiple checks—multiple invoicesProvide list of Banks and Purpose?
31. Explain Payment Process?
32. Are you using Automatic Receipts? Explain?
33. Explain Remittance Process?
34. Explain your Auto Lock Box Proces if you have enabled it?
35. What types of payments are received and input? (i.e. check, cash etc.)
36. Explain Receipt Reconcilliation Process?
37. What reports do you currently generate for Invoicing, Sales and Revenue tracking? Provide samples including frequency and distribution.

Inventory related Queries

SOURCE

--- get item attributes NOT UNDER status control 
SELECT   meaning1 attrib_group, user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1,
-- lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM     mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/

-- get item status attribute controls

SELECT   ia.attribute_group_id GROUP_ID, ia.user_attribute_name_gui,
lk.meaning controlled_at, ia.attribute_name,
--   ia.user_attribute_name,
ia.status_control_code,
ia.validation_code
FROM fnd_lookup_values lk, mtl_item_attributes ia
WHERE ia.control_level = lk.lookup_code
AND lk.lookup_type = 'ITEM_CONTROL_LEVEL_GUI'
ORDER BY ia.attribute_group_id, 1
/

-- find item status attributes :

SELECT   mis.inventory_item_status_code item_status, mis.description,
mis.disable_date, av.attribute_name, av.attribute_value VALUE
FROM mtl_item_status mis, mtl_status_attribute_values av
WHERE mis.inventory_item_status_code = av.inventory_item_status_code
ORDER BY 1
/
-- get item attributes UNDER status control :

SELECT   meaning1 attrib_group, user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1, lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM     mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NOT NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/

--- find an Item attribute info :

SELECT   segment1 item, msi.description, inventory_item_id,
ml.meaning item_type,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_status_code
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_status_code')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.shippable_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.shippable_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.mtl_transactions_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.mtl_transactions_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.so_transactions_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.so_transactions_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.customer_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.customer_order_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_asset_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_asset_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.eng_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) = 'mtl_system_items.eng_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name
|| '.'
|| msi.service_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.service_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.build_in_wip_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.build_in_wip_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.bom_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.bom_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.stock_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.stock_enabled_flag')
ATTRIBUTE
FROM fnd_lookup_values ml, mtl_system_items msi
WHERE msi.segment1 LIKE 'AS18947%'
AND msi.organization_id = 204
AND msi.item_type = ml.lookup_code(+)
AND ml.lookup_type(+) = 'ITEM_TYPE'
ORDER BY 1, 2
/

--- find Item template attribute VALUES :

SELECT   it.template_name, ita.attribute_name, ita.attribute_value
FROM mtl_item_templates it, mtl_item_templ_attributes ita
WHERE it.template_name LIKE 'xxx%'
AND it.template_id = ita.template_id
AND ita.attribute_value IS NOT NULL
ORDER BY 1, 2
/

--- find  item cross-REFERENCES :


/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT   msi.segment1 item, mcr.cross_reference_type reference_type,
mcr.cross_reference, mcr.description
FROM mtl_cross_references mcr, mtl_system_items msi
WHERE mcr.cross_reference_type = 'Vendor'
AND mcr.inventory_item_id = msi.inventory_item_id
AND mcr.organization_id = msi.organization_id
ORDER BY 1, 2
/


-- find Customer items :


/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT   hp.party_name customer, ci.customer_item_number,
ci.customer_item_desc, msi.segment1 item, msi.description item_desc,
ci.customer_category_code, ci.item_definition_level,
ci.commodity_code_id, ci.address_id
FROM hz_parties hp,
hz_cust_accounts hca,
mtl_system_items msi,
mtl_customer_items ci,
mtl_customer_item_xrefs ix
WHERE ci.customer_item_id = ix.customer_item_id
AND ix.inventory_item_id = msi.inventory_item_id
AND ix.master_organization_id = msi.organization_id
AND ci.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
ORDER BY 1, 2
/

---find Manufacturer items :


SELECT   mm.manufacturer_name, mp.mfg_part_num, mp.description,
msi.segment1 inv_item, msi.description item_desc
FROM mtl_system_items msi, mtl_mfg_part_numbers mp, mtl_manufacturers mm
WHERE mm.manufacturer_id = mp.manufacturer_id
AND mp.inventory_item_id = msi.inventory_item_id
AND mp.organization_id = msi.organization_id
ORDER BY 1, 2
/

--find related items :

SELECT   ito.segment1 item, ito.description, itr.segment1 related_item,
itr.description, ml.meaning relation, ri.reciprocal_flag
FROM mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
WHERE ri.inventory_item_id = ito.inventory_item_id
AND ri.organization_id = ito.organization_id
AND ri.related_item_id = itr.inventory_item_id
AND ri.organization_id = itr.organization_id
AND ri.relationship_type_id = ml.lookup_code
AND ml.lookup_type(+) = 'MTL_RELATIONSHIP_TYPES'
ORDER BY 1, 2
/
-- find DEFAULT category FOR a category SET :

/* Formatted on 2010/08/24 11:28 (Formatter Plus v4.8.0) */
SELECT   mcats.category_set_name, mcat.segment1 default_category,
mcat.description cat_desc, mcat.category_id, mcats.category_set_id
FROM mtl_category_sets mcats, mtl_categories mcat
WHERE mcats.category_set_name LIKE '%'
AND mcat.category_id = mcats.default_category_id
ORDER BY 1, 2
/

-- find ALL items assigned TO categories OF a category SET :

SELECT   mcats.category_set_name,
mcat.segment1 || '.' || mcat.segment2 CATEGORY, msi.segment1 item,
msi.description item_desc
FROM mtl_item_categories micat,
mtl_category_sets mcats,
mtl_categories mcat,
mtl_system_items_vl msi
WHERE mcats.category_set_name LIKE 'Inv%'
AND micat.category_set_id = mcats.category_set_id
AND micat.category_id = mcat.category_id
AND mcat.segment1 LIKE 'N%'
AND msi.inventory_item_id = micat.inventory_item_id
AND msi.organization_id = micat.organization_id
AND msi.organization_id = 204
ORDER BY 1, 2, 3
/

OE ORDER LINES ALL Important columns

ordered_item_id  =  MTL_CUSTOMER_ITEMS



SELECT   hp.party_name customer, ci.customer_item_number,
ci.customer_item_desc, msi.segment1 item, msi.description item_desc,
ci.customer_category_code, ci.item_definition_level,
ci.commodity_code_id, ci.address_id
FROM hz_parties hp,
hz_cust_accounts hca,
mtl_system_items msi,
mtl_customer_items ci,
mtl_customer_item_xrefs ix
WHERE ci.customer_item_id = ix.customer_item_id
AND ix.inventory_item_id = msi.inventory_item_id
AND ix.master_organization_id = msi.organization_id
AND ci.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
ORDER BY 1, 2

Understanding ATO and PTO Models

In Oracle there are 3 different type of Models which can be created

1. ATO Models - (Assemble to Order)
2. PTO Models - (Pick to Order)
3. Hybrid Models - These are the PTO Models which have ATO models as Child

Before you start creating any of the above Models following criteria must be understood

1. If the finished product is shipped as an Assembled unit then we can use ATO Model
2. If the finishd product is shipped as disassembled unit (as loose parts) then we can use PTO Model
3. If the finshed product is shipped as disassembled unit (as loose parts) and has Assemly items which needs to be configured then we can use Hybrid Models (PTO Model having ATO Model as Child)

Few things which needs to be considered while Model designing

ATO Models can have ATO Option Class as well as ATO Models as Child.
PTO Models can have PTO Option Class as well as PTO Models and ATO Models as Child.
ATO Option Class can have ATO Option Class as well as ATO Models as Child.
PTO Option Class can have PTO Option Class as well as PTO Models and ATO models as Child.

ATO Models:

Configuration items can be created.
Standard Bill of Material can be created for Configuration items.
This BOM is consisting of the selected items in Oracle Configurator from the ATO Model.
This BOM is a single level BOM showing ATO Option Class and the item(s) selected in the ATO Option Class.
Job Order (Work Order) can be generated for the Configuration items.
Only the Top Configuration item will be eligible for Shipping.

The are standard products and are often configured by customers.
Subassemblies are manufactured prior to receiving the order and when the order is received ,the subassemblies are assembled to make the finished products
For Example : Automobiles , Laptops

This is a pull based manufacturing system where the parts are kept in stock but not manufactured until the actual order is created.  Based on the order, the work order gets created (based on a predefined BOM) and fulfilled.

PTO Models:

No Configuraion item can be created.
All the selected items in PTO Option Classes or in the PTO Model are shipping eligible.
If an ATO model is a child for the PTO Model then in that case only the Configuration item created for that ATO Model will be shipping eligible.

Models can be deisgned in various ways depending upon the Business Product Structure/Manufacturing Flow for the Product etc.

A Variety of shippable components are stocked.

Customers order kits or collection of these parts under a single item number.

Kits can be predefined or configured by the customer during the order entry process.

There is no additional value added after the customer order.

For Example : Computer System (CPU , Monitor , and Printer)

Oracle Master Scheduling/MRP and Supply Chain Planning does not support planning For PTO

Pick to Order (PTO) items have the Pick Component attribute set to Yes. 

Pick-to-order bills cannot have fractional component quantities if Oracle Order Management is installed. 

You cannot create routings for planning or pick-to-order items. 

This is a push based manufacturing system where the goods are manufactured based on forecasted demand. The goods are picked as and when the orders come in and fulfilled against the order.

KIT

A kit is similar to a pick–to–order model because it has shippable components, but it has no available options to choose like PTO model
In Oracle Inventory module, kit item type and kit item template are pre-defined by Oracle
An example of a product kit is a motor car maintenance kit which consists spanners, jack etc.
The word ‘Kitting’ is used when you make a kit after picking it's components from subinventory/ies and pack it. 


Configure-To-Order (CTO):

It is a method of manufacturing which allows you, or your customer, to select a "base product" and configure all the variable parameters associated with that product. 

It allows you, or your customer, to choose a base product at the very moment of ordering and then configure all the variable parameters (features) associated with that product from defined/available options. Based on these selections, configurable items on each quote or order typically generates the "unique product" configuration and manufacturing routing and/or bill of materials based on various features and options. Vendor/order receiving company subsequently builds that configuration dynamically upon receipt of the order. 

Revenue Recognition Conversion Question

Source

Say you have sold service or subscription for entire year in advance. Example is the yearly subscription price I pay in advance for my HBR copy. I pay about 144 USD in advance. What HBR does with this advance payment I make? Basically they had an invoice created on my name for that amount. But since the magazine needs to be delivered 12 months into the future, they have to recognize the revenue appropriately every month for each issue that is delivered. They cannot recognize the revenue in one month for all the144 USD. But I have already paid for it so they apply cash to this invoice thus bringing down the receivables to zero.

Imagine if HBSP was using a legacy system when I was a subscriber and would like to move to Oracle in the middle of my subscription. My subscription is from Jan-09 to Jan-10 and if they are migrating in Aug-09, how will this invoice converted?

In general this invoice is not considered as open because I have already paid for it (usually we convert open invoices only). So the invoice that is converted should have zero outstanding balance but outstanding revenue or in other words deferred or unearned revenue.

Why is this hard? The reason is accounting. Let us take the same example and create accounting entries for the same. In the first month when the invoice is created here is the entry.

Account	GL Date	Cr	Dr
Unearned Revenue  Account	01-Jan-2009	144
Receivables Account	01-Jan-2009		144
Unearned Revenue Account	01-Feb-2009		12
Revenue Account	01-Feb-2009	12
Unearned Revenue Account	01-Mar-2009		12
Revenue Account	01-Mar-2009	12
Unearned Revenue Account	01-Apr-2009		12
Revenue Account	01-Apr-2009	12
Unearned Revenue Account	01-May-2009		12
Revenue Account	01-May-2009	12
Unearned Revenue Account	01-Jun-2009		12
Revenue Account	01-Jun-2009	12
Unearned Revenue Account	01-Jul-2009		12
Revenue Account	01-Jul-2009	12
Unearned Revenue Account	01-Aug-2009		12
Revenue Account	01-Aug-2009	12
Unearned Revenue Account	01-Sep-2009		12
Revenue Account	01-Sep-2009	12
Unearned Revenue Account	01-Oct-2009		12
Revenue Account	01-Oct-2009	12
Unearned Revenue Account	01-Nov-2009		12
Revenue Account	01-Nov-2009	12
Unearned Revenue Account	01-Dec-2009		12
Revenue Account	01-Dec-2009	12
Unearned Revenue Account	01-Jan-2010		12
Revenue Account	01-Jan-2010	12

Since the one issue of the magazine will be delivered in this month they need to recognize 12 USD in the month of Jan-09. Also mind that I am paying this invoice. Accounting entries are:

When cash is applied against the invoice:

Account	GL Date	Cr	Dr
Receivables  Account	01-Jan-2009	144
Cash Account	01-Jan-2009		144

When revenue is recognized:

Account	GL Date	Cr	Dr
Revenue  Account	01-Jan-2009	12
Unearned Revenue  Account	01-Jan-2009		12

And this recognition continues till August in the legacy system. In August when we convert the invoice into Oracle, how to convert this? Should we convert the entire balance of receivables and unearned revenue or only the remaining unearned revenue about should be the amount in the invoice to convert?

In general cases are:

1.     Invoice is completely paid, and unearned amount is greater than receivables amount (receivables is zero)

2.     Invoice is partially paid and remaining receivable balance is less than the remaining unearned amount.

3.     Invoice is partially paid and the open balance on the invoice is more than the unearned amount.

Two important things to keep in mind are: how much should be the invoice value in all these cases? This question is relevant because receivables balance is not the same as the original invoice amount in the legacy system. And the second question is which GL date to use? And we need an accounting rule to make this happen. As invoices in the legacy can have different number of periods to recognize revenue, we define this rule as Variable type? As we schedule the revenue as soon the revenue recognition is run, we do not check the deferred flag in the accounting rule.

You can convert the invoice amount as is from the legacy and then use adjustments to reduce the receivables balance to appropriate number(zero or partial).  As for the revenue use the rule start date as the equal to the transaction date (as it is in the source). If you keep only current period open gl date will be current for all the past revenue (when revenue recognition is run) and future date for the future periods as per the schedule till the end of it. The future revenue will be recognized on that appropriate date.

But again this falls into bigger picture of your migration strategy and depends on how you convert GL balances for these accounts.