APPS.CE_VALIDATE_BANKINFO SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9

   SELECT OrgProfileBank.BANK_OR_BRANCH_NUMBER
    FROM  HZ_PARTIES   PartyBank,
          HZ_ORGANIZATION_PROFILES   OrgProfileBank,
          HZ_CODE_ASSIGNMENTS   CABank
   WHERE
          PartyBank.party_id = Xi_BANK_ID
     AND  PartyBank.PARTY_TYPE = 'ORGANIZATION'
     AND  PartyBank.status = 'A'
     AND  PartyBank.PARTY_ID = OrgProfileBank.PARTY_ID
     AND  SYSDATE BETWEEN TRUNC(OrgProfileBank.EFFECTIVE_START_DATE)
          AND NVL(TRUNC(OrgProfileBank.EFFECTIVE_END_DATE),SYSDATE)
     AND  CABank.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
     AND  CABank.CLASS_CODE = 'BANK'
     AND  CABank.OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND  CABank.OWNER_TABLE_ID = PartyBank.PARTY_ID
     AND  NVL(CABank.status, 'A') = 'A';

Line: 36

  SELECT COUNT(*)
  INTO l_count_int
  FROM ce_bank_accounts
  WHERE NVL(end_date,Xi_BRANCH_END_DATE+1) > Xi_BRANCH_END_DATE
  AND bank_branch_id                       = Xi_BRANCH_ID;

Line: 42

  SELECT COUNT(*)
  INTO l_count_ext
  FROM iby_ext_bank_accounts
  WHERE NVL(end_date,Xi_BRANCH_END_DATE+1) > Xi_BRANCH_END_DATE
  AND branch_id                      = Xi_BRANCH_ID;

Line: 188

        SELECT count(*) INTO bank_count
        FROM   HZ_PARTIES   PartyBank,
               HZ_ORGANIZATION_PROFILES   OrgProfileBank,
               HZ_CODE_ASSIGNMENTS   CABank
        WHERE
               PartyBank.party_id = Xi_BANK_ID
          AND  PartyBank.PARTY_TYPE = 'ORGANIZATION'
          AND  PartyBank.status = 'A'
          AND  PartyBank.PARTY_ID = OrgProfileBank.PARTY_ID
          AND  sysdate BETWEEN trunc(OrgProfileBank.EFFECTIVE_START_DATE)
               AND nvl(trunc(OrgProfileBank.EFFECTIVE_END_DATE),sysdate)
          AND  CABank.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
          AND  CABank.CLASS_CODE = 'BANK'
          AND  CABank.OWNER_TABLE_NAME = 'HZ_PARTIES'
          AND  CABank.OWNER_TABLE_ID = PartyBank.PARTY_ID
          AND  nvl(CABank.status, 'A') = 'A';

Line: 322

    CURSOR CHECK_UNIQUE_TAXID_BK IS       -- Banks 8614674 changed the select
        SELECT TAX_PAYER_ID
        FROM   CE_BANKS_V
        WHERE  tax_payer_id = p_taxpayer_id
        AND home_country     = p_country_code
	 AND bank_party_id <> nvl(p_bank_id,-1) ;

