Create Customer Through API

DECLARE
   p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
   p_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
   p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
   p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE ;
   x_cust_account_id NUMBER;
   x_account_number  VARCHAR2 ( 2000 ) ;
   x_party_id        NUMBER;
   x_party_number    VARCHAR2 ( 2000 ) ;
   x_profile_id      NUMBER;
   x_return_status   VARCHAR2 ( 2000 ) ;
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 ( 2000 ) ;
BEGIN
   p_cust_account_rec.account_name      := 'ABCXYZ';
   p_cust_account_rec.created_by_module := 'TCA_V2_API';
   p_person_rec.person_first_name       := 'ABC';
   p_person_rec.person_last_name        := 'XYZ';
   mo_global.init ( 'AR' ) ;
   mo_global.set_org_context ( 204, NULL, 'AR' ) ;
   fnd_global.set_nls_context ( 'AMERICAN' ) ;
   mo_global.set_policy_context ( 'S', 204 ) ;
   hz_cust_account_v2pub.create_cust_account (  p_init_msg_list           =>    'T'                    ,
                                                p_cust_account_rec        =>    p_cust_account_rec     ,
                                                p_person_rec              =>    p_person_rec           ,
                                                p_customer_profile_rec    =>    p_customer_profile_rec ,
                                                p_create_profile_amt      =>    'F'                    ,
                                                x_cust_account_id         =>    x_cust_account_id      ,
                                                x_account_number          =>    x_account_number       ,
                                                x_party_id                =>    x_party_id             ,
                                                x_party_number            =>    x_party_number         ,
                                                x_profile_id              =>    x_profile_id           ,
                                                x_return_status           =>    x_return_status        ,
                                                x_msg_count               =>    x_msg_count            ,
                                                x_msg_data                =>    x_msg_data
                                             );
   dbms_output.put_line ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) ) ;
   dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
   dbms_output.put_line ( 'Party Id = '||TO_CHAR ( x_party_id ) ) ;
   dbms_output.put_line ( 'Party Number = '||x_party_number ) ;
   dbms_output.put_line ( 'Profile Id = '||TO_CHAR ( x_profile_id ) ) ;
   dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( x_msg_data, 1, 255 ) ) ;
   IF x_msg_count >1 THEN
      FOR I      IN 1..x_msg_count
      LOOP
         dbms_output.put_line ( I||'.'||SUBSTR ( FND_MSG_PUB.Get ( p_encoded=> FND_API.G_FALSE ), 1, 255 ) ) ;
      END LOOP;
   END IF;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE ( 'Error: '||SQLERRM ) ;
END;

/

COMMIT;

Compiling forms,libraries,reports in Oracle Apps

Posted: November 20, 2012 in APPS DBA

0

You all are may be well aware that we use f60gen for compiling forms and libraries in Oracle Applications Version 11i,But this is deprecated in R12 and we need to use frmcmp(Form Compiler) To compile forms,menus,PL/SQL libraries.

Compiling library files 

11.5.9 and 11.5.10

$ f60gen module=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.pll userid=apps/passwd
output_file=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.plx module_type=library
batch=no compile_all=special

R12

$ frmcmp_batch module=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.pll userid=apps/passwd
output_file=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.plx module_type=library
batch=no compile_all=special

Compiling forms

11.5.9 and 11.5.10

[applmgr@oracle vis11appl]$export FORMS60_PATH=$FORMS60_PATH:$AU_TOP/fomrs/US
[applmgr@oracle vis11appl]$ echo $FORMS60_PATH
/apps/appl/vis11appl/au/11.5.0/resource:/apps/appl/vis11appl/au/11.5.0/resource/stub:
/apps/appl/vis11appl/au/11.5.0/forms/US
[applmgr@oracle vis11appl]$

f60gen module=$AU_TOP/forms/US/APXSUMBA.fmb userid=apps/appspasswd
output_file=$PROD_TOP/forms/US/APXSUMBA.fmx module_type=form
batch=no compile_all=special

R12

$ frmcmp_batch module=<path to the fmb file> userid=apps/<apps pwd>
output_file=<path to the fmx file> compile_all=special batch=yes

Compiling reports

Syntax

