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.
85 lines
3.4 KiB
85 lines
3.4 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[EXCHANGE_PROC_GETRECEIPT] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
DROP PROCEDURE [dbo].[EXCHANGE_PROC_GETRECEIPT]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[EXCHANGE_PROC_GETRECEIPT] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
CREATE procEDURE [dbo].[EXCHANGE_PROC_GETRECEIPT]
|
|
@FLAG VARCHAR(10),
|
|
@ID INT,
|
|
@MAPCODE VARCHAR(20)
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @HEADMSG VARCHAR(MAX),@receiptPrint CHAR(1)
|
|
DECLARE @currencyName NVARCHAR(30) ='KRW'
|
|
|
|
SELECT
|
|
@HEADMSG = ISNULL(REPLACE(B.headMessage,'|','<BR>'),'')+ISNULL(B.agentAddress,' ')+'<br>Phone: '+ISNULL(B.agentPhone1,' ')
|
|
,@receiptPrint = 'm'
|
|
FROM dbo.agentMaster B WITH(NOLOCK)
|
|
INNER JOIN EXCHANGE_TRANSACTION_INFO T ON B.agentId = T.branch_id
|
|
WHERE T.tran_id = @ID
|
|
|
|
IF @FLAG = 'PC' -->> purchase receipt for crosscurrency select * from transaction_info order by tran_id desc
|
|
BEGIN
|
|
|
|
SELECT T.contact,T.tran_id,T.entered_date tran_date,ISNULL(@HEADMSG,'') HEADMSG,@receiptPrint receiptPrint,T.billNo
|
|
,T.customer_name,C.CID,T.entered_by,T.id_number,t.Nationality ref_code,C.ct_address ,id.typeID [idtype]
|
|
FROM EXCHANGE_TRANSACTION_INFO t WITH (NOLOCK)
|
|
LEFT JOIN dbo.EXCHANGE_CUSTOMER C(nolock) ON T.tran_id = C.tran_id
|
|
LEFT JOIN dbo.staticDataValue id WITH (NOLOCK) ON t.id_type=id.typeID
|
|
WHERE t.tran_id = @ID
|
|
|
|
SELECT fcy_Curr,usd_amt,format(usd_rate,'000.00') as sys_rate,ROUND(usd_amt*usd_rate,2) amt
|
|
FROM FastMoneyPro_Account.dbo.tran_master WITH (NOLOCK)
|
|
WHERE fcy_Curr <> @currencyName AND tran_type ='p' AND billno = @ID
|
|
|
|
END
|
|
--ELSE IF @FLAG = 'HREC' -->> purchase receipt TEMP
|
|
--BEGIN
|
|
|
|
-- SELECT
|
|
-- @HEADMSG = ISNULL(REPLACE(headMsg,'|','<BR>'),BRANCH_ADDRESS+'<br>,Phone'+ISNULL(BRANCH_PHONE,'')+',Fax'+ISNULL(BRANCH_FAX,BRANCH_PHONE))
|
|
-- ,@receiptPrint = ISNULL(B.receiptPrint ,'S')
|
|
-- FROM Branches B WITH(NOLOCK)
|
|
-- INNER JOIN EXCHANGE_HOLDTRANSACTION_INFO T ON B.BRANCH_ID = T.branch_id
|
|
-- WHERE T.tran_id = @ID
|
|
|
|
-- SELECT T.contact,T.tran_id,T.entered_date tran_date,ISNULL(@HEADMSG,'') HEADMSG,@receiptPrint receiptPrint,T.tran_id billNo
|
|
-- ,T.customer_name,C.CID,T.entered_by,T.id_number,t.Nationality ref_code,C.ct_address ,id.typeID [idtype],RTRIM(t.tran_type) tran_type
|
|
-- FROM EXCHANGE_HOLDTRANSACTION_INFO t WITH (NOLOCK)
|
|
-- LEFT JOIN EXCHANGE_Customer C ON CAST(T.tran_id AS VARCHAR) = C.sessionId
|
|
-- LEFT JOIN staticDataValue id WITH (NOLOCK) ON t.id_type=id.typeID
|
|
-- WHERE t.tran_id = @ID
|
|
|
|
-- SELECT currency fcy_Curr,fcyamt usd_amt,apprate sys_rate,ROUND(fcyamt*apprate,2) amt
|
|
-- FROM EXCHANGE_OFACTransaction WITH (NOLOCK)
|
|
-- WHERE currency <> @currencyName AND sessionId = CAST(@ID AS VARCHAR)
|
|
|
|
--END
|
|
|
|
IF @FLAG = 'SC' -->> SELL receipt for crosscurrency
|
|
BEGIN
|
|
|
|
SELECT T.contact,T.tran_id,T.entered_date tran_date,ISNULL(@HEADMSG,'') HEADMSG,@receiptPrint receiptPrint,T.billNo
|
|
,T.customer_name,C.CID,T.entered_by,T.id_number,t.Nationality ref_code,C.ct_address ,id.typeID [idtype]
|
|
FROM EXCHANGE_TRANSACTION_INFO t WITH (NOLOCK)
|
|
LEFT JOIN EXCHANGE_CUSTOMER C(nolock) ON T.tran_id = C.tran_id
|
|
LEFT JOIN staticDataValue id WITH (NOLOCK) ON t.id_type=id.typeID
|
|
WHERE t.tran_id = @ID
|
|
|
|
SELECT fcy_Curr,usd_amt,format(usd_rate,'000.00') as sys_rate,ROUND(usd_amt*usd_rate,2) amt
|
|
FROM FastMoneyPro_Account.dbo.tran_master WITH (NOLOCK)
|
|
WHERE fcy_Curr <> @currencyName AND tran_type ='S' AND billno = @ID
|
|
|
|
END
|
|
|
|
GO
|