Payment Term formula 1

[ClowneUser]

Hi,
I'm looking to write a report that shows outstanding supplier orders and total amount we need to pay on any given date depending on the payment terms of the particular supplier. ie:

supplier1 - payment terms 30 days
supplier2 - payment terms 60 days
supplier3 - payment terms 'count 30 days then end of month'
supplier4 - paymemt terms end of month then 30 days

supplier1 & 2 are easy enough but its the other 2 im struggling with, in fact I don't know where to start.

Has anyone any idea how to do this?

If you need some test data let me know, I'm using crystal 11 querying oracle 10g database.

Regards,

Mick.

 

[SkipVought]

hi,

Assuming that the start date is in A1...
[tt]
supplier3 - payment terms 'count 30 days then end of month'
=DATE(YEAR(A1+30),MONTH(A1+30)+1,0)
supplier4 - paymemt terms end of month then 30 days
=DATE(YEAR(A1),MONTH(A1)+1,0)+30
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GJParker]

Try these

Supplier 3
DATE(YEAR(date+30),MONTH(date+30)+1,1) -1

Supplier 4
DATE(YEAR(date),MONTH(date)+1,1)+29

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[ClowneUser]

Hi,
Thats great thanks, can this be used for days over 30 days just by changing the numbers:

DATE(YEAR(date+45),MONTH(date+45)+1,1) -1 etc...

We have some payment terms that are 45 days then end of month, right upto 120 days then end of month?

I only ask because at the moment I only have limited data to test with, I only have 5 records with 45 then EOM and it gives errors, well not so much errors, it just opens the formula editor and highlights the 45+EOM formula.

Hope this makes sense.

Mick.

 

[GJParker]

Yes you should be able to simply change the number of days in the formula, so not sure why you're getting an error. Sounds like you have an error in one of your formulas.

If you post the formula that is erroring and any associated formula I can take a look.

Gary Parker
MIS Data Analyst
Manchester, England

 

[ClowneUser]

Thanks, below is the folmula I've used.

EOM+45
DATE(YEAR({PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE}+45),MONTH({PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE}+45)+1,1) -1

I also have EOM+50 this is the same, but obviously 50 in place of 45, this works fine, its just the 45 one... I'm going to check the data as it may be something here, as I'm sure the formula is fine.

Mick.

 

[GJParker]

EOM+45 - what does this line do ?

When you run the report which part of the formula is highlighted and what error do you get ?

Gary Parker
MIS Data Analyst
Manchester, England

 

[ClowneUser]

Hi,

Sorry EOM+45 is just the name of the formula, below is the formula that calls the EOM+45 formula

if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '30' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 30 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '60' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 60 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '14' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 14 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '10' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 10 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '20' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 20 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '45' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 45 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '52' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 52 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '7' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 7 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '75' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 75 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '90' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE} + 90 else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = 'EOM' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = 'M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+30} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '120M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+120} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '15M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+15} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '20M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+20} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '45M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+45} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '50M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+50} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '60M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+60} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '75M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+75} else
if {IDENTITY_INVOICE_INFO.PAY_TERM_ID} = '90M' and {IDENTITY_INVOICE_INFO.COMPANY} = '02' then {@EOM+90} else

{PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE}

when the report errors it opens the formula editor and highlights {@EOM+45} - there is no error message, just highlights the above.

Mick.

 

[IanWaterman]

You need to take care with Gary's formula

What happens when

MONTH({PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE}+45) returns 12, adding another 1 will cause an error.

You really need to use dateadd properly

so instead of {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE}

Should be

dateadd("d", 45, {PURCHASE_ORDER_LINE_PART.PLANNED_DELIVERY_DATE})

Ian

 

[GJParker]

Well spotted Ian but it's the adding of the months that is the issue not days, try chaning the formula(s) to this

DATE(YEAR(today+45),MONTH(dateadd('m',1,today+45)),1) -1

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

You should just replace date() with dateserial(), which will return the correct value when the date + 45 lands in month 12 and causes the date() formula to fail.

-LB