Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_stock_amount_card(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pOuId ALIAS FOR $6;
- pProductCode ALIAS FOR $7;
- pProductName ALIAS FOR $8;
- pPeriodStart ALIAS FOR $9;
- pPeriodEnd ALIAS FOR $10;
- vEmptyId bigint := -99;
- vRgDocTypeId bigint := 111;
- vDoDocTypeId bigint := 311;
- vCnDocTypeId bigint := 511;
- vRnDocTypeId bigint := 502;
- vDoReceiptDocTypeId bigint := 526;
- vAdjStockQtyDocTypeId bigint := 521;
- vAdjStockAmountDocTypeId bigint := 522;
- vCostingAllocationDocTypeId bigint := 528;
- vGoodsTransferOutDocTypeId bigint := 533;
- vGoodsTransferInDocTypeId bigint := 535;
- vDeliveryGoodsBorrowingDocTypeId bigint := 551;
- vReturnGoodsBorrowingDocTypeId bigint := 552;
- vZero numeric := 0;
- vEmptyString character varying := '';
- vEmptyData character varying := '-';
- vReleased character varying := 'R';
- vFinal character varying := 'F';
- vNo character varying := 'N';
- vBgnBalance character varying := 'Beginning Balance';
- vBpbDoc character varying := 'BPB';
- vDoDoc character varying := 'DO';
- vCnDoc character varying := 'Claim Note';
- vRnDoc character varying := 'Return Note';
- vDoReceiptDoc character varying := 'DO Receipt';
- vAdjStockQtyDoc character varying := 'Adjustment Stock Qty';
- vAdjStockAmountDoc character varying := 'Adjustment Stock Amount';
- vCostingAllocationDoc character varying := 'Costing Allocation to Product';
- vGoodsTransferOutDoc character varying := 'Goods Transfer Out';
- vDeliveryGoodsBorrowingDoc character varying := 'Delivery Goods Borrowing';
- vFilterProductCode character varying := '';
- vFilterProductName character varying := '';
- vSignJournalCredit character varying := 'C';
- BEGIN
- IF(pProductCode <> '') THEN
- vFilterProductCode := ' AND UPPER(f_get_product_code(X.product_id)) LIKE UPPER(''%'||pProductCode||'%'')';
- END IF;
- IF(pProductName <> '') THEN
- vFilterProductName := ' AND UPPER(f_get_product_name(X.product_id)) LIKE UPPER(''%'||pProductName||'%'')';
- END IF;
- --saldo awal
- -- untuk yang ada beginning balance nya pada bulan tahun tersebut
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, $2, $2,
- A.doc_type_id, $3, $4, $4, $4, $4, sum(A.qty), sum(A.gl_amount),
- sum(A.qty), sum(A.gl_amount)
- from in_summary_monthly_amount A
- JOIN m_product X ON X.product_id = A.product_id
- where date_year_month = $5
- AND doc_type_id = $6
- AND A.ou_bu_id = $7
- AND A.tenant_id = $8'
- || vFilterProductCode || vFilterProductName ||
- ' group by A.product_id, X.product_code, X.product_name, A.doc_type_id
- order by A.product_id'
- USING pSessionId, vEmptyString, vBgnBalance, vZero, pPeriodStart, vEmptyId, pOuId, pTenantId;
- --untuk yang tidak ada beginning balance nya pada bulan tahun tersebut
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, $2, $2,
- $3, $4, $5, $5, $5, $5, $5, $5,
- $5, $5
- from m_product X
- where X.product_id NOT IN(select A.product_id from in_summary_monthly_amount A
- JOIN m_product X ON X.product_id = A.product_id
- where A.date_year_month = $6
- AND A.doc_type_id = $3
- AND A.tenant_id = $7
- group by A.product_id, X.product_code, X.product_name, A.doc_type_id, X.product_id) '
- || vFilterProductCode || vFilterProductName ||
- ' order by X.product_id'
- USING pSessionId, vEmptyString, vEmptyId, vBgnBalance, vZero, pPeriodStart, pTenantId;
- --RG
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date, A.doc_no,
- A.doc_type_id, $2, SUM(D.qty), SUM(D.gl_amount), $3, $3, $3, $3,
- SUM(D.qty), SUM(D.gl_amount)
- from pu_receive_goods A
- JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
- JOIN m_product X ON X.product_id = B.product_id
- INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
- JOIN gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.receive_goods_id = C.doc_id
- AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND E.ou_bu_id = C.ou_bu_id
- JOIN gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.receive_goods_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (E.ou_bu_id = $6 OR E.ou_id = $6)
- AND A.tenant_id = $7
- AND C.status_doc = $8 '
- || vFilterProductCode || vFilterProductName ||
- ' AND A.doc_type_id = $9
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
- USING pSessionId, vBpbDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vRgDocTypeId;
- --DO
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date, A.doc_no,
- A.doc_type_id, $2, $3, $3, SUM(D.qty), SUM(D.gl_amount), $3, $3,
- SUM(D.qty*(-1)), SUM(D.gl_amount*(-1))
- from sl_do A
- JOIN sl_do_item B ON A.do_id = B.do_id
- JOIN m_product X ON X.product_id = B.product_id
- INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
- JOIN gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.do_id = C.doc_id
- AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND E.ou_bu_id = C.ou_bu_id
- JOIN gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.do_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (E.ou_bu_id = $6 OR E.ou_id = $6)
- AND A.tenant_id = $7
- AND C.status_doc = $8 '
- || vFilterProductCode || vFilterProductName ||
- ' AND A.doc_type_id = $9
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
- USING pSessionId, vDoDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vDoDocTypeId;
- --CLAIM NOTE
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
- A.doc_type_id, $2, $3, $3, SUM(D.qty), SUM(D.gl_amount), $3, $3,
- SUM(D.qty*(-1)), SUM(D.gl_amount*(-1))
- FROM in_inventory A
- JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- JOIN m_product X ON X.product_id = B.product_id
- join m_ou_structure Z ON A.ou_from_id = Z.ou_id
- join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
- AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
- join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND C.status_doc = $8
- AND A.doc_type_id = $9
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
- USING pSessionId, vCnDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vCnDocTypeId;
- --RETURN NOTE
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
- A.doc_type_id, $2, SUM(D.qty), SUM(D.gl_amount), $3, $3, $3, $3,
- SUM(D.qty), SUM(D.gl_amount)
- FROM in_inventory A
- JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- JOIN m_product X ON X.product_id = B.product_id
- join m_ou_structure Z ON A.ou_from_id = Z.ou_id
- join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
- AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
- join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND C.status_doc = $8
- AND A.doc_type_id = $9
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
- USING pSessionId, vRnDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vRnDocTypeId;
- --DO RECEIPT
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date, A.doc_no,
- A.doc_type_id, $2, SUM(D.qty), SUM(D.gl_amount), $3, $3, $3, $3,
- SUM(D.qty), SUM(D.gl_amount)
- from in_do_receipt A
- JOIN in_do_receipt_item B ON A.do_receipt_id = B.do_receipt_id
- JOIN m_product X ON X.product_id = B.product_id
- INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
- JOIN gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.do_receipt_id = C.doc_id
- AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND E.ou_bu_id = C.ou_bu_id
- join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.do_receipt_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (E.ou_bu_id = $6 OR E.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND C.status_doc = $8
- AND A.doc_type_id = $9
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
- USING pSessionId, vDoReceiptDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vDoReceiptDocTypeId;
- --ADJ STOCK QTY
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
- A.doc_type_id, $2, SUM(CASE WHEN D.sign_journal <> $10 THEN D.qty ELSE 0 END) AS qty_in,
- SUM(CASE WHEN D.sign_journal <> $10 THEN D.gl_amount ELSE 0 END) AS amount_in,
- SUM(CASE WHEN D.sign_journal = $10 THEN D.qty ELSE 0 END) AS qty_out,
- SUM(CASE WHEN D.sign_journal = $10 THEN D.gl_amount ELSE 0 END) AS amount_out,
- $3, $3,
- SUM(CASE WHEN D.sign_journal = $10 THEN D.qty * (-1) ELSE D.qty END), SUM(CASE WHEN D.sign_journal = $10 THEN D.gl_amount * (-1) ELSE D.gl_amount END)
- FROM in_inventory A
- JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- JOIN m_product X ON X.product_id = B.product_id
- join m_ou_structure Z ON A.ou_from_id = Z.ou_id
- join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
- AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
- join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND C.status_doc = $8
- AND A.doc_type_id = $9
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
- USING pSessionId, vAdjStockQtyDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vAdjStockQtyDocTypeId, vSignJournalCredit;
- --ADJ STOCK AMOUNT
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
- A.doc_type_id, $2, SUM(CASE WHEN D.sign_journal <> $10 THEN D.qty ELSE 0 END) AS qty_in,
- SUM(CASE WHEN D.sign_journal <> $10 THEN D.gl_amount ELSE 0 END) AS amount_in,
- SUM(CASE WHEN D.sign_journal = $10 THEN D.qty ELSE 0 END) AS qty_out,
- SUM(CASE WHEN D.sign_journal = $10 THEN D.gl_amount ELSE 0 END) AS amount_out,
- $3, $3,
- SUM(CASE WHEN D.sign_journal = $10 THEN D.qty * (-1) ELSE D.qty END), SUM(CASE WHEN D.sign_journal = $10 THEN D.gl_amount * (-1) ELSE D.gl_amount END)
- FROM in_inventory A
- JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- JOIN m_product X ON X.product_id = B.product_id
- join m_ou_structure Z ON A.ou_from_id = Z.ou_id
- join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
- AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
- join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND C.status_doc = $8
- AND A.doc_type_id = $9
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
- USING pSessionId, vAdjStockAmountDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vAdjStockAmountDocTypeId, vSignJournalCredit;
- -- COSTING ALLOCATION TO PRODUCT
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
- A.doc_type_id, $2, $3 AS qty_in,
- $3 AS amount_in,
- $3 AS qty_out,
- SUM(D.gl_amount) AS amount_out,
- $3, $3,
- $3, SUM(D.gl_amount)
- FROM in_inventory A
- JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- JOIN m_product X ON X.product_id = B.product_id
- join m_ou_structure Z ON A.ou_from_id = Z.ou_id
- join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
- AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
- join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND C.status_doc = $8
- AND A.doc_type_id = $9
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
- USING pSessionId, vCostingAllocationDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vCostingAllocationDocTypeId;
- -- GOODS TRANSFER OUT
- -- Yang belum dibuatkan Goods Transfer In
- -- Added by Adrian, Jan 31, 2017
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
- A.doc_type_id, $2, $3 AS qty_in,
- $3 AS amount_in,
- SUM(B.qty_realization) AS qty_out,
- $3,
- $3, $3,
- $3, $3
- FROM in_inventory A
- JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- JOIN m_product X ON X.product_id = B.product_id
- join m_ou_structure Z ON A.ou_from_id = Z.ou_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND A.doc_type_id = $9
- AND A.inventory_id NOT IN(
- SELECT ref_id
- FROM in_inventory
- WHERE doc_type_id = $10 AND status_doc = $8
- )
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id, B.qty_realization'
- USING pSessionId, vGoodsTransferOutDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vGoodsTransferOutDocTypeId, vGoodsTransferInDocTypeId;
- -- GOODS TRANSFER OUT
- -- Yang sudah dibuatkan Goods Transfer In
- -- Added by Adrian, Jan 31, 2017
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
- A.doc_type_id, $2, $3 AS qty_in,
- $3 AS amount_in,
- SUM(D.qty_out - D.qty_in) AS qty_out,
- $3,
- $3, $3,
- $3, $3
- FROM in_inventory A
- JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- JOIN m_product X ON X.product_id = B.product_id
- join m_ou_structure Z ON A.ou_from_id = Z.ou_id
- INNER JOIN in_inventory_item C ON C.ref_item_id = B.inventory_item_id
- INNER JOIN in_balance_transfer_in_item D ON D.inventory_item_id = C.inventory_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND A.status_doc = $8
- AND A.doc_type_id = $9
- AND D.flg_receipt = $10
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id, B.qty_realization, B.qty_request, D.qty_out, D.qty_in'
- USING pSessionId, vGoodsTransferOutDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vGoodsTransferOutDocTypeId, vNo;
- -- DELIVERY GOODS BORROWING
- -- Added by Adrian, Jan 31, 2017
- EXECUTE 'INSERT INTO tt_r_stock_amount_card(
- session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
- qty_original, amount_original
- )
- SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
- A.doc_type_id, $2, $3 AS qty_in,
- $3 AS amount_in,
- B.qty_realization AS qty_out,
- $3,
- $3, $3,
- $3, $3
- FROM in_inventory A
- JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- JOIN m_product X ON X.product_id = B.product_id
- join m_ou_structure Z ON A.ou_from_id = Z.ou_id
- INNER JOIN in_inventory_borrow_balance_item C ON C.inventory_item_id = B.inventory_item_id
- WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
- AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
- AND A.tenant_id = $7 '
- || vFilterProductCode || vFilterProductName ||
- ' AND A.doc_type_id = $9
- AND C.status_item <> $10
- AND C.qty_do > C.qty_return
- GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id, B.qty_realization'
- USING pSessionId, vDeliveryGoodsBorrowingDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vDeliveryGoodsBorrowingDocTypeId, vFinal;
- --hapus data produk yang tidak ada transaksi pada periode tersebut
- WITH product_delete AS (
- SELECT session_id, product_code, COUNT(product_code) AS count_delete
- FROM tt_r_stock_amount_card
- WHERE session_id = pSessionId
- GROUP BY session_id, product_code
- )
- DELETE FROM tt_r_stock_amount_card A
- WHERE EXISTS (
- SELECT 1 FROM product_delete B
- WHERE A.product_code = B.product_code
- AND A.session_id = B.session_id
- AND B.count_delete = 1
- );
- Open pRefHeader FOR
- SELECT f_get_ou_name(pOuId) AS ou, pPeriodStart AS start_month_year, pPeriodEnd AS end_month_year,
- pProductCode AS product_code_filter,
- pProductName AS product_name_filter, pDatetime AS datetime, f_get_username(pUserId) AS username;
- RETURN NEXT pRefHeader;
- Open pRefDetail FOR
- SELECT session_id, product_code, product_name, transaction_date, transaction_no,
- doc_type_id, transaction_type, qty_in AS in_qty, amount_in AS in_amount, qty_out AS out_qty, amount_out AS out_amount,
- SUM(qty_original) OVER (PARTITION BY product_code ORDER BY transaction_date, doc_type_id, transaction_no) AS balance_qty,
- SUM(amount_original) OVER (PARTITION BY product_code ORDER BY transaction_date, doc_type_id, transaction_no) AS balance_amount,
- qty_original, amount_original
- FROM tt_r_stock_amount_card
- WHERE session_id = pSessionId
- ORDER BY product_code, transaction_date, doc_type_id, transaction_no;
- RETURN NEXT pRefDetail ;
- DELETE FROM tt_r_stock_amount_card WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement