QueryGen Case: Fix Standard Cost

Standard Cost Mismatch

Case - Standard cost in products mismatches the current standard cost record in product transaction table.

When Standard Cost for a product is updated through Product Maintenance a corresponding cost transaction is created into ARTPRTRX. When costs are updated by other means these transactions need to be generated for proper function of Keystone.

Diagnostic query

select P.PRODUCT_CODE,OH.STANDARD_COST "TRX STD COST",P.STANDARD_COST "PRD STD COST",OH.*,P.UNIT_OF_MEASURE,P.INVENTORY_GL,
(SELECT MAX(TRX_DATE) FROM artprtrx x where x.product_code=p.product_code and trx_type='C') LAST_COST
 FROM  ARTPROD P
LEFT OUTER JOIN ARPPRTRX_QTY_ON_HAND(P.PRODUCT_CODE,CAST('TODAY' AS DATE),2359,0,1) OH ON 1=1
where p.stocked='Y'
AND P.STANDARD_COST<>OH.STANDARD_COST

QueryGen Configuration

Source Query

select P.PRODUCT_CODE,OH.STANDARD_COST "TRX STD COST",P.STANDARD_COST "PRD STD COST",OH.*,P.UNIT_OF_MEASURE,P.INVENTORY_GL,
(SELECT MAX(TRX_DATE) FROM artprtrx x where x.product_code=p.product_code and trx_type='C') LAST_COST
 FROM  ARTPROD P
LEFT OUTER JOIN ARPPRTRX_QTY_ON_HAND(P.PRODUCT_CODE,CAST('TODAY' AS DATE),2359,0,1) OH ON 1=1
where p.stocked='Y'
AND P.STANDARD_COST<>OH.STANDARD_COST
and  ((
    (SELECT MAX(TRX_DATE) FROM artprtrx x where x.product_code=p.product_code and trx_type='C')  IS NULL)
OR ((SELECT MAX(TRX_DATE) FROM artprtrx x where x.product_code=p.product_code and trx_type='C')  <CAST('TODAY' AS DATE)));

Header

The purpose of the header text is to create a new session number and store it into the temporary table TMP_SNO. If the Table TMP_SNO exists keep the "Create" portion in comments (/* ... */).

/* CREATE  TABLE TMP_SNO(SNO INTEGER); */ DELETE FROM TMP_SNO;
INSERT INTO TMP_SNO (SNO)  SELECT session_no FROM CCP_SESSION_BEGIN('ADMIN', 'Fix Std Cost artprtrx', '127.0.0.1', '3.X');

Update Query:

In the script below - set the default date you wish to use as the effective date for standard cost.

INSERT INTO 
ARTPRTRX
( SESSION_NO, TRANS_NO, PRODUCT_CODE, UNIT_OF_MEASURE, QTY_CHANGE, COST, TRX_TYPE, TRX_DATE, TRX_TIME, INVENTORY_GL )  VALUES 
( (SELECT MAX(SNO) FROM TMP_SNO), {PRODUCT_CODE|INC=1}, '{PRODUCT_CODE}', '{UNIT_OF_MEASURE}', 0 , {PRD STD COST}, 'C', '<as of date>', 0, '{INVENTORY_GL}'    );