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;
/

AR customer Bank upload api script

/* Formatted on 2013/06/12 15:49 (Formatter Plus v4.8.8) */
DECLARE
   party_id                  NUMBER (15);
   l_cust_account_id         NUMBER;
   p_api_version             NUMBER                                    := 1.0;
   p_init_msg_list           VARCHAR2 (1)                              := 'F';
   p_commit                  VARCHAR2 (1)                              := 'F';
   p_ext_bank_acct_rec       iby_ext_bankacct_pub.extbankacct_rec_type;
   x_payer                   iby_fndcpt_common_pub.payercontext_rec_type;
   x_acct_id                 NUMBER;
   x_assign_id               NUMBER;
   x_return_status           VARCHAR2 (2000);
   x_msg_count               NUMBER (5);
   x_msg_data                VARCHAR2 (100);
   x_response                iby_fndcpt_common_pub.result_rec_type;
   p_ext_payee_tab           iby_disbursement_setup_pub.external_payee_tab_type;
   x_ext_payee_id_tab        iby_disbursement_setup_pub.ext_payee_id_tab_type;
   x_ext_payee_status_tab    iby_disbursement_setup_pub.ext_payee_create_tab_type;
   l_ext_payee_rec           iby_disbursement_setup_pub.external_payee_rec_type;
   l_ext_payee_id_rec_type   iby_disbursement_setup_pub.ext_payee_id_rec_type;
   p_payee                   iby_disbursement_setup_pub.payeecontext_rec_type;
   p_assignment_attribs      iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
   p_instrument              iby_fndcpt_setup_pub.pmtinstrument_rec_type;
   v_org_id                  NUMBER;
   v_party_id                NUMBER;
   v_bank_id                 NUMBER;
   v_bank_branch_id          NUMBER;
   v_party_site_id           NUMBER;
   v_vendor_site_id          NUMBER;

   CURSOR c1
   IS
      SELECT   *
          FROM XXX_customer_bank
         WHERE customer_code IN (SELECT party_number
                                   FROM hz_parties)
           AND customer_code  IN (SELECT   customer_code
                                         FROM XXX_customer_bank
                                       HAVING COUNT (customer_code) > 1
                                     GROUP BY customer_code)
           AND bank_account_num NOT IN (SELECT bank_account_num
                                          FROM iby_ext_bank_accounts)
      ORDER BY customer_code;
BEGIN
   FOR i IN c1
   LOOP
      IF i.bank_name IS NOT NULL
      THEN
         BEGIN
            SELECT party_id
              INTO v_party_id
              FROM hz_parties
             WHERE party_number = i.customer_code;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               DBMS_OUTPUT.put_line ('Party Does not exist' || v_party_id);
         END;

         BEGIN
            SELECT cust_account_id
              INTO l_cust_account_id
              FROM hz_cust_accounts_all
             WHERE account_number NOT LIKE '%Unapplied%'
               AND party_id = v_party_id
               and rownum=1;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               DBMS_OUTPUT.put_line (   ' Customer account Does not exist'
                                     || v_party_id
                                    );
         END;

         BEGIN
            SELECT bank_party_id, branch_party_id
              INTO v_bank_id, v_bank_branch_id
              FROM ce_bank_branches_v
             WHERE bank_name = i.bank_name
               AND bank_branch_name = i.bank_branch_name;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
         END;
      END IF;

-- *****************************************************************************
      p_ext_bank_acct_rec.object_version_number := 1.0;
      p_ext_bank_acct_rec.acct_owner_party_id := v_party_id;
