Oracle Projects- Accounts Payable Integration

Oracle Projects- Accounts Payable Integration

PA - AP  Flow:
Here iam considering the AP invoice integration with Oracle Projects. I will explain about the Expense Reports flow  in another topic.
 This integration involves 2 sub processes given below:
Interfacing Supplier Invoice From Oracle Payable To Oracle Projects
Interfacing Supplier Invoice Adjustments From Oracle Projects To Oracle Payable
Interfacing Supplier Invoice From Oracle Payable To Oracle Projects:
This process involves :
Creating the invoice.
Approve the invoice.
Account for the invoice in payable.
Run the 'Interface Supplier Costs' process in Oracle Projects which interfaces the invoices from AP to PA.
Interfacing Supplier Invoice Adjustments From Oracle Projects To Oracle Payable:
This process involves:
Adjust the supplier invoice interfaced to Oracle Projects by either splitting the quantity or transferring an invoice from one project/task to another.
 Execute the PRC: Distribute Supplier Invoice Adjustment Costs process in Oracle Projects.
Run the PRC: Interface Supplier Invoice Adjustment Costs to Payable process in Oracle Projects.
Important Project Related Fields in AP:
Project Name - Project Name to which the Invoice is accounted in Payable.
Task Number - Task Number of the Project to which the invoice is accounted. Note: If the Task is not chargeable, the system displays the following error message: APP-PA-19270 The Task is Not Chargeable. The same error will be received if the Chargeable Flag is not checked while defining Tasks in Projects Setup or if the task is a Parent Task. Expenditures cannot be created at the Parent Task level.
Expenditure Types - Expenditure Type of the invoice. This is based on the projects expenditure types (pa_expenditure_types table)
Expenditure Item Date - The date of the invoice expenditure item to be created in Projects.
Expenditure Organization - Active Project Expenditure/Event Organization against which the invoice has to be mapped.
The Expenditure Org can be find in PA_ALL_Organizations table with pa_org_use_type = 'EXPENDITURES'. For any organization to be a Expenditure Organization, it has to be classified in HR as 'Expenditure/Event Organization'.
Quantity - Based on the Expenditure Type definition, quantity is verified by checking the PA_QUANTITY column in the AP_INVOICE_DISTRIBUTIONS_ALL. If the COST_RATE_FLAG column in PA_EXPENDITURE_TYPES table is set to Y, then the quantity field in the Payables Invoice Workbench needs to be filled in.
Once all the requisite information has been entered and the invoice distribution saved, the system checks whether the values given in the Project, Task, Expenditure Type and Expenditure Organization fields are active as of the Expenditure Item Date.
 The following columns in the AP_INVOICE_DISTRIBUTIONS_ALL table are relevant for project-related supplier invoices:

PROJECT_ACCOUNTING_CONTEXT - This column is set to Yes if the Project ID column is filled.
ASSET_ADDITION_FLAG - If the project-related invoice distribution is charged to a Capital Project, then the  ASSET_ADDITION_FLAG is set to P when the PA_ADDITION_FLAG is set to Y, Z or T.
To avoid the same invoice distribution being interfaced to both Projects and Fixed Assets, you must interface any project-related invoice distribution to Oracle Projects before you interface it to Oracle Assets.
PA_ADDITION_FLAG - The PA_ADDITION_FLAG tracks the status of project-related supplier invoice distribution lines and expense report distribution lines. For supplier invoice distributions entered via Oracle Payable, the PA_ADDITION_FLAG is set to N if the distribution is project-related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process.
 Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Projects.  For supplier invoice adjustment lines interfaced from Projects to Payable (which must net to zero with another line), the value for the PA_ADDITION_FLAG is set to T.  Listed below are the Quick Codes available for the PA_ADDITION_FLAG:
B  No open PA period
C  Task does not allow charges
 D  Outside project dates
E  Non-project related invoice distributions
 I  Outside task dates
J  Project level transaction controls violated
K  Task level transaction controls violated
M  Invalid project/task combination
 N  New line not yet processed by Oracle Projects
P  Project is closed
Q  Transaction control extension violated
S  Temporary status used during processing
T  Adjustment line transferred from Oracle Projects
V  Invalid data (catch-all error)
X  Burdening error
Y  Transferred to Oracle Projects
Z  Net zero adjustment line. Never transferred to PA
  If an item is rejected, you must correct the rejection reason and re-run the interface process.
