Oracle E-Business Suite R12 Rapid forms development using Developer 6i forms

December 18, 2012

 

Moving from client server architecture to R12 based domains could be quite challenging for Oracle developers, especially those who have spent years, enjoying the flexibility of testing forms modules from the local development machines prior moving the “working” modules to the production instances.

Let us see the general development scenario for R12 instances.

  1. From 10g forms, connect to database
  2. open TEMPLATE.fmb and save as “XXMYFORM” etc
  3. Then keep on adding the elements

Now, move the form module to $AU_TOP/forms/US and compile it to respective application repositories

Create form, menu items…

A long list and the most frustrating is keeping on FTP the slightly modified forms module to the R12 instance to compile and testing.

We have came across a quick resolution for this PIA(Pain in the ASS) approach (for forms modules which do not use folder views) by using developer 6i (Uhu, beware Oracle fan boys would tell you, Oracle does not support developer 6i any further)

Create a template.fmb using Developer 6i forms

Change the system coordinates to use inches in the place of points

image

  1. Save the template.fmb
  2. Using save as, save your template.fmb as a new fmb file (eg: aa.fmb)
  3. Connect to apps database instance and start adding windows, canvases, blocks etc to the form and TEST IT locally!!!
  4. TEST it until the results are as expected.
  5. Make a backup for your working module (copy aa.fmb to bb.fmb for example)

 

Open your aa.fmb file, developed using forms 6i with Developer suite 10g forms designer (You will get prompts stating the possible conversions for triggers, coding etc)

Open TEMPLATE.fmb and save it as “MYAPPSFORM.fmb”

Now, please give maximum attention towards copying the objects from your 6i module which is opened with Developer 10g forms, TO your form module which will go to apps instance

  1. Copy all the windows you have created with 6i module to 10g forms
  2. Copy all the canvases you have created with 6i module to 10g forms
  3. Copy all the blocks you have created with 6i module to 10g forms
  4. Copy record groups, lovs, form level triggers if any

Now, try to compile the 10g form. If you have copied everything from 6i, this new form shouldn’t give you any compilation errors.

Move the form to apps instance, compile, create forms, menus and do final testing, confirming everything is working fine.

regards,

admin

Advertisements

Oracle E-Business suite custom application development-How to submit a concurrent request from a FORM module through push button click

November 23, 2012

 

Required: FNDCONC.pll attached to custom form

with when-button-pressed trigger, attach the following code (Please remove what is not applicable in your case)

Declare
  l_ord_num               NUMBER := 0;
   l_ord_type_name         VARCHAR2 (240);
   l_req_id_Rep            NUMBER;
   l_request_completed     BOOLEAN := FALSE;
   l_req_phase             VARCHAR2 (20);
   l_req_status            VARCHAR2 (1000);
   l_req_dev_phase         VARCHAR2 (1000);
   l_req_dev_status        VARCHAR2 (1000);
   l_req_message           VARCHAR2 (1000);
   l_conc_mgr_status       NUMBER;
   p_call_stat             NUMBER;
   p_activep_stat          NUMBER;

   l_order_category_code   NUMBER;
   l_inv_report_name       VARCHAR2 (40);
   l_ret_report_name       VARCHAR2 (40);
   l_req_id                                 NUMBER;
   l_order_type_name                VARCHAR2(30);
Begin
  l_req_id :=
      fnd_request.submit_request (‘ONT’,—Actual application short name
                                  l_inv_report_name,—Short name of concurrent program, please note this is not the executable name
                                  NULL,—Description not required
                                  SYSDATE,—start time, not required
                                  FALSE,—subsequent report name, not required

/*You can pass a total of 100 parameters, just make sure to pass them in the same order you have defined them in the parameter session for the concurrent program*/
                                  :ORDERS.OE_ORDER_NUMBER,                                   l_order_type_name,                                   NULL,
                                  NULL,
                                  apps.fnd_profile.VALUE (‘ORG_ID’));

:SYSTEM.Message_Level := ’25’;
   COMMIT;

   l_request_completed :=
      fnd_concurrent.wait_for_request (request_id   => l_req_id,
                                       INTERVAL     => 1,
                                       phase        => l_req_phase,
                                       status       => l_req_status,
                                       dev_phase    => l_req_dev_phase,
                                       dev_status   => l_req_dev_status,
                                       MESSAGE      => l_req_message);

—   :SYSTEM.Message_Level := ’25’;
   COMMIT;

:SYSTEM.Message_Level := ‘0’;

   editor_pkg.report (l_req_id, ‘Y’);

/*for displaying the pdf automatically with a new window or tab after the concurrent request completes successfully*/
END;

This should provide you the desired functionality.

regards,

admin


Oracle Descriptive flexfields, hands-on training

June 23, 2012

 

(This document was prepared from a blog/website entry and unfortunately the actual link was not copied while we made a local document, if you are the original author of this article, please contact us and we will add the document credits to you)

 

A training article on Descriptive flexfields, also refered as DFF

