|
1
|
|
|
2
|
ALTER PROC [dbo].[TR_REQ_PAY_BUDGET_ByID]
|
|
3
|
@p_REQ_PAY_ID VARCHAR(1500) = NULL
|
|
4
|
AS
|
|
5
|
BEGIN
|
|
6
|
-- NHIEU PO HOAC SO PHIEU TAM UNG / THANH TOAN
|
|
7
|
DECLARE @l_LSTSERI TABLE (
|
|
8
|
[ID] [int] IDENTITY(1,1) NOT NULL,
|
|
9
|
[VALUE] [NVARCHAR](MAX) NULL)
|
|
10
|
DECLARE @l_FILENAME VARCHAR(MAX)
|
|
11
|
INSERT INTO @l_LSTSERI SELECT VALUE FROM WSISPLIT(@p_REQ_PAY_ID,';')
|
|
12
|
----
|
|
13
|
DECLARE @t_NSCP_ACCNO TABLE(
|
|
14
|
GD_CODE VARCHAR(250),
|
|
15
|
ACC_NO VARCHAR(20),
|
|
16
|
COST_APPR DECIMAL(18, 0)
|
|
17
|
)
|
|
18
|
insert into @t_NSCP_ACCNO SELECT GD_CODE, ACC_NO, COUNT(GD_CODE) FROM TR_REQ_BUGGED_NSCP GROUP BY GD_CODE, ACC_NO
|
|
19
|
|
|
20
|
SELECT A.*,B.GOODS_CODE,G.GD_ID AS GOODS_ID,G.GD_NAME AS GOODS_NAME,A.RATE, A.CURRENCY,
|
|
21
|
0.0 AMT_REMAIN_HIS, NSCP.ACC_NO,
|
|
22
|
CASE WHEN ISNULL(A.TLNAME, '') <> '' THEN H.TLNANME + ' - ' + H.TLFullName
|
|
23
|
ELSE I.BRANCH_CODE + ' - ' + I.BRANCH_NAME
|
|
24
|
END AS DISPLAY_NAME
|
|
25
|
FROM TR_REQ_PAY_BUDGET A
|
|
26
|
LEFT JOIN PL_TRADEDETAIL B ON A.TRADE_ID = B.TRADE_ID
|
|
27
|
LEFT JOIN CM_GOODS G ON B.GOODS_ID = G.GD_ID
|
|
28
|
LEFT JOIN @t_NSCP_ACCNO NSCP ON G.GD_CODE = NSCP.GD_CODE
|
|
29
|
LEFT JOIN TL_USER H ON A.TLNAME = H.TLNANME
|
|
30
|
LEFT JOIN CM_BRANCH I ON A.BRANCH_TAKE_COST_ID = I.BRANCH_ID
|
|
31
|
WHERE A.REQ_PAY_ID IN (SELECT VALUE FROM @l_LSTSERI)
|
|
32
|
END
|