Hi folks, I am out of ideas. Using MODIFY I always end up wi...

[Martin Yergeau]

Hi folks,

I am out of ideas.

Using MODIFY I always end up with a FOC1426 error and cant figure why.

But from another similar process, it work well where in both cases, its the same input data/format using the same master file.

The output target are also similar in several points. At some points, the one that produce the FOC1426, its a copy of the one that works.

I even have forced the field format in the TABLE FILE that produce the output (DATA ON) for MODIFY using: FieldName/format feature without any luck.

My master files (input / output) have the right format.

My SQL table (input / output) have the right format.

I even have recreated the master files (input / output).

Looking at the data (input / output), they are good without any overflow.

Doing a: HOLD FileName, display the expected format for fields.

If the DATA ON for the MODIFY step is a hold file using: ON TABLE HOLD AS FileName FORMAT ALPHA,

I receive the FOC1426 for a FieldA

If the DATA ON for the MODIFY step is a hold file using: ON TABLE HOLD AS FileName,

I receive the FOC1426 for a FieldX

The DATA ON FileName is in he same field order than the output for MODIFY FILE OutName

Also, MODIFY result in same number of input records then accepted (no reject) but nothing in target SQL table

So it it seems that its an odd error

Any ideas on how to fix that or at least find where the issue is

Regards,

[Warren Hinchliffe]

Any chance you can post the code and masters

[Martin Yergeau]

I know by experience that it may help you helping me but there is too many lines of code to post it all.

Here is some.

The most relevant regarding my issue.

In a sense, its simple but it doesnt work

DEFINE FILE INPUTVRCM_V2

SKUFST2 /A2 = EDIT(PRODUCTSKU, '99');

COUNTRYORIG /A3V = '&CNTRYORG';

COUNTRYNOTORIG /A3V = '&CNTRYNORG';

ROWID /I11 = &ROWID;

INVOICENUM /A12V = INVOICENUM;

YYYY /A4 = FTOA(YEAR,'(F4L)', YYYY);

MM /A2 = FTOA(MONTH,'(F2L)', MM);

YRMONTH /A6V = YYYY || MM;

SHIPTO /A9V = SHIPTO;

