Skip to main content
Solved

year / period

  • February 17, 2021
  • 3 replies
  • 206 views
M
Sidekick (Customer)
Forum|alt.badge.img+10
  • Matthew
  • Sidekick (Customer)
  • 178 replies

Hi,

 

Has anyone experience of converting an accounting year / period to a calendar date. We have a number of lobby tiles that we are creating.

 

I’ve looked for an API call - but not look.

Thanks in advance a always,

 

Matt

Best answer by william.klotz

Hi @Matthew ,

 

You could do something like the following in your query to build the date.

TO_DATE(TO_CHAR(accounting_period)||'/01/'||TO_CHAR(accounting_year),'MM/DD/YYYY') AS "DATE"

SELECT accounting_year,
       accounting_period,
       TO_DATE(TO_CHAR(accounting_period)||'/01/'||TO_CHAR(accounting_year),'MM/DD/YYYY') AS "DATE",
       account_type,
       account,
       amount_balance
FROM ACCOUNTING_BALANCE_AUTH
WHERE UPPER(company)=UPPER('&COMPANY')
  AND accounting_year BETWEEN '&START_YEAR' AND '&END_YEAR'
  AND accounting_period BETWEEN '&START_PERIOD' AND '&END_PERIOD';

 

Regards,

William Klotz

This topic has been closed for replies.

3 replies

william.klotz
Superhero (Customer)
Forum|alt.badge.img+21
  • william.klotz
  • Superhero (Customer)
  • 479 replies
  • Answer
  • February 17, 2021

Hi @Matthew ,

 

You could do something like the following in your query to build the date.

TO_DATE(TO_CHAR(accounting_period)||'/01/'||TO_CHAR(accounting_year),'MM/DD/YYYY') AS "DATE"

SELECT accounting_year,
       accounting_period,
       TO_DATE(TO_CHAR(accounting_period)||'/01/'||TO_CHAR(accounting_year),'MM/DD/YYYY') AS "DATE",
       account_type,
       account,
       amount_balance
FROM ACCOUNTING_BALANCE_AUTH
WHERE UPPER(company)=UPPER('&COMPANY')
  AND accounting_year BETWEEN '&START_YEAR' AND '&END_YEAR'
  AND accounting_period BETWEEN '&START_PERIOD' AND '&END_PERIOD';

 

Regards,

William Klotz

R
Superhero (Partner)
Forum|alt.badge.img+18
  • RutJWhalen
  • Superhero (Partner)
  • 368 replies
  • February 17, 2021

if you would like the period start date or end date you could use:

 

&AO.ACCOUNTING_PERIOD_API.Get_Date_From(company, accounting_year, accounting_period)

 

or 

&AO.ACCOUNTING_PERIOD_API.Get_Date_Until(company, accounting_year, accounting_period)

 

Regards

 

John

M
Sidekick (Customer)
Forum|alt.badge.img+10
  • Matthew
  • Author
  • Sidekick (Customer)
  • 178 replies
  • February 24, 2021

Hi @Matthew ,

 

You could do something like the following in your query to build the date.

TO_DATE(TO_CHAR(accounting_period)||'/01/'||TO_CHAR(accounting_year),'MM/DD/YYYY') AS "DATE"

SELECT accounting_year,
       accounting_period,
       TO_DATE(TO_CHAR(accounting_period)||'/01/'||TO_CHAR(accounting_year),'MM/DD/YYYY') AS "DATE",
       account_type,
       account,
       amount_balance
FROM ACCOUNTING_BALANCE_AUTH
WHERE UPPER(company)=UPPER('&COMPANY')
  AND accounting_year BETWEEN '&START_YEAR' AND '&END_YEAR'
  AND accounting_period BETWEEN '&START_PERIOD' AND '&END_PERIOD';

 

Regards,

William Klotz

Thansk for this - it gave me a good starting point. I think  my brain had frozen at the time and seems about 18 months ago I did the code I was looking for

 

where 
ACCOUNTING_PERIOD_API.Get_Acc_Year_Period(company_id, to_date(account_date, 'DD/MM/YY')) = '202105'

was what I used, with the 2021 and 05 built up from parameters entered on the lobby tile.

Terms & ConditionsCookie settingsAccessibility statement