Once the Invoice is interfaced to Projects, the following tables are populated with appropriate values:
PA_EXPENDITURE_GROUPS_ALL
PA_EXPEDITURES_ALL
 PA_EXPENDITURE_ITEMS_ALL
 PA_COST_DISTRIBUTION_LINES_ALL
The interrelationship between the relevant Payables and Projects tables are as follows:
 

AP_INVOICES_ALL	PA_EXPENDITURES_ALL	PA_COST_DISTRIBUTION_LINES_ALL
INVOICE_NUM	ORIG_USER_EXP_TXN_REFERENCE
INVOICE_ID	ORIG_EXP_TXN_REFERENCE1	SYSTEM_REFERENCE2
VENDOR_ID	VENDOR_ID	SYSTEM_REFERENCE1
ORG_ID	ORG_ID	ORG_ID

  

AP_INVOICE_DISTRIBUTIONS_ALL	PA_EXPENDITURE_ITEMS_ALL	PA_COST_DISTRIBUTION_LINES_ALL
DISTRIBUTION_LINE_NUMBER		SYSTEM_REFERENCE3
DIST_CODE_COMBINATION_ID		DR_CODE_COMBINATION_ID
INVOICE_ID		SYSTEM_REFERENCE2
EXPENDITURE_TYPE	EXPENDITURE_TYPE
EXPENDITURE_ORGANIZATION_ID	EXPENDITURE_ORGANIZATION_ID
PROJECT_ID	PROJECT_ID	PROJECT_ID
TASK_ID	TASK_ID	TASK_ID
EXPENDITURE_ITEM_DATE	EXPENDITURE_ITEM_DATE	GL_DATE
ORG_ID	ORG_ID	ORG_ID
PA_QUANTITY	QUANTITY	QUANTITY
AMOUNT	RAW_COST	AMOUNT

It is possible to map between the TRANSFER_STATUS_CODE column in the PA_COST_DISTRIBUTION_LINES_ALL table and the 

PA_ADDITION_FLAG column in the  AP_INVOICE_DISTRIBUTIONS_ALL table.  TRANSFER_STATUS_CODEs available in the PA_COST_DISTRIBUTION_LINES_ALL table for supplier invoices are:

V- Interface from Oracle Payable - Upon creation set to Received

P - Pending - Upon execution of Distribute Cost Process after Supplier Invoice Adjustments

G - If payable rules do not allow adjustments to the invoice (example: if the invoice is cancelled), then distribute supplier invoice adjustments program would create the CDL and set the value to (G), these lines will always reside in Oracle Projects and will not be transferred to Oracle Payable

A - Transfer Costs - If successfully transferred to Oracle Payable, set to Accepted

X - Rejected in transfer to Oracle Payable, set to Rejected in Transfer

Oracle Template Forms

Template Form in Oracle Apps 11i/R12

This document provides an overview of the template form. This form derives its importance from the fact that this form is the starting point of all development involving forms. The document highlights the importance of Template.fmb in forms development and also provides a detailed explanation of the various components of the Template form. 

Figure -1 Template.fmb
Overview of the Template Form
The TEMPLATE form is the starting point for all development of new forms. The first step in creating a form for use in Oracle Applications is to copy the template form from $AU_TOP/forms/US, to a local directory and renaming it. 
The Template form is unique because it contains some special libraries and triggers that render the application using the template form some standard characteristics. The components of the template form are: 
⦁    References to object groups: The template form contains platform–independent references to predefined standard object groups in the APPSTAND form (STANDARD_PC_AND_VA,STANDARD_TOOLBAR, and STANDARD_CALENDAR). 
⦁    Libraries: The template form contains platform–independent attachments of several libraries (including FNDSQF, APPCORE, and APPDAYPK). 
⦁    Special triggers: The template form contains several form–level triggers with required code. These are responsible for standard the behavior of the form. 
⦁    Predefined Program Units: The template form contains predefined program units that include a spec and a body for the package APP_CUSTOM, which contains default behavior for window opening and closing events. 
⦁    Applications Color Palette: The template form contains the application color palette. This gives the forms developed using the template form the look and feel of Oracle applications. 
⦁    Many referenced objects (from the object groups) that support the Calendar, the toolbar, alternative regions, and the menu. These objects include LOVs, blocks, parameters, and property classes, and so on. 
⦁    The TEMPLATE form contains sample objects that can be seen as examples for the expected layout cosmetics. These samples can be completely removed from the form later as they are only examples and are not required. The following objects are the samples and can be removed: 
⦁    Blocks: BLOCKNAME, DETAILBLOCK 
⦁    Window: BLOCKNAME 
⦁    Canvas–view: BLOCKNAME 
Hence, the template form comes along with many attachments, predefined program units, and defined visual attributes as well as examples that not only give the forms that are developed using the template.fmb a standard look and feel, but also make t easier to develop forms with consistent and standard functionality. 
Libraries in the Template form
As stated above, the template form contains platform–independent attachments of several libraries. These libraries are used while running the form as a part of Oracle Applications. Hence, these libraries should not be changed or modified. There are three main libraries that are attached to the template form: 

