R12 BANK ACCOUNTS-SUPPLIER AND CUSTOMER

- Banks and their Branches are now each stored as Parties (HZ_PARTIES) in their own right. They are linked together through Relationships(HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

- The Bank Accounts themselves are now stored in the new Oracle Payments Application

- Below are the Key tables where the Bank Account information is stored

IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL

- Below are the Key tables where the Bank Data of R12 TCA is stored

HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_ORGANIZATION_PROFILES
HZ_CONTACT_POINTS
HZ_ORG_CONTACT
HZ_ORG_CONTACT_ROLES
The following query gives you the links required for matching a Bank Account to its Supplier Site Record
SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;

The following query gives you the links required for matching a Bank Account to its Customer Site Record:
SELECT cust.party_name customer_name
, cust_acct.account_number
, cust_uses.site_use_code
, cust_loc.address1
, cust_loc.address2
, cust_loc.address3
, cust_loc.address4
, cust_loc.city
, cust_loc.postal_code
, bank.party_name bank_name
, bank_prof.home_country
, branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
, account.bank_account_num
, account.bank_account_name
FROM hz_parties bank
, hz_relationships rel
, hz_parties branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
, iby_ext_bank_accounts account
, iby_account_owners acc_owner
, iby_external_payers_all ext_payer
, iby_pmt_instr_uses_all acc_instr
, hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
WHERE 1=1
AND bank.party_id = rel.object_id
and bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = cust_acct.cust_account_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust_uses.location = cust_loc.location_id
AND cust.party_id = cust_acct.party_id;

Register a custom table in Oracle 11i/R12

Register a custom table in Oracle 11i/R12
You register your custom application tables using a PL/SQL routine in the AD_DD package.
Flexfields and Oracle Alert are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their

columns) that will be used with flexfields or Oracle Alert.
You can also use the AD_DD API to delete the registrations of tables and columns. You should delete the column registration first, then the table registration.
To alter a registration you should first delete the registration, then re-register the table or column.
The AD_DD API does NOT check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that

the tables and columns registered actually exist and have the same format as that defined using the AD_DD API.
You need NOT register views.
You should include calls to the table registration routines in a PL/SQL script.
You should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.
Procedures in the AD_DD Package:

 procedure register_table (p_appl_short_name in varchar2,
                       p_tab_name    in varchar2,
                       p_tab_type    in varchar2,
                       p_next_extent in number default 512,
                       p_pct_free    in number default 10,
                       p_pct_used    in number default 70);

p_appl_short_ name: The application short name of the application that owns the table (usually your custom application).
p_tab_name: The name of the table (in uppercase letters).
p_tab_type: Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table.
p_next_extent: The next extent size, in kilobytes. Do not include the 'K'.
p_pct_free: The percentage of space in each of the table's blocks reserved for future updates to the table (1-99).
p_pct_used: Minimum percentage of used space in each data block of the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.

procedure register_column (p_appl_short_name in varchar2,
                       p_tab_name   in varchar2,
                       p_col_name   in varchar2,
                       p_col_seq    in number,
                       p_col_type   in varchar2,
                       p_col_width  in number,
                       p_nullable   in varchar2,
                       p_translate  in varchar2,
                       p_precision  in number default null,
                       p_scale      in number default null);

p_col_name: The name of the column (in uppercase letters).
p_col_seq: The sequence number of the column in the table (the order in which the column appears in the table definition).
p_col_type: The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
p_col_width: The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
p_nullable: Use 'N' if the column is mandatory or 'Y' if the column allows null values.
p_translate: Use 'Y' if the column values will be translated for an Oracle E-Business Suite product release (used only by Oracle E-Business Suite products) or 'N' if

the values are not translated (most application columns).
p_precision: The total number of digits in a number.
p_scale: The number of digits to the right of the decimal point in a number.

procedure delete_table  (p_appl_short_name in varchar2,
                       p_tab_name    in varchar2);

procedure delete_column (p_appl_short_name in varchar2,
                       p_tab_name    in varchar2,
                       p_col_name    in varchar2);

Example:

EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);

EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID', 1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE', 2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SUMMARY_FLAG', 8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG', 9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16, 'VARCHAR2', 60, 'Y', 'N');

registering primary keys

Begin
ad_dd.register_primary_key('XXCUS','LINES_FK','XX_LINES','number','S','Y','Y');
end;

BEGIN
ad_dd.register_primary_key_column('XXCUS','LINES_FK','XX_LINES','ORDER_NO',1);
END;

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