How to add color to the workflow notification HTML message body

There can be scenario when customers want specific text in workflow messages to be color coded. Like there could be some warning message to users that they need to review before approving the workflow notification. Such kind of requirement can be achieved easily by using specific HTML tags in the workflow message HTML body:
eg:

br
span style='color:red'
TEXT INPUT
/span
br
I have removed < > "HTML braces" from the tags.

This would result in RED color text when the notification from workflow goes out to the users like below..

TEXT INPUT
 

Oracle Apex and cloud based development integrating EBS R12.1 and above

Either by design or coincidence APEX is an incredible fit as the product for cloud based-systems from development to deployment. From a development perspective, APEX is a cloud development platform as all development is done in a web browser. There are absolutely no development components to be installed on a client machine. All components can that are developed in APEX can be exposed as web services and be consumed by other applications running on a private, public or hybrid cloud. All the end user needs to access the application is a URL. From a deployment perspective, APEX addresses some of the biggest issues with public cloud application deployments: dynamic multi-tenant workload migration, management and provisioning. This is because the APEX security, database, metadata, and application are all contained in the Oracle Database. This means an APEX application from bits on disk, to network, to database to the application can be managed, provisioned and migrated as one workload. This type of end to end workload management, migration and provisioning can only be achieved by using virtualization. With Oracle APEX, all these capabilities are provided without the need and overhead of a virtual server.

Details on integrating EBS with APEX can be found on following note:
Extending Oracle E-Business Suite Release 12.1 and above Oracle Application Express (Revision 2, OTN and Note 1306563.1)

Helpful Queries for EBS developers

Query to get responsibility and attached request groups
==============================================================================
SELECT responsibility_name responsibility, request_group_name, frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name ;

Query to get all Request attached to a responsibility
=============================================================
SELECT responsibility_name, frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg, fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Query to Count Module Wise Report
==========================================
SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method, COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1;

Query to calculate request time
=================================
SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
, DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;

Query to check responsibility assigned to a specific USER
==========================================================
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
and a.application_name = 'Purchasing'
ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)
Query to print the oracle apps version
============================================
SELECT substr(a.application_short_name, 1, 5) application_short_name,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')

Script used to pick up the recent EBS users and their email addresses
=====================================================================
SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')

Query to fetch the manager's manager information in HRMS
========================================================
select
papf.employee_number
,papf.person_id employee_person_id
,papf.full_name employee_name
,papf.email_address emp_email_address
,papf.employee_number mgr_employee_number
,papf_mgr.person_id manager_person_id
,papf_mgr.FULL_NAME manager_full_name
,papf_mgr.email_address manager_email_address
from
per_all_people_f papf,
per_all_people_f papf_mgr
where
(papf.person_id,papf_mgr.person_id) in
( select
pasf.person_id,
pasf.supervisor_id
from
per_all_Assignments_f pasf
start with
( pasf.person_id = :x_person_id and
sysdate between pasf.effective_start_date and pasf.effective_end_date )
connect by prior
pasf.supervisor_id = pasf.person_id
and sysdate between pasf.effective_start_date and pasf.effective_end_date
)
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between papf_mgr.effective_start_date and papf_mgr.effective_end_date
 

Security in Webadi

If you add security rule to your integrator and have a function there then it is important that those functions are part of responsibility menu from where the Web adi is called. Else it might give error like integrator is an invalid Integrator Key when the users try to open the ADI.
Security Rules are an important way to restrict the use of ADI from users who are not supposed to access it.

How clear BNE cache for webadi development

Login into E-business Suite. You should have System Administrator responsibility.
Once the Home Page is up, paste the URL below into the same Browser window (replace hostname,domain and portnumber as applicable)

BNE ADMIN Servlet for 11i
http://hostname:portnumber/oa_servlets/oracle.apps.bne.framework.BneAdminServlet

BNE ADMIN Servlet for Release 12
http://hostname:portnumber/OA_HTML/BneAdminServlet

