Registering Concurrent Programs in Oracle Apps

Lets first discuss the below scenario
Being a developer, you have just developed a SQL script or a PL/SQL package procedure. The end user wants to be able to run this script ad-hoc or they wish to schedule this to run every night.

Question : How can an end-user be given control to run a script developed by a developer, given that an end user will never have access to apps password(and rightly so)?
Answer: This script can be attached to a Concurrent Program via a concurrent program executable. The user will then be given access to this Concurrent Program.

Question : But how will the end user or Oracle Apps make this script run every 10hours daily?
Answer : A concurrent program can be scheduled to run at desired intervals. The schedule is defined at the time of submission.

Question: What are the different types of scripts/programs that can be attached to concurrent programs?
Answer :
A sql script
A Sql*Loader program
Java program
Oracle Report
Pl/SQL program ( or call it a stored procedure/package procedure)
Host script, like a unix shell script
Pro*C/Spawned
Perl

Question: What are the basic steps when defining a concurrent program.
Answer: Broadly speaking there are three steps when developing a concurrent program in Oracle Apps
Step 1. Make Oracle Apps identify the executable
Step 2. Provide a handle to the executable by means of defining a concurrent program
Step 3. Make this concurrent program accesible to selected users via their responsibility.

Question: Please can you describe the above steps in detail
Answer:
Step 1. Make Oracle Apps identify the executable
In Oracle Apps we have something called as concurrent program executable. Concurrent program executable is defined to register a script or a procedure for its usage within oracle apps.

Step 2. Provide a handle to the executable by means of defining a Concurrent Program.
We need to define a concurrent program and attach that to the executable defined in above step.

Step 3. Make this concurrent program accesible to selected users via their responsibility.
We do this by adding the concurrent program to something called as request group. The request group is either associated with a responsibility or is passed in as a parameter to the function request form function. Don't worry if you do not understand this step, I will be writing a dedicated article to explain this step.


Question : Please explain the steps for defining a pl/sql concurrent program, with screenshots and with almost real life example?
Answer:
1. Create a table and a stored procedure in pl/sql
create table xx_hello_world_tab
(
 message_text VARCHAR2(100)
,creation_date DATE
) ;

CREATE OR REPLACE PROCEDURE
xx_register_user_prc(errbuf      OUT VARCHAR2,retcode     OUT VARCHAR2) IS
BEGIN
  INSERT INTO xx_hello_world_tab VALUES ('Hello World' ,SYSDATE);
END xx_register_user_prc;

SELECT * FROM xx_hello_world_tab ;
--Zero records will be returned here


2. Note the two parameters, namely errbuff and retcode
These parameters for used for the interaction between the concurrent program and the pl/sql Program. I suggest to the beginners not to spend much time on these parameters.

3. Define an executable attached to this procedure.
This can be done by navigating to the responsibility “Application Developer”, and selecting the menu /Concurrent/Executable




4. Define the concurrent program
This can be done by navigating to responsibility “Application Developer” and then selecting menu /Concurrent/Program




5. Attach this program to a report group.
Go to System Administrator responsibility, and select
/Security/Responsibility/Request




Now, we can navigate to the Receivables Manager and submit the concurrent request.



After the completion of the request, we check the table

AR INVOICE INTERFACE

AR Invoice Interface - ra_interface_lines_all, ra_interface_distributions_all, ra_interface_errors_all

AR Invoice Interface
AR INVOICE INTERFACE
************************

Interface Tables are :-
-----------------------
ra_interface_lines_all
ra_interface_distributions_all
ra_interface_errors_all

Concurrent Program to import data from interface tables :-
------------------------------------------------------------
Autoinvoice Import Program

Script to Migrate data from Staging table to Interface Table------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE xxx_ar_invoice_api (errbuf out varchar2, rectcode out varchar2)
AS

l_org_id hr_operating_units.organization_id%type;
l_sob_id hr_operating_units.set_of_books_id%type;
l_cust_trx_type_id ra_cust_trx_types_all.cust_trx_type_id%type;
l_gl_id_rev ra_cust_trx_types_all.gl_id_rev%type;
l_cust_trx_type_name ra_cust_trx_types_all.name%type;
l_currency_code fnd_currencies.currency_code%type;
l_term_id ra_terms_tl.term_id%type;
l_term_name ra_terms_tl.name%type;
l_address_id hz_cust_acct_sites_all.cust_acct_site_id%type;
l_customer_id hz_cust_accounts.cust_account_id%type;
l_verify_flag char(1);
l_error_message varchar2(2500);


BEGIN

BEGIN
SELECT organization_id, SET_OF_BOOKS_ID
INTO l_org_id, l_sob_id
FROM hr_operating_units
WHERE name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Operating Unit...';

END;


BEGIN
SELECT cust_trx_type_id,name,gl_id_rev
INTO l_cust_trx_type_id,l_cust_trx_type_name, l_gl_id_rev
FROM ra_cust_trx_types_all
WHERE set_of_books_id = l_sob_id
AND org_id = l_org_id
AND name = 'xxx-Spares-Inv';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Invoice Type...';
END;


BEGIN
select currency_code
into l_currency_code
from fnd_currencies
where currency_code = 'INR';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Currency Code...';
END;
BEGIN
SELECT term_id,name
into l_term_id,l_term_name
FROM ra_terms_tl
WHERE upper(name) = upper('IMMEDIATE');
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Terms Name...';
END;


BEGIN
SELECT DISTINCT HCAS.cust_acct_site_id,HCA.cust_account_id
INTO l_address_id,l_customer_id
FROM hz_parties HP
,hz_party_sites HPS
,hz_cust_accounts HCA
,hz_cust_acct_sites_all HCAS
,hz_cust_site_uses_all HCSU
WHERE HCA.party_id = HP.party_id
AND HP.party_id = HPS.party_id
AND HCA.cust_account_id = HCAS.cust_account_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id
AND HCSU.site_use_code = 'BILL_TO'
AND HCSU.primary_flag = 'Y'
AND upper (ltrim (rtrim (HP.party_name))) = upper (ltrim (rtrim ('Customer Name')))
AND HCAs.org_id = l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Customer Name...';
END;


INSERT INTO ra_interface_lines_all(
INTERFACE_LINE_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
CUST_TRX_TYPE_ID,
cust_trx_type_name,
TRX_DATE,
GL_DATE,
CURRENCY_CODE,
term_id,
term_name,
orig_system_bill_customer_id,
ORIG_SYSTEM_BILL_CUSTOMER_REF,
orig_system_bill_address_id,
ORIG_SYSTEM_BILL_ADDRESS_REF,
--orig_system_ship_customer_id,
--orig_system_ship_address_id,
orig_system_sold_customer_id,
QUANTITY,
--unit_selling_price
AMOUNT,
DESCRIPTION,
conversion_type,
conversion_rate,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
org_id
)
Values
(
RA_CUSTOMER_TRX_LINES_S.NEXTVAL,
'Invoice Migration',
'LINE',
l_cust_trx_type_id,
l_cust_trx_type_name,
sysdate,
sysdate,
l_currency_code,
l_term_id,
l_term_name,
l_customer_id,
l_customer_id,
l_address_id,
l_address_id,
--85222,
--87978,
l_customer_id,
1,
--40000
4000,
'Test Invoice2 12JUN08',
'User',
1,
'Invoice Migration',
'RINV_000000002',
l_org_id
);



