Friday, February 7, 2014

AR - Receipt Creation in Receivables R12


AR - Receipt Creation in Receivables R12

API to be used : 

1. AR_RECEIPT_API_PUB

Required Tables :

1.  ar_receipt_methods
2.  ar_cash_receipts_all
3.  ar_receivable_applications_all  
4.  ar_payment_schedules_all  
5.  ra_customer_trx_all
6.  hz_parties 
7.  hz_cust_accounts

Validations Need to Perform  :

1. Validating Receipt  Method :
    
  •   The receipt method ID is validated per the following conditions:
    It must be a valid receipt method ID in the AR_RECEIPT_METHOD table.
    Receipt date must lie between the receipt method start date and end date (if not
    null).
  •  The creation method code for the receipt class of this particular receipt method
    ID should be ’AUTOMATIC,’ the remit flag =’Y,’ and the confirm flag = ’N’ or
    ’MANUAL.’
  •  At least one remittance bank account associated with this receipt method ID
    must have either the multi-currency flag set to ’Y’ or the same currency as the
    receipt currency. In addition, this should have a bank account type =
    ’INTERNAL’ and its inactive date (if specified) greater than the receipt_date.

2.  Validating Receipt Number  to Avoid Duplication .


Find the Below Sample Code Used to Create Oracle AR Receipt .
---------------------------------------------------------------------------------------------------------------------------------

 CREATE OR REPLACE PACKAGE BODY APPS.xxfin_ar_receipts_pkg