Once logged in you would have the option to clear cache as shown in below diagram..

 

AP-EBTAX Entity Relationship Diagram

Here is the ERD which shows how AP tables are joined to EBTAX tables. This is very much needed for building the queries around EBTAX.

AP-Tax ERD




Add custom tags in Payment Process Request

Disbursement program (Payment process Request) comes with seeded xml tags and if we have to add a new tag then Oracle provides a way of doing it. There is a package called IBY_FD_EXTRACT_EXT_PUB, which can be customized to add custom tags. There are multiple functions which can be customized based on your clients requirements.
The way to do it is
 SELECT XMLConcat( XMLElement("EmployeeNumber",l_empno),
 XMLElement("EmployeeName",l_empname),
 XMLElement("Employee_address_line1",l_address_line1),
 XMLElement("Employee_address_line2",l_address_line2),
 XMLElement("Employee_address_line3",l_address_line3),
  INTO l_ins_ext_agg
 FROM dual;

The function returns l_ins_ext_agg and the xml tags for
EmployeeNumber, EmployeeName, Employee_address_line1, Employee_address_line2, Employee_address_line3are created once PPR(Payment Process request) runs

Add customization to Invoice validation program In EBS

Sometime few clients come up with requirement of adding some customization to invoice validation process. Oracle allows that via hook  AP_CUSTOM_INV_VALIDATION_PKG.AP_Custom_Validation_Hook.

You can provide all the custom logic that you want the invoice validation program to perform and that should suffice clients requirement.

Getting invoice validation status in Oracle EBS

Invoice validation status is not stored in any of the database tables. One can use the below query to check the invoice validation status:


select Displayed_field
from ap_lookup_codes
where (lookup_type = 'NLS TRANSLATION' or lookup_type = 'PREPAY STATUS')
and lookup_code = apps.ap_invoices_pkg.get_approval_status(p_invoice_id,p_invoice_amount,p_payment_status_flag,p_invoice_type_lookup_code)

Vertex O series and Oracle R12 integration

There are very few documentation which provide insight to integrate Oracle P2P and Vertex. I would try to cover some basics here but if you need any indepth consulting knowledge, please email me.

Vertex allows the customers to customize the Oracle / Vertex integration process by adding logic in the client extension packages. The client extension package for the Procure to Pay process is VTX_P2P_UTL_CUSTOM_ATTR_AP_PKG

 

Vertex Oracle Integration Connector (OIC) supports sending additional data to Vertex through the following data elements in the OIC array data structure

•  25 character fields LINE_CHAR1~25)
•  10 numeric fields (LINE_NUMERIC1 ~ 10)
•  5 date fields (LINE_DATE1 ~5)

OIC reserves some fields for internal use, and we can only control the data source for such reserved fields

•  LINE_CHAR3 for source system name
•  LINE_CHAR16 for vendor class
•  LINE_CHAR17 for purchasing class
•  LINE_CHAR18 for usage class
•  LINE_CHAR19 for store location code
•  LINE_CHAR20 for cost center
•  LINE_CHAR21 for department code
•  LINE_CHAR22 for GL account
•  LINE_CHAR23 for material code
•  LINE_CHAR24 for project number
•  LINE_CHAR25 for vendor SKU
•  LINE_NUMERIC6 for charged tax

Some of the basic vertex table to debug the P2P transactions are as follows:
LINEITEM and lineitemtaxovrflw
TRANSSYNCIDCODE in LINEITEM table has the invoice details in it.

Customizing Automatic Withholding Tax in R12.1.3

If you are using Payables to automatically withhold tax, you may want to customize the AP_CUSTOM_WITHHOLDING_PKG to perform special behavior. This PL/SQL package contains a procedure Ap_Special_Withheld_Amt which can be used to implement functionality like rounding witholding tax.

