Create Mass Additions from Invoice Distributions in Payables

11i/R12 process to Add Asset through PO/Invoice to FA

1. Make sure that the Asset item that you want to procure is having the expense account  as the Asset
   Clearing account of the Asset category that you want to assign in Fixed Assets.

2. Raise a PO for this Asset item you want to procure, so that the PO Distribution account is the Asset
   Clearing item.

3. Receive the Asset item in the Expense Location.

4. Make new Invoice and match it with Inventory Receipt.

5. The Invoice Distributions will automatically be defaulted due to matching with receipt. "Track As Asset
    Flag" will be set to Yes in Invoice distribution

7. Validate the Invoice and Create Online Accounting for this Invoice.

8. Run the Mass Addition Creation request from Payables module. The Invoice distribution will be imported
    into the Mass Addition Tables in FA.

10. Prepare and Post the Mass Addition line to create the Asset.

Conditions for Invoice distribution Line to be picked up by Create Mass additions Program following
are the important points
1. Invoice Distribution account is a Asset Account.
2. The account is set up for an existing asset category as either the
    asset clearing account or the CIP clearing account
3. Track As Asset check box is checked.
4. Invoice line distribution is posted to GL.

Conditions for Expensed Invoice Line Distributions To Be Imported
The create mass additions process imports an expensed line only if:
1. Invoice line distribution is an Expense account
2. Track as Asset is checked
4. Invoice line distribution is posted to GL.
5. The general ledger


R12 Features:
1. Enhanced Asset Tracking:
   Asset information typically entered on a purchase order, such as manufacturer, serial number, and model
   number, can now flow seamlessly into Payables and Assets. R12 provides the ability to track attributes such as Manufacturer, Model, Serial Number, Warranty Number from Purchasing through Accounts Payable to Fixed Assets.This increases processing efficiency and eliminates manual updates in FA that may have been required in the past.

2. Addition of  Asset Book at Invoice Line level:
   The Create Mass Additions process can now be submitted to select only those invoice distributions with a   specific Asset Book, reducing the possibility of errors that need to be manually corrected. Addition   of this field to the Lines and Distributions model will enable controlled transfer of Asset Distributions per Asset Book over to Oracle Assets via the Create Mass Additions process.  In 11i, the Asset Book is defaulted from the parameter given to the Mass Additions process; the user has not control in terms of selecting appropriate distributions to transfer to the given Asset Book since the distributions don’t have Asset Book information.  With the addition of this field to the Lines and Distributions model, the Payables user will be able to select the appropriate Asset Book for a line, have that default to the distribution and in turn have the Create Mass Additions process transfer distributions having Asset Book matching the parameter for the run.

3. Addition of  Asset Category at Invoice Distribution level:
With the addition of this field to the Lines model, the Payable user will be able to populate the Asset Category via the Invoice Entry mechanisms.  One caveat of this solution is that the field will not be mandatory and thus, distributions may still be transferred with null Asset Category.  Furthermore, since Oracle Payable will not support validation of the Asset Category against the Asset Clearing Account, distributions may be transferred with mismatched Asset Category.

4. Invoice Lines:
   Oracle Payables incorporates Invoice Lines into the invoice model. Adding Invoice Lines is a key 
   architectural change, which enables Oracle Payables to better model the paper or electronic business
   document yet maintain key features that exist at the invoice distributions level.Furthermore, it facilitates
  the capture and transfer of additional, pertinent information to and from Oracle Projects and Oracle Assets.

OAF Basics

With arrival for R12 and now Oracle Fusion, oracle is moving from Forms based interface to
Web based UI. So now days there is a buzz of OAF and ADF which has been the bases for development
of these Web based UI for Oracle ebiz.

Oracle Application Framework(OA Framework) is a proprietary framework developed by Oracle Corporation for application development within the Oracle E-Business Suite.
OA Framework is an architecture for creating web based front end pages and J2EE type of applications within the Oracle EBS ERP platform. In order to develop and maintain OAF functionality, Oracle's JDeveloper tool is used. OA Framework uses the UIX other XML technologies for building the components.

OA Framework is based on J2EE technology called BC4J (Business Components for Java)
The OA Framework is a Model-view-controller (MVC) framework built using J2EE (Java 2 Platform, Enterprise Edition) technologies.

                              

EO(Entity Objects)
Entity Object is based on database table or other data source.Entity Object contains attributes which represent database columns.All insert/update/delete (DML Operations) transactions go through EO to database.

VO:
View Objects are based on EO or SQL Query which is again based on EO Objects
Two types
1. SQL based
2. EO based
Basically VO is synonymous to views used in PLSQL Programming they are used for joining tables, filtering based on conditions and sorting the data. Entity Objects can be based on any number of
EO and provide access to EO.

Application Module:
Its a container for VO. Once you create a Application Module you need to associate the corresponding
VO to the Application Modules. Access to the VO is always provided through the Application Module.
Every Page in OAF Framework need to be associated with a AM.

Controller:
When user clicks a button, or performs certain action what responses should be triggered is coded in the
Controller. All the responses to User actions, Application Flow is coded into the Controller. m
Model objects like EO and VO can't be accessed directly from the Controller Class, except AM.

Some common methods that controller has
1. ProcessRequest: Fires when OAF page loads for the first time
2. ProcessFormRequest: Fires when user submits the page.

Below is the Onion ring MVC architectural representation, it explains how the security/encapsulation of
each layer happens when an OAF application is build. 

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Error:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Issue and its details:
Submit your concurrent Program and the Program ends up in error with log showing
the below error message

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Cause:
If you go by the error its actually caused when there is huge data being fetched by the Program/Query
and it packs up the allocated TEMP Tablespace. In this case there is nothing much can be done
by the developer and its DBA job.
But do few checks before actually contacting the DBA
  1. Run the Program for different set of Parameters(which will fetch almost same amount of data) and it should
     fail.
    
  2. Run the query in Toad with same/different set of Parameters and you should get the same error.
 
If you don't get the same error in step 1 and 2 its not issue with TEMP space its something do
with your query/Program

Solution:
In case its DBA issue, contact DBA and ask him to perform following steps

1.Verify and modify the datafiles sizes
   SELECT * FROM DBA_DATA_FILES;
  

2.Verify the user’s default tablespace to a bigger one
  SELECT * FROM Dba_Users;


The issue in my case was not with the DBA,as I performed the Step 1 and 2 in Cause section
the error didn't occur.
Actually I was using a OPTIMIZER HINT and it was causing this issue, I removed the HINT and
the error disappeared. But I had hit on performance of my query by removing the HINT

Oracle Apps GRC 860 Terminologies

Oracle GRC 860 Terminologies:

To prevent frauds/misuse of various functionality of Oracle applications in business some of control will be required in the way application is used by the Users. A simple example is user in Oracle application should not be able to Initiate Purchase Order and also approve it, this can be achieved with Oracle GRC.

Governance, Risk and Compliance is a Application provided by Oracle to enforce certain business or compliance rules in usage of various modules provided by Oracle Application. The main purpose of this application is to achieve compliance of the Audit rules or any specific requirement to avoid frauds/misuse of functionality in Oracle applications. GRC is not just limited to Oracle Applications but also can be configured with other products like PeopleSoft , etc.

It consists of basically 3 modules

1.    ACG:

Access control Governance which deals with various accesses related rules and controls required in Oracle applications.

2.    TCG:

Transaction Control which deals with the transactions being performed in Oracle applications

3.    PCG:

It helps in having the Preventive controls in place to avoid any misuse/fraud in Oracle applications. ACG is actually finding the existing issues related in misuse/fraud while PCG is the next step to prevent these kind of issues from happening.

GRC 860 differs from 722 very much in the controls are implemented in Oracle applications or any other ERP system.

Data source:

Its actually Data systems against which GRC application will run to evaluate any issues of Governance and Risk compliance. Simply it defines the database of the ERP/Other system, GRC may run against multiple data sources. Data source configuration is important setup in implementing GRC.

Access point:

An access point is an object in a business-management application which, when made available to a user, enables him to view or manipulate application data. In Oracle E-Business Suite, access points include roles, responsibilities, menus, functions, grants, and concurrent programs.

Access points are considered to conflict when, in combination, they would enable individual users to complete transactions that may expose a company to risk.

Entitlement:

Its collection of access points or simply grouping the related access points into one.

