toblerone65
MIS
Hello I have a created a store proc on a simple store proc, but i get error , please help
CREATE OR REPLACE PROCEDURE p_procedure2 IS
BEGIN
Select
PROVIDER.PROV_ID_NBR,
IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_USG_CD_SK,
PROVIDER.PROV_FULL_NM,
IDSXREF.PROV_TYP_SPCL_XREF.PROV_TYP_CD
FROM
(Select * from
IDS.PROVIDER @ods_co.BSCA.EDS.COM
WHERE IDS.PROVIDER.SS_CD = 'CAPS'
AND (
IDS.PROVIDER.ODS_INSRT_DT <= trunc(sysdate,'MM')-1
OR
IDS.PROVIDER.ODS_UPDT_DT <= trunc(sysdate,'MM')-1
)) PROVIDER
INNER JOIN IDS.PROVIDER_TAX_IDENTIFIER @ods_co.BSCA.EDS.COM ON (PROVIDER.PROV_SK=IDS.PROVIDER_TAX_IDENTIFIER.PROV_SK)
and IDS.PROVIDER_TAX_IDENTIFIER.SS_CD = 'CAPS'
INNER JOIN (Select MAX(TAX_ID_UPDT_DT)LTIME,PROV_SK from
IDS.PROVIDER_TAX_IDENTIFIER @ods_co.BSCA.EDS.COM
WHERE IDS.PROVIDER_TAX_IDENTIFIER.SS_CD = 'CAPS'
GROUP BY PROV_SK
) L on IDS.PROVIDER_TAX_IDENTIFIER.PROV_SK = L.PROV_SK and IDS.PROVIDER_TAX_IDENTIFIER.TAX_ID_UPDT_DT = L.LTIME
LEFT OUTER JOIN IDS.PROVIDER_EMAIL @ods_co.BSCA.EDS.COM ON (PROVIDER.PROV_SK = IDS.PROVIDER_EMAIL.PROV_SK)
and IDS.PROVIDER_EMAIL.SS_CD = 'CAPS'
INNER JOIN IDS.PROVIDER_TYPE_SPECIALTY @ods_co.BSCA.EDS.COM on (PROVIDER.PROV_SK=IDS.PROVIDER_TYPE_SPECIALTY.PROV_SK)
and IDS.PROVIDER_TYPE_SPECIALTY.SS_CD = 'CAPS'
AND IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_USG_CD_SK = ( select cd_map_sk from com.cd_map@ods_co.BSCA.EDS.COM where SDN = 'PROVIDERROVIDER SPECIALITY USAGE'
AND SRC_CD='0' AND PROV_TYP_SPCL_END_DT IS NULL )
LEFT OUTER JOIN IDSXREF.PROV_TYP_SPCL_XREF @ods_co.BSCA.EDS.COM ON (IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_SK=IDSXREF.PROV_TYP_SPCL_XREF.PROV_TYP_SPCL_SK)
and IDSXREF.PROV_TYP_SPCL_XREF.SS_CD = 'CAPS'
WHERE
TAX_ID_UPDT_DT IS NOT NULL
;
END p_procedure2;
CREATE OR REPLACE PROCEDURE p_procedure2 IS
BEGIN
Select
PROVIDER.PROV_ID_NBR,
IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_USG_CD_SK,
PROVIDER.PROV_FULL_NM,
IDSXREF.PROV_TYP_SPCL_XREF.PROV_TYP_CD
FROM
(Select * from
IDS.PROVIDER @ods_co.BSCA.EDS.COM
WHERE IDS.PROVIDER.SS_CD = 'CAPS'
AND (
IDS.PROVIDER.ODS_INSRT_DT <= trunc(sysdate,'MM')-1
OR
IDS.PROVIDER.ODS_UPDT_DT <= trunc(sysdate,'MM')-1
)) PROVIDER
INNER JOIN IDS.PROVIDER_TAX_IDENTIFIER @ods_co.BSCA.EDS.COM ON (PROVIDER.PROV_SK=IDS.PROVIDER_TAX_IDENTIFIER.PROV_SK)
and IDS.PROVIDER_TAX_IDENTIFIER.SS_CD = 'CAPS'
INNER JOIN (Select MAX(TAX_ID_UPDT_DT)LTIME,PROV_SK from
IDS.PROVIDER_TAX_IDENTIFIER @ods_co.BSCA.EDS.COM
WHERE IDS.PROVIDER_TAX_IDENTIFIER.SS_CD = 'CAPS'
GROUP BY PROV_SK
) L on IDS.PROVIDER_TAX_IDENTIFIER.PROV_SK = L.PROV_SK and IDS.PROVIDER_TAX_IDENTIFIER.TAX_ID_UPDT_DT = L.LTIME
LEFT OUTER JOIN IDS.PROVIDER_EMAIL @ods_co.BSCA.EDS.COM ON (PROVIDER.PROV_SK = IDS.PROVIDER_EMAIL.PROV_SK)
and IDS.PROVIDER_EMAIL.SS_CD = 'CAPS'
INNER JOIN IDS.PROVIDER_TYPE_SPECIALTY @ods_co.BSCA.EDS.COM on (PROVIDER.PROV_SK=IDS.PROVIDER_TYPE_SPECIALTY.PROV_SK)
and IDS.PROVIDER_TYPE_SPECIALTY.SS_CD = 'CAPS'
AND IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_USG_CD_SK = ( select cd_map_sk from com.cd_map@ods_co.BSCA.EDS.COM where SDN = 'PROVIDERROVIDER SPECIALITY USAGE'
AND SRC_CD='0' AND PROV_TYP_SPCL_END_DT IS NULL )
LEFT OUTER JOIN IDSXREF.PROV_TYP_SPCL_XREF @ods_co.BSCA.EDS.COM ON (IDS.PROVIDER_TYPE_SPECIALTY.PROV_TYP_SPCL_SK=IDSXREF.PROV_TYP_SPCL_XREF.PROV_TYP_SPCL_SK)
and IDSXREF.PROV_TYP_SPCL_XREF.SS_CD = 'CAPS'
WHERE
TAX_ID_UPDT_DT IS NOT NULL
;
END p_procedure2;