AS
-----------validate Receipt Method
   FUNCTION validate_receipt_method (p_receipt_method_name   IN     VARCHAR2,
                                     p_receipt_date          IN     DATE,
                                     p_receipt_method_id        OUT NUMBER,
                                     p_error_loc                OUT VARCHAR2,
                                     p_error_msg                OUT VARCHAR2)
      RETURN BOOLEAN
   IS
   --lc_receipt_method_var VARCHAR2(1);
   BEGIN
      p_error_loc := 'Function: VALIDATE_RECEIPT_METHOD';

      BEGIN
         SELECT receipt_method_id
           INTO p_receipt_method_id
           FROM ar_receipt_methods
          WHERE UPPER (TRIM (NAME)) = TRIM (p_receipt_method_name)
           AND NVL (p_receipt_date, SYSDATE) BETWEEN start_date
                                                     AND NVL (end_date,
                                                              '31-DEC-4712');
       RETURN TRUE;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            p_error_msg :=
                  'Error: Receipt Method '
               || p_receipt_method_name
               || ' is not defined in the System';
            fnd_file.put_line (fnd_file.LOG, p_error_msg);
            DBMS_OUTPUT.put_line (p_error_msg);
            RETURN FALSE;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg :=
               'Error while validating Receipt Method: '
            || p_receipt_method_name
            || ' '
            || SUBSTR (SQLERRM, 1, 255);
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN FALSE;
   END;
 -------------Validating for duplicate Receipt  ------------------------------
   FUNCTION validate_receipt_number (p_receipt_number   IN     VARCHAR2,
                                     p_org_id           IN     NUMBER,
                                     p_error_loc           OUT VARCHAR2,
                                     p_error_msg           OUT VARCHAR2)
      RETURN BOOLEAN
   IS
      lc_receipt_var   VARCHAR2 (1);
   BEGIN
      p_error_loc := 'Function: VALIDATE_RECEIPT_NUMBER';

      BEGIN
         SELECT 'X'
           INTO lc_receipt_var
           FROM ar_cash_receipts_all
          WHERE receipt_number = p_receipt_number AND org_id = p_org_id; -- added by ranjeet 15/05/2012

         p_error_msg :=
               'Error: Receipt Number '
            || p_receipt_number
            || ' already in the System';
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN FALSE;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            --p_error_msg := 'Error: Customer '||p_customer_name||' is not defined in the System';
            --Fnd_File.PUT_LINE(Fnd_File.LOG,p_error_msg);
            --DBMS_OUTPUT.PUT_LINE(p_error_msg);
            RETURN TRUE;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg :=
               'Error while validating Receipt: '
            || p_receipt_number
            || ' '
            || SUBSTR (SQLERRM, 1, 255);
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN FALSE;
   END;
 ------------ Validating for Receipt Existence  for invoice Number---------------------
   FUNCTION validate_receipt_existence (p_chr_trx_number   IN     VARCHAR2,
                                        p_org_id           IN     NUMBER,
                                        p_error_loc           OUT VARCHAR2,
                                        p_error_msg           OUT VARCHAR2)
      RETURN BOOLEAN
   IS
      lc_receipt_var   VARCHAR2 (1);
   BEGIN
      p_error_loc := 'Function: VALIDATE_RECEIPT_EXISTENCE';

      BEGIN
 
         SELECT 'X'
           INTO lc_receipt_var
           FROM ar_payment_schedules_all apsa,
                ar_receivable_applications_all araa,
                ar_cash_receipts_all acra,
                ra_customer_trx_all racta
          WHERE     apsa.payment_schedule_id = araa.payment_schedule_id
                AND apsa.cash_receipt_id = araa.cash_receipt_id
                AND acra.cash_receipt_id = apsa.cash_receipt_id
                AND racta.customer_trx_id = araa.applied_customer_trx_id
                AND racta.org_id = apsa.org_id
                AND araa.APPLICATION_TYPE = 'CASH'
                AND racta.trx_number = p_chr_trx_number
                AND apsa.org_id = p_org_id;

         p_error_msg :=
               'Error: Receipt  for invoice number '
            || p_chr_trx_number
            || '  is already in the System';
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN TRUE;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            --p_error_msg := 'Error: Customer '||p_customer_name||' is not defined in the System';
            --Fnd_File.PUT_LINE(Fnd_File.LOG,p_error_msg);
            --DBMS_OUTPUT.PUT_LINE(p_error_msg);
            RETURN FALSE;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg :=
               'Error while validating Receipt Existence: '
            || p_chr_trx_number
            || ' '
            || SUBSTR (SQLERRM, 1, 255);
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN FALSE;
   END;
   PROCEDURE imp_values (p_trx_in_number   IN     VARCHAR2,
                         errbuf               OUT VARCHAR2,
                         retcode              OUT NUMBER)
   AS
      --              Variable Declaration
      lc_program_name        VARCHAR2 (150);
      ln_user_id             NUMBER;
      ln_login_id            NUMBER;
      ln_org_id              NUMBER;
      lc_error_flag          VARCHAR2 (1);
      ln_total_rec_cnt       NUMBER;                -- count for total records
      ln_success_rec_cnt     NUMBER;
      -- count for successfully processed records
      ln_error_rec_cnt       NUMBER;              -- count fro errored records
      lc_error_loc           VARCHAR2 (50);
      lc_error_msg           VARCHAR2 (500);
      ln_request_id          NUMBER;
      ld_request_date        DATE;
      ln_record_insert       NUMBER;
      lc_errbuf              VARCHAR2 (24000);
      v_n_msg_index_out      VARCHAR2 (32000);
      lc_retcode             VARCHAR2 (10);
      lc_return_status       VARCHAR2 (1);
      lc_msg_data            VARCHAR2 (255);
      ln_err_count           NUMBER := 0;
      ln_count               NUMBER;
      ln_receipt_method_id   NUMBER;
      ln_customer_number     NUMBER;
      ln_cr_id               NUMBER;
      ln_msg_count           NUMBER;
      lb_receipt_method      BOOLEAN;
      lb_receipt_exists      BOOLEAN;
      lb_invoice_number      BOOLEAN;
      lb_customer            BOOLEAN;
      ln_cust_site_use_id    NUMBER;
      ld_gl_date             DATE;
      lc_account_name        VARCHAR2 (240);
      ln_amt                 NUMBER (14, 3);
      l_receipt_number       VARCHAR2 (250);
      l_recpt_methd_name     VARCHAR2 (300);


      CURSOR lci_rec                        --(receipt_num varchar2,ou NUMBER)
      IS
           SELECT apsa.TRX_DATE AS "TRX_DATE",
                  apsa.GL_DATE AS "GL_DATE",
                  apsa.trx_number AS "INVOICE_NUMBER",
                  apsa.INVOICE_CURRENCY_CODE AS "INVOICE_CURRENCY_CODE",
                  apsa.AMOUNT_DUE_REMAINING AS "AMOUNT_DUE_REMAINING",
                  rcta.BILL_TO_CUSTOMER_ID AS "BILL_TO_CUSTOMER_ID",
                  hz.party_name AS "PARTY_NAME",
                  hz.PARTY_NUMBER "PARTY_NUMBER",
                  apsa.org_id AS "ORG_ID",
                  rcta.EXCHANGE_RATE AS "EXCHANGE_RATE",
                  rcta.EXCHANGE_RATE_TYPE AS "EXCHANGE_RATE_TYPE",
                  rcta.EXCHANGE_DATE AS "EXCHANGE_DATE"
             FROM ar_payment_schedules_all apsa,
                  ra_customer_trx_all rcta,
                  abpom.sd64_invoiceheader lsd,
                  hz_parties HZ,
                  hz_cust_accounts hca
            WHERE     apsa.customer_trx_id = rcta.customer_trx_id
                  AND apsa.org_id = rcta.org_id
                  AND TRIM (LSD.INVOICENUMBER) = TRIM (rcta.trx_number)
                  AND TRIM (lsd.INVOICENUMBER) =
                         NVL (p_trx_in_number, TRIM (rcta.trx_number))
                  AND HZ.PARTY_ID = HCA.PARTY_ID
                  AND HCA.CUST_ACCOUNT_ID = rcta.BILL_TO_CUSTOMER_ID
                  --AND TRUNC(apsa.TRX_DATE)       =TRUNC(SYSDATE)
                  AND lsd.CASH_INVOICE = 'Y'
         GROUP BY apsa.TRX_DATE,
                  apsa.GL_DATE,
                  apsa.trx_number,
                  apsa.INVOICE_CURRENCY_CODE,
                  apsa.AMOUNT_DUE_REMAINING,
                  rcta.BILL_TO_CUSTOMER_ID,
                  hz.party_name,
                  hz.PARTY_NUMBER,
                  apsa.org_id,
                   rcta.EXCHANGE_RATE,
                  rcta.EXCHANGE_RATE_TYPE,
                  rcta.EXCHANGE_DATE ;
   BEGIN
      -- Apps Initialize
      mo_global.init ('AR');
      mo_global.set_policy_context ('S',
                                    TO_NUMBER (fnd_profile.VALUE ('ORG_ID')));
      lc_program_name := 'Conversion Program Name: ABP Auto  Receipt Creation';
      ln_user_id := fnd_profile.VALUE ('USER_ID');
      ln_login_id := fnd_profile.VALUE ('LOGIN_ID');
      ln_org_id := fnd_profile.VALUE ('ORG_ID');
      --Hardcoding the Receipt mathod Name

      fnd_file.put_line (
         fnd_file.LOG,
         '---------------------------------------------------------------------- ');
      DBMS_OUTPUT.put_line (
         '---------------------------------------------------------------------- ');
      fnd_file.put_line (fnd_file.LOG, lc_program_name);
      DBMS_OUTPUT.put_line (lc_program_name);
      fnd_file.put_line (fnd_file.LOG, 'Start of Log Messages: ');
      DBMS_OUTPUT.put_line ('Start of Log Messages: ');
      fnd_file.put_line (
         fnd_file.LOG,
         '---------------------------------------------------------------------- '
         || CHR (10));
      DBMS_OUTPUT.put_line (
         '---------------------------------------------------------------------- '
         || CHR (10));

      FOR cur IN lci_rec
      LOOP
         lc_error_loc := NULL;
         lc_error_msg := NULL;
         lc_errbuf := NULL;
         lc_retcode := NULL;
         ln_cust_site_use_id := NULL;
         ld_gl_date := NULL;
         lc_account_name := NULL;
         lc_error_flag := 'N';
         ln_amt := NULL;
         fnd_file.put_line (
            fnd_file.LOG,
            'Insertion Process Start for Receipt  number:'
            || cur.INVOICE_NUMBER);
  l_recpt_methd_name := TRIM ('AUTO_CASH_RECEIPT');
         --Receipt Number

         BEGIN
            SELECT 'REC0' || xxabp_receipt_seq.NEXTVAL
              INTO l_receipt_number
              FROM DUAL;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lc_error_flag := 'Y';
               lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
         END;

         --Check for Dulicate Recept Existence
         IF cur.INVOICE_NUMBER IS NOT NULL
         THEN
            lb_receipt_exists :=
               validate_receipt_existence (cur.INVOICE_NUMBER,
                                           cur.ORG_ID,
                                           lc_error_loc,
                                           lc_error_msg);

            IF lb_receipt_exists
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                  'Receipt  already Exists for this Transaction'||cur.INVOICE_NUMBER);
                    lc_error_flag := 'Y';
               lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
                 DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            ELSE
                lc_error_flag := 'N';
        
            END IF;
         ELSE
            lc_error_flag := 'Y';
            fnd_file.put_line (fnd_file.LOG, 'Receipt Number Cant be null.');
            lc_error_msg := (lc_error_msg || 'Receipt Number Cant be null.');
         END IF;

         --check for duplicate Receipt
         IF l_receipt_number IS NOT NULL
         THEN
            lb_invoice_number :=
               validate_receipt_number (l_receipt_number,
                                        cur.ORG_ID,
                                        lc_error_loc,
                                        lc_error_msg);

            IF lb_invoice_number
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                  'Receipt Number Does not exists in Base table i.e no duplicasy');
            ELSE
               lc_error_flag := 'Y';
               lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            END IF;
         ELSE
            lc_error_flag := 'Y';
            fnd_file.put_line (fnd_file.LOG, 'Receipt Number Cant be null.');
            lc_error_msg := (lc_error_msg || 'Receipt Number Cant be null.');
         END IF;



         --validate for receipts method
         IF l_recpt_methd_name IS NOT NULL
         THEN
            lb_receipt_method :=
               validate_receipt_method (l_recpt_methd_name,
                                        cur.TRX_DATE,
                                        ln_receipt_method_id,
                                        lc_error_loc,
                                        lc_error_msg);

            IF lb_receipt_method
            THEN
               IF ln_receipt_method_id IS NOT NULL
               THEN
                  --                  UPDATE xxran_ar_receipts_interim
                  --                     SET li_receipt_method_id = ln_receipt_method_id
                  --                   WHERE li_receipt_num = cur.receipt_number;
                  fnd_file.put_line (
                     fnd_file.LOG,
                     'The Receipt Method ' || l_recpt_methd_name || 'exists.');
                     DBMS_OUTPUT.put_line ( 'The Receipt Method ' || l_recpt_methd_name || 'exists.');
               ELSE
                  fnd_file.put_line (
                     fnd_file.LOG,
                        'The Receipt Method ID  for '
                     || l_recpt_methd_name
                     || ' does not exist.');
                  DBMS_OUTPUT.put_line (
                        'The Receipt Method ID  for '
                     || l_recpt_methd_name
                     || ' does not exist.');
                      lc_error_flag := 'Y';
                    lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               END IF;
            ELSE
               lc_error_flag := 'Y';
               lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            END IF;
         ELSE
            fnd_file.put_line (
               fnd_file.LOG,
               'Receipt Method does Not exists for Receipt Number: '
               || l_receipt_number);
         END IF;


         DBMS_OUTPUT.put_line (
            'ln_amt  ' || ln_amt || '  lc_error_flag: ' || lc_error_flag);

         -- calling create cash api
         IF lc_error_flag = 'N'
         THEN
            ln_cr_id := NULL;
            lc_return_status := NULL;
            ln_msg_count := NULL;
            lc_msg_data := NULL;
            DBMS_OUTPUT.put_line ('ln_amt  ' || ln_amt);
            ar_receipt_api_pub.create_cash (
               p_api_version          => 1.0,
               p_init_msg_list        => fnd_api.g_true,
               p_amount               => cur.AMOUNT_DUE_REMAINING, --cur.amount,
               p_receipt_number       => l_receipt_number,
               p_receipt_date         => cur.TRX_DATE,
               p_currency_code        => CUR.INVOICE_CURRENCY_CODE,
               --lc_currency_code,
               p_gl_date              => cur.GL_DATE,           --cur.gl_date,
               p_customer_number      => cur.PARTY_NAME,
               --ln_customer_number,
               --p_customer_site_use_id      => ln_cust_site_use_id,
               --   p_comments                  => lc_account_name,
               p_receipt_method_id    => ln_receipt_method_id,
               p_exchange_rate_type   => cur.exchange_rate_type,
               p_exchange_rate        => cur.exchange_rate,
               p_exchange_rate_date   => cur.exchange_date,
               p_org_id               => cur.ORG_ID,              --lc_org_id,
               p_cr_id                => ln_cr_id,
               x_return_status        => lc_return_status,
               x_msg_count            => ln_msg_count,
               x_msg_data             => lc_msg_data);

            IF ln_cr_id IS NOT NULL AND lc_msg_data IS NULL
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                  'Successful Ceation Of AR Receipts:' || ln_cr_id);
               DBMS_OUTPUT.put_line (
                  'Successful Ceation Of AR Receipts:' || ln_cr_id);
               COMMIT;
            ELSE
               IF ln_msg_count >= 1
               THEN
                  FOR v_n_i IN 1 .. ln_msg_count
                  LOOP
                     pa_interface_utils_pub.get_messages (
                        p_msg_data        => lc_msg_data,
                        p_encoded         => 'F',
                        p_msg_index       => ln_msg_count,
                        p_data            => lc_msg_data,
                        p_msg_count       => ln_msg_count,
                        p_msg_index_out   => v_n_msg_index_out);
                  END LOOP;
               END IF;

               fnd_file.put_line (fnd_file.LOG,
                                  'Receipt Creation Error:' || lc_msg_data);
               DBMS_OUTPUT.put_line (
                  'Receipt Creation Error:' || lc_msg_data);
               lc_error_flag := 'Y';
               lc_error_msg :=
                  (lc_error_msg || 'Receipt Creation Error:' || lc_msg_data);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            --p_out_chr_retcode := 'E';
            --p_out_chr_errbuf :=p_out_chr_errbuf||'Party Creation Error: '||l_msg_data;
            END IF;
         ELSE
            fnd_file.put_line (
               fnd_file.LOG,
               'Before Receipt Creation Error:' || lc_msg_data);
            DBMS_OUTPUT.put_line (
               'Before Receipt Creation Error :' || lc_msg_data);
            lc_error_flag := 'Y';
            lc_error_msg :=
               (lc_error_msg || 'Receipt Creation Error:' || lc_msg_data);
            DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
            fnd_file.put_line (fnd_file.LOG, lc_error_msg);
         END IF;

         ----  CALLING APPLY API
         IF lc_error_flag = 'N'
         THEN
            --ln_cr_id := NULL;
            lc_return_status := NULL;
            ln_msg_count := NULL;
            lc_msg_data := NULL;
            ar_receipt_api_pub.APPLY (
               p_api_version       => 1.0,
               p_init_msg_list     => fnd_api.g_true --,p_amount            => cur.amount
                                                    ,
               p_cash_receipt_id   => ln_cr_id,
               p_trx_number        => cur.INVOICE_NUMBER,
               p_apply_date        => cur.TRX_DATE,             --INvoice Date
               p_apply_gl_date     => cur.GL_DATE,                   --GL Date
               p_amount_applied    => cur.AMOUNT_DUE_REMAINING, --Amount Applied
               p_receipt_number    => l_receipt_number,
               p_org_id            => cur.org_id --,p_cr_id             => ln_cr_id
                                                ,
               x_return_status     => lc_return_status,
               x_msg_count         => ln_msg_count,
               x_msg_data          => lc_msg_data);

            IF ln_cr_id IS NOT NULL AND lc_msg_data IS NULL
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                  'Successful Apply Of AR Receipts:' || l_receipt_number);
               DBMS_OUTPUT.put_line (
                  'Successful Apply Of AR Receipts:' || l_receipt_number);
               COMMIT;
            ELSE
               IF ln_msg_count >= 1
               THEN
                  FOR v_n_i IN 1 .. ln_msg_count
                  LOOP
                     pa_interface_utils_pub.get_messages (
                        p_msg_data        => lc_msg_data,
                        p_encoded         => 'F',
                        p_msg_index       => ln_msg_count,
                        p_data            => lc_msg_data,
                        p_msg_count       => ln_msg_count,
                        p_msg_index_out   => v_n_msg_index_out);
                  END LOOP;
               END IF;

               fnd_file.put_line (fnd_file.LOG,
                                  'Receipt Apply Error:' || lc_msg_data);
               DBMS_OUTPUT.put_line ('Receipt Apply Error:' || lc_msg_data);
               lc_error_flag := 'Y';
               lc_error_msg :=
                  (lc_error_msg || 'Receipt Apply Error:' || lc_msg_data||SQLERRM);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            --p_out_chr_retcode := 'E';
            --p_out_chr_errbuf :=p_out_chr_errbuf||'Party Creation Error: '||l_msg_data;
            END IF;
         ELSE
            fnd_file.put_line (fnd_file.LOG,
                               'Before Receipt Apply Error:' || lc_msg_data);
            DBMS_OUTPUT.put_line (
               'Before Receipt Apply Error:' || lc_msg_data);
            lc_error_flag := 'Y';
            lc_error_msg :=
               (lc_error_msg || 'Receipt Apply Error:' || lc_msg_data);
            DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
            fnd_file.put_line (fnd_file.LOG, lc_error_msg);
         END IF;

         --
         IF lc_error_flag = 'N'
         THEN
            INSERT INTO xxabp_ar_receipts_err (ERROR_MSG,
                                               STATUS,
                                               INVOICE_NUMBER,
                                               RECEIPT_NUMBER,
                                               ORG_ID,
                                                DATE_PROCESSED)
                 VALUES ('',
                         'S',
                         CUR.INVOICE_NUMBER,
                         l_receipt_number,
                         CUR.ORG_ID,
                         SYSDATE);

            COMMIT;
            ln_success_rec_cnt := ln_success_rec_cnt + 1;
         ELSE
            fnd_file.put_line (fnd_file.LOG,
                               'Validations failed: ' || lc_error_msg);
            DBMS_OUTPUT.put_line ('Validations failed');

            INSERT INTO xxabp_ar_receipts_err (ERROR_MSG,
                                               STATUS,
                                               INVOICE_NUMBER,
                                               RECEIPT_NUMBER,
                                               ORG_ID,
                                               DATE_PROCESSED)
                 VALUES (lc_error_msg,
                         'E',
                         CUR.INVOICE_NUMBER,
                         l_receipt_number,
                         CUR.ORG_ID,
                         SYSDATE);

            COMMIT;
            ln_error_rec_cnt := ln_error_rec_cnt + 1;
         END IF;

         fnd_file.put_line (
            fnd_file.LOG,
            '---------------------------------------------------------------------------------');
         COMMIT;
      END LOOP;

      DBMS_OUTPUT.put_line ('Total ' || TO_CHAR (ln_total_rec_cnt));
      DBMS_OUTPUT.put_line ('Success ' || TO_CHAR (ln_success_rec_cnt));
      DBMS_OUTPUT.put_line ('Error ' || TO_CHAR (ln_error_rec_cnt));
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (
            fnd_file.LOG,
            'Error executing the program : ' || SUBSTR (SQLERRM, 1, 255));
         DBMS_OUTPUT.put_line (
               'Error executing the program : '
            || lc_error_msg
            || SUBSTR (SQLERRM, 1, 255));
         errbuf := SUBSTR (SQLERRM, 1, 255);
         retcode := -1;
         ROLLBACK;
   END;
END xxfin_ar_receipts_pkg;
/

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...