QueryGen Case: Create Inventory Transactions From Structure

Case: Repairing missing inventory transaction detail from mixes

In some versions of Keystone where mixes can be created but the process structure flag is turned off Inventory transactions would only be created for the mix and not the mix components. This procedure will create transactions for the ingredients.

QueryGen Settings

Source query

Note

• This contains many more fields than required for diagnostic reporting.
• The case statement lists only some of the possible units of measure conversions required.

select PC.PRODUCT_CODE "PCP",STR.QTY_ASSEMBLY,-X.QTY_CHANGE "Mix Qty",
PC.UNIT_OF_MEASURE "Inv UM",pc.ingredient_type,str.comp_ums "Mix UM",str.assy_product_code "MixID",
STR.COMP_UMS||PC.UNIT_OF_MEASURE COMPSTRUM,PC.DESCRIPTION_1 "ING NAME",PC.INVENTORY_GL "PCINVGL",pc.costofsales_gl "COGS",
(select sum(s2.qty_assembly) from artprstr s2 
 inner join artprod p2 on p2.product_code=s2.comp_product_code and p2.ingredient_type='C'
 where s2.assy_product_code=str.assY_PRODUCT_CODE)  "CemPrYd",
CASE STR.COMP_UMS||PC.UNIT_OF_MEASURE
WHEN 'LBTN' THEN -X.QTY_CHANGE*STR.QTY_ASSEMBLY/2000
WHEN 'OZGL' THEN -X.QTY_CHANGE*STR.QTY_ASSEMBLY/128
WHEN '/CGL' THEN -X.QTY_CHANGE*STR.QTY_ASSEMBLY*(select sum(s2.qty_assembly) from artprstr s2 
 inner join artprod p2 on p2.product_code=s2.comp_product_code and p2.ingredient_type='C'
 where s2.assy_product_code=str.assY_PRODUCT_CODE)/200000
ELSE  -X.QTY_CHANGE*STR.QTY_ASSEMBLY END "New Ing Qty",
PC.STANDARD_COST,
X.* from artprtrx x
inner join artprod pa on pa.product_code=x.product_code
INNER JOIN ARTPRSTR STR ON STR.ASSY_PRODUCT_CODE=X.PRODUCT_CODE
INNER JOIN ARTPROD PC ON STR.COMP_PRODUCT_CODE=PC.PRODUCT_CODE
where pa.product_type='M'
AND TRX_TYPE='S'
ORDER BY TRX_DATE DESC,session_no,trans_no

Heading Query

/* drop table tmp_sno; */
CREATE  TABLE TMP_SNO(SNO INTEGER);
INSERT INTO TMP_SNO (SNO)  SELECT session_no FROM CCP_SESSION_BEGIN('ADMIN', 'Insert Prod Trx from structure', '127.0.0.1', '3.X');

Update Query

INSERT INTO artprtrx /* Updating MIX: {MixID} */
( SESSION_NO, TRANS_NO, PRODUCT_CODE, UNIT_OF_MEASURE, QTY_CHANGE, COST, TRX_TYPE, TRX_DATE, TRX_TIME, INVENTORY_GL, OFFSET_GL, SRC_TRANS_NO, SRC_LINE_NO, SRC_DOC_NO )  VALUES 
( (SELECT SNO FROM TMP_SNO), {PCP|INC=1}, '{PCP}', '{Inv UM}', {New Ing Qty}, 0, 'S', '{TRX_DATE}', {TRX_TIME}, '{PCINVGL}', '{COGS}', {SRC_TRANS_NO}, {SRC_LINE_NO}, '{SRC_DOC_NO}'    );