Skip to main content
Solved

Report Rule Create PDF with Date as part of name


Forum|alt.badge.img+3
  • Do Gooder (Customer)
  • 7 replies

We are currently using APPS10 update 20

We have a report rule working to generate a PDF file on a local server with an OutputFileName naming convention:

INV_6302_IFS[@CUSTOMER_ORDER_IVC_REP/CUSTOMER_NO]_[@CUSTOMER_ORDER_IVC_REP/INVOICE_NO].pdf

This works perfectly

I now want to add the invoice date as a string ‘YYYYDDMM’

When I try to add the field I get no error but also no output.

INV_6302_IFS[@CUSTOMER_ORDER_IVC_REP/CUSTOMER_NO]_[@CUSTOMER_ORDER_IVC_REP/INVOICE_NO]_[@CUSTOMER_ORDER_IVC_REP/INVOICE_DATE].pdf

 

Assuming this is because of the date format being pulled from the field but how do I change it.

 

Any help appreciated

Action properties:

 

Best answer by MatthewConk

@pknight This should work now.

[&select 'INV_6302_IFS'||'[@CUSTOMER_ORDER_IVC_REP/CUSTOMER_NO]'||'_'||'[@CUSTOMER_ORDER_IVC_REP/INVOICE_NO]'||'_'||to_char(to_date('[@CUSTOMER_ORDER_IVC_REP/INVOICE_DATE]', 'YYYY-MM-DD"T"HH24:MI:SS'), 'YYYYMMDD')||'.pdf' from dual]  

The date was coming in as a string like this: 2009-06-29T00:00:00

So, we need to convert that to a date and then we can format it as YYYYMMDD.

to_char(to_date('2009-06-29T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'), 'YYYYMMDD')

 

View original

4 replies

MatthewConk
Do Gooder (Customer)
Forum|alt.badge.img+4
  • Do Gooder (Customer)
  • 9 replies
  • January 12, 2024

@pknight You could format the file name using SQL. This would allow to you create any date format you need and convert it to a string.

[&SELECT 'INV_6302_IFS'||'[@CUSTOMER_ORDER_IVC_REP/CUSTOMER_NO]'||'_'||'[@CUSTOMER_ORDER_IVC_REP/INVOICE_NO]'||'_'||to_char(trunc('[@CUSTOMER_ORDER_IVC_REP/INVOICE_DATE]'), 'YYYYDDMM')||'.pdf' FROM dual]

 

 

 


Forum|alt.badge.img+3
  • Author
  • Do Gooder (Customer)
  • 7 replies
  • January 12, 2024

Thanks for the prompt reply.

I didn’t realise SQL was allowed.

Tried the above though with no success but using SQL without the date works fine. So still an issue with format i assume.

[&SELECT 'INV_6302_IFS'||'[@CUSTOMER_ORDER_IVC_REP/CUSTOMER_NO]'||'_'||'[@CUSTOMER_ORDER_IVC_REP/INVOICE_NO]'||’.pdf' FROM dual]

Works perfectly but no date.

 

Thanks


MatthewConk
Do Gooder (Customer)
Forum|alt.badge.img+4
  • Do Gooder (Customer)
  • 9 replies
  • Answer
  • January 12, 2024

@pknight This should work now.

[&select 'INV_6302_IFS'||'[@CUSTOMER_ORDER_IVC_REP/CUSTOMER_NO]'||'_'||'[@CUSTOMER_ORDER_IVC_REP/INVOICE_NO]'||'_'||to_char(to_date('[@CUSTOMER_ORDER_IVC_REP/INVOICE_DATE]', 'YYYY-MM-DD"T"HH24:MI:SS'), 'YYYYMMDD')||'.pdf' from dual]  

The date was coming in as a string like this: 2009-06-29T00:00:00

So, we need to convert that to a date and then we can format it as YYYYMMDD.

to_char(to_date('2009-06-29T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'), 'YYYYMMDD')

 


Forum|alt.badge.img+3
  • Author
  • Do Gooder (Customer)
  • 7 replies
  • January 12, 2024

Perfect.

Thank you for the quick responses.

Much appreciated.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings