Olá Galera,
Estou obtendo um erro bobo aqui que não estou conseguindo ver. Esta dando erro
que eu devo declarar algumas variáveis e as mesmas esta declaradas. O Código
abaixo
DECLARE
t0 number := dbms_utility.get_time;
v_idx number :=1;
type t_rec is record(
SRC_DT_CALL NUMBER,
SRC_TM_CALL VARCHAR2(30),
SRC_CD_BNSS_UNIT VARCHAR2(30),
SRC_CD_CALL_AREA VARCHAR2(30),
SRC_CD_NTWK_ELMT VARCHAR2(30),
SRC_CD_OPRT_ORGN VARCHAR2(30),
SRC_CD_OPRT_DSTN VARCHAR2(30),
SRC_CD_PDSV VARCHAR2(30),
SRC_CD_SBSC VARCHAR2(30),
SRC_CD_PRCN_PLAN VARCHAR2(30),
SRC_CD_TRF_WAY VARCHAR2(30),
SRC_CD_TRF_TYPE VARCHAR2(30),
SRC_CD_TRF_SUB_TYPE VARCHAR2 (30),
SRC_CD_TRF_NTWK VARCHAR2 (30),
SRC_CD_TRF_RMNG_TYPE VARCHAR2 (30),
SRC_CD_TRF_PERD_TYPE VARCHAR2 (30),
SRC_CD_TRF_CHRG_CALL VARCHAR2 (30),
SRC_CD_TRF_LINE_TYPE VARCHAR2 (30),
SRC_CD_TRF_TRMN_PONT VARCHAR2 (30),
SRC_CD_TRF_DROP_CALL VARCHAR2 (30),
SRC_CD_TRF_HOME_ZONE VARCHAR2 (30),
SRC_CD_TRF_TCNL VARCHAR2 (30),
SRC_CD_FIN_BLNC_TYPE VARCHAR2 (30),
SRC_CD_PAY_TYPE VARCHAR2 (30),
SRC_CD_EQPM_MODL VARCHAR2 (30),
SRC_NR_ORGN VARCHAR2 (30),
SRC_NR_DSTN VARCHAR2 (30),
SRC_QT_DRTN_TRFC NUMBER (22),
SRC_QT_CHRG_DRTN_TRFC NUMBER (22),
SRC_QT_UPLD_TRFC NUMBER (22),
SRC_QT_DWLD_TRFC NUMBER (22),
SRC_VL_TRFC NUMBER (22),
SRC_VL_BLNC_USED NUMBER (22),
X_VL_TRFC_UTT NUMBER (22),
X_VL_TRFC_USD NUMBER (22),
V_TIMESTAMP date,
X_CD_GLOB_CALL_REF VARCHAR2 (30),
X_FL_RMNG NUMBER (22),
SRC_CD_TRF_CHRG_TYPE number(22),
X_CD_MCC_ROAM number(22),
X_CD_MNC_ROAM number(22),
X_QT_DRTN_TRFC_FREE NUMBER (22),
X_CD_FAF NUMBER (22),
X_DS_SRC_SYST VARCHAR2 (30),
X_VL_PRVS_BLNC NUMBER (22),
X_VL_BLNC NUMBER (22),
X_QT_TOTL_DATA_TRFC NUMBER (22),
X_CD_SRCE_FILE NUMBER (22),
NSEQ_TRF NUMBER(22),
X_CD_SERV_CLSS NUMBER (22));
type t_rec_array is table of t_rec index by pls_integer;
a_rec t_rec_array;
BEGIN
for r in(SELECT SUBSTR(A.DATA_EVENTO,1,8)AS
SRC_DT_CALL,SUBSTR(A.DATA_EVENTO,9,6)AS SRC_TM_CALL,-1 AS SRC_CD_BNSS_UNIT,-1
AS SRC_CD_CALL_AREA,nvl(ID_CELULA,-1) AS SRC_CD_NTWK_ELMT,
CASE
WHEN DIR_TRAFEGO = 'Outgoing' or large_account is not null then
'63102' -- Angola
WHEN DIR_TRAFEGO = 'Incoming' or DIR_TRAFEGO = 'Forwarding' then
nvl(pais_orig||operador,nvl(ID_TRF_TRMN_PONT,-1))
Else '-1'
END, --SRC_CD_OPRT_ORGN
CASE
WHEN DIR_TRAFEGO = 'Incoming' or DIR_TRAFEGO = 'Forwarding' or
large_account is not null then
'63102' -- Angola
WHEN DIR_TRAFEGO = 'Outgoing' then
nvl(pais_orig||operador,nvl(ID_TRF_TRMN_PONT,-1))
Else '-1'
END , --SRC_CD_OPRT_DSTN
nvl(A.Service_identifier,-1) AS SRC_CD_PDSV,
--NVL(SERVICE_IDENTIFIER,-1)
CASE
WHEN A.DIR_TRAFEGO = 'Outgoing' then substr(A.MSISDN_ORIGEM,4,9)
WHEN A.DIR_TRAFEGO = 'Incoming' then
decode(A.SUB_TIPO_TRAFEGO,'4','-2',substr(A.MSISDN_DESTINO,4,9))
WHEN A.DIR_TRAFEGO = 'Forwarding' then
substr(A.MSISDN_ORIGEM,4,9)
Else '-1'
END AS SRC_CD_SBSC,
nvl(A.servicos,'-1') AS SRC_CD_PRCN_PLAN,
nvl(A.DIR_TRAFEGO,-1) AS SRC_CD_TRF_WAY,
case
when service_identifier = 'Gy_10' then '1'
when SUB_TIPO_TRAFEGO in ('5') then '5'
when service_identifier = 'SCAP_6' then '5'
when SUB_TIPO_TRAFEGO in ('0','1') then '2'
when SUB_TIPO_TRAFEGO in ('2','3') then '3'
when SUB_TIPO_TRAFEGO in ('4') then '1'
when SUB_TIPO_TRAFEGO in ('6','7','8') then '4'
else '-1'
end as SRC_CD_TRF_TYPE ,---SRC_CD_TRF_TYPE
case
when service_identifier = 'Gy_10' then '4'
when service_identifier = 'SCAP_6' then '5'
else nvl(SUB_TIPO_TRAFEGO,-1)
end as SRC_CD_TRF_SUB_TYPE, -----SRC_CD_TRF_SUB_TYPE
nvl( A.ID_TRAFEGO,-1) AS SRC_CD_TRF_NTWK,
-1 AS SRC_CD_TRF_RMNG_TYPE,
nvl(A.ID_PERIODO,3) AS SRC_CD_TRF_PERD_TYPE,
-1 AS SRC_CD_TRF_CHRG_CALL,
-1 AS SRC_CD_TRF_LINE_TYPE,
nvl(E.ID_TRF_TRMN_PONT,-1) AS SRC_CD_TRF_TRMN_PONT,
-1 AS SRC_CD_TRF_DROP_CALL,
-1 AS SRC_CD_TRF_HOME_ZONE,
-1 AS SRC_CD_TRF_TCNL,
case --caso um dos dois seja null -> -1
when B.TIPO_SALDO_USADO is null or B.SERVICE_CLASS is null then
'-1'
when substr(B.TIPO_SALDO_USADO,1,2)='AC' then B.TIPO_SALDO_USADO
else B.TIPO_SALDO_USADO||'_'||B.SERVICE_CLASS
end AS SRC_CD_FIN_BLNC_TYPE,
NVL(D.SRC_CD_PAY_TYPE,-1) AS SRC_CD_PAY_TYPE,
--NVL(B.SERVICE_CLASS,-1),
-1 AS SRC_CD_EQPM_MODL,
nvl(A.MSISDN_ORIGEM,-1) AS SRC_NR_ORGN,
NVL(A.MSISDN_DESTINO,-1) AS SRC_NR_DSTN,
NVL(A.DURACAO_REAL,0) AS SRC_QT_DRTN_TRFC,
nvl(A.DURACAO_TAXADA,0) AS SRC_QT_CHRG_DRTN_TRFC,
0 AS SRC_QT_UPLD_TRFC,
nvl(A.QNT_TRAFEGO_TOTAL,0) AS SRC_QT_DWLD_TRFC,
nvl(ROUND(TO_NUMBER(B.VALOR_TAXADO)*NVL(X_FL_MSRE_TYPE,0)*7.2,5),0) AS
SRC_VL_TRFC,
nvl(ROUND(TO_NUMBER(B.VALOR_TAXADO),6),0) AS SRC_VL_BLNC_USED,
nvl(ROUND(TO_NUMBER(B.VALOR_TAXADO)*NVL(X_FL_MSRE_TYPE,0),5),0)
AS X_VL_TRFC_UTT, -- mail Rui Alves
nvl(ROUND(TO_NUMBER(B.VALOR_TAXADO)*NVL(X_FL_MSRE_TYPE,0)*.06666667,5),0) AS
X_VL_TRFC_USD, -- mail Rui Alves
to_date('20150218','YYYYMMDD') , --TIMESTAMP
NVL(A.GCR,-1) AS X_CD_GLOB_CALL_REF,
case -- identificação de sms em roaming
when A.SUB_TIPO_TRAFEGO ='2' and
A.servicos in ('12', '13', '14', '15', '22', '51', '61',
'62', '63', '64', '65', '117', '118', '119', '120', '121', '122', '123', '124',
'125', '126', '131', '132' , '159')
then '1'
when SUB_TIPO_TRAFEGO ='2' then '0'
else NVL(A.ROAMING,0)
end, --X_FL_RMNG,
NVL(A.TIPO_TAXACAO,-1) AS SRC_CD_TRF_CHRG_TYPE,
nvl(A.pais_orig,-1), -- X_CD_MCC_ROAM --JIRA207,
nvl(A.operador,-1), -- X_CD_MNC_ROAM --JIRA207,
nvl(A.DURACAO_TAXADA_ZERO,0) AS X_QT_DRTN_TRFC_FREE,
nvl(A.FAF,-1) AS X_CD_FAF,
nvl(A.FONTE_ID,-1) AS X_DS_SRC_SYST,
nvl(TO_NUMBER(B.VALOR_SALDO_ANTERIOR),0) X_VL_PRVS_BLNC,
nvl(TO_NUMBER(B.VALOR_SALDO_ACTUAL),0) X_VL_BLNC,
nvl(QNT_TRAFEGO_TOTAL,0) X_QT_TOTL_DATA_TRFC,
A.NFILE_ID AS X_CD_SRCE_FILE
,A.NSEQ_TRF --JIRA164
,NVL (TO_NUMBER(B.SERVICE_CLASS),-1) --JIRA192 X_CD_SERV_CLSS
FROM (select decode(DIR_TRAFEGO,'Incoming',MSISDN_ORIGEM,MSISDN_DESTINO)
NR_TRMN_PONT,const.*
from DW_ETL.TMP_STG_MME_TRAFEGO_TAX_CONST const
where
( sub_tipo_trafego in ('0','1','2','3','4')
or
service_identifier in ('Gy_10')
)
and
(service_identifier not in ('SCAP_6') or
service_identifier is null)
) A,
DW_ETL.TMP_STG_MME_TRAFEGO_TAX_VAR B,
DW_ETL.STG_MME_BLNC_TYPE C,
(select SRC_CD_PAY_TYPE from DW_STAGE.src_sbsc
partition(day_20150218) group by SRC_CD_PAY_TYPE ) D,
DW_ETL.tmp_trf_trmn_pont E,
(select cd_srce_sytm as large_account from
dw.ref_trf_trmn_pont where ds_trmn_detl = 'Móvel (Intra-rede)') F
WHERE A.ROW_ID_STG=B.ROW_ID_STG
AND B.SERVICE_CLASS=D.SRC_CD_PAY_TYPE (+)
AND B.TIPO_SALDO_USADO=C.X_DS_ACR_FIN_BLNC_TYPE (+)
AND B.SERVICE_CLASS=C.X_ID_SRVC_CLASS (+)
AND A.NR_TRMN_PONT=E.MSISDN_DESTINO(+)
--AND A.MSISDN_DESTINO=E.MSISDN_DESTINO(+)
AND E.ID_TRF_TRMN_PONT=F.large_account(+))
loop
a_rec(v_idx).SRC_DT_CALL := r.SRC_DT_CALL;
a_rec(v_idx).SRC_TM_CALL := r.SRC_TM_CALL;
a_rec(v_idx).SRC_CD_BNSS_UNIT := r.SRC_CD_BNSS_UNIT;
a_rec(v_idx).SRC_CD_CALL_AREA := r.SRC_CD_CALL_AREA;
a_rec(v_idx).SRC_CD_NTWK_ELMT := r.SRC_CD_NTWK_ELMT;
a_rec(v_idx).SRC_CD_OPRT_ORGN := r.SRC_CD_OPRT_ORGN;
a_rec(v_idx).SRC_CD_OPRT_DSTN := r.SRC_CD_OPRT_DSTN;
a_rec(v_idx).SRC_CD_PDSV := r.SRC_CD_PDSV;
a_rec(v_idx).SRC_CD_SBSC := r.SRC_CD_SBSC;
a_rec(v_idx).SRC_CD_PRCN_PLAN := r.SRC_CD_PRCN_PLAN;
a_rec(v_idx).SRC_CD_TRF_WAY := r.SRC_CD_TRF_WAY;
a_rec(v_idx).SRC_CD_TRF_TYPE := r.SRC_CD_TRF_TYPE;
a_rec(v_idx).SRC_CD_TRF_SUB_TYPE := r.SRC_CD_TRF_SUB_TYPE;
a_rec(v_idx).SRC_CD_TRF_NTWK := r.SRC_CD_TRF_NTWK;
a_rec(v_idx).SRC_CD_TRF_RMNG_TYPE := r.SRC_CD_TRF_RMNG_TYPE;
a_rec(v_idx).SRC_CD_TRF_CHRG_CALL := r.SRC_CD_TRF_CHRG_CALL;
a_rec(v_idx).SRC_CD_TRF_LINE_TYPE := r.SRC_CD_TRF_LINE_TYPE;
a_rec(v_idx).SRC_CD_TRF_TRMN_PONT := r.SRC_CD_TRF_TRMN_PONT;
a_rec(v_idx).SRC_CD_TRF_DROP_CALL := r.SRC_CD_TRF_DROP_CALL;
a_rec(v_idx).SRC_CD_TRF_HOME_ZONE := r.SRC_CD_TRF_HOME_ZONE;
a_rec(v_idx).SRC_CD_TRF_TCNL := r.SRC_CD_TRF_TCNL;
a_rec(v_idx).SRC_CD_FIN_BLNC_TYPE := r.SRC_CD_FIN_BLNC_TYPE;
a_rec(v_idx).SRC_CD_PAY_TYPE := r.SRC_CD_PAY_TYPE;
a_rec(v_idx).SRC_CD_EQPM_MODL := r.SRC_CD_EQPM_MODL;
a_rec(v_idx).SRC_NR_ORGN := r.SRC_NR_ORGN;
a_rec(v_idx).SRC_NR_DSTN := r.SRC_NR_DSTN;
a_rec(v_idx).SRC_QT_DRTN_TRFC := r.SRC_QT_DRTN_TRFC;
a_rec(v_idx).SRC_QT_CHRG_DRTN_TRFC := r.SRC_QT_CHRG_DRTN_TRFC;
a_rec(v_idx).SRC_QT_UPLD_TRFC := r.SRC_QT_UPLD_TRFC;
a_rec(v_idx).SRC_QT_DWLD_TRFC := r.SRC_QT_DWLD_TRFC;
a_rec(v_idx).SRC_VL_TRFC := r.SRC_VL_TRFC;
a_rec(v_idx).SRC_VL_BLNC_USED := r.SRC_VL_BLNC_USED;
a_rec(v_idx).X_VL_TRFC_UTT := r.X_VL_TRFC_UTT;
a_rec(v_idx).X_VL_TRFC_USD := r.X_VL_TRFC_USD;
a_rec(v_idx).V_TIMESTAMP := r.V_TIMESTAMP;
a_rec(v_idx).X_CD_GLOB_CALL_REF := r.X_CD_GLOB_CALL_REF;
a_rec(v_idx).X_FL_RMNG := r.X_FL_RMNG;
a_rec(v_idx).SRC_CD_TRF_CHRG_TYPE := r.SRC_CD_TRF_CHRG_TYPE;
a_rec(v_idx).X_CD_MCC_ROAM := r.X_CD_MCC_ROAM;
a_rec(v_idx).X_CD_MNC_ROAM := r.X_CD_MNC_ROAM;
a_rec(v_idx).X_QT_DRTN_TRFC_FREE := r.X_QT_DRTN_TRFC_FREE;
a_rec(v_idx).X_CD_FAF := r.X_CD_FAF;
a_rec(v_idx).X_DS_SRC_SYST := r.X_DS_SRC_SYST;
a_rec(v_idx).X_VL_PRVS_BLNC := r.X_VL_PRVS_BLNC;
a_rec(v_idx).X_VL_BLNC := r.X_VL_BLNC;
a_rec(v_idx).X_QT_TOTL_DATA_TRFC := r.X_QT_TOTL_DATA_TRFC;
a_rec(v_idx).X_CD_SRCE_FILE := r.X_CD_SRCE_FILE;
a_rec(v_idx).NSEQ_TRF := r.NSEQ_TRF;
a_rec(v_idx).X_CD_SERV_CLSS := r.X_CD_SERV_CLSS;
v_idx := v_idx + 1;
if (mod(v_idx, 100) = 0) then
forall i in a_rec.first .. a_rec.last
insert into T_LUIZ_SRC_TRF_BLNC_TYPE_DETL values a_rec(i);
a_rec.delete;
v_idx := 1;
end if;
end loop;
if a_rec.first is not null then
forall i in a_rec.first .. a_rec.last
insert into T_LUIZ_SRC_TRF_BLNC_TYPE_DETL values a_rec(i);
end if;
commit;
dbms_output.put_line('Tempo: ' || ((dbms_utility.get_time - t0) / 100) ||
' segundos');
end;
O ERRO APRESENTADO:
ORA-06550: line 172, column 44:
PLS-00302: component 'SRC_CD_OPRT_ORGN' must be declared
ORA-06550: line 172, column 9:
PL/SQL: Statement ignored
ORA-06550: line 173, column 44:
PLS-00302: component 'SRC_CD_OPRT_DSTN' must be declared
ORA-06550: line 173, column 9:
PL/SQL: Statement ignored
ORA-06550: line 201, column 39:
PLS-00302: component 'V_TIMESTAMP' must be declared
ORA-06550: line 201, column 9:
PL/SQL: Statement ignored
ORA-06550: line 203, column 37:
PLS-00302: component 'X_FL_RMNG' must be declared
ORA-06550: line 203, column 9:
PL/SQL: Statement ignored
ORA-06550: line 205, column 41:
PLS-00302: component 'X_CD_MCC_ROAM' must be declared
ORA-06550: line 205, column 9:
PL/SQL: Statement ignored
ORA-06550: line 206, column 41:
PLS-00302: component 'X_CD_MNC_ROAM' must be declared
ORA-06550: line 206, column 9:
PL/SQL: Statement ignored
ORA-06550: line 215, column 42:
PLS-00302: component 'X_CD_SERV_CLSS' must be declared
ORA-06550: line 215, column 9:
PL/SQL: Statement ignored
ORA-06550: line 220,
Agradeço se alguém puder me ajudar.
Obrigado
Luiz Marinho
