Integration Manager question again 1

[jymm]

My accounting department is pulling a file from a vendor that tells us where
the GL debits should go in our GL (how nice). Problem is that in
Integration manager I can import all of the debits for each pieces of the
batch, but I am not sure how to insert the other side of the GL entry (the
credit). I have a script that is accumulating the debits for each document
AND I know which GL key I need the credits to go to (one per document) I just
do not know how to tell IM to insert that record - had thought about some
Union query but can not get my mind around it.

VB Script with a sql insert?

GP9 - SQL 2005 here

one straight excel file is the input - one batch per month, one document per
credit card user... so the file is pretty much
Person, Account piece 1, Account Piece 2, Amount, Description

so this is one side of each document - the other would go against cash (or
another hard coded account - since I will be doing this same process for
other input files).

So Ideally I would put out the two records for John, then the entry for Cash
to offset the two records for John--- then Kay's 25 records and the one
record against cash...

Make sense?

Suggestions (and no - I do not want to hand enter the offsets either in GP or Excel)?

 

[Alcar]

I ran into a similar problem with IM 8.0

MAke sure that in your integration, after opening the destination mappings you choose (from the left tre view) the destination folder...

That's where I found references on how to enter the debits and the credits amounts in the GL accounts.

hope this helps

Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[jymm]

sorry - I should have been a bit more clear - I do IM ALL the time --- bringing in both sides of GL transactions, SOP transactions, payroll... so this is not a problem --- problem exists in creating a record that does not exist on the input.

so my start file might be
Person Account1 Account2 Dollars Description
John 100000 001 10.00 Food
John 100000 002 88.00 Hotel
Kay 100000 003 12.00 Food
Kay 100000 004 99.00 Hotel
Key 100000 004 99.00 Hotel

Putting just this into GP (and getting the warning that things are out of balance) is a snap. What I want out of this is one batch with two 'docuemnts' One for John and one for Kay. I would expect John's document to have 3 entries - the two from above and a manufactured entry that would go to account 222222-000 for $98 (with 222222-000 being cash or similar). Kay's would have four entries (always one more than the record count in the input file so that you can put in the other side of the GL entry).

This more clear for what I want? I THINK it almost has to be done as an insert in the VB Scripts of the 'after docuement' to make it work... but there lies the rub. I have even thought of doing a macro in Excel to create the additional records, but I think IM should be able to handle this on it's own... maybe not though...

 

[RobertC22]

Have been stuck with this issue before with Integration Manager and general journals.

You _must_ have a record/ row in the source query for it to create the distribution line. The option I have used is to pre-process the data file (eg Excel) to add line(s).

(The only place Integration Manager doesnt need this is in Payables and Receivables when the import rule is set to 'Default Non-Imported' which occurs for the distribution types.)

The other option is to use the After Document VBscript to call using ADO a stored procedure within your MSSQL database that adds a distribution line equivalent to the difference to the generated journal.
If you use this technique, be sure that the REFERENCE field contains a unique value that you can use to match to identify the journal you have just integrated, as you will not know the journal number assigned.

A final idea (which I havent done with Integration Manager) would be to use the BEFORE INTEGRATION VBscript and using FSO object to preprocess the data file to add the necessary lines. I beleive the BEFORE integration should occur before the source queries are accessed.

------
Robert

 

[jymm]

I found another source for some info and this may just do the trick - take a gander at this

http://www.dynextra.com/