Simple Accounting Entries in Payables

  At the time of invoice (For inventory item)

Inventory AP accrual a/c Dr
To AP liability a/c Cr
At the time of invoice (For Expense item) Charge a/c Dr
To AP liability a/c Cr
At the of Payment AP liability a/c Dr
To Cash Clearing a/c
Prepayment Entry Prepaid a/c Dr
To AP liability a/c Cr
AP liability a/c Dr
To Cash Clearing a/c Cr
Cash Clearing a/c Dr
To Cash or bank a/c Cr
Adjustment of prepayment against Invoice AP liability a/c Dr
To prepayment a/c Cr
Credit memo or debit memo entry AP liability a/c Dr
To charge a/c or Inv AP accrual a/c
With holding Tax Entry AP liability a/c Dr
To Withholding Tax a/c Cr
Releasing With holding Tax Withholding Tax a/c Dr
To AP Liability a/c
Purchase price variance Entry Material a/c Dr
To Receiving a/c Cr
To PO price Variance a/c Cr
If standard price is less than PO price
Material a/c Dr
To PO price variance a/c Cr
To Receiving a/c Cr
Currency Gain Entry AP liability a/c Dr
To realized gain a/c Cr
To Cash a/c Cr

Currency LossAP liability a/c Dr
Realized Loss a/c Dr
To Cash a/c Cr
For future dated Payment AP liability a/c Dr
To Future dated payment a/c Cr
At payment Maturity Future Payment a/c Dr
To cash a/c Cr
Discount takenAP Liability a/c Dr
To System discount a/c Cr
To cash a/c Cr
Cross currency Entry Charge a/c Dr
To Liability a/c Cr
Cross currency Payment time Entry Liability a/c Dr
Rounding a/c Dr
To cash clearing a/c  

Module wise Functional Changes from in R12 & 11i

 Inventory 1- Deferred Cost of Goods Sold (COGS) Recognition
    

Starting in this release, Oracle Inventory can defer the recognition of COGS until all

contract contingencies are filled and Receivables has recognized the revenue. Oracle

Inventory holds incurred costs in a deferred COGS account until Receivables recognizes

it per the revenue recognition rules. This enables you to recognize both COGS and

revenue in the same accounting period. The new accounting rules also support

customer returns.

All sales order issue transactions are debited to the deferred COGS account except:

• Internal sales orders: For internal sales orders, the COGS account is debited directly

when the new accounting rule is not enabled.

• Intercompany transactions: For all intercompany transactions (external drop

shipments, internal drop shipments, and non-ship flows) COGS is debited directly

if the new accounting rule is not enabled.

Oracle Cost Management moves incurred costs from the deferred COGS to the COGS

account based on the revenue recognition events or order close events. The remaining
2- Material Workbench

In Release 11.5.10, Oracle Inventory introduced the option to use the Material

Workbench to view material that resides in receiving in addition to on-hand material. In

this release, you can use the Material Workbench to view detailed information about

material that resides in receiving, in-transit material, and on hand material. When you

choose to view in-transit material, you can view the following document types:

• Purchase orders

• Advanced shipment notices (ASNs)

• Internal Orders

Oracle Inventory calculates availability information according to the material location of

the relevant material. Instead of viewing on hand material, material in receiving, and

in-transit material through separate queries, you can perform one query that displays

an item across different material locations. You can also perform a query that displays

item information across organizations. This provides you with access to a global picture

of inventory for the item, and allows you to make quick decisions regarding item

sourcing and procurement.

3- Picking Rule Enhancements

In this release, the picking rule window enables you to capture individual customer

product quality and material characteristic preferences. For example, one customer may

require premium grade material, while another more price-sensitive client may not

have that restriction. To manage customer preferences, restrictions were added to the

picking rules engine allocation logic in addition to the existing sort criteria for

acceptable material. In this release, the Inventory Picking Rules window enables you to

create picking rules without installing Oracle Warehouse Management.

These rules are a subset of Oracle Warehouse Management rules and have the following

usage and restrictions:

• Allocate based on first in first out (FIFO) or first expired first out (FEFO)

• Ensure only one lot is allocated, or allow multiple lot allocation

