[Home] [Help]
390: l_object_version_number NUMBER;
391:
392: CURSOR c_bank_ovn IS
393: SELECT object_version_number
394: FROM hz_parties
395: WHERE party_id = p_bank_id;
396:
397: BEGIN
398:
755: CURSOR c_rfc_identifier_ovn(p_branch_id NUMBER) IS
756: SELECT object_version_number
757: FROM HZ_CODE_ASSIGNMENTS
758: WHERE class_category = 'RFC_IDENTIFIER'
759: AND owner_table_name = 'HZ_PARTIES'
760: AND owner_table_id = p_branch_id;
761:
762: -- Picks up object version number of EFT Record
763: CURSOR c_eft_record_ovn(p_branch_id NUMBER) IS
763: CURSOR c_eft_record_ovn(p_branch_id NUMBER) IS
764: SELECT object_version_number
765: FROM HZ_CODE_ASSIGNMENTS
766: WHERE class_category = 'EFT'
767: AND owner_table_name = 'HZ_PARTIES'
768: AND owner_table_id = p_branch_id;
769:
770: BEGIN
771: print_debuginfo('ENTER ' || l_module_name);
953: l_object_version_number NUMBER;
954:
955: CURSOR c_branch_party_ovn IS
956: SELECT object_version_number
957: FROM hz_parties
958: WHERE party_id = p_branch_id;
959:
960: BEGIN
961:
3866: x_return_status IN OUT NOCOPY VARCHAR2
3867: ) RETURN VARCHAR2 IS
3868: CURSOR c_country IS
3869: SELECT hp.country
3870: FROM hz_parties hp
3871: WHERE hp.party_id = p_bank_id
3872: AND hp.status = 'A';
3873: l_country VARCHAR2(60);
3874: BEGIN
3924: SELECT hz_bank.party_id AS bank_id,
3925: hz_bank.country,
3926: hz_bank.party_name,
3927: hz_bankorg.bank_or_branch_number
3928: FROM hz_parties hz_bank,
3929: hz_organization_profiles hz_bankorg,
3930: hz_parties hz_branch,
3931: hz_relationships hz_rel,
3932: hz_code_assignments hz_bankCA,
3926: hz_bank.party_name,
3927: hz_bankorg.bank_or_branch_number
3928: FROM hz_parties hz_bank,
3929: hz_organization_profiles hz_bankorg,
3930: hz_parties hz_branch,
3931: hz_relationships hz_rel,
3932: hz_code_assignments hz_bankCA,
3933: hz_code_assignments hz_branchCA
3934: WHERE hz_branchCA.owner_table_name = 'HZ_PARTIES'
3930: hz_parties hz_branch,
3931: hz_relationships hz_rel,
3932: hz_code_assignments hz_bankCA,
3933: hz_code_assignments hz_branchCA
3934: WHERE hz_branchCA.owner_table_name = 'HZ_PARTIES'
3935: AND hz_branchCA.owner_table_id = hz_branch.party_id
3936: AND hz_branchCA.class_category = 'BANK_INSTITUTION_TYPE'
3937: AND hz_branchCA.class_code = 'BANK_BRANCH'
3938: AND NVL(hz_branchCA.STATUS, 'A') = 'A'
3937: AND hz_branchCA.class_code = 'BANK_BRANCH'
3938: AND NVL(hz_branchCA.STATUS, 'A') = 'A'
3939: AND hz_bankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
3940: AND hz_bankCA.CLASS_CODE = 'BANK'
3941: AND hz_bankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
3942: AND hz_bankCA.OWNER_TABLE_ID = hz_bank.PARTY_ID
3943: AND NVL(hz_bankCA.STATUS, 'A') = 'A'
3944: AND hz_rel.OBJECT_ID = hz_bank.PARTY_ID
3945: And hz_branch.PARTY_ID = hz_rel.SUBJECT_ID
3945: And hz_branch.PARTY_ID = hz_rel.SUBJECT_ID
3946: And hz_rel.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
3947: And hz_rel.RELATIONSHIP_CODE = 'BRANCH_OF'
3948: And hz_rel.STATUS = 'A'
3949: And hz_rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3950: And hz_rel.SUBJECT_TYPE = 'ORGANIZATION'
3951: And hz_rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3952: And hz_rel.OBJECT_TYPE = 'ORGANIZATION'
3953: AND hz_bank.party_id = hz_bankorg.party_id
3947: And hz_rel.RELATIONSHIP_CODE = 'BRANCH_OF'
3948: And hz_rel.STATUS = 'A'
3949: And hz_rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3950: And hz_rel.SUBJECT_TYPE = 'ORGANIZATION'
3951: And hz_rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3952: And hz_rel.OBJECT_TYPE = 'ORGANIZATION'
3953: AND hz_bank.party_id = hz_bankorg.party_id
3954: AND SYSDATE between TRUNC(hz_bankorg.effective_start_date)
3955: and NVL(TRUNC(hz_bankorg.effective_end_date), SYSDATE+1)