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
pardeshirahulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pardeshirahulAuthor Commented:
can you tell me how to go about this logic if you need any information please let me know
0
MikeOM_DBACommented:
And your question is?
0
DavidSenior Oracle Database AdministratorCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pardeshirahulAuthor Commented:
sample report output
sample-report-output-.pdf
0
MikeOM_DBACommented:
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), insert row for that period and set sequence = 1, previous period = Complete.
    b) If period exists, add 1 to last sequence number and return the number.
0
pardeshirahulAuthor Commented:
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
0
pardeshirahulAuthor Commented:
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 )
0
MikeOM_DBACommented:
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?
2) Difference between Last_Sequence_Number and CCL_JE_SEQUENCE NUMBER
3) What is the primary key for these tables?
4) Why a period name if you have the number?

SQL> CREATE TABLE ccl_je_le_sequence_ctrl
  2  (
  3    period_year            NUMBER
  4  , period_name            VARCHAR2 (1000)
  5  , period_number          NUMBER
  6  , ledger_short_name      VARCHAR2 (100)
  7  , legal_entity_name      VARCHAR2 (1000)
  8  , status                 VARCHAR2 (100)
  9  , last_sequence_number   NUMBER
 10  , created_by             VARCHAR2 (100)
 11  , creation_date          DATE
 12  , last_updated_by        VARCHAR2 (1000)
 13  , last_update_date       DATE
 14  )
 15  /

Table created.

SQL> CREATE UNIQUE INDEX ccl_je_le_sequence_ctrl_pk
  2    ON ccl_je_le_sequence_ctrl (
  3                                period_year
  4                              , period_number
  5                              , ledger_short_name
  6                              , legal_entity_name
  7                               )
  8  /

Index created.

SQL> ALTER TABLE ccl_je_le_sequence_ctrl
  2        ADD CONSTRAINT ccl_je_le_sequence_ctrl_pk
  3        PRIMARY KEY ( period_year
  4                    , period_number
  5                    , ledger_short_name
  6                    , legal_entity_name )
  7        USING INDEX ccl_je_le_sequence_ctrl_pk
  8  /

Table altered.

SQL> CREATE OR REPLACE FUNCTION ccl_je_le_sequence
  2  (
  3    p_period_year          NUMBER
  4  , p_period_number        NUMBER
  5  , p_ledger_short_name    VARCHAR2
  6  , p_legal_entity_name    VARCHAR2
  7  )
  8    RETURN NUMBER
  9  IS
 10    v_cnt   NUMBER;
 11    v_seq   NUMBER;
 12  BEGIN
 13    SELECT COUNT ( *)
 14      INTO v_cnt
 15      FROM ccl_je_le_sequence_ctrl q
 16     WHERE q.period_year = p_period_year
 17       AND q.period_number = p_period_number
 18       AND q.ledger_short_name = p_ledger_short_name
 19       AND q.legal_entity_name = p_legal_entity_name;
 20
 21    IF v_cnt = 0
 22    THEN
 23      INSERT INTO ccl_je_le_sequence_ctrl
 24           VALUES (
 25                   p_period_year
 26                 , '???'                    --period_name (select from a table?)
 27                 , p_period_number
 28                 , p_ledger_short_name
 29                 , p_legal_entity_name
 30                 , 'A'                                                 -- status
 31                 , 1                                     -- last_sequence_number
 32                 , USER                                            -- created_by
 33                 , SYSDATE                                      -- creation_date
 34                 , NULL                                       -- last_updated_by
 35                 , NULL                                      -- last_update_date
 36                  );
 37
 38      v_seq   := 1;
 39    ELSE
 40      SELECT last_sequence_number + 1
 41        INTO v_seq
 42        FROM ccl_je_le_sequence_ctrl q
 43       WHERE q.period_year = p_period_year
 44         AND q.period_number = p_period_number
 45         AND q.ledger_short_name = p_ledger_short_name
 46         AND q.legal_entity_name = p_legal_entity_name
 47      FOR UPDATE;
 48
 49      UPDATE ccl_je_le_sequence_ctrl q
 50         SET last_sequence_number = v_seq
 51           , last_updated_by = USER
 52           , last_update_date = SYSDATE
 53       WHERE q.period_year = p_period_year
 54         AND q.period_number = p_period_number
 55         AND q.ledger_short_name = p_ledger_short_name
 56         AND q.legal_entity_name = p_legal_entity_name;
 57    END IF;
 58
 59    COMMIT;
 60    RETURN v_seq;
 61  END;
 62  /

Function created.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pardeshirahulAuthor Commented:
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
0
pardeshirahulAuthor Commented:
sorry i meant
i dont have to use the database  sequence
as we may loose the number if we doing a rollback
0
pardeshirahulAuthor Commented:
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 )
0
DavidSenior Oracle Database AdministratorCommented:
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 999999999999999999999999999
  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....
0
pardeshirahulAuthor Commented:
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
0
DavidSenior Oracle Database AdministratorCommented:
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.
0
pardeshirahulAuthor Commented:
is there something i can do without a sequence
0
DavidSenior Oracle Database AdministratorCommented:
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 http://www.experts-exchange.com/Database/Oracle/Q_28234669.html#a39477422.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.