Monday 24 June 2013

Outbound interfce mail program coding

CREATE OR REPLACE PACKAGE APPS.xxtt_pmp_extract_pkg
AS
   PROCEDURE xxtt_pmp_extract_prc (
      errbuf             OUT      VARCHAR2,
      retcode            OUT      VARCHAR2,
      p_pa_period_name   IN       VARCHAR2,
      p_operating_unit   IN       VARCHAR2,
      p_month_end        IN       VARCHAR2,
      p_rpt_type         IN       VARCHAR2
   );
END xxtt_pmp_extract_pkg;
/

==================================

CREATE OR REPLACE PACKAGE BODY APPS.xxtt_pmp_extract_pkg
AS
   PROCEDURE xxtt_pmp_extract_prc (
      errbuf         OUT      VARCHAR2,
      retcode        OUT      VARCHAR2,
      p_pa_period_name   IN       VARCHAR2,
      p_operating_unit   IN       VARCHAR2,
      p_month_end        IN       VARCHAR2,
      p_rpt_type         IN       VARCHAR2
   )
   IS
  
   l_recipient       VARCHAR2 (4000):='elangovanragavan@hotmail.com';
   l_error_message   VARCHAR2 (4000);
   l_error_status    NUMBER;   

  
   CURSOR c_property (P_PA_PERIOD_NAME in varchar2,
                                    P_OPERATING_UNIT IN varchar2,
                                    P_MONTH_END in varchar2,
                                    P_RPT_TYPE in varchar2)  
   is 
         SELECT   pra.package_id, pfl_pkg.portfolio_descr, pra.company,
         pra.gl_period_name, pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH12:MI:SS AM') run_start,
         to_char(pra.run_end,'MM/DD/YYYY HH12:MI:SS AM') run_end,
         COUNT (rdoc.report_doc_id) CNT
    FROM pmreports.portfolio_run_audit@ttrdb pra,
         pmreports.report_doc@ttrdb rdoc,
         pmreports.portfolio_package@ttrdb pfl_pkg
   WHERE pra.trigger_name = rdoc.trigger_name
     AND pra.package_id = pfl_pkg.portfolio_id
     AND pra.company = NVL (p_operating_unit, pra.company)
     AND pra.pa_period_name = rdoc.pa_period_name
     AND p_rpt_type = 'Summarized' 
     AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
     OR pra.pa_period_name in
                    (SELECT DECODE (UPPER (P_MONTH_END),
                                    'Y', current_pa_period,
                                    last_closed_pa_period
                                   )
                       FROM param_gl_period@ttrdb
                      WHERE org_id = (SELECT NVL
                                                   (TO_NUMBER
                                                       (DECODE
                                                           (SUBSTRB
                                                               (USERENV
                                                                   ('CLIENT_INFO'
                                                                   ),
                                                                1,
                                                                1
                                                               ),
                                                            '', NULL,
                                                            SUBSTRB
                                                               (USERENV
                                                                   ('CLIENT_INFO'
                                                                   ),
                                                                1,
                                                                10
                                                               )
                                                           )
                                                       ),
                                                    -99
                                                   )
                                           FROM DUAL) ))
GROUP BY pra.package_id,
         pfl_pkg.portfolio_descr,
         pra.company,
         pra.gl_period_name,
         pra.pa_period_name,
         pra.run_start,
         pra.run_end
ORDER BY pra.company, pra.package_id;

     CURSOR c_property1 (P_PA_PERIOD_NAME in varchar2,
                                    P_OPERATING_UNIT IN varchar2,
                                    P_MONTH_END in varchar2,
                                    P_RPT_TYPE in varchar2)  
   is 
