Key Tables in Oracle Projects

Table	Description
PA_PROJECTS_ALL	It stores the highest units of work defined in Oracle Projects.
PA_PROJECT_ASSETS_ALL	It contains assets information defined for capital projects.
PA_PROJECT_ASSIGNMENTS	It stores details of all Assignments for a project.
PA_PROJECT_CLASSES	It contains the class codes of class categories that are used to classify projects.
PA_PROJECT_ROLE_TYPES	Implementation-defined responsibilities or positions assigned to employees on projects are stored here.
PA_PROJECT_STATUSES	It stores valid project status codes.
PA_PROJECT_TYPES_ALL	It stores implementation-defined project classifications that supply default information and drive some project processing.
PA_TASKS	It contains user-defined subdivisions of project work.
PA_TASK_TYPES	It stores implementation-defined classifications of task.
PA_TRANSACTION_INTERFACE_ALL	It is an interface table to import transactions from external sources into Oracle Projects.
PA_TRANSACTION_SOURCES	It stores implementation-defined sources of imported transactions originating in an external system.
PA_IMPLEMENTATIONS_ALL	It contains information about the configuration of an Oracle Projects installation.
PA_ACTION_SETS	It stores action set templates as well as action sets belonging to an object, such as projects, requirements, etc.
PA_ACTION_SET_LINES	It stores action set lines that belong to an action set or an action set template.
PA_ACTION_SET_TYPES	It stores attributes of action set types.
PA_AGREEMENTS_ALL	It has customer contracts that serve as the basis for work authorization.
PA_AGREEMENT_TYPES	Implementation-defined classifications of customer agreements.
PA_BILL_RATES_ALL	Information about bill rates and markups of standard bill rate schedules.
PA_BUDGETS	It stores budgets information.
PA_BUDGET_LINES	It stores detail lines of project and task budgets.
PA_BUDGET_TYPES	It contains implementation-defined classifications of types of budgets used for different business purposes.
PA_CLASS_CATEGORIES	It stores implementation-defined categories for classifying projects.
PA_CLASS_CODES	It stores implementation-defined values within class categories that can be used to classify projects.
PA_EVENTS	It stores entries assigned to tasks that generate revenue and/or billing but are not directly related to expenditure items.
PA_EVENT_TYPES	It stores implementation-defined classifications of events.
PA_EXPENDITURES_ALL	Groups of expenditure items incurred by employees or organizations for an expenditure period.
PA_EXPENDITURE_CATEGORIES	Implementation-defined groupings of expenditure types by type of cost.
PA_EXPENDITURE_ITEMS_ALL	It contains the smallest units of expenditure charged to projects and tasks.
PA_EXPENDITURE_TYPES	Implementation-defined classifications of expenditures charged to projects and tasks.
PA_PERIODS_ALL	Implementation-defined periods against which project performance is measured.
PA_RBS_DENORM	This table stores normalized resource breakdown structure information.
PA_RBS_ELEMENTS	This table stores the RBS element information and the parent-child relationship.
PA_RESOURCES	It contains resources used in budgeting and project summary amounts.
PA_ROLE_LISTS	It stores lists of roles defined with the system.
PA_SCHEDULES	It displays the schedule details for requirements and assignments. It also displays calendar schedules.

Important questions to be remembered by a Oracle Consultant

What is Set of Books ? What are the four conditions when you change your SOBs?

Chart of Accounts, Currency & Calendar
It is similar to the bank passbook, used to record all the financial transactions. There could be one primary set of books and many reporting set of books. Defining COA, currency and calendar are pre-requisite to define the SOB.

What is an Invoice? How many types of invoices AR...
There are 9 types of Invoices in AP: Standard, Credit Memo, Debit Memo, Prepayment, Withholding Tax, Mixed Invoice, Expense Report, PO default and Quick Match.
There are 6 types of Invoice in AR : Invoice, Credit Memo, Debit Memo, Chargeback, Deposit and Guarantee.

What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

Set ups need to approve invoice in AP
For approving an invoice in AP we have to raise one invoice for raising an invoice we have some mandatory fields like supplier, supplier number, Payment terms, distribution sets, date, payment method, bank, payment document etc. After defining all the above we can raise a invoice, There's no need of defining the approval group it s not a mandatory,

What is a profile option? What are the types?
The profile options are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile's setting.
1) User Level
2) Responsibility Level
3) Application Level
4) Site Level.

What are _ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.

