appEXTRACT - a next generation of data extraction tool for Oracle E-Business Suite R12/11i

appEXTRACTappEXTRACT is a simple user driven data extraction tool. appEXTRACT can extract data from databases such as Oracle, SQL Server, SyBase DB2, MySQL and any other database that supports JDBC connectivity.This tool aims to reduce the cost of data extraction activities.
appextract appEXTRACT can extract data from databases such as Oracle, SQL Server, SyBase DB2, MySQL and any other database that supports JDBC connectivity.
xml appEXTRACT can produce output in Excel, CSV, XML, HTML and PDF formats.
integration appEXTRACT works seamlessly in the appLOAD family suite of products which provides a robust scheduling engine.
no prog Business Users can use the tool with confidence for their data extraction needs with no technical assistance. appEXTRACT is a flexible configuration engine which needs no programming

demo request appextract
  • Wizard driven extraction engine.
  • Substantially reduces time and effort in data extraction.
  • Seamless integration with appLOAD and appINTERFACE products.
  • Our customers have seen over 80% savings in data extractions.

Preprocessing Engine

Database Independence

Data Output

appLOAD and appINTERFACE Integration Engine

Scheduling Engine

Jasper reporting engine

Security

 

Preprocessing Engine:

appEXTRACT provides PL/SQL preprocessing engine. You can write custom PL/SQL package to preprocess the data for data extraction.The extraction can use the preprocessing engine's output.

go up button

Database Independence:

appEXTRACT is capable of processing data from any database. Certified databases include Oracle, DB2, SQL Server and SyBase. As our commitment towards open source we also certify for MySQL database. We do support any other databases supporting JDBC connectivity contact us for more information.

go up button

Data Output: appEXTRACT can produce data outputs in various format such as: XLS, CSV, TXT, XML, HTML, and PDF. The formatted data output from appEXTRACT can be used for data loading and reporting purposes.

go up button

appLOAD and appINTERFACE Integration Engine:

appEXTRACT is delivered with built-in appLOAD and appINTERFACE integration. This enables you to extract the data from other systems and load into Oracle E-Business suite without additional programming.

go up button

Scheduling Engine:

appEXTRACT has automated scheduling capability for data extraction. The scheduler can process on-demand or on a periodic frequency .Scheduler can call appLOAD for data loading into EBS and it can also call appropriate appLOAD templates for performing dataloads into Oracle EBS.

go up button

Jasper reporting engine:

appEXTRACT can be used as a reporting tool as well. It is integrated with Jasper reporting engine to create clean formatted reports in the PDF, XLS and HTML formats.

go up button

Security:

appEXTRACT templates are secured for modification and execution by user accounts.

go up button

appMIGRATE architecture

appLOAD suite templates

The appLOAD suite has 100+ pre-configured templates covering various functional modules of Oracle EBS. appINTERFACE offers these templates as web services for SOA orchestration.

Oracle Financials

1

 Create GL Code Combinations:

This template is used for creation of the new general ledger chart of accounts.

2

AR Invoice, Credit and debit memos

  This template is used for loading Account Receivables Invoice, Credit memos and Debit memos. Both open and historical transactions can be processed using this template.

3

Create Supplier, sites and contacts

  This template is used for creation of the supplier, supplier sites and supplier contacts.

4

AR Cash Receipts

  This template is used for loading Account Receivables Cash Receipts – partial or complete receipts against 1 or more AR Invoices.

5

Create Customer, address and sites

  This template is used for creation of the customer, customer address, customer sites and customer contacts.

6

AR Cash Remittance

  This template is used for recording the Account Receivables Cash Remittance transaction which will post the cash received into Oracle Cash Management module.

7

Append Customer Address

  This template is used for appending new customer addresses to the existing customer master data.

8

FA Mass Addition

  This template is used for loading the Fixed Assets.

9

GL Balances (Journal import)

  This template is used to import Journals into Oracle General Ledger module. The opening balances of the GL accounts can be converted using this template.

