The Oracle Prodigy: Updating Customer Site Profile Classes using SOAP Web Services in Oracle Fusion Applications

First off, there are two ways to upload/update Customer Site Profiles in Oracle Fusion Applications:

Using File-Based Data Import (FBDI)
Using Web Services such as SOAP and REST.

However, I would recommend using Web Services instead of FBDI because it's more straight-forward and simpler than FBDI. More details can be found in a separate article: When to Use FBDI or WebServices in Oracle Fusion Applications.

This article will talk about how to upload/update Customer Site Profile Classes in Oracle Fusion Applications using SOAP web services and in the future, I will also discuss how to do this using FBDI.

Before we begin, let's first discuss what a Customer Profile Class is. Customer profile Class is a master data setup object that holds attributes that are important to the interaction between the organization and customers in terms of their selling relationship. It holds details such as the Customer's creditworthiness, credit tolerance, credit limit, its default payment terms, statement configurations, and its assigned collector. A Customer Profile class is also used drive how the system will match receipts together with their invoices and defines how invoices are grouped together. For more in-depth information, check out a separate article: Defining a Customer Profile Class in Oracle Fusion Applications.

Below are some prerequisites before you can actually upload/update Customer Site Profiles using SOAP Web Services:

Download a copy of ReadyAPI, a software that will drive the updates to Customer Site Profiles
Prepare an "Extraction Report" that will generate the spreadsheet with the required Customer Data.

Since the first prerequisite is pretty straight forward, we'll move on to discuss the second prerequisite: Preparing the "Extraction Report". The script will be run from Business Intelligence Publisher (BIP), a reporting tool that we'll utilize to generate the actual data that will be fed to the Web Service.

To create the extraction report, you would have to create the following in order:

A custom Data Model that contains the SQL Script
A custom Report with CSV as the Output
A custom "dummy" Excel Template

Check out this article for an idea on how to create a custom Data Model (Customize the Data Models of seeded Oracle Fusion Applications reports). In the future, i'll create a step-by-step guide on how to create a custom data model, report and dummy excel template.

The SQL Script to be used in the Data Model is as follows:

select  ACCT.ACCOUNT_NUMBER
 , hps.party_site_number
 , ACCT.CUST_ACCOUNT_ID
 --, 'DEFAULT' SET_CODE
 , STID.SET_CODE
 , custprof.CLEARING_DAYS
 , custprof.CREDIT_BALANCE_STATEMENTS
 , custprof.CREDIT_CHECKING
 , custprof.CREDIT_HOLD
 , custprof.PROFILE_CLASS_ID
 , decode(custprof.DISCOUNT_TERMS, 'N', NULL, custprof.DISCOUNT_GRACE_DAYS) DISCOUNT_GRACE_DAYS
 , custprof.DISCOUNT_TERMS
 , 'Y' DUNNING_LETTERS
 , custprof.EFFECTIVE_START_DATE
 , custprof.EFFECTIVE_END_DATE
 , custprof.LAST_CREDIT_REVIEW_DATE
 , custprof.NEXT_CREDIT_REVIEW_DATE
 , custprof.OVERRIDE_TERMS
 , HP.PARTY_ID
 , custprof.PERCENT_COLLECTABLE
 , custprof.SEND_STATEMENTS
 , custprof.TOLERANCE
 , coll.NAME CollectorName
 , custprof.EXCHANGE_RATE_TYPE
 , RTM.name  PaymentTerms
 , custprof.AUTOCASH_HIERARCHY_ID
 , null ApplicationExceptionRuleSet
 , aar.Name AutoMatchRuleSet
 , custprof.AUTOCASH_HIERARCHY_ID_FOR_ADR
 , cyc.NAME StatementCycle
 , grp.name GroupingRule
 , custprof.CREDIT_CLASSIFICATION
 , custprof.ACCOUNT_STATUS
 , custprof.RISK_CODE
 , custprof.CREDIT_RATING
 , custprof.CONS_BILL_LEVEL
 , custprof.CONS_INV_TYPE
 , custprof.LOCKBOX_MATCHING_OPTION
 , replace(custprof.PREF_CONTACT_METHOD, 'EMAIL', 'E-Mail') PREF_CONTACT_METHOD
 , replace(custprof.TXN_DELIVERY_METHOD, 'EMAIL', 'E-Mail') TXN_DELIVERY_METHOD
 , custprof.PRINTING_OPTION_CODE
 , custprof.MATCH_BY_AUTOUPDATE_FLAG
 , custprof.AUTO_REC_INCL_DISPUTED_FLAG
 , custprof.CONS_INV_FLAG
 , NULL CREDIT_REVIEW_CYCLE
 , custprof.CREDIT_ANALYST_ID
 , custprof.CREDIT_LIMIT
 , custprof.CREDIT_CURRENCY_CODE
 , replace(custprof.STMT_DELIVERY_METHOD, 'EMAIL', 'E-Mail') STMT_DELIVERY_METHOD
 , custprof.ORDER_AMOUNT_LIMIT
