Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION dw_insert_sales_invoice(character varying, character varying, bigint, bigint, character varying, bigint)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pDataId ALIAS FOR $2;
- pTenantId ALIAS FOR $3;
- pOuId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pUserId ALIAS FOR $6;
- vDocTypeInvoice bigint := 321;
- vDocTypeSoByBrand bigint := 398;
- vDocTypeDo bigint := 311;
- vLastId bigint := -99;
- vEmptyId bigint := -99;
- BEGIN
- --cek last_id for Invoice
- IF EXISTS(SELECT 1 FROM dw_last_data_log WHERE tenant_id = pTenantId AND ou_id = pOuId AND doc_type_id = vDocTypeInvoice) THEN
- SELECT data_log_id INTO vLastId
- FROM dw_last_data_log
- WHERE tenant_id = pTenantId AND
- ou_id = pOuId AND
- doc_type_id = vDocTypeInvoice;
- END IF;
- DELETE FROM tt_process_dw_sales_header WHERE session_id = pSessionId;
- --persiapan dokumen yang akan di proses item" nya
- INSERT INTO tt_process_dw_sales_header (
- session_id,doc_type_id, doc_id, data_log_id, doc_no, doc_date,
- year_month, customer_id, salesman_id, province, city)
- SELECT pSessionId,A.doc_type_id, B.invoice_id, A.invoice_ar_balance_id, B.doc_no, B.doc_date,
- LEFT(B.doc_date, 6) AS year_month, B.partner_id, B.salesman_id,
- f_get_province_by_partner(B.partner_id) AS province, f_get_city_by_partner(B.partner_id) AS city
- FROM fi_invoice_ar_balance A
- INNER JOIN sl_invoice B ON A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
- WHERE A.tenant_id = pTenantId
- AND A.ou_id = pOuId
- AND A.invoice_ar_balance_id > vLastId
- AND A.doc_type_id = vDocTypeInvoice;
- --get data Invoice Non FOC dari DO
- INSERT INTO tt_process_dw_sales (
- session_id, data_id, doc_type_id, doc_id, data_log_id, doc_no, doc_date,
- year_month, customer_id, salesman_id, warehouse_id,
- product_id, ctgr_product_id,
- sub_ctgr_product_id,
- brand_id,
- province, city,
- qty, price, item_amount,
- discount_amount, tax_amount)
- SELECT pSessionId, pDataId, A.doc_type_id,A.doc_id, A.data_log_id, A.doc_no, A.doc_date,
- A.year_month, A.partner_id, A.salesman_id, E.warehouse_id,
- D.product_id, f_get_ctgr_product_by_product_id(D.product_id) AS ctgr_product_id,
- f_get_sub_ctgr_product_by_product_id(D.product_id) AS sub_ctgr_product_id,
- f_get_brand_by_product_id(D.product_id) AS brand_id,
- A.province, A.city,
- SUM(G.qty_dlv_so), F.nett_sell_price, SUM(G.item_amount - G.regular_disc_amount) AS item_amount,
- SUM(G.regular_disc_amount) AS discount_amount, SUM(COALESCE(H.tax_amount,0)) AS tax_amount
- FROM tt_process_dw_sales_header A
- INNER JOIN sl_invoice_item C ON A.doc_id = C.invoice_id
- INNER JOIN sl_do_item D ON C.ref_item_id = D.do_item_id
- INNER JOIN sl_do E ON D.do_id = E.do_id
- INNER JOIN sl_so_item F ON D.ref_id = F.so_item_id
- INNER JOIN sl_so_balance_invoice G ON C.tenant_id = G.tenant_id
- AND G.ou_id = B.ou_id
- AND G.so_id = E.ref_id
- AND G.ref_item_id = C.ref_item_id
- AND G.ref_doc_type_id = E.doc_type_id
- AND G.ref_id = E.do_id
- AND C.do_receipt_item_id = G.do_receipt_item_id
- AND C.do_receipt_item_id = vEmptyId
- LEFT OUTER JOIN sl_so_balance_invoice_tax H ON B.tenant_id = H.tenant_id
- AND H.ou_id = B.ou_id
- AND H.so_id = E.ref_id
- AND H.ref_item_id = C.ref_item_id
- AND H.ref_doc_type_id = E.doc_type_id
- AND H.ref_id = E.do_id
- AND C.do_receipt_item_id = H.do_receipt_item_id
- AND C.do_receipt_item_id = vEmptyId
- AND F.tax_id = H.tax_id
- WHERE A.session_id = pSessionId
- AND F.nett_sell_price <> 0
- GROUP BY A.doc_type_id,A.doc_id, A.data_log_id, A.doc_no, A.doc_date,
- A.partner_id, A.salesman_id, E.warehouse_id, D.product_id, F.nett_sell_price,A.province, A.city,A.year_month
- HAVING SUM(G.qty_dlv_so) > 0;
- --get data Invoice FOC
- INSERT INTO tt_process_dw_sales (
- session_id, data_id, doc_type_id, doc_id, data_log_id, doc_no, doc_date,
- year_month, customer_id, salesman_id, warehouse_id,
- product_id, ctgr_product_id,
- sub_ctgr_product_id,
- brand_id,
- province, city,
- qty_foc, price, item_amount,
- discount_amount, tax_amount)
- SELECT pSessionId, pDataId, A.doc_type_id, A.doc_id, A.data_log_id, A.doc_no, A.doc_date,
- A.year_month, A.partner_id, A.salesman_id, E.warehouse_id,
- D.product_id, f_get_ctgr_product_by_product_id(D.product_id) AS ctgr_product_id,
- f_get_sub_ctgr_product_by_product_id(D.product_id) AS sub_ctgr_product_id,
- f_get_brand_by_product_id(D.product_id) AS brand_id,
- A.province, A.city,
- SUM(G.qty_dlv_so), F.nett_sell_price, SUM(G.item_amount - G.regular_disc_amount) AS item_amount,
- SUM(G.regular_disc_amount) AS discount_amount, SUM(COALESCE(H.tax_amount,0)) AS tax_amount
- FROM tt_process_dw_sales_header A
- INNER JOIN sl_invoice_item C ON A.doc_id = C.invoice_id
- INNER JOIN sl_do_item D ON C.ref_item_id = D.do_item_id
- INNER JOIN sl_do E ON D.do_id = E.do_id
- INNER JOIN sl_so_item F ON D.ref_id = F.so_item_id
- INNER JOIN sl_so_balance_invoice G ON C.tenant_id = G.tenant_id
- AND G.ou_id = B.ou_id
- AND G.so_id = E.ref_id
- AND G.ref_item_id = C.ref_item_id
- AND G.ref_doc_type_id = E.doc_type_id
- AND G.ref_id = E.do_id
- AND C.do_receipt_item_id = G.do_receipt_item_id
- AND C.do_receipt_item_id = vEmptyId
- LEFT OUTER JOIN sl_so_balance_invoice_tax H ON B.tenant_id = H.tenant_id
- AND H.ou_id = B.ou_id
- AND H.so_id = E.ref_id
- AND H.ref_item_id = C.ref_item_id
- AND H.ref_doc_type_id = E.doc_type_id
- AND H.ref_id = E.do_id
- AND C.do_receipt_item_id = H.do_receipt_item_id
- AND C.do_receipt_item_id = vEmptyId
- AND F.tax_id = H.tax_id
- WHERE A.session_id = pSessionId
- AND F.nett_sell_price = 0
- GROUP BY A.doc_type_id, A.doc_id, A.data_log_id, A.doc_no, A.doc_date,
- A.partner_id, A.salesman_id, E.warehouse_id, D.product_id, F.nett_sell_price,A.province, A.city,A.year_month
- HAVING SUM(G.qty_dlv_so) > 0;
- -- INSERT DATA DO RECEIPT UNTUK DATA INVOICE
- WITH tt_process_dw_sales_inv_temp AS (
- SELECT data_id, doc_type_id, doc_id, data_log_id
- FROM tt_process_dw_sales
- WHERE session_id = pSessionId
- AND data_id = pDataId
- AND doc_type_id = vDocTypeInvoice
- GROUP BY data_id, doc_type_id, doc_id, data_log_id
- )
- INSERT INTO tt_process_dw_sales (
- session_id, data_id, doc_type_id, doc_id, data_log_id, doc_no, doc_date,
- year_month, customer_id, salesman_id, warehouse_id,
- product_id, ctgr_product_id,
- sub_ctgr_product_id,
- brand_id,
- province, city,
- qty, qty_foc,
- price, item_amount,
- discount_amount, tax_amount)
- SELECT pSessionId, pDataId, B.doc_type_id, B.invoice_id, A.data_log_id, B.doc_no, B.doc_date,
- substr(B.doc_date, 0, 7) AS year_month, B.partner_id, B.salesman_id, F.warehouse_id,
- H.product_id, f_get_ctgr_product_by_product_id(H.product_id) AS ctgr_product_id,
- f_get_sub_ctgr_product_by_product_id(H.product_id) AS sub_ctgr_product_id,
- f_get_brand_by_product_id(H.product_id) AS brand_id,
- f_get_province_by_partner(B.partner_id) AS province, f_get_city_by_partner(B.partner_id) AS city,
- CASE WHEN D.nett_sell_price <> 0 THEN SUM(I.qty_dlv_so) ELSE 0 END AS qty,
- CASE WHEN D.nett_sell_price = 0 THEN SUM(I.qty_dlv_so) ELSE 0 END AS qty_foc,
- D.nett_sell_price, SUM(I.item_amount - I.regular_disc_amount) AS item_amount,
- SUM(I.regular_disc_amount) AS discount_amount, SUM(COALESCE(J.tax_amount, 0)) AS tax_amount
- FROM tt_process_dw_sales_inv_temp A
- INNER JOIN sl_invoice B ON A.doc_type_id = B.doc_type_id AND A.doc_id = B.invoice_id
- INNER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = vDocTypeSoByBrand
- INNER JOIN sl_so_item D ON C.so_id = D.so_id
- INNER JOIN sl_do_item E ON D.so_item_id = E.ref_id
- INNER JOIN sl_do F ON E.do_id = F.do_id AND C.so_id = F.ref_id AND C.doc_type_id = F.ref_doc_type_id
- INNER JOIN in_do_receipt G ON F.do_id = G.ref_id AND G.ref_doc_type_id = vDocTypeDo
- INNER JOIN in_do_receipt_item H ON G.do_receipt_id = H.do_receipt_id AND H.ref_id = E.do_item_id
- INNER JOIN sl_so_balance_invoice I ON B.tenant_id = I.tenant_id
- AND I.ou_id = B.ou_id
- AND I.so_id = C.so_id
- AND I.ref_item_id = E.do_item_id
- AND I.ref_doc_type_id = F.doc_type_id
- AND I.ref_id = F.do_id
- AND H.do_receipt_item_id = I.do_receipt_item_id
- LEFT OUTER JOIN sl_so_balance_invoice_tax J ON B.tenant_id = J.tenant_id
- AND J.ou_id = B.ou_id
- AND J.so_id = C.so_id
- AND J.ref_item_id = E.do_item_id
- AND J.ref_doc_type_id = F.doc_type_id
- AND J.ref_id = F.do_id
- AND H.do_receipt_item_id = J.do_receipt_item_id
- AND D.tax_id = J.tax_id
- GROUP BY B.doc_type_id, B.invoice_id, A.data_log_id, B.doc_no, B.doc_date,
- B.partner_id, B.salesman_id, F.warehouse_id, H.product_id, D.nett_sell_price;
- IF EXISTS(SELECT 1 FROM tt_process_dw_sales WHERE session_id = pSessionId AND data_id = pDataId AND doc_type_id = vDocTypeInvoice) THEN
- SELECT MAX(data_log_id) INTO vLastId
- FROM tt_process_dw_sales
- WHERE session_id = pSessionId AND
- data_id = pDataId AND
- doc_type_id = vDocTypeInvoice;
- END IF;
- IF EXISTS(SELECT 1 FROM dw_last_data_log WHERE tenant_id = pTenantId AND ou_id = pOuId AND doc_type_id = vDocTypeInvoice) THEN
- UPDATE dw_last_data_log
- SET data_log_id = vLastId,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- version = version + 1
- WHERE tenant_id = pTenantId AND
- ou_id = pOuId AND
- doc_type_id = vDocTypeInvoice;
- ELSE
- INSERT INTO dw_last_data_log (
- tenant_id, ou_id, doc_type_id, doc_desc, data_log_id,
- create_datetime, create_user_id, update_datetime, update_user_id, version)
- SELECT pTenantId, pOuId, vDocTypeInvoice, doc_desc, vLastId,
- pDatetime, pUserId, pDatetime, pUserId, 0 AS version
- FROM m_document
- WHERE doc_type_id = vDocTypeInvoice;
- END IF;
- DELETE FROM tt_process_dw_sales_header WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement