OracleJourney2Cloud: July 2021

Friday, 30 July 2021

How to get the UserKey Values for the Attachment Upload

Scope:

The scope of this document is to tell you how to identify the user key values when uploading the attachment to Fusion SaaS using the web services

Requirement:

Here the requirement is to upload the attachment to Fusion SaaS and we need to identify the UserKey Values in the payload for this.

How To:

We use the below URL for uploading the attachment.

https://<hostname>.<domainname>/publicFinancialCommonErpIntegration/ ErpObjectAttachmentService?WSDL

Below is the request payload

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/" xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">

<soapenv:Header/>

<soapenv:Body>

<typ:uploadAttachment>

<typ:entityName>?</typ:entityName>

<typ:categoryName>?</typ:categoryName>

<typ:allowDuplicate>?</typ:allowDuplicate>

<typ:attachmentRows>

<erp:UserKeyA>?</erp:UserKeyA>

<erp:UserKeyB>?</erp:UserKeyB>

<erp:UserKeyC>?</erp:UserKeyC>

<erp:UserKeyD>?</erp:UserKeyD>

<erp:UserKeyE>?</erp:UserKeyE>

<erp:AttachmentType>?</erp:AttachmentType>

<erp:Title>?</erp:Title>

<erp:Content>?</erp:Content>

</typ:attachmentRows>

</typ:uploadAttachment>

</soapenv:Body>

</soapenv:Envelope>

In the above

Entity Name of the attachment

Navigate to “Setup & Maintenance UI > Set up Tasks > Manage Attachment Entities or Manage Attachment Categories” to get valid entity names. For example, AP_INVOICES_ALL, AR_BATCHES_ALL, PO_CONFIG_COMPONENTS, etc. are possible entity names.

Category Name of the attachment

Navigate to “Setup & Maintenance UI > Set up Tasks > Manage Attachment Entities or Manage Attachment Categories” to get valid category names. For example, AP_SUPPORTING_DOC, PAYMENT_SUPPORTING_DOC, SCANNED_INVOICE_IMAGE, etc. are possible category names.

Option to allow duplicate attachments

Values: Yes or No

And for UserKeys, we use the below

select * from FUN_ERP_ATTACHMENT_CONTEXTS where ERP_FAMILY_CODE='FIN'

For Entity AP_INVOICES_ALL, we get the below SQL statement

"SELECT API.INVOICE_ID FROM AP_INVOICES_ALL API, FUN_ALL_BUSINESS_UNITS_V BU, POZ_SUPPLIERS PZ WHERE API.ORG_ID = BU.BU_ID AND BU.BU_NAME = :1 AND API. INVOICE_NUM=:2 AND API.VENDOR_ID=PZ.VENDOR_ID AND PZ.SEGMENT1=:3"

The below bind parameter define the user key elements and other user key elements passed as #NULL

Create a user with Access to UCM

Scope:

So, in this post, we will see how to grant access to users with UCM.

Requirement:

The requirement here is to create a user with UCM access and Custome role and using it with data source creation.

How To:

Below information is taken from the Oracle Support document

How to create a Custom UCM Datasource To Retrieve The Text Attachments for PDF ? (Doc ID 2643214.1)

1) Creating Custom User with access to UCM

i) Create a user category with password expiry as 9999 days. This would ensure that the password need not be reset frequently and wouldn't break the functionality.

ii) Create a new Role and add Attachment Read Role. This would grant the user access to read the attachments

iii) Create a new user with the above user category and custom role and verify the above properties are inherited by the user.

2) Creating custom UCM Datasource

i) Navigate to Administration→Data Sources→Content Server

ii) Create a new Datasource with the same URL as the provisioned UCM Datasource (FA_UCM_PROVISIONED) and with the new user credentials created in the above steps. Grant the required roles as shown in the screenshot below:

iii) Use this new data source in all the UCM related datasets

Tuesday, 27 July 2021

Assign the external bank account to the Customer using SOAP

Scope:

In this post, we see how to assign the already created external bank account to the customer account level or customer site level. Please see here to know how to create an external bank account https://oraclejourney2cloud.blogspot.com/2021/07/create-external-bank-account-in-oracle.html

