APPS.xxftc_open_orders_report_pkg_copy_PROD_01052012

CREATE OR REPLACE PACKAGE APPS.xxftc_open_orders_report_pkg
AS
/*************************************************************************

Creation Date       :  24-AUG-2009
Updated  Date       :  08-OCT-2009
updated By          :  Thirumalaivasan Subramani
Description         :  The email part to send the email to users
Purpose of Change   :  Include the hidden parameter
                       Included All in Order Type LOV
                       Added  Email Description In the Email attachment and subject--07-OCT-09
                       Included New Parameter BILL To--08-OCT-2009
***************************************************************************/
 PROCEDURE xxftc_open_orders_report_proc (
    errbuf                     IN OUT NOCOPY  VARCHAR2
   ,retcode                    IN OUT NOCOPY  VARCHAR2
   ,p_display                  IN VARCHAR2
   ,p_hidden                   IN VARCHAR2--Added By Thiru on 13-SEP-2009
   ,p_acct_num                 IN VARCHAR2 -- Added By Bvengana 20-JUN-2010
   ,p_billto                   IN VARCHAR2--Added By Thiru on 08-OCT-2009
   ,p_allocation_status        IN VARCHAR2
   ,p_customerpo               IN VARCHAR2
   ,p_item_number              IN VARCHAR2
   ,p_ordernumber              IN VARCHAR2
   ,p_ordertype                IN VARCHAR2
   ,p_warehouse                IN VARCHAR2
   ,p_report_type              IN VARCHAR2
      ,p_email                 IN VARCHAR2
      );
END;
/






------------------------------------------------------------------------------------





CREATE OR REPLACE PACKAGE BODY APPS.xxftc_open_orders_report_pkg
AS
   PROCEDURE xxftc_open_orders_report_proc (
      errbuf                IN OUT NOCOPY   VARCHAR2,
      retcode               IN OUT NOCOPY   VARCHAR2,
      p_display             IN              VARCHAR2,
      p_hidden              IN              VARCHAR2,--Added By Thiru on 13-SEP-2009
      p_acct_num           IN              VARCHAR2, -- Added By Bvengana 20-JUN-2010
      p_billto              IN              VARCHAR2,--Added By Thiru on 08-OCT-2009
      p_allocation_status   IN              VARCHAR2,
      p_customerpo          IN              VARCHAR2,
      p_item_number         IN              VARCHAR2,
      p_ordernumber         IN              VARCHAR2,
      p_ordertype           IN              VARCHAR2,
      p_warehouse           IN              VARCHAR2,
      p_report_type         IN              VARCHAR2,
      p_email               IN              VARCHAR2
   )
   IS
      v_request_id       NUMBER;
      reqidxml           NUMBER;
      v_result           BOOLEAN;
      v_phase            VARCHAR2 (50);
      v_status           VARCHAR2 (50);
      v_dev_phase        VARCHAR2 (50);
      v_dev_status       VARCHAR2 (50);
      v_error_mesg       VARCHAR2 (1000);
      l_customer_id      NUMBER;
      l_statement_date   NUMBER;
      l_application_id   NUMBER;
      l_as_of_date       VARCHAR2 (50);
      reqid              NUMBER;
      reqidxml           NUMBER;
      l_request_id1      BOOLEAN;
      v_template         VARCHAR2 (40);
      l_party_name       VARCHAR2 (500);
      l_item_name        VARCHAR2 (500);
      v_v_request_id     NUMBER;
      v_v_result         BOOLEAN;
      v_v_phase          VARCHAR2 (200);
      v_v_status         VARCHAR2 (200);
      v_v_dev_phase      VARCHAR2 (200);
      v_v_dev_status     VARCHAR2 (200);
      v_v_error_mesg     VARCHAR2 (2000);
      v_instance_name    VARCHAR2 (20);
      v_date             VARCHAR2 (20)   := TO_CHAR (SYSDATE, 'MMDDYY');
      l_return_value     VARCHAR2 (100);
      l_result           BOOLEAN;
   l_description      VARCHAR2(70);
   l_subject          VARCHAR2(70);

   BEGIN
     --fnd_file.put_line (fnd_file.LOG,
       --                        ' From date --> ' || p_from_date
         --                     );
           -- fnd_file.put_line (fnd_file.LOG,
             --                  ' To date --> ' || p_to_date
               --               );
