> DEVELOP THE PLSQL PACKAGE AND TRANSFER TO THE SERVER.
> OUTBOUND MEANS EXTRACT TABLE DATA TO TEXT,EXCEL,HTML ETC.
> OTHER MEANING DATABASE TABLE TO FLAT FILE.
> REGISTER OUR PROGRAM AS CONCURRENT PROGRAM.
> ATTACH TO REQUEST GROUP.
> SUBMIT THE PROGRAM.
> CHECK THE FLAT FILE.
PATH LOCATION:(AFTER THE SUBMIT THE PROGRAM OUR FILE STORE IN THIS PATH)
CHOOSE ANYONE PATH c:\temp, c:\temp, D:\oracle\VIS\db\tech_st\11.1.0\plsql\temp, D:\oracle\VIS\db\tech_st\11.1.0\appsutil\outbound\VIS_apps, c:\temp
OUTBOUND CODE:
CREATE OR REPLACE PROCEDURE OUTBOUND_QTY(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2)
IS
QTY UTL_FILE.FILE_TYPE; --DECLARE UTL LOCAL VARIABLE
PATH VARCHAR2(200);
FORMAT VARCHAR2(200);
CURSOR CAGE IS SELECT ITM.SEGMENT1,ITM.DESCRIPTION,QTY.TRANSACTION_QUANTITY,QTY.SUBINVENTORY_CODE FROM MTL_SYSTEM_ITEMS_B ITM,MTL_ONHAND_QUANTITIES QTY
WHERE ITM.ORGANIZATION_ID=QTY.ORGANIZATION_ID
AND ITM.SEGMENT1 LIKE 'SAINT%';
BEGIN
FORMAT := 'QTY_ITEMS.txt';
PATH := 'D:\oracle\VIS\db\tech_st\11.1.0\appsutil\outbound\VIS_apps';
QTY := UTL_FILE.FOPEN(PATH,FORMAT,'W'); --OPEN THE UTL FILE DATA MODE ARE WRITE AND READ
FOR LION IN CAGE LOOP
UTL_FILE.PUT_LINE(QTY,LION.SEGMENT1||' '|| LION.DESCRIPTION||' '||LION.TRANSACTION_QUANTITY||' '||LION.SUBINVENTORY_CODE);
END LOOP;
UTL_FILE.FCLOSE(QTY); --CLOSE THE UTL FILE
END;
/
Supplier Conversion / Interface :
=================================
-- Used to import the Suppliers from Legacy to Oracle Applications
-- Using Open Interface Approach
Using API Approach
COMMIT;
dbms_output.put_line('API Return Status : '||v_return_status);
dbms_output.put_line('v_msg_data : '||v_msg_data);
dbms_output.put_line('v_msg_count : '||v_msg_count);
dbms_output.put_line('Vendor Id : '||v_vendor_id);
dbms_output.put_line('Party Id : '||v_party_id);
for i in 1..v_msg_count
loop
v_msg := apps.fnd_msg_pub.Get ( p_msg_index => i, p_encoded => FND_API.G_TRUE );
dbms_output.put_line('Error Message : '||v_msg);
end loop;
dbms_output.put_line('API Return Status : '||v_site_return_status);
dbms_output.put_line('v_msg_data : '||v_site_msg_data);
dbms_output.put_line('v_msg_count : '||v_site_msg_count);
dbms_output.put_line('Vendor Site Id : '||v_vendor_site_id);
dbms_output.put_line('Party Site Id : '||v_party_site_id);
dbms_output.put_line('Location Id : '||v_location_id);
for i in 1..v_site_msg_count
loop
v_site_msg := apps.fnd_msg_pub.Get ( p_msg_index => i, p_encoded => FND_API.G_TRUE );
dbms_output.put_line('Error Message : '||v_site_msg);
end loop;
XML Publisher Reports :
----------------------------
-- Is a Template based solustion for Oracle Reports
-- Template based Reporting Tool
-- This is an Independent (Not depend on any Code)
Depency on .xml data file
-- template builder to add add-ins
we can generate the .xml data file using many methods.
1. Using .rdf
2. PLSQL Method
3. Data Templates / BI
4. HTML Commands
5. Note pad Methods
6. PDF Method
Process :
-----------
1. We have to generate the .xml data file
2. Use the file as input source to the LayOut
(Layout will be designed in the MS Word -> .rtf)
3. Will see the Output in required format
Process : (Registration) (Using .rdf)
----------
1. We have to develope the .rdf by placing our code in Data Model
(i.e., No need to design the Lay Out in .rdf)
2. Move the .rdf file in to server ($Custom_TOP/reports/US/)
3. Login to Applications
Go to System Administrator to create Executable
Create Concurrent Program by attaching the Executable
** Concurrent Program Output method should be XML
4. Add the Conc Program to the Request Group
Navigate the Responsibility, Submit the Request
Get the xml data file.
5. Design the Layout in MS Word by using the .xml data file as source
6. Save the File as .rtf (Rich Text Format)
7. Go to XML Publisher Administrator Responsibility to create Data Definition
While creating Data Definition,
we have to give Conc Program Short Name to the "Code" Field.
8. Create Template by giving the Data Definition Details
and Attach the .rtf file
Diff. b/n .rdf and .rtf :
-------------------------- RDF RTF ----- -----
1. Layout Model is dependent on |1. Layout Model is independent
Data model Code | (It will look for .xml data file)
2. We can see output in one |2. can see the output in different output
format(by default) | formats
System parameters
3. we can not use SQL Functions | 3. We can use SQL Functions at Source
in rdf Lay out source field field define level
define level
4. Only one Layout will be there | 4. we can define multiple .rtf files (Multiple .rtf files can be attached against to the Single Template) (while submitting the conc prog we have to select the .rtf file)
5. Not User Friendly | 5. User Friendly
AP Invoice Report Using .rdf Method :
--------------------------------------------
OU Name Invoice Number Invoice Type Supplier Name Supp Site
Invoice Amount
Parameter :
--------------
* OU Name
Supplier Name
Anchor Type Declarations :
--------------------------
%type - Used to give specific column data type to a Variable
%rowtype - Used to give entire row data type to a Variable
Examples : %type
----------
declare
v_vendor_nameap_suppliers.vendor_name%type;
begin
selectvendor_name
intov_vendor_name
fromap_suppliers
wherevendor_id = 600;
dbms_output.put_line('Vendor Name : '||v_vendor_name);
end;
Examples : %rowtype
----------
declare
v_vendor_typeap_suppliers%rowtype;
begin
selectvendor_name,
segment1, terms_id, PAY_GROUP_LOOKUP_CODE
intov_vendor_type.vendor_name, v_vendor_type.segment1, v_vendor_type.terms_id, v_vendor_type.PAY_GROUP_LOOKUP_CODE
fromap_suppliers
wherevendor_id = 600;
dbms_output.put_line('Vendor Name : '||v_vendor_type.vendor_name);
dbms_output.put_line('Vendor Number : '||v_vendor_type.segment1);
dbms_output.put_line('Terms ID : '||v_vendor_type.terms_id);
dbms_output.put_line('Pay Group Lookup Code : '||v_vendor_type.PAY_GROUP_LOOKUP_CODE);
end;
PLSQL Collections :
-------------------
-- Composite Data Types / User defined Data types
-- Collection of multiple data types as a Data Type
PLSQL Record Types
PLSQL Table Types (Index by Table)
Nested Table
VARRAY
PLSQL Record Type :
-------------------
-- Collection of Multiple Data Types each with its own name and own attribute
values
Syntax :
--------
TYPE <type_name> is RECORD (col list with Data Type );
Ex :
----
TYPE xx_supp_rec IS RECORD (vendor_name ap_suppliers.vendor_name%type, segment1 varchar2(100), terms_name ap_terms_tl.name%type, PAY_GROUP_LOOKUP_CODE varchar2(100) );
v_supp_rec xx_supp_rec;
Examples : (RECORD TYPE)
----------
declare
TYPE xx_supp_rec IS RECORD (vendor_name ap_suppliers.vendor_name%type, segment1 varchar2(100), terms_name ap_terms_tl.name%type, PAY_GROUP_LOOKUP_CODE varchar2(100) );
v_supp_rec xx_supp_rec;
begin
selectvendor_name,
segment1, (select name from ap_terms_tl where term_id=aps.terms_id) terms_name, PAY_GROUP_LOOKUP_CODE
intov_supp_rec.vendor_name, v_supp_rec.segment1, v_supp_rec.terms_name, v_supp_rec.PAY_GROUP_LOOKUP_CODE
fromap_suppliers aps
wherevendor_id = 600;
dbms_output.put_line('Vendor Name : '||v_supp_rec.vendor_name);
dbms_output.put_line('Vendor Number : '||v_supp_rec.segment1);
dbms_output.put_line('Terms Name : '||v_supp_rec.terms_name);
dbms_output.put_line('Pay Group Lookup Code : '||v_supp_rec.PAY_GROUP_LOOKUP_CODE);
end;
Examples : (RECORD TYPE with Loop)
----------
declare
cursor c_supp_cur
is
selectvendor_name,
segment1, (select name from ap_terms_tl where term_id=aps.terms_id) terms_name, PAY_GROUP_LOOKUP_CODE
fromap_suppliers aps
whererownum < 11;
TYPE xx_supp_rec IS RECORD (vendor_name ap_suppliers.vendor_name%type, segment1 varchar2(100), terms_name ap_terms_tl.name%type, PAY_GROUP_LOOKUP_CODE varchar2(100), DESCRIPTION ap_terms_tl.DESCRIPTION%type );
v_supp_rec xx_supp_rec;
begin
for i in c_supp_cur
loop
v_supp_rec.vendor_name := i.vendor_name;
v_supp_rec.segment1 := i.segment1;
v_supp_rec.terms_name := i.terms_name;
v_supp_rec.PAY_GROUP_LOOKUP_CODE := i.PAY_GROUP_LOOKUP_CODE; selectDESCRIPTION into v_supp_rec.DESCRIPTION fromap_terms_tl wherename = i.terms_name;
dbms_output.put_line('Vendor Name : '||v_supp_rec.vendor_name);
dbms_output.put_line('Vendor Number : '||v_supp_rec.segment1);
dbms_output.put_line('Terms Name : '||v_supp_rec.terms_name);
dbms_output.put_line('Terms Desc : '||v_supp_rec.DESCRIPTION);
dbms_output.put_line('Pay Group Lookup Code : '||v_supp_rec.PAY_GROUP_LOOKUP_CODE);
end loop;
end;
PLSQL Table Types :
-------------------
-- Object of Type Table is called PLSQL Table
-- Referenced with Index By
Syntax :
--------
TYPE <type_name> IS TABLE OF <data type>
INDEX BY DATATYPE;
Index By :
----------
pls_interger
varchar2
date
Ex :
----
declare
type xx_emp_tab is TABLE of varchar2
index by pls_integer;
v_emp_tab xx_emp_tab;
begin
selectename
intov_emp_tab
fromemp
whereempno = 7839;
end;
Ex :
----
declare
TYPE xx_supp_rec IS RECORD (vendor_name ap_suppliers.vendor_name%type, segment1 varchar2(100), terms_name ap_terms_tl.name%type, PAY_GROUP_LOOKUP_CODE varchar2(100) );
type xx_sup_tab is TABLE of xx_supp_rec
index by pls_integer;
v_supp_tab xx_sup_tab;
begin
selectvendor_name,
segment1, (select name from ap_terms_tl where term_id=aps.terms_id) terms_name, PAY_GROUP_LOOKUP_CODE
intov_supp_tab(1).vendor_name, v_supp_tab(1).segment1, v_supp_tab(1).terms_name, v_supp_tab(1).PAY_GROUP_LOOKUP_CODE
fromap_suppliers aps
wherevendor_id = 600;
dbms_output.put_line('Vendor Name : '||v_supp_tab(1).vendor_name);
dbms_output.put_line('Vendor Number : '||v_supp_tab(1).segment1);
dbms_output.put_line('Terms Name : '||v_supp_tab(1).terms_name);
dbms_output.put_line('Pay Group Lookup Code : '||v_supp_tab(1).PAY_GROUP_LOOKUP_CODE);
end;
Table Type Attributes :
-----------------------
FIRST
LAST
COUNT
DELETE
EXTEND
etc..
if v_supp_tab.COUNT >0 then v_supp_tab.delete;
end if;
- NO_DATA_FOUND:
--------------------------
declare
a number;
begin
select empno into a from emp where deptno=100;
dbms_output.put_line(a);
exception
when no_data_found then
dbms_output.put_line('DATA NOT FOUND');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
END;
O/P: DATA NOT FOUND
SQLCODE: 100
SQLERRM:ORA-01403: no data found
- TOO_MANY_ROWS:
---------------------------
declare
a number;
begin
select empno into a from emp where deptno=10;
dbms_output.put_line(a);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('WE CANT EXECUTE MORE THAN ONE ROW');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
end;
O/P: WE CANT EXECUTE MORE THAN ONE ROW
CODE: 1433
-- DUP_VAL_ON_INDEX:
------------------------------
begin
INSERT INTO DEPT VALUES(10,'DATA','NO');
exception
when DUP_VAL_ON_INDEX then
dbms_output.put_line('U CANOT ENTER DUPLICATE VALUE IN DEPTNO');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
end;
O/P: U CANOT ENTER DUPLICATE VALUE IN DEPTNO
CODE: 0001
-- ZERO_DIVIDE:
---------------------
DECLARE
A NUMBER;
begin
A:=10/0;
exception
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('DONT ENTER ZEROS IN DIVISION POSITION');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
when NO_DATA_FOUND then
dbms_output.put_line('U CANOT ENTER DUPLICATE VALUE IN DEPTNO')
WHEN DUP_VAL_ON_INDEX THEN
dbms_output.put_line('ANOTHER EXCEPTION');
WHEN OTHERS THEN
dbms_output.put_line('ANOTHER TWO');
end;
O/P: DONT ENTER ZEROS IN DIVISION POSITION
ERRCODE:-1476
-- ERRORS IN EXCEPTIONS:
1 begin
2 INSERT INTO DEPT VALUES(10,'DATA','NO');
3 exception
4 WHEN OTHERS THEN
5 dbms_output.put_line('ANOTHER TWO');
6 when NO_DATA_FOUND then
7 dbms_output.put_line('U CANOT ENTER DUPLICATE VALUE IN DEPTNO');
8 WHEN DUP_VAL_N_INDEX THEN
9 dbms_output.put_line('ANOTHER EXCEPTION');
10* end;
SQL> /
WHEN OTHERS THEN
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
-- VALUE_ERROR:
---------------------
DECLARE
A NUMBER;
begin
SELECT ENAME INTO A FROM EMP WHERE ENAME LIKE 'KIN%';
exception
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('DONT ENTER ZEROS IN DIVISION POSITION');
when NO_DATA_FOUND then
dbms_output.put_line('U CANOT ENTER DUPLICATE VALUE IN DEPTNO');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('DATA TYPE ERROR');
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
WHEN DUP_VAL_ON_INDEX THEN
dbms_output.put_line('ANOTHER EXCEPTION');
WHEN OTHERS THEN
dbms_output.put_line('ANOTHER TWO');
end;
O/P:- DATA TYPE ERROR
ERRCODE:-6502
DECLARE
L_SAL EMP.SAL%TYPE;
BEGIN
INSERT INTO DEPT VALUES(13,'RAISE','CURSOR');
SELECT SAL INTO L_SAL FROM EMP WHERE EMPNO=7788;
IF L_SAL>2000 THEN
RAISE_APPLICATION_ERROR(-20150,'SAL IS HIGH');
END IF;
DBMS_OUTPUT.PUT_LINE('SAL IS'||L_SAL);
END;
DECLARE
SEGMEN MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
BEGIN
DELETE FROM DEPT WHERE DEPTNO=90;
SELECT SEGMENT1 INTO SEGMEN FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1 LIKE 'SAINT%';
IF SEGMEN='SAINT BERNARD' THEN
RAISE_APPLICATION_ERROR(-20150,'USER NAME EXITS');
END IF;
DBMS_OUTPUT.PUT_LINE('SAL IS'||SEGMEN);
END;
To Identify Interface Tables :
-----------------------------------
SELECT * FROM ALL_OBJECTS
WHERE OWNER = 'AP'
AND OBJECT_NAME LIKE 'AP%INV%INTERFACE%'
AND OBJECT_TYPE = 'TABLE'
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
Single Insert Process :
--------------------------
1. Identify the Mandatory columns from the Front End
At Header :
-------------
Invoice Type
Supplier Name
Supplier Number
Supplier Site
Invoice Date
Invoice Num
Invoice Currency Code
Invoice Amount
GL Date
Payment Currency Code
Terms Date
Terms Name
Payment Method
At Lines :
-----------
Line Num
Line Type
Line Amount
GL Date
At Distributions :
--------------------
Dist Num
Dist Type
Dist Amount
GL Date
Account
Note :
-------
When we are finding the Request Group name from Responsibility window,
Note the Request Group name along with Application
D:\oracle\VIS\apps\apps_st\appl\ap\12.0.0\bin
AP_INV_HDR_DATA.dat
AP_INV_LINE_DATA.dat
Interview Questions:
------------------------
1. Interface Process
(Note: Know the Tables(Int&Base), Standard Program with Parameters,
Validations for all Interfaces and Conversions)
2. How to process Error Records
-- Generate a Report using Queries or Scripts
-- Give the rejected data to the Users or Clients
-- Users validate that data and correct it
and they will place the new data/files into the server
-- Again run the programs for re process
DBA's will place the files into server
3. how will u get the flat file
custom_top/data/in/
1)Report Builder is one of the Oracle GUI Tool
Graphical user Interface
2)By using this we can develop simple and Complex reports
3)There are two ways we can develop the report
1)Wizard base
2)Manuval
4) 1)Data Model
2)Layout model
DataModel will be used to select the data from Database,
SELECT Statemen and Formulas,Summary functions and so on.......
Layout model will used to displays the data model fields in the desired
format.
Layout Model Objects:
1)Text : Will be used to display the standard text information
like titles,prompts,headings,Address............... 2)Frame : Will be used to display the layout objects only one time
like totals,titles,Headings and so on
3)Repeating Frame: Will be used to display the objects multiple times
like database columns,Sub Totals,Page Totals.......
4)Field : Will be used to display the Data base columns,variables and
so on.
First page : Employee Information Report
Second Page : Empno Ename Sal hiredate Comm Last page : End of the report
Summary Column : If we want to apply summary functions then we will
go for summary columns like Average,count,min,max and so on.
1)Group level : Executed for every record in the group 2)Report Level : executed only once for entire report.
Empno Ename Sal HireDate
Total Emp:
Total Sal:
Min Salary :
Max Salary :
Formula Columns : When we want implement some logic and return the
value then we will use formula columns.
It is a PL/SQL Function. where we can write PL/SQL code
we can return only one value. 1)Group Level 2)Report Level
Empno Ename Sal Grade (If sal >5000 'A'
(If sal<=5000 and >3000 'B')
(if sal<=3000 then 'C')
Total Emp:
Total Salary:
Average Emp Salary : (Total salary/TotalEmp)
Place Holder Columns:
A datatype which stores some value and we can use this across the
report.
It will work lika Global variable in the reports.
If we want to return more than one value from the Formula Columns then
it will be used.
Empno Ename Sal HireDate Comm TotalSal(SAl+Comm) Tax
Tax : if (totalsal>5000 then 7% tax
(Totalsal>3000 and <=5000 then 4%tax
(Totalsal<=3000 then 2% tax)
Empno Ename Sal Comm Tax Netsalary
Comm := 15% sal
tax : 7 % (sal+comm)
netsal : sal+comm-tax
Triggers :
Format Trigger
Validation Trigger
Action Trigger
Report Triggers
Format Trigger : To Hide or display the layout objects dynamically it will be
used.
layout objects means frame,repeating frame,field,............ Action Triggers: If we want to develop drill down reports then we will use
Action Triggers.
Place the Button in the layout when user press the button we can
execute some PL/SQL code. Report Triggers: 5 Types which will fired automatically when we run the
report Sequence Order
1)Before Parameter Form
2)after Parameter Form
3)Before report
4)Between pages
5)After REport
Before Parameter Form : Will Execute before the parameter form is going to
open.
To assign any default values for the parameter. After Parameter Form : After the Parameters are entered in the form and
submit it will be executed.
To change the Parameter values dynamically
And To populate the Lexical parameter values.
Before Report : It will be executed before report is executing and select
data from database.
Between Pages : When cursor moves from one page to another page it will be
executed .
It will be executed (n-1) times. After Report : after completion of the report. once the output is reach the
destination (Printer,file,fax,e-mail) then it will be executed.
Parameters :
1)System Parameters
2)User Defined Parameters 1)Bind
2)Lexical
Bind parameters will used to pass the the value into the query in the WHERE
Clause
Lexical parameters will be used to replace the string in side of the query
in any clause.
From Deptno
To Deptno
Deptno Empno Ename Sal HireDate
Total Emp : should display if >=5
Total Salary : Should display if >=7000
Before Parameter form is open Deptno = 10
To Deptno = 20
Sort By : Empno
Sal
Deptno
SELECT * FROM EMP
&P_ORDER_BY
After Parameter Form Trigger
From Deptno
To Deptno
if user pass the deptno then select between that
otherwise select all the dept employees data
SELECT * FROM EMP
WHERE DEPTNO BETWEEN :P_FROM AND :P_TO
If P_from is entered then it should retrieve all dept from the specified dept
if p_to is entered then it whould retireve upto specified deptno.
WHERE DEPTNO>=:P_FROM
WHERE DEPTNO<=:P_TO
Bind Lexical
1)To pass the Values into the WHERE 1) To Replace the string in the
clause query.
2)We will use the symbol ":" 2)We will use the symbol "&"
3)Can be used only in the WHERE 3)In Any Clause
clause
System Parameters :
DESFORMAT : Html,pdf.....
DESNAME : Name of the Printer,E-mail,Fax,File
DESTYPE : File,Screen,Print,Fax,E-mail
MODE : Bitmap,Character Confine Mode :
Lock mode if it is on then we can not take
the child object out of the Parent Object.If it is
off then we can take out of the parent. FlexMode:
If Flex mode is on . If we Incr/Decr the child
object automatically parent object also incr/decr.
If it is off then Only child object will be decr/Incr.
Program Unit :
Is a PL/SQL objects(Procedures,Functions,Packages)
which will be stored in the Report we can use only in
the Current Report.
Program units PL/SQL Objects
1)Will be stored in the file 1)Stored in the Database.
2)Can be used only in the 2)We can use any report
Current Report
3)Improve the Performance 3)It takes more time
Libraries : Group of Program units and Libraries
We can attach the Attach the Library to another report
and use the Program units.
We can not attach the Program units to another reports
directley that's why we will PL/SQL Library to attach
.pll - Source Code - Program Link Library
.plx - Executable Code - Program Link Executable
Data block : Database Data block : Select Data from table
Control Data block : Without table
Database Data block : Yes
Record Groups :
A record group is an internal Form Builder data structure
that has a column/row framework similar to a database table
Static : Enter the values manually Query based : based on the select statement get the values Non-Query based : Change the values dynamically. LOV: List of Value which will be used to provide the list of values
in the fields user can select the value from the list.
Without Record group we can not create LOV.
Once LOV is created it will be attached to the field by selecting
the property called 'List Of Values'
Note : CREATE_GROUP by using this procedure we can create the Record
group dynamically.
Property Classes Visual Attributes
1)Group of Properties 1)Group of Look and Feel properties Font,Color,Size.....
2)We can not change dynamically 2)Visual attributes can be changed
SET_VA_PROPERTY Procedure
3)We can define the Triggers for 3)We can not define
Property Class
Note : If same property given in both Property Class and Visual
Attribute then Visual Attribute will take the High priority.
Popup Menu : Will be created To populate group of menu options
when we select the Right click option.
Once popup is created then it will be assigned to field or Canvas.
Alerts : Alerts are nothing but Messages which will be used to display
message boxes based on the user input we can change the Form
execution.
Triggers Levels :
Form Level
Data Block Level
Record Level
Item Level Form Level : it Will be executed once for the entire Form. Block Level : It Will be executed when we enter into the data block. Record level : It Will be executed for every record. Item Level : It Will be executed for the Particular Item.
WHEN-VALIDATE-RECORD : when user change the value in the fields then
it will be fired. WHEN-NEW-RECORD-INSTANCE : When user create new record then it will be
fired.
Transactional Triggers: When user make some Transaction like
(INSERT,UPDATE,DELETE,SELECT,COMMIT,ROLLBACK,QUERY,CLOSE)
That time these triggers will be fired.
WHEN-VALIDATE-ITEM - At any level
WHEN-VALIDATE-RECORD - At Block Level
When we change the value in the item (or) record it will be
fired.
Query Time Trigers: Pre-Query : Trigger will be fired before query the data
Only once Post-Query : Will be fired after query the data multiple times
for every record once it will be fired.
To Populate Non-Database Items.
Navigational Triggers :
When we navigate from one form to another form
from one data block to another data block
from one record to another record these triggers
from one field to another field then these triggers
will be fired
Deferred : yes : We must place the cursor in the detail block Automatic Query : yes : Data will be retrieved automatically in the detail
block.
Key Triggers:
Key-Fun : A Key-Fun trigger fires when an operator presses
the associated key key-Others :A Key-Others trigger is associated with all keys
that can have key triggers associated with
them but are not currently defined by function key triggers
Example 3:
WHERE vendor_id between :fromif and :toid
Attach the Parameters and Conditions:
===================== ================
1)Goto Toole Menu =>Parameters=>New
Select the Column Name and Define the Parameter
Use Parameter in the Condition:
================================
1)Goto Tools Meneu=>Conditions=>New
Selct Condition , Item And Value
2)If we would like to provide multiple conditions by using either AND (or) OR
we will use the Add Button
3)Double click on the AND it will be changed to OR
LOV Creation:
============
Goto Admin Edition select Business Area => Folder
Right Click=> Item Classes =>Select column Name.
2)Goto the Desktop Edition Parameters attach the parameter
will be displayed LOV.
Display Total:
==============
1)Goto Tools=>Total option => New button
select group function and Column Name
select either Grand Total or Sub Total (If it subtotal At what level)
2)Enter the Prompt as per that value will be displayed
Disply Formula Columns:
=======================
1)Toolsmenu=>Calculations options=>New
select the Function an pass the Aruments select column in the Workbook
Layout
Register UserDefined Functions:
===============================
1)Goto SQl Prompt create Function with return value compile succesfully.
2)Goto Admin Edition => Toole=>Register PL/SQL Function=>
Enter the Function Name (If package function enter package name also)
3)Goto Arguments tab enter the Parameter details also.
4)Select Validiate button and Ok button.
5)goto Desktop Edition Tools=>Calculations=>Allfunction
where we can find the function whatever we have registered.
6)Select the function pass the parameter we can get the result.
Custom Folder : SQL query WHERE ORDER BY Having Group By
1)Connect to the Admin Edition
2)Create Business Area and Create Simple Folder
3)Connect to the Desktop Edition and Create new Workbook by select
the Business Area whatever we have created.
4)Once we select the Business Area it will shows the folders which we have in the Business Area
select folder and Columns => Select Next button and Finish Button
Note:- In the Business Area we have more than one Simple folder we can select only one
simple folder columns not multiple folder
2)If we want to select columns from Multiple folders then we will go for creation of
Complex Folders.
1)Create Two Simple Folders in Admin Edition
2)Select the Primary key from Simple Folder => Right click=>New join
Select the Join Condition.
Note: for Outer join select Options Button
for multiple join conditions select Multi Item Button
3)System will create the join condition between the Simple folder we can find that end of the
Simple folder.
4)Goto Desktop Edition and Create Workbook by select Business Area select the columns from
multiple folders and design the Format.
Sort Options :
=============
1)Right Click on the Workbook column name => Item Properties =>to change the Column Name
=> Group Sort =>To make grouping => Sort Low to High =>Assending order => Sort high to Low =>Desending order => Foramt Data => To format the Data => Format Heading => Format the Heading => Format Exception =>To give the Format Conditions
Add Title to the Workbook:
=======================
1)Goto Tools Menu=> Options=> Table Tab=>Check the Title Check Box
Double click on the Title Text enter the Title
2)Select the Button called Insert button to Inser Date , Time, Parameter valued and so on
Custom Folder:
=============== SQL Query
1)Goto Admin Edition
2)Rigjht click Business Ares => New Custom Folder
=> New Folder From Database to Create Simple Folder
3)Goto Desktop dition and select the Business Area
we can find both Simple and Custom Folder
Note: Once we select column from Custom Folder Simple folders will be disabled we can not
select the Colmn from simple folders.
1)creation of Custom Folder:
==============================
1)Goto Administrator Edition Select the Business area =>Right Click
select the option called New Custom Folder
2)Enter the Select Statement whatever is valid
3)Define the Workbook in the Desktop Edition by selecting the Business area.
Grouping the Records:
=====================
If we would like to apply any group by clause then we will select Column name
=>Right Click => Group Sort which will group the records based on the column
Attach the Title
================
goto the Tools menu Options field =Select the Tab called Table check the
check box called Title => Double click on that and enter the Title.
Select Insert button to insert the Data , Time and as well as Parameter Values.
Attach the parameters:
=====================
Select Tools Menu Parameters option
select the new button select the column name for comparision select the relation
and specifuy the condition.
To Display the parameter value in the Title then goto title Select Insert button
insert the Parameter name .
Item Classes :
==============
Item class is nothing but list of values which will be attach to the parameter to
select single value or multiple values.
Goto Admin Edition select Item classes tab =>Right Click select Item classes
select the Folder . and select the column name
so that we can use that LOV for that folder and as well as for the selected column.
Go To DeskTop Edition attach the parameter
Check the check box called Allow users to enter multiple values.
so that user can select multiple values at a time otherwise single value.
Copolex Folders :
=================
Joins Simple Folders by using Join Condition
For Ex :ap_invoices_all simple folder
ap_invocie_distributions_all Simple Folder
Use the Join condition between both the Foler select data fro both the tables.
Goto Desktop edition select the columns from both the folders whatever are required
and design the work sheet
Display the Total:
=================
select Toole menu Totals option to disply the Grand total and as well as the
Sub Total
select the Group function and column Name and select either Grand total or Subtotal.
Steps for Creating a Workbook and Graph In Discoverer
Connect to
administrator Edition with APPS/APPS@VIS
Select Create a new
Business Area From Load Wizard.
Click on the On-Line
Dictionary (By Default), Click Next
Select the Database
Link <Default Database>
Select the user from
the list. Ex. APPS
Click on Next Button
In the Available list
of APPS all the tables and views will be displayed Select the
corresponding table/view on which workbook has to be created.
Select the table and
move it to Selected List
Click Next Button
The following screen
will be displayed
Select the Options as given in the screen.
·Click Next
·Give the name to business area and Also a Meaningful Description
·Click on Finish Button
·Select the Tool -> Security
·Select the Business Area which has been created
·Move the Available Users/roles to Selected User/roles
·Click on the User -> Business Area
·Select User/role – APPS
·Select the Business Area – REGION and Click on the Allow Administration
Check box.
·Click on Apply
·Click OK Button
·Click on Tools -> Privileges
·In the Privileges table give the Appropriate privileges for the user
like Create/Edit business Area, Format Business Area Etc.
·Click on Apply Button
·Click OK Button
Connect to Discover Desktop Edition – APPS/APPS@VIS
Once connected to
Discoverer Desktop Workbook Wizard will be opened
Select Create a new
workbook Option
Select Table
Click on Next
Select the Business
area (REGION) which has been created in Admin Edition
·Select the XX_MYREGION_COUNTRY
·Select the Columns which You want to display in workbook
·Move it to Selected List.
·Click Next
·Click on the Show Page Items Checkbox
·Drag the Region CD and move it to page items
·Click on the Hide Duplicate Check box also
·Click on the Next
·If any condition need to be specified can be specified in workbook
wizard Step 4
·Click Next
·In Workbook wizard of step 5 Sorting Order can be specified
·Click Next
·In Workbook wizard of step 6 calculation like Totals, % Etc can be
specified
·Click on Finish
·Workbook will be created
·Now Click on the graph
·Select the Type of the graph
·Click on the Next
·Click on Finish
·Graph will be created.
·Save the workbook in the database.
·In File ->Manage Workbooks giving sharing to users, so that they can
access the workbook.
·For Connecting to Oracle Applications the following steps has to be
done.
·In Administration Select Tools – Options
Select the 3 Option Button and specify the
gateway/Password, FNDNAM
·Now connect with Oracle Application User Id Preceded with : for example
10975:
·Click on Oracle Application User Check box
·Once we click on the connect Responsibility Pop-Up window will be
Populated will all the responsibilities will be displayed.
·Select One responsibility and press ok
The Process for Creating the Business Area and Workbook
remains the same. In oracle Application the DBA has to give access to all the
business areas then only user will be able to access the business areas.
In
the Security also user can give access to Application Users and responsibility
for accessing the business areas.
The Steps for Calling the Oracle Discover workbook and Graph in Oracle
Applications
·Create a function as specified in the tab
In Tab of Properties select Type as SSWA PL/SQL
Function
In the Form Tab Give the workbook Name which
you have created. Give the name as specified in the Discoverer workbook. In
Discover Desktop Edition select manage workbook and click on Properties. In that
identifier name will be there. It should be same what we specify in the
parameters workbook = ‘ASSET’
In
HTML Call give the values as OracleOASIS.RunDiscoverer
·Save the function
·Create a Menu and attach the
Function
·Assign this menu to
Responsibility.
·Assign the responsibility to
User.
·Log in with the user and
select the responsibility
·In This Menu Graph will
invoke the discoverer workbook and graph.
·Select the menu entry for
discoverer. Which will invoke the Discoverer workbook and graph will be
displayed on the browser.