adrepgen userid=APPS/APPS
source=/<full path of schema top>/reports/<report name>.rdf
dest=/<full path of schema top>/reports/<report name>.rdf stype=rdffile
dtype=rdffile logfile=<report name>.log overwrite=yes batch=yes dunit=character

Example

adrepgen userid=apps/appspasswd source=/test/applmgr/11510/ap/11.5.0/reports/US/APXPBBLD.rdf dest=/test/applmgr/11510/admin/TEST/out/tmp001.rdf stype=rdffile dtype=rdffile logfile=/test/applmgr/11510/admin/TEST/out/adrep001.txt overwrite=yes batch=yes compile_all=yes

****what is the diff between batch=yes and no *******

If batch=yes -> it won’t display the output,If  batch=no -> it will display output

How to compile forms in R12

we have to use frmcmp(Form Compiler) for compiling forms and libraries in Oracle Applications Version R12, But in 11i we need to use f60gen to compile forms and libraries which is deprecated in R12.
1) Login to application server.
2) Go to the directory $AU_TOP/forms/US
3) Place “.fmb” file in binary mode
4) Execute the below command to generate “.fmx”.
frmcmp_batch userid=apps/<apps_paswd> module=<Form_Name>.fmb output_file=<Form_Name>.fmx module_type=form batch=no compile_all=special
EXAMPLE:
frmcmp_batch userid=apps/apps module=RCVRCERC.fmb output_file=$AU_TOP/forms/US/RCVRCERC.fmx module_type=form batch=no compile_all=special

Anonymous Block to End date All Responsibilities with API

DECLARE
   CURSOR c1
   IS
      SELECT fr.responsibility_id,
             fr.application_id,
             fr.data_group_application_id,
             fr.data_group_id,
             fr.menu_id,
             fr.web_host_name,
             fr.web_agent_name,
             fr.group_application_id,
             fr.request_group_id,
             fr.responsibility_key,
             frt.responsibility_name,
             frt.description,
             fr.start_date,
             fr.version
        from fnd_responsibility_tl frt, fnd_responsibility fr
where frt.application_id = 20003
and frt.application_id = fr.application_id
and frt.responsibility_id = fr.responsibility_id
and frt.creation_date < to_date('01-FEB-2017')
--and fr.responsibility_key = 'CSCUS_MANAGER'
;
BEGIN

fnd_global.APPS_INITIALIZE(3918,20420,1);

   FOR i IN c1
   LOOP
      BEGIN
         fnd_responsibility_pkg.
          UPDATE_ROW (
            X_RESPONSIBILITY_ID           => i.responsibility_id,
            X_APPLICATION_ID              => i.application_id,
            X_WEB_HOST_NAME               => i.web_host_name,
            X_WEB_AGENT_NAME              => i.web_agent_name,
            X_DATA_GROUP_APPLICATION_ID   => i.data_group_application_id,
            X_DATA_GROUP_ID               => i.data_group_id,
            X_MENU_ID                     => i.menu_id,
            X_START_DATE                  => i.start_date,
            X_END_DATE                    => SYSDATE - 1,
            X_GROUP_APPLICATION_ID        => i.group_application_id,
            X_REQUEST_GROUP_ID            => i.request_group_id,
            X_VERSION                     => i.version,
            X_RESPONSIBILITY_KEY          => i.responsibility_key,
            X_RESPONSIBILITY_NAME         => i.responsibility_name,
            X_DESCRIPTION                 => i.description,
            X_LAST_UPDATE_DATE            => SYSDATE,
            X_LAST_UPDATED_BY             => fnd_global.user_id,
            X_LAST_UPDATE_LOGIN           => 0);

         COMMIT;

         DBMS_OUTPUT.
          put_line (i.responsibility_name || ' has been updated !!!');
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;

Multi Org Concept

Multi Org Concept

Introduction

Any enterprise has several organization structures. These organizations interact with each other for various purposes. While interacting with other organizations data has to be secure and should be transferred to the correct organization.

What is Multi Org

Multi Org is a feature, which helps us to classify and define various organizations in such a way that hierarchy is maintained and data is secure across organizations. It also decides how transactions flow through different organizations and how those organizations interact with each other.

Types of Organizations

The various organizations that can be defined within Oracle Applications are as follows:

Business Group. The business group represents the highest level in the organization structure, such as the consolidated enterprise, a major division, or an operation company. Oracle Apps provides with a predefined Business Group ‘Setup Business Group’. Reliance Group of Companies is an example of Business Group.

