[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 933
  • Last Modified:

How do i increase the size of the buffer in toad

Hi I am trying to execute a procedure in toad that is 2800 lines of query.... However i am getting the error :

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

How do I increase the buffer size in toad?  I am using Version 9.5.0.31
0
ernie_shah
Asked:
ernie_shah
  • 26
  • 24
  • 2
  • +1
1 Solution
 
johnsoneSenior Oracle DBACommented:
This error is coming from your procedure, not from TOAD.

Typically this would mean that you are trying to put a string into a variable that is defined too small.
0
 
slightwv (䄆 Netminder) Commented:
That isn't a Toad buffer issue.

It is in the procedure or the pl/sql you are using to execute the procedure if it has OUT parameters.

For example:
You are trying to put 5 characters into a variable that can only hold 4.

http://ora-06502.ora-code.com/
0
 
ernie_shahAuthor Commented:
When the lines of code is less than 2000 i don't get an error, if it is greater than 2000 then I get this error, only when I try to add more lines of code i get this error...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ernie_shahAuthor Commented:
This is what my code looks like:  

CREATE OR REPLACE procedure LEASEOPS01.proc_testing_12



  (blockno_var varchar2(200), startdate_var  varchar2(200), enddate_var  varchar2(200))  is



begin

    begin  
    
    EXECUTE IMMEDIATE 'alter table leaseops01.testing8 nologging';
          
    execute immediate 'truncate table leaseops01.testing8';
    

   
    EXCEPTION
       WHEN OTHERS THEN
        -- -942 = table or view does not exist
        IF (SQLCODE <> -942) THEN
          RAISE_APPLICATION_ERROR(-20000, SQLERRM);
        END IF;

    END;




 

begin


execute immediate
   
    '

 
insert into leaseops01.testing8

  

--This script calculates from the 0% to the 10%

select closed_date,  fiscal,  bbls,USD_PER_BBL,  USD_VALUE, BUYING,  SELLING, AVG_RATE,orr1 as orr,  (USD_VALUE * AVG_RATE) *( orr1/100) orr_value, 
 TT_VALUE, BLOCK_NO, OPERATOR_NAME from (

select next_day1 as closed_date,  fiscal, split5 as bbls,USD_PER_BBL, split5 * USD_PER_BBL as 
USD_VALUE, BUYING,  SELLING, AVG_RATE,BLOCK_NO, OPERATOR_NAME,
  case when split3 = split5 then 0 else 10 end orr1, AVG_RATE * (split5 * USD_PER_BBL) as TT_VALUE  from (

SELECT 

a.next_day1, a.bbls, b.split3, split3 as split5,

 a.TENPERCENT,  a.ZEROPERCENT,  a.CLOSED_DATE
,  a.cum,  a.BASE_RATE_VOLUME,  a.fiscal,
 a.BLOCK_NO,  a.OPERATOR_NAME,  a.BUYING,  a.SELLING,  a.USD_PER_BBL,  a.USD_VALUE,  a.AVG_RATE,  a.TT_VALUE,  a.CRUDE_PRICE FROM (

select LAG(closed_date, 0) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, 
cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,

TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
        TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)
a

)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum

))
)
)

where split2 is  null 
order by closed_date DESC) a


(MUCH MORE LINES OF CODE........................)


'

;

DBMS_OUTPUT.ENABLE (200000);
commit;

   
 
END;
 
end;



/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Something in the code you are adding is causing an error when you execute it.

To execute it, it has to compile so the number of lines by itself is not the issue.
0
 
ernie_shahAuthor Commented:
This is the full code:
CREATE OR REPLACE procedure LEASEOPS01.proc_testing_12



  (blockno_var varchar2, startdate_var  varchar, enddate_var  varchar)  is



begin

    begin  
    
    EXECUTE IMMEDIATE 'alter table leaseops01.testing8 nologging';
          
    execute immediate 'truncate table leaseops01.testing8';
    

   
    EXCEPTION
       WHEN OTHERS THEN
        -- -942 = table or view does not exist
        IF (SQLCODE <> -942) THEN
          RAISE_APPLICATION_ERROR(-20000, SQLERRM);
        END IF;

    END;




 

begin


execute immediate
   
    '

 
insert into leaseops01.testing8

  

--This script calculates from the 0% to the 10%

select closed_date,  fiscal,  bbls,USD_PER_BBL,  USD_VALUE, BUYING,  SELLING, AVG_RATE,orr1 as orr,  (USD_VALUE * AVG_RATE) *( orr1/100) orr_value, 
 TT_VALUE, BLOCK_NO, OPERATOR_NAME from (

select next_day1 as closed_date,  fiscal, split5 as bbls,USD_PER_BBL, split5 * USD_PER_BBL as 
USD_VALUE, BUYING,  SELLING, AVG_RATE,BLOCK_NO, OPERATOR_NAME,
  case when split3 = split5 then 0 else 10 end orr1, AVG_RATE * (split5 * USD_PER_BBL) as TT_VALUE  from (

SELECT 

a.next_day1, a.bbls, b.split3, split3 as split5,

 a.TENPERCENT,  a.ZEROPERCENT,  a.CLOSED_DATE
,  a.cum,  a.BASE_RATE_VOLUME,  a.fiscal,
 a.BLOCK_NO,  a.OPERATOR_NAME,  a.BUYING,  a.SELLING,  a.USD_PER_BBL,  a.USD_VALUE,  a.AVG_RATE,  a.TT_VALUE,  a.CRUDE_PRICE FROM (

select LAG(closed_date, 0) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, 
cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,

TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
        TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)
a

)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum

))
)
)

where split2 is  null 
order by closed_date DESC) a

,

(
select next_day1, CLOSED_DATE, split3 , bbls, cum, BASE_RATE_VOLUME, fiscal, bbls - split3 as split5,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, case when bbls is not null then 0 end orr, TT_VALUE, CRUDE_PRICE from (

select case when split2 < split1 then split2 end split3, next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

select lag(closed_date, 1) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,

TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
        TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
 
)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


)
)
)
))
)Where split3 is not null 

order by closed_date DESC
)b

where a.next_day1 = b.next_day1

union



SELECT 


 a.next_day1,  a.bbls, b.split3, split3 as split5,

 a.TENPERCENT,  a.ZEROPERCENT,  a.CLOSED_DATE
,  a.cum,  a.BASE_RATE_VOLUME,  a.fiscal,
 a.BLOCK_NO,  a.OPERATOR_NAME,  a.BUYING,  a.SELLING,  a.USD_PER_BBL,  a.USD_VALUE,  a.AVG_RATE,  a.TT_VALUE,  a.CRUDE_PRICE FROM (

select LAG(closed_date, 0) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, 
cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,

TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)
a

)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


))
)
)

where split2 is  null 
order by closed_date DESC) a

,


(
select next_day1, CLOSED_DATE, split3 , bbls, cum, BASE_RATE_VOLUME, fiscal, bbls - split3 as split5,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, case when bbls is not null then 0 end orr, TT_VALUE, CRUDE_PRICE from (

select case when split2 < split1 then split2 end split3, next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

select lag(closed_date, 1) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,

TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
 



)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


)
)
)
))
)Where split3 is not null 

order by closed_date DESC
)b

where a.next_day1 = b.next_day1

union 

select c.next_day1,  c.bbls,c.split3,  bbls - split3 as split7,   

 c.TENPERCENT,  c.ZEROPERCENT,  c.CLOSED_DATE
