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;