Update The Item Average Cost From Transaction Open Interface

Update The Item Average Cost From Transaction Open Interface

INSERT
INTO mtl_transactions_interface
(
source_code ,
source_line_id ,
source_header_id ,
process_flag ,
transaction_mode ,
creation_date ,
last_update_date ,
created_by ,
last_updated_by ,
inventory_item_id ,
organization_id ,
transaction_date ,
transaction_quantity ,
transaction_uom ,
transaction_type_id ,
transaction_interface_id ,
material_overhead_account ,
material_account ,
resource_account ,
overhead_account ,
outside_processing_account,
cost_group_id
)
SELECT 'AvgCostUpdate' ,
1 ,
1 ,
1 ,
3 ,
SYSDATE ,
SYSDATE ,
1010026 ,
1010026 ,
1137465 ,
606 ,
sysdate ,
0 ,
'Ea' ,
80 ,
mtl_material_transactions_s.nextval ,
17347 ,
17347 ,
17347 ,
17347 ,
17347 ,
1327
FROM dual;

INSERT
INTO mtl_txn_cost_det_interface
(
cost_element_id ,
level_Type ,
Organization_id ,
new_average_cost ,
transaction_interface_id,
last_update_date ,
creation_date ,
last_updated_by ,
created_by
)
VALUES
(
1 ,
1 ,
606 ,
20 ,
16032545,
sysdate ,
sysdate ,
1010026 ,
1010026
);

COMMIT;

Next step, Please query and submit these transactions from:
Inventory -> Transactions -> Transaction Open Interface, submit by selecting Tools -> Resubmit All.

Note: Ensure that you have the transaction managers (Inventory -> Setup -> Interface Managers – Material Transaction Manager) up and running.

--

Interview Questions on Inventory,Purchasing

Interview Questions on Inventory,Purchasing

Questions:

1. What is an Organization & Location?
2. What are the KeyFlexFields in Oracle Inventory Module?
3. What are the Attributes of Item Category & System Items?
4. What are the KeyFlexFields in Oracle Purchasing & Oracle Payables?
5. What are the KeyFlexFields in Oracle HumanResources & Oracle Payroll?
6. How would you create an Employee (Module Name) Describe?
7. What is a Position Hierarchy? Is there any restriction to create that?
8. To whom we call as a Buyer? What are the Responsibilities?
9. How do you Setup an Employee as a User – Navigation?
10. How many Approval Groups we have? Describe?
11. Describe the Types of Requisition?
12. How many Status's and Types For RFQ's & Quotations Describe?
13. How many Types of Purchase Orders We Have?
14. What is a Receipt?
15. What is Catlog Rfq?
16. Give me online about Planned Po?
17. How can the manger view the Approval Documents Information?
18. Whether the manager can forward to Any other person? How?
19. Can u resend the document your subordinate how?
20. What is Po Summary?

Answers:

1. It is a Ware House Which you can Store the Items, And You can setup your business Organization Information Like Key Flexfilelds,Currency,Hr Information and starting time and end time. Location's are like godown place, office place, production point.

2. System items,Item Categories,Account Alias,Sales Order,Item Catalog,Stock Locators

3. The classification of items are Category Like Hard ware and Software, where as the Systems are individual items like Cpu,Key Board.

4. No Flexfields, But the help of Inventory And Human Resources we can use

5. Job Flexfield,Position Flexfield,Grade Flexfiled,Costing,People Group Flexfield,Personal Analysis Flexfield.

6. If Human Resource is Installed Hr/Payroll, If Not I can create Employee using with Gl,Ap,Purchasing,Fixed Assets

7. It is a Grouping of Persons for Approving and Forwarding the Documents from one person to another person, there is no restriction.

8. The Employee is nothing a Buyer, who is responsible for Purchase of Goods or services.

9. Security—-User—-Define, System Administration Module

10. Document Total,Accounts,Items,Item Categories,Location

11. Purchase Requistion,Internal Requistion

12. In Process,Print,Active,Closed for Rfq's In Process,Active,Closed for Quotation

13. Standard,Purchase Agreement,Blanket Po,Planned Po

14. To Register the Purchase orders/Po lines for shipment purpose

15. It contains Price breaks with different quantity levels

16. For a Agreement for long period for goods

17. Notifications Window

18. Yes, In the Notifications Window under the Forward to Push Button

19. Yes, In the Notifications Window under the Forward to Push Button

20. The Purchase Order Summay Information like total lines, and status.

