How to change PL-SQL script in Qlik sense

[pawwy1415]

Hi,

how to change attached PL-SQL scripts in Qlikview script.. There're lot of sub-queries used.

[migueldelval]

Hi P Kumar,

I have charged your script in my notbook with Qlikview language, and it´s ok.

Put first the connection to your BBDD, and the name of the table. (EG: TABLE:)

Try it and tell me.

Regards

Miguel del Valle

[pawwy1415]

Hi Miguel,

there is no attachment.. Please can you attach file.. Also, I have attached one more sql code if you can convert please.

[migueldelval]

Hi P Kumar,

I haven´t transformed anything, I´ve only seen your code in notepad ++ with qlikview language and it´s ok.

You can use the sql statement without changes in qlikview.

MyTable:

Select * from Sometable where a=1;

Qlikview just sends the sql statement to the database that you connect to. The database executes the statement and returns the results. You do first need to define a connection of course.

Try it and your reload show some errors; send me one picture with them to advance.

Regards

Miguel del Valle

[pawwy1415]

Hi Miguel,

It's doesn't work directly.. I am using Qlik sense..

I want to store this query 1st into QVD and then want to use..

Can you make necessary changes which need to make please?? Do we need to use LOAD statement and remove SELECT..

[pawwy1415]

check error:

Output

05:15

Output cleared.

05:15

---

Started loading data

---

Connecting to MIS Dev Connec (main_l0482421)

Connected

---

The following error occurred:

ErrorSource: OraOLEDB, ErrorMsg: ORA-00920: invalid relational operator

---

The error occurred here:

v_GasQ1:

SELECT

S1.NOOF_MTR,

DECODE(NVL(S0.NOOF_VAT,0),0,'N','Y') NOOF_VAT,

DECODE(NVL(S9.NOOF_CCL,0),0,'N','Y') NOOF_CCL,

S3.STARTDATE,

S1.CUSTOMER_TYPE_CODE,

S2.CONSUMERNAME PAYING_CUST_NAME,

S2.CO_EMAIL,

S1.UCBCNTR_EXTERNAL_REF,

TO_CHAR(S1.ACCT_CODE) ACCT_CODE,

S2.SPLYPTID,

S2.SITEADDR,

S2.ACBILLADDR,

S2.MPR,

rtrim(decode(trim(S2.CONS_CONTACT), NULL, '', trim(S2.CONS_CONTACT)||', ')||

decode(trim(S2.CONS_PHONE), NULL, '', trim(S2.CONS_PHONE)||', ')||

decode(trim(S2.CO_ACCT_NAME), NULL, '', trim(S2.CO_ACCT_NAME)||', ')||

decode(trim(S2.CO_ACCT_PHONE), NULL, '', trim(S2.CO_ACCT_PHONE)),', ') CONS_CONTACT,

rtrim(decode(trim(S2.SITE_PHONE), NULL, '', trim(S2.SITE_PHONE)||', ')||

decode(trim(S2.SITE_CONTACT), NULL, '', trim(S2.SITE_CONTACT)||', ')||

decode(trim(S2.SITE_ACCT_PHONE), NULL, '', trim(S2.SITE_ACCT_PHONE)),', ') SITE_CONTACT,

S1.GAS_ELEC_IND,

S1.ACCOUNT_STATUS,

S1.BILL_CYCLE_CODE,

S1.PAYMENT_METHOD,

S2.CURRENT_PRICE_PKWH,

S3.AGGREGATEAQKWH,

(S1.TOTAL_CHARGES - S1.CHARGE_ADJUSTED) NET_CHARGES,

S1.ACCOUNT_BALANCE,

S1.QUERY_IND,

S11.UNBILLED_AMOUNT "Unbilled Value",

S11.WORKLIST "Worklist"

FROM (SELECT UCRCCLH_PREM_CODE,

SUM(UCRCCLH_EXEMPTION_PERCENT) NOOF_CCL

FROM ICBAN.UCRCCLH

GROUP BY

UCRCCLH_PREM_CODE) S9,

(SELECT UCRDSUH_PREM_CODE,

SUM(UCRDSUH_DOM_SPLIT_USAGE_PERC) NOOF_VAT

FROM ICBAN.UCRDSUH

GROUP BY

UCRDSUH_PREM_CODE) S0,

