Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Function: r_sales_summary_accounting(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, character varying, character varying)
- -- DROP FUNCTION r_sales_summary_accounting(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, character varying, character varying);
- CREATE OR REPLACE FUNCTION r_sales_summary_accounting(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pRefDetail2 REFCURSOR := 'refDetail2';
- pRefSummary REFCURSOR := 'refSummary';
- pRefSummaryDetail REFCURSOR := 'refSummaryDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pOuId ALIAS FOR $6;
- pStartDate ALIAS FOR $7;
- pEndDate ALIAS FOR $8;
- pPartnerId ALIAS FOR $9;
- pDocType ALIAS FOR $10;
- pFlgManualConsignment ALIAS FOR $11;
- vVoid character varying := 'V';
- vEmptyId bigint := -99;
- vEmptyString character varying := '';
- vEmptyNumeric numeric := 0;
- vReleaseDoc character varying := 'R';
- vStock character varying := 'Y';
- vFilterPartner text := '';
- vDocTypeDo bigint := 311;
- vDocTypeDoReceipt bigint := 526;
- vDocTypeReturnNote bigint := 502;
- vDocTypePosShop bigint := 401;
- vDocTypePosShopInShop bigint := 403;
- vDocTypeVoidPosShop bigint := 405;
- vDocTypeVoidPosShopInShop bigint := 406;
- vReceiveGoodsManualDocTypeId bigint := 114;
- vYes character varying := 'Y';
- vNo character varying := 'N';
- vDoc character varying := '';
- vPOS character varying := 'POS';
- vFilterDocType text := '';
- vSubTotal numeric := 0;
- vDPP numeric := 0;
- vPPN numeric := 0;
- BEGIN
- IF (pRoleId = -2) THEN
- SELECT partner_id INTO pPartnerId
- FROM m_partner_user WHERE user_id = pUserId;
- vFilterPartner := ' AND C.supplier_id = ' || pPartnerId;
- ELSE
- IF (pPartnerId <> vEmptyId) THEN
- vFilterPartner := ' AND C.supplier_id = ' || pPartnerId;
- END IF;
- END IF;
- IF (pDocType = vPOS) THEN
- vDoc := 'POS';
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
- INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.trx_pos_id = F.trx_pos_id AND C.supplier_id = F.supplier_id
- AND E.process_no = F.pos_process_no AND E.trx_pos_item_id = F.trx_pos_item_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($8, $9) ' ||
- vFilterPartner || '
- AND F.from_manual = $11
- GROUP BY C.supplier_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypePosShop, vDocTypePosShopInShop, vEmptyId, vNo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- SUBSTRING(A.update_datetime, 1, 8) AS doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
- INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.trx_pos_id = F.trx_pos_id AND C.supplier_id = F.supplier_id
- AND E.process_no = F.pos_process_no AND E.trx_pos_item_id = F.trx_pos_item_id
- WHERE A.tenant_id = $2
- AND SUBSTRING(A.update_datetime, 1, 8) BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($8, $9) ' ||
- vFilterPartner || '
- AND F.from_manual = $11
- GROUP BY C.supplier_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUBSTRING(A.update_datetime, 1, 8)
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeVoidPosShop, vDocTypeVoidPosShopInShop, vEmptyId, vNo;
- ELSE
- vDoc := 'ALL';
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN sl_do D ON A.ref_id = D.do_id
- INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id = $8 ' ||
- vFilterPartner || '
- AND F.from_manual = $9
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo, vNo;
- IF (pFlgManualConsignment = vYes) THEN
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id,A.so_item_id, f_get_doc_desc($10),
- B.doc_type_id,
- B.receive_goods_id,
- B.doc_no,
- B.doc_date, $4,
- C.product_id, f_get_product_code(C.product_id), C.supplier_product_code,
- f_get_product_name(C.product_id), E.style_product, E.color, E.size,
- A.normal_price_correction, A.sold_price_used,
- A.discount, A.margin_supp_correction, A.sold_price_after_margin, SUM(D.qty_rcv_po)
- FROM pu_po_balance_item_consignment_sold A
- INNER JOIN pu_receive_goods B ON A.receive_goods_id = B.receive_goods_id
- INNER JOIN pu_receive_goods_item D ON B.receive_goods_id = D.receive_goods_id AND A.receive_goods_item_id = D.receive_goods_item_id
- INNER JOIN m_product_consignment_supp_info C ON C.product_id = D.product_id
- INNER JOIN m_product_custom E ON E.product_id = C.product_id
- WHERE B.tenant_id = $2
- AND B.doc_date BETWEEN $5 AND $6
- AND B.ou_id = $3
- AND E.flg_buy_konsinyasi = $7
- AND B.doc_type_id = $8 ' || vFilterPartner || '
- AND A.from_manual = $7
- GROUP BY C.supplier_id, A.so_item_id, B.doc_type_id, B.receive_goods_id, B.doc_no, B.doc_date, C.product_id,
- C.supplier_product_code, E.style_product, E.color, E.size,
- A.normal_price_correction, A.sold_price_used, A.discount, A.margin_supp_correction, A.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vReceiveGoodsManualDocTypeId, vYes, vDocTypeDo;
- ELSE
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN sl_do D ON A.ref_id = D.do_id
- INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold_manual F ON F.do_id = D.do_id AND F.do_item_id = E.do_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id = $8 ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo, vNo;
- END IF;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN sl_do D ON A.ref_id = D.do_id
- INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id = $8 ' ||
- vFilterPartner || '
- AND F.from_manual = $9
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo, vNo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
- INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.trx_pos_id = F.trx_pos_id AND C.supplier_id = F.supplier_id
- AND E.process_no = F.pos_process_no AND E.trx_pos_item_id = F.trx_pos_item_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($8, $9) ' ||
- vFilterPartner || '
- AND F.from_manual = $11
- GROUP BY C.supplier_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypePosShop, vDocTypePosShopInShop, vEmptyId, vNo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- SUBSTRING(A.update_datetime, 1, 8) AS doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
- INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.trx_pos_id = F.trx_pos_id AND C.supplier_id = F.supplier_id
- AND E.process_no = F.pos_process_no AND E.trx_pos_item_id = F.trx_pos_item_id
- WHERE A.tenant_id = $2
- AND SUBSTRING(A.update_datetime, 1, 8) BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($8, $9) ' ||
- vFilterPartner || '
- AND F.from_manual = $11
- GROUP BY C.supplier_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUBSTRING(A.update_datetime, 1, 8)
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeVoidPosShop, vDocTypeVoidPosShopInShop, vEmptyId, vNo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
- INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($9) ' ||
- vFilterPartner || '
- AND F.from_manual = $10
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vNo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
- INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($8) ' ||
- vFilterPartner || '
- AND F.from_manual = $10
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vNo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
- INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($9) ' ||
- vFilterPartner || '
- AND F.from_manual = $10
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vNo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
- F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
- INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $5 AND $6
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $7
- AND A.doc_type_id IN ($8) ' ||
- vFilterPartner || '
- AND F.from_manual = $10
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vNo;
- END IF;
- SELECT SUM(sub_total) INTO vSubTotal FROM (
- SELECT (sold_price_after_margin * SUM(qty_so)) AS sub_total
- FROM tr_report_sales_supplier
- WHERE session_id = pSessionId
- AND tenant_id = pTenantId
- GROUP BY transaction_type, doc_no, doc_date, payment_method, product_code,
- supplier_product_code, product_name, product_style, color, psize, normal_price,
- sold_price, discount, margin_supp, sold_price_after_margin, partner_id ) Z;
- vDPP = vSubTotal / 1.1;
- vPPN = vSubTotal - vDPP;
- UPDATE tr_report_sales_supplier_for_sales_summary Z
- SET sold_price_after_margin = 0
- WHERE Z.session_id = pSessionId;
- UPDATE tr_report_sales_supplier_for_sales_summary Z
- SET sold_price_after_margin = B.gl_amount / B.qty
- FROM in_summary_monthly_amount B
- WHERE Z.doc_type_id = B.doc_type_id
- AND Z.product_id = B.product_id
- AND SUBSTRING(Z.doc_date,1,6) = B.date_year_month
- AND Z.session_id = pSessionId
- AND B.qty <> 0
- AND Z.doc_type_id <> vReceiveGoodsManualDocTypeId;
- UPDATE tr_report_sales_supplier_for_sales_summary Z
- SET sold_price_after_margin = B.gl_amount / B.qty
- FROM in_summary_monthly_amount B
- WHERE Z.product_id = B.product_id
- AND SUBSTRING(Z.doc_date,1,6) = B.date_year_month
- AND Z.session_id = pSessionId
- AND B.qty <> 0
- AND B.doc_type_id = vDocTypeDo
- AND Z.doc_type_id = vReceiveGoodsManualDocTypeId;
- UPDATE tr_report_sales_supplier_for_sales_summary A
- SET margin_supp = (A.sold_price_after_margin / A.sold_price) * 100
- WHERE A.session_id = pSessionId
- AND A.sold_price <> 0;
- UPDATE tr_report_sales_supplier_for_sales_summary A
- SET margin_supp = 0
- WHERE A.session_id = pSessionId
- AND A.sold_price = 0;
- Open pRefHeader FOR
- SELECT pPartnerId AS partner_id, CASE WHEN pPartnerId = vEmptyId THEN '(All)' ELSE f_get_partner_name(pPartnerId) END AS partner_name, pStartDate AS start_date,
- pEndDate AS end_date, pDatetime AS print_datetime, pOuId AS ou_id, f_get_ou_name(pOuId) AS ou_name, vDoc AS filter_doc_type,
- ROUND(COALESCE(vDPP, 0.0), 0) AS dpp, ROUND(COALESCE(vPPN, 0.0), 0) AS ppn;
- RETURN NEXT pRefHeader;
- Open pRefDetail FOR
- SELECT A.transaction_type, A.doc_no, A.doc_date, A.payment_method, A.product_code,
- A.supplier_product_code, A.product_name, A.product_style, A.color, A.psize, A.normal_price,
- A.sold_price, A.discount, A.margin_supp, A.sold_price_after_margin, SUM(A.qty_so) AS qty_so, (A.sold_price_after_margin * SUM(A.qty_so)) AS sold_price_after_margin_multiply_quantity,
- f_get_partner_name(A.partner_id) AS supplier_name, f_get_partner_code(A.partner_id) AS supplier_code,
- (A.sold_price * SUM(A.qty_so)) AS sub_total , ROUND(COALESCE(A.sold_price_after_margin * SUM(A.qty_so) / (1.1),0.0),0) AS dpp,
- ROUND(COALESCE(A.sold_price_after_margin * SUM(A.qty_so) - A.sold_price_after_margin * SUM(A.qty_so) / (1.1),0.0),0) AS ppn,
- ROUND((A.sold_price * SUM(A.qty_so))/1.1) as dpp_terjual, ((A.sold_price * SUM(A.qty_so))-ROUND((A.sold_price * SUM(A.qty_so))/1.1)) as ppn_terjual
- FROM tr_report_sales_supplier_for_sales_summary A
- WHERE A.session_id = pSessionId
- AND A.tenant_id = pTenantId
- GROUP BY A.transaction_type, A.doc_no, A.doc_date, A.payment_method, A.product_code,
- A.supplier_product_code, A.product_name, A.product_style, A.color, A.psize, A.normal_price,
- A.sold_price, A.discount, A.margin_supp, A.sold_price_after_margin, A.partner_id
- ORDER BY supplier_name, doc_date, doc_no;
- RETURN NEXT pRefDetail ;
- Open pRefSummary FOR
- SELECT f_get_partner_name(A.partner_id) AS supplier_name, SUM(A.sold_price * A.qty_so) AS sub_total, SUM(A.sold_price_after_margin * A.qty_so) AS sold_price_after_margin_multiply_quantity
- FROM tr_report_sales_supplier_for_sales_summary A
- WHERE A.session_id = pSessionId
- AND A.tenant_id = pTenantId
- GROUP BY A.partner_id
- ORDER BY supplier_name;
- RETURN NEXT pRefSummary;
- DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
- DELETE FROM tr_report_sales_supplier_for_sales_summary WHERE session_id = pSessionId;
- IF (pRoleId = -2) THEN
- SELECT partner_id INTO pPartnerId
- FROM m_partner_user WHERE user_id = pUserId;
- vFilterPartner := ' AND C.supplier_id = ' || pPartnerId;
- ELSE
- IF (pPartnerId <> vEmptyId) THEN
- vFilterPartner := ' AND C.supplier_id = ' || pPartnerId;
- END IF;
- END IF;
- IF (pDocType = vPOS) THEN
- vDoc := 'POS';
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_type_id , A.ref_id, A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, E.gross_sell_price, E.nett_sell_price,
- E.discount_percentage, $5, $5, SUM(A.qty)
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
- INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id IN ($9, $10) ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.trx_pos_item_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- E.gross_sell_price, E.nett_sell_price, E.discount_percentage
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypePosShop, vDocTypePosShopInShop, vEmptyId;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, $11, f_get_doc_desc(A.doc_type_id), A.doc_type_id , A.ref_id, A.doc_no,
- SUBSTRING(A.update_datetime, 1, 8) AS doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, E.gross_sell_price, E.nett_sell_price,
- E.discount_percentage, $5, $5, SUM(A.qty)
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_no = D.doc_no AND A.doc_date = D.doc_date AND D.status = $12
- INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
- WHERE A.tenant_id = $2
- AND SUBSTRING(A.update_datetime, 1, 8) BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id IN ($9, $10) ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- E.gross_sell_price, E.nett_sell_price, E.discount_percentage, SUBSTRING(A.update_datetime, 1, 8)
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypeVoidPosShop, vDocTypeVoidPosShopInShop, vEmptyId, vVoid;
- ELSE
- vDoc := 'ALL';
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, G.gross_sell_price, G.nett_sell_price,
- (((G.gross_sell_price - G.nett_sell_price)/100) * 100), $5, $5, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN sl_do D ON A.ref_id = D.do_id
- INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
- INNER JOIN sl_so_balance_invoice F ON D.do_id = F.ref_id AND D.ref_id = so_id AND E.do_item_id = F.ref_item_id
- AND F.do_receipt_item_id = -99
- AND F.ref_doc_type_id = D.doc_type_id
- INNER JOIN sl_so_item G ON E.ref_id = G.so_item_id AND F.so_id = G.so_id AND E.product_id = G.product_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id = $9 ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- G.gross_sell_price, G.nett_sell_price
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypeDo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, G.gross_sell_price, G.nett_sell_price,
- (((G.gross_sell_price - G.nett_sell_price)/100) * 100), $5, $5, SUM(A.qty) * -1
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN sl_do D ON A.ref_id = D.do_id
- INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
- INNER JOIN sl_so_balance_invoice F ON D.do_id = F.ref_id AND D.ref_id = so_id AND E.do_item_id = F.ref_item_id
- AND F.do_receipt_item_id = -99
- AND F.ref_doc_type_id = D.doc_type_id
- INNER JOIN sl_so_item G ON E.ref_id = G.so_item_id AND F.so_id = G.so_id AND E.product_id = G.product_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id = $9 ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- G.gross_sell_price, G.nett_sell_price
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypeDo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id , doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, $10, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, E.gross_sell_price, E.nett_sell_price,
- E.discount_percentage, $5, $5, SUM(A.qty)
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_type_id = D.doc_type_id
- INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id IN ($9, $10) ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.trx_pos_item_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- E.gross_sell_price, E.nett_sell_price, E.discount_percentage
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypePosShop, vDocTypePosShopInShop, vEmptyId;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, $11, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- SUBSTRING(A.update_datetime, 1, 8) AS doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, E.gross_sell_price, E.nett_sell_price,
- E.discount_percentage, $5, $5, SUM(A.qty)
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN i_trx_pos D ON A.ref_id = D.trx_pos_id AND A.doc_no = D.doc_no AND A.doc_date = D.doc_date AND D.status = $12
- INNER JOIN i_trx_pos_item E ON D.trx_pos_id = E.trx_pos_id AND D.process_no = E.process_no AND D.tenant_id = E.tenant_id
- WHERE A.tenant_id = $2
- AND SUBSTRING(A.update_datetime, 1, 8) BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id IN ($9, $10) ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- E.gross_sell_price, E.nett_sell_price, E.discount_percentage, SUBSTRING(A.update_datetime, 1, 8)
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypeVoidPosShop, vDocTypeVoidPosShopInShop, vEmptyId, vVoid;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, I.gross_sell_price, I.nett_sell_price,
- (((I.gross_sell_price - I.nett_sell_price)/100) * 100), $5, $5, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
- INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN sl_do H ON E.do_id = H.do_id
- INNER JOIN sl_so_balance_invoice F ON
- F.ref_doc_type_id = D.doc_type_id
- AND D.inventory_id = F.ref_id
- AND H.ref_id = F.so_id
- AND E.do_item_id = F.ref_item_id
- INNER JOIN sl_so_item I ON E.ref_id = I.so_item_id AND F.so_id = I.so_id AND E.product_id = I.product_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id IN ($10) ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- I.gross_sell_price, I.nett_sell_price
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypeDoReceipt, vDocTypeReturnNote;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, I.gross_sell_price, I.nett_sell_price,
- (((I.gross_sell_price - I.nett_sell_price)/100) * 100), $5, $5, SUM(A.qty) * -1
- FROM in_log_product_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
- INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN sl_do H ON E.do_id = H.do_id
- INNER JOIN sl_so_balance_invoice F ON H.do_id = F.ref_id AND H.ref_id = F.so_id AND E.do_item_id = F.ref_item_id
- AND G.do_receipt_item_id = F.do_receipt_item_id
- AND F.ref_doc_type_id = $11
- INNER JOIN sl_so_item I ON E.ref_id = I.so_item_id AND F.so_id = I.so_id AND E.product_id = I.product_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id IN ($9) ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- I.gross_sell_price, I.nett_sell_price
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypeDoReceipt, vDocTypeReturnNote, vDocTypeDo;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id , doc_id, doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, I.gross_sell_price, I.nett_sell_price,
- (((I.gross_sell_price - I.nett_sell_price)/100) * 100), $5, $5, SUM(A.qty) * -1
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
- INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN sl_do H ON E.do_id = H.do_id
- INNER JOIN sl_so_balance_invoice F ON
- F.ref_doc_type_id = D.doc_type_id
- AND D.inventory_id = F.ref_id
- AND H.ref_id = F.so_id
- AND E.do_item_id = F.ref_item_id
- INNER JOIN sl_so_item I ON E.ref_id = I.so_item_id AND F.so_id = I.so_id AND E.product_id = I.product_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id IN ($10) ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- I.gross_sell_price, I.nett_sell_price
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypeDoReceipt, vDocTypeReturnNote;
- EXECUTE '
- INSERT INTO tr_report_sales_supplier_for_sales_summary(
- session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_type_id, doc_id , doc_no,
- doc_date, payment_method, product_id, product_code, supplier_product_code,
- product_name, product_style, color, psize, normal_price, sold_price,
- discount, margin_supp, sold_price_after_margin, qty_so)
- SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_type_id, A.ref_id , A.doc_no,
- A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
- f_get_product_name(A.product_id), B.style_product, B.color, B.size, I.gross_sell_price, I.nett_sell_price,
- (((I.gross_sell_price - I.nett_sell_price)/100) * 100), $5, $5, SUM(A.qty) * -1
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product_custom B ON A.product_id = B.product_id
- INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
- INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
- INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
- INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
- INNER JOIN sl_do H ON E.do_id = H.do_id
- INNER JOIN sl_so_balance_invoice F ON H.do_id = F.ref_id AND H.ref_id = F.so_id AND E.do_item_id = F.ref_item_id
- AND G.do_receipt_item_id = F.do_receipt_item_id
- AND F.ref_doc_type_id = $11
- INNER JOIN sl_so_item I ON E.ref_id = I.so_item_id AND F.so_id = I.so_id AND E.product_id = I.product_id
- WHERE A.tenant_id = $2
- AND A.doc_date BETWEEN $6 AND $7
- AND A.ou_id = $3
- AND B.flg_buy_konsinyasi = $8
- AND A.doc_type_id IN ($9) ' ||
- vFilterPartner || '
- GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
- I.gross_sell_price, I.nett_sell_price
- ' USING pSessionId, pTenantId, pOuId, vEmptyString, vEmptyNumeric, pStartDate, pEndDate, vNo, vDocTypeDoReceipt, vDocTypeReturnNote, vDocTypeDo;
- END IF;
- UPDATE tr_report_sales_supplier_for_sales_summary Z
- SET sold_price_after_margin = 0
- WHERE Z.session_id = pSessionId;
- UPDATE tr_report_sales_supplier_for_sales_summary Z
- SET sold_price_after_margin = B.gl_amount / B.qty
- FROM in_summary_monthly_amount B
- WHERE Z.doc_type_id = B.doc_type_id
- AND Z.product_id = B.product_id
- AND SUBSTRING(Z.doc_date,1,6) = B.date_year_month
- AND Z.session_id = pSessionId
- AND B.qty <> 0;
- UPDATE tr_report_sales_supplier_for_sales_summary A
- SET margin_supp = (A.sold_price_after_margin / A.sold_price) * 100
- WHERE A.session_id = pSessionId
- AND A.sold_price <> 0;
- UPDATE tr_report_sales_supplier_for_sales_summary A
- SET margin_supp = 0
- WHERE A.session_id = pSessionId
- AND A.sold_price = 0;
- Open pRefDetail2 FOR
- SELECT transaction_type, doc_no, doc_date, payment_method, product_code,
- supplier_product_code, product_name, product_style, color, psize, normal_price,
- sold_price, discount, margin_supp, sold_price_after_margin, SUM(qty_so) AS qty_so, (sold_price_after_margin * SUM(qty_so)) AS sold_price_after_margin_multiply_quantity,
- f_get_partner_name(partner_id) AS supplier_name, f_get_partner_code(partner_id) AS supplier_code, 'N' AS flg_pkp ,
- (sold_price * SUM(qty_so)) AS sub_total,ROUND((sold_price * SUM(qty_so))/1.1) as dpp_terjual, ((sold_price * SUM(qty_so))-ROUND((sold_price * SUM(qty_so))/1.1)) as ppn_terjual
- FROM tr_report_sales_supplier_for_sales_summary
- WHERE session_id = pSessionId
- AND tenant_id = pTenantId
- GROUP BY transaction_type, doc_no, doc_date, payment_method, product_code,
- supplier_product_code, product_name, product_style, color, psize, normal_price,
- sold_price, discount, margin_supp, sold_price_after_margin, partner_id
- ORDER BY supplier_name, doc_date, doc_no;
- RETURN NEXT pRefDetail2;
- Open pRefSummaryDetail FOR
- SELECT f_get_partner_name(partner_id) AS supplier_name, SUM(sold_price * qty_so) AS sub_total, SUM(sold_price_after_margin * qty_so) AS sold_price_after_margin_multiply_quantity
- FROM tr_report_sales_supplier_for_sales_summary
- WHERE session_id = pSessionId
- AND tenant_id = pTenantId
- GROUP BY partner_id
- ORDER BY supplier_name;
- RETURN NEXT pRefSummaryDetail;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- ALTER FUNCTION r_sales_summary_accounting(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, character varying, character varying)
- OWNER TO sts;
Add Comment
Please, Sign In to add comment