R12 AR Receipts upload API

  AR_RECEIPT_API_PUB.CREATE_CASH(P_API_VERSION                => 1.0,
                                 P_INIT_MSG_LIST              => FND_API.G_TRUE,
                                 P_COMMIT                     => FND_API.G_TRUE,
                                 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              => V_CUR_DATA.RECEIPT_CURRENCY_CODE,
                                 P_AMOUNT                     => V_CUR_DATA.UNAPPLIED_AMOUNT,
                                 P_RECEIPT_NUMBER             => V_CUR_DATA.RECEIPT_NUMBER,
                                 P_RECEIPT_DATE               => V_CUR_DATA.RECEIPT_DATE,
                                 P_GL_DATE                    => V_CUR_DATA.RECEIPT_DATE,
                                 P_CUSTOMER_NUMBER            => V_CUR_DATA.CUSTOMER_NUMBER,
                                 P_CUSTOMER_SITE_USE_ID       => V_CUR_DATA.SITE_USE_ID,
                                 P_REMITTANCE_BANK_ACCOUNT_ID => V_BANK_ACC_ID,
                                 P_RECEIPT_METHOD_ID          => V_RECEIPT_METHOD_ID,
                                 P_CR_ID                      => L_CASH_RECEIPT_ID, -- Out Parameter
                                 P_ORG_ID                     => FND_PROFILE.VALUE('ORG_ID'));
  -----------------------------------------------------------
  AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT(P_API_VERSION      => 1.0,
                                      P_INIT_MSG_LIST    => FND_API.G_TRUE,
                                      P_COMMIT           => FND_API.G_TRUE,
                                      P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
                                      X_RETURN_STATUS    => L_RETURN_STATUS_ACC,
                                      X_MSG_COUNT        => L_MSG_COUNT_ACC,
                                      X_MSG_DATA         => L_MSG_DATA_ACC,
                                      p_cash_receipt_id  => L_CASH_RECEIPT_ID,
                                      p_amount_applied   => V_CUR_DATA.ON_ACCOUNT_AMOUNT,
                                      p_apply_date       => V_CUR_DATA.RECEIPT_DATE,
                                      p_apply_gl_date    => V_CUR_DATA.RECEIPT_DATE,
                                      p_org_id           => FND_PROFILE.VALUE('ORG_ID'));

---------------------------------------------------------------------------------------
In R12 instead of Bank Account ID, Bank account use id has to be passed. 


---Custom Table 
CREATE TABLE XXVIR.XXVIRTU_DM_RCPT_TBL
(
  OPERATING_UNIT       VARCHAR2(200 BYTE),
  RECEIPT_METHOD       VARCHAR2(200 BYTE),
  RECEIPT_NO           VARCHAR2(50 BYTE),
  RECEIPT_DATE         DATE,
  GL_DATE              DATE,
  CUSTOMER_NUMBER      VARCHAR2(200 BYTE),
  CUSTOMER_LOCATION    VARCHAR2(40 BYTE),
  CURRENCY             VARCHAR2(3 BYTE),
  RECEIPT_AMOUNT       NUMBER,
  BANK_ACCOUNT_ID      NUMBER,
  ORG_ID               NUMBER,
  PROCESS              VARCHAR2(1 BYTE)         DEFAULT 'N',
  ERROR_DESCRIPTION    VARCHAR2(4000 BYTE),
  CUSTOMER_ID          NUMBER,
  SITE_ID              NUMBER,
  RECEIPT_METHOD_ID    NUMBER,
  BANK_ACCOUNT_USE_ID  NUMBER,
  ATTRIBUTE1           VARCHAR2(150 BYTE),
  ATTRIBUTE2           VARCHAR2(150 BYTE)
)

--Validation procedure ---
   l_error_flag           VARCHAR2 (1)                             := 'N';
   l_description          VARCHAR2 (4000)                          := NULL;
   l_customer_id          ar_customers.customer_id%TYPE            := NULL;
   l_site_use_id          hz_cust_site_uses_all.site_use_id%TYPE   := NULL;
   --
   l_attribute_rec_type   ar_receipt_api_pub.attribute_rec_type;
   l_receipt_method_id    NUMBER;
   l_cr_id                INTEGER;
   l_org_id               NUMBER;
   l_location             VARCHAR2 (40);
   x_return_status        VARCHAR2 (2000);
   x_msg_count            NUMBER;
   x_msg_data             VARCHAR2 (2000);
   l_process              VARCHAR2 (1)                             := 'N';
   l_error_description    VARCHAR2 (4000)                          := NULL;
   l_error_message        VARCHAR2 (255)                           := NULL;