10

A Mass Transfer

  This template is used for transferring the Fixed Assets from one division to another.

11

GL Budget

  This template is used to load the General Ledger Budgets.

12

FA Adjustments

  This template is used for updating the Fixed Asset information.

13

AP Invoice

  Accounts Payable Invoices (open and closed) can be imported using this template. Both open and historical transactions can be processed using this template.

14

Bank Interface

  This template is used for loading the bank statements from the Bank into Oracle Cash management for both internal and external banks.

15

AP Payments

  This is a custom template made for loading Accounts payable payments against the AP invoices.

16

iExpenses

  This template helps in loading the iExpense Reports, Approving the expense report and interfacing the expense report into Oracle Payables as an AP Invoice.
Oracle Manufacturing

1

 Item, Item Revisions and Material cost

  This template is used for creation and update of item master in master and child organizations including revision control and material / material overhead cost. Item categories can also be loaded through this template.

2

MRP Forecasting

  The MRP forecasting can be created using this template. Forecast name should be setup manually and then forecasting entries can be generated using this template.

3

Item Categories

  This template is used for creation and update of item category assignment against items in Oracle Inventory module.

4

QA Collection Plan

  Oracle Quality’s Collection Plan data can be loaded through this template. This is a very useful functionality to maintain all the quality data collected from the Inbound and shop floor transactions and also maintain any custom collection plan data.

5

Item Catalogs

  Assign catalog and catalog values to an item using this template.

 

 

 

 

6

Demantra – Sales History

  Demantra is a statistical forecasting solution. This template helps in loading the sales history into Demantra. This data can be used for executing the statistical forecasting engine to get the final forecasting data which then can be interfaced into Oracle ASCP.

7

Item Cross Reference

  Item cross references can be created using this template for individual inventory organizations or all inventory organizations.

8

WIP Mass load

  This template helps you to load the Work in process Discrete Jobs and Schedules

9

Advanced Products Catalog

  This template helps to load the additional custom item attributes created in the Oracle Product Lifecycle management module using the Advanced products catalog functionality.

10

Item On-hand Conversion

  This template helps you to load the quantity on-hand information into Oracle Inventory module. This template also would load the costing information if you are following average costing methodology.

11

Sub-inventory item assignment

  This is a functionality in Oracle Inventory – Sub-inventory definition. Items can be assigned to the sub-inventories with or without locator controls.

12

WIP Move Transactions

  This template helps you to record the movement of Work in process (WIP) stock from one operation to another. Also this template can be used for moving the work in process stock from one status (IN-QUEUE, RUNNING, REJECT, REWORK, COMPLETE) to another in the same operation.

13

Item Costs (material, material overhead, resource, resource overhead, outside processing)

  This template is used for loading material, material overhead, resource, resource overhead and outside processing cost for an item against any PENDING cost types. Once the cost is verified and rolled up, this pending cost type values can be updated to the FROZEN cost type.

14

Inventory Transactions (Miscellaneous Receipt, Miscellaneous Issue, Account Alias Receipt, Account Alias Issue and Work order less Completion)

  This template will be very useful to perform any inventory transactions from an external system such as: recording the material movements from PLC, Scrap management, Work order less completion transactions for Flow manufacturing scenarios etc.

15

Purchase Receipt

  This template helps in loading the Purchase order receipt transaction.

16

OPM Item Creation

  Oracle process manufacturing Item creation can be automated using this XML template.

17

Bills of material Creation

  Bills of material – Primary, Alternate and Common BOM’s can be created using this template. The data loaded through this template includes: Assembly, components, revisions, reference designators and substitute components.

18

OPM Create Item LOT/ SUB LOT’s

  Oracle process manufacturing stores the LOT and SUBLOT information for an item and this template can be used for loading the LOT and SUBLOT information for the items created in OPM.

19