CUSTNM /A100V = TRIM_(BOTH, ' ', REPLACE(REPLACE(CUSTOMERNAME, '&', ''), '''', ''));

REVENUEDTSK /A8V = '&CUR_EOM.EVAL';

DIRECTYARD /A1V = IF DROPSHIP NE 0 THEN 'D' ELSE 'Y';

BUYERCODE /A8V = '';

MTXYR /A4V = TRIM_(BOTH, ' ', FPRINT(&MATRIXYEAR, 'I11', 'A11'));

MTXCNTR /A3V = TRIM_(BOTH, ' ', '&MATRIXCONTRACTNO.EVAL');

MTXNO /A2V = TRIM_(BOTH, ' ', '&MATRIXNO.EVAL');

MTXNAME /A100V = TRIM_(BOTH, ' ', '&MATRIXNAME.EVAL');

MTXCOMPANY /A3V = TRIM_(BOTH, ' ', '&COMPANY.EVAL');

MTXCNTRY /A2V = TRIM_(BOTH, ' ', '&COUNTRY.EVAL');

MTXPROV /A2V = TRIM_(BOTH, ' ', '&PROVINCE.EVAL');

MTXSITE /A4V = TRIM_(BOTH, ' ', '&SITECODE.EVAL');

MTXSLSVAL /A2V = TRIM_(BOTH, ' ', '&SALESVALUE.EVAL');

MTXFIXAMT /A1V = TRIM_(BOTH, ' ', '&FIXAMOUNTCONTROL.EVAL');

MTXINVOICEFIXAMT /P9.2 = &INVAMT;

MTXCOMPPCT /P10.4 = &COMPPCT;

MTXANTIPCT /P10.4 = &ANTIPCT;

MTXDUTYPCT /P10.4 = &DUTYPCT;

VRORIGINALDATE /YYMD = HDATE(VALUEDATE, 'YYMD');

REVENUEDT /YYMD = HDATE(REVENUEDATE, 'YYMD');

NETSALESAMT /P15.2 = IF NETSALESAMT LT 0 AND GROSSSALESAMT GT 0 THEN GROSSSALESAMT ELSE NETSALESAMT;

END

TABLE FILE INPUTVRCM_V2

PRINT POSTEDDATESK

POSTEDDATE

POSTEDBY

APPLYDATESK

APPLYDATE

VRORIGINALDATE AS 'VRORIGINALDATE'

REVENUEDT AS 'REVENUEDATE'

CUSTNUM AS 'CUSTNUM'

SHIPTONUM/A14V AS 'SHIPTONUM'

CUSTNM AS 'CUSTOMERNAME'

SOLDTOSTATE/A2V AS 'SOLDTOSTATE'

BUYERCODE AS 'BUYERCODE'

ORDERNUM AS 'ORDERNUM'

SHIPDATE

ISORIGCOUNTRYNAME AS 'ISORIGCOUNTRYNAME'

INVOICECURRENCYSK AS 'INVOICECURRENCYSK'

&SLSFLD/P15.2 AS 'SALESAMOUNT'

COMPUTE COMP_PCT_AMOUNT /P17.2 = IF ISORIGCOUNTRYNAME EQ '' THEN 0

ELSE IF COUNTRYORIG EQ '' THEN 0

ELSE IF SKUFST2 NE '15' AND ISORIGCOUNTRYNAME EQ COUNTRYORIG THEN (&SLSFLD.EVAL - ((&SLSFLD.EVAL * &COMPPCT / 100)) - TMSCOSTAMT) * &COMPPCT / 100

ELSE 0;

COMPUTE ANTI_PCT_AMOUNT /P17.2 = IF ISORIGCOUNTRYNAME EQ '' THEN 0

ELSE IF COUNTRYORIG EQ '' THEN 0

ELSE IF SKUFST2 NE '15' AND ISORIGCOUNTRYNAME EQ COUNTRYORIG THEN (&SLSFLD.EVAL - ((&SLSFLD.EVAL * &ANTIPCT / 100)) - TMSCOSTAMT) * &ANTIPCT / 100

ELSE 0;

COMPUTE DUTY_PCT_AMOUNT /P17.2 = IF ISORIGCOUNTRYNAME EQ '' THEN 0

ELSE IF COUNTRYNOTORIG EQ '' THEN 0

ELSE IF (ISORIGCOUNTRYNAME NE COUNTRYNOTORIG) AND (ISORIGCOUNTRYNAME NE 'USA') THEN (&SLSFLD.EVAL - ((&SLSFLD.EVAL * &DUTYPCT / 100)) - TMSCOSTAMT) * &DUTYPCT / 100

ELSE 0;

COMPUTE INVOICE_AMOUNT /P17.2 = IF '&FIXAMT.EVAL' EQ 'S' AND DUTY_PCT_AMOUNT EQ 0 AND INVOICELINE EQ '1' THEN 0

ELSE IF INVOICELINE EQ '1' THEN MTXINVOICEFIXAMT

ELSE 0;

COMPUTE DUTY_TOT_AMOUNT /P17.2 = INVOICE_AMOUNT + COMP_PCT_AMOUNT + ANTI_PCT_AMOUNT + DUTY_PCT_AMOUNT;

COMPUTE GLACCT /A30 = '400000-' || GLACCTCOMPANY || '-' || GLACCTLOCATION || '-' || GLACCTDEPT || '-' || GLACCTPRODUCT;

COMPUTE GLACCTCONTEXT /A60 = 'DUTY';

GLACCTNATURAL/A6V AS 'GLACCTNATURAL'

GLACCTCOMPANY/A3V AS 'GLACCTCOMPANY'

GLACCTLOCATION/A4V AS 'GLACCTLOCATION'

GLACCTDEPT/A4V AS 'GLACCTDEPT'

GLACCTPRODUCT/A4V AS 'GLACCTPRODUCT'

MTXNAME/A100V AS 'MATRIXNAME'

COUNTRYORIG/A2V AS 'MATRIX_COUNTRYORIG'

COUNTRYNOTORIG/A2V AS 'MATRIX_COUNTRYNOTORIG'

MTXSITE/A4V AS 'MATRIX_SITECODE'

MTXCOMPANY/A3V AS 'MATRIX_COMPANY'

MTXCNTRY/A2V AS 'MATRIX_COUNTRY'

MTXPROV/A2V AS 'MATRIX_PROVINCE'

MTXSLSVAL/A2V AS 'MATRIX_SALESVALUE'

MTXFIXAMT/A1V AS 'MATRIX_FIXAMOUNTCONTROL'

MTXINVOICEFIXAMT AS 'MATRIX_INVOICEFIXAMT'

MTXCOMPPCT AS 'MATRIX_COMP_PCT'

MTXANTIPCT AS 'MATRIX_ANTI_PCT'

MTXDUTYPCT AS 'MATRIX_DUTY_PCT'

BY COMPANYID/A4V AS 'COMPANY'

BY INVOICENUM AS 'INVOICENUM'

BY INVOICELINE AS 'INVOICELINE'

BY MTXYR AS 'MATRIXYEAR'

BY MTXCNTR AS 'MATRIXCONTRACTNO'

BY MTXNO AS 'MATRIXNO'

BY REVENUEDTSK AS 'REVENUEDATESK'

BY YRMONTH AS 'YRMONTH'

BY SITECODE AS 'SITECODE'

BY YARDCODE AS 'YARDCODE'

BY BUYINGMASTERGROUP AS 'MASTBUYGRP'

BY BUYINGGROUPCODE AS 'BUYGRP'

BY REPGROUPCODE AS 'SALESGRP'

BY REPCODE AS 'REPCODE'

BY SHIPTO AS 'SHIPTO'

BY CAT1CODE AS 'CATEGORY1'

BY CAT2CODE AS 'CATEGORY2'

BY CAT3CODE AS 'CATEGORY3'

BY CAT4CODE AS 'CATEGORY4'

BY PRODUCTSKU AS 'SKUCODE'

BY ROWID AS 'ROWID'

WHERE GLACCTNATURAL EQ '400000';

WHERE INVOICETYPE NE 'VR';

WHERE INVOICETYPE NE 'VL';

WHERE INVOICETYPE NE 'VS';

WHERE &SLSFLD.EVAL NE 0;

WHERE COMPANYID EQ '&CIE';

WHERE SITECODE NE '&SITECD';

WHERE SOLDTOCOUNTRY EQ '&CNTRY';

WHERE SHIPTOSTATE EQ '&PROV';

WHERE CAT1CODE EQ '&CAT1';

WHERE CAT2CODE EQ '&CAT2';

WHERE CAT3CODE EQ '&CAT3';

WHERE CAT4CODE EQ '&CAT4';

WHERE PRODUCTSKU EQ '&SKU';

ON TABLE HOLD AS FINALFILE FORMAT ALPHA

END

-RUN

 

MODIFY FILE OUTPUTDUTYCM_V2

FIXFORM FROM FINALFILE

MATCH INVOICENUM INVOICELINE MATRIXYEAR MATRIXCONTRACTNO MATRIXNO VRORIGINALDATE

ON MATCH REJECT

ON NOMATCH INCLUDE

DATA ON FINALFILE

END

-RUN

 

 

FILENAME=INPUTVRCM_V2, SUFFIX=SQLMSS , $

SEGMENT=INPUTVRCM_V2, SEGTYPE=S0, $

FIELDNAME=COMPANYID, ALIAS=CompanyID, USAGE=A10V, ACTUAL=A10V, $

FIELDNAME=INVOICENUM, ALIAS=InvoiceNum, USAGE=A12V, ACTUAL=A12V, $

FIELDNAME=INVOICELINE, ALIAS=InvoiceLine, USAGE=A8V, ACTUAL=A8V, $

FIELDNAME=INVOICETYPE, ALIAS=InvoiceType, USAGE=A3V, ACTUAL=A3V, $

FIELDNAME=INVOICESUFFIX, ALIAS=InvoiceSuffix, USAGE=A4V, ACTUAL=A4V, $

FIELDNAME=UNAPPLIEDCASH, ALIAS=UnappliedCash, USAGE=I11, ACTUAL=I4, $

FIELDNAME=STARTUP, ALIAS=StartUp, USAGE=I11, ACTUAL=I4, $

FIELDNAME=CREDITMEMO, ALIAS=CreditMemo, USAGE=I11, ACTUAL=I4, $

FIELDNAME=DEBITNOTE, ALIAS=DebitNote, USAGE=I11, ACTUAL=I4, $

FIELDNAME=SALEDATESK, ALIAS=SaleDatesk, USAGE=I11, ACTUAL=I4, $

FIELDNAME=VALUEDATE, ALIAS=ValueDate, USAGE=HYYMDs, ACTUAL=HYYMDs, $

FIELDNAME=REVENUEDATESK, ALIAS=RevenueDateSk, USAGE=I11, ACTUAL=I4, $

FIELDNAME=REVENUEDATE, ALIAS=RevenueDate, USAGE=HYYMDs, ACTUAL=HYYMDs, $

FIELDNAME=POSTEDDATESK, ALIAS=PostedDateSk, USAGE=I11, ACTUAL=I4, $

FIELDNAME=POSTEDDATE, ALIAS=PostedDate, USAGE=YYMD, ACTUAL=DATE, $

FIELDNAME=POSTEDBY, ALIAS=PostedBy, USAGE=A75V, ACTUAL=A75V, $

FIELDNAME=APPLYDATESK, ALIAS=ApplyDateSk, USAGE=I11, ACTUAL=I4, $

FIELDNAME=APPLYDATE, ALIAS=ApplyDate, USAGE=YYMD, ACTUAL=DATE, $

FIELDNAME=SHIPDATE, ALIAS=ShipDate, USAGE=YYMD, ACTUAL=DATE, $

FIELDNAME=YEAR, ALIAS=Year, USAGE=I6, ACTUAL=I2, $

FIELDNAME=MONTH, ALIAS=Month, USAGE=I6, ACTUAL=I2, $

FIELDNAME=YESTERDAY, ALIAS=Yesterday, USAGE=I11, ACTUAL=I4, $

FIELDNAME=FIRSTDAYMONTH, ALIAS=FirstDayMonth, USAGE=I11, ACTUAL=I4, $

FIELDNAME=LASTDAYMONTH, ALIAS=LastDayMonth, USAGE=I11, ACTUAL=I4, $

FIELDNAME=PREVIOUSMONTH, ALIAS=PreviousMonth, USAGE=I11, ACTUAL=I4, $

FIELDNAME=FIRSTDAYPREVIOUSMONTH, ALIAS=FirstDayPreviousMonth, USAGE=I11, ACTUAL=I4, $

FIELDNAME=LASTDAYPREVIOUSMONTH, ALIAS=LastDayPreviousMonth, USAGE=I11, ACTUAL=I4, $

FIELDNAME=PREVIOUSYEAR, ALIAS=PreviousYear, USAGE=I11, ACTUAL=I4, $

FIELDNAME=MONTHWORKINGDAYS, ALIAS=MonthWorkingDays, USAGE=I11, ACTUAL=I4, $

FIELDNAME=ORDERNUM, ALIAS=OrderNum, USAGE=I11, ACTUAL=I4, $

FIELDNAME=DROPSHIP, ALIAS=DropShip, USAGE=I11, ACTUAL=I4, $

FIELDNAME=DIRECTYARD, ALIAS=DirectYard, USAGE=A6V, ACTUAL=A6V, $

FIELDNAME=SITECODE, ALIAS=SiteCode, USAGE=A10V, ACTUAL=A10V, $

FIELDNAME=YARDCODE, ALIAS=YardCode, USAGE=A16V, ACTUAL=A16V, $

FIELDNAME=BUYINGMASTERGROUP, ALIAS=BuyingMasterGroup, USAGE=A100V, ACTUAL=A100V, $

FIELDNAME=BUYINGGROUPCODE, ALIAS=BuyingGroupCode, USAGE=A20V, ACTUAL=A20V, $

FIELDNAME=REPGROUPCODE, ALIAS=RepGroupCode, USAGE=A16V, ACTUAL=A16V, $

FIELDNAME=REPCODE, ALIAS=RepCode, USAGE=A16V, ACTUAL=A16V, $

FIELDNAME=REPNAME, ALIAS=RepName, USAGE=A60V, ACTUAL=A60V, $

FIELDNAME=CUSTGROUPCODE, ALIAS=CustGroupCode, USAGE=A8V, ACTUAL=A8V, $

FIELDNAME=CUSTNUM, ALIAS=CustNum, USAGE=I11, ACTUAL=I4, $

FIELDNAME=SHIPTO, ALIAS=ShipTo, USAGE=A20V, ACTUAL=A20V, $

FIELDNAME=SHIPTONUM, ALIAS=ShipToNum, USAGE=A14V, AC

[David Beagan]

You have SET HOLDLIST=PRINTONLY There arent any copies of FINALFILE lurking in an app folder

[Martin Yergeau]

Yes

SET ASNAMES = ON

SET HOLDLIST = PRINTONLY

And No, FINALFILE is only hold in memory. Never saved in any app folder.

[John Gelona]

Ive never gotten MODIFY to work with V fields. Have you looked at the HOLD file, I mean opened it with an editor and looked at it In the past, the variable length fields are prefixed with a number that shows that actual length. I dont think MODIFY has ever been updated to use variable length fields. Maybe it has because it has been 20 or more years since I tried. You could also try FORMAT COMT or TABT.

[Martin Yergeau]

john.gelona:

 

Ive never gotten MODIFY to work with V fields

 

 

It does, I have two other fex that use similar coding and they work.

(also working from the old fex version from WF77 since at least 5 years)

The one that I am trying to make it run is a copy from the two others that does work.

I have no choice to have several similar fex because there are slight differences that will become too complex trying to manage within only one fex

No luck with either COMT or TABT

[Ian Dalton]

MartinY:

 

&SLSFLD/P15.2 AS SALESAMOUNT

 

 

My gut feeling is it is something to do with the line above

and this line below

FIELDNAME=SALESAMOUNT, ALIAS=SalesAmount, USAGE=P17.2, ACTUAL=P8, $

[Martin Yergeau]

Nop.

I changed it just to be sure that both have same format but it doesnt solved the problem

[David Beagan]

Seems like some buggish behavior. I would start commenting out fields from the TABLE FILE and run it to see if it is one particular field that is triggering the issue.

[Martin Yergeau]

Seems that I finally found it

I gave other eyes a look and they found that two fields were inverted which creates the FOC1426

Thanks for your help

[David Beagan]

Glad you solved it. How were the fields inverted Wrong data values

[Martin Yergeau]

In the order they were listed in the TABLE FILE PRINT BY END

Seems that MODIFY is not working with field reference but is positional : Fld A with Posit 1, Fld B with Posit 2,

[David Beagan]

Thats good to know about, thanks.