VI job for Invoice data entry (A/R) 2

[Blorf]

I have a problem with a VI job. It just won't work!

It puts the data into the invoice header all right, but the user can't access it. Even after we do a rebuild sort and key files.

Further, the detail lines all come up blank!

All indications are that the job is running fine. I get OK on all validation.

Been pulling my hair out!

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[Omnicef]

Is the system set up to use batches?

Thnks in advance to all users out there who continually answer all of our questions. Although I try to answer some questions, I usually am asking.

 

[Blorf]

Yes, it is set up to use batches. I created the batch manualy, and put the batch number into my data set for import.

Is that a problem?

Thanks,
ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[Omnicef]

I'm not sure, I tried to do the same thing and was never successful. It worked perfect without batches however.

Thnks in advance to all users out there who continually answer all of our questions. Although I try to answer some questions, I usually am asking.

 

[Blorf]

I have a case with Best, and was told that there may be a problem with their code. I have not received resolution.

O well.



"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[toccata]

The following is a WORKING invoice import using a delimited (.csv) file. You can create a delimited file in Excel by loading the exported data and saving as a .csv file.

Modify as you see fit - see the GL accounts.

I hope this helps.


CONFIGURATION INFORMATION
-------------------------------------------------------------------------------------------------------------
FILE NAME: AR.INVOICE A/R Invoice Data Entry File
IMPORT FILE: C:\Test\tblInvoice.csv
FILE TYPE: DELIMITED DELIMITER: ,

INSERT ALL REQUIRED FIELDS DURING SETUP: Y
HEADER INFORMATION INCLUDED IN DETAIL LINES: Y
PASSWORD: NO

DATA ITEMS
-------------------------------------------------------------------------------------------------------------
ITEM ITEM NAME FG TYPE OPERATION COLUMN IMPORT MASK SUB-STRING
-------------------------------------------------------------------------------------------------------------
00001 INVOICE_NO H ACCOUNT REPLACE 00001 ####### No
00002 INVOICE_TYPE H STRING ASSIGN IN No
00003 DIVISION_NO H ZERO-FILL ASSIGN 00 No
00004 CUSTOMER_NO H ACCOUNT REPLACE 00004 ####### No
00005 INVOICE_DATE H DATE REPLACE 00002 MMDDYY No
00006 TERMS_CODE H ZERO-FILL ASSIGN 0 No
00007 TAX_SCHEDULE H STRING ASSIGN NONTAX No
00008 SALESPRSN_NO H ACCOUNT REPLACE 00007 #### No
00009 PRINT_INVOICE H YES/NO ASSIGN N No
00010 ADJUSTMENT? H YES/NO ASSIGN N No
00011 ACT_PRINTED? H YES/NO ASSIGN Y No
00012 INVALID_TX_CALC H YES/NO ASSIGN N No
00013 BATCH_FAX H YES/NO ASSIGN N No
00014 SALES_CODE L1 STRING REPLACE 00010 Start: 1 Len: 6 No
00015 SUBJ_TO_EXEMPT L4 YES/NO ASSIGN N No
00016 COMMISSIONABLE? L4 YES/NO ASSIGN Y No
00017 TAX_CLASS L4 STRING FILE ASSIGN AR.SALESCD:TAX_CLASS No
00018 LINE_INDEX L ZERO-FILL ASSIGN 1 No
00019 AMOUNT L4 NUMBER REPLACE 00009 No
00020 COMMENT_DESCRIP L3 STRING ASSIGN Invoice No
00021 MISC_G/L_ACCNT L2 ACCOUNT FILE ASSIGN AR.SALESCD:SALES_ACCNT No
00022 SALES_ACCNT L1 ACCOUNT FILE ASSIGN AR.SALESCD:SALES_ACCNT No
00023 COST_SALE_ACCNT L1 ACCOUNT ASSIGN 500000000 No
00024 INVENTORY_ACCNT L1 ACCOUNT ASSIGN 500000000 No
00025 QUANTITY L1 NUMBER CALCULATED No
CALC EXP: 1
00026 PRICE L1 NUMBER REPLACE 00009 No
00027 NON_TAX_AMT H NUMBER REPLACE 00009 No