Bills of material update

  This template is used for updating the Bills of material data and appending components to an existing Bills of material structure.

20

OPM Create Immediate

  Initial stock for OPM items can be created using the Create Immediate transaction. This template helps you perform the create immediate transaction.

21

Routing Creation

  This template is used for creating Routing information. The data loaded includes: Routing Header, Operation details, Resource details, Alternate Resources and Item revision details.

22

OPM Transfer to warehouse

  This template can be used for recording all the material movement from one warehouse to another in OPM environment.

23

BOM with component item creation

  This template is used when you want to create new items from the component listing which are not currently available and then create the Bills of material assembly and component structure. This can minimize the time in creating the items and bills of material in separate loads.

24

OPM Adjust Immediate

  This template is used for making any material stock adjustments to the OPM items.
Oracle Distribution

1

Approved Supplier List

This template helps in loading Approved Supplier list. Approved supplier functionality ties suppliers with items and during purchase order approval if the ASL flag is turned on for an item, the system will not allow to buy from any unapproved suppliers.

2

SO Open Interface

  This template used the Order import process for loading sales orders in Entered, Booked or Closed status.

3

Requisitions Open Interface

  This template helps in creating Standard Purchase requisitions in the Oracle Purchasing module.

4

Process Order API

  This template is useful for loading Sales order header information. This also allows to perform the actions on the sales order headers.

 

5

Internal Requisition

  This template helps in creating the Internal requisitions in the Oracle Purchasing module.

6

Process Line API

  This template is useful for loading sales order lines and performing several actions on sales order lines.

7

PO Open Interface

  This template helps in creating Purchase orders.

8

Item Reservation

  This template helps in performing Sales order reservation process.

9

Receipts Open Interface

  This template helps in creating Purchase order receipts.

10

Move Orders

  This template helps in performing the move orders against the sales orders and without the reference to a sales order.

11

Price List create and update

  This template helps in creating Price list header and lines in Oracle Advanced Pricing module which is part of Oracle Order Management Suite.

12

Pick Confirmation

  Pick confirmation template helps you to allocate the inventory to the delivery or trip and perform the pick confirmation process in Oracle Shipping module.

13

Price List with Attributes

  This template helps in loading price list along with Pricing attribute information.

14

Ship Confirmation

  This template helps you to ship confirm the deliveries or trips in Oracle Shipping module. This can trigger the Interface trip stop process as well.

15

Price List Qualifiers

  This template helps in loading price list header and lines along with Price list qualifiers for the pricing lines.

16

Interface Trip Stop Closure

  This template can be used to closing the Trip Stops automatically in Oracle Shipping module.
Oracle Projects

1

 Create Resources

  This template helps in loading the Project Resources. Resources are also employees.

2

Project Budget conversion

  The Project Budgets can be loaded using this template.

3

Create Projects and Tasks

  This template helps in loading Project, Tasks and Key member information.

4

Project Billing

  The Project Billing information can be loaded using this template.

5

Create Agreement

  This template helps in loading the Project agreements.

6

Material issue to Project and task

  Any material inbound and outbound transactions can be recorded with project and task reference. This template is used for loading the material transactions with project and task information.

7

Cost Conversion

  The Project Cost can be loaded using this template.

8

Mass update to key members

  This template is used for updating the key members on the Project or the task.

9

Revenue Conversion

  The Project Revenue can be converted using this template.

 

 
Oracle CRM/Configurator

1

Installed Base

  This template helps in loading the Installed base information in Oracle CRM/ Service suite.

2

CZ Item, Item type, Property and prop values creation

  This template helps in creating the Oracle Configurator data such as: items, item types, item type property and item type property values.

3

Service Contracts

  This template helps in loading the Service Contracts in Oracle CRM/ Service suite.

4

CZ Item, Item type, Property and prop values update

  This template helps in updating the Oracle Configurator data such as: items, item types, item type property and item type property values.

5