First some basic Question and answers, and then we will do screenshots detailing how flexfields are configured.
Question: What does DFF mean?
Answer: DFF is a mechanism that lets us create new fields in screens that are delivered by Oracle.
Question: Oh good, but can these new fields be added without modifying/customization of the screen?.
Answer: Yes, certainly. Only some setup is needed, but no programmatic change is needed to setup DFF.
Question: Why the word Descriptive in Name DFF?
Answer: I think Oracle used this terminology because by means of setup…you are describing the structure of these new fields. Or may be Oracle simply used a silly word to distinguish DFF from KFF(discussed in latter training lesson).
Question: Are these DFF’s flexible?
Answer: A little flexible, for example, depending upon the value in a field, we can make either Field1 or Field2 to appear in DFF.
Question: So we create new fields in existing screen, but why the need of doing so?
Answer: Oracle delivers a standard set of fields for each screen, but different customers have different needs, hence Oracle lets us create new fields to the screen.
Question: Are these new fields that get created as a result of DFF free text?
I mean, can end user enter any junk into the new fields that are added via DFF?
Answer: If you attach a value set to the field(at time of setup of dff), then field will no longer be free text. The entered value in the field will be validated, also a list of valid values will be provided in LOV.
Question : Will the values that get entered by the user in dff fields be updated to database?
Answer: Indeed, this happens because for each field that you create using DFF will be mapped to a column in Oracle Applications.
Question: Can I create a DFF on any database column?
Answer: Not really. Oracle delivers a predefined list of columns for each table that are meant for DFF usage. Only those columns can be mapped to DFF segments. These columns are named similar to ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3 ETC. Usually Oracle provides upto 15 columns, but this number can vary.
Question: Can I add hundreds of fields to a given screen?
Answer: This depends on the number of attribute columns in the table that screen uses. Also, those columns must be flagged as DFF enabled in DFF Registration screen. Don’t need to worry much about this because all the ATTRIBUTE columns are by default flagged for their DFF usage.
Question: Hmmm, I can see that DFFs are related to table and columns…
Answer: Yes correct. Each DFF is mapped to one table. And also each segment(or call it field) is mapped to one of the attribute columns in that table.
Question: I want these fields to appear in screen only when certain conditions are met. Is it possible?
Answer: Yes, we have something known as Context Sensitive Descriptive Flexfields.
In Order to do this, we will follow the below steps(screenshots will follow) :-
1. Navigate to the DFF Registration screen in Oracle Apps and query on Table AP_BANK_BRANCES. Now click on Reference Field
2. Navigate to DFF Segments screen and query on the Title of the “Bank Branch” and Unfreeze the Flexfield and add segments as to Section “GLOBAL Data Elements” as shown in screenshots.

Here are the screenshots……The descriptions are embedded within the screenshots.
We are in “Bank Branches screen” below, that is available in Payables responsibility. We need to add a new field as below.
clip_image001
Once having noted down the table, we try to find the Title of the DFF for that Table. We go to Flexfield/Register
clip_image002
Here we pick the Title of the respective DFF
clip_image003
Query on that DFF Title from Descriptive Flexfield Segment Screen
clip_image004
Add a new segment under “Global Data Elements”
clip_image005
The options for making mandatory or enabling validations for the new field.
clip_image006

Once you finalize the changes, you will be prompted to Freeze the DFF definition. Click on OK
clip_image007
Now, we see the fruits of our configuration
clip_image008

for Windows7bugs

Admin


Oracle EBS R12 how to call standard API/packages from custom form or reports

April 17, 2011

Okay guys and gals

We had a requirement to use certain Oracle standard API/packages from custom developer forms. After loads of struggle and asking various Oracle related forums, we hardly had a chance to get any satisfactory answers to our query “how to call oracle standard API or packages through custom forms or reports”

Finally with the help of a long term friend cum technical support person Mr. Anil Menon, we were able to successfully call the standard API or package call with our custom (test) form.

For the example we used “apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES” procedure which indirectly refers fnd_api.g_false and apps.inv_quantity_tree_pub.g_transaction_mode parameters/constants defined in the package definitions.

Actual call to the API through a PL/SQL environment (Toad/Oracle PL/SQL) code is as following

SET SERVEROUTPUT ON;
DECLARE
v_api_return_status VARCHAR2 (1);
v_qty_oh NUMBER;
v_qty_res_oh NUMBER;
v_qty_res NUMBER;
v_qty_sug NUMBER;
v_qty_att NUMBER;
v_qty_atr NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2(1000);
v_inventory_item_id VARCHAR2(250);
v_organization_id VARCHAR2(10);
BEGIN

v_inventory_item_id := 24445;
v_organization_id := 901;

