Workflow Builder 2.6.3.5 Client Installation tips

Download from Oracle here:
http://www.oracle.com/technetwork/database/options/winclient-101059.html

Ensure to extract the software before running the Oracle installation.

Symptoms: Cannot install Workflow Builder, or getting "Error Accessing the System Registry"

Solution: Before installing the Workflow builder, install this software with 'Compatibility Mode' as either Windows XP or XP Service Pack 2

After successfully installing, run the Workflow Builder software as System Administrator, by right-clicking the program from the Start Menu.


Happy Workflow-ing!

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; 

Pre AME rollout - HR sanity check scripts

Prior to rolling out AME for iProcurement and/or Internet Expenses, it is important to ensure the HR hierarchy is clean with no breaks or holes in it.

Here's an example of a script to help identify corrupt data in the HR hierarchy (e.g. direct reports with no supervisors, missing default expense accounts, jobs (positions), office location, email address)


select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.supervisor_id is null --with missing supervisor record
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.default_code_comb_id is null --with missing default expense account
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.job_id is null  --with missing job (or position, for fetching the DOA)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.location_id is null  --with missing location (used to create supplier records of Employees with OIE)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and ppf.email_address is null  --with missing email address (to send email notifications)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
order by 1;

Review of OIE processes, expense items, expense report templates

Here's a script that extracts all expense report templates and expense items with accounting information, the extract should be reviewed by the Finance and/or Accounts Payable team(s) on a regular basis and make changes when required:

select decode (exi.org_id,1,'',2,'org name 2',3,'org name 3',4,'org name 4') organisation,
ext.report_type template_name, exi.prompt, exi.vat_code, category_code, exi.flex_concactenated, exi.flex_description, exi.creation_date
from AP_EXPENSE_REPORT_PARAMS_ALL exi,
ap_expense_reports_all ext
where ext.org_id = exi.org_id
and ext.expense_report_id = exi.expense_report_id
and ext.inactive_date is null
and ext.web_enabled_flag = 'Y'
and exi.end_date is null
order by 1, 2, 3;

Some tech tips and links

Where are the .wft files located, like APEXP.wft ?

WFLOAD - what does this command do in PUTTY?
Have a read of this blog entry here.

More to come - keep watching !

Tax Codes not enabled for Internet Expenses

Try the following SQL scripts to help you identify if the setup of tax codes were correctly done or upgraded:

--Query 1
SELECT lookup_code, meaning, description
FROM zx_input_classifications_v
WHERE lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
AND org_id =
AND enabled_flag = 'Y'

--Query 2
SELECT lookup_code, meaning, description
FROM zx_input_classifications_v
WHERE lookup_type = 'ZX_WEB_EXP_TAX_CLASSIFICATIONS'
AND org_id =  
AND enabled_flag = 'Y'

If the tax code appears in both queries, then the tax code should appear in the Internet Expenses module, when the tax code only appears in query 1 above, then you know there is an issue with the tax code.

To fix this issue, follow the additional setups identified in Oracle note 1312692.1 : Payables - Upgrade - Tax Codes not Enabled for Internet Expenses OIE