Bank API's in R12


Package name: IBY_EXT_BANKACCT_PUB

Operations & Routines: 

1. Create external banks
2. Update external banks
3. End up bank relationship by ending-dates
4. Check bank relationship is exist in system
5. Create external bank branches
6. Update external bank branches
7. End up bank branch relationship by ending-dates
8. Check bank branch relationship is exists
9. Create external bank accounts
10. Update external bank accounts
11. Get external bank accounts
12.  Check external accounts exists
13. Mask bank numbers
14. Encrypt and Decrypt bank account numbers
15. Unchiper bank number

Signature to create a external banks

   PROCEDURE create_ext_bank (
    p_api_version              IN  NUMBER,
    p_init_msg_list            IN  VARCHAR2 DEFAULT FND_API.G_FALSE,
    p_ext_bank_rec             IN  ExtBank_rec_type,
    x_bank_id                  OUT NOCOPY NUMBER,
    x_return_status            OUT NOCOPY VARCHAR2,
    x_msg_count                OUT NOCOPY NUMBER,
    x_msg_data                 OUT NOCOPY VARCHAR2,
    x_response                 OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  );

Signature to create a external bank branch

  PROCEDURE create_ext_bank_branch (
   p_api_version                IN   NUMBER,
   p_init_msg_list              IN   VARCHAR2 default FND_API.G_FALSE,
   p_ext_bank_branch_rec        IN   ExtBankBranch_rec_type,
   x_branch_id                  OUT  NOCOPY  NUMBER,
   x_return_status              OUT  NOCOPY  VARCHAR2,
   x_msg_count                  OUT  NOCOPY  NUMBER,
   x_msg_data                   OUT  NOCOPY  VARCHAR2,
   x_response                   OUT  NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  );

Signature to create a external bank accounts

  PROCEDURE create_ext_bank_acct (
   p_api_version                IN   NUMBER,
   p_init_msg_list                IN   VARCHAR2 default FND_API.G_FALSE,
   p_ext_bank_acct_rec          IN   ExtBankAcct_rec_type,
   x_acct_id                    OUT  NOCOPY NUMBER,
   x_return_status                OUT  NOCOPY  VARCHAR2,
   x_msg_count                    OUT  NOCOPY  NUMBER,
   x_msg_data                     OUT  NOCOPY  VARCHAR2,
   x_response                   OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  );