-- Set the layout of the template
      v_template := 'XXFTCXML';
      l_request_id1 :=
         fnd_request.add_layout (template_appl_name      => 'XXFTC',
                                 template_code           => v_template,
                                 template_language       => 'en',
                                 template_territory      => 'US',
                                 output_format           => 'EXCEL'
                                );
      errbuf := NULL;
      retcode := 0;

      BEGIN
         BEGIN
            SELECT instance_name
              INTO v_instance_name
              FROM v$instance;
         EXCEPTION
            WHEN OTHERS
            THEN
               v_instance_name := NULL;
         END;



         -- Call  the XML Report
         v_request_id :=
            fnd_request.submit_request (application      => 'XXFTC',
                                        program          => 'XXFTCXML',
                                        description      => NULL,
                                        start_time       => SYSDATE,
                                        sub_request      => FALSE,
                                        argument1        => p_display,
                                        argument2        => p_hidden,
                    argument3        => p_acct_num,
                                        argument4        => p_billto,
                                        argument5        => p_allocation_status,
                                        argument6        => p_customerpo,
                                        argument7        => p_item_number,
                                        argument8        => p_ordernumber,
                                        argument9        => p_ordertype,
                                        argument10       => p_warehouse,
                                        argument11       => p_report_type
                                   /*   argument10       => p_from_date, commented by thiru on 24-Aug-2009 suggested by
                                        argument11       => p_to_date
                                        argument11       => NULL,
                                        argument42       => NULL*/
                                       );
         COMMIT;

         IF v_request_id = 0
         THEN
            l_return_value := 'Submission failed';
            fnd_file.put_line (fnd_file.LOG,
                               ' Data LoadIng Error ' || l_return_value
                              );
         ELSE
            LOOP
               v_v_result :=
                  fnd_concurrent.wait_for_request (v_request_id,
                                                   60,
                                                   600,
                                                   v_v_phase,
                                                   v_v_status,
                                                   v_v_dev_phase,
                                                   v_v_dev_status,
                                                   v_v_error_mesg
                                                  );

               IF (    RTRIM (v_v_status) != 'Normal'
                   AND RTRIM (v_v_status) != 'Warning'
                  )
               THEN
                  l_return_value := v_v_status;
               ELSE
                  l_return_value := 'SUCCESS';
               END IF;

               IF (RTRIM (v_v_phase) = 'Completed')
               THEN
         fnd_file.put_line (fnd_file.LOG, 'dev_phase -->'||v_v_phase);
                  EXIT;
               END IF;
            END LOOP;

            fnd_file.put_line (fnd_file.LOG, 'Status ' || v_v_status);
            fnd_file.put_line (fnd_file.LOG, ' End of Data Loading');
         END IF;
            fnd_file.put_line (fnd_file.LOG,'l_return_value1'||l_return_value);
        -- COMMIT;
         IF l_return_value = 'SUCCESS'
        THEN
    IF p_report_type='F' THEN
       l_description :='Open_Order_Falken_';
       l_subject     :='Open Order Falken'||' ';
    ELSE
        l_description :='Open_Order_Customer_';
        l_subject     :='Open Order Customer'||' ';
    END IF;
    IF p_display='DETAIL' THEN
        l_description :=l_description||'Detail_Report_';
        l_subject     :=l_subject||'Detail'||' '||'Report'||' '||'Attachment';
       ELSE
        l_description :=l_description||'Summary_Report_';
        l_subject     :=l_subject||'Summary'||' '||'Report'||' '||'Attachment';
    END IF;
            fnd_file.put_line (fnd_file.LOG,'Inside Email Part');
---call the email part-------
            v_v_request_id :=
               fnd_request.submit_request
                     (application      => 'XXFTC',
                      program          => 'XXFTCXMLMAIL',
                      description      => NULL,
                      start_time       => SYSDATE,
                      sub_request      => FALSE,
                      argument1        => v_request_id,
                      argument2        => p_email,
                      argument3        =>    l_subject
                                          || ' - '
                                          || v_instance_name,
                      argument4        => l_description|| v_date
                     );
         END IF;

         COMMIT;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, v_error_mesg);
         fnd_file.put_line (fnd_file.LOG, SUBSTR (SQLERRM, 1, 50));
   END xxftc_open_orders_report_proc;
END xxftc_open_orders_report_pkg;
/