• Restrict allocation by shelf life days

• Allow partial allocation, or ensure full allocation

• Specify matching based on item quality data

• Allocate lots in lot number sequence, or no sequence

• Allocate revisions by revision, effective date, or no sequence

• Allocate by sub-inventory, receipt date, or no sequence

• Allocate by locator, receipt date, or no sequence

• Allocate by preferred grade

• Ensure lots of indivisible items are fully consumed

• Allow over allocation

After you create the picking rules, you can use the Rules Workbench page to assign

picking rules in the following combinations:

• Item

• Item category

• Customer

• Source type

• Transaction type

When you enable a rule the system builds a rules package. After the system builds a

rules package, it creates an enabled strategy with the same name and description. If you

disable the rule, then the system automatically disables the strategy. You can only

disable rules that are not used in any disabled strategy assignments. You can also only

modify disabled rules.

Note:

The Rules Workbench available in an inventory-only

organization does not have the full capabilities of the Oracle

Warehouse Management Rules Workbench.

4- Enhanced Reservations

You can create linkages between supply and demand to guarantee material availability.

These linkages are known as reservations. A reservation guarantees the availability of

reserved supply to a specific demand. In previous releases, reservations supported

limited supply and demand types.

In this release, Oracle Inventory introduced the following new supply and demand

types:

Supply types

• Purchase orders

• Internal requisitions

• Discrete jobs

• Process manufacturing batches

• Shop floor jobs

Demand types

• Components for Complex Maintenance Repair and Overhaul work orders

• Components for process manufacturing batches

Reservations supports document validation, availability checks, and change

management for the new supply and demand types. In this release, reservations also

supports crossdocking in the warehouse, and enables you to reserve the most

appropriate inbound receipts for an outbound shipment. The crossdock attribute was

added to the Item Reservations window to link supply to demand. The system creates

crossdock reservations automatically. You cannot delete a crossdock reservation if the

supply type is receiving.

In this release, you can reserve a specific serial number and Oracle Inventory ensures

the system allocates the serial number at pick release. A new Serial Entry window was

added to enable you to reserve multiple serial numbers for a reservation. If you reserve

serial numbers, pick release allocates the serials irrespective of the picking rules. Pick

release allocates the reserved serials first and honors the organization parameter

Allocate Serial Numbers for the remaining demand. Oracle Inventory also allows you to

substitute serial numbers during picking and shipping. If you choose to substitute a

serial number, then the system deletes the reservation for the substituted serial number.

5- Demand Fulfillment Lead Time

Demand fulfillment lead time is the time between order placement and order

fulfillment. You usually set it either to the time allowed by the customer or based on

business practice. You can express a customer service level target in terms of a demand

fulfillment lead time. For example, you can set a 95% service level with a three-day

demand fulfillment lead time.

In previous releases, Oracle Inventory Optimization assumed that the lead time was

zero. And, service levels were specified in different places - item-specific service levels

as a flex field for the item, customer-specific service levels as a flex field for the

customer, and demand class-specific service levels when associating a demand class

with allocation rules.

In this release, service level and demand fulfillment lead time can be specified in one

place as part of a service level set at the following levels: Item - Organization - Demand

class, Item - Demand class, Item - Item category - Demand class, Item - Organization,

Category, Demand class, Customer site, Customer, Organization - Demand class, and

Organization.

You can enter lead time in days as a fractional number. For example, a 4-hour lead time

as 0.167 (4 hours / 24 hours).
6- Lead Time Variability

In previous releases, Inventory Optimization did not consider the variability of lead

times when it calculated safety stock levels. In this release, it calculates these variability

measures from lead times when it calculates safety stock levels:

•

Manufacturing lead-time variability: A standard deviation value that the planning

engine applies to the item processing lead time. You enter item processing lead time

in Collections Workbench form, Item Details window. Oracle Inventory

Optimization assumes that the statistical distribution of the manufacturing lead

time is normal.

•

In-transit lead-time variability: A standard deviation value that the planning

engine takes against the ship method transit time. You enter ship method transit

time in the Transit Times form. Oracle Inventory Optimization assumes that the

