DATES [message #19010] |
Mon, 25 February 2002 22:22 |
Rm69
Messages: 39 Registered: January 2002
|
Member |
|
|
The following error has occurred:
ORA-01861: literal does not match format string
ORA-06512: at "EXWH.MIS_NEWBUSINSERT_PROC", line 573
ORA-06512: at line 9
CREATE OR REPLACE PROCEDURE mis_newbusinsert_proc (
v_start_date DATE,
v_end_date DATE
)
IS
v_cnt NUMBER;
BEGIN
--------------------------------------------------------------------------------
-- OTHER ---------------------------------------------------------------------
--------------------------------------------------------------------------------
-- SBSA --
INSERT INTO mis_newbusiness
(branch, sbsa, sbsa_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital),SYSDATE, v_start_date
FROM zw30800p
WHERE disdate BETWEEN v_start_date AND v_end_date
AND company IN ('002')
GROUP BY branch;
-- QUERY TO SUM CAPITAL ON EDCON --
INSERT INTO mis_newbusiness
(branch, edcon, edcon_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital),SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd = 'KIOSKS'
AND disdate BETWEEN v_start_date AND v_end_date
OR loanoff IN ('153',
'1114',
'97',
'4843',
'4849',
'1157',
'E0080',
'J0196'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
AND company NOT IN ('002')
GROUP BY branch;
-- SUM OF STAFF LOANS --
INSERT INTO mis_newbusiness
(branch, staff_loans, staff_loans_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) = 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- CALL CENTRE --
INSERT INTO mis_newbusiness
(branch, call_centre, call_center_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd = 'AB CC'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
--------------------------------------------------------------------------------
-- CHANNELS ------------------------------------------------------------------
--------------------------------------------------------------------------------
-- BSP --
INSERT INTO mis_newbusiness
(branch, bsp, bsp_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- SUM OF HYBRID products at branch level --
INSERT INTO mis_newbusiness
(branch, hybrid_consultants, hybrid_count, summary_date,
captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd = 'HMC'
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- MOBILE SALES product at branch level --
INSERT INTO mis_newbusiness
(sbrokercd, mobile_sales, mob_sales_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE brokercd = 'CREDIT'
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- ESPS product at branch level --
INSERT INTO mis_newbusiness
(sbrokercd, esps, esps_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE brokercd = 'ESP'
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- PSA product at branch level --
INSERT INTO mis_newbusiness
(sbrokercd, psa, psa_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE brokercd = 'PSA'
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- BROKERS product at branch level --
INSERT INTO mis_newbusiness
(sbrokercd, brokers, brokers_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE brokercd IN ('AIN',
'HOSPERA',
'REGIONAL',
'COVERHOLD',
'NATU',
'ACC CARE',
'WACC',
'TAF BROK'
)
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
------------------------------------------------------------------------
------ PRODUCTS - BRANCHES -----------------------------------------------
------------------------------------------------------------------------
-- EXEMPT LOANS --
INSERT INTO mis_newbusiness
(branch, payroll_exempt, pay_exempt_count, summary_date,
captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) NOT IN ('D', 'N', 'J', 'S')
AND product NOT IN ('CL001', 'CL002')
AND SUBSTR (product, 1, 1) != 'T'
AND SUBSTR (product, 3, 1) != 'T'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- COMPLIANT --
INSERT INTO mis_newbusiness
(branch, payroll_compliant, pay_comp_count, summary_date,
captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) NOT IN ('D', 'N', 'J', 'S')
AND ( product = 'CL001'
OR product = 'CL002'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN ('153',
'1114',
'97',
'4843',
'4849',
'1157',
'E0080',
'J0196'
)
OR SUBSTR (product, 1, 1) = 'T'
OR SUBSTR (product, 3, 1) = 'T'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- EPLAN , EPLAN_COUNT --
INSERT INTO mis_newbusiness
(branch, eplan, eplan_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE product NOT IN ('JL016', 'JL021', 'JL022')
AND SUBSTR (product, 1, 1) = 'J'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- EPLAN_ENTRY, EPLAN_ENTRY_COUNT " INVESTIGATE ERROR" --
INSERT INTO mis_newbusiness
(branch, eplan_entry, eplan_entry_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE product IN ('JL016', 'JL021', 'JL022')
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- NUPAY , NUPAY_COUNT --
INSERT INTO mis_newbusiness
(branch, nupay, nupay_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE product NOT IN ('NL005', 'NL006')
AND SUBSTR (product, 1, 1) = 'N'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- NUPAY_ENTRY, NUPAY_ENTRY_COUNT --
INSERT INTO mis_newbusiness
(branch, nupay_entry, nupay_entry_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE product IN ('NL005', 'NL006')
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
-- CATCHALL, CATCHALL_COUNT --
INSERT INTO mis_newbusiness
(branch, catchall, catchall_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) = 'D'
AND brokercd NOT IN ('SBSA',
'KIOSKS',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
------------------------------------------------------------------------
------ PRODUCTS - SBROKERCD --------------------------------------------------
------------------------------------------------------------------------
-- EXEMPT LOANS --
INSERT INTO mis_newbusiness
(sbrokercd, payroll_exempt, pay_exempt_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) NOT IN ('D', 'N', 'J', 'S')
AND product NOT IN ('CL001', 'CL002')
AND SUBSTR (product, 1, 1) != 'T'
AND SUBSTR (product, 3, 1) != 'T'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- COMPLIANT --
INSERT INTO mis_newbusiness
(sbrokercd, payroll_compliant, pay_comp_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) IN ('D', 'N', 'J', 'S')
AND ( product = 'CL001'
OR product = 'CL002'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
OR SUBSTR (product, 1, 1) = 'T'
OR SUBSTR (product, 3, 1) = 'T'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- EPLAN , EPLAN_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, eplan, eplan_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE product NOT IN ('JL016', 'JL021', 'JL022')
AND SUBSTR (product, 1, 1) = 'J'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- EPLAN_ENTRY, EPLAN_ENTRY_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, eplan_entry, eplan_entry_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE product IN ('JL016', 'JL021', 'JL022')
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- NUPAY , NUPAY_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, nupay, nupay_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE product NOT IN ('NL005', 'NL006')
AND SUBSTR (product, 1, 1) = 'N'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- NUPAY_ENTRY, NUPAY_ENTRY_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, nupay_entry, nupay_entry_count, summary_date,
captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE product IN ('NL005', 'NL006')
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
-- CATCHALL, CATCHALL_COUNT --
INSERT INTO mis_newbusiness
(sbrokercd, catchall, catchall_count, summary_date, captured)
SELECT sbrokercd, SUM (capital), COUNT (capital), SYSDATE,
v_start_date
FROM zw30800p
WHERE SUBSTR (product, 1, 1) = 'D'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY sbrokercd;
--------------------------------------------------------------------------------
-- CHECKING FOR DUPLICATES BEFORE COPYING DATA TO HISTORY TABLE --------------
--------------------------------------------------------------------------------
v_cnt := 0;
SELECT COUNT(*)
INTO v_cnt
FROM mis_wrkflw_pdincl_hist
WHERE TO_DATE(v_start_date,'yyyy-mm-dd') =
TO_DATE(captured,'yyyy-mm-dd')
AND TO_DATE(SYSDATE,'yyyy-mm-dd') =
TO_DATE(summary_date,'yyyy-mm-dd');
IF v_cnt = 0
THEN
INSERT INTO mis_newbus_hist
(branch, sbrokercd, sbsa, sbsa_count, bsp, bsp_count, edcon,
edcon_count, staff_loans, staff_loans_count, call_centre,
call_center_count, payroll_exempt, pay_exempt_count,
payroll_compliant, pay_comp_count, hybrid_consultants,
hybrid_count, mobile_sales, mob_sales_count, esps,
esps_count, psa, psa_count, brokers, brokers_count, eplan,
eplan_count, nupay, nupay_count, eplan_entry,
eplan_entry_count, nupay_entry, nupay_entry_count, catchall,
catchall_count, captured, summary_date)
SELECT branch, sbrokercd, sbsa, sbsa_count, bsp, bsp_count, edcon,
edcon_count, staff_loans, staff_loans_count, call_centre,
call_center_count, payroll_exempt, pay_exempt_count,
payroll_compliant, pay_comp_count, hybrid_consultants,
hybrid_count, mobile_sales, mob_sales_count, esps, esps_count,
psa, psa_count, brokers, brokers_count, eplan, eplan_count,
nupay, nupay_count, eplan_entry, eplan_entry_count,
nupay_entry, nupay_entry_count, catchall, catchall_count,
captured, summary_date
FROM mis_newbusiness;
END IF;
END;
/
|
|
|