GRE/Legal Entity. The company for which we prepare tax returns. You assign tax identifiers and other legal entity information to this type of organization. If there are several subsidiary companies for which tax returns are filed, each company is a separate legal entity. Reliance Group of Companies consists of several companies like Reliance Industries ltd, Reliance Info-comm, Reliance Energy ltd etc. Each company is a separate Legal Entity.

Note: The Set of Book can contain multiple Legal Entities.

Operating Unit. Each group company will have several branch offices involved with purchasing, order management, shipping execution etc. Each such branch will be an Operating Unit.

It could be a sales office, a division, or a department. An operating unit is associated with a legal entity.

For implementing Oracle Cash Management, Order Management and Shipping Execution, Oracle Payables, Oracle Projects, Oracle Purchasing, Oracle Property Manager, Oracle Receivables and Sales and Marketing Operating Unit is required.

Information is secured by operating unit for these applications. Each user sees information only for their operating unit.

Inventory Organization. An organization for which you track inventory transactions and balances, and/or an organization that manufactures or distributes products. Inventory organization is generally assigned to a manufacturing plant. An operating unit can also be an Inventory Organization. Many branches (operating units) might be Manufacturing Plants and will be attached to an Inventory/Warehouse. Such inventory/warehouses will be Inventory Organizations.

HR Organization. HR organizations represent the basic work structure of any enterprise. They usually represent the functional management, or reporting groups that exist within a business group.

Asset Organization. An asset organization is an organization that allows you to perform asset–related activities for a specific Oracle Assets corporate depreciation book. Any organization (legal entity, operating unit, inventory organization), which has Fixed Assets can be classified as Asset Organization. For implementing Oracle Assets, this type of organization is used.

Secure Access within Multi Org

An individual Operating Unit is assigned to an individual application Responsibility. To see and work with data that is relevant to a specific Operating Unit, users choose the appropriate Responsibility after logging on to Oracle Applications.

The current Multi-Org architecture utilizes database objects like Views to build a security layer that allows you to logically partition all your application data in one database, instead of physically partitioning data with multiple instances and even multiple application installations.

In earlier versions of Oracle Applications data partitioning was done using multiple instances and therefore installation had to be done several times for various operating units.

Note: All Multi Org tables in the database will be suffixed with ‘_ALL’.

HRMS Organization Model 

The business group is the largest organization unit you set up in Human Resources to represent your enterprises as an employer. After defining one or more business groups for your enterprise, you set up one or more Government Reporting Entities (GREs) within each business group. The GRE is the organization that federal, state, and local governments recognize as the employer.

Needless to say, HR does not require Operating Units, Inventory Organization and Asset Organization.

Multi Org Setup

The following are the summarized steps for implementing Multi-Org in Oracle Applications or when converting from a Non Multi-Org environment to a Multi-Org environment.

1.1 Develop an Organization Structure

1.2 Define Set of Books

1.3 Define Locations

1.4 Define Business Groups (Optional)

1.5 Define Responsibilities

1.6 Associate Responsibilities with Business Groups

1.7 Define Organizations

1.8 Define Organization Relationships

1.9 Set MO: Operating Unit Profile Option

1.10 Convert to Multi-Org Architecture

1.11 Define Inventory Organization Security

1.12 Define Inter-company Relations

Technical

How to set Org_id at back-endWe have a API to set the org_id from the backend.

begin
fnd_client_info.set_org_context();
end;

Note:- Org_id indicates the "Operating Unit" in the Business Structure.

How to I know what Org_id is set at back-end

We can use userenv function to know the Org_id set at backend.

select userenv('CLIENT_INFO') from dual;

Why we need to set org_id at back-end

We usually, set org_id at back-end. So that we can get the org_id records from the Multi Organization views.

What happens if I try to query records from Multi-Origination views without setting Org_id at back-endIt will not fetch any records. As, "where" condition fails (org_id = Userenv('CLIENT_INFO'));

Note:- If you set org_id from back-end, then you get org_id value populated by above userenv function.

Is this Multi -Org concept is same for all the Oracle Application versionsNo. Above Technical points are applicable only till 11i version. It is different in Release 12.

MO_GLOBAL-Dive into R12 Multi Org Design