Figure -2 Template Form attached libraries
APPCORE

APPDAYPK 

FNDSQF 

Each of these libraries is explained in detail below. 
APPCORE
APPCORE contains the packages and procedures that are responsible for the standard ‘Oracle Applications’ behavior of the forms. The forms have to support the menu, toolbar, and other required standard behaviors in compliance with oracle applications. 

The Oracle Applications forms also have to display specific runtime behaviors in accordance with the Oracle Applications User Interface Standards, such as the way in which fields are enabled, behaviors of specific types of windows etc. APPCORE contains the procedures to support this standard runtime behavior. 

APPCORE also contains various other utilities for exception handling, message levels, and so on. 


APPDAYPK
The APPDAYPK library contains the packages that control the Oracle Applications Calendar feature. The calendar (or the date picker) is a utility that oracle apps provide to pick the dates for a date type field. 
FNDSQF
FNDSQF contains packages and procedures for Message Dictionary, flexfields, profiles, and concurrent processing. It also has various other utilities for navigation, multicurrency, WHO, etc. 23–4 Oracle Applications Developer’s Guide Procedures and functions in FNDSQF typically have names beginning with ”FND”.
Other Libraries
The template form also contains a few other libraries that are not linked directly to the template form, but are linked to the three libraries listed above. Although, while using the form it makes no difference whether the library is linked directly to template or to another library that is linked to template. These are discussed below.
CUSTOM library: 
The CUSTOM library (CUSTOM.pll) is probably the most widely used and customized in the libraries attached to the template form. This library allows extension of Oracle Applications forms without modification of Oracle Applications code. 

Any form goes to the CUSTOM.pll whenever any event fires on the form. Code can be written in the CUSTOM.pll with the logic branching based on the form, block and trigger on which you want it to run. 
You can use the CUSTOM library for customizations such as Zoom (such as moving to another form and querying up specific records), enforcing business rules (for example, vendor name must be in uppercase letters), and disabling fields that do not apply for your site.
GLOBE: 
The GLOBE library allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms 

without modification of the base Oracle Applications form. Oracle Applications sends events to the GLOBE library. Regional code can 

take effect based on these events. The GLOBE library calls routines in 

the JA, JE, and JL libraries.
VERT:
The VERT library allows Oracle Applications developers to incorporate vertical industry features (for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modification of the base Oracle Applications form. Oracle Applications sends events to the VERT library. Vertical industry code can take effect based on these events. The VERT library calls routines in various other libraries.
JA
The JA library contains code specific to the Asia/Pacific region and is called by the GLOBE library.
JE
The JE library contains code specific to the EMEA (Europe/MiddleEast/Africa) region and is called by the GLOBE library.
JL
The JL library contains code specific to the Latin America region and is called by the GLOBE library. 

Oracle Forms Interview Questions-1

Q->Triggers fired while closing a form?
 Post-Item, Post-Block, Post-Form

 Q-> How to get the block Name?
 By using SYSTEM.CURRENT_BLOCK

  Q->What are the triggers fired while creating Master Detail form?
 On-Clear Details (Form Level)
 On-Populate-Details (Block Level)
 On-Check-delete-Master (Block Level)
 Pre-Delete

 Q->Difference between Pre-Form and When-New_Form-Instance?
Pre-Form will be fired before entering into the form
When-New-Form-Instance will be fired whenever form is ready to accept the data from the user.

 Q->T=Can we write Commit statement in forms triggers?
Yes

 Q->What is the difference between On-insert and Pre-insert?
Pre-insert Fires during the Post and Commit Transactions process, before a row is inserted.  It fires once for each record that is marked for insert.
On-insert Fires during the Post and Commit Transactions process when a record is inserted.  Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Form Builder would normally insert a record in the database.  It fires once for each row that is marked for insertion into the database.

Q->Difference b/w Pre-Query and Post-Query?
Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record.  Fires once for each record fetched into the block.

 Q->Trigger sequence while opening a form?

Pre-Form,
Pre-Block,
Pre-Record,
Pre-Item,
When-new-form-Instance,
When-new-block-Instance,
When-new-Record-instance,
When-new-Item-instance,

 Q->TDifference b/w Library and Package?


If Master blocks having one Record and Child block having 10 Records then how many times Pre-Query and Post-Query fired?
Post-Query will be fired only once and
Pre-Query will be fired 10 times
     
   Q->T What r the Form statuses?
Form statuses are 3
A) New – All blocks are New
B) Query – All blocks are Query or New
C) Changed – If any of the blocks are changed

