AR RECEIPT API

AR RECEIPT API

==========creation of staging table======

CREATE TABLE XX_BEL_AR_RECEIPT_STG_TBL
(
  RECEIPT_NUMBER    VARCHAR2(100 BYTE),
  RECEIPT_AMOUNT    NUMBER(20,5),
  RECEIPT_METHOD    VARCHAR2(100 BYTE),
  RECEIPT_DATE      DATE,
  CUSTOMER_NUMBER   VARCHAR2(100 BYTE),
  ORG_ID            NUMBER,
  PROCESS_FLAG      VARCHAR2(10 BYTE),
  BATCH_ID          NUMBER,
  HEADER_INT_ID     NUMBER
)

==================== creating procedure=================


CREATE OR REPLACE PROCEDURE xx_bel_ar_receipt_api_pro (
   errbuf    OUT   VARCHAR2,
   retcode   OUT   VARCHAR2
)
AS
   CURSOR xx_bel_ar_receipt_cur
   IS
      SELECT xbar.ROWID r_id, xbar.*
        FROM xx_bel_ar_receipt_stg_tbl xbar;

   l_return_status         VARCHAR2 (10);
   l_return_status1        VARCHAR2 (10);
   l_msg_count             NUMBER;
   l_msg_data              VARCHAR2 (1000);
   l_msg_data1             VARCHAR2 (1000);
   l_receipt_id            NUMBER;
   l_msg_data_out          VARCHAR2 (100);
   l_msg_data_out1         VARCHAR2 (100);
   v_receipt_method_name   ar_receipt_methods.NAME%TYPE;
   v_cust_account_id       hz_cust_accounts.cust_account_id%TYPE;
   v_cust_acct_site_id     hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
   v_site_id               hz_cust_site_uses_all.site_use_id%TYPE;
   v_err_msg               VARCHAR2 (1000);
   v_territory_id          NUMBER;
   ----Added on 20Apr09------------------------
   l_org_id                NUMBER;
   l_legal_entity_id       hr_organization_information.org_information2%TYPE;
   l_appl_id               fnd_responsibility_tl.application_id%TYPE;
   l_resp_id               fnd_responsibility_tl.responsibility_id%TYPE;
   l_user_id               fnd_user.user_id%TYPE;
   l_coa_id                gl_ledgers.chart_of_accounts_id%TYPE;
   l_territory_id          ra_territories.territory_id%TYPE;
   l_cust_account_id       hz_cust_accounts.cust_account_id%TYPE;
   l_cust_acct_site_id     hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
   l_site_id               hz_cust_site_uses_all.site_use_id%TYPE;
   l_status                VARCHAR2 (10);
   l_err_msg               VARCHAR2 (500);
   v_ar_int_chk            NUMBER                                        := 0;
   v_ar_int_rej_chk        NUMBER                                        := 0;
   v_ar_tot_cnt            NUMBER                                        := 0;
   v_ar_int_cnt            NUMBER                                        := 0;
   v_ar_int_rej_cnt        NUMBER                                        := 0;
   v_ar_cnt_1              NUMBER                                        := 0;
   v_ar_cnt_2              NUMBER                                        := 0;
   v_ar_cnt_3              NUMBER                                        := 0;
   v_ar_cnt_4              NUMBER                                        := 0;
   v_ar_cnt_5              NUMBER                                        := 0;
   v_ar_cnt_6              NUMBER                                        := 0;
   l_receivables_trx_id    ar_receivables_trx_all.receivables_trx_id%TYPE;
   l_receipt_date          DATE;
BEGIN
   SELECT fnd_profile.VALUE ('ORG_ID')
     INTO l_org_id
     FROM DUAL;

   SELECT chart_of_accounts_id
     INTO l_coa_id
     FROM gl_ledgers
    WHERE ledger_id = TO_NUMBER (fnd_profile.VALUE ('GL_SET_OF_BKS_ID'));

   SELECT application_id
     INTO l_appl_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = 'RECEIVE_RESP';

   SELECT responsibility_id
     INTO l_resp_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = 'RECEIVE_RESP';

   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = 'OPERATIONS';

   mo_global.init ('AR');
   mo_global.set_policy_context ('S', l_org_id);
   DBMS_APPLICATION_INFO.set_client_info (l_org_id);
   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => l_resp_id,
                               resp_appl_id      => l_appl_id
                              );

   FOR i IN xx_bel_ar_receipt_cur
   LOOP
      v_ar_int_chk := 0;
      l_err_msg := NULL;
      fnd_file.put_line (fnd_file.LOG, 'CREATE CASH API STARTS');
      ar_receipt_api_pub.create_cash
                           (
                            -- Standard API parameters.
                            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_usr_currency_code                 => NULL,
                            p_currency_code                     => NULL,
                            p_usr_exchange_rate_type            => NULL,
                            p_exchange_rate_type                => NULL,
                            p_exchange_rate                     => NULL,
                            p_exchange_rate_date                => NULL,
                            p_amount                            => i.receipt_amount,
                            p_factor_discount_amount            => NULL,
                            p_receipt_number                    => i.receipt_number,
                            p_receipt_date                      => i.receipt_date,
                            p_gl_date                           => '31-Mar-09',
                            p_maturity_date                     => NULL,
                            p_postmark_date                     => NULL,
                            p_customer_id                       => NULL,
                            p_customer_name                     => NULL,
                            p_customer_number                   => NULL,
                            p_customer_bank_account_id          => NULL,
                            p_customer_bank_account_num         => NULL,
                            p_customer_bank_account_name        => NULL,
                            p_payment_trxn_extension_id         => NULL,
                            p_location                          => NULL,
                            p_customer_site_use_id              => NULL,
                            p_default_site_use                  => 'Y',
                            p_customer_receipt_reference        => NULL,
                            p_override_remit_account_flag       => NULL,
                            p_remittance_bank_account_id        => NULL,
                            p_remittance_bank_account_num       => NULL,
                            p_remittance_bank_account_name      => NULL,
                            p_deposit_date                      => NULL,
                            p_receipt_method_id                 => NULL,
                            p_receipt_method_name               => NULL,
                            p_doc_sequence_value                => NULL,
                            p_ussgl_transaction_code            => NULL,
                            p_anticipated_clearing_date         => NULL,
                            p_called_from                       => NULL,
                            p_comments                          => NULL,
                            p_issuer_name                       => NULL,
                            p_issue_date                        => NULL,
                            p_issuer_bank_branch_id             => NULL,
                            p_org_id                            => i.org_id,
                            p_installment                       => NULL,
                            p_cr_id                             => l_receipt_id
                           );
      fnd_file.put_line (fnd_file.LOG, '*****************');
      fnd_file.put_line (fnd_file.LOG,
                         'CREATE CASH RECEIPT ID ' || '  ' || l_receipt_id
                        );
      fnd_file.put_line (fnd_file.LOG,
                         'RETURN STATUS   ' || '  ' || l_return_status
                        );
      fnd_file.put_line (fnd_file.LOG, '*****************');
   END LOOP;
END;
====================================end of proc=====================

=====================creating controle file============================

LOAD DATA
INFILE *
INSERT
INTO TABLE XX_BEL_AR_RECEIPT_STG_TBL
FIELDS TERMINATED BY "," 
TRAILING NULLCOLS
(
RECEIPT_NUMBER      "trim(:RECEIPT_NUMBER)"
,RECEIPT_AMOUNT      "trim(:RECEIPT_AMOUNT)"
,RECEIPT_METHOD   "trim(:RECEIPT_METHOD)"
,RECEIPT_DATE     "trim(:RECEIPT_DATE)"
,CUSTOMER_NUMBER  "trim(:CUSTOMER_NUMBER)"
,ORG_ID           "trim(:ORG_ID)"
,process_flag      "trim(:process_flag)"
,BATCH_ID      "trim(:BATCH_ID)"
,HEADER_INT_ID    "XX_BEL_AP_SUP_INT_S.NEXTVAL"
)

==================end of controle file=======================================