Model: (SOD rule in 722)

An access model specifies access points in business-management applications that conflict with one another that would enable individual users to complete risky transactions. If a person has the Responsibility to Initiate the Purchase Order as well as responsibility to approve it, then these two access points i.e. Responsibility can be defined in model to identify such users to avoid any fraud/misuse.

An Model can be evaluated to find out such Users who the access points defined in the model. Models can be manually defined or they can be imported using templates provided by Oracle. Usually Oracle provides a set of standards models in the templates and they can be reviewed and only relevant models can be then imported.

Control:

A control is defined for a model it adds details like information needed for the control to be run and its incidents to be resolved, a data-source to which the control is applied, participants who resolve its incidents, a priority, and more. It also add enforcement type to the model — Prevent, Monitor, or Approval required — that determines what a participant may do about the control’s incidents

An AACG model returns “temporary” results  a snapshot of risk that is replaced each time the model is evaluated. A control returns “permanent” results records of violations that remain available to be resolved no matter how often the control is run.

Incidents:

Records of control violations are known as “incidents.” So that incidents may be resolved, each control must name one or more “participants” — GRCC users who are associated with controls either as individuals or as members of participant groups. At least one participant  is assigned to address incidents generated by the control; other participants observe the decisions made by those who are entitled to act.

Moreover, each AACG control is assigned one of three “enforcement types” — Prevent, Monitor, or Approval Required.

Filter:

A filter may specify an access point or an entitlement (a set of access points); if so, it identifies users who have been assigned the specified access point, or any access point in the specified entitlement. A conflict exists when a user is selected by a combination of these filters. Combinations are determined by the way you arrange filters in the model.

Condition:

A filter may define a condition, which sets limits on the conflicts a model may identify. Typically, a condition specifies users or other those are excluded from analysis by the model, or it specifies a type of item and requires that the model return results only when access points conflict within individual instances of that item type.

Global conditions:

A global condition sets limits on the conflicts identified by all access models or controls evaluated on a given data source. Like a condition written for a specific model, a global condition typically specifies users or other items that are excluded from analysis by a model or control, or it specifies a type of item and requires the model or control to return results only when access points conflict within individual instances of that item type..

Cursor Attributes

There are five cursor attributes:
%isopen

%found

%notfound

%rowcount

%bulk_rowcount

------------------------------------------------------------------------------
%isopen
With %isopen it is possible to test whether a cursor was opened:

declare 
      cursor cur_emp is
        SELECT NAME
        FROM   EMP;       
      l_name
begin
      open cur_emp;
      fetch cur_emp  into  l_name;
      if cur_emp%isopen
      then
            log("Cursor is Open")
      end if;
      close cur;
end;

If there are records in EMP table then the message "Cursor is Open" will be logged at the terminal.

------------------------------------------------------------------------------
%found returns true when the last fetch operation on the cursor fetched a row.
%notfound returns true when the last fetch operation on the cursor did not fetch a row.

declare 
        cursor cur_emp is
        SELECT NAME
        FROM   EMP;
begin
       open cur_emp;
       Loop
       if cur_emp%found
       then
              log("Record returned");        
       elsif cur_emp%notfound
              log("No records");
       exit;          
       end if;
       close cur;
end;

If there are 5 records in EMP TABLE then you will get "Record returned" message five times and then
loop will exit logging the message "No records".
%NOTFOUND is mostly used to exit the LOOP for cursor after the last record has been fetched/processed.

---------------------------------------------------
%rowcount returns the number of rows that have been fetched so far

example;
declare 
        cursor cur_emp is
        SELECT NAME
        FROM   EMP;       
begin
       open cur_emp;
       loop
       if cur%found then     
                    dbms_output.put_line(cur%rowcount);
      else
                    exit;
      end if;
      end loop;
end;

%bulk_rowcount is similar to %rowcount, but is used in bulk collects.

Count a specific character occurence in a String

In PLSQL there are functions like INSTR, SUBSTR, REPLACE, but
to Count number of occurrences of a character in the string there is no standard function.

Here is a simple way, In the example below I am trying to find out the number of
occurrence of '|' in the string

select length('san|tom|elp|ert|') -
length(replace('san|tom|elp|ert|','|')) count from dual;