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.