PROPOSAL SUMMARY
This proposal is to create a report on ‘Capital Invested Vs Received’ using multiple saved searches to display the total payable amount and the total receivable amount.
REQUIREMENT
VC Paper Resources needs to create multiple saved searches to display the total payable amount in one column and the total receivable amount in another column based on the vendor’s name. The total receivable amount would be taken from the transactions – customer deposit (Unapplied amount), invoice payment, and credit memo. The total payable amount would be taken from the vendor prepayment (Unapplied amount), bill payment, and bill credit. Also display the differences between the total receivable and total payable amount.
OUR SOLUTION
We will be creating multiple saved searches for Capital Invested Vs Received Report, we are displaying the total receivable amount and total payable amount
For displaying the total receivable amount, we will be considering the transactions like customer deposits, invoice payments, and credit memos.
For displaying the total payable amount, we will be considering the transactions like vendor prepayment, bill payment, and bill credit.
Field Customisation in Sales order
To combine all the transactions (fetched from multiple saved searches) into a single report, we need to create 4 custom fields in the sales order record. The details are provided below:
- To Get the total amount paid in the invoice payment, we will be creating the custom field named Invoice Ref# and the field type would be the multi-select this will source the invoice payment number.
- To Get the total amount paid in the credit memo, we will be creating the custom field named Credit Memo Ref# and the field type would be the multi-select this will source the credit memo number.
- To Get the total amount paid in the bill payment, we will be creating the custom field named Bill Payment Ref# and the field type would be the multi-select this will source the bill payment number.
- To get the total amount paid in the bill credit, we will be creating the custom field named Bill Credit Ref# and the field type would be the multi-select this will source the bill credit number.
The main search will be based on the sales order, and we will be getting the total amount paid for the credit memo, invoice payment, bill payment, and bill credit.
Script Customization
We will be creating 2 saved searches for customer deposit and vendor prepayment to get the unapplied amount by using the script.
We will combine 3 searches and display them through a suitelet page by using a script.
For updating the present Sales order record, we need to use CSV Import or mass update the sales order record to link the invoice payment, credit memo, bill payment, and bill credit record. In order to link the invoice payment, credit memo, bill payment, and bill credit to the sales order record, we will be deploying a script at the creation of the mentioned transactions (invoice payment, credit memo, bill payment, and bill credit)
| Criteria/ Available Filter: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary name from sales order |
| 2 | Market/Department | Department field from all transaction |
| 3 | Year | Year field in the Sales order |
| 4 | Quater | Quater field in the Sale order |
| 5 | Vendor | Vendor field from transactions |
| 6 | Buyer | Buyer name in the transaction |
| Report Columns: | ||
| # | Field | Field Name from NS records |
| 1 | Vendor Name | The vendor Name (Summary Type= Group) |
| 2 | Total Payable | The transaction we are referring Customer deposit (Unapplied amount) Invoice payment Credit memo |
| 3 | Total Receivable | The transaction we are referring Vendor prepayment (Unapplied amount) Bill payment Bill credit |
| 4 | Difference | Total Payable – Total Receivable |
ASSUMPTIONS
- We are taking the Unapplied amounts of customer deposit and the vendor prepayment which will be linked to the sales order and purchase order record.