|
1
|
|
|
2
|
ALTER PROC [dbo].[TR_REQ_ADVANCE_DT_BYID]
|
|
3
|
@p_REQ_PAY_ID VARCHAR(15)= NULL
|
|
4
|
AS
|
|
5
|
BEGIN
|
|
6
|
SELECT A.*,
|
|
7
|
--A.REF_ID AS REF_CODE,
|
|
8
|
CASE WHEN ISNULL(B.PO_CODE,C.CONTRACT_CODE) IS NOT NULL AND ISNULL(B.PO_CODE,C.CONTRACT_CODE) <>'' THEN ISNULL(B.PO_CODE,C.CONTRACT_CODE) ELSE A.REF_ID END AS REF_CODE,
|
|
9
|
ISNULL(B.PO_NAME, C.CONTRACT_NAME) AS REF_NAME,
|
|
10
|
ISNULL(S1.SUP_NAME, S2.SUP_NAME) AS SUP_NAME,
|
|
11
|
ISNULL(S1.TAX_NO, S2.TAX_NO) AS SUP_TAX_NO,
|
|
12
|
ISNULL(D1.REQ_CODE, D2.REQ_CODE) AS SO_PYC, ISNULL(P1.REQ_CODE, P2.REQ_CODE) AS SO_TO_TRINH,
|
|
13
|
ISNULL(P1.REQ_NAME, P2.REQ_NAME) AS TEN_TO_TRINH, '' AS IS_CLOSED, ISNULL(D1.REQ_ID,D2.REQ_ID) AS PYC_ID,ISNULL(P1.REQ_ID, P2.REQ_ID) AS TO_TRINH_ID,
|
|
14
|
CASE WHEN A.REF_TYPE ='P' THEN 'BVB'+ ISNULL((SELECT ' - ' + BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_CODE = LEFT(S1.ACC_NUM,3)),'') ELSE 'BVB'+ ISNULL((SELECT ' - '+ BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_CODE = LEFT(S2.ACC_NUM,3)),'') END AS BANK_NAME, ISNULL(S1.ACC_NAME,S2.ACC_NAME) AS ACC_NAME, ISNULL(S1.ACC_NUM, S2.ACC_NUM) AS ACC_NUM,
|
|
15
|
ISNULL(S1.BANK_NAME_OUT, S2.BANK_NAME_OUT) AS BANK_NAME_OUT, ISNULL(S1.ACC_NAME_OUT,S2.ACC_NAME_OUT) AS ACC_NAME_OUT, ISNULL(S1.ACC_NUM_OUT, S2.ACC_NUM_OUT) AS ACC_NUM_OUT, ISNULL(C.CONT_TYPE,'') AS CONT_TYPE,
|
|
16
|
CASE WHEN CONVERT(DATE, C.END_DT, 103) < CONVERT(DATE, GETDATE(), 103) AND C.END_DT IS NOT NULL AND C.END_DT <> ''
|
|
17
|
THEN N'Hợp đồng số :' + C.CONTRACT_ID + N' đã hết hiệu lực kể từ ngày ' + FORMAT(DATEADD(DAY,1,C.END_DT),'dd/MM/yyyy') ELSE '' END AS IS_FLAG_END,
|
|
18
|
--doanptt 150922 them so hop dong
|
|
19
|
C.CONTRACT_ID, C.CONTRACT_CODE, C.[CONTRACT_NAME], ISNULL(C.END_DT, GETDATE()) AS END_DT
|
|
20
|
FROM TR_REQ_ADVANCE_DT A
|
|
21
|
LEFT JOIN TR_PO_MASTER B ON A.REF_ID= B.PO_ID
|
|
22
|
LEFT JOIN CM_SUPPLIER S1 ON B.SUP_ID = S1.SUP_ID
|
|
23
|
LEFT JOIN TR_CONTRACT C ON A.REF_ID= C.CONTRACT_ID
|
|
24
|
LEFT JOIN CM_SUPPLIER S2 ON C.SUP_ID = S2.SUP_ID
|
|
25
|
LEFT JOIN TR_REQUEST_DOC D1 ON C.REQ_DOC_ID = D1.REQ_ID
|
|
26
|
LEFT JOIN PL_REQUEST_DOC P1 ON D1.PL_REQ_ID = P1.REQ_ID
|
|
27
|
-- PO
|
|
28
|
LEFT JOIN TR_REQUEST_DOC D2 ON B.REQ_DOC_ID = D2.REQ_ID
|
|
29
|
LEFT JOIN PL_REQUEST_DOC P2 ON D2.PL_REQ_ID = P2.REQ_ID
|
|
30
|
WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID IS NULL OR @p_REQ_PAY_ID =''
|
|
31
|
END
|