· 5 years ago · May 06, 2020, 06:54 AM
1/**
2 * Referensi dari function r_sales_journal
3 */
4
5/* ada perubahan terkait parameter input , dari 11 menjadi 10, karena periode tanggal (from-to) diubah menjadi periode tahun */
6/* hapus function sebelumnya yang terdiri 11 parameter */
7DROP FUNCTION IF EXISTS r_sales_journal_for_salesman(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying, character varying);
8
9/* periode year nya mau dibuat from-to, jadi parameternya 11, hapus function dengan param 10 */
10DROP FUNCTION IF EXISTS r_sales_journal_for_salesman(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying);
11
12CREATE OR REPLACE FUNCTION r_sales_journal_for_salesman(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying, character varying)
13 RETURNS SETOF refcursor AS
14$BODY$
15DECLARE
16 pRefHeader REFCURSOR := 'refHeader';
17 pRefDetail REFCURSOR := 'refDetail';
18 pRefDetailInfo REFCURSOR := 'refDetailInfo';
19 pSessionId ALIAS FOR $1;
20 pTenantId ALIAS FOR $2;
21 pUserId ALIAS FOR $3;
22 pRoleId ALIAS FOR $4;
23 pDatetime ALIAS FOR $5;
24 pOuId ALIAS FOR $6;
25 pCustomerId ALIAS FOR $7;
26 pYearFromOfPeriod ALIAS FOR $8;
27 pYearToOfPeriod ALIAS FOR $9;
28 pSubCtgrCode ALIAS FOR $10;
29 pPaymentStatus ALIAS FOR $11;
30
31 vCustomerFrom character varying(1024) := '';
32 vCustomerTo character varying(1024) := '';
33 vFilterSubCtgr character varying(100) := '';
34 vSubCtgrName character varying(100) := 'All';
35 vRawData character varying(10) := 'RAW-DATA';
36 vSumData character varying(10) := 'SUM-DATA';
37 vInvoiceFix character varying(20) := 'Final Invoice';
38 vInvoiceTemp character varying(20) := 'Temporary Invoice';
39 vDebtNoteAr character varying(20) := 'Debt Note AR';
40 vCreditNoteAr character varying(20) := 'Credit Note AR';
41 vReturnInvoice character varying(20) := 'Return Invoice';
42 vRvs character varying(3) := 'RVS';
43 vTrl character varying(3) := 'TRL';
44 vSmp character varying(3) := 'SMP';
45 vFoc character varying(3) := 'FOC';
46 vReg character varying(3) := 'REG';
47 vSettledPaymentStatus character varying := 'settled';
48 vNotYetSettledPaymentStatus character varying := 'notYetSettled';
49 vFlgYes character varying := 'Y';
50 vFlgNo character varying := 'N';
51 vFlgVoid character varying := 'V';
52 vFlgAlloc character varying := 'C';
53 vFlgCustomer character varying := 'C';
54 vFlgDisc character varying := 'P';
55 vVoidDoc character varying := 'V';
56 vReleaseDoc character varying := 'R';
57 vDraftDoc character varying := 'D';
58 vRejectedDoc character varying := 'C';
59
60 vDocTypeSalesInvoice bigint := 321;
61 vDocTypeSalesInvoiceTemp bigint := 361;
62 vDocTypeSoByBrand bigint := 398;
63 vDocTypeDo bigint := 311;
64 vDocTypeConvExcOut bigint := 395;
65 vDocTypeReturNote bigint := 502;
66 vDocTypeDNAR bigint := 241;
67 vDocTypeCNAR bigint := 251;
68 vDocTypeRSIB bigint := 380;--Return Sales Invoice By Brand
69 vDocTypeRRS bigint := 381;--Request Return Sales
70 vDocTypeRN bigint := 502;--Return Note
71 vDocCgReceipt bigint := 624;--Cheque/Giro Receipt
72 vDocCbInPartnerReceive bigint := 621;--Cash/Bank In Partner Receive (AR)
73 vDocConvCbInOtherToPartner bigint := 626;--Conversion of CB In Other To Partner Receive
74 vDocDebtNotArId bigint := 241;--Debt Note AR
75 vDocTempSiId bigint := 361;--Temporary Sales Invoice
76 vDocSiId bigint := 321;--Sales Invoice
77 vGroupCustomerId bigint := -99;-- default group customer
78 vEmptyId bigint := -99;
79
80 vOne integer := 1;
81 vNolAmount numeric := 0;
82 vNilaiPlafon numeric := 0;
83 vNilaiSoInProgress numeric := 0;
84 vGiroMundur numeric := 0;
85 vGiroBelumAlokasi numeric := 0;
86 vSaldoAr numeric := 0;
87 vNilaiCashBankGiroCair numeric := 0; -- belum alokasi
88 vTotalNilai numeric := 0;
89 vNilaiCurrentARExposure numeric := 0;
90 vDiscountPercentage numeric := 0;
91 vDiscountAmount numeric := 0;
92 vNilaiReturnNote numeric := 0;
93 vEmptyAmount bigint := 0;
94
95 vNol numeric := 0;
96
97 vCustomerIsValid integer := 0;
98 vSalesmanId bigint := -99;
99 vSalesmanLevel bigint := -99;
100 vFilterSalesman character varying := '';
101 vPeriodNow character varying(8) := '';
102
103 vGrandTotalAmountInvoice numeric := 0;
104 vGrandPaymentAmount numeric := 0;
105 vGrandRemainingAmount numeric := 0;
106 vEmptyValue character varying := '';
107
108 vParamCodeReportSalesJournalDisclaimer01 character varying := 'report.sales.journal.for.salesman.disclaimer.01';
109 vParamCodeReportSalesJournalDisclaimer02 character varying := 'report.sales.journal.for.salesman.disclaimer.02';
110 vSystemConfigValueDisclaimer01 character varying := '';
111 vSystemConfigValueDisclaimer02 character varying := '';
112
113BEGIN
114
115 DELETE FROM tt_sl_invoice WHERE session_id = pSessionId;
116 DELETE FROM tt_report_sales_journal WHERE session_id = pSessionId;
117 DELETE FROM tt_group_customer_item WHERE session_id = pSessionId;
118 DELETE FROM tt_data_detail_sales_journal WHERE session_id = pSessionId;
119
120 /**
121 * Ambil nilai system config value disclaimer untuk report ini
122 */
123 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeReportSalesJournalDisclaimer01) INTO vSystemConfigValueDisclaimer01;
124 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeReportSalesJournalDisclaimer02) INTO vSystemConfigValueDisclaimer02;
125
126 /* Lakukan pengecekan untuk memastikan customerId yg dikirim benar" masuk dalam hirarki salesman yg login */
127 SELECT salesman_id INTO vSalesmanId
128 FROM m_user_salesman_mapping_item
129 WHERE user_id = pUserId;
130
131 SELECT to_char(current_date, 'YYYYMMDD') INTO vPeriodNow;
132
133 IF vSalesmanId <> -99 THEN
134
135 SELECT f_get_salesman_level(vSalesmanId) INTO vSalesmanLevel;
136
137 IF vSalesmanLevel <> -99 THEN
138
139 IF(vSalesmanLevel = 1) THEN
140 vFilterSalesman := ' AND A.bod_id = '||vSalesmanId||' ';
141 ELSIF (vSalesmanLevel = 2) THEN
142 vFilterSalesman := ' AND A.saso_id = '||vSalesmanId||' ';
143 ELSIF (vSalesmanLevel = 3) THEN
144 vFilterSalesman := ' AND A.sales_manager_id = '||vSalesmanId||' ';
145 ELSIF (vSalesmanLevel = 4) THEN
146 vFilterSalesman := ' AND A.sales_supervisor_id = '||vSalesmanId||' ';
147 ELSIF (vSalesmanLevel = 5) THEN
148 vFilterSalesman := ' AND A.salesman_id = '||vSalesmanId||' ';
149 ELSIF (vSalesmanLevel = 6) THEN
150 vFilterSalesman := ' AND A.salesman_junior_id = '||vSalesmanId||' ';
151 END IF;
152
153 EXECUTE '
154 SELECT 1 as one
155 FROM m_mapping_salesman_hierarchy_customer A
156 WHERE A.customer_id = $1
157 AND A.active = $3 '
158 || vFilterSalesman ||'
159 AND $2 BETWEEN A.date_from AND A.date_to'
160 USING pCustomerId, vPeriodNow, vFlgYes
161 INTO vCustomerIsValid;
162
163 END IF;
164 END IF;
165 /* ---------------Pengecekan customerId END---------------------- */
166
167 IF (pSubCtgrCode !='' ) THEN
168
169 -- CREATE FILTER --
170 vFilterSubCtgr := ' AND '', ''||UPPER(c.sub_ctgr_product_code)||'','' LIKE UPPER(''%, '||pSubCtgrCode||',%'')';
171
172 -- MASUKAN SUB CTGR NAME KE VARIABLE vSubCtgrName --
173 SELECT sub_ctgr_product_name INTO vSubCtgrName
174 FROM m_sub_ctgr_product
175 WHERE tenant_id = pTenantId
176 AND sub_ctgr_product_code = pSubCtgrCode;
177 END IF;
178
179 /* Pengambilan/pengolahan data detail report hanya dilakukan apabila customer/partner yg dikirim masuk dalam hirarki salesman yg login */
180 IF vCustomerIsValid = vOne THEN
181
182 /**
183 * 1. Melakukan pengecekan apakah customer terdaftar sebagai group
184 * jika customer terdaftar sebagai group customer maka pengambilan nilai plafon = m_group_customer.total_amount_limit
185 * jika tidak maka pengambilan nilai plafon = m_partner_type.amount_limit
186 */
187 IF EXISTS (SELECT 1
188 FROM m_group_customer A
189 INNER JOIN m_group_customer_item B ON A.group_customer_id = B.group_customer_id
190 WHERE A.tenant_id = pTenantId
191 AND A.ou_id = pOuId
192 AND B.partner_id = pCustomerId)
193 THEN
194
195 SELECT A.total_amount_limit, A.group_customer_id INTO vNilaiPlafon, vGroupCustomerId
196 FROM m_group_customer A
197 WHERE A.tenant_id = pTenantId
198 AND A.ou_id = pOuId
199 AND EXISTS (
200 SELECT 1 FROM m_group_customer_item Z
201 WHERE A.group_customer_id = Z.group_customer_id
202 AND Z.partner_id = pCustomerId
203 );
204
205 INSERT INTO tt_group_customer_item(session_id, group_customer_id, partner_id, amount_limit, flg_group_customer)
206 SELECT pSessionId, group_customer_id, partner_id, amount_limit, vFlgYes
207 FROM m_group_customer_item
208 WHERE group_customer_id = vGroupCustomerId;
209
210 ELSE
211
212 SELECT CASE WHEN A.amount_limit > vNol THEN A.amount_limit ELSE 0 END INTO vNilaiPlafon
213 FROM m_partner_type A
214 WHERE A.tenant_id = pTenantId
215 AND A.partner_id = pCustomerId;
216
217 INSERT INTO tt_group_customer_item(session_id, group_customer_id, partner_id, amount_limit, flg_group_customer)
218 SELECT pSessionId, vEmptyId, partner_id, amount_limit, vFlgNo
219 FROM m_partner_type
220 WHERE partner_id = pCustomerId
221 AND group_partner = vFlgCustomer;
222
223 END IF;
224
225 /**
226 * 2. Pengambilan nilai SO yang masih dalam proses pembuatan
227 * Yang artinya adalah SO yang belum dibuatkan DO sampai approved dan status SO tidak VOID/DRAFT/REJECTED
228 */
229 SELECT COALESCE(SUM(B.nett_brand_amount+
230 f_get_tax_amount_after_discount(
231 f_get_gross_amount_after_discount(
232 B.gross_sell_price,
233 B.flg_disc,
234 B.discount_percentage,
235 B.discount_amount,
236 B.qty,
237 0),
238 B.flg_tax_amount,
239 B.tax_percentage,
240 0)
241 ), vNol) INTO vNilaiSoInProgress
242 FROM sl_so A
243 INNER JOIN sl_so_brand_item B ON A.so_id = B.so_id
244 INNER JOIN dt_date C ON A.doc_date = C.string_date
245 WHERE A.tenant_id = pTenantId
246 AND A.ou_id = pOuId
247 AND A.status_doc NOT IN (vDraftDoc, vVoidDoc, vRejectedDoc)
248 AND C.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
249 AND NOT EXISTS (SELECT 1
250 FROM sl_do Z
251 WHERE A.so_id = Z.ref_id
252 AND A.doc_type_id = Z.ref_doc_type_id
253 AND Z.status_doc = vReleaseDoc)
254 AND EXISTS (SELECT 1
255 FROM tt_group_customer_item Z
256 WHERE A.partner_id = Z.partner_id
257 AND Z.session_id = pSessionId);
258
259 -- 3.A. Ambil dokumen TSI, SI , CNAR dan DNAR, RSIB (ambil nilai yg belum dibayar/dialokasi) --
260 SELECT COALESCE(SUM(A.amount - A.payment_amount), vNol) INTO vSaldoAr
261 FROM fi_invoice_ar_balance A
262 INNER JOIN dt_date B ON A.doc_date = B.string_date
263 WHERE A.tenant_id = pTenantId
264 AND A.ou_id = pOuId
265 AND A.doc_type_id IN (vDocDebtNotArId, vDocTempSiId, vDocSiId, vDocTypeCNAR, vDocTypeRSIB)
266 AND B.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
267 AND EXISTS (SELECT 1
268 FROM tt_group_customer_item Z
269 WHERE A.partner_id = Z.partner_id
270 AND Z.session_id = pSessionId);
271
272 -- 3.B.Ambil Dokumen Cg Receipt (yang sudah cair), CB In Partner (AR), Conversion of CB In Other To Partner Receive yang belum alokasi--
273 WITH data_cb_in_with_conv_with_data_cg_receipt AS (
274 SELECT COALESCE(SUM(A.amount), vNol) AS amount
275 FROM fi_receipt_ar_balance A
276 INNER JOIN dt_date B ON A.doc_date = B.string_date
277 WHERE A.tenant_id = pTenantId
278 AND A.ou_id = pOuId
279 AND A.flg_alloc = vFlgNo
280 AND A.doc_type_id IN (vDocCbInPartnerReceive, vDocConvCbInOtherToPartner)
281 AND B.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
282 AND EXISTS (SELECT 1
283 FROM tt_group_customer_item Z
284 WHERE A.partner_id = Z.partner_id
285 AND Z.session_id = pSessionId)
286
287 UNION ALL
288
289 SELECT COALESCE(SUM(A.amount), vNol) AS amount
290 FROM fi_receipt_ar_balance A
291 INNER JOIN cb_cheque_giro_balance B ON A.ref_item_id = B.in_out_cheque_giro_payment_id
292 AND B.flg_realization = vFlgYes
293 INNER JOIN dt_date C ON A.doc_date = C.string_date
294 WHERE A.tenant_id = pTenantId
295 AND A.ou_id = pOuId
296 AND A.flg_alloc = vFlgNo
297 AND A.doc_type_id = vDocCgReceipt
298 AND C.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
299 AND EXISTS (SELECT 1
300 FROM tt_group_customer_item Z
301 WHERE A.partner_id = Z.partner_id
302 AND Z.session_id = pSessionId)
303 )
304 SELECT SUM(amount) INTO vNilaiCashBankGiroCair
305 FROM data_cb_in_with_conv_with_data_cg_receipt;
306
307-- -- 4.Giro Mundur yg sudah Alokasi (Cheque/Giro yang sudah dialokasi tapi belum cair)--
308-- SELECT COALESCE(SUM(B.amount), vNol) INTO vGiroMundur
309-- FROM fi_allocation_ar A
310-- INNER JOIN fi_receipt_ar_balance B ON A.ref_id = B.receipt_ar_balance_id
311-- AND A.ref_doc_type_id = B.doc_type_id
312-- INNER JOIN cb_cheque_giro_balance C ON B.ref_item_id = C.in_out_cheque_giro_payment_id
313-- AND C.flg_realization NOT IN (vFlgYes, vFlgVoid)
314-- INNER JOIN dt_date D ON A.doc_date = D.string_date
315-- WHERE A.tenant_id = pTenantId
316-- AND A.ou_id = pOuId
317-- AND A.ref_doc_type_id = vDocCgReceipt
318-- AND D.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
319-- AND EXISTS (SELECT 1
320-- FROM fi_allocation_ar_balance X
321-- WHERE A.allocation_ar_id = X.allocation_ar_id
322-- AND X.flg_alloc = vFlgAlloc )
323-- AND EXISTS (SELECT 1
324-- FROM tt_group_customer_item Z
325-- WHERE A.partner_id = Z.partner_id
326-- AND Z.session_id = pSessionId);
327
328 -- 4.Giro Mundur (Saldo AR yang dialokasi dengan Cheque/Giro yang belum cair)--
329 SELECT COALESCE(SUM(A.debit_amount), vEmptyAmount) INTO vGiroMundur
330 FROM fi_allocation_ar_balance A
331 INNER JOIN fi_receipt_ar_balance B ON A.credit_id = B.receipt_ar_balance_id AND A.credit_doc_type_id = B.doc_type_id
332 INNER JOIN cb_cheque_giro_balance C ON B.ref_item_id = C.in_out_cheque_giro_payment_id
333 INNER JOIN fi_invoice_ar_balance D ON A.debit_id = D.invoice_ar_balance_id AND A.debit_doc_type_id = D.doc_type_id
334 INNER JOIN fi_allocation_ar E ON A.allocation_ar_id = E.allocation_ar_id
335 INNER JOIN dt_date F ON E.doc_date = F.string_date
336 WHERE A.tenant_id = pTenantId
337 AND A.flg_alloc = vFlgAlloc
338 AND A.credit_doc_type_id = vDocCgReceipt
339 AND C.flg_realization NOT IN (vFlgYes, vFlgVoid)
340 AND D.ou_id = pOuId
341 AND F.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
342 AND EXISTS (
343 SELECT 1
344 FROM tt_group_customer_item Z
345 WHERE B.partner_id = Z.partner_id
346 AND Z.session_id = pSessionId
347 );
348
349 -- 5. Ambil giro yang belum alokasi & belum cair --
350 SELECT COALESCE(SUM(A.amount), vNol) INTO vGiroBelumAlokasi
351 FROM fi_receipt_ar_balance A
352 INNER JOIN cb_cheque_giro_balance B ON A.ref_item_id = in_out_cheque_giro_payment_id
353 AND B.flg_realization NOT IN (vFlgYes, vFlgVoid)
354 INNER JOIN cb_in_out_cheque_giro_payment C ON B.in_out_cheque_giro_payment_id = C.in_out_cheque_giro_payment_id
355 INNER JOIN dt_date D ON A.doc_date = D.string_date
356 WHERE A.tenant_id = pTenantId
357 AND A.ou_id = pOuId
358 AND A.doc_type_id = vDocCgReceipt
359 AND A.flg_alloc = vFlgNo
360 AND D.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
361 AND EXISTS (SELECT 1
362 FROM tt_group_customer_item Z
363 WHERE A.partner_id = Z.partner_id
364 AND Z.session_id = pSessionId);
365
366 --Ambil nilai retur yg blm dibuat RSIB atau in progress dibuat RSIB
367 SELECT
368 COALESCE(SUM(C.nett_brand_amount+
369 f_get_tax_amount_after_discount(
370 f_get_gross_amount_after_discount(
371 C.gross_sell_price,
372 vFlgDisc,
373 vDiscountPercentage,
374 vDiscountAmount,
375 D.qty_realization,
376 0),
377 C.flg_tax_amount,
378 C.tax_percentage,
379 0)
380 ) , vNolAmount) INTO vNilaiReturnNote
381 FROM in_inventory A
382 INNER JOIN sl_request_return_sales B ON B.request_return_sales_id = A.ref_id
383 AND B.doc_type_id = A.ref_doc_type_id AND B.doc_type_id = vDocTypeRRS
384 INNER JOIN sl_request_return_sales_brand_item C ON C.request_return_sales_id = B.request_return_sales_id
385 INNER JOIN in_inventory_item D ON D.inventory_id = A.inventory_id AND f_get_brand_by_product_id(D.product_id) = C.brand_id
386 INNER JOIN dt_date E ON A.doc_date = E.string_date
387 WHERE A.tenant_id = pTenantId
388 AND A.ou_from_id = pOuId
389 AND A.status_doc = vReleaseDoc
390 AND A.doc_type_id = vDocTypeRN
391 AND E.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
392 AND EXISTS (SELECT 1
393 FROM tt_group_customer_item Z
394 WHERE A.partner_id = Z.partner_id
395 AND Z.session_id = pSessionId)
396 AND NOT EXISTS(
397 SELECT 1
398 FROM sl_invoice Z
399 WHERE Z.doc_type_id = vDocTypeRSIB
400 AND Z.status_doc = vReleaseDoc
401 AND Z.ref_id = A.ref_id
402 AND Z.ref_doc_type_id = A.ref_doc_type_id);
403
404 -- Dapatkan total nilai (sisa plafon) = total Plafon-(SO dalam proses+Invoice yang belum dibayar+Giro Mundur yg sudah Alokasi)
405 SELECT vNilaiPlafon-(vNilaiSoInProgress+(vSaldoAr-vNilaiCashBankGiroCair-vNilaiReturnNote)+vGiroMundur) INTO vTotalNilai;
406
407 -- Dapatkan nilai Current AR Exposure = SO dalam proses+Invoice yang belum dibayar+Giro Mundur yg sudah Alokasi
408 SELECT vNilaiSoInProgress+(vSaldoAr-vNilaiCashBankGiroCair-vNilaiReturnNote)+vGiroMundur INTO vNilaiCurrentARExposure;
409
410 ----------------------------------------------------------------------------------------------------------
411
412 -- MASUKAN SL INVOICE yg APPROVED YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
413 INSERT INTO tt_sl_invoice(
414 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
415 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
416 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
417 advance_amount, tax_amount, add_amount, total_amount, status_doc,
418 workflow_status, VERSION, ref_inv_temp_id, discount_amount,
419 mou_id, invoice_status, payment_amount,
420 so_date, promo_id, product_launching_id)
421 SELECT pSessionId, A.invoice_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
422 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
423 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, A.gross_amount,
424 A.advance_amount, A.tax_amount, A.add_amount, A.total_amount, A.status_doc,
425 A.workflow_status, A.VERSION, A.ref_inv_temp_id, A.discount_amount,
426 COALESCE(B.tag_doc_id, -99) AS mou_id, vInvoiceFix, C.payment_amount,
427 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
428 FROM fi_invoice_ar_balance C
429 INNER JOIN sl_invoice A ON C.invoice_ar_id = A.invoice_id AND C.doc_type_id = A.doc_type_id
430 INNER JOIN sl_so D ON A.ref_id = D.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
431 INNER JOIN dt_date G ON C.doc_date = G.string_date
432 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'
433 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'
434 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'
435 WHERE C.tenant_id = pTenantId
436 AND C.ou_id = pOuId
437 AND C.doc_type_id = vDocTypeSalesInvoice
438 AND G.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
439 AND EXISTS (SELECT 1
440 FROM tt_group_customer_item Z
441 WHERE C.partner_id = Z.partner_id
442 AND Z.session_id = pSessionId)
443 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
444 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
445
446
447 -- MASUKAN SL INVOICE TEMP yg APPROVED dan dari SOB REGULAR dan amount nya <> 0 YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
448 WITH valid_so_balance_so_invoice AS (
449 SELECT A.so_id
450 FROM sl_so_balance_invoice A
451 WHERE EXISTS (SELECT 1
452 FROM tt_group_customer_item Z
453 WHERE A.partner_id = Z.partner_id
454 AND Z.session_id = pSessionId)
455 GROUP BY A.so_id
456 HAVING SUM(A.qty_dlv_so) > 0
457 )
458 INSERT INTO tt_sl_invoice(
459 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
460 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
461 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
462 advance_amount, tax_amount, add_amount, total_amount, status_doc,
463 workflow_status, VERSION, ref_inv_temp_id, discount_amount,
464 mou_id, invoice_status, payment_amount,
465 so_date, promo_id, product_launching_id)
466 SELECT pSessionId, A.invoice_temp_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
467 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
468 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, COALESCE(A.gross_amount, 0),
469 A.advance_amount, COALESCE(A.tax_amount, 0), A.add_amount, A.total_amount, A.status_doc,
470 A.workflow_status, A.VERSION, -99, A.discount_amount,
471 COALESCE(C.tag_doc_id, -99) AS mou_id, vInvoiceTemp, D.payment_amount,
472 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
473 FROM fi_invoice_ar_balance D
474 INNER JOIN sl_invoice_temp A ON D.invoice_ar_id = A.invoice_temp_id AND D.doc_type_id = A.doc_type_id
475 INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
476 INNER JOIN sl_so_info E ON B.so_id = E.so_id AND E.flg_type_so = vReg
477 INNER JOIN valid_so_balance_so_invoice EX ON B.so_id = EX.so_id
478 INNER JOIN dt_date H ON A.doc_date = H.string_date
479 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'
480 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'
481 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'
482 WHERE D.tenant_id = pTenantId
483 AND D.ou_id = pOuId
484 AND D.doc_type_id = vDocTypeSalesInvoiceTemp
485 AND H.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
486 AND EXISTS (SELECT 1
487 FROM tt_group_customer_item Z
488 WHERE D.partner_id = Z.partner_id
489 AND Z.session_id = pSessionId)
490 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, D.ou_id) = vOne
491 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, D.partner_id) = vOne
492 AND A.total_amount <> 0
493 AND A.status_doc <> 'V';
494
495 -- MASUKAN FI INVOICE DN AR APPROVED YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
496 INSERT INTO tt_sl_invoice(
497 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
498 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
499 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
500 advance_amount, tax_amount, add_amount, total_amount, status_doc,
501 workflow_status, VERSION, ref_inv_temp_id, discount_amount,
502 mou_id, invoice_status, payment_amount,
503 so_date, promo_id, product_launching_id)
504 SELECT pSessionId, A.invoice_ar_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
505 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
506 -99 AS ou_legal_id, A.due_date, -99 AS salesman_id, A.curr_code, 0 AS gross_amount,
507 0 AS advance_amount, A.tax_amount, 0 AS add_amount, A.total_amount, A.status_doc,
508 A.workflow_status, A.VERSION, -99, 0 AS discount_amount,
509 COALESCE(B.tag_doc_id, -99) AS mou_id, vDebtNoteAr, C.payment_amount,
510 '' AS so_date, -99 AS product_launching_id, -99 AS product_launching_id
511 FROM fi_invoice_ar_balance C
512 INNER JOIN fi_invoice_ar A ON C.invoice_ar_id = A.invoice_ar_id AND C.doc_type_id = A.doc_type_id
513 INNER JOIN dt_date D ON A.doc_date = D.string_date
514 LEFT OUTER JOIN fi_invoice_ar_tagging B ON A.tenant_id = B.tenant_id
515 AND A.invoice_ar_id = B.invoice_ar_id
516 AND B.tag_key = 'MOU'
517 WHERE C.tenant_id = pTenantId
518 AND C.ou_id = pOuId
519 AND C.doc_type_id = vDocTypeDNAR
520 AND D.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
521 AND EXISTS (SELECT 1
522 FROM tt_group_customer_item Z
523 WHERE C.partner_id = Z.partner_id
524 AND Z.session_id = pSessionId)
525 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
526 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
527
528 -- MASUKAN FI INVOICE CN AR APPROVED YANG DI PILIH BERDASARKAN FILTER KE TABLE TEMP --
529 INSERT INTO tt_sl_invoice(
530 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
531 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
532 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
533 advance_amount, tax_amount, add_amount, total_amount, status_doc,
534 workflow_status, VERSION, ref_inv_temp_id, discount_amount,
535 mou_id, invoice_status, payment_amount,
536 so_date, promo_id, product_launching_id)
537 SELECT pSessionId, A.invoice_ar_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
538 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
539 -99 AS ou_legal_id, A.due_date, -99 AS salesman_id, A.curr_code, 0 AS gross_amount,
540 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,
541 A.workflow_status, A.VERSION, -99, 0 AS discount_amount,
542 COALESCE(B.tag_doc_id, -99) AS mou_id, vCreditNoteAr, C.payment_amount,
543 '' AS so_date, -99 AS product_launching_id, -99 AS product_launching_id
544 FROM fi_invoice_ar_balance C
545 INNER JOIN fi_invoice_ar A ON C.invoice_ar_id = A.invoice_ar_id AND C.doc_type_id = A.doc_type_id
546 INNER JOIN dt_date D ON A.doc_date = D.string_date
547 LEFT OUTER JOIN fi_invoice_ar_tagging B ON A.tenant_id = B.tenant_id
548 AND A.invoice_ar_id = B.invoice_ar_id
549 AND B.tag_key = 'MOU'
550 WHERE C.tenant_id = pTenantId
551 AND C.ou_id = pOuId
552 AND C.doc_type_id = vDocTypeCNAR
553 AND D.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
554 AND EXISTS (SELECT 1
555 FROM tt_group_customer_item Z
556 WHERE C.partner_id = Z.partner_id
557 AND Z.session_id = pSessionId)
558 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, C.ou_id) = vOne
559 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, C.partner_id) = vOne;
560
561 -- MASUKAN SO TRIAL, FOC, REVISI, SAMPLE DILIHAT DARI DATA TEMPORARY INVOICE --
562 WITH valid_so_balance_so_invoice AS (
563 SELECT A.so_id
564 FROM sl_so_balance_invoice A
565 WHERE EXISTS (SELECT 1
566 FROM tt_group_customer_item Z
567 WHERE A.partner_id = Z.partner_id
568 AND Z.session_id = pSessionId)
569 GROUP BY A.so_id
570 HAVING SUM(A.qty_dlv_so) > 0
571 )
572 INSERT INTO tt_sl_invoice(
573 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
574 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
575 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
576 advance_amount, tax_amount, add_amount, total_amount, status_doc,
577 workflow_status, VERSION, ref_inv_temp_id, discount_amount,
578 mou_id, invoice_status, payment_amount,
579 so_date, promo_id, product_launching_id)
580 SELECT pSessionId, B.so_id, A.tenant_id, B.doc_type_id, A.doc_no, A.doc_date, A.ou_id,
581 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
582 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, COALESCE(A.gross_amount, 0),
583 A.advance_amount, COALESCE(A.tax_amount, 0), A.add_amount, A.total_amount, A.status_doc,
584 A.workflow_status, A.VERSION, -99, A.discount_amount,
585 COALESCE(C.tag_doc_id, -99) AS mou_id,
586 CASE WHEN (E.flg_type_so = vRvs)
587 THEN 'Revisi'
588 WHEN (E.flg_type_so = vTrl)
589 THEN 'Trial'
590 WHEN (E.flg_type_so = vSmp)
591 THEN 'Sample'
592 WHEN (E.flg_type_so = vFoc)
593 THEN 'FOC'
594 END AS invoice_status, D.payment_amount,
595 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
596 FROM fi_invoice_ar_balance D
597 INNER JOIN sl_invoice_temp A ON D.invoice_ar_id = A.invoice_temp_id AND D.doc_type_id = A.doc_type_id
598 INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = vDocTypeSoByBrand
599 INNER JOIN sl_so_info E ON B.so_id = E.so_id
600 INNER JOIN valid_so_balance_so_invoice EX ON B.so_id = EX.so_id
601 INNER JOIN dt_date H ON A.doc_date = H.string_date
602 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'
603 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'
604 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'
605 WHERE D.tenant_id = pTenantId
606 AND D.ou_id = pOuId
607 AND D.doc_type_id = vDocTypeSalesInvoiceTemp
608 AND H.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
609 AND EXISTS (SELECT 1
610 FROM tt_group_customer_item Z
611 WHERE D.partner_id = Z.partner_id
612 AND Z.session_id = pSessionId)
613 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, D.ou_id) = vOne
614 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, D.partner_id) = vOne
615 AND A.status_doc <> 'V'
616 AND E.flg_type_so IN (vRvs,vTrl,vSmp,vFoc);
617
618 -- MASUKAN KE TABLE TEMP UNTUK DATA RSIB
619 INSERT INTO tt_sl_invoice(
620 session_id, invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
621 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
622 partner_id, ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
623 advance_amount, tax_amount, add_amount, total_amount, status_doc,
624 workflow_status, VERSION, ref_inv_temp_id, discount_amount,
625 mou_id, invoice_status, payment_amount,
626 so_date, promo_id, product_launching_id)
627 SELECT pSessionId, B.invoice_id, B.tenant_id, B.doc_type_id, B.doc_no, B.doc_date, B.ou_id,
628 B.ext_doc_no, B.ext_doc_date, B.ref_doc_type_id, B.ref_id, B.remark, B.partner_id,
629 B.ou_legal_id, B.due_date, B.salesman_id, B.curr_code, -1*B.gross_amount,
630 -1*B.advance_amount, -1*B.tax_amount, -1*B.add_amount, -1*B.total_amount, B.status_doc,
631 B.workflow_status, B.VERSION, B.ref_inv_temp_id, B.discount_amount,
632 COALESCE(E.tag_doc_id, -99) AS mou_id, vReturnInvoice, A.payment_amount,
633 COALESCE(F.doc_date, '') AS so_date, -99 AS promo_id, -99 AS product_launching_id
634 FROM fi_invoice_ar_balance A
635 INNER JOIN sl_invoice B ON A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
636 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
637 INNER JOIN in_inventory D ON C.request_return_sales_id = D.ref_id AND C.doc_type_id = D.ref_doc_type_id
638 INNER JOIN dt_date G ON A.doc_date = G.string_date
639 LEFT OUTER JOIN in_return_tagging E ON D.tenant_id = E.tenant_id
640 AND D.inventory_id = E.inventory_id
641 AND E.tag_key = 'MOU'
642 LEFT OUTER JOIN sl_so F ON C.ref_id = F.so_id AND C.ref_doc_type_id = F.doc_type_id
643 WHERE A.tenant_id = pTenantId
644 AND A.ou_id = pOuId
645 AND A.doc_type_id = vDocTypeRSIB
646 AND G.year_date BETWEEN pYearFromOfPeriod AND pYearToOfPeriod
647 AND EXISTS (SELECT 1
648 FROM tt_group_customer_item Z
649 WHERE A.partner_id = Z.partner_id
650 AND Z.session_id = pSessionId)
651 AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, A.ou_id) = vOne
652 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = vOne
653 AND C.doc_type_id = vDocTypeRRS;
654
655 -- REF DOC NO DARI INVOICE, TEMP INVOICE & SOB (TRIAL, FOC, SAMPLE, REVISI) --
656 INSERT INTO tt_report_sales_journal(
657 session_id, data_id, sales_invoice_id, doc_type_id, ref_doc_no)
658 SELECT pSessionId, vRawData, a.invoice_id, a.doc_type_id, COALESCE(d.awb_no, '-')
659 FROM tt_sl_invoice a
660 INNER JOIN sl_do b ON a.ref_id = b.ref_id AND b.ref_doc_type_id = vDocTypeSoByBrand
661 INNER JOIN in_item_log_book_balance c ON b.tenant_id = c.tenant_id
662 AND b.ou_id = c.ou_id
663 AND b.doc_type_id = c.ref_doc_type_id
664 AND b.doc_date = c.ref_doc_date
665 AND b.doc_no = c.ref_doc_no
666 LEFT OUTER JOIN in_awb_log_book d ON c.awb_log_book_id = d.awb_log_book_id
667 WHERE a.session_id = pSessionId
668 AND a.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeSalesInvoiceTemp, vDocTypeSoByBrand);
669
670 -- REF DOC NO DARI RSIB --
671 INSERT INTO tt_report_sales_journal(
672 session_id, data_id, sales_invoice_id, doc_type_id, ref_doc_no)
673 SELECT pSessionId, vRawData, A.invoice_id, A.doc_type_id, COALESCE(C.doc_no, COALESCE(D.doc_no, '-'))
674 FROM tt_sl_invoice A
675 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
676 LEFT OUTER JOIN sl_invoice C
677 ON B.ref_id = C.ref_id
678 AND B.ref_doc_type_id = C.ref_doc_type_id
679 AND C.doc_type_id = vDocTypeSalesInvoice
680 LEFT OUTER JOIN sl_invoice_temp D
681 ON B.ref_id = D.ref_id
682 AND B.ref_doc_type_id = D.ref_doc_type_id
683 AND D.doc_type_id = vDocTypeSalesInvoiceTemp
684 AND D.status_doc <> 'V'
685 WHERE A.session_id = pSessionId
686 AND A.doc_type_id = vDocTypeRSIB;
687
688 -- REF DOC NO dari DN/CN Ar pasti nggak ada
689 INSERT INTO tt_report_sales_journal(
690 session_id, data_id, sales_invoice_id, doc_type_id, ref_doc_no)
691 SELECT pSessionId, vRawData, a.invoice_id, a.doc_type_id, '-'
692 FROM tt_sl_invoice a
693 WHERE a.session_id = pSessionId
694 AND a.doc_type_id IN(vDocTypeDNAR, vDocTypeCNAR);
695
696 -- SUM PAYMENT AMOUNT --
697 INSERT INTO tt_report_sales_journal(
698 session_id, data_id, sales_invoice_id, doc_type_id, payment_amount, ref_doc_no)
699 SELECT pSessionId, vRawData, a.invoice_id, a.doc_type_id, a.payment_amount, b.ref_doc_no
700 FROM tt_sl_invoice a
701 INNER JOIN tt_report_sales_journal b ON a.invoice_id = b.sales_invoice_id
702 AND a.doc_type_id = b.doc_type_id
703 WHERE a.session_id = pSessionId
704 AND b.session_id = pSessionId
705 GROUP BY a.invoice_id, a.doc_type_id, a.payment_amount, b.ref_doc_no;
706
707 -- SUM DATA --
708 INSERT INTO tt_report_sales_journal (session_id, data_id, sales_invoice_id, doc_type_id,
709 add_amount,
710 payment_amount,
711 retur_amount,
712 ref_doc_no)
713 SELECT pSessionId, vSumData, sales_invoice_id, doc_type_id,
714 SUM(add_amount) AS add_amount,
715 SUM(payment_amount) AS payment_amount,
716 SUM(retur_amount) AS retur_amount,
717 ref_doc_no
718 FROM tt_report_sales_journal
719 WHERE session_id = pSessionId
720 AND data_id = vRawData
721 GROUP BY sales_invoice_id, doc_type_id, ref_doc_no;
722
723 -- DELETE TEMP RAW-DATA --
724 DELETE FROM tt_report_sales_journal WHERE data_id= vRawData AND session_id = pSessionId;
725
726 ------------------------------------------------------------------------------------------------------------------
727
728 -- SIAPKAN DATA DETAIL REPORT SALES JOURNAL ( tampung dalam temp table) --
729 IF pPaymentStatus = vSettledPaymentStatus THEN
730 RAISE NOTICE 'SETTLED = %', pPaymentStatus;
731
732 EXECUTE
733 'WITH sl_inv_sub_cat AS (
734 SELECT a.invoice_id, a.doc_type_id,
735 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
736 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
737 FROM tt_sl_invoice a
738 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $1
739 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
740 INNER JOIN m_product d ON c.product_id = d.product_id
741 WHERE a.doc_type_id = $2
742 AND a.session_id = $3
743 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
744 ), sls_inv_temp_sub_cat AS (
745 SELECT a.invoice_id, a.doc_type_id,
746 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
747 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
748 FROM tt_sl_invoice a
749 INNER JOIN sl_invoice_temp_item b ON a.invoice_id = b.invoice_temp_id AND b.ref_doc_type_id = $1
750 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
751 INNER JOIN m_product d ON c.product_id = d.product_id
752 WHERE a.doc_type_id = $5
753 AND a.session_id = $3
754 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
755 ), sls_order_sub_cat AS (
756 SELECT a.invoice_id, a.doc_type_id,
757 f_get_sub_ctgr_product_name(c.sub_ctgr_product_id) AS sub_ctgr_product_name,
758 f_get_sub_ctgr_product_code(c.sub_ctgr_product_id) AS sub_ctgr_product_code
759 FROM tt_sl_invoice a
760 INNER JOIN sl_so_item b ON a.invoice_id = b.so_id
761 INNER JOIN m_product c ON b.product_id = c.product_id
762 WHERE a.doc_type_id = $12
763 AND a.session_id = $3
764 GROUP BY a.invoice_id, a.doc_type_id, c.sub_ctgr_product_id
765 ), return_sls_sub_cat AS (
766 SELECT a.invoice_id, a.doc_type_id,
767 f_get_sub_ctgr_product_name(e.sub_ctgr_product_id) AS sub_ctgr_product_name,
768 f_get_sub_ctgr_product_code(e.sub_ctgr_product_id) AS sub_ctgr_product_code
769 FROM tt_sl_invoice a
770 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $15
771 INNER JOIN in_inventory c ON b.ref_id = c.inventory_id AND b.ref_doc_type_id = c.doc_type_id
772 INNER JOIN in_inventory_item d ON c.inventory_id = d.inventory_id
773 INNER JOIN m_product e ON d.product_id = e.product_id
774 WHERE a.doc_type_id = $14
775 AND a.session_id = $3
776 GROUP BY a.invoice_id, a.doc_type_id, e.sub_ctgr_product_id
777 ), category_invoice AS (
778 SELECT a.invoice_id, a.doc_type_id,
779 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
780 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
781 FROM sl_inv_sub_cat a
782 GROUP BY a.invoice_id, a.doc_type_id
783 UNION
784 SELECT a.invoice_id, a.doc_type_id,
785 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
786 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
787 FROM sls_inv_temp_sub_cat a
788 GROUP BY a.invoice_id, a.doc_type_id
789 UNION
790 SELECT a.invoice_id, a.doc_type_id,
791 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
792 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
793 FROM sls_order_sub_cat a
794 GROUP BY a.invoice_id, a.doc_type_id
795 UNION
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 return_sls_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 ''-'' AS sub_ctgr_product_name,
804 ''-'' AS sub_ctgr_product_code
805 FROM tt_sl_invoice a
806 WHERE a.doc_type_id IN ($6, $7 )
807 AND a.session_id = $3
808 GROUP BY a.invoice_id, a.doc_type_id
809 )
810 INSERT INTO tt_data_detail_sales_journal (
811 session_id, invoice_no, sub_ctgr_product_name, mou_no,
812 invoice_date, total_amount_invoice, payment_amount,
813 remaining_amount, partner_code,
814 partner_name, invoice_status)
815 SELECT $3 AS session_id, a.doc_no AS invoice_no, c.sub_ctgr_product_name, COALESCE(f.doc_no, ''-'') AS mou_no,
816 a.doc_date AS invoice_date, (a.total_amount+a.tax_amount) AS total_amount_invoice, d.payment_amount,
817 ((a.total_amount+a.tax_amount)-d.payment_amount) AS sisa, f_get_partner_code(a.partner_id) AS partner_code,
818 f_get_partner_name(a.partner_id) AS partner_name, a.invoice_status
819 FROM tt_sl_invoice a
820 INNER JOIN category_invoice c ON a.invoice_id = c.invoice_id AND a.doc_type_id = c.doc_type_id '||vFilterSubCtgr||'
821 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
822 LEFT OUTER JOIN sl_mou f ON a.mou_id = f.mou_id
823 WHERE a.session_id = $3
824 AND ((a.total_amount+a.tax_amount)-d.payment_amount) = $13'
825 USING vDocTypeDo,vDocTypeSalesInvoice,pSessionId,vDocTypeConvExcOut,vDocTypeSalesInvoiceTemp,vDocTypeDNAR,vDocTypeCNAR,
826 pTenantId,pUserId,pRoleId,vOne,vDocTypeSoByBrand,vNolAmount,vDocTypeRSIB,vDocTypeRN;
827
828 ELSE
829
830 RAISE NOTICE 'NOT YET SETTLED = %', pPaymentStatus;
831 EXECUTE
832 'WITH sl_inv_sub_cat AS (
833 SELECT a.invoice_id, a.doc_type_id,
834 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
835 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
836 FROM tt_sl_invoice a
837 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $1
838 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
839 INNER JOIN m_product d ON c.product_id = d.product_id
840 WHERE a.doc_type_id = $2
841 AND a.session_id = $3
842 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
843 ), sls_inv_temp_sub_cat AS (
844 SELECT a.invoice_id, a.doc_type_id,
845 f_get_sub_ctgr_product_name(d.sub_ctgr_product_id) AS sub_ctgr_product_name,
846 f_get_sub_ctgr_product_code(d.sub_ctgr_product_id) AS sub_ctgr_product_code
847 FROM tt_sl_invoice a
848 INNER JOIN sl_invoice_temp_item b ON a.invoice_id = b.invoice_temp_id AND b.ref_doc_type_id = $1
849 INNER JOIN sl_do_item c ON b.ref_item_id = c.do_item_id
850 INNER JOIN m_product d ON c.product_id = d.product_id
851 WHERE a.doc_type_id = $5
852 AND a.session_id = $3
853 GROUP BY a.invoice_id, a.doc_type_id, d.sub_ctgr_product_id
854 ), sls_order_sub_cat AS (
855 SELECT a.invoice_id, a.doc_type_id,
856 f_get_sub_ctgr_product_name(c.sub_ctgr_product_id) AS sub_ctgr_product_name,
857 f_get_sub_ctgr_product_code(c.sub_ctgr_product_id) AS sub_ctgr_product_code
858 FROM tt_sl_invoice a
859 INNER JOIN sl_so_item b ON a.invoice_id = b.so_id
860 INNER JOIN m_product c ON b.product_id = c.product_id
861 WHERE a.doc_type_id = $12
862 AND a.session_id = $3
863 GROUP BY a.invoice_id, a.doc_type_id, c.sub_ctgr_product_id
864 ), return_sls_sub_cat AS (
865 SELECT a.invoice_id, a.doc_type_id,
866 f_get_sub_ctgr_product_name(e.sub_ctgr_product_id) AS sub_ctgr_product_name,
867 f_get_sub_ctgr_product_code(e.sub_ctgr_product_id) AS sub_ctgr_product_code
868 FROM tt_sl_invoice a
869 INNER JOIN sl_invoice_item b ON a.invoice_id = b.invoice_id AND b.ref_doc_type_id = $15
870 INNER JOIN in_inventory c ON b.ref_id = c.inventory_id AND b.ref_doc_type_id = c.doc_type_id
871 INNER JOIN in_inventory_item d ON c.inventory_id = d.inventory_id
872 INNER JOIN m_product e ON d.product_id = e.product_id
873 WHERE a.doc_type_id = $14
874 AND a.session_id = $3
875 GROUP BY a.invoice_id, a.doc_type_id, e.sub_ctgr_product_id
876 ), category_invoice AS (
877 SELECT a.invoice_id, a.doc_type_id,
878 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
879 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
880 FROM sl_inv_sub_cat a
881 GROUP BY a.invoice_id, a.doc_type_id
882 UNION
883 SELECT a.invoice_id, a.doc_type_id,
884 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
885 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
886 FROM sls_inv_temp_sub_cat a
887 GROUP BY a.invoice_id, a.doc_type_id
888 UNION
889 SELECT a.invoice_id, a.doc_type_id,
890 string_agg(a.sub_ctgr_product_name, '', '') AS sub_ctgr_product_name,
891 string_agg(a.sub_ctgr_product_code, '', '') AS sub_ctgr_product_code
892 FROM sls_order_sub_cat a
893 GROUP BY a.invoice_id, a.doc_type_id
894 UNION
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 return_sls_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 ''-'' AS sub_ctgr_product_name,
903 ''-'' AS sub_ctgr_product_code
904 FROM tt_sl_invoice a
905 WHERE a.doc_type_id IN ($6, $7 )
906 AND a.session_id = $3
907 GROUP BY a.invoice_id, a.doc_type_id
908 )
909 INSERT INTO tt_data_detail_sales_journal (
910 session_id, invoice_no, sub_ctgr_product_name, mou_no,
911 invoice_date, total_amount_invoice, payment_amount,
912 remaining_amount, partner_code,
913 partner_name, invoice_status)
914 SELECT $3 AS session_id, a.doc_no AS invoice_no, c.sub_ctgr_product_name, COALESCE(f.doc_no, ''-'') AS mou_no,
915 a.doc_date AS invoice_date, (a.total_amount+a.tax_amount) AS total_amount_invoice, d.payment_amount,
916 ((a.total_amount+a.tax_amount)-d.payment_amount) AS sisa, f_get_partner_code(a.partner_id) AS partner_code,
917 f_get_partner_name(a.partner_id) AS partner_name, a.invoice_status
918 FROM tt_sl_invoice a
919 INNER JOIN category_invoice c ON a.invoice_id = c.invoice_id AND a.doc_type_id = c.doc_type_id '||vFilterSubCtgr||'
920 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
921 LEFT OUTER JOIN sl_mou f ON a.mou_id = f.mou_id
922 WHERE a.session_id = $3
923 AND ((a.total_amount+a.tax_amount)-d.payment_amount) <> $13'
924 USING vDocTypeDo,vDocTypeSalesInvoice,pSessionId,vDocTypeConvExcOut,vDocTypeSalesInvoiceTemp,vDocTypeDNAR,vDocTypeCNAR,
925 pTenantId,pUserId,pRoleId,vOne,vDocTypeSoByBrand,vNolAmount,vDocTypeRSIB,vDocTypeRN;
926
927 END IF;
928
929 -- Dapatkan nilai grandTotalInvoice, grandPaymentAmount dan grandRemainingAmount
930 SELECT COALESCE(SUM(total_amount_invoice), vNolAmount) AS grand_total_amount_invoice,
931 COALESCE(SUM(payment_amount), vNolAmount) AS grand_payment_amount,
932 COALESCE(SUM(remaining_amount), vNolAmount) AS grand_remaining_amount
933 INTO vGrandTotalAmountInvoice, vGrandPaymentAmount, vGrandRemainingAmount
934 FROM tt_data_detail_sales_journal
935 WHERE session_id = pSessionId;
936
937 END IF;
938
939 ---------------------------------------------------------------------------------------------------------------------
940
941 -- RETURN DATA HEADER --
942 Open pRefHeader FOR
943 SELECT pSessionId AS session_id, pDatetime AS datetime, f_get_ou_name(pOuId) AS ou_name,
944 f_get_username(pUserId) AS username, f_get_role_name(pRoleId) AS rolename, pYearFromOfPeriod AS period_from, pYearToOfPeriod AS period_to,
945 vSubCtgrName AS sub_ctgr_product_name, f_get_partner_code(pCustomerId)||' - ' ||f_get_partner_name(pCustomerId) AS partner,
946 CASE WHEN pPaymentStatus = vSettledPaymentStatus THEN 'SETTLED'
947 WHEN pPaymentStatus = vNotYetSettledPaymentStatus THEN 'NOT YET SETTLED'
948 ELSE '' END AS payment_status,
949 vNilaiPlafon AS nilai_plafon, vNilaiSoInProgress AS nilai_so_dalam_proses,
950 (vSaldoAr-vNilaiCashBankGiroCair) AS nilai_invoice_belum_bayar, vNilaiReturnNote AS nilai_return_note,
951 vGiroMundur AS nilai_giro_mundur, vGiroBelumAlokasi AS nilai_giro_belum_alokasi,
952 vTotalNilai AS total_nilai_sisa_plafon, vNilaiCurrentARExposure AS nilai_current_ar_posure,
953 vGrandTotalAmountInvoice AS grand_total_amount_invoice, vGrandPaymentAmount AS grand_payment_amount,
954 vGrandRemainingAmount AS grand_remaining_amount, vSystemConfigValueDisclaimer01 AS disclaimer_01,
955 vSystemConfigValueDisclaimer02 AS disclaimer_02;
956 RETURN NEXT pRefHeader;
957
958 -- RETURN DATA DETAIL --
959 Open pRefDetail FOR
960 SELECT invoice_no, sub_ctgr_product_name, mou_no, invoice_date, total_amount_invoice,
961 payment_amount, remaining_amount AS sisa,
962 partner_code, partner_name, invoice_status
963 FROM tt_data_detail_sales_journal
964 WHERE session_id = pSessionId
965 AND vCustomerIsValid = vOne
966 ORDER BY invoice_date, invoice_status;
967 RETURN NEXT pRefDetail;
968
969 --refDetail untuk Info (mengenai pemberitahuan jika tidak ada detail report yg dikembalikan karena tidak memiliki hak akses)
970 Open pRefDetailInfo FOR
971 WITH data_info AS(
972 SELECT 'Tidak ada data mapping salesman hierarki dan customer yang sesuai. Silahkan hubungi admin'::text AS info
973 )
974 SELECT A.info
975 FROM data_info A
976 WHERE (vCustomerIsValid IS NULL OR vCustomerIsValid <> vOne);
977 RETURN NEXT pRefDetailInfo;
978
979
980 -- DELETE TEMP DATA --
981 DELETE FROM tt_sl_invoice WHERE session_id = pSessionId;
982 DELETE FROM tt_report_sales_journal WHERE session_id = pSessionId;
983 DELETE FROM tt_group_customer_item WHERE session_id = pSessionId;
984 DELETE FROM tt_data_detail_sales_journal WHERE session_id = pSessionId;
985
986END;
987$BODY$
988 LANGUAGE plpgsql VOLATILE
989 COST 100
990 ROWS 10000
991 /