Duplicate Bank Names in 11i do not upgrade correctly in R12

When the 11i bank data looks like this:

bank name bank_number
--------- -----------
ANZ 013-427
ANZ 014-295
ANZ (blank)

The upgrade will treat this as three different banks..since bank numbers are different..

1) if they want only one ANZ .. say for eg.. ANZ without bank number, then in that case they will have to remove the bank numbers from the UI..

2) They also need to make sure that the bank names are same(case sensitive). So if name is say 'National Australia Bank' and 'NATIONAL AUSTRALIA BANK' then they are treated as two different banks..

So the best approach is as follows:

Please correct the data in their 11i instance from the UI. Once they are done with all the corrections, they can run the below script to figure out..what banks will be created..

select bank_branch_id, bank_name, bank_number from ap_bank_branches
where bank_branch_id in (
SELECT
t.group_id
FROM ap_bank_branches b,
(SELECT bank_branch_id,
fv group_id,
country,
DECODE(bank_branch_id, fv, 'Y', 'N') primary_flag
FROM
(SELECT bank_branch_id,
bank_number,
institution_type,
country,
bank_admin_email,
bank_name,
bank_name_alt,
jgzz_fiscal_code,
tax_reference,
description,
active_date,
end_date,
first_value(bank_branch_id) over (partition BY bank_number,
institution_type, country, bank_name, jgzz_fiscal_code, tax_reference order
by creation_date DESC) fv
FROM
(SELECT creation_date,
x.bank_branch_id,
bank_number,
DECODE(upper(institution_type), 'BANK', 'BANK', 'CLEARINGHOUSE')
institution_type,
NVL(x.country, DECODE(SUBSTR(global_attribute_category,4,2), 'BR',
'BR', 'CO', 'CO', 'ES', 'ES', NVL(y.country,'AU'))) country,
bank_admin_email,
bank_name,
bank_name_alt,
DECODE(NVL(x.country,SUBSTR(global_attribute_category,4,2)), 'ES',
global_attribute1, 'CO', NVL(global_attribute11
|| global_attribute12, DECODE(global_attribute14,NULL,NULL,
(SELECT b2.global_attribute11
||b2.global_attribute12
FROM ap_bank_branches b2
WHERE NVL(b2.country, SUBSTR(b2.global_attribute_category, 4, 2)) =
'CO'
AND b2.bank_branch_id =
to_number(x.global_attribute14)
)))) jgzz_fiscal_code,
DECODE(NVL(x.country,SUBSTR(global_attribute_category,4,2)), 'CO',
global_attribute13) tax_reference,
description,
active_date,
end_date
FROM ap_bank_branches x,
(SELECT bank_branch_id,
country
FROM
(SELECT ba.bank_branch_id,
ba.bank_account_id,
ba.org_id,
loc.country,
first_value(ba.bank_account_id) over (partition BY
ba.bank_branch_id order by DECODE(ba.account_type, 'INTERNAL', 1, 2)) fv
FROM ap_bank_accounts_all ba,
hr_all_organization_units org,
hr_locations_all loc
WHERE ba.account_type IN ('INTERNAL', 'EXTERNAL', 'SUPPLIER')
AND org.organization_id = ba.org_id
AND loc.location_id = org.location_id
)
WHERE bank_account_id = fv
) y
WHERE y.bank_branch_id(+) = x.bank_branch_id
)
)
) t
WHERE b.bank_branch_id = t.bank_branch_id )
ORDER BY 2 ;

Script to identify inactive supplier records

The following script can be used to identify the supplier records not used for a specific number of days:

SELECT aps.vendor_id, aps.vendor_name, aps.segment1, aps.vendor_type_lookup_code FROM ap_suppliers aps WHERE NOT EXISTS (SELECT DISTINCT vendor_id FROM (SELECT vendor_id FROM ap_invoices_all WHERE creation_date > (sysdate - p_days) AND org_id = UNION ALL SELECT vendor_id FROM po_headers_all WHERE creation_date > (sysdate - p_days) AND closed_code <> 'CLOSED') AND org_id = WHERE vendor_id = aps.vendor_id) AND aps.end_date_active IS NULL;