SELECT   package_id, company, gl_period_name, pa_period_name, run_start,
         run_end, report_name, SUM (COUNT) COUNT
    FROM (SELECT pra.package_id, pra.company, pra.gl_period_name,
                 pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH:MI:SS AM') run_start,
         to_char(pra.run_end,'MM/DD/YYYY HH:MI:SS AM') run_end,
                 rdoc.report_name, 1 COUNT
            FROM pmreports.portfolio_run_audit@ttrdb pra,
                 pmreports.report_doc@ttrdb rdoc
           WHERE pra.trigger_name = rdoc.trigger_name
             AND pra.pa_period_name = rdoc.pa_period_name
             AND P_RPT_TYPE = 'Detailed'
             AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
             OR pra.pa_period_name in
                    (SELECT DECODE (UPPER (P_MONTH_END),
                                    'Y', current_pa_period,
                                    last_closed_pa_period
                                   )
                       FROM param_gl_period@ttrdb
                      WHERE org_id = (SELECT NVL
                                                   (TO_NUMBER
                                                       (DECODE
                                                           (SUBSTRB
                                                               (USERENV
                                                                   ('CLIENT_INFO'
                                                                   ),
                                                                1,
                                                                1
                                                               ),
                                                            '', NULL,
                                                            SUBSTRB
                                                               (USERENV
                                                                   ('CLIENT_INFO'
                                                                   ),
                                                                1,
                                                                10
                                                               )
                                                           )
                                                       ),
                                                    -99
                                                   )
                                           FROM DUAL) ))
             AND pra.company = nvl(P_OPERATING_UNIT,pra.company)        
          UNION ALL
          SELECT pfl_rpts.package_id, pra.company, pra.gl_period_name,
                 pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH:MI:SS AM') run_start,
         to_char(pra.run_end,'MM/DD/YYYY HH:MI:SS AM') run_end,
                 rpt.report_name, 0 COUNT
            FROM pmreports.report_def@ttrdb rpt,
                 pmreports.portfolio_reports@ttrdb pfl_rpts,
                 pmreports.portfolio_package@ttrdb pfl_pkg,
                 pmreports.portfolio_run_audit@ttrdb pra
           WHERE 1 = 1
             AND rpt.report_def_id = pfl_rpts.report_def_id
             AND pfl_rpts.package_id = pfl_pkg.portfolio_id
             AND pra.package_id = pfl_pkg.portfolio_id
             AND P_RPT_TYPE = 'Detailed'
              AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
              OR pra.pa_period_name in
                    (SELECT DECODE (UPPER (P_MONTH_END),
                                    'Y', current_pa_period,
                                    last_closed_pa_period
                                   )
                       FROM param_gl_period@ttrdb
                      WHERE org_id = (SELECT NVL
                                                   (TO_NUMBER
                                                       (DECODE
                                                           (SUBSTRB
                                                               (USERENV
                                                                   ('CLIENT_INFO'
                                                                   ),
                                                                1,
                                                                1
                                                               ),
                                                            '', NULL,
                                                            SUBSTRB
                                                               (USERENV
                                                                   ('CLIENT_INFO'
                                                                   ),
                                                                1,
                                                                10
                                                               )
                                                           )
                                                       ),
                                                    -99
                                                   )
                                           FROM DUAL) ))
             AND pra.company = nvl(P_OPERATING_UNIT,pra.company)          
             )
GROUP BY package_id,
         company,
         gl_period_name,
         pa_period_name,
         run_start,
         run_end,
         report_name
ORDER BY company, package_id, report_name;

      cur1                 c_property%ROWTYPE; 
      cur2                 c_property1%ROWTYPE;     
      l_file_dir           VARCHAR2 (512);
      l_header             VARCHAR2 (4000);
      l_utl_file           UTL_FILE.file_type;
      l_file_name          VARCHAR2 (100);
      l_rec                VARCHAR2 (4000);
      l_rec_update_count   NUMBER               := 0;
      l_ftp_file_name            VARCHAR2(255);
      l_ftp_req_id_val   NUMBER:=0;
     
   BEGIN
      BEGIN
         SELECT SUBSTR (VALUE, 1, INSTR (VALUE, ',') - 1)
           INTO l_file_dir
           FROM v$parameter
          WHERE NAME = 'utl_file_dir' AND ROWNUM = 1;
         
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      l_file_name := 'TT: PMP Package Extract' || '.csv';
     
      l_ftp_file_name := l_file_dir||'/'||l_file_name;
     
      l_utl_file := UTL_FILE.fopen (l_file_dir, l_file_name, 'W', 2000);
     
      l_header :=
         ('PACKAGE_ID,PORTFOLIO_DESCR,COMPANY,GL_PERIOD_NAME,PA_PERIOD_NAME,RUN_START,RUN_END,Total Doc Id'
         );
         fnd_file.put_line (fnd_file.output,
                            'PACKAGE_ID'
                         || 'PORTFOLIO_DESCR'
                         || 'COMPANY'
                         || 'GL_PERIOD_NAME'
                         || 'PA_PERIOD_NAME'
                         || 'RUN_START'
                         || 'RUN_END'   
                         || 'Total Doc Id'                                  
                        );
      UTL_FILE.put_line (l_utl_file, l_header);
        
     
      
      IF P_RPT_TYPE = 'Summarized'
      THEN
      FOR cur1 IN c_property (P_PA_PERIOD_NAME,P_OPERATING_UNIT,P_MONTH_END,P_RPT_TYPE)
      LOOP
         BEGIN
            l_rec :=
                  NVL (cur1.PACKAGE_ID, '')
               || ','
               || NVL (cur1.PORTFOLIO_DESCR, '')
               || ','
               || NVL (cur1.COMPANY, '')
               || ','
               || NVL (cur1.GL_PERIOD_NAME, '')
               || ','
               || NVL (cur1.PA_PERIOD_NAME, '')
               || ','
               || NVL (cur1.RUN_START, '')
               || ','
               || NVL (cur1.RUN_END, '')
               || ','
               || NVL (cur1.CNT, '');                         
            UTL_FILE.put_line (l_utl_file, l_rec);
