Here are the following base tables for the Discount Lists in the Oracle Fusion
qp_discount_lists_vl
qp_discount_list_items
qp_discount_lists_all_b
qp_discount_lists_tl
qp_matrix_dimensions_b
qp_matrix_dimensions_tl
qp_matrix_inherited_dimensions
qp_matrices
qp_pricing_terms_b
qp_matrix_rules
qp_pricing_terms_b
qp_pricing_terms_vl
This post will provide the details of the base tables that are useful for fetching the Employee Information in the Oracle fusion.
per_all_people_f
per_person_names_f_v
per_email_addresses
per_people_legislative_f
per_all_assignments_m
per_assignment_supervisors_f
hr_organization_units_f_tl
per_periods_of_service
per_all_people_f
hr_operating_units
hr_organization_units
per_jobs_f_tl
per_person_addresses_v
Here are the tables used for the Item Import in Oracle fusion.
1.egp_import_errors
2.egp_system_items_interface
And here is the table name to be referred for the Items Import to and additional where clause to be referred.
egp_import_errors.error_table_name = 'EGP_SYSTEM_ITEMS_INTERFACE'
egp_import_errors.process_status <> '7'
Here are the tables used for the FA(Fixed Asset) import in Oracle fusion :
1. FA_MASS_ADDITIONS
To check the records which are in error, check for the status
POSTING_STATUS = 'ERROR'
In this post, I will provide you the list of the Interface tables for the AP Invoices import in Oracle cloud fusion and also the corresponding AP Invoice Interface error table.
The Invoices rejection interface table will provide you the Status of the records and the corresponding rejection reason if there are any errors during the Import Process.
Here is the list of the AP Invoice Interface tables in the Oracle fusion :
1. AP_INVOICES_INTERFACE
2. AP_INVOICE_LINES_INTERFACE
These tables will hold the records to be imported into the Oracle fusion. Each of these tables has reject lookup code. And say if there are any rejections or errors in the Interface records, the error records will be stored in the rejections table.
Following is the error/rejection table which will hold the error or the rejected reason for the interface records.
AP_INTERFACE_REJECTIONS
Here is the sample query to pull the error report for the rejected records.
AP Invoice Header - Error Information
Here is the sample SOAP Web service for applying the AR Receipt.
WSDL : <Instance Name>/fscmService/StandardReceiptService?WSDL
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:typ="http://xmlns.oracle.com/apps/financials/receivables/receipts/shared/standardReceiptService/commonService/types/"
xmlns:com="http://xmlns.oracle.com/apps/financials/receivables/receipts/shared/standardReceiptService/commonService/"
xmlns:app="http://xmlns.oracle.com/apps/flex/financials/receivables/receipts/shared/standardReceiptService/commonService/ApplyReceiptDff/">
<soapenv:Header/>
<soapenv:Body>
<typ:createApplyReceipt>
<typ:applyReceipt>
<!--Optional:-->
<com:AmountApplied>100</com:AmountApplied>
<!--Optional:-->
<com:ReceiptId>11122</com:ReceiptId>
<com:CustomerTrxId><Invoice ID/Transaction ID></com:CustomerTrxId>
<!--Optional:-->
<com:ReceiptAmount>100</com:ReceiptAmount>
<!--Optional:-->
<com:ReceiptDate>2022-01-01</com:ReceiptDate>
<com:ApplicationDate>2022-01-01</com:ApplicationDate>
<!--Optional:-->
<com:AccountingDate>2022-01-01</com:AccountingDate>
</typ:applyReceipt>
</typ:createApplyReceipt>
</soapenv:Body>
</soapenv:Envelope>
Here is the sample Rest Web service payload to create the AR Receipt. Do modify the values accordingly and test them.
"<Instance URL>/fscmRestApi/resources/11.13.18.05/standardReceipts"
{
"ReceiptNumber" : "001-Receipt",
"ReceiptMethod" : "AMEX",
"BusinessUnit" : "USD BU",
"Amount" : "100",
"CustomerAccountNumber" : 1000,
"CustomerSite" : "S1",
"Currency" : "USD",
"RemittanceBankAccountNumber" : "1001100",
"AccountingDate" : "2022-02-10",
"ReceiptDate" : "2022-02-10",
"ConversionDate" : "2022-02-10",
"ConversionRateType" : "User",
"ConversionRate" : "1",
"MaturityDate" : "2022-02-10"
}
In this post, we will see how to import the Project Miscellaneous Cost using the Oracle FBDI templates in the fusion.
1. Download the latest FBDI template : ProjectUnprocessedMiscellaneousExpenditureItemImportTemplate.xlsm
2. Enter all the required columns and generate the data in CSV file format.
3. Following the CSV file is generated, Load the file into the staging tables using the process : "Load Interface File for Format"in the Scheduled process. Ensure the submitted process completes successfully.
4. After successful loading of the data, run the "Import Costs" process.
5. Select all the Mandatory input parameters and submit the process.
6. To check the output of the Import Cost Process, see the chld job "Import Cost : Generate Output Report" which has output details.
Here is the post which provides details about the PO Interface Error Table.
PO_INTERFACE_ERRORS
Following are the key columns which stores the error information in the interface table.
a. Error_Message
b. Error_Message_Name
c. Column_Name
d. Column_Value
e. Table_Name
Following are the tables involved in the Contract Attachments in Oracle Fusion.
1. FND_ATTACHED_DOCUMENTS
2. FND_DOCUMENTS_VL
Here is some more additional details for the WHERE clause which needed for the Contract Attachments.
a. FND_ATTACHED_DOCUMENTS.ENTITY_NAME = 'OKC_SUPPORTING_DOCS'
b. FND_ATTACHED_DOCUMENTS.PK1_VALUE = OKC_K_HEADERS_VL.ID
c. FND_ATTACHED_DOCUMETNS.DOCUMENT_ID =
FND_DOCUMENTS_VL.DOCUMENT_ID
In this post we will see the base tables for the Contracts in Oracle fusion.
These are some of the tables involved in the Contracts.
1. OKC_K_HEADERS_VL
2. OKC_CONTRACT_TYPES_TL
3. OKC_CONTACTS
4. OKC_K_PARTY_ROLES_B
5. OKC_K_REL_OBJS
In order to import the Import Journals in Oracle fusion along with the Descriptive Flexfields, make sure that you have the FBDI created for the Journals along with the attribute columns populated.
And during the Import Journal Process, just make sure you select the input parameter : Import Descriptive Flexfields either as "With validation" or "Without validation"
For the Import Journals - General Ledger, below is the interface table used in Oracle fusion.
GL_INTERFACE
In order to identify the records for the Journals which you load, always use the Group ID in the FBDI file that distinguishes the GL records.
Import the Journals using the Oracle FBDI, following are the steps needs to be followed :
1. Generate the GLInterface.csv file using the Oracle FBDI.
2. Load Interface File for Import
Import Process : Import Journals
Data File : Upload the GL Journal FBDI zip file to be uploaded.
3. Once the process completes successfully, run the next import process :
Import Journals
Following are the parameters to be used for the import
Data Access Set : Select the data access set
Source : Select the source as per what is mentioned in the FBDI file.
Ledger : Select the ledger to be imported.
Group ID : Identifier from the FBDI file.
Post Account Errors to Suspense : No
Create Summary Journals : No
Import Descriptive Flexfields : No
Here are the interface tables for the Supplier Bank Accounts in Oracle fusion :
1. IBY_TEMP_EXT_PAYEES
This table stores the Supplier Bank Payees data records.
2. IBY_TEMP_EXT_BANK_ACCTS
This table stores the Supplier Bank Accounts
3. IBY_TEMP_PMT_INSTR_USES
This table stores the Supplier Bank Account Assignments.
The Supplier Bank Accounts Import has the three different csv files
1. Supplier Bank Accounts
2. Supplier Payees
3. Supplier Accounts Assignments.
The zip files needs to be uploaded into the UCM into the following area for the Import process.
Here are the steps to be followed to upload the zip file into UCM.
1. Navigate to Tools -> File Import and Export
2. In the file import and export page, click on the '+' (upload) .
3. In the file field browse and select for the zip file.
4. From the Account list of values, select the fin$/payables$/import$
5. Click Save and Close
Here are the tables for the GL Ledger in Oracle fusion. This tables will store the details of the General Ledger Journals and related data information tables.
Other modules related tables list here :
Tables for the PO(Purchase Order) in Oracle fusion
Supplier Site FBDI has two columns VAT Code and Tax Registration Number. These two fields are not imported through the Supplier Site FBDI import process.
Supplier Site Import Process Not Populating The Tax Registration Number (VAT Registration Number) ( Doc ID 2034667.1 )Cause : "Populating the Tax Registration Number via the Supplier Site import process is currently not supported functionality."
Here is the SQL query to fetch the Unposted GL Journal batches in the Oracle fusion.This query might be helpful in case if you want to find how many Unposted GL Batches exists.
SELECT
GL.NAME "Ledger Name",
GLJB.NAME "Batch name" ,
GLJH.PERIOD_NAME,
GLJH.STATUS,
GLJH.JE_CATEGORY CATEGORY ,
GLJH.JE_SOURCE SOURCE ,
GLJH.CURRENCY_CODE CURRENCY
FROM
GL_JE_HEADERS GLJH,
GL_JE_BATCHES GLJB,
GL_LEDGERS GL
WHERE
GLJB.JE_BATCH_ID = GLJH.JE_BATCH_ID AND
GLJH.LEDGER_ID = GL.LEDGER_ID AND
GLJH.STATUS <> 'P'
ORDER BY GL.NAME,GLJB.NAME,GLJH.PERIOD_NAME
Here is the quick post to import the Purchase orders(PO) Receipts using the FBDI process.
1. Log in to the application and click on navigator icon and select Scheduled Process
2. Click on Schedule New Process. Search and select Load Interface File for Import Program.
3. Search and select the "Manage Receiving Transactions". Upload the zipped FBDI data files.
4. Once the program is submitted, click on done and check the status of submitted process in Process window. Make sure the status or submitted process id must be completed successfully.
5.Once the Load Interface File for Import Program is completed successfully, click on Schedule New Process. Search and select "Manage Receiving Transactions"
Ignore the Group ID parameters and Submit the process.
6. Once the program is complete, download the program output form the process window.
In this post, I will provide you the list of the Interface tables for the Suppliers import in Oracle cloud fusion and also the corresponding Supplier Interface error table.
The Supplier rejection interface table will provide you the Status of the records and the corresponding rejection reason if there are any errors during the Import Process.
Here is the list of the Supplier Interface tables in the Oracle fusion :
1. POZ_SUPPLIERS_INT
2. POZ_SUP_ADDRESSES_INT
3. POZ_SUPPLIER_SITES_INT
4. POZ_SITE_ASSIGNMENTS_INT
5. POZ_SUP_CONTACTS_INT
6. POZ_SUP_CONTACT_ADDRESSES_INT
These tables will hold the records to be imported into the Oracle fusion. Each of these tables have either "Status" or "Import_Status" column which provides the status of the records following the import process.
And say if there are any rejections or errors in the Interface records, the "Status" or the "Import_Status" column are updated with the "REJECTED" otherwise it will be updated with the "PROCESSED" status.
Following is the error/rejection table which will hold the error or the rejected reason for the interface records.
POZ_SUPPLIER_INT_REJECTIONS
How do you link these interface tables with this rejections table ? See below for the connecting these tables.
1. Suppliers Import :
SELECT psi.vendor_interface_id, psir.reject_lookup_code, psir.attribute
FROM POZ_SUPPLIERS_INT psi,
POZ_SUPPLIER_INT_REJECTIONS psir
WHERE psir.parent_id = psi.vendor_interface_id
AND psi.status = 'REJECTED'
AND psir.parent_table = 'POZ_SUPPLIERS_INT'
2. Suppliers Address Import
SELECT psai.address_interface_id, psir.reject_lookup_code, psir.attribute
FROM POZ_SUP_ADDRESSES_INT psai,
POZ_SUPPLIER_INT_REJECTIONS psir
WHERE psir.parent_id = psai.address_interface_id
AND psai.import_status = 'REJECTED'
AND psir.parent_table = 'POZ_SUP_ADDRESSES_INT'
3. Suppliers Sites Import
SELECT pssi.vendor_site_interface_id,psir.reject_lookup_code, psir.attribute
FROM POZ_SUPPLIER_SITES_INT pssi,
POZ_SUPPLIER_INT_REJECTIONS psir
WHERE psir.parent_id = pssi.vendor_site_interface_id
AND pssi.status = 'REJECTED'
AND psir.parent_table = 'POZ_SUPPLIER_SITES_INT'
4.Supplier Site Assignment Import
SELECT psai.assignment_interface_id, psir.reject_lookup_code, psir.attribute
FROM POZ_SITE_ASSIGNMENTS_INT psai,
POZ_SUPPLIER_INT_REJECTIONS psir
WHERE psir.parent_id = psai.assignment_interface_id
AND psai.status = 'REJECTED'
AND psir.parent_table = 'POZ_SITE_ASSIGNMENTS_INT'
5. Supplier Contacts Import
SELECT psci.contact_interface_id, psir.reject_lookup_code, psir.attribute
FROM POZ_SUP_CONTACTS_INT psci,
POZ_SUPPLIER_INT_REJECTIONS psir
WHERE psir.parent_id = psci.contact_interface_id
AND psci.import_status = 'REJECTED'
AND psir.parent_table = 'POZ_SUP_CONTACTS_INT'
6. Supplier Contact Address Import
SELECT pscai.contact_address_interface_id,psir.reject_lookup_code, psir.attribute
FROM POZ_SUP_CONTACT_ADDRESSES_INT pscai,
POZ_SUPPLIER_INT_REJECTIONS psir
WHERE psir.parent_id = pscai.contact_address_interface_id
AND pscai.import_status = 'REJECTED'
AND psir.parent_table = 'POZ_SUP_CONTACT_ADDRESSES_INT'