, c.cum,  c.BASE_RATE_VOLUME,  c.fiscal,
 c.BLOCK_NO, c.OPERATOR_NAME, c.BUYING,  c.SELLING,  c.USD_PER_BBL, c.USD_VALUE,  c.AVG_RATE,  c.TT_VALUE,  c.CRUDE_PRICE from (

select

a.next_day1,  a.bbls, b.split3, 

 a.TENPERCENT,  a.ZEROPERCENT,  a.CLOSED_DATE
,  a.cum,  a.BASE_RATE_VOLUME,  a.fiscal,
 a.BLOCK_NO,  a.OPERATOR_NAME,  a.BUYING,  a.SELLING,  a.USD_PER_BBL,  a.USD_VALUE,  a.AVG_RATE,  a.TT_VALUE,  a.CRUDE_PRICE FROM (

select LAG(closed_date, 0) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, 
cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,

TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)
a

)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum

))
)
)

where split2 is  null 
order by closed_date DESC )a

,


(
select next_day1, CLOSED_DATE, split3 , bbls, cum, BASE_RATE_VOLUME, fiscal, bbls - split3 as split5,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, case when bbls is not null then 0 end orr, TT_VALUE, CRUDE_PRICE from (

select case when split2 < split1 then split2 end split3, next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

select lag(closed_date, 1) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,

TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
 
)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


)
)
)
))
)Where split3 is not null 


order by closed_date DESC
)b

where a.next_day1 = b.next_day1

)c
) order by orr1 desc
)

UNION

--This script calculates the split from 10% to 22%

select closed_date,  fiscal,  bbls,USD_PER_BBL,  USD_VALUE, BUYING,  SELLING, AVG_RATE,  orr1,  (USD_VALUE * AVG_RATE) * (orr1/100) orr_value,  TT_VALUE, block_no, operator_name from (

select  closed_date,  fiscal, split9 as bbls,USD_PER_BBL, split9 * USD_PER_BBL as USD_VALUE, BUYING,  SELLING, AVG_RATE,
  case when split8 = split9 then 10 else enhanced_norr end orr1, AVG_RATE * (split9 * USD_PER_BBL) as TT_VALUE, enhanced_norr, block_no, operator_name  from (

select abc.split8 as split9, enhanced_norr,
def1.buying, def1.SELLING, def1.tenpercent,  abc.split8,  def1.zeropercent, def1.closed_date, def1.next_day2, def1.USD_PER_BBL, def1.USD_VALUE,  def1.AVG_RATE,  
def1.TT_VALUE,  def1.CRUDE_PRICE, def1.next_day1, def1.cum,  def1.BASE_RATE_VOLUME,  def1.fiscal,   def1.barrels, def1.block_no, def1.operator_name from (

select 
 TENPERCENT - split7 split8, split7, split3, TENPERCENT, ZEROPERCENT,  CLOSED_DATE, Lead(closed_date, 1) OVER (ORDER BY closed_date) AS next_day2,--, bbls
 next_day1, cum,  BASE_RATE_VOLUME,  fiscal, ENHANCED_NORR,
BLOCK_NO, OPERATOR_NAME, BUYING,  SELLING, USD_PER_BBL, USD_VALUE,  AVG_RATE,  TT_VALUE,  CRUDE_PRICE, barrels from (


select c.next_day1, c.split3,  bbls - split3 as split7, c.bbls barrels,

  c.TENPERCENT, c.ZEROPERCENT,  c.CLOSED_DATE, 
 c.cum,  c.BASE_RATE_VOLUME,  c.fiscal, 
 c.BLOCK_NO, c.OPERATOR_NAME, c.BUYING,  c.SELLING,  c.USD_PER_BBL, c.USD_VALUE,  c.AVG_RATE,  c.TT_VALUE,  c.CRUDE_PRICE, c.ENHANCED_NORR
from (
select 

a.next_day1,  a.bbls, b.split3,

 a.TENPERCENT,  a.ZEROPERCENT,  a.CLOSED_DATE, 
  a.cum,  a.BASE_RATE_VOLUME,  a.fiscal,
 a.BLOCK_NO,  a.OPERATOR_NAME,  a.BUYING,  a.SELLING,  a.USD_PER_BBL,  a.USD_VALUE,  a.AVG_RATE,  a.TT_VALUE,  a.CRUDE_PRICE , a.ENHANCED_NORR

 FROM (

select LAG(closed_date, 0) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, 
cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, ENHANCED_NORR from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE,CRUDE_PRICE, ENHANCED_NORR  from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, ENHANCED_NORR from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, ENHANCED_NORR,  CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1, 
TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, ENHANCED_NORR from (


SELECT 
 b.*        
         ,
     (select BASE_NORR
              from leaseops01.OVERRIDING_ROYALTY orr    
              where '''||enddate_var||''' between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  BASE_NORR
        ,
        (select ENHANCED_NORR
              from leaseops01.OVERRIDING_ROYALTY orr
              where '''||enddate_var||''' between to_char(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  ENHANCED_NORR
        
from 
(

select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
        TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
)b


)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum



))
)
)




where split2 is  null 
order by closed_date DESC )a

,


(
select next_day1, CLOSED_DATE, split3 , bbls, cum, BASE_RATE_VOLUME, fiscal, bbls - split3 as split5,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, case when bbls is not null then 0 end orr, TT_VALUE, CRUDE_PRICE from (

select case when split2 < split1 then split2 end split3, next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

select lag(closed_date, 1) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,


TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
        TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
 



)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


)
)
)
))
)


order by closed_date DESC
)b

where a.next_day1 = b.next_day1

)c
)
)
abc

, 

(
select 
 TENPERCENT - split7 split8, TENPERCENT, ZEROPERCENT,  CLOSED_DATE, Lead(closed_date, 1) OVER (ORDER BY closed_date) AS next_day2,
 next_day1, cum,  BASE_RATE_VOLUME,  fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING,  SELLING, USD_PER_BBL, USD_VALUE,  AVG_RATE,  TT_VALUE,  CRUDE_PRICE,barrels from (


select c.next_day1,  c.bbls,c.split3,  bbls - split3 as split7, c.bbls barrels,

  c.TENPERCENT, c.ZEROPERCENT,  c.CLOSED_DATE
, c.cum,  c.BASE_RATE_VOLUME,  c.fiscal,
 c.BLOCK_NO, c.OPERATOR_NAME, c.BUYING,  c.SELLING,  c.USD_PER_BBL, c.USD_VALUE,  c.AVG_RATE,  c.TT_VALUE,  c.CRUDE_PRICE from (

select

a.next_day1,  a.bbls, b.split3, 

 a.TENPERCENT,  a.ZEROPERCENT,  a.CLOSED_DATE
,  a.cum,  a.BASE_RATE_VOLUME,  a.fiscal,
 a.BLOCK_NO,  a.OPERATOR_NAME,  a.BUYING,  a.SELLING,  a.USD_PER_BBL,  a.USD_VALUE,  a.AVG_RATE,  a.TT_VALUE,  a.CRUDE_PRICE FROM (

select LAG(closed_date, 0) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, 
cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,


TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
        TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)
a



)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum



))
)
)




where split2 is  null 
order by closed_date DESC )a

,


(
select next_day1, CLOSED_DATE, split3 , bbls, cum, BASE_RATE_VOLUME, fiscal, bbls - split3 as split5,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, case when bbls is not null then 0 end orr, TT_VALUE, CRUDE_PRICE from (

select case when split2 < split1 then split2 end split3, next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

select lag(closed_date, 1) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,


TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
 



)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


)
)
)
))
)


order by closed_date DESC
)b

where a.next_day1 = b.next_day1

)c
)
)def1
where abc.closed_date = def1.next_day2
and abc.split8 is not null

union