Q->When a Form is opened then what is the status of that form?

New

Q-> What is the syntax of RUN_PRODUCT?

Printername: = '"'||'HP LaserJet 4P'||'"';  add_parameter (plid, 'DESNAME', TEXT_PARAMETER, printername);RUN_PRODUCT (REPORTS, rptname, ASYNCHRONOUS, RUNTIME, FILESYSTEM, plid);
Syntax:
RUN_PRODUCT (product   NUMBER, module VARCHAR2, commmode NUMBER, execmode NUMBER, location NUMBER, paramlist_id              VARCHAR2,   display   VARCHAR2);
What is System_in?


Q->What are Canvas, Window and Canvas View?
Canvas is the Back-ground object on which we can place different items.

Window itself is an empty frame providing the window handlers i.e scroll,                                    minimize e.t.c

Canvas View is a rectangular visible area of the canvas through the window.

What are the types of canvases available in forms & what is the default canvas?
Content:-it is the base view of window which occupies the entire surface of window. It can have any no of canvases but at a time only one is visible.

Stacked:-It is always displayed above the content canvas because the content
Canvas is the base view. It can have any no of stacked canvases and more than one stacked canvas can be displayed at a time. It is mainly used as headers and footers to display the tittles, dates and times e.t.c

Tool bar:- A toolbar canvas often is used to create toolbars for individual windows. There are two types of tool bars Horizontal and Vertical Tool bar Canvas. Horizontal tool bar canvases are displayed at the top of window and only one horizontal tool bar can be attached to a form module.Vertical tool bar is used to display top to bottom on the left side of the window.

Tab:-It is a collection of one or more tab pages. It is mainly used to display a large amount of related information a single dynamic form builder canvas object.

Default is Content Canvas

Q-> What is data block & control block?
Data Block:-It is a Logical Collection of Items
Control Block:-It is a data block which is totally independent of Data Base Table or View.
Note: - A data block can have control block items but not vice versa

What is property class, visual attributes? Difference between property class & visual attribute?
Property Class:-It is a global Property sheet where we can define the properties of different objects.
Visual Attributes:-it is used to set the visual appearance of interface objects like items, records and canvases .There are three types of visual attributes
Default:-It is the visual attribute by default attached to the oracle form related objects.
Custom:-When the default VA is amended with few properties.
User Defined: - This can be attached to the objects at design time under VA Node as well as run time programmatically.

Q->Difference between VA and PC

We can change Visual Attribute properties dynamically at runtime,                  but we cannot change Property class properties.

When u inherit the both Visual Attribute properties and Property class        properties to an item Visual Attribute properties overrides the Property class   properties

What is the execution hierarchy of Triggers? Can we change execution hierarchy?
When-new-form-Instance,
When-new-block-Instance,
When-new-Record-instance,
When-new-Item-instance

Q->What is the object group?
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module. Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects

Q->What is Record Group?
It is an internal memory data structure and a separate object in the form module.
Usage:-To provide data to the LOV and dynamically to the list item.
     To perform client level validations
     To reduce the no of i/p or o/ps on the data base.


Q->Types of Record-Groups?
Static Record Group:- A static record group is not associated with a query; instead, you define its structure and row values at design time, and they remain fixed at runtime. Static record groups can be created and modified only at design time.