BEGIN
   fnd_file.put_line (fnd_file.LOG, 'Validation started !!!');
   FOR rec IN (SELECT a.ROWID r_id, a.*
                 FROM xxvirtu_dm_rcpt_tbl a
                WHERE a.process = 'N')
   LOOP
      l_error_flag := 'N';
      l_description := NULL;
      l_customer_id := NULL;
      l_site_use_id := NULL;
      l_receipt_method_id := NULL;
      l_org_id := NULL;
      l_location :=NULL;
      fnd_file.put_line (fnd_file.LOG, '---------------');
      fnd_file.put_line (fnd_file.LOG, 'Receipt No. ' || rec.receipt_no);
      BEGIN
         SELECT organization_id
           INTO l_org_id
           FROM hr_operating_units
          WHERE NAME = rec.operating_unit;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_flag := 'E';
            l_description := 'Invalid Org.';
      END;
      BEGIN
         SELECT hca.cust_account_id
           INTO l_customer_id
           FROM hz_cust_accounts hca
          --WHERE account_number = rec.customer_number;
          WHERE trim (account_name) = trim (rec.customer_number);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_flag := 'E';
            l_description := 'Invalid customer.';
      END;
      BEGIN
         SELECT site_use_id, LOCATION
           INTO l_site_use_id, l_location
           FROM hz_cust_site_uses_all
          WHERE cust_acct_site_id IN (SELECT cust_acct_site_id
                                        FROM hz_cust_acct_sites_all
                                       WHERE cust_account_id = l_customer_id)
            AND site_use_code = 'BILL_TO'
            AND trim (LOCATION) = trim(rec.customer_location)
            AND org_id = l_org_id;
            --AND primary_flag = 'Y';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_flag := 'E';
            l_description := l_description || ' Invalid site';
      END;
      BEGIN
         SELECT receipt_method_id
           INTO l_receipt_method_id
           FROM ar_receipt_methods
          WHERE NAME = rec.receipt_method;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_flag := 'E';
            l_description := l_description || ' Invalid Receipt Method';
      END;
      fnd_file.put_line (fnd_file.LOG, l_description);
      UPDATE xxvirtu_dm_rcpt_tbl
         SET process = DECODE (l_error_flag, 'N', 'V', l_error_flag),
             error_description = l_description,
             customer_id = l_customer_id,
             site_id = l_site_use_id,
             org_id = l_org_id,
             --customer_location = l_location,
             receipt_method_id = l_receipt_method_id
       WHERE ROWID = rec.r_id;
   END LOOP;
   COMMIT;
END;

---Upload procedure 
CREATE OR REPLACE PROCEDURE XX_rcpt_upload_prc (
   retcode                  NUMBER,
   error_buf                VARCHAR2,
   pi_operating_unit   IN   VARCHAR2
)
AS
   l_error_flag           VARCHAR2 (1)                             := 'N';
   l_description          VARCHAR2 (4000)                          := NULL;
   l_customer_id          ar_customers.customer_id%TYPE            := NULL;
   l_site_use_id          hz_cust_site_uses_all.site_use_id%TYPE   := NULL;
   l_location             VARCHAR2 (40);
   --
   l_attribute_rec_type   ar_receipt_api_pub.attribute_rec_type;
   l_cr_id                INTEGER;
   l_process              VARCHAR2 (1)                             := 'N';
   l_error_description    VARCHAR2 (4000)                          := NULL;
   l_error_message        VARCHAR2 (4000)                           := NULL;
   i                      NUMBER                                   := 0;
   l_org_id               NUMBER;
   l_return_status        VARCHAR2 (2000);
   l_msg_count            NUMBER;
   l_msg_data             VARCHAR2 (4000);
   l_msg_index_out        NUMBER;
   l_dummy_cnt            NUMBER;
   l_loop_cnt             NUMBER;
BEGIN
   SELECT organization_id
     INTO l_org_id
     FROM hr_operating_units
    WHERE NAME = pi_operating_unit;
   fnd_global.apps_initialize (user_id           => fnd_global.user_id,
                               resp_id           => fnd_global.resp_id,
                               resp_appl_id      => fnd_global.resp_appl_id
                              );
   mo_global.set_org_context (l_org_id, NULL, 'AR');
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', l_org_id);
   FOR rec_1 IN (SELECT x.ROWID r_id, x.*
                   FROM xxvirtu_dm_rcpt_tbl x
                  WHERE x.process = 'V'
                    AND operating_unit = pi_operating_unit)
                    --AND receipt_no = 'PYMNT0005286')
   LOOP
      fnd_file.put_line (fnd_file.LOG, 'Receipt creation started !!!');
      --i:=10;
      l_cr_id := NULL;
      l_attribute_rec_type := NULL;
      l_return_status := NULL;
      l_msg_count := NULL;
      l_msg_data := NULL;
      l_location := NULL;
      l_process := 'Y';
      l_error_description := NULL;
      l_error_message := NULL;
      fnd_file.put_line (fnd_file.LOG, '---------------');
      fnd_file.put_line (fnd_file.LOG, 'Receipt No. ' || rec_1.receipt_no);
      fnd_file.put_line (fnd_file.LOG, 'rec_1.org_id' || rec_1.org_id);
      l_attribute_rec_type.attribute2 := '000000';
    
      ar_receipt_api_pub.create_cash
                  (p_api_version                     => 1.0,
                   p_init_msg_list                   => fnd_api.g_true,
                   p_commit                          => fnd_api.g_true,
                   p_validation_level                => fnd_api.g_valid_level_full,
                   p_receipt_number                  => rec_1.receipt_no,
                   p_amount                          => rec_1.receipt_amount,
                   p_receipt_date                    => rec_1.receipt_date,
                   p_gl_date                         => rec_1.gl_date,
                   p_receipt_method_id               => rec_1.receipt_method_id,
                   p_customer_id                     => rec_1.customer_id,
                   --p_exchange_rate_type              => 'User',
                   --p_exchange_rate                   =>1,
                   --p_customer_number                 => rec_1.customer_number,
                   p_customer_site_use_id            => rec_1.site_id,
                   p_location                        => rec_1.customer_location,
                   p_remittance_bank_account_id      => rec_1.bank_account_use_id,
                   -- 10061,
                   p_org_id                          => rec_1.org_id,
                   p_usr_currency_code               => NULL,
                   p_currency_code                   => rec_1.currency,
                   p_attribute_rec                   => l_attribute_rec_type,
                   p_cr_id                           => l_cr_id,
                   x_return_status                   => l_return_status,
                   x_msg_count                       => l_msg_count,
                   x_msg_data                        => l_msg_data
                  );
      xxvirtu_common_pkg.writelog ('l_return_status :' || l_return_status);
      IF NVL (l_return_status, 'E') != 'S'
      THEN
         l_process := 'E';
         IF l_msg_count > 0
         THEN
            l_loop_cnt := 1;
            LOOP
               fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                                p_data               => l_msg_data,
                                p_encoded            => fnd_api.g_false,
                                p_msg_index_out      => l_dummy_cnt
                               );
               IF l_return_status = 'E' OR l_return_status = 'U'
               THEN
                  l_msg_data := CONCAT ('ERROR >>> ', l_msg_data);
                  IF LENGTH (l_error_message || l_msg_data) < 3000
                  THEN
                     l_error_message := l_error_message || l_msg_data;
                  END IF;
               END IF;
               l_loop_cnt := l_loop_cnt + 1;
               EXIT WHEN l_loop_cnt > l_msg_count;
            END LOOP;
            xxvirtu_common_pkg.writelog ('l_error_message :'
                                         || l_error_message
                                        );
         END IF;
         ROLLBACK;
      ELSE
         l_process := 'Y';
         fnd_file.put_line (fnd_file.LOG, 'Receipt Created!!!');
         fnd_file.put_line (fnd_file.LOG, 'Receipt ID :' || l_cr_id);
         xxvirtu_common_pkg.writelog ('l_error_message :' || l_error_message);
         xxvirtu_common_pkg.writelog ('l_error_message :' || l_error_message);
      END IF;
      UPDATE xxvirtu_dm_rcpt_tbl
         SET process = l_process,
             error_description = l_error_message
       WHERE ROWID = rec_1.r_id;
      COMMIT;
   END LOOP;
END;

Credit goes to the below website(s) :

http://manaskalsa.blogspot.com