Function dynamically separate values from the given string like(10,20,30,40,50,60,70,80,90,100)

Function  dynamically separate values from the given string like(10,20,30,40,50,60,70,80,90,100) and store the values in Table called mytempvals

Made by Tahir Khalid

create or replace function separate5(a varchar2)return number

is

begin

for i in 1..9 loop

insert into mytempvals

select substr(a,instr(a,',',1,i)-2,2) from dual;

end loop;

commit;

return 1;

end;

Example of more Dynamic function using varray of Separation

More dynamic function of separation

create or replace function separate4(a varchar2)return x

is

z x;

begin

z:=x(0,0,0,0,0,0,0,0,0,0);

for i in 1..9 loop

select  instr(a,',',1,i) into z(i) from dual;

end loop;

return z;

end;

Calling Environment for  more dynamic function

declare

  -- Local variables here

  a varchar2(30) :='10,20,30,40,50,60,70,80,90,100';

  b varchar2(10);

  c varchar2(10);

  r x;

  s y;

 begin

  s:=y(0,0,0,0,0,0,0,0,0,0);

  r:=separate4(a);

  for i in 1..9 loop

  select substr(a,r(i)-2,2) into s(i) from dual;

  dbms_output.put_line(s(i));

  end loop;

end;

Example of Comma separation Procedure

--Self created variable array data type of length 2.

CREATE Or Replace TYPE x AS VARRAY(2) OF number;

Procedure made by Tahir Khalid to get seprated values

create or replace procedure sep_vals(a varchar2)

is

z x;

c varchar2(2);

d varchar2(2);

e varchar2(2);

begin

z:=x(0,0,0,0,0,0,0,0,0,0);

for i in 1..9 loop

select  instr(a,',',1,i) into z(i) from dual;

end loop;

dbms_output.put_line('The value of z(1)='||z(1));

select substr(a,z(1)-2,2) into c from dual;

dbms_output.put_line('The value of z(2)='||z(2));

select substr(a,z(2)-2,2) into d from dual;

dbms_output.put_line('The value of z(3)='||z(3));

select substr(a,z(3)-2,2) into e from dual;

dbms_output.put_line(c);

dbms_output.put_line(d);

dbms_output.put_line(e);

end;

calling environment in test window for above procedure

declare

  -- Local variables here

  a varchar2(30) :='10,20,30,40,50,60,70,80,90,100';

 begin

 sep_vals(a);

 end;

User Creation and Responsibilities in E-Business Suite

System Administrator Responsibility

Security: Responsibility

Define (Responsibility Name, Application, Responsibility Key, Description)

Effective Dates

Available Form

Oracle Applications

Data Group

Name: Standard

Application: Human Resource

Request Group

User Creation

Security: User

Define (User Name, Password, Description)

Direct Responsibility Tab

Responsibility: System Administrator

Application Human Resource

Security Group: Standard

Monitor

Defining General Ledger In Oracle E-Business Suite

Defining General Ledger in E-Business Suite
In General Responsibility:- Go to  Setup->Financials->Flex Fields->Key->Segments
Now Search of the General Ledger Accounting Flex Field.
Add a Structure (Company Name and ect). By pressing add button.
Create Your Company Segments (Company, Accounts, Departments, and so on).
For Example
Company Segment (Segment 1).
Then Create a value set  for the Company Segments (  value set Name, Description, List of values, Security type(non _hierarchical Security),Format validation(Char, Date, Number),Max size, Max value, Min value, Value Validation(Dependent, Independent, None, Pair, Special)  )

Then set the Flex Field Qualifier for your Segment (Category Labels for your Accounts).

Then Press Compile button.

Afterwords define the Values the Structure Just created above.

Go to SetupàFinancialsàFlex FieldsàKeyàValues

Find Key Flex Field Segments

IN the window

Select  Application (General Ledger) , Tile(Accounting FlexField) , Structure(Company’s Structure), Segment

In Segment values window give the value for the Segment you define for example company

Values (Company) segment

Value    Translatedvalue  Description

00                           00           Head Office

01                           01           Uk Operations

