· 7 years ago · Oct 17, 2018, 10:12 AM
1DROP FUNCTION IF EXISTS r_sales_journal(character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying, character varying);
2
3CREATE OR REPLACE FUNCTION r_sales_journal(character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
4 RETURNS SETOF refcursor AS
5$BODY$
6DECLARE
7 pRefHeader REFCURSOR := 'refHeader';
8 pRefDetail REFCURSOR := 'refDetail';
9 pSessionId ALIAS FOR $1;
10 pTenantId ALIAS FOR $2;
11 pOuId ALIAS FOR $3;
12 pUserId ALIAS FOR $4;
13 pRoleId ALIAS FOR $5;
14 pDatetime ALIAS FOR $6;
15
16 pCustomerCodeFrom ALIAS FOR $7;
17 pCustomerCodeTo ALIAS FOR $8;
18 pDateFrom ALIAS FOR $9;
19 pDateTo ALIAS FOR $10;
20 pSubCtgrCode ALIAS FOR $11;
21 pPaymentStatus ALIAS FOR $12;
22
23 vCustomerFrom character varying(1024) := '';
24 vCustomerTo character varying(1024) := '';
25 vFilterSubCtgr character varying(100) := '';
26 vSubCtgrName character varying(100) := 'All';
27 vRawData character varying(10) := 'RAW-DATA';
28 vSumData character varying(10) := 'SUM-DATA';
29 vDocTypeSalesInvoice bigint := 321;
30 vDocTypeSalesInvoiceTemp bigint := 361;
31 vDocTypeSoByBrand bigint := 398;
32 vDocTypeDo bigint := 311;
33 vDocTypeConvExcOut bigint := 395;
34 vDocTypeReturNote bigint := 502;
35 vDocTypeDNAR bigint := 241;
36 vDocTypeCNAR bigint := 251;
37 vDocTypeRSIB bigint := 380;--Return Sales Invoice By Brand
38 vDocTypeRRS bigint := 381;--Request Return Sales
39 vDocTypeRN bigint := 502;--Return Note
40 vOne integer := 1;
41 vInvoiceFix character varying(20) := 'Final Invoice';
42 vInvoiceTemp character varying(20) := 'Temporary Invoice';
43 vDebtNoteAr character varying(20) := 'Debt Note AR';
44 vCreditNoteAr character varying(20) := 'Credit Note AR';
45 vReturnInvoice character varying(20) := 'Return Invoice';
46 vRvs character varying(3) := 'RVS';
47 vTrl character varying(3) := 'TRL';
48 vSmp character varying(3) := 'SMP';
49 vFoc character varying(3) := 'FOC';
50 vReg character varying(3) := 'REG';
51 vSettledPaymentStatus character varying := 'settled';
52 vNotYetSettledPaymentStatus character varying := 'notYetSettled';
53 vNolAmount numeric := 0;
54
55-- vActivityOngkir character varying;
56-- vParamCodeActivityOngkir character varying := 'DEL_COST';
57 vFlgYes character varying := 'Y';
58
59BEGIN
60 RAISE NOTICE 'BEGIN Report Sales Journal=%', clock_timestamp();
61
62 -- DELETE TEMP DATA --
63 DELETE FROM tt_sl_invoice WHERE session_id = pSessionId;
64 DELETE FROM tt_report_sales_journal WHERE session_id = pSessionId;
65
66 SELECT partner_name INTO vCustomerFrom
67 FROM m_partner
68 WHERE tenant_id = pTenantId AND partner_code = pCustomerCodeFrom;
69
70 SELECT partner_name INTO vCustomerTo
71 FROM m_partner
72 WHERE tenant_id = pTenantId AND partner_code = pCustomerCodeTo;
73
74
75 RAISE NOTICE 'Set pSubCtgrCode Start=%', clock_timestamp();
76 IF (pSubCtgrCode !='' ) THEN
77
78 -- CREATE FILTER --
79 vFilterSubCtgr := ' AND '', ''||UPPER(c.sub_ctgr_product_code)||'','' LIKE UPPER(''%, '||pSubCtgrCode||',%'')';
80
81 -- MASUKAN SUB CTGR NAME KE VARIABLE vSubCtgrName --
82 SELECT sub_ctgr_product_name INTO vSubCtgrName
83 FROM m_sub_ctgr_product
84 WHERE tenant_id = pTenantId
85 AND sub_ctgr_product_code = pSubCtgrCode;
86 END IF;
87
88 RAISE NOTICE 'Set pSubCtgrCode End=%', clock_timestamp();
89
90 -- GET ACTIVITY GL ONGKIR
91-- SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeActivityOngkir) INTO vActivityOngkir;
92
93
94 RAISE NOTICE 'pRefHeader Start=%', clock_timestamp();
95 -- HEADER --
96 Open pRefHeader FOR
97 SELECT pDatetime AS datetime, f_get_ou_name(pOuId) AS ou_name, f_get_username(pUserId) AS username,
98 f_get_role_name(pRoleId) AS rolename, pDateFrom AS date_from, pDateTo AS date_to, pCustomerCodeFrom AS partner_code_from,
99 vCustomerFrom AS partner_name_from, pCustomerCodeTo AS partner_code_to,
100 vCustomerTo AS partner_name_to, vSubCtgrName AS sub_ctgr_product_name,
101 CASE WHEN (pCustomerCodeFrom !='ALL' AND pCustomerCodeTo !='ALL')
102 THEN pCustomerCodeFrom||'/'||vCustomerFrom||' - '||pCustomerCodeTo||'/'||vCustomerTo
103 ELSE 'ALL'
104 END AS partner,
105 CASE WHEN pPaymentStatus = vSettledPaymentStatus THEN 'SETTLED'
106 WHEN pPaymentStatus = vNotYetSettledPaymentStatus THEN 'NOT YET SETTLED'
107 ELSE 'ALL' END AS payment_status;
108 RETURN NEXT pRefHeader;
109 RAISE NOTICE 'pRefHeader End=%', clock_timestamp();
110
111
112 -- MASUKAN SL INVOICE yg APPROVED YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
113 IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
114 RAISE NOTICE 'Get Data SI <> ALL Start=%', clock_timestamp();
115
116 INSERT INTO tt_sl_invoice(
117 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
118 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
119 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
120 advance_amount, tax_amount, add_amount, total_amount, status_doc,
121 workflow_status, version, ref_inv_temp_id, discount_amount,
122 mou_id, invoice_status, payment_amount,
123 so_date, promo_id, product_launching_id)
124 SELECT pSessionId, A.invoice_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
125 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
126 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, A.gross_amount,
127 A.advance_amount, A.tax_amount, A.add_amount, A.total_amount, A.status_doc,
128 A.workflow_status, A.version, A.ref_inv_temp_id, A.discount_amount,
129 COALESCE(B.tag_doc_id, -99) AS mou_id, vInvoiceFix, C.payment_amount,
130 D.doc_date AS so_date, COALESCE(E.tag_doc_id, -99) AS promo_id, COALESCE(F.tag_doc_id, -99) AS product_launching_id
131 FROM fi_invoice_ar_balance C
132 INNER JOIN sl_invoice A ON C.invoice_ar_id = A.invoice_id AND C.doc_type_id = A.doc_type_id
133 INNER JOIN sl_so D ON A.ref_id = D.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
134 INNER JOIN m_partner G ON C.partner_id = G.partner_id
135 LEFT OUTER JOIN sl_invoice_tagging B ON D.tenant_id = B.tenant_id AND A.invoice_id = B.invoice_id AND B.tag_key = 'MOU'
136 LEFT OUTER JOIN sl_so_tagging E ON D.tenant_id = E.tenant_id AND D.so_id = E.so_id AND E.tag_key = 'PROMO'
137 LEFT OUTER JOIN sl_so_tagging F ON D.tenant_id = F.tenant_id AND D.so_id = F.so_id AND F.tag_key = 'LAUNCHING'
138 WHERE C.tenant_id = pTenantId
139 AND C.ou_id = pOuId
140 AND C.doc_type_id = vDocTypeSalesInvoice
141 AND G.partner_code BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
142 AND A.doc_date BETWEEN pDateFrom AND pDateTo
143 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
144 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
145 ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
146 RAISE NOTICE 'Get Data SI ALL Start=%', clock_timestamp();
147 INSERT INTO tt_sl_invoice(
148 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
149 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
150 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
151 advance_amount, tax_amount, add_amount, total_amount, status_doc,
152 workflow_status, version, ref_inv_temp_id, discount_amount,
153 mou_id, invoice_status, payment_amount,
154 so_date, promo_id, product_launching_id)
155 SELECT pSessionId, A.invoice_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
156 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
157 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, A.gross_amount,
158 A.advance_amount, A.tax_amount, A.add_amount, A.total_amount, A.status_doc,
159 A.workflow_status, A.version, A.ref_inv_temp_id, A.discount_amount,
160 COALESCE(B.tag_doc_id, -99) AS mou_id, vInvoiceFix, C.payment_amount,
161 D.doc_date AS so_date, COALESCE(E.tag_doc_id, -99) AS promo_id, COALESCE(F.tag_doc_id, -99) AS product_launching_id
162 FROM fi_invoice_ar_balance C
163 INNER JOIN sl_invoice A ON C.invoice_ar_id = A.invoice_id AND C.doc_type_id = A.doc_type_id
164 INNER JOIN sl_so D ON A.ref_id = D.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
165 LEFT OUTER JOIN sl_invoice_tagging B ON D.tenant_id = B.tenant_id AND A.invoice_id = B.invoice_id AND B.tag_key = 'MOU'
166 LEFT OUTER JOIN sl_so_tagging E ON D.tenant_id = E.tenant_id AND D.so_id = E.so_id AND E.tag_key = 'PROMO'
167 LEFT OUTER JOIN sl_so_tagging F ON D.tenant_id = F.tenant_id AND D.so_id = F.so_id AND F.tag_key = 'LAUNCHING'
168 WHERE C.tenant_id = pTenantId
169 AND C.ou_id = pOuId
170 AND C.doc_type_id = vDocTypeSalesInvoice
171 AND A.doc_date BETWEEN pDateFrom AND pDateTo
172 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
173 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
174 END IF;
175 RAISE NOTICE 'Get Data SI End=%', clock_timestamp();
176
177 -- MASUKAN SL INVOICE TEMP yg APPROVED dan dari SOB REGULAR dan amount nya <> 0 YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
178 IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
179 RAISE NOTICE 'Get Data TSI <> ALL Start=%', clock_timestamp();
180 WITH valid_so_balance_so_invoice AS (
181 SELECT A.so_id
182 FROM sl_so_balance_invoice A
183 INNER JOIN m_partner B ON A.partner_id = B.partner_id
184 WHERE A.ref_doc_type_id = vDocTypeDo
185 AND B.partner_code BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
186 GROUP BY A.so_id
187 HAVING SUM(A.qty_dlv_so) > 0
188 )
189 INSERT INTO tt_sl_invoice(
190 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
191 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
192 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
193 advance_amount, tax_amount, add_amount, total_amount, status_doc,
194 workflow_status, version, ref_inv_temp_id, discount_amount,
195 mou_id, invoice_status, payment_amount,
196 so_date, promo_id, product_launching_id)
197 SELECT pSessionId, A.invoice_temp_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
198 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
199 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, COALESCE(A.gross_amount, 0),
200 A.advance_amount, COALESCE(A.tax_amount, 0), A.add_amount, A.total_amount, A.status_doc,
201 A.workflow_status, A.version, -99, A.discount_amount,
202 COALESCE(C.tag_doc_id, -99) AS mou_id, vInvoiceTemp, D.payment_amount,
203 B.doc_date AS so_date, COALESCE(F.tag_doc_id, -99) AS promo_id, COALESCE(G.tag_doc_id, -99) AS product_launching_id
204 FROM fi_invoice_ar_balance D
205 INNER JOIN sl_invoice_temp A ON D.invoice_ar_id = A.invoice_temp_id AND D.doc_type_id = A.doc_type_id
206 INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
207 INNER JOIN sl_so_info E ON B.so_id = E.so_id AND E.flg_type_so = vReg
208 INNER JOIN valid_so_balance_so_invoice EX ON B.so_id = EX.so_id
209 INNER JOIN m_partner H ON D.partner_id = H.partner_id
210 LEFT OUTER JOIN sl_so_tagging C ON B.tenant_id = C.tenant_id AND B.so_id = C.so_id AND C.tag_key = 'MOU'
211 LEFT OUTER JOIN sl_so_tagging F ON B.tenant_id = F.tenant_id AND B.so_id = F.so_id AND F.tag_key = 'PROMO'
212 LEFT OUTER JOIN sl_so_tagging G ON B.tenant_id = G.tenant_id AND B.so_id = G.so_id AND G.tag_key = 'LAUNCHING'
213 WHERE D.tenant_id = pTenantId
214 AND D.ou_id = pOuId
215 AND D.doc_type_id = vDocTypeSalesInvoiceTemp
216 AND H.partner_code BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
217 AND A.doc_date BETWEEN pDateFrom AND pDateTo
218 AND A.total_amount <> 0
219 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, D.ou_id) = vOne
220 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, D.partner_id) = vOne
221 AND A.status_doc <> 'V';
222 ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
223 RAISE NOTICE 'Get Data TSI ALL Start=%', clock_timestamp();
224 WITH valid_so_balance_so_invoice AS (
225 SELECT A.so_id
226 FROM sl_so_balance_invoice A
227 WHERE A.ref_doc_type_id = vDocTypeDo
228 GROUP BY A.so_id
229 HAVING SUM(A.qty_dlv_so) > 0
230 )
231 INSERT INTO tt_sl_invoice(
232 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
233 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
234 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
235 advance_amount, tax_amount, add_amount, total_amount, status_doc,
236 workflow_status, version, ref_inv_temp_id, discount_amount,
237 mou_id, invoice_status, payment_amount,
238 so_date, promo_id, product_launching_id)
239 SELECT pSessionId, A.invoice_temp_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
240 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
241 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, COALESCE(A.gross_amount, 0),
242 A.advance_amount, COALESCE(A.tax_amount, 0), A.add_amount, A.total_amount, A.status_doc,
243 A.workflow_status, A.version, -99, A.discount_amount,
244 COALESCE(C.tag_doc_id, -99) AS mou_id, vInvoiceTemp, D.payment_amount,
245 B.doc_date AS so_date, COALESCE(F.tag_doc_id, -99) AS promo_id, COALESCE(G.tag_doc_id, -99) AS product_launching_id
246 FROM fi_invoice_ar_balance D
247 INNER JOIN sl_invoice_temp A ON D.invoice_ar_id = A.invoice_temp_id AND D.doc_type_id = A.doc_type_id
248 INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
249 INNER JOIN sl_so_info E ON B.so_id = E.so_id AND E.flg_type_so = vReg
250 INNER JOIN valid_so_balance_so_invoice EX ON B.so_id = EX.so_id
251 LEFT OUTER JOIN sl_so_tagging C ON B.tenant_id = C.tenant_id AND B.so_id = C.so_id AND C.tag_key = 'MOU'
252 LEFT OUTER JOIN sl_so_tagging F ON B.tenant_id = F.tenant_id AND B.so_id = F.so_id AND F.tag_key = 'PROMO'
253 LEFT OUTER JOIN sl_so_tagging G ON B.tenant_id = G.tenant_id AND B.so_id = G.so_id AND G.tag_key = 'LAUNCHING'
254 WHERE D.tenant_id = pTenantId
255 AND D.ou_id = pOuId
256 AND D.doc_type_id = vDocTypeSalesInvoiceTemp
257 AND A.doc_date BETWEEN pDateFrom AND pDateTo
258 AND A.total_amount <> 0
259 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, D.ou_id) = vOne
260 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, D.partner_id) = vOne
261 AND A.status_doc <> 'V';
262 END IF;
263 RAISE NOTICE 'Get Data TSI End=%', clock_timestamp();
264
265 -- MASUKAN FI INVOICE DN AR APPROVED YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
266 IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
267 RAISE NOTICE 'Get Data DN AR <> ALL Start=%', clock_timestamp();
268 INSERT INTO tt_sl_invoice(
269 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
270 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
271 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
272 advance_amount, tax_amount, add_amount, total_amount, status_doc,
273 workflow_status, version, ref_inv_temp_id, discount_amount,
274 mou_id, invoice_status, payment_amount,
275 so_date, promo_id, product_launching_id)
276 SELECT pSessionId, A.invoice_ar_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
277 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
278 -99 AS ou_legal_id, A.due_date, -99 AS salesman_id, A.curr_code, 0 AS gross_amount,
279 0 AS advance_amount, A.tax_amount, 0 AS add_amount, A.total_amount, A.status_doc,
280 A.workflow_status, A.version, -99, 0 AS discount_amount,
281 COALESCE(B.tag_doc_id, -99) AS mou_id, vDebtNoteAr, C.payment_amount,
282 '' AS so_date, -99 AS product_launching_id, -99 AS product_launching_id
283 FROM fi_invoice_ar_balance C
284 INNER JOIN fi_invoice_ar A ON C.invoice_ar_id = A.invoice_ar_id AND C.doc_type_id = A.doc_type_id
285 INNER JOIN m_partner D ON C.partner_id = D.partner_id
286 LEFT OUTER JOIN fi_invoice_ar_tagging B ON A.tenant_id = B.tenant_id
287 AND A.invoice_ar_id = B.invoice_ar_id
288 AND B.tag_key = 'MOU'
289 WHERE C.tenant_id = pTenantId
290 AND C.ou_id = pOuId
291 AND C.doc_type_id = vDocTypeDNAR
292 AND D.partner_code BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
293 AND A.doc_date BETWEEN pDateFrom AND pDateTo
294 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
295 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
296 ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
297 RAISE NOTICE 'Get Data DN AR ALL Start=%', clock_timestamp();
298 INSERT INTO tt_sl_invoice(
299 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
300 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
301 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
302 advance_amount, tax_amount, add_amount, total_amount, status_doc,
303 workflow_status, version, ref_inv_temp_id, discount_amount,
304 mou_id, invoice_status, payment_amount,
305 so_date, promo_id, product_launching_id)
306 SELECT pSessionId, A.invoice_ar_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
307 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
308 -99 AS ou_legal_id, A.due_date, -99 AS salesman_id, A.curr_code, 0 AS gross_amount,
309 0 AS advance_amount, A.tax_amount, 0 AS add_amount, A.total_amount, A.status_doc,
310 A.workflow_status, A.version, -99, 0 AS discount_amount,
311 COALESCE(B.tag_doc_id, -99) AS mou_id, vDebtNoteAr, C.payment_amount,
312 '' AS so_date, -99 AS product_launching_id, -99 AS product_launching_id
313 FROM fi_invoice_ar_balance C
314 INNER JOIN fi_invoice_ar A ON C.invoice_ar_id = A.invoice_ar_id AND C.doc_type_id = A.doc_type_id
315 LEFT OUTER JOIN fi_invoice_ar_tagging B ON A.tenant_id = B.tenant_id
316 AND A.invoice_ar_id = B.invoice_ar_id
317 AND B.tag_key = 'MOU'
318 WHERE C.tenant_id = pTenantId
319 AND C.ou_id = pOuId
320 AND C.doc_type_id = vDocTypeDNAR
321 AND A.doc_date BETWEEN pDateFrom AND pDateTo
322 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
323 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
324 END IF;
325 RAISE NOTICE 'Get Data DN AR End=%', clock_timestamp();
326
327 -- MASUKAN FI INVOICE CN AR APPROVED YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
328 IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
329 RAISE NOTICE 'Get Data CN AR <> ALL Start=%', clock_timestamp();
330 INSERT INTO tt_sl_invoice(
331 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
332 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
333 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
334 advance_amount, tax_amount, add_amount, total_amount, status_doc,
335 workflow_status, version, ref_inv_temp_id, discount_amount,
336 mou_id, invoice_status, payment_amount,
337 so_date, promo_id, product_launching_id)
338 SELECT pSessionId, A.invoice_ar_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
339 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
340 -99 AS ou_legal_id, A.due_date, -99 AS salesman_id, A.curr_code, 0 AS gross_amount,
341 0 AS advance_amount, A.tax_amount * -1 AS tax_amount, 0 AS add_amount, A.total_amount * -1 AS total_amount, A.status_doc,
342 A.workflow_status, A.version, -99, 0 AS discount_amount,
343 COALESCE(B.tag_doc_id, -99) AS mou_id, vCreditNoteAr, C.payment_amount,
344 '' AS so_date, -99 AS product_launching_id, -99 AS product_launching_id
345 FROM fi_invoice_ar_balance C
346 INNER JOIN fi_invoice_ar A ON C.invoice_ar_id = A.invoice_ar_id AND C.doc_type_id = A.doc_type_id
347 INNER JOIN m_partner D ON C.partner_id = D.partner_id
348 LEFT OUTER JOIN fi_invoice_ar_tagging B ON A.tenant_id = B.tenant_id
349 AND A.invoice_ar_id = B.invoice_ar_id
350 AND B.tag_key = 'MOU'
351 WHERE C.tenant_id = pTenantId
352 AND C.ou_id = pOuId
353 AND C.doc_type_id = vDocTypeCNAR
354 AND D.partner_code BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
355 AND A.doc_date BETWEEN pDateFrom AND pDateTo
356 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
357 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
358 ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
359 RAISE NOTICE 'Get Data CN AR ALL Start=%', clock_timestamp();
360 INSERT INTO tt_sl_invoice(
361 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
362 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
363 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
364 advance_amount, tax_amount, add_amount, total_amount, status_doc,
365 workflow_status, version, ref_inv_temp_id, discount_amount,
366 mou_id, invoice_status, payment_amount,
367 so_date, promo_id, product_launching_id)
368 SELECT pSessionId, A.invoice_ar_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
369 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
370 -99 AS ou_legal_id, A.due_date, -99 AS salesman_id, A.curr_code, 0 AS gross_amount,
371 0 AS advance_amount, A.tax_amount * -1 AS tax_amount, 0 AS add_amount, A.total_amount * -1 AS total_amount, A.status_doc,
372 A.workflow_status, A.version, -99, 0 AS discount_amount,
373 COALESCE(B.tag_doc_id, -99) AS mou_id, vCreditNoteAr, C.payment_amount,
374 '' AS so_date, -99 AS product_launching_id, -99 AS product_launching_id
375 FROM fi_invoice_ar_balance C
376 INNER JOIN fi_invoice_ar A ON C.invoice_ar_id = A.invoice_ar_id AND C.doc_type_id = A.doc_type_id
377 LEFT OUTER JOIN fi_invoice_ar_tagging B ON A.tenant_id = B.tenant_id
378 AND A.invoice_ar_id = B.invoice_ar_id
379 AND B.tag_key = 'MOU'
380 WHERE C.tenant_id = pTenantId
381 AND C.ou_id = pOuId
382 AND C.doc_type_id = vDocTypeCNAR
383 AND A.doc_date BETWEEN pDateFrom AND pDateTo
384 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
385 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
386 END IF;
387 RAISE NOTICE 'Get Data CN AR End=%', clock_timestamp();
388
389 -- MASUKAN SO TRIAL, FOC, REVISI, SAMPLE DILIHAT DARI DATA TEMPORARY INVOICE --
390 IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
391 RAISE NOTICE 'Get Data SO FOC <> ALL Start=%', clock_timestamp();
392 WITH valid_so_balance_so_invoice AS (
393 SELECT A.so_id
394 FROM sl_so_balance_invoice A
395 INNER JOIN m_partner B ON A.partner_id = B.partner_id
396 WHERE A.ref_doc_type_id = vDocTypeDo
397 AND B.partner_code BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
398 GROUP BY A.so_id
399 HAVING SUM(A.qty_dlv_so) > 0
400 )
401 INSERT INTO tt_sl_invoice(
402 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
403 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
404 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
405 advance_amount, tax_amount, add_amount, total_amount, status_doc,
406 workflow_status, version, ref_inv_temp_id, discount_amount,
407 mou_id, invoice_status, payment_amount,
408 so_date, promo_id, product_launching_id)
409 SELECT pSessionId, B.so_id, A.tenant_id, B.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
410 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
411 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, COALESCE(A.gross_amount, 0),
412 A.advance_amount, COALESCE(A.tax_amount, 0), A.add_amount, A.total_amount, A.status_doc,
413 A.workflow_status, A.version, -99, A.discount_amount,
414 COALESCE(C.tag_doc_id, -99) AS mou_id,
415 CASE WHEN (E.flg_type_so = vRvs)
416 THEN 'Revisi'
417 WHEN (E.flg_type_so = vTrl)
418 THEN 'Trial'
419 WHEN (E.flg_type_so = vSmp)
420 THEN 'Sample'
421 WHEN (E.flg_type_so = vFoc)
422 THEN 'FOC'
423 END AS invoice_status, D.payment_amount,
424 B.doc_date AS so_date, COALESCE(F.tag_doc_id, -99) AS promo_id, COALESCE(G.tag_doc_id, -99) AS product_launching_id
425 FROM fi_invoice_ar_balance D
426 INNER JOIN sl_invoice_temp A ON D.invoice_ar_id = A.invoice_temp_id AND D.doc_type_id = A.doc_type_id
427 INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
428 INNER JOIN sl_so_info E ON B.so_id = E.so_id
429 INNER JOIN valid_so_balance_so_invoice EX ON B.so_id = EX.so_id
430 INNER JOIN m_partner H ON D.partner_id = H.partner_id
431 LEFT OUTER JOIN sl_so_tagging C ON B.tenant_id = C.tenant_id AND B.so_id = C.so_id AND C.tag_key = 'MOU'
432 LEFT OUTER JOIN sl_so_tagging F ON B.tenant_id = F.tenant_id AND B.so_id = F.so_id AND F.tag_key = 'PROMO'
433 LEFT OUTER JOIN sl_so_tagging G ON B.tenant_id = G.tenant_id AND B.so_id = G.so_id AND G.tag_key = 'LAUNCHING'
434 WHERE D.tenant_id = pTenantId
435 AND D.ou_id = pOuId
436 AND D.doc_type_id = vDocTypeSalesInvoiceTemp
437 AND H.partner_code BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
438 AND A.doc_date BETWEEN pDateFrom AND pDateTo
439 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, D.ou_id) = vOne
440 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, D.partner_id) = vOne
441 AND A.status_doc <> 'V'
442 AND E.flg_type_so IN (vRvs,vTrl,vSmp,vFoc);
443 ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
444 RAISE NOTICE 'Get Data SO FOC ALL Start=%', clock_timestamp();
445 WITH valid_so_balance_so_invoice AS (
446 SELECT A.so_id
447 FROM sl_so_balance_invoice A
448 WHERE A.ref_doc_type_id = vDocTypeDo
449 GROUP BY A.so_id
450 HAVING SUM(A.qty_dlv_so) > 0
451 )
452 INSERT INTO tt_sl_invoice(
453 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
454 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
455 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
456 advance_amount, tax_amount, add_amount, total_amount, status_doc,
457 workflow_status, version, ref_inv_temp_id, discount_amount,
458 mou_id, invoice_status, payment_amount,
459 so_date, promo_id, product_launching_id)
460 SELECT pSessionId, B.so_id, A.tenant_id, B.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
461 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
462 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, COALESCE(A.gross_amount, 0),
463 A.advance_amount, COALESCE(A.tax_amount, 0), A.add_amount, A.total_amount, A.status_doc,
464 A.workflow_status, A.version, -99, A.discount_amount,
465 COALESCE(C.tag_doc_id, -99) AS mou_id,
466 CASE WHEN (E.flg_type_so = vRvs)
467 THEN 'Revisi'
468 WHEN (E.flg_type_so = vTrl)
469 THEN 'Trial'
470 WHEN (E.flg_type_so = vSmp)
471 THEN 'Sample'
472 WHEN (E.flg_type_so = vFoc)
473 THEN 'FOC'
474 END AS invoice_status, D.payment_amount,
475 B.doc_date AS so_date, COALESCE(F.tag_doc_id, -99) AS promo_id, COALESCE(G.tag_doc_id, -99) AS product_launching_id
476 FROM fi_invoice_ar_balance D
477 INNER JOIN sl_invoice_temp A ON D.invoice_ar_id = A.invoice_temp_id AND D.doc_type_id = A.doc_type_id
478 INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
479 INNER JOIN sl_so_info E ON B.so_id = E.so_id
480 INNER JOIN valid_so_balance_so_invoice EX ON B.so_id = EX.so_id
481 LEFT OUTER JOIN sl_so_tagging C ON B.tenant_id = C.tenant_id AND B.so_id = C.so_id AND C.tag_key = 'MOU'
482 LEFT OUTER JOIN sl_so_tagging F ON B.tenant_id = F.tenant_id AND B.so_id = F.so_id AND F.tag_key = 'PROMO'
483 LEFT OUTER JOIN sl_so_tagging G ON B.tenant_id = G.tenant_id AND B.so_id = G.so_id AND G.tag_key = 'LAUNCHING'
484 WHERE D.tenant_id = pTenantId
485 AND D.ou_id = pOuId
486 AND D.doc_type_id = vDocTypeSalesInvoiceTemp
487 AND A.doc_date BETWEEN pDateFrom AND pDateTo
488 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, D.ou_id) = vOne
489 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, D.partner_id) = vOne
490 AND A.status_doc <> 'V'
491 AND E.flg_type_so IN (vRvs,vTrl,vSmp,vFoc);
492 END IF;
493 RAISE NOTICE 'Get Data SO FOC End=%', clock_timestamp();
494
495
496 -- MASUKAN KE TABLE TEMP UNTUK DATA RSIB YG TIDAK MEMILIKI REFERENSI DOKUMEN SOB
497 IF (pCustomerCodeFrom!='ALL' AND pCustomerCodeTo!='ALL') THEN
498 RAISE NOTICE 'Get Data RSIB with Reference <> ALL Start=%', clock_timestamp();
499 INSERT INTO tt_sl_invoice(
500 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
501 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
502 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
503 advance_amount, tax_amount, add_amount, total_amount, status_doc,
504 workflow_status, version, ref_inv_temp_id, discount_amount,
505 mou_id, invoice_status, payment_amount,
506 so_date, promo_id, product_launching_id)
507 SELECT pSessionId, B.invoice_id, B.tenant_id, B.doc_type_id, B.doc_no, B.doc_date, B.ou_id,
508 B.ext_doc_no, B.ext_doc_date, B.ref_doc_type_id, B.ref_id, B.remark, B.partner_id,
509 B.ou_legal_id, B.due_date, B.salesman_id, B.curr_code, -1*B.gross_amount,
510 -1*B.advance_amount, -1*B.tax_amount, -1*B.add_amount, -1*B.total_amount, B.status_doc,
511 B.workflow_status, B.version, B.ref_inv_temp_id, B.discount_amount,
512 COALESCE(E.tag_doc_id, -99) AS mou_id, vReturnInvoice, A.payment_amount,
513 COALESCE(F.doc_date, '') AS so_date, -99 AS promo_id, -99 AS product_launching_id
514 FROM fi_invoice_ar_balance A
515 INNER JOIN sl_invoice B ON A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
516 INNER JOIN sl_request_return_sales C ON B.ref_id = C.request_return_sales_id AND B.ref_doc_type_id = C.doc_type_id
517 INNER JOIN in_inventory D ON C.request_return_sales_id = D.ref_id AND C.doc_type_id = D.ref_doc_type_id
518 INNER JOIN m_partner G ON A.partner_id = G.partner_id
519 LEFT OUTER JOIN in_return_tagging E ON D.tenant_id = E.tenant_id
520 AND D.inventory_id = E.inventory_id
521 AND E.tag_key = 'MOU'
522 LEFT OUTER JOIN sl_so F ON C.ref_id = F.so_id AND C.ref_doc_type_id = F.doc_type_id
523 WHERE A.tenant_id = pTenantId
524 AND A.ou_id = pOuId
525 AND A.doc_type_id = vDocTypeRSIB
526 AND B.doc_date BETWEEN pDateFrom AND pDateTo
527 AND G.partner_code BETWEEN pCustomerCodeFrom AND pCustomerCodeTo
528 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, A.ou_id) = vOne
529 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = vOne
530 AND C.doc_type_id = vDocTypeRRS;
531 ELSIF(pCustomerCodeFrom='ALL' AND pCustomerCodeTo='ALL') THEN
532 RAISE NOTICE 'Get Data RSIB with Reference ALL Start=%', clock_timestamp();
533 INSERT INTO tt_sl_invoice(
534 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
535 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
536 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
537 advance_amount, tax_amount, add_amount, total_amount, status_doc,
538 workflow_status, version, ref_inv_temp_id, discount_amount,
539 mou_id, invoice_status, payment_amount,
540 so_date, promo_id, product_launching_id)
541 SELECT pSessionId, B.invoice_id, B.tenant_id, B.doc_type_id, B.doc_no, B.doc_date, B.ou_id,
542 B.ext_doc_no, B.ext_doc_date, B.ref_doc_type_id, B.ref_id, B.remark, B.partner_id,
543 B.ou_legal_id, B.due_date, B.salesman_id, B.curr_code, -1*B.gross_amount,
544 -1*B.advance_amount, -1*B.tax_amount, -1*B.add_amount, -1*B.total_amount, B.status_doc,
545 B.workflow_status, B.version, B.ref_inv_temp_id, B.discount_amount,
546 COALESCE(E.tag_doc_id, -99) AS mou_id, vReturnInvoice, A.payment_amount,
547 COALESCE(F.doc_date, '') AS so_date, -99 AS promo_id, -99 AS product_launching_id
548 FROM fi_invoice_ar_balance A
549 INNER JOIN sl_invoice B ON A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
550 INNER JOIN sl_request_return_sales C ON B.ref_id = C.request_return_sales_id AND B.ref_doc_type_id = C.doc_type_id
551 INNER JOIN in_inventory D ON C.request_return_sales_id = D.ref_id AND C.doc_type_id = D.ref_doc_type_id
552 LEFT OUTER JOIN in_return_tagging E ON D.tenant_id = E.tenant_id
553 AND D.inventory_id = E.inventory_id
554 AND E.tag_key = 'MOU'
555 LEFT OUTER JOIN sl_so F ON C.ref_id = F.so_id AND C.ref_doc_type_id = F.doc_type_id
556 WHERE A.tenant_id = pTenantId
557 AND A.ou_id = pOuId
558 AND A.doc_type_id = vDocTypeRSIB
559 AND B.doc_date BETWEEN pDateFrom AND pDateTo
560 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, A.ou_id) = vOne
561 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = vOne
562 AND C.doc_type_id = vDocTypeRRS;
563 END IF;
564 RAISE NOTICE 'Get Data RSIB With Reference End=%', clock_timestamp();
565
566
567 RAISE NOTICE 'Get REF DOC NO DARI INVOICE, TEMP INVOICE & SOB=%', clock_timestamp();
568 -- REF DOC NO DARI INVOICE, TEMP INVOICE & SOB (TRIAL, FOC, SAMPLE, REVISI) --
569 INSERT INTO tt_report_sales_journal(
570 session_id, data_id, sales_invoice_id, doc_type_id, ref_doc_no)
571 SELECT pSessionId, vRawData, a.invoice_id, a.doc_type_id, COALESCE(d.awb_no, '-')
572 FROM tt_sl_invoice a
573 INNER JOIN sl_do b ON a.ref_id = b.ref_id AND b.ref_doc_type_id = vDocTypeSoByBrand
574 INNER JOIN in_item_log_book_balance c ON b.tenant_id = c.tenant_id
575 AND b.ou_id = c.ou_id
576 AND b.doc_type_id = c.ref_doc_type_id
577 AND b.doc_date = c.ref_doc_date
578 AND b.doc_no = c.ref_doc_no
579 LEFT OUTER JOIN in_awb_log_book d ON c.awb_log_book_id = d.awb_log_book_id
580 WHERE a.session_id = pSessionId
581 AND a.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeSalesInvoiceTemp, vDocTypeSoByBrand);
582
583 RAISE NOTICE 'Get REF DOC NO DARI RSIB=%', clock_timestamp();
584 -- REF DOC NO DARI RSIB --
585 INSERT INTO tt_report_sales_journal(
586 session_id, data_id, sales_invoice_id, doc_type_id, ref_doc_no)
587 SELECT pSessionId, vRawData, A.invoice_id, A.doc_type_id, COALESCE(C.doc_no, COALESCE(D.doc_no, '-'))
588 FROM tt_sl_invoice A
589 INNER JOIN sl_request_return_sales B ON A.ref_id = B.request_return_sales_id AND A.ref_doc_type_id = B.doc_type_id
590 LEFT OUTER JOIN sl_invoice C
591 ON B.ref_id = C.ref_id
592 AND B.ref_doc_type_id = C.ref_doc_type_id
593 AND C.doc_type_id = vDocTypeSalesInvoice
594 LEFT OUTER JOIN sl_invoice_temp D
595 ON B.ref_id = D.ref_id
596 AND B.ref_doc_type_id = D.ref_doc_type_id
597 AND D.doc_type_id = vDocTypeSalesInvoiceTemp
598 AND D.status_doc <> 'V'
599 WHERE A.session_id = pSessionId
600 AND A.doc_type_id = vDocTypeRSIB;
601
602 RAISE NOTICE 'Get REF DOC NO dari DN/CN Ar =%', clock_timestamp();
603 -- REF DOC NO dari DN/CN Ar pasti nggak ada
604 INSERT INTO tt_report_sales_journal(
605 session_id, data_id, sales_invoice_id, doc_type_id, ref_doc_no)
606 SELECT pSessionId, vRawData, a.invoice_id, a.doc_type_id, '-'
607 FROM tt_sl_invoice a
608 WHERE a.session_id = pSessionId
609 AND a.doc_type_id IN(vDocTypeDNAR, vDocTypeCNAR);
610
611 RAISE NOTICE 'Get SUM PAYMENT AMOUNT =%', clock_timestamp();
612 -- SUM PAYMENT AMOUNT --
613 INSERT INTO tt_report_sales_journal(
614 session_id, data_id, sales_invoice_id, doc_type_id, payment_amount, ref_doc_no)
615 SELECT pSessionId, vRawData, a.invoice_id, a.doc_type_id, a.payment_amount, b.ref_doc_no
616 FROM tt_sl_invoice a
617 INNER JOIN tt_report_sales_journal b ON a.invoice_id = b.sales_invoice_id
618 AND a.doc_type_id = b.doc_type_id
619 WHERE a.session_id = pSessionId
620 AND b.session_id = pSessionId
621 GROUP BY a.invoice_id, a.doc_type_id, a.payment_amount, b.ref_doc_no;
622
623
624 RAISE NOTICE 'Get SUM DATA =%', clock_timestamp();
625 -- SUM DATA --
626 INSERT INTO tt_report_sales_journal (session_id, data_id, sales_invoice_id, doc_type_id,
627 add_amount,
628 payment_amount,
629 retur_amount,
630 ref_doc_no)
631 SELECT pSessionId, vSumData, sales_invoice_id, doc_type_id,
632 SUM(add_amount) AS add_amount,
633 SUM(payment_amount) AS payment_amount,
634 SUM(retur_amount) AS retur_amount,
635 ref_doc_no
636 FROM tt_report_sales_journal
637 WHERE session_id = pSessionId
638 AND data_id = vRawData
639 GROUP BY sales_invoice_id, doc_type_id, ref_doc_no;
640
641 -- DELETE TEMP RAW-DATA --
642 DELETE FROM tt_report_sales_journal WHERE data_id=vRawData AND session_id = pSessionId;
643
644 -- DETAIL --
645 IF pPaymentStatus = vSettledPaymentStatus THEN
646 RAISE NOTICE 'SETTLED = %', pPaymentStatus;
647 RAISE NOTICE 'RefDetail SETTLED Start =%', clock_timestamp();
648
649 Open pRefDetail FOR
650 EXECUTE
651 'WITH sl_inv_sub_cat AS (
652 SELECT a.invoice_id, a.doc_type_id,
653 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
654 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
655 FROM tt_sl_invoice a
656 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $1
657 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
658 INNER JOIN m_product d ON c.product_id = d.product_id
659 WHERE a.doc_type_id = $2
660 AND a.session_id = $3
661 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
662 ), sls_inv_temp_sub_cat AS (
663 SELECT a.invoice_id, a.doc_type_id,
664 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
665 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
666 FROM tt_sl_invoice a
667 INNER JOIN sl_invoice_temp_item b ON a.invoice_id = b.invoice_temp_id AND b.ref_doc_type_id = $1
668 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
669 INNER JOIN m_product d ON c.product_id = d.product_id
670 WHERE a.doc_type_id = $5
671 AND a.session_id = $3
672 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
673 ), sls_order_sub_cat AS (
674 SELECT a.invoice_id, a.doc_type_id,
675 f_get_sub_ctgr_product_name(c.sub_ctgr_product_id) AS sub_ctgr_product_name,
676 f_get_sub_ctgr_product_code(c.sub_ctgr_product_id) AS sub_ctgr_product_code
677 FROM tt_sl_invoice a
678 INNER JOIN sl_so_item b ON a.invoice_id = b.so_id
679 INNER JOIN m_product c ON b.product_id = c.product_id
680 WHERE a.doc_type_id = $12
681 AND a.session_id = $3
682 GROUP BY a.invoice_id, a.doc_type_id, c.sub_ctgr_product_id
683 ), return_sls_sub_cat AS (
684 SELECT a.invoice_id, a.doc_type_id,
685 f_get_sub_ctgr_product_name(e.sub_ctgr_product_id) AS sub_ctgr_product_name,
686 f_get_sub_ctgr_product_code(e.sub_ctgr_product_id) AS sub_ctgr_product_code
687 FROM tt_sl_invoice a
688 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $15
689 INNER JOIN in_inventory c ON b.ref_id = c.inventory_id AND b.ref_doc_type_id = c.doc_type_id
690 INNER JOIN in_inventory_item d ON c.inventory_id = d.inventory_id
691 INNER JOIN m_product e ON d.product_id = e.product_id
692 WHERE a.doc_type_id = $14
693 AND a.session_id = $3
694 GROUP BY a.invoice_id, a.doc_type_id, e.sub_ctgr_product_id
695 ), category_invoice AS (
696 SELECT a.invoice_id, a.doc_type_id,
697 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
698 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
699 FROM sl_inv_sub_cat a
700 GROUP BY a.invoice_id, a.doc_type_id
701 UNION
702 SELECT a.invoice_id, a.doc_type_id,
703 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
704 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
705 FROM sls_inv_temp_sub_cat a
706 GROUP BY a.invoice_id, a.doc_type_id
707 UNION
708 SELECT a.invoice_id, a.doc_type_id,
709 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
710 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
711 FROM sls_order_sub_cat a
712 GROUP BY a.invoice_id, a.doc_type_id
713 UNION
714 SELECT a.invoice_id, a.doc_type_id,
715 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
716 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
717 FROM return_sls_sub_cat a
718 GROUP BY a.invoice_id, a.doc_type_id
719 UNION
720 SELECT a.invoice_id, a.doc_type_id,
721 ''-'' AS sub_ctgr_product_name,
722 ''-'' AS sub_ctgr_product_code
723 FROM tt_sl_invoice a
724 WHERE a.doc_type_id IN ($6, $7 )
725 AND a.session_id = $3
726 GROUP BY a.invoice_id, a.doc_type_id
727 )
728 SELECT f_get_partner_code(a.partner_id) AS partner_code, f_get_partner_name(a.partner_id) AS partner_name,
729 f_get_city_by_partner(a.partner_id) AS city, a.doc_no AS invoice_no, c.sub_ctgr_product_name, COALESCE(f.doc_no, ''-'') AS mou_no,
730 a.doc_date AS invoice_date, a.due_date, a.total_amount, d.add_amount AS ongkir, d.retur_amount AS retur,
731 d.payment_amount, a.tax_amount AS pot_bayar, (a.total_amount+a.tax_amount)-d.payment_amount AS sisa, a.invoice_status, d.no_resi,
732 d.ref_doc_no, a.so_date, COALESCE(g.promo_code, ''-'') AS promo, COALESCE(h.promo_code, ''-'') AS launching
733 FROM tt_sl_invoice a
734 INNER JOIN category_invoice c ON a.invoice_id = c.invoice_id AND a.doc_type_id = c.doc_type_id '||vFilterSubCtgr||'
735 INNER JOIN tt_report_sales_journal d ON a.invoice_id = d.sales_invoice_id AND a.doc_type_id = d.doc_type_id AND d.session_id = $3
736 LEFT OUTER JOIN sl_mou f ON a.mou_id = f.mou_id
737 LEFT OUTER JOIN m_promo g ON a.promo_id = g.promo_id
738 LEFT OUTER JOIN m_promo h ON a.product_launching_id = h.promo_id
739 WHERE a.session_id = $3
740 AND ((a.total_amount+a.tax_amount)-d.payment_amount) = $13
741 ORDER BY f_get_partner_name(a.partner_id), a.doc_date, a.invoice_status'
742 USING vDocTypeDo,vDocTypeSalesInvoice,pSessionId,vDocTypeConvExcOut,vDocTypeSalesInvoiceTemp,vDocTypeDNAR,vDocTypeCNAR,pTenantId,pUserId,pRoleId,vOne,vDocTypeSoByBrand,vNolAmount,vDocTypeRSIB,vDocTypeRN;
743 RETURN NEXT pRefDetail;
744 ELSE
745 IF pPaymentStatus = vNotYetSettledPaymentStatus THEN
746 RAISE NOTICE 'NOT YET SETTLED = %', pPaymentStatus;
747 RAISE NOTICE 'RefDetail NOT YET SETTLED Start =%', clock_timestamp();
748
749 Open pRefDetail FOR
750 EXECUTE
751 'WITH sl_inv_sub_cat AS (
752 SELECT a.invoice_id, a.doc_type_id,
753 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
754 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
755 FROM tt_sl_invoice a
756 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $1
757 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
758 INNER JOIN m_product d ON c.product_id = d.product_id
759 WHERE a.doc_type_id = $2
760 AND a.session_id = $3
761 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
762 ), sls_inv_temp_sub_cat AS (
763 SELECT a.invoice_id, a.doc_type_id,
764 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
765 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
766 FROM tt_sl_invoice a
767 INNER JOIN sl_invoice_temp_item b ON a.invoice_id = b.invoice_temp_id AND b.ref_doc_type_id = $1
768 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
769 INNER JOIN m_product d ON c.product_id = d.product_id
770 WHERE a.doc_type_id = $5
771 AND a.session_id = $3
772 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
773 ), sls_order_sub_cat AS (
774 SELECT a.invoice_id, a.doc_type_id,
775 f_get_sub_ctgr_product_name(c.sub_ctgr_product_id) AS sub_ctgr_product_name,
776 f_get_sub_ctgr_product_code(c.sub_ctgr_product_id) AS sub_ctgr_product_code
777 FROM tt_sl_invoice a
778 INNER JOIN sl_so_item b ON a.invoice_id = b.so_id
779 INNER JOIN m_product c ON b.product_id = c.product_id
780 WHERE a.doc_type_id = $12
781 AND a.session_id = $3
782 GROUP BY a.invoice_id, a.doc_type_id, c.sub_ctgr_product_id
783 ), return_sls_sub_cat AS (
784 SELECT a.invoice_id, a.doc_type_id,
785 f_get_sub_ctgr_product_name(e.sub_ctgr_product_id) AS sub_ctgr_product_name,
786 f_get_sub_ctgr_product_code(e.sub_ctgr_product_id) AS sub_ctgr_product_code
787 FROM tt_sl_invoice a
788 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $15
789 INNER JOIN in_inventory c ON b.ref_id = c.inventory_id AND b.ref_doc_type_id = c.doc_type_id
790 INNER JOIN in_inventory_item d ON c.inventory_id = d.inventory_id
791 INNER JOIN m_product e ON d.product_id = e.product_id
792 WHERE a.doc_type_id = $14
793 AND a.session_id = $3
794 GROUP BY a.invoice_id, a.doc_type_id, e.sub_ctgr_product_id
795 ), category_invoice AS (
796 SELECT a.invoice_id, a.doc_type_id,
797 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
798 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
799 FROM sl_inv_sub_cat a
800 GROUP BY a.invoice_id, a.doc_type_id
801 UNION
802 SELECT a.invoice_id, a.doc_type_id,
803 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
804 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
805 FROM sls_inv_temp_sub_cat a
806 GROUP BY a.invoice_id, a.doc_type_id
807 UNION
808 SELECT a.invoice_id, a.doc_type_id,
809 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
810 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
811 FROM sls_order_sub_cat a
812 GROUP BY a.invoice_id, a.doc_type_id
813 UNION
814 SELECT a.invoice_id, a.doc_type_id,
815 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
816 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
817 FROM return_sls_sub_cat a
818 GROUP BY a.invoice_id, a.doc_type_id
819 UNION
820 SELECT a.invoice_id, a.doc_type_id,
821 ''-'' AS sub_ctgr_product_name,
822 ''-'' AS sub_ctgr_product_code
823 FROM tt_sl_invoice a
824 WHERE a.doc_type_id IN ($6, $7 )
825 AND a.session_id = $3
826 GROUP BY a.invoice_id, a.doc_type_id
827 )
828 SELECT f_get_partner_code(a.partner_id) AS partner_code, f_get_partner_name(a.partner_id) AS partner_name,
829 f_get_city_by_partner(a.partner_id) AS city, a.doc_no AS invoice_no, c.sub_ctgr_product_name, COALESCE(f.doc_no, ''-'') AS mou_no,
830 a.doc_date AS invoice_date, a.due_date, a.total_amount, d.add_amount AS ongkir, d.retur_amount AS retur,
831 d.payment_amount, a.tax_amount AS pot_bayar, (a.total_amount+a.tax_amount)-d.payment_amount AS sisa, a.invoice_status, d.no_resi,
832 d.ref_doc_no, a.so_date, COALESCE(g.promo_code, ''-'') AS promo, COALESCE(h.promo_code, ''-'') AS launching
833 FROM tt_sl_invoice a
834 INNER JOIN category_invoice c ON a.invoice_id = c.invoice_id AND a.doc_type_id = c.doc_type_id '||vFilterSubCtgr||'
835 INNER JOIN tt_report_sales_journal d ON a.invoice_id = d.sales_invoice_id AND a.doc_type_id = d.doc_type_id AND d.session_id = $3
836 LEFT OUTER JOIN sl_mou f ON a.mou_id = f.mou_id
837 LEFT OUTER JOIN m_promo g ON a.promo_id = g.promo_id
838 LEFT OUTER JOIN m_promo h ON a.product_launching_id = h.promo_id
839 WHERE a.session_id = $3
840 AND ((a.total_amount+a.tax_amount)-d.payment_amount) <> $13
841 ORDER BY f_get_partner_name(a.partner_id), a.doc_date, a.invoice_status'
842 USING vDocTypeDo,vDocTypeSalesInvoice,pSessionId,vDocTypeConvExcOut,vDocTypeSalesInvoiceTemp,vDocTypeDNAR,vDocTypeCNAR,pTenantId,pUserId,pRoleId,vOne,vDocTypeSoByBrand,vNolAmount,vDocTypeRSIB,vDocTypeRN;
843 RETURN NEXT pRefDetail;
844 ELSE
845 RAISE NOTICE 'ALL = %', pPaymentStatus;
846 RAISE NOTICE 'RefDetail ALL Start =%', clock_timestamp();
847
848 Open pRefDetail FOR
849 EXECUTE
850 'WITH sl_inv_sub_cat AS (
851 SELECT a.invoice_id, a.doc_type_id,
852 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
853 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
854 FROM tt_sl_invoice a
855 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $1
856 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
857 INNER JOIN m_product d ON c.product_id = d.product_id
858 WHERE a.doc_type_id = $2
859 AND a.session_id = $3
860 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
861 ), sls_inv_temp_sub_cat AS (
862 SELECT a.invoice_id, a.doc_type_id,
863 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
864 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
865 FROM tt_sl_invoice a
866 INNER JOIN sl_invoice_temp_item b ON a.invoice_id = b.invoice_temp_id AND b.ref_doc_type_id = $1
867 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
868 INNER JOIN m_product d ON c.product_id = d.product_id
869 WHERE a.doc_type_id = $5
870 AND a.session_id = $3
871 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
872 ), sls_order_sub_cat AS (
873 SELECT a.invoice_id, a.doc_type_id,
874 f_get_sub_ctgr_product_name(c.sub_ctgr_product_id) AS sub_ctgr_product_name,
875 f_get_sub_ctgr_product_code(c.sub_ctgr_product_id) AS sub_ctgr_product_code
876 FROM tt_sl_invoice a
877 INNER JOIN sl_so_item b ON a.invoice_id = b.so_id
878 INNER JOIN m_product c ON b.product_id = c.product_id
879 WHERE a.doc_type_id = $12
880 AND a.session_id = $3
881 GROUP BY a.invoice_id, a.doc_type_id, c.sub_ctgr_product_id
882 ), return_sls_sub_cat AS (
883 SELECT a.invoice_id, a.doc_type_id,
884 f_get_sub_ctgr_product_name(e.sub_ctgr_product_id) AS sub_ctgr_product_name,
885 f_get_sub_ctgr_product_code(e.sub_ctgr_product_id) AS sub_ctgr_product_code
886 FROM tt_sl_invoice a
887 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $14
888 INNER JOIN in_inventory c ON b.ref_id = c.inventory_id AND b.ref_doc_type_id = c.doc_type_id
889 INNER JOIN in_inventory_item d ON c.inventory_id = d.inventory_id
890 INNER JOIN m_product e ON d.product_id = e.product_id
891 WHERE a.doc_type_id = $13
892 AND a.session_id = $3
893 GROUP BY a.invoice_id, a.doc_type_id, e.sub_ctgr_product_id
894 ), category_invoice AS (
895 SELECT a.invoice_id, a.doc_type_id,
896 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
897 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
898 FROM sl_inv_sub_cat a
899 GROUP BY a.invoice_id, a.doc_type_id
900 UNION
901 SELECT a.invoice_id, a.doc_type_id,
902 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
903 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
904 FROM sls_inv_temp_sub_cat a
905 GROUP BY a.invoice_id, a.doc_type_id
906 UNION
907 SELECT a.invoice_id, a.doc_type_id,
908 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
909 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
910 FROM sls_order_sub_cat a
911 GROUP BY a.invoice_id, a.doc_type_id
912 UNION
913 SELECT a.invoice_id, a.doc_type_id,
914 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
915 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
916 FROM return_sls_sub_cat a
917 GROUP BY a.invoice_id, a.doc_type_id
918 UNION
919 SELECT a.invoice_id, a.doc_type_id,
920 ''-'' AS sub_ctgr_product_name,
921 ''-'' AS sub_ctgr_product_code
922 FROM tt_sl_invoice a
923 WHERE a.doc_type_id IN ($6, $7 )
924 AND a.session_id = $3
925 GROUP BY a.invoice_id, a.doc_type_id
926 )
927 SELECT f_get_partner_code(a.partner_id) AS partner_code, f_get_partner_name(a.partner_id) AS partner_name,
928 f_get_city_by_partner(a.partner_id) AS city, a.doc_no AS invoice_no, c.sub_ctgr_product_name, COALESCE(f.doc_no, ''-'') AS mou_no,
929 a.doc_date AS invoice_date, a.due_date, a.total_amount, d.add_amount AS ongkir, d.retur_amount AS retur,
930 d.payment_amount, a.tax_amount AS pot_bayar, (a.total_amount+a.tax_amount)-d.payment_amount AS sisa, a.invoice_status, d.no_resi,
931 d.ref_doc_no, a.so_date, COALESCE(g.promo_code, ''-'') AS promo, COALESCE(h.promo_code, ''-'') AS launching
932 FROM tt_sl_invoice a
933 INNER JOIN category_invoice c ON a.invoice_id = c.invoice_id AND a.doc_type_id = c.doc_type_id '||vFilterSubCtgr||'
934 INNER JOIN tt_report_sales_journal d ON a.invoice_id = d.sales_invoice_id AND a.doc_type_id = d.doc_type_id AND d.session_id = $3
935 LEFT OUTER JOIN sl_mou f ON a.mou_id = f.mou_id
936 LEFT OUTER JOIN m_promo g ON a.promo_id = g.promo_id
937 LEFT OUTER JOIN m_promo h ON a.product_launching_id = h.promo_id
938 WHERE a.session_id = $3
939 ORDER BY f_get_partner_name(a.partner_id), a.doc_date, a.invoice_status'
940 USING vDocTypeDo,vDocTypeSalesInvoice,pSessionId,vDocTypeConvExcOut,vDocTypeSalesInvoiceTemp,vDocTypeDNAR,vDocTypeCNAR,pTenantId,pUserId,pRoleId,vOne,vDocTypeSoByBrand,vDocTypeRSIB,vDocTypeRN;
941 RETURN NEXT pRefDetail;
942 END IF;
943 END IF;
944 RAISE NOTICE 'RefDetail End =%', clock_timestamp();
945
946
947 -- DELETE TEMP DATA --
948 DELETE FROM tt_sl_invoice WHERE session_id = pSessionId;
949 DELETE FROM tt_report_sales_journal WHERE session_id = pSessionId;
950
951 RAISE NOTICE 'END Proses Report Sales Journal =%', clock_timestamp();
952END;
953$BODY$
954 LANGUAGE plpgsql VOLATILE
955 COST 100
956/