Line: 389

    SELECT AP.VENDOR_NAME, AP.NUM_1099
    FROM   PO_VENDORS AP
    WHERE  (AP.VENDOR_NAME=p_entity_name OR  AP.NUM_1099= p_taxpayer_id)
      AND  substrb(nvl(AP.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;

Line: 397

    SELECT AR.PARTY_NAME, AR.JGZZ_FISCAL_CODE
    FROM   HZ_PARTIES  AR
    WHERE  (AR.PARTY_NAME=p_entity_name OR  AR.JGZZ_FISCAL_CODE= p_taxpayer_id)
      AND  substrb(nvl(AR.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;

Line: 404

    SELECT HR.GLOBAL_ATTRIBUTE8, HR.GLOBAL_ATTRIBUTE11
    FROM   HR_LOCATIONS HR
    WHERE  (HR.GLOBAL_ATTRIBUTE8= p_entity_name
       OR  HR.GLOBAL_ATTRIBUTE11= p_taxpayer_id)
      AND  substrb(nvl(HR.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code
      AND  HR.LOCATION_USE = 'HR';

Line: 591

    SELECT COUNT(*) INTO temp_name
    FROM   HZ_PARTIES              BankParty,
           HZ_PARTIES              BranchParty,
           HZ_ORGANIZATION_PROFILES        BankOrgProfile,
           HZ_ORGANIZATION_PROFILES        BranchOrgProfile,
           HZ_RELATIONSHIPS            BRRel,
           HZ_CODE_ASSIGNMENTS         BankCA,
           HZ_CODE_ASSIGNMENTS         BranchCA
    WHERE  BankParty.PARTY_TYPE = 'ORGANIZATION'
    AND    BankParty.status = 'A'
    AND    BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
    AND    SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
           and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
    AND    BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
    AND    BankCA.CLASS_CODE = 'BANK'
    AND    BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
    AND    BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
    AND    NVL(BankCA.STATUS, 'A') = 'A'
    AND    BranchParty.PARTY_TYPE(+) = 'ORGANIZATION'
    AND    BranchParty.status(+) = 'A'
    AND    BranchOrgProfile.PARTY_ID(+) = BranchParty.PARTY_ID
    AND    SYSDATE BETWEEN TRUNC(BranchOrgProfile.effective_start_date(+))
           AND NVL(TRUNC(BranchOrgProfile.effective_end_date(+)), SYSDATE+1)
    AND    BranchCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE'
    AND    BranchCA.CLASS_CODE(+) = 'BANK_BRANCH'
    AND    BranchCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
    AND    BranchCA.OWNER_TABLE_ID(+) = BranchParty.PARTY_ID
    AND    NVL(BranchCA.STATUS(+), 'A') = 'A'
    AND    BRRel.OBJECT_ID(+) = BankParty.PARTY_ID
    AND    BranchParty.PARTY_ID(+) = BRRel.SUBJECT_ID
    AND    BRRel.RELATIONSHIP_TYPE(+) = 'BANK_AND_BRANCH'
    AND    BRRel.RELATIONSHIP_CODE(+) = 'BRANCH_OF'
    AND    BRRel.STATUS(+) = 'A'
    AND    BRRel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND    BRRel.SUBJECT_TYPE(+) =  'ORGANIZATION'
    AND    BRRel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND    BRRel.OBJECT_TYPE(+) = 'ORGANIZATION'
    AND    BankParty.PARTY_ID     =  Xi_BANK_ID
    AND    BranchParty.party_name =  Xi_BRANCH_NAME
    /* Bug 13641238: country is stored in OrgProfile
	AND    BranchParty.country    =  Xi_COUNTRY_NAME */
	AND    BranchOrgProfile.home_country =  Xi_COUNTRY_NAME
    AND    BranchParty.PARTY_ID  <>  nvl(Xi_BRANCH_ID, -1);

Line: 669

    SELECT  COUNT(*) INTO temp_number
    FROM    HZ_PARTIES              BankParty,
            HZ_PARTIES              BranchParty,
            HZ_ORGANIZATION_PROFILES        BankOrgProfile,
            HZ_ORGANIZATION_PROFILES        BranchOrgProfile,
            HZ_RELATIONSHIPS            BRRel,
            HZ_CODE_ASSIGNMENTS         BankCA,
            HZ_CODE_ASSIGNMENTS         BranchCA
    WHERE   BankParty.PARTY_TYPE = 'ORGANIZATION'
    AND     BankParty.status = 'A'
    AND     BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
    AND     SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
            and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
    AND     BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
    AND     BankCA.CLASS_CODE = 'BANK'
    AND     BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
    AND     BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
    AND     NVL(BankCA.STATUS, 'A') = 'A'
    AND     BranchParty.PARTY_TYPE(+) = 'ORGANIZATION'
    AND     BranchParty.status(+) = 'A'
    AND     BranchOrgProfile.PARTY_ID(+) = BranchParty.PARTY_ID
    AND     SYSDATE between TRUNC(BranchOrgProfile.effective_start_date(+))
            and NVL(TRUNC(BranchOrgProfile.effective_end_date(+)), SYSDATE+1)
    AND     BranchCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE'
    AND     BranchCA.CLASS_CODE(+) = 'BANK_BRANCH'
    AND     BranchCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BranchCA.OWNER_TABLE_ID(+) = BranchParty.PARTY_ID
    AND     NVL(BranchCA.STATUS(+), 'A') = 'A'
    AND     BRRel.OBJECT_ID(+) = BankParty.PARTY_ID
    AND     BranchParty.PARTY_ID(+) = BRRel.SUBJECT_ID
    AND     BRRel.RELATIONSHIP_TYPE(+) = 'BANK_AND_BRANCH'
    AND     BRRel.RELATIONSHIP_CODE(+) = 'BRANCH_OF'
    AND     BRRel.STATUS(+) = 'A'
    AND     BRRel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BRRel.SUBJECT_TYPE(+) =  'ORGANIZATION'
    AND     BRRel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BRRel.OBJECT_TYPE(+) = 'ORGANIZATION'
    AND     BankParty.PARTY_ID     =  Xi_BANK_ID
    AND     BranchOrgProfile.BANK_OR_BRANCH_NUMBER  = Xi_BRANCH_NUMBER
    /* Bug 13641238: country is stored in OrgProfile
	AND    BranchParty.country    =  Xi_COUNTRY_NAME */
	AND    BranchOrgProfile.home_country =  Xi_COUNTRY_NAME
    AND     BranchParty.PARTY_ID  <>  nvl(Xi_BRANCH_ID, -1);

Line: 745

    SELECT  COUNT(*) INTO temp_name_alt
    FROM    HZ_PARTIES              BankParty,
            HZ_PARTIES              BranchParty,
            HZ_ORGANIZATION_PROFILES        BankOrgProfile,
            HZ_ORGANIZATION_PROFILES        BranchOrgProfile,
            HZ_RELATIONSHIPS            BRRel,
            HZ_CODE_ASSIGNMENTS         BankCA,
            HZ_CODE_ASSIGNMENTS         BranchCA
    WHERE   BankParty.PARTY_TYPE = 'ORGANIZATION'
    AND     BankParty.status = 'A'
    AND     BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
    AND     SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
            and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
    AND     BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
    AND     BankCA.CLASS_CODE = 'BANK'
    AND     BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
    AND     BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
    AND     NVL(BankCA.STATUS, 'A') = 'A'
    AND     BranchParty.PARTY_TYPE(+) = 'ORGANIZATION'
    AND     BranchParty.status(+) = 'A'
    AND     BranchOrgProfile.PARTY_ID(+) = BranchParty.PARTY_ID
    AND     SYSDATE between TRUNC(BranchOrgProfile.effective_start_date(+))
            and NVL(TRUNC(BranchOrgProfile.effective_end_date(+)), SYSDATE+1)
    AND     BranchCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE'
    AND     BranchCA.CLASS_CODE(+) = 'BANK_BRANCH'
    AND     BranchCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BranchCA.OWNER_TABLE_ID(+) = BranchParty.PARTY_ID
    AND     NVL(BranchCA.STATUS(+), 'A') = 'A'
    AND     BRRel.OBJECT_ID(+) = BankParty.PARTY_ID
    AND     BranchParty.PARTY_ID(+) = BRRel.SUBJECT_ID
    AND     BRRel.RELATIONSHIP_TYPE(+) = 'BANK_AND_BRANCH'
    AND     BRRel.RELATIONSHIP_CODE(+) = 'BRANCH_OF'
    AND     BRRel.STATUS(+) = 'A'
    AND     BRRel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BRRel.SUBJECT_TYPE(+) =  'ORGANIZATION'
    AND     BRRel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BRRel.OBJECT_TYPE(+) = 'ORGANIZATION'
    AND     BankParty.PARTY_ID     =  Xi_BANK_ID
    AND     BranchParty.ORGANIZATION_NAME_PHONETIC =  Xi_BRANCH_NAME_ALT
    AND     BranchOrgProfile.home_country    =  Xi_COUNTRY_NAME -- bug 8552410 Changed BranchParty to BranchOrgProfile
    AND     BranchParty.PARTY_ID  <>  nvl(Xi_BRANCH_ID, -1);

Line: 824

    SELECT  COUNT(*) INTO temp_name
    FROM    ce_bank_accounts ba
    WHERE   ba.bank_account_name  = Xi_ACCOUNT_NAME
    AND     ba.bank_branch_id      = Xi_BRANCH_ID
    AND     ba.bank_account_id    <> nvl(Xi_ACCOUNT_ID,-1);

Line: 1248

            FND_MSG_PUB.delete_msg(l_count_after_custom);

Line: 1357

    SELECT              decode(
                   upper(substr(X_ACCOUNT_NUMBER, 1, 1)),
                   'P', upper(X_ACCOUNT_NUMBER),
                   'G', 'P' || substr(X_ACCOUNT_NUMBER, 2),
               'I', 'P' || substr(X_ACCOUNT_NUMBER, 2),
                   lpad(X_ACCOUNT_NUMBER, 10, '0')
                   )
    INTO account_value
    FROM dual; -- Bug 13420335

Line: 1793

            FND_MSG_PUB.delete_msg(l_count_after_custom);

Line: 2024

            FND_MSG_PUB.delete_msg(l_count_after_custom);

Line: 2143

            SELECT meaning
            INTO length_val
            FROM fnd_lookup_values_vl
            WHERE lookup_type = 'NUMBERS'
            AND lookup_code = p_Token;

Line: 6359

    SELECT COUNT(*) INTO temp_number
    FROM   ce_bank_accounts ba
    WHERE  ba.bank_account_num  =  Xi_ACCOUNT_NUMBER
    AND    ba.bank_account_name =  Xi_ACCOUNT_NAME
    AND    ba.bank_branch_id    =  Xi_BRANCH_ID
    AND    ba.currency_code     =  Xi_CURRENCY_CODE
    AND    ba.bank_account_id   <> nvl(Xi_ACCOUNT_ID,-1);

Line: 6397

    SELECT count(*) INTO temp_number
    FROM   ce_bank_accounts ba
    WHERE  ba.bank_account_num = Xi_ACCOUNT_NUMBER
    AND    ba.bank_account_name = Xi_ACCOUNT_NAME
    AND    ba.bank_account_type = Xi_ACCOUNT_TYPE
    AND    ba.currency_code     = Xi_CURRENCY_CODE
    AND    ba.bank_branch_id    = Xi_BRANCH_ID
    AND    ba.bank_account_id  <> nvl(Xi_ACCOUNT_ID,-1);

Line: 6438

    SELECT COUNT(*) INTO temp_number
    FROM   ce_bank_accounts ba
    WHERE  ba.bank_account_num = Xi_ACCOUNT_NUMBER
    AND    ba.bank_account_name = Xi_ACCOUNT_NAME
    AND    ba.account_suffix    = Xi_ACCOUNT_SUFFIX
    AND    ba.currency_code     = Xi_CURRENCY_CODE
    AND    ba.bank_branch_id    = Xi_BRANCH_ID
    AND    ba.bank_account_id  <> nvl(Xi_ACCOUNT_ID,-1);

Line: 6484

    SELECT  COUNT(*)
    INTO    l_temp_number
    FROM    HZ_PARTIES              BankParty,
            HZ_PARTIES              BranchParty,
            HZ_ORGANIZATION_PROFILES        BankOrgProfile,
            HZ_ORGANIZATION_PROFILES        BranchOrgProfile,
            HZ_RELATIONSHIPS            BRRel,
            HZ_CODE_ASSIGNMENTS         BankCA,
            HZ_CODE_ASSIGNMENTS         BranchCA
    WHERE   BankParty.PARTY_TYPE = 'ORGANIZATION'
    AND     BankParty.status = 'A'
    AND     BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
    AND     SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
            and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
    AND     BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
    AND     BankCA.CLASS_CODE = 'BANK'
    AND     BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
    AND     BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
    AND     NVL(BankCA.STATUS, 'A') = 'A'
    AND     BranchParty.PARTY_TYPE(+) = 'ORGANIZATION'
    AND     BranchParty.status(+) = 'A'
    AND     BranchOrgProfile.PARTY_ID(+) = BranchParty.PARTY_ID
    AND     SYSDATE between TRUNC(BranchOrgProfile.effective_start_date(+))
            and NVL(TRUNC(BranchOrgProfile.effective_end_date(+)), SYSDATE+1)
    AND     BranchCA.CLASS_CATEGORY(+) = 'BANK_INSTITUTION_TYPE'
    AND     BranchCA.CLASS_CODE(+) = 'BANK_BRANCH'
    AND     BranchCA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BranchCA.OWNER_TABLE_ID(+) = BranchParty.PARTY_ID
    AND     NVL(BranchCA.STATUS(+), 'A') = 'A'
    AND     BRRel.OBJECT_ID(+) = BankParty.PARTY_ID
    AND     BranchParty.PARTY_ID(+) = BRRel.SUBJECT_ID
    AND     BRRel.RELATIONSHIP_TYPE(+) = 'BANK_AND_BRANCH'
    AND     BRRel.RELATIONSHIP_CODE(+) = 'BRANCH_OF'
    AND     BRRel.STATUS(+) = 'A'
    AND     BRRel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BRRel.SUBJECT_TYPE(+) =  'ORGANIZATION'
    AND     BRRel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
    AND     BRRel.OBJECT_TYPE(+) = 'ORGANIZATION'
    AND     BankParty.PARTY_ID =  Xi_BANK_ID
    AND     BranchParty.PARTY_NAME  = Xi_BRANCH_NAME
    AND     nvl(BranchOrgProfile.BANK_OR_BRANCH_NUMBER,'X') = nvl(Xi_BRANCH_NUMBER,'X')
	AND     BranchOrgProfile.HOME_COUNTRY = Xi_COUNTRY_NAME
    AND     BranchParty.PARTY_ID  <>  nvl(Xi_BRANCH_ID, -1);

Line: 6606

    SELECT COUNT(*)
    INTO   temp_name_alt
    FROM   HZ_PARTIES   HzPartyBankEO,
           HZ_ORGANIZATION_PROFILES   HzOrgProfileBankEO,
           HZ_CODE_ASSIGNMENTS   HzCodeAssignmentBankEO
    WHERE  HzPartyBankEO.ORGANIZATION_NAME_PHONETIC =  Xi_BANK_NAME_ALT
      AND  HzOrgProfileBankEO.home_country     = Xi_COUNTRY_NAME -- 8552410: Changed HzPartyBankEO to HzOrgProfileBankEO
      AND  HzPartyBankEO.PARTY_TYPE = 'ORGANIZATION'
      AND  HzPartyBankEO.status = 'A'
      AND  HzPartyBankEO.PARTY_ID = HzOrgProfileBankEO.PARTY_ID
      AND  SYSDATE BETWEEN TRUNC(HzOrgProfileBankEO.EFFECTIVE_START_DATE)
                   AND NVL(TRUNC(HzOrgProfileBankEO.EFFECTIVE_END_DATE),SYSDATE)
      AND  HzCodeAssignmentBankEO.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
      AND  HzCodeAssignmentBankEO.CLASS_CODE = 'BANK'
      AND  HzCodeAssignmentBankEO.OWNER_TABLE_NAME = 'HZ_PARTIES'
      AND  HzCodeAssignmentBankEO.OWNER_TABLE_ID = HzPartyBankEO.PARTY_ID
      AND  NVL(HzCodeAssignmentBankEO.status, 'A') = 'A'
      AND  HzPartyBankEO.PARTY_ID <> NVL(Xi_BANK_ID, -1);  -- Bug 8552410: Changed = to <>

Line: 7330

            FND_MSG_PUB.delete_msg(l_count_after_custom);

Line: 7680

            FND_MSG_PUB.delete_msg(l_count_after_custom);

Line: 8061

            FND_MSG_PUB.delete_msg(l_count_after_custom);

Line: 8184

    select *
    from ce_uk_val_modulus_weights
    where p_sort_code between from_sort_code and to_sort_code
    order by modulus_check_id;

Line: 8381

                        select subs_sort_code
                        into l_sort_code
                        from ce_uk_val_subs_codes
                        where orig_sort_code = xi_sort_code;