INSERT INTO ra_interface_distributions_all
(
INTERFACE_LINE_ID
,account_class
,amount
,code_combination_id
,percent
,interface_line_context
,interface_line_attribute1
,org_id
)
VALUES
(
RA_CUSTOMER_TRX_LINES_S.CURRVAL,
'REV'
,4000
,l_gl_id_rev
,100
,'Invoice Migration',
'RINV_000000002',
l_org_id
);

Commit;
END xxx_ar_invoice_api;
/

FAQ in Oracle Forms

FAQ in Oracle Forms

FAQs in Oracle Forms – Part:3


1)What is the difference between Pre-Form and When-New-Form-Instance trigger?

Pre-Form trigger will be fired before entering into the form. It is the first trigger that fires when a form is run; fires before the form is visible. It is useful for setting access to form items, initializing global variables, and assigning unique primary key from an Oracle sequence.
When-New-Form-Instance trigger will be fired whenever form is ready to accept the data from the user.
Another main difference between the two is that you cannot navigate in a pre-from trigger (restricted) whereas you can navigate in a when-new-form-instance trigger. For example : go_block and execute-query will only work in when-new-form-instance trigger not in pre-from trigger.

2)What are the triggers fired while creating Master Detail form?

On-Clear Details (Form Level)
On-Populate-Details (Block Level)
On-Check-delete-Master (Block Level)
How will you get the block Name in a form?
By using SYSTEM.CURRENT_BLOCK

3)What is the difference between Pre-insert and On-insert trigger?

Pre-insert trigger fires during the Post and Commit Transactions process, before a row is inserted. It fires once for each record that is marked for insert. On-insert trigger fires during the Post and Commit Transactions process when a record is inserted. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Form Builder would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.

4)What is the difference between Pre-Query and Post-Query trigger?

Pre-Query trigger validates the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database. Post-Query trigger perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. It fires once for each record fetched into the block.

5)What is the trigger sequence while opening a form?
Pre-Form,
Pre-Block,
Pre-Record,
Pre-Item,
When-new-form-Instance,
When-new-block-Instance,
When-new-Record-instance,
When-new-Item-instance,
Post-item,
post-record,
Post-block,
Post-form

6)What is the difference between new form, open form & call form?

New_form:-Once we move into the destination automatically source will be closed.
Open_form:- It is a two way connection between source and destination. Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.
Call_form:- Call_form() runs an indicated form while keeping the parent form active. Without closing the destination one cannot come back to the source.

7)How to call a Report from a form?

By using RUN_PRODUCT Built-in.

9)Can we write Commit statement in forms triggers?
Yes

10)What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS ?

These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively.

11)A query fetched 10 records. How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed ?

PRE-QUERY fires once. POST-QUERY fires 10 times.
How can you execute the user defined triggers in forms?
To execute a user-named trigger, you must call the EXECUTE_TRIGGER built-in procedure, as shown here: Execute_Trigger(‘my_user_named_trigger’);

12)What are Restricted Built-in Subprograms in forms?

Any built-in subprogram that initiates navigation is restricted. This includes subprograms that move the input focus from one item to another, and those that involve database transactions. Restricted built-ins are not allowed in triggers that fire in response to navigation.
For example, the GO_ITEM and NEXT_SET built-ins are both restricted procedures. GO_ITEM moves the input focus from a source item to a target item, which requires navigation. Similarly, the NEXT_SET procedure causes Oracle Forms to navigate internally to the block level, fetch a set of records, and then navigate to the first item in the first record. (Note that this navigation happens internally as a result of default processing, and may not be apparent to the form operator.) Because GO_ITEM and NEXT_SET both initiate navigation, they cannot be called from triggers that fire in response to internal navigational events; that is, triggers that fire while navigation is already occurring. Thus, a restricted procedure cannot be called from a Pre-Block trigger, because the Pre-Block trigger fires during internal navigation. In fact, restricted subprograms are not allowed from any PRE- or POST- navigational triggers. You can, however, call a restricted built-in subprogram from a When-New-Instance trigger. For example, the When-New-Item-Instance trigger fires after navigation to an item has succeeded, when the form is waiting for input.

13)What are System Variables in forms?

A system variable is a Oracle Forms variable that keeps track of an internal Oracle Forms state. You can reference the value of a system variable to control the way an application behaves.

14)List system variables available in forms 10g?
SYSTEM.CURRENT_FORM
SYSTEM.CURRENT_ITEM
SYSTEM.CURRENT_VALUE
SYSTEM.CURSOR_BLOCK
SYSTEM.CURSOR_ITEM
SYSTEM.CURSOR_RECORD
SYSTEM.CURSOR_VALUE
SYSTEM.FORM_STATUS
SYSTEM.LAST_FORM
SYSTEM.LAST_QUERY
SYSTEM.LAST_RECORD
SYSTEM.MASTER_BLOCK
SYSTEM.MESSAGE_LEVEL
SYSTEM.SUPPRESS_WORKING…..etc.

15)What are the triggers associated with the image item?

When-Image-activated(fires when the operator double clicks on an image Items)
When-image-pressed(fires when the operator selects or deselects the image item)

16)What are the built-in routines available in forms to create and manipulate a parameter list?
Add_parameter
Create_Parameter_list
Delete_parameter
Destroy_parameter_list
Get_parameter_attr
Get_parameter_list
set_parameter_attr

17)What are the built-ins used to trapping errors in forms?
Error_type : Returns the error message type(character)
Error_code : Returns the error number
Error_text  : Returns the message text of the Oracle Forms error
Dbms_error_code : Returns the error number of the last database error that was detected.
Dbms_error_text : Returns the message number (such as ORA-01438) and message text of the database error

18)What is the predefined exception available in forms ?

The FORM_TRIGGER_FAILURE exception is a predefined PL/SQL exception available only in Oracle Forms.

19)What are the Built-ins used for sending Parameters to forms?

You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.

20)How do you reference a Parameter?
In pl/sql, You can reference and set the values of form parameters using bind variables syntax.

21)How do you reference a parameter indirectly?
To indirectly reference a parameter use the NAME_IN or COPY built-ins.

22)What is forms_DDL?
It issues dynamic sql statements at run time, including server side pl/sql and DDL

23)What is Text_io Package?
It allows you to read and write information to a file in the file system.

24)What is When-Database-Record trigger?
It fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. It generally occurs only when the operator modifies the first item in the record, and after the operator attempts to navigate out of the item.

25)What is the difference between $$DATE$$ & $$DBDATE$$?
$$DBDATE$$ retrieves the current database date
$$DATE$$ retrieves the current operating system date.

26)What is a timer?
Timer is a “internal time clock” that you can programmatically create to perform an action each time the timer expires.

27)What are built-ins associated with timers?
find_timer
create_timer
delete_timer

29)What is the difference between post database commit and post-form commit?
Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.

30)What is the form development process?
a) open template form
b) Save as <your form>.fmb
c) Change the form module name as form name.
d) Delete the default blocks, window, and canvas
e) Create a window.
f) Assign the window property class to window
g) Create a canvas
h) Assign canvas property class to the canvas
i) Assign the window to the canvas and canvas to the window
j) Create a data block    
k) Modify the form level properties. (sub class item à Text item)
l)  Modify the app_cusom package. In the program unit.
m) Modify the pre-form trigger (form level)
n) Modify the module level properties
o) Save and compile the form.
p) Place the .fmx in the server directory.
q) Register in the AOL
APPLICATION à FORMà FUNCTIONà MENU

