Submitting a concurrent request from a FORM module through button click....

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;

Submit concurrent program FND_REQUEST.SUBMIT_REQUEST along with XML Publisher layout and printer options

FND_REQUEST.SUBMIT_REQUEST submits concurrent request to be processed by a concurrent manager.

Using submit_request will only submits the program and will not attach any layout or print option. Code below will help to set XMLpublisher template/layout along with print option.

*****Add_layout and Add_printer procedures are optional in calling submit_request. Use only if you need to set them.

Layout is submitted to a concurrent request using below procedure

fnd_request.add_layout (

                    template_appl_name   => 'Template Application',

                    template_code        => 'Template Code',

                    template_language    => 'en', --Use language from template definition

                    template_territory   => 'US', --Use territory from template definition

                    output_format        => 'PDF' --Use output format from template definition

                     );

Setting printer while submitting concurrent program

fnd_submit.set_print_options (printer      => lc_printer_name

                                   ,style        => 'PDF Publisher'

                                   ,copies       => 1

                                   );

fnd_request.add_printer (

                    printer => printer_name,

                    copies  => 1);

DECLARE

   lc_boolean        BOOLEAN;

   ln_request_id     NUMBER;

   lc_printer_name   VARCHAR2 (100);

   lc_boolean1       BOOLEAN;

   lc_boolean2       BOOLEAN;

BEGIN

      -- Initialize Apps  

      fnd_global.apps_initialize (>USER_ID<

                                 ,>RESP_ID<

                                 ,>RESP_APPL_ID<

                                 );

   -- Set printer options

   lc_boolean :=

      fnd_submit.set_print_options (printer      => lc_printer_name

                                   ,style        => 'PDF Publisher'

                                   ,copies       => 1

                                   );

   --Add printer 

   lc_boolean1 :=

                fnd_request.add_printer (printer      => lc_printer_name

                                         ,copies       => 1);

  --Set Layout

  lc_boolean2 :=

               fnd_request.add_layout (

                            template_appl_name   => 'Template Application',

                            template_code        => 'Template Code',

                            template_language    => 'en', --Use language from template definition

                            template_territory   => 'US', --Use territory from template definition

                            output_format        => 'PDF' --Use output format from template definition

                                    );

   ln_request_id :=

      fnd_request.submit_request ('FND',                -- application

                                  'COCN_PGM_SHORT_NAME',-- program short name

                                  '',                   -- description

                                  '',                   -- start time

                                  FALSE,                -- sub request

                                  'Argument1',          -- argument1

                                  'Argument2',          -- argument2

                                  'N',                  -- argument3

                                  NULL,                 -- argument4

                                  NULL,                 -- argument5

                                  'Argument6',          -- argument6

                                  CHR (0)               -- represents end of arguments

                                 );

   COMMIT;

   IF ln_request_id = 0

   THEN

      dbms.output.put_line ('Concurrent request failed to submit');

   END IF;

END;

Submitting a concurrent request from a FORM module through button click....

Required: FNDCONC.pll attached to custom formwith 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;

Submit concurrent program FND_REQUEST.SUBMIT_REQUEST along with XML Publisher layout and printer options

FND_REQUEST.SUBMIT_REQUEST submits concurrent request to be processed by a concurrent manager.

Using submit_request will only submits the program and will not attach any layout or print option. Code below will help to set XMLpublisher template/layout along with print option.

*****Add_layout and Add_printer procedures are optional in calling submit_request. Use only if you need to set them.

Layout is submitted to a concurrent request using below procedure

fnd_request.add_layout (

                    template_appl_name   => 'Template Application',

                    template_code        => 'Template Code',

                    template_language    => 'en', --Use language from template definition

                    template_territory   => 'US', --Use territory from template definition

                    output_format        => 'PDF' --Use output format from template definition

                     );

Setting printer while submitting concurrent program

fnd_submit.set_print_options (printer      => lc_printer_name

                                   ,style        => 'PDF Publisher'

                                   ,copies       => 1

                                   );

fnd_request.add_printer (

                    printer => printer_name,

                    copies  => 1);

DECLARE

   lc_boolean        BOOLEAN;

   ln_request_id     NUMBER;

   lc_printer_name   VARCHAR2 (100);

   lc_boolean1       BOOLEAN;

   lc_boolean2       BOOLEAN;

BEGIN

      -- Initialize Apps  

      fnd_global.apps_initialize (>USER_ID<

                                 ,>RESP_ID<

                                 ,>RESP_APPL_ID<

                                 );

   -- Set printer options

   lc_boolean :=

      fnd_submit.set_print_options (printer      => lc_printer_name

                                   ,style        => 'PDF Publisher'

                                   ,copies       => 1

                                   );

   --Add printer 

   lc_boolean1 :=

                fnd_request.add_printer (printer      => lc_printer_name

                                         ,copies       => 1);

  --Set Layout

  lc_boolean2 :=

               fnd_request.add_layout (

                            template_appl_name   => 'Template Application',

                            template_code        => 'Template Code',

                            template_language    => 'en', --Use language from template definition

                            template_territory   => 'US', --Use territory from template definition

                            output_format        => 'PDF' --Use output format from template definition

                                    );

   ln_request_id :=

      fnd_request.submit_request ('FND',                -- application

                                  'COCN_PGM_SHORT_NAME',-- program short name

                                  '',                   -- description

                                  '',                   -- start time

                                  FALSE,                -- sub request

                                  'Argument1',          -- argument1

                                  'Argument2',          -- argument2

                                  'N',                  -- argument3

                                  NULL,                 -- argument4

                                  NULL,                 -- argument5

                                  'Argument6',          -- argument6

                                  CHR (0)               -- represents end of arguments

                                 );

   COMMIT;

   IF ln_request_id = 0

   THEN

      dbms.output.put_line ('Concurrent request failed to submit');

   END IF;

END;

Custom form solutions  

• Unable to Run Oracle forms locally (without deploying on server)  from form builder 
• Issue :  i) a plugin is needed to display this content 
     ii)  FRM-10142: The HTTP Listener is not running 

1. To run forms locally we must have jinitiator installed
2. Check file -> preferences and set the application server URL to default

 Select browser as internet explorer 

iii) Check your java version

iv) Here we consider 1.6.0.0
Open the formweb.cfg from below location 

Change the information as higlighted below , this is based on the java version you are using in your system




5. Start the server as below and run the form, you should see the form is opening by activating the java which will be promoted when the form is opened in the browser.Enjoy 

• Who columns in custom Oracle forms

http://oraclemaniac.com/2012/11/20/how-to-enable-record-history-for-a-custom-form/

• Set Environment Variable

cd $APPL_TOP
ls *.env
.space(copy white environment variable name from ls n enter  )

• Adding Coloumns to already built forms

Don’t do this -- right click block and add coloums by the data block wizard as it will re create the all items of that datablock and mess everything


Do this --- add fields directly by drag and drop on canvas and make them database item and map their column with database column. Set their class property and respective properties

• Item movement on Canvas can be set interms of pixel, cm , inches

View -> customise ruler guide

• Adding Calender to text field

http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T457082.htm#I_calendar


Also pay attention that textbox on which you are popping out the calender should have its property class as TEXT_ITEM_DATE  and not  as TEXT_ITEM  ,


1. Use calendar.show; in key list val trigger
2. Make lov of item to enable_list_lamp
3. Make validate from list to no

• LOV List of values in form

4. Create a record group
5. Add query in property of record group record group query
6. Create lov by wizard , attach record group to lov in its property

• Showing Runtime Message  / Fnd_message

IF :XXQLG_VAT_RATE_TBL.end_date > :XXQLG_VAT_RATE_TBL.start_Date
THEN
FND_MESSAGE.SET_STRING('End Date  should be greater than Start date.');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;

• Two Windows on one form /Multiple window closing

  if (wnd = 'MAIN_WINDOW') then   
    app_window.close_first_window;
  elsif (wnd = 'POPUP_WINDOW') then   
  go_block('XX_QVC_EAM_ASSET_HISTORY_TBL');
    --defer relations
    --close related windows               
  elsif (wnd = '<test>') then 
    --defer relations
    --close related windows   
    null;
  end if;




When you have multiple windows and multiple form then always remember that when you close one window having datablock ,its necessary to set the navigation to other block (whatever b the window)  , by default forms set to the previous block (assigned in properties of block ) if you want to set the specific block to be navigated when window is closed mention the block name in previous nevigation data block property


You can use the hide_window property in when new form instance

• How to suppress the system messages

:system.message_level := 0;
:system.message_level := 25;

• GLOBAL Variable