What is descriptive flex field and what is the use
Descriptive Flexfileds r used to add additional information's, and these values r stored to the column attributes. Go to sysadmin application-flex fields-descriptive-segments.

What is a FlexField? What are Descriptive and Key...
A flexfield is made up of sub-fields or segments.. A flexfield appers on ur form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values..
Two types of Flex field..
Key Flexfield: Key flexfield are flexible enough to let any organization use the code scheme they want without programming.Key flexfield can be used to represent codes that is made up of meaningful segment to identify GL a/c Part no. and other business entities..Oracle app store these codes in key flexfields..
Descriptive Flexfield: They provide customization "expansion space" on ur forms. You can use desc flexfields to tract additional information important and unique to ur business that would not otherwise be captured by the form.

What Credit memo / Debit Memo
Both Credit and Debit Memo are used for adjusting the suppliers balance and both is a negative amount. Debit Memo is created by you and send to the supplier and credit memo is received from the supplier and record it.

What is a request Sets? how to create a request Se..
Request set is a group of requests.It is made to perform the request in a certain sequence.Request se can be created from System administrator responsibility.

What are the types of Concurrent Managers?Ca...
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatibility checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

What is Multiple Organization technical architecture?
Multi Organization :Using a single installation of any oracle application product , to support any number of an organization even it has different Set Of Books Business Level(It Secures Human Transaction Information) Set Of Books(It secures all Transaction Information In Oracle General Ledger) Legal Entry(All legal Information in the Organization) Operating Unit(It Uses Order management, Cash management,AR,AP...it may be Sales Office , Division,Department) Inventory Organization(Inventory Details)
before multi org existed, we could have only one business group,set of books, legal entity, operating unit in one installation of oracle E-business suite, and now with the multi organisation structure in place, we can have multiple business groups, set of books, legal entity and operating unit and the best part is once these are set up inter company accounting is automatically taken care of, say for example i have Two inventory organisation, and these use a common set of books(to start with) , now if we have sales order on one inventory organisation A1 and if that item is not available in A1, and we have inventory for the item in Inventory org A2, we need to take the order in A1 as internal order and run the order import concurrent program and for the item in inventory A1(org assignment) we need to set up the source(purchase tab) as inventory organisation A2,and also specify shipping network between A1 and A2, and once we have run the order import program and Oracle applications now imports the order to organisation A2, with the ship to address as A1 location. and after you perform pick release and pick confirm process, and run auto invoice in A2, it automatically sends invoice to inventory organisation A1 and now we can receive the item in Inventory organisation A1 from organisation A2.
and all transactions are taken care of, courtesy multi organisation structure.

What are different period types ?
Year Quarter Month Week

What are the different types of files used in SQL
Different types of files are Data File,Control File,Discard File,Bad file

What is Oracle Financials?
Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:
o Oracle Assets: Ensures that an organization's property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.
o Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.
o Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.
o Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.
o Oracle Payable's  Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.
o Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.
o Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.
o Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.
o Oracle Revenue Accounting: Gives an organization timely and accurate revenue and flexible commissions reporting.
o Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What is the difference between Fields and FlexFields?
A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.

Difference between Conversions and Interfaces.
Conversion is to bring the data from other (non-OraApps) system to Oracle-Application system. This is one time activity (bring account details, transactions, orders, receipts and so on). So conversion itself is a project.
Interfaces are Concurrent Program's/Program sets (pl/sql, pro*C, unix scripts, executable's) . Interfaces are basically of two types (Oracle supplied (vanilla programs eg: Auto invoice in AR) and created by developer).
Hope this clarifies the concepts.
Conversion means one time activity interface means periodic activity example:- to transfer the data old version to new version it is called conversion to transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ........

Which module is not a multiorg? 
General Ledger and CRM Foundation Modules

What are the types of Concurrent Managers?Can we delete a Concurrent Manager?
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

Trading community Architecture(TCA)
ICA (Internet Computing Architecture)

What are AP setup steps ?
setup---->suppliers....>invoices..>payments.....>reports......>periods.....>transfer to GL

What does US mean in appl_top/au/11.5.0/reports/US?
US is the language directory specifying that the source files to be placed under this dir is for English/American Language
This is the standard of apps directory structure that for very language you implement oracle apps there should be a language specific folder

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers? 
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What are the Back ground processes in Oracle and what are they.
There are basically 9 Processes.They do the house keeping activities for the Oracle and are common in any system.The various background processes in oracle are:
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is committed.
b)LogWriter(LGWR) :: Log Writer writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance start up. This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor performs process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the
most recent checkpoint
f)Archieves(ARCH) :: The Archives copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have up to 10 lock processes for inter instance locking in parallel sql.