from  HZ_PARTIES      hp
JOIN  HZ_PARTY_SITES     hps   on HPS.PARTY_ID     = HP.PARTY_ID
JOIN  HZ_CUST_ACCOUNTS   ACCT  on ACCT.PARTY_ID     = HP.PARTY_ID
JOIN  HZ_CUST_ACCT_SITES_ALL  ACTS  ON (ACTS.CUST_ACCOUNT_ID    = ACCT.CUST_ACCOUNT_ID
           AND  HPS.party_site_id     = ACTS.party_site_id)
JOIN  HZ_CUSTOMER_PROFILES_F   custprof ON (hp.party_id             = custprof.party_id
           AND  ACCT.CUST_ACCOUNT_ID    = custprof.CUST_ACCOUNT_ID)
join  HZ_CUST_SITE_USES_ALL  usg   ON (usg.SITE_USE_ID          = custprof.SITE_USE_ID
           and usg.CUST_ACCT_SITE_ID    = ACTS.CUST_ACCT_SITE_ID)
JOIN  HZ_CUST_PROFILE_CLASSES  class  ON (custprof.PROFILE_CLASS_ID = class.PROFILE_CLASS_ID)
JOIN  FND_SETID_SETS    STId  ON (STID.SET_ID       = ACTS.SET_ID)
join  AR_COLLECTORS    coll  on (coll.collector_id     = custprof.COLLECTOR_ID)
join  AR_STATEMENT_CYCLES   cyc   on (cyc.statement_cycle_id   = custprof.STATEMENT_CYCLE_ID)
join  RA_GROUPING_RULES   grp   on (custprof.GROUPING_RULE_ID = grp.GROUPING_RULE_ID)
JOIN  RA_TERMS      RTM   ON  (custprof.STANDARD_TERMS = rtm.TERM_ID)
left JOIN AR_AUTOMATCH_RULES  aar   on (custprof.AUTOMATCH_RULE_ID = aar.AUTOMATCH_RULE_ID)
where custprof.status      = 'A'
and  class.status       = 'A'
AND  ACTS.STATUS       = 'A'
and  hp.party_type       = 'ORGANIZATION'
AND  hp.status           = 'A'
and  custprof.site_use_id is not null
and  sysdate between custprof.EFFECTIVE_START_DATE and custprof.EFFECTIVE_END_DATE
and  class.NAME = 'DEFAULT'

This is the script I used to capture all the required information for the WebService Payload that will update the Customer Site Profile Classes. Feel free to change it according to your Organization's requirements.

