How To Submit Concurrent Program From Backend In Oracle

How To Submit Concurrent Program From Backend In Oracle

How To Submit Concurrent Program From Backend In Oracle

   create or replace PROCEDURE XXSD_REPORT_SUBMIT_PROC (
      errbuf               OUT   NUMBER,
      retcode              OUT   VARCHAR2,
      P_DEPT_NO IN NUMBER) AS

      l_request_id         NUMBER;
      v_phase              VARCHAR2 (20);
      v_dev_phase          VARCHAR2 (20);
      v_dev_status         VARCHAR2 (20);
      v_message            VARCHAR2 (20);
      v_wait_status        VARCHAR2 (20);
      l_return_status      BOOLEAN;
      e_submit_error       EXCEPTION;
      e_program_status     EXCEPTION;
      e_program_status1    EXCEPTION;
      v_lay                BOOLEAN;


l_user_id number       := 15570; --fnd_global.user_id;
l_resp_id  number     := 20419; --fnd_global.resp_id;
l_resp_appl_id number := 0 ;-- fnd_global.resp_appl_id;

--select * from fnd_user where user_name = 'DEVELOPER'
--
--select * from fnd_responsibility_tl where RESPONSIBILITY_NAME = 'Application Developer'
--
--select * from fnd_application_tl where APPLICATION_NAME = 'Application Object Library'

BEGIN

BEGIN
fnd_global.APPS_INITIALIZE(l_user_id,l_resp_id, l_resp_appl_id);
END;

         l_request_id := 0;
         fnd_file.put_line (fnd_file.LOG, '**************   Launching (XXSD_MULTI_TEMPLTE_REPORT) Program   **************' );

IF P_DEPT_NO = 10 THEN
 v_lay :=
                  fnd_request.add_layout ('XXSD',
                                          'XXSD_Template_1',
                                          'en',
                                          'US',
                                          'EXCEL');
                                     
ELSE
 v_lay :=
                  fnd_request.add_layout ('XXSD',
                                          'XXSD_Template_2',
                                          'en',
                                          'US',
                                          'EXCEL');

END IF;                                     
                                     
         l_request_id :=
         fnd_request.submit_request
                            (application       => 'XXSD',
                             program          => 'XXSD_MULTI_TEMPLTE_REPORT',
                             description       => 'XXSD_MULTI_TEMPLTE_REPORT',
                             start_time        => SYSDATE,
                             sub_request     => FALSE,
                             argument1        => P_DEPT_NO
                            );
         COMMIT;

         IF l_request_id = 0  THEN
            fnd_file.put_line (fnd_file.LOG, 'Request Not Submitted');
            RAISE e_submit_error;
         ELSIF l_request_id != 0
         THEN
            fnd_file.put_line (fnd_file.LOG,  'Request Submitted : ' || l_request_id );

            LOOP
               l_return_status :=
                  apps.fnd_concurrent.wait_for_request
                                                 (request_id      => l_request_id,
                                                  INTERVAL        => 2,
                                                  max_wait        => 60,
                                                  -- out arguments
                                                  phase           => v_phase,
                                                  status          => v_wait_status,
                                                  dev_phase       => v_dev_phase,
                                                  dev_status      => v_dev_status,
                                                  MESSAGE         => v_message
                                                 );
               EXIT WHEN UPPER (v_phase) = 'COMPLETED' OR UPPER (v_wait_status) IN ('CANCELLED', 'ERROR', 'TERMINATED');
            END LOOP;
           

         fnd_file.put_line (fnd_file.LOG,'(XXSD_MULTI_TEMPLTE_REPORT) Program|| '|| v_dev_phase||' With '||v_dev_status);                   
         END IF;     
     
      EXCEPTION WHEN e_program_status THEN
            RAISE e_program_status1;
      WHEN OTHERS  THEN
      fnd_file.put_line (fnd_file.LOG, 'Errored In exception'||SQLERRM);
      END;


--===========================================

declare
V_CODE VARCHAR2(100);
V_ERROR VARCHAR2(100);
V_DEPT_NO NUMBER:= 10;
begin
XXSD_REPORT_SUBMIT_PROC(V_CODE, V_ERROR, V_DEPT_NO);
end;
--==========================================

WEB ADI_Creation

*=========================================================================*

Ticket NO   Date            Updated By      Description

*==================================================================*

         21-Sep-2019     Ravikas Verma         updated template

==========================================================================*

1=> Take the responsibility of Desktop Integrator.

INTERFACE NAME => XXESTEST

Desk top Integrator Setup document.

This document for insert data into customer table

Click On Next button

Click on next button.

Click on Next button.

Click On Create document system will ask to save the file.

Click Open system will generate the Excel file.

Prepare a package in database to insert in table.

Enter the Data

Click On ADDINS oracle menu from the menu bar then click oracle Upload.

Click on Define Layout and enter the required details.

Click Open.

Select the Integrator name from the list of Values which you created.

Click GO

Click on Create

Click Next

Select the line in Placement List of values

Click next.

Enter the range of values of width

Click Apply.

After create WEB ADI create , make a function from System Administrator – Application – Function

TYPE : SSWA servlet function

