asked on

PLS-00488:

please hel as exec this block gives me back

ORA-06550: line 142, column 28:
PLS-00488: 'A1_CUR' must be a type
ORA-06550: line 142, column 28:
PL/SQL: Item ignored

set define off;
DECLARE
CURSOR a_cur
IS
SELECT bs.CA_turn_ID,
bs.PROJECT_NUMBER,
bs.client_NUMBER,
bs.jkl_turn_NUMBER_START,
bs.jkl_turn_NUMBER_END,
bs.ko_starting_START_DT,
bs.ko_starting_END_DT,
bs.jkl_starting_START_DT,
bs.jkl_starting_END_DT,
bs.jkl_starting,
bs.ko_starting,
bs.ko_TOTAL,
bs.jkl_TOTAL,
bs.CALC_ko_starting,
bs.CALC_jkl_starting,
bs.FLAG,
bs.jkl_starting_START_DT_USER,
bs.jkl_starting_END_DT_USER,
bs.ko_starting_START_DT_USER,
bs.ko_starting_END_DT_USER,
BS.jkl_PERCENTAGE,
BS.ko_PERCENTAGE,
pc.jkl_starting_END_DATE AS PC_jkl_starting_END_DATE,
pc.jkl_starting_START_DATE AS pc_jkl_starting_START_DATE,
ko_starting_START_DATE,
pc.ko_starting_START_DATE AS pc_ko_starting_START_DATE,
pc.ko_starting_END_DATE AS PC_ko_starting_END_DATE
FROM myschema_reports.CA_starting bs, myschema.pacs_post_clear pc
WHERE bs.project_number = pc.project_number
AND (BS.CALC_jkl_starting = 'Y' OR bs.calc_ko_starting = 'Y');

a_rec a_cur%ROWTYPE;
IdGenerator NUMBER;
sGbmaxyearmo VARCHAR2 (10);
dtgbmaxyearmo DATE;
dtjklstartingstartdate DATE;
fromjkl NUMBER := 0;
koGbmaxyearmo VARCHAR2 (10);
dtkogbmaxyearmo DATE;
dtkostartingstartdate DATE;
BEGIN
OPEN a_cur;

LOOP
BEGIN
FETCH a_cur INTO a_rec;

EXIT WHEN a_cur%NOTFOUND;
fromjkl := 0;

----------------------jkl Calculation-----------------------------------------------
IF (a_rec.CALC_jkl_starting = 'Y')
THEN
SELECT MAX (yearmo) yearmo
INTO sGbmaxyearmo
FROM inquiry.p01_billing_summary_IND
WHERE prov_id = a_rec.client_number;

sGbmaxyearmo :=
right (sGbmaxyearmo, 2) || '/01' || left (sGbmaxyearmo, 4);
dtjklstartingstartdate :=
TRUNC (TRUNC (a_rec.jkl_starting_START_DT_USER, 'MM') - 1,
'MM');

IF dtjklstartingstartdate <= dtgbmaxyearmo
THEN
--calc jklstarting
DECLARE
CURSOR a1_cur
IS
SELECT DISTINCT tm.provid AS client_NUMBER,
tm.provname AS client_name,
qq.jkl_total AS jkl_total,
qq.starting_By_turn AS jkl_starting,
qq.min_turn jkl_turn_NUMBER_START,
qq.max_turn jkl_turn_NUMBER_END
FROM inquiry.p01_tblmaster tm,
( SELECT ind.prov_id,
ttt.min_turn,
ttt.max_turn,
TO_CHAR (SUM (ind.total_dollars),
'99999999999.00')
AS jkl_TOTAL,
CASE
WHEN (TO_CHAR (
SUM (ind.total_dollars),
'99999999999.00'
) = 0)
OR (TO_NUMBER(ttt.max_turn
- ttt.min_turn)) =
0
THEN
0
ELSE
TO_CHAR (
SUM (ind.total_dollars),
'99999999999.00'
)
/ (TO_NUMBER(ttt.max_turn
- ttt.min_turn)
+ 1)
END
starting_By_turn
FROM inquiry.p01_billing_summary_ind ind,
( SELECT MAX(cf.PROCESSING_turn_NUMBER)
max_turn,
CASE
WHEN TO_NUMBER(MAX(cf.PROCESSING_turn_NUMBER)
- MIN(cf.PROCESSING_turn_NUMBER)) <
TO_NUMBER (25)
THEN
TO_NUMBER(MIN(cf.PROCESSING_turn_NUMBER))
ELSE
TO_NUMBER(MAX(cf.PROCESSING_turn_NUMBER))
- TO_NUMBER (25)
END
AS min_turn,
prov_id
FROM INQUIRY.P01_turn_FACT cf,
INQUIRY.P01_BILLING_SUMMARY_IND ind
WHERE IND.PROCESSING_turn_NUMBER =
CF.PROCESSING_turn_NUMBER
AND cf.PAYMENT_DATE <=
TO_DATE (
a_rec.pc_jkl_starting_START_DATE,
'mm/dd/yyyy'
)
GROUP BY prov_id) ttt
WHERE ttt.prov_id = ind.prov_id
AND processing_turn_number BETWEEN ttt.min_turn
AND ttt.max_turn
GROUP BY ind.prov_id,
ttt.min_turn,
ttt.max_turn) qq
WHERE tm.provid = qq.prov_id
AND tm.provid = a_rec.client_number;