02                           02             EU Operations

03                           03             US Operations

T                              T                 Total All Companies                                                    

In Value, Hierarchy, Qualifiers (Tab)

Make Total All Companies as Parent (check Box on)

--GL Voucher

SELECT          distinct

gh.name,

gh.default_effective_date,

gh.period_name,

tl.user_je_category_name cat_name,

gh.creation_date,

substr(tl.user_je_category_name,1,3)||' - '||gh.doc_sequence_value jv_no,

gh.created_by user_id,

gh.je_source,    

gh.name journal_nam,

gl.je_line_num,

round(gl.entered_dr,0) entered_dr,

round(gl.entered_cr,0) entered_cr,

nvl(gl.accounted_dr,0) acdr,

nvl(gl.accounted_cr,0)  accr,

'GL' MODULE,

'Jun-20'||substr(gh.period_name,-2,2) acct_year,

' '||gl.description,

cc.segment3 Cost_center,

cc.segment4 Departement,

cc.segment5 Natural_acc,

cc.segment6 Product,

--     W_DEV_PKG.GET_COMPANY_DESC(CC.CODE_COMBINATION_ID)||'-'||W_DEV_PKG.GET_BUSINESS_DESC(CC.CODE_COMBINATION_ID)||'-'

                   ' '||W_DEV_PKG.GET_COST_CENTER_DESC(CC.CODE_COMBINATION_ID)||'-'||

                      W_DEV_PKG.GET_NATURAL_DESC(CC.CODE_COMBINATION_ID)||'-'||W_DEV_PKG.GET_Product_DESC(CC.CODE_COMBINATION_ID)fLEXdESCRIPTION

      

from

                       gl_je_batches        gb,                                                                                                              

                       gl_je_headers        gh,

                       gl_je_lines              gl,

                       gl_code_combinations cc,

                       gl_je_categories_tl       tl ,

                      fnd_user u

   

where  

                   gb.je_batch_id=gh.je_batch_id

and                gh.je_header_id=gl.je_header_id

and                gl.code_combination_id=cc.code_combination_id

and                tl.je_category_name=gh.je_category

and                u.user_id=gh.created_by

and                gh.ledger_id=1003

--Parameters--

and  ( nvl(gh.doc_sequence_value,-9999) between nvl(&from_voucher,-9999) and nvl(&To_voucher,-9999)

     or gh.doc_sequence_value between nvl(&from_voucher,gh.doc_sequence_value) and nvl(&To_voucher,gh.doc_sequence_value)

     )

and           tl.user_je_category_name =NVL(&category, tl.user_je_category_name )

and                gh.default_effective_date between NVL(&from_date,gh.default_effective_date) and NVL(&To_date,gh.default_effective_date)

and                u.user_name=nvl(&created_by,u.user_name)

AND     gb.status = (case when &bstatus = 'P' then 'P' when &bstatus = 'U' then 'U' else gb.status end)

ORDER BY gl.je_line_num

--GL Edit List

SELECT          distinct

gh.name,

gh.default_effective_date,

gh.period_name,

tl.user_je_category_name cat_name,

gl.JE_LINE_NUM,

substr(tl.user_je_category_name,1,3)||' - '||gh.doc_sequence_value jv_no,

                  cc.segment3||'.'||cc.segment4||'.'||cc.segment5  AC_NO,

                  W_DEV_PKG.GET_COST_CENTER_DESC(CC.CODE_COMBINATION_ID)||'-'||

                  W_DEV_PKG.GET_NATURAL_DESC(CC.CODE_COMBINATION_ID)||'-'||

                  W_DEV_PKG.GET_Product_DESC(CC.CODE_COMBINATION_ID)  AC_Head,

                  gh.created_by,

                  gh.je_source,

                  gb.status,    

                  gh.name journal_nam,

                  round(gl.entered_dr,0) entered_dr,

                  round(gl.entered_cr,0) entered_cr,

                   nvl(gl.accounted_dr,0) acdr,

                   nvl(gl.accounted_cr,0)  accr, 

                  'GL' MODULE,

                  'Jun-20'||substr(gh.period_name,-2,2) acct_year,

                   gl.description