--            l_rec_update_count := l_rec_update_count + 1;
            fnd_file.put_line (fnd_file.output,
                                  NVL (cur1.PACKAGE_ID, '')
                               || NVL (cur1.PORTFOLIO_DESCR, '')
                               || NVL (cur1.COMPANY, '')
                               || NVL (cur1.GL_PERIOD_NAME, '')
                               || NVL (cur1.PA_PERIOD_NAME, '')
                               || NVL (cur1.RUN_START, '')
                               || NVL (cur1.RUN_END, '')   
                               || NVL (cur1.CNT, '')                                                      
                              );                       
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END LOOP;           
      ELSIF P_RPT_TYPE = 'Detailed'
      THEN
      FOR cur2 IN c_property1 (P_PA_PERIOD_NAME,P_OPERATING_UNIT,P_MONTH_END,P_RPT_TYPE)
      LOOP
         BEGIN
            l_rec :=
                  NVL (cur2.PACKAGE_ID, '')          
               || ','   
               || NVL (cur2.REPORT_NAME, '')
               || ','       
               || NVL (cur2.COMPANY, '')
               || ','
               || NVL (cur2.GL_PERIOD_NAME, '')
               || ','
               || NVL (cur2.PA_PERIOD_NAME, '')
               || ','
               || NVL (cur2.RUN_START, '')
               || ','
               || NVL (cur2.RUN_END, '')
               || ','
               || NVL (cur2.COUNT, '');                         
            UTL_FILE.put_line (l_utl_file, l_rec);
--            l_rec_update_count := l_rec_update_count + 1;
            fnd_file.put_line (fnd_file.output,
                                  NVL (cur2.PACKAGE_ID, '')                              
                               || NVL (cur2.COMPANY, '')
                               || NVL (cur2.GL_PERIOD_NAME, '')
                               || NVL (cur2.PA_PERIOD_NAME, '')
                               || NVL (cur2.RUN_START, '')
                               || NVL (cur2.RUN_END, '') 
                               || NVL ( cur2.COUNT, '')                                                         
                              );                             
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END LOOP;
        END IF;
    
      fnd_file.put_line (fnd_file.LOG,
                         'Total Record Count: ' || l_rec_update_count
                        );
      UTL_FILE.fclose_all;
     
      l_error_status :=
            xxtt_sendmailjpkg.sendmail
               (smtpservername      => xx_get_current_smtp,
                sender              => 'test.com',
                recipient           => 'elangovanragavan@hotmail.com',
                ccrecipient         => '',
                bccrecipient        => '',
                subject             => 'PMP Package Validation',
                BODY                => 'PMP Package Validation List',
                errormessage        => l_error_message,
                attachments         => xxtt_sendmailjpkg.attachments_list(l_ftp_file_name)
               );            
                    
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG,
                               'Other error occured in program: ' || SQLERRM
                              );
      END;    
         
        END;
/

No comments:

Post a Comment