pardeshirahul
asked on
need a logi to increment a sequence in a report as i dont have to use a database sequence
SELECT a.effective_date journal_date,
period_name,
1 Document_Sequence_Number,
account Account_Code,
(SELECT c1.description
FROM APPS.FND_FLEX_VALUES a1,
APPS.FND_FLEX_VALUE_SETS b1,
APPS.FND_FLEX_VALUES_TL c1
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.line_description,
a.accounted_debit,
a.accounted_credit,
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)
4. DEFAULT_EFFECTIVE_DATE
5. JE_HEADER_ID
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.
ii.
¿ 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
period_name,
1 Document_Sequence_Number,
account Account_Code,
(SELECT c1.description
FROM APPS.FND_FLEX_VALUES a1,
APPS.FND_FLEX_VALUE_SETS b1,
APPS.FND_FLEX_VALUES_TL c1
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.line_description,
a.accounted_debit,
a.accounted_credit,
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)
4. DEFAULT_EFFECTIVE_DATE
5. JE_HEADER_ID
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.
ii.
¿ 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
And your question is?
1. In-class assignment, seemingly. What have you tried on your own?
2. If you don't use a new SEQUENCE counter, you'll have to extract high value from a table column, increment it, and update the column. Hugely, hugely inefficient.
3. Any end-of-year reset, whether a sequence or column, will have be coded.
2. If you don't use a new SEQUENCE counter, you'll have to extract high value from a table column, increment it, and update the column. Hugely, hugely inefficient.
3. Any end-of-year reset, whether a sequence or column, will have be coded.
ASKER
sample report output
sample-report-output-.pdf
sample-report-output-.pdf
It seems you already have the logic worked out.
To implement you will need to:
1) Create the CCL_JE_LE_SEQUENCE_CTRL? table.
2) Code a function to give you the "next" sequence and update the above table.
a) If it's new period (Year/Name/Number/LGN/LE),
b) If period exists, add 1 to last sequence number and return the number.
To implement you will need to:
1) Create the CCL_JE_LE_SEQUENCE_CTRL? table.
2) Code a function to give you the "next" sequence and update the above table.
a) If it's new period (Year/Name/Number/LGN/LE),
b) If period exists, add 1 to last sequence number and return the number.
ASKER
i have to write a procedure
and the procedure will have the parameters
Procedure Asign_sequence ( p_legal_entity,
p_year
p_period
)
get all the values from GL_JE_HEADERS, GL_JE_LINES
and assing the sequnce colum in the table CCL_JE_LE_SEQUENCE
unique across legal_entity, year , default_effective_date, je_header_id
and the procedure will have the parameters
Procedure Asign_sequence ( p_legal_entity,
p_year
p_period
)
get all the values from GL_JE_HEADERS, GL_JE_LINES
and assing the sequnce colum in the table CCL_JE_LE_SEQUENCE
unique across legal_entity, year , default_effective_date, je_header_id
ASKER
I HAVE CREATED THIS 2 TABLES NOW
CAN YOU HELP ME WITH WRITING THE PROCEDURE
SELECT * FROM CCL_JE_LE_SEQUENCE_CTRL
CREATE TABLE CCL_JE_LE_SEQUENCE_CTRL (
Period_Year VARCHAR2(1000),
Period_Name VARCHAR2(1000),
Period_Number NUMBER,
Ledger_Short_Name VARCHAR2(100),
Legal_Entity_Name VARCHAR2(1000),
Status VARCHAR2(100),
Last_Sequence_Number NUMBER,
Created_by VARCHAR2(100),
Creation_date DATE,
Last_Updated_By VARCHAR2(1000),
Last_Update_Date DATE )
SELECT * FROM CCL_JE_LE_SEQUENCE
create table CCL_JE_LE_SEQUENCE
(
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
CCL_JE_SEQUENCE NUMBER )
CAN YOU HELP ME WITH WRITING THE PROCEDURE
SELECT * FROM CCL_JE_LE_SEQUENCE_CTRL
CREATE TABLE CCL_JE_LE_SEQUENCE_CTRL (
Period_Year VARCHAR2(1000),
Period_Name VARCHAR2(1000),
Period_Number NUMBER,
Ledger_Short_Name VARCHAR2(100),
Legal_Entity_Name VARCHAR2(1000),
Status VARCHAR2(100),
Last_Sequence_Number NUMBER,
Created_by VARCHAR2(100),
Creation_date DATE,
Last_Updated_By VARCHAR2(1000),
Last_Update_Date DATE )
SELECT * FROM CCL_JE_LE_SEQUENCE
create table CCL_JE_LE_SEQUENCE
(
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
CCL_JE_SEQUENCE NUMBER )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is a (sample) beginning code, but it seems I got the columns incorrect, you need to provide some more information:
1) why years are VARCHAR2?
where period_year= '2013'
period_name= 'JUL-2013'
period_number = 1 for JAN 2 for FEB etc
2) Difference between Last_Sequence_Number and CCL_JE_SEQUENCE NUMBER
the table
create table CCL_JE_LE_SEQUENCE
(
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
CCL_JE_SEQUENCE NUMBER )
the primaty key here is
ledger_short_name, legal_entity_name, period_year, period_number, default_effective_date, je_header_id
so i have to generate sequence for this primary key
i dont hace to use the sequence
and update the table column CCL_JE_SEQUENCE with the value
store the last sequence number for the
ledger_name, legal_entity, period_name, period_number in the CCL_JE_LE_SEQUENCE_CTRL table
1) why years are VARCHAR2?
where period_year= '2013'
period_name= 'JUL-2013'
period_number = 1 for JAN 2 for FEB etc
2) Difference between Last_Sequence_Number and CCL_JE_SEQUENCE NUMBER
the table
create table CCL_JE_LE_SEQUENCE
(
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
CCL_JE_SEQUENCE NUMBER )
the primaty key here is
ledger_short_name, legal_entity_name, period_year, period_number, default_effective_date, je_header_id
so i have to generate sequence for this primary key
i dont hace to use the sequence
and update the table column CCL_JE_SEQUENCE with the value
store the last sequence number for the
ledger_name, legal_entity, period_name, period_number in the CCL_JE_LE_SEQUENCE_CTRL table
ASKER
sorry i meant
i dont have to use the database sequence
as we may loose the number if we doing a rollback
i dont have to use the database sequence
as we may loose the number if we doing a rollback
ASKER
i jsu wanted a sample code to get the sequence generation for the table
create table CCL_JE_LE_SEQUENCE
(
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
CCL_JE_SEQUENCE NUMBER )
create table CCL_JE_LE_SEQUENCE
(
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
CCL_JE_SEQUENCE NUMBER )
Does this help?
From source: http://www.techonthenet.com/oracle/sequences.php
As the schema owner (for this example, SCOTT):
CREATE SEQUENCE parde_seq
MINVALUE 1
MAXVALUE 99999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
GRANT SELECT, EXECUTE on SCOTT.parde_seq to PUBLIC;
And then, all the DML needed is to
INSERT INTO SCOTT.ccl_je_le_sequence VALUES (yada yada through legal_entity then ...
parde_seq.nextval);
As you read up on sequencing, you'll see that the .CURRVAL function will return the current integer, and the .NEXTVAL function, obviously, returns the value incremented by 1.
You can always find the current value of the sequence with the query:
SELECT SCOTT.parde_seq.CURRVAL FROM DUAL;
At the end-of-year reset, simply drop the sequence, recreate it, and you're back to value one. The sequence cannot be dynamically reset. No triggers, no extra tables, no waste.
PS Search and read up on best practices for object naming conventions. Naming a table with the suffix ...SEQUENCE is going to get awkward....
From source: http://www.techonthenet.com/oracle/sequences.php
As the schema owner (for this example, SCOTT):
CREATE SEQUENCE parde_seq
MINVALUE 1
MAXVALUE 99999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
GRANT SELECT, EXECUTE on SCOTT.parde_seq to PUBLIC;
And then, all the DML needed is to
INSERT INTO SCOTT.ccl_je_le_sequence VALUES (yada yada through legal_entity then ...
parde_seq.nextval);
As you read up on sequencing, you'll see that the .CURRVAL function will return the current integer, and the .NEXTVAL function, obviously, returns the value incremented by 1.
You can always find the current value of the sequence with the query:
SELECT SCOTT.parde_seq.CURRVAL FROM DUAL;
At the end-of-year reset, simply drop the sequence, recreate it, and you're back to value one. The sequence cannot be dynamically reset. No triggers, no extra tables, no waste.
PS Search and read up on best practices for object naming conventions. Naming a table with the suffix ...SEQUENCE is going to get awkward....
ASKER
in my table
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
CCL_JE_SEQUENCE NUMBER )
now there are multiple records
i have to update with one sequence number
and the next set oof records
with another sequence number
these are the columns which is the primary key
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
i can have 5 records where i have to update with 1 sequence
and another 5 records i have to update with another sequence
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
CCL_JE_SEQUENCE NUMBER )
now there are multiple records
i have to update with one sequence number
and the next set oof records
with another sequence number
these are the columns which is the primary key
Ledger_Short_Name varchar2(1000),
Period_year varchar2(100),
period_name VARCHAR2(100),
period_number NUMBER,
DEFAULT_EFFECTIVE_DATE DATE,
JE_HEADER_ID NUMBER,
legal_Entity VARCHAR2(1000),
i can have 5 records where i have to update with 1 sequence
and another 5 records i have to update with another sequence
Tricky, but consider that the sequence CURRVAL function returns the current value.
Your logic could increment the counter (NEXTVAL) when the group changes. Use the CURRVAL for the remaining rows in that group. When the group key changes, repeat the logic, alternating between the NEXTVAL and CURRVAL.
Your logic could increment the counter (NEXTVAL) when the group changes. Use the CURRVAL for the remaining rows in that group. When the group key changes, repeat the logic, alternating between the NEXTVAL and CURRVAL.
ASKER
is there something i can do without a sequence
Sure. Create a table to track and increment a numeric column. Query the table for the current value, and return it to your process. I mentioned that back in https://www.experts-exchange.com/questions/28234669/need-a-logi-to-increment-a-sequence-in-a-report-as-i-dont-have-to-use-a-database-sequence.html?anchorAnswerId=39477422#a39477422.
ASKER