Wednesday, December 28, 2011

Creating Bank Branch in Accounts Payables

Responsibility: Payables Manager
Navigation: Setup -> Payments -> Banks and Bank Branches

  1. Click on Tab "Bank Branches" and hit "Create" button. You will navigate to "Create Bank Branch" Page. In this page enter Country and Bank name and hit "Continue" button .

  2. You will navigate to "Create Bank Branch: Bank Branch Information" Page. Only branch name and type are mandatory

  3. Hit "Save and Next" button

  4. At this time, following changes are committed to database and you move to "Create Bank Branch: Branch Address" Page Two records will be inserted into HZ_PARTIES table: One record for bank branch name, other record for relationship between Bank and Bank Branch. Record for Bank brach will have party_name and Branch name and PARTY_TYPE as ORGANIZATION. Record for bank and branch relationship will have PARTY_NAME as Bank name + '-' + Branch_name + '-' + PARTY_NUMBER for the record. Get PARTY_ID for both records. Let’s say party_id for BRANCH is branch_party_id and bank branch relationship party is bank_branch_relation_party_id Two recors are inserted into HZ_RELATIONSHIPS table. Details of these records will be

    SubjectIdObjectIdRelationshipCodeDirectionalFlagRelationshipTypeDirectionCode
    Bank_Party_idBranch_Party_idHAS_BRANCHFBANK_AND_BRANCHP
    Branch_Party_idBank_Party_idBRANCH_OFBBANK_AND_BRANCHC

    A record is inserted into HZ_ORGANIZATION_PROFILES. branch_party_id, branch name, routing number, Alternate name go to party_id, organization_name, bank_or_branch_number and organization_name_phonetic columns respectively.

  5. On "Create Bank Branch: Branch Address" Page, Hit "Create" button to create branch Address. You will navigate to "Branch Address Detail" Page.

  6. Enter Address and hit "Apply button

  7. You will go back to "Create Bank Branch: Branch Address" Page and a record in created in HZ_PARTY_SITES and HZ_LOCATIONS tables.

    Following query can give address details
    SELECT *
      FROM hz_party_sites psite
         , hz_locations   loc
     WHERE psite.location_id = loc.location_id
       AND psite.party_id    = :branch_party_id
    
    Multiple addresses can be entered on this page

  8. Hit "Save and Next" button. You will navigate to "Create Bank Branch: Branch Contact" page

  9. Hit "Create contact Button on this page "Create Contact Page". In this page you can enter following
        Contact Information
        Contact Email
        Contact Phones
        Contact Addresses

  10. To enter contact phone, hit "Add Another Row" button on "Phone" region and enter phone details

  11. To enter Contact address, hit "Create" button on "Addresses" region. You will navigate to "address Details" page.

  12. Enter Address Details and then hit "Apply" button. I think there is bug in this page. Hitting APPLY gives error "Attribute PartyId in HzPuiPartySiteEOEx is required". So we need to Commit Contact information first and then create Address for the contact. By this time, following changes are committed to database Two records are created in HZ_PARTIES. one for Contact person and other for relationship between Branch Contact person and Bank (not the bank branch). These records will have party type as PERSON and PARTY_RELATIONSHIP. Take note of party_id for PERSON and PARTY_RELATIONSHIP records and call them branch_contact_party_id and branch_contact_rel_party_id. This branch_contact_rel_party_id value is used for creating Contact point and address records. The value of branch_contact_party_id is used for creating relationship between BANK and Contact PERSON.

    Following query can retrieve Contact points

    SELECT * 
      FROM HZ_CONTACT_POINTS
     WHERE owner_table_id   = :branch_contact_rel_party_id
       AND owner_table_name = 'HZ_PARTIES' 
    

    Following query can give details of relationship records. I do not know why Oracle created relationship between branch contact and bank instead of bank branch. This may be a bug in Oracle.
    SELECT *
      FROM hz_relationships
     WHERE subject_id         = :branch_contact_party_id
       AND subject_table_name = 'HZ_PARTIES'
       AND object_id          = :bank_party_id
       AND object_table_name  = 'HZ_PARTIES';
    
    For this record, DIRECTIONAL_FLAG, DIRECTION_CODE and RELATIONSHIP_CODE should be 'F' (forward), 'P' (for Parent) and 'CONTACT_OF' Oracle will create backward relationship between bank_party_id and branch_contact_party_id. For backward relationship record, DIRECTIONAL_FLAG, DIRECTION_CODE and RELATIONSHIP_CODE should be 'B' (backward), 'C' (child) and 'CONTACT'. PARTY_ID value in both of these records will be same as relation_party_id value. RELATIONSHIP_TYPE for both records is CONTACT.

    For each of these two records, a record is inserted into CE_CONTACT_ASSIGNMENTS table. Branch_party_id, bank_party_id and relationship_id go branch_party_id, bank_party_id and relationship_columns of this table. Since there seems to be a bug in the way branch contacts are inserted into HZ schema, this table is importance to get details of branch contact. In order to get bracnh contact, hz_parties record for branch with this table on branch_party_id column and then relationship_id column with hz_relationships table and select only that record that directional_flag as 'F' and use SUBJECT_ID column to get branch contact details. For this record OBJECT_ID in hz_relationship is pointing to bank_party_id not the branch party_id.

  13. Navigate to Navigation: Setup -> Payments -> Banks and Bank Branches

  14. Query Branch created in previous steps and hit "Update Branch icon

  15. Hit "Save and Next twice to reach "Update Bank Branch: Branch Contact" Page

  16. Click on Update icon for Contact created in previous steps

  17. Hit "Create" button on Address region

  18. Enter Address and hit APPLY button

  19. You will go back to "Create Contact page". Hit APPLY button again. By this time Contact address is inserted into database.

    Following query can retrieve address details
    SELECT *
      FROM hz_party_sites psite
         , hz_locations   loc
     WHERE psite.location_id = loc.location_id
       AND psite.party_id    = :branch_contact_rel_party_id
    

  20. You will navigate to "Update Bank Branch: Branch Contact" page. Now Hit Finish button. Now bank branch is created.

No comments:

Post a Comment