SELECT
DEPTNO,
ENAME,
SAL,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL,ENAME) CUMDEPTTOT,
SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTAL,
SUM(SAL) OVER (ORDER BY DEPTNO, SAL) CUMTOT,
SUM(SAL) OVER () TOTSAL
FROM
SCOTT.EMP
ORDER BY
DEPTNO
DEPTNO,
ENAME,
SAL,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL,ENAME) CUMDEPTTOT,
SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTAL,
SUM(SAL) OVER (ORDER BY DEPTNO, SAL) CUMTOT,
SUM(SAL) OVER () TOTSAL
FROM
SCOTT.EMP
ORDER BY
DEPTNO
SELECT COMPANY,
ORDER_NO,
CUSTOMER_ID,
INVOICE_ID,
SUM(OPEN_AMOUNT*(0.2)/365*DDD) AS DELAY_CHARGE_CAL
FROM
(
SELECT CCPP.COMPANY,
CCPP.ORDER_NO,
IDENTITY AS CUSTOMER_ID,
CCPP.INVOICE_ID,
PP.INSTALLMENT_ID,
to_char(to_date(DUE_DATE,'DD-MON-RR'),'DD-MON-RR') DUE_DATE,
to_number(to_char(last_day(DUE_DATE),'dd')) DD,
SUM(to_number(to_char(last_day(DUE_DATE),'dd'))) OVER (ORDER BY DUE_DATE) DDD,
C_Credit_Account_Hist_API.Get_Open_Amount(CCPP.COMPANY, CCPP.INVOICE_ID, INSTALLMENT_ID) OPEN_AMOUNT
C_Credit_Account_Hist_API.Get_Open_Amount(CCPP.COMPANY, CCPP.INVOICE_ID, INSTALLMENT_ID)*.2/365*SUM(to_number(to_char(last_day(DUE_DATE),'dd'))) OVER (ORDER BY DUE_DATE) DC
FROM C_CREDIT_PAYMENT_PLAN CCPP,
PAYMENT_PLAN PP
WHERE CCPP.COMPANY=PP.COMPANY
AND CCPP.INVOICE_ID=PP.INVOICE_ID
AND CCPP.ORDER_NO=ORDER_NO_
AND PP.INSTALLMENT_ID<>'1'
AND to_date(DUE_DATE,'DD-MON-RR')<=REPORT_DATE_
ORDER BY to_date(DUE_DATE,'DD-MON-RR')
)
GROUP BY COMPANY,
ORDER_NO,
CUSTOMER_ID,
INVOICE_ID
No comments:
Post a Comment