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

Steps for debugging issues in Oracle

How to capture the back-end FND debugging messages thrown when errors are thrown.

See the action plan below:

(a) Set the following profile options at your user level

FND: Diagnostics: Yes
FND: Debug Log Enabled: Yes
FND: Debug Log Level: Statement
FND: Debug Log Mode: Asynchronous with Cross-Tier Sequencing
FND: Debug Log Module to %


(b) Go to the form or prior step where the issue arises.

At this point, run the following SQL:

select max(log_sequence) from fnd_log_messages;

Note the max(log_sequence) value as &start


(c) Then proceed to replicate the error.

After clicking away the error, re-run the SQL:

select max(log_sequence) from fnd_log_messages;

Note the max(log_sequence) value as &end


(d) Then run

SELECT substr(module,1,70), MESSAGE_TEXT, timestamp, log_sequence
FROM fnd_log_messages msg
WHERE log_sequence between &start and &end
ORDER BY LOG_SEQUENCE


(e) REMOVE PROFILE OPTION VALUES AT YOUR USER LEVEL!!!!

R12 SLA Tree: a visual diagram

>> ADR JED JLT

>>>> JLD

>>>>>> AAD

>>>>>>>> SLAM

Where:
ADR = Account Derivation Rules
JED = Journal Entry Description
JLT = Journal Line Type
JLD = Journal Line Definition
AAD = Application Accounting Definition
SLAM = Subledger Accounting Method

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.

How to setup a defaulting DFF value of Default Type SQL Statement

If you need to repeat a value already part of the original transaction and automatically populate it in another area of the same transaction, in this case: the data is located on the AP Invoice header and it needs to be passed to the invoice line.

Then you need to create a DFF with a default type of 'SQL Statement' at the destination level and pass a bind variable into the 'where' clause of the select statement.

Here's an example: let's say there is a value (e.g. attribute4) in the AP invoice header that needs to be copied down to each AP invoice lines, you simply need to have the following default SQL statement for the DFF created at the invocie line level:

select aph.attribute4
from ap_invoice_lines_all apl, ap_invoices_all aph
where aph.invoice_id = apl.invoice_id
and apl.invoice_id = :INV_SUM_FOLDER.invoice_id

In order for the attribute4 from the invoice header to default automatically to the invoice lines, the last clause of the query above requires to pass a bind variable, in this case, it corresponds to ":INV_SUM_FOLDER.invoice_id"

To get the value above, Navigate to the form where the data is located, then extract the 'block'.'field' information by navigating to Help > Diagnostic > Examine

Prefix the combination of the 'block'.'field' with a " : "

My friend Cathy B. also pointed me to this excellent reference explaining Bind Variables: http://docs.oracle.com/cd/A60725_05/html/comnls/us/fnd/values19.htm