Tables for the Profiles , Profile Options in Oracle fusion

 Here are the tables for the Profiles in Oracle fusion.

1. FND_PROFILE_OPTIONS_B

2. FND_PROFILE_OPTION_VALUES

3. FND_PROFILE_OPTIONS_TL

Tables for the Users , Roles and Data Access in Oracle fusion.

 Here are the tables used for the Users, Roles and Data Access in Oracle fusion.

1. PER_ROLES

2. PER_ROLES_DN_VL

3. PER_USERS

4. PER_USER_ROLES

5. PER_ROLE

6. FUN_USER_ROLE_DATA_ASGNMNTS

Tables for the Discount Lists in Oracle Fusion.

 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

Tables for the HCM Employee Information in Oracle Fusion.

 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

Tables for the Item Import in Oracle Fusion

 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'

Tables for the FA Interface in Oracle Fusion

 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'

Tables for the AP Invoice Interfaces and Errors in Oracle Cloud Fusion

 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

SELECT aii.invoice_num                                                        ,
       aii.invoice_id                                                         ,
   NULL                                                               ,
   fbu.bu_name                                                        ,
   aii.vendor_name                                                    ,
   aii_error.reject_lookup_code                                       ,
   aii.LOAD_REQUEST_ID                                                
  FROM ap_invoices_interface                                                  aii,
       ap_interface_rejections                                                aii_error,
       fun_all_business_units_v                                               fbu
 WHERE aii_error.parent_id                                                  = aii.invoice_id
   AND aii_error.LOAD_REQUEST_ID                                            = aii.LOAD_REQUEST_ID
   AND aii_error.parent_table                                               = 'AP_INVOICES_INTERFACE'
   AND aii.org_id                                                           = fbu.bu_id

Here is the sample query to pull the error report for the rejected records for the Invoices lines

AP Invoices Lines - Error Information

SELECT aii.invoice_num                                                        ,

   aii.invoice_id                                                     ,

   aili.line_number                                                   ,

   fbu.bu_name                                                        ,

   aii.vendor_name                                                    ,

   aili_err.reject_lookup_code                                        ,

   aii.LOAD_REQUEST_ID                                               

FROM AP_INVOICE_LINES_INTERFACE aili,

     AP_INVOICES_INTERFACE aii,

     AP_INTERFACE_REJECTIONS aili_err,

     fun_all_business_units_v fbu

WHERE aili_err.parent_table                                                = 'AP_INVOICE_LINES_INTERFACE'

  AND aii.invoice_id                                                       = aili.invoice_id

  AND aili_err.parent_id                                                   = aili.invoice_line_id

  AND aili_err.LOAD_REQUEST_ID                             = aii.LOAD_REQUEST_ID

  AND aili.org_id                                                          = fbu.bu_id

Sample SOAP Web service to apply the Oracle AR receipt

 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>

Sample Rest Web service Payload to create the Oracle AR Receipt

 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"

}

How to Import Project Misc Cost in Oracle Fusion using the FBDI

 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.

Tables for the PO Interface Errors in Oracle Fusion.

 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

    

Tables for the Contract Attachments in Oracle Fusion.

 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

 

Tables for Contracts in Oracle fusion.

 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

How to Import Journals with the Descriptive Flex Fields in Oracle fusion.

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"

Import Journals - GL Interface table in Oracle fusion.

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 

         

 

  

    

     

        

   

 

 

Tables for Supplier Bank Accounts in Oracle fusion

 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.

Supplier Banks Accounts - Uploading files into UCM - Oracle fusion

 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

Tables for the General Ledger - Journals (GL) in Oracle fusion.

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. 

• GL_JE_HEADERS

• GL_JE_LINES

• GL_CODE_COMBINATIONS

• GL_LEDGERS

• GL_JE_SOURCES

• GL_JE_CATEGORIES

• GL_JE_BATCHES

• GL_BALANCES 

• GL_PERIODS

• GL_INTERFACE : The interface table used for the GL Journal Import process.

Other modules related tables list here :

Tables for the PO(Purchase Order) in Oracle fusion

Tables for the Items in Oracle fusion

Tables for the Sales Contracts in Oracle fusion

Supplier Site Import Process Not Populating The Tax Registration Number (VAT Registration Number) - Oracle fusion Cloud

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 gets created successfully, however the VAT code and Tax Registration numbers are not getting populated anywhere. 

Also notice that in the interface table, these two columns are not getting populated with the FBDI data values( due to the FILLER options while loading into the interface tables - POZ_SUPPLIER_SITES_INT, which is getting skipped). Please see screenshot provided below.

Oracle has confirmed the same. Both Vat Code and Tax Registration Number are not supported functionality via the Supplier Site import process.  Please see below the Doc ID provided.

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."

 

SQL Query to fetch the Unposted GL Journal Batches in Oracle fusion.

 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