Validations. . Purchasing category should not be null. . Agent name should not be null. . Organization should exists for ship to organization. . Address1 should not be null. . Vendor name should not be null. . Vendor site code should not be null. . Bill to location should not be null and should be a valid one. . Ship to location should not be null and should be a valid one at both header and line level. . Charge account should be a valid one.
not open period in AR MODULE
-------------------------------------
Type Bill to Customer Name Item Description Currency Amount
-------------------- ---------------------------------------- ---------------------------------------- ----------- -----------------
Invoice ABC Application Software Sentinel Standard Desktop - Rugged USD 1,900.00
Errors: 1) Invalid Warehouse ID (WAREHOUSE_ID) (207)
2) Unable to derive a gl date for your transaction. Please ensure that your transaction is in a gl period which you
have defined
In Oracle reports we use Formula column, Summary column and Place
Holder column in different scenarios, this post is to avoid confusion
and give clear picture as when and where we need to use these columns.
Formula column
We use formula column to calculate some information dynamically
using information based on the columns of the data model or from the
concurrent program parameters. It is basically used to apply some
custom logic on input data and return some value.
Formula columns can be written using PL/SQL syntax and we can use
pl/sql functions for computation on the data. Formula column will
accept data of Character, Number, or Date type. Inside or outside data group?
If we want to calculate a value for every row place the formula column
in the group of the data model , so that it called every time for every
record and if we want to derive a value at report level place the
formula column outside to the data group.
Formula columns are generally preceded by CF_
to distinguish from other columns. Column names or parameters with a
prefix ‘:’ in formula column are considers as input to a formula
column. Examples:
Comparison of data in two different columns/variables and perform some action.
Using some standard oracle pl/sql functions to find out some values
like, finding out the master org using oracle standard function as
shown below
To execute some sql query to find out email-address of a party using party_id
Note: Formula column should return some or the other value.
Different Columns contained in Report Builder are:
1)Data Columns
2)Formula Columns
3)Summary Columns
4)Placeholder Columns
Data Columns:
Data Columns contain the data values for a report.
Data Columns are corresponding to the table columns included in the select list of a query.
Each column is placed in the group associated with the query.
Formula Columns:
Formula Columns performs user-defined computations.
Formula Columns executes a PL/SQL function and must return a value.
Formula Columns can be placed at the Report or Query level. Formula Columns should not be used to set a parameter’s value.
Formula Columns is denoted by CF_
What is a Formula Column
We apply a formula
or piece of code to calculate a value. In Reports we calculate a value
for every row by considering an input from that row. So we need to
apply a particular logic for every row of the data in the report. In
other cases we want to calculate a value at report level. So we need to
write a pl/sql code in the block format. So we have an option of
writing a function to achieve so. It's called formaula column
Basically we apply a custom logic and return a value.
If
we want to calculate a value for every row place the formula column in
the group of the data model , so that it called every time for every
record and if we want to derive a value at report level place the
formula column outide to the data group.
We have to prefix ':' to the column name which we consider as an input to the formula column.
Let us understand with the example
Let us consider the
followig insurance information. We need to check the total
installments payed and check the difference between insured months and
installments payed. If the difference is 0 then display the customer is
eligible to claim the total Insurance with 40% interest on the total
amount and display the total Insurance amount will be claimed.
Customer_Name
Insured_amount
Insured_months
Insurance_frequency
Installments_payed
Surendra
100000
100
Monthly
100
Nath
200000
100
Monthly
90
Reddy
300000
100
Monthly
80
Tirumala
400000
100
Monthly
70
Venkata
500000
100
Monthly
60
So we write a query
to fetch the Insurer name,
and if the cutomer is eligible to claim the
entire amount diplay that 'Eligible'
and total amount can be claimed.
SELECT * FROM insurance_details.
We select the formula column from the data model and palce
it in the same group as the above query returns the columns.
And write the below code to achieve the desired result.
function CF_ELIGIBLEFormula return Number is
l_amount number;
begin
IF (:Insured_months - :Installments_payed) = 0
THEN
l_amount := :Insured_amount*1.4;
ELSE
l_amount := 0;
END IF;
return(l_amount);
end;
The aboove formula
calculates and returns the value with 40% more else 0. We may have to
control layout to display the total claim amount only when it is
greater than 0.
So the result would like below
Customer_Name
Eligible?
Total_Amount_claim
Surendra
Eligible
140000
Nath
Reddy
Tirumala
Venkata
The above can be
achieved with the help of a select statement also, but we wanted to
consider the simple concept to implement Formula columns
Summary column
Summary columns are used for calculating summary information
like sum, average etc,. on specific columns of a data group. This
column uses a set of predefined oracle aggregate functions. Summary
columns are generally preceded by CS_ to distinguish them from other columns.
The datatype of a summary column depends on the data type of the
source of the summary. If you change the data type of the source
column, the datatype of the summary also changes.
In report layout summary columns should be used out of repeating frames. Summary columns will have only one parameter as input.
Below are the standard functions provided by Oracle for a summary column
Function
Purpose
Average
Calculates the average of the column’s values within the reset group.
Count
Counts the number of records within the reset group.
First
Prints the column’s first value fetched for the reset group.
Last
Prints the column’s last value fetched for the reset group.
Maximum
Calculates the column’s highest value within the reset group.
Minimum
Calculates the column’s lowest value within the reset group.
% of Total
Calculates the column’s percent of the total within the reset group.
Std. Deviation
Calculates the column’s positive square root of the variance for the reset group.
Sum
Calculates the total of the column’s values within the reset group.
Variance
Sums the squares of each column value’s distance from the mean
value of the reset group and divides the total by the number of values
minus 1.
All the above functions work only for number datatype column and return number datatype.
Place Holder column
Place holder column is an empty container at design time which is used to hold some value in run time,
they are like local/global variables which can be used in the logic of
a formula column or in report triggers. Value in the place holder
column can be directly accessible in the report layout. Inside or outside data group?
We can use this place holder columns inside the data group or outside the data group.
Place Holder column can be of Character, Date or Number datatype. Place Holder columns are generally preceded by CP_ to distinguish from other columns.
Example of Usage of PlaceHolder Column
function CF_Cal_MaxPaymentFormula return Number is
begin
if :Payments=:CS_Max_Pay then
:CP_MAX_Payer:=:vendor_name;
end if;
return (1);
end;
--take CF_CalMaxPaymentFormul as group Level in group
--CP_MAX_Payer is a report level out side the group placeholder column with data type Character 80
--CS_Max_Pay is a report level Summary Column having maxiumn Payments using max function
FUNCTION dept_name IS
RETURN VARCHAR2 ( 50 )
BEGIN
DECLARE
t_dname VARCHAR2( 50 );
t_loc VARCHAR2( 50 );
CURSOR c_dept IS
SELECT dname, loc
FROM dept
WHERE deptno = :deptno;
BEGIN
OPEN c_dept;
FETCH c_dept INTO t_dname, t_loc;
IF c_dept%FOUND THEN
CLOSE c_dept;
:place_loc := t_loc; -- PLACE HOLDER COLUMN
ELSE
CLOSE c_dept;
END IF;
RETURN t_dname;
END;
END;
What is a Place Holder Column?
In any programming
language we do declare local or global variables to store the values
and use the values as per needed. In Oracle report tool we use place
holder column for the same purpose. Place Holder column can be of
Character, Date or Number datatype.
We can use this place holder
columns inside the data group or outside the data group. Usually we
need the place holder column when we want to derive multiple values in
a single program unit and return multiple values.
We use a
formula column to derive few values for a particular record in the data
group and want to return multiple values. Since formula column is based
on a function and can return only a single value we use multiple place
holder columns to store multiple values so that we can display then on
the report.
CREATE OR REPLACE PROCEDURE apps.xx_file_copy_test (
errbuf OUT VARCHAR2
,retcode OUT NUMBER
)
AS
msg VARCHAR2 (4000) := NULL;
ufilehndl UTL_FILE.file_type;
ufilehnd2 UTL_FILE.file_type;
sfilename VARCHAR2 (30) := 'FND_USER.txt';
l_source_dir VARCHAR2 (500);
l_destination_dir VARCHAR2 (500);
i NUMBER := 0;
desfilename VARCHAR2 (500)
:= TO_CHAR (SYSDATE, 'MMDDYYHH24MISS')
|| '.txt';
BEGIN
desfilename := 'UTLFILE' || TO_CHAR (SYSDATE, 'MMDDYYHH24MISS') || '.txt';
ufilehndl := UTL_FILE.fopen ('/u005/data/XXX/appl/temp', sfilename, 'r');
ufilehnd2 :=
UTL_FILE.fopen ('/u004/XXXX/XXX/appl/data/in', desfilename, 'w');
LOOP
BEGIN
i := i + 1;
UTL_FILE.get_line (ufilehndl, msg);
fnd_file.put_line (fnd_file.LOG, 'Line no ' || i || ' ' || msg);
UTL_FILE.put_line (ufilehnd2, 'Line no ' || i || ' ' || msg);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
First time while registering the concurrent programs we must forget
the 2 mandatory parameters. After the error we can know that we need to
you those mandatory parameters those are ERRBUF and RETCODE.
These are really good if you use properly.
ERRBUF: It return the error message. For you program if you get any
error in exception block you can assign the error message to this
parameter. This error message you can see after concurrent program run
go to details button it will open details in that Completion Text filed
will show your errbuf.
RETCODE: This parameter returns the status of the concurrent program.
0- Success --Completed
1- Warning -- Yellow color
2- Error -- Red
These parameters we call as a first parameters for the program.
Ex:
Create procedure concurren1(ERRBUF out varchar2, RETCODE out varchar2, v_order_id in varchar2)
as
begin...
...
begin
..
exception
when no_data_found then
retcode := 1;
errbuf:= 'No data found for this query';
end;
...
...
...
retcode:= 0;
commit;
exception
when others then
COMMIT; DBMS_OUTPUT.put_line ( 'Password is changed successfully for the user ' || v_user_name ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'Error encountered while setting new password to the user and the error is ' || SQLERRM ); END;
Output after executing the API: Password is changed successfully for the user TEST_USER