(SELECT UC.UCBCNTR_EXTERNAL_REF,

US.UCBPREM_EXTERNAL_PREM_REF,

UM.UCRSERV_PREM_CODE,

US.UCBPREM_CODE,

IA.CUSTOMER_TYPE_CODE,

IA.PAYMENT_METHOD,

IA.PAYING_CUST_NAME,

IA.ACCT_CODE,

IA.ACCOUNT_STATUS,

IA.BILL_CYCLE_CODE,

IA.CURRENT_PRICE_PKWH,

IA.GAS_ELEC_IND,

IA.ACCOUNT_BALANCE,

IA.QUERY_IND,

COUNT(UM.UCRSERV_PREM_CODE) NOOF_MTR,

SUM(IB.TOTAL_CHARGES) TOTAL_CHARGES,

SUM(IB.CHARGE_ADJUSTED) CHARGE_ADJUSTED

FROM ICBAN.UCBCNTR UC,

ICBAN.UCBPREM US,

MIS.IC_ACCOUNTS IA,

ICBAN.UCRSERV UM,

ICBAN.UCRACCT UA,

MIS.IC_BILLING IB

WHERE UC.UCBCNTR_CUST_CODE = UA.UCRACCT_CUST_CODE

AND US.UCBPREM_CODE = UA.UCRACCT_PREM_CODE

AND UA.UCRACCT_ACCT_CODE = IA.ACCT_CODE

AND UA.UCRACCT_ACCT_CODE = IB.ACCT_CODE(+)

AND IA.ACCT_CODE > 0

AND US.UCBPREM_CODE = UM.UCRSERV_PREM_CODE

AND UM.UCRSERV_DATE_INSTALLED

GROUP BY

UC.UCBCNTR_EXTERNAL_REF,

US.UCBPREM_EXTERNAL_PREM_REF,

UM.UCRSERV_PREM_CODE,

US.UCBPREM_CODE,

IA.CUSTOMER_TYPE_CODE,

IA.PAYMENT_METHOD,

IA.PAYING_CUST_NAME,

IA.ACCT_CODE,

IA.ACCOUNT_STATUS,

IA.BILL_CYCLE_CODE,

IA.CURRENT_PRICE_PKWH,

IA.GAS_ELEC_IND,

IA.ACCOUNT_BALANCE,

IA.QUERY_IND) S1,

(SELECT LK.STARTDATE,

GP.ACTUALPRICEPKWH CURRENT_PRICE_PKWH,

LTRIM(RTRIM(CO.CONSUMERNAME)) CONSUMERNAME,

CN.CONTRACTREFNO,

SP.SPLYPTID,

MTR.TCMETERPTREF MPR,

CO.CONTACTNAME CONS_CONTACT,

CO.PHONENO CONS_PHONE,

CO.ACCOUNTSCONTACTNAME CO_ACCT_NAME,

CO.ACCOUNTSCONTACTPHONE CO_ACCT_PHONE,

CO.EMAIL CO_EMAIL,

ST.PHONENO SITE_PHONE,

ST.CONTACTNAME SITE_CONTACT,

ST.CONTACTPHONE SITE_ACCT_PHONE,

ST.SITEID,

ST.SITENAME||', '||ST.SITEADDRLINE1||', '||ST.SITEADDRLINE2||', '||ST.SITEADDRLINE3||', '||ST.SITEADDRLINE4||', '||ST.SITEPOSTCODE SITEADDR,

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE1,CO.ACCOUNTSADDRLINE1)),ST.CONTACTADDR1) ||', '||

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE2,CO.ACCOUNTSADDRLINE2)),ST.CONTACTADDR2) ||', '||

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE3,CO.ACCOUNTSADDRLINE3)),ST.CONTACTADDR3) ||', '||

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE4,CO.ACCOUNTSADDRLINE4)),ST.CONTACTADDR4) ||', '||

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEPOSTCODE,CO.ACCOUNTSPOSTCODE)),ST.CONTACTPOSTCODE) ACBILLADDR

FROM ICSPA.SPCNT CN,

ICSPA.SPCNTSP LK,

ICSPA.SP SP,

ICSPA.MTR MTR,

ICSPA.SITE ST,

ICSPA.CONSUMER CO,

ICSPA.SPCNTGRP GR,

ICSPA.SPCNTGRPPERIOD GP

WHERE SP.SPLYPTID = MTR.SPLYPTID AND

SP.SPLYPTID = LK.SPLYPTID

AND LK.SPLYCNTRCTID = CN.SPLYCNTRCTID

AND SP.SITEID = ST.SITEID

AND ST.CONSUMERID = CO.CONSUMERID

AND CO.CONSUMERID = CN.CONSUMERID

AND LK.SPLYCNTRCTGRPID = GR.SPCNTGRPID

AND GR.SPCNTGRPID = GP.SPCNTGRPID

AND LK.STARTDATE BETWEEN GP.STARTDATE AND GP.ENDDATE) S2,

