Home » RDBMS Server » Performance Tuning » Procedure is running from more than 3 hrs but do not return the results
| Procedure is running from more than 3 hrs but do not return the results [message #300378] |
Fri, 15 February 2008 04:37  |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
My user report that the procedure is running from more than 3 hrs but still did not return the results.
It was working fine earlier.
kindly have a look onto the procedure below.
CREATE OR REPLACE PROCEDURE Rp_Alert_Test_Sp
AS
/* Declaration of all the variables used in the Procedure */
V_BillStartDate DATE ;
V_BillStopDate DATE ;
V_BillCycleNo NUMBER(5);
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE Rp_Alert_TEST' ;
EXECUTE IMMEDIATE 'TRUNCATE TABLE RP_TEMP_ALERTSERVICE_TEST' ;
EXECUTE IMMEDIATE 'TRUNCATE TABLE RP_TEMP2_ALERT_TEST' ;
-- Inserting all the accounts whose status is active or temporary deactivation
/*
CREATE TABLE Rp_Alert_TEST
(
ACCOUNTNUMBER NUMBER(20),
CUSTOMERNAME VARCHAR2(152 BYTE),
IMSI VARCHAR2(500 BYTE),
MSISDN VARCHAR2(50 BYTE),
CELL_CITY_CODE VARCHAR2(50 BYTE),
PLANNAME VARCHAR2(50 BYTE),
STATUS VARCHAR2(10 BYTE),
INVOICEAMOUNT NUMBER(16,2),
PAYMENT NUMBER(20,2),
POSTBILLCREDITADJUSTMENT NUMBER(16,2),
POSTBILLDEBITADJUSTMENT NUMBER(16,2),
REVERSALPAYMENT NUMBER(20,2),
UNBILLED_USAGE NUMBER(20,2),
PAYBLEAMOUNT NUMBER(20,2),
CREDITLIMIT NUMBER(16,2),
SECURITYDEPOSIT NUMBER(16,2),
USAGEPERCENTAGE NUMBER(16,2),a
REPORTDATE DATE,
ADVANCEPAYMENT NUMBER(20,2),
PREBILLCREDITADJUSTMENT NUMBER(16,2),
PREBILLDEBITADJUSTMENT NUMBER(16,2)
***********************RP_TMP_ALERTSERVICE*********************
CREATE TABLE RP_TMP_ALERTSERVICE
(
ACCOUNTNO NUMBER(20),
CUSTOMERNAME VARCHAR2(152 BYTE),
IMSI VARCHAR2(500 BYTE),
MSISDN VARCHAR2(50 BYTE),
CELL_CITY_CODE VARCHAR2(50 BYTE),
PLANNAME VARCHAR2(50 BYTE),
STATUS VARCHAR2(10 BYTE)
)
********************RP_TMP2_ALERT*******************
CREATE TABLE RP_TMP2_ALERT
(
ACCOUNTNUMBER NUMBER(20),
INVOICEAMOUNT NUMBER(16,2),
PAYMENT NUMBER(20,2),
POSTBILLCREDITADJUSTMENT NUMBER(16,2),
POSTBILLDEBITADJUSTMENT NUMBER(16,2),
REVERSALPAYMENT NUMBER(20,2),
UNBILLED_USAGE NUMBER(20,2),
CREDITLIMIT NUMBER(16,2),
SECURITYDEPOSIT NUMBER(16,2),
ADVANCEPAYMENT NUMBER(20,2),
PREBILLCREDITADJUSTMENT NUMBER(16,2),
PREBILLDEBITADJUSTMENT NUMBER(16,2)
)
*/
INSERT INTO RP_TEMP_ALERTSERVICE_TEST
(
ACCOUNTNO,MSISDN, PLANNAME, IMSI , CELL_CITY_CODE, CUSTOMERNAME, STATUS,MSISDNSTARTDATE
)
SELECT X.ACCOUNTNO,X.MSISDN,X.PLANNAME,X.IMSI,Y.CELL_CITY_CODE,Y.CUSTOMERNAME,DECODE(X.STATUS,'1','Active','3','TD')STATUS,X.MSISDNSTARTDATE
FROM
(
/*
SELECT A.ACCOUNTNO ACCOUNTNO,A.SERVICENO MSISDN,B.OFFERINGNAME PLANNAME,A.NETWORKIDENTIFIERNO IMSI,A.STATUS STATUS ,A.MSISDNSTARTDATE FROM
(
SELECT ACCOUNTNO,
CASE WHEN NOC = 1 THEN SERVICENO ELSE
LPAD(NOC,10,'0') END SERVICENO,NETWORKIDENTIFIERNO,STATUS,DECODE(NOC,'1',startdate)msisdnstartdate
FROM (
SELECT ACCOUNTNO,MAX(SERVICENO)SERVICENO,COUNT(*) NOC,MAX(NETWORKIDENTIFIERNO)NETWORKIDENTIFIERNO,MAX(STATUS)STATUS,startdate
FROM
(SELECT ACCOUNTNO,SERVICENO,NETWORKIDENTIFIERNO,STATUS,startdate FROM CC_ACCOUNTSERVICE
WHERE PRODUCTLINEID=1 AND STOPDATE IS NULL AND STATUS IN (1,3)
)
GROUP BY ACCOUNTNO,startdate)
) A ,*/
SELECT A.ACCOUNTNO ACCOUNTNO,A.SERVICENO MSISDN,B.OFFERINGNAME PLANNAME,A.NETWORKIDENTIFIERNO IMSI,A.STATUS STATUS ,A.MSISDNSTARTDATE FROM
(
SELECT ACCOUNTNO,
CASE WHEN NOC = 1 THEN SERVICENO ELSE
LPAD(NOC,10,'0') END SERVICENO,NETWORKIDENTIFIERNO,STATUS,DECODE(NOC,'1',startdate)msisdnstartdate
FROM (
SELECT ACCOUNTNO,SERVICENO, NOC,NETWORKIDENTIFIERNO,STATUS,startdate
FROM
(SELECT DISTINCT A.ACCOUNTNO,DECODE(B.NOC,1,A.SERVICENO,NULL) SERVICENO,B.NOC,
DECODE(B.NOC,1,A.NETWORKIDENTIFIERNO,NULL) NETWORKIDENTIFIERNO,
DECODE(B.NOC,1,A.STATUS,NULL) STATUS,DECODE(B.NOC,1,A.startdate,NULL) startdate
FROM CC_ACCOUNTSERVICE A,
(SELECT ACCOUNTNO,COUNT(*) NOC FROM CC_ACCOUNTSERVICE WHERE PRODUCTLINEID=1 AND STOPDATE IS NULL AND STATUS IN (1,3) GROUP BY ACCOUNTNO) B
WHERE A.PRODUCTLINEID=1 AND A.STOPDATE IS NULL AND A.STATUS IN (1,3) AND A.ACCOUNTNO = B.ACCOUNTNO
))
) A,
(SELECT ACCOUNTNO,SERVICENO,OFFERINGNAME FROM (
SELECT ACCOUNTNO,SERVICENO,OFFERINGNAME FROM CC_ACCOUNTSERVICEOFFERING
WHERE OFFERINGTYPEID = 2 AND PRODUCTLINEID = 1 AND STATUS IN (1,3) AND STOPDATE IS NULL
))B
WHERE A.ACCOUNTNO = B.ACCOUNTNO (+)
AND A.SERVICENO = B.SERVICENO (+)
ORDER BY A.ACCOUNTNO
)X,
(
SELECT DISTINCT /* PARALLEL (A, 2) NOLOGGING */ SYSDATE REPORTDATE ,
A.ACCOUNTNO ACCOUNTNUMBER,
B.ACCOUNTNAME CUSTOMERNAME,
E.BILLABLEACCOUNTNO BILLABLEACCOUNTNUMBER,
F.BILLGROUPID BILL_GROUP ,
E.SALESAGENTCD CELL_CITY_CODE
FROM
(SELECT A.ACCOUNTNO FROM CC_ACCOUNTSERVICE A GROUP BY A.ACCOUNTNO HAVING COUNT(*)>=1)A,
CC_ACCOUNTSERVICE B, CC_ACCOUNT E , SA_BILLGROUP F
WHERE
A.ACCOUNTNO=B.ACCOUNTNO
AND A.ACCOUNTNO=E.ACCOUNTNO AND
B.STARTDATE IS NOT NULL
AND B.STOPDATE IS NULL
AND B.ACCOUNTNO = E.ACCOUNTNO
AND E.BILLGROUPID = F.BILLGROUPID
AND B.PRODUCTLINEID = 1
AND E.BILLGROUPID =105
AND B.STATUS IN (1,3)
)Y
WHERE X.ACCOUNTNO=Y.ACCOUNTNUMBER;
-- Find out new Bill Start Date
SELECT d.STOPDATE+1
INTO V_BillStartDate
FROM SA_BILLGROUP a, SA_BILLGROUPCYCLE b , SA_BILLCYCLEPERIOD c, SA_BILLPERIOD d
WHERE a.BILLGROUPID = b.BILLGROUPID
AND a.BILLGROUPCYCLENO = c.BILLCYCLENO
AND b.BILLCYCLEID = c.BILLCYCLEID
AND c.BILLPERIODID = d.BILLPERIODID
AND a.BILLGROUPID = 105;
-- Bill Stop Date
SELECT TRUNC(SYSDATE)
INTO V_BillStopDate
FROM dual;
INSERT INTO RP_TEMP2_ALERT_TEST
(
ACCOUNTNUMBER, INVOICEAMOUNT, PAYMENT, POSTBILLCREDITADJUSTMENT, POSTBILLDEBITADJUSTMENT, REVERSALPAYMENT, CREDITLIMIT, SECURITYDEPOSIT,CREDITPROPERTY,
ADVANCEPAYMENT, PREBILLCREDITADJUSTMENT, PREBILLDEBITADJUSTMENT,
UNBILLED_USAGE, WAIVEDAMOUNT,PAYBLEAMOUNT
)
SELECT Z.ACCOUNTNO,G.INVOICEAMOUNT ,A.PAYMENT,D.POSTCRDADJ,C.POSTDEBITADJ, H.REVERSALPAYMENT,
I.CREDITLIMIT,J.SECURITYDEPOSIT,Y.CREDITPROPERTY,
B.ADVANCEPAYMENT ,E.PRECRDADJ,F.PREDEBADJ,K.UNBILLEDUSAGE,S.WAIVEDAMOUNT,
( NVL(G.INVOICEAMOUNT,0) - NVL(A.PAYMENT,0)
- NVL(E.PRECRDADJ,0)+ NVL(F.PREDEBADJ,0)
- NVL(D.POSTCRDADJ,0)+ NVL(C.POSTDEBITADJ,0)
+ NVL(K.UNBILLEDUSAGE,0) - NVL(S.WAIVEDAMOUNT,0)) PAYBLEAMOUNT
FROM
--G.INVOICEOUTSTANDINGAMOUNT,G.ALLOCATEDAMOUNT , ((G.INVOICEAMOUNT - NVL(G.ALLOCATEDAMOUNT,0) + NVL(C.POSTDEBITADJ,0) - NVL(D.POSTCRDADJ,0)) - G.INVOICEOUTSTANDINGAMOUNT ) FROM
(SELECT ACCOUNTNO, ROUND(SUM(PAYMENTAMOUNT),2) PAYMENT FROM AP_PAYMENT
WHERE PAYMENTSTATUS =1 AND ALLOCATIONTYPE!=0
GROUP BY ACCOUNTNO )A,
(SELECT ACCOUNTNO, ROUND(SUM( PAYMENTBALANCE),2) ADVANCEPAYMENT FROM AP_ADVANCEPAYMENT
GROUP BY ACCOUNTNO ) B,
(SELECT ACCOUNTNO, ROUND(SUM(ADJUSTMENTAMOUNT),2) POSTDEBITADJ FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE = 2 AND ADJUSTMENTCATEGORY = 2
GROUP BY ACCOUNTNO )C,
(SELECT ACCOUNTNO, ROUND(SUM(ADJUSTMENTAMOUNT),2) POSTCRDADJ FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE = 1 AND ADJUSTMENTCATEGORY = 2
GROUP BY ACCOUNTNO )D,
(SELECT ACCOUNTNO, ROUND(SUM(ADJUSTMENTAMOUNT),2) PRECRDADJ FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE = 1 AND ADJUSTMENTCATEGORY = 1 AND ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO )E,
(SELECT ACCOUNTNO, ROUND(SUM(ADJUSTMENTAMOUNT),2) PREDEBADJ FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE = 2 AND ADJUSTMENTCATEGORY = 1 AND ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO )F,
( SELECT X.ACCOUNTNO,ROUND(SUM(X.INVOICEAMOUNT),2) INVOICEAMOUNT FROM AP_INVOICE X
WHERE invoicetypeid !=3 GROUP BY X.ACCOUNTNO ) G,
(SELECT ACCOUNTNO, ROUND(SUM(PAYMENTAMOUNT),2) REVERSALPAYMENT FROM AP_PAYMENT
WHERE PAYMENTSTATUS IN (3,4) AND ALLOCATIONTYPE!=0
GROUP BY ACCOUNTNO ) H,
(SELECT b.accountno,q.CREDITTHRESHOLD CREDITLIMIT FROM cr_riskcategorymaster q , CC_ACCOUNT b
WHERE b.CREDITRATING BETWEEN q.LOWERSCORE AND q.UPPERSCORE AND b.billgroupid= 105 ) I,
(SELECT ACCOUNTNO,ROUND(SUM(PAIDDEPOSITAMOUNT),2) SECURITYDEPOSIT FROM AP_COLLECTEDDEPOSIT
WHERE REFUNDSTATUS=0
GROUP BY ACCOUNTNO) J,
( SELECT accountno, PROPERTYVALUE CREDITPROPERTY FROM CC_ACCOUNTPROPERTY WHERE propertyid=18 )Y,
(SELECT ACCOUNTNO,NVL(SUM(WAIVEDAMOUNT),0) WAIVEDAMOUNT FROM AP_INVOICEWAIVER WHERE TRUNC(WAIVEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO)S,
RP_TEMP_ALERTSERVICE_TEST Z,
/* (SELECT ACCOUNTNO,ROUND(SUM(USAGECHARGE),2) UNBILLEDUSAGE FROM AC_BILLINGPOSTED
WHERE UOMID = 100 AND ACCUMULATEDDATE BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO) K*/
( SELECT ACCOUNTNO,SUM(BILLABLEAMOUNT) UNBILLEDUSAGE FROM
(SELECT ACCOUNTNO,NVL(ROUND(SUM(BILLABLEAMOUNT),2),0) BILLABLEAMOUNT FROM AC_GSMPOSTED
WHERE TRUNC(ACCUMULATEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO
UNION ALL
SELECT ACCOUNTNO,NVL(ROUND(SUM(BILLABLEAMOUNT),2),0) BILLABLEAMOUNT FROM AC_GSMSMSPOSTED
WHERE TRUNC(ACCUMULATEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO
UNION ALL
SELECT ACCOUNTNO,NVL(ROUND(SUM(BILLABLEAMOUNT),2),0) BILLABLEAMOUNT FROM AC_GSMROAMPOSTED
WHERE TRUNC(ACCUMULATEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO
UNION ALL
SELECT ACCOUNTNO,NVL(ROUND(SUM(BILLABLEAMOUNT),2),0) BILLABLEAMOUNT FROM AC_GPRSPOSTED
WHERE TRUNC(ACCUMULATEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO)
GROUP BY ACCOUNTNO)K
WHERE Z.ACCOUNTNO = A.ACCOUNTNO (+)
AND Z.ACCOUNTNO = B.ACCOUNTNO (+)
AND Z.ACCOUNTNO = C.ACCOUNTNO (+)
AND Z.ACCOUNTNO = D.ACCOUNTNO (+)
AND Z.ACCOUNTNO = E.ACCOUNTNO (+)
AND Z.ACCOUNTNO = F.ACCOUNTNO (+)
AND Z.ACCOUNTNO = G.ACCOUNTNO (+)
AND Z.ACCOUNTNO = H.ACCOUNTNO (+)
AND Z.ACCOUNTNO= I.ACCOUNTNO(+)
AND Z.ACCOUNTNO = J.ACCOUNTNO (+)
AND Z.ACCOUNTNO = S.ACCOUNTNO (+)
AND Z.ACCOUNTNO = K.ACCOUNTNO (+)
AND Z.ACCOUNTNO = Y.ACCOUNTNO (+);
INSERT INTO RP_ALERT_TEST
(
REPORTDATE,ACCOUNTNUMBER, MSISDN,PLANNAME,IMSI,CELL_CITY_CODE, CUSTOMERNAME,STATUS,MSISDNSTARTDATE,
INVOICEAMOUNT, PAYMENT, POSTBILLCREDITADJUSTMENT, POSTBILLDEBITADJUSTMENT, REVERSALPAYMENT,
CREDITLIMIT, SECURITYDEPOSIT,CREDITPROPERTY, ADVANCEPAYMENT,PREBILLCREDITADJUSTMENT, PREBILLDEBITADJUSTMENT,
UNBILLED_USAGE, WAIVEDAMOUNT,PAYBLEAMOUNT
)
SELECT DISTINCT /* parallel (a, 2) nologging */
SYSDATE ReportDate,M.ACCOUNTNO,M.MSISDN,M.PLANNAME,M.IMSI,M.CELL_CITY_CODE,M.CUSTOMERNAME,M.STATUS,M.MSISDNSTARTDATE,
N.INVOICEAMOUNT,N.PAYMENT,N.POSTBILLCREDITADJUSTMENT,N.POSTBILLDEBITADJUSTMENT,N.REVERSALPAYMENT,
N.CREDITLIMIT,N.SECURITYDEPOSIT,N.CREDITPROPERTY,N.ADVANCEPAYMENT,N.PREBILLCREDITADJUSTMENT,N.PREBILLDEBITADJUSTMENT,
NVL(N.UNBILLED_USAGE,0),N. WAIVEDAMOUNT,NVL(N.PAYBLEAMOUNT,0)
FROM RP_TEMP_ALERTSERVICE_TEST M,RP_TEMP2_ALERT_TEST N
WHERE M.ACCOUNTNO=N.ACCOUNTNUMBER(+);
--------CALCULATE PREBILLADJUSTMENT--------------------------------------------
/* UPDATE Rp_Alert_TEST a
SET a.PREBILLCREDITADJUSTMENT = (SELECT ROUND(SUM(b.ADJUSTMENTAMOUNT),2)
FROM AP_ACCOUNTADJUSTMENT b
WHERE b.ACCOUNTNO = a.ACCOUNTNUMBER
AND b.ADJUSTMENTTYPE = 1
AND b.ADJUSTMENTCATEGORY = 1
AND b.ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate ),
a.PREBILLDEBITADJUSTMENT = (SELECT ROUND(SUM(b.ADJUSTMENTAMOUNT),2)
FROM AP_ACCOUNTADJUSTMENT b
WHERE b.ACCOUNTNO = a.ACCOUNTNUMBER
AND b.ADJUSTMENTTYPE = 2
AND b.ADJUSTMENTCATEGORY = 1
AND b.ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate )
WHERE (ACCOUNTNUMBER) IN (SELECT ACCOUNTNO
FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE IN (1,2)
AND ADJUSTMENTCATEGORY = 1
AND ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate );*/
--CALCULATE UNBILLEDUSAGE---------------------------------------
/* UPDATE Rp_Alert_TEST a
SET a.UNBILLED_USAGE=(SELECT ROUND(SUM(USAGECHARGE),2) UNBILLEDUSAGE FROM AC_BILLINGPOSTED b
WHERE a.accountnumber=b.ACCOUNTNO AND UOMID = 100 AND ACCUMULATEDDATE BETWEEN V_BillStartDate AND V_BillStopDate)
WHERE (ACCOUNTNUMBER) IN (SELECT ACCOUNTNO
FROM AC_BILLINGPOSTED b
WHERE UOMID = 100
AND ACCUMULATEDDATE BETWEEN V_BillStartDate AND V_BillStopDate );
-- Calculate Payable Amount
UPDATE Rp_Alert_TEST a
SET PAYBLEAMOUNT= NVL(INVOICEAMOUNT,0) - NVL(PAYMENT,0) - NVL(ADVANCEPAYMENT,0)
- NVL(PREBILLCREDITADJUSTMENT,0)+ NVL(PREBILLDEBITADJUSTMENT,0)
- NVL(POSTBILLCREDITADJUSTMENT,0)+ NVL(POSTBILLDEBITADJUSTMENT,0)
+ NVL(REVERSALPAYMENT,0)+ NVL(UNBILLED_USAGE,0) ;*/
UPDATE RP_ALERT_TEST a
SET UsagePercentage = (NVL(PaybleAmount,0) / NVL(CREDITLIMIT,0)) *100
WHERE NVL(CREDITLIMIT,0) > 0;
COMMIT;
END Rp_Alert_Test_Sp;
/
Highly Appreciate your valuable time spent on the issue.
Thank you.
Regards.
MN
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:04:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