How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are
a) Data Definition Language(DDL) :: The DDL statements define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statements manipulate database data.
c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g, Alter Statements,Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g, Alter System
f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programming Language.e.g, Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly committed or rolled back.

Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Committing :: A transaction is said to be committed when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) Save Point :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Save points can be used to divide a transaction into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific statement. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Database Buffer of SGA stores the most recently used blocks of database data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.

What are Procedure,functions and Packages
* Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
* Procedures do not Return values while Functions return one One Value
*Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g:: operations insert,update ,delete 3
before ,after 3*2 A total of 6 combinations
At statement level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 On wards.
Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is existing.
c) Cascading :: The child gets deleted when the Master is deleted.

What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

What are the Different Optimization Techniques
The Various Optimization techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenever they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

Restrict '&' in Sql*Plus
sql> set define off
This will restrict the use of '&' in the sql*plus for that particular session

RICE components

Mostly an oracle apps technical consultant will be working on RICE components.
R--Reports
I--Interfaces
C--Conversions
E--Extensions(Forms personalization)

Reports:
For suppose I'm running a business named 'ALAM' which is wide spread across the globe.Now,I want to know how my business is running across the globe.I will ask a tech guy(oracle apps technical consultant) to develop a report.In that report I ask him to simply print the details of profit or loss,of every branch of my business across the world.He(tech guy) will come up with the report to me then I will come to know whether my company is running fine or not.Thereby, I can take necessary decisions to run my business fine.
Clearly we have seen that report will be developed by technical person,who must know the report building.
We build a report using sql,pl/sql.So,if your are strong in sql,pl/sql then its not a big deal to learn the report building.Its enough to learn the report developer tool.

Conversion:
As I said earlier I'm running a 'ALAM' business(which is factious).Suppose I'm using excel sheets to store the data.Now I want to install oracle apps(E-Business Suite) in my company. So,What ever data that is present in my excel sheets must also be present in my oracle apps tables(A table is a collection of rows and columns).Now I ask a tech guy to write a code such that my excel sheet data will get into oracle apps base tables.Then that tech guy will use 'conversion' to get the data from the legacy system(in our case it is excel sheet) into oracle apps base tables.

Interfaces:
Interfaces are similar to conversions.Conversion is a one-time process where as Interface is on-going process every now and then.We run interfaces daily or periodically.
Interfaces are of two types
1)Inbound Interface
2)Outbound Interface
Inbound Interface:Transferring the data from the legacy system(in our case it is excel sheet) into the Oracle apps base tables.
Outbound Interface:Transferring the data from the Oracle apps base tables into the legacy system(It might be any of these SAP,People soft etc).

Extensions:
Extensions are nothing but personalizing the forms.In oracle e-business suite we have some where around 5000 to 6000 forms.In my 'ALAM' business i want to customise the po(purchase order) form of the oracle apps then i ask the tech guy to do that.He will use form builder tool to do that.

SQL Query to Link GL Journal and SLA Tables for 'Misc Receipts','Receipts' and 'Reconciled Payments'

SELECT  ada.line_id, jh.je_category Category,
  jh.period_name Period_Name,
  NULL Invoice_No_or_Memo_No,
  nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0) Amount_journal,
  NVL(xal.entered_dr,0)-NVL(xal.entered_cr,0) Amount_Xla,
  NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0) Amount_Receivables,
  DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD') Currency_Code,
   xdl.source_distribution_type,
  jh.ledger_id Book
FROM gl_je_headers jh,
  gl_je_lines jl,
  gl_code_combinations gcc,
  gl_import_references gir,
  xla_ae_lines xal,
  XLA_DISTRIBUTION_LINKS xdl,
  xla_ae_headers xah,
  AR_DISTRIBUTIONS_ALL ada
WHERE 1                    =1
AND jh.je_header_id        =jl.je_header_id
AND jl.code_combination_id =gcc.code_combination_id
AND gir.je_header_id       =jh.je_header_id
AND gir.je_line_num        =jl.je_line_num
AND gir.gl_sl_link_id      =xal.gl_sl_link_id
and xah.ae_header_id=xal.ae_header_id
and xal.application_id = xdl.application_id
and xah.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
and xdl.source_distribution_id_num_1 = ada.line_id
AND gcc.code_combination_id=46032
and jh.period_name like '13-Jun'
and jh.je_category in ('Misc Receipts','Receipts')
and abs(nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))<> abs(NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0))
and abs(NVL(xal.entered_dr,0)-NVL(xal.entered_cr,0)) <> abs(NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0))
and jh.ledger_id = '2029';



