Oracle Apps Inventory Tables

Following are important tables in Oracle Apps Inventory

MTL_SYSTEM_ITEMS_B
 This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID.

MTL_ITEM_STATUS
This is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete.

MTL_UNITS_OF_MEASURE_TL
This is the definition table for both the 25-character and the 3-character units of measure. The base_uom_flag indicates if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item.

MTL_ITEM_LOCATIONS
This is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.

MTL_ITEM_CATEGORIES
This table stores inventory item assignments to categories within a category set.

MTL_CATEGORIES_B
This is the code combinations table for item categories.

MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.

MTL_CATEGORY_SETS_B
It contains the entity definition for category sets.

MTL_DEMAND
This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows,Open Demand Rows, and Reservation Rows.

MTL_SECONDARY_INVENTORIES
This is the definition table for the subinventory.

MTL_ONHAND_QUANTITIES
It stores quantity on hand information by control level and location.

MTL_TRANSACTION_TYPES
It contains seeded transaction types and the user defined ones.

MTL_MATERIAL_TRANSACTIONS
This table stores a record of every material transaction or cost update performed in Inventory.

MTL_ITEM_ATTRIBUTES
This table stores information on item attributes.

MTL_ITEM_CATALOG_GROUPS_B
This is the code combinations table for item catalog groups.

MTL_ITEM_REVISIONS_B
It stores revision levels for an inventory item.

MTL_CUSTOMER_ITEMS
It stores customer item information for a specific customer. Each record can be defined at one of the following levels: Customer, Address Category, and Address. The customer item definition is organization independent.

MTL_SYSTEM_ITEMS_INTERFACE
It temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.

MTL_TRANSACTIONS_INTERFACE
It allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.

MTL_ITEM_REVISIONS_INTERFACE
It temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.

MTL_ITEM_CATEGORIES_INTERFACE
This table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.

MTL_DEMAND_INTERFACE
It is the interface point between non-Inventory applications and the Inventory demand module. Records inserted into this table are processed by the Demand Manager concurrent program.

MTL_INTERFACE_ERRORS
It stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

MTL_PARAMETERS
It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory.

Oracle Apps Credit Card: Process in iExpense and Payables

Tables
ap_credit_card_trxns_all
ap_card_programs_all
ap_cards_all
AP_EXPENSE_FEED_LINES
AP_EXPENSE_FEED_DISTS


Credit Card setups:
1. Credit Card Code Sets window: create credit card code sets.

2. Credit Card Programs window: define your credit card program, including the card issuer, card type, and credit card code set.

3. Credit Card GL Sets window: define GL account sets

4. Credit Card Profiles window: define credit card profiles that you assign to credit cards. Attributes of a credit card profile include credit card program, GL account set, default GL account, exception clearing account, employee verification options, and manager approval options.

5. Credit Cards window: assign a card to a card holder and assign a credit card profile to the card


Steps to process credit card transactions:
Procure to Pay process:
You can streamline your procure-to-pay process by implementing a procurement card program in which your employees purchase items directly from suppliers using a credit card. The credit card issuer then sends transaction files directly to you (the employer). You can import credit card transaction files from your card issuer directly into Payables. Then, you can automatically generate transaction accounting distributions and create invoices to pay the card issuer.

1. You can create a SQL*Loader program that uses a flat file containing the credit card transaction details you want to transfer into the AP_EXPENSE_FEED_LINES table

2. Credit Card Transaction Validation and Exception Report:
   Use this program to validate the credit card transactions you imported into AP_EXPENSE_FEED_LINES.  This program identifies   exceptions such as undefined credit card numbers, invalid transaction or posted currency codes, and invalid credit card codes.

3. Employee verification. This initiates the Credit Card Transaction Employee Workflow, and it executes as you have defined it. If verification is required, an employee can verify transactions directly from a workflow notification.

4. Manager approval or notification. This inititates the Credit Card Transaction Manager Workflow, and it executes as you have defined it. If approval is required from the manager, a manager can approve an employee's credit card transactions directly from a workflow notification.

