CREATE GLOBAL TEMPORARY TABLE tt_cmi_call_centre_billing
AS
(SELECT v_month MONTH ,
TYPE ,
SUM(
CASE
WHEN Invoice_Amount = 140
AND Invoice_Month = v_month
AND Invoice_year = v_current_year
THEN 1
ELSE NULL
END) CMI_140_M ,
SUM(
CASE
WHEN Invoice_Amount = 85
AND Invoice_Month = v_month
AND Invoice_year = v_current_year
THEN 1
ELSE NULL
END) CMI_85_M ,
SUM(
CASE
WHEN Invoice_Amount = 140
THEN 1
ELSE NULL
END) CMI_140_Y ,
SUM(
CASE
WHEN Invoice_Amount = 85
THEN 1
ELSE NULL
END) CMI_85_Y ,
SUM(
CASE
WHEN Invoice_Month = v_month
AND Invoice_year = v_current_year
AND C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 70
AND Funded_Month = v_month
THEN 1
ELSE NULL
END) CIBCI_140_M ,
SUM(
CASE
WHEN ( ( C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 85 )
OR ( C2_Compensated_Month = v_month
AND C2_Compensated_Amt = 85 ) )
AND Invoice_Month = v_month
AND Invoice_year = v_current_year
AND Funded_Month = v_month
THEN 1
ELSE NULL
END) CIBCI_85_M ,
SUM(
CASE
WHEN ( ( C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 55 )
OR ( C2_Compensated_Month = v_month
AND C2_Compensated_Amt = 55 ) )
AND Invoice_Month = v_month
AND Invoice_year = v_current_year
AND Funded_Month = v_month
THEN 1
ELSE NULL
END) CIBCI_55_M ,
SUM(
CASE
WHEN ( Funded_month IS NULL )
AND Invoice_Month = v_month
AND Invoice_year = v_current_year
THEN 1
ELSE NULL
END) CIBCI_NF_M ,
SUM(
CASE
WHEN Funded_month = v_month
AND Invoice_Month = v_month
AND Invoice_year = v_current_year
AND C1_Compensated_Month IS NULL
AND C2_Compensated_Month IS NULL
THEN 1
ELSE NULL
END) CIBCI_F_M ,
SUM(
CASE
WHEN C1_Compensated_Month IS NOT NULL
AND C1_Compensated_Amt = 70
AND C2_Compensated_Amt = 70
AND Funded_month IS NOT NULL
THEN 1
ELSE NULL
END) CIBCI_140_Y ,
SUM(
CASE
WHEN ( C1_Compensated_Amt = 85
OR C2_Compensated_Amt = 85
OR (NVL(C1_Compensated_Amt, 0) + NVL(C2_Compensated_Amt, 0)) = 85 )
AND Funded_month IS NOT NULL
THEN 1
ELSE NULL
END) CIBCI_85_Y ,
SUM(
CASE
WHEN ( C1_Compensated_Amt = 55
OR C2_Compensated_Amt = 55
OR (C1_Compensated_Amt + C2_Compensated_Amt) = 55 )
AND Funded_month IS NOT NULL
THEN 1
ELSE NULL
END) CIBCI_55_Y ,
SUM(
CASE
WHEN Funded_month IS NOT NULL
AND C1_Compensated_Amt IS NULL
AND C2_Compensated_Amt IS NULL
THEN 1
ELSE NULL
END) CIBCI_F_Y ,
SUM(
CASE
WHEN Funded_month IS NULL
THEN 1
ELSE NULL
END) CIBCI_NF_Y ,
SUM(
CASE
WHEN funded_month IS NULL
THEN 0
WHEN Invoice_month <> v_month
AND C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 70
THEN 1
WHEN Invoice_month = v_month
AND invoice_year <> v_current_year
AND C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 70
THEN 1
ELSE NULL
END) check_140 ,
SUM(
CASE
WHEN funded_month IS NULL
THEN 0
WHEN Invoice_month <> v_month
AND C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 85
THEN 1
WHEN Invoice_month <> v_month
AND C2_Compensated_Month = v_month
AND C2_Compensated_Amt = 85
THEN 1
WHEN Invoice_month = v_month
AND invoice_year <> v_current_year
AND C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 85
THEN 1
WHEN Invoice_month = v_month
AND invoice_year <> v_current_year
AND C2_Compensated_Month = v_month
AND C2_Compensated_Amt = 85
THEN 1
ELSE NULL
END) check_85 ,
SUM(
CASE
WHEN funded_month IS NULL
THEN 0
WHEN Invoice_month <> v_month
AND C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 55
THEN 1
WHEN Invoice_month <> v_month
AND C2_Compensated_Month = v_month
AND C2_Compensated_Amt = 55
THEN 1
WHEN Invoice_month = v_month
AND invoice_year <> v_current_year
AND C1_Compensated_Month = v_month
AND C1_Compensated_Amt = 55
THEN 1
WHEN Invoice_month = v_month
AND invoice_year <> v_current_year
AND C2_Compensated_Month = v_month
AND C2_Compensated_Amt = 55
THEN 1
ELSE NULL
END) check_55 ,
SUM(
CASE
WHEN Funded_month = v_month
AND C1_Compensated_Month IS NULL
AND C2_Compensated_month IS NULL
AND ( ( Invoice_month <> v_month )
OR ( invoice_month = v_month
AND invoice_year <> v_current_year ) )
THEN 1
ELSE NULL
END) check_F ,
SUM(
CASE
WHEN invoice_month = v_month
AND invoice_year = v_current_year
AND C1_Compensated_Month = v_month
AND C1_Compensated_amt = 999
THEN 1
ELSE NULL
END) dupl_M ,
SUM(
CASE
WHEN C1_Compensated_amt = 999
THEN 1
ELSE NULL
END) dupl_y
FROM cmi_call_centre_billing
GROUP BY TYPE
ORDER BY TYPE
);
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"