--

Types of Natural Accounts in Accounting

Types of Natural Accounts in Accounting

There are 5 natures of account. Every account can have any one nature and that's why we can also call it natural account. These natures are:

Assets
Liabilities
Revenue
Expenses
Owner's Equity

ASSET: Literally asset is any thing which is valuable to a person, organization or any entity. For example we say that "his quick learning ability is an asset to him" or "Her writing ability is her asset". Why do we say that? Because quick learning skill or writing ability adds value to a person. A writer sells his writing skills to earn money, similarly in terms of business anything which is valuable to a business is the asset.
Say your organization is a pharmaceutical and manufactures Medicines, then all the chemicals used to manufacture medicine is your asset or in other words the Raw Material is your asset. The cash your organization own is an asset because it can be used to buy items or pay your employee who in turn are used to run your business. There are different types of assets, the broader categories of asset are Current Asset and Fixed, but let's not discuss it here. For now it is enough to know that asset is anything which is valuable to your organization.
Asset INCREASES when it is Debited and DECREASES when Credited.
Any organization which is registered with the government and exists as Legal Entity is obligated to disclose its Assets on the balance sheet to the government and its Creditors. You might ask Who are creditors and Why is it that an organization is obligated to disclose asset to them? With Creditor comes in the liability.
LIABILITY: Comes from the word "Liable". Literal meaning of Liable is "to be obligated" , "to be responsible" or "Legally responsible". In terms of accounting you become liable, responsible to pay when you buy or purchase any thing from another entity. You are liable to compensate whatever you've bought. Generally an organization records its liability and pays it afterward. Again, there are different types of liabilities like Short Term Liability and Long Term Liability.
Liability INCREASES when it is Credited and DECREASES when Debited.
OWNER'S EQUITY: This is the share of owner in the business.
Equity INCREASES when it is Credited and DECREASES when Debited.
REVENUE: By definition it is the total gain before inducting any expense. It is mostly associated with the Asset. When any organization sell goods or renders its services, it records an increase in Asset and with this increase comes the gain it has made from selling the goods or services. This gain is called Revenue or Income.
Revenue INCREASES when it is Credited and DECREASES when Debited.
Revenue are not displayed in Balance Sheet. They are reflected in Owner's Equity.
EXPENSE: By definition any payment made is an expense. How payments are made? Either by Cash or Credit which eventually means Cash. So redefining Expense "The outflow of cash to any person or organization for its supplied Goods or rendered Services". We incur expenses daily, for example, taxi fare is an expense, dine-out payments are expenses. Expenses are associated with Liability. Whenever an organization books a liability, it is mostly against some expense. There are different type of expense

Expense INCREASES when it is Debited and DECREASES when Credited.

Following table shows the Tabular form of the effect

Nature	DEBIT	CREDIT
Asset	Increase (+)	Decrease (-)
Liability	Decrease (-)	Increase (+)
Equity	Decrease (-)	Increase (+)
Revenue	Decrease (-)	Increase (+)
Expense	Increase (+)	Decrease (-)

Pricing information query R12

Pricing tables and links:

 pricing base tables and their links with other

 We have tested this query in R12.1.1 instance.


SELECT 
      qph.list_header_id
     ,qph.name
     ,qph.description
     ,qphh.start_date_active
     ,qphh.currency_code
     ,qphh.source_system_code
     ,qphh.active_flag
     ,qphh.orig_system_header_ref
     ,qphh.orig_org_id
     ,qphh.global_flag
     ,qpl.list_line_id
     ,qpl.start_date_active
     ,qpl.end_date_active
     ,qpl.arithmetic_operator
     ,qpl.operand
     ,qpl.orig_sys_line_ref
     ,qpp.pricing_attribute_id
     ,qpp.product_attribute_context
     ,qpp.product_attribute
     ,qpp.product_attr_value
     ,qpp.product_uom_code
     ,qpp.comparison_operator_code
     ,qpp.orig_sys_pricing_attr_ref
     ,mtl.inventory_item_id
     ,mtl.segment1
     ,mtlc.cross_reference_type
     ,mtlc.cross_reference
FROM  apps.qp_list_headers_b qphh 
     ,apps.qp_list_headers_tl qph 
     ,apps.qp_list_lines qpl 
     ,apps.qp_pricing_attributes qpp
     ,apps.mtl_system_items_b mtl
     ,apps.mtl_cross_references_b mtlc