Generic Record Types

 -- External Bank Record
  TYPE ExtBank_rec_type IS RECORD (
    bank_id                     hz_parties.PARTY_ID%TYPE,
    bank_name                   hz_parties.PARTY_NAME%TYPE,
    bank_number                 hz_organization_profiles.BANK_OR_BRANCH_NUMBER%TYPE,
    institution_type            hz_code_assignments.CLASS_CODE%TYPE,
    country_code                hz_parties.COUNTRY%TYPE,
    bank_alt_name               hz_parties.ORGANIZATION_NAME_PHONETIC%TYPE,
    bank_short_name             hz_parties.KNOWN_AS%TYPE,
    description                 hz_parties.MISSION_STATEMENT%TYPE,
    tax_payer_id                hz_organization_profiles.JGZZ_FISCAL_CODE%TYPE,
    tax_registration_number     hz_organization_profiles.TAX_REFERENCE%TYPE,
    attribute_category          hz_parties.ATTRIBUTE_CATEGORY%TYPE,
    attribute1                  hz_parties.ATTRIBUTE1%TYPE,
    attribute2                  hz_parties.ATTRIBUTE2%TYPE,
    attribute3                  hz_parties.ATTRIBUTE3%TYPE,
    attribute4                  hz_parties.ATTRIBUTE4%TYPE,
    attribute5                  hz_parties.ATTRIBUTE5%TYPE,
    attribute6                  hz_parties.ATTRIBUTE6%TYPE,
    attribute7                  hz_parties.ATTRIBUTE7%TYPE,
    attribute8                  hz_parties.ATTRIBUTE8%TYPE,
    attribute9                  hz_parties.ATTRIBUTE9%TYPE,
    attribute10                 hz_parties.ATTRIBUTE10%TYPE,
    attribute11                 hz_parties.ATTRIBUTE11%TYPE,
    attribute12                 hz_parties.ATTRIBUTE12%TYPE,
    attribute13                 hz_parties.ATTRIBUTE13%TYPE,
    attribute14                 hz_parties.ATTRIBUTE14%TYPE,
    attribute15                 hz_parties.ATTRIBUTE15%TYPE,
    attribute16                 hz_parties.ATTRIBUTE16%TYPE,
    attribute17                 hz_parties.ATTRIBUTE17%TYPE,
    attribute18                 hz_parties.ATTRIBUTE18%TYPE,
    attribute19                 hz_parties.ATTRIBUTE19%TYPE,
    attribute20                 hz_parties.ATTRIBUTE20%TYPE,
    attribute21                 hz_parties.ATTRIBUTE21%TYPE,
    attribute22                 hz_parties.ATTRIBUTE22%TYPE,
    attribute23                 hz_parties.ATTRIBUTE23%TYPE,
    attribute24                 hz_parties.ATTRIBUTE24%TYPE,
    object_version_number       hz_parties.OBJECT_VERSION_NUMBER%TYPE
    );

 -- External Bank Branch Record
  TYPE ExtBankBranch_rec_type IS RECORD(
        branch_party_id            hz_parties.PARTY_ID%TYPE,
        bank_party_id               hz_parties.PARTY_ID%TYPE,
        branch_name                hz_parties.PARTY_NAME%TYPE,
        branch_number              hz_organization_profiles.BANK_OR_BRANCH_NUMBER%TYPE,
        branch_type                   hz_code_assignments.CLASS_CODE%TYPE,
        alternate_branch_name      hz_parties.ORGANIZATION_NAME_PHONETIC%TYPE,
        description                hz_parties.MISSION_STATEMENT%TYPE,
        bic                        hz_contact_points.EFT_SWIFT_CODE%TYPE,
        eft_number                   hz_contact_points.EFT_USER_NUMBER%TYPE,
        rfc_identifier               hz_code_assignments.CLASS_CODE%TYPE,
        attribute_category         hz_parties.ATTRIBUTE_CATEGORY%TYPE,
        attribute1                 hz_parties.ATTRIBUTE1%TYPE,
          attribute2                 hz_parties.ATTRIBUTE2%TYPE,
        attribute3                 hz_parties.ATTRIBUTE3%TYPE,
        attribute4                 hz_parties.ATTRIBUTE4%TYPE,
        attribute5                 hz_parties.ATTRIBUTE5%TYPE,
        attribute6                 hz_parties.ATTRIBUTE6%TYPE,
        attribute7                 hz_parties.ATTRIBUTE7%TYPE,
        attribute8                 hz_parties.ATTRIBUTE8%TYPE,
        attribute9                 hz_parties.ATTRIBUTE9%TYPE,
        attribute10                hz_parties.ATTRIBUTE10%TYPE,
        attribute11                hz_parties.ATTRIBUTE11%TYPE,
        attribute12                hz_parties.ATTRIBUTE12%TYPE,
        attribute13                hz_parties.ATTRIBUTE13%TYPE,
        attribute14                hz_parties.ATTRIBUTE14%TYPE,
        attribute15                hz_parties.ATTRIBUTE15%TYPE,
        attribute16                hz_parties.ATTRIBUTE16%TYPE,
        attribute17                hz_parties.ATTRIBUTE17%TYPE,
        attribute18                hz_parties.ATTRIBUTE18%TYPE,
        attribute19                hz_parties.ATTRIBUTE19%TYPE,
        attribute20                hz_parties.ATTRIBUTE20%TYPE,
        attribute21                hz_parties.ATTRIBUTE21%TYPE,
        attribute22                hz_parties.ATTRIBUTE22%TYPE,
        attribute23                hz_parties.ATTRIBUTE23%TYPE,
        attribute24                hz_parties.ATTRIBUTE24%TYPE,
        bch_object_version_number  hz_parties.OBJECT_VERSION_NUMBER%TYPE,
        typ_object_version_number  hz_code_assignments.OBJECT_VERSION_NUMBER%TYPE,
        rfc_object_version_number  hz_code_assignments.OBJECT_VERSION_NUMBER%TYPE,
        eft_object_version_number  hz_code_assignments.OBJECT_VERSION_NUMBER%TYPE
  );

 -- External Bank Account Record
  TYPE ExtBankAcct_rec_type IS RECORD (
    bank_account_id                 iby_ext_bank_accounts.EXT_BANK_ACCOUNT_ID%TYPE,
    country_code                 iby_ext_bank_accounts.COUNTRY_CODE%TYPE,
    branch_id                     iby_ext_bank_accounts.BRANCH_ID%TYPE,
    bank_id                         iby_ext_bank_accounts.BANK_ID%TYPE,
    acct_owner_party_id          iby_account_owners.ACCOUNT_OWNER_PARTY_ID%TYPE,
    bank_account_name             iby_ext_bank_accounts.BANK_ACCOUNT_NAME%TYPE,
    bank_account_num             iby_ext_bank_accounts.BANK_ACCOUNT_NUM%TYPE,
    currency                     iby_ext_bank_accounts.CURRENCY_CODE%TYPE,
    iban                         iby_ext_bank_accounts.IBAN%TYPE,
    check_digits                 iby_ext_bank_accounts.CHECK_DIGITS%TYPE,
    multi_currency_allowed_flag  varchar2(1),
    alternate_acct_name             iby_ext_bank_accounts.BANK_ACCOUNT_NAME_ALT%TYPE,
    short_acct_name              iby_ext_bank_accounts.SHORT_ACCT_NAME%TYPE,
    acct_type                     iby_ext_bank_accounts.BANK_ACCOUNT_TYPE%TYPE,
    acct_suffix                     iby_ext_bank_accounts.ACCOUNT_SUFFIX%TYPE,
    description                     iby_ext_bank_accounts.DESCRIPTION%TYPE,
    agency_location_code         iby_ext_bank_accounts.AGENCY_LOCATION_CODE%TYPE,
    attribute_category             iby_ext_bank_accounts.ATTRIBUTE_CATEGORY%TYPE,
    attribute1                     iby_ext_bank_accounts.ATTRIBUTE1%TYPE,
    attribute2                     iby_ext_bank_accounts.ATTRIBUTE2%TYPE,
    attribute3                     iby_ext_bank_accounts.ATTRIBUTE3%TYPE,
    attribute4                   iby_ext_bank_accounts.ATTRIBUTE4%TYPE,
    attribute5                   iby_ext_bank_accounts.ATTRIBUTE5%TYPE,
    attribute6                   iby_ext_bank_accounts.ATTRIBUTE6%TYPE,
    attribute7                   iby_ext_bank_accounts.ATTRIBUTE7%TYPE,
    attribute8                   iby_ext_bank_accounts.ATTRIBUTE8%TYPE,
    attribute9                   iby_ext_bank_accounts.ATTRIBUTE9%TYPE,
    attribute10                  iby_ext_bank_accounts.ATTRIBUTE10%TYPE,
    attribute11                  iby_ext_bank_accounts.ATTRIBUTE11%TYPE,
    attribute12                  iby_ext_bank_accounts.ATTRIBUTE12%TYPE,
    attribute13                  iby_ext_bank_accounts.ATTRIBUTE13%TYPE,
    attribute14                  iby_ext_bank_accounts.ATTRIBUTE14%TYPE,
    attribute15                  iby_ext_bank_accounts.ATTRIBUTE15%TYPE,
    object_version_number        iby_ext_bank_accounts.OBJECT_VERSION_NUMBER%TYPE,
    contact_name          iby_ext_bank_accounts.CONTACT_NAME%TYPE,
    contact_phone                 iby_ext_bank_accounts.CONTACT_PHONE%TYPE,
    contact_email                 iby_ext_bank_accounts.CONTACT_EMAIL%TYPE,
    contact_fax              iby_ext_bank_accounts.CONTACT_FAX%TYPE
  );

