MC RM AR HISTORICAL AGED TRIAL BALANCE 1

[BHAT100]

Thanks to everybody for help. it would be great help to me

At present, we are using due date to calculate AR aging. now we need aging by document date without changing set up of aging for calculating provisions

-is there any way in great plains to have aging by document date without changing set up aging by due date

-if not then I would need to create report in crystal report but I know table SOP30300 for pulling historical records for sales transactions but what are other in AR I should use for picking up payments and debit and credit notes directly entered in AR

Thank for your time and reply

have a great day

Bhat

 

[Dukester0122]

Minimum tables would be RM00101, RM00201, RM20101 and RM30101.

 

[BHAT100]

Thanks for your reply.

I have used above tables to build report.
Now what type of link I should assign between rm20101 and rm30101 to pull data from both tables in a single column

Any idea would be great help to me

Thanks

Bhat

 

[Dukester0122]

If you want to create a Customer Class Group then your highest group level would be RM00201 if not then RM00101. Link both RM20101 and RM30101 to RM00101.

 

[BHAT100]

Thanks for your reply.
sorry to bother you again.

you are right I am creating customer class group. I am still not able to figure out how would I be able pull document # and document amount from both tables(open as well as history) together.

My report is follows:

GP1: CUSTOMER CLASS(RM00201)
GRP2: CUSTOMER(RM00101)
Detail: DOCUMENT# DOCUMENT AMOUNT 0-30 30-60 60-90 OVER90


and I am using following tables:

RM OPEN: RM20101
RM APPLY OPEN:RM20201
RM HISTORY: RM30101
RM APPLY HISTORY: RM30201
RM CUST MASTER: RM00101

for document# and document amount what table should I refer to RM20101 OR RM30101 so that I could pull record of both tables or should I use some formula to pull records or create view to union all the records from both tables

Any Idea would be really appreciated

Thanks

Bhat

 

[BHAT100]

My report is follows:

GP1: CUSTOMER CLASS(RM00201)
GRP2: CUSTOMER(RM00101)
Detail: DOCUMENT# DOCUMENT AMOUNT 0-30 30-60 60-90 OVER90


and I am using following tables:

RM OPEN: RM20101
RM HISTORY: RM30101
RM APPLY OPEN:RM20201
RM APPLY HISTORY: RM30201
RM CUST MASTER: RM00101

HOW CAN I PULL DATA IN OPEN AND HISTORICAL TABLES TOGETHER IN ONE COLUMN. I AM DOCUMENT NUMBER AND DOCUMENT AMOUNT AND DATE FROM BOTH TABLES SO THAT I CAN CALCULATE AGING ON DOC DATE
Any Idea would be really appreciated

Thanks

Bhat

 

[Dukester0122]

You need to add RM00401 then use it as your main table. Create a formula for the amount using the following:

if RM00401.DCSTATUS = 2 then RM20101.ORTRXAMT else

if RM00401.DCSTATUS = 3 then RM30101.ORTRXAMT

This would be your base formula then revise it for your purpose. You can use the RMDTYPAL field to manipulate which data to show. Use SQL Analyzer to get a feel of what documents will show in RMDTYPAL. Also you include the discount and writeoff in your report if necessary.

Goddluck!

 

[BHAT100]

sorry for bothering you again

what are tables for write off and discount and exchange gain and loss

Thanks once again for your help

Bhat

 

[BHAT100]

Thanks, I found the tables names
RM20201
RM30201

Bhat

 

[BHAT100]

I have created view for tables rm20101 and rm30101.
and I am using following formula to pull the record

IF {RM20101_V2.RMDTYPAL} = 7
THEN {RM20101_V2.ORTRXAMT}*-1
ELSE IF {RM20101_V2.RMDTYPAL} = 8
THEN {RM20101_V2.ORTRXAMT}*-1
ELSE IF {RM20101_V2.RMDTYPAL} = 9
THEN {RM20101_V2.ORTRXAMT}*-1
ELSE IF {RM20101_V2.RMDTYPAL} = 6
THEN {RM20101_V2.ORTRXAMT}*-1
ELSE IF {RM20101_V2.RMDTYPAL} =1
THEN {RM20101_V2.ORTRXAMT}
ELSE IF {RM20101_V2.RMDTYPAL} = 2
THEN {RM20101_V2.ORTRXAMT}
ELSE IF {RM20101_V2.RMDTYPAL} = 3
THEN {RM20101_V2.ORTRXAMT}
ELSE IF {RM20101_V2.RMDTYPAL} = 4
THEN {RM20101_V2.ORTRXAMT}
ELSE IF {RM20101_V2.RMDTYPAL} = 5
THEN {RM20101_V2.ORTRXAMT}

This formula does not pull the discount, writeoff and gain/loss. Could you help me to pull discount,write off and gain/loss

I have created another view for rm20201 and rm30201 tables
called rm20201_v

thanks for your help

bhat

 

[BHAT100]

and I have linked these views with customer and docnumbr to apfrdcnm

thanks

bhat

 

[Dukester0122]

Just add the fields on the formula I gave you:

if RM00401.DCSTATUS = 2 then RM20101.ORTRXAMT + discount + writeoff

 

[BHAT100]

I think,I am totally confused now.Sorry for giving you lot of headache on this.

I am started report again with following tables RM00101,RM00201,RM00401,RM20101 AND RM30101
I think, I am messing up with linking the tables. the reports is keep on running for forever.

with your above formula, we would be missing exchange gain/loss on applied documents. how it will be pulled in the report.
rm30101 is a historical table and I understand that all the fully applied documents transfered to this table by runing great plains utility - reconcile and remove transactions and if this this utility is not run then even fully applied stay as open transactions in rm20101 table.

if I understand it fully then why there is need to use rm30101 table because ultimately we would be hiding or not printing the transactions that have already been fully paid and applied

sorry for giving you trouble again and again

Input on the above would be great help to me

Thanks

bhat

 

[BHAT100]

sorry again, it is custnmbr, not custclass.

formula from record selection in sub report is as below:
{TGO_SOP30200.DOCDATE} <= {?Ending Date} and
{RM00101.CUSTCLAS} = {?location}
and
{RM00101.CUSTNMBR} = {?Pm-RM00101.CUSTNMBR}

Thanks

Bhat