Hi Friends,
I wrote this procedure and dba givena ll right to execute this...
But i am getting some compilation errors as below.. Please help me..where this is wrong..
create or replace procedure g_ring
( p_log_level IN NUMBER,
smode IN NUMBER,
o_cursor OUT sys_refcursor
) is
c_PKG_NAME CONSTANT VARCHAR2(18):='g_ring';
g_target_table tm100_proces_log.swm100_target_tab_n%TYPE := '';
g_log_level NUMBER(1);
g_rec_cnt tm100_proces_log.swm100_rec_cnt_t%TYPE := 0;
g_msg tm100_proces_log.swm100_message_x%TYPE := '';
g_proc_name tm202_mail_cntl.swm202_process_n%TYPE:='';
g_pgm tm100_proces_log.swm100_pgm_name_n%TYPE :='';
g_target_index VARCHAR2(30) := NULL;
g_err_cd NUMBER := 0;
g_err_msg VARCHAR2(2000) := NULL;
v_target_index VARCHAR2(30) := NULL;
v_err_msg VARCHAR2(1000) := NULL;
v_proc_name tm202_mail_cntl.swm202_process_n%TYPE := 'g_ring';
v_err_cd NUMBER := 0;
e_ddl_error EXCEPTION;
BEGIN
DBMS_OUTPUT.PUT_LINE('START OF '|| to_char(sysdate,'dd-MON-yyyy hh24:mm:ss'));
g_proc_name := 'decoder_ring';
g_pgm := c_PKG_NAME || '.'||g_proc_name;
g_target_table := 'tp270_supplr';
wr_common_pkg.add_process_log_rec (g_target_table, g_pgm, 3,0, 'g_ring');
COMMIT;
g_msg := 'SELECTING ALL SUPPLIER CODE, GROUP SITE CODE FROM tp270_supplr';
wr_common_pkg.add_process_log_rec ('tp270_supplr', g_pgm, 3,
0, g_msg);
COMMIT;
OPEN o_cursor FOR
SELECT DISTINCT
CASE
WHEN step3.swm018_group_site_c < 80000 AND smode=1 THEN Step3.swm018_group_site_c
ELSE step3.SWM018_GROUP_SITE_C
END
Group_Site_Code,
step3.CD_SUPPLR_SITE Supplier_Code,
step3.NA_SITE_NAME Supplier_Name,
step3.cd_parent_company Parent_Code,
step3.NA_SUPPLR_PARENT Parent_Name
FROM (SELECT DISTINCT Step2.swm018_group_site_c,
Step2.CD_SUPPLR_SITE,
Step2.NA_SITE_NAME,
Step2.cd_parent_company,
Step2b.NA_SUPPLR_PARENT
FROM (SELECT DISTINCT step1.swm018_group_site_c,
wr.tahp271_site.CD_SUPPLR_SITE,
wr.tahp271_site.NA_SITE_NAME,
wr.tahp271_site.cd_parent_company
FROM (SELECT swm005_supplier_site_c,
swm018_group_site_c
FROM wr.tm019_grpsupsite
WHERE swm021_rec_type_r = 5) step1
LEFT JOIN
wr.tahp271_site
ON step1.swm005_supplier_site_c =
wr.tahp271_site.CD_SUPPLR_SITE
WHERE (wr.tahp271_site.CD_SUPPLR_SITE) IS NOT NULL) Step2
LEFT JOIN
(SELECT DISTINCT
wr.tp270_supplr.cd_parent_company,
wr.tp270_supplr.NA_SUPPLR_PARENT,
wr.tp270_supplr.IN_INSIDE_SUPPLR
FROM wr.tp270_supplr) step2b
ON Step2.cd_parent_company = Step2b.cd_parent_company) step3
order by Group_Site_Code;
wr_ddl_pkg.analyze_tbl (g_proc_name,
'tp270_supplr',
'Y',
'ESTIMATE',
g_err_cd,
g_err_msg);
IF g_err_msg IS NOT NULL
THEN
RAISE e_ddl_error;
END IF;
EXCEPTION
WHEN e_ddl_error THEN
DBMS_OUTPUT.PUT_LINE('There is a DDL error!');
IF p_log_level <= 5
THEN
g_target_table := 'tp270_supplr';
g_log_level := 5;
g_rec_cnt := 0;
g_msg := 'There is a ddl error !'|| g_err_msg;
wr_common_pkg.add_process_log_rec (g_target_table, g_pgm, g_log_level,
g_rec_cnt, g_msg);
COMMIT;
END IF;
COMMIT;
IF p_log_level <= 2
THEN
g_target_table := 'tp270_supplr';
g_log_level := 2;
g_rec_cnt := 0;
g_msg := 'grpsite_decoder_ring ended UNSUCCESSFULLY';
wr_common_pkg.add_process_log_rec (g_target_table, g_pgm, g_log_level,
g_rec_cnt, g_msg);
COMMIT;
DBMS_OUTPUT.PUT_LINE(g_msg||to_char(sysdate,'dd-MON-yyyy hh24:mm:ss'));
END IF;
DBMS_OUTPUT.PUT_LINE('grpsite_decoder_ring ended UNSUCCESSFULLY '||to_char(sysdate,'dd-MON-yyyy hh24:mm:ss'));
WHEN OTHERS THEN
g_target_table := 'tp270_supplr';
g_log_level := 5;
g_msg := SQLCODE||' '||SQLERRM;
wr_common_pkg.add_process_log_rec (g_target_table, g_pgm,
g_log_level,
g_rec_cnt, g_msg);
COMMIT;
end g_ring;
when i compile it gives.. error like
LINE/COL ERROR
-------- ---------------------------------------------------------------
17/28 PLS-00201: identifier 'TM100_PROCES_LOG' must be declared
17/28 PL/SQL: Item ignored
19/28 PLS-00201: identifier 'TM100_PROCES_LOG' must be declared
19/28 PL/SQL: Item ignored
20/28 PLS-00201: identifier 'TM100_PROCES_LOG' must be declared
20/28 PL/SQL: Item ignored
21/28 PLS-00201: identifier 'TM202_MAIL_CNTL' must be declared
21/28 PL/SQL: Item ignored
22/28 PLS-00201: identifier 'TM100_PROCES_LOG' must be declared
22/28 PL/SQL: Item ignored
28/22 PLS-00201: identifier 'TM202_MAIL_CNTL' must be declared
LINE/COL ERROR
-------- ---------------------------------------------------------------
28/22 PL/SQL: Item ignored
33/5 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
33/5 PL/SQL: Statement ignored
34/5 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
34/5 PL/SQL: Statement ignored
35/5 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
LINE/COL ERROR
-------- ---------------------------------------------------------------
35/5 PL/SQL: Statement ignored
36/5 PL/SQL: Statement ignored
36/41 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
any suggestion
prasanth