31)What is template?
The TEMPLATE form is the required starting point for all development of new Forms. The TEMPLATE form includes platform–independent attachments of several Libraries.
APPSCORE :- It contains package and procedures that are required of all forms to support  the MENUS ,TOOLBARS.
APPSDAYPK :- It contains packages that control the oracle applications CALENDER FEATURES.
FNDSQF  :- It contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.
CUSTOM :- It allows extension of oracle applications forms with out modification of oracle application code, you can use the custom library for customization such as zoom    ( such as moving to another form and querying up specific records)

32)Where exactly you place your forms in APPS environment?
/apps/visappl/au/11.5.0/forms/US

33)What are the Different PLL’s used in Forms?
CUSTOM.pll
FNDSQF.pll
APPCORE.pll
APPCORE2.pll
appdaypk.pll
APPSTAND.pll

34)What are the triggers that can be modified during Forms Customization?
Pre-Forms
When-New-Form-Instance
Query_Find
Post-Form
Key-Clrfrm
Accept

35)What are the triggers that cannot be modified during Forms Customization?
STANDARD_ATTACHMENT
ZOOM
FOLDER_ACTION
KEY-HELP
KEY-EXIT
KEY-COMMIT
WHEN-WINDOW_CLOSED
CLOSE_WINDOW


FAQs in Oracle Forms – Part:2


36)What is a display item?
Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.

37)How many maximum number of radio buttons can you assign to a radio group?
Unlimited no of radio buttons can be assigned to a radio group.

38)Can you change the default value of the radio button group at run time?
No.

39)What triggers are associated with the radio group?
Only when-radio-changed trigger associated with radio group.

40)What are the various states of a form?
CHANGED: Indicates that the form contains at least one block with a Changed record.
NEW: Indicates that the form contains only New records.
QUERY: Indicates that a query is open.

41)What are the different objects that you cannot copy or reference in object groups?
objects of different modules
another object groups
individual block dependent items
program units

42)What are the different modals of a window?
Modeless windows
Modal windows

43)What are Modeless windows?
More than one Modeless window can be displayed at the same time, and operators can navigate among them if your application allows them to do so . On most GUI platforms, Modeless windows can also be layered to appear either in front of or behind other windows.

44)What are modal windows?
Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.

45)How do you display console on a window ?
The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.

46)Can you have more than one content canvas attached with a window?
Yes. Each window you create must have at least one content canvas assigned to it. You can also create a window that has manipulate content canvas. At run time only one of the content canvas assign to a window is displayed at a time.

47)How many windows in a form can have console?
Only one window in a form can display the console, and you cannot change the console assignment at runtime.

48)What are the different window events activated at runtimes?
When_window_activated
When_window_closed
When_window_deactivated
When_window_resized

49)When a form call a pl/sql routine if there is an error in the pl/sql routine how do you place custom message in the form?
FND_MESSAGE.SHOW displays an informational message in a forms modal window or in a concurrent program log file only.
fnd_message.set_string(‘Message Text’);
fnd_message.show;
FND_MESSAGE.HINT to display a message in the forms status line and FND_MESSAGE.ERASE to clear the forms status line. FND_MESSAGE.HINT takes its message from the stack, displays the message, and then clears that message from the message stack.

50)List the built-in routines for controlling a window during run-time?
Find_window,
get_window_property,
hide_window,
move_window,
resize_window,
set_window_property,
show_View

51)What built-in is used for changing the properties of a window dynamically?
Set_window_property

52)What built-in is used for showing an alert during run-time?
Show_alert

53)Can you change alert messages at run-time?
Yes. By Set_alert_property.

54)What is the built-in function used for finding the alert?
Find_alert

55)What built-in routines are used to display editor dynamically?
Edit_text item
show_editor

56)What is the difference between COPY and NAME_IN ?
Copy is package procedure that writes values into a field. Name_in is a package function that returns the contents of the variable to which you apply.

57)Can you attach an lov to a field at run-time?
Yes. By Set_item_proprety

58)What is the built-in used to get and set lov properties during run-time?
Get_lov_property
Set_lov_property

59)Give built-in routines related to a record groups?
Create_group (Function)
Create_group_from_query(Function)
Delete_group(Procedure)
Add_group_column(Function)
Add_group_row(Procedure)
Delete_group_row(Procedure)
Populate_group(Function)
Populate_group_with_query(Function)
Set_group_Char_cell(procedure)

60)What is the built-in routine used to count the no of rows in a group?
Get_group_row_count

61)What are the built-ins that are used to attach an LOV programmatically to an item?
set_item_property
get_item_property

62)How many number of columns a record group can have?
A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of column does not exceed 64K.

63)What is the Maximum allowed length of Record group Column?
Record group column names cannot exceed 30 characters.

64)What are the built-ins used for Creating and deleting groups?
CREATE-GROUP (function)
CREATE_GROUP_FROM_QUERY(function)
DELETE_GROUP(procedure

65)What are the difference between Lov & List Item?
Lov is a property where as list item is an item. A list item can have only one column while a lov can have one or more columns.

66)What are the different display styles of list items?
Pop List
Text List
Combo box
What is Pop List?
The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.

67)What is a Text List?
The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select undisplayed values.

68)What is a Combo Box?
A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.



FAQs in Oracle Forms – Part:1


69)What are different types of modules available in oracle form?
Form module – a collection of objects and code routines.
Menu module – a collection of menus and menu item commands that together make up an application menu.
Library module – a collection of user named procedures, functions and packages that can be called from other modules in the application.

70)What are the default extensions of the files created by forms modules?
.fmb – form module binary
.fmx – form module executable

71)What are the default extensions of the files created by menu module?
.mmb, .mmx

72)What is data block & control block?
Data Block:-It is a Logical Collection of Items.
Control Block:-It is a data block which is totally independent of Database Table or View.
Note: – A data block can have control block items but not vice versa.

73)What is property class & visual attributes?
Property Class:
A property class is a named object that contains a list of properties and their settings. Once you create a property class you can base other objects on it. An object based on a property class can inherit the setting of any property in the class that makes sense for that object. Property class inheritance is an instance of subclassing. Conceptually, you can consider a property class as a universal subclassing parent.
Visual Attributes:
Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application’s interface. Visual attributes can include the following properties: Font properties: Font Name, Font Size, Font Style, Font Width, Font Weight Color and pattern properties: Foreground Color, Background Color, Fill Pattern. Every interface object has a Visual Attribute Group property that determines how the object’s individual visual attribute settings (Font Size, Foreground Color, etc.) are derived. The Visual Attribute Group property can be set to Default, NULL, or the name of a named visual attribute defined in the same module.

74)What is the difference between property class and visual attribute?
We can change Visual Attribute properties dynamically at runtime, but we cannot change Property class properties. When you inherit the both Visual Attribute properties and Property class properties to an item Visual Attribute properties overrides the Property class properties.

75)What is object group?
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module. Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.

76)What is Record Group?
It is an internal memory data structure and a separate object in the form module. It’s main usage is to provide data to the LOV and dynamically to the list item and to perform client level validations.

77)What are the types of Record-Groups?
Static Record Group:- A static record group is not associated with a query; instead, you define its structure and row values at design time, and they remain fixed at runtime. Static record groups can be created and modified only at design time.
Query Based Record Group: – A query record group is a record group that has an associated SELECT statement. The columns in a query record group derive their default names, data types, and lengths from the database columns referenced in the SELECT statement. The records in a query record group are the rows retrieved by the query associated with that record group.
Non Query Record Group:- A non-query record group is a group that does not have an associated query, but whose structure and values can be modified programmatically at runtime. Non-query record groups can be created and modified only at runtime.