Requirement:

Here the requirement is to assign the external bank account to the customer.

How To:

We use the below web service URL to assign the already create external bank account to the customer level.

https://<<hostname>>/fscmService/PayerDetailServiceV2?WSDL

<soapenv:Header/>

<soapenv:Body>

<typ:processPayerBankAccount>

<typ:payer>

<ext:PaymentFunction>CUSTOMER_PAYMENT</ext:PaymentFunction>

<ext:PartyId>300000005880032</ext:PartyId>

<ext:CustomerAccountId>300000005880034</ext:CustomerAccountId>

<ext:DefaultPaymentMethodCode>BANK_ACCT_XFER</ext:DefaultPaymentMethodCode>

</typ:payer>

<typ:bankAccount>

<ban:ExternalBankAccountId>300000014746168</ban:ExternalBankAccountId>

</typ:bankAccount>

</typ:processPayerBankAccount>

</soapenv:Body>

</soapenv:Envelope>

Assign ExternalBankAccount to the Supplier Site

Scope:

In this post, we see how to assign an external bank account to the supplier site. Prerequisite for this external bank account already created. Please see here to know how to create an external bank account https://oraclejourney2cloud.blogspot.com/2021/07/create-external-bank-account-in-oracle.html

Requirement:

We need to assign the already created external bank account to the supplier site level

How to:

In this example, we have taken an existing external bank account that was created for the supplier level.

Note: there is no option to create an external bank account at the site level directly. First, it has to be created at the supplier (create +owner) and after that at the site is assigned with Instrument API.

A. Using bellow queries to identify data for Instrument assignment Rest API.

1. Identify the Payee ID (supplier ID)

select * from hz_parties where party_name = ‘Dummy supplier';

Payee_id = 12444

2. Identify the Supplier site ID

Select * from hz_party_sites where party_id = '12444' and party_site_name = ‘Dummy site name’;

Payee_site_id = 172725

3. Identify the External bank account ID

select * from iby_ext_bank_accounts where bank_account_name = ‘Dummy bank account';

Ext_bank_account_id = 1

4. Identify the external payee id for this supplier site

select * from IBY_EXTERNAL_PAYEES_ALL where Payee_party_id = 12444 and party_site_id = '172725';

Ext_payee_id = 300100027626399

B. Rest Api Instrument assignment

Api: https://<hostname>//fscmRestApi/resources/11.13.18.05/instrumentAssignments

Payload Used:

"PaymentPartyId": 300100027626399,

"PaymentInstrumentId": 1,

"PaymentFlow": "DISBURSEMENTS",

"PaymentInstrumentType": "BANKACCOUNT",

"Intent": "Supplier",

"StartDate": "2020-10-29"

}

Where

PaymentPartyId = Ext_payee_id for supplier site.

PaymentInstrumentId = External bank account ID.

Response:

{

"PaymentInstrumentAssignmentId": 300100234993900,

"PaymentPartyId": 300100027626399,

"PaymentInstrumentId": 1,

"PaymentFlow": "DISBURSEMENTS",

"PaymentInstrumentType": "BANKACCOUNT",

"PrimaryIndicator": "Y",

"StartDate": "2020-10-29",

"EndDate": "4712-12-31",

"MaskedBankAccountNumber": "XXXX4147",

"MaskedCreditCardNumber": null,

"VendorId": null,

"PersonId": null,

"Intent": "Supplier",

}

Create/Update an External Bank Account in Oracle Fusion Cloud

Scope:

In this post, we see how to create or update the external bank account in Oracle Fusion Cloud Application using REST service

Requirement:

The requirement here is to integrate the external bank account to Oracle Fusion Cloud.

How to:

for creating External Bank Account :

- Go in Task Manage Cash Management Profile

- profile option CE_USE_EXISTING_BANK_BRANCH enabled at both level site and Rest user level ( Yes)

- then try again to call the External Bank Account creation REST API using the below payload

POST https://<<hostname>>/fscmRestApi/resources/11.13.18.05/externalBankAccounts

Payload:

