Friday, May 31, 2019

Create a list that shows the last purchase price of an item based on Vendor bill instead of Item Receipt

Last Purchase Price field shown on the Item record  is determined by the most recent transaction for the item that added positive inventory, such as a purchase receipt, inventory transfer or inventory adjustment.

To create a list that shows the last purchase price of an item based on recent Vendor bill instead of Item Receipt, the steps are as follows:

    - Navigate to Transactions > Management > Saved Searches > New > Transaction
    - Under the Criteria subtab enter the following:
        - Filter : Type | Description : is Bill
        - Filter : Main Line | Description : is false
        - Filter : Tax Line | Description : is false
        - Filter : Shipping Line | Description : is false
        - Filter : COGS Line | Description : is false
    - Under the Results subtab hit Remve All button and enter the following:
        - Field : Date | Summary Type : Maximum
        - Field : Item | Summary Type : Group
        - Field : Item Rate | Summary Type : Maximum | Function : Absolute Value | When Ordered By Field : Date | Custom Label : Unit Rate
        - Field : Quantity| Summary Type : Maximum | Function : Absolute Value | When Ordered By Field : Date | Custom Label : Quantity Billed
        - Field : Amount | Summary Type : Maximum | Function : Absolute Value | When Ordered By Field : Date | Custom Label : Billed Amount
    - Edit Search Title and hit Save and Run

Price Level is Not Showing on Sales Transaction

There are only three reasons why a Price Level is not showing up as an Available selection on a Sales Transaction > Price Level Column:

1. Price Level is Inactive

2. Online Price Level is checked

3. Item record shows no amount specified for the Price Level created (Item Record > Sales / Pricing Tab > Price Level Column). This happens when upon creation of Price Level, Update Existing Prices is unchecked.


Unable to Over Fulfill Dropshipment even if Allow Overage on Item Fulfillments = True

When users want to over-fulfill a Dropship Transaction, they must mark both below preferences under Setup > Accounting > Accounting Preferences > Order Management Tab

1. Allow Overage on Item Fulfillments.

2. Allow Overage on Item Receipts.

If the Allow Overage on Item Receipts is not marked, they will encounter an error message when overfulfilling: Notice: "You cannot over-fulfil an item unless you have selected Allow overage on item fulfilment."

Using Adjust Inventory Worksheet CSV Import with Standard Cost Items

Here are some points to consider in using the Inventory Worksheet Import on Standard Cost Items.

1. Before doing the import, make sure that the Standard Cost Item has already been revalued. Else, the import will not update the item. You will receive an error saying Inventory Revaluation is no longer the first transaction in the item's history.

2. Regardless of whatever you put in the New Value Column on your CSV File, the system will change it to New Qty Entered * Effective Standard Cost.

Variance will not be recognized for any discrepancy hence, the need to set a value in the New Value Column. Initially, the record will show the value that was entered in the CSV File but after refreshing the page, the value changes and reflects New Qty Entered * Effective Standard Cost.

Saved Search displaying Average Monthly Expenses

This saved search will display running average monthly expenses as of a certain month, with each month displayed under a separate column to see on any increase or decrease versus budgeted expenses.

 

Expenses will refer to all expenses posted in via transaction GL impact.  You may include additional filters to narrow down to list of expenses to be included.



To create the saved search:

 

1. Navigate to Lists > Search > Saved Searches > New. Select Transaction as search type.

2. At the Criteria tab > Standard subtab, enter the following filters:

-----a. Account type - at the pop up select Expense

-----b. Posting - at the pop up select Yes

-----c. Date - at the pop up select within this fiscal year

 

3. At the Results tab > Column subtab, enter the following:

-----a. Account-----Summary Type: Group

-----b. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '01' then -nvl({amount},0) else 0 end)/1 ----Summary Label: JAN

-----c. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '02' then -nvl({amount},0) else 0 end)/2 ----Summary Label: FEB

-----d. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '03' then -nvl({amount},0) else 0 end)/3 ----Summary Label: MAR

