Shannon will pass the error from ICM
https://spartanchemicalprod.cloud.varicent.com/payeewebv2/PresenterReport/32
at the top is the number of the report it is running
Correct SCC_TRANSACTION_QUANTITY in SA_DETAIL_LINES
Correct SA_DETAIL_LINE_QTYS there will be 2 lines for 1 real line with a different UOM.
Delete that wrong UOM line from the table. In this case it is a "PA" line along with the correct "CA".
Double check by running SQL to see that you have only 1 line with the same qty.
Select * FROM SA_DETAIL_LINES WHERE (TRANSACTION_NUMBER = 628862)
SELECT TOP (200) GEM_DBKEY, COMPANY_CODE, DIVISION, TRANSACTION_TYPE, TRANSACTION_NUMBER, TRANSACTION_LINE_NUMBER, UNIT_OF_MEASURE, IC_PRODUCT_UOM_TYPE, INVOICE_QUANTITY, CREDIT_QUANTITY,
REBATE_QUANTITY, TRANSFER_QUANTITY, SCC_OUTSTANDING_QTY, GEM_ARCHIVE_FLAG
FROM SA_DETAIL_LINE_QTYS WHERE (TRANSACTION_NUMBER = 628862)
RUN SQL to find the line that is trying to be added twice.
Change these 3 to find the error on the transaction.
It is usually an issue where the qty on the line and the
(L.transaction_date >= '16-Apr-2024'
and L.transaction_date <= '30-Apr-2024')
and L.transaction_number = 628862
SELECT distinct
(L.company_code + L.Division + L.transaction_type + left(L.transaction_number,7) + '-000' + left(L.transaction_line_number,4) ) as TransID
, (L.transaction_type ) as TransactionType
, (L.transaction_type + left(L.transaction_number,7) ) as TransactionNumber
, L.transaction_line_number as TransactionLineNumber
, (L.transaction_line_number ) as TranLineNumber
, H.SCC_COMMISSION_RUN_NUMBER as CommissionRunNumber
, ( left(Year( L.transaction_Date),4) + '0' + left(Month( L.transaction_Date),2) ) as YearMonth
, L.transaction_Date as TransactionDate
, (L.order_number ) as OrderNumber
, (L.order_line_number ) as OrderLineNumber
, L.SCC_COMMISSION_PART_CODE as PartCode
, L.Part_Code as SoldPartCode
, L.Warehouse as Warehouse
, L.SCC_Base_Price_Code as SoldPriceCode
, L.SCC_Commission_price_code as SccBasePriceCode
, WD.SCC_WEIGHT_DISCOUNT_CODE as WeightDiscountCode
, QD.SCC_ICM_PRODUCT_GROUP as QuantityDiscountCode
, WD.SCC_DISC_MULTIPLIER as SccDiscountPercent1
, QD.SCC_DISC_MULTIPLIER as SccDiscountPercent2
, L.SCC_COMMISSION_DISCOUNT_PCT_1 as SccRossWgtPct
, L.SCC_COMMISSION_DISCOUNT_PCT_2 as SccRossQtyPct
, L.Unit_Price as UnitPrice
, ( (L.SA_Total_Currency - L.Line_Discount) * DC.DorC_Indicator) as CalculatedNetSales
, ( ( Q.Invoice_quantity + Q.Credit_quantity ) * DC.DorC_Indicator) as CalculatedQuantity
, (L.CUSTOMER_NUMBER + L.Delivery_ADDRESS_CODE ) as CustomerAddressCode
, SCC_Commission_Deduction_Flag as CommissionDeductionFlag
, ( ( SCC_Commission_Deduction_Amount ) * DC.DorC_Indicator) as CommissionDeductionAmount
, SCC_Commission_CALC_Amount as VerifyCommissionAmount
, c.SCC_DISTR_NUMBER as DistributorID
, L.CUSTOMER_NUMBER as CustomerNumber
, L.Delivery_ADDRESS_CODE as DeliveryAddrCode
, l.salesperson_code as RM#ForImport
, DC.DorC_Indicator as signMultiplier
, L.SCC_Pricing_Product_group as PricingProductGroup
, M.Product_group as ReportingProductGroup
, l.salesperson_code as RegionID
, Pay.Payee_Number as PayeeID
, Pay.location as Division
, Pay.SCC_Payee_Adjustment_Percent as PayeeAdjustmentPercent
, L.SCC_COMMISSION_CALC_AMOUNT as CommissionAmountOld
, RG.SALES_COMMISSION_CODE as SalesCommissionCode
, RG.SCC_REGION_ADJUSTMENT_PERCENT as RegionAdjustmentPercent
, ((L.SCC_PRODUCT_NET_SALES ) * DC.DorC_Indicator) as ProductNetSales
, DC.SCC_ICM_PLAN_TYPE_COMPONENT as CommonComponent
, 'Regional Managers' as CompPlanID
, c.price_code as Default_SCC_Base_Price_Code
, (SCC_COM_LINE_ADJUSTMENT_PERCENT *.01) as LineAdjustmentPercent
, (L.Source_trans_type + left(L.source_trans_number,7) + '-0' + left(L.Source_trans_line_number,4) ) as SourceTransID
, l.SCC_SALES_PRICE_OVERRIDE as SalesPriceOverride
, l.SCC_COMMISSION_NOT_PAID_FLAG as DoNotPayCommissionFlag
, l.SCC_DO_NOT_INCLUDE_SALES_FLAG as DoNotIncludeSalesFlag
from SA_DETAIL_LINES L
join SA_DETAIL_HEADERS H
On H.company_code = '1'
and H.division = '00'
and H.TRANSACTION_TYPE = L.TRANSACTION_TYPE
and H.transaction_number = L.transaction_number
join SOP_TRANSACTION_TYPES DC
On DC.company_code = '1'
and DC.division = '00'
and DC.TRANSACTION_TYPE = L.TRANSACTION_TYPE
JOIN Product_Master M
On M.Part_code = L.Part_Code
left outer join SCC_region_commission_Codes RG
On RG.salesperson_code = l.salesperson_code
and RG.end_date >= '2100-01-01'
left outer join SCC_Payee PAY
On Pay.salesperson_code = l.salesperson_code
and Pay.end_date >= '2100-01-01'
and Pay.SCC_ICM_ACTIVE_CODE = 'ACTIVE'
and Pay.SCC_STATUS_CODE = 'A'
left outer join SA_Detail_Line_Qtys Q
On Q.transaction_Type = L.transaction_Type
and Q.transaction_number = L.transaction_number
and Q.transaction_line_number = L.transaction_line_number
left outer join Price_Codes P
On P.Price_code = L.SCC_Base_Price_Code
left outer join Customers C
On C.Customer_number = L.Customer_number
left outer join SCC_COMMISSION_WEIGHT_DISC WD
On WD.Price_Code = L. SCC_COMMISSION_PRICE_CODE
and WD.Scc_Disc_Percent = L.SCC_COMMISSION_DISCOUNT_PCT_1
left outer join SCC_ICM_product_groups QD
On QD.SCC_Pricing_product_group = L.SCC_Pricing_Product_group
and QD.Scc_Disc_Percent = L.SCC_COMMISSION_DISCOUNT_PCT_2
where
(L.transaction_date >= '16-Apr-2024'
and L.transaction_date <= '30-Apr-2024')
and L.transaction_number = 628862
and L.Part_code <> ''
and L.transaction_line_number < 10
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article