a1_rec a1_cur%ROWTYPE;
OPEN a1_cur;
-- Loop
BEGIN
FETCH a1_cur INTO a1_rec;

EXIT WHEN a1_cur%NOTFOUND;

IF (a1_rec.jkl_total > 0)
THEN
UPDATE myschema_REPORTS.CA_starting
SET flag = 'N',
CALC_jkl_starting = 'N',
jkl_starting_END_DT_USER =
a_rec.jkl_starting_START_DT_USER - 1,
Z_MODIFIED_BY = 'MXA15',
Z_MODIFIED_DATE = SYSDATE
WHERE ca_turn_id = a_rec.ca_turn_id;
commit;

SELECT myschema.id_generator.NEXTVAL
INTO IdGenerator
FROM DUAL;

INSERT INTO myschema_REPORTS.CA_starting (
CA_turn_ID,
PROJECT_NUMBER,
jkl_turn_NUMBER_START,
jkl_turn_NUMBER_END,
jkl_starting,
jkl_TOTAL,
client_NUMBER,
CALC_jkl_starting,
CALC_ko_starting,
FLAG,
ko_starting_START_DT,
ko_starting_END_DT,
ko_starting,
ko_TOTAL,
jkl_starting_START_DT_USER,
jkl_starting_END_DT_USER,
ko_starting_START_DT_USER,
ko_starting_END_DT_USER,
ko_PERCENTAGE,
jkl_PERCENTAGE,
Z_CREATED_BY,
Z_MODIFIED_BY,
Z_CREATE_DATE,
Z_MODIFIED_DATE
)
VALUES (IdGenerator,
a_rec.project_number,
a1_rec.jkl_turn_NUMBER_START,
a1_rec.jkl_turn_NUMBER_END,
a1_rec.jkl_starting,
a1_rec.jkl_total,
a_rec.client_number,
'N',
a_rec.calc_ko_starting,
'Y',
a_rec.ko_starting_START_DT,
a_rec.ko_starting_END_DT,
a_rec.ko_starting,
a_rec.ko_total,
a_rec.jkl_starting_START_DT_USER,
a_rec.jkl_starting_END_DT_USER,
a_rec.ko_starting_START_DT_USER,
a_rec.ko_starting_END_DT_USER,
a_rec.ko_percentage,
a_rec.jkl_percentage,
'MXA15',
'MXA15',
SYSDATE,
SYSDATE);
commit;

fromjkl := 1;
END IF;
END;

-- END LOOP;
CLOSE a1_cur;
-- ELSE
--write to log file.
END IF;
END IF;

----------------------ko Calculation-----------------------------------------------------------------------------------------------
IF (a_rec.CALC_ko_starting = 'Y')
THEN
SELECT DISTINCT MAX (year) || MAX (month) AS yearmo
INTO koGbmaxyearmo
FROM inquiry.PS_41_TBLORDEREDSERVICES
WHERE provid = '" & provnumber & "'
AND year = (SELECT MAX (year) yearmo
FROM inquiry.PS_41_TBLORDEREDSERVICES
WHERE provid = '" & provnumber & "');

dtkogbmaxyearmo :=
right (koGbmaxyearmo, 2) || '/01' || left (koGbmaxyearmo, 4);
dtkostartingstartdate :=
TRUNC (TRUNC (a_rec.ko_starting_START_DT_USER, 'MM') - 1,
'MM');

IF dtkostartingstartdate <= dtkogbmaxyearmo
THEN
DECLARE
CURSOR a2_cur
IS
SELECT DISTINCT tm.provid AS client_NUMBER,
tm.provname,
qq.ko_total AS ko_total,
qq.ko_starting AS ko_starting,
qq.min_turn AS ko_turn_START_DT,
qq.max_turn ko_turn_END_DT
FROM inquiry.p01_tblmaster tm,
( SELECT ind.provid,
ttt.min_turn,
ttt.max_turn,
TO_CHAR (SUM (ind.total),
'99999999999.00')
AS ko_TOTAL,
CASE
WHEN (TO_CHAR (SUM (ind.TOTAL),
'99999999999.00') =
0)
OR (MONTHS_BETWEEN (
ttt.max_turn,
ttt.min_turn
) = 0)
THEN
0
ELSE
TO_CHAR (SUM (ind.total),
'99999999999.00')
/ (MONTHS_BETWEEN (
ttt.max_turn,
ttt.min_turn
)
+ 1)
END
ko_starting
FROM inquiry.PS_41_TBLORDEREDSERVICES ind,
( SELECT MAX (ind.billing_date)
max_turn,
CASE
WHEN ROUND(MONTHS_BETWEEN (
MAX(ind.billing_date),
MIN(ind.billing_date)
)) < 6
THEN
MIN (
ind.billing_date
)
ELSE
ADD_MONTHS (
MAX(ind.billing_date),
-5
)
END
AS min_turn,
provid
FROM inquiry.PS_41_TBLORDEREDSERVICES ind
WHERE IND.BILLING_DATE <=
(SELECT DISTINCT
ADD_MONTHS (
TRUNC (
TO_DATE (
a_rec.pc_ko_starting_START_DATE,
'mm/dd/yyyy'
),
'MM'
),
-1
)
FROM DUAL)
GROUP BY provid) ttt
WHERE ttt.provid = ind.provid
AND billing_date BETWEEN ttt.min_turn
AND ttt.max_turn
GROUP BY ind.provid,
ttt.min_turn,
ttt.max_turn) qq
WHERE tm.provid = qq.provid
AND tm.provid = a_rec.client_number;

a2_rec a2_cur%ROWTYPE;
OPEN a2_cur;
BEGIN
FETCH a2_cur INTO a2_rec;

EXIT WHEN a2_cur%NOTFOUND;

IF (a2_rec.ko_Total > 0)
THEN
IF (fromjkl = 0)
THEN
UPDATE myschema_REPORTS.CA_starting
SET flag = 'N',
CALC_ko_starting = 'N',
ko_starting_END_DT_USER =
a_rec.ko_starting_END_DATE_USER - 1,
Z_MODIFIED_BY = 'MXA15',
Z_MODIFIED_DATE = SYSDATE
WHERE ca_turn_id = a_rec.ca_turn_id;
commit;

INSERT INTO myschema_REPORTS.CA_starting (
CA_turn_ID,
PROJECT_NUMBER,
jkl_turn_NUMBER_START,
jkl_turn_NUMBER_END,
jkl_starting,
jkl_TOTAL,
client_NUMBER,
CALC_jkl_starting,
CALC_ko_starting,
FLAG,
ko_starting_START_DT,
ko_starting_END_DT,
ko_starting,
ko_TOTAL,
jkl_starting_START_DT_USER,
jkl_starting_END_DT_USER,
ko_starting_START_DT_USER,
ko_starting_END_DT_USER,
ko_PERCENTAGE,
jkl_PERCENTAGE
)
VALUES (myschema.id_generator.NEXTVAL,
a_rec.project_number,
a1_rec.jkl_turn_NUMBER_START,
a1_rec.jkl_turn_NUMBER_END,
a1_rec.jkl_starting,
a1_rec.jkl_total,
a_rec.client_number,
a_rec.calc_jkl_starting,
'N',
'Y',
a_rec.ko_starting_START_DT,
a_rec.ko_starting_END_DT,
a_rec.ko_starting,
a_rec.ko_total,
a_rec.jkl_starting_START_DT_USER,
a_rec.jkl_starting_END_DT_USER,
a_rec.ko_starting_START_DT_USER,
a_rec.ko_starting_END_DT_USER,
a_rec.ko_percentage,
a_rec.jkl_percentage);
commit;
ELSE
--Already new record inserted.. so update the record

UPDATE myschema_REPORTS.CA_starting
SET ko_starting_START_DT =
a_rec.ko_starting_START_DT,
ko_starting_END_DT = a_rec.ko_starting_END_DT,
ko_starting_START_DT_USER =
a_rec.ko_starting_START_DT,
ko_starting_END_DT_USER =
a_rec.ko_starting_END_DT,
ko_starting = a2_rec.ko_starting,
ko_TOTAL = a2_rec.ko_TOTAL,
CALC_ko_starting = 'N',
Z_MODIFIED_BY = 'MXA15',
Z_MODIFIED_DATE = SYSDATE
WHERE ca_turn_id = IdGenerator;
commit;
END IF;
-- ELSE
--Write to log file.
END IF;
END;

-- ELSE
--write to log file. and update the CALC_jkl_starting='Y'

CLOSE a2_cur;
END IF;
END IF;
END;
END LOOP;

CLOSE a_cur;
END;

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

this works fine

/* Formatted on 3/21/2011 2:38:06 PM (QP5 v5.163.1008.3004) */
DECLARE
a INT := 0;
b INT;
BEGIN
IF (a = 0)
THEN
B := 1;
ELSE
B := 3;
END IF;

DBMS_OUTPUT.put_line (b);
END;

but this does not

DECLARE
a INT := 0;
BEGIN
IF (a = 0)
THEN
DECLARE
b INT;
B := 1;
ELSE
DECLARE
b INT;
B := 3;
END IF;

DBMS_OUTPUT.put_line (b);
END;

it-rex

ASKER

Great!!
that works,but before closing
would you please explain that to me

it-rex

ASKER

thanx