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;