[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NVL(SUM(DECODE(GPS.PERIOD_NAME
,CURRENT_PERIOD
,1
,0))
,0) CURRENT_CUSTOMER_COUNT,
NVL(SUM(DECODE(GPS.PERIOD_NAME
,PRIOR_PERIOD
,1
,0))
,0) PRIOR_CUSTOMER_COUNT,
NVL(SUM(DECODE(GPS.PERIOD_YEAR
,CURRENT_YEAR
,1
,0))
,0) CURRENT_YEAR_CUSTOMER_COUNT
INTO CURRENT_CUSTOMER_COUNT,PRIOR_CUSTOMER_COUNT,CURRENT_YEAR_CUSTOMER_COUNT
FROM
HZ_CUST_ACCOUNTS C,
GL_PERIOD_STATUSES GPS
WHERE C.CREATION_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE
AND GPS.APPLICATION_ID = 222
AND GPS.SET_OF_BOOKS_ID = SET_OF_BOOKS_ID_LP
AND ( GPS.PERIOD_NAME = CURRENT_PERIOD
OR GPS.PERIOD_NAME = PRIOR_PERIOD )
AND C.CREATION_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE;
SELECT
NVL(SUM(DECODE(GPS.PERIOD_NAME
,CURRENT_PERIOD
,1
,0))
,0) CURRENT_LOCATION_COUNT,
NVL(SUM(DECODE(GPS.PERIOD_NAME
,PRIOR_PERIOD
,1
,0))
,0) PRIOR_LOCATION_COUNT,
NVL(SUM(DECODE(GPS.PERIOD_YEAR
,CURRENT_YEAR
,1
,0))
,0) PRIOR_YEAR_COUNT
INTO CURRENT_LOCATION_COUNT,PRIOR_LOCATION_COUNT,PRIOR_YEAR_COUNT
FROM
HZ_CUST_ACCT_SITES A,
GL_PERIOD_STATUSES GPS
WHERE A.CREATION_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE
AND GPS.SET_OF_BOOKS_ID = SET_OF_BOOKS_ID_LP
AND GPS.APPLICATION_ID = 222
AND ( GPS.PERIOD_NAME = CURRENT_PERIOD
OR GPS.PERIOD_NAME = PRIOR_PERIOD );
SELECT
NVL(SUM(DECODE(GPS.PERIOD_NAME
,CURRENT_PERIOD
,1
,0))
,0) CURRENT_ON_HOLD_Y_COUNT,
NVL(SUM(DECODE(GPS.PERIOD_NAME
,PRIOR_PERIOD
,1
,0))
,0) PRIOR_ON_HOLD_Y_COUNT,
NVL(SUM(DECODE(GPS.PERIOD_YEAR
,CURRENT_YEAR
,1
,0))
,0) YEAR_ON_HOLD_Y_COUNT
INTO CURRENT_ON_HOLD_Y_COUNT,PRIOR_ON_HOLD_Y_COUNT,YEAR_ON_HOLD_Y_COUNT
FROM
AR_CREDIT_HISTORIES CH,
GL_PERIOD_STATUSES GPS
WHERE CH.CREATION_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE
AND CH.ON_HOLD = 'Y'
AND GPS.SET_OF_BOOKS_ID = SET_OF_BOOKS_ID_LP
AND GPS.APPLICATION_ID = 222
AND ( GPS.PERIOD_NAME = CURRENT_PERIOD
OR GPS.PERIOD_NAME = PRIOR_PERIOD );
SELECT
NVL(SUM(DECODE(GPS.PERIOD_NAME
,CURRENT_PERIOD
,1
,0))
,0) CURRENT_ON_HOLD_N_COUNT,
NVL(SUM(DECODE(GPS.PERIOD_NAME
,PRIOR_PERIOD
,1
,0))
,0) PRIOR_ON_HOLD_N_COUNT,
NVL(SUM(DECODE(GPS.PERIOD_YEAR
,CURRENT_YEAR
,1
,0))
,0) YEAR_ON_HOLD_N_COUNT
INTO CURRENT_ON_HOLD_N_COUNT,PRIOR_ON_HOLD_N_COUNT,YEAR_ON_HOLD_N_COUNT
FROM
AR_CREDIT_HISTORIES CH,
GL_PERIOD_STATUSES GPS
WHERE CH.CREATION_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE
AND CH.ON_HOLD = 'N'
AND GPS.SET_OF_BOOKS_ID = SET_OF_BOOKS_ID_LP
AND GPS.APPLICATION_ID = 222
AND ( GPS.PERIOD_NAME = CURRENT_PERIOD
OR GPS.PERIOD_NAME = PRIOR_PERIOD );
SELECT
ROUND(NVL(SUM(DECODE(GPS.PERIOD_NAME
,CURRENT_PERIOD
,1
,0))
,0) / ((CURRENT_END_DATE) - (CURRENT_START_DATE))
,1) CURRENT_PAY_PER_DAY,
ROUND(NVL(SUM(DECODE(GPS.PERIOD_NAME
,PRIOR_PERIOD
,1
,0))
,0) / ((PRIOR_END_DATE) - (PRIOR_START_DATE))
,1) PRIOR_PAY_PER_DAY
INTO CURRENT_PAY_PER_DAY,PRIOR_PAY_PER_DAY
FROM
AR_CASH_RECEIPTS CR,
AR_RECEIPT_METHODS RM,
AR_RECEIPT_CLASSES RC,
AR_CASH_RECEIPT_HISTORY CRH,
GL_PERIOD_STATUSES GPS
WHERE CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND RM.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.FIRST_POSTED_RECORD_FLAG = 'Y'
AND RC.CREATION_METHOD_CODE = 'MANUAL'
AND GPS.SET_OF_BOOKS_ID = SET_OF_BOOKS_ID_LP
AND GPS.APPLICATION_ID = 222
AND ( GPS.PERIOD_NAME = CURRENT_PERIOD
OR GPS.PERIOD_NAME = PRIOR_PERIOD )
AND CRH.GL_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE
AND CR.STATUS NOT IN ( 'NSF' , 'REV' , 'STOP' );
SELECT
NVL(ROUND(SUM(DECODE(GPS.PERIOD_NAME
,CURRENT_PERIOD
,1
,0)) / ((CURRENT_END_DATE) - (CURRENT_START_DATE))
,1)
,0) CURRENT_BATCH_PER_DAY,
NVL(ROUND(SUM(DECODE(GPS.PERIOD_NAME
,PRIOR_PERIOD
,1
,0)) / ((PRIOR_END_DATE) - (PRIOR_START_DATE))
,1)
,0) PRIOR_BATCH_PER_DAY
INTO CURRENT_BATCH_PER_DAY,PRIOR_BATCH_PER_DAY
FROM
AR_BATCHES B,
GL_PERIOD_STATUSES GPS
WHERE GPS.SET_OF_BOOKS_ID = SET_OF_BOOKS_ID_LP
AND GPS.APPLICATION_ID = 222
AND ( GPS.PERIOD_NAME = CURRENT_PERIOD
OR GPS.PERIOD_NAME = PRIOR_PERIOD )
AND B.TYPE = 'MANUAL'
AND B.GL_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE;
SELECT
NVL(ROUND(AVG(DECODE(GPS.PERIOD_NAME
,CURRENT_PERIOD
,COUNT(CR.CASH_RECEIPT_ID)
,NULL))
,1)
,0) CURRENT_PAY_PER_BATCH,
NVL(ROUND(AVG(DECODE(GPS.PERIOD_NAME
,PRIOR_PERIOD
,COUNT(CR.CASH_RECEIPT_ID)
,NULL))
,1)
,0) PRIOR_PAY_PER_BATCH
INTO CURRENT_PAY_PER_BATCH,PRIOR_PAY_PER_BATCH
FROM
AR_CASH_RECEIPTS CR,
AR_RECEIPT_METHODS RM,
AR_RECEIPT_CLASSES RC,
AR_CASH_RECEIPT_HISTORY CRH,
AR_BATCHES B,
GL_PERIOD_STATUSES GPS
WHERE CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND RM.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.FIRST_POSTED_RECORD_FLAG = 'Y'
AND RC.CREATION_METHOD_CODE = 'MANUAL'
AND GPS.SET_OF_BOOKS_ID = SET_OF_BOOKS_ID_LP
AND GPS.APPLICATION_ID = 222
AND ( GPS.PERIOD_NAME = CURRENT_PERIOD
OR GPS.PERIOD_NAME = PRIOR_PERIOD )
AND CRH.BATCH_ID = B.BATCH_ID
AND B.GL_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE
GROUP BY
B.BATCH_ID,
GPS.PERIOD_NAME;
SELECT
DECODE(P_PRIOR_ON_HOLD_N_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((P_CURRENT_ON_HOLD_N_COUNT - P_PRIOR_ON_HOLD_N_COUNT) * 100 / P_PRIOR_ON_HOLD_N_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(P_PRIOR_ON_HOLD_Y_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((P_CURRENT_ON_HOLD_Y_COUNT - P_PRIOR_ON_HOLD_Y_COUNT) * 100 / P_PRIOR_ON_HOLD_Y_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_NEW_INVOICES_COUNT
,0
,'n/a'
,TO_CHAR((((CURRENT_NEW_INVOICES_COUN - PRIOR_NEW_INVOICES_COUNT) / PRIOR_NEW_INVOICES_COUNT)) * 100
,'990D00'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_NEW_INVOICES_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_NEW_INVOICES_AMOU - PRIOR_NEW_INVOICES_AMOUNT) * 100 / PRIOR_NEW_INVOICES_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_NEW_CREDIT_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_NEW_CREDIT_COUNT - PRIOR_NEW_CREDIT_COUNT) * 100 / PRIOR_NEW_CREDIT_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_NEW_CREDIT_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_NEW_CREDIT_AMOUNT - PRIOR_NEW_CREDIT_AMOUNT) * 100 / PRIOR_NEW_CREDIT_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_DUE_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_DUE_COUNT - PRIOR_DUE_COUNT) * 100 / PRIOR_DUE_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_DUE_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_DUE_AMOUNT - PRIOR_DUE_AMOUNT) * 100 / PRIOR_DUE_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(P_PRIOR_PAST_DUE_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_PAST_DUE_AMOUNT - P_PRIOR_PAST_DUE_AMOUNT) * 100 / P_PRIOR_PAST_DUE_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(P_PRIOR_PAST_DUE_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_PAST_DUE_COUNT - P_PRIOR_PAST_DUE_COUNT) * 100 / P_PRIOR_PAST_DUE_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_QC_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_QC_COUNT - PRIOR_QC_COUNT) * 100 / PRIOR_QC_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_QC_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_QC_AMOUNT - PRIOR_QC_AMOUNT) * 100 / PRIOR_QC_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_C_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_C_COUNT - PRIOR_C_COUNT) * 100 / PRIOR_C_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_C_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_C_AMOUNT - PRIOR_C_AMOUNT) * 100 / PRIOR_C_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_CR_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_CR_COUNT - PRIOR_CR_COUNT) * 100 / PRIOR_CR_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_CR_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_CR_AMOUNT - PRIOR_CR_AMOUNT) * 100 / PRIOR_CR_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_ADJUST_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_ADJUST_COUNT - PRIOR_ADJUST_COUNT) * 100 / PRIOR_ADJUST_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_ADJUST_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_ADJUST_AMOUNT - PRIOR_ADJUST_AMOUNT) * 100 / PRIOR_ADJUST_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_TYPE_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_TYPE_AMOUNT - PRIOR_TYPE_AMOUNT) * 100 / PRIOR_TYPE_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_PT_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_PT_COUNT - PRIOR_PT_COUNT) * 100 / PRIOR_PT_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_NSF_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_NSF_COUNT - PRIOR_NSF_COUNT) * 100 / PRIOR_NSF_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_NSF_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_NSF_AMOUNT - PRIOR_NSF_AMOUNT) * 100 / PRIOR_NSF_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_OR_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_OR_AMOUNT - PRIOR_OR_AMOUNT) * 100 / PRIOR_OR_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_OR_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_OR_COUNT - PRIOR_OR_COUNT) * 100 / PRIOR_OR_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_EDT_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_EDT_COUNT - PRIOR_EDT_COUNT) * 100 / PRIOR_EDT_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_EDT_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_EDT_AMOUNT - PRIOR_EDT_AMOUNT) * 100 / PRIOR_EDT_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_UDT_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_UDT_COUNT - PRIOR_UDT_COUNT) * 100 / PRIOR_UDT_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(PRIOR_UDT_AMOUNT
,0
,'n/a'
,NVL(TO_CHAR(((CURRENT_UDT_AMOUNT - PRIOR_UDT_AMOUNT) * 100 / PRIOR_UDT_AMOUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(P_PRIOR_CUSTOMER_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((P_CURRENT_CUSTOMER_COUNT - P_PRIOR_CUSTOMER_COUNT) * 100 / P_PRIOR_CUSTOMER_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(P_PRIOR_LOCATION_COUNT
,0
,'n/a'
,NVL(TO_CHAR(((P_CURRENT_LOCATION_COUNT - P_PRIOR_LOCATION_COUNT) * 100 / P_PRIOR_LOCATION_COUNT)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(P_PRIOR_PAY_PER_BATCH
,0
,'n/a'
,NVL(TO_CHAR(((P_CURRENT_PAY_PER_BATCH - P_PRIOR_PAY_PER_BATCH) * 100 / P_PRIOR_PAY_PER_BATCH)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(P_PRIOR_PAY_PER_DAY
,0
,'n/a'
,NVL(TO_CHAR(((P_CURRENT_PAY_PER_DAY - P_PRIOR_PAY_PER_DAY) * 100 / P_PRIOR_PAY_PER_DAY)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;
SELECT
DECODE(P_PRIOR_BATCH_PER_DAY
,0
,'n/a'
,NVL(TO_CHAR(((P_CURRENT_BATCH_PER_DAY - P_PRIOR_BATCH_PER_DAY) * 100 / P_PRIOR_BATCH_PER_DAY)
,'990D00')
,'n/a'))
INTO PERCENT_VALUE
FROM
DUAL;