WHERE qph.list_header_id    = qphh.list_header_id
AND   qph.list_header_id    = qpl.list_header_id
AND   qph.list_header_id    = qpp.list_header_id
AND   qpl.list_line_id      = qpp.list_line_id
AND   mtl.inventory_item_id = qpp.product_attr_value
AND   mtl.organization_id   = (SELECT UNIQUE master_organization_id
                               FROM   mtl_parameters)
AND   mtl.inventory_item_id = mtlc.inventory_item_id
AND   SYSDATE BETWEEN qpl.start_date_active  
                AND   NVL(qpl.end_date_active,SYSDATE)
AND   SYSDATE BETWEEN qphh.start_date_active 
                AND   NVL(qphh.end_date_active,SYSDATE)
AND   qph.name LIKE '%&priceListName%';

--

ORACLE GENERAL LEDGER

 

ORACLE GL CONCEPTS:

Before any system can be used, it has to be "set up". Hence usage follows setup. This is something common. Therefore before the module GL becomes financials; it must be set up.

Oracle General Ledger is a complete financial management system for recording transactions, maintaining account balances and creating financial statements.

A) VALUE SETS

The value set contains a predefined name which contains information regarding segments, viz., the data type for the segment, maximum size of the segment (not more than 25 characters) minimum and maximum values of the segments. Value set is required to ensure that there is consistency as regards codes used. For the consistency a set of regulating rules have to be recorded. These rules ensure that only the values testing positively against these conditions are accepted. It is a group of values and related attributed you assign to a key flexfield segment or descriptive flexfield segment.

Note: For each segment there should be a separate value set.

VALIDATION TYPES FOR VALUE SETS

1. INDEPENDENT: It is an individual value set and there is no relation to this value set.
2. DEPENDENT: This value set always depends on an independent value set.
3. TABLE: This value set is used for retrieving the data or values existed in a table by using select statements.
4. NONE: Values cannot be stored by using this value set. During the transaction level values are entered.
5. PAIR :
6. SPECIAL: These value sets are used for descriptive flexfields.

FIELD: It is an area where data can be entered, updated and from where it can be deleted.

FLEXFIELD: A flexfield is a field made up of sub fields or segments. A flexfield appears on your forms as a pop up window that contains a prompt for each segment. Each segment has a name and a set of valid rules.

There are two types of flexfields.

1. Key Flexfield: The flexfield used for pinpointing accurate information are called key flexfields. It is made up of segments where each segment has both a value and a meaning, so we can think of flexfield as an intelligent field that business can use to store information represented as codes.
2. Descriptive flexfield: The flexfield used for describing an entity is termed as descriptive flexfield. According to the business requirements, descriptive flexfields are used to expand oracle application or to customize the same. It enables to capture additional information from the transactions.

Accounting Key Flexfields:

The OGL module contains only one key flexfield " accounting key flexfield', which will give us the financial information like how the data will be entered, how to generate reports, viz., financial statements. Without writing any program-codes, it can be customized as per business requirements.

Structure: The combination of segments with value sets is called as a structure.

B) SEGMENT

Segment is a single sub field within a flexfield. You can define the structure and meaning of individual segment when customizing a flexfield. It is also a part of the organization where information regarding division, region, company, product, department, etc., can be stored.

Organization

Company

Department

Accounts

In GL module, there should be a minimum of 2 segments (company & accounts) and a maximum of 30 segments.

VIEW: Using the view name, the rest of the modules of oracle application can be mapped for reports.

ALLOW DYNAMIC INSERT [check box]: It allows the user to enter a new unique code combination required. (It can be expanded as required).

Segment separator: They are . : - . Using segment separators the code combination values can be identified.

Freeze Flexfield definition: After enabling this checkbox, the structural information cannot be modified. For modifying information the checkbox has to be disabled.

Compile (push button): Once the freeze flexfield checkbox is enabled, the compile push button is activated. This push button will create the code combinations and flexfield view for the flexfield structure. After clicking the compile push button, the system will provide the user a "request".

Request: It is the feedback information from the server. It finds the status of your structure and whether the program is successfully compiled. For knowing about the request status the following path is to be used. View – request.

We have 4 statuses of the request:

1. Stand by
2. Running
3. Pending

• Error
• Warning
• Inactive
• No manager

4. Completed

C] QUALIFIERS

Qualifiers are words that explain the functions performed by a particular segment. There are four flexfield qualifiers provided by oracle for accounting flexfields. They are:

