Oracle Applications: 2022

Saturday 10 December 2022

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

Wednesday 23 November 2022

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'

Thursday 3 November 2022

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>

<com:AmountApplied>100</com:AmountApplied>

<com:ReceiptId>11122</com:ReceiptId>

<com:CustomerTrxId><Invoice ID/Transaction ID></com:CustomerTrxId>

<com:ReceiptAmount>100</com:ReceiptAmount>

<com:ReceiptDate>2022-01-01</com:ReceiptDate>

<com:ApplicationDate>2022-01-01</com:ApplicationDate>

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

}