5. Credit Card Invoice Interface Summary: This program creates invoices for your credit card issuers in the Payables Open Interface tables. This program selects all records for a given date range in AP_EXPENSE_FEED_DISTS with a status of at least Validated.

6. Use Payables Open Interface Import Program to create the invoices for data in Payables Open Interface tables.

iExpense Credit card transactions process:
1. Load Employee credit card expense data into ap_credit_card_trxns_all.

2. Run the "Credit Card Transactions Validation Program" to validated the credit card transactions data.

3. Once data is validated the lines become available in iExpense for employees to select and submit the Expense reports.

Oracle Inventory: Material status Control

Materail Status Control allows to track status of materail along with movement of materail. Oracle allows to track materail status at following levels
     1. Lot
     2. Subinventory
     3. Item
     4. Locator

Following are important Setups to enable the tracking of materail status
  1. Define Materail Status
          a. Usage level:
          b. Type of Transactions allowed
  2. At Organization Inv Parameters enable the "Default On Hand Material status".
  3. If Inv Organization has onhand then it can be enabled by running
       "Activate Onhand Level Material Status Tracking"

R12 Features:
1. Separation of Responsibility for Materail Movement and Status Update.
2. Mutliple status of same lots in different Locators
3. Multiple status of different Lots in Same Locator

Notes:
1. Material movement doesnt change the Lot material status, one needs to update material status.

Oracle Developer 10g: frm-18103:fail to initialize the development Environment

Installed Oracle Developer 10g on windows machine (win 7), all the installation was successful. Now when I click on any Developer tools its not opening
   1. Oracle forms fails with error and doesn’t start
        "frm-18103:fail to initialize the development Environment"
  2. Oracle reports fails to start

Issue:
Oracle Developer 10g though installed successfully it messed up with Oracle Home setting on your machine. The path in ORACLE_HOME environment variable is not correct and its causing all the Developer tools to fail from starting.

Solution:
You need to correct the ORACLE_HOME environment variable
 1. Navigate to Computer>Properties>Advanced System settings> go to Advanced Tab>Environment Variables
2.  Check the path in ORACLE_HOME User variables, if no ORACLE_HOME variable creates one.
     The path should look like this
           C:\DevSuiteHome_1

Oracle Apps Receivables (AR) Tables

Transactions
RA_CUSTOMER_TRX_ALL	Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL	Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL	Distribution for Transaction Lines
RA_CUST_TRX_LINE_SALESREPS_ALL	Salesrep information for Transaction Lines
Transaction Interface Tables
RA_INTERFACE_LINES_ALL	Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL	Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL	Transaction Distribution information
RA_INTERFACE_ERRORS_ALL	Transaction errors table
AR_PAYMENTS_INTERFACE_ALL	Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL	Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL	Lockbox transfers the receipts that pass validation to the interim tables
Receipts tables
AR_CASH_RECEIPTS_ALL	Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL	stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL	This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL	This table stores all of the activity that is contained for the life cycle of a receipt. • Each row represents one step. • The status field for that row tells you which step the receipt has reached. • Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.
Customer Tables
HZ_PARTIES	A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS	This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES	This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL	This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL	This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS	A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
Setup tables
RA_CUST_TRX_TYPES_ALL	This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES	This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS	This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications

Oracle Apps: Payables Table structure

Oracle Apps Payable Table Structure:

