Business Challenge
In Oracle Fusion Subscription Management, performing mass date changes on active subscriptions—such as updating start dates for specific product lines—can be complex. Standard processes often require manual amendments, updates, and activations, which are time-consuming and error-prone for large volumes. Additionally, handling parameters like new dates, periods, durations, and targeted line numbers dynamically is not natively supported in scheduled Groovy functions.
This post demonstrates a real-world implementation using the technique outlined in our previous article: Create Parametrized Custom ESS Jobs Running Scheduled Custom Groovy Function. By leveraging a parametrized custom ESS job, we enable automated mass date changes on subscriptions while overcoming Oracle’s parameter escaping limitations through a generic REST connection with dynamic parameters.
Our Solution
The solution utilizes a custom Groovy-based ESS job that accepts parameters for an active subscription number, new start date, period type, duration, and a list of product line numbers to update. It performs the following steps:
- Fetches subscription and product details using a REST query with parameters to minimize payload (e.g., only relevant fields like SubscriptionProductPuid and StartDate).
- Amends the specified product lines using the AMEND action, copying adjustments, charges, and custom objects.
- Updates the draft-status products and covered levels with the new start date, period, and duration, while generating billing schedules.
- Updates the subscription header to generate billing schedules and sets flex fields as needed.
- Activates the subscription at the header level.
- Updates bill lines by setting the InterfacedFlag to true for non-interfaced lines with past or current DateToInterface.
This approach showcases advanced Groovy techniques in ESS jobs, including parameterized queries, action calls (e.g., amend and activate), efficient updates, and payload optimization for performance.
Generic Web Service Connection Setup
To handle complex URLs with parameters (e.g., avoiding escaping issues with #REL_URL##), we use a generic REST web service connection named “LocalCrmRestWithParams”. This connection is configured as follows:
- URL:. (internal Oracle Outsourcing URL with placeholders for #REL_URL##, #C1# to #C10#).
- Authentication Scheme: Propagate User Identity using SAML.
- Methods: GET (with Method Name: GET, Format: JSON), PUT (Schema URL or Code Sample), POST, PATCH, DELETE.
The screenshot below illustrates the setup in the Edit REST Web Service Connection interface:
This setup allows dynamic query parameters to be passed without Oracle’s default escaping, enabling URLs like crmRestApi/resources/latest/subscriptions/10139661/child/products?q=LineNumber in (5,6)&limit=500&fields=SubscriptionProductPuid;billLines:BillLineId,BillLinePuid,DateToInterface,InterfacedFlag&onlyData=true.
Groovy Code: Mass Date Change Implementation
The custom Groovy function is defined as a row-level function in your custom object, with return type String and invokable by external systems. It retrieves parameters from the ESS request context (as per our parametrized technique), processes the subscription, and logs the output for auditing.
Copy the full code below:
def payload = [];
def queryParams = [];
def today = new Date();
def output = new StringBuilder();
output.append("\n");
def conn = adf.webServices.LocalCrmRestWithParams;
def httpHeaders = ['Rest-Framework-Version':'9'];
def httpHeadersContentType = ['Rest-Framework-Version':'9', 'Content-Type':'application/vnd.oracle.adf.action+json'];
conn.requestHTTPHeaders = httpHeaders;
def sessionId = adf.util.getSession().toString().split('/')[0]
def json = adf.util.runSqlToJson("""
select fsa.attribute_value requestid,
erp.name param_name,
erp.value param_value
from fnd_session_attributes fsa,
ess_request_property erp
where fsa.session_id='${sessionId}'
and fsa.attribute_name='JOB_REQUEST_ID'
and to_number(fsa.attribute_value) = erp.requestid
and erp.name like 'submit.argument%'
order by erp.name
""");
def res = (List)adf.util.parseJson(json)
def subscriptionNumber = res[2]["PARAM_VALUE"];
def startDate = res[3]["PARAM_VALUE"];
def productLineNumbers = res[4]["PARAM_VALUE"];
def periodCode = res[6]["PARAM_VALUE"];
def duration = res[7]["PARAM_VALUE"];
output.append("# Parameters\n");
output.append("Subscription Number = " + subscriptionNumber + "\n");
output.append("New Start Date = " + startDate + "\n");
output.append("Subscription Line Numbers = " + productLineNumbers + "\n");
output.append("Is Credit And Invoice = " + res[5]["PARAM_VALUE"] + "\n");
output.append("Period Code = " + res[6]["PARAM_VALUE"] + "\n");
output.append("Duration = " + res[7]["PARAM_VALUE"] + "\n");
output.append("# Main Process: Started\n");
//
// ## Fetch Subscription and Products Information
//
output.append("## Fetch Subscription and Products Information: Started\n");
queryParams = ["q": "LineNumber in (" + productLineNumbers + ")", "onlyData": "true", "fields":"SubscriptionProductPuid,StartDate,LineNumber"];
conn.dynamicQueryParams = queryParams;
def resProducts;
try {
resProducts = conn.GET("subscriptions",subscriptionNumber,"child","products","","","","","","","");
output.append("### Response: resProducts: \n" + resProducts.toString() + "\n");
if (!resProducts.items.size()) throw new oracle.jbo.ValidationException("Products not found. Subscription Name: " + subscriptionNumber + ". Product Line Number in:" + productLineNumbers + ".");
resProducts
} catch (e) {
output.append(e.toString());
}
def products = resProducts.items;
output.append("## Fetch Subscription and Products Information: Finished\n");
//
// ## Amend Products
//
output.append("## Amend Products: Started\n");
conn.requestHTTPHeaders = httpHeadersContentType;
conn.dynamicQueryParams = null;
for (product in products) {
payload = [
"lineNumber": product.LineNumber,
"amendReason": "DATE_CHANGE",
"copyAdjustments": "Y",
"copyCharges": "Y",
"copyCustomChildObjects" :"Y",
"copyOneTimeCharges": "Y",
"amendEffectiveDate": product.StartDate,
"amendCreditMethod": "ORA_PRORATE_WITH_CREDIT"
];
output.append("### Processing line number: " + product.LineNumber + "\n");
output.append("### Payload: \n" + payload + "\n");
try {
def postAmendResult = conn.POST("subscriptions",subscriptionNumber,"child","products", product.SubscriptionProductPuid,"action","amend","","","","",payload);
output.append("### Response: postAmendResult: \n" + postAmendResult.toString() + "\n");
} catch (e) {
output.append(e.toString()+ "\n");
}
}
output.append("## Amend Products: Finished\n");
//
// ## Updating Products - ORA_DRAFT status
//
output.append("## Updating Products - ORA_DRAFT status: Started\n");
output.append("### Fetching products in draft status\n");
queryParams = ["q": "Status='ORA_DRAFT'", "fields":"LineNumber,SubscriptionProductPuid;coveredLevels:CoveredLevelId,CoveredLevelPuid", "limit": "500", "onlyData": "true"];
conn.dynamicQueryParams = queryParams;
def resProductsDraft;
try {
resProductsDraft = conn.GET("subscriptions",subscriptionNumber,"child","products","","","","","","","");
output.append("### Response: resProductsDraft: \n" + resProductsDraft.toString() + "\n");
if (!resProductsDraft.items.size()) throw new oracle.jbo.ValidationException("Products in Draft Status not found. Subscription Number: " + subscriptionNumber + ".");
} catch (e) {
output.append(e.toString() + "\n");
}
def productsDraft = resProductsDraft.items;
conn.requestHTTPHeaders = httpHeaders;
conn.dynamicQueryParams = null;
payload = [
"products": [
"items": []
]
];
for (productDraft in productsDraft) {
def payloadProductDraft = [
"LineNumber": productDraft.LineNumber,
"SubscriptionProductPuid": productDraft.SubscriptionProductPuid,
"StartDate": startDate,
"Period": periodCode,
"Duration": duration,
"GenerateBillingSchedule": "Y",
"coveredLevels": [
"items": []
]
];
for (coveredLevel in productDraft.coveredLevels.items) {
payloadProductDraft.coveredLevels.items.add(
[
"CoveredLevelId": coveredLevel.CoveredLevelId,
"CoveredLevelPuid": coveredLevel.CoveredLevelPuid,
"StartDate": startDate,
"Period": periodCode,
"Duration": duration,
"GenerateBillingSchedule": "Y"
]
);
}
payload.products.items.add(payloadProductDraft);
}
try {
output.append("### Updating Subscription\n");
output.append("### Payload: \n" + payload + "\n");
def resPatchSubscription = conn.PATCH("subscriptions",subscriptionNumber,"","","","","","","","","",payload);
output.append("### Response: resPatchSubscription: \n" + resPatchSubscription.toString() + "\n");
} catch (e) {
output.append(e.toString() + "\n");
}
output.append("## Updating Products - ORA_DRAFT status: Finished\n");
//
// ## Updating Subscription - GenerateBillingSchedule field
//
output.append("## Updating Subscription - GenerateBillingSchedule field: Started\n");
conn.requestHTTPHeaders = httpHeaders;
payload = [
"GenerateBillingSchedule": "Y",
"flexFields": [
"items": [
[
"contractCreditCheckStatus": "PASS",
"__FLEX_Context": null,
"__FLEX_Context_DisplayValue": null
]
]
]
];
output.append("## Payload: \n" + payload + "\n");
try {
def patchGenerateBillingSchedule = conn.PATCH("subscriptions",subscriptionNumber,"","","","","","","","","",payload);
output.append("### Response: patchGenerateBillingSchedule: \n" + patchGenerateBillingSchedule.toString() + "\n");
} catch (e) {
output.append(e.toString() + "\n");
}
output.append("## Updating Subscription - GenerateBillingSchedule field: Finished\n");
//
// ## Executing activate action on header level
//
output.append("## Executing activate action on header level: Started\n");
output.append("### Processing Subscription: " + subscriptionNumber + "\n");
payload = [];
conn.requestHTTPHeaders = httpHeadersContentType;
try {
def postSubscriptionActivate = conn.POST("subscriptions",subscriptionNumber,"action","activate","","","","","","","",payload);
output.append("## Response: postSubscriptionActivate: \n" + postSubscriptionActivate.toString() + "\n");
} catch (e) {
output.append(e.toString() + "\n");
}
output.append("## Executing activate action on header level: Finished\n");
//
// ## Updating product lines and billlines - InterfacedFlag
//
output.append("## Updating product lines and billlines - InterfacedFlag: Started\n");
def allLinesToBeUpdated = products + productsDraft;
def lineNumbersStr = allLinesToBeUpdated.collect { it.LineNumber }.join(",")
conn.requestHTTPHeaders = httpHeaders;
conn.dynamicQueryParams = ["q": "LineNumber in (" + lineNumbersStr + ")", "fields":"LineNumber,SubscriptionProductPuid;billLines:BillLineId,BillLinePuid,DateToInterface,InterfacedFlag", "limit": "500", "onlyData": "true"];
payload = [
"products": [
"items": []
]
];
def resProductsBillLines;
output.append("### Fetching product lines and billlines\n");
try {
resProductsBillLines = conn.GET("subscriptions",subscriptionNumber,"child","products","","","","","","","");
output.append("### Response: resProductsBillLines: \n" + resProductsBillLines.toString() + "\n");
if (!resProductsBillLines.items.size()) throw new oracle.jbo.ValidationException("Products and billlines not found. Subscription Name: " + subscriptionNumber + ". Product Line Number in:" + productLineNumbers + ".");
} catch (e) {
output.append(e.toString() + "\n");
}
output.append("### Creating payload to update product lines and billlines\n");
resProductsBillLines.items.eachWithIndex { product, index ->
def billLines = product.billLines.items;
payload.products.items.add([
"LineNumber": product.LineNumber,
"SubscriptionProductPuid": product.SubscriptionProductPuid,
"billLines": [
"items": []
]
]);
for (billLine in billLines) {
def parts = billLine.DateToInterface.split("-");
def inputDate = new Date(parts[0].toInteger() - 1900, parts[1].toInteger() - 1, parts[2].toInteger());
if (billLine.InterfacedFlag?.toString()?.toLowerCase() == "false" && inputDate <= today) {
billLine.InterfacedFlag = "true";
billLine.remove("@context");
payload.products.items[index].billLines.items.add(billLine);
}
}
}
conn.dynamicQueryParams = null;
output.append("### Updating Subscription\n");
output.append("#### Payload: " + payload + "\n");
try {
def patchSubscriptionUpdatingBilllines = conn.PATCH("subscriptions",subscriptionNumber,"","","","","","","","","",payload);
output.append("### Response: patchSubscriptionUpdatingBilllines: \n" + patchSubscriptionUpdatingBilllines.toString() + "\n");
} catch (e) {
output.append(e.toString() + "\n");
}
output.append("## Updating product lines and billlines - InterfacedFlag: Fineshed\n");
output.append("# Main Process: Finished\n");
return output.toString();Code Explanation
- Parameter Retrieval: Uses SQL to fetch ESS job parameters via session ID.
- Fetching Data: Queries products with dynamic parameters for efficiency.
- Amend and Update: Loops through products for amendments, then patches drafts and headers.
- Activation and Cleanup: Activates the subscription and updates bill lines’ interfaced flags.
- Error Handling: Try-catch blocks ensure robust execution with logging.
This code minimizes API calls and payloads by using fields filtering and onlyData=true.
Conclusion
This implementation provides a scalable way to perform mass date changes in subscriptions using custom Groovy ESS jobs. It builds directly on our parametrized scheduling technique, demonstrating practical applications in Oracle Fusion.
Need assistance with custom Groovy scripts, ESS jobs, or Subscription Management automations? CID Software Solutions can help.