select case when abc.split8 is not null then def1.barrels - abc.split8 end split9,  ENHANCED_NORR ,
def1.buying, def1.SELLING, def1.tenpercent,  abc.split8,  def1.zeropercent, def1.closed_date, def1.next_day2, def1.USD_PER_BBL, def1.USD_VALUE,  def1.AVG_RATE,  
def1.TT_VALUE,  def1.CRUDE_PRICE, def1.next_day1, def1.cum,  def1.BASE_RATE_VOLUME,  def1.fiscal, def1.barrels, def1.block_no, def1.operator_name from (

select 
 TENPERCENT - split7 split8, split7, split3, TENPERCENT, ZEROPERCENT,  CLOSED_DATE, Lead(closed_date, 1) OVER (ORDER BY closed_date) AS next_day2,--, bbls
 next_day1, cum,  BASE_RATE_VOLUME,  fiscal,ENHANCED_NORR,
BLOCK_NO, OPERATOR_NAME, BUYING,  SELLING, USD_PER_BBL, USD_VALUE,  AVG_RATE,  TT_VALUE,  CRUDE_PRICE, barrels from (


select c.next_day1, c.split3,  bbls - split3 as split7, c.bbls barrels,c.ENHANCED_NORR,

  c.TENPERCENT, c.ZEROPERCENT,  c.CLOSED_DATE, 
 c.cum,  c.BASE_RATE_VOLUME,  c.fiscal,
 c.BLOCK_NO, c.OPERATOR_NAME, c.BUYING,  c.SELLING,  c.USD_PER_BBL, c.USD_VALUE,  c.AVG_RATE,  c.TT_VALUE,  c.CRUDE_PRICE 
from (
select 

a.next_day1,  a.bbls, b.split3,

 a.TENPERCENT,  a.ZEROPERCENT,  a.CLOSED_DATE, 
  a.cum,  a.BASE_RATE_VOLUME,  a.fiscal,
 a.BLOCK_NO,  a.OPERATOR_NAME,  a.BUYING,  a.SELLING, a.ENHANCED_NORR, a.USD_PER_BBL,  a.USD_VALUE,  a.AVG_RATE,  a.TT_VALUE,  a.CRUDE_PRICE 

 FROM (

select LAG(closed_date, 0) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, 
cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, ENHANCED_NORR, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE,TT_VALUE, CRUDE_PRICE, ENHANCED_NORR from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, ENHANCED_NORR, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, ENHANCED_NORR, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,


TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal, ENHANCED_NORR,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (



SELECT 
 b.*        
         ,
       (select BASE_NORR
              from leaseops01.OVERRIDING_ROYALTY orr    
              where '''||enddate_var||''' between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  BASE_NORR
        ,
        (select ENHANCED_NORR
              from leaseops01.OVERRIDING_ROYALTY orr
              where '''||enddate_var||''' between to_char(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  ENHANCED_NORR
        
from 
(


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
)b



)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum



))
)
)




where split2 is  null 
order by closed_date DESC )a

,


(
select next_day1, CLOSED_DATE, split3 , bbls, cum, BASE_RATE_VOLUME, fiscal, bbls - split3 as split5,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, case when bbls is not null then 0 end orr, TT_VALUE, CRUDE_PRICE from (

select case when split2 < split1 then split2 end split3, next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

select lag(closed_date, 1) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,


TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
 



)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


)
)
)
))
)


order by closed_date DESC
)b

where a.next_day1 = b.next_day1

)c
)
)
abc

, 

(
select 
 TENPERCENT - split7 split8, TENPERCENT, ZEROPERCENT,  CLOSED_DATE, Lead(closed_date, 1) OVER (ORDER BY closed_date) AS next_day2,

 next_day1, cum,  BASE_RATE_VOLUME,  fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING,  SELLING, USD_PER_BBL, USD_VALUE,  AVG_RATE,  TT_VALUE,  CRUDE_PRICE,barrels from (


select c.next_day1,  c.bbls,c.split3,  bbls - split3 as split7, c.bbls barrels,

  c.TENPERCENT, c.ZEROPERCENT,  c.CLOSED_DATE
, c.cum,  c.BASE_RATE_VOLUME,  c.fiscal,
 c.BLOCK_NO, c.OPERATOR_NAME, c.BUYING,  c.SELLING,  c.USD_PER_BBL, c.USD_VALUE,  c.AVG_RATE,  c.TT_VALUE,  c.CRUDE_PRICE from (

select

a.next_day1,  a.bbls, b.split3, 

 a.TENPERCENT,  a.ZEROPERCENT,  a.CLOSED_DATE
,  a.cum,  a.BASE_RATE_VOLUME,  a.fiscal,
 a.BLOCK_NO,  a.OPERATOR_NAME,  a.BUYING,  a.SELLING,  a.USD_PER_BBL,  a.USD_VALUE,  a.AVG_RATE,  a.TT_VALUE,  a.CRUDE_PRICE FROM (

select LAG(closed_date, 0) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, 
cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,


TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
      TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)
a



)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum



))
)
)




where split2 is  null 
order by closed_date DESC )a

,


(
select next_day1, CLOSED_DATE, split3 , bbls, cum, BASE_RATE_VOLUME, fiscal, bbls - split3 as split5,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, case when bbls is not null then 0 end orr, TT_VALUE, CRUDE_PRICE from (

select case when split2 < split1 then split2 end split3, next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

select lag(closed_date, 1) OVER (ORDER BY closed_date) AS next_day1, split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select zeropercent - cum2 split2, cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,


TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID

AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)a
 



)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


)
)
)
))
)


order by closed_date DESC
)b

where a.next_day1 = b.next_day1

)c
)
)def1
where abc.closed_date = def1.next_day2
and abc.split8 is not null


union

--This script calculates the first 0%

select bbls, orr, buying, SELLING, tenpercent,  null ,  zeropercent, closed_date, null as next_day2, USD_PER_BBL, USD_VALUE,  AVG_RATE,  
TT_VALUE,  null as CRUDE_PRICE, null as next_day1, null as cum, null as  BASE_RATE_VOLUME,  fiscal, null as barrels, BLOCK_NO, OPERATOR_NAME

from (

select CLOSED_DATE, fiscal, bbls, USD_PER_BBL,  USD_VALUE, BUYING, SELLING, AVG_RATE,  TENPERCENT, ZEROPERCENT,
TT_VALUE, split1,BLOCK_NO, OPERATOR_NAME,
case when bbls is not null then 0 end orr from (


select cum2, split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, bbls * USD_PER_BBL as USD_VALUE, AVG_RATE, AVG_RATE * (bbls * USD_PER_BBL) as TT_VALUE, 
CRUDE_PRICE from (

SELECT case when split1 is not null then SUM(split1)OVER  (ORDER BY CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end cum2, 
split1, TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (

SELECT  CASE WHEN  ZEROPERCENT  >= CUM THEN BBLS END SPLIT1,

TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, cum, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE from (


select  TENPERCENT,ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, 
SUM(a.bbls)OVER  (ORDER BY a.CLOSED_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

        TENPERCENT,ZEROPERCENT,
        TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b,
      
      (

SELECT SUM(TEN) TENPERCENT, SUM(ZERO) ZEROPERCENT, BLOCK_NAME FROM (

SELECT DISTINCT JV1.WELL_BOHOLE BOHOLE, JV1.END_TIME, TEST_DATE, JV1.FIRST_YEAR, JV1.SECOND_YEAR, 
JV1.NEW_VOLUME, JV1.PERCENT1 PERCENT1,JV1.BLOCK_NAME BLOCK_NAME,
CASE WHEN PERCENT1 = 0 THEN VOLUME END ZERO,
CASE WHEN PERCENT1 = 10 THEN VOLUME END TEN

FROM JV_INCENTIVES1 JV1,
    leaseops01.jv_incentives jv

WHERE TO_CHAR(END_TIME, ''YYYYMMDD'') = '''|| enddate_var||'''
AND jv1.well_name = jv.well_name
and jv1.BLOCK_NAME LIKE '''||blockno_var||'''
) 
GROUP BY BLOCK_NAME
)JV
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID


AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')

AND JV.BLOCK_NAME LIKE '''||blockno_var||'''

)
a 

)c group by TENPERCENT, ZEROPERCENT,CLOSED_DATE, bbls, BASE_RATE_VOLUME, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE, cum


)
)
)
)
where split1 is not null
order by closed_date DESC

))

