Linking POTRAN with GLTRAN

I´m creating a report which needs to connect the INVTID from the POTRAN  
table with the movement created on GLTRAN  on the same accounts. 

But GLTRAN  only has  TransDesc, since an InvtID can have the same 
description as another but equal price and quantity bought on the POTRAN  
table this is provoking duplicate lines on the report if the report try to 
use GLTran to get all the transactions created for the specific POTRAN Batch 

What is the procedure by which GL Tran batches are created coming from an 
specific POTran Batch?

How are the specific lines  created on the GLTran Batch coming from an 
specific POTranBatch?

And most important, how can I link the POTran Batch with the GLTran Batch in 
an unique way in which only one specific line from the POTranbatch creates an 
specific line on the GLTranBatch?

I have not looked at POTran to GLTran specifically in quite some time. 
However, in general and unfortunately, there is no specific cross reference 
maintained between the exact transactions in a subledger that contribute the 
specific transactions in the general ledger.

From an accounting perspective this was probably originally concidered 
reasonable and appropriate.  The purpose of the general ledger is to record 
activity related to specific combinations of Account, Subaccount and fiscal 
period (and Project and Task if integration wit hproject controller is 
enabled).  Anything about vendors, customers, inventory, etc. is not 
considered relivant to GL and is assumed to be tracked when needed in the 
corresponding subledger.

I'm OK with that view from an accounting perspective.  However, from a 
database design perspective and from a data mining/business intelegence 
support perspective, I'm generally of the opinion that a system should 
maintain complete cross reference information anytime data is transmitted 
from one place to another.  Unfortunately this design phylosophy was not 
implemented in the Solomon database.

Note that given the fact that they didn't explicitly implement cross 
reference tables combined with the fact that Solomon supports the ability to 
"post in summary" from the subledger to the general ledger, there is
always 
the potential that "detail is lost" as data moves to GL.  YOu may have
a 
dozen transastion n the subledger that identify things like specific 
customers/vendors/inventory items/vouchers/invoces/etc./etc but if "post in

summary" is turned on and all 12 of those transaction have the exactly same

value in the Account/Sub/Project/Task fields, then they will generate a 
single GLTRan that is the consolidated Debit/Credit amounts of the 12 
seperate transactions.

so, after my characteristically long winded response, the answer to your 
final question is most likely that there is no way to ensure a unique 
one-to-one link between POTran and GLTran.

"almondragon" <almondragon@discussions.microsoft.com> wrote in
message 
news:6CCF5046-6F0B-4223-91F5-89ECB4DFF5BA@microsoft.com...
> I´m creating a report which needs to connect the INVTID from the POTRAN
> table with the movement created on GLTRAN  on the same accounts.
>
> But GLTRAN  only has  TransDesc, since an InvtID can have the same
> description as another but equal price and quantity bought on the POTRAN
> table this is provoking duplicate lines on the report if the report try to
> use GLTran to get all the transactions created for the specific POTRAN 
> Batch
>
> What is the procedure by which GL Tran batches are created coming from an
> specific POTran Batch?
>
> How are the specific lines  created on the GLTran Batch coming from an
> specific POTranBatch?
>
> And most important, how can I link the POTran Batch with the GLTran Batch 
> in
> an unique way in which only one specific line from the POTranbatch creates

> an
> specific line on the GLTranBatch?
> 

Thomas:

Thanks for your kind and long answer  is very rare to find people willing to 
give long answers on their e-mails  which have enough information to take a 
reasoned and clear decision about a certain matter. 

The info you sent me is clear  and exactly reponds the question I was 
asking. Thanks  you very much  for taking your time to be clear and exact on 
your repply. 


"Thomas Malia" wrote:

> I have not looked at POTran to GLTran specifically in quite some time. 
> However, in general and unfortunately, there is no specific cross reference

> maintained between the exact transactions in a subledger that contribute
the 
> specific transactions in the general ledger.
> 
> From an accounting perspective this was probably originally concidered 
> reasonable and appropriate.  The purpose of the general ledger is to record

> activity related to specific combinations of Account, Subaccount and fiscal

> period (and Project and Task if integration wit hproject controller is 
> enabled).  Anything about vendors, customers, inventory, etc. is not 
> considered relivant to GL and is assumed to be tracked when needed in the 
> corresponding subledger.
> 
> I'm OK with that view from an accounting perspective.  However, from a 
> database design perspective and from a data mining/business intelegence 
> support perspective, I'm generally of the opinion that a system should 
> maintain complete cross reference information anytime data is transmitted 
> from one place to another.  Unfortunately this design phylosophy was not 
> implemented in the Solomon database.
> 
> Note that given the fact that they didn't explicitly implement cross 
> reference tables combined with the fact that Solomon supports the ability
to 
> "post in summary" from the subledger to the general ledger, there
is always 
> the potential that "detail is lost" as data moves to GL.  YOu may
have a 
> dozen transastion n the subledger that identify things like specific 
> customers/vendors/inventory items/vouchers/invoces/etc./etc but if
"post in 
> summary" is turned on and all 12 of those transaction have the exactly
same 
> value in the Account/Sub/Project/Task fields, then they will generate a 
> single GLTRan that is the consolidated Debit/Credit amounts of the 12 
> seperate transactions.
> 
> so, after my characteristically long winded response, the answer to your 
> final question is most likely that there is no way to ensure a unique 
> one-to-one link between POTran and GLTran.
> 
> "almondragon" <almondragon@discussions.microsoft.com> wrote
in message 
> news:6CCF5046-6F0B-4223-91F5-89ECB4DFF5BA@microsoft.com...
> > I´m creating a report which needs to connect the INVTID from the
POTRAN
> > table with the movement created on GLTRAN  on the same accounts.
> >
> > But GLTRAN  only has  TransDesc, since an InvtID can have the same
> > description as another but equal price and quantity bought on the
POTRAN
> > table this is provoking duplicate lines on the report if the report
try to
> > use GLTran to get all the transactions created for the specific POTRAN

> > Batch
> >
> > What is the procedure by which GL Tran batches are created coming from
an
> > specific POTran Batch?
> >
> > How are the specific lines  created on the GLTran Batch coming from
an
> > specific POTranBatch?
> >
> > And most important, how can I link the POTran Batch with the GLTran
Batch 
> > in
> > an unique way in which only one specific line from the POTranbatch
creates 
> > an
> > specific line on the GLTranBatch?
> > 
> 
>