--p_ext_bank_acct_rec.acct_owner_party_site_id := v_party_site_id;
      p_ext_bank_acct_rec.bank_account_num := i.bank_account_num;
      p_ext_bank_acct_rec.bank_account_name := i.bank_account_name;
      p_ext_bank_acct_rec.alternate_acct_name := i.bank_account_name;
      p_ext_bank_acct_rec.bank_id := v_bank_id;
      p_ext_bank_acct_rec.branch_id := v_bank_branch_id;
      p_ext_bank_acct_rec.start_date := SYSDATE;
      p_ext_bank_acct_rec.country_code := 'OM';
      p_ext_bank_acct_rec.currency := 'OMR';
      p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';
      p_ext_bank_acct_rec.payment_factor_flag := 'N';
      iby_ext_bankacct_pub.create_ext_bank_acct (p_api_version,
                                                 p_init_msg_list,
                                                 p_ext_bank_acct_rec,
                                                 x_acct_id,
                                                 x_return_status,
                                                 x_msg_count,
                                                 x_msg_data,
                                                 x_response
                                                );
      COMMIT;
      DBMS_OUTPUT.put_line ('1ST API ERROR STATUS:' || ' ' || x_return_status);
      /*p_payee.supplier_site_id := v_vendor_site_id;
      p_payee.party_id := v_party_id;
      p_payee.party_site_id := v_party_site_id;
      p_payee.payment_function := 'PAYABLES_DISB';
      p_payee.org_id := 81;
      p_payee.org_type := 'OPERATING_UNIT';*/
      x_payer.payment_function := 'CUSTOMER_PAYMENT';
      x_payer.party_id := v_party_id;
      --x_payer.org_id := 81;
      --x_payer.org_type := 'OPERATING_UNIT';
      x_payer.cust_account_id := l_cust_account_id;
      x_payer.account_site_id := NULL;
      DBMS_OUTPUT.put_line (   ' Details are cust id'
                            || l_cust_account_id
                            || 'PARTY ID'
                            || v_party_id
                           );
--l_ext_payee_id_rec_type := x_ext_payee_id_tab(0);
--p_instrument.Instrument_Id := x_acct_id;
--p_instrument.Instrument_Type := 'BANKACCOUNT';
--p_assignment_attribs.assignment_id:=
      p_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';
      p_assignment_attribs.instrument.instrument_id := x_acct_id;
      p_assignment_attribs.priority := 1;
      p_assignment_attribs.start_date := SYSDATE;
      p_assignment_attribs.end_date := NULL;
--p_assignment_attribs.Instrument := p_instrument;
      iby_fndcpt_setup_pub.set_payer_instr_assignment
                                (p_api_version             => p_api_version,
                                 p_init_msg_list           => p_init_msg_list,
                                 p_commit                  => p_commit,
                                 x_return_status           => x_return_status,
                                 x_msg_count               => x_msg_count,
                                 x_msg_data                => x_msg_data,
                                 p_payer                   => x_payer,
                                 p_assignment_attribs      => p_assignment_attribs,
                                 x_assign_id               => x_assign_id,
                                 x_response                => x_response
                                );
      DBMS_OUTPUT.put_line ('3RD API ERROR STATUS:' || ' ' || x_return_status);
      DBMS_OUTPUT.put_line ('Message:' || ' ' || x_msg_data);
      COMMIT;
   END LOOP;
END;

Receipt writeof scripts

/* Formatted on 2012/06/30 14:59 (Formatter Plus v4.8.8) */
--- Note no 1361075.1
DECLARE
   l_return_status                  VARCHAR2 (1);
   l_msg_count                      NUMBER;
   l_msg_data                       VARCHAR2 (240);
   l_cash_receipt_id                NUMBER;
   p_count                          NUMBER                               := 0;
   l_application_ref_type           ar_receivable_applications.application_ref_type%TYPE;
   l_application_ref_id             ar_receivable_applications.application_ref_id%TYPE;
   l_application_ref_num            ar_receivable_applications.application_ref_num%TYPE;
   l_secondary_application_ref_id   ar_receivable_applications.secondary_application_ref_id%TYPE;
   l_receivable_application_id      ar_receivable_applications.receivable_application_id%TYPE; 

   CURSOR trx_lines_to_ar_c
   IS
      SELECT sno, receipt_number, customer_name, customer_number,
             location_code, apply_amount
        FROM XXX_receipt_write_off
       WHERE sno not in (9);