from

                       gl_je_batches        gb,                                                                                                              

                       gl_je_headers        gh,

                       gl_je_lines              gl,

                       gl_code_combinations cc,

                       gl_je_categories_tl       tl ,

                       fnd_user                 u

   

where  

                   gb.je_batch_id=gh.je_batch_id

and                gh.je_header_id=gl.je_header_id

and                gl.code_combination_id=cc.code_combination_id

and                u.user_id=gh.created_by

and                tl.je_category_name=gh.je_category

and                gh.ledger_id=1003

and                u.user_name=nvl(&uname,u.user_name)

--Parameters--

and gh.default_effective_date between &from_date and &to_date

AND     tl.user_je_category_name =nvl(&Cat_nam,tl.user_je_category_name)

AND     gh.je_source=nvl(&source,gh.je_source)

AND     gb.status = (case when &bstatus = 'P' then 'P' when &bstatus = 'U' then 'U' else gb.status end)

--GL_SEGWISE

SELECT

            DISTINCT

            gcc.&segments code,

            (case when &segments=segment1 then w_dev_pkg.GET_COMPANY_DESC(gcc.code_combination_id)

                  when &segments=segment2 then w_dev_pkg.GET_BUSINESS_DESC(gcc.code_combination_id)

                  when &segments=segment3 then w_dev_pkg.GET_COST_CENTER_DESC(gcc.code_combination_id)

                  when &segments=segment4 then W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)

                  when &segments=segment5 then W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)

                  when &segments=segment6 then W_DEV_PKG.GET_CUSTOMER_DESC(gCC.CODE_COMBINATION_ID)

                  when &segments=segment7 then w_dev_pkg.GET_Spare1_DESC(gcc.code_combination_id)

                  when &segments=segment8 then w_dev_pkg.GET_Spare2_DESC(gcc.code_combination_id)

              end ) fLEXdESCRIPTION,

 

       sum(jel.entered_dr) enter_dr,

       sum(jel.entered_cr) enter_cr,

       sum(nvl(jel.accounted_dr,0)) acdr,

       sum(nvl(jel.accounted_cr,0))  accr

FROM

                         gl_je_batches jeb,

                         gl_je_headers jeh,

                         gl_je_lines   jel,

                         gl_code_combinations gcc

                        

WHERE

                              jeb.je_batch_id=jeh.je_batch_id

      AND                     jeh.je_header_id=jel.je_header_id

      AND                     jel.code_combination_id=gcc.code_combination_id

      AND                     jeb.status = (case when &bstatus = 'P' then 'P' when &bstatus = 'U' then 'U' else jeb.status end)

      AND                     jeh.default_effective_date between NVL(&from_date,jeh.default_effective_date) and NVL(&To_date, jeh.default_effective_date)    

--    AND                     jeh.SET_OF_BOOKS_ID=1003

      AND                     jeh.ledger_ID=1003

      AND                     jeh.actual_flag='A'

      AND                      gcc.&segments between &code_from and &code_to

group by

                              gcc.&segments,   

                                       (case  when &segments=segment1 then w_dev_pkg.GET_COMPANY_DESC(gcc.code_combination_id)

                                        when &segments=segment2 then w_dev_pkg.GET_BUSINESS_DESC(gcc.code_combination_id)

                                    when &segments=segment3 then w_dev_pkg.GET_COST_CENTER_DESC(gcc.code_combination_id)

                                    when &segments=segment4 then W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)

                                    when &segments=segment5 then W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)

                                    when &segments=segment6 then W_DEV_PKG.GET_CUSTOMER_DESC(gCC.CODE_COMBINATION_ID)

                                    when &segments=segment7 then w_dev_pkg.GET_Spare1_DESC(gcc.code_combination_id)

                                    when &segments=segment8 then w_dev_pkg.GET_Spare2_DESC(gcc.code_combination_id)

                               end )

ORDER BY CODE

--GL Combination Wise

--GL Ledger (Combination Wise) Query Analysis.

