ap invoice payment interface

CREATE OR REPLACE PACKAGE BODY xxte_apinvoice_pay_intf
AS
   PROCEDURE xxte_apinvoice_main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      lv_errbuff   VARCHAR2 (1000);
      lv_retcode   NUMBER;
   BEGIN
      xxte_apinvoice_ins (lv_errbuff, lv_retcode);
      xxte_apinvoice_comm (lv_errbuff, lv_retcode);
   EXCEPTION
      WHEN OTHERS
      THEN
         lv_retcode := 1;
         lv_errbuff := 'Error: In Main Procudure.' || SQLERRM;
         fnd_file.put_line (fnd_file.LOG, lv_errbuff);
   END;

   PROCEDURE xxte_apinvoice_comm (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      CURSOR stg_cur
      IS
         SELECT a.*, a.ROWID
           FROM xxte_apinv_comm_st a
          WHERE process_flag = 'N';

      -- declare local variables.
      lv_user_id          NUMBER;
      lv_resp_id          NUMBER;
      lv_resp_appl_id     NUMBER;
      lv_vendor_id        NUMBER;
      lv_vendor_no        NUMBER;
      lv_error_message    VARCHAR2 (2400);
      lv_error_code       NUMBER          := 0;
      lv_vendor_site_id   NUMBER;
      lv_invoice_date     VARCHAR2 (1);
      lv_gl_date          VARCHAR2 (1);
      lv_exist_rec        NUMBER;
      lv_invoice_amt      NUMBER;
      lv_acc              NUMBER;
      lv_source           VARCHAR2 (30);
      lv_invoice_type     VARCHAR2 (50);
      lv_dist_ccid        NUMBER;
   BEGIN
      lv_user_id := fnd_profile.VALUE ('USER_ID');
      lv_resp_id := fnd_profile.VALUE ('RESP_ID');
      lv_resp_appl_id := fnd_profile.VALUE ('RESP_APPL_ID');

      BEGIN
         fnd_global.apps_initialize (lv_user_id, lv_resp_id, lv_resp_appl_id);
         mo_global.init ('AP');
      END;

      -- Purge the previous processed data in the stage/interface table
      BEGIN
         DELETE FROM ap_invoice_lines_interface apil
               WHERE apil.invoice_id IN (SELECT invoice_id
                                           FROM ap_invoices_interface
                                          WHERE status = 'PROCESSED');

         DELETE FROM ap_invoices_interface
               WHERE status = 'PROCESSED';
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_error_code := 1;
            lv_error_message :=
                  'Error: Unable To Delete The Invoice Stage/Interface Table.'
               || SQLERRM;
            fnd_file.put_line (fnd_file.LOG, lv_error_message);
      END;

      fnd_file.put_line
         (2,
          '****************************Agent Commission Invoice***************************'
         );
      fnd_file.put_line
         (2,
          '------------------------------------------------------------------------------- '
         );

      FOR i IN stg_cur
      LOOP
         lv_error_message := NULL;
         lv_error_code := 0;
         lv_source := 'TE INSIS';
         lv_dist_ccid := 2106;

         -- Validation for Invoice Type
         BEGIN
            SELECT invoice_type
              INTO lv_invoice_type
              FROM xxte_apinv_comm_st
             WHERE ROWID = i.ROWID;

            IF lv_invoice_type IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invoice Type Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invalid Invoice Type';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for vendor name
         BEGIN
            SELECT vendor_id
              INTO lv_vendor_id
              FROM po_vendors
             WHERE vendor_name = i.vendor_name;

            IF lv_vendor_id IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor Name '
                  || ' '
                  || i.vendor_name
                  || ' '
                  || 'is Not Found';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor Name '
                  || ' '
                  || i.vendor_name
                  || ' '
                  || 'is Not Found';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

-- Validation for vendor no
         BEGIN
            SELECT segment1
              INTO lv_vendor_no
              FROM po_vendors
             WHERE segment1 = i.vendor_no AND vendor_name = i.vendor_name;

            IF lv_vendor_no IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor No '
                  || ' '
                  || i.vendor_no
                  || ' '
                  || ' is Not Found for the vendor'
                  || ' '
                  || i.vendor_name;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor No '
                  || ' '
                  || i.vendor_no
                  || ' '
                  || ' is Not Found for the vendor'
                  || ' '
                  || i.vendor_name;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for vendor site id
         BEGIN
            SELECT vendor_site_id
              INTO lv_vendor_site_id
              FROM po_vendor_sites_all
             WHERE UPPER (vendor_site_code) = UPPER (i.vendor_site)
               AND vendor_id IN (SELECT vendor_id
                                   FROM po_vendors
                                  WHERE vendor_name = i.vendor_name);

            IF lv_vendor_site_id IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor Site Name '
                  || ' '
                  || i.vendor_site
                  || ' '
                  || 'is Not found for the Vendor'
                  || ' '
                  || i.vendor_name;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Vendor Site Name '
                  || ' '
                  || i.vendor_site
                  || ' '
                  || 'is Not found for the Vendor'
                  || ' '
                  || i.vendor_name;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for invoice date
         BEGIN
            SELECT 'x'
              INTO lv_invoice_date
              FROM gl_period_statuses
             WHERE application_id = 200                ---For Oracle Payables.
               AND set_of_books_id = 2022
               AND (i.invoice_date >= start_date
                    AND i.invoice_date <= end_date
                   )
               AND closing_status = 'O';

            IF lv_invoice_date IS NULL
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invoice Date Is NULL ';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Period is Not Open for the Invoice Date '
                  || ' '
                  || i.invoice_date;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

-- Validation for GL date
         BEGIN
            SELECT 'x'
              INTO lv_gl_date
              FROM gl_period_statuses
             WHERE application_id = 200                ---For Oracle Payables.
               AND set_of_books_id = 2022
               AND (i.gl_date >= start_date AND i.gl_date <= end_date)
               AND closing_status = 'O';

            IF lv_gl_date IS NULL
            THEN
               RAISE NO_DATA_FOUND;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'GL Date Is NULL ';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Period is Not Open for the GL Date '
                  || ' '
                  || i.gl_date;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for currency code
         BEGIN
            IF i.invoice_currency != 'AED'
            THEN
               lv_error_code := 1;
               lv_error_message :=
                     'Error: '
                  || 'Invalid Currency Code '
                  || ' '
                  || i.invoice_currency;
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         END;

         -- Validation for Invoice amount
         BEGIN
            SELECT invoice_amount
              INTO lv_invoice_amt
              FROM xxte_apinv_comm_st
             WHERE ROWID = i.ROWID;

            IF lv_invoice_amt IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invoice Amount Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Invoice Amount Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         -- Validation for Account
         BEGIN
            SELECT dist_account
              INTO lv_acc
              FROM xxte_apinv_comm_st
             WHERE ROWID = i.ROWID;

            IF lv_acc IS NULL
            THEN
               lv_error_code := 1;
               lv_error_message :=
                                  'Error: ' || 'Distribution Account Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message :=
                                  'Error: ' || 'Distribution Account Is Null';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         --Validation for excisting record in base table
         BEGIN
            SELECT COUNT (*)
              INTO lv_exist_rec
              FROM ap_invoices_all
             WHERE invoice_amount = i.invoice_amount
               AND invoice_date = i.invoice_date
               AND vendor_id IN (SELECT vendor_id
                                   FROM po_vendors
                                  WHERE vendor_name = i.vendor_name);

            IF lv_exist_rec > 0
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Record Already Exists';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_code := 1;
               lv_error_message := 'Error: ' || 'Record Already Exists';
               fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END;

         IF     lv_invoice_type IN
                   ('PAYABLE', 'COMMISION ADV', 'CLAIM', 'SURRENDER',
                    'MATURITY', 'RETAKAFUL EXP', 'RETAKAFUL PAYABLE',
                    'CLAIM PAYABLE', 'DEATH CLAIM EXP')
            AND lv_invoice_amt > 0
         THEN
            lv_invoice_type := 'STANDARD';
         ELSIF     lv_invoice_type IN
                      ('PAYABLE', 'COMMISION ADV', 'REFUND', 'RETAKAFUL EXP',
                       'RETAKAFUL PAYABLE', 'RETAFUL  COMMISION',
                       'RETAKAFUL SHARE IN EXP', 'RECEIVABLE FROM RETAKAFUL')
               AND lv_invoice_amt < 0
         THEN
            lv_invoice_type := 'DEBIT';
         ELSE
            lv_error_code := 1;
            lv_error_message := 'Error: ' || 'Invalid Invoice Type';
            fnd_file.put_line (fnd_file.LOG, lv_error_message);
         END IF;

         IF lv_error_code = 0
         THEN
            INSERT INTO ap_invoices_interface
                        (invoice_id,
                         invoice_num,
                         gl_date, invoice_type_lookup_code, invoice_date,
                         vendor_id, vendor_site_id, invoice_amount,
                         invoice_currency_code, description, SOURCE, org_id
                        )
                 VALUES (ap_invoices_interface_s.NEXTVAL,
                            'INSIS_'
                         || i.vendor_site
                         || '_'
                         || ap_invoices_interface_s.CURRVAL,
                         i.gl_date, lv_invoice_type,            -- will change
                                                    i.invoice_date,
                         lv_vendor_id, lv_vendor_site_id, i.invoice_amount,
                         i.invoice_currency, i.description,     -- will change
                                                           lv_source, 81
                        );

            INSERT INTO ap_invoice_lines_interface
                        (invoice_id, line_number, line_type_lookup_code,
                         amount, dist_code_combination_id,
-- if value dist id is entered here no need to enter value in dist_code_concatenated
                                                          org_id
                        )
                 VALUES (ap_invoices_interface_s.CURRVAL, i.line_no, 'ITEM',
                         i.amount, lv_dist_ccid,                -- will update
                                                81
                        );

            fnd_file.put_line (2, 'Payable Invoice Is Created');

            UPDATE xxte_apinv_comm_st
               SET process_flag = 'S'
             WHERE ROWID = i.ROWID;
         ELSE
            fnd_file.put_line (2, 'Please Check The Log File For Error(s)');

            UPDATE xxte_apinv_comm_st
               SET process_flag = 'E',
                   error_message = lv_error_message
             WHERE ROWID = i.ROWID;
         END IF;
      END LOOP;

      COMMIT;
      fnd_file.put_line
               (2,
                '**************************** END ***************************'
               );
      fnd_file.put_line
         (2,
          '------------------------------------------------------------------------------- '
         );
   EXCEPTION
      WHEN OTHERS
      THEN
         lv_error_code := 1;
         fnd_file.put_line (fnd_file.LOG, 'Error :' || SQLERRM);
         COMMIT;
   END;

   PROCEDURE xxte_apinvoice_ins (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      CURSOR ins_c
      IS
         SELECT   'PAYABLE' invoice_type, a.vendor_name, a.vendor_no,
                  a.vendor_site,
                  (SELECT invoice_date
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) invoice_date,
                  (SELECT invoice_currency
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) invoice_currency,
                  SUM (a.invoice_amount) invoice_amount,
                  (SELECT gl_date
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) gl_date,
                  (SELECT description
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) description,
                  SUM (a.amount) amount,
                  (SELECT dist_account
                     FROM xxte_apinv_pay_temp
                    WHERE vendor_name = a.vendor_name
                      AND vendor_site = a.vendor_site
                      AND ROWNUM = 1) dist_account
             FROM xxte_apinv_pay_temp a
            WHERE a.invoice_type IN ('COMMISION ADV', 'PAYABLE')
         GROUP BY a.vendor_name, a.vendor_site, a.vendor_no
           HAVING SUM (a.invoice_amount) != 0
         UNION
         SELECT *
           FROM xxte_apinv_pay_temp
          WHERE invoice_type NOT IN ('COMMISION ADV', 'PAYABLE');

      lv_error_message   VARCHAR2 (2400);
      lv_error_code      NUMBER          := 0;
   BEGIN
      BEGIN
         DELETE FROM xxte_apinv_comm_st
               WHERE process_flag != 'N';
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_error_code := 1;
            lv_error_message :=
                  'Error: Unable To Delete Record(s) In Staging Table.'
               || SQLERRM;
            fnd_file.put_line (fnd_file.LOG, lv_error_message);
      END;

      FOR i IN ins_c
      LOOP
         INSERT INTO xxte_apinv_comm_st
                     (invoice_type, vendor_name, vendor_no,
                      vendor_site, invoice_date, invoice_currency,
                      invoice_amount, gl_date, description, amount,
                      dist_account
                     )
              VALUES (i.invoice_type, i.vendor_name, i.vendor_no,
                      i.vendor_site, i.invoice_date, i.invoice_currency,
                      i.invoice_amount, i.gl_date, i.description, i.amount,
                      i.dist_account
                     );
      END LOOP;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         lv_error_code := 1;
         lv_error_message :=
                  'Error: Unable To Insert Data In Staging Table.' || SQLERRM;
         fnd_file.put_line (fnd_file.LOG, lv_error_message);
   END;
END xxte_apinvoice_pay_intf;
/