BEGIN
   -- 1) Set the applications context
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', '82');
   fnd_global.apps_initialize (1110, 20678, 222, 0);

   FOR c1 IN trx_lines_to_ar_c
   LOOP
      BEGIN
         SELECT cash_receipt_id
           INTO l_cash_receipt_id
           FROM ar_cash_receipts_all a, hz_cust_accounts_all b, hz_parties c
          WHERE a.pay_from_customer = b.cust_account_id
            AND b.party_id = c.party_id
            AND receipt_number = c1.receipt_number
            AND b.account_number = c1.customer_number
            AND UPPER (TRIM (c.party_name)) = UPPER (TRIM (c1.customer_name));
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Receipt Number does not exist'
                                  || c1.receipt_number
                                 );
      END;

      ar_receipt_api_pub.activity_application
             (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_cash_receipt_id                   => l_cash_receipt_id ,
              p_applied_payment_schedule_id       => -3,
              p_receivables_trx_id                => 2001,
              p_receivable_application_id         => l_receivable_application_id,
              p_application_ref_type              => l_application_ref_type,
              p_application_ref_id                => l_application_ref_id,
              p_application_ref_num               => l_application_ref_num,
              p_secondary_application_ref_id      => l_secondary_application_ref_id
             );
      -- 3) Review the API output
      DBMS_OUTPUT.put_line ('Status ' || l_return_status);
      DBMS_OUTPUT.put_line ('Message count ' || l_msg_count);
      DBMS_OUTPUT.put_line ('Application ID ' || l_receivable_application_id);
      DBMS_OUTPUT.put_line (   'Successfully Receipt Write off has been done '
                            || c1.receipt_number
                           );

      IF l_msg_count = 1
      THEN
         DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
      ELSIF l_msg_count > 1
      THEN
         LOOP
            p_count := p_count + 1;
            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 ' || p_count || '. ' || l_msg_data);
         END LOOP;
      END IF;

      COMMIT;
   END LOOP;

   COMMIT;
END;

AR invoice adjustment api script

SET serveroutput on

DECLARE
   v_init_msg_list         VARCHAR2 (1000);
   v_commit_flag           VARCHAR2 (5)                            := 'F';
   v_validation_level      NUMBER (4)           := fnd_api.g_valid_level_full;
   v_msg_count             NUMBER (4);
   v_msg_data              VARCHAR2 (1000);
   v_return_status         VARCHAR2 (5);
   v_adj_rec               ar_adjustments%ROWTYPE;
   v_chk_approval_limits   VARCHAR2 (5)                            := 'F';
   v_check_amount          VARCHAR2 (5)                            := 'F';
   v_move_deferred_tax     VARCHAR2 (1)                            := 'Y';
   v_new_adjust_number     ar_adjustments.adjustment_number%TYPE;
   v_new_adjust_id         ar_adjustments.adjustment_id%TYPE;
   v_called_from           VARCHAR2 (25)                         := 'ADJ-API';
   v_old_adjust_id         ar_adjustments.adjustment_id%TYPE;
   l_customer_trx_id       NUMBER;
   l_payment_schedule_id   NUMBER;
   l_receivables_trx_id    NUMBER;

   CURSOR trx_lines_to_ar_c
   IS
      Select * from AR_CM_ADUJUSTMENTS
Where invoice_currency_code='USD' ORDER BY  1;
BEGIN
   FOR c1 IN trx_lines_to_ar_c
   LOOP
      BEGIN
         SELECT a.customer_trx_id, b.payment_schedule_id
           INTO l_customer_trx_id, l_payment_schedule_id
           FROM ra_customer_trx_all a, ar_payment_schedules_all b
          WHERE a.customer_trx_id = b.customer_trx_id
            AND a.trx_number = c1.trx_number;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Transaction Number does not exist'
                                  || c1.trx_number
                                 );
      END;

      BEGIN
         SELECT receivables_trx_id
           INTO l_receivables_trx_id
           FROM ar_receivables_trx_all
          WHERE NAME = 'Adjustment-Data Migration';
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Receivable Transaction  does not exist'
                                  || 'Adjustment-Data Migration'
                                 );
      END;

/*------------------------------------+
| Setting global initialization |
+------------------------------------*/
      mo_global.init ('AR');
      mo_global.set_policy_context ('S', '82');
      fnd_global.apps_initialize (1113, 20678, 222, 0);