inv_quantity_tree_grp.clear_quantity_cache;
DBMS_OUTPUT.put_line ('Transaction Mode');
DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );
DBMS_OUTPUT.put_line ('Organization :'|| v_organization_id);
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => v_api_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_organization_id => v_organization_id,
p_inventory_item_id => v_inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => 'ABC-01', --NULL for whole org
p_locator_id => NULL,
x_qoh => v_qty_oh,
x_rqoh => v_qty_res_oh,
x_qr => v_qty_res,
x_qs => v_qty_sug,
x_att => v_qty_att,
x_atr => v_qty_atr);
DBMS_OUTPUT.put_line ('on hand Quantity :'|| v_qty_oh);
DBMS_OUTPUT.put_line ('Reservable quantity on hand :'|| v_qty_res_oh);
DBMS_OUTPUT.put_line ('Quantity reserved :'|| v_qty_res);
DBMS_OUTPUT.put_line ('Quantity suggested :'|| v_qty_sug);
DBMS_OUTPUT.put_line ('Quantity Available To Transact :'|| v_qty_att);
DBMS_OUTPUT.put_line ('Quantity Available To Reserve :'|| v_qty_atr);
END;

This PL/SQL procedure will successfully print the results during a PL/SQL session through Toad or Oracle PL/SQL session. However, if you would try to invoke the same code through a PL/SQL procedure from a developer form development instance, the errors will start from the call to “fnd_api.g_false” stating “Implementation restriction: ‘APPS.FND_API.G_FALSE’ cannot directly access remote package variable or cursor. Under the package specification you will find G_FALSE constant has the the value ‘F’ and apps.inv_quantity_tree_pub.g_transaction_mode has a value ‘2’ pre-defined.

Umm, not a straight forward method to dwell the packages, find the constant values and modifying the PL/SQL script. For a small API call like above one, a programmer could use this approach as a work around, however, while dealing with APIs which take 10s of parameters, it could be really tiring.

Here is the workaround we used

Created a custom database level package


CREATE OR REPLACE PACKAGE XX_CHK_QTY IS
PROCEDURE retrive_quantity(item_id NUMBER, org_id NUMBER, subinv VARCHAR2, oqtt OUT NUMBER, oqtr OUT NUMBER);
END;

and created corresponding package body as following


CREATE OR REPLACE PACKAGE BODY XX_CHK_QTY AS
PROCEDURE retrive_quantity(item_id NUMBER, org_id NUMBER, subinv VARCHAR2, oqtt OUT NUMBER, oqtr OUT NUMBER) IS
v_api_return_status  VARCHAR2 (1);
v_qty_oh             NUMBER;
v_qty_res_oh         NUMBER;
v_qty_res            NUMBER;
v_qty_sug            NUMBER;
v_qty_att            NUMBER;
v_qty_atr            NUMBER;
v_msg_count          NUMBER;
v_msg_data           VARCHAR2(1000);
--v_inventory_item_id  VARCHAR2(250);
--v_organization_id    VARCHAR2(10) ;
BEGIN
inv_quantity_tree_grp.clear_quantity_cache;
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
(p_api_version_number  => 1.0,
p_init_msg_lst        => apps.fnd_api.g_false,
--p_init_msg_lst        => 'F',
x_return_status       => v_api_return_status,
x_msg_count           => v_msg_count,
x_msg_data            => v_msg_data,
p_organization_id     => org_id, --v_organization_id,
p_inventory_item_id   => item_id, --v_inventory_item_id,
p_tree_mode           => apps.inv_quantity_tree_pub.g_transaction_mode,
--p_tree_mode           => 2,
p_onhand_source       => 3,
p_is_revision_control => FALSE,
p_is_lot_control      => FALSE,
p_is_serial_control   => FALSE,
p_revision            => NULL,
p_lot_number          => NULL,
p_subinventory_code   => subinv,
p_locator_id          => NULL,
x_qoh                 => v_qty_oh,
x_rqoh                => v_qty_res_oh,
x_qr                  => v_qty_res,
x_qs                  => v_qty_sug,
x_att                 => v_qty_att,
x_atr                 => v_qty_atr);
oqtt := v_qty_att;
oqtr := v_qty_atr;
END;
END XX_CHK_QTY;


As you could see we are using 2 numbers of OUT variables to store the output data, which will be referred in the forms at later stages.

Our sample form has a control block and 3 items

:ITEM_ID, :QTY_AVL_TRANSACT,:QTY_AVL_RESERVE and under the WVI(WHEN-VALIDATE-ITEM) scope for the block item :ITEM_ID  we wanted to populate total quantity available to transact and total quantity available to reserve into block items :QTY_AVL_TRANSACT,:QTY_AVL_RESERVE

Following is how we call the custom procedure through WVI trigger:

Declare
oqtt number := 0;
oqtr number := 0;
Begin
apps.XX_CHK_QTY.retrive_quantity(:CTRL.ITEM_ID,901,’ABC-01′,oqtt,oqtr);
:CTRL.QTY_AVL_TRANSACT := oqtt;
:CTRL.QTY_AVL_RESERVE := oqtr;
End;

and bingo! as the procedure was called from a database package, there were no compilation errors pointing towards referring remote variables or cursors and once executed all intended results were fetched to corresponding block items.

As we are also learning how to use Oracle APIs within custom forms/reports, we hope this guideline would be useful for those few who are trying to find a way to start calling Oracle’s standard API/packages from such environments.

for Windows7bugs

Admin