SELECT

DISTINCT

 gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8||'.'||gcc.segment9 cod,

      W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)||'-'||W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)||'-'||W_DEV_PKG.GET_CUSTOMER_DESC                        (gCC.CODE_COMBINATION_ID)||'-'||

      W_DEV_PKG.GET_Spare1_DESC(gCC.CODE_COMBINATION_ID)||'-'||W_DEV_PKG.GET_Spare2_DESC(gCC.CODE_COMBINATION_ID) fLEXdESCRIPTION,

       jeh.doc_sequence_value ||'-'|| jel.subledger_doc_sequence_value new,

       jel.subledger_doc_sequence_value,

       jeh.default_effective_date,

       jel.description,

       jeh.je_source,

       Jeb.status,

       gcc.code_combination_id,jeh.period_name,

       round(jel.entered_dr,0) entered_dr,

       round(jel.entered_cr,0) entered_cr,

       nvl(jel.accounted_dr,0) acdr,

       nvl(jel.accounted_cr,0) accr,

       jeh.doc_sequence_value vno,

       jel.reference_1 supplier,

       jel.reference_2,

       jel.reference_3,

       jel.reference_4 cheq_num,

       jel.reference_5,

       jel.effective_date line_effective_date,

       jel.creation_date,

       jel.je_line_num,

       'GL' MODULE,

      (CASE WHEN Jeb.status='P' THEN 'POSTED' ELSE 'UNPOSTED' END) STATUS,

     

      jeh.description header_des                                                                                                        /*   This column sub query reduce the processing time...... In this query i concatenate the invoice_type to voucher number.....*/

FROM

                         gl_je_batches jeb,

                         gl_je_headers jeh,

                         gl_je_lines   jel,

                         gl_code_combinations gcc

                         

                        

WHERE

            jeb.je_batch_id=jeh.je_batch_id

      AND   jeh.je_header_id=jel.je_header_id

      AND   jel.code_combination_id=gcc.code_combination_id

      --AND   jeb.status = (case when &bstatus = 'P' then 'P' when &bstatus = 'U' then 'U' else jeb.status end)

      AND   jeh.default_effective_date between NVL(&from_date,jeh.default_effective_date) and NVL(&To_date, jeh.default_effective_date)    

       AND    jeh.ledger_id=&SOB 

       AND    jeh.actual_flag='A'

AND         gcc.segment1 between substr(&cc1,1,1) and substr(&cc2,1,1)

AND         gcc.segment2 between substr(&cc1,3,2) and substr(&cc2,3,2)

AND         gcc.segment3 between substr(&cc1,6,8) and substr(&cc2,6,8)

AND         gcc.segment4 between substr(&cc1,15,3) and substr(&cc2,15,3)

AND         gcc.segment5 between substr(&cc1,19,8) and substr(&cc2,19,8)

AND         gcc.segment6 between substr(&cc1,28,4) and substr(&cc2,28,4)

AND         gcc.segment7 between substr(&cc1,33,2) and substr(&cc2,33,2)

AND         gcc.segment8 between substr(&cc1,36,3) and substr(&cc2,36,3)

AND         gcc.segment9 between substr(&cc1,40,3) and substr(&cc2,40,3)

ORDER BY jel.effective_date,jeh.doc_sequence_value,jel.subledger_doc_sequence_value

--GL ActivitySummary with Break