• Balancing segment
• Cost center segment
• Natural accounts
• Inter-company adjustment

Balancing Segment: The balancing segment qualifier is to be assigned to the company segment or enterprise segment or corporation segment. This assignment ensures that the debits at the company level are equal to the credits by compiling with the matching principle of accounting.

Cost Center Segment: The cost center segment qualifier allows us to draw special readymade reports provided by the module oracle assets. The use of it is high utility. Cost center assignment pertains to the fixed assets. Use of cost center qualifier is not mandatory, it is optional. By attaching this qualifier to any segment, income and expenditure of any cost division can be found out viz., department-wise, division-wise, cost is basically incurred at department / division.

Natural Accounts: This qualifier allows the user to specify the account types. The standard account types provided are:

Expenses

Revenue

Assets

Liabilities

Ownership / stockholders' equity

Inter-company adjustment: For adjustment of inter-company transactions, this qualifier is used. It is not mandatory.

Segment Qualifiers:

1. Allow Posting: By attaching this qualifier at any segment, the journals will be allowed to post at that segment level.
2. Allow Budgeting: Budgets will be prepared at segment level when this qualifier is attached.
3. Account Type: The account types, viz., expenses, revenues, assets, liabilities, stockholder / ownership describes the type of account. In addition to 'allow posting' and 'allow budgeting', this qualifier is also attached to accounts.
4. Reconciliation Flag: For account segment to reconcile the accounts with any sub accounts.
5. Control account: For controlling the sub accounts, if any.

D] CURRENCY (Define or enable)

Code Description Territory (country) Precision

INR Indian Rupee India 2

USD US Dollar USA 2

AUD Australian Dollar Australia 2

(ISO has recognized approx. 240 country's currency)

Using the currency code, we can record the expenses and income of every country. Whenever, oracle application is installed, it creates all the ISO currency codes. If those defined currencies are to be used, we have to enable that currency.

Non ISO currency codes can also be created.

E] PERIOD TYPES

Period – Daily / Monthly / Quarterly

Using period types, you can divide the financial year as per requirements for reporting purposes. In a fiscal year, two calendar years are covered.

Future Period: If the user gives No1 option, one future period will be opened.

F] CALENDAR

Accounting calendar

This calendar defines your accounting periods and fiscal years in OGL. Using accounting calendar window, accounting calendars are defined. Oracle financial analyzer will automatically create a "Time Dimension" using your accounting calendar. Give a prefix to every period to identify the same.

Fiscal Calendar

Without relation to a calendar year, any yearly accounting period is called a fiscal year. If fiscal year has been defined, the user should specify the AD of termination of the year.

Calendar Year

If the calendar year has been defined, the user can specify the AD of commencement.

Transaction Calendar

Using this calendar, you can setup the business on and off days (i.e., holidays). Apart from weekly holidays, additional holidays can be included. This calendar helps to restrict the user to enter any transactions on any holiday or business off days. The financial institutions will generate average balances reports with the help of this calendar.

G] SET OF BOOKS

A financial reporting entity that uses a particular chart of accounts, functional currency and accounting calendar, at least one set of books has to be defined for each business location.

Standard Options

Allow suspense posting

Enable average balance

Journal approval

Journal Entry tax

Budgetary Control Options

Enable Budget Control

Required Budget Journal

Average Balance options (Amount Rate Type)

QTD – Quarter To Date

PTD – Period To Date

YTD – Year To Date

EOD – End Of Day

Mandatory Accounts

Retained Earnings: The earlier period or earlier year balances will be transferred whenever the user closes the accounting period. This is a compulsory account to be created.

Suspense account: The difference in the amounts of debit and credit of the transactions will be transferred to this account. If the user, enables the 'allow suspense posting' checkbox in the standard option then this account gets activated.

Rounding off adjustment: The transaction total amounts can be adjusted (rounded off) to the nearest currency denomination and the difference is posted to this account.

Translation Adjustment account: If the user wants to translate the functional currency balances to foreign currency and if there is fluctuation in the currency rates in between the periods then the difference because of currency rates will be recorded in this account.

Reserve for encumbrance: Prepayments or anticipated expenditure is called as encumbrance. If budgets are to be prepared, this account will be created by enabling "budget control" in the budget control option.

Net Income: If the average balance option is enabled in the set of books, enabling "average balance" check box in standard options will create the net income account. This account is not to be posted manually.

Mandatory Account Chart