UNION 

--this script calculates the bbls at the beginning of the month
select CLOSED_DATE , fiscal, split2 as bbls,  USD_PER_BBL,   USD_VALUE, BUYING, SELLING, AVG_RATE, orr as orr1, ORR_VALUE, 
round((USD_VALUE * AVG_RATE),2) TT_VALUE, BLOCK_NO, OPERATOR_NAME

            from (

select CLOSED_DATE, fiscal, split2, USD_PER_BBL,  USD_VALUE, BUYING, SELLING, 
        AVG_RATE,  orr, round(((USD_VALUE * AVG_RATE) * orr/100),2) ORR_VALUE,  
        round((USD_VALUE * AVG_RATE),2) TT_VALUE,BLOCK_NO, OPERATOR_NAME from (

select  CLOSED_DATE, fiscal, split2, USD_PER_BBL, (SPLIT2 *  CRUDE_PRICE) USD_VALUE, BUYING, SELLING, 
        AVG_RATE, base_norr orr, (USD_VALUE * AVG_RATE) * base_norr/100 ORR_VALUE,  
        (USD_VALUE * AVG_RATE) TT_VALUE,BLOCK_NO, OPERATOR_NAME
         
         from (

select f.*, (bbls - split1) split2 from (
select e.*, (cum - base_rate_volume)  split1 from  (
select d.*, row_number()over(partition by orr ORDER BY closed_date) myrn,
case when cum > base_rate_vol then base_norr end

from(

select c.* ,                 

case 

when c.cum  > c.base_rate_vol then  c.ENHANCED_NORR 
when c.cum  < c.base_rate_vol  then c.BASE_NORR
end orr

from
(

SELECT 
 b.* ,
                    (select br.volume
              from leaseops01.OVERRIDING_ROYALTY orr, leaseops01.base_rates br,leaseops01.BASERATE_DATES b,
              leaseops01.block bl
              where br.year = to_char(orr.END_DATE,''YYYY'')
              AND br.BRD_ID = b.BRD_ID
              AND b.BLOCK_ID = bl.BLOCK_ID
              AND '''|| enddate_var||''' between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL  between orr.MINIMUM_RANGE and MAXIMUM_RANGE
              and BLOCKNO= '''||blockno_var||'''
              ) base_rate_vol
         ,
        (select BASE_NORR
              from leaseops01.OVERRIDING_ROYALTY orr    
              where '''||enddate_var||''' between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  BASE_NORR
        ,
        (select ENHANCED_NORR
              from leaseops01.OVERRIDING_ROYALTY orr
              where '''||enddate_var||''' between to_char(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  ENHANCED_NORR
        
from 
(
select CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY (a.CLOSED_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')
order by closed_date
)a
)b 
)c 
)d where cum > base_rate_vol
)e where myrn = 1
)f
)
)

union



select  CLOSED_DATE, fiscal, bbls, USD_PER_BBL, (SPLIT2 *  CRUDE_PRICE) USD_VALUE, BUYING, SELLING, 
        AVG_RATE, orr, round(((USD_VALUE * AVG_RATE) * orr/100),2) ORR_VALUE,  
        round((USD_VALUE * AVG_RATE),2) TT_VALUE,BLOCK_NO, OPERATOR_NAME
         
         from (

select f.*, (bbls - split1) split2 from (
select e.*, (cum - base_rate_volume)  split1 from  (
select d.*, row_number()over(partition by orr ORDER BY closed_date) myrn,
case when cum > base_rate_vol then base_norr end

from(

select c.* ,                 

case 

when c.cum  > c.base_rate_vol then  c.ENHANCED_NORR 
when c.cum  < c.base_rate_vol  then c.BASE_NORR
end orr

from
(

SELECT 
 b.* ,
              (select br.volume
              from leaseops01.OVERRIDING_ROYALTY orr, leaseops01.base_rates br,leaseops01.BASERATE_DATES b,
              leaseops01.block bl
              where br.year = to_char(orr.END_DATE,''YYYY'')
              AND br.BRD_ID = b.BRD_ID
              AND b.BLOCK_ID = bl.BLOCK_ID
              AND '''|| enddate_var||''' between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL  between orr.MINIMUM_RANGE and MAXIMUM_RANGE
              and BLOCKNO= '''||blockno_var||'''
              ) base_rate_vol
         ,
        (select BASE_NORR
              from leaseops01.OVERRIDING_ROYALTY orr    
              where '''||enddate_var||''' between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  BASE_NORR
        ,
        (select ENHANCED_NORR
              from leaseops01.OVERRIDING_ROYALTY orr
              where '''||enddate_var||''' between to_char(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  ENHANCED_NORR
from 
(
select CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY (a.CLOSED_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')
order by closed_date
)a
)b 
)c 
)d
where cum < base_rate_vol
)e 
)f
))

UNION


--To calculate split when cum is greater than base rate volume
select  CLOSED_DATE, fiscal, cum - base_rate_vol as bbls, USD_PER_BBL, (SPLIT2 *  CRUDE_PRICE) USD_VALUE, BUYING, SELLING, 
        AVG_RATE, orr, round(((USD_VALUE * AVG_RATE) * orr/100),2) ORR_VALUE,  
        round((USD_VALUE * AVG_RATE),2) TT_VALUE,BLOCK_NO, OPERATOR_NAME
         
         from (

select f.*, (bbls - split1) split2 from (
select e.*, (cum - base_rate_volume)  split1 from  (
select d.*, row_number()over(partition by orr ORDER BY closed_date) myrn,
case when cum > base_rate_vol then base_norr end

from(

select c.* ,                 

case 

when c.cum  > c.base_rate_vol then  c.ENHANCED_NORR 
when c.cum  < c.base_rate_vol  then c.BASE_NORR
end orr

from
(

SELECT 
 b.* ,
          (select br.volume
              from leaseops01.OVERRIDING_ROYALTY orr, leaseops01.base_rates br,leaseops01.BASERATE_DATES b,
              leaseops01.block bl
              where br.year = to_char(orr.END_DATE,''YYYY'')
              AND br.BRD_ID = b.BRD_ID
              AND b.BLOCK_ID = bl.BLOCK_ID
              AND '''|| enddate_var||''' between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL  between orr.MINIMUM_RANGE and MAXIMUM_RANGE
              and BLOCKNO= '''||blockno_var||'''
              ) base_rate_vol
         ,
        (select BASE_NORR
              from leaseops01.OVERRIDING_ROYALTY orr    
              where '''||enddate_var||''' between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  BASE_NORR
        ,
        (select ENHANCED_NORR
              from leaseops01.OVERRIDING_ROYALTY orr
              where '''||enddate_var||''' between to_char(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
              and b.USD_PER_BBL between orr.MINIMUM_RANGE and MAXIMUM_RANGE
        )  ENHANCED_NORR
        
from 
(
select CLOSED_DATE, bbls, BASE_RATE_VOLUME, SUM(a.bbls)OVER  (ORDER BY (a.CLOSED_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum, fiscal,
BLOCK_NO, OPERATOR_NAME, BUYING, SELLING, USD_PER_BBL, USD_VALUE, AVG_RATE, TT_VALUE, CRUDE_PRICE
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), ''YYYYMMDD'') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),''/YYYY'') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
AND '''||enddate_var||'''  between TO_CHAR(orr.START_DATE, ''YYYYMMDD'') and TO_CHAR(orr.END_DATE, ''YYYYMMDD'')
and BLOCKNO= '''||blockno_var||'''
AND   BL.BLOCKNO LIKE '''||blockno_var||'''
AND   TO_CHAR(TDR.TICKET_DATE, ''YYYYMMDD'') BETWEEN '''||startdate_var||''' and '''||enddate_var||'''
AND   TO_CHAR(OB.END_DATE, ''YYYYMMDD'') <= '''|| enddate_var||'''
AND   to_char(OB.START_DATE, ''YYYYMMDD'') >= '''|| startdate_var||'''
AND  br.year = to_char(orr.END_DATE,''YYYY'')
order by closed_date
)a
)b 
)c 
)d
where cum > base_rate_vol
)e where myrn = 1
)f
)order by orr desc


'

;

DBMS_OUTPUT.ENABLE (
     200000);
commit;

   
 
END;
 
end;



/

Open in new window

0
 
ernie_shahAuthor Commented:
If I break this code in half it works.....
0
 
slightwv (䄆 Netminder) Commented:
From the code you posted, are you saying it is the insert into followed by over 2000 lines of a single select statement?

What code are you trying to add to what is already there?


The error is pretty straight forward.

Here is an example of code that causes it:
declare
    myvar char(4);
begin
	myvar := '12345';
end;
/

Open in new window


I declare a variable limited to 4 characters and try to assign it 5 characters.
0
 
slightwv (䄆 Netminder) Commented:
You are using execute immediate followed by a string to do the insert.

Strings in PL/SQL have a limit of 32K.

In 11g, you can use a CLOB with execute immediate but why would you?

Any reason you aren't using native SQL without execute immediate?
0
 
ernie_shahAuthor Commented:
Thanks I did not know how to place the code in the code block...

When I try to run the query without execute immediate I get a compile error
0
 
johnsoneSenior Oracle DBACommented:
I would break each SQL statement into an individual call to EXECUTE IMMEDIATE.  Although at a quick glance, I'm not sure why you need to use EXECUTE IMMEDIATE in the first place.

There also seems to be a lot of select statements in there, which wouldn't be allowed.

As far as I know, EXECUTE IMMEDIATE can only process one statement at a time.
0
 
slightwv (䄆 Netminder) Commented:
You need execute immediate only on DDL in a procedure.

The insert into select shouldn't need any.

The variables you have should be able to be used as-is.

For example:
drop table tab1 purge;
create table tab1(co1l varchar2(10), col2 date);

create or replace procedure myproc(p_var1 in varchar2, p_var2 in varchar2)
is
begin
	execute immediate 'truncate table tab1';

	insert into tab1 (
	   select p_var1, to_date(p_var2,'MM/DD/YYYY') from dual
	);

	commit;
end;
/

show errors


exec myproc('Hello','01/01/2014');

select * from tab1;

Open in new window

0
 
awking00Commented:
Have you tried getting rid of the execute immediate that appears to start at line 38 and goes to line 2467? It would only take an average of 13.5 characters per line to exceed 32K and it doesn't appear that execute immediate is needed anyway since all you are doing is selecting and inserting. You should also get rid of all of those linefeeds since pure sql doesn't like blank lines. You will also need to change all of those double and triple quotes needed to emulate a single string back to single quotes.
0
 
slightwv (䄆 Netminder) Commented:
>>Have you tried getting rid of the execute immediate
>>it doesn't appear that execute immediate is needed anyway since all you are doing is selecting and inserting

Pretty sure that is what I had already suggested?

>>You should also get rid of all of those linefeeds since pure sql doesn't like blank lines

sqlplus without telling it blank lines are allowed, YES.

Not true in PL/SQL.  You can have as many blank lines as you like.

Using my example above, this runs just fine:
drop table tab1 purge;
create table tab1(co1l varchar2(10), col2 date);

create or replace procedure myproc(p_var1 in varchar2, p_var2 in varchar2)
is
begin
	execute immediate 'truncate table tab1';

	insert into tab1 (









	   select



	   p_var1,





	   to_date(p_var2,'MM/DD/YYYY')






	   from dual











	);

	commit;
end;
/

show errors


exec myproc('Hello','01/01/2014');

select * from tab1;

Open in new window

0
 
ernie_shahAuthor Commented:
Hi slightwv

The above code is great, but how do i reference the bind variables for example, I am using:
AND   to_char(OB.START_DATE, 'YYYYMMDD') >= '''|| startdate_var||'''
and BLOCKNO= '''||blockno_var||'''
and this returns no data
0
 
slightwv (䄆 Netminder) Commented:
You aren't using bind variables.  You were using string concatenation.  Different things.

Just reference them like I did in my example:

AND   to_char(OB.START_DATE, 'YYYYMMDD') >= startdate_var
 and BLOCKNO= blockno_var
0
 
ernie_shahAuthor Commented:
I was using the string concatenation to replace the bind variables so my initial code was:

AND   to_char(OB.START_DATE, 'YYYYMMDD') >= :startdate_var
and BLOCKNO= :blockno_var

and to get it to work in the procedure I changed it to:

AND   to_char(OB.START_DATE, 'YYYYMMDD') >= '''|| startdate_var||'''
and BLOCKNO= '''||blockno_var||'''

but when I use :

AND   to_char(OB.START_DATE, 'YYYYMMDD') >= startdate_var
 and BLOCKNO= blockno_var

as in your example I am not getting data
so what is the syntax for the bind variables?
0
 
slightwv (䄆 Netminder) Commented:
You never posted an example using bind variables or I missed them.

If you aren't getting data, then check the format of the variables you are passing.

Also make sure the data types match the column values.  Some times when Oracle tries to do implicit data type conversions things get messed up.

for example:
blockno_var is defined as varchar2.  Is BLOCKNO in the database varchar2?

I would also compares dates to dates instead of strings:
OB.START_DATE >= to_date(startdate_var,'YYYMMDD')
0
 
ernie_shahAuthor Commented:
You are correct, the script I posted was after I tried to use the concatenation as bind variables.  BLOCKNO is defined as varchar2 in the database.

Even when I take out the dates and just use one bind variable which is BLOCKNO it is still not working.....

Am I referencing the bind variable correctly?
0
 
slightwv (䄆 Netminder) Commented:
>> Am I referencing the bind variable correctly?

Again, you are not using bind variables.  They are regular pl/sql variables.

What I posted should work.  The example I posted was a complete test case that mirrors what I believe you are doing as close as I can without your tables and data.

If the select works from sqlplus with hard-coded values like:

select ...
where ...
AND   to_char(OB.START_DATE, 'YYYYMMDD') >= '20140101'
  and BLOCKNO= '123456'

Then it should work with:
AND   to_char(OB.START_DATE, 'YYYYMMDD') >= startdate_var
  and BLOCKNO= blockno_var

As long as startdate_var has the value '20140101'
and blockno_var has the value '123456'
0
 
ernie_shahAuthor Commented:
When I hard code it, no problems but when I put the variable i get no data....when I run your code I get data......
0
 
slightwv (䄆 Netminder) Commented:
>>but when I put the variable i get no data

Really not possible unless the formatting doesn't match.

Can you post the working code and the non-working code with the example of how you are calling the procedure (remember:  in code blocks please)?
0
 
ernie_shahAuthor Commented:
This is an example of the working code
select  CLOSED_DATE, fiscal,bbls, USD_PER_BBL,USD_VALUE, BUYING, SELLING, AVG_RATE,TT_VALUE,
BLOCK_NO, OPERATOR_NAME
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), 'YYYYMMDD') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),'/YYYY') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
and BLOCKNO= :blockno_var
AND   BL.BLOCKNO LIKE :blockno_var
AND  br.year = to_char(orr.END_DATE,'YYYY')
order by closed_date
)a

