Customer credit Memo API script

/* Formatted on 2012/07/09 11:31 (Formatter Plus v4.8.8) */
DECLARE
   k_api_version        CONSTANT NUMBER                                  := 1;
   k_init_msg_list      CONSTANT VARCHAR2 (1)              := fnd_api.g_false;
   k_comments           CONSTANT ar_receivable_applications.comments%TYPE
                                                 := 'JUN APPLIED TRANSACTION';
   k_commit             CONSTANT VARCHAR2 (1)              := fnd_api.g_false;
   l_acctd_amount_applied_from   ar_receivable_applications_all.acctd_amount_applied_from%TYPE;
   l_acctd_amount_applied_to     ar_receivable_applications_all.acctd_amount_applied_to%TYPE;
   l_cm_app_rec                  ar_cm_api_pub.cm_app_rec_type;
   l_msg_count                   NUMBER;
   l_msg_data                    VARCHAR2 (255);
   l_out_rec_application_id      NUMBER;
   l_return_status               VARCHAR2 (1);
   credit_memo_customer_trx_id   NUMBER;
   invoice_customer_trx_id       NUMBER;

   CURSOR trx_lines_to_ar_c
   IS
      SELECT   trx_number, account_number, customer_name,
               applied_to_trx_number, amount_applied, apply_date, gl_date
          FROM XXX_cm_appply
         WHERE invoice_currency_code = 'OMR' AND sno not in (1,2)
      ORDER BY 1;