SELECT

            DISTINCT

            gcc.&segments code,

            gcc.&detail_segments nat_account,

            (case when &detail_segments=segment1 then w_dev_pkg.GET_COMPANY_DESC(gcc.code_combination_id)

                  when &detail_segments=segment2 then w_dev_pkg.GET_BUSINESS_DESC(gcc.code_combination_id)

                  when &detail_segments=segment3 then w_dev_pkg.GET_COST_CENTER_DESC(gcc.code_combination_id)

                  when &detail_segments=segment4 then W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)

                  when &detail_segments=segment5 then W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)

                  when &detail_segments=segment6 then W_DEV_PKG.GET_CUSTOMER_DESC(gCC.CODE_COMBINATION_ID)

                  when &detail_segments=segment7 then w_dev_pkg.GET_Spare1_DESC(gcc.code_combination_id)

                  when &detail_segments=segment8 then w_dev_pkg.GET_Spare2_DESC(gcc.code_combination_id)

              end ) nat_acct_des,

            (case when &segments=segment1 then w_dev_pkg.GET_COMPANY_DESC(gcc.code_combination_id)

                  when &segments=segment2 then w_dev_pkg.GET_BUSINESS_DESC(gcc.code_combination_id)

                  when &segments=segment3 then w_dev_pkg.GET_COST_CENTER_DESC(gcc.code_combination_id)

                  when &segments=segment4 then W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)

                  when &segments=segment5 then W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)

                  when &segments=segment6 then W_DEV_PKG.GET_CUSTOMER_DESC(gCC.CODE_COMBINATION_ID)

                  when &segments=segment7 then w_dev_pkg.GET_Spare1_DESC(gcc.code_combination_id)

                  when &segments=segment8 then w_dev_pkg.GET_Spare2_DESC(gcc.code_combination_id)

              end ) fLEXdESCRIPTION,

   

      

      

      

      

     

       sum(jel.entered_dr) enter_dr,

       sum(jel.entered_cr) enter_cr,

       sum(nvl(jel.accounted_dr,0)) acdr,

       sum(nvl(jel.accounted_cr,0))  accr

     

FROM

                         gl_je_batches jeb,

                         gl_je_headers jeh,

                         gl_je_lines   jel,

                         gl_code_combinations gcc

                        

WHERE

                                  jeb.je_batch_id=jeh.je_batch_id

      AND                     jeh.je_header_id=jel.je_header_id

      AND                     jel.code_combination_id=gcc.code_combination_id

      AND                     jeb.status = (case when :bstatus = 'P' then 'P' when :bstatus = 'U' then 'U' else jeb.status end)

      AND                     jeh.default_effective_date between NVL(:from_date,jeh.default_effective_date) and NVL(:To_date, jeh.default_effective_date)    

AND                     jeh.LEDGER_ID=1003  

AND                     jeh.actual_flag='A'

AND                      gcc.&segments between :code_from and :code_to

group by

                              gcc.&segments,   

                             (case  when &segments=segment1 then w_dev_pkg.GET_COMPANY_DESC(gcc.code_combination_id)

                                    when &segments=segment2 then w_dev_pkg.GET_BUSINESS_DESC(gcc.code_combination_id)

                                    when &segments=segment3 then w_dev_pkg.GET_COST_CENTER_DESC(gcc.code_combination_id)

                                    when &segments=segment4 then W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)

                                    when &segments=segment5 then W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)

                                    when &segments=segment6 then W_DEV_PKG.GET_CUSTOMER_DESC(gCC.CODE_COMBINATION_ID)

                                    when &segments=segment7 then w_dev_pkg.GET_Spare1_DESC(gcc.code_combination_id)

                                    when &segments=segment8 then w_dev_pkg.GET_Spare2_DESC(gcc.code_combination_id)

                               end ),

                                 gcc.&detail_segments,

                            (case when &detail_segments=segment1 then w_dev_pkg.GET_COMPANY_DESC(gcc.code_combination_id)

                                     when &detail_segments=segment2 then w_dev_pkg.GET_BUSINESS_DESC(gcc.code_combination_id)

                                     when &detail_segments=segment3 then w_dev_pkg.GET_COST_CENTER_DESC(gcc.code_combination_id)

                                     when &detail_segments=segment4 then W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)

                                     when &detail_segments=segment5 then W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)

                                     when &detail_segments=segment6 then W_DEV_PKG.GET_CUSTOMER_DESC(gCC.CODE_COMBINATION_ID)

                                     when &detail_segments=segment7 then w_dev_pkg.GET_Spare1_DESC(gcc.code_combination_id)

                                     when &detail_segments=segment8 then w_dev_pkg.GET_Spare2_DESC(gcc.code_combination_id)

                            end )

  

   ORDER BY  nat_account