Solved

year / period

  • 17 February 2021
  • 3 replies
  • 184 views
M
Rank
Userlevel 5
Badge +10

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

icon

Best answer by william.klotz 17 February 2021, 19:34

View original
This topic has been closed for comments

3 replies

william.klotz
Rank
Userlevel 7
Badge +21

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
Rank
Userlevel 7
Badge +18

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
Rank
Userlevel 5
Badge +10

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 settings