statistical distribution of the in-transit lead time is normal.

•

Purchasing lead-time variability: A standard deviation value that the planning

engine takes against the supplier processing lead time. If entered, Oracle Inventory

Optimization assumes that the statistical distribution of supplier variability is

normal. As in previous releases, you can also enter this value by specifying a

histogram of purchasing lead time-probability pairs. If you enter the value using a

histogram, Oracle Inventory Optimization takes the probability distribution from

the user inputs.

Order Management

Obsolete Profile Options

Here are the profile options that are obsolete. All functionality previously provided by

these profile options is now controlled by Oracle Payments.

• OM: Estimated Authorization Validity Period

• OM: Number of Days to Backdate Bank Account Creation

• OM: Payment Method for Credit Card Transactions. Control is now available at the

Payment Type level in the Define Payment Types window.

• OM: Process Payment Immediately at Booking. Control is now available at the

Payment Type level in the Define Payment Types window.

• OM: Risk Factor Threshold for Electronic Payments

Changed Profile Options

These profile options have been converted to Oracle Order Management system

parameters:

• OM: Credit Memo Transaction Type

• OM: Credit Salesperson for Freight on Sales

• OM: Employee for Self-Service Orders

• OM: GSA Discount Violation Action

• OM: Invoice Source

• OM: Invoice Transaction Type

• OM: Non-Delivery Invoice Source

• OM: Overshipment Invoice Basis

• OM: Reservation Time Fence

• OM: Schedule Line on Hold

• OM: Show Discount Details on Invoice

• Tax: Inventory Item for Freight

• Tax: Invoice Freight as Revenue

The system parameters retain the same names as the profile options without the

prefixes (OM: or Tax:). The profile OM: Employee for Self-Service Orders is replaced by

the system parameter called Requestor for Drop Ship Orders created by external user.

These profile options were changed to system parameters to support Multiple

Organization Access Control (MOAC), which allows you to access one or more

operating units using a single responsibility. Some addition benefits include:

• Implementers need to set some application controls at an operating unit level so

that the business flows they operate on can be consistent within that operating unit.

At the same time, the application controls can be set differently for different

operating units. Delivering those controls as system parameters (which are specific

to operating units) instead of as profile options meets this need.

• After you set the values of certain key application controls during an

implementation, you need to ensure that those values are not changed later in the

implementation process. Using system parameters for these controls ensures that

implementers are appropriately warned or disallowed from making such changes.

The upgrade migrates the values of the profile options to system parameter values.

Defaulting Rules

In previous releases of Order Management, seeded defaulting rules defaulted the Order

Type and Salesrep from the Customer. These defaulting rules are deleted. You can still

default the Order Type and Salesrep values from other sources, such as the Customer

Ship-to and Customer Bill-to.

The sources Customer.Order type and Customer.Salesrep are also disabled, so all

custom defaulting rules that used these source are deleted.

Oracle Apps 11i - R12 Migration – Queries for Identifying & Migrating Components

 

1. Responsibilities Listing

SELECT (SELECT application_short_name FROM fnd_application fa WHERE fa.application_id = frt.application_id) application, frt.responsibility_id, frt.responsibility_name FROM apps.fnd_responsibility_tl frt;

 

2. Menus Listing

SELECT DISTINCT a.responsibility_name, c.user_menu_name FROM apps.fnd_responsibility_tl a, apps.fnd_responsibility b, apps.fnd_menus_tl c, apps.fnd_menus d, apps.fnd_application_tl e, apps.fnd_application f WHERE a.responsibility_id(+) = b.responsibility_id AND a.responsibility_id = ‘20538’ AND b.menu_id = c.menu_id AND b.menu_id = d.menu_id AND e.application_id = f.application_id AND f.application_id = b.application_id AND a.LANGUAGE = 'US';

3. Submenu And Function Listing

SELECT c.prompt, c.description FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'F4 UK PAY Navigator';

4. User And Assigned Responsibility Listing

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60);

5. Responsibility And Assigned Request Group Listing

SELECT responsibility_name responsibility, request_group_name, frg.description FROM fnd_request_groups frg, fnd_responsibility_vl frv WHERE frv.request_group_id = frg.request_group_id ORDER BY responsibility_name

