Oracle P2P Data Extraction Guide
The Procure-to-Pay (P2P) process is the example used in this guide, however the ideas can be adapted for other cases
Identifying the Source System:
Recognize the originating system from which you'll be extracting data. This step is crucial for ensuring data integrity and consistency.
Environmental Settings:
Adjust and verify the specific environmental settings related to your system. This ensures that the extraction process is tailored to your unique operational environment.
Accessing Oracle Transactional BI:
To extract data, you'll need to access the Oracle Transactional Business Intelligence system:
Navigate to the OTBI dashboard.
Log in using your credentials.
Navigating Table Fields:
Oracle P2P with OTBI consists of various table fields. Here are some key tables you might encounter. Familiarize yourself with these tables, as they contain the data you'll be extracting:
Entity | Transactional tables | Master data tables |
Purchase requisitions | Por_requisition_lines_all, Por_requisition_headers_all | Egp_categories_tl, Po_line_types_tl, Per_users, Ap_system_parameters_all, Inv_units_of_measure_b, Inv_units_of_measure_tl, Gl_daily_rates |
Purchase orders | Po_headers_all | Xle_entity_profiles, Hr_organization_units_f_tl, Poz_suppliers, Hz_parties, Fnd_territories_tl, Poz_supplier_sites_v |
Purchase order items | Po_lines_all, Por_requisition_lines_all, Rcv_shipment_lines, Po_line_locations_all, Po_headers_all | Egp_categories_tl, Ap_system_parameters_all, Per_location_details_f, Per_location_details_f_tl, Inv_units_of_measure_b, Inv_units_of_measure_tl |
Goods receipt | Rcv_shipment_lines | |
Invoice | Ap_invoices_all | Xle_entity_profiles,, Ap_terms_lines, Iby_payment_methods_tl, Ap_terms_tl, Fnd_lookups |
Invoice item | Ap_invoice_lines_all, Ap_invoices_all | Hr_organization_units_f_tl, Inv_units_of_measure_b, Inv_units_of_measure_tl, Ap_system_parameters_all, Gl_daily_rates |
Accounting documents | Ap_invoices_all | |
Payments | Ap_invoice_payments_all, Ap_payment_schedules_all |
Use these queries for each table in the Oracle Transactional BI to create a data model. The data model can then be converted into a full report, that can be extracted to a csv/excel file.
Table Name | SQL | Application and Docs Link |
AP_HOLDS_ALL | SELECT * from AP_HOLDS_ALL | |
AP_SYSTEM_PARAMETERS_ALL | select * from AP_SYSTEM_PARAMETERS_ALL | |
AP_TERMS_LINES | select * from AP_TERMS_LINES | |
AP_TERMS_TL | select * from AP_TERMS_TL | |
EGP_CATEGORIES_TL | select * from EGP_CATEGORIES_TL | |
FND_LOOKUPS | SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, DISPLAY_SEQUENCE,CHANGE_SINCE_LAST_REFRESH, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,TAG FROM FND_LOOKUP_VALUES_VL WHERE VIEW_APPLICATION_ID = 0 AND SET_ID = 0 | Common features within Oracle Applications Cloud (Middleware extensions views) |
FND_TERRITORIES_TL | Select * from FND_TERRITORIES_TL | Common features within Oracle Applications Cloud (Middleware extensions) |
GL_DAILY_RATES | Select * from GL_DAILY_RATES | |
HR_ORGANIZATION_UNIT_F_TL | Select * from HR_ORGANIZATION_UNITS_F_TL | |
HZ_PARTIES | Select * from HZ_PARTIES | |
IBY_PAYMENT_METHODS_TL | Select * from IBY_PAYMENT_METHODS_TL | |
INV_UNITS_OF_MEASURE_B | Select * from INV_UNITS_OF_MEASURE_B | |
INV_UNITS_OF_MEASURE_TL | Select * from INV_UNITS_OF_MEASURE_TL | |
PER_LOCATION_DETAILS_F | Select * from PER_LOCATION_DETAILS_F | |
PER_LOCATION_DETAILS_F_TL | Select * from PER_LOCATION_DETAILS_F_TL | |
PER_USERS | Select * from PER_USERS | |
PO_LINE_TYPES_TL | Select * from PO_LINE_TYPES_TL | |
POZ_SUPPLIER_SITES_V | Select * from POZ_SUPPLIER_SITES_V | |
POZ_SUPPLIERS | Select * from POZ_SUPPLIERS | |
XLE_ENTITY_PROFILES | Select * from XLE_ENTITY_PROFILES | |
AP_INV_APRVL_HIST_ALL | Select * from AP_INV_APRVL_HIST_ALL | |
AP_INVOICE_LINES_ALL | Select * from AP_INVOICE_LINES_ALL | |
AP_INVOICE_PAYMENTS_ALL | Select * from AP_INVOICE_PAYMENTS_ALL | |
AP_INVOICES_ALL | Select * from AP_INVOICES_ALL | |
AP_PAYMENT_SCHEDULES_ALL | Select * from AP_PAYMENT_SCHEDULES_ALL | |
PO_ACTION_HISTORY | Select * from PO_ACTION_HISTORY | |
PO_HEADERS_ALL | Select * from PO_HEADERS_ALL | |
PO_LINE_LOCATIONS_ALL | Select * from PO_LINE_LOCATIONS_ALL | |
PO_LINES_ALL | Select * from PO_LINES_ALL | |
POR_REQUISITION_HEADERS_ALL | Select * from POR_REQUISITION_HEADERS_ALL | |
POR_REQUISITION_LINES_ALL | Select * from POR_REQUISITION_LINES_ALL | |
RCV_SHIPMENT_LINES | Select * from RCV_SHIPMENT_LINES | |
RCV_TRANSACTIONS | Select * from RCV_TRANSACTIONS |
Extracting Activity Data
Activity information can be created from transactional tables. Activities were created from transactional tables whenever there was enough information available (i.e. event_end and user information). Each of these activities has a specific DBT model where they're being created and the names of the models are derived from the activity names.
Entity | Activity | Activity codes | Transactional/ Historical table | Master data table |
Purchase requisitions | Create Purchase Requisition | CREATE_PR | Por_requisition_lines_all | Setup_users |
Purchase orders | Create Purchase Order | CREATE_PO | Po_headers_all | Setup_users |
Purchase order items | Create Purchase Order Item | CREATE_PO_ITEM | Po_lines_all | Setup_users |
Purchase order items | Cancel Purchase Order Item | null | Po_lines_all | Per_users |
Purchase order items | Close Purchase Order Item | null | Po_lines_all | Per_users |
Invoices | Create Invoice | CREATE_INVOICE | Ap_invoices_all | Setup_users |
Invoices | Cancel Invoice | null | Ap_invoices_all | Setup_users |
Invoice items | Create Invoice Item | CREATE_INVOICE_ITEM | Ap_invoice_lines_all | Setup_users |
Payments | Execute Outgoing Payment | CREATE_OUTGOING_PAYMENT | Ap_invoice_payments_all | Setup_users |
Payments | Void Outgoing Payment | null | Ap_invoice_payments_all | Setup_users |