3 Replies Latest reply on Jun 11, 2017 1:28 PM by mdtaylor

    how to Call Concurrent program from PLSQL and Output as email

    FS_Libra

      Hi, I have following requirement.  while executing  PLSQL procedure,  Call concurrent program (which is a Report) and after completion,  pdf Output should deliver to some employees via email. We have oracle application 12.2. Thanks,.

        • 1. Re: how to Call Concurrent program from PLSQL and Output as email
          mdtaylor

          Set the notification and layout as shown:

           

          procedure submit_apgl_activity_report(

                p_errbuf           IN OUT NOCOPY   VARCHAR2

               ,p_errcode          IN OUT NOCOPY   VARCHAR2)

          is

           

          l_time NUMBER;

          l_date DATE;

          l_max_date DATE;

          l_resp_id      NUMBER;

          l_user_id      NUMBER;

          l_appl_id      NUMBER;

          l_request_id   NUMBER;

          l_request_id2  NUMBER;

          l_request_id3  NUMBER;

          l_dept_from    VARCHAR2(20);

          l_dept_to      VARCHAR2(20);

          l_location     VARCHAR2(20);

          l_email        VARCHAR2(30);

          l_period_name  VARCHAR2(10);

          l_notify_user  BOOLEAN;

          l_layout       BOOLEAN;

          c_continue     BOOLEAN := TRUE;

          c_continue2    BOOLEAN := TRUE;

          l_start_date   DATE;

          l_end_date     DATE;

          l_arg1         VARCHAR2(20) := '';    -- Period Name

          l_arg2         VARCHAR2(20) := '';    -- Natural Account From

          l_arg3         VARCHAR2(20) := '';    -- Natural Account To

          l_arg4         VARCHAR2(20) := ''; -- Department From

          l_arg5         VARCHAR2(20) := ''; -- Department To

          l_arg6         VARCHAR2(20) := '2023'; -- Ledger_id

           

           

           

          cursor c_dept is

          select distinct dept_from, dept_to

          from xxcep.xxcep_ap_dept_dist where email <> 'KEYLEE' order by dept_from, dept_to;

           

          cursor c_email (c_dept_from VARCHAR2, c_dept_to VARCHAR2) is

          select distinct email

            from xxcep.xxcep_ap_dept_dist

          where dept_from = c_dept_from

             and dept_to = c_dept_to

             and email <> 'KEYLEE';

           

          BEGIN

           

          SELECT TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) INTO l_time FROM dual;

          SELECT SYSDATE-1 INTO l_date from dual;

           

           

          --Submit Requests

           

          --   SELECT TO_CHAR(sysdate-1,'YYYY/MM/DD')||' 00:00:00'

          --   INTO l_arg5 FROM dual;

           

              SELECT period_name INTO l_period_name

              FROM gl_periods WHERE period_set_name = 'CEP_GL_CALENDAR' AND sysdate-7 BETWEEN start_date AND end_date;

           

              --SELECT TO_CHAR(end_date,'DD-MON-YYYY')||' 23:59:59' FROM gl_periods WHERE period_set_name = 'CEP_GL_CALENDAR' AND sysdate BETWEEN start_date AND end_date

           

           

              SELECT RESPONSIBILITY_ID

              INTO l_resp_id

              FROM APPLSYS.FND_RESPONSIBILITY_TL

              WHERE RESPONSIBILITY_NAME = 'CEP Payables Super User'; --< something like 'Inventory Manager' or whatever

           

              SELECT USER_ID

              INTO l_user_id

              FROM APPLSYS.FND_USER

              WHERE USER_NAME = 'CLEAREDGE'; --< person who can normally submit the import

           

              SELECT APPLICATION_ID

              INTO l_appl_id

              FROM APPLSYS.FND_APPLICATION_TL

              WHERE APPLICATION_NAME = 'Payables'; --< full name of INV ('Inventory' or maybe 'Oracle Inventory')

           

              fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);

             

                  OPEN c_dept ;

               <<dept_loop>>

           

              LOOP

                c_continue := TRUE ;

               

              FETCH c_dept INTO l_dept_from, l_dept_to;

                                     

              EXIT WHEN c_dept%NOTFOUND ;

             

              l_arg1 := '';

              l_arg4 := '';

              l_arg5 := '';

             

              l_arg4 := l_dept_from;

              l_arg5 := l_dept_to;

              l_arg1 := l_period_name;

             

              open c_email(l_dept_from, l_dept_to);

             

              loop

                c_continue2 := TRUE ;

             

              fetch c_email into l_email;

             

              EXIT WHEN c_email%NOTFOUND;

           

              l_notify_user := fnd_request.add_notification(l_email);

             

              DBMS_OUTPUT.PUT_LINE('Adding notification for '||l_email);

             

              end loop;

             

              close c_email;

              

              l_layout := fnd_request.add_layout ('XXCEP','CEPGLACC','en','US','PDF');

             

              l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'XXCEP',

                                                        program    => 'CEPGLACC',

                                                        argument1  => l_arg1,   --Period Name

                                                        argument2  => l_arg2,   --Natural Account From

                                                        argument3  => l_arg3,   --Natural Account To

                                                        argument4  => l_arg4,   --Department From

                                                        argument5  => l_arg5,   --Department To

                                                        argument6  => l_arg6);  --Ledger_ID

           

             

               IF l_request_id != 0 THEN

                      FND_FILE.PUT_LINE(FND_FILE.LOG,'SUBMITTING CYCLE COUNT ENTRIES AND ADJUSTMENTS FOR:');

                      FND_FILE.PUT_LINE(FND_FILE.LOG,'THE REQUEST ID '||TO_CHAR(l_request_id));

                      DBMS_OUTPUT.PUT_LINE('Submitted Request ID '||TO_CHAR(l_request_id)||' '||l_arg1||' '||l_arg4||' '||l_arg5 );

                      COMMIT;

               ELSE

                     DBMS_OUTPUT.PUT_LINE('ERROR WHILE SUBMITTING ITEM IMPORT FOR:');

                     l_request_id := 0;

               END IF;

              

            end loop;

           

          end submit_apgl_activity_report;

           

          Regards,

          Michael

          • 2. Re: how to Call Concurrent program from PLSQL and Output as email
            FS_Libra

            Hi Michael, Thanks for your reply. I guess fnd_request.add_notification(l_email);  will only send notification. Output of report will not be attached with email. Please confirm. Thanks, Faisal.

            • 3. Re: how to Call Concurrent program from PLSQL and Output as email
              mdtaylor

              Hi Faisal,

               

              The PDF output appears as an attachment when using this code as long as add_layout is used to specify the layout/format type of the report. This needs to already be an XML Publisher report.

               

              Regards,

              Michael