-----e. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '04' then -nvl({amount},0) else 0 end)/4 ----Summary Label: APR

-----f. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '05' then -nvl({amount},0) else 0 end)/5 ----Summary Label: MAY

-----g. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '06' then -nvl({amount},0) else 0 end)/6 ----Summary Label: JUN

 

**For the rest of the months, just copy the formula and change the month as appropriate.

Search that pulls out inventory items that are at or below reorder point

To create a saved search that pulls up items wherein stock levels are  "at or below reorder point", the steps are as follows:

- Navigate to Transactions > Management > Saved Searches > New
- Select Item and you'll be navigated to Item Saved Search page.
    - Under the Criteria subtab, enter the following:
        - Filter : Formula (Text) | Formula : case when nvl({locationreorderpoint},0) > = nvl({locationquantityonorder},0)+nvl({locationquantityavailable},0) then 0 else null end | Formula (Text) : is not empty
        - Filter : Location Reorder Point | Description : is not empty
   - Under the Results subtab hit Remove All button then enter the follwoing:
        - Field : Name
        - Field: Display Name
        - Field: Inventory Location Fields... > Name
        - Field: Location Available
        - Field: Location On Order
        - Field: Location On Hand
        - Field: Location Reorder Point
    - Edit Search Title the hit Saved and Run

The Location Reorder Point is necessary to have proper tracking of the to order items on a location level.

Quantity On Order Column on Item Records

There are three main sources for the Quantity On Order:

1. Open Purchase Orders > when Items are purchased such as normal Inventory Items and Assembly Items when Allow Purchase of Assembly Items preference is enabled under Setup > Accounting > Accounting Preferences > Items/Transactions > Inventory.
2. Open Work Orders > for Assembly Items.
3. Transfer Orders > when Multi Location Inventory is enabled. The quantity will be added as On order in the 'To Location' set on the Transfer Order.

Note: Transactions that have Status: Pending Approval are excluded.

Saved Search to show the Current Effective Standard Cost vs the Location Standard Cost for non zero items

1. Go to Lists > Search > Saved Searches > New.
2. Select Item.
3. On the Criteria Tab, add the below Filters:
--Costing Method is Standard
4. On the Results Tab, add the below Fields:
--Name
--Location On Hand
--Location Standard Cost
--Location Average Cost > Custom Label: Current Effective Standard Cost
5. Rename the Report.
6. Click Save & Run.

 

Note that this is currently logged under Enhancement: 227219: Standard Costing : Display the effective / current standard cost per location on the item record together with its effective date.
This Alternate Solution will show the Current Effective Standard Cost provided that Location On Hand is not zero.
      

Alternate Report for Inventory Valuation Summary with Preferred Stock Level Information

The results will mimic the information located in the item record and is an alternative report for an inventory valuation summary report with preferred stock level information.

To create the saved search:
1. Navigate to
Lists > Search > Saved Searches > New. Select Item.
2. Edit the
Search Title
3. At the Criteria tab > Standard subtab, enter the following details:
-----a. Inactive -- is No
4. At the
Results tab > Columns subtab, enter the following fields:
-----a. Name ---- Summary Type: Group
-----b. Display Name
-----c. Location on Hand ---- Summary Type: Sum
-----d. Location Total Value ---- Summary Type: Sum
-----e. Location Preferred Stock Level ---- Summary Type: Average ---- Summary Label: Ave. PSL
-----f. Inventory Location
** Check the
Show Totals box
5. Click
Save and Run

Results:
Summary Window: Shows the item name, display name, total quantity and total value similar to the Inventory Valuation Summary report plus the average preferred stock level across all locations.

If users click on an item, it will provide all the information on a per location basis.

Demand Planning Across Multi Location - Item Purchased and Sold on Different Locations

Item Setup: Navigate to Lists > Accounting > Items > New

Item A
Replenishment Method: Time Phased
Location 1 > Demand Source: Forecast from Demand Plan


