ICM Cloud Commission Processing

Created by Kathy Kale, Modified on Mon, 16 Sep, 2024 at 2:38 PM by Kathy Kale

ICM Cloud Commission Processing

Commission Adjusts
1. Commission Adjustment Records
2. Reserve Check Records
4. Export Data to CSV
  3. this will clear after it is Exported to ICM
  5. this will clear after it is Exported to ICM
Commission Region Maintenance
1. Make change to:
  1. Region Draw set by Sales VP
  2. Region Draw minimum ($200 usually)
Commission Payee Maint - make Payee changes for RMs
1. Make changes to
  1. 25% additional withheld
  2. End RM that is leaving
  3. if there is a Reserve check and the region is ending
    1. set Draw to $0.00, Draw minimum to $0.00, Leave Zero Reserve flag ="N"
  4. if no Reserve check and the region is ending
2. as you exit, it will automatically export 4 tables to ICM

Option: Commission Before Export Commission Cvt SCC_SA_U_010
Leave these 2 blank: Tran Type & Tran Number
they are used to update a single transaction 1 at a time
It will show the record count of what was updated
if there are only a few lines changed, there is a chance that the SA Update was NO RUN!!!
it will show the record count of what was exported to ICM
and ready to load into ICM

In ICM

Download

User Options (circle with person) Click
Downloads Click
Click each csv to download - one at a time
Overwrite click
Activity

Run Scheduled Jobs

After each scheduled job, check to see if all jobs were run successfully
- Audit
- If the import csv was empty you will get this message:
- If the import csv was not empty, and there were no errors, you will get this message:
- or click out of the scheduled jobs and back in so that it updates the last runs
- or reload page to see that the jobs were successful
Run when needed:

run once each commission pay to get updates
run until commissions complete without errors
Sales Portal - Login (varicent.com)
Administrative Commission Run Validation
- Validate SADetailLines Transactions Not Calculated
Administrative Payroll Exports
- Export: PDS Commission Paid
- Export: ROSS Commission Pay
- Export: Commission Report
5 Export Lines to Ross - Run Schedule
- *** T H I S must be run B E F O R E: "Commiss Pay Period Release" is done!!!! ***
  - Calculated Quantity by Region with Commission Deduction
  - Commission by Base Rate
  - Commission by Bid Rate
- Export all 3 to your ROSS folder
  - User options
    - Downloads
      - Publisher
        3 dots
        Download - repeat for all 3
- Downloads
- Full download history
- Show in folder
- Copy From the PC
- Paste into your ROSS Folder
- Rename all 3; format the Value; save as CSV
  - *** Open in excel; in "Value" column, change the Format to "Number 2 decimals"
    - if this is not done, the output over 1,000 will update as 0 since it will not know what to do with the value
    - Repeat for all 3
in ROSS
- Commiss Import into SCC_COMMISSION_LINES
  - reads the CSV in Shannon's folder into SCC_COMMISSION_LINES
- Commiss Update Lines into ROSS
  - reads the table: SCC_COMMISSION_LINES
  - updates the values in SA_Detail_Lines
- Commiss Before Export Commissions Cvt
  - fill in Commission Price code "D" to "E" or "W"
  - fill in Commission discount Percent_1 & _2
  - Fill in flag: SCC_SA_COMMISSION_CVT_FLAG = "Y"
  - THEN it shows the lines that were changed
  - THEN it exports the data in SA_DETAIL_LINES into CSV for import to ICM
in ICM
- User Options
  - Downloads
    - Imports
      - Import File (you cannot see this unless you switch from Publisher to Imports)
        SADetailLines.csv
        Open
After all processing is completed without errors
- AND SADetailLines.csv is imported into ICM
In ROSS
Commiss Pay Period Release
flip from "N" to "Y" to release
The csv will be exported E V E R Y T I M E even if no change is made
it will not do anything if you do not download and import the csv in ICM
In ICM
User Options
Downloads Click
Review on Website to see Commissions are available to view.

******************************************************************************************************************************************************************************************************************************************************************************************************

Scheduler

0 Web Portal - Imports Payees, Regions
0 Web Portal - Imports Parts, Distributors
1 Web Portal - Imports Codes, Types, Groups, Prices
2 Web Portal - Imports ManualPayment, ReserveCheck
3 Web Protal - Imports SADetailLines
1. SADetailLInes Import 2024-05(2) second run in month of May
  1. occassionally we have issues with importing these records if there are 2 records for SA_DETAIL_LINES with different quantities.
  2. find the record and make them match in ROSS
4 Nightly Tasks, Update Reserve, Calculate

Watch in Activity

/****** Match the UOM in Qty to Lines Where there is an issue of not unique keys for imports into ICM ******/

SELECT L.TRANSACTION_date, q.TRANSACTION_TYPE, q.TRANSACTION_NUMBER, q.TRANSACTION_LINE_NUMBER, q.IC_PRODUCT_UOM_TYPE, q.INVOICE_QUANTITY, q.CREDIT_QUANTITY, q.REBATE_QUANTITY, q.TRANSFER_QUANTITY

