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:

1. Using File-Based Data Import (FBDI)
2. 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:

1. Download a copy of ReadyAPI, a software that will drive the updates to Customer Site Profiles
2. 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:

1. A custom Data Model that contains the SQL Script
2. A custom Report with CSV as the Output
3. 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

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