6. Profile Option With Modification Date and User

SELECT t.user_profile_option_name, profile_option_value, v.creation_date, v.last_update_date, v.creation_date - v.last_update_date "Change Date", (SELECT UNIQUE user_name FROM fnd_user WHERE user_id = v.created_by) "Created By", (SELECT user_name FROM fnd_user WHERE user_id = v.last_updated_by) "Last Update By" FROM fnd_profile_options o, fnd_profile_option_values v, fnd_profile_options_tl t WHERE o.profile_option_id = v.profile_option_id AND o.application_id = v.application_id AND start_date_active <= SYSDATE AND NVL (end_date_active, SYSDATE) >= SYSDATE AND o.profile_option_name = t.profile_option_name AND level_id = 10001 AND t.LANGUAGE IN (SELECT language_code FROM fnd_languages WHERE installed_flag = 'B' UNION SELECT nls_language FROM fnd_languages WHERE installed_flag = 'B') ORDER BY user_profile_option_name;

77. Forms Personalization Listing

SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE, ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event, ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query, (SELECT user_name FROM fnd_user fu WHERE fu.user_id = ffcr.created_by) "Created By " FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft WHERE ffcr.ID = ffft.function_id ORDER BY 1;

8. Patch Level Listing

SELECT a.application_name, DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status, patch_level FROM apps.fnd_application_vl a, apps.fnd_product_installations b WHERE a.application_id = b.application_id;

9. Function Listing

SELECT function_id, user_function_name, creation_date, description FROM applsys.fnd_form_functions_tl y order by user_function_name;

10. Request Attached To Responsibility Listing

SELECT responsibility_name , frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description FROM fnd_request_groups frg, fnd_request_group_units frgu, fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv WHERE frgu.request_unit_type = 'P' AND frgu.request_group_id = frg.request_group_id AND frgu.request_unit_id = fcpv.concurrent_program_id AND frv.request_group_id = frg.request_group_id ORDER BY responsibility_name;

11. Request Listing Application Wise

SELECT fa.application_short_name, fcpv.user_concurrent_program_name, description, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus', 'H', 'Host', 'L', 'SQL*Loader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, output_file_type, program_type, printer_name, minimum_width, minimum_length, concurrent_program_name, concurrent_program_id FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id ORDER BY description

12. Count Module Wise Reports

SELECT fa.application_short_name, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus', 'H', 'Host', 'L', 'SQL*Loader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, COUNT (concurrent_program_id) COUNT FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id GROUP BY fa.application_short_name, fcpv.execution_method_code ORDER BY 1;

13. Request Status Listing

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name , f.actual_start_date actual_start_date , f.actual_completion_date actual_completion_date, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600) || ' HOURS ' || floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) || ' MINUTES ' || round((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 - (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) || ' SECS ' time_difference , DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio n||']',p.concurrent_program_name) concurrent_program_name , decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase , f.status_code FROM apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl pt , apps.fnd_concurrent_requests f WHERE f.concurrent_program_id = p.concurrent_program_id and f.program_application_id = p.application_id and f.concurrent_program_id = pt.concurrent_program_id and f.program_application_id = pt.application_id AND pt.language = USERENV('Lang') and f.actual_start_date is not null ORDER by f.actual_completion_date-f.actual_start_date desc;

14. User And Responsibility Listing

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id and a.application_name = 'Purchasing' ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60)

15. Applied Patch Listing

SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches ORDER BY creation_date DESC

16. Query To Find Responsibility of a Concurrent Program