Open in new window

0
 
ernie_shahAuthor Commented:
This is an example of the procedure:
DROP TABLE LEASEOPS01.TESTING21 PURGE;


CREATE TABLE LEASEOPS01.TESTING21
(
  CLOSED_DATE    VARCHAR2(8 BYTE),
  FISCAL         VARCHAR2(45 BYTE),
  BBLS           NUMBER,
  USD_PER_BBL    NUMBER(10,3),
  USD_VALUE      NUMBER(10,3),
  BUYING         NUMBER(6,5),
  SELLING        NUMBER(6,5),
  AVG_RATE       NUMBER(10,3),
  TT_VALUE       NUMBER(10,3),
  BLOCK_NO       VARCHAR2(45 BYTE),
  OPERATOR_NAME  VARCHAR2(45 BYTE)
)
TABLESPACE WISDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE OR REPLACE procedure LEASEOPS01.proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
    execute immediate 'truncate table LEASEOPS01.TESTING21';

    insert into LEASEOPS01.TESTING21 (select * from (select CLOSED_DATE, fiscal,bbls, USD_PER_BBL,USD_VALUE, BUYING, SELLING, AVG_RATE,TT_VALUE,
BLOCK_NO, OPERATOR_NAME
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), 'YYYYMMDD') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),'/YYYY') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
AND   BL.BLOCKNO LIKE blockno_var
AND  br.year = to_char(orr.END_DATE,'YYYY')

order by closed_date
)a ));

    commit;
end;
/

show errors


exec LEASEOPS01.proc_testing_19('WD9');

select * from LEASEOPS01.TESTING21;

Open in new window

select  CLOSED_DATE, fiscal,bbls, USD_PER_BBL,USD_VALUE, BUYING, SELLING, AVG_RATE,TT_VALUE,
BLOCK_NO, OPERATOR_NAME
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), 'YYYYMMDD') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),'/YYYY') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
and BLOCKNO= :blockno_var
AND   BL.BLOCKNO LIKE :blockno_var
AND  br.year = to_char(orr.END_DATE,'YYYY')
order by closed_date
)a

Open in new window

0
 
ernie_shahAuthor Commented:

select  CLOSED_DATE, fiscal,bbls, USD_PER_BBL,USD_VALUE, BUYING, SELLING, AVG_RATE,TT_VALUE,
BLOCK_NO, OPERATOR_NAME
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), 'YYYYMMDD') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),'/YYYY') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
and BLOCKNO= :blockno_var
AND   BL.BLOCKNO LIKE :blockno_var
AND  br.year = to_char(orr.END_DATE,'YYYY')
order by closed_date
)a

Open in new window

DROP TABLE LEASEOPS01.TESTING21 PURGE;


CREATE TABLE LEASEOPS01.TESTING21
(
  CLOSED_DATE    VARCHAR2(8 BYTE),
  FISCAL         VARCHAR2(45 BYTE),
  BBLS           NUMBER,
  USD_PER_BBL    NUMBER(10,3),
  USD_VALUE      NUMBER(10,3),
  BUYING         NUMBER(6,5),
  SELLING        NUMBER(6,5),
  AVG_RATE       NUMBER(10,3),
  TT_VALUE       NUMBER(10,3),
  BLOCK_NO       VARCHAR2(45 BYTE),
  OPERATOR_NAME  VARCHAR2(45 BYTE)
)
TABLESPACE WISDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE OR REPLACE procedure LEASEOPS01.proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
    execute immediate 'truncate table LEASEOPS01.TESTING21';

    insert into LEASEOPS01.TESTING21 (select * from (select CLOSED_DATE, fiscal,bbls, USD_PER_BBL,USD_VALUE, BUYING, SELLING, AVG_RATE,TT_VALUE,
BLOCK_NO, OPERATOR_NAME
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), 'YYYYMMDD') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),'/YYYY') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
AND   BL.BLOCKNO LIKE blockno_var
AND  br.year = to_char(orr.END_DATE,'YYYY')

order by closed_date
)a ));

    commit;
end;
/

show errors


exec LEASEOPS01.proc_testing_19('WD9');

select * from LEASEOPS01.TESTING21;

Open in new window

0
 
ernie_shahAuthor Commented:
I submitted twice....sorry
0
 
slightwv (䄆 Netminder) Commented:
For the 'working' one that is using bind variables, I don't see where/how you are setting them.

the working one has:
and BLOCKNO= :blockno_var
AND   BL.BLOCKNO LIKE :blockno_var


The procedure is missing:
and BLOCKNO= blockno_var
0
 
ernie_shahAuthor Commented:
That's the problem, I don't know how to set it....

The working code is what i am using to create the procedure

I took out and BLOCKNO= :blockno_var and its still not working in the procedure...

How do i set the  variable in the procedure?
0
 
slightwv (䄆 Netminder) Commented:
I think we are getting confused.

You run the 'working' select and get data.  The 'working' select uses bind variables (the ':' makes them bind variables).

For the select to run, you must first assign a value to the bind variable or you will get an error.

See the example below:
SQL> select * from dual where dummy=:q;
SP2-0552: Bind variable "Q" not declared.
SQL> var q varchar2(10)
SQL> exec :q := 'X';

PL/SQL procedure successfully completed.

SQL> select * from dual where dummy=:q;
X

SQL>

Open in new window


So, the working code has values.  You need to make sure the actual values are the same.

In the procedure call example you posted, blockno_var will have the value 'WD9'

When you manually run the select with bind variables, does :blockno_var have the same value?

>>I took out and BLOCKNO= :blockno_var and its still not working in the procedure...

The 'working' select has that in it.  If you take it out of the procedure, you aren't running the exact same code...

>>How do i set the  variable in the procedure?

You are passing in the values to the parameters in the procedure when you call the procedure.  You don't set values of parameters inside the procedure.
0
 
ernie_shahAuthor Commented:
What I meant to say was that I took out the BLOCKNO= :blockno_var out of the original code so that it is the exact same code as the procedure.....

You are passing in the values to the parameters in the procedure when you call the procedure.  You don't set values of parameters inside the procedure.

ok....how do i call the procedure to test that i am getting values?

I thought this line in the procedure was the call to the procedure - exec LEASEOPS01.proc_testing_19('WD9');
0
 
slightwv (䄆 Netminder) Commented:
>>I thought this line in the procedure was the call to the procedure - exec LEASEOPS01.proc_testing_19('WD9');

It is.

The stand-alone select that is using bind variables is the one you say returns rows.  You need to confirm the bind variable,blockno_var , has the value of 'WD9'.  Then the two selects should be the same.

If the bind variable has the value of 'XXX', then I can see how the select returns rows and the procedure doesn't.

If you can provide some sample data to go with the table where the select returns data and the procedure doesn't, I can set up the exact same test on my system and see exactly where the problems are.
0
 
slightwv (䄆 Netminder) Commented:
So this returns data:
select  CLOSED_DATE, fiscal,bbls, USD_PER_BBL,USD_VALUE, BUYING, SELLING, AVG_RATE,TT_VALUE,
BLOCK_NO, OPERATOR_NAME
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), 'YYYYMMDD') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),'/YYYY') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
AND   BL.BLOCKNO LIKE 'WD9'
AND  br.year = to_char(orr.END_DATE,'YYYY')
order by closed_date
)a

Open in new window

0
 
ernie_shahAuthor Commented:
Yes it does....I will send some sample data for you....
0
 
ernie_shahAuthor Commented:
Here are some of the results:
CLOSED_DATE	FISCAL	BBLS	USD_PER_BBL	USD_VALUE	BUYING	SELLING	AVG_RATE	TT_VALUE	BLOCK_NO	OPERATOR_NAME
										
20130505	41/2013	3972	92.3	366616	6.3401	6.4564	6.398	2345700.822	WD9	LEASE OPERATORS LTD
20130505	41/2013	3972	92.3	366616	6.3401	6.4564	6.398	2345700.822	WD9	LEASE OPERATORS LTD
20130510	42/2013	4173	92.3	385168	6.3773	6.4435	6.41	2469080.947	WD9	LEASE OPERATORS LTD
20130510	42/2013	4173	92.3	385168	6.3773	6.4435	6.41	2469080.947	WD9	LEASE OPERATORS LTD
20130514	43/2013	3784	92.3	349263	6.4089	6.4274	6.418	2241622.323	WD9	LEASE OPERATORS LTD
20130514	43/2013	3784	92.3	349263	6.4089	6.4274	6.418	2241622.323	WD9	LEASE OPERATORS LTD
20130517	44/2013	2485	92.3	229366	6.3797	6.4435	6.412	1470603.046	WD9	LEASE OPERATORS LTD
20130517	44/2013	2485	92.3	229366	6.3797	6.4435	6.412	1470603.046	WD9	LEASE OPERATORS LTD
20130521	45/2013	3329	92.3	307267	6.4066	6.4588	6.433	1976556.431	WD9	LEASE OPERATORS LTD
20130521	45/2013	3329	92.3	307267	6.4066	6.4588	6.433	1976556.431	WD9	LEASE OPERATORS LTD
20130524	46/2013	2549	92.3	235273	6.3891	6.4364	6.413	1508746.931	WD9	LEASE OPERATORS LTD
20130524	46/2013	2549	92.3	235273	6.3891	6.4364	6.413	1508746.931	WD9	LEASE OPERATORS LTD

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I hope that data was scrubbed of anything sensitive.

OK, try this:
CREATE OR REPLACE procedure LEASEOPS01.proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
    execute immediate 'truncate table LEASEOPS01.TESTING21';

    insert into LEASEOPS01.TESTING21 (
select  CLOSED_DATE, fiscal,bbls, USD_PER_BBL,USD_VALUE, BUYING, SELLING, AVG_RATE,TT_VALUE,
BLOCK_NO, OPERATOR_NAME
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), 'YYYYMMDD') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),'/YYYY') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
AND   BL.BLOCKNO LIKE 'WD9'
AND  br.year = to_char(orr.END_DATE,'YYYY')
order by closed_date
)a
);

    commit;
end;
/

show errors


exec LEASEOPS01.proc_testing_19('WD9');.


select * from LEASEOPS01.TESTING21;

Open in new window

0
 
ernie_shahAuthor Commented:
This will work because the variable is hard coded.......  I tried hard coding the variable and it works.....
0
 
slightwv (䄆 Netminder) Commented:
It is a one step at a time process.

Confirm what I posted runs as-is.  Then I'll make a change and you can try again.

Or give me sample data and table definition for all the tables, and I can run the procedure myself.
0
 
slightwv (䄆 Netminder) Commented:
Here is what I would like you to test as-is:
CREATE OR REPLACE procedure LEASEOPS01.proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
    execute immediate 'truncate table LEASEOPS01.TESTING21';

    insert into LEASEOPS01.TESTING21 (
select  CLOSED_DATE, fiscal,bbls, USD_PER_BBL,USD_VALUE, BUYING, SELLING, AVG_RATE,TT_VALUE,
BLOCK_NO, OPERATOR_NAME
from
(
select distinct

       TO_CHAR ((TDR.TICKET_DATE), 'YYYYMMDD') CLOSED_DATE,
       TDR.CONSIGNMENT_NUMBER||TO_CHAR (add_months (TDR.TICKET_DATE,3),'/YYYY') FISCAL,
       TDR.VOLUME BBLS,
       BL.BLOCKNO BLOCK_NO,
       BL.BLOCK_TYPE,
       O.OPERATOR_NAME OPERATOR_NAME,
       BR.BUYING BUYING,
       BR.SELLING SELLING,
      CRU.CRUDE_PRICE_LAND USD_PER_BBL,
      BR.VOLUME BASE_RATE_VOLUME,
      CRU.CRUDE_PRICE_LAND CRUDE_PRICE,
       ROUND (TDR.VOLUME * CRU.CRUDE_PRICE_LAND) USD_VALUE,
       (BR.BUYING + BR.SELLING)/2 AVG_RATE,
      ROUND(TDR.VOLUME * CRU.CRUDE_PRICE_LAND)*((BR.BUYING + BR.SELLING)/2) TT_VALUE
       
FROM  LEASEOPS01.TICKETS TDR,
      LEASEOPS01.BLOCK BL,
      LEASEOPS01.OPERATORS O,
      LEASEOPS01.OPERATOR_LICENSEE OL,
      LEASEOPS01.OPERATOR_BANK OB,
      LEASEOPS01.BANK_RATES BR,
      LEASEOPS01.BANK B,
     (SELECT C.CRUDE_PRICE_LAND, C.CRUDE_DATE
       FROM CRUDE_RATE C )CRU,
      LEASEOPS01.LICENSEE_BLOCK LB,
      LEASEOPS01.LICENSEE L,
      LEASEOPS01.OVERRIDING_ROYALTY orr,
      LEASEOPS01.base_rates br,
      LEASEOPS01.BASERATE_DATES b
            
WHERE TDR.BLOCK_ID = BL.BLOCK_ID
AND   OL.OPERATOR_ID = O.OPERATOR_ID
AND   O.OPERATOR_ID = OB.OPERATOR_ID
AND   OB.BANK_ID = B.BANK_ID
AND   B.BANK_ID = BR.BANK_ID
AND   BL.BLOCK_ID = LB.BLOCK_ID
AND   LB.LICENSEE_ID = L.LICENSEE_ID
AND   L.LICENSEE_ID = OL.LICENSEE_ID
AND   TDR.TICKET_DATE = BR.RATE_DATE
AND   br.BRD_ID = b.BRD_ID
AND b.BLOCK_ID = bl.BLOCK_ID
AND   BL.BLOCKNO LIKE blockno_var
AND  br.year = to_char(orr.END_DATE,'YYYY')
order by closed_date
)a
);

    commit;
end;
/

show errors


exec LEASEOPS01.proc_testing_19('WD9');


select * from LEASEOPS01.TESTING21;
                                          

Open in new window

0
 
ernie_shahAuthor Commented:
OK, try this:  This script freezes up TOAD and is processing very long and i have to close TOAD cause it freezes up the system

Here is what I would like you to test as-is:  I tested this and it returned no data (no problems unlike the first script)
0
 
ernie_shahAuthor Commented:
This is the results:


Procedure created.
No errors.
PL/SQL procedure successfully completed.
no rows selected.
0
 
slightwv (䄆 Netminder) Commented:
>>OK, try this:  This script freezes up TOAD and is processing very long and i have to close TOAD cause it freezes up the system

That is the one that has the hard-coded value for BL.BLOCKNO.  I would like to know what is causing the issue with it and the one below it since there is only one difference.


Lets start breaking it down into smaller pieces.

Please post the results from the following script:
select BL.BLOCKNO BLOCK_NO
FROM  LEASEOPS01.BLOCK BL
WHERE 
AND   BL.BLOCKNO LIKE 'WD9'
and rownum<=10
/



CREATE OR REPLACE procedure LEASEOPS01.proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
    execute immediate 'truncate table LEASEOPS01.TESTING21';

    insert into LEASEOPS01.TESTING21 (BLOCK_NO)
    (select BLOCK_NO
		from
		(
			select 
       			BL.BLOCKNO BLOCK_NO
			FROM  LEASEOPS01.BLOCK BL
			WHERE 
			AND   BL.BLOCKNO LIKE blockno_var
			and rownum<=10
			)a
	);
    commit;
end;
/

show errors


exec LEASEOPS01.proc_testing_19('WD9');

select BLOCK_NO from LEASEOPS01.TESTING21;

Open in new window

0
 
ernie_shahAuthor Commented:
This is the code:  
DROP TABLE LEASEOPS01.TESTING21 PURGE;


CREATE TABLE LEASEOPS01.TESTING21
(
  
  BLOCK_NO       VARCHAR2(45 BYTE)

)
TABLESPACE WISDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE OR REPLACE procedure LEASEOPS01.proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
    execute immediate 'truncate table LEASEOPS01.TESTING21';

    insert into LEASEOPS01.TESTING21 (BLOCK_NO)
    (select BLOCK_NO
		from
		(
			select 
       			BL.BLOCKNO BLOCK_NO
			FROM  LEASEOPS01.BLOCK BL
			
			WHERE   BL.BLOCKNO LIKE blockno_var
			and rownum<=10
			)a
	);
    commit;
end;
/

show errors


exec LEASEOPS01.proc_testing_19('WD9');

select BLOCK_NO from LEASEOPS01.TESTING21;

Open in new window


This is the results:

Table dropped.
Table created.
Procedure created.
No errors.
PL/SQL procedure successfully completed.
no rows selected.
0
 
slightwv (䄆 Netminder) Commented:
You missed the select I posted.

select BL.BLOCKNO BLOCK_NO
FROM  LEASEOPS01.BLOCK BL
WHERE 
AND   BL.BLOCKNO LIKE 'WD9'
and rownum<=10
/

Open in new window


Here is a simple test case based on the code above.

I changed the name of the BLOCK table because I drop it.
I also removed the tablespace and storage parameters for testing21.

If the code below works for you, the issue almost has to be with the BLOCKNO column in the BLOCK table.



drop table myblock purge;
create table myblock(blockno varchar2(10));
insert into myblock values('AAA');
insert into myblock values('WD9');
insert into myblock values('ZZZ');
commit;


DROP TABLE TESTING21 PURGE;


CREATE TABLE TESTING21
(
  BLOCK_NO       VARCHAR2(45 BYTE)
)
;

CREATE OR REPLACE procedure proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
    execute immediate 'truncate table TESTING21';

    insert into TESTING21 (BLOCK_NO)
    (select BLOCK_NO
		from
		(
			select 
       			BL.BLOCKNO BLOCK_NO
			FROM  myBLOCK BL
			
			WHERE   BL.BLOCKNO LIKE blockno_var
			and rownum<=10
			)a
	);
    commit;
end;
/

show errors


exec proc_testing_19('WD9');

select BLOCK_NO from TESTING21;

Open in new window

0
 
ernie_shahAuthor Commented:
This is the results:

BLOCK_NO                                          
--------------------------------------------------
WD9                                              


1 row selected.
Procedure created.
No errors.
PL/SQL procedure successfully completed.
no rows selected.
0
 
slightwv (䄆 Netminder) Commented:
One more:

select ':' || BL.BLOCKNO BLOCK_NO || ':'
FROM  LEASEOPS01.BLOCK BL
WHERE
AND   BL.BLOCKNO LIKE 'WD9'
and rownum<=10
/
0
 
slightwv (䄆 Netminder) Commented:
And the latest run you posted that shows no rows, was that using the example with myblock?
0
 
ernie_shahAuthor Commented:
ok here is what i found, using your example with the myblock:  This is the code:

drop table myblock purge;
create table myblock(blockno varchar2(10));
insert into myblock values('AAA');
insert into myblock values('WD9');
insert into myblock values('ZZZ');
commit;


CREATE OR REPLACE procedure proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
   execute immediate 'truncate table myblock';

    insert into myblock (BLOCKNO)
    (select BLOCKNO
        from
        (
            select 
                   BL.BLOCKNO BLOCKNO
            FROM  myBLOCK BL
            
            WHERE   BL.BLOCKNO LIKE 'WD9'
            and rownum<=10
            )a
    );
    commit;
end;
/

show errors


exec proc_testing_19('WD9');

select BLOCKNO from myblock;

Open in new window


 1.  When I comment this line:  execute immediate 'truncate table myblock'; The code works as is and returns all the data (does not recognize the parameters)   so I get this data:

Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
Commit complete.
Procedure created.
No errors.
PL/SQL procedure successfully completed.

BLOCKNO  
----------
AAA      
WD9      
ZZZ      


3 rows selected.

2.  If I don't comment: execute immediate 'truncate table myblock'; I get no data

3.  It is returning all the data, it is not recognizing the parameter
0
 
slightwv (䄆 Netminder) Commented:
>>.  If I don't comment: execute immediate 'truncate table myblock'; I get no data

That is understandable...

Where in my code do I have:
execute immediate 'truncate table myblock';
or
    insert into myblock (BLOCKNO)
    (select BLOCKNO
...

Why did you modify the code I posted?

Since I don't have your table BLOCK, I created the table myblock to simulate the BLOCK table used in your query.

If you truncate it, there is no data in it to be selected and inserted into the TESTING21 table.
0
 
ernie_shahAuthor Commented:
When I left the original code I got no data.  Your code is:
drop table myblock purge;
create table myblock(blockno varchar2(10));
insert into myblock values('AAA');
insert into myblock values('WD9');
insert into myblock values('ZZZ');
commit;


DROP TABLE TESTING21 PURGE;


CREATE TABLE TESTING21
(
  BLOCK_NO       VARCHAR2(45 BYTE)
)
;

CREATE OR REPLACE procedure proc_testing_19

 (blockno_var in VARCHAR2)
 
 is

begin
    execute immediate 'truncate table TESTING21';

    insert into TESTING21 (BLOCK_NO)
    (select BLOCK_NO
		from
		(
			select 
       			BL.BLOCKNO BLOCK_NO
			FROM  myBLOCK BL
			
			WHERE   BL.BLOCKNO LIKE blockno_var
			and rownum<=10
			)a
	);
    commit;
end;
/

show errors


exec proc_testing_19('WD9');

select BLOCK_NO from TESTING21;
                                          

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I never saw the results from the select I asked for above:
select ':' || BL.BLOCKNO BLOCK_NO || ':'
 FROM  LEASEOPS01.BLOCK BL
 WHERE
 AND   BL.BLOCKNO LIKE 'WD9'
 and rownum<=10
 /


Do you have sqlplus?
If so, issue the following command and run the test:
set echo on


Does toad allow you to set echo on?

If so, provide a complete test run that also actually shows the commends being executed.

Here is the run from my system:
SQL> drop table myblock purge;

Table dropped.

SQL> create table myblock(blockno varchar2(10));

Table created.

SQL> insert into myblock values('AAA');

1 row created.

SQL> insert into myblock values('WD9');

1 row created.

SQL> insert into myblock values('ZZZ');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> DROP TABLE TESTING21 PURGE;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE TESTING21
  2  (
  3    BLOCK_NO  VARCHAR2(45 BYTE)
  4  )
  5  ;

Table created.

SQL>
SQL> CREATE OR REPLACE procedure proc_testing_19
  2
  3   (blockno_var in VARCHAR2)
  4
  5   is
  6
  7  begin
  8      execute immediate 'truncate table TESTING21';
  9
 10      insert into TESTING21 (BLOCK_NO)
 11      (select BLOCK_NO
 12                  from
 13                  (
 14                          select
 15                          BL.BLOCKNO BLOCK_NO
 16                          FROM  myBLOCK BL
 17
 18                          WHERE   BL.BLOCKNO LIKE blockno_var
 19                          and rownum<=10
 20                          )a
 21          );
 22      commit;
 23  end;
 24  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> exec proc_testing_19('WD9');

PL/SQL procedure successfully completed.

SQL>
SQL> select BLOCK_NO from TESTING21;
WD9

SQL>
SQL>




                                         

Open in new window

0
 
ernie_shahAuthor Commented:
Hi slightwv

I figured out the problem....instead of trying to call the procedure in the same statement (on the Toad editor) I called it in the Set Parameters in toad (Schema Browser) and it worked so your original code was what did the trick...thank you so very much for your patience.  YOU ARE TRULY AN EXPERT.
0
 
ernie_shahAuthor Commented:
Thank you for your patience and prompt response.  I am really happy and satisfied.  Again. Thanks a million.
0
 
slightwv (䄆 Netminder) Commented:
No problem.  Glad you finally figured out the problem.  I was running out of ideas!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 26
  • 24
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now