PARAMETERS : bne:page=BneCreateDoc&bne:integrator=20003:GENERAL_146_INTG

Note : To get integrator code use below SQL query

HTML CALL= BneApplicationService

Select * From BNE_INTEGRATORS_TL

WHERE USER_NAME LIKE'%BI%'    TO GET GENERAL_224_INTG VALUE

PARAMETER = bne:page=BneCreateDoc&bne:integrator=20003:GENERAL_224_INTG

After this you can add this above function in any MENU where you want to all this option

Now Add Web ADI To a Menu

Goto SYSADMINàSECURITYàRESPOSIBILITYàDEFINE

QUERY FOR RESPONSIBILITY NAME COPY THE MENU NAME

Goto SYSADMINàAPPLICATIONàMENU

QUERY THE MENU NAME AND ADD THE FUNCTION AS SHOWN BELOW

MENU (ORDER ENTRY EAM)

ESCORTS_ORDER_ENTRY_NEW

CHANGE COLUMN NAME

GET INTEGRATOR NAME FROM

Select * From BNE_INTEGRATORS_TL  WHERE USER_NAME LIKE'%BI%'

GET INTERFACE_CODE NAME USING THIS QUERY. CHANGE INTG TO INTF IN INTERFACE_CODE

SELECT * FROM bne_interface_cols_tl

WHERE

1=1

AND INTERFACE_CODE='GENERAL_224_INTF'

UPDATE bne_interface_cols_tl

SET prompt_above = 'Code Nature',

prompt_left = 'Code Nature'

WHERE prompt_above = 'DE_NATURE'

AND application_id = 20003

AND interface_code = 'GENERAL_224_INTF'

SELECT * FROM bne_interface_cols_tl

WHERE

1=1

AND INTERFACE_CODE='GENERAL_684_INTF'

UPDATE bne_integrators_b SET SOURCE  = 'C' WHERE integrator_code IN ('GENERAL_684_INTG');

select * from

bne_integrators_b

where

integrator_code IN ('GENERAL_684_INTG');

select * from

bne_param_list_items

where

PARAM_LIST_CODE like '%GENERAL%684%';

select * from

bne_attributes

where

ATTRIBUTE_CODE like '%GENERAL%684%';

WEB ADI FIND PROGRAM

   select ba.attribute2,bit.user_name
     from bne_integrators_tl bit,
          bne_interfaces_b   bib,
          bne_param_lists_b  bplb,
          bne_attributes     ba
    where bit.integrator_code = bib.integrator_code
      and bib.upload_param_list_code = bplb.param_list_code
      and bplb.attribute_code = ba.attribute_code
      and bit.user_name like 'Escorts Service%'--Web adi Name

Program Responsibility

SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND fcpt.user_concurrent_program_name = :conc_prg_name
    ORDER BY 1,2,3,4

Prog_Req_Grp_Reg LDT

Program To register a report on a request Group through LDT-

FNDLOAD apps/xxx123 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct <our required req group ldt name>.ldt REQUEST_GROUP REQUEST_GROUP_NAME='<request group name>' APPLICATION_SHORT_NAME='<request group application short name>' REQUEST_GROUP_UNIT UNIT_APP='<concurrent program application short name>' UNIT_TYPE='P' UNIT_NAME='<concurrent program short name>'

Ex.

FNDLOAD apps/app8002s0tst O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXES_JAI_OrderMgmt_RG.ldt REQUEST_GROUP REQUEST_GROUP_NAME='JAI_OrderMgmt_RG' APPLICATION_SHORT_NAME='JA' REQUEST_GROUP_UNIT UNIT_APP='XXES' UNIT_TYPE='P' UNIT_NAME='XXES_OWD_FRT_GRN_SUMMARY'

To Find Highlighted ‘request group application short name’ use this program-

SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",

       DECODE(rgu.request_unit_type,

              'P', 'Program',

              'S', 'Set',

              rgu.request_unit_type)        "Unit Type",

       cp.concurrent_program_name           "Concurrent Program Short Name",

       rg.application_id                    "Application ID",

       rg.request_group_name                "Request Group Name",

       fat.application_name                 "Application Name",

       fa.application_short_name            "Application Short Name",

       fa.basepath                          "Basepath"

FROM   fnd_request_groups          rg,

       fnd_request_group_units     rgu,

       fnd_concurrent_programs     cp,

       fnd_concurrent_programs_tl  cpt,

       fnd_application             fa,

       fnd_application_tl          fat

WHERE  rg.request_group_id       =  rgu.request_group_id

   AND rgu.request_unit_id       =  cp.concurrent_program_id

   AND cp.concurrent_program_id  =  cpt.concurrent_program_id

   AND rg.application_id         =  fat.application_id

   AND fa.application_id         =  fat.application_id

   AND cpt.language              =  USERENV('LANG')

   AND fat.language              =  USERENV('LANG')

   AND cpt.user_concurrent_program_name = :Concurrent_program_name

To Upload  LDT File use Program:-

FNDLOAD apps/xxx123 O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct < ldt name>.ldt

Ex.         FNDLOAD apps/app8002s0tst O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXES_JAI_OrderMgmt_RG.ldt