Eg:
procedure Ap_Special_Withheld_Amt
                (
                 P_Withheld_Amount        IN OUT NOCOPY Number
                ,P_Base_WT_amount         IN OUT NOCOPY Number
                ,P_CurrCode               IN Varchar2
                ,P_BaseCurrCode           IN Varchar2
                ,P_Invoice_exchange_rate  IN Number
                ,P_Tax_Name               IN Varchar2
                ,P_Calling_sequence       IN Varchar2
                ,P_Unrounded_WT_Amount    IN Number     DEFAULT NULL --bug 10262174
                ,P_Calling_parameter      IN Varchar2   DEFAULT NULL --bug 14271407
                 )
is
BEGIN
-- IMPORTANT: This section is reserved for Globalization features.
--            Please do not modify code here.
-- BUG 7232736 replaced sys_context with g_zz_shared_pkg.get_product
-- Uncommented the call to JG_WITHHOLDING_TAX_PKG.JG_Special_Withheld_Amt
-- IF sys_context('JG','JGZZ_PRODUCT_CODE') is not null THEN
IF jg_zz_shared_pkg.get_product(AP_CALC_WITHHOLDING_PKG.g_org_id, NULL) is not null THEN
    JG_WITHHOLDING_TAX_PKG.JG_Special_Withheld_Amt
                                     (P_Withheld_Amount
                                     ,P_Base_WT_amount
                                     ,P_CurrCode
                                     ,P_BaseCurrCode
                                     ,P_Invoice_exchange_rate
                                     ,P_Tax_Name
                                     ,P_Calling_sequence
                                     );
    NULL;
  END IF;
-- Please enter all custom code below this line.
-- Begin Custom Code
    --Added by Siddhartha S. for rounding witholding tax to floor value.

       P_Withheld_Amount:= FLOOR(P_Unrounded_WT_Amount);

    --END of addition
-- End Custom Code
END Ap_Special_Withheld_Amt;

Customize Webadi which cannot be customized using Desktop Integration Manager Responsibility in R12.1.3

If you are not able to update a webadi from Desktop Integration Manager Responsibility because it is created using API then you need to run the following update in the database.
Query for the integrator you want to update from application in bne_integrators_b.
Then update the column Source to 'C'. Once you commit, the integrator can be updated from Desktop Integration Manager responsibility. Please do not try this for seeded webadi.
This is only for custom ADI created from the API.
The command can look like
update bne_integrators_b
set source = 'C'
where integrator_code = 'XXWEBADI_INTG';

'XXWEBADI_INTG' is the integrator code you may want to update from Desktop Integration Manager Responsibility.

Please post your comments in case you run into issues while trying it.

Extending DFFs via KFF

There has always been a constraint on number of DFF and every big implementation looks for more number of DFF where additional information can be stored. Oracle by default provides only 15 attributes for single context value. I would outline the way we can overcome this constraint by creating custom KFF on DFFs and in a way make the number of DFFs as unlimited.

Step 1: Create a custom table and the corresponding custom sequence . Give grants to the Apps schema.

 

Step 2:  Create a synonym for both table name and sequence in APPS schema.

 

Step 3:  Register the table using the AD utility and also register the sequence using the FND_DICTIONARY_PKG.

 

Step 4: Register the KFF in the standard application. Also define the segments.

Step 5: Create a structure and enable as many as segments as needed. Also ensured the dynamic inserts is enabled. ** This design gives flexibility to create multiple structure for multiple form like invoice header, journal header, journal line etc.

 

           Step 6: Created a special value set for the edit, load and valid events.

Step 7: Add the value set to the DFF.

 

Viola.. Your KFF on DFF is ready.

**For implementing it through interface you just need a call to fnd_flex_ext API and that would generate the combination.

We need to create unique structure and special value set to implement it in multiple tables. This way one single custom table would serve to fill up as many as DFF we need. There will not be any limitations for using it.

 

Please reach out to me for technical details on how it can be implemented.