I hope you have already read article Basics of Multi Org in R12 . A few questions come to mind when we think about Multi Org in R12. The best way to analyse those questions is by opening package MO_GLOBAL. Don't worry if you are not yet on R12, package MO_GLOBAL is installed 11.5.10 too.Lets get digging. How is CLIENT_INFO being replaced in R12?   Lets take an example. In pre Release 12, you would have had following methodology for PO_HEADERS_ALL a. A table is created in PO Schema, named PO_HEADERS_ALL b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL c. Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info" But now in R12, following will happen a. A table is created in PO Schema, named PO_HEADERS_ALL b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL c. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL d. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY. This can be double-checked by running SQL select * from all_policies where object_name='PO_HEADERS' e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below SELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id)  Also see **** below, latter Does this mean, if I create a new custom table, I will have to apply RLS [ Row Level Security ] against Custom table too? Yes indeed, if it contains data partitioned by ORG_ID. All you need to do in such case is to assign package function MO_GLOBAL.ORG_SECURITY to that table/synonym/view. Will the Multi Org Row Level security be applied against the table or the synonym or the view? In theory, RLS can be applied against any of the above objects. However in practice, you will apply RLS against Objects in APPS Schema. This means, you will most probably apply RLS on Synonyms. Basically, the Multi Org Views are now replaced by RLS Secured Synonyms.  Hence no code change is  required  where  the pre-R12  Multi-Org  secured  view was being  accessed. The responsibility of securing data as per ORG_ID now lies with RLS [also known as VPD - Virtual Private Database]. I have made changes to my Multi Org Security Profile, by attaching a new Org Hierarchy. Do i need to run any process? Just like we do in HRMS, it is advised that any changes to Security Profiles must be followed by running "Security List Maintenance" What is MO_GLOBAL.INIT Purpose of mo_global.init :- It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used. If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table  mo_glob_org_access_tmp When & from where is mo_global.init called ? This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE Is mo_glob_org_access_tmp table a global temporary table? Yes, it is. Hence after Multi Org is initialised for your session, your session will have X number of records in table mo_glob_org_access_tmp. X is the number of organizations assigned to MO Security profile [view org hierarchy or org list in security profile] What is the purpose of MO_GLOBAL.ORG_SECURITY? The purpose of Row-Level-Security is to hide certain data[based on some conditions]. RLS does so by appending a where clause to the secured object. 1. MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE 2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT ? This procedure has two parameters     p_access_mode           Pass a value "S" in case you want your current session to work against Single ORG_ID           Pass a value of "M" in case you want your current session to work against multiple ORG_ID's     p_org_id           Only applicable if p_access_mode is passed value of "S" In SQL*Plus, I wish to set my session to work against a specific Org [one single org]. How do I do that in R12 SQL>> exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101); In the above case, ORG_ID 101 will be assigned as current org for your session. Internally, following code in blue will be executed by Oracle when you set your context to single Org, dbms_session.set_context('multi_org2', 'current_org_id', 101); **** If the current database session is initialised for Single Org[as in above step], then Where clause appended to object by Row-Level-Security will be WHERE org_id = sys_context('multi_org2','current_org_id') Why will I as a Apps Techie ever use MO_GLOBAL.SET_POLICY_CONTEXT ? Lets say you wish to call an API to create invoices in ORG_ID 101. In case the API does not have a parameter for Org_id, you can do the below a. exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101) b. Call the Invoice API, which will internally read the ORG_ID from MO Current Context. From SQL*Plus, I wish to simulate login to a specific responsibility. How do I do this? a. Call FND_GLOBAL.INITIALIZE This will set your responsibility id, user_id etc b. call MO_GLOBAL.INIT This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.

Enabling & Disabling Concurrent Program Parameter Dynamically

Enabling and disabling a parameter (for taking a valid date) in a concurrent program depending on another parameter

Let’s do this with one example. 
There is a concurrent program named XXX Invoice Generation which has 2 parameters 
Document Type (Others & Sun Trns) and Date 
Date field should be enabled only when the Document Type is Sub Trns or else it should be disabled. Also when the Date parameter is enabled, it should accept only dates

So here we have 2 requirements

1. Enable and Disable the Date parameter based on the value in Document Type parameter
2. Accept only valid dates in Date parameter

