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; 

Using Folders to manage AP Invoice workloads

Remember the old days when AP functions and workloads were split between AP Invoice Officers using the first letter of the supplier name (we called this the alphabet split), or using the invoice date, or the invoice receipt date.

In today's world, with ERP systems and scanning solutions, the processing of AP invoices is mostly automated. Invoices are often automatically created or imported into the AP Invoice workbench using interface tables or a scanning solution. With these systems in place the majority of invoices do not require any human intervention, while others do. For this last group, the workload distribution can be achieved in different ways, alphabetically, by invoice date (invoice receipt date), or by evenly distributing the workload to the AP Officers using the count of invoices.

I have replicated the alphabet split approach using form folders with embedded queries, sorting unprocessed invoices by first letter of the supplier name, this worked nicely, but this approach had a few disadvantages:
- the workload was often not evenly distributed,
- all invoices from the same supplier were processed by the same AP officer, employee morale was low as the work was monotonous,
- suppliers often called the AP officer directly to find out when their invoices were going to be paid, instead of calling the designated hotline,
- other AP officers did not know how to process these invoices, if there were any particularities related tho this supplier,
- some suppliers would request special attention or request the AP officer to process their invoices in a particular way, instead of applying the standardized process.

The second approach defined above, by distributing the workload using dates, also does not split the workload evenly between the AP Officers, affecting employee morale as well.

The evenly distributed workload approach seems like the best option and can easily be implemented by using the same Form Folders with embedded queries. How did we achieve this you may ask, here's how we did it. We grouped the last 2 digits of the INVOICE_ID (numeric value) into ranges of values, e.g. we created 5 folders with the following ranges: 00-19, 20-39, 40-59, 60-79, and finally 80-99.
So when invoices are migrated from the interface or from the invoice scanning software, the unprocessed AP invoices are allocated evenly between all 5 AP Officers.

I put a smile on the face of the AP manager when this solution was implemented. The team immediately felt more engaged and valued.

NAB Online vs. NAB Direct

As an Oracle consultant, I often need to configure Oracle to create EFT files that get sent electronically to bank institutions for making payments to suppliers.


In Australia, there is a Bank Institution that currently has 2 different software offerings (Online & Direct) that connect to an ERP system like Oracle. This institution is NAB, National Australia Bank.


And during conversations I had with a financial director of one of my previous engagements, the difference between NAB Online and NAB direct was explained to me as per below, or this is what I understoond the difference was:


NAB Online: is the current version of software provided by NAB for enabling EFT payments, and has a built-in authorisation process for every payments which require 2 people to approve payments before the bank releases the funds.



NAB Direct: is the new software offering which has more automation when it comes to process and configuration, but the authorisation process from the previous version (NAB Online) is no longer offered in the new software, and this is critical per the Finance Director.

The director in question raised the new NAB offering, NAB Direct, as a risky piece of software and left me with these questions: "How can you prevent a techie person from dropping an EFT payment file in the designated NAB directory and tell me who will approve this payment?"

I could not argue with him regarding the director's last point. And I knew I could not diverge the director's irrational distrust of others towards a viable workaround or solution. But I could have had easily provided the director with a few hundred other ways the "techies" could cheat the system.

Here are a few benefits of "NAB Direct" listed their website:


• Leverages your ERP investment – use your ERP system to add ‘end to end’ connectivity between you and NAB.

• Helps save time and reduces costs – fully automatic straight through processing virtually eliminates the needfor manual reconciliation.

• Reduces risk – process high-volume transactions with limited human interaction, thereby reducing operationalrisk.

• Strong security – four layers of security, including digital signatures and message encryption, help to ensure yourconnection to NAB is protected.

My personal opinion about this issue is that the new R12 Oracle Payments module can easily handle the authorisation process within Oracle. The 'end to end' connectivity between Oracle and NAB should be leveraged as well. If a payment goes through by fraud, this will get flagged in the cash management module - alerts can also be defined to notify HR, the legal department, and your Finance Director.

As a side note, if your techies cannot be trusted, I suggest you start looking at outsourcing their tasks to an insured and qualified 3rd party vendor with a reputation to protect.