,q.UNIT_OF_MEASURE ,l.UNIT_SALES ,l.SCC_TRANSACTION_QUANTITY ,l.PART_CODE ,l.WAREHOUSE

FROM SA_DETAIL_LINE_QTYS q

left outer join SA_DETAIL_LINES L on L.TRANSACTION_NUMBER=q.TRANSACTION_NUMBER and L.TRANSACTION_LINE_NUMBER= q.TRANSACTION_LINE_NUMBER

where L.TRANSACTION_date >= '16-May-2024' and q.UNIT_OF_MEASURE <> l.UNIT_SALES order by q.TRANSACTION_NUMBER, q.TRANSACTION_line_NUMBER

In Spreadsheets:

Insert into spreadsheet: exclude the open regions

SELECT s.payee_number, l.salesperson_code,s.CODE_DESCRIPTION, s.location, a.SALESPERSON_CODE

,sum((l.sa_total_currency - l.line_discount )* DORC_INDICATOR) as line_net

,sum((l.SCC_product_net_sales )* DORC_INDICATOR) as prodNetSales

,sum((l. SCC_COMMISSION_CALC_AMOUNT )) as ComAmt

FROM [fin_prod].[dbo].[SA_DETAIL_LINES] L

left outer join customers c on c.customer_number = L.customer_number and c.sales_order_type <> 'EUORD'

left outer join customer_addresses A on A.customer_number = C.customer_number and A.address_code = L.delivery_address_code

left outer join salespersons s on s.salesperson_code = l.salesperson_code

left outer Join product_master M on m.part_code = L.part_code

where l.transaction_date >= '16-MAY-2024' and l.transaction_date <= '31-MAY-2024'

and SCC_DO_NOT_INCLUDE_SALES_FLAG <> 'Y'

and SCC_COMMISSION_NOT_PAID_FLAG <> 'Y'

and SCC_TRANSACTION_QUANTITY <> 0 and (l.warehouse ='FG' or l.warehouse = 'BK')

and l.salesperson_code <> '969' and l.salesperson_code <> '999'

and l.salesperson_code <> '800' and l.salesperson_code <> '620'

group by s.payee_number,l.salesperson_code,s.CODE_DESCRIPTION, s.location, a.SALESPERSON_CODE

order by s.payee_number,l.salesperson_code

Administrative Commission Run Validation: Validate SADetailLines Transactions Not Calculated

If there are kickouts,

correct what can be corrected in SA_DETAIL_LINES

USE: Commission Force Commission SCC_SOP_T_043

either manually add the commission rate or

Fix the kickouts to force the commissions for a T E M P Fix for this run.

See the transaction # and line and go to it for corrections.

Ross: Commiss Force Commissions SCC_SOP_T_043

Look for the line - END to get to the bottom.

Force the Commission pay "Y"

put the Commission AMOUNT for the line in Forced Amount.

Once you make your forced commission change, and exit forward and back into the transaction, it will show the forced commissions at the top.

Administrative Commission Run Validation: Validate SADetailLines Transactions Not Calculated

If the most recent date with a kickout is 2017-PAY 04 FEB (2) - then this run has no kickouts

Drop 3 exports from Shannon into master spreadsheet.

DO NOT include the last line with the totals.

Look for differences.

RUn this SQL to get totals to find the transaction that does not match

/* Verify details by transaction for 1 RM */

SELECT s.location ,l.transaction_type, l.transaction_number, l.transaction_Date, l.customer_number, a.SALESPERSON_CODE,sum((l.sa_total_currency - l.line_discount )* DORC_INDICATOR) as line_net ,sum((l.SCC_product_net_sales )* DORC_INDICATOR) as prodNetSales ,sum((l. SCC_COMMISSION_CALC_AMOUNT )) as ComAmt

FROM [fin_prod].[dbo].[SA_DETAIL_LINES] L

left outer join customers c on c.customer_number = L.customer_number and c.sales_order_type <> 'EUORD'

left outer join customer_addresses A on A.customer_number = C.customer_number and A.address_code = L.delivery_address_code

left outer join salespersons s on s.salesperson_code = l.salesperson_code

left outer join product_master M on m.part_code = L.part_code

where l.transaction_date >= '16-MAY-2024' and l.transaction_date <= '31-MAY-2024'

and SCC_DO_NOT_INCLUDE_SALES_FLAG <> 'Y' and SCC_COMMISSION_NOT_PAID_FLAG <> 'Y' and SCC_TRANSACTION_QUANTITY <> 0 and (l.warehouse ='FG' or l.warehouse = 'BK') and( l.salesperson_code = '616' )

group by l.transaction_Date, s.location ,l.transaction_type, l.transaction_number, l.customer_number, a.SALESPERSON_CODE

order by l.transaction_type,l.transaction_number, l.transaction_Date, s.location , l.customer_number

Compare this to the details for the Commission Report: Regular Commission

Compare this to the details for the Commission Report: Bid Rebate

You can see the last 3 were added after the imports were put into ICM

Run the imports again and calculate and run this again to see if any differences.