So actually to achieve the enable and disable of the Date parameter, we need to have a dummy parameter created which will not be displayed and the same should not be used in the code present in background.

So there will be 3 parameters in the concurrent program

Document Type:

Here in this example the parameter is created as Type and prompt as Document Type which is attached to a value set TEST_TYPE which is of type independent and has the values “Others” and “Sub Trns”.

Dummy1:

This is the Dummy parameter which is created to control the enabling disabling of the Date parameter.
This parameter is attached to a valueset DUMMY1 which is of type independent and has the values “Y”.

Also set default value to populate Y when the Document Type is Sub Trns else NULL
select decode(:$FLEX$.Type,'Sub Trns','Y', null) from dual
So when Document Type is “Sub Trns”, Dummy1 value will be Y or else NULL

Date:

This parameter will behave based on the values in the Dummy1 Parameter

So to achieve this, we need to use a table type valueset where we need to put the validation for validating with Dummy1 parameter value.

So as per the requirement when Document Type is “Sub Trns”, then Date should be enabled which means when Dummy1 value is Y.

Using above setup enabling and disabling of the Date parameter can be achieved.

But we have another requirement is to validate the values entered in Date parameter as a valid date or not.
Normally this could have been validated using a valueset FND_STANDARD_DATE but that is of Validation Type None and we cannot have the enabling and disabling functionality if we use this.
So technically to achieve this, we need to tweek the requirement little bit.

We cannot have a plain valueset which will simply validate whether it’s a valid date or not. Instead, as we are using a table type valueset to trigger the enabling and siabling of the parameter, we have to create and use a view which will hold dates for certain period like in this example, last 6 months and next 6 months date. And pull the dates from the view in the valueset.

So when user enters a date, it will validate with the list of values for date between last and next 6 months and allow to proceed further.

So mentioning below the view created for the same

create or replace view system_date_six_months as

(SELECT (TO_DATE (SYSDATE - 180 + LEVEL, 'DD-MON-RRRR')) date_range

           , (TO_CHAR (SYSDATE - 180 + LEVEL, 'Month, DD RRRR')) date_word

      FROM DUAL CONNECT BY LEVEL <= 360)

Update on achieving the second requirement for validating entered value as a valid date or not in Date parameter.

Normal date condition can be also achieved with valueset type as special and with Validate condition as

FND PLSQL "BEGIN
IF 'AND :$FLEX$.Dummy1' <> 'Y' THEN
NULL;
END IF;
END;"

One of the forum member asked this question "How does one send the body of the email as html instead of text?".

We can set the content-type attribute of message section as 'html/text'. This will treat the email body content as HTML.

Here is an example:

How To Set/Change The Profile Option Value From Back-End

For setting a Profile Option Value from back end we need the Following three parameters;

1. Name of Profile Option
2. Value for the profile option which we want to set.
3. And the level at which we want to set the profile option value.

To get the name of profile option we can use the table  table fnd_profile_options_tl which stores the all the profile option details. 

For example if we want to set the values of the profile option "MO: Operating Unit" then we can find out the name of this profile option using the following Query:

SELECT profile_option_name
  FROM fnd_profile_options_tl
 WHERE user_profile_option_name = 'MO: Operating Unit'

Result:

ORG_ID

Now to find out the organization id for a specific Org we can use the following Query:

SELECT organization_id, NAMEFROM hr_all_organization_units;

Finally using the Profile Option Name, Organization Id and the Level at which we have to set the profile option value we can change the profile option using the following code: 

DECLARE
   stat   BOOLEAN;
BEGIN
   DBMS_OUTPUT.DISABLE;
   DBMS_OUTPUT.ENABLE (100000);
   stat := fnd_profile.SAVE ('ORG_ID', 204, 'SITE');
   IF stat
   THEN
      DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');
   ELSE
      DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');
   END IF;
   COMMIT;
END;

When others then Exception order info

             Return multiple values from function

create type my_type3 as object (a number,b varchar2(10) , c number , d varchar2(10));
/

create or replace function my3(a number,b varchar2 , c number , d varchar2 ) return my_type3
as
mobj my_type3;
begin
mobj := my_type3(a,b,c,d);
return mobj;
end;
/


select obj.x.a , obj.x.b,obj.x.c,obj.x.d from  (select my3(1,'A',2,'B') x from dual) obj;

                    NOCOPY Parameter Hint in PL/SQL