Next, open up ReadyAPI and create a new SOAP project and provide the Web Services Description/Definition Language (WSDL) for your Instance. Next, load the Spreadsheet generated from the Report and assign it as a Data Source for the XML Payload below:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/receivables/customers/customerProfileService/types/" xmlns:cus="http://xmlns.oracle.com/apps/financials/receivables/customers/customerProfileService/" xmlns:cus1="http://xmlns.oracle.com/apps/financials/receivables/customerSetup/customerProfiles/model/flex/CustomerProfileDff/" xmlns:cus2="http://xmlns.oracle.com/apps/financials/receivables/customerSetup/customerProfiles/model/flex/CustomerProfileGdf/">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:updateCustomerProfileAsync>
         <typ:customerProfile>
            <cus:AccountNumber>${DataSource#AccountNumber}</cus:AccountNumber>
            <cus:SiteNumber>${DataSource#SiteNumber}</cus:SiteNumber>
            <cus:CustomerAccountId>${DataSource#CustomerAccountId}</cus:CustomerAccountId>
            <cus:ProfileClassName>${DataSource#ProfileClassName}</cus:ProfileClassName>
            <cus:ClearingDays/>
            <cus:CreditBalanceStatements>${DataSource#CreditBalanceStatements}</cus:CreditBalanceStatements>
            <cus:CreditChecking>${DataSource#CreditChecking}</cus:CreditChecking>
            <cus:CreditHold>${DataSource#CreditHold}</cus:CreditHold>
            <cus:CustomerAccountProfileId>${DataSource#CustomerAccountProfileId}</cus:CustomerAccountProfileId>
            <cus:DiscountGraceDays/>
            <cus:DiscountTerms>${DataSource#DiscountTerms}</cus:DiscountTerms>
            <cus:DunningLetters>${DataSource#DunningLetters}</cus:DunningLetters>
            <cus:EffectiveEndDate>${DataSource#EffectiveEndDate}</cus:EffectiveEndDate>
            <cus:EffectiveStartDate>${DataSource#EffectiveStartDate}</cus:EffectiveStartDate>
            <cus:LastCreditReviewDate/>
            <cus:NextCreditReviewDate/>
            <cus:OverrideTerms>${DataSource#OverrideTerms}</cus:OverrideTerms>
            <cus:PartyId>${DataSource#PartyId}</cus:PartyId>
            <cus:PercentCollectable/>
            <cus:SendStatements>${DataSource#SendStatements}</cus:SendStatements>
            <cus:Tolerance>${DataSource#Tolerance}</cus:Tolerance>
            <cus:CollectorName>${DataSource#CollectorName}</cus:CollectorName>
            <cus:ConversionRateType/>
            <cus:PaymentTerms>${DataSource#PaymentTerms}</cus:PaymentTerms>
            <cus:AutoCashRuleSet/>
            <cus:ApplicationExceptionRuleSet/>
            <cus:AutoMatchRuleSet/>
            <cus:ReminderRuleSet/>
            <cus:StatementCycle>${DataSource#StatementCycle}</cus:StatementCycle>
            <cus:GroupingRule>${DataSource#GroupingRule}</cus:GroupingRule>
            <cus:CreditClassificationValue/>
            <cus:AccountStatusValue/>
            <cus:RiskCodeValue/>
            <cus:CreditRatingValue/>
            <cus:BillLevel/>
            <cus:BillType/>
            <cus:MatchReceiptsBy/>
            <cus:PreferredContactMethod>${DataSource#PreferredContactMethod}</cus:PreferredContactMethod>
            <cus:PreferredDeliveryMethod>${DataSource#PreferredDeliveryMethod}</cus:PreferredDeliveryMethod>
            <cus:GenerateBill/>
            <cus:MatchByAutoupdate/>
            <cus:AutoReceiptsIncludeDisputedItems>${DataSource#AutoReceiptsIncludeDisputedItems}</cus:AutoReceiptsIncludeDisputedItems>
            <cus:ConsolidatedInvoice>${DataSource#ConsolidatedInvoice}</cus:ConsolidatedInvoice>
            <cus:CreditReviewCycleName/>
            <cus:CreditAnalystName/>
            <cus:CreditLimit/>
            <cus:CreditCurrencyCode/>
            <cus:StatementDeliveryMethod>${DataSource#StatementDeliveryMethod}</cus:StatementDeliveryMethod>
            <cus:OrderAmountLimit/>
         </typ:customerProfile>
      </typ:updateCustomerProfileAsync>
   </soapenv:Body>
</soapenv:Envelope>

Provide the Username and Password of the Application User you intend to use to update the Classes. Test out a few customers first one-by-one before doing a full bulk load to ensure you get the correct output.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Updating Customer Site Profile Classes using SOAP Web Services in Oracle Fusion Applications

3 comments: