Home » SQL & PL/SQL » SQL & PL/SQL » Getting some wierd compile errors that i can't solve......Please Help
Getting some wierd compile errors that i can't solve......Please Help [message #244973] Thu, 14 June 2007 14:35 Go to next message
bigpapi
Messages: 6
Registered: June 2007
Junior Member
Hoping someone can help me out. I'm very new to oracle stored procedures. I have been creating one throughout the day but not it is time to compile and i am getting a couple errors, that i can't seem to solve and i was hoping someone could help me out.

Here are my three errors:
1 PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
57 PL/SQL: ORA-06544: PL/SQL: internal error, arguments[ph2csql_strdef_to_diana:bind],[],[],[],[],[],[],[]
17 PL/SQL: SQL Statement Ignored


Here is my code:

create or replace procedure UP_Unbilled_Revenue_Query(cur_unbilled_rev IN OUT ck_crystal_reports_cursors.Unbilled_Revenue_Query,
dt_pivot_date in varchar2,
n_account_number in number,
vc_service_type in varchar2 default 'ALL',
vc_rate_category in varchar2 default 'ALL') AS

begin
open cur_unbilled_rev for

/********Line 17 **********/
Select /*+ RULE*/
service,
rate_group,
rate_category,
rate_period,
serv_trans_type,
unit_rate,
from_units,
to_units,
bill_charge_type,
groupin,
sum(revenue) revenue,
sum(consumption) consumption
from (
select /*+ RULE */ distinct
a.account_number,
a.trans_posted_date,
s.service_acct_trans_id,
sbpo.service_type SERVICE,
rpt.rate_period_type RATE_PERIOD,
rg.rate_group RATE_GROUP,
rcat.rate_category RATE_CATEGORY,
rclass.rate_class_desc RATE_CLASS_DESC,
nvl(rb.from_units,0) FROM_UNITS,
nvl(rb.to_units,0) TO_UNITS,
s.billing_charge_type BILL_CHARGE_TYPE,
s.transaction_type SERV_TRANS_TYPE,
a.transaction_source transaction_source,
round(decode((least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date)),0,1,(least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date))) / decode(trunc(sbpe.end_date) - trunc(sbpe.effective_date),
0, 1, trunc(sbpe.end_date) - trunc(sbpe.effective_date)) * decode(instr(s.transaction_type,'OFF'),0, decode(s.billing_charge_type, 'COMPET',
decode(s.transaction_type,'ECONSUME_NT',decode(rp.rate_period_type,'FCEC',0,rba.allocation_amount)
,rba.allocation_amount), rba.allocation_amount),0),2) revenue,
round(decode((least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date)),0,1,(least( TO_DATE(&dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date))) / decode(trunc(sbpe.end_date) - trunc(sbpe.effective_date),
0, 1, trunc(sbpe.end_date) - trunc(sbpe.effective_date)) * decode(instr(s.transaction_type,'OFF'),0, decode(s.billing_charge_type, 'COMPET',
decode(s.transaction_type,'ECONSUME_NT',decode(rp.rate_period_type,'FCEC',0,rba.allocation_unit)
,rba.allocation_unit), rba.allocation_unit),0),2) consumption,
rb.unit_rate,
sbpe.effective_date,
sbpe.end_date,
'A' GroupIn
/****************Line 57 *****/
from service_acct_transaction s,
account_transaction a,
service_bill_portion sbpo,
rate_category rcat,
rate_class rclass,
rate_period rp,
rate_period_type rpt,
rate_group rg,
rate_block rb,
rate_block_allocation rba,
service_bill_period sbpe,
service se,
ck_unbilled_rev_trans_type curtt
where a.account_transaction_id = s.account_transaction_id
and s.service_bill_portion_id = sbpo.service_bill_portion_id
and sbpo.rate_category = rcat.rate_category
and rcat.rate_group = rg.rate_group
and sbpo.rate_class_id = rclass.rate_class_id
and sbpo.service_bill_portion_id = rba.service_bill_portion_id
and rba.rate_block_id = rb.rate_block_id
and rb.rate_period_id = rp.rate_period_id
and s.rate_period_id = rp.rate_period_id
and rp.rate_period_type = rpt.rate_period_type
and s.transaction_type = curtt.transaction_type
and a.transaction_status = 'POSTED'
and s.transaction_type not in ('ECONSUME_NT','ECONSUME_NT_OFF')
and (s.retailer_transaction_id is null or
(s.retailer_transaction_id is not null and
s.offset_transaction_id is not null))
and sbpo.service_bill_period_id = sbpe.service_bill_period_id
AND SBPE.EFFECTIVE_DATE <= TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS')
and a.trans_posted_date > TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS')
and sbpe.service_id = se.service_id
AND (n_account_number IS NULL or A.ACCOUNT_NUMBER = n_account_number)
and (vc_service_type IS NULL OR se.service_type = vc_service_type)
and (vc_rate_category IS NULL OR rcat.rate_category = vc_rate_category)
union all
select /*+ RULE */ distinct
a.account_number,
a.trans_posted_date,
s.service_acct_trans_id,
sbpo.service_type SERVICE,
' ' Rate_Period,
rg.rate_group RATE_GROUP,
rcat.rate_category RATE_CATEGORY,
rclass.rate_class_desc RATE_CLASS_DESC,
0 FROM_UNITS,
0 TO_UNITS,
s.billing_charge_type BILL_CHARGE_TYPE,
s.transaction_type SERV_TRANS_TYPE,
a.transaction_source transaction_source,
round(decode((least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date)),0,1,(least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date))) / decode(trunc(sbpe.end_date) - trunc(sbpe.effective_date),
0, 1, trunc(sbpe.end_date) - trunc(sbpe.effective_date)) * decode(instr(s.transaction_type,'OFF'),0,s.trans_amount,0),2) revenue,
round(decode((least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date)),0,1,(least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date))) / decode(trunc(sbpe.end_date) - trunc(sbpe.effective_date),
0, 1, trunc(sbpe.end_date) - trunc(sbpe.effective_date)) * decode(instr(s.transaction_type,'OFF'),0,s.allocation_unit,0)) consumption,
0 UNIT_RATE,
sbpe.effective_date,
sbpe.end_date,
'B' groupin
from service_acct_transaction s,
account_transaction a,
service_bill_portion sbpo,
rate_category rcat,
rate_class rclass,
rate_group rg,
service_bill_period sbpe,
service se,
ck_unbilled_rev_trans_type curtt
where a.account_transaction_id = s.account_transaction_id
and s.service_bill_portion_id = sbpo.service_bill_portion_id
and sbpo.rate_category = rcat.rate_category
and rcat.rate_group = rg.rate_group
and sbpo.rate_class_id = rclass.rate_class_id
and s.rate_period_id is null
and a.transaction_status = 'POSTED'
and s.transaction_type not in ('ECONSUME_NT','ECONSUME_NT_OFF')
and s.transaction_type = curtt.transaction_type
and s.billing_charge_type <> 'RETAILER'
and sbpo.service_bill_period_id = sbpe.service_bill_period_id
and SBPE.EFFECTIVE_DATE <= TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS')
and a.trans_posted_date > TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS')
and sbpe.service_id = se.service_id
and (n_account_number IS NULL or a.account_number = n_account_number)
and (vc_service_type IS NULL OR se.service_type = vc_service_type)
and (vc_rate_category IS NULL OR rcat.rate_category = vc_rate_category)
union all
select /*+ RULE */ distinct
a.account_number,
a.trans_posted_date,
s.service_acct_trans_id,
sbpo.service_type SERVICE,
rpt.rate_period_type RATE_PERIOD,
rg.rate_group RATE_GROUP,
rcat.rate_category RATE_CATEGORY_DESC,
rclass.rate_class_desc RATE_CLASS_DESC,
0 FROM_UNITS,
0 TO_UNITS,
s.billing_charge_type BILL_CHARGE_TYPE,
s.transaction_type SERV_TRANS_TYPE,
a.transaction_source transaction_source,
round(decode((least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date)),0,1,(least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date))) / decode(trunc(sbpe.end_date) - trunc(sbpe.effective_date),
0, 1, trunc(sbpe.end_date) - trunc(sbpe.effective_date)) * decode(instr(s.transaction_type,'OFF'),0, decode(s.billing_charge_type, 'COMPET',
decode(s.transaction_type,'ECONSUME_NT',decode(rp.rate_period_type,'FCEC',0,s.trans_amount)
,s.trans_amount), s.trans_amount),0),2) revenue,
round(decode((least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date)),0,1,(least( TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS'), trunc(sbpe.end_date)) - trunc(sbpe.effective_date))) / decode(trunc(sbpe.end_date) - trunc(sbpe.effective_date),
0, 1, trunc(sbpe.end_date) - trunc(sbpe.effective_date)) * decode(instr(s.transaction_type,'OFF'),0, decode(s.billing_charge_type, 'COMPET',
decode(s.transaction_type,'ECONSUME_NT',decode(rp.rate_period_type,'FCEC',0,s.allocation_unit)
,s.allocation_unit), s.allocation_unit),0),2) consumption,
0 unit_rate,
sbpe.effective_date,
sbpe.end_date,
'C' GroupIn
from service_acct_transaction s,
account_transaction a,
service_bill_portion sbpo,
rate_category rcat,
rate_class rclass,
rate_period rp,
rate_period_type rpt,
rate_group rg,
service_bill_period sbpe,
service se,
ck_unbilled_rev_trans_type curtt
where a.account_transaction_id = s.account_transaction_id
and s.service_bill_portion_id = sbpo.service_bill_portion_id
and sbpo.rate_category = rcat.rate_category
and rcat.rate_group = rg.rate_group
and sbpo.rate_class_id = rclass.rate_class_id
and s.rate_period_id = rp.rate_period_id
and rp.rate_period_type = rpt.rate_period_type
and a.transaction_status = 'POSTED'
and s.transaction_type in ('ECONSUME_NT','ECONSUME_NT_OFF')
and s.transaction_type = curtt.transaction_type
and (s.retailer_transaction_id is null or
(s.retailer_transaction_id is not null and
s.offset_transaction_id is not null))
and sbpo.service_bill_period_id = sbpe.service_bill_period_id
AND SBPE.EFFECTIVE_DATE <= TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS')
and a.trans_posted_date > TO_DATE(dt_pivot_date,'DD-MON-YYYY HH24:MI:SS')
and sbpe.service_id = se.service_id
AND (n_account_number IS NULL or A.ACCOUNT_NUMBER = n_account_number)
and (vc_service_type IS NULL OR se.service_type = vc_service_type)
and (vc_rate_category IS NULL OR rcat.rate_category = vc_rate_category)
)group by
service,
rate_group,
rate_category,
rate_period,
serv_trans_type,
unit_rate,
from_units,
to_units,
bill_charge_type,
groupin;

end UP_Unbilled_Revenue_Query;
Re: Getting some wierd compile errors that i can't solve......Please Help [message #244974 is a reply to message #244973] Thu, 14 June 2007 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts.

ora-06544: PL/SQL: internal error, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]
 *Cause: A pl/sql internal error occurred.
 *Action:Report as a bug; the first argument is the internal error nuber.

Search on Metalink or/and call Oracle support.

Regards
Michel
Re: Getting some wierd compile errors that i can't solve......Please Help [message #244975 is a reply to message #244973] Thu, 14 June 2007 14:42 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
you're going to have for format your post if you want anyone to be able to read it. Start here: http://www.orafaq.com/forum/t/59964/66800/

Maybethenyouwillgetsomeresponses.Seehowharditistoreadwithoutformatting?
Re: Getting some wierd compile errors that i can't solve......Please Help [message #244977 is a reply to message #244973] Thu, 14 June 2007 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Read & FOLLOW the STICKY posts at teh top of this forum.
First make work, then make it fancy!
Debug the SQL using SQL*Plus before embedding it inside PL/SQL.
Re: Getting some wierd compile errors that i can't solve......Please Help [message #245063 is a reply to message #244977] Fri, 15 June 2007 01:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
CREATE OR REPLACE PROCEDURE up_unbilled_revenue_query (
   cur_unbilled_rev   IN OUT   ck_crystal_reports_cursors.unbilled_revenue_query
 , dt_pivot_date      IN       VARCHAR2
 , n_account_number   IN       NUMBER
 , vc_service_type    IN       VARCHAR2 DEFAULT 'ALL'
 , vc_rate_category   IN       VARCHAR2 DEFAULT 'ALL'
)
AS
BEGIN
   OPEN cur_unbilled_rev FOR
      /********Line 17 **********/
      SELECT   /*+ RULE*/
               service
             , rate_group
             , rate_category
             , rate_period
             , serv_trans_type
             , unit_rate
             , from_units
             , to_units
             , bill_charge_type
             , groupin
             , SUM (revenue) revenue
             , SUM (consumption) consumption
      FROM     (SELECT          /*+ RULE */
                DISTINCT        a.account_number
                              , a.trans_posted_date
                              , s.service_acct_trans_id
                              , sbpo.service_type service
                              , rpt.rate_period_type rate_period
                              , rg.rate_group rate_group
                              , rcat.rate_category rate_category
                              , rclass.rate_class_desc rate_class_desc
                              , NVL (rb.from_units, 0) from_units
                              , NVL (rb.to_units, 0) to_units
                              , s.billing_charge_type bill_charge_type
                              , s.transaction_type serv_trans_type
                              , a.transaction_source transaction_source
                              , ROUND
                                   (  DECODE
                                         ((  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                        , 0, 1
                                        , (  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                         )
                                    / DECODE (  TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                            , 0, 1
                                            ,   TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                             )
                                    * DECODE
                                         (INSTR (s.transaction_type, 'OFF')
                                        , 0, DECODE
                                             (s.billing_charge_type
                                            , 'COMPET', DECODE
                                                 (s.transaction_type
                                                , 'ECONSUME_NT', DECODE
                                                        (rp.rate_period_type
                                                       , 'FCEC', 0
                                                       , rba.allocation_amount
                                                        )
                                                , rba.allocation_amount
                                                 )
                                            , rba.allocation_amount
                                             )
                                        , 0
                                         )
                                  , 2
                                   ) revenue
                              , ROUND
                                   (  DECODE
                                         ((  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                        , 0, 1
                                        , (  LEAST
                                                (TO_DATE
                                                     (&dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                         )
                                    / DECODE (  TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                            , 0, 1
                                            ,   TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                             )
                                    * DECODE
                                         (INSTR (s.transaction_type, 'OFF')
                                        , 0, DECODE
                                             (s.billing_charge_type
                                            , 'COMPET', DECODE
                                                 (s.transaction_type
                                                , 'ECONSUME_NT', DECODE
                                                         (rp.rate_period_type
                                                        , 'FCEC', 0
                                                        , rba.allocation_unit
                                                         )
                                                , rba.allocation_unit
                                                 )
                                            , rba.allocation_unit
                                             )
                                        , 0
                                         )
                                  , 2
                                   ) consumption
                              , rb.unit_rate
                              , sbpe.effective_date
                              , sbpe.end_date
                              , 'A' groupin
                /****************Line 57 *****/
                FROM            service_acct_transaction s
                              , account_transaction a
                              , service_bill_portion sbpo
                              , rate_category rcat
                              , rate_class rclass
                              , rate_period rp
                              , rate_period_type rpt
                              , rate_group rg
                              , rate_block rb
                              , rate_block_allocation rba
                              , service_bill_period sbpe
                              , service se
                              , ck_unbilled_rev_trans_type curtt
                WHERE           a.account_transaction_id =
                                                      s.account_transaction_id
                AND             s.service_bill_portion_id =
                                                  sbpo.service_bill_portion_id
                AND             sbpo.rate_category = rcat.rate_category
                AND             rcat.rate_group = rg.rate_group
                AND             sbpo.rate_class_id = rclass.rate_class_id
                AND             sbpo.service_bill_portion_id =
                                                   rba.service_bill_portion_id
                AND             rba.rate_block_id = rb.rate_block_id
                AND             rb.rate_period_id = rp.rate_period_id
                AND             s.rate_period_id = rp.rate_period_id
                AND             rp.rate_period_type = rpt.rate_period_type
                AND             s.transaction_type = curtt.transaction_type
                AND             a.transaction_status = 'POSTED'
                AND             s.transaction_type NOT IN
                                           ('ECONSUME_NT', 'ECONSUME_NT_OFF')
                AND             (   s.retailer_transaction_id IS NULL
                                 OR (    s.retailer_transaction_id IS NOT NULL
                                     AND s.offset_transaction_id IS NOT NULL
                                    )
                                )
                AND             sbpo.service_bill_period_id =
                                                   sbpe.service_bill_period_id
                AND             sbpe.effective_date <=
                                   TO_DATE (dt_pivot_date
                                          , 'DD-MON-YYYY HH24:MI:SS'
                                           )
                AND             a.trans_posted_date >
                                   TO_DATE (dt_pivot_date
                                          , 'DD-MON-YYYY HH24:MI:SS'
                                           )
                AND             sbpe.service_id = se.service_id
                AND             (   n_account_number IS NULL
                                 OR a.account_number = n_account_number
                                )
                AND             (   vc_service_type IS NULL
                                 OR se.service_type = vc_service_type
                                )
                AND             (   vc_rate_category IS NULL
                                 OR rcat.rate_category = vc_rate_category
                                )
                UNION ALL
                SELECT          /*+ RULE */
                DISTINCT        a.account_number
                              , a.trans_posted_date
                              , s.service_acct_trans_id
                              , sbpo.service_type service
                              , ' ' rate_period
                              , rg.rate_group rate_group
                              , rcat.rate_category rate_category
                              , rclass.rate_class_desc rate_class_desc
                              , 0 from_units
                              , 0 to_units
                              , s.billing_charge_type bill_charge_type
                              , s.transaction_type serv_trans_type
                              , a.transaction_source transaction_source
                              , ROUND
                                   (  DECODE
                                         ((  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                        , 0, 1
                                        , (  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                         )
                                    / DECODE (  TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                            , 0, 1
                                            ,   TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                             )
                                    * DECODE (INSTR (s.transaction_type
                                                   , 'OFF')
                                            , 0, s.trans_amount
                                            , 0
                                             )
                                  , 2
                                   ) revenue
                              , ROUND
                                   (  DECODE
                                         ((  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                        , 0, 1
                                        , (  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                         )
                                    / DECODE (  TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                            , 0, 1
                                            ,   TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                             )
                                    * DECODE (INSTR (s.transaction_type
                                                   , 'OFF')
                                            , 0, s.allocation_unit
                                            , 0
                                             )
                                   ) consumption
                              , 0 unit_rate
                              , sbpe.effective_date
                              , sbpe.end_date
                              , 'B' groupin
                FROM            service_acct_transaction s
                              , account_transaction a
                              , service_bill_portion sbpo
                              , rate_category rcat
                              , rate_class rclass
                              , rate_group rg
                              , service_bill_period sbpe
                              , service se
                              , ck_unbilled_rev_trans_type curtt
                WHERE           a.account_transaction_id =
                                                      s.account_transaction_id
                AND             s.service_bill_portion_id =
                                                  sbpo.service_bill_portion_id
                AND             sbpo.rate_category = rcat.rate_category
                AND             rcat.rate_group = rg.rate_group
                AND             sbpo.rate_class_id = rclass.rate_class_id
                AND             s.rate_period_id IS NULL
                AND             a.transaction_status = 'POSTED'
                AND             s.transaction_type NOT IN
                                           ('ECONSUME_NT', 'ECONSUME_NT_OFF')
                AND             s.transaction_type = curtt.transaction_type
                AND             s.billing_charge_type <> 'RETAILER'
                AND             sbpo.service_bill_period_id =
                                                   sbpe.service_bill_period_id
                AND             sbpe.effective_date <=
                                   TO_DATE (dt_pivot_date
                                          , 'DD-MON-YYYY HH24:MI:SS'
                                           )
                AND             a.trans_posted_date >
                                   TO_DATE (dt_pivot_date
                                          , 'DD-MON-YYYY HH24:MI:SS'
                                           )
                AND             sbpe.service_id = se.service_id
                AND             (   n_account_number IS NULL
                                 OR a.account_number = n_account_number
                                )
                AND             (   vc_service_type IS NULL
                                 OR se.service_type = vc_service_type
                                )
                AND             (   vc_rate_category IS NULL
                                 OR rcat.rate_category = vc_rate_category
                                )
                UNION ALL
                SELECT          /*+ RULE */
                DISTINCT        a.account_number
                              , a.trans_posted_date
                              , s.service_acct_trans_id
                              , sbpo.service_type service
                              , rpt.rate_period_type rate_period
                              , rg.rate_group rate_group
                              , rcat.rate_category rate_category_desc
                              , rclass.rate_class_desc rate_class_desc
                              , 0 from_units
                              , 0 to_units
                              , s.billing_charge_type bill_charge_type
                              , s.transaction_type serv_trans_type
                              , a.transaction_source transaction_source
                              , ROUND
                                   (  DECODE
                                         ((  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                        , 0, 1
                                        , (  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                         )
                                    / DECODE (  TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                            , 0, 1
                                            ,   TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                             )
                                    * DECODE
                                         (INSTR (s.transaction_type, 'OFF')
                                        , 0, DECODE
                                             (s.billing_charge_type
                                            , 'COMPET', DECODE
                                                 (s.transaction_type
                                                , 'ECONSUME_NT', DECODE
                                                         (rp.rate_period_type
                                                        , 'FCEC', 0
                                                        , s.trans_amount
                                                         )
                                                , s.trans_amount
                                                 )
                                            , s.trans_amount
                                             )
                                        , 0
                                         )
                                  , 2
                                   ) revenue
                              , ROUND
                                   (  DECODE
                                         ((  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                        , 0, 1
                                        , (  LEAST
                                                (TO_DATE
                                                     (dt_pivot_date
                                                    , 'DD-MON-YYYY HH24:MI:SS'
                                                     )
                                               , TRUNC (sbpe.end_date)
                                                )
                                           - TRUNC (sbpe.effective_date)
                                          )
                                         )
                                    / DECODE (  TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                            , 0, 1
                                            ,   TRUNC (sbpe.end_date)
                                              - TRUNC (sbpe.effective_date)
                                             )
                                    * DECODE
                                         (INSTR (s.transaction_type, 'OFF')
                                        , 0, DECODE
                                             (s.billing_charge_type
                                            , 'COMPET', DECODE
                                                 (s.transaction_type
                                                , 'ECONSUME_NT', DECODE
                                                         (rp.rate_period_type
                                                        , 'FCEC', 0
                                                        , s.allocation_unit
                                                         )
                                                , s.allocation_unit
                                                 )
                                            , s.allocation_unit
                                             )
                                        , 0
                                         )
                                  , 2
                                   ) consumption
                              , 0 unit_rate
                              , sbpe.effective_date
                              , sbpe.end_date
                              , 'C' groupin
                FROM            service_acct_transaction s
                              , account_transaction a
                              , service_bill_portion sbpo
                              , rate_category rcat
                              , rate_class rclass
                              , rate_period rp
                              , rate_period_type rpt
                              , rate_group rg
                              , service_bill_period sbpe
                              , service se
                              , ck_unbilled_rev_trans_type curtt
                WHERE           a.account_transaction_id =
                                                      s.account_transaction_id
                AND             s.service_bill_portion_id =
                                                  sbpo.service_bill_portion_id
                AND             sbpo.rate_category = rcat.rate_category
                AND             rcat.rate_group = rg.rate_group
                AND             sbpo.rate_class_id = rclass.rate_class_id
                AND             s.rate_period_id = rp.rate_period_id
                AND             rp.rate_period_type = rpt.rate_period_type
                AND             a.transaction_status = 'POSTED'
                AND             s.transaction_type IN
                                           ('ECONSUME_NT', 'ECONSUME_NT_OFF')
                AND             s.transaction_type = curtt.transaction_type
                AND             (   s.retailer_transaction_id IS NULL
                                 OR (    s.retailer_transaction_id IS NOT NULL
                                     AND s.offset_transaction_id IS NOT NULL
                                    )
                                )
                AND             sbpo.service_bill_period_id =
                                                   sbpe.service_bill_period_id
                AND             sbpe.effective_date <=
                                   TO_DATE (dt_pivot_date
                                          , 'DD-MON-YYYY HH24:MI:SS'
                                           )
                AND             a.trans_posted_date >
                                   TO_DATE (dt_pivot_date
                                          , 'DD-MON-YYYY HH24:MI:SS'
                                           )
                AND             sbpe.service_id = se.service_id
                AND             (   n_account_number IS NULL
                                 OR a.account_number = n_account_number
                                )
                AND             (   vc_service_type IS NULL
                                 OR se.service_type = vc_service_type
                                )
                AND             (   vc_rate_category IS NULL
                                 OR rcat.rate_category = vc_rate_category
                                ))
      GROUP BY service
             , rate_group
             , rate_category
             , rate_period
             , serv_trans_type
             , unit_rate
             , from_units
             , to_units
             , bill_charge_type
             , groupin;
END up_unbilled_revenue_query;


I added a link to the SQL formatter in the sticky.

MHE

[Updated on: Fri, 15 June 2007 02:40]

Report message to a moderator

Re: Getting some wierd compile errors that i can't solve......Please Help [message #245081 is a reply to message #245063] Fri, 15 June 2007 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good idea... but I can't read lines of more than 300 characters wide and... who read the sticky? ./fa/1985/0/

Regards
Michel
Re: Getting some wierd compile errors that i can't solve......Please Help [message #245103 is a reply to message #245081] Fri, 15 June 2007 03:14 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I've noticed that the online formatter has it's flaws, yes.

The formatter has a "wrap after N characters" option. I didn't try it though...

MHE
Previous Topic: ora 00913
Next Topic: pl/sql parsing
Goto Forum:
  


Current Time: Fri Dec 09 05:48:36 CST 2016

Total time taken to generate the page: 0.15980 seconds