/*------------------------------------+
| Setting value to input parameters |
+------------------------------------*/
--Populate v_adj_rec record
      v_adj_rec.customer_trx_id := l_customer_trx_id;
      v_adj_rec.TYPE := 'LINE';
      v_adj_rec.payment_schedule_id := l_payment_schedule_id;
      v_adj_rec.receivables_trx_id := l_receivables_trx_id;
      v_adj_rec.amount := c1.AMOUNT;
      v_adj_rec.apply_date := TO_DATE (c1.apply_date);
      v_adj_rec.gl_date := TO_DATE (c1.gl_date);
      v_adj_rec.created_from := 'ADJ-API';
      ar_adjust_pub.create_adjustment ('AR_ADJUST_PUB',
                                       1.0,
                                       v_init_msg_list,
                                       v_commit_flag,
                                       v_validation_level,
                                       v_msg_count,
                                       v_msg_data,
                                       v_return_status,
                                       v_adj_rec,
                                       v_chk_approval_limits,
                                       v_check_amount,
                                       v_move_deferred_tax,
                                       v_new_adjust_number,
                                       v_new_adjust_id,
                                       v_called_from,
                                       v_old_adjust_id
                                      );
      DBMS_OUTPUT.put_line (v_msg_data);
      DBMS_OUTPUT.put_line (v_new_adjust_number);
      DBMS_OUTPUT.put_line (v_new_adjust_id);
      DBMS_OUTPUT.put_line (v_init_msg_list);
      DBMS_OUTPUT.put_line ('Successfully Completed');
      COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      FOR i IN 1 .. v_msg_count
      LOOP
         DBMS_OUTPUT.put_line (v_msg_data);
         DBMS_OUTPUT.put_line (v_new_adjust_number);
         DBMS_OUTPUT.put_line (v_new_adjust_id);
         DBMS_OUTPUT.put_line (v_init_msg_list);
      END LOOP;
END;
/

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;

Receipt apply API script

/* Formatted on 2012/05/31 20:10 (Formatter Plus v4.8.8) */
DECLARE
   l_return_status     VARCHAR2 (1);
   l_msg_count         NUMBER;
   l_msg_data          VARCHAR2 (240);
   l_cash_receipt_id   NUMBER;
   l_customer_trx_id   NUMBER;

   CURSOR trx_lines_to_ar_c
   IS
      SELECT   sno, receipt_number, trx_number, account_number,
               amount_applied, apply_date, applied_gl_date
          FROM XXX_ar_receip_rec_3
         WHERE sno IN (1)
           AND UPPER (TRIM (customer_name)) IN (
                                              SELECT UPPER (TRIM (party_name))
                                                FROM hz_parties)
           AND receipt_amount NOT LIKE '-%'
           and account_number=5754
           AND receipt_number ='    270143-CASH'
      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 cash_receipt_id
           INTO l_cash_receipt_id
           FROM ar_cash_receipts_all a, hz_cust_accounts_all b
          WHERE a.pay_from_customer = b.cust_account_id
            AND TRIM (b.account_number) = TRIM (c1.account_number)
            AND TRIM (receipt_number) = TRIM (c1.receipt_number);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Recept Number Does not exist'
                                  || TRIM (c1.receipt_number)
                                 );
      END;

      BEGIN
         SELECT customer_trx_id
           INTO l_customer_trx_id
           FROM ra_customer_trx_all
          WHERE TRIM (trx_number) = TRIM (c1.trx_number);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Trx_number Does not exist'
                                  || TRIM (c1.trx_number)
                                 );
      END;

      ar_receipt_api_pub.APPLY
                            (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_cash_receipt_id                  => l_cash_receipt_id,
                             p_customer_trx_id                  => l_customer_trx_id,
                             p_applied_payment_schedule_id      => NULL,
                             p_amount_applied                   => c1.amount_applied,
                             p_show_closed_invoices             => 'Y',
                             p_apply_date                       => c1.apply_date,
                             p_apply_gl_date                    => c1.applied_gl_date,
                             p_line_number                      => 1
                            );
      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;
      DBMS_OUTPUT.put_line ('Successfully completed');
   END LOOP;
END;

Dissecting TCA Architecture

Before start , you try to understand the a Typical Business model as per figure below then you can able to understand the TCA Data Architecture easily.

Hz_Parties

Information Stored

• The HZ_PARTIES stores information about parties such as organizations, people, and groups, including the identifying address information for the party.Several pieces of data, such as the identifying address, organization profile information, and person profile information, are denormalized onto this table for performance reasons.Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name.
• The identifying address contained in the HZ_PARTIES table is denormalized from the HZ_LOCATIONS table.
• Example, It stores the information about Hub Inc. as Party_Relationship, Hub Corp. as Organization and Mike as Person Hz_Person_Profiles

Information Stored

• The HZ_PERSON_PROFILES stores detail information about people (including demographic information, phonetic name pronunciation, academic/professional titles,
  etc.).
• Some of the information stored on this table is denormalized to the HZ_PARTIES table.
• Example, It stores complete details about Mike. Hz_Organization_Profiles

Information Stored

• The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created.This table stores credit rating, financial statistics, socio-economic and corporate linkage information for organizations.Each time organization information is changed, the effective end date column for the original record is updated and a new record that contains the updated information is created.Some of the information stored on this table is denormalized to the HZ_PARTIES table.
• Example, it stores complete details about the Hub Corp Organization. Hz_Person_Language

Information Stored

• Hz_Person_Language table stores information about a language spoken by a party of the Person type.
• Example, Mike may speak Spanish as his primary language. You would create another record if he speaks French, but it is not his primary language. Note that a separate record must exist for each language. Hz_Relationships

Information Stored

• Hz_Relationships table stores information about relationships between one party and another party. The SUBJECT_ID and OBJECT_ID columns specify the relationship that exists between two parties.

Linking Details

• Subject_id is stored in the party id of Hz_Parties when the party type of Hz_Parties is person.
• Object id is stored in party of Hz_Parties when the party type of Hz_Parties is Organization. HZ_RELATIONSHIP_TYPES

The HZ_RELATIONSHIP_TYPES table defines the business rules that are associated with a relationship type. A non-directional relationship type consists of a single record with the same forward (FORWARD_REL_CODE) and backward (BACKWARD_REL_CODE) relationship codes. A directional relationship type consists of two records: one for the parent (DIRECTION_CODE is P) and the other for a child (DIRECTION_CODE is C) of that parent. Forward and backward relationship codes are validated against the PARTY_RELATIONS_TYPE lookup type. Hz_Party_Relationships

Information Stored

• Hz_Party_Relationships table stores parent – child information and the relationship between them.
• Example, It stores information about the Hub Inc with the subject as John and Object as Hub Corp and relationship as Employee of.

Linking Details

• Subject_id is stored in the party id of Hz_Parties when the party type of Hz_Parties is person.
• Object id is stored in party of Hz_Parties when the party type of Hz_Parties is Organization. Hz_Org_Contacts

Information Stored

• Hz_Org_Contacts table stores information about the position of the contact for a party or party site. The records in this table provide information about a contact position such as JOB_TITLE,DEPARTMENT_CODE and general contact information.
• This table is not used to store information about a specific person or organization, such as name and identification codes, that information in stored in the HZ_PARTIES table.
• Example, this table may include a record for the position of vice president of manufacturing that indicates that the contact is a senior

Linking Details

• HZ_RELATIONSHIPS is the ‘parent table’ to HZ_ORG_CONTACTS (i.e. 1-to-Many) but there is a Many to-1 relationship enforced in the API. That is, inserting a row into HZ_ORG_CONTACTS will insert two rows into HZ_RELATIONSHIPS table One row is inserted for the relationship and another one for the reciprocal relationship.
• Example “Mike Employee of Hub Corp.” and “Hub Corp. Employer of Mike”.

Hz_Org_Contact_Roles

Information Stored

• Hz_Org_Contact_Roles stores information about the role of the contact position that is specified in the Hz_Org_Contacts table. Contacts may have multiple roles.
• Example an employee may have a role a Manager, Assistant etc Hz_Party_Sites

Information Stored

• Hz_Party_Sites table stores location specific party information. One party can Optionally have one or more party sites.
• Example, It stores SFO and LA as sites for the party Hub Corp.. Hz_Party_Site_Uses

Information Stored

• Hz_Party_Site_Uses table stores information about how a party site is used.
• Party sites can have multiple uses for example, Bill to and Ship to uses. Hz_Locations :

Information Stored

The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. Records in the HZ_LOCATIONS table can store delivery and postal information about a location through columns such as the LOCATION_DIRECTIONS, POST_OFFICE,
and TIME_ZONE columns. You can also use the HZ_LOCATIONS table to store latitude and longitude information. Data in the HZ_LOCATIONS table is also used to determine the appropriate tax authority and tax rates for sales tax and VAT calculations Hz_Loc_Assignments

Information Stored

• Hz_Loc_Assignments table stores information about the relationship between a location defined in the HZ_LOCATIONS table and a tax
  authority defined in the AR_LOCATION_COMBINATIONS table.
• The appropriate sales tax can be calculated when you assign a location to a tax authority.
• In a multi–org environment, a record is created for each organization at the location. HZ_CUST_ACCOUNTS

Information Stored

• The HZ_CUST_ACCOUNTS table stores information about customer relationships established with a party. Since a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice. Note that the focus of this table is a business relationship and how transactions are conducted in the relationship.
• Example, When Hub Corp or Mike becomes the customer, and then their account information is stored in this table. Hz_Cust_Acct_Relate_Al

Information Stored

• Hz_Cust_Acct_Relate_All stores information about relationships between customer accounts.
• A flag allows you to indicate whether a relationship is reciprocal.

Linking Details

None Hz_Cust_Account_Roles

Information Stored

• Hz_Cust_Account_Roles table stores information about a role or function that a party performs in relation to a customer account. Note that only Parties of type Relationship should be inserted into this table.
• Example, Jack is an employee of Hub Corp. Jack might be a legal contact forHub Corp. So the role type is Contact. Hz_Role_Responsibility

Information Stored

• Hz_Role_Responsibility table stores information about the required or expected activities of a party based on the party’s role or function in relation to a customer account.
• Example, Jack might be the first contact for Hub Corp. So the responsibility type is First Contact. Hz_Cust_Acct_Sites_All

Information Stored

• Hz_Cust_Acct_Sites_All table stores information about customer account sites or locations for customer accounts. One customer account can have multiple sites or locations

Linking Details

• Address information for a site is stored in Hz_Locations table. But it is linked via Hz_Party_Sites table. Hz_Cust_Site_Uses_All

Information Stored

• Hz_Cust_Site_Uses_All table stores information about the business purposes assigned to a customer account site.
• Example, Bill to and Ship to uses. Hz_Customer_Profiles

Information Stored

• Hz_Customer_Profiles table stores information about the credit characteristics of a single customer account. Hz_Cust_Profile_Amts

Information Stored

• Hz_Cust_Profile_Amts table stores information about the credit limits specified for a customer profile class for a single currency.
• The credit limits of the profile class can then be assigned to specific customer accounts or customer account sites. Hz_Cust_Profile_Classes

Information Stored

• Hz_Cust_Profile_Classes table stores information about credit characteristics those are common across a group of customer accounts.
• Example, you can create a profile class called Trade and can specify several attributes that describe this class of customer. In the future, you can assign new customers to this class so that the new customer inherits the characteristics of the class. Hz_Contact_Points

Information Stored

• Hz_Contact_Points table stores information about how to communicate to parties or party sites using electronic media or methods such as Electronic Data Interchange (EDI), e-mail, telephone, telex, and the Internet.
• Example, telephone related data can include the type of telephone line,a touch-tone indicator, a country code, the area code, the telephone number, and an extension number to a specific handset.
• NOTE: Each media or method should be stored as a separate record in this table.with Hz_Parties via Hz_Relationships.
• If the party type is Person,then Hz_Contact_Points can be directly linked with Hz_Parties

Linking Details

• If the party type is Organization, then Hz_Contact_Points is linked here. Hz_Contact_Restrictions

Information Stored

• Hz_Contact_Restrictions table stores information about limitations on when and how parties should be contacted or why no contact should be made with the party.
• Example, a customer contact that is on vacation for several weeks may request that no faxes be sent during a specific time period.