(SELECT T.SPLYPTID,

T.STDATE STARTDATE,

T.AGGREGATEAQKWH

FROM ICSPA.TSCNTSP T,

(SELECT SPLYPTID,

MIN(STDATE) STDATE

FROM ICSPA.TSCNTSP

GROUP BY

SPLYPTID) SS1,

(SELECT SPLYPTID,

MAX(STDATE) STDATE

FROM ICSPA.TSCNTSP

WHERE STDATE

GROUP BY

SPLYPTID) SS2

WHERE SS1.SPLYPTID = SS2.SPLYPTID

AND T.SPLYPTID = SS1.SPLYPTID

AND SS1.STDATE = SS2.STDATE

AND T.STDATE = SS1.STDATE) S3,

(SELECT IC_ACCOUNTS.ACCT_CODE,

IC_ACCOUNTS.ACCOUNT_BALANCE,

IC_ACCOUNTS.WORKLIST,

SUM(MIS_UNBILLED_GAS.UNBILLED_GAS_CHARGE+MIS_UNBILLED_GAS.UNBILLED_CCL_CHARGE+MIS_UNBILLED_GAS.UNBILLED_VAT_CHARGE) UNBILLED_AMOUNT

FROM MIS_UNBILLED_GAS,

IC_ACCOUNTS,

MIS_PERMISSIBLE_READ_DATE

WHERE IC_ACCOUNTS.ACCT_CODE=MIS_UNBILLED_GAS.ACCOUNT_CODE(+)

AND ( DECODE(MONTHS_BETWEEN( TRUNC(SYSDATE,'MON'),TRUNC(SYSDATE,'Q'))

||substr(MIS_UNBILLED_GAS.CYCLE_CODE,1,1)||substr(MIS_UNBILLED_GAS.CYCLE_CODE,4,1)

,'0Q1',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')

,'0Q2',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')

,'0Q3',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')

,'1Q1',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')

,'1Q2',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')

,'1Q3',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')

,'2Q1',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')

,'2Q2',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')

,'2Q3',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')

,TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')) = MIS_PERMISSIBLE_READ_DATE.MONTH

)

AND (last_day(

DECODE(MONTHS_BETWEEN(TRUNC(SYSDATE,'MON'),TRUNC(SYSDATE,'Q'))

||substr(MIS_UNBILLED_GAS.CYCLE_CODE,1,1)||substr(MIS_UNBILLED_GAS.CYCLE_CODE,4,1)

,'0Q1',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')

,'0Q2',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')

,'0Q3',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')

,'1Q1',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')

,'1Q2',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')

,'1Q3',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')

,'2Q1',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')

,'2Q2',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')

,'2Q3',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')

,TRUNC(ADD_MONTHS(SYSDATE,-1),'MON'))

) - MIS_UNBILLED_GAS.CONS_MONTH) >= 0

AND ( MIS_UNBILLED_GAS.CONS_MONTH <= (TRUNC(SYSDATE,'MON') - 1) )

AND ( trunc(MIS_UNBILLED_GAS.UNBILLED_START_DATE) <

(SELECT TRUNC(MIS_PERMISSIBLE_READ_DATE.LAST_READ_DATE)

FROM MIS_PERMISSIBLE_READ_DATE WHERE TRUNC(MIS_PERMISSIBLE_READ_DATE.MONTH) = TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')

)

)

AND ( nvl(TRUNC(MIS_PERMISSIBLE_READ_DATE.LAST_READ_DATE),sysdate) -trunc(MIS_UNBILLED_GAS.UNBILLED_START_DATE) >= 0 )

GROUP BY

IC_ACCOUNTS.ACCT_CODE,

IC_ACCOUNTS.ACCOUNT_BALANCE,

IC_ACCOUNTS.WORKLIST) s11

WHERE S2.CONTRACTREFNO = S1.UCBCNTR_EXTERNAL_REF

AND S1.UCBPREM_EXTERNAL_PREM_REF = 'SPA'||S2.SITEID

AND S3.SPLYPTID = S2.SPLYPTID

AND S2.STARTDATE = S3.STARTDATE

AND LOWER(S2.CONSUMERNAME)

AND S0.UCRDSUH_PREM_CODE(+)=S1.UCRSERV_PREM_CODE

AND S1.UCRSERV_PREM_CODE=S9.UCRCCLH_PREM_CODE(+)

AND S1.ACCT_CODE = S11.ACCT_CODE(+)

UNION

SELECT 0,

TO_CHAR(null),

TO_CHAR(null),

S5.STARTDATE,

S4.CUSTOMER_TYPE_CODE,

S4.CONSUMERNAME PAYING_CUST_NAME,

S4.CO_EMAIL,

S4.CONTRACTREFNO,

'',

S4.SPLYPTID,

S4.SITEADDR,

S4.ACBILLADDR,

S4.MPR,

rtrim(decode(trim(S4.CONS_CONTACT), NULL, '', trim(S4.CONS_CONTACT)||', ')||

decode(trim(S4.CONS_PHONE), NULL, '', trim(S4.CONS_PHONE)||', ')||

decode(trim(S4.CO_ACCT_NAME), NULL, '', trim(S4.CO_ACCT_NAME)||', ')||

decode(trim(S4.CO_ACCT_PHONE), NULL, '', trim(S4.CO_ACCT_PHONE)),', ') CONS_CONTACT,

rtrim(decode(trim(S4.SITE_PHONE), NULL, '', trim(S4.SITE_PHONE)||', ')||

decode(trim(S4.SITE_CONTACT), NULL, '', trim(S4.SITE_CONTACT)||', ')||

decode(trim(S4.SITE_ACCT_PHONE), NULL, '', trim(S4.SITE_ACCT_PHONE)),', ') SITE_CONTACT,

'',

'',

'',

'',

S4.CURRENT_PRICE_PKWH,

S5.AGGREGATEAQKWH,

0,

0,

'',

0 "Unbilled Value",

' ' "Worklist"

FROM (SELECT LK.STARTDATE,

LTRIM(RTRIM(CO.CONSUMERNAME)) CONSUMERNAME,

DECODE ( UPPER(CO.CONSUMERTYPE),

'VOLUME', 'VOLM',

'COMG', 'COMG',

'COMS', 'COMS',

'HSCG', 'HSCG',

'HSSS', 'HSSS',

'SPECIALHAN', 'SPEC',

'CPL','CPL',

'SPECHANCNT','SPCO',

'SCF','SCF',

'SEB','SEE',

'SCF VOLM','SCFV',

'SEB VOLM','SEEV',

'PEN VOLM','PENV',

'PENV', 'PENV',

'PUBG', 'PUBG',

'PUBS', 'PUBS',

'NWST','NWST',

'MGL VOLM','MGLV',

'MGL KEY AC','MKEY',

'VOLM') CUSTOMER_TYPE_CODE,

GP.ACTUALPRICEPKWH CURRENT_PRICE_PKWH,

CN.CONTRACTREFNO,

SP.SPLYPTID,

MTR.TCMETERPTREF MPR,

CO.CONTACTNAME CONS_CONTACT,

CO.PHONENO CONS_PHONE,

CO.ACCOUNTSCONTACTNAME CO_ACCT_NAME,

CO.ACCOUNTSCONTACTPHONE CO_ACCT_PHONE,

CO.EMAIL CO_EMAIL,

ST.PHONENO SITE_PHONE,

ST.CONTACTNAME SITE_CONTACT,

ST.CONTACTPHONE SITE_ACCT_PHONE,

ST.SITEID,

ST.SITENAME||', '||ST.SITEADDRLINE1||', '||ST.SITEADDRLINE2||', '||ST.SITEADDRLINE3||', '||ST.SITEADDRLINE4||', '||ST.SITEPOSTCODE SITEADDR,

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE1,CO.ACCOUNTSADDRLINE1)),ST.CONTACTADDR1) ||', '||

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE2,CO.ACCOUNTSADDRLINE2)),ST.CONTACTADDR2) ||', '||

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE3,CO.ACCOUNTSADDRLINE3)),ST.CONTACTADDR3) ||', '||

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE4,CO.ACCOUNTSADDRLINE4)),ST.CONTACTADDR4) ||', '||

DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEPOSTCODE,CO.ACCOUNTSPOSTCODE)),ST.CONTACTPOSTCODE) ACBILLADDR

FROM ICSPA.SPCNT CN,

ICSPA.SPCNTSP LK,

ICSPA.SP SP,

ICSPA.MTR MTR,

ICSPA.SITE ST,

ICSPA.CONSUMER CO,

ICSPA.SPCNTGRP GR,

ICSPA.SPCNTGRPPERIOD GP

WHERE SP.SPLYPTID = MTR.SPLYPTID AND

SP.SPLYPTID = LK.SPLYPTID

AND LK.SPLYCNTRCTID = CN.SPLYCNTRCTID

AND SP.SITEID = ST.SITEID

AND ST.CONSUMERID = CO.CONSUMERID

AND CO.CONSUMERID = CN.CONSUMERID

AND CO.CONSUMERID <> '0000046601'

AND LK.SPLYCNTRCTGRPID = GR.SPCNTGRPID

AND GR.SPCNTGRPID = GP.SPCNTGRPID

AND LK.STARTDATE BETWEEN GP.STARTDATE AND GP.ENDDATE) S4,

(SELECT T.SPLYPTID,

T.STDATE STARTDATE,

T.AGGREGATEAQKWH

FROM ICSPA.TSCNTSP T,

(SELECT SPLYPTID,

MIN(STDATE) STDATE

FROM ICSPA.TSCNTSP

GROUP BY

SPLYPTID) SS1,

(SELECT SPLYPTID,

MAX(STDATE) STDATE

FROM ICSPA.TSCNTSP

WHERE STDATE

GROUP BY

SPLYPTID) SS2

WHERE SS1.SPLYPTID = SS2.SPLYPTID

AND T.SPLYPTID = SS1.SPLYPTID

AND SS1.STDATE = SS2.STDATE

AND T.STDATE = SS1.STDATE) S5

