hi
below is stored procedure takes 5 to 10 minuute to execute..
i want to make this fast...below is code......
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
exec USp_Reconciliation 'RECONCILIATION'
ALTER PROCEDURE USp_Reconciliation
(
@.CommandType varchar(50)=null,
@.fileFormatId numeric =null,
@.FDate varchar(10)=null,
@.TDate varchar(10)=null
)
AS
BEGIN
IF @.CommandType='RECONCILIATION'
BEGIN
DECLARE @.DataFormat_Name VARCHAR(50)
DECLARE @.DataFormat_ID NUMERIC
DECLARE @.Folio_No VARCHAR(50)
DECLARE @.Scheme_Code VARCHAR(50)
DECLARE @.SchemeGroup VARCHAR(50)
DECLARE @.Amount NUMERIC
DECLARE @.Cheque_No VARCHAR(50)
DECLARE @.Compare_Status VARCHAR(50)
DECLARE @.Tradedate DATETIME
DECLARE @.Sip_Status CHAR(1)
DECLARE @.DrCr CHAR(1)
DECLARE @.Format_ID NUMERIC
Blocks for Mutiple equal countExact 'Mutiple Records' updated to 'Success' //for Instrm_no and Cheque_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
into #MultipleChequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)
--Select * from #MultipleChequeno where format_id=82
--//For Folio_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name
into #Multiplefoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)
//For Channels User_Trxn_no for Multiple Records
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
into #Multiple_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)
-- Update CAMS files-
--
For IBank CMS
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultipleChequeno.cams_upload_Details_id from #MultipleChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultipleChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultipleChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultipleChequeno.additionalfield6 and
#MultipleChequeno.format_id in ('82'))
--For HDFC CMS
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultipleChequeno.cams_upload_Details_id from #MultipleChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultipleChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultipleChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultipleChequeno.additionalfield13 and
#MultipleChequeno.format_id in ('83'))
--IBANK SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('85','86'))
--BJ SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='EC' and
#Multiplefoliono.format_id in ('88','89'))
--HDFC SI-
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('90','91'))
--CHANNEL--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiple_user_trxnno.cams_upload_Details_id from #Multiple_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Multiple_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiple_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiple_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiple_user_trxnno.scheme_code and
#Multiple_user_trxnno.format_id in ('98'))
--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Multiplefoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.format_id in ('99'))
-- Update CMS files-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplechequeno.cms_upload_Details_id from #Multiplechequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplechequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplechequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#Multiplechequeno.additionalfield6 and
#Multiplechequeno.format_id in ('82'))
-HDFC-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplechequeno.cms_upload_Details_id from #Multiplechequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplechequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplechequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield13=#Multiplechequeno.additionalfield13 and
#Multiplechequeno.format_id in ('83'))
IBANK SI-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('85','86'))
-BJ SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='EC' and
#Multiplefoliono.format_id in ('88','89'))
HDFC SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('90','91'))
--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiple_user_trxnno.cms_upload_Details_id from #Multiple_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Multiple_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiple_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiple_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiple_user_trxnno.scheme_code and
#Multiple_user_trxnno.format_id in ('98'))
--RTGS--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplefoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.format_id in ('99'))
drop table #MultipleChequeno
drop table #Multiplefoliono
drop table #Multiple_user_trxnno
Exact 'Pending' to pending updated to 'Success'Blocks for Pending equal count
--//for Instrm_no and Cheque_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
into #PendingChequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no) and count(cams.instrm_no)>1
--//For Folio_no Exact 'Pending' updated to 'Success
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name
into #Pendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no) and count(cams.Folio_no)>1
//For Channels User_Trxn_no for 'Pending'-- Exact 'Pending' updated to 'Success
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
into #Pending_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2) and count(cams.user_trxnno)>1
-- Update CAMS files-
--
--IBANK--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #PendingChequeno.cams_upload_Details_id from #PendingChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#PendingChequeno.additionalfield6 and
#PendingChequeno.format_id in ('82'))
--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #PendingChequeno.cams_upload_Details_id from #PendingChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#PendingChequeno.additionalfield13 and
#PendingChequeno.format_id in ('83'))
--IBANK SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('85','86'))
BJ SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='EC' and
#Pendingfoliono.format_id in ('88','89'))
HDFC SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('90','91'))
--Channel
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pending_user_trxnno.cams_upload_Details_id from #Pending_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Pending_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pending_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pending_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pending_user_trxnno.scheme_code and
#Pending_user_trxnno.format_id in ('98'))
--RTGS-
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Pendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.format_id in ('99'))
-- Update CMS files-pending to pending --
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #PendingChequeno.cms_upload_Details_id from #PendingChequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#PendingChequeno.additionalfield6 and
#PendingChequeno.format_id in ('82'))
--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #PendingChequeno.cms_upload_Details_id from #PendingChequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield13=#PendingChequeno.additionalfield13 and
#PendingChequeno.format_id in ('83'))
--IBANK SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('85','86'))
--BJ SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='EC' and
#Pendingfoliono.format_id in ('88','89'))
--HDFC SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('90','91'))
--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pending_user_trxnno.cms_upload_Details_id from #Pending_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Pending_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pending_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pending_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pending_user_trxnno.scheme_code and
#Pending_user_trxnno.format_id in ('98'))
--RTGS-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Pendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.format_id in ('99'))
drop table #PendingChequeno
drop table #Pendingfoliono
drop table #Pending_user_trxnno
For Updating the Exact count 'Redundant' to Success'
--//for Instrm_no and Cheque_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #Redundantchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)
--//For Folio_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #Redundantfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)
//For Channels User_Trxn_no for 'Pending'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #Redundant_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)
-- Update CAMS filesREDUNDANT to REDUNDANT-
--
--IBANK
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantchequeno.cams_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_slip_No=#Redundantchequeno.additionalfield6 and
#Redundantchequeno.format_id in ('82'))
-- HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantchequeno.cams_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_slip_No=#Redundantchequeno.additionalfield13 and
#Redundantchequeno.format_id in ('83'))
--IBANK SI --
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('85','86'))
--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='EC' and
#Redundantfoliono.format_id in ('88','89'))
--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
pruamc.Tbl_CAMS_UploadDetails.name=#Redundantfoliono.additionalfield2 and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('90','91'))
--CHANNEL--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundant_user_trxnno.cams_upload_Details_id from #Redundant_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Redundant_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundant_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundant_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundant_user_trxnno.scheme_code and
#Redundant_user_trxnno.format_id in ('98'))
--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.format_id in ('99'))
-- Update CMS filesREDUNDANT to REDUNDANT-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantchequeno.cms_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#Redundantchequeno.additionalfield6 and
#Redundantchequeno.format_id in ('82'))
--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantchequeno.cms_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#Redundantchequeno.additionalfield13 and
#Redundantchequeno.format_id in ('83'))
--IBANK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('85','86'))
--BJ SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='EC' and
#Redundantfoliono.format_id in ('88','89'))
--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('90','91'))
--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundant_user_trxnno.cms_upload_Details_id from #Redundant_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Redundant_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundant_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundant_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundant_user_trxnno.scheme_code and
#Redundant_user_trxnno.format_id in ('98'))
--RTGS--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.format_id in ('99'))
drop table #Redundantchequeno
drop table #Redundantfoliono
drop table #Redundant_user_trxnno
-- Update CAMS filesREDUNDANT to PENDING-
--
-for Channels exact enter of 'Pending' in CMS and 'Redundant' in Purchase
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #RedundantchequenoChannel from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending' and cms.format_id='98'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)
--Select * from #RedundantchequenoChannel
--CHANNEL-- CAMS UPDATE
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantchequenoChannel.cams_upload_Details_id from #RedundantchequenoChannel join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#RedundantchequenoChannel.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantchequenoChannel.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantchequenoChannel.scheme_code and
#RedundantchequenoChannel.format_id in ('98'))
--CHANNEL-- CMS UPDATE
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantchequenoChannel.cms_upload_Details_id from #RedundantchequenoChannel join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#RedundantchequenoChannel.user_trxnno
where --pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantchequenoChannel.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantchequenoChannel.scheme_code and
#RedundantchequenoChannel.format_id in ('98'))
For Updating the Exact count 'Redundant' in Purchase and 'Pending' in CMS to Success'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #RedundantPendingchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)
--//For exact count of 'Pending' in CMS and 'Redundant' in Purchase for CMS Banks on folio_no basis
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #RedundantPendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)
--IBANK--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingchequeno.cams_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#RedundantPendingchequeno.additionalfield6 and
#RedundantPendingchequeno.format_id in ('82'))
--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingchequeno.cams_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#RedundantPendingchequeno.additionalfield13 and
#RedundantPendingchequeno.format_id in ('83'))
--IBNAK SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('85','86'))
--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='EC' and
#RedundantPendingfoliono.format_id in ('88','89'))
--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
pruamc.Tbl_CAMS_UploadDetails.name=#RedundantPendingfoliono.additionalfield2 and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('90','91'))
--RTGS
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.format_id in ('99'))
-- Update CMS filesREDUNDANT to PENDING-
--
--IBANK
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingchequeno.cms_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield6=#RedundantPendingchequeno.additionalfield6 and
#RedundantPendingchequeno.format_id in ('82'))
--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingchequeno.cms_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#RedundantPendingchequeno.additionalfield13 and
#RedundantPendingchequeno.format_id in ('83'))
--IBNAK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('85','86'))
--BJ SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='EC' and
#RedundantPendingfoliono.format_id in ('88','89'))
--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('90','91'))
--RTGS --
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.format_id in ('99'))
drop table #RedundantPendingchequeno
drop table #RedundantPendingfoliono
drop table #RedundantchequenoChannel
-- Update FOR CHANNEL filesMULTIPLE to PENDING-
--
-for Channels exact enter of 'Pending' in CMS and 'Multiple Records' in Purchase
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #MultiplechequenoChannel from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending' and cms.format_id='98'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)
-- CHANNEL--CAMS UPDATE
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplechequenoChannel.cams_upload_Details_id from #MultiplechequenoChannel join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#MultiplechequenoChannel.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplechequenoChannel.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplechequenoChannel.scheme_code and
#MultiplechequenoChannel.format_id in ('98'))
-- CHANNEL--CMS UPDATE
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplechequenoChannel.cms_upload_Details_id from #MultiplechequenoChannel join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#MultiplechequenoChannel.user_trxnno
where --pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplechequenoChannel.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplechequenoChannel.scheme_code and
#MultiplechequenoChannel.format_id in ('98'))
-- Update CAMS filesMULTIPLE to PENDING-
--
For Updating the Exact count 'Multiple Records' in Purchase and 'Pending' in CMS to Success'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #MultiplePendingchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)
--//For exact count of 'Pending' in CMS and 'Multiple Records' in Purchase for CMS Banks on folio_no basis
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #MultiplePendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)
--IBANK--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingchequeno.cams_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingchequeno.cams_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultiplePendingchequeno.additionalfield6 and
#MultiplePendingchequeno.format_id in ('82'))
--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingchequeno.cams_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingchequeno.cams_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultiplePendingchequeno.additionalfield13 and
#MultiplePendingchequeno.format_id in ('83'))
--IBANK SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('85','86'))
--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='EC' and
#MultiplePendingfoliono.format_id in ('88','89'))
--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('90','91'))
--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.format_id in ('99'))
-- Update CMS filesMULTIPLE to PENDING-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingchequeno.cms_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield6=#MultiplePendingchequeno.additionalfield6 and
#MultiplePendingchequeno.format_id in ('82'))
--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingchequeno.cms_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#MultiplePendingchequeno.additionalfield13 and
#MultiplePendingchequeno.format_id in ('83'))
--IBANK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('85','86'))
--BJ SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='EC' and
#MultiplePendingfoliono.format_id in ('88','89'))
--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('90','91'))
--RTGS --
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.format_id in ('99'))
drop table #MultiplePendingchequeno
drop table #MultiplePendingfoliono
drop table #MultiplechequenoChannel
DECLARE CUR_DATAFORMAT CURSOR FOR
SELECT DataFormat_ID,DataFormat_Name FROM Tbl_DataFormat WITH(NOLOCK) WHERE DataFormat_Isactive =1 AND DataFormat_Name <>'CAMS'
OPEN CUR_DATAFORMAT
FETCH NEXT FROM CUR_DATAFORMAT INTO @.DataFormat_ID,@.DataFormat_Name
WHILE @.@.FETCH_STATUS =0
BEGIN
IF @.DataFormat_Name ='IBANK'
Begin
proper query for updating success record in purchase file
select instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessIBANK from tbl_cams_uploaddetails WITH(NOLOCK)
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
cams_schemegroup=cms_schemegroup --and
--tbl_cams_uploaddetails.instrm_date=tbl_cms_uploaddetails.instrm_date --'Added Gopal feb 07 new criteria
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and tbl_cms_uploaddetails.Format_ID=82 and
--Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploaddetails.additionalfield6 and
Payment_Mechanism<>'EC' and Payment_Mechanism<>'M' and Payment_Mechanism <> 'TR'
group by instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
having count(instrm_no) = 1
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANK
where tbl_cams_uploaddetails.instrm_no=#SuccessIBANK.instrm_no and
tbl_cams_uploaddetails.amount=#SuccessIBANK.amount and
tbl_cams_uploaddetails.cams_schemegroup=#SuccessIBANK.cams_schemegroup and
tbl_cams_uploaddetails.payin_slip_no=#SuccessIBANK.payin_slip_no and
--tbl_cams_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cams_uploaddetails.compare_status='Pending'
proper query for updating success record in bank file
print 'g6'
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success', ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANK
where tbl_cms_uploaddetails.cheque_no=#SuccessIBANK.instrm_no and
tbl_cms_uploaddetails.amount=#SuccessIBANK.amount and
tbl_cms_uploaddetails.cms_schemegroup=#SuccessIBANK.cams_schemegroup and
tbl_cms_uploaddetails.additionalfield6=#SuccessIBANK.payin_slip_no and
--tbl_cms_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cms_uploaddetails.compare_status='Pending'and tbl_cms_uploaddetails.Format_ID=82
drop table #SuccessIBANK
DD for IBank
select instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessIBANKDD from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
cams_schemegroup=cms_schemegroup --and
--tbl_cams_uploaddetails.instrm_date=tbl_cms_uploaddetails.instrm_date --'Added Gopal feb 07 new criteria
where tbl_cams_uploaddetails.compare_status='Pending' and
(0.98 * tbl_cams_uploaddetails.amount <= tbl_cms_uploaddetails.amount
and tbl_cams_uploaddetails.amount >= tbl_cms_uploaddetails.amount)
and tbl_cms_uploaddetails.compare_status='Pending' and tbl_cms_uploaddetails.Format_ID=82 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploaddetails.additionalfield6 and
Payment_Mechanism in ('D','P')
group by instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
having count(instrm_no) = 1
--
-After chnage as on 6th march
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success', ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANKDD
where tbl_cms_uploaddetails.cheque_no=#SuccessIBANKDD.instrm_no and
-- tbl_cms_uploaddetails.amount =0.98 * #SuccessIBANKDD.amount or
-- tbl_cms_uploaddetails.amount = #SuccessIBANKDD.amount or
-- tbl_cms_uploaddetails.amount < 0.98 * #SuccessIBANKDD.amount and
(0.98 * #SuccessIBANKDD.amount <= tbl_cms_uploaddetails.amount
and #SuccessIBANKDD.amount >= tbl_cms_uploaddetails.amount) and
tbl_cms_uploaddetails.cms_schemegroup=#SuccessIBANKDD.cams_schemegroup and
tbl_cms_uploaddetails.additionalfield6=#SuccessIBANKDD.payin_slip_no and
--tbl_cms_uploaddetails1.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cms_uploaddetails.compare_status='Pending'and tbl_cms_uploaddetails.Format_ID=82
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANKDD
where tbl_cams_uploaddetails.instrm_no=#SuccessIBANKDD.instrm_no and
-- 0.98 * tbl_cams_uploaddetails.amount = #SuccessIBANKDD.amount or
-- tbl_cams_uploaddetails.amount = #SuccessIBANKDD.amount or
-- 0.98 * tbl_cams_uploaddetails.amount < #SuccessIBANKDD.amount and
(0.98 * tbl_cams_uploaddetails.amount <= #SuccessIBANKDD.amount
and tbl_cams_uploaddetails.amount >= #SuccessIBANKDD.amount) and
tbl_cams_uploaddetails.cams_schemegroup=#SuccessIBANKDD.cams_schemegroup and
tbl_cams_uploaddetails.payin_slip_no=#SuccessIBANKDD.payin_slip_no and
--tbl_cams_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cams_uploaddetails.Payment_Mechanism in ('D','P')
--
drop table #SuccessIBANKDD
end
IF @.DataFormat_Name ='HDFC'
Begin
proper query for updating success record in purchase file hdfc bank
select instrm_no
--,tbl_cams_uploadDetails.instrm_date
,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessHDFC from tbl_cams_uploaddetails WITH(NOLOCK)
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount AND
--tbl_cams_uploadDetails.instrm_date=tbl_cms_uploadDetails.instrm_date And --'Added Gopal feb 07
tbl_cams_uploaddetails.cams_schemegroup=tbl_cms_uploadDetails.cms_schemegroup --'Added Gopal feb 07
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending'and Format_ID=83 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploadDetails.additionalfield13 and
Payment_Mechanism<>'EC'and Payment_Mechanism<>'M' and Payment_Mechanism <> 'TR'
group by instrm_no,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
--,tbl_cams_uploadDetails.instrm_date
having count(instrm_no) = 1
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFC
where tbl_cams_uploaddetails.instrm_no=#SuccessHDFC.instrm_no and
tbl_cams_uploaddetails.amount=#SuccessHDFC.amount and
--tbl_cams_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cams_uploaddetails.cams_schemegroup=#SuccessHDFC.cams_schemegroup and
--tbl_cams_uploaddetails.payin_slip_no=#SuccessHDFC.payin_slip_no and
tbl_cams_uploaddetails.compare_status='Pending'
proper query for updating success record in bank file hdfc bank
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFC
where tbl_cms_uploaddetails.cheque_no=#SuccessHDFC.instrm_no and
tbl_cms_uploaddetails.amount=#SuccessHDFC.amount and
tbl_cms_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cms_uploaddetails.cms_schemegroup=#SuccessHDFC.cams_schemegroup and --added 26-Feb- 07 by gopal
tbl_cms_uploaddetails.additionalfield13=#SuccessHDFC.payin_slip_no and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=83
drop table #SuccessHDFC
--DD for HDFC
--For DD charges for HDFC
select instrm_no
--,tbl_cams_uploadDetails.instrm_date
,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessHDFCDD from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount AND
--tbl_cams_uploadDetails.instrm_date=tbl_cms_uploadDetails.instrm_date And --'Added Gopal feb 07
tbl_cams_uploaddetails.cams_schemegroup=tbl_cms_uploadDetails.cms_schemegroup --'Added Gopal feb 07
where tbl_cams_uploaddetails.compare_status='Pending' and
(0.98 * tbl_cams_uploaddetails.amount <= tbl_cms_uploaddetails.amount
and tbl_cams_uploaddetails.amount >= tbl_cms_uploaddetails.amount)
and tbl_cms_uploaddetails.compare_status='Pending'and Format_ID=83 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploadDetails.additionalfield13 and
Payment_Mechanism in ('D','P')
group by instrm_no,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
--,tbl_cams_uploadDetails.instrm_date
having count(instrm_no) = 1
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCDD
where tbl_cams_uploaddetails.instrm_no=#SuccessHDFCDD.instrm_no and
--0.98 * tbl_cams_uploaddetails.amount = #SuccessHDFCDD.amount or 0.98 * tbl_cams_uploaddetails.amount < #SuccessHDFCDD.amount or tbl_cams_uploaddetails.amount = #SuccessHDFCDD.amount and
(0.98 * tbl_cams_uploaddetails.amount <= #SuccessHDFCDD.amount
and tbl_cams_uploaddetails.amount >= #SuccessHDFCDD.amount) and
--tbl_cams_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cams_uploaddetails.cams_schemegroup=#SuccessHDFCDD.cams_schemegroup and
--tbl_cams_uploaddetails.payin_slip_no=#SuccessHDFCDD.payin_slip_no and
tbl_cams_uploaddetails.compare_status='Pending' and Payment_Mechanism in ('D','P')
--Added on 21 mar
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCDD
where tbl_cms_uploaddetails.cheque_no=#SuccessHDFCDD.instrm_no and
--tbl_cms_uploaddetails.amount=#SuccessHDFC.amount and
(0.98 * #SuccessHDFCDD.amount <= tbl_cms_uploaddetails.amount
and #SuccessHDFCDD.amount >= tbl_cms_uploaddetails.amount) and
tbl_cms_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cms_uploaddetails.cms_schemegroup=#SuccessHDFCDD.cams_schemegroup and --added 26-Feb- 07 by gopal
tbl_cms_uploaddetails.additionalfield13=#SuccessHDFCDD.payin_slip_no and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=83
drop table #SuccessHDFCDD
--
end
IF @.DataFormat_Name ='RTGS'
Begin
proper query for updating success record in purchase file RTGS
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup,tbl_cams_uploaddetails.trade_date into #SuccessRTGS from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
cams_schemegroup=cms_schemegroup and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=99
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup,trade_date
having count(tbl_cams_uploaddetails.folio_no) = 1
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessRTGS
where tbl_cams_uploaddetails.folio_no=#SuccessRTGS.folio_no and
tbl_cams_uploaddetails.amount=#SuccessRTGS.amount and
tbl_cams_uploaddetails.cams_schemegroup=#SuccessRTGS.cams_schemegroup and
tbl_cams_uploaddetails.trade_date=#SuccessRTGS.trade_date and
tbl_cams_uploaddetails.compare_status='Pending'
proper query for updating success record in bank file RTGS
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessRTGS
where tbl_cms_uploaddetails.folio_no=#SuccessRTGS.folio_no and
tbl_cms_uploaddetails.amount=#SuccessRTGS.amount and
tbl_cms_uploaddetails.cms_schemegroup=#SuccessRTGS.cams_schemegroup and
tbl_cms_uploaddetails.tradedate=#SuccessRTGS.trade_date and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=99
drop table #SuccessRTGS
end
IF @.DataFormat_Name='CHANNEL'
Begin
proper query for updating success record in purchase file Channel
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Folio_No,--tbl_cms_uploadDetails.Folio_No Folio_No1,
--Nachiket
--tbl_cams_uploaddetails.name
tbl_cams_uploaddetails.user_trxnno
into #SuccessChannel from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.cams_schemegroup from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount --and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate
--Nachiket
and tbl_cams_uploaddetails.user_trxnno=tbl_cms_uploaddetails.additionalfield2
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=98
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.Scheme_Code,
tbl_cams_uploadDetails.Folio_No,
--,tbl_cms_uploadDetails.Folio_No--Nachiket
--,tbl_cams_uploadDetails.name
tbl_cams_uploaddetails.user_trxnno
having count(tbl_cams_uploaddetails.trade_date) =1
--select * from #SuccessChannelG
-- drop table #SuccessChannelG
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessChannel
where -tbl_cams_uploaddetails.trade_date=#SuccessChannel.trade_date and by Nachiket on 15th March as per CR
tbl_cams_uploaddetails.amount=#SuccessChannel.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessChannel.Scheme_Code and
tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cams_uploaddetails.user_trxnno=#SuccessChannel.user_trxnno
proper query for updating success record in bank file Channel
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessChannel
where -tbl_cms_uploaddetails.tradedate=#SuccessChannel.trade_date and by Nachiket on 15th March as per CR
tbl_cms_uploaddetails.amount=#SuccessChannel.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessChannel.Scheme_Code and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=98
and tbl_cms_uploaddetails.additionalfield2=#SuccessChannel.user_trxnno
--
drop table #SuccessChannel
End
IF @.DataFormat_Name ='IBANK SUCCESS' OR @.DataFormat_Name ='IBANK FAILURE'
Begin
proper query for updating success record in purchase file IBank Success
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism into #SuccessIBankSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no AND
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate --ADDED GOPAL 07 FEB
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (85,86) and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success' ,ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBankSIP
where tbl_cams_uploaddetails.folio_no=#SuccessIBankSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessIBankSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessIBankSIP.Scheme_Code and
tbl_cams_uploaddetails.trade_date=#SuccessIBankSIP.trade_date and--ADDED GOPAL 07 NEW CR
tbl_cams_uploaddetails.compare_status='Pending' and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
proper query for updating success record in bank file IBank Success
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBankSIP
where tbl_cms_uploaddetails.folio_no=#SuccessIBankSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessIBankSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessIBankSIP.Scheme_Code and
tbl_cms_uploaddetails.tradedate=#SuccessIBankSIP.trade_date and --ADDED GOPAL 07 NEW CR tbl_cms_uploaddetails.compare_status='Pending'
#SuccessIBankSIP.Payment_Mechanism='M'
drop table #SuccessIBankSIP
end
IF @.DataFormat_Name ='BJ SUCCESS' OR @.DataFormat_Name ='BJ FAILURE'
Begin
proper query for updating success record in purchase file BJ Success
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism into #SuccessBJSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate --GOPAL 07 CR
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89) and Tbl_CAMS_UploadDetails.Payment_Mechanism='EC'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessBJSIP
where tbl_cams_uploaddetails.folio_no=#SuccessBJSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessBJSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessBJSIP.Scheme_Code and
tbl_cams_uploaddetails.trade_date=#SuccessBJSIP.trade_date and--GOPAL 07 CR
tbl_cams_uploaddetails.compare_status='Pending' and tbl_cams_uploaddetails.Payment_Mechanism='EC'
proper query for updating success record in bank file BJ Success
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessBJSIP
where tbl_cms_uploaddetails.folio_no=#SuccessBJSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessBJSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessBJSIP.Scheme_Code and
tbl_cms_uploaddetails.tradedate=#SuccessBJSIP.trade_date and--GOPAL 07 CR
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89) and #SuccessBJSIP.Payment_Mechanism='EC'
drop table #SuccessBJSIP
end
IF @.DataFormat_Name ='HDFC SUCCESS' OR @.DataFormat_Name ='HDFC FAILURE'
Begin
proper query for updating success record in purchase file HDFC SUCCESS
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Trade_date
--Nachiket
,tbl_cams_uploaddetails.name,tbl_cams_uploaddetails.Payment_Mechanism
into #SuccessHDFCSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploadDetails.Trade_date=tbl_cms_uploadDetails.Tradedate --GOPAL 07 CR
Nachiket
--and tbl_cams_uploadDetails.name=tbl_cms_uploadDetails.additionalfield2
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (90,91) and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Trade_date
--Nachiket
,tbl_cams_uploaddetails.name,tbl_cams_uploaddetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1
update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success' ,
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCSIP
where tbl_cams_uploaddetails.folio_no=#SuccessHDFCSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessHDFCSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessHDFCSIP.Scheme_Code and
tbl_cams_uploadDetails.Trade_date=#SuccessHDFCSIP.Trade_date and --GOPAL 07 CR
tbl_cams_uploaddetails.compare_status='Pending' and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
--Nachiket
--and tbl_cams_uploaddetails.name=#SuccessHDFCSIP.name
proper query for updating success record in bank file HDFC SUCCESS
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103)
from #SuccessHDFCSIP
where tbl_cms_uploaddetails.folio_no=#SuccessHDFCSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessHDFCSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessHDFCSIP.Scheme_Code and
tbl_cms_uploadDetails.Tradedate=#SuccessHDFCSIP.Trade_date and --GOPAL 07 CR
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (90,91) and #SuccessHDFCSIP.Payment_Mechanism='M'
--Nachiket
--and tbl_cms_uploaddetails.additionalfield2=#SuccessHDFCSIP.name
drop table #SuccessHDFCSIP
end
FETCH NEXT FROM CUR_DATAFORMAT INTO @.DataFormat_ID,@.DataFormat_Name
END
CLOSE CUR_DATAFORMAT
DEALLOCATE CUR_DATAFORMAT
--FORMAT END
SELECT top 30 CAMS.Name,CAMS.Scheme_Code,
CAMS.Trade_Date,CAMS.Posted_Date, CAMS.Amount, CAMS.Instrm_No, CAMS.Instrm_Date,
CAMS.Bank
from Tbl_CAMS_UploadDetails CAMS WITH(NOLOCK)
where CaMS.Compare_Status = 'Success' and ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103)
SELECT top 30 CAMS.Name,CAMS.Scheme_Code,
CAMS.Trade_Date,CAMS.Posted_Date, CAMS.Amount, CAMS.Instrm_No, CAMS.Instrm_Date,
CAMS.Bank
from Tbl_CAMS_UploadDetails CAMS WITH(NOLOCK) where CaMS.Compare_Status = 'Pending'
END
i know its very vast procedure....not written by me..so i want to optimize it........
any idea?
thanx a lot
No comments:
Post a Comment