{

"BankAccountNumber": "12398765",

"CountryCode": "US",

"BankIdentifier": "300000003692821",

"BankBranchIdentifier": "300000003692825",

"CurrencyCode": "USD",

"AccountType": "SAVINGS",

"AllowInternationalPaymentIndicator": "Y",

"Intent": "Supplier",

"PartyId": 300000003692792,

"BankAccountName": "DDP_01",

"accountOwners": [

{

"AccountOwnerPartyIdentifier": 300000003692792,

"Intent": "Supplier"

}

]

}

Notes:

-- Get the "AccountOwnerPartyIdentifier" using the party ID of the supplier

You can update an external bank account- PATCH method

https://<<hostname>>/fscmRestApi/resources/11.13.18.05/externalBankAccounts/<<ext bank account id which is already created>>

Sample Payload where the name to update

{

"BankAccountName": "TEST API",

"AlternateAccountName": "REST API",

"Intent": "Supplier",

"PartyId": 300100126889527

}

Note: we can also create an external bank account using the SOAP Service as below

https://<<hostname>>/fscmService/ExternalBankAccountServiceV2?WEDL

Create an AR Invoice using REST Service

Scope:

This post will help you to know how to create an AR Invoice using the REST service. This invoice will have a Tax line and Distribution Line for the Invoice lines

Requirement:

The requirement here is to create an AR Invoice using the REST service.

How To Do:

We can use the REST URL and Payload as below.

REST action: POST

https://<<hostname>>/fscmRestApi/resources/11.13.18.05/receivablesInvoices

Payload:

The values in the payload are samples and please change them accordingly as per your instances

Note: When Tax distribution is passed in the payload, you'll have to pass "DetailedTaxLineNumber".

SAMPLE PAYLOAD :

================

{

"BillingDate" : "2021-07-27",

"BillToCustomerNumber" : "1577300",

"BillToSite":"TCA",

"DueDate":"2021-03-20",

"BusinessUnit" : "Vision Operations",

"PaymentTerms" : "30 Net",

"InvoiceCurrencyCode" : "USD",

"TransactionDate" : "2021-03-03",

"AccountingDate" : "2021-03-03",

"TransactionSource" : "Manual",

"TransactionType" : "Invoice",

"receivablesInvoiceLines":[

{

"Description":"AM REST API TEST",

"LineNumber":1,

"Quantity":10,

"UnitSellingPrice":1,

"LineAmount" : "10.0",

"TaxClassificationCode":"VAT20",

"receivablesInvoiceLineTaxLines": [

{

"TaxAmount":"2",

"TaxRegimeCode":"FUS_STCC_REGIME-UES",

"TaxRateCode":"VAT20"

}

]

}

"receivablesInvoiceDistributions" : [

{

"InvoiceLineNumber" : 1,

"AccountClass" : "Receivable",

"AccountCombination" : "01-000-1212-0000-000",

"Percent" : 100

{

"InvoiceLineNumber" : 1,

"AccountClass" : "Revenue",

"AccountCombination" : "01-000-4110-0000-000",

"Percent" : 100

{

"InvoiceLineNumber" : 1,

"AccountClass" : "Tax",

"AccountCombination" : "01-000-7610-0000-000",

"DetailedTaxLineNumber": 1,

"Percent" : 100

}

]

}

I hope this helps.

Delete a column value using Customer FBDI

Scope:

This post will help you to know how to remove a column value which is already existing in the database using the Customer FBDI

Requirement:

In many cases, we import or update the data into Oracle Fusion cloud ERP instances using the FBDI approach. So, here we may need to update the existing column value to NULL (means removing the column value) as per the requirement. This can be achieved using a particular process.

How To Do:

The correct symbol in the column to be used when you want to update the value of a field to blank is "!".So, instead of #NULL please use "!" and it should work. The #NULL value is to be used in the WEBSERVICE input payload. #NULL will not work in cases of FBDI. Keep in mind that if you upload fresh data (creation step) the system will see "!" as a normal character and will upload it as is. It works as a nullifier when you have some data in that specific field. "!" cannot be used to update Date columns to NULL.

I hope this helps.