The problem we need to solve:

• The date we want in this field is the Pay Period End Date
• If the record is an Accrual though, we want the Fiscal Period End date
• If the record is an Accrual Reversal, we want the Fiscal Period Start date

We can determine if the record being processed is an Accrual or not, by looking at the Journal Source field. The values of interest are "03" for Accrual and "06" for Accrual Reversal.

So, writing this in pseudo code we would see something like this:

DECODE(journal_source,
       '03',fiscal_end,
       '06',fiscal_start,
       pay_period_end)     -- The "Else" condition

using the substr function, we can extract the pieces that we need from GL Buffer as such:

decode(subtr(~,17,2),
       '03',substr(~,40,11),
       '06',substr(~,29,11),
       substr(~,160,11))

---

Notes#

Click to create a new notes page