Query Based Record Group: - A query record group is a record group that has an associated SELECT statement.  The columns in a query record group derive their default names, data types, and lengths from the database columns referenced in the SELECT statement.  The records in a query record group are the rows retrieved by the query associated with that record group.

Non Query Record Group:- A non-query record group is a group that does not have an associated query, but whose structure and values can be modified programmatically at runtime. Non-query record groups can be created and modified only at runtime.

Q->How to change Record Group dynamically?
By Using Non-Query Record Group

Q->What is library? How will you call a form from another form?

A library is a collection of subprograms, including user-named procedures, functions, and packages. We can attach a library to any other form, menu or library module. it is reusable in multiple applications
By using Call_form() runs an indicated form while keeping the parent form active.
Difference between new form, open form & call form?
New_form:-Once we move into the destination automatically source will be closed
Open_form:- It is a two way connection between source and destination. Opens the indicated form.  Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.
Call_form:-Without closing the destination cannot come back to source

Q->How to call a Report from a form?
By using RUN_PRODUCT

What are the types in master details forms?

Isolated:-Two types
ON_CLEAR_DETAILS AND ON_POPULATE_DETAILS
NON ISOLATED:-Three Types
ON_CLEAR_DETAILS, ON_POPULATE_DETAILS AND ON_CHECK_DELETE_MASTER
CASCADING:-
ON_CLEAR_DETAILS, ON_POPULATE_DETAILS AND PRE-DELETE
What are the procedures that will gets created when a master details form created?
CHECK_PACKAGE_FAILURE
CHECK_ALL_MASTER_DETAILS
QUERY_MASTER_DETAILS

Q-> How call a form from another form in apps?
What are the triggers that will fire when the cursor moves from master form to detail forms?
I want to validate a field not to enter wrong data how to that in forms?
What is when validate item trigger?
When from builder open which trigger will fire first and last?
Pre-Form first when form is opened
Post-form last when form is exited

When you close your form window which trigger fire?
Post-form last when form is exited

Oracle Inventory Intransit Value Report

Use the Intransit Value Report to report the value and quantity of items in intransit inventory. These are items that are being transferred between organizations using the intransit transfer method.

Query: 

SELECT ms.from_organization_id, 
ms.from_subinventory, 
ms.to_organization_id, 
ms.to_subinventory, 
ms.quantity,

ms.quantity* mmt.new_cost mtl_cost, ms.quantity*mmt.actual_cost act_cost, 

mmt.transaction_id, 
msi.segment1||'.'||msi.segment2 item_code, 
msi.primary_uom_code, msi.description, 
mmt.new_cost, mmt.actual_cost, mmt.transaction_cost 
FROM mtl_supply ms, mtl_material_transactions mmt, rcv_shipment_lines rsl, mtl_system_items msi

WHERE --MS.to_organization_id = :p_organization_id 
-- AND 

ms.supply_type_code IN ('SHIPMENT', 'RECEIVING') 
AND ms.destination_type_code = 'INVENTORY' 
AND rsl.shipment_line_id = ms.shipment_line_id 
AND mmt.transaction_id(+) = rsl.mmt_transaction_id 
AND ms.from_organization_id = :p_org_id 
AND msi.inventory_item_id = ms.item_id 
AND msi.organization_id = ms.from_organization_id 
AND ( mmt.transaction_action_id = 12 
OR mmt.transaction_action_id = 21)

Oracle Inventory Joining

------Item category Query---------------

SELECT   DISTINCT msib.SEGMENT1 "item_name",
                  ood.ORGANIZATION_NAME,
                  msi.SECONDARY_INVENTORY_NAME,
                  mil.SEGMENT1 || '-' || mil.SEGMENT2 "locator_name",
                  mcb.SEGMENT1 "category",
                  mcst.CATEGORY_SET_NAME
  FROM   mtl_system_items_b msib,
         mtl_secondary_inventories msi,
         org_organization_definitions ood,
         mtl_item_locations mil,
         MTL_ITEM_CATEGORIES mic,
         mtl_categories_b mcb,
         mtl_category_sets_tl mcst
 WHERE       msib.ORGANIZATION_ID = ood.ORGANIZATION_ID
         AND msib.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID
         AND mic.CATEGORY_ID = mcb.CATEGORY_ID
         AND mic.CATEGORY_SET_ID = mcst.CATEGORY_SET_ID
         AND msib.ORGANIZATION_ID = msi.ORGANIZATION_ID
         AND msib.ORGANIZATION_ID = mil.ORGANIZATION_ID
         AND msib.SEGMENT1 LIKE 'CP10ITE%'
         AND msi.SECONDARY_INVENTORY_NAME LIKE 'CP10SUB1'
         