SELECT  jh.name,jh.je_category Category,
  jh.period_name Period_Name,
  NULL Invoice_No_or_Memo_No,
  abs(nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0)) Amount_journal,
  abs (NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0)) Amount_Xla,
  abs(sum(aphd.amount)) amount_payment,
    DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD') Currency_Code,
   xdl.source_distribution_type,
  jh.ledger_id Book
FROM gl_je_headers jh,
  gl_je_lines jl,
  gl_code_combinations gcc,
  gl_import_references gir,
  xla_ae_lines xal,
  XLA_DISTRIBUTION_LINKS xdl,
  xla_ae_headers xah,
  AP_PAYMENT_HIST_DISTS aphd
WHERE 1                    =1
AND jh.je_header_id        =jl.je_header_id
AND jl.code_combination_id =gcc.code_combination_id
AND gir.je_header_id       =jh.je_header_id
AND gir.je_line_num        =jl.je_line_num
AND gir.gl_sl_link_id      =xal.gl_sl_link_id
and xah.ae_header_id=xal.ae_header_id
and xal.application_id = xdl.application_id
and xah.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
AND gcc.code_combination_id=46032
and xdl.source_distribution_type = 'AP_PMT_DIST'
and xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
and jh.je_category in ('Reconciled Payments')
and jh.ledger_id ='2029'
and jh.period_name like '13-Jun'
--and abs (nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))  <> abs(sum(aphd.amount) )
--and NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0) <> abs(sum(aphd.amount)),
 group by  jh.name,jh.je_category,
  jh.period_name,
   nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0),
  NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0),
     DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD'),
   xdl.source_distribution_type,
  jh.ledger_id
  having --abs (nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))  <> abs(sum(aphd.amount) );
abs( NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0)) <> abs(sum(aphd.amount));

Oracle Applications Idle Session Timeout

What is Session Idle time?

If Oracle Apps client is idle for some time (Eg. Application user goes for a coffee break) session during that time is called as Idle Session & because of security reason, performance issues and to free up system resource Oracle Applications terminates client session (both forms & self service) after idle time value is reached to the one mentioned in configuration file.

To enter into application, profile option "ICX Session Timeout" is used.

ICX Session Time out mentioned in profile option ICX: Session Timeout is in minutes, so ICX session timeout=30

From where ICX: Session Timeout & session.timeout get values?

Autoconfig determines value for profile option "ICX: Session Timeout" and "session.timeout" from entry in context file ($APPL_TOP/admin/SID_hostname.xml) with parameter s_sesstimeout where value mentioned is in milliseconds so profile option ICX: Session Timeout value should be s_sesstimeout/ (1000 * 60) which means here its 10 Minutes. This value is also set in zone.properties in $IAS_ORACLE_HOME/Apache/Jserv where number mentioned is in milli second i.e. 600000 (equal to 10 Minutes) session.timeout = 600000.

Eg.  Session Timeout is in minutes, so ICX session timeout=480 is 8Hrs. 

Query to find an PO details :

execute fnd_client_info.set_org_context('204');

