Perform salesforce query using “in” Operator in Mulesoft

Case: When we want to query on a Salesforce object for multiple records at a time instead of using “=” for each record inside a loop, use “in” operator to get records in a single request.

Example:

Set Variable: sfdcQueryParam

%dw 2.0
output application/java
---
"(" ++ ((['PROD-A','PROD-B','PROD-C','PROD-D']) map ("'" ++ $ ++ "'") joinBy ",") ++ ")"


Salesforce Query using Connector:
<salesforce:query doc:name="Product2" doc:id="49a4875c-b477-4733-b528-8c19ed5dcb06" config-ref="Salesforce_Config">
			<salesforce:salesforce-query ><![CDATA[SELECT Id, ProductCode FROM Product2 WHERE ProductCode in :sfdcQueryParam]]></salesforce:salesforce-query>
			<salesforce:parameters ><![CDATA[#[output application/java
---
{
	"sfdcQueryParam" : vars.sfdcQueryParam
}]]]></salesforce:parameters>
</salesforce:query>

Mulesoft – Merge two arrays by Identifier in dataweave

Use Case: When we have two arrays in different mule variables (or one in payload and one in variable), merge two arrays into a single array. We can use below dataweave logic to merge by an identifier.

Input:
Payload:
{
    "orders": [
        {
            "id": 123,
            "customer": "c1"
        },
        {
            "id": 124,
            "customer": "c2"
        }
    ]
}

orderVar:
{
    "orders": [
        {
            "id": 123,
            "number": "xyz1"
        },
        {
            "id": 124,
            "number": "xyz2"
        }
    ]
}

Dataweave:
%dw 2.0
output application/json
---
"orders":payload.orders map (record,index) -> {
    (vars.orderVar.orders filter (record.id == $.id) map (record2,index2) -> {
        "id": record.id,
        "number": record2.number,
        "customer": record.customer
    })
}

Output:
{
  "orders": [
    {
      "id": 123,
      "number": "xyz1",
      "customer": "c1"
    },
    {
      "id": 124,
      "number": "xyz2",
      "customer": "c2"
    }
  ]
}

In few scenarios we might need to merge by index of both input and output. Example, for salesforce bulk update/creates the response will be in the same order of the request payload. Here we should use index as an identifier. Below is the sample code.

%dw 2.0
output application/json
---
"orders":payload.orders map (record,index) -> {
    (vars.orderVar.orders filter ($$ == index) map (record2,index2) -> {
        "id": record.id,
        "number": record2.number,
        "customer": record.customer
    })
}

Oracle HCM – Organization Hierarchy Extraction

Query:

This example is if the hierarchy is at 4 levels, add new columns if you have more levels by adjusting the distance value

SELECT
per_org_l1.name AS Level1_name,
per_org_l2.name AS Level2_name,
(SELECT per_org_l3.name
FROM per_org_tree_node_rf org_tree_l3,
hr_organization_units_f_tl per_org_l3,
fnd_languages fnd_lang_l3
WHERE 1=1
AND org_tree_l3.tree_structure_code = ‘PER_ORG_TREE_STRUCTURE’
AND org_tree_l3.tree_code = ‘XYZ HR Hierarchy’
AND org_tree_l3.distance = 2
AND org_tree_l3.pk1_value = org_tree.pk1_value
AND per_org_l3.language = fnd_lang_l3.language_code
AND trunc(sysdate) BETWEEN nvl(per_org_l3.effective_start_date, sysdate – 1) AND nvl(per_org_l3.effective_end_date, sysdate +
1)
AND per_org_l3.organization_id (+) = to_number(org_tree_l3.ancestor_pk1_value)
AND nvl(per_org_l3.language, ‘US’) = ‘US’) as Level3_name,
(SELECT per_org_l4.name
FROM per_org_tree_node_rf org_tree_l4,
hr_organization_units_f_tl per_org_l4,
fnd_languages fnd_lang_l4
WHERE 1=1
AND org_tree_l4.tree_structure_code = ‘PER_ORG_TREE_STRUCTURE’
AND org_tree_l4.tree_code = ‘XYZ HR Hierarchy’
AND org_tree_l4.distance = 3
AND org_tree_l4.pk1_value = org_tree.pk1_value
AND per_org_l4.language = fnd_lang_l4.language_code
AND trunc(sysdate) BETWEEN nvl(per_org_l4.effective_start_date, sysdate – 1) AND nvl(per_org_l4.effective_end_date, sysdate +
1)
AND per_org_l4.organization_id (+) = to_number(org_tree_l4.ancestor_pk1_value)
AND nvl(per_org_l4.language, ‘US’) = ‘US’) AS Level4_name
FROM
per_org_tree_node_rf org_tree,
hr_organization_units_f_tl per_org_l1,
fnd_languages fnd_lang,
hr_organization_units_f_tl per_org_l2,
fnd_languages fnd_lang_parent
WHERE
1 = 1
AND org_tree.tree_structure_code = ‘PER_ORG_TREE_STRUCTURE’
AND org_tree.tree_code = ‘XYZ HR Hierarchy’
AND org_tree.distance = 1
AND per_org_l1.language = fnd_lang.language_code
AND trunc(sysdate) BETWEEN per_org_l1.effective_start_date AND per_org_l1.effective_end_date
AND per_org_l1.organization_id = to_number(org_tree.pk1_value)
AND per_org_l1.language = ‘US’
AND per_org_l2.language = fnd_lang_parent.language_code
AND trunc(sysdate) BETWEEN nvl(per_org_l2.effective_start_date, sysdate – 1) AND nvl(per_org_l2.effective_end_date, sysdate +
1)
AND per_org_l2.organization_id (+) = to_number(org_tree.ancestor_pk1_value)
AND nvl(per_org_l2.language, ‘US’) = ‘US’