Global variable are declare above the declare
It don’t have datatype its syntax is :GLOBAL.s:='1';  and we can access it as :global.s

• Random Number in ORACLE

SELECT substr(dbms_random.value(10, 99),-2)
FROM DUAL;   this will create random number value from 10 to 99

 General Issues in Oracle Custom Form Development 


• Form not displyaing proper output/ only lable item showed
Change the form name
Create new menu / function /form and attach the new renamed form to that menu


Or restart the puttty and winscp
Check out appstand.fmb file is there in the resource folder at AU_TOP


• Cntrl+ MAJ+K --- shows shortcut available for in form
 forms not running


Go to folder where Jinitiator reside normally c:/Program Files/Oracle....... and search for the file "jvm.dll"
3. Rename the "jvm.dll" to any name you want.
4. If you are using Forms 10g, go to its folder i.e c:/devsuite_*/jre and search for the same file "jvm.dll" in that folder.
5. Then copy that "jvm.dll" to the folder of Jiniatiator mentioned in step 2. replacing the jvm.dll that you renamed.


• Forms not running
Install all setup files present in below folder and set default browser as netscape navigator (edit->preferences->runtime)


D:\swapnil dandgvhal\reading stuff\form builder not running forms 


• fmx /fmb not find


some time you check path of fmb / fmx from front end and check out in server according to that path but its not found on that path because its stored in custom path rather than oracle apss seeded path


• Progress indicator issue …..


Solution : follow the reigistration steps of custom form , when windows name is  not properly set in app.custom package and pre_from trigger  we will get progress indicator after closing the form







•  Multiple windows and canvas in same form 
You can get the same issue when there is conflict between windows set while colsing the window for multiple windows related same form we have to mention the second window name  in custom app package


  if (wnd = 'MAIN_WINDOW') then   
    app_window.close_first_window;
  elsif (wnd = 'POPUP_WINDOW') then   
  go_block('XX_QVC_EAM_ASSET_HISTORY_TBL');
    --defer relations
    --close related windows               
  elsif (wnd = '<test>') then 
    --defer relations
    --close related windows   
    null;
  end if;






• FMRWEB.res   enabling disabling short cut key on oracle custom form  this file is located at
D:\DevSuiteHome_1\forms

Registering oracle custom form to Application (using template.fmb)


•  Prerequisites


To do this we have to first download the few files from the server which are located in the resource folder along with them we have to downlaod the template.fmb file and the appstand.fmb file from the server , we have to place all this files to the resource folder (whose path will be given in environment variable.


To attach form to application we have to use the template.fmb file  , this is used for custom form


--Fist copy the resource folder from the server where you want to attach form , which have all its pll and all required files  (resources reside in the appl top )
--set environment variable for form builder for R12 variable name should be forms_path for  6i variable name should be form60_path
--also copy template.fmb and appstand.fmb file from server
--open this template.fmb if opening it from form builder is showing dirrernet error then just close the form builder and open the template.fmb file direclty .
--- chang the name of template .fmb 






1. Download the required fmb(s) and pll(s) from the server.
2. Develop the custom form in forms developer.
3. Move the form into server.
4. Compile the form file.
5. Form registration.
6. Form function registration.
7. Attaching the form function into menu.


Form path on local pc
Mycoputer -> right click select  prpoerties ->advanced ->environment variable  if adding form path to user variable not working then add form path to the system variable





We have to set this because in oracle apps server all form are being stored on AU top  ( cd $AU_TOP) where there is a folder  resource folder which holds
All the important .pll files which are being used in the all forms .








Required PLL files  for any custom form development are


APPCORE.pll
APPCORE2.pll
APPDAYPK.pll
APPFLDR.pll
CUSTOM.pll
FNDSQF.pll
FV.pll
GHR.pll
GLOBE.pll
GMS.pll
HRKPI.pll
IGILUTIL.pll
IGILUTIL2.pll
JA.pll
JE.pll
JL.pll
OPM.pll
PQH_GEN.pll
PSA.pll
PSAC.pll
PSB.pll
VERT.pll
VERT1.pll
VERT2.pll
VERT3.pll
VERT4.pll
VERT5.pl
Appstand.fmb


For each item , each window , each datablock , each canvas we have to inherit the subclass information this is done because the form should look like the other forms as that of in e business suite of perticular instance by doing this it inherit all properties like fonts, colour etc .







• Steps in detail


1. Download the TEMPLATE.fmb and APPSTAND.fmb from $AU_TOP/forms/US.
    (You can find all the oracle apps seeded forms in this directory)


2. Download the required pll files from $AU_TOP/resource.
    (You can find all the oracle apps seeded library files in this directory)


3. Copy and save the TEMPLATE.fmb into meaningful custom name.


4. Delete the following objects
             i. Window name (BLOCKNAME)
            ii. Canvas name (BLOCKNAME)
           iii. Data blocks (BLOCKNAME and DETAILBLOCK)



5. Create the windows, canvases and data blocks as per the requirements. (Don’t forget to sub class the properties for the same as above ) If you are creating the multi record blocks then include a one non-data base item in the same block and sub class the property called “CURRENT_RECORD_INDICATOR”.











6. You must modify the following triggers ( when new form instance is optional one which can be used to navigate to new form as soon as it open ) 
          i. PRE-FORM
         ii. WHEN-NEW-FORM-INSTANCE.







7. You do not modify these form-level triggers, but you can write block or item level triggers that override the form-level trigger.
           i. KEY-LISTVAL
          ii. ON-ERROR.


8. You do not modify the following triggers in any way.
               i. STANDARD_ATTACHMENTS
              ii. ZOOM
             iii. FOLDER_ACTION
             iv. KEY-HELP
              v. KEY-EXIT
             vi. KEY-EDIT
            vii. KEY-COMMIT
           viii. WHEN-WINDOW-CLOSED
            ix. CLOSE_WINDOW


9. You must modify the APP_CUSTOM package body as mentioned like a below figure.
here 'EMPDETAILS_WIN' Is your first and main window to travel as soon as form open( Refer other post on this blog when you want to use multiple windows )



10. Move the fmb file into $XXCUSTOM_TOP/forms/US.( in your case move the file to that application top in the application you have register the fmb file )


11. Compile the fmb file in PuTTY  for the linux server (connect to PuTTY and change the current directory into $XXCUSTOM_TOP/forms/US, execute the below mentioned command with apps password.)


 Setting Environment variable in linux
 go to appl top by
Cd $APPL_TOP
Find the list of environemnt variable
Ls *.env
From the  list of dirrerent variable we will have to select the appropriate enviroament variable depend on instnce on which we are deploying the form as below
.(space)(environment_variable.env)


Compile Command Release 12




frmcmp_batch userid=apps/apps module=/oraphs/TEST/apps/apps_st/appl/au/12.0.0/forms/US/XXQLGVAT.fmb output_file=/oraphs/TEST/apps/apps_st/appl/inv/12.0.0/forms/US/XXQLGVAT.fmx compile_all=special
--------------------------------------------------------------------------------------------------------------------------------


While comipling this query on putty we have to fisrt enter the username and password in putty simply by login in  then go to the path where the form fmb file is located by command exa.
 $ cd oraphs/TEST/apps/apps_st/appl/au/12.0.0/forms/US/


Then use  compile command on linux server  ( change the username / password  ,path , fmx and fmb name as per instance details here  username password is apps/apps)


frmcmp_batch userid=apps/apps module=/oraphs/TEST/apps/apps_st/appl/au/12.0.0/forms/US/XXQLGVAT.fmb output_file=/oraphs/TEST/apps/apps_st/appl/inv/12.0.0/forms/US/XXQLGVAT.fmx compile_all=special




Compile commnd for 11i Release
f60gen module= userid=APPS/ output_file= module_type=form batch=yes compile_all=special


12. Register the form in oracle apps.
Navigation -> Application Developer > Application > Form
(here the form name EMPDETAILS is nothing but the fmx name )



13. Register the form functions
Navigation -> Application Developer > Application > Function


Description TAB (Function name and user function name)
Properties TAB (Type -> Form)
Form TAB (chose the custom form name)




Attach this form function into menu. 
To which menu form to be attached can be find by go to system administrator > security > responsibility > (here query the responsibility where you want to attach your custom form ) >copy the MENU and attach form to that menu by
Navigation -> Application Developer > Application > Menu
Query the appropriate menu and add the custom function into the same.


15. Verify the form in oracle apps. ( IF YOU ARE GETTING ANY CALENDER ERROR THEN PLEASE CHANGE THE DATA BLOCK ORDER , PUT CURRENTLY MODIFIED DATABLOCK AHEAD OF CALENDER AND OTHER DATA BLOCK )