Service Request Creation

  This template helps in loading the Service Requests in Oracle CRM/ Service suite.

6

CZ Non BOM Model Structure creation

  This template helps in creating the Oracle Configurator data such as: Non BOM model structure with Components linked with ATO/PTO items, Boolean Features and values, Text features and values, Option Features and values, Property and Property values assignment for each node.

7

Service Request Update

  This template helps in updating the Service Requests in Oracle CRM/ Service suite.

8

CZ Non BOM Model Structure update

 

This template helps in updating the Oracle Configurator data such as: Non BOM model structure with Components linked with ATO/PTO items, Boolean Features and values, Text features and values, Option Features and values, Property and Property values assignment for each node.
Oracle HRMS

1

 Create Location API

  This template helps to create HR Locations. Locations are used in creation of HR Organizations.

2

Create Previous Job Usage API

  This template  is used for creating the employee’s history details related to the previous job usage.

3

Create Organization API

  This template is used for creating the HR Organizations – Business Units, Legal Entities, Operating Units and Inventory Organizations.

4

Create Previous Job extra information

  This template  is used for creating the employee’s history details related to the previous job extra information.

5

Create Job API

  This template is used for creating the Jobs data into the Job Key Flexfield.

6

Create Employee History Summary

  All History related to the employee can be loaded using this template. This helps in loading all current employment (date tracked history).

7

Create Position API

  This template is used for creating the Position data into the Position Key Flexfield.

8

Create Employee Assignment

  This template helps in loading the Employee assignments to an organization, job, position, payroll, grade, supervisor assignments, salary basis etc.

9

Create Applicant

  This template is used for creating the applicant in Oracle HRMS module.

10

Employee Grade

  This template is used for creating and updating the Employee’s grade information.

11

Create Employee API

  This template is used for creating the employee master details: All Basic Information about employee.

12

Employee Grade Rate API

  This template is used for creating the pay scale rate for the grade.

13

Create Person Address API

  This template is used for creating the employee’s address (Home, Work and Any other details). This can also be used for updating the employee’s address whenever it changes.

14

Employee Grade rate values API

  This template helps in creating grade rate values.

15

Create Phone API

  This template is used for creating the employee’s phone (Home, Work and Any other details). This can also be used for updating the employee’s phone whenever it changes.

16

Employee Qualification

  This template is used to create the employee’s qualification.

17

Create Contract API

  This template is used for creating the employment contract.

18

Employee Qualification Type

  This is a quick code. This data can be loaded using

19

Create Previous Employer API

  This template is used for creating the employee’s history details related to the previous employers.

20

HR Appraisals

  This template helps to upload employee appraisal values and results.

21

Create Previous Job API

  This template  is used for creating the employee’s history details related to the previous jobs.

22

Competency Assignment

  Creating and updating competency profiles to the employees

Technical Specifications for the appLOAD suite

The document comes up with the following technical specifications for the appLOAD suite

  • Hardware and Software requirements
  • Platform Certifications
    • Operating System
    • Application Servers
  • System Architecture
  • Technical Architecture

Software Requirements

The following are the software pre-requisites for the appLOAD suite.

  • J2SE 5 – Java 2 Standard Edition 5 (JDK 1.5.x)
  • Oracle 10 g Enterprise Edition
  • J2EE Application Server

Hardware Requirements

Server Capacity varies with the quantum of data and transactions. An approximate server configuration would need the following

  • Should be a server hardware
  • Processor – Dual-Core Intel Core2 Duo processor (2 GHz and 2 MB Cache or higher)
  • Memory – 8 GB memory or Higher
  • Drives – Hard Disk Drive (HDD) 160 GB

Operating Systems

appLOAD suite can run on the following operating systems

  • Microsoft Windows Server Editions (XP / 2003 / VISTA)
  • Redhat Linux Enterprise Edition (4.x / 5.x) server
    • Certified by redHat  and runs well with the RHEL server versions as well.

