Scenario
The formula used to calculate for the Total Payments Made (Payment Applied) for a Sales Order is Payments on Invoice + Customer Deposits not applied to Invoice.
Solution
I. Create a Saved Search to get Customer Deposits that are not yet applied to an Invoice.
1. Navigate to Lists > Search > Saved Searches > New.
2. Select Transaction. Use the following settings: (Note: Change the title to your preference)
Search Title: Customer Deposit by SO
Public: Checked
2.1. On the Criteria subtab:
Filter: Type
Description: is Customer Deposit
Filter: Status
Description: is none of Customer Deposit:Fully Applied, Customer Deposit:Unappoved Payment
2.2. On the Results subtab:
Field: Amount (Foreign Currency)
Summary Type: Sum
2.3. On the Available Filters subtab:
Filter: Sales Order
Show in Filter Region: Yes
4. Click Save or test by clicking on Save & Run then select a Sales Order on the Filter region.
II. Create the Custom Transaction Body Field for Customer Deposit Amount.
1. Navigate to Customization > Lists, Records, & Fields > Transaction Body Fields > New.
2. Use the following settings:
Label: Deposited Amount
ID: _amountdeposited
Type: Currency
Store Value: Unchecked
2.1 On the Applies To subtab:
Sale: Checked
2.2 On the Display subtab:
Subtab: Main
Display Type: Hidden
2.3 On the Validation & Defaulting subtab:
Search: Customer Deposit by SO
3. Click Save & Apply to Forms.
4. Mark the Show check boxes with Form Type = Sales Order.
5. Click Save.
III. Create a Saved Search to get Payments made on the Invoice. This includes Customer Deposits that are applied to the Invoice.
1. Navigate to Lists > Search > Saved Searches > New.
2. Select Transaction. Use the following settings: (Note: Change the title to your preference)
Search Title: Invoice Amount Paid by SO
Public: Checked
2.1. On the Criteria subtab:
Filter: Type
Description: is Invoice
2.2. On the Results subtab:
Field: Amount Paid (Foreign Currency)
Summary Type: Sum
2.3. On the Available Filters subtab:
Filter: Created From
Show in Filter Region: Yes
4. Click Save or test by clicking on Save & Run then select a Sales Order on the Filter region.
IV. Create the Custom Transaction Body Field for Invoice Payments.
1. Navigate to Customization > Lists, Records, & Fields > Transaction Body Fields > New.
2. Use the following settings:
Label: Invoice Payment
ID: _amountdpaid
Type: Currency
Store Value: Unchecked
2.1. On the Applies To subtab:
Sale: Checked
2.2. On the Display subtab:
Subtab: Main
Display Type: Hidden
2.3. On the Validation & Defaulting subtab:
Search: Invoice Amount Paid by SO
3. Click Save & Apply to Forms.
4. Mark the Show check boxes with Form Type = Sales Order.
5. Click Save.
Note: The Display Type for the Fields created is Hidden. It is not necessary to have these Fields show on the Sales Order as it will only be used as part of the formula.
V. Create the Custom Transaction Body Field for Payment Applied.
1. Navigate to Customization > Lists, Records, & Fields > Transaction Body Fields > New.
2. Use the following settings:
Label: Payment Applied
ID: _paymentapplied
Type: Currency
Store Value: Unchecked
2.1. On the Applies To subtab:
Sale: Checked
2.2. On the Display subtab:
Subtab: Main
Display Type: Normal
2.3. On the Validation & Defaulting subtab:
Default Value: NVL({custbody_amountpaid}, 0) + NVL({custbody_amountdeposited}, 0)
Formula: Checked
3. Click Save & Apply to Forms.
4. Mark the Show check boxes with Form Type = Sales Order.
5. Click Save.
To test this, open a Sales Order record. Payment Applied should appear on the main section with the total payment made on the Sales Order.