78)How to change Record Group dynamically?
By Using Non-Query Record Group.

79)What is Library?
A library is a collection of subprograms, including user-named procedures, functions, and packages. We can attach a library to any other form, menu or library module. It provides an easy method of reusing objects and enforcing standards across the entire development organization.

80)What are the types of canvases available in forms & what is the default canvas?
Content:-it is the base view of window which occupies the entire surface of window. It can have any no of canvases but at a time only one is visible.

Stacked:-It is always displayed above the content canvas because the content Canvas is the base view. It can have any no of stacked canvases and more than one stacked canvas can be displayed at a time.

Tool bar:- A toolbar canvas often is used to create toolbars for individual windows. There are two types of tool bars Horizontal and Vertical Tool bar Canvas. Horizontal tool bar canvases are displayed at the top of window and only one horizontal tool bar can be attached to a form module. Vertical tool bar is used to display top to bottom on the left side of the window.

Tab:-It is a collection of one or more tab pages. It is mainly used to display a large amount of related information a single dynamic form builder canvas object.
Content Canvas is the default canvas.

81)What are Alerts?
An alert is a modal window that displays a message notifying the operator of some application condition. There are three styles of alerts: Stop, Caution, and Note.
To display an alert, your application must execute the SHOW_ALERT built-in subprogram from a trigger or user-named subprogram. SHOW_ALERT is a function that returns a numeric constant.
Show_Alert(alert_name) Return NUMBER;

82)What are LOVs?
An LOV is a scrollable popup window that provides the end user with either a single or multi-column selection list. LOV values are derived from record groups. The LOVs in the Form Builder interface have the same auto-reduction and searching functionality as the LOVs you create for your own applications.
There are two built-in subprograms that can be used to display an LOV:
1) LIST_VALUES
2) SHOW_LOV

83)List the system variables related in Block and Field?
1. System.block_status
2. System.current_block
3. System.current_field
4. System.current_value
5. System.cursor_block
6. System.cursor_field
7. System.field_status

84)What is a master detail relationship?
A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.

85)What are the Various Master and Detail Relationships?
The various Master and Detail Relationship are:
a) NonIsolated :: The Master cannot be deleted when a child is existing.
b) Isolated :: The Master can be deleted when the child is existing.
c) Cascading :: The child gets deleted when the Master is deleted.

86)What are the procedures that will be created when a master details form created?
CHECK_PACKAGE_FAILURE
CHECK_ALL_MASTER_DETAILS
QUERY_MASTER_DETAILS

87)What is mouse navigate property of button?
When Mouse Navigate is True (the default), Oracle Forms performs standard navigation to move the focus to the item when the operator activates the item with the mouse. When Mouse Navigate is set to False, Oracle Forms does not perform navigation (and the resulting validation) to move to the item when an operator activates the item with the mouse.

88)What is difference between PL/SQL library and object library in Forms?
PL/SQL library contains only pl/sql codes which can be used for standardizing the validations etc. in forms whereas Object Library is used to create, store, maintain, and distribute standard and reusable Form objects.

89)What are the different default triggers created when Master Deletes Property is set to Nonisolated?
On-Check-Delete-Master
On-Clear-Details
On-Populate-Details

90)What are the different default triggers created when Master Deletes Property is set to Cascade?
On-Clear-Details
On-Populate-Details
Pre-delete

91)What are the different default triggers created when Master Deletes Property is set to isolated?
On-Clear-Details
On-Populate-Details

92)What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?
Show_editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.

93)What is the “LOV of Validation” Property of an item? What is the use of it?
When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.

TECHNICAL DATA FLOW IN ORDER TO CASH

TECHNICAL DATA FLOW IN ORDER TO CASH

DATA FLOW IN ORDER TO CASH

TECHNICAL DATA FLOW IN ORDER TO CASH
STEP 1:
Enter order received from customer in Order Management (Sales Order)
Also enter the line level information of items ordered for. On saving the information, the data will be inserted in OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL Order_number will be automatically generated, Header and line Flow_status_code = ‘ENTERED’
OE_ORDER_HEADERS_ALL

select header_id,
order_number,
request_date,
transactional_curr_code,
shipping_method_code,
ship_from_org_id,
ship_to_org_id,
org_id,
flow_status_code,
salesrep_id,
booked_date
from OE_ORDER_HEADERS_ALL
where flow_status_code = 'CLOSED'
and order_number = '56706'

OE_ORDER_LINES_ALL

select header_id,
        line_id,
        line_number,
        ordered_item,
        inventory_item_id ,
        request_date,
        promise_date,
        schedule_ship_date,
        pricing_quantity,
        ordered_quantity,
        cancelled_quantity,
        shipped_quantity,
        invoiced_quantity,
        payment_term_id,
        visible_demand_flag,
        actual_shipment_date,
        unit_selling_price,
        tax_value,
        cancelled_flag,
        flow_status_code
from OE_ORDER_LINES_ALL
where header_id = 94256


STEP 2:
Book the order. Header Flow_status_code = ‘BOOKED’
Line Flow_status_code = ‘AWAITING SHIPPING’
The data is captured in WSH_DELIVERY_DETAILS.
WSH_DLIVERY_ASSIGNMENTS is the intermediate table between WSH_DELIVERY_DETAIL and WSH_NEW_DELIVERIES.

WSH_DELIVERY_DETAILS
select delivery_detail_id,
        source_header_id,
        source_line_id,
        source_code,
        customer_id,
        inventory_item_id,
        item_description,
        ship_from_location_id,
        ship_to_location_id,
        move_order_line_id,
        requested_quantity,
        shipped_quantity,
        subinventory,
        released_status,
        ship_method_code,
        carrier_id,
        net_weight,
        unit_weight,
        unit_volume,
        unit_price,
        inspection_flag,
        source_header_number,
        batch_id
from WSH_DELIVERY_DETAILS
where source_header_id = 94256


WSH_DELIVERY_ASSIGNMENTS

select delivery_detail_id ,
        delivery_id,
        parent_delivery_detail_id,
        creation_date
from WSH_DELIVERY_ASSIGNMENTS
where delivery_detail_id in (216151, 216152, 216153) -- from above query


WSH_NEW_DELIVERIES

select delivery_id,
        name,
        status_code,
        customer_id ,
        gross_weight,
        net_weight,
        weight_uom_code,
        confirm_date,
        delivery_type,
        source_header_id,
        hash_string
from WSH_NEW_DELIVERIES
where delivery_id in (65881, 65882) -- from above query




STEP 3:
You can enter, view and update the Sales order information using Sales Order window.
Release the sales Order
Once the order is released, the data is updated in WSH_DELIVERY_DETAILS.The released_status becomes ‘Y’ and MOVE ORDER is generated in MTL_TXN_REQUEST_HEADERS and LINES. The move_order_line_id is also populated in WSH_DELIVERY_DETAILS

STEP 4:
Ship Confirm will update the data in WSH_DELIVERY_DETAILS. Released_status = ‘C’ and Shipped_quantity gets updated.
If you choose Tools >> Workflow Status then you will see that the workflow activity is in Deferred stage.
The concurrent program Workflow Background Engine needs to be executed to proceed further.

MTL_TXN_REQUEST_LINES
select line_id move_order_line_id,
        header_id move_order_header_id,
        line_number,
        inventory_item_id,
        organization_id,
        quantity_delivered,
        line_status,
        txn_source_id,
        transaction_type_id,
        pick_slip_date
from MTL_TXN_REQUEST_LINES
where line_id in (174779, 174780, 174781)


MTL_TXN_REQUEST_HEADERS
select header_id move_order_header_id,
        request_number move_order_number,
        move_order_type,
        organization_id,
        header_status move_order_status
from MTL_TXN_REQUEST_HEADERS
where header_id in (55272, 55273)


MTL_MATERIAL_TRANSACTIONS
select transaction_id,
        inventory_item_id,
        organization_id,
        subinventory_code,
        transaction_type_id,
        transaction_action_id,
        transaction_quantity,
        transaction_uom,
        transaction_date,
        distribution_account_id,
        invoiced_flag,
        shipment_number delvery_name
from MTL_MATERIAL_TRANSACTIONS
where shipment_number in ('65881', '65882')


Workflow Background Process executes Autoinvoice master, Autoinvoice Import Program.
The date is first transferred from Shipping to RA_INTERFACE_LINES_ALL.
Then the Autoinvoice Import program validates the records and populates the base table in Account Receivable. RA_CUSTOMER_TXN_ALL , RA_CUSTOMER_TXN_LINES_ALL

RA_CUSTOMER_TRX_ALL

select interface_header_attribute1,
        customer_trx_id,
        trx_number,
        cust_trx_type_id,
        complete_flag,
        ship_date_actual
from RA_CUSTOMER_TRX_ALL
where interface_header_attribute1 = '56706'

Concurrent Program Parameters Default Type and Default Value

Concurrent Program Parameters Default Type and Default Value

Concurrent Program Parameters Window Details:
Concurrent program can have parameter values which can be entered by the user at run time. All the parameters to be passed to a concurrent program need to be defined with the concurrent program.
Concurrent Program Parameters window contains the following fields:

1) Sequence
Enter sequence numbers that specify the order in which your program receives parameter values from the concurrent manager.
2) Parameter
Specify Name of the Parameter.
3) Description
Description of the Parameter.
4) Enabled
When checked, parameter is enabled. Disabled parameters do not display at request submission time
5) Validation
(i)  Value Set
Each Parameter should have a value set. It can be user defined value set or one of the standard values set available in Oracle Apps.
 (ii) Description
this is defaulted when Value Set is selected

6) Required:- When this is checked, the parameter is mandatory and must be entered during program submission.

7) Display:- When this check-box is checked, the parameter will be displayed during program submission.

8) Display Size: - Size of the Display field.

9) Prompt: - Prompt for the parameter that will appear for parameter name when running the concurrent program.

10) Token: - Name of the token. This is used when registering Oracle Report
as a concurrent program. The name of this field is same as the token defined in the report.
Concurrent Program Parameters Default Type and Default Value Details:
 When it is required to default value of the parameter, the fields default type and default values can be selected. The possible values of default type field are:
1) Profile
When Profile is selected in Default type; the profile option name should be entered in the default value field. Use the profile option name.
For example, for getting operating unit (profile ‘MO: Operating Unit’), we can enter ORG_ID in the default value field.

2) SQL Statement
When SQL Statement is selected in Default type, any SQL statement can be written in the default value field.
SQL statement must return exactly one row and one column in all cases.

3) Current Date
When Current Date is selected in Default Type, then default value will be non-enterable. The current date will be automatically defaulted when running the program.
The default value is the current date in the format DD-MON-YY or DD-MON-YYYY,
depending on the length of the segment.
Maximum              Date
Size                        Format
9                             DD-MON-YY
11                           DD-MON-YYYY

4) Segment
the default value is the value entered in a prior segment of the same parameter window.
5) Constant
When Constant is selected in Default Type, any constant value can be entered in Default value field.

6) Current Time         the default value is the current time or the current date and time, depending on the length of the segment.
Maximum       Time
Size                  Format
5                      HH24:MI
8                      HH24:MI:SS
15                    DD-MON-YY HH24:MI
17                    DD-MON-YYYY HH24:MI
18                    DD-MON-YY HH24:MI:SS
20                    DD-MON-YYYY HH24:MI:SS

SQL to determine the first and last working day of the month for a given date

SQL to determine the first and last working day of the month for a given date:

SELECT MIN(DATES) FIRST_WORKING_DAY , MAX(DATES) LAST_WORKING_DAY FROM
(
    SELECT TRUNC(sysdate,'MM')+ROWNUM -1 DATES FROM
    (
    SELECT 1
    FROM Dual
    GROUP BY CUBE (2, 2, 2, 2, 2)
    )
    WHERE ROWNUM <= ADD_MONTHS(TRUNC(sysdate,'MM'),1) - TRUNC(sysdate,'MM')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN');

SQL Query to find No. of working day in a month

SQL Query to find No. of working day in a month

SELECT * FROM
(
    SELECT TRUNC(sysdate,'MM')+ROWNUM -1 DATES FROM
    (
    SELECT 1
    FROM Dual
    GROUP BY CUBE (2, 2, 2, 2, 2)
    )
    WHERE ROWNUM <= ADD_MONTHS(TRUNC(sysdate,'MM'),1) - TRUNC(sysdate,'MM')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN');

Sending Mail Using PL/SQL Code

Sending Mail Using PL/SQL Code:

DECLARE
v_From VARCHAR2(80) := 'oracle@mycompany.com';
v_Recipient VARCHAR2(80) := 'ravindra.varma@gmail.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.gmail.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
/* Open the connection to host server using
UTL_SMTP.OPEN_CONNECTION */
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
/* Use UTL_SMTP.HELO function to initialize handshaking with
host server */
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
/* Use UTL_SMTP.MAIL function/procedure to initiate the server
transaction */
utl_smtp.Mail(v_Mail_Conn, v_From);
/* Specify recipient name using UTL_SMTP.RCPT function/procedure */
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
/* Compose email data using UTL_SMTP.DATA */
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf
);
/* Use UTL_SMTP.QUIT method to terminate the connection */
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;

Query for Ledger, OU, Legal Enity

Query for Ledger, OU, Legal Enity
Query for Ledger, OU, Legal Enity, balancing segment:

SELECT hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units   tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

Sub Ledger Break Up Query - Oracle Apps R-12

Sub Ledger Break Up Query - Oracle Apps R-12

SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  ASUP.VENDOR_NAME,
  TO_CHAR(ACA.CHECK_NUMBER),
  ACA.CHECK_DATE,
  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
  ACA.CREATION_DATE VOUCHER_DATE,
--  DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CANCELLED',AMOUNT* NVL(EXCHANGE_RATE,1),'REFUND RECORDED',XAL.ACCOUNTED_DR,
--   0)  RECEIPT,
--  DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CREATED',AMOUNT* NVL(EXCHANGE_RATE,1),0) PAYMENT
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC,
  AP_SUPPLIERS ASUP,
  AP_CHECKS_ALL ACA
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  ASUP.VENDOR_ID(+)=XAL.PARTY_ID AND
  ACA.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND
  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Payables'
UNION ALL
------ DATA FROM CASH MANAGEMENT --------------------------------
SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  '' VENDOR_NAME,
  '' CHECK_NUMBER,
  NULL CHECK_DATE,
  NULL VOUCHER_NUMBER,
  NULL VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
 -- GJH.PERIOD_NAME IN ('APR-11-12','MAY-11-12','JUN-11-12','JUL-11-12') AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Cash Management' AND
  GJH.JE_CATEGORY='Bank Transfers'