RECORD SELECTION
-------------------------------------------------------------------------------------------------------------
ITEM TYPE DESCRIPTION COLUMN START LENGTH RELATION VALUE
-------------------------------------------------------------------------------------------------------------
00001 1 Sales Code Lines 00001 00001 00001 <>
PERFORM: AFTER WRITE ..\AR\ARWVIP;AR_INVOICE
00002 3 Comment Lines 00001 00001 00001 =
PERFORM: AFTER WRITE ..\AR\ARWVIP;AR_INVOICE
00003 SKIP Skip Record 00001 00001 00001 =

VALIDATION RECORDS
-------------------------------------------------------------------------------------------------------------
ITEM FIELD METHOD SKIP FILE/STRING/EXPRESSION
-------------------------------------------------------------------------------------------------------------
00001 INVOICE_TYPE STRING N CMDMINFCCAXD
00002 DIVISION_NO FILE N AR.DIV
00003 CUSTOMER_NO FILE N AR.CUST
00004 INVOICE_DATE EXPRESSION N AR7$(49,6)<>DIM(6)
00005 TERMS_CODE FILE N AR.TERMS
00006 TAX_SCHEDULE FILE N SY.TXSCHDH
00007 SALESPRSN_NO FILE N AR.SPERSON
00008 PRINT_INVOICE UPPERCASE N YN
00009 ADJUSTMENT? UPPERCASE N YN
00010 ACT_PRINTED? UPPERCASE N YN

 

[Blorf]

Thank you toccata, I will poke it in and see what happens.



"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[Blorf]

Toccata, can you tell me if this job is designed to work with batches? Also, can you tell me what the ARWVIP instruction does?

Either way, I will begin keying and see what happens.

Thanks,


"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[toccata]

The record selection section (ARWVIP)is the logic that determines line type.

If you're using batches, add that field to the import.

 

[Blorf]

No such luck.

Add batches or no, it puts the data in the tables, but mas doesn't let the user access it.

I appreciate your help though,


"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[toccata]

Does the invoice register display your entries?

This import pulls invoice data into the A/R invoice data entry files (AR7, AR8 and others) to be updated.

 

[Blorf]

Sadly, no.

It pretends that nothings there at all.


"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[Blorf]

I have the solution!

Turns out I made an error on my record selection.

It reads = blank, should be <>0 for sales code lines.

And I had to contact best, I feel like such a bufoon.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[jc29]

I had this same problem and I happen to see this posting and these posting gave me quite a few fix of my problems. BUT now I am stuck to this particular one, where after a successful(maybe) import the name of the customer won't show up. The customer no is there but doesn't pull the name of the company which usually display after the customer no and the magnifying glass on the header.

Please help and thanks in advance...

 

[Blorf]

Hi.

Are you posting the devision number? It will fail with out that.

If so, maybe post the job and we will see something.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[jc29]

Thanks for replying back...

Looks like I got it fixed by adding SOLD_TO field and map it to FILE ASSIGN AR:CUST:NAME

Anyway, the import went really well from SQL to MAS90 using ODBC connections.

But now my problem is when I am doing AR cash receipts entry, I am NOT able to post against the invoices and distribute check amount to the invoices because theres no invoice no shows up at all on the list and its greyed out. Once you select the customer it pulled out the correct current balance for that customer just fine. I checked the security clearance, just in case, it was fine.

I am not sure if my import was incomplete and missing something...

Any suggestion is highly appreciated, thanks...

here's the code:

CONFIGURATION INFORMATION
-------------------------------------------------------------------------------------------------------------
FILE NAME: AR.INVOICE A/R Invoice Data Entry File
DATA SOURCE: XXXXX
FILE TYPE: ODBC

INSERT ALL REQUIRED FIELDS DURING SETUP: Y
HEADER INFORMATION INCLUDED IN DETAIL LINES: Y
PASSWORD: NO