Before we get to understanding the NOCOPY parameter hint in PL/SQL, it would help to first look at PL/SQL parameter modes and the ways in which they pass values in and out of a subprogram.
PL/SQL subprograms have three parameter modes:

1. IN parameter mode is used to send values into the subprogram from the calling program. An IN parameter can only be read, its value cannot be modified inside the subprogram.

1. OUT parameter mode is used to return values from the subprogram to the calling program.

1. IN OUT parameter mode is a hybrid of IN and OUT. It is used to send values into the subprogram, and its value can be modified within the subprogram. On exit, the subprogram returns the updated value of the IN OUT parameter to the calling program.

A major difference between IN, OUT and IN OUT parameter modes is the way in which they pass a parameter to a subprogram: by reference or by value.
Parameter passing methods: by Reference vs. by Value
Pass by reference: The compiler sends to the subprogram a pointer to the actual parameter of the calling program. Any change to the parameter value inside the subprogram reflects in the calling program, since the parameter inside and outside the subprogram refer to the same memory location.
Pass by value: The compiler sends to the subprogram a copy of the actual parameter value from the calling program. A parameter passed by value can be modified independently of its value in the calling program. If the subprogram completes successfully, the parameter value is copied back to the calling program. If an exception occurs, the parameter value is not copied back to the calling program.
This thread explains the concept with a nice analogy – passing by reference is like sharing the URL of this page, while passing by value is like handing out a printout of this page.
By default,
IN parameters are passed by reference.
OUT and IN OUT parameters are passed by value.
Passing by value – and the problem of performance
When pass-by-value parameters (i.e. OUT and IN OUT parameters) are large data structures like collections, records, and object types, the copying of data from subprogram to calling program slows down execution and uses up memory. For smaller data structures, the difference in performance between passing by value vs passing by reference would be insignificant, but the overhead can be large for large data structures.
That’s where the NOCOPY parameter hint comes to the rescue.
What does NOCOPY do?
NOCOPY is a hint to the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value. The use of NOCOPY saves on the processing and memory overhead of copying data from subprogram to calling program.
Why is NOCOPY called a hint, not a command?
NOCOPY is called a hint because unlike with a command, it is not mandatory for the PL/SQL compiler to honor the hint all the time.
The compiler can ignore the PL/SQL NOCOPY hint in such scenarios:

• The parameter passed is an element of an associative array in the calling program.[ This restriction does not apply if the parameter is an entire associative array.]

• The parameter passed has a NOT NULL constraint or a scale specified. e.g. a variable defined as NUMBER(5) in the calling program is used to call process_number(p_num IN OUT NOCOPY NUMBER).

• The parameters in the calling program and subprogram are records, one or both records are declared using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.

• The parameters in the calling program and subprogram are records, the parameter in the calling program is the index of a cursor FOR loop, and constraints on corresponding fields in the records differ.

• An implicit datatype conversion happens in passing the data from the calling program to the subprogram.

Though the PL/SQL compiler opts for the default pass-by-value in spite of the NOCOPY hint in the cases above, no warning message is generated.
Caution! NOCOPY and program execution errors
The downside of using NOCOPY is that you cannot trust the value of NOCOPY parameters if the subprogram results in error.
With normal OUT and IN OUT parameters [pass-by-value], the return values are modified only when the subprogram exits successfully. If the subprogram exits with error, the parameter values do not modified.
But with OUT and IN OUT parameters using NOCOPY, parameter values may get modified even if the subprogram exits with error, since any changes made to the parameters inside the subprogram are immediately copied to actual parameters of the calling program. To complicate things further, because NOCOPY is only a hint, one cannot know for certain if NOCOPY passed parameters to the subprogram by value or by reference. If the subprogram exits with error, the values of the parameters become unpredictable.
So when should you use NOCOPY?
Use NOCOPY when both of these conditions are true:

• the OUT or IN OUT parameters of a subprogram use large data structures causing performance issues in parameter passing

• the calling program can ignore the parameter values returned by the subprogram if the subprogram exits with error

What happens when you enter an Order?

After you enter an order,

•      The items on the order are validated in Oracle Inventory
•             Price is calculated for the items using the pricing engine
•            The availability of items are checked and may be reserved
•            The items are then pick released and shipped to the customer.