WHERE S5.SPLYPTID = S4.SPLYPTID

AND S4.STARTDATE = S5.STARTDATE

AND LOWER(S4.CONSUMERNAME)

AND NOT EXISTS

(SELECT 1

FROM ICBAN.UCBPREM

WHERE 'SPA'||S4.SITEID = UCBPREM_EXTERNAL_PREM_REF)

---

Data has not been loaded. Please correct the error and try loading again.

05:16

Connecting to MIS Dev Connec (main_l0482421)

Connected

ErrorSource: OraOLEDB, ErrorMsg: ORA-00920: invalid relational operator: v_GasQ1: SELECT S1.NOOF_MTR, DECODE(NVL(S0.NOOF_VAT,0),0,'N','Y') NOOF_VAT, DECODE(NVL(S9.NOOF_CCL,0),0,'N','Y') NOOF_CCL, S3.STARTDATE, S1.CUSTOMER_TYPE_CODE, S2.CONSUMERNAME PAYING_CUST_NAME, S2.CO_EMAIL, S1.UCBCNTR_EXTERNAL_REF, TO_CHAR(S1.ACCT_CODE) ACCT_CODE, S2.SPLYPTID, S2.SITEADDR, S2.ACBILLADDR, S2.MPR, rtrim(decode(trim(S2.CONS_CONTACT), NULL, '', trim(S2.CONS_CONTACT)||', ')|| decode(trim(S2.CONS_PHONE), NULL, '', trim(S2.CONS_PHONE)||', ')|| decode(trim(S2.CO_ACCT_NAME), NULL, '', trim(S2.CO_ACCT_NAME)||', ')|| decode(trim(S2.CO_ACCT_PHONE), NULL, '', trim(S2.CO_ACCT_PHONE)),', ') CONS_CONTACT, rtrim(decode(trim(S2.SITE_PHONE), NULL, '', trim(S2.SITE_PHONE)||', ')|| decode(trim(S2.SITE_CONTACT), NULL, '', trim(S2.SITE_CONTACT)||', ')|| decode(trim(S2.SITE_ACCT_PHONE), NULL, '', trim(S2.SITE_ACCT_PHONE)),', ') SITE_CONTACT, S1.GAS_ELEC_IND, S1.ACCOUNT_STATUS, S1.BILL_CYCLE_CODE, S1.PAYMENT_METHOD, S2.CURRENT_PRICE_PKWH, S3.AGGREGATEAQKWH, (S1.TOTAL_CHARGES - S1.CHARGE_ADJUSTED) NET_CHARGES, S1.ACCOUNT_BALANCE, S1.QUERY_IND, S11.UNBILLED_AMOUNT "Unbilled Value", S11.WORKLIST "Worklist" FROM (SELECT UCRCCLH_PREM_CODE, SUM(UCRCCLH_EXEMPTION_PERCENT) NOOF_CCL FROM ICBAN.UCRCCLH GROUP BY UCRCCLH_PREM_CODE) S9, (SELECT UCRDSUH_PREM_CODE, SUM(UCRDSUH_DOM_SPLIT_USAGE_PERC) NOOF_VAT FROM ICBAN.UCRDSUH GROUP BY UCRDSUH_PREM_CODE) S0, (SELECT UC.UCBCNTR_EXTERNAL_REF, US.UCBPREM_EXTERNAL_PREM_REF, UM.UCRSERV_PREM_CODE, US.UCBPREM_CODE, IA.CUSTOMER_TYPE_CODE, IA.PAYMENT_METHOD, IA.PAYING_CUST_NAME, IA.ACCT_CODE, IA.ACCOUNT_STATUS, IA.BILL_CYCLE_CODE, IA.CURRENT_PRICE_PKWH, IA.GAS_ELEC_IND, IA.ACCOUNT_BALANCE, IA.QUERY_IND, COUNT(UM.UCRSERV_PREM_CODE) NOOF_MTR, SUM(IB.TOTAL_CHARGES) TOTAL_CHARGES, SUM(IB.CHARGE_ADJUSTED) CHARGE_ADJUSTED FROM ICBAN.UCBCNTR UC, ICBAN.UCBPREM US, MIS.IC_ACCOUNTS IA, ICBAN.UCRSERV UM, ICBAN.UCRACCT UA, MIS.IC_BILLING IB WHERE UC.UCBCNTR_CUST_CODE = UA.UCRACCT_CUST_CODE AND US.UCBPREM_CODE = UA.UCRACCT_PREM_CODE AND UA.UCRACCT_ACCT_CODE = IA.ACCT_CODE AND UA.UCRACCT_ACCT_CODE = IB.ACCT_CODE(+) AND IA.ACCT_CODE > 0 AND US.UCBPREM_CODE = UM.UCRSERV_PREM_CODE AND UM.UCRSERV_DATE_INSTALLED GROUP BY UC.UCBCNTR_EXTERNAL_REF, US.UCBPREM_EXTERNAL_PREM_REF, UM.UCRSERV_PREM_CODE, US.UCBPREM_CODE, IA.CUSTOMER_TYPE_CODE, IA.PAYMENT_METHOD, IA.PAYING_CUST_NAME, IA.ACCT_CODE, IA.ACCOUNT_STATUS, IA.BILL_CYCLE_CODE, IA.CURRENT_PRICE_PKWH, IA.GAS_ELEC_IND, IA.ACCOUNT_BALANCE, IA.QUERY_IND) S1, (SELECT LK.STARTDATE, GP.ACTUALPRICEPKWH CURRENT_PRICE_PKWH, LTRIM(RTRIM(CO.CONSUMERNAME)) CONSUMERNAME, CN.CONTRACTREFNO, SP.SPLYPTID, MTR.TCMETERPTREF MPR, CO.CONTACTNAME CONS_CONTACT, CO.PHONENO CONS_PHONE, CO.ACCOUNTSCONTACTNAME CO_ACCT_NAME, CO.ACCOUNTSCONTACTPHONE CO_ACCT_PHONE, CO.EMAIL CO_EMAIL, ST.PHONENO SITE_PHONE, ST.CONTACTNAME SITE_CONTACT, ST.CONTACTPHONE SITE_ACCT_PHONE, ST.SITEID, ST.SITENAME||', '||ST.SITEADDRLINE1||', '||ST.SITEADDRLINE2||', '||ST.SITEADDRLINE3||', '||ST.SITEADDRLINE4||', '||ST.SITEPOSTCODE SITEADDR, DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE1,CO.ACCOUNTSADDRLINE1)),ST.CONTACTADDR1) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE2,CO.ACCOUNTSADDRLINE2)),ST.CONTACTADDR2) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE3,CO.ACCOUNTSADDRLINE3)),ST.CONTACTADDR3) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE4,CO.ACCOUNTSADDRLINE4)),ST.CONTACTADDR4) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEPOSTCODE,CO.ACCOUNTSPOSTCODE)),ST.CONTACTPOSTCODE) ACBILLADDR FROM ICSPA.SPCNT CN, ICSPA.SPCNTSP LK, ICSPA.SP SP, ICSPA.MTR MTR, ICSPA.SITE ST, ICSPA.CONSUMER CO, ICSPA.SPCNTGRP GR, ICSPA.SPCNTGRPPERIOD GP WHERE SP.SPLYPTID = MTR.SPLYPTID AND SP.SPLYPTID = LK.SPLYPTID AND LK.SPLYCNTRCTID = CN.SPLYCNTRCTID AND SP.SITEID = ST.SITEID AND ST.CONSUMERID = CO.CONSUMERID AND CO.CONSUMERID = CN.CONSUMERID AND LK.SPLYCNTRCTGRPID = GR.SPCNTGRPID AND GR.SPCNTGRPID = GP.SPCNTGRPID AND LK.STARTDATE BETWEEN GP.STARTDATE AND GP.ENDDATE) S2, (SELECT T.SPLYPTID, T.STDATE STARTDATE, T.AGGREGATEAQKWH FROM ICSPA.TSCNTSP T, (SELECT SPLYPTID, MIN(STDATE) STDATE FROM ICSPA.TSCNTSP GROUP BY SPLYPTID) SS1, (SELECT SPLYPTID, MAX(STDATE) STDATE FROM ICSPA.TSCNTSP WHERE STDATE GROUP BY SPLYPTID) SS2 WHERE SS1.SPLYPTID = SS2.SPLYPTID AND T.SPLYPTID = SS1.SPLYPTID AND SS1.STDATE = SS2.STDATE AND T.STDATE = SS1.STDATE) S3, (SELECT IC_ACCOUNTS.ACCT_CODE, IC_ACCOUNTS.ACCOUNT_BALANCE, IC_ACCOUNTS.WORKLIST, SUM(MIS_UNBILLED_GAS.UNBILLED_GAS_CHARGE+MIS_UNBILLED_GAS.UNBILLED_CCL_CHARGE+MIS_UNBILLED_GAS.UNBILLED_VAT_CHARGE) UNBILLED_AMOUNT FROM MIS_UNBILLED_GAS, IC_ACCOUNTS, MIS_PERMISSIBLE_READ_DATE WHERE IC_ACCOUNTS.ACCT_CODE=MIS_UNBILLED_GAS.ACCOUNT_CODE(+) AND ( DECODE(MONTHS_BETWEEN( TRUNC(SYSDATE,'MON'),TRUNC(SYSDATE,'Q')) ||substr(MIS_UNBILLED_GAS.CYCLE_CODE,1,1)||substr(MIS_UNBILLED_GAS.CYCLE_CODE,4,1) ,'0Q1',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,'0Q2',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'0Q3',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'1Q1',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'1Q2',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,'1Q3',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'2Q1',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'2Q2',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'2Q3',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')) = MIS_PERMISSIBLE_READ_DATE.MONTH ) AND (last_day( DECODE(MONTHS_BETWEEN(TRUNC(SYSDATE,'MON'),TRUNC(SYSDATE,'Q')) ||substr(MIS_UNBILLED_GAS.CYCLE_CODE,1,1)||substr(MIS_UNBILLED_GAS.CYCLE_CODE,4,1) ,'0Q1',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,'0Q2',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'0Q3',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'1Q1',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'1Q2',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,'1Q3',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'2Q1',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'2Q2',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'2Q3',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')) ) - MIS_UNBILLED_GAS.CONS_MONTH) >= 0 AND ( MIS_UNBILLED_GAS.CONS_MONTH <= (TRUNC(SYSDATE,'MON') - 1) ) AND ( trunc(MIS_UNBILLED_GAS.UNBILLED_START_DATE) < (SELECT TRUNC(MIS_PERMISSIBLE_READ_DATE.LAST_READ_DATE) FROM MIS_PERMISSIBLE_READ_DATE WHERE TRUNC(MIS_PERMISSIBLE_READ_DATE.MONTH) = TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ) ) AND ( nvl(TRUNC(MIS_PERMISSIBLE_READ_DATE.LAST_READ_DATE),sysdate) -trunc(MIS_UNBILLED_GAS.UNBILLED_START_DATE) >= 0 ) GROUP BY IC_ACCOUNTS.ACCT_CODE, IC_ACCOUNTS.ACCOUNT_BALANCE, IC_ACCOUNTS.WORKLIST) s11 WHERE S2.CONTRACTREFNO = S1.UCBCNTR_EXTERNAL_REF AND S1.UCBPREM_EXTERNAL_PREM_REF = 'SPA'||S2.SITEID AND S3.SPLYPTID = S2.SPLYPTID AND S2.STARTDATE = S3.STARTDATE AND LOWER(S2.CONSUMERNAME) AND S0.UCRDSUH_PREM_CODE(+)=S1.UCRSERV_PREM_CODE AND S1.UCRSERV_PREM_CODE=S9.UCRCCLH_PREM_CODE(+) AND S1.ACCT_CODE = S11.ACCT_CODE(+) UNION SELECT 0, TO_CHAR(null), TO_CHAR(null), S5.STARTDATE, S4.CUSTOMER_TYPE_CODE, S4.CONSUMERNAME PAYING_CUST_NAME, S4.CO_EMAIL, S4.CONTRACTREFNO, '', S4.SPLYPTID, S4.SITEADDR, S4.ACBILLADDR, S4.MPR, rtrim(decode(trim(S4.CONS_CONTACT), NULL, '', trim(S4.CONS_CONTACT)||', ')|| decode(trim(S4.CONS_PHONE), NULL, '', trim(S4.CONS_PHONE)||', ')|| decode(trim(S4.CO_ACCT_NAME), NULL, '', trim(S4.CO_ACCT_NAME)||', ')|| decode(trim(S4.CO_ACCT_PHONE), NULL, '', trim(S4.CO_ACCT_PHONE)),', ') CONS_CONTACT, rtrim(decode(trim(S4.SITE_PHONE), NULL, '', trim(S4.SITE_PHONE)||', ')|| decode(trim(S4.SITE_CONTACT), NULL, '', trim(S4.SITE_CONTACT)||', ')|| decode(trim(S4.SITE_ACCT_PHONE), NULL, '', trim(S4.SITE_ACCT_PHONE)),', ') SITE_CONTACT, '', '', '', '', S4.CURRENT_PRICE_PKWH, S5.AGGREGATEAQKWH, 0, 0, '', 0 "Unbilled Value", ' ' "Worklist" FROM (SELECT LK.STARTDATE, LTRIM(RTRIM(CO.CONSUMERNAME)) CONSUMERNAME, DECODE ( UPPER(CO.CONSUMERTYPE), 'VOLUME', 'VOLM', 'COMG', 'COMG', 'COMS', 'COMS', 'HSCG', 'HSCG', 'HSSS', 'HSSS', 'SPECIALHAN', 'SPEC', 'CPL','CPL', 'SPECHANCNT','SPCO', 'SCF','SCF', 'SEB','SEE', 'SCF VOLM','SCFV', 'SEB VOLM','SEEV', 'PEN VOLM','PENV', 'PENV', 'PENV', 'PUBG', 'PUBG', 'PUBS', 'PUBS', 'NWST','NWST', 'MGL VOLM','MGLV', 'MGL KEY AC','MKEY', 'VOLM') CUSTOMER_TYPE_CODE, GP.ACTUALPRICEPKWH CURRENT_PRICE_PKWH, CN.CONTRACTREFNO, SP.SPLYPTID, MTR.TCMETERPTREF MPR, CO.CONTACTNAME CONS_CONTACT, CO.PHONENO CONS_PHONE, CO.ACCOUNTSCONTACTNAME CO_ACCT_NAME, CO.ACCOUNTSCONTACTPHONE CO_ACCT_PHONE, CO.EMAIL CO_EMAIL, ST.PHONENO SITE_PHONE, ST.CONTACTNAME SITE_CONTACT, ST.CONTACTPHONE SITE_ACCT_PHONE, ST.SITEID, ST.SITENAME||', '||ST.SITEADDRLINE1||', '||ST.SITEADDRLINE2||', '||ST.SITEADDRLINE3||', '||ST.SITEADDRLINE4||', '||ST.SITEPOSTCODE SITEADDR, DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE1,CO.ACCOUNTSADDRLINE1)),ST.CONTACTADDR1) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE2,CO.ACCOUNTSADDRLINE2)),ST.CONTACTADDR2) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE3,CO.ACCOUNTSADDRLINE3)),ST.CONTACTADDR3) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE4,CO.ACCOUNTSADDRLINE4)),ST.CONTACTADDR4) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEPOSTCODE,CO.ACCOUNTSPOSTCODE)),ST.CONTACTPOSTCODE) ACBILLADDR FROM ICSPA.SPCNT CN, ICSPA.SPCNTSP LK, ICSPA.SP SP, ICSPA.MTR MTR, ICSPA.SITE ST, ICSPA.CONSUMER CO, ICSPA.SPCNTGRP GR, ICSPA.SPCNTGRPPERIOD GP WHERE SP.SPLYPTID = MTR.SPLYPTID AND SP.SPLYPTID = LK.SPLYPTID AND LK.SPLYCNTRCTID = CN.SPLYCNTRCTID AND SP.SITEID = ST.SITEID AND ST.CONSUMERID = CO.CONSUMERID AND CO.CONSUMERID = CN.CONSUMERID AND CO.CONSUMERID <> '0000046601' AND LK.SPLYCNTRCTGRPID = GR.SPCNTGRPID AND GR.SPCNTGRPID = GP.SPCNTGRPID AND LK.STARTDATE BETWEEN GP.STARTDATE AND GP.ENDDATE) S4, (SELECT T.SPLYPTID, T.STDATE STARTDATE, T.AGGREGATEAQKWH FROM ICSPA.TSCNTSP T, (SELECT SPLYPTID, MIN(STDATE) STDATE FROM ICSPA.TSCNTSP GROUP BY SPLYPTID) SS1, (SELECT SPLYPTID, MAX(STDATE) STDATE FROM ICSPA.TSCNTSP WHERE STDATE GROUP BY SPLYPTID) SS2 WHERE SS1.SPLYPTID = SS2.SPLYPTID AND T.SPLYPTID = SS1.SPLYPTID AND SS1.STDATE = SS2.STDATE AND T.STDATE = SS1.STDATE) S5 WHERE S5.SPLYPTID = S4.SPLYPTID AND S4.STARTDATE = S5.STARTDATE AND LOWER(S4.CONSUMERNAME) AND NOT EXISTS (SELECT 1 FROM ICBAN.UCBPREM WHERE 'SPA'||S4.SITEID = UCBPREM_EXTERNAL_PREM_REF)