-----------------Item Description--------------         
        
SELECT   ood.organization_name "INVENTORY_ORG_NAME",
         ood.organization_code "INVENTORY_CODE",
         msib.segment1 "ITEM_NAME",
         msib.description "ITEM_DESCRIPTION",
         msib.primary_unit_of_measure,
         msib.inventory_item_status_code "ITEM_STATUS",
         msib.inventory_item_flag "INVENTORY_ITEM_CHECK_BOX",
         msib.segment1 "INVENTORY_NAME",
         msib.mtl_transactions_enabled_flag "TRANSACTION_ITEM",
         msib.purchasing_item_flag "PURCHASING_ITEM",
         msib.expense_account "PURCHASING_ITEM_EXPENSE_ACC",
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '_'
         || gcc.segment4
            "ACCOUNT_N0"
  FROM   mtl_system_items_b MSIB,
         org_organization_definitions ood,
         gl_code_combinations gcc
 WHERE       msib.organization_id = ood.organization_id
         AND gcc.code_combination_id = msib.expense_account
         AND msib.segment1 like 'KITTU_ITEM'
         AND ood.organization_name like 'Vision Operations'
         
----------------------Item Transactions--------

/* Formatted on 2/13/2015 3:47:50 PM (QP5 v5.115.810.9015) */
SELECT   mmt.transaction_date,
         mtt.transaction_type_name,
         mmt.subinventory_code,
         mmt.transaction_quantity,
         mmt.transaction_uom,
         gcck.concatenated_segments
  FROM   mtl_material_transactions mmt,
         mtl_system_items_b msib,
         mtl_transaction_types mtT,
         gl_code_combinations_kfv gcck
 WHERE       msib.inventory_item_id = mmt.inventory_item_id
         AND mmt.distribution_account_id = gcck.code_combination_id
         AND mmt.transaction_type_id = mtt.transaction_type_id
         AND msib.segment1 LIKE 'XYX'
         AND mmt.transaction_date BETWEEN TO_DATE ('10-12-2010',
                                                   'dd-mm-yyyy')
                                      AND  TO_DATE (,(SYSDATE),
                                                    'dd-mm-yyyy')
         AND mtt.transaction_type_name LIKE 'Miscellaneous issue'
         
         
----------------------------------------------------------
-----------------onhand quantities-------
/* Formatted on 2/13/2015 3:51:38 PM (QP5 v5.115.810.9015) */
SELECT   ood.organization_name "INVENTORY_ORG_NAME",
         ood.organization_code "INVENTORY_CODE",
         msib.segment1 "ITEM_NAME",
         msib.description "ITEM_DESCRIPTION",
         msib.primary_unit_of_measure,
         msib.inventory_item_status_code "ITEM_STATUS",
         msib.inventory_item_flag "INVENTORY_ITEM_CHECK_BOX",
         msib.segment1 "INVENTORY_NAME",
         msib.mtl_transactions_enabled_flag "TRANSACTION_ITEM",
         msib.purchasing_item_flag "PURCHASING_ITEM",
         msib.expense_account "PURCHASING_ITEM_EXPENSE_ACC",
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '_'
         || gcc.segment4
            "ACCOUNT_N0"
  FROM   mtl_system_items_b MSIB,
         org_organization_definitions ood,
         gl_code_combinations gcc
 WHERE       msib.organization_id = ood.organization_id
         AND gcc.code_combination_id = msib.expense_account
         AND msib.segment1 like 'xyz'
         AND ood.organization_name LIKE 'Vision Operations'        

Oracle Inventory Interview Questions and Answers-4

Oracle Inventory:

What is a category and category set?
A category is a logical classification of items that
have similar characteristics. A category set is a distinct grouping scheme and consists of categories.

How many structures can an item category flexfield have?
You can define multiple segment structures for the Item Categories Flexfield. Each segment structure may have its own display prompts and fields.

Category Set:

What are the two-category flexfield structures by default
When you install or upgrade Oracle Inventory or Oracle Purchasing?
Oracle provides two category flexfield structures by default: Item Categories and PO Item Category.