SELECT fcpl.user_concurrent_program_name "REPORT NAME", fnrtl.responsibility_name, frg.request_group_name, fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME" FROM apps.fnd_request_groups frg, apps.fnd_request_group_units frgu, apps.fnd_concurrent_programs fcp, apps.fnd_concurrent_programs_tl fcpl, apps.fnd_executables fe, apps.fnd_responsibility fnr, apps.fnd_responsibility_tl fnrtl WHERE frg.application_id = frgu.application_id AND frg.request_group_id = frgu.request_group_id AND frg.request_group_id = fnr.request_group_id AND frg.application_id = fnr.application_id AND fnr.responsibility_id = fnrtl.responsibility_id AND frgu.request_unit_id = fcp.concurrent_program_id AND frgu.unit_application_id = fcp.application_id AND fcp.concurrent_program_id = fcpl.concurrent_program_id AND fe.execution_method_code LIKE 'P' --'Oracle Reports' AND fcp.executable_id = fe.executable_id AND fe.application_id = fcp.executable_application_id AND fcpl.user_concurrent_program_name LIKE :conc_prog_name --AND fnrtl.responsibility_name LIKE '6010 DPW FUJ A%' -- Example Resp. Name : Inventory, Vision Operations (USA) -- AND fnrtl.LANGUAGE = 'US' --AND fcpl.LANGUAGE = 'US';

17. Query To Find Parameters and ValueSets of a Concurrent Program

SELECT fcpl.user_concurrent_program_name "Concurrent Program Name", fcp.concurrent_program_name "Short Name", fdfcuv.column_seq_num "Column Seq Number", fdfcuv.end_user_column_name "Parameter Name", fdfcuv.form_left_prompt "Prompt", fdfcuv.enabled_flag " Enabled Flag", fdfcuv.required_flag "Required Flag", fdfcuv.display_flag "Display Flag", fdfcuv.flex_value_set_id "Value Set Id", ffvs.flex_value_set_name "Value Set Name", flv.meaning "Default Type", fdfcuv.DEFAULT_VALUE "Default Value" FROM fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpl, fnd_descr_flex_col_usage_vl fdfcuv, fnd_flex_value_sets ffvs, fnd_lookup_values flv WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id AND fcpl.user_concurrent_program_name = :conc_prg_name AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE' AND flv.lookup_code(+) = fdfcuv.default_type AND fcpl.LANGUAGE = USERENV ('LANG') AND flv.LANGUAGE(+) = USERENV ('LANG') ORDER BY fdfcuv.column_seq_num;

18. Query To Find Discoverer Reports – Last Run Details

SELECT DISTINCT docs.doc_created_by doc_owner, docs.doc_name doc_name, (SELECT TRUNC (MAX (dm.qs_created_date)) FROM eul5_qpp_stats dm WHERE dm.qs_doc_name = stats.qs_doc_name) last_run FROM eul5_qpp_stats stats, eul5_documents docs WHERE docs.doc_name NOT LIKE 'Workbook%' AND stats.qs_created_date(+) > :cutoff AND docs.doc_created_date < :cutoff AND docs.doc_name = stats.qs_doc_name(+) HAVING :run_date >= NVL ((SELECT TRUNC (MAX (dm.qs_created_date)) FROM eul5_qpp_stats dm WHERE dm.qs_doc_name = stats.qs_doc_name), '01-JAN-2000' ) GROUP BY docs.doc_created_by, docs.doc_name, stats.qs_doc_name ORDER BY last_run DESC, docs.doc_created_by, docs.doc_name;

19. Query to Find Output Type of a Concurrent Program

SELECT --fcpv.application_id,fcpv.executable_id, fe.executable_name, fcpv.user_concurrent_program_name, fcpv.output_file_type FROM fnd_concurrent_programs_vl fcpv, fnd_executables fe, fnd_application fa WHERE fcpv.executable_id = fe.executable_id --AND fcpv.output_file_type = 'XML' AND fcpv.application_id = fa.application_id AND fa.basepath LIKE ('%') AND fa.application_short_name LIKE ('%') AND fcpv.user_concurrent_program_name = :concurrent_program_name

20. Query to get details of XML Publisher Templates

SELECT * FROM XDO_TEMPLATES_VL WHERE END_DATE IS NOT NULL WHERE TEMPLATE_CODE =:temp_code; select * from xdo_lobs where lob_code =:template_code;

21. Query to get the details of Valueset

SELECT flex_value_set_name,application_table_name, value_column_name, id_column_name, meaning_column_name, additional_where_clause FROM fnd_flex_validation_tables ffvt,fnd_flex_value_sets ffvs WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id and flex_value_set_name=:ValueSetName