Supplier Tables
AP_SUPPLIERS	This table replaces the old PO_VENDORS table. It stores information about your supplier level attributes.
AP_SUPPLIER_SITES_ALL	It stores information about your supplier site level attributes.
IBY_EXTERNAL_PAYEES_ALL	stores Payee(supplier) information.
HZ_PARTIES	Party data for the suppliers.
HZ_PARTY_SITES	Party site data for the supplier sites.B4
Supplier Interface tables
ap_suppliers_int	Supplier Interface. Execute Supplier Open Interface Import Program
ap_supplier_sites_int	Supplier site interface. Execute "Supplier Sites Open Interface Import" Program
Invoice
AP_INVOICES_ALL	It contains records for invoices you enter
AP_INVOICE_LINES_ALL	It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
AP_INVOICE_DISTRIBUTIONS_ALL	It holds the distribution information that is manually entered or system-generated
Invoice Interface
AP_INVOICES_INTERFACE	Invoice Header Information. Run Payables Open Interface Import concurrent program
AP_INVOICE_LINES_INTERFACE	Invoice Lines and Distributions Information
Payments:
AP_CHECKS_ALL	Check Header Information
AP_INVOICE_PAYMENTS_ALL	Details of Invoice paid by a Check/Payment
AP_PAYMENT_HISTORY_ALL
AP_INV_SELECTION_CRITERIA_ALL
AP_SELECTED_INVOICES_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
CE_PAYMENT_DOCUMENTS	AP_CHECK_STOCKS_ALL
IBY_PAY_SERVICE_REQUESTS	Payment Process Request information  (11i Terminology is  Payment Batch)
IBY_PAY_INSTRUCTIONS_ALL	Payment Instruction information
IBY_DOC_PAYABLES_ALL	Invoice information stored by IBY for generating payment
IBY_PAYMENTS_ALL	Payment Information
Accounting
XLA_EVENTS	replaces AP_ACOCUNTING_EVENTS_ALL
XLA_AE_HEADERS	replaces AP_AE_HEADERS_ALL
XLA_AE_LINES	replaces AP_AE_LINES_ALL
Bank Accounts
CE_BANK_ACCOUNTS	AP_BANK_ACCOUNTS_ALL
CE_BANK_ACCT_USES_ALL	replaces AP_BANK_ACCOUNT_USES_ALL
Credit Card Transactions Table
AP_EXPENSE_FEED_LINES	Use the Credit Card Transaction Interface Table to load transaction datafrom your credit card issuers into your Payables system. When younsubmit the Credit Card Transaction Validation and Exception Report, for records where the CREATE_DISTRIBUTION_FLAG is Y, Payables uses the data in the AP_EXPENSE_FEED_LINES table to create distributions with proper accounts in the AP_EXPENSE_FEED_DISTS table

Oracle Order to Cash tables: O2C tables

Oracle Apps Order to Cash important tables:
Order entry
	OE_ORDER_HEADERS_ALL	1 record created in header table
	OE_ORDER_LINES_ALL	Lines for particular records
	OE_PRICE_ADJUSTMENTS	When discount gets applied
	OE_ORDER_PRICE_ATTRIBS	If line has price attributes then populated
	OE_ORDER_HOLDS_ALL	If any hold applied for order like credit check etc
Order Booked
	OE_ORDER_HEADERS_ALL	Booked_Flag=Y, Order booked.
	WSH_DELIVERY_DETAILS	Status Opened
	WSH_DELIVERY_ASSIGNMENTS	WSH_DELIVERY_ASSIGNMENTS.delivery_id will be NULL as still pick release operation is not performed as final delivery is not yet created.
	MTL_DEMAND.	‘Demand interface program’ is triggered in the background and demand of the item with specified quantity is created
Order Scheduled/Reserved	This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background(if scheduled) and quantities are reserved.
	OE_ORDER_LINES_ALL	Awaiting Shipping
	MTL_RESERVATIONS	This is only soft reservations. No physical movement of stock
	WSH_DELIVERY_DETAILS	R: Ready to Release: Line is ready to be released
Pick Released	Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.
	OE_ORDER_LINES_ALL
	WSH_DELIVERY_DETAILS	S: Released to Warehouse
	WSH_NEW_DELIVERIES	A new record is created in WSH_NEW_DELIVERIES with status_code = ‘OP’ (Open). WSH_NEW_DELIVERIES has the delivery records.
	WSH_DELIVERY_ASSIGNMENTS	Deliveries get assigned
	WSH_PICKING_BATCHES	After batch is created for pick release
	MTL_TXN_REQUEST_HEADERS	A move order is created in Pick Release process which is used to pick and move the goods to staging area (here move order is just created but not transacted). MTL_TXN_REQUEST_HEADERS, MTL_TXN_REQUEST_LINES  are move order tables
	MTL_TXN_REQUEST_LINES	move order line
