Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Martin Yergeau

Recommended Posts

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,

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...