DATA ITEMS
-------------------------------------------------------------------------------------------------------------
ITEM ITEM NAME FG TYPE OPERATION COLUMN IMPORT MASK SUB-STRING
-------------------------------------------------------------------------------------------------------------
00001 INVOICE_NO H ACCOUNT REPLACE 00001 ####### No
00002 INVOICE_TYPE H STRING ASSIGN IN No
00003 DIVISION_NO H ZERO-FILL ASSIGN 00 No
00004 CUSTOMER_NO H ACCOUNT REPLACE 00007 ####### No
00005 INVOICE_DATE H DATE REPLACE 00006 YYYY/MM/DD No
00006 TERMS_CODE H ZERO-FILL ASSIGN 00 No
00007 TAX_SCHEDULE H STRING ASSIGN NONTAX No
00008 SALESPRSN_NO H ACCOUNT ASSIGN 0001 No
00009 PRINT_INVOICE H YES/NO ASSIGN N No
00010 ADJUSTMENT H YES/NO ASSIGN N No
00011 ACT_PRINTED H YES/NO ASSIGN Y No
00012 INVALID_TX_CALC H YES/NO ASSIGN N No
00013 BATCH_FAX H YES/NO ASSIGN N No
00014 SALES_CODE L1 STRING ASSIGN TRANS No
00015 SALES_ACCNT L1 STRING FILE ASSIGN AR.SALESCD:SALES_ACCNT No
00016 COST_SALE_ACCNT L1 STRING FILE ASSIGN AR.SALESCD:COST_OF_SALES No
00017 INVENTORY_ACCNT L1 STRING FILE ASSIGN AR.SALESCD:INVENTORY_ACCNTNo
00018 SUBJ_TO_EXEMPT L4 YES/NO ASSIGN N No
00019 COMMISSIONABLE L4 YES/NO ASSIGN N No
00020 TAX_CLASS L4 STRING ASSIGN NT No
00021 INVC_COMMENT H STRING REPLACE 00002 Start: 1 Len: 30 No
00022 CUSTOMER_PO_NO H STRING REPLACE 00012 Start: 1 Len: 15 No
00023 AMOUNT L4 NUMBER REPLACE 00016 No
00024 DESCRIPTION L4 STRING ASSIGN Transportation Services No
00025 NON_TAX_AMT H NUMBER REPLACE 00016 No
00026 PRICE L1 NUMBER REPLACE 00016 No
00027 LINE_INDEX L ZERO-FILL ASSIGN 00001 No
00028 QUANTITY L1 NUMBER RUN-TIME ASSN Runtime: 1 No
00029 UNIT_MEASURE L1 STRING ASSIGN EACH No
00030 SOLD_TO H STRING FILE ASSIGN AR.CUST:NAME No

RECORD SELECTION
-------------------------------------------------------------------------------------------------------------
ITEM TYPE DESCRIPTION COLUMN START LENGTH RELATION VALUE
-------------------------------------------------------------------------------------------------------------
00001 1 Sales Code Lines 00001 00001 00001 <> 0
PERFORM: AFTER WRITE ..\AR\ARWVIP;AR_INVOICE
00002 3 Comment Lines 00001 00001 00001 =
PERFORM: AFTER WRITE ..\AR\ARWVIP;AR_INVOICE
00003 SKIP Skip Record 00001 00001 00001 =

VALIDATION RECORDS
-------------------------------------------------------------------------------------------------------------
ITEM FIELD METHOD SKIP FILE/STRING/EXPRESSION
-------------------------------------------------------------------------------------------------------------
00001 INVOICE_TYPE STRING N CMDMINFCCAXD
00002 DIVISION_NO FILE N AR.DIV
00003 CUSTOMER_NO FILE N AR.CUST
00004 INVOICE_DATE EXPRESSION N AR7$(49,6)<>DIM(6)
00005 TERMS_CODE FILE N AR.TERMS
00006 TAX_SCHEDULE FILE N SY.TXSCHDH
00007 SALESPRSN_NO FILE N AR.SPERSON
00008 PRINT_INVOICE UPPERCASE N YN
00009 ADJUSTMENT UPPERCASE N YN