SELECT   DECODE (por.release_num,
                   NULL, poh.segment1,
                   poh.segment1 || '-' || por.release_num)
              PO_Number_Release,
           pol.line_num Line,
           pov.vendor_name Vendor,
           pol.item_revision Rev,
           pol.item_description Description,
           pll.shipment_num,
           pod.distribution_num Distribution,
           DECODE (plt.order_type_lookup_code,
                   'AMOUNT', NULL,
                   pll.price_override)
              Unit_Price,
           pll.promised_date Promised_Date,
           pol.unit_meas_lookup_code Unit,
           DECODE (POL.order_type_lookup_code,
                   'RATE', POD.amount_ordered,
                   'FIXED PRICE', POD.amount_ordered,
                   POD.quantity_ordered)
              Quantity_Amount_Ordered,
           DECODE (POL.order_type_lookup_code,
                   'RATE', POD.amount_billed,
                   'FIXED PRICE', POD.amount_billed,
                   POD.quantity_billed)
              Quantity_Amount_Billed,
           DECODE (POL.order_type_lookup_code,
                   'RATE', POD.amount_delivered,
                   'FIXED PRICE', POD.amount_delivered,
                   POD.quantity_delivered)
              Qty_Amount_Delivered,
           DECODE (
              POL.order_type_lookup_code,
              'RATE',
              (NVL (POD.amount_ordered, 0) - NVL (POD.amount_billed, 0))
              / DECODE (NVL (POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
              'FIXED PRICE',
              (NVL (POD.amount_ordered, 0) - NVL (POD.amount_billed, 0))
              / DECODE (NVL (POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
              (NVL (POD.quantity_ordered, 0) - NVL (POD.quantity_billed, 0))
              / DECODE (NVL (POD.quantity_ordered, 0),
                        0, 1,
                        POD.quantity_ordered)
           )
           * 100
              Percent_Unbilled,
           DECODE (
              POL.order_type_lookup_code,
              'RATE',
                POD.amount_ordered
              - NVL (POD.amount_cancelled, 0)
              - NVL (POD.amount_billed, 0),
              'FIXED PRICE',
                POD.amount_ordered
              - NVL (POD.amount_cancelled, 0)
              - NVL (POD.amount_billed, 0),
              (  POD.quantity_ordered
               - NVL (POD.quantity_cancelled, 0)
               - NVL (POD.quantity_billed, 0))
              * PLL.price_override
           )
              C_AMOUNT_OPEN_INV,
           poh.po_header_id,
           pol.po_line_id,
           por.release_num,
           poh.currency_code C_CURRENCY,
           NVL (por.po_release_id, -1) release_id
    FROM   po_distributions pod,
           mtl_system_items msi,
           po_line_locations pll,
           po_lines pol,
           po_releases por,
           po_headers poh,
           po_vendors pov,
           financials_system_parameters fsp,
           po_line_types plt
   WHERE       poh.segment1 = '804'
           AND poh.po_header_id = pol.po_header_id
           AND pol.po_line_id = pll.po_line_id
           AND pll.line_location_id = pod.line_location_id
           AND pol.item_id = msi.inventory_item_id(+)
           AND msi.organization_id = fsp.inventory_organization_id
           AND poh.vendor_id = pov.vendor_id(+)
           AND pll.po_release_id = por.po_release_id(+)
           AND pol.line_type_id = plt.line_type_id
           AND pll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
           AND NVL (pol.closed_code, 'OPEN') NOT IN
                    ('CLOSED', 'FINALLY CLOSED')
           AND NVL (pll.closed_code, 'OPEN') NOT IN
                    ('CLOSED', 'FINALLY CLOSED')
           AND NVL (poh.closed_code, 'OPEN') NOT IN
                    ('CLOSED', 'FINALLY CLOSED')
           AND NVL (por.closed_code, 'OPEN') NOT IN
                    ('CLOSED', 'FINALLY CLOSED')
           AND NVL (poh.cancel_flag, 'N') = 'N'
           AND NVL (por.cancel_flag, 'N') = 'N'
           AND NVL (pol.cancel_flag, 'N') = 'N'
           AND NVL (pll.cancel_flag, 'N') = 'N'
ORDER BY   pll.line_location_id
/

Query to find receipts against a PO shipment line :

/* Formatted on 1/13/2015 2:24:43 PM (QP5 v5.115.810.9015) */
SELECT   pol.po_header_id,
           pol.po_line_id,
           pll.line_location_id,
           pll.quantity,
           rsh.shipment_header_id,
           rsh.receipt_source_code,
           rsh.vendor_id,
           rsh.vendor_site_id,
           rsh.organization_id,
           rsh.shipment_num,
           rsh.receipt_num,
           rsh.ship_to_location_id,
           rsh.bill_of_lading,
           rsl.shipment_line_id,
           rsl.QUANTITY_SHIPPED,
           rsl.QUANTITY_RECEIVED,
           rct.transaction_type,
           rct.transaction_id,
           DECODE (pol.order_type_lookup_code,
                   'RATE', NVL (rct.amount, 0),
                   'FIXED PRICE', NVL (rct.amount, 0),
                   NVL (rct.source_doc_quantity, 0))
              transaction_qty
    FROM   rcv_transactions rct,
           rcv_shipment_headers rsh,
           rcv_shipment_lines rsl,
           po_lines pol,
           po_line_locations pll
   WHERE       rct.po_line_location_id = 28302
           AND rct.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
                    ('RATE', 'FIXED PRICE')
           AND rct.shipment_line_id = rsl.shipment_line_id
           AND rsl.shipment_header_id = rsh.shipment_header_id
ORDER BY   rct.transaction_id
/

Query to find PO returns :

/* Formatted on 1/13/2015 2:24:58 PM (QP5 v5.115.810.9015) */
SELECT   pol.po_header_id,
           pol.po_line_id,
           rct.po_line_location_id Line_location_id,
           SUM ( (NVL (rct.source_doc_quantity, 0))) Qty_returned
    FROM   rcv_transactions rct, po_lines pol, po_line_locations pll
   WHERE       rct.transaction_type = 'RETURN TO VENDOR'
           AND rct.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
                    ('RATE', 'FIXED PRICE')
GROUP BY   pol.po_header_id, pol.po_line_id, rct.po_line_location_id
UNION ALL
  SELECT   pol.po_header_id,
           pol.po_line_id,
           rct.po_line_location_id Line_location_id,
           SUM ( (NVL (rct.amount, 0))) Qty_returned
    FROM   rcv_transactions rct, po_lines pol, po_line_locations pll
   WHERE       rct.transaction_type = 'RETURN TO VENDOR'
           AND rct.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN
                    ('RATE', 'FIXED PRICE')
GROUP BY   pol.po_header_id, pol.po_line_id, rct.po_line_location_id
/

Query to find PO corrections :

/* Formatted on 1/13/2015 2:26:28 PM (QP5 v5.115.810.9015) */
SELECT   pol.po_header_id,
           pol.po_line_id,
           rct.po_line_location_id Line_location_id,
           SUM (NVL (rct1.source_doc_quantity, 0)) Qty_corrected
    FROM   rcv_transactions rct,
           rcv_transactions rct1,
           po_lines pol,
           po_line_locations pll
   WHERE       rct.transaction_type IN ('RECEIVE', 'MATCH')
           AND rct.po_line_location_id = pll.line_location_id
           AND rct1.transaction_type = 'CORRECT'
           AND rct1.parent_transaction_id = rct.transaction_id
           AND rct1.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
                    ('RATE', 'FIXED PRICE')
GROUP BY   pol.po_header_id, pol.po_line_id, rct.po_line_location_id
UNION ALL
  SELECT   pol.po_header_id,
           pol.po_line_id,
           rct.po_line_location_id Line_location_id,
           SUM (NVL (rct1.amount, 0)) Qty_corrected
    FROM   rcv_transactions rct,
           rcv_transactions rct1,
           po_lines pol,
           po_line_locations pll
   WHERE       rct.transaction_type IN ('RECEIVE', 'MATCH')
           AND rct.po_line_location_id = pll.line_location_id
           AND rct1.transaction_type = 'CORRECT'
           AND rct1.parent_transaction_id = rct.transaction_id
           AND rct1.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN
                    ('RATE', 'FIXED PRICE')
GROUP BY   pol.po_header_id, pol.po_line_id, rct.po_line_location_id

Query to find delivery leg and pick up stop info :

SELECT   wt.trip_id,
         wt.name,
         wt.STATUS_CODE,
         wt.VEHICLE_ITEM_ID,
         wt.VEHICLE_NUMBER,
         wt.CARRIER_ID,
         wt.SHIP_METHOD_CODE,
         wts.STOP_ID,
         wts.STOP_LOCATION_ID,
         wts.STATUS_CODE,
         wts.STOP_SEQUENCE_NUMBER,
         wts.PLANNED_ARRIVAL_DATE,
         wts.PLANNED_DEPARTURE_DATE,
         wts.ACTUAL_ARRIVAL_DATE,
         wts.ACTUAL_DEPARTURE_DATE,
         wts.DEPARTURE_NET_WEIGHT,
         wts.WEIGHT_UOM_CODE,
         wdl.DELIVERY_LEG_ID,
         wdl.DELIVERY_ID,
         wdl.PICK_UP_STOP_ID,
         wdl.DROP_OFF_STOP_ID,
         wdl.SEQUENCE_NUMBER,
         wdl.LOADING_ORDER_FLAG,
         wdl.SHIPPER_TITLE,
         wdl.SHIPPER_PHONE
  FROM   wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl
 WHERE       wdl.delivery_id = 12814
         AND wts.stop_id = wdl.pick_up_stop_id
         AND wts.trip_id = wt.trip_id;

Query to find Bill of Lading info of the Delivery :

SELECT   wnd.delivery_id delivery_id,
         wdi.sequence_number bol_number,
         wdi.bol_notify_party,
         wdi.port_of_loading,
         wdi.port_of_discharge,
         wnd.WAYBILL waybill,
         wnd.GROSS_WEIGHT gross_weight,
         wnd.WEIGHT_UOM_CODE uom,
         wnd.status_code
  FROM   wsh_new_deliveries wnd,
         wsh_delivery_legs wdl,
         wsh_document_instances wdi
 WHERE       wnd.delivery_id = 12784
         AND wnd.delivery_id = wdl.delivery_id(+)
         AND wdi.entity_id(+) = wdl.delivery_leg_id
         AND wdi.entity_name(+) = 'WSH_DELIVERY_LEGS'
         AND wdi.document_type(+) = 'BOL'
         AND wdi.status(+) <> 'CANCELLED'
/

Query to find out Move order line details :

SELECT   wnd.delivery_id,
         wnd.name delivery_name,
         wnd.initial_pickup_location_id,
         mtrh.request_number mo_number,
         mtrl.line_number mo_line_number,
         mtrl.line_id mo_line_id,
         mtrl.from_subinventory_code,
         mtrl.to_subinventory_code,
         mtrl.lot_number,
         mtrl.serial_number_start,
         mtrl.serial_number_end,
         mtrl.uom_code,
         mtrl.quantity,
         mtrl.quantity_delivered,
         mtrl.quantity_detailed,
         wdd.source_header_number so_order_number,
         oola.line_number so_line_number,
         wdd.source_header_id so_header_id,
         wdd.source_line_id so_line_id,
         wdd.shipping_instructions,
         wdd.inventory_item_id,
         wdd.requested_quantity_uom,
         msi.description item_description,
         msi.revision_qty_control_code,
         wdd.ship_method_code carrier,
         wdd.shipment_priority_code priority,
         wdd.organization_id,
         wdd.released_status,
         wdd.source_code
  FROM   mtl_system_items_vl msi,
         oe_order_lines_all oola,
         mtl_txn_request_lines mtrl,
         mtl_txn_request_headers mtrh,
         wsh_delivery_details wdd,
         wsh_delivery_assignments wda,
         wsh_new_deliveries wnd
 WHERE       wnd.delivery_id = 18910
         AND wda.delivery_id = wnd.delivery_id(+)
         AND wdd.delivery_detail_id = wda.delivery_detail_id
         AND wdd.move_order_line_id = mtrl.line_id
         AND mtrl.header_id = mtrh.header_id
         AND wdd.inventory_item_id = msi.inventory_item_id(+)
         AND wdd.organization_id = msi.organization_id(+)
         AND wdd.source_line_id = oola.line_id
         AND wdd.source_header_id = oola.header_id
/

Query to find price discounts and surcharges on order lines :

SELECT   h.order_number,
           l.line_number,
           pa.list_line_type_code,
           pa.arithmetic_operator,
           pa.operand,
           DECODE (
              PA.MODIFIER_LEVEL_CODE,
              'ORDER',
                L.UNIT_LIST_PRICE
              * L.ORDERED_QUANTITY
              * PA.OPERAND
              * SIGN (PA.ADJUSTED_AMOUNT)
              / 100,
              (PA.ADJUSTED_AMOUNT * NVL (L.ORDERED_QUANTITY, 0))
           )
              DISCOUNT_AMT
    FROM   qp_list_headers_vl lh,
           oe_price_adjustments pa,
           oe_order_lines_all l,
           oe_order_headers_all h
   WHERE       h.order_number = 14463
           AND h.header_id = l.header_id
           AND h.org_id = l.org_id
           AND h.header_id = pa.header_id
           AND l.line_id = pa.line_id(+)
           AND pa.list_header_id = lh.list_header_id
           AND (   PA.LIST_LINE_TYPE_CODE = 'DIS'
                OR PA.LIST_LINE_TYPE_CODE = 'SUR'
                OR PA.LIST_LINE_TYPE_CODE = 'PBH')
           AND PA.APPLIED_FLAG = 'Y'
           AND NOT EXISTS
                 (SELECT   'X'
                    FROM   OE_PRICE_ADJ_ASSOCS PAS, OE_PRICE_ADJUSTMENTS PA1
                   WHERE   PAS.RLTD_PRICE_ADJ_ID = PA.PRICE_ADJUSTMENT_ID
                           AND PA1.PRICE_ADJUSTMENT_ID =
                                 PAS.PRICE_ADJUSTMENT_ID
                           AND PA1.LIST_LINE_TYPE_CODE = 'PBH')
ORDER BY   l.line_id
/

a: Qp_list_headers_vl is view based on qp_list_headers_b and qp_list_headers_tl tables.

Query to find out order and line hold information :

SELECT   ho.name hold_name,
           hs.hold_until_date,
           hs.hold_comment,
           h.order_number,
           oh.header_id,
           oh.line_id,
           oh.order_hold_id,
           l.item_identifier_type,
           l.inventory_item_id,
           l.ordered_item
    FROM   oe_order_holds_all oh,
           oe_order_lines_all l,
           oe_order_headers_all h,
           oe_hold_definitions ho,
           oe_hold_sources_all hs
   WHERE       h.order_number = 14463
           AND oh.header_id = h.header_id
           AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')
           AND h.open_flag = 'Y'
           AND oh.hold_source_id = hs.hold_source_id
           AND hs.hold_id = ho.hold_id
           AND h.header_id = l.header_id(+)
           AND l.open_flag = 'Y'
           AND l.line_id = NVL (oh.line_id, l.line_id)
           AND l.service_reference_line_id IS NULL
           AND oh.hold_release_id IS NULL
           AND NVL (h.org_id, 0) = 204
           AND NVL (l.org_id, 0) = NVL (h.org_id, 0)
ORDER BY   ho.name, h.order_number

Query to find customer, ship to and bill to information of an order :

SELECT   h.order_number,
         c.name customer_name,
         lk1.meaning Freight_Terms,
         lk2.meaning FOB,
         s.location_code ship_location_code,
         s.address_line_1 ship_address1,
         s.address_line_2 ship_address2,
         s.state ship_state,
         s.postal_code ship_zip,
         s.country ship_country,
         b.location_code bill_location_code,
         b.address_line_1 bill_address1,
         b.address_line_2 bill_address2,
         b.country bill_country
  FROM   ar_lookups lk2,
         oe_lookups lk1,
         oe_sold_to_orgs_v c,
         oe_invoice_to_orgs_v b,
         oe_ship_to_orgs_v s,
         oe_order_headers_all h
 WHERE       h.order_number = 14463
         AND h.org_id = 204
         AND h.ship_to_org_id = s.organization_id
         AND h.invoice_to_org_id = b.organization_id
         AND h.sold_to_org_id = c.organization_id
         AND h.freight_terms_code = lk1.lookup_code(+)
         AND lk1.lookup_type(+) = 'FREIGHT_TERMS'
         AND lk2.lookup_code(+) = h.fob_point_code
         AND lk2.lookup_type(+) = 'FOB'

Query to find out the customer, line item, ordered qty and price info of the order :

SELECT   h.order_number,
           org.name customer_name,
           h.ordered_date order_date,
           ot.name order_type,
           s.name sales_rep,
           l.line_id,
           l.line_number,
           l.inventory_item_id,
           si.segment1,
           l.ordered_quantity,
           l.unit_selling_price,
           NVL (l.ordered_quantity, 0) * NVL (l.unit_selling_price, 0) amount,
           h.transactional_curr_code currency_code
    FROM   ra_salesreps s,
           oe_transaction_types_tl ot,
           oe_sold_to_orgs_v org,
           mtl_system_items_vl si,
           oe_order_lines_all l,
           oe_order_headers_all h
   WHERE       h.order_number = 14463
           AND h.org_id = 204
           AND l.header_id = h.header_id
           AND h.sold_to_org_id = org.organization_id
           AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')
           AND h.open_flag = 'Y'
           AND l.open_flag = 'Y'
           AND l.service_reference_line_id IS NULL
           AND l.inventory_item_id = si.inventory_item_id
           AND NVL (si.organization_id, 0) = 204            --Item master orgn
           AND h.order_type_id = ot.transaction_type_id
           AND h.salesrep_id = s.salesrep_id
           AND h.org_id = s.org_id
ORDER BY   l.line_id
/

a: Oe_sold_to_orgs_v is a view based on hz_parties and hz_cust_accounts.

b: Ra_salesreps is a view based on JTF_RS_SALESREPS and JTF_RS_RESOURCE_EXTNS_VL.

You must set the org context for the views to function properly as..

BEGIN
   fnd_client_info.set_org_context ('204');
END;

Query to 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
/

Query to find default category for a category set :

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

Query to 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
/

Query to find 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
/

Query to find item cross-references :

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
/

Query to 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