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