r12 payables leverage desktop integrator
DESCRIPTION
Leverage Desktop Integrator to drive efficiencies in Oracle R12 E-Business Suite.TRANSCRIPT
SO-OAUG – June 2014
R12 Payables – Enhance User Productivity
using Desktop Integrator
Sajid Rahim, MSc, PMP
Disclaimer: The views and opinions expressed in this document and presentation are solely those of the author and not associated with any organization.
Introduction
• About myself
– Worked on Financials for 18 years as Project
Lead/Manager, Technical Lead and Solutions Engineer in
Government and Beverage industry
– Architect for R12 EBS Bolt-ons and extensions
– Architect for Cash Application Solution bolt-on
– EDI/E-Commerce
• Audience
– Poll Questions
Objectives
• Introduction to Payables
• Solutions Architecture
• Integrator Examples:
– Supplier Maintenance
– Invoice Maintenance
• Invoice Uploads
• Invoice Cancellation
– Others
• Conclusion
• Q&A
Introduction to Payables
• Payables delivers five major business functions
– Supplier/Employee Entry
– Invoice Import/Entry
– Invoice Validation
– Invoice Payment/Voiding Payments
– Invoice and Payment accounting
Introduction to Payables
Introduction to Payables
• R12 Payables users challenged with manual processes which
cannot deal with volume.
– Drive up costs
– Head count
• Some key areas of efficiency opportunities for business users:
– Supplier Entry and Maintenance
– Invoice Creation/Cancellation
– Payment Process Requests
– Void Process
• Build user driven processes to cater for productivity and high
volume using R12 tool sets.
Solution Architecture
• User centric solution with R12 tools:
– Desktop Integrator Framework
• Create Custom Web ADIs
– Custom Web ADI can be built for any functionality that which
Oracle makes available via PL/SQL wrappers:
• Using APIs (Public or Private)
• Using Open Interface tables
• Open Interface Concurrent Programs
NOTE: R12.1.3 and R12.2.2 have most complete versions of Desktop Integrator Framework
R12.2.2 has additional features such as switching responsibilities, compression, custom
logos, commit and diagnostics.
Solution Architecture
• Desktop Integrator Framework
– Allows integration of MS Office Desktop application such
Excel with Oracle E-Business Suite
– Generates Excel spreadsheet on user’s desktop and
allows data to be uploaded/modified in Oracle EBS
– Validation logic based on business rules
– Exception passed back in meaningful message
– ADI Documents are complaint with EBS Security Model
– Easy to develop and FREE!!
Solution Architecture
• Approach to using an integrator based solution
Solution Architecture Desktop Framework Integrator
• Steps to build an Integrator.
– PL/SQL validation package
– Create custom tables (optional)
– Register custom staging table and columns (optional)
– Create Integrator using Desktop Integration Manager
• Configure Interface
• Create Layout and Mapping
• Generate Integrator/Document
• Create Form function and associate it to user menu.
Solution Architecture Desktop Framework Integrator
• Create custom staging table (optional)
CREATE TABLE XXAP.XXAP_SUPPLIER_STG_TABLE
( RECORD_ID NUMBER,
VENDOR_NAME VARCHAR2(400),
ALT_VENDOR_NAME VARCHAR2(400)
);
Solution Architecture Desktop Framework Integrator
• Register Custom table and columns (optional)
– Application Developer->Database->Tables
Solution Architecture Desktop Framework Integrator
• Example of validation package which defines parameters for
each column in spreadsheet e.g Vendor Name
CREATE OR REPLACE PACKAGE XXAP_SUPPLIER_UPLOAD_PKG AS PROCEDURE main_proc ( p_record_id NUMBER, p_vendor_name VARCHAR2, p_alt_vendor_name VARCHAR2); END XXAP_SUPPLIER_UPLOAD_PKG;
CREATE OR REPLACE PACKAGE BODY XXAP_SUPPLIER_UPLOAD_PKG AS
PROCEDURE main_proc(
p_record_id NUMBER,
p_vendor_name VARCHAR2,
p_alt_vendor_name VARCHAR2) AS
BEGIN
INSERT into apps.XXAP_SUPPLIER_STG_TABLE
(vendor_name, alt_vendor_name)
VALUES
(p_vendor_name, p_alt_vendor_name);
END main_proc;
END XXAP_SUPPLIER_UPLOAD_PKG;
Solution Architecture Desktop Framework Integrator
• Create Integrator via Desktop Integration Manager Resp.
Solution Architecture Desktop Framework Integrator
• Configure Interface – Creates/validates interface attributes
– Interface associates to database objects in the integrator
Solution Architecture Desktop Framework Integrator
• Configure Interface
Solution Architecture Desktop Framework Integrator
• Configure Interface (cont.)
Solution Architecture Desktop Framework Integrator
• Create Layout specifying which columns to include.
Solution Architecture Desktop Framework Integrator
• Create Layout (cont.)
Solution Architecture Desktop Framework Integrator
• Generate Document via Desktop Integration Resp.
Solution Architecture Desktop Framework Integrator
• Generate Document via Desktop Integration Resp.
Solution Architecture Desktop Framework Integrator
• Generate Document via Desktop Integration Resp.
Solution Architecture Desktop Framework Integrator
• Generate Document via Desktop Integration Resp.
Solution Architecture Desktop Framework Integrator
• PL/SQL Wrappers
– Programs which have business logic to handle user data.
– Can be called directly by the integrator or by separately by a
concurrent program.
– Can perform several tasks
• Parse staging table data
• Load interface tables or call public/private apis.
• Submit standard import concurrent programs using
FND_REQUEST.SUBMIT_REQUEST
• Produce reports
Solution Architecture Desktop Framework Integrator
• Public vs Private apis
– Public apis are published and users are allowed to use them.
Open Interfaces allow only to add but apis are available with
add/update/delete
– Private apis are unpublished and can be changed by the vendor
at any time.
– Recommend using Public apis and only in extreme cases resort
to private apis.
Examples…
Supplier Maintenance
• Challenging due to manual nature.
• Daily maintenance vs Conversions
– Front End (OAF Supplier Page) or iSupplier
– Using API
– Using Standard Supplier Interface
• Each approach has own challenges.
– Interface allows only ‘Adds’. ‘Change’ is not possible.
– APIs not well documented, trial/error required.
• Choose Desktop Integrator with API based process
– Allows Adds/Changes in a unified process.
– Public APIs sample code given in paper and in metalink
Supplier Maintenance
• Supplier/Site/Contact
Supplier Maintenance
• Trading Community Architecture
Supplier Maintenance
• TCA – Bank Model
Supplier Maintenance Integrator Solution
• Integrator Model for Supplier Maintenance
Supplier Maintenance Integrator Solution
• Create Integrator and staging table with required columns.
Level Column Notes
Supplier Supplier Name Validate for duplicate name; this value cannot be null
Alternate Supplier Name (default to Supplier Name) Default to Supplier name unless otherwise
Tax Reporting Name Default to Supplier Name unless otherwise
1099 Number
Tax Payer Type Use List of Values
Federal Reportable Use List of Values (Y/N)
Organization Type Use List of Values
Site Operating Unit Use List of Values
Site Name Use List of Values for Standardized names
Site Address Line 1 Free format
Site Address Line 2 Free format
Site Address Line 3 Free format
Site City Free format
Site State or Province List of Values
Site Zip
Site Country List of Values
Site Currency Code List of Values
Tax Reporting Site List of Values. This can be defaulted to Federal Reportable Flag.
Contacts First Name Free format
Last Name Free format
Phone Number Format
Phone Area Code Format
Contact Email Format
Supplier Maintenance Integrator Solution
• Use validation routine for default values and save data on
custom staging table.
• Use List of Values to enforce integrity.
• Sample integrator:
Supplier Maintenance Integrator Solution
• Create a PL/SQL Concurrent program to process Supplier
data from staging table.
• Adding a Supplier/Site/Contact make use of following apis.
– AP_VENDOR_PUB_PKG.Create_Vendor;
– AP_VENDOR_PUB_PKG.Create_Vendor_Site
– AP_VENDOR_PUB_PKG.Create_Vendor_Contact
Supplier Maintenance Integrator Solution
• Changing Supplier/Site/Contact make use of following apis.
– AP_VENDOR_PUB_PKG.Update_Vendor;
• Hz_party_v2pub.update_organization for name change
– AP_VENDOR_PUB_PKG.Update_Vendor_Site
• Hz_location_v2pub.update_location for address change
– Update Contact
• HZ_CONTACT_POINT_V2PUB.update_email_contact_point
• HZ_CONTACT_POINT_V2PUB.update_phone_contact_point
• HZ_PARTY_V2PUB.update_person
• For new email and phone use
HZ_CONTACT_POINT_V2PUB.create_contact_point
Supplier Maintenance Integrator Solution
• Bank/Branch/Account maintenance (optional)
– Not recommended for Integrator due to security concerns.
– If you have a secure interface, following APIs can be used:
• IBY_EXT_BANKACCT_PUB.create_ext_bank
• IBY_EXT_BANKACCT_PUB.create_ext_bank_branch
• IBY_EXT_BANKACCT_PUB.create_ext_bank_acct
• IBY_EXT_BANKACCT_PUB..add_joint_account_owner
• IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment
• IBY_DISBURSEMENT_SETUP_PUB.Update_External_Payee
Supplier Maintenance Integrator Solution
• Reporting should always be considered for audit purposes
Invoice Maintenance
• Need for manual invoices to be keyed.
• Allows User productivity and focus on higher level tasks
• Leverage Desktop Integrator for such a solution as alternative
to buying a Excel package solution
• Free of charge and custom to your environment
Invoice Maintenance
• Invoices
Invoice Maintenance Invoice Upload - Integrator solution
• Invoice creation process
Invoice Maintenance Invoice Upload - Integrator solution
• Create integrator with columns that support business needs
Level Column Notes
Group Group ID Unique number to this batch.
Invoice Operating Unit List of Values. Must be valid
Invoice Type List of Values
Supplier Number Validation
Invoice Currency List of Values
Invoice Amount
Description
Due Date
Payment Method List of Values
Remit Message 1
Remit Message 2
Legal Entity Name/Id These can be resolved via GL Segments or List of Values
Invoice Line Line Type List of Values
Line Amount
GL Segments List of Values or validation
Line Pay Reason List of Values
Line Description
Project Number List of Values or validation
Task
Expenditure Item Date
List of Values or validation
Expenditure Type List of Values or validation
Expenditure Organization List of Values or validation
Pro-Rate Flag List of Values
Invoice Maintenance Invoice Upload - Integrator solution
• Use validation routine for default values.
• Use List of Values to enforce integrity.
• Load data into Payables Open Interface
• Sample integrator
Invoice Maintenance Invoice Upload - Integrator solution
• Concurrent program which submit Payables Open Interface
(APXIIMPT) followed by the Invoice validation (APPRVL)
using FND_REQUEST.SUBMIT_REQUEST
• Solution allows users to mass upload invoices on demand
with great ease vs having to perform this task via Invoice
WorkBench
Invoice Maintenance Invoice Cancellation
• Invoice Cancellation process
– Oracle does not provide a solution for mass cancellation of
invoices.
– A public API is available which can be used within a concurrent
program
• AP_CANCEL_PKG.ap_cancel_single_invoice
Invoice Maintenance Invoice Cancellation – Integrator Solution
• Integrator model for Invoice Cancellation
Invoice Maintenance Invoice Cancellation – Integrator Solution
• Create Integrator with mandatory columns
Field
name Comments Validation
Supplier
Number Mandatory Validate Supplier
Invoice
Number Mandatory Validate Invoice Number
Invoice
Date Mandatory
Invoice Maintenance Invoice Cancellation – Integrator Solution
• Use validation routine to check invoice status
• Sample integrator
Invoice Maintenance Invoice Cancellation – Integrator Solution
• Concurrent program to process invoice cancellation
– Process all uploaded rows
Fetch records for invoices to be cancelled.
LOOP
IF Invoice is not cancelled or paid THEN
Call standard API -
AP_CANCEL_PKG.ap_cancel_single_invoice
END;
END LOOP
Other ADIs
• Voiding Payments
• Tax Alignments – 1099
• Fixed Assets - Mass transfers
• Receivables – Customer & Hierarchy maintenance/Party
merge
Q & A