BEGIN
-- First set the environment of the user submitting the request by submitting
-- fnd_global.apps_initialize().
-- The procedure requires three parameters
-- Fnd_Global.apps_initialize(userId,responsibilityId,applicationId)
-- replace the following code with correct value as get from sql above
   FOR c1 IN trx_lines_to_ar_c
   LOOP
      mo_global.init ('AR');
      mo_global.set_policy_context ('S', '82');
      fnd_global.apps_initialize (1110, 20678, 222, 0);

      BEGIN
         SELECT customer_trx_id
           INTO credit_memo_customer_trx_id
           FROM ra_customer_trx_all a, hz_cust_accounts_all b, hz_parties c
          WHERE a.bill_to_customer_id = b.cust_account_id
            AND b.party_id = c.party_id
            AND a.trx_number = c1.trx_number
            AND b.account_number = c1.account_number
            AND UPPER (TRIM (c.party_name)) = UPPER (TRIM (c1.customer_name));
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
                          (   'Credit Memo Transaction Number does not exist'
                           || c1.trx_number
                          );
      END;

      BEGIN
         SELECT customer_trx_id
           INTO invoice_customer_trx_id
           FROM ra_customer_trx_all a, hz_cust_accounts_all b, hz_parties c
          WHERE a.bill_to_customer_id = b.cust_account_id
            AND b.party_id = c.party_id
            AND a.trx_number = c1.applied_to_trx_number
            AND b.account_number = c1.account_number
            AND UPPER (TRIM (c.party_name)) = UPPER (TRIM (c1.customer_name));
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
                          (   'Invoice Number does not exist'
                           || c1.applied_to_trx_number
                          );
      END;

      l_cm_app_rec.cm_customer_trx_id := credit_memo_customer_trx_id;
      l_cm_app_rec.cm_trx_number := NULL;                -- Credit Memo Number
      l_cm_app_rec.inv_customer_trx_id := invoice_customer_trx_id;
      l_cm_app_rec.inv_trx_number := NULL;                   -- Invoice Number
      l_cm_app_rec.installment := NULL;
      l_cm_app_rec.applied_payment_schedule_id := NULL;
      l_cm_app_rec.amount_applied := c1.amount_applied;
      l_cm_app_rec.apply_date := TRUNC (c1.apply_date);
      l_cm_app_rec.gl_date := TRUNC (c1.gl_date);
      l_cm_app_rec.inv_customer_trx_line_id := NULL;
      l_cm_app_rec.inv_line_number := NULL;
      l_cm_app_rec.show_closed_invoices := NULL;
      l_cm_app_rec.ussgl_transaction_code := NULL;
      l_cm_app_rec.attribute_category := NULL;
      l_cm_app_rec.attribute1 := NULL;
      l_cm_app_rec.attribute2 := NULL;
      l_cm_app_rec.attribute3 := NULL;
      l_cm_app_rec.attribute4 := NULL;
      l_cm_app_rec.attribute5 := NULL;
      l_cm_app_rec.attribute6 := NULL;
      l_cm_app_rec.attribute7 := NULL;
      l_cm_app_rec.attribute8 := NULL;
      l_cm_app_rec.attribute9 := NULL;
      l_cm_app_rec.attribute10 := NULL;
      l_cm_app_rec.attribute11 := NULL;
      l_cm_app_rec.attribute12 := NULL;
      l_cm_app_rec.attribute13 := NULL;
      l_cm_app_rec.attribute14 := NULL;
      l_cm_app_rec.attribute15 := NULL;
      l_cm_app_rec.global_attribute_category := NULL;
      l_cm_app_rec.global_attribute1 := NULL;
      l_cm_app_rec.global_attribute2 := NULL;
      l_cm_app_rec.global_attribute3 := NULL;
      l_cm_app_rec.global_attribute4 := NULL;
      l_cm_app_rec.global_attribute5 := NULL;
      l_cm_app_rec.global_attribute6 := NULL;
      l_cm_app_rec.global_attribute7 := NULL;
      l_cm_app_rec.global_attribute8 := NULL;
      l_cm_app_rec.global_attribute9 := NULL;
      l_cm_app_rec.global_attribute10 := NULL;
      l_cm_app_rec.global_attribute11 := NULL;
      l_cm_app_rec.global_attribute12 := NULL;
      l_cm_app_rec.global_attribute12 := NULL;
      l_cm_app_rec.global_attribute14 := NULL;
      l_cm_app_rec.global_attribute15 := NULL;
      l_cm_app_rec.global_attribute16 := NULL;
      l_cm_app_rec.global_attribute17 := NULL;
      l_cm_app_rec.global_attribute18 := NULL;
      l_cm_app_rec.global_attribute19 := NULL;
      l_cm_app_rec.global_attribute20 := NULL;
      l_cm_app_rec.comments := k_comments;
      l_cm_app_rec.called_from := NULL;
      ar_cm_api_pub.apply_on_account
                  (p_api_version                    => k_api_version,
                   p_init_msg_list                  => k_init_msg_list,
                   p_commit                         => k_commit,
                   p_cm_app_rec                     => l_cm_app_rec,
                   x_return_status                  => l_return_status,
                   x_msg_count                      => l_msg_count,
                   x_msg_data                       => l_msg_data,
                   x_out_rec_application_id         => l_out_rec_application_id,
                   x_acctd_amount_applied_from      => l_acctd_amount_applied_from,
                   x_acctd_amount_applied_to        => l_acctd_amount_applied_to
                  );
      COMMIT;
      DBMS_OUTPUT.put_line ('return_status: ' || l_return_status);
      DBMS_OUTPUT.put_line ('msg_count: ' || l_msg_count);
      DBMS_OUTPUT.put_line (   'out_rec_application_id: '
                            || l_out_rec_application_id
                           );
      DBMS_OUTPUT.put_line (   'acctd_amount_applied_from: '
                            || l_acctd_amount_applied_from
                           );
      DBMS_OUTPUT.put_line (   'acctd_amount_applied_to: '
                            || l_acctd_amount_applied_to
                           );

      IF l_msg_count = 1
      THEN
         DBMS_OUTPUT.put_line (l_msg_data);
      ELSIF l_msg_count > 1
      THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            DBMS_OUTPUT.put_line
                      (   i
                       || '. '
                       || SUBSTR
                               (fnd_msg_pub.get (p_encoded      => fnd_api.g_false),
                                1,
                                255
                               )
                      );
         END LOOP;
      END IF;
   END LOOP;
END;
/