Code	Account Name	Allow Budgeting	Allow Posting	Type of account
M01	Retained Earnings	Yes	Yes	Stock/ownership
M02	Suspense Account	Yes	Yes	Asset/ Liability
M03	Rounding Off Difference Account	Yes	Yes	Stock/ownership
M04	Translation Adjustment	Yes	Yes	Stock/ownership
M05	Reserve for encumbrance	Yes	Yes	Stock/ownership
M06	Net Income	Yes	No	Stock/ownership

H] USER PROFILE

After defining set of books, the books will have to be assigned to a single or multiple users. After assigning the books, the user can perform and record transaction in the set of books.

I] OPEN / CLOSE ACCOUNTING PERIODS

After assigning the set of books, for recording the accounting transactions, the user should open the desired accounting period. Once the transactions are completed, the period can be closed.

The five statuses of accounting periods are:

Never Opened

Open

Future period

Closed

Permanently closed

J] DETAILS OF TABLES IN GL

Fundamental (Master) Tables:

Fnd_Applications

Fnd_ID_Flex_Structure

Fnd_ID_Flex_Code

Fnd_Tables

Fnd_Flex_Values

Fnd_ID_Value_Sets

Fnd_Columns

Base or Set Up Tables

Gl_Code_Combination (view)

GL_Set_Of_Books

STEPS FOR DEFINATION OF SET OF BOOKS

1. Define value sets

Setup: Financials: Flexfield: Validation: Sets

2. Define key flexfield segments

Setup: Financials: Flexfield: Key: Segments

3. Enter the segment values

Setup: Financials: Flexfield: Key: Values

4. Define or enable currency

Setup: Financials: Currency: Define

5. Define period types

Setup: Financials: Calendar: Type

6. Define accounting calendar

Setup: Financials: Calendar: Accounting

7. Define transaction calendar

Setup: Financials: Calendar: Transaction

8. Define set of books

Setup: Financials: Books: Define

9. Attach set of books to profile

Other: Profile

10.Sign in again

11.Open the accounting period

Setup: Open/ Close

 

R12 ORDER MANAGEMENT – WHAT’S CHANGED?

 

 

R12 ORDER MANAGEMENT – WHAT'S CHANGED

 

 

Good question. I'm in the process of figuring this out myself. However, here are a few things I do know based on my research and my recent R12 engagement.

Multiple Operating Unit Access – In 11i, a responsibility could only be tied to a single operating unit. However, in R12, responsibilities can be assigned to one or more operating units by assigning the responsibility to a security profile. This is a great enhancement for businesses where users require multi-organization access.

Credit Card Entry Enhancements - An additional field has been added to the order entry screen to capture the security code typically located on the back of a credit card. Additionally, the credit card number is encrypted at the database level and stored within the Payments (formally iPayments) module. I'll put out a more detailed article that talks about the drastic changes to the iPayments module.

Reoccurring Charges - Functionality has been added to the TSO (Telecommunications Service Ordering) module which allows reoccurring billings. This is perfect for subscriptions and other like services.

Partial Period Revenue Recognition – A joint OM and AR enhancement, partial period revenue recognition is a set of new revenue recognition rules in AR which allow for revenue recognition on a daily basis. Previously in 11i revenue recognition could only be done on a monthly basis.

Pay Now or Pay Later – Upfront billing in Order Management can now be done and can be configured based on pricing charges, taxes, deposits, installment billing, and prepayments.

Mass Scheduling Changes – The scheduling concurrent request can now pick up lines that have errored in the scheduling workflow activity. It also can now pick up lines that are in Entered status – both converted and manually entered orders.

Customer Acceptance Process - Oracle R12 now provides the ability to introduce a customer acceptance step prior to invoicing. By setting up a deferral reason in AR, invoicing can be deferred until acceptance has been captured.

Tax Updates – Additional tax fields have been added to the order entry screens that provide more flexibility with Vertex.

 

 

Architectural Changes to Inventory - in 11i, Discrete and Process Inventory modules were entirely separate entities. Oracle has converged these modules into one data structure, where functionalities from both modules have combined to be available to both. This doesn't mean much from an OM perspective, but technical changes have been performed to the Pick Release and Ship Confirmation processes to work with the new combined inventory model.

Architectural Changes to OM, Install Base, and Service Contracts Integration – My understanding is that there is no functionality changes, but from a technical standpoint Oracle has implemented a best practice approach  to OM to Install Base and OM to Service Contracts touchpoints to ensure API calls are used rather than direct queries.