You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
119 lines
4.9 KiB
119 lines
4.9 KiB
SET QUOTED_IDENTIFIER ON
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
/**
|
|
|
|
EXEC proc_ChangePayoutPartnerTxn @flag = 'Route', @controlno = '80171759238'
|
|
select * from remittran(nolock) where controlno = dbo.fnaencryptstring('80171759238') AND tranStatus='HOLD' AND pAgent=601361
|
|
|
|
*/
|
|
|
|
ALTER PROCEDURE proc_ChangePayoutPartnerTxn
|
|
@CONTROLNO VARCHAR(20) = NULL,
|
|
@flag VARCHAR(50) = NULL,
|
|
@pcountry VARCHAR(100) = NULL,
|
|
@User VARCHAR(100) = NULL,
|
|
@NewpAgent VARCHAR(100) = NULL
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
IF @flag='getAgent'
|
|
BEGIN
|
|
SELECT Pagentname,pcountry FROM remittran(NOLOCK) WHERE controlno = dbo.fnaEncryptstring(@controlno)
|
|
END
|
|
IF @flag='SettlingAgent'
|
|
BEGIN
|
|
select '' as agentid, 'Select Agent' AS agentName
|
|
UNION ALL
|
|
sELECT CAST (agentid AS VARCHAR(100)) , agentName FROM agentmaster(NOLOCK) WHERE issettlingAgent='Y' AND agentId IN (2090)--and agentCountry = @pcountry
|
|
END
|
|
IF @flag='Route'
|
|
BEGIN
|
|
DECLARE @ENCNO varchar(20) = dbo.fnaencryptstring(@CONTROLNO)
|
|
|
|
DECLARE @TRANID BIGINT, @REF_NUM VARCHAR(20)
|
|
--1 check transaction exists or not
|
|
select @TRANID = ID from remittran(nolock) where controlno = @ENCNO AND tranStatus='HOLD' AND pAgent=601361
|
|
|
|
--PRINT @TRANID
|
|
IF @TRANID IS NULL
|
|
BEGIN
|
|
SELECT '1' ErrorCode, 'Transaction doesnot exist.' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
--2 update routing agent and commission
|
|
update remitTran set tranStatus='Payment',pSuperAgent=224391,pSuperAgentName='DONGA MONEY TRANSFER'
|
|
,pAgent=2090,pAgentName='Donga Money Transfer Co., Ltd.',pBranch='2090',pBranchName='Donga Money Transfer Co., Ltd.',pAgentComm=2.54,pAgentCommCurrency='USD'
|
|
where id = @TRANID and tranStatus='Hold' AND pAgent=601361
|
|
|
|
--3 check voucher generated or not
|
|
select @REF_NUM = ref_num from FastMoneyPro_Account.dbo.VW_PostedAccountDetail(nolock) where field1= @CONTROLNO AND acc_num='101809022'
|
|
|
|
IF @REF_NUM IS NULL
|
|
BEGIN
|
|
SELECT '1' ErrorCode, 'Old Voucher no doesnot exist.' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
|
|
IF EXISTS(SELECT 'A' FROM FastMoneyPro_Account.dbo.tran_masteR (NOLOCK) WHERE field1= @CONTROLNO AND ref_num=@REF_NUM AND tran_type='T')
|
|
BEGIN
|
|
----4delete voucher
|
|
INSERT INTO FastMoneyPro_Account.dbo.tran_master_deleted(tran_id,acc_num,del_flg,entry_user_id,gl_sub_head_code,part_tran_srl_num,part_tran_type
|
|
,ref_num,rpt_code,tran_amt,tran_date,tran_particular,tran_rmks,billno,billdate,party,otherinfo,tran_type,created_date
|
|
,v_type,vfd_user_id,vfd_date,usd_amt,usd_rate,field1,field2,dept_id,branch_id,emp_name,trn_currency)
|
|
SELECT tran_id,acc_num,'D',entry_user_id,gl_sub_head_code,part_tran_srl_num,part_tran_type
|
|
,T.ref_num,rpt_code,tran_amt,tran_date,tran_particular,tran_rmks,billno,billdate,D.party,D.otherinfo,T.tran_type,created_date
|
|
,T.tran_type, 'system', GETDATE(),t.usd_amt,t.usd_rate,t.field1,t.field2,t.departmentId,t.branchId,t.employeeName,t.fcy_Curr
|
|
from FastMoneyPro_Account.dbo.tran_master T(NOLOCK)
|
|
INNER JOIN FastMoneyPro_Account.dbo.tran_masterDetail D(NOLOCK) ON T.ref_num = D.ref_num AND T.tran_type=D.tran_type
|
|
WHERE T.ref_num =@REF_NUM AND T.tran_type = 't'
|
|
|
|
delete from FastMoneyPro_Account.dbo.tran_masterDetail where ref_num =@REF_NUM and tran_type='t'
|
|
|
|
delete from FastMoneyPro_Account.dbo.tran_master where ref_num =@REF_NUM and tran_type='t'
|
|
END
|
|
|
|
IF EXISTS(SELECT 'A' FROM FastMoneyPro_Account.dbo.tran_master_post (NOLOCK) WHERE field1= @CONTROLNO AND ref_num=@REF_NUM AND tran_type='T')
|
|
BEGIN
|
|
----4delete voucher
|
|
INSERT INTO FastMoneyPro_Account.dbo.tran_master_deleted(tran_id,acc_num,del_flg,entry_user_id,gl_sub_head_code,part_tran_srl_num,part_tran_type
|
|
,ref_num,rpt_code,tran_amt,tran_date,tran_particular,tran_rmks,billno,billdate,party,otherinfo,tran_type,created_date
|
|
,v_type,vfd_user_id,vfd_date,usd_amt,usd_rate,field1,field2,dept_id,branch_id,emp_name,trn_currency)
|
|
SELECT tran_id,acc_num,'D',entry_user_id,gl_sub_head_code,part_tran_srl_num,part_tran_type
|
|
,T.ref_num,rpt_code,tran_amt,tran_date,tran_particular,tran_rmks,billno,billdate,D.party,D.otherinfo,T.tran_type,created_date
|
|
,T.tran_type, 'system', GETDATE(),t.usd_amt,t.usd_rate,t.field1,t.field2,t.departmentId,t.branchId,t.employeeName,t.fcy_Curr
|
|
from FastMoneyPro_Account.dbo.tran_master_post T(NOLOCK)
|
|
INNER JOIN FastMoneyPro_Account.dbo.tran_masterDetail_post D(NOLOCK) ON T.ref_num = D.ref_num AND T.tran_type=D.tran_type
|
|
WHERE T.ref_num =@REF_NUM AND T.tran_type = 't'
|
|
|
|
delete from FastMoneyPro_Account.dbo.tran_masterDetail_post where ref_num =@REF_NUM and tran_type='t'
|
|
|
|
delete from FastMoneyPro_Account.dbo.tran_master_post where ref_num =@REF_NUM and tran_type='t'
|
|
END
|
|
|
|
--5 re generate voucher
|
|
|
|
DECLARE @tbl TABLE(ErrorCode VARCHAR(10) NULL, Msg VARCHAR(MAX) NULL, Id varchar(100) NULL)
|
|
|
|
INSERT INTO @tbl
|
|
exec FastMoneyPro_Account.dbo.proc_transactionVoucherEntry @CONTROLNO
|
|
|
|
INSERT INTO dbo.tranModifyLog
|
|
(
|
|
tranId,
|
|
controlNo,
|
|
message,
|
|
createdBy,
|
|
createdDate,
|
|
MsgType
|
|
)SELECT @TRANID, @ENCNO, 'Partner has been changed from Exim bank to Donga', @User, GETDATE(),'Core'
|
|
|
|
|
|
SELECT * FROM @tbl
|
|
|
|
END
|
|
|
|
|
|
GO
|