Serendipity strikes. I am working on a similar problem at this very moment. We are writing a validation stored procedure that takes COA (Chart of Accounts) strings and then validates them against the General Ledger to make sure that the COA string is a valid one before passing a transaction to the GL.
One thing that really helped out is that I wrote two functions. One that parses COA strings and another one that puts them together again using the components of each chart string. For example: function parseCOAstring ( COAString, SegID ) Where SegID is the part of the COA string you want to get back... SEG1, SEG2, SEG3, etc. function buildCOAString ( SEG1, SEG2, SEG3,..., SEG10 ) You can make an additional parameter in each to use a custom delimiter or just hardcode a ".". For us, our delimiter would always be a hyphen "-" so I just hardcoded it in. Just a start... - Rich On Wed, Apr 28, 2010 at 8:07 AM, Roope <[email protected]> wrote: > Table "TRANSACTION": > COA_ID > SEG1 > SEG2 > SEG3 > SEG4 > SEG5 > SEG6 > SEG7 > SEG8 > SEG9 > SEG10 > IND > > EXAMPLE DATA TRANSACTION: > COA_ID SEG1 SEG2 SEG3 > SEG4 SEG5 SEG6 SEG7 > SEG8 SEG9 SEG10 IND > ==== ====== ===== ====== ===== ====== > ===== ====== ====== ===== ====== ====== > SO121 0003 01121 0001 00123 001 > 000112 00012 0000 0000 0000 > > > > Table "VALIDATE_X": > > > COA_ID > INCL_EXCL_IND > CONCAT_SEGMENTS_LOW > CONCAT_SEGMENTS_HIGH > > EXAMPLE DATA VALIDATE_X: > > COA_ID INCL_EXCL_IND > CONCAT_SEGMENTS_LOW > CONCAT_SEGMENTS_HIGH > ======= ============= > =============================== > =============================== > SO121 I > 0001.00001.0001.12345.123.123456.00001.0000.0000.0000 > 1000.10000.1000.54321.321.654321.10000.0000.0000.0000 > SO121 E > 0012.00012.0001.00012.001.000123.00012.0000.0000.0000 > 0013.00012.0000.00001.001.000012,00001.0000.0000.0000 > SO121 E > 0015.00015.0001.00012.001.000123.00012.0000.0000.0000 > 0016.00011.0000.00001.001.000012,00001.0000.0000.0000 > COA_IDs from "transaction" table have all the segment values in different > columns (seg1, seg2, seg3....) and we have "validate_x" table which > specifies if the segment combination is allowed or not. > we need to take the COA_ID from transaction table and its segment > combination and check if the combination lies between the allowed > combination in "validate_x" table. Problem i am facing is, segment values > in "validate_x" table are concantinated and each segment values are > seperated by "*.*". > > We need to select only those COA_IDs from transaction table whose segment > combination is allowed ( COA_ID is in INCL_EXCL_IND = "I" range and not in > the INCL_EXCL_IND = "E" range) > PS: for 1 COA_ID there can be more than 1 range of INCL_EXCL_IND = "I" and > more than 1 range of INCL_EXCL_IND = "E" > > -- > Thanx'N'Regards > Roope > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en -- [email protected] Rich Pascual Database Programmer, IT Systems Management Advancement Operations U.C. Berkeley University Relations Phone: (510) 643-7652 -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
