Oracle apps ar receipt upload API script

/* Formatted on 2012/05/28 16:18 (Formatter Plus v4.8.8) */
DECLARE
   l_return_status          VARCHAR2 (1);
   l_msg_count              NUMBER;
   l_msg_data               VARCHAR2 (240);
   l_cr_id                  NUMBER;
   l_bill_to_cust_id        NUMBER;
   l_bank_account_id        NUMBER;
   l_bank_acct_use_id       NUMBER;
   l_customer_site_use_id   NUMBER;
   l_receipt_method_id      NUMBER;

   CURSOR trx_lines_to_ar_c
   IS
      SELECT   Distinct customer_name,BANK_ACCOUNT_NO,receipt_amount,receipt_number,receipt_date,gl_date
          FROM XXX_ar_receip_rec_3
         WHERE sno not in ( 1)
           AND UPPER (TRIM (customer_name)) IN (
                                              SELECT UPPER (TRIM (party_name))
                                                FROM hz_parties)
           AND RECEIPT_AMOUNT NOT LIKE '-%'                                    
       --and receipt_number  not in ( Select receipt_number from ar_cash_receipts_all)
       and receipt_number='12420766-OAB'                                                                                            
      ORDER BY 1;
BEGIN
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', '82');
   fnd_global.apps_initialize (1113, 20678, 222, 0);

   FOR c1 IN trx_lines_to_ar_c
   LOOP
      BEGIN
         SELECT DISTINCT b.cust_account_id
                    INTO l_bill_to_cust_id
                    FROM hz_parties a, hz_cust_accounts b
                   WHERE a.party_id = b.party_id
                     AND ROWNUM = 1
                     AND UPPER (TRIM (a.party_name)) =
                                               UPPER (TRIM (c1.customer_name));
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
                           (   'Customer does not exist in E Business Suite '
                            || c1.customer_name
                           );
      END;

      BEGIN
         SELECT receipt_method_id
           INTO l_receipt_method_id
           FROM ar_receipt_methods
          WHERE NAME = 'Cash';
      END;

      BEGIN
      SELECT a.bank_account_id, b.bank_acct_use_id INTO l_bank_account_id, l_bank_acct_use_id
           FROM ce_bank_accounts a, ce_bank_acct_uses_all b,ce_bank_branches_v c
          WHERE a.bank_account_id = b.bank_account_id
            AND a.BANK_BRANCH_ID=c.branch_party_id
            --and TRIM (c.BANK_NAME) =  TRIM (c1.bank_account_name)
            and trim(a.BANK_ACCOUNT_NUM)=trim(c1.BANK_ACCOUNT_NO);
        /* SELECT a.bank_account_id, b.bank_acct_use_id
           INTO l_bank_account_id, l_bank_acct_use_id
           FROM ce_bank_accounts a, ce_bank_acct_uses_all b
          WHERE a.bank_account_id = b.bank_account_id
            AND TRIM (a.bank_account_name) = TRIM (c1.bank_account_name);*/
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
                  (   'BANK_ACCOUNT_NAME does not exist in E Business Suite '
                   || TRIM (c1.BANK_ACCOUNT_NO)
                  );
      END;

      BEGIN
         SELECT c.site_use_id
           INTO l_customer_site_use_id
           FROM hz_cust_accounts_all a,
                hz_cust_acct_sites_all b,
                hz_cust_site_uses_all c,
                hz_parties d
          WHERE a.cust_account_id = b.cust_account_id
            AND b.cust_acct_site_id = c.cust_acct_site_id
            AND a.party_id = d.party_id
            AND c.site_use_code = 'BILL_TO'
            AND UPPER (TRIM (d.party_name)) = TRIM (c1.customer_name)
            AND ROWNUM = 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               (   'Custemor site use id does not exist in E Business Suite '
                || TRIM (c1.customer_name)
               );
      END;

      ar_receipt_api_pub.create_cash
                          (p_api_version                     => 1.0,
                           p_init_msg_list                   => fnd_api.g_false,
                           p_commit                          => fnd_api.g_false,
                           p_validation_level                => fnd_api.g_valid_level_full,
                           x_return_status                   => l_return_status,
                           x_msg_count                       => l_msg_count,
                           x_msg_data                        => l_msg_data,
                           p_currency_code                   => 'OMR',
                           p_amount                          => c1.receipt_amount,
                           p_receipt_number                  => c1.receipt_number,
                           p_receipt_date                    => TO_DATE
                                                                   (c1.receipt_date
                                                                   ),
                           p_gl_date                         => TO_DATE
                                                                   (c1.gl_date),
                           p_customer_id                     => l_bill_to_cust_id,
                           p_customer_bank_account_id        => NULL,
                           p_receipt_method_id               => l_receipt_method_id,
                           p_remittance_bank_account_id      => l_bank_acct_use_id,
                           p_customer_site_use_id            => NULL,
                           p_cr_id                           => l_cr_id
                          );
      COMMIT;
      DBMS_OUTPUT.put_line ('Message count ' || l_msg_count);
      DBMS_OUTPUT.put_line ('Status ' || l_return_status);

      IF l_msg_count = 1
      THEN
         DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
      ELSIF l_msg_count > 1
      THEN
         LOOP
            l_msg_data :=
                        fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

            IF l_msg_data IS NULL
            THEN
               EXIT;
            END IF;

            DBMS_OUTPUT.put_line ('Message' || l_msg_data);
         END LOOP;
      END IF;

      arp_standard.disable_debug;
      COMMIT;
      DBMS_OUTPUT.put_line ('Successfully completed');
   END LOOP;
END;