WikiAdmin: 1 revision imported

2023-07-14T13:00:35Z

1 revision imported

← Older revision

Revision as of 13:00, 14 July 2023

Intra>Chanson: /* Standard Cost Mismatch */

2018-05-23T16:14:02Z

Standard Cost Mismatch

New page

[[CATEGORY: QueryGen]]
=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=

<PRE>
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
</PRE>

=QueryGen Configuration=

==Source Query==
<PRE>
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)));
</PRE>

==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 (/* ... */).
<PRE>
/* 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');
</PRE>

==Update Query:==
In the script below - set the default date you wish to use as the effective date for standard cost.
<PRE>
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}' );
</PRE>

QueryGen Case: Fix Standard Cost - Revision history

WikiAdmin: 1 revision imported

Intra>Chanson: /* Standard Cost Mismatch */