WikiAdmin: 1 revision imported

2023-07-14T13:00:35Z

1 revision imported

← Older revision

Revision as of 13:00, 14 July 2023

Intra>Chanson at 14:30, 11 May 2018

2018-05-11T14:30:29Z

New page

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

==Heading Query==
<PRE>
/* 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');
</PRE>

==Update Query==
<PRE>
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}' );
</PRE>

QueryGen Case: Create Inventory Transactions From Structure - Revision history

WikiAdmin: 1 revision imported

Intra>Chanson at 14:30, 11 May 2018