Application Servers

appLOAD suite can run on the following J2EE Application/Web servers

  • Oracle Application server 10.1.3.x
  • JBoss Enterprise Web Server 4.x and 5.x
  • Apache Tomcat 5.5 and 6.0

 

technical architecture

Specifics of the Architecture

The Model-View-Controller (MVC) pattern, originally developed to support the Smalltalk language, supports a logical separation between presentation (view), application (controller) and the data (model) layers of the system.
As developers have grown familiar with the J2EE architecture, it has become increasingly apparent that access to the data, or persistent storage layer, through enterprise beans often incurs an unnecessary overhead. This overhead is a result of the transactional support provided through these components. In general, read-only access to data is much more common than transactional (create, update, delete) access.
As a result, non-transactional methods for access to the persistent data layer have been developed. These methods continue to provide the abstraction between applications and data, but don’t incur the overhead associated with an enterprise bean approach. Chainsys intended to develop frameworks that implement these data access methods to support access to the persistent data layer without an enterprise bean implementation.
This architecture is based upon the Struts MVC pattern and provides abstractions between the various tiers of the architecture. The dataflow is as follows:
Step 1 – Browser sends request to HTTP server via a URL that forwards the request to the Web application server. Based on the <servlet-mapping> entries in web.xml, the application server passes the requests having matching URL patterns, to the Struts Controller Servlet.
Step 2 – The Struts Controller Servlet matches the incoming request to a Struts Action. Further Struts Controller Servlet based on the action mappings passes control either directly to a JSP or instantiates the Action Form associated with the Struts Action. Struts populates the Action Form using the HTTP request parameters and stores the form bean in either HTTP request or HTTP session based on the form scope.
Step 3 – The Custom Action creates a Value Object (VO) and sets its attributes using data provided within the Action Form. It then calls Business Delegate method with VO as parameter. The Business Delegate serves to abstract the lookup of other resources and the methods accessed by the Custom Action
Step 4 – Business Delegate directs application business logic and calls other business objects to affect the application workflow.
Step 5 – Business Object requiring data access uses DAO Factory. A DAO Factory provides connections to a specific data store and implements retrieval of various DAOs.
Step 6 –DAO Implementation implements DAO Interface and provides interaction with Persistent Data (in some cases) via embedded SQL. For other sources of Persistent Data, this access may include EDI interfaces, XML exchanges or proprietary formats. The getDAO () method of the DAO Factory instantiates a new DAO Implementation object.
Step 7 –DAO object reference is passed to the Business Object.
Step 8 – Object calls CRUD operations on DAO Interface.
Step 9 – DAO Interface abstracts data store request to data store-specific DAO Implementation.
Step 10 – Data Access Object (DAO) accesses Persistent Data through JDBC or data store-specific interface.
Step 11 – Persistent Data is returned to DAO Implementation.
Step 12 – DAO Implementation converts retrieved data into list of VO’s.
Step 13 – DAO Interface passes VO list back.
Step 14 – Business Objects passes VO list back.
Step 15 – Business Delegate passes VO list back.
Step 16 – Custom Action places results embedded list of VOs into either the HTTP Request Object or HTTP Session Object depending on whether the information is session-persistent or transactional in nature. The Custom Action returns a “forward” tag attribute value to the Controller Servlet, indicating the next URI to which the flow should proceed.
Step 17 – Controller Servlet identifies and forwards request to the appropriate JSP for the above Custom Action.
Step 18 – JSP assembles HTML page and responds to browser with resulting page.

system architecture
  • 13 April
    Aqua 2 deployed sucessfully
    Chain-Sys sucessfully deploys AQUA 2 which is a design software developed for CST Industries in order to replace their existing design software AQUA 2000 and DLIQ.
  • 15 Dec
    Patel Integrated Logistics LTD - GOLIVE
    Oracle Application E Business Suite R12 - FINANCIALS implemented in a record time of 30 days.