Comments

Post a Comment

Popular posts from this blog

How to use lexical parameters in Data template (XML document) R12

Lexical parameters can be added in data definition using &variable name This is flexible variable string where you can execute runtime conditions based on user-inputs Example 1. Append &gc_journal_filter at the end of sql statement 2. Define beforereport function in package signature FUNCTION beforereport RETURN BOOLEAN; 3. Define all the report parameters as a package variable in package signature including lexical parameters 4. Implement beforereport function by building your runtime conditions to be added to SQL statements  FUNCTION beforereport RETURN BOOLEAN IS  BEGIN          IF p_journal_source IS NULL THEN       gc_journal_filter := ' AND jh.je_source IN (''AutoCopy'',''Manual'')';     END IF;          IF p_journal_category IS NULL THEN       gc_journal_filter := 'AND jh.je_category IN (''Other'') ';     END IF;    RETURN TRUE;  END;
Read more

Query to get FSG report details with row/column set

Below query is to fetch FSG report titles, names, and description along with will give a view to list of row sets and column sets associated with the report. -- FSG REPORTS -- SELECT fst.id_flex_structure_name , R.NAME , R.report_title , R.DESCRIPTION , R.column_set "COLUMN SET" , rw2.structure_id , rw2.DESCRIPTION "COL DESC" , R.row_set "ROW SET" , rw.DESCRIPTION "ROW DESC" , R.report_display_set "DISPLAY SET" , R.content_set "CONTENT SET" , R.row_order "ROW ORDER" , R.rounding_option "RND" , u.user_name , u.DESCRIPTION , R.creation_date FROM rg_reports_v R    , fnd_id_flex_structures_v fst    , fnd_user u    , rg_report_axis_sets_v rw    , rg_report_axis_sets_v rw2 WHERE r.structure_id = fst.id_flex_num AND R.row_set_id     = rw.axis_set_id AND r.column_set_id  = rw2.axis_set_id AND SUBSTR(fst.id_flex_structure_name,1,2) IN ('DC') AND R.created_by     = u.user_id OR
Read more

Sub Inventory Transfer API.

Image
Sub Inventory transfer is used to move materials from one sub-inventory to another within same organization. This is especially used to 1. Adjust on-hand quantities between sub-inventories 2. Transfer to another sub-inventories for specific business need like additional sales 3. Damages/expiry products can be moved to non-asset sub-inventories to discard it from inventory valuation report and so on., Key elements 1. From sub inventory 2. To sub inventory 3. Organization 4. Inventory item 5. Transact quantity 6. Transact unit of measure 7. Transaction type 8. Transaction action 9. Transact Reference 10. Transact date 11. Lot Number Open Interfaces mtl_transactions_interface - is used to populate sub inventory transfer details mtl_transaction_lots_interface - is used to populate lot details for particular sub inventory transfer Base Tables mtl_material_transactions - is used to get all type of material transactions like sub-inventory transfer, inter-org tra
Read more