A category can belong to multiple category sets. You can assign a category to a category set either at the time you define a category set or at the time you assign an item to the category.

Attention: The categories assigned to a category set must have the same flexfield structure as the set itself. This is true even if you choose not to validate the category list.

Category sets: You can use categories and category sets to group items for various reports and programs.

Note: The category set Inventory is seeded when you install Oracle Inventory. The category set Purchasing is seeded when you install Oracle Purchasing.

Why do we need default category set?
Default category sets are required so that each functional
area has at least one category set that contains all items in that functional area.

If a functional area’s item defining attribute is controlled at the Organization level, then that functional area may only have an Organization level default category set.

What is the prerequisite for assigning item to categories?
You must define default category sets for your functional areas.

What is the need of item cataloging?
You can use item cataloging to add descriptive information to items and to partition your Item Master into groups of items that share common characteristics.

When you assign an item to a catalog group, you choose descriptive elements that apply to the item, and assign values to the descriptive elements.

What is concatenated (connected) Item Descriptions?
If you choose Update Description, Oracle Inventory concatenates(connects) the item catalog group information and overwrites the item description with this new information.

How can we import the items?
We can import items using the Open Item Interface and  
the Customer Item Interface.

How do you define an item by copying attribute information from an existing item?
 From the Master Item window choose Copy From on the Tools
menu to open the Copy From window.

NOTE: The following are not copied: 
Category relationships, organization assignments,
Catalog group information, and costs are not copied.

Note: Use the Organization Items window to update Organization level item attributes for your current organization. To update Master level attributes use the Master Items window.

Oracle Inventory Item Attributes
Item Attributes Listed by Group Name

Main: 
•Primary Unit of Measure
•User Item Type
•Item Status
•Conversions.
Inventory: 
•Inventory Item
•Stockable
•Transactable
•Revision Control
•Reservable

Bills of Material
•BOM Allowed
•BOM Item Type
•Base Model
•Engineering Item (Oracle Engineering only)

Costing: 
•Costing Enabled
•Inventory Asset Value
•Include in Rollup
•Cost of Goods Sold Account

Purchasing: 
•Purchased
•Purchasable
•Use Approved Supplier
•Allow Description Update
•RFQ Required

Receiving: 
•Receipt Date Action
•Receipt Days Early
•Receipt Days Late
•Allow Substitute Receipts
•Allow Unordered Receipts
•Allow Express Transactions

Physical Attributes: 
•Weight Unit of Measure
•Unit Weight
•Volume Unit of Measure
•Unit Volume
•Container
Maximum Load Weight
•Minimum Fill Percentage

General Planning: 
•Inventory Planning Method
•Planner
•Make or Buy
•Min–Max Minimum Quantity
•Min–Max Maximum Quantity
•Minimum Order Quantity
•Maximum Order Quantity

MPS/MRP Planning
•Planning Method
•Forecast Control
•Exception Set
•Pegging

Lead Times: 
•Preprocessing
•Processing
•Postprocessing
•Fixed
•Variable

Work in Process: 
•Build in WIP
•Supply Type
•Supply Sub inventory
•Supply Locator

Order Management: 
•Customer Ordered
•Customer Orders Enabled
•Shippable
•Internal Ordered
•Internal Orders Enabled

Invoicing: 
•Invoiceable Item
•Invoice Enable
•Accounting Rule
•Invoicing Rule



Service: 
•Support Service
•Warranty
•Coverage
•Service Duration
•Billing Type

Primary Unit of Measure
This is the stocking and selling unit of measure. Any necessary
conversions are based on this unit of measure. This attribute is not updatable.

The primary unit of measure is the default for invoices and credit memos entered in Oracle Receivables.

User Item Type
Oracle provides several types by default at installation. These types correspond to the item templates also provided.

ATO model
•Finished good
•Freight
•Inventory Type

Revision Control
Indicate whether to track inventory balances by revision. If you turn this option on you must specify an existing revision number for issues and receipts]

Attention: You cannot change revision control when an item has
quantity on hand. If Revision Control is controlled at the Master Item level, the check for on–hand quantity is against the sum of on–hand quantities in all child organizations.

Note: Reservation control for a subinventory overrides reservation control for an item. In other words, if an item is reservable but a subinventory is not, the item quantity in that subinventory is not reservable.