Pick Confirm	Pick Confirm is to transact the move order created in Pick Release process
	OE_ORDER_LINES_ALL	flow_status_code =’PICKED’
	MTL_MATERIAL_TRANSACTIONS_TEMP	(Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
	MTL_MATERIAL_TRANSACTIONS	MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Pick Transaciton
	MTL_TRANSACTION_ACCOUNTS	updated with accounting information for mtl_material Transactions
	WSH_DELIVERY_DETAILS	Y: Staged- Line has been picked and staged by Inventory
	MTL_ONHAND_QUANTITIES
Ship Confirmed	The goods are picked from staging area and given to shipping. “Interface Trip Stop” program runs in the backend.
	OE_ORDER_LINES_ALL	.flow_status_code =‘SHIPPED’ Shipped_Quantity get populated
	WSH_DELIVERY_DETAILS	Released_Status=C ;Shipped ;Delivery Note get printed Delivery assigned to trip stop quantity will be decreased
	MTL_TRANSACTIONS_INTERFACE	Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS
	MTL_MATERIAL_TRANSACTIONS	updated with Sales Order Issue transaction
	WSH_NEW_DELIVERIES	If Defer Interface is checked then OM & inventory not updated. If Defer Interface is not checked: Shipped
	OE_ORDER_LINES_ALL
	WSH_DELIVERY_LEGS	1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
	OE_ORDER_HEADERS_ALL	If all the lines get shipped then only flag N
	WSH_NEW_DELIVERIES	Data Deleted
	MTL_RESERVATIONS	Data Deleted
	MTL_DEMAND	Data Deleted
	MTL_ONHAND_QUANTITIES	Item deducted from MTL_ONHAND_QUANTITIES
	MTL_TRANSACTION_ACCOUNTS	updated with accounting information.
	WSH_TRIPS
	WSH_TRIP_STOPS
Auto Invoice	After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL.
	OE_ORDER_LINES_ALL	invoice_interface_status_code = ‘YES’
	WSH_DELIVERY_DETAILS	Released_Status=I
	RA_INTERFACE_LINES_ALL	Data will be populated after work flow process.
	RA_CUSTOMER_TRX_ALL	After running Auto Invoice Master Program for
	RA_CUSTOMER_TRX_LINES_ALL	Specific batch transaction tables get populated
Close Order	Last step of the process is to close the order which happens automatically once the goods are shipped
	OE_ORDER_LINES_ALL	flow_status_code =’CLOSED’ and open_flag = ‘N’

Cloud Computing: What is Cloud, Cloud based Computing?

   

what is the cloud,it cloud, what is in the cloud,  what is a cloud,  the cloud, cloud,  the cloud what is it,  cloud computing,  implementation of cloud computing, cloud computing techniques, cloud based computing cloud and computing, computing in the cloud

What is Cloud computing?
To understand what is Cloud computing, one needs to understand why its actually required. A small company decides to use Microsoft outlook to facilitates its internal communications. This company will require to procure required Hardware and software, do the setups, maintian the Software and decdicated IT team to support the Email client incase of any issues. What if the Company decided to use Gmail instead, in that case they don't need any Hardware(Servers), software to be installed on Computers and no IT Team required. All the company needs to do is provide acces to GMAIL to each of its users. 

Cloud computing is a type of computing that relies on sharing computing resources rather than having local servers or personal devices to handle applications. In cloud computing, the word cloud is used as a metaphor for "the Internet," so the phrase cloud computing means "a type of Internet-based computing," where different services such as servers, storage and applications are delivered to an organization's computers and devices through the Internet.

 
Background of Cloud Computing:
Cloud computing is comparable to grid computing, a type of computing where unused processing cycles of all computers in a network are harnesses to solve problems too intensive for any stand-alone machine.A great example of how this technology is being used is SETI@HOME, SETI needs a lot of computational power to scan through all of its radio signal data to find intelligent signal. Buying that amount of computational power would be incredibly expensive, so SETI@home comes up with an absolutely ingenious way to create that computing power out of thin air. When your computer is idle, it displays a screensaver. In most cases, a computer displaying its screensaver is doing absolutely nothing. All the computing power available in your machine is being wasted. So SETI@home created its own screensaver, which you install on your machine. With the SETI@home screensaver installed, your computer actually processes SETI data when it is idle. The screensaver downloads a packet of data containing a work unit of radio signals and then grinds away on them. When it's done, it sends the results back and gets another packet. The screensaver, instead of displaying fish or flying toasters, displays the work in progress.

Distributed Computing Systems are of two types
 1. Cluster Computing: Collection of high-end computers having same OS, and similar Hardware
     (workstations/PCs) usually closely connected through a LAN.
 2. Grid Computing: Clusters of different Hardware/Software may be combined to form a "Grid“ of a
      massive computing power.

Types of Grids
 1. Computational Grid–Shared Compute Resources.
 2. Data Grid–Access to Large amounts of Data spread across various sites
 3. Collaboration Grid-multiple collaboration systems for collaborating on a common issue.
Grid Computing was an enabling technology and inspiration for Cloud Computing. Distributed systems tend to have distributed memory, so the most common programming model is Message Passing between the different nodes.The processes on a distributed system communicate by passing messages which can be either control or data messages.Message Passing Interface (MPI) is a standardized protocol and API for this type of model

Types of clouds:
There are different types of clouds that you can subscribe to depending on your needs. As a home user or small business owner, you will most likely use public cloud services.
1. Public Cloud: A public cloud can be accessed by any subscriber with an internet connection and access to the cloud space.
2. Private Cloud: A private cloud is established for a specific group or organization and limits access to just that group.
3. Community Cloud: A community cloud is shared among two or more organizations that have similar cloud requirements.
4. Hybrid Cloud: A hybrid cloud is essentially a combination of any of the above.

Type of Clouds based on the service:
1. Software as a Service: A SaaS provider gives subscribers access to both resources and
    applications. SaaS makes it unnecessary for you to have a physical copy of software to install on
    your devices. SaaS also makes it easier to have the same software on all of your devices at once by
    accessing it on the cloud.
2. Platform as a Service: A PaaS system goes a level above the Software as a Service setup. A PaaS
    provider gives subscribers access to the components that they require to develop and operate
    applications over the internet. It provides the required Platform for deploying your own Software
    applications into the Cloud.
3. Infrastructure as a Service: An IaaS agreement, as the name states, deals primarily with
    computational infrastructure. So it provides the required Hardware's and other infrastructure for
    your to establish the Platform and deploy your Software applications.

Oracle Apps: Balance Sheet and Profit and Loss Reports

Oracle Apps General Ledger provided two critical reports for the Business. Its important to know
the basics of these reports.
Balance sheets:
The relationship between balance sheets and profit and loss accounts

Profit and Loss:
The profit and loss (P&L) account summarizes a business' trading transactions - income, sales and expenditure - and the resulting profit or loss for a given period.

The balance sheet, by comparison, provides a financial snapshot at a given moment. It doesn't show day-to-day transactions or the current profitability of the business. However, many of its figures relate to - or are affected by - the state of play with P&L transactions on a given date.
Any profits not paid out as dividends are shown in the retained profit column on the balance sheet.
The amount shown as cash or at the bank under current assets on the balance sheet will be determined in part by the income and expenses recorded in the P&L. For example, if sales income exceeds spending in the period preceding publication of the accounts, all other things being equal, current assets will be higher than if expenses had outstripped income over the same period.

If the business takes out a short-term loan, this will be shown in the balance sheet under current liabilities, but the loan itself won't appear in the P&L. However, the P&L will include interest payments on that loan in its expenditure column - and these figures will affect the net profitability figure or 'bottom line'.
Using balance sheet and P&L figures to assess performanceMany of the standard measures used to assess the financial health of a business involve comparing figures on the balance sheet with those on the P&L.

Accounting periods:
A balance sheet normally reflects a company's position on its accounting reference date (ARD), which is the last day of its accounting reference period. The accounting reference period, also known as the financial year, is usually 12 months. However, it can be longer or shorter in the first year of trading, or if the ARD is subsequently changed for some reason.

Companies House automatically sets the first ARD. Thus the end of the first financial year is the first anniversary of the last day of the month in which the company was formed. If you decide to change this, you will need to notify Companies House.

Contents of the balance sheet
A balance sheet shows:
    fixed assets - long-term possessions
    current assets - short-term possessions
    current liabilities - what the business owes and must repay in the short term
    long-term liabilities - including owner's or shareholders' capital

The balance sheet is so-called because there is a debit entry and a credit entry for everything (but one entry may be to the profit and loss account), so the total value of the assets is always the same value as the total of the liabilities.

Fixed assets include:
    tangible assets - eg buildings, land, machinery, computers, fixtures and fittings - shown at their depreciated or resale value where appropriate
    intangible assets - eg goodwill, intellectual property rights (such as patents, trade marks and website domain names) and long-term investments

Current assets are short-term assets whose value can fluctuate from day to day and can include:
    stock
    work in progress
    money owed by customers
    cash in hand or at the bank
    short-term investments
    pre-payments - eg advance rents

Current liabilities are amounts owing and due within one year. These include:
    money owed to suppliers
    short-term loans, overdrafts or other finance
    taxes due within the year - VAT, PAYE (Pay As You Earn) and National Insurance

Long-term liabilities include:
    creditors due after one year - the amounts due to be repaid in loans or financing after one year, eg bank or directors' loans, finance agreements
    capital and reserves - share capital and retained profits, after dividends (if your business is a limited company), or proprietors capital invested in business (if you are an unincorporated business)

Oracle Apps: Generate Function Tree for specific Oracle Apps Menu

In Oracle Apps you don't have a standard report that lists all the functions that are part of a particular Menu. Though in Application developer>Menu you can see in Front end the List of Functions, but using this in the code is not straight forward.
Below is the code that was available from metalink and modified to generate the list of Function ids for a specific Menu and store in Oracle database custom table.

Note: script ignores any exclusions setup at Responsibility level.

Database Object creation script:
CREATE OR REPLACE TYPE  STACK_REC AS OBJECT
(
  SUB_MENU_ID NUMBER,
  PROMPT      VARCHAR(500) ,
  T_LEVEL       NUMBER ) ;


CREATE OR REPLACE TYPE STACK_REC_ARRAY AS VARRAY(1000) OF STACK_REC ;

CREATE OR REPLACE  TYPE TRA_STACK AS OBJECT
( MAX_SIZE INTEGER ,
  TOP      INTEGER ,
  POSITION STACK_REC_ARRAY ,
  MEMBER PROCEDURE INITIALIZE ,
  MEMBER FUNCTION FULL RETURN BOOLEAN ,
  MEMBER FUNCTION EMPTY RETURN BOOLEAN ,
  MEMBER PROCEDURE PUSH(STACK_REC_IN_PAR IN STACK_REC),
  MEMBER PROCEDURE POP(STACK_REC_OUT_PAR OUT  STACK_REC)
) ;

CREATE OR REPLACE TYPE BODY TRA_STACK AS

MEMBER PROCEDURE INITIALIZE IS
BEGIN
 TOP:=0;
 POSITION :=STACK_REC_ARRAY(NULL);
 MAX_SIZE :=POSITION.LIMIT ;
 POSITION.EXTEND(MAX_SIZE -1,1);
END INITIALIZE ;

MEMBER FUNCTION FULL RETURN BOOLEAN IS
BEGIN
 RETURN (TOP=MAX_SIZE);
END FULL ;

MEMBER FUNCTION EMPTY RETURN BOOLEAN IS
BEGIN
 RETURN (TOP=0);
END EMPTY ;

MEMBER PROCEDURE PUSH(STACK_REC_IN_PAR IN STACK_REC) IS
BEGIN
IF NOT FULL THEN
 TOP := TOP+ 1;
 POSITION(TOP) :=STACK_REC_IN_PAR ;
ELSE
 RAISE_APPLICATION_ERROR(-20101,'STACK OVERFLOW');
END IF;
END PUSH ;

MEMBER PROCEDURE POP(STACK_REC_OUT_PAR OUT  STACK_REC)
IS
BEGIN
IF NOT EMPTY THEN
  STACK_REC_OUT_PAR :=POSITION(TOP) ;
  TOP := TOP -1 ;
ELSE
  RAISE_APPLICATION_ERROR(-20102,'STACK UNDERFLOW');
END IF;
END POP;

END ;
/



Table creation script: To store the Tree structure
Create table tree_tab
(
col1 varchar2(200),
col2 varchar2(200),
col3 varchar2(200),
col4 varchar2(200),
col5 varchar2(200),
col6 varchar2(200),
col7 varchar2(200),
col8 varchar2(200),
col9 varchar2(200),
col10 varchar2(200),
col11 varchar2(200),
col12 varchar2(200),
col13 varchar2(200),
col14 varchar2(200),
col15 varchar2(200)
)
/


Oracle apps database Procedure to generate the structure:
CREATE OR REPLACE PROCEDURE GENERATE_TREE (P_ROOT_MENU_ID IN NUMBER) IS
    Temp_stack_rec stack_rec ;
    Temp_id_var number ;
    M_id_var number ;
    I number ;
    N number ;
    Sql_str varchar2(200);
    Stack_obj TRA_STACK ;
    Cursor m_cursor (p_menu_id NUMBER) is
    Select A.menu_id , B.sub_menu_id ,b.function_id prompt ,B.entry_sequence from fnd_menus A ,
    Fnd_menu_entries B ,
    Fnd_menu_entries_tl C
    Where
    A.menu_id = B.menu_id and
    B.menu_id=C.menu_id  and
    B.entry_sequence=C.entry_sequence and
    C.Language='US' and
    A.menu_id=p_menu_id ;

Begin
    Stack_obj:=TRA_STACK(NULL,NULL,NULL);
    Stack_obj.initialize ;
    Temp_stack_rec := stack_rec(NULL,NULL,NULL);
    M_id_var := P_ROOT_MENU_ID ;
    N :=0;
    <<label_outer>>
    I:=0;
    N:=N+1;


    For s_tab_rec in m_cursor(M_id_var) loop
        I := I+1;   
        If I=1 then

    Sql_str :=   'insert into tree_tab' || '(' || concat('col',N) || ')' ||  ' values ' || '(' || concat('''',s_tab_rec.prompt) || '''' ||  ')' ;
    Execute immediate sql_str ;
         If s_tab_rec.sub_menu_id is NULL then
              Temp_id_var := NULL ;
         Else
              Temp_id_var := s_tab_rec.sub_menu_id ;
         End if ;
      Else
          Temp_stack_rec.sub_menu_id :=s_tab_rec.sub_menu_id ;
          Temp_stack_rec.prompt := s_tab_rec.prompt ;
          Temp_stack_rec.T_level := N ;
          Stack_obj.push(Temp_stack_rec);
      End if ;
    End loop ;
    If Temp_id_var is not NULL then
       M_id_var := Temp_id_var ;
       Goto label_outer ;
    Else
       <<label_inner>>
           if Stack_obj.empty=true then
         return ;
           else
         Stack_obj.pop(Temp_stack_rec) ;
           end if ;
         if Temp_stack_rec.sub_menu_id is not NULL then
             Sql_str :=   'insert into tree_tab'    || '(' ||      concat('col',Temp_stack_rec.T_level) || ')' ||  ' values  ' || '(' || concat('''',Temp_stack_rec.prompt) || '''' ||  ')' ;
             Execute immediate sql_str ;
             M_id_var :=Temp_stack_rec.sub_menu_id ;
             N := Temp_stack_rec.T_level ;
             Goto label_outer ;
         Else
             Sql_str :=   'insert into tree_tab'    || '(' ||      concat('col',Temp_stack_rec.T_level) || ')' ||  ' values  ' || '(' || concat('''',Temp_stack_rec.prompt) || '''' ||  ')' ;
             Execute immediate sql_str ;
             N := Temp_stack_rec.T_level ;
             Goto label_inner ;
         End if ;
    End if ;
    commit ;
End    ;
/



Script to Call the Procedure:
Begin
GENERATE_TREE (991);
end;