SELECT a.effective_date journal_date,
FROM APPS.FND_FLEX_VALUES a1,
WHERE a1.flex_value_set_id = b1.flex_value_set_id
AND b1.flex_value_set_name = 'CCL_ACCOUNTS'
AND a1.flex_value_id = c1.flex_value_id
AND c1.language = USERENV ('LANG')
AND a1.flex_value = a.account) account_desc,
a.management_entity || '-'||
a.legal_entity || '-'||
a.book_type || '-'||
a.account || '-'||
a.cost_center || '-'||
a.project_code || '-'||
a.ime || '-'||
a.ile || '-'||
a.reference_code || '-'||
a.future1 || '-'||
a.future2 Account ,
a.accounted_debit- a.accounted_credit balance_total
FROM GECL.CCL_JOURNALS_V a
WHERE a.ledger_id = :pi_ledger_id
AND a.management_entity = NVL (:pi_me, a.management_entity)
AND a.account BETWEEN NVL (:pi_acct_from, a.account)
AND NVL (:pi_acct_to, a.account)
and period_name in ( SELECT period_name
FROM GL.GL_PERIOD_STATUSES a, APPS.FND_APPLICATION b
WHERE a.application_id = b.application_id
AND application_short_name = 'SQLGL'
AND ledger_id = :pi_ledger_id
AND period_year = :pi_pyear
AND period_num BETWEEN NVL (:pi_pnum_from, 1)
AND NVL (:pi_pnum_to, 13)
logic for sequence
¿ This will be known as ‘CCL_JE_SEQUENCE’ number.
¿ This will be a sequence number different from seeded Accounting Sequence or Reporting Sequence number provided by Oracle. This sequence number will only be created for the STAT reporting purpose.
¿ The sequence number will be created and assigned to Journals by a separate Program ‘CCL JE SEQUENCE ASSIGNMENT’ program.
¿ This sequence number will be assigned by Legal Entity.
a. This behavior is different from Accounting Sequence or Reporting Sequence number, where the sequence is shared among legal entities belonging to same Ledger.
¿ The sequence number will start with ‘1’ for the first Journal for every year and then increase incrementally till the last journal created for that year. Then the sequence number will again be reset to ‘1’ for the first journal for next year.
a. This behavior is different from Accounting Sequence or Reporting Sequence number, where the sequence doesn’t rest every year.
¿ The sequence number will not be assigned to the journals unless the GL Period is already closed, just like seeded sequence assignment. Hence this will be done on a Period by Period basis.
¿ When the program ‘CCL JE SEQUENCE ASSIGNMENT’ runs, the below process should occur.
a. All the journals belonging to the period needs to be selected based on the below logic.
i. The ‘DEFAULT_EFFECTIVE_DATE’ on the GL_JE_HEADERS table for the Journal needs to be in the period.
ii. Order the Journals by the DEFAULT_EFFECTIVE_DATE. For journals with same DEFAULT_EFFECTIVE_DATE, Order further by JE_HEADER_ID.
iii. Bring the following information from the Journal and populate on the CCL_JE_LE_SEQUENCE table. This will be a custom table storing the sequence.
1. Ledger Short Name
2. Period year
3. Period Name (e.g. JUL)
6. Distinct Legal Entity value (This is the SEGMENT2 of the COA on the Journal line).
a. This is required so that separate sequence can be assigned based on legal Entity.
b. The table will also have a column for CCL_JE_SEQUENCE. After all the journals have been selected for the period and placed in required order, the CCL_JE_SEQUENCE column needs to be populated against them.
i. The first period of the year will have the first Journal sequence as ‘1’ for each LE.
¿ We have the requirement that the sequence shouldn’t be assigned for a Period, when at least one of the previous period still doesn’t have the sequence assigned to it. This needs to be ensured through a Control table. The table name should be ‘CCL_JE_LE_SEQUENCE_CTRL’.
a. This table should have the below values
i. Period Year
ii. Period Name
iii. Period Number (e.g. 1 for January, 2 for February and so on)
iv. Ledger Short Name
v. Legal Entity Name
vi. Status (Incomplete or Complete)
vii. Last Sequence Number
i will attact the sample output