Step 1: Create Item Demand Plan for Item A on Location 1.
Navigate to Transactions > Demand Planning > Item Demand Plans > New.
Location: Location 1
Item: Item A
Start Date: 1/1/2013 > Quantity: 100 up to Start Date: 12/1/2013 > Quantity: 1200. Each month, we increased the quantity by 100 units. Feb 2013: 200, Mar 2013: 300 and so on.

Step 2: Create Supply Plan for Item A on Location 1.
Navigate to Transactions > Demand Planning > Generate Item Supply Plan.
Location: Location 1
End Date: 4/1/2013
Mark Item A > Click Submit.
Item Supply Plan shows that we should purchase based on the item demand plan.

Step 3: Create Purchase Orders.
Navigate to Transactions > Inventory > Order Items.
Set Order Start Date: 1/1/2013
Set Order End Date: 5/1/2013
Make sure that you set a Vendor for the suggested orders for Item 1.
Click Submit.
Multiple Purchase Orders will be created.

Step 4. Regenerate the Item Supply Plan.
Navigate to Transactions > Demand Planning > Generate Item Supply Plan.
Location: Location 1
End Date: 4/1/2013
Mark Item A > Click Submit.

Updated Item Supply Plan will now show zero since Purchase Orders were already created.

Step 5. Edit all Purchase Orders created in Step 3.
Pull up the Purchase Orders and change the Location Field from Location 1 to Location 2


Notes:
1. Item Supply Plan for Item A under Location 1 should remain zero unless regenerated after Step 5.
2. Users need not generate another Item Supply Plan under Location 2.
Note: Issue: 227858 is logged for the ability to Process Demand Planning across all location.

Global Search Item using Internal ID

Ability to use Internal ID via Global Search is logged under Enhancement 110813.

Alternate Solution:


1. Pull up any Item Record via Global Search or Lists > Accounting > Items.


2. Go to the browser's Address Bar.


3. Replace the number after "id=" with the item's Internal ID.

4. Hit Enter.


Since Internal ID is a unique identifier, the system will pull up the actual record if there is any match or show a notice: "There are no records of this type" if there are no records that match the Internal ID entered.

Generate Bill based from partial Item Received / Item Receipt rather than based on entire Purchase Order

This is currently logged under Enhancement 66062: Preference for Bill to be generated from Receipt

A workable Alternate Solution will be to Generate the Bill as soon as the partial Item Receipt is created.

Example:
Purchase Order with five line items.

Click Receive Button > On the Item Receipt Page, mark the first two Line Items > Click Save & Bill > On the Bill Page, click Save.

Go back to the Purchase Order > Click Receive Button > On the Item Receipt Page, mark the next three Line Items > Click Save & Bill > On the Bill Page, click Save.

Note: If the Bills need to be dated as of the actual Billing Date, users may edit the Bills created and change the Date.

Revalue Assembly Item based on Top Level instead of based on Component Costs without enabling preference to allow Purchase of Assembly Items

To create Planned Standard Cost Record for the Assembly via Rollup:
1. Navigate to Lists > Accounting > Planned Standard Cost Rollup.
2. Select the Subsidiary, Standard Cost Version and Effective Date.
3. Select the Assembly Item.
4. Click Submit.

Edit the Planned Standard Cost Record created:
1. Navigate to Lists > Accounting > Planned Standard Cost. Edit the Planned Standard Cost Record of the Assembly Item you need to revalue.
2. Remove all the Line Items and retain the first Line Item for the Component.
3. Replace the Cost Category to Default Cost Category.
4. Specify the Standard Cost.
5. Click Save.

To revalue the Items / Assemblies based on the new Planned Standard Costs created:
1. Navigate to Lists > Accounting > Revalue Standard Cost Inventory.
2. Select the Subsidiary, Standard Cost Version, Adjustment Account and Effective Date.
3. Look for the Assembly to adjust and mark the checkbox.
4. Click Submit.

