select coa_id, incl_excl_ind,
substr(concat_segments_low,1,4) seg_1_low,
substr(concat_segments_low,6,5) seg_2_low,
substr(concat_segments_low,12,4) seg_3_low,
substr(concat_segments_low,17,5) seg_4_low,
substr(concat_segments_low,23,3) seg_5_low,
substr(concat_segments_low,27,6) seg_6_low,
substr(concat_segments_low,34,5) seg_7_low,
substr(concat_segments_low,40,4) seg_8_low,
substr(concat_segments_low,45,4) seg_9_low,
substr(concat_segments_low,50,4) seg_10_low,
substr(concat_segments_high,1,4) seg_1_high,
substr(concat_segments_high,6,5) seg_2_high,
substr(concat_segments_high,12,4) seg_3_high,
substr(concat_segments_high,17,5) seg_4_high,
substr(concat_segments_high,23,3) seg_5_high,
substr(concat_segments_high,27,6) seg_6_high,
substr(concat_segments_high,34,5) seg_7_high,
substr(concat_segments_high,40,4) seg_8_high,
substr(concat_segments_high,45,4) seg_9_high,
substr(concat_segments_high,50,4) seg_10_high
from
(SELECT 'SO121' coa_id,
'I' incl_excl_ind,
'0001.00001.0001.12345.123.123456.00001.0000.0000.0000'
concat_segments_low,
'1000.10000.1000.54321.321.654321.10000.0000.0000.0000'
concat_segments_high
FROM DUAL);
Is this what you want?
Mike
On Wed, Apr 28, 2010 at 11:59 AM, Richard Pascual <richg...@gmail.com>wrote:
> 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 <sro...@gmail.com> 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 Oracle-PLSQL@googlegroups.com
>> To unsubscribe from this group, send email to
>> oracle-plsql-unsubscr...@googlegroups.com
>> For more options, visit this group at
>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
>
>
>
> --
> rgpasc...@berkeley.edu
> 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 Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
