Advanced PDF/HTML Template code for COO Calculation and Grouped Item Display in NetSuite PDFs

Key Functions:

  • Item Processing: Parses transaction line items, skipping kit/group children to avoid duplication. Handles both item groups and kit structures properly.
  • Amount Normalization: Converts formatted amount strings into usable numeric values (e.g., handling commas, decimals, symbols).
  • COO Assignment: Calculates _computedCOO for each item using:
  • custcolcountry_of_origin (if available),
  • Previous COO for discount items,
  • Defaults to "Others" if missing.
  • Grouping by COO: Groups processed items by COO and calculates subtotals per COO group.
  • HTML Table Rendering: Generates a formatted item table including item details, quantities, pricing, discounts, subtotals by COO, and a grand total.

Use Case:

This script is ideal for export or compliance documents where COO tracking is required, and where item groupings or kits are involved in the transaction.

<#if record.item?has_content>
<#assign itemArray = []>
<#assign isGroupOpen = false>
<#assign insideKit = false>
<#assign tempGroupItem = {}>
 
<#assign lineIndex = 0>
 
<#function parseAmount rawAmt>
  <#if rawAmt?has_content>
    <#assign digitsOnly = rawAmt?replace("[^0-9.,-]", "", "r")>
    <#if digitsOnly?contains(",") && digitsOnly?contains(".")>
      <#assign digitsOnly = digitsOnly?replace(".", "", "r")>
    </#if>
    <#assign normalized = digitsOnly?replace(",", ".", "r")>
    <#return (normalized?matches(".*d.*"))?then(normalized?number, 0)>
  <#else>
    <#return 0>
  </#if>
</#function>
 
<#list record.item as item>
  <#assign lineIndex += 1>
  <#assign itemType = item.itemtype?lower_case?trim>
  <#assign isGroupStart = itemType == "group">
  <#assign isEndGroup = itemType == "endgroup">
  <#assign isKitStart = itemType == "kit">
  <#assign isKitEnd = itemType == "endkit">
  <#assign parsedAmt = parseAmount(item.amount?if_exists?string)>
  <#assign isKitChild = insideKit && !isKitStart && !isKitEnd>
 
  <#if isGroupStart>
    <#assign isGroupOpen = true>
    <#assign tempGroupItem = item>
 
  <#elseif isEndGroup && isGroupOpen>
    <#assign finalGroupItem = tempGroupItem + {"amount": parsedAmt}>
    <#assign itemArray += [finalGroupItem]>
    <#assign isGroupOpen = false>
 
  <#elseif isGroupOpen>
    <#-- skip group children -->
 
  <#elseif isKitStart>
    <#assign itemArray += [item]>
    <#assign insideKit = true>
 
  <#elseif isKitEnd>
    <#assign insideKit = false>
 
  <#elseif isKitChild>
    <#-- skip kit children -->
 
  <#else>
    <#assign itemArray += [item]>
  </#if>
</#list>
 
<#if isGroupOpen>
  <#assign fallbackAmt = parseAmount(tempGroupItem.amount?if_exists?string)>
  <#assign finalGroupItem = tempGroupItem + {"amount": fallbackAmt}>
  <#assign itemArray += [finalGroupItem]>
</#if>

    <#assign processedItems = []>
    <#assign lastKnownCOO = "Others">

    <#list itemArray as item>
        <#assign itemName = item.item?string?lower_case>
        <#assign isDiscountItem = itemName?contains("discount")>
        <#assign hasCOO = item.custcolcountry_of_origin?has_content && item.custcolcountry_of_origin?trim != "">

        <#assign effectiveCOO = "">
        <#if hasCOO>
            <#assign effectiveCOO = item.custcolcountry_of_origin>
            <#assign lastKnownCOO = item.custcolcountry_of_origin>
        <#elseif isDiscountItem>
            <#assign effectiveCOO = lastKnownCOO>
        <#else>
            <#assign effectiveCOO = "Others">
            <#assign lastKnownCOO = "Others">
        </#if>

        <#assign newItem = item + {"_computedCOO": effectiveCOO}>
        <#assign processedItems += [newItem]>
    </#list>

    <#assign groupedByCOO = {}>

    <#list processedItems as pItem>
        <#assign cooKey = pItem._computedCOO>
        <#if groupedByCOO[cooKey]?has_content>
            <#assign groupedByCOO = groupedByCOO + {cooKey: groupedByCOO[cooKey] + [pItem]}>
        <#else>
            <#assign groupedByCOO = groupedByCOO + {cooKey: [pItem]}>
        </#if>
    </#list>


    <table style="border:thin solid #a09fa3;" width="100%">
    <tr>
<th align="left" style="border:thin solid #a09fa3;" width="16%">Item</th>
<th align="left" style="border:thin solid #a09fa3;" width="29%">Description</th>
<th align="left" style="border:thin solid #a09fa3;" width="10%">Misc</th>
<th align="left" style="border:thin solid #a09fa3;" width="14%">COO</th>
<th align="right" style="border:thin solid #a09fa3;" width="11%">Invoiced Quantity</th>
<th align="right" style="border:thin solid #a09fa3;" width="10%">Total Shipped</th>
<th align="right" style="border:thin solid #a09fa3;" width="10%">Price</th>
<th align="center" style="border:thin solid #a09fa3;" width="10%">Discount</th>
<th align="right" style="border:thin solid #a09fa3;" width="12%">Amount</th>
</tr>

<#assign currencySymbolMap = {
  "USD":"$",
  "EUR":"€",
  "GBP":"£",
  "INR":"₹",
  "JPY":"¥",
  "AUD":"A$"
} />

<#assign symbol = currencySymbolMap[record.currencysymbol]!record.currencysymbol />
    <#assign grandTotal = 0>
    <#list groupedByCOO?keys?sort as coo>
        <#assign groupItems = groupedByCOO[coo]>
        <#assign groupTotal = 0>

        <#list groupItems as i>
           <#assign amtRaw = (i.amount?string.number)?replace("[^0-9.-]", "", "r")>
        <#assign amt = (amtRaw?has_content)?then(amtRaw?number, 0)>
        <#assign isDiscount = i.item?lower_case?contains("discount")>
        <#if isDiscount>
            <#assign groupTotal -= amt?abs>
            <#assign grandTotal -= amt?abs>
        <#else>
            <#assign groupTotal += amt>
            <#assign grandTotal += amt>
        </#if>
            <tr class="itemLine">
                <td style="border:thin solid #a09fa3;" width="16%">${i.item}</td>
                <td style="border:thin solid #a09fa3;" width="29%">${i.description}</td>
                <td style="border:thin solid #a09fa3;" width="12%">${i.custcol_eb_misc}</td>
                <td style="border:thin solid #a09fa3;" align="center" width="12%">${i._computedCOO}</td>
                <td style="border:thin solid #a09fa3;" align="right" width="11%">${i.quantity}</td>
                <td style="border:thin solid #a09fa3;" align="right" width="10%">${i.quantityfulfilled}</td>
           <#assign rate = parseAmount(i.rate?string)>
                <td style="border:thin solid #a09fa3;" align="right" width="10%">${symbol}${rate?string["#,##0.00"]}</td>
                <td style="border:thin solid #a09fa3;" align="center" width="10%">
                    <#if i.custcol_eb_discount?has_content>${i.custcol_eb_discount}%</#if>
                </td>
  <#assign amt = parseAmount(i.amount?string)>
                <td style="border:thin solid #a09fa3;" align="right" width="12%">${symbol}${amt?string["#,##0.00"]}</td>
            </tr>
        </#list>
              
        <tr style="background-color: #FFFF99;">
            <td align="left" style="font-weight:bold;">Subtotal</td>
            <td align="left" style="font-weight:bold;">Subtotal for same COO</td>
            <td></td>
            <td align="center" style="font-weight:bold;">${coo}</td>
            <td colspan="4"></td>
           <#assign groupTotalNum = (groupTotal?has_content)?then(groupTotal?number, 0)>
<td align="right" style="font-weight:bold;">${symbol} ${groupTotalNum?string["#,##0.00"]}</td>
        </tr>
    </#list>

    <tr>
        <td colspan="8" align="right" style="font-weight:bold; border-top:1px solid #a09fa3;">Grand Total</td>
        <td align="right" style="font-weight:bold; border-top:1px solid #a09fa3;">${record.subtotal}</td>
    </tr>
    </table>
</#if>

Leave a comment

Your email address will not be published. Required fields are marked *