Project

General

Profile

Download (9 KB)

Feature #2175 » PDSDOCREF_20230523.sql

Tri Rizqiaty, 06/12/2023 11:20 AM

 
1 
??USE [MinovaES_Pertalife_Prod]
2
3 
GO
4
5 
/****** Object:  StoredProcedure [dbo].[PDSDOCREF]    Script Date: 23/05/2023 10.21.00 ******/
6
7 
SET ANSI_NULLS ON
8
9 
GO
10
11 
SET QUOTED_IDENTIFIER ON
12
13 
GO
14
15 
ALTER PROCEDURE [dbo].[PDSDOCREF]
16 
(
17 
	@DocType VARCHAR(50)
18 
)
19 
AS
20
21 
--DECLARE @DocType VARCHAR(50) = ',DocType[=]PO'
22 
SET @DocType = REPLACE(REPLACE(@DocType, 'DocType[=]', ''),',','')
23
24 
SELECT
25 
  *
26 
FROM
27 
  (
28 
    SELECT
29 
      ref.TransType,
30 
      ref.DocType,
31 
      ref.TransTypeRef,
32 
      ref.DocTypeRef,
33 
      docdata.DocNo,
34 
      docdata.DocDate,
35 
      docdata.CompanyID,
36 
      docdata.BusinessUnit,
37 
      docdata.Description,
38 
      docdata.DocItemID,
39 
      docdata.ExternalID
40 
    FROM
41 
      PCMBSDOCREF ref WITH (Nolock)
42 
      INNER JOIN (
43 
        SELECT
44 
          'FIN' AS TransType,
45 
          DocType,
46 
          DocNo,
47 
          DocDate,
48 
          CompanyID,
49 
          BusinessUnit,
50 
          '' AS Description,
51 
          '' AS DocItemID,
52 
          ExternalID
53 
        FROM
54 
          PTRFINANCEHEAD WITH (Nolock)
55 
        WHERE
56 
          DocNo IN (
57 
            SELECT
58 
              DISTINCT DocNo
59 
            FROM
60 
              PTRFINANCEITEM WITH (Nolock)
61 
            WHERE
62 
              (
63 
                ItemStatus = '0'
64 
                OR ItemStatus = '1'
65 
              )
66 
          )
67 
        UNION ALL
68 
        SELECT
69 
          'SLS' AS TransType,
70 
          DocType,
71 
          slsHead.DocNo,
72 
          DocDate,
73 
          CompanyID,
74 
          BusinessUnit,
75 
          slsPay.Description AS Description,
76 
          slsPay.DocItemID AS DocItemID,
77 
          ExternalID
78 
        FROM
79 
          PTRSALESHEADER slsHead WITH (Nolock)
80 
          INNER JOIN dbo.PTRSALESPAYTERM slsPay WITH (Nolock) ON slsHead.DocNo = slsPay.DocNo
81 
          AND slsPay.Status = '0'
82 
        UNION ALL
83 
        SELECT
84 
          'PUR' AS TransType,
85 
          DocType,
86 
          purHead.DocNo,
87 
          DocDate,
88 
          CompanyID,
89 
          BusinessUnit,
90 
          '' AS Description, --purPay.Description AS Description,
91 
          '' AS DocItemID, --purPay.DocItemID AS DocItemID,
92 
          ExternalID
93 
        FROM
94 
          PTRPURCHHEAD purHead WITH (Nolock)
95 
          --INNER JOIN dbo.PTRSALESPAYTERMPURCH purPay WITH (Nolock) ON purHead.DocNo = purPay.DocNo
96 
          --AND purPay.Status = '0'
97 
        UNION ALL
98 
        SELECT
99 
          'PRD' AS TransType,
100 
          DocType,
101 
          DocNo,
102 
          DocDate,
103 
          CompanyID,
104 
          '0' AS BusinessUnit,
105 
          '' AS Description,
106 
          '' AS DocItemID,
107 
          ExternalID
108 
        FROM
109 
          PTRPRODUCTIONHEAD WITH (Nolock)
110 
        UNION ALL
111 
        SELECT
112 
          'DLVR' AS TransType,
113 
          DocType,
114 
          DocNo,
115 
          DocDate,
116 
          '0' AS CompanyID,
117 
          '0' AS BusinessUnit,
118 
          '' AS Description,
119 
          '' AS DocItemID,
120 
          ExternalID
121 
        FROM
122 
          PTRDELIVERYHEAD WITH (Nolock)
123 
        UNION ALL
124 
        SELECT
125 
          DISTINCT 'MTR' AS TransType,
126 
          DocType,
127 
          mtrHead.DocNo,
128 
          DocDate,
129 
          ReceiverID AS CompanyID,
130 
          '0' AS BusinessUnit,
131 
          '' AS Description,
132 
          '' AS DocItemID,
133 
          ExternalID
134 
        FROM
135 
          PTRMATERIALHEAD mtrHead WITH (Nolock)
136 
          INNER JOIN dbo.PTRMATERIALITEM mtrItem WITH (Nolock) ON mtrHead.DocNo = mtrItem.DocNo
137 
        WHERE
138 
          mtrHead.DocStatus = '3'
139 
		UNION ALL
140 
        SELECT
141 
          DISTINCT 'ASTM' AS TransType,
142 
          DocType,
143 
          astmhead.DocNo,
144 
          DocDate,
145 
          astmhead.CompanyID AS CompanyID,
146 
          astmhead.BusinessUnit AS BusinessUnit,
147 
          astmhead.Description AS Description,
148 
          astmItem.DocItemID AS DocItemID,
149 
          '' AS ExternalID
150 
        FROM
151 
          PTRASTMAINHEAD astmhead WITH (Nolock)
152 
          INNER JOIN PTRASTMAINITEM astmItem WITH (Nolock) ON astmhead.DocNo = astmItem.DocNo
153 
		UNION ALL
154 
        SELECT
155 
          DISTINCT 'ASTU' AS TransType,
156 
          DocType,
157 
          astuhead.DocNo,
158 
          DocDate,
159 
          astuhead.CompanyID AS CompanyID,
160 
          astuhead.BusinessUnit AS BusinessUnit,
161 
          astuhead.Description AS Description,
162 
          astuItem.DocItemID AS DocItemID,
163 
          '' AS ExternalID
164 
        FROM
165 
          PTRASTUTILHEAD astuhead WITH (Nolock)
166 
          INNER JOIN PTRASTUTILITEM astuItem WITH (Nolock) ON astuhead.DocNo = astuItem.DocNo
167 
      ) AS docdata ON ref.TransTypeRef = docdata.TransType
168 
      AND ref.DocTypeRef = docdata.DocType
169 
  ) AS tbl
170 
where DocType = @DocType
171
(4-4/5)