Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_report_sales_invoice_mlm_generated(bigint, bigint, character varying, character varying, bigint)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeaderSalesInvoiceMlm REFCURSOR := 'refHeaderSalesInvoiceMlm';
- pRefSalesInvoiceMlm REFCURSOR := 'refSalesInvoiceMlm';
- pOuId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pSessionId ALIAS FOR $3;
- pYearMonth ALIAS FOR $4;
- pPartnerId ALIAS FOR $5;
- vYes character varying := 'Y';
- vEmptyString character varying := '';
- vEmptyBigintValue bigint := -99;
- vDoDocTypeId bigint;
- vDoMlmDocTypeId bigint;
- vReturDocTypeId bigint;
- vBuybackDocTypeId bigint;
- vReturnNoteDocTypeId bigint;
- vDoRecieptDocTypeId bigint;
- vFilterPartner character varying := '';
- vFilterPartnerDoAssembly character varying := '';
- vDateFrom character varying := '';
- vDateTo character varying := '';
- vReportTitle character varying := 'REPORT HASIL GENERATE SALES INVOICE';
- BEGIN
- vDoDocTypeId := 311;
- vDoMlmDocTypeId := 313;
- vReturDocTypeId := 562;
- vBuybackDocTypeId := 563;
- vReturnNoteDocTypeId := 502;
- vDoRecieptDocTypeId := 526;
- IF pPartnerId <> vEmptyBigintValue THEN
- vFilterPartner := 'AND A.partner_id = ' || pPartnerId;
- vFilterPartnerDoAssembly := ' AND D.partner_id = ' || pPartnerId;
- END IF;
- --membuat dateFrom dan dateEnd
- vDateFrom := pYearMonth || '01';
- SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') + interval '1 Month - 1 day','YYYYMMDD') INTO vDateTo;
- --inisialisasi pRefHeaderSalesInvoiceMlm
- OPEN pRefHeaderSalesInvoiceMlm FOR
- SELECT vReportTitle AS report_title, f_get_partner_code(pPartnerId) AS partner_code, f_get_partner_name(pPartnerId) AS partner_name,
- pYearMonth AS year_month_period, vDateFrom, vDateTo;
- RETURN NEXT pRefHeaderSalesInvoiceMlm;
- --memastikan tabel temp kosong
- DELETE FROM tt_report_sales_invoice_generated WHERE session_id = pSessionId;
- DELETE FROM tt_item_penjualan_by_period WHERE session_id = pSessionId;
- --insert data ke tt_item_penjualan_by_period dengan function f_get_data_penjualan_by_period
- PERFORM f_get_data_penjualan_by_period(pSessionId, pTenantId, pOuId, vDateFrom, vDateTo);
- -- DO MLM
- EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
- product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
- WITH komisi_do_mlm AS (
- SELECT A.doc_id, (SUM(A.qty * F.price_after_disc) * E.commission_percentage * 0.01) AS komisi
- FROM tt_item_penjualan_by_period A
- INNER JOIN sl_do_mlm D
- ON A.doc_id = D.do_mlm_id
- INNER JOIN sl_so_mlm E
- ON D.ref_id = E.so_mlm_id
- INNER JOIN sl_so_mlm_balance_product F
- ON E.so_mlm_id = F.so_mlm_id
- AND A.product_id = F.product_id
- LEFT JOIN sl_so_mlm_cost G
- ON E.so_mlm_id = G.so_mlm_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3
- GROUP BY A.doc_id, E.commission_percentage
- )
- SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, E.doc_no, E.doc_date, H.komisi, COALESCE(G.add_amount, 0) AS add_amount,
- A.product_catalog_id, A.product_id, A.qty, F.gross_sell_price, F.discount_member_percentage, F.discount_promo_percentage,
- F.price_after_disc, (A.qty * F.gross_sell_price) AS total_harga_katalog, (A.qty * F.price_after_disc) AS total_harga_netto
- FROM tt_item_penjualan_by_period A
- INNER JOIN sl_do_mlm D
- ON A.doc_id = D.do_mlm_id
- INNER JOIN sl_so_mlm E
- ON D.ref_id = E.so_mlm_id
- INNER JOIN sl_so_mlm_balance_product F
- ON E.so_mlm_id = F.so_mlm_id
- AND A.product_id = F.product_id
- LEFT JOIN sl_so_mlm_cost G
- ON E.so_mlm_id = G.so_mlm_id
- LEFT JOIN komisi_do_mlm H
- ON A.doc_id = H.doc_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
- USING pSessionId, vDoMlmDocTypeId, pYearMonth;
- -- Retur SO MLM
- EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
- product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
- WITH komisi_retur_mlm AS (
- SELECT A.doc_id, (SUM(A.qty * I.price_after_disc) * H.commission_percentage * 0.01) AS komisi
- FROM tt_item_penjualan_by_period A
- INNER JOIN in_inventory E
- ON A.doc_id = E.inventory_id
- AND A.doc_type_id = E.doc_type_id
- INNER JOIN in_inventory_return_buyback_mlm F
- ON E.inventory_id = F.inventory_id
- AND A.product_id = F.product_id
- INNER JOIN sl_so_mlm H
- ON E.ref_id = H.so_mlm_id
- AND E.ref_doc_type_id = H.doc_type_id
- INNER JOIN sl_so_mlm_balance_product I
- ON I.so_mlm_id = H.so_mlm_id
- AND I.product_id = A.product_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3
- GROUP BY A.doc_id, H.commission_percentage
- )
- SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, H.doc_no, H.doc_date, J.komisi, 0, A.product_catalog_id, A.product_id,
- A.qty, I.gross_sell_price , I.discount_member_percentage, I.discount_promo_percentage,
- I.price_after_disc, (I.gross_sell_price * A.qty) AS total_harga_katalog, (I.price_after_disc * A.qty) AS total_harga_netto
- FROM tt_item_penjualan_by_period A
- INNER JOIN in_inventory E
- ON A.doc_id = E.inventory_id
- AND A.doc_type_id = E.doc_type_id
- INNER JOIN in_inventory_return_buyback_mlm F
- ON E.inventory_id = F.inventory_id
- AND A.product_id = F.product_id
- INNER JOIN sl_so_mlm H
- ON E.ref_id = H.so_mlm_id
- AND E.ref_doc_type_id = H.doc_type_id
- INNER JOIN sl_so_mlm_balance_product I
- ON I.so_mlm_id = H.so_mlm_id
- AND I.product_id = A.product_id
- LEFT JOIN komisi_retur_mlm J
- ON A.doc_id = J.doc_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
- USING pSessionId, vReturDocTypeId, pYearMonth;
- -- Buyback
- EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
- product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
- WITH komisi_buyback AS (
- SELECT A.doc_id, (SUM(A.qty * I.price_after_disc) * H.commission_percentage * 0.01) AS komisi
- FROM tt_item_penjualan_by_period A
- INNER JOIN in_inventory E
- ON A.doc_id = E.inventory_id AND A.doc_type_id = E.doc_type_id
- INNER JOIN in_inventory_return_buyback_mlm F
- ON E.inventory_id = F.inventory_id
- AND A.product_id = F.product_id
- INNER JOIN sl_so_mlm H
- ON E.ref_id = H.so_mlm_id
- AND E.ref_doc_type_id = H.doc_type_id
- INNER JOIN sl_so_mlm_balance_product I
- ON I.so_mlm_id = H.so_mlm_id
- AND I.product_id = A.product_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3
- GROUP BY A.doc_id, H.commission_percentage
- )
- SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, H.doc_no, H.doc_date, J.komisi, 0, A.product_catalog_id, A.product_id,
- A.qty, I.gross_sell_price , I.discount_member_percentage, I.discount_promo_percentage,
- I.price_after_disc, (I.gross_sell_price * A.qty) AS total_harga_katalog, (I.price_after_disc * A.qty) AS total_harga_netto
- FROM tt_item_penjualan_by_period A
- INNER JOIN in_inventory E
- ON A.doc_id = E.inventory_id AND A.doc_type_id = E.doc_type_id
- INNER JOIN in_inventory_return_buyback_mlm F
- ON E.inventory_id = F.inventory_id
- AND A.product_id = F.product_id
- INNER JOIN sl_so_mlm H
- ON E.ref_id = H.so_mlm_id
- AND E.ref_doc_type_id = H.doc_type_id
- INNER JOIN sl_so_mlm_balance_product I
- ON I.so_mlm_id = H.so_mlm_id
- AND I.product_id = A.product_id
- LEFT JOIN komisi_buyback J
- ON A.doc_id = J.doc_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
- USING pSessionId, vBuybackDocTypeId, pYearMonth;
- --return note
- EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
- product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
- SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, H.doc_no, H.doc_date, 0, 0, A.product_catalog_id, A.product_id,
- A.qty, (I.gross_sell_price - discount_amount) AS price_after_disc, 0, 0, (I.gross_sell_price - discount_amount) AS price_after_disc,
- (A.qty * (I.gross_sell_price - discount_amount)) AS total_price_after_disc, (A.qty * (I.gross_sell_price - discount_amount)) AS total_price_after_disc
- FROM tt_item_penjualan_by_period A
- INNER JOIN in_inventory D
- ON A.doc_id = D.inventory_id
- AND A.doc_type_id = D.doc_type_id
- INNER JOIN sl_do G
- ON D.ref_id = G.do_id
- AND A.doc_id = G.do_id
- INNER JOIN sl_so H
- ON G.ref_id = H.so_id
- INNER JOIN sl_so_item I
- ON H.so_id = I.so_id
- AND A.product_id = I.product_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
- USING pSessionId, vReturnNoteDocTypeId, pYearMonth;
- --DO
- EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
- product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
- SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, E.doc_no, E.doc_date, 0, COALESCE(G.add_amount, 0), A.product_catalog_id, A.product_id,
- A.qty, (F.gross_sell_price - discount_amount) AS price_after_disc, 0, 0, (F.gross_sell_price - discount_amount) AS price_after_disc,
- (A.qty * (F.gross_sell_price - discount_amount)) AS total_price_after_disc, (A.qty * (F.gross_sell_price - discount_amount)) AS total_price_after_disc
- FROM tt_item_penjualan_by_period A
- INNER JOIN sl_do D
- ON A.doc_id = D.do_id
- INNER JOIN sl_so E
- ON D.ref_id = E.so_id
- INNER JOIN sl_so_item F
- ON E.so_id = F.so_id
- AND A.product_id = F.product_id
- LEFT JOIN sl_so_cost G
- ON E.so_id = G.so_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
- USING pSessionId, vDoDocTypeId, pYearMonth;
- -- Added By Julius, 22 Sept 2017
- -- DO PRODUCT ASSEMBLY
- EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
- product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
- SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, E.doc_no, E.doc_date, 0, COALESCE(G.add_amount, 0), D.product_catalog_id, H.parent_product_id,
- B.qty_dlv_int, (F.price_so - 0) AS price_after_disc, 0, 0, (F.price_so - 0) AS price_after_disc,
- (B.qty_dlv_int * (F.price_so - 0)) AS total_price_after_disc, (B.qty_dlv_int * (F.price_so - 0)) AS total_price_after_disc
- FROM sl_do A
- INNER JOIN sl_do_item B
- ON A.do_id = B.do_id
- INNER JOIN sl_do_child_item C
- ON B.do_item_id = C.do_item_id
- INNER JOIN tt_item_penjualan_by_period D
- ON A.do_id = D.doc_id
- AND C.product_id = D.product_id
- INNER JOIN sl_so E
- ON A.ref_id = E.so_id
- INNER JOIN sl_so_balance_invoice F
- ON E.so_id = F.so_id
- AND B.do_item_id = F.ref_item_id
- LEFT JOIN sl_so_cost G
- ON E.so_id = G.so_id
- INNER JOIN m_product_assembly H
- ON C.product_id = H.child_product_id
- AND B.product_id = H.parent_product_id
- WHERE D.session_id = $1
- AND D.doc_type_id = $2 ' || vFilterPartnerDoAssembly || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
- USING pSessionId, vDoDocTypeId, pYearMonth;
- --DO RECIEPT
- EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
- product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
- SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, G.doc_no, G.doc_date, 0, 0, A.product_catalog_id, A.product_id,
- A.qty, (H.gross_sell_price - discount_amount) AS price_after_disc, 0, 0, (H.gross_sell_price - discount_amount) AS price_after_disc,
- (A.qty * (H.gross_sell_price - discount_amount)) AS total_price_after_disc, (A.qty * (H.gross_sell_price - discount_amount)) AS total_price_after_disc
- FROM tt_item_penjualan_by_period A
- INNER JOIN in_do_receipt D
- ON A.doc_id = D.do_receipt_id
- INNER JOIN in_do_receipt_item E
- ON D.do_receipt_id = E.do_receipt_id
- AND A.product_id = E.product_id
- INNER JOIN sl_do F
- ON D.ref_id = F.do_id AND D.ref_doc_type_id = 311
- INNER JOIN sl_so G
- ON F.ref_id = G.so_id AND F.ref_doc_type_id = 301
- INNER JOIN sl_so_item H
- ON G.so_id = H.so_id
- WHERE A.session_id = $1
- AND A.doc_type_id = $2 ' || vFilterPartner ||' AND SUBSTR(A.doc_date, 1, 6) = $3;'
- USING pSessionId, vDoRecieptDocTypeId, pYearMonth;
- /* NOTE: untuk Zona HK, ditemukan kasus:
- * Jika nilai catalog_price0 dan catalog_price1 sama, maka zona yang akan muncul adalah zona yang terlebih dahulu masuk ke kondisi CASE WHEN
- * Untuk saat ini, sementara ditentukan jika nilainya sama, akan dibuat menjadi HK (catalog_price0)
- */
- OPEN pRefSalesInvoiceMlm FOR
- SELECT f_get_doc_desc(A.doc_type_id) AS doc_desc, A.doc_no, A.doc_date, A.so_no, A.so_date, A.komisi, A.additional_cost, COALESCE(C.product_catalog_code,'-') AS product_catalog_code,
- COALESCE(Z.catalog_name,'-') AS catalog_name, B.product_code, B.product_name, A.qty, A.harga_katalog, A.disc_member, A.disc_promo, A.harga_netto,
- A.total_harga_katalog, A.total_harga_netto,
- CASE
- WHEN A.harga_katalog = COALESCE(C.catalog_price0,0) THEN 'HK'
- WHEN A.harga_katalog = COALESCE(C.catalog_price,0) THEN 'HK1'
- WHEN A.harga_katalog = COALESCE(C.catalog_price2,0) THEN 'HK2'
- ELSE 'OTHER-HK' END AS zona_hk
- FROM tt_report_sales_invoice_generated A
- INNER JOIN m_product B
- ON A.product_id = B.product_id
- LEFT JOIN m_product_catalog C
- ON B.product_id = C.product_id
- AND A.product_catalog_id = C.product_catalog_id
- LEFT JOIN m_catalog Z
- ON C.catalog_id = Z.catalog_id
- WHERE session_id = pSessionId
- GROUP BY f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date, A.so_no, A.so_date, A.komisi, A.additional_cost, COALESCE(C.product_catalog_code,'-'),
- COALESCE(Z.catalog_name,'-'), B.product_code, B.product_name, A.qty, A.harga_katalog, A.disc_member, A.disc_promo, A.harga_netto,
- A.total_harga_katalog, A.total_harga_netto,A.harga_katalog,COALESCE(C.catalog_price0,0),COALESCE(C.catalog_price,0),COALESCE(C.catalog_price2,0), C.product_catalog_code
- ORDER BY f_get_doc_desc(A.doc_type_id), A.doc_date, A.doc_no, COALESCE(Z.catalog_name,'-'), C.product_catalog_code ASC;
- RETURN NEXT pRefSalesInvoiceMlm;
- DELETE FROM tt_report_sales_invoice_generated WHERE session_id = pSessionId;
- DELETE FROM tt_item_penjualan_by_period WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement