1 2 Previous Next 27 Replies Latest reply: Apr 16, 2009 10:53 AM by Jean Coupal Go to original post RSS
      • 15. Re: TCA MIgration
        Nadia_Bendjedou
        I am not sure if there are APIs or interfacse API for the Banking information, but it would be good opportunity to check the Integration Repository (iRep). It is shipped with R12.

        You can also check the 11i10 irep on irep.oracle.com

        Regards,
        Nadia
        • 16. Re: TCA MIgration
          605010
          Hi All,
          I am working with Supplier interface in R12.
          I am able to load Supplier .. but supplier sites are rejected with no reason in the LOG/ OUTPUT..
          You help will be appreciated

          Thanks in advance,
          Regards,
          Pani.reddy@gmail.com
          • 17. Re: TCA MIgration
            609720
            Hi All,

            I have the same questions as my collegue above.
            Taking his words...

            Hi

            Does any one of any interface table or api to load banks, bank branches and supplier bank account information to Release 12.

            In R12 vision, bank information is present in CE tables, HZ tables and AP tables (not used), but I am not able to find any api or interface table to load bank, bank accounts and the relationship with suppliers.

            Also, please let me know if CE is the master schema for banks or is it IBY?

            any tips would help..
            • 18. Re: TCA MIgration
              Jean Coupal
              Hi,
              We just complete the conversion of Bank Accout information in AR and AP.
              in AR the open interface RA_CUSTOMER_BANK_INT_ALL is working fine.
              in AP we are using the API's:

              CREATE_EXT_BANK from APPS.IBY_EXT_BANKACCT_PUB
              CREATE_EXT_BANK_ACCOUNT from APPS.IBY_EXT_BANKACCT_PUB
              CREATE_EXTERNAL_PAYEE from IBY_DISBURSEMENT_SETUP_PUB
              SET_PAYEE_INSTR_ASSIGNMENT from IBY_DISBURSEMENT_SETUP_PUB

              Regards,
              JC
              • 19. Re: TCA MIgration
                123821
                Jean

                Do u have any example code on this ?
                • 20. Re: TCA MIgration
                  Jean Coupal
                  Hi,
                  That the package that we are using. We have created a views (p_coord_banc_row) with the bank account data that we want to convert and the required field (ID of party, bank, branch) there is db link because we do our dev on another instance.



                  PROCEDURE P_CONV_COORD_BANC(
                  p_coord_banc_row IN g_coord_banc_four_rec_type,
                  x_errors_msg OUT NOCOPY VARCHAR2
                  ) IS

                  party_id NUMBER(15);

                  p_api_version NUMBER := 1.0;
                  p_init_msg_list VARCHAR2(1) := G_FALSE;
                  p_commit VARCHAR2(1) := G_FALSE;
                  p_ext_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type@XX@LOC_T2_REA_APPS;
                  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@XX@LOC_T2_REA_APPS;

                  p_ext_payee_tab IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Tab_Type@XX@LOC_T2_REA_APPS;
                  x_ext_payee_id_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Tab_Type@XX@LOC_T2_REA_APPS;
                  x_ext_payee_status_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Tab_Type@XX@LOC_T2_REA_APPS;
                  l_ext_payee_rec IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type@XX@LOC_T2_REA_APPS;

                  l_ext_payee_id_rec_type IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Rec_Type@XX@LOC_T2_REA_APPS;

                  p_payee IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type@XX@LOC_T2_REA_APPS;
                  p_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type@XX@LOC_T2_REA_APPS;
                  p_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type@XX@LOC_T2_REA_APPS;

                  v_org_id NUMBER;

                  BEGIN
                  XXPFND_GLOBAL.set_application_context@XX@LOC_T2_REA_APPS('AR');

                  -- *****************************************************************************
                  p_ext_bank_acct_rec.object_version_number := 1.0;
                  p_ext_bank_acct_rec.acct_owner_party_id := p_coord_banc_row.CLE_FOURN;
                  p_ext_bank_acct_rec.bank_account_num := p_coord_banc_row.NO_COMPTE_BANC;
                  p_ext_bank_acct_rec.bank_id := p_coord_banc_row.BANK_ID;
                  p_ext_bank_acct_rec.branch_id := p_coord_banc_row.BANK_BRANCH_ID;
                  p_ext_bank_acct_rec.start_date := p_coord_banc_row.DAT_DEBUT_PERIO_COORD_BANC;
                  p_ext_bank_acct_rec.country_code := 'CA';
                  p_ext_bank_acct_rec.currency := 'CAD';
                  p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';
                  p_ext_bank_acct_rec.payment_factor_flag := 'N';
                  p_ext_bank_acct_rec.bank_account_name := 'REGULIER';

                  IBY_EXT_BANKACCT_PUB.create_ext_bank_acct@XX@LOC_T2_REA_APPS(
                  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
                  );


                  l_ext_payee_rec.Payee_Party_Site_Id := p_coord_banc_row.PARTY_SITE_ID;
                  l_ext_payee_rec.Payee_Party_Id := p_coord_banc_row.CLE_FOURN;
                  l_ext_payee_rec.payment_function := 'PAYABLES_DISB';

                  BEGIN
                  SELECT ORGANIZATION_ID
                  INTO v_org_id
                  FROM HR_OPERATING_UNITS@XX@LOC_T2_REA_APPS
                  WHERE DATE_TO is null;
                  EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                  DBMS_OUTPUT.PUT_LINE('Aucune HR_OPERATING_UNITS.ORGANIZATION_ID trouvé ');
                  v_org_id := NULL;
                  END;

                  l_ext_payee_rec.payer_org_id := v_org_id;
                  l_ext_payee_rec.payer_org_type := 'OPERATING_UNIT';
                  l_ext_payee_rec.Exclusive_Pay_Flag := 'N';
                  l_ext_payee_rec.Default_Pmt_method := 'XX_REP_VIREMENT';
                  l_ext_payee_rec.Supplier_Site_Id := p_coord_banc_row.VENDOR_SITE_ID;

                  p_ext_payee_tab(0) := l_ext_payee_rec;


                  IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee@XX@LOC_T2_REA_APPS(
                  p_api_version,
                  p_init_msg_list,
                  p_ext_payee_tab,
                  x_return_status,
                  x_msg_count,
                  x_msg_data,
                  x_ext_payee_id_tab,
                  x_ext_payee_status_tab
                  );

                  p_payee.Supplier_Site_id := p_coord_banc_row.VENDOR_SITE_ID;
                  p_payee.Party_Id := p_coord_banc_row.CLE_FOURN;
                  p_payee.Party_Site_Id := p_coord_banc_row.PARTY_SITE_ID;
                  p_payee.Payment_Function := 'PAYABLES_DISB';
                  p_payee.Org_Id := v_org_id;
                  p_payee.Org_Type := 'OPERATING_UNIT';

                  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.priority := 1;
                  p_assignment_attribs.start_date := p_coord_banc_row.DAT_DEBUT_PERIO_COORD_BANC;

                  p_assignment_attribs.Instrument := p_instrument;


                  IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment@XX@LOC_T2_REA_APPS(
                  p_api_version,
                  p_init_msg_list,
                  p_commit,
                  x_return_status,
                  x_msg_count,
                  x_msg_data,
                  p_payee,
                  p_assignment_attribs,
                  x_assign_id,
                  x_response
                  );

                  COMMIT;


                  EXCEPTION

                  WHEN OTHERS THEN
                  FOR i IN 1..x_msg_count
                  LOOP
                  DBMS_OUTPUT.PUT_LINE( i||'. '||SubStr(FND_MSG_PUB.Get@XX@LOC_T2_REA_APPS(G_NEXT, G_FALSE), 1, 255) || CHR(10));
                  x_errors_msg := x_errors_msg || i||'. '||SubStr(FND_MSG_PUB.Get@XX@LOC_T2_REA_APPS(G_NEXT, G_FALSE), 1, 255) || CHR(10);
                  END LOOP;

                  END P_CONV_COORD_BANC;
                  • 21. Re: TCA MIgration
                    231548
                    Hi,

                    I also have bank transaction codes,documents in 11i and do you convert them along with bank if so what is the API we need to use in r12?

                    Thanks
                    Kiyas
                    • 22. Re: TCA MIgration
                      alexander.steinbeisz
                      thank you Jean for your code example. my goal was to create a bank account for a customer bill-to site, so I had to analyse the APIs. with understanding the suppliers way, I could create the following procedure

                      --------------------------------------------------------------------------------
                      -- PROCEDURE: PRC_CREATE_PAYEE_INSTR_AR
                      -- Created by: alexander.steinbeisz [a] prodatis.com
                      -- Parameters: p_party_id Party ID
                      -- p_cust_account_id Cust Account ID
                      -- p_cust_site_use_id Cust Site Use ID (e.g. for BILL-TO, can be NULL)
                      -- p_ext_bank_acct_id Bank Account ID
                      -- p_org_id Organization ID
                      -- Return: x_assign_id ?? API returns empty assign ID
                      -- x_return_status FND_API.G_RET_STS_SUCCESS if all is fine
                      -- x_msg_count message count of FND API
                      -- x_msg_data message of FND API, if count = 1
                      -- x_response IBY_FNDCPT_COMMON_PUB.Result_rec_type
                      -- x_update_step error step on failure
                      -- Date: 2008-11-12
                      -- Issue: creates an external payer for the given customer or
                      -- customer_site and stores the bank account as payment instrument
                      -- Changes:
                      --------------------------------------------------------------------------------
                      procedure prc_create_payer_instr_ar(
                      p_party_id in number,
                      p_cust_account_id in number,
                      p_cust_site_use_id in number,
                      p_ext_bank_acct_id in number,
                      p_org_id in number,

                      x_assign_id out number,
                      x_return_status out varchar2,
                      x_msg_count out number,
                      x_msg_data out varchar,
                      x_response out IBY_FNDCPT_COMMON_PUB.Result_rec_type,
                      x_update_step out varchar2
                      )
                      is
                      l_payer_context_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
                      l_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
                      l_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
                      begin
                      x_update_step := 'IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment - fill';

                      l_payer_context_rec.Payment_Function := 'CUSTOMER_PAYMENT';
                      l_payer_context_rec.Party_Id := p_party_id;
                      l_payer_context_rec.Org_Type := 'OPERATING_UNIT';
                      l_payer_context_rec.Org_Id := p_org_id;
                      l_payer_context_rec.Cust_Account_Id := p_cust_account_id;
                      l_payer_context_rec.Account_Site_Id := p_cust_site_use_id;

                      l_instrument.Instrument_Id := p_ext_bank_acct_id;
                      l_instrument.Instrument_Type := 'BANKACCOUNT';

                      l_assignment_attribs.priority := 1; -- should be a parameter, if more than one bank expected
                      l_assignment_attribs.start_date := sysdate;
                      l_assignment_attribs.Instrument := l_instrument;

                      x_update_step := 'IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment - call';

                      IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment(
                      p_api_version => g_api_version
                      , p_init_msg_list => fnd_api.g_true
                      , p_commit => fnd_api.g_false

                      , x_return_status => x_return_status
                      , x_msg_count => x_msg_count
                      , x_msg_data => x_msg_data
                      , p_payer => l_payer_context_rec
                      , p_assignment_attribs => l_assignment_attribs
                      , x_assign_id => x_assign_id
                      , x_response => x_response
                      );

                      if x_return_status != FND_API.G_RET_STS_SUCCESS then
                      return ;
                      end if;

                      x_update_step := 'ALL DONE';

                      end;
                      --------------------------------------------------------------------------------
                      • 23. Re: TCA MIgration
                        320748
                        This is really good information. I am working on an R12 conversion of external banks and bank branches. Since I am not converting account information, I have been assuming the HZ_BANK_PUB api is the one I will utilize. I am very new to conversions. Has anyone converted just Bank/Branch data. Any insight would be greatly appreciated.

                        Thanks,

                        Rob
                        • 24. Re: TCA MIgration
                          320748
                          I was under the impression that External (or Supplier) Banks data resides in HZ_PARTIES and HZ_ORGANIZATION_PROFILES tables. I am trying to convert only bank and branch info abd have been thinking it was HZ_BANK_PUB api. But now I am thinking it should be HZ_PARTY_V2PUB.create_organization to create banks? IN TCA, they are parties, so why not create theme there?

                          Any input is greatly appreciated.

                          Thanks

                          Rob
                          • 25. Re: TCA MIgration
                            user652966
                            Hi Jean,

                            Did your code import suppliers with their corresponding bank accounts? If so, thats great because that is what I amtrying to do. I reviewed your code and tried working with it but am running into an error with the following Procedure call:

                            IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment@XX@LOC_T2_REA_APPS(
                            p_api_version,
                            p_init_msg_list,
                            p_commit,
                            x_return_status,
                            x_msg_count,
                            x_msg_data,
                            p_payee,
                            p_assignment_attribs,
                            x_assign_id,
                            x_response
                            );

                            I get the following error:

                            ORA-01086: savepoint 'SET_PAYEE_INSTR_ASSIGNMENT' never established
                            ORA-06510: PL/SQL: unhandled user-defined exception

                            What is the purpose of this API? Is it necessary for importing Suppliers and their corresponding Bank Accounts? I assume it is, but woul like some assistance if you had received this error whilw working with this process.

                            Any help would be greatly appreciated. Thanks.

                            Ray
                            • 26. Re: TCA MIgration
                              Jean Coupal
                              Hi,
                              We didn't import the supplier using API, we were using Open interface for it.
                              The code was not written by me (I was the analyst) but the distribution API was to create the link between the bank account and AP because even if the bank account is link to the supplier it does'nt tell where it could be used.
                              For the errors, I have no clue.
                              Regards, JC
                              • 27. Re: TCA MIgration
                                Jean Coupal
                                Hi,
                                If I remember, they are parties but I'm not sure if the party API let you fill the bank and branch number, if not may that the reason you need to use bank API. Iwas working on the supplier conversion and I was assuming that the bank was already converted using the bank API and we were also using them to keep up to date them with the file we were receiving from the Canadadian bank association.
                                1 2 Previous Next