Reference Checkout: Display A Custom Transaction Body Field on Checkout and Save Value in Sales Order

Image


Applicable for the following version:
Reference Checkout 2.0 - 2.05 (Bundle ID 69019)

Note: This solution is only applicable if user only has 1 custom transaction body field. For Reference Checkout with more than 1 custom body field, please see SuiteAnswers Answer ID 46004.

Requisites:
  1. Custom Checkout folder is already populated with files copied from the Reference Checkout folder.
  2. Custom Checkout Touchpoint is deployed.
  3. A Transaction Body field has been created where Web Store = T and Store Value = T.

Steps:

  1. Navigate to Documents > Files > File Cabinet > Web Site Hosting Files > Live Hosting Files > SSP Applications > NetSuite Inc. - Checkout > Custom Checkout > templates > order_wizard and edit order_wizard_showshipments_module.txt.
  2. Add the script below on the template before the closing section:

    * custbody_message is the id of the transaction body field

  3. Save. The custom transaction body (free-form text) field should display on the section above the Place Order button and below the Items list.

  4. Take note of the id used (e.g.#custbody_message)

        5. Navigate to Web Site Hosting Files > Live Hosting Files > SSP Applications > NetSuite Inc. - Checkout > Custom Checkout > js > src > app, and edit Configuration.js.

        6. Find OrderWizard.Module.TermsAndConditions then add code inside the save function like below:

                

        7. Save.

        8. Navigate to Web Site Hosting Files > Live Hosting Files > SSP Applications > NetSuite Inc. - Checkout > Custom Checkout > services, and edit live-order.ss

        9. Add code before the comment '// Finally Submits the order' : 

                

        10. Save. 
        11. Ensure that the Application.js file in Custom Checkout>js folder is updated
          a. If it is not updated, edit the Application.js file and make no changes
          b. Click on Save
          This will trigger the update.

    Though the custom_message value is not displayed in the json, it is still passed over to the Sales Order record.
                

Unable to mark Online Price Level checkbox on the Price Level Record

There is only one standard Price Level in NetSuite in which users cannot mark the Online Price Level checkbox and it is the Base Price level.

However, users may change the Price Level Name for the Base Price Level.

To identify if the Price Level is the standard Base Price Level, check the Internal ID. Internal ID for standard Price Level is 1.

Create a Customer Saved Search which Counts the Sales Transactions within a Specific Date Range for Customers with or without Transactions

Below are the steps to create a saved search which displays the count of customers sales transaction within a specific date range. It also includes customers who have no transaction.


1. Navigate to Lists > Search > Saved Searches > New > Customer 
2. Under the Criteria tab > Standard sub tab, add the following: 


*The sample date filters below is for December 1- 31, 2013. User can change it as per requirement.

Filter
Description
Formula
Transaction : Dateis not before 12/1/2013 
Transaction : Dateis not after 12/31/2013 
Transaction : Typeis any of none, Invoice, Cash Sale 

3. Under the Results tab > Columns sub tab, add the following:

 
Field
Summary Type







 
NameGroup       
 
Transaction : NumberCount       


4. Give it a name and Save.

Create a Transaction Saved Search which Displays Separate Columns for New and Existing Customer Sales



While the New Customer Sales report displays transactions for new customers, it does not have the ability to show a separate column to display the sales for existing customers. For this, users can create a search instead. See the steps below:

1. Navigate to Lists > Search > Saved Searches > New > Transaction.
2. Under the Criteria tab > Standard sub tab, add the following:

Filter
Description
Formula
Typeis any of Invoice, Cash Sale 
Main Lineis true 
Dateis within    <---- select date range here
Amountis greater than 0.00 

3. Under the Results tab > Columns sub tab, add the following:

 
Field
Summary Type
Function
Formula
Custom Label



 
Formula (Text)  case when {trandate}={customer.firstsaledate} THEN 'New Customer' ELSE 'Returning Customer' END New/Returning   
 
Date        
 
Type        
 
Number    Transaction #   
 
Name        
 
Formula (Numeric)  case when {trandate}={customer.firstsaledate} THEN 1 ELSE Null END Count of New Sale   
 
Formula (Numeric)  case when {trandate}!={customer.firstsaledate} THEN 1 ELSE Null END Count of Returning Customer   
 
Formula (Currency)  case when {trandate}={customer.firstsaledate} THEN {Amount} ELSE Null END New Customer Sales   
 
Formula (Currency)  case when {trandate}!={customer.firstsaledate} THEN {Amount} ELSE Null END Returning Customer Sales   
 
* Please mark 'Show Totals' box, so it will sum the total the amount and count of transactions. 

4. Give it a name and save.

How to put Credit hold for customers that does not have terms and credit limit on invoice transactions but not on cash sales.

Assuming that you have set your Customer Credit Limit Handling (Setup>Accounting>Accounting Preferences) to Enforce Holds and on the customer record the Hold was set to Auto, once a customer reaches the credit limit, sales invoices should not be saved.  

In order to enforce holds for customers that does not have terms and credit limit for invoice transactions and at the same time can save and process cash transaction, make sure to set the Credit Limit to zero on the customer record.  If the Credit Limit is null, you can still save the invoice and enforce hold will not work.

Multi-Currency and Credit Limit


Credit Limit is calculated based on the Total of all Currency in the customer record using the exchange rate.

Example:

1. Customer have the following currency:

a. Primary = USD

b. Secondary = Euro

C. Exchange Rate = $1 to Euro 0.89

2. Credit Limit = 2,000.

3. Balances:

a. USD = 1,000.00

b. EUR = 891.50

Total = 2,000

4. Navigate to Setup> Accounting> Accounting Preferences> General> Customer Credit Limit Includes Orders = True or checked

5. If a user creates a sales order for the customer they will encounter the following message:

a. USD: Customer balance of 2000.00 exceeds credit limit of 2000.00

EUR: Customer balance of 2000.00 exceeds credit limit of 2,000

Credit Card Processing > Can we settle Verbal authorizations received from the Merchant Bank or Issuing bank inside NetSuite?

Unfortunately, there is no process in place right now to settle Verbal Authorizations given by Merchant Bank or Customer's Issuing Bank in NetSuite. You can only process or settle these authorizations outside of NetSuite.

Kindly contact your Payment Gateways (i.e. CyberSource, etc.) directly to know how you can process these transactions on their end.

For customers asking to have the ability to process these transactions inside NetSuite, they can have their case attached to the Enhancement Request below:

Enhancement 227898 – Ability to process Verbal authorizations in NetSuite

Remove the UPS Account on the Shipping Carrier field of a Sales Order

Some Customers use a specific shipping account and they would like to remove UPS on the Shipping Carrier field of a Sales Order record.

UPS is a default value on the Shipping Carrier field and users will not be able to remove it as a selection on the dropdown. If users want to default the Shipping Carrier field to "FedEx/USPS/More", then consider customizing the form.

I.             Customize the Form

 

1.    Navigate to Setup > Customization > Transaction Forms.

2.    Edit a Sales Order Transaction Form.

3.    Go to the Screen Fields tab > Shipping sub tab.

4.    Set the Display Type of the "Shipping Carrier" field to Inline Text.

5.    Hit Save.

 

II.            Update the Shipping page.

 

1.    Using an Administrator role, navigate to Setup > Accounting > Shipping.

2.    On Set Up Shipping page, set Default Shipping Carrier to FedEx/USPS/More.

3.    Hit Submit.

The Shipping Carrier field on the sales order record will now be an inline text which means that it is no longer editable. Sales Orders will now default to FedEx/USPS/More.

Note: This will not be applicable if UPS is set as the default shipping carrier on the Customer record (Preferences tab > Shipping Carrier). The behavior will be the same if users mark the Enable Item Line Shipping preference on the sales order and there is a default shipping carrier on the item record (Purchasing/Inventory tab > Carrier).