UNION ALL
-------------------Data from Receivable --------------------------------
SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
      GJH.NAME JV_NAME,
      GJH.JE_CATEGORY,
      GJH.JE_SOURCE,
      GJH.PERIOD_NAME,
      NVL(XAL.ACCOUNTED_CR,0) GL_CR,
      NVL(XAL.ACCOUNTED_DR,0) GL_DR,
      GJL.DESCRIPTION JV_LINE_DESCRIPTION,
      XAH.EVENT_TYPE_CODE,
      XAH.DESCRIPTION SLA_DESCRIPTION,
      XAL.AE_LINE_NUM,
      XAL.ACCOUNTING_DATE GL_DATE,
      (SELECT AC.CUSTOMER_NAME
      FROM AR_CUSTOMERS AC WHERE AC.CUSTOMER_ID=XAL.PARTY_ID) CUSTOMER_NAME,
      (SELECT ACR.RECEIPT_NUMBER FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) RECEIPT_NUMBER,
      (SELECT ACR.RECEIPT_DATE FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE
      ) RECEIPT_DATE,
      (SELECT ACR.DOC_SEQUENCE_VALUE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) VOUCHER_NUMBER,
      (SELECT ACR.CREATION_DATE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE)  VOUCHER_DATE,
      DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
      DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Receivables'
  UNION ALL
 ---------------- Manual -----------------------
SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
       GJH.NAME JV_NAME,
       GJH.JE_CATEGORY,
       GJH.JE_SOURCE,
       GJH.PERIOD_NAME,
       NVL(GJL.ACCOUNTED_DR,0) ACCOUNTED_DR,
       NVL(GJL.ACCOUNTED_CR,0) ACCOUNTED_CR,
       gjl.description jv_line_description,
        '' EVENT_TYPE_CODE,
        '' SLA_DESCRIPTION,
        NULL AE_LINE_NUM,
        GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
        '' VENDOR_NAME,
        '' CHECK_NUMBER,
        NULL CHECK_DATE,
        NULL VOUCHER_NUMBER,
        NULL VOUCHER_DATE,
        NVL(GJL.ACCOUNTED_DR,0) RECEIPT,
        NVL(GJL.ACCOUNTED_CR,0) PAYMENT
 FROM GL_JE_BATCHES GJB,
      GL_JE_HEADERS GJH,
      GL_JE_LINES GJL,
      GL_CODE_COMBINATIONS GCC
 WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
   AND GJH.JE_SOURCE = 'Manual'
UNION ALL
-----ALL OTHER SOURCES OTHER THAN ABOVE----------
SELECT
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  '' VENDOR_NAME,
  '' CHECK_NUMBER,
  NULL CHECK_DATE,
  NULL VOUCHER_NUMBER,
  NULL VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE NOT IN ('Receivables','Payables','Cash Management')

GL to AP (Payable) Query

GL to AP (Payable) Query....
Q1:--------------------------------------------------------------------------------------------
SELECT GJH.NAME,
       GJH.DESCRIPTION,
       TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'dd-MON-yyyy') EFF_DATE,
       DECODE (XTE.ENTITY_CODE,
               'AP_INVOICES', PV.VENDOR_NAME,
               (SELECT AC.VENDOR_NAME
                  FROM AP_CHECKS_ALL AC
                 WHERE XTE.SOURCE_ID_INT_1 = AC.CHECK_ID)
       ) PARTY,
       AIA.INVOICE_NUM DOC_SEQUENCE_VALUE,
       GJH.JE_CATEGORY,
       XAL.ACCOUNTED_DR ACCOUNTED_DR,
       XAL.ACCOUNTED_CR ACCOUNTED_CR,
       GJL.JE_HEADER_ID,
       XAL.PARTY_TYPE_CODE,
       GJH.JE_SOURCE,
       GJH.PERIOD_NAME,
       GCC.SEGMENT5,
       GJL.JE_LINE_NUM,
       GJH.DEFAULT_EFFECTIVE_DATE
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       AP_INVOICES_ALL AIA,
       PO_VENDORS PV
 WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND AIA.INVOICE_ID(+) = XTE.SOURCE_ID_INT_1
   AND AIA.VENDOR_ID = PV.VENDOR_ID(+)
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:ACCOUNT_ID, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:PERIOD_FROM, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:PERIOD_TO, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Payables'
-------------------------------------------------------------------------------------------------------
Q2:- --------------------------------------------------------------------------------------------------

SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  ASUP.VENDOR_NAME,
  TO_CHAR(ACA.CHECK_NUMBER),
  ACA.CHECK_DATE,
  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
  ACA.CREATION_DATE VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC,
  AP_SUPPLIERS ASUP,
  AP_CHECKS_ALL ACA
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  ASUP.VENDOR_ID(+)=XAL.PARTY_ID AND
  ACA.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND
  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Payables'

Query to get Year Calendar

SELECT   LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",
         "Tue", "Wed", "Thu", "Fri", "Sat"
    FROM (SELECT   TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
                   TO_CHAR (dt + 1, 'iw') week,
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sun",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Mon",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Tue",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Wed",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Thu",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Fri",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sat"
              FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
                      FROM all_objects
                     WHERE ROWNUM <=
                                ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
                              - TRUNC (SYSDATE, 'y'))
          GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week);

Display Concurrent Program Output In EXCEL Without Using XMLPublisher

Below is the workaround I followed.

1. Add one record to Lookup Type 'CP_OUTPUT_FILE_TYPE' :
1.1) Navigation Path : (Responsibility) Application Developer
> (Menu) Application
> (SubMenu) Lookups
> (Function) Application Object Library.
1.2) Query out Type 'CP_OUTPUT_FILE_TYPE'.
1.3) Add the record:
Code: TEST,
Meaning: TEST,
Description: Test of Excel Output.
1.4)Save

2. Match the newly defined concurrent program output file type 'TEST' to Mime Type 'application/vnd.ms-excel' :
2.1) NavigationPath : (Responsibility) System Administrator
> (Menu) Install
> (Function) Viewer Options.
2.2) Add a record:
File Format : TEST,
 Mime Type : application/vnd.ms-excel,
Description : Test Excel Format.
2.3) Save.

3. Check whether Output Format Type 'TEST' is added to concurrent program definition form :
3.1) Navigation Path : (Responsibility) System Administrator
> (Menu) Concurrent
> (SubMenu) Program
> (Function) Define.
3.2) Make sure there is the newly created option 'TEST' in 'Format' field of 'Output' block.

4. Submit one concurrent report with output format 'TEST', make sure it is opened via MS Excel in Client PC when clicking button 'View Output' in Standard Request Submission Form.

Oracle reports output in excel

Oracle reports output in excel
While developing a tabular report, its always flexible if the output is generated in an excel spread sheet format. It becomes easy for the user to perform manipulations and other data comparisons.

                                              


So, often the customer demands for an excel output from the oracle reports concurrent program.
Here is the sequence of setup steps to create a report in excel format:
Create Printer file (.prt)
Create Oracle report and specify appropriate printer codes for the fields
Define Printer Driver
Define Printer Style
Associate Printer Driver and Style to a Printer Type
Associate the Printer Type to the Printer
Define concurrent program and specify the report style created
Run report and save the output as text file and open it with excel format.
Create Printer file:
Oracle Reports reads the printer definition file (.prt) specified by the "desformat" parameter to determine the values of certain printer attributes such as the line feed control code, the page break control code, or the control code for bold or italic.  It is called "prt file" after the extension of the file.

Printer file (.prt file) need to be created with printer codes for tab and newline and should be placed in $FND_TOP/$APPLREP Directory.


Oracle supplies a default .prt file located at $ORACLE_HOME/reports/printer. Its contents will look like this.
printer "dflt"
height 66
width 80
after page control(L)
return control(M)
linefeed control(J)

printer "dflt"

This line puts information about the printer. That is to which printer it belongs. From usage point of view it's just a information

height 66  
     Indicates no. of lines which can be printed

width  80  
Reports width in terms of no. of character which can be printed

after page control(L)
This is to break page. So when output file is generated it will have control(J) for Page break

control (M)
for "return key strokes"

control(J)  
for linefeed

So, use the below contents and create a prt file for our purpose (xls output)
printer "Excel Format"
height 66
width 80
code "600" control(J)
code "500" control(I)

Create Oracle report and specify appropriate printer codes for the fields:
Create an oracle report and as created in the prt file, specify &600 for new line and &500 for tab in the objects properties of the report layout.




When printing the first field in the layout of a tabular report, print it on a new line. So, use ‘&600’ in “printer code before” property for that object (F_1).
After printing the first object, leave a tab space. So, use ‘&500’ in “printer code after” property for the same object, as shown below.



Similarly specify for all the objects.




This will enable to print the fields within the repeating frame in a new line and also after  leaving a cell from second record onwards as the printer codes are mentioned in the “Printer Code After” property.
Also in the report output generated if value gets truncated for the fields, the Horizontal elasticity for the field should be set to variable so that the contents of the field will fit in a cell.

Define Printer Style
Navigation Path:
Go to System Administrator Responsibility
Browse through Menu=>Install=>Printer => Style

Define a Printer style and mention the name of the .prt file in the SRW Driver field.



Define Printer Driver
Navigation Path:
Go to System Administrator Responsibility
Browse through Menu=>Install=>Printer => Driver

Create a Printer Driver and mention the .prt file name in the SRW Driver field.

Provide the following in the arguments field.

lpr -P$PROFILES$.PRINTER -#$PROFILES$.CONC_COPIES -T"$PROFILES$.TITLE" $PROFILES$.FILENAME

$PROFILES$.PRINTER retrieves the operating system name of the printer associated with the request.

-P destination the name of the printer from which you wish to print too.

$PROFILES$.CONC_COPIES retrieves the value of the profile option Concurrent: Report Copies, unless this value is updated at runtime.

"$PROFILES$.TITLE" retrieves the title of the output file, typically titled as Application username. Request ID -T calls out the report title to print on a banner or header page.

$PROFILES$.FILENAME calls out the filename of the report to be printed. The value retrieved is the output file name, including the path to the file. 

Associate Printer Style and Driver to a Printer Type

Navigation Path:

Go to System Administrator Responsibility

Browse through Menu=>Install=>Printer => Types

Associate the printer style and driver to a new printer type or to an existing printer type based on the requirement.

Associate the Printer Type to the Printer 

Navigation Path:

System Administrator Responsibility

Menu=>Install=>Printer => Register

Associate the printer type to an existing printer or to a new Printer based on the requirement.

Define concurrent program and specify the created report style 

Provide the printer style while defining the concurrent program and enable the style required checkbox.

Run report and save the output as text file and open it with excel 

Run the concurrent program and save the output generated as text file in your local machine. The output file is a tab delimited text file, now right click and open the file in excel format.

Important GL Tables

Important GL Tables
GL Application Foundation (FND) Tables
Accounting Flexfield - “FND_ID_FLEX_STRUCTURES” & “FND_ID_FLEX_SEGMENTS”
Value Sets - “FND_FLEX_VALUE_SETS”
Segment Values - “FND_FLEX_VALUES” & “FND_FLEX_VALUES_TL”
FND_CURRENCIES (_TL)

GL Application - Setup Tables
Set of Books - “GL_SETS_OF_BOOKS”
Code Combinations - “GL_CODE_COMBINATIONS”
Calendar - “GL_PERIODS”, “GL_PERIOD_STATUSES”
Budget Definitions - “GL_BUDGETS”, “GL_BUDGET_ENTITIES”, “GL_BUDGET_VERSIONS” , "GL_BUDGET_ASSIGNMENT_RANGES"
Daily Rates - "GL_DAILY_RATES"


GL Application - Open Interface Tables
Actuals Interface - “GL_INTERFACE”
Budget Interface - “GL_BUDGET_INTERFACE”
Daily Foreign Currency Rates - “GL_DAILY_RATES_INTERFACE”

GL Application - Transaction & Balance Data Tables
Journal Entries (Transactions) - “GL_JE_BATCHES”,
“GL_JE_HEADERS”,
“GL_JE_LINES”
Balances - “GL_BALANCES”

GL Lookup Values - “GL_LOOKUPS”

GL Application Archive, Log, & History Tables
History - “GL_ALLOC_HISTORY”, “GL_ARCHIVE_HISTORY”, “GL_INTERFACE_HISTORY”
Archive & Purge - “GL_ARCHIVE_BATCHES”

ORACLE TRIGGERS

Definition: – A set of PL/SQL statements stored permanently in database and automatically activated when ever an event raising statement (DML) is executed.

They are stored in USER_TRIGGERS system table
They are used to impose business rules or user defined restrictions on table columns.
They are also activated when tables are manipulated by other application software tools.
They provide high security.
It will execute implicitly whenever the triggering event happens and trigger does not accept arguments.
The act of executing a trigger is known as firing
The trigger event can be a DML (INSERT, UPDATE, or DELETE) operation on database table or certain kinds of views; or system event, such as database startup or shutdown, and certain kinds of DDL operations.
INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view


Trigger Parts: – Four parts are there.

Triggering event
Trigger types
Trigger restriction
Trigger body

Triggering event:- Indicates when to activate the trigger
BEFORE –INSERT/UPDATE/DELETE

AFTER –INSERT/UPDATE/DELETE


Trigger types:- Two types of Triggers are there
Row level trigger- Activates the trigger for every row manipulated by DML statement
Statement level trigger- Activates the trigger only one’s for one DML statement(default
type).


Trigger Restriction: – Used to stop the activation of trigger based on condition. If condition is
TRUE trigger is active.

4.     Trigger body: – A set of PL/SQL statements.





NEW and OLD (pseudo columns):-

Used to retrieve data from DML statement temporary buffer to trigger body
Valid with ROW level trigger only
NEW supports with


Explanation About syntax:-

CREATE [OR REPLACE] TRIGGER <trigger_name> – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.


{BEFORE | AFTER | INSTEAD OF } – This clause indicates at what time the trigger should get fired. i.e. for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. Before and after cannot be used to create a trigger on a view.


{INSERT [OR] | UPDATE [OR] | DELETE} – This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.


[OF column_name] – This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.


CREATE [OR REPLACE] TRIGGER <trigger_name> – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.


[ON table_name] – This clause identifies the name of the table or view to which the trigger is associated.


[REFERENCING OLD AS o NEW AS n] – This clause is used to reference the old and new values of the data being changed. By default, you reference the values as: old.column_name or: new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.


[FOR EACH ROW] – This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e. statement level Trigger).


WHEN (condition) – This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

ROW LEVEL TRIGGER

The trigger body executes once for each row affected by the triggering event. A row level trigger is not executed if the triggering event affects no rows.

Row level triggers are useful if the trigger action depends on data of rows that are affected or on data provided by the triggering event itself.

Example:-1

/* here we are going to write trigger for automatically updating the ‘ename’ when ever we insert the record into ‘emp’ which is shown below*/

SQL>CREATE OR REPLACE TRIGGER up_ename

   BEFORE INSERT

   ON emp

   FOR EACH ROW

BEGIN

   :NEW.ename := UPPER (:NEW.ename);

END;

– Trigger created.

Now we are going to insert record into ‘emp’ table

/* here we are going to insert ‘ename’ in LOWER case*/

SQL> INSERT INTO emp (empno, ename,  sal,  comm, deptno)

                 VALUES (7711, 'andrew',3500, 500,  10);

Output:-

SELECT * FROM emp WHERE empno=7711;

7711 ANDREW 3500 500 10

/* here we can see that ename is in UPPER case, now we can conclude that above trigger what we had written is fired at this event*/

Example2:-

/*here we trying to decrease the salary by updating then below trigger will raise*/

CREATE OR REPLACE TRIGGER up_sal BEFORE UPDATE ON emp FOR EACH ROW

BEGIN

    IF :NEW.sal<:OLD.sal THEN

        RAISE_APPLICATION_ERROR(-20000,'updating salary must greater than existing salary');

    END IF;

END;

Now we are going to decrease the ‘sal’

UPDATE emp SET sal=sal-1000 WHERE empno=7839;

Output:-

/* here we can see that trigger raised*/

Error report:

SQL Error: ORA-20000: updating salary must greater than existing salary

ORA-06512: at "APPS.UP_SAL"

ORA-04088: error during execution of trigger 'APPS.UP_SAL'

                     STATEMENT LEVEL TRIGGER

Statement level Trigger:-

Activates the trigger for only once for one DML statement.

A statement trigger is fired once on behalf of the triggering event, even if no rows are affected at all.

Statement triggers are useful if the trigger action does not depend on the data from rows that are affected or on data provided by the triggering event itself: for example, a trigger that performs a complex security check on the current user.



Example:-

/*here we are trying to INSERT record INTO ‘emp’ table in week ends…. Below trigger will fire when we try to insert record in week ends*/
CREATE OR REPLACE TRIGGER sec_emp
 BEFORE INSERT ON emp
BEGIN
         IF TO_CHAR (SYSDATE,'DY') in ('SAT','SUN') THEN
               RAISE_APPLICATION_ERROR (-20101,'in week ends we can not insert record');
         END IF;
END;


Now are trying to insert into emp in week ends and we can see the output below

/* Before inserting change the system date to saturday or sunday date */ SQL>INSERT INTO EMP(EMPNO,                        ENAME,                        DEPTNO)            VALUES (7913,                       ’mike’,                        10); Output:- Error report: SQL Error: ORA-20101: in week ends we can not insert record ORA-06512: at “APPS.SEC_EMP” ORA-04088: error during execution of trigger ‘APPS.SEC_EMP’
RAISE_APPLICATION_ERROR:- it is built in function that stops the DML operation and displays the error message.

                              Trigger Execution Hierarchy

PL/SQL Trigger Execution Hierarchy

The following is the sequence in which different triggers are fired.

Before statement level

Before row level

After row level

After statement level

For Example: Let’s create a table ‘test’ to store messages when triggers are fired.

CREATE TABLE test

(Message varchar2(2000));

1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table ‘test’ before a sql update statement is executed, at the statement level.

CREATE OR REPLACE TRIGGER emp_sal_upd_stmt_level_trigBEFORE UPDATE ON emp

BEGIN

   DBMS_OUTPUT.PUT_LINE('Updating employees ');

   INSERT INTO test values('Stmt level Before update - Updating employees ');

END;

2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table ‘test’ before each row is updated.

CREATE OR REPLACE TRIGGER emp_sal_upd_stmt_level_trig

BEFORE UPDATE ON emp

FOR EACH ROW

DECLARE

    sal_diff       NUMBER;

BEGIN

    sal_diff := :NEW.sal - :OLD.sal;

    DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);

    DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);

    DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);

    DBMS_OUTPUT.PUT_LINE('..Raise     : ' || sal_diff);

Insert into test values('Row level Before update - Updating employee ' || :OLD.empno);

END;

/

3) AFTER UPDATE, Statement Level: This trigger will insert a record into the table ‘product_check’ after a sql update statement is executed, at the statement level.

 CREATE or REPLACE TRIGGER emp_sal_aftr_upd_stmt_level_trig

 AFTER

 UPDATE ON emp

 BEGIN

 DBMS_OUTPUT.PUT_LINE('Updating employees ');

 INSERT INTO test values('Stmt level After update - Updating employees ');

 END;

/

4) AFTER UPDATE, Row Level: This trigger will insert a record into the table ‘product_check’ after each row is updated.

CREATE OR REPLACE TRIGGER emp_sal_aftr_upd_row_level_trig

AFTER UPDATE ON emp

FOR EACH ROW

DECLARE

    sal_diff       NUMBER;

BEGIN

    sal_diff := :NEW.sal - :OLD.sal;

    DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);

    DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);

    DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);

    DBMS_OUTPUT.PUT_LINE('..Raise     : ' || sal_diff);

INSERT INTO test values('Row level after update - Updating employee ' || :OLD.empno);

END;

/

Now lets execute a update statement on table emp.

 UPDATE emp SET sal = 2000

 WHERE deptno = 10;

Let’s check the data in test table to see the order in which the trigger is fired.

 SQL>SELECT * FROM test;

                                  Mutating Triggers

When we perform two DML operations at one’s then it leads to ‘Trigger Mutating’ error while working with software tools.

If trigger applied on table1 (before insert on table1) performing any other DML or DRL on same table(table1) in trigger body causes “Mutating Error”.

Example1:-

/*here we are deleting the row and at the same time we are selecting(DRL) the record then it leads to mutating error*/

CREATE OR REPLACE TRIGGER mut_trig

 AFTER DELETE ON emp FOR EACH ROW

 DECLARE

           x number;

 BEGIN

          SELECT sal

                INTO x

              FROM emp

           WHERE ename='SMITH';

 INSERT INTO emp(empno,ename,deptno) VALUES(9999,'APPS',10);

 DBMS_OUTPUT.PUT_LINE('SALARY =>'||x);

 END;

Now we can delete the record from ‘emp’

SQL>DELETE FROM emp;

Output:-

Error starting at line 'x' in command:

DELETE FROM emp

Error report:

SQL Error: ORA-04091: table APPS.EMP is mutating, trigger/function may not see it

ORA-06512: at "APPS.MUT_TRIG", line 'y'

ORA-04088: error during execution of trigger 'APPS.MUT_TRIG'

Example2:-

/*here we are deleting the row and at the same time we are updating(DML) the record then it leads to mutating error*/

CREATE OR REPLACE TRIGGER mutuating_trig

 AFTER DELETE ON emp FOR EACH ROW

 DECLARE

 BEGIN

 UPDATE emp SET sal=sal+sal*.05

           WHERE ename='SMITH';

 END;

Now we can delete the record from ‘emp’

SQL>DELETE FROM emp;

Output:-

Error starting at line 'x' in command:

DELETE FROM emp

Error report:

SQL Error: ORA-04091: table APPS.EMP is mutating, trigger/function may not see it

ORA-06512: at "APPS.MUTUATING_TRIG", line 'y'

ORA-04088: error during execution of trigger 'APPS.MUTUATING_TRIG'