Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP FUNCTION IF EXISTS pu_create_doc_buy_po_internal(character varying, bigint, bigint, character varying);
- --Modified by Adrian, Jun 19 2017
- --Menambahkan group by pada saat update product balance stock
- CREATE OR REPLACE FUNCTION pu_create_doc_buy_po_internal(bigint, character varying, bigint, bigint, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pPoInternalId ALIAS FOR $3;
- pUserId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- vEmptyId bigint;
- vStatusRelease character varying(1);
- vStatusDraft character varying(1);
- vStatusFinal character varying(1);
- vWorkflowStatus character varying(10);
- vEmptyValue character varying(1);
- vFlgAcceptTax character varying(1);
- vPoDocType bigint;
- vRgDocType bigint;
- vPurchInvDocType bigint;
- vPayApDocType bigint;
- vCbOutDocType bigint;
- vPoId bigint;
- vRgId bigint;
- vPurchInvId bigint;
- vPayApId bigint;
- vCbOutId bigint;
- vInvoiceApBalanceId bigint;
- vProductStatus character varying(50);
- vFakturPajakMasukan bigint;
- vOuBuyStructure OU_BU_STRUCTURE%ROWTYPE;
- vDocJournalRg DOC_JOURNAL%ROWTYPE;
- vDocJournalPurchInv DOC_JOURNAL%ROWTYPE;
- vDocJournalCbOut DOC_JOURNAL%ROWTYPE;
- vJournalTrxId bigint;
- result RECORD;
- vItemTaxAmount numeric;
- vGovTaxAmount numeric;
- vTrxCashbankBalanceId bigint;
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vActivityCOA character varying(10);
- vProductCOA character varying(10);
- vSystemCOA character varying(10);
- vCashBankCOA character varying(10);
- vTaxCOA character varying(10);
- vPoInternalDocTypeId bigint;
- vRoundingModeNonTax character varying(5);
- vCountItemTax integer;
- BEGIN
- vEmptyId := -99;
- vStatusRelease := 'R';
- vStatusFinal := 'F';
- vStatusDraft := 'D';
- vWorkflowStatus := 'APPROVED';
- vEmptyValue := ' ';
- vPoDocType := 101;
- vRgDocType := 111;
- vPurchInvDocType := 131;
- vPayApDocType := 231;
- vCbOutDocType := 611;
- vFakturPajakMasukan := 221;
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vActivityCOA := 'ACTIVITY';
- vProductCOA := 'PRODUCT';
- vSystemCOA := 'SYSTEM';
- vCashBankCOA := 'CASHBANK';
- vTaxCOA := 'TAX';
- vCountItemTax := 0;
- vPoInternalDocTypeId := 151;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- /*
- * WTC, 17 Jan 2015
- * diganti dng membaca m_product_status
- */
- --vProductStatus := 'GOOD';
- SELECT product_status_code INTO vProductStatus
- FROM m_product_status
- WHERE flg_buy = 'Y';
- DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
- DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- SELECT NEXTVAL('pu_po_seq') INTO vPoId;
- SELECT NEXTVAL('pu_receive_goods_seq') INTO vRgId;
- SELECT NEXTVAL('pu_invoice_seq') INTO vPurchInvId;
- SELECT NEXTVAL('fi_payment_order_seq') INTO vPayAPId;
- SELECT NEXTVAL('cb_in_out_cashbank_seq') INTO vCbOutId;
- SELECT f_get_ou_bu_structure(A.ou_buy_id) AS ou_buy, A.flg_accept_tax,
- f_get_document_journal(vRgDocType) AS doc_rg,
- f_get_document_journal(vPurchInvDocType) AS doc_inv,
- f_get_document_journal(vCbOutDocType) AS doc_cb
- FROM pu_po_internal A
- WHERE A.po_internal_id = pPoInternalId INTO result;
- vFlgAcceptTax := result.flg_accept_tax;
- vOuBuyStructure := result.ou_buy;
- vDocJournalRg := result.doc_rg;
- vDocJournalPurchInv := result.doc_inv;
- vDocJournalCbOut := result.doc_cb;
- UPDATE pu_po_internal_doc_no SET ref_id = vPoId WHERE po_internal_id = pPoInternalId AND ref_doc_type_id = vPoDocType;
- UPDATE pu_po_internal_doc_no SET ref_id = vRgId WHERE po_internal_id = pPoInternalId AND ref_doc_type_id = vRgDocType;
- UPDATE pu_po_internal_doc_no SET ref_id = vPurchInvId WHERE po_internal_id = pPoInternalId AND ref_doc_type_id = vPurchInvDocType;
- UPDATE pu_po_internal_doc_no SET ref_id = vPayApId WHERE po_internal_id = pPoInternalId AND ref_doc_type_id = vPayApDocType;
- UPDATE pu_po_internal_doc_no SET ref_id = vCbOutId WHERE po_internal_id = pPoInternalId AND ref_doc_type_id = vCbOutDocType;
- /*
- * menghitung total nilai tax berdasarkan nilai item
- * hal ini diperlukan untuk mendapatkan nilai selisih gov tax amount thd total tax item amount,
- * dimana nilai akan diadjust ke nilai item barang, untuk kondisi po internal yang tidak accept tax (FlgAcceptTax = N )
- */
- SELECT COUNT(*) INTO vCountItemTax
- FROM pu_po_internal_item A
- WHERE A.po_internal_id = pPoInternalId AND
- A.tax_id <> vEmptyId;
- IF vCountItemTax > 0 THEN
- SELECT SUM(A.tax_amount)
- INTO vItemTaxAmount
- FROM pu_po_internal_item A
- WHERE A.po_internal_id = pPoInternalId AND
- A.tax_id <> vEmptyId;
- SELECT A.gov_tax_amount INTO vGovTaxAmount
- FROM pu_po_internal_tax A
- WHERE A.po_internal_id = pPoInternalId;
- ELSE
- vItemTaxAmount := 0;
- vGovTaxAmount := 0;
- END IF;
- /*
- * buat data header PO,
- * status = Release
- */
- INSERT INTO pu_po
- (po_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
- ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
- partner_id, purchaser_id, warehouse_id, flg_delivery, curr_code,
- add_discount_percentage, add_discount_amount, top_code, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vPoId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date, A.ou_buy_id,
- A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark,
- A.partner_sell_id, A.purchaser_id, A.warehouse_buy_id, A.flg_delivery, A.curr_code,
- 0, 0, 'COD', vStatusRelease, vWorkflowStatus,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_doc_no B
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id AND
- B.ref_doc_type_id = vPoDocType;
- /*
- * buat data item PO
- * harus perhatikan apakah nilai pajak dari penjual diterima oleh pembeli
- * arti diterima adalah , harga product tidak akan termasuk nilai pajak
- * arti tidak diterima adalah, harga product akan termasuk nilai pajak
- */
- IF vFlgAcceptTax = 'Y' THEN
- INSERT INTO pu_po_item
- (tenant_id, po_id, line_no, ref_doc_type_id, ref_id,
- warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
- flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
- qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
- discount_amount, gross_item_amount, nett_item_amount, tax_amount, activity_gl_id,
- product_coa_id, ou_rc_id, eta, tolerance_rcv_qty, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vPoId, B.line_no, A.doc_type_id, B.po_internal_item_id,
- A.warehouse_buy_id, B.product_id, 'Y', B.curr_code, B.gross_price_po,
- B.flg_tax_amount, B.tax_id, B.tax_percentage, B.tax_price, B.nett_price_po,
- B.qty_int, B.base_uom_id, B.qty_int, B.base_uom_id, B.discount_percentage,
- B.discount_amount, B.gross_item_amount, B.nett_item_amount, B.tax_amount, vEmptyId,
- f_get_product_coa_group_product(A.tenant_id, B.product_id), vEmptyId, A.doc_date, 0, B.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_item B
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id;
- INSERT INTO pu_po_tax
- (tenant_id, po_id, tax_id, flg_amount,
- tax_percentage, base_amount, tax_amount, remark,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vPoId, A.tax_id, A.flg_amount,
- A.tax_percentage, A.base_amount, A.tax_amount, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal_tax A
- WHERE A.po_internal_id = pPoInternalId;
- ELSE
- /*
- * karena flg accept tax = N, tax id, tax percentage, tax amount dianggap tidak ada
- * jika flg tax amount = Y,
- * maka nilai akan ambil apa adanya dari pu_po_internal_item
- * jika flg tax amount = N,
- * maka nilai gross price = nett price, gross amount = nett amount,dimana nilai harus sudah ditambah tax percentage
- *
- */
- INSERT INTO pu_po_item
- (tenant_id, po_id, line_no, ref_doc_type_id, ref_id,
- warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
- flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
- qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
- discount_amount, gross_item_amount, nett_item_amount, tax_amount, activity_gl_id,
- product_coa_id, ou_rc_id, eta, tolerance_rcv_qty, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vPoId, B.line_no, A.doc_type_id, B.po_internal_item_id,
- A.warehouse_buy_id, B.product_id, 'Y', B.curr_code,
- CASE WHEN B.flg_tax_amount = 'Y' THEN (B.gross_price_po - B.discount_amount)
- ELSE
- CASE WHEN vRoundingModeNonTax = 'RD' THEN TRUNC((B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code))
- ELSE ROUND((B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code)) END
- END,
- 'N', vEmptyId, 0, 0,
- CASE WHEN B.flg_tax_amount = 'Y' THEN (B.gross_price_po - B.discount_amount)
- ELSE
- CASE WHEN vRoundingModeNonTax = 'RD' THEN TRUNC((B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code))
- ELSE ROUND((B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code)) END
- END,
- B.qty_int, B.base_uom_id, B.qty_int, B.base_uom_id, 0,
- 0,
- CASE WHEN B.flg_tax_amount = 'Y' THEN B.gross_item_amount - (B.qty_int * B.discount_amount)
- ELSE
- CASE WHEN vRoundingModeNonTax = 'RD' THEN TRUNC(B.qty_int * (B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code))
- ELSE ROUND(B.qty_int * (B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code)) END
- END,
- CASE WHEN B.flg_tax_amount = 'Y' THEN B.gross_item_amount - (B.qty_int * B.discount_amount)
- ELSE
- CASE WHEN vRoundingModeNonTax = 'RD' THEN TRUNC(B.qty_int * (B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code))
- ELSE ROUND(B.qty_int * (B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code)) END
- END,
- 0, vEmptyId,
- f_get_product_coa_group_product(A.tenant_id, B.product_id), vEmptyId, A.doc_date, 0, B.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_item B
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id;
- /*
- * selisih nilai tax amount di detail item dan gov tax amount akan dialokasi kan ke po item yang paling akhir
- */
- UPDATE pu_po_item SET nett_item_amount = nett_item_amount + (vGovTaxAmount - vItemTaxAmount)
- WHERE po_id = vPoId AND
- po_item_id IN (SELECT MAX(po_item_id) FROM pu_po_item WHERE po_id = vPoId);
- END IF;
- /*
- * buat saldo po item dengan status sudah Final
- * karena po internal langsung dianggap terima barang semua nya.
- */
- INSERT INTO pu_po_balance_item
- (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
- qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
- tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
- A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
- A.tolerance_rcv_qty, vStatusFinal,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_item A, pu_po B
- WHERE A.po_id = vPoId AND
- A.po_id = B.po_id;
- /*
- * buat log saldo po item untuk transaksi po item sendiri
- */
- INSERT INTO pu_log_po_balance_item
- (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.po_id, A.po_item_id, vEmptyId, vEmptyId, vEmptyId,
- A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_item A
- WHERE A.po_id = vPoId;
- /*
- * buat data header receive goods sesuai dengan po id di atas
- */
- INSERT INTO pu_receive_goods
- (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
- remark, partner_id, warehouse_id, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vRgId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date,
- A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, vPoDocType, vPoId,
- A.remark, A.partner_sell_id, A.warehouse_buy_id, vStatusRelease, vWorkflowStatus,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_doc_no B
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id AND
- B.ref_doc_type_id = vRgDocType;
- /*
- * buat data item receive goods
- */
- INSERT INTO pu_receive_goods_item
- (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
- product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
- remark, "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vRgId, A.line_no, vPoDocType, A.po_item_id,
- A.product_id, A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id,
- A.remark, 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_item A
- WHERE A.po_id = vPoId;
- /*
- * buat data product receive goods
- * item yang memiliki product balance id
- */
- INSERT INTO pu_receive_goods_product
- (tenant_id, receive_goods_item_id, line_no, product_id, serial_number,
- product_expired_date, product_year_made, lot_number, qty_rcv_po, po_uom_id,
- qty_rcv_int, base_uom_id, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, C.receive_goods_item_id, A.line_no, A.product_id, D.serial_number,
- D.product_expired_date, D.product_year_made, D.lot_number, A.qty_int, A.base_uom_Id,
- A.qty_int, A.base_uom_id, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal_product A, pu_po_item B, pu_receive_goods_item C, in_product_balance D
- WHERE A.po_internal_item_id = B.ref_id AND
- B.po_id = vPoId AND
- B.po_item_id = C.ref_id AND
- A.product_balance_id = D.product_balance_id;
- /*
- * buat log saldo po item untuk item receive goods
- */
- INSERT INTO pu_log_po_balance_item
- (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ref_id, B.ref_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
- B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B
- WHERE A.receive_goods_id = vRgId AND
- A.receive_goods_id = B.receive_goods_id;
- /*
- * buat saldo po invoice
- * dengan status sudah invoice = Y
- * nett item amount langsung digunakan, karena receive goods tidak partial, qty terima sesuai dengan qty order
- */
- INSERT INTO pu_po_balance_invoice
- (tenant_id, ou_id, partner_id, po_id,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
- curr_code, price_po, item_amount, flg_invoice, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
- A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, B.receive_goods_item_id, B.qty_rcv_po, B.po_uom_id,
- C.curr_code, C.nett_price_po,
- C.nett_item_amount,
- 'Y', vPurchInvId,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
- WHERE A.receive_goods_id = vRgId AND
- A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id;
- INSERT INTO pu_po_balance_invoice_tax
- (tenant_id, ou_id, partner_id, po_id,
- ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
- tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
- A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
- C.tax_percentage, C.curr_code,
- f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, C.curr_code), vRoundingModeNonTax),
- f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
- 'Y', vPurchInvId,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
- WHERE A.receive_goods_id = vRgId AND
- A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id AND
- C.tax_id = D.tax_id;
- /*
- * tampung data item receive goods, untuk dapat digunakan dan disimpan ke product_balance, product_price_balance
- */
- INSERT INTO tt_pu_product_balance
- (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
- serial_number, lot_number, product_expired_date, product_year_made,
- product_price_balance_id, product_buy_date, partner_id,
- doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
- po_id, po_no, po_date, po_item_id,
- curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
- SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
- vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
- vEmptyId, A.doc_date, A.partner_id,
- A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.gross_price_po - D.discount_amount, SUM(B.qty_rcv_po), D.po_uom_id, SUM(B.qty_rcv_int), D.base_uom_id, D.flg_stock
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item D, pu_po E
- WHERE A.receive_goods_id = vRgId AND
- A.receive_goods_id = B.receive_goods_id AND
- NOT EXISTS (SELECT 1 FROM pu_receive_goods_product C
- WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
- B.ref_id = D.po_item_id AND
- D.po_id = E.po_id
- GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
- A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;
- INSERT INTO tt_pu_product_balance
- (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
- serial_number, lot_number, product_expired_date, product_year_made,
- product_price_balance_id, product_buy_date, partner_id,
- doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
- po_id, po_no, po_date, po_item_id,
- curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
- SELECT pSessionId, A.warehouse_id, F.product_balance_id, A.tenant_id, A.ou_id, B.product_id,
- C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
- vEmptyId, A.doc_date, A.partner_id,
- A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.gross_price_po - D.discount_amount, SUM(C.qty_rcv_po), D.po_uom_id, SUM(C.qty_rcv_int), D.base_uom_id, D.flg_stock
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product C, pu_po_item D, pu_po E, pu_po_internal_product F
- WHERE A.receive_goods_id = vRgId AND
- A.receive_goods_id = B.receive_goods_id AND
- B.receive_goods_item_id = C.receive_goods_item_id AND
- B.ref_id = D.po_item_id AND
- D.po_id = E.po_id AND
- F.po_internal_item_id = D.ref_id AND
- D.po_item_id = B.ref_id AND
- C.line_no = F.line_no
- GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
- C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
- A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_id, B.receive_goods_item_id, A.doc_no,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock, F.product_balance_id;
- INSERT INTO in_product_price_balance
- (tenant_id, ou_id, product_id, product_balance_id,
- product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
- curr_code, amount, qty, uom_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.product_id, A.product_balance_id,
- A.doc_date, A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.doc_date,
- A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, A.curr_code), vRoundingModeNonTax)),
- SUM(A.qty_int_rcv), A.base_uom_id,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_pu_product_balance A, pu_po_item B
- WHERE A.session_id = pSessionId AND
- A.po_item_id = B.po_item_id
- GROUP BY A.tenant_id, A.ou_id, A.product_id, A.product_balance_id, A.doc_date,
- A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.curr_code, A.base_uom_id, B.nett_item_amount;
- UPDATE tt_pu_product_balance SET product_price_balance_id = A.product_price_balance_id
- FROM in_product_price_balance A
- WHERE tt_pu_product_balance.session_id = pSessionId AND
- tt_pu_product_balance.tenant_id = A.tenant_id AND
- tt_pu_product_balance.ou_id = A.ou_id AND
- tt_pu_product_balance.product_id = A.product_id AND
- tt_pu_product_balance.product_balance_id = A.product_balance_id AND
- tt_pu_product_balance.partner_id = A.partner_id AND
- tt_pu_product_balance.doc_type_id = A.doc_type_id AND
- tt_pu_product_balance.ref_item_id = A.ref_id AND
- tt_pu_product_balance.doc_no = A.doc_no AND
- tt_pu_product_balance.doc_date = A.doc_date;
- /*
- * update product_balance_stock, yang sudah ada di in_produc_balance_stock
- */
- --Modified by Adrian, Jun 19, 2017
- --Mengubah update product balance stock menjadi dari hasil group by
- WITH summed_tt_pu_product_balance AS (
- SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
- SUM(A.qty_int_rcv) AS qty_int_rcv
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- AND A.flg_stock = 'Y'
- GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id
- )
- UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv, update_datetime = pDatetime, update_user_id = pUserId, version = version + 1
- FROM summed_tt_pu_product_balance A
- WHERE --A.session_id = pSessionId AND
- in_product_balance_stock.product_id = A.product_id AND
- in_product_balance_stock.tenant_id = A.tenant_id AND
- in_product_balance_stock.warehouse_id = A.warehouse_id AND
- in_product_balance_stock.product_balance_id = A.product_balance_id AND
- in_product_balance_stock.product_status = vProductStatus;
- --AND A.flg_stock = 'Y';
- /*
- * insert data in_product_balance_stock
- */
- INSERT INTO in_product_balance_stock
- (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, vProductStatus,
- A.base_uom_id, SUM(A.qty_int_rcv),
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId AND
- A.flg_stock = 'Y' AND
- NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
- WHERE A.tenant_id = B.tenant_id AND
- A.warehouse_id = B.warehouse_id AND
- A.product_id = B.product_id AND
- A.product_balance_id = B.product_balance_id AND
- B.product_status = vProductStatus)
- GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.base_uom_id;
- /*
- * insert data product_price_balance_stock
- */
- INSERT INTO in_product_price_balance_stock
- (tenant_id, warehouse_id, product_id, product_balance_id,
- product_price_balance_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
- A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId AND
- A.flg_stock = 'Y'
- GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
- /*
- * buat data log product balance stock
- */
- INSERT INTO in_log_product_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, A.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_pu_product_balance A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- WHERE A.session_id = pSessionId AND
- A.flg_stock = 'Y'
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, A.product_balance_id, A.warehouse_id, A.base_uom_id;
- /*
- * buat data log product price balance stock
- */
- INSERT INTO in_log_product_price_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_pu_product_balance A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- WHERE A.session_id = pSessionId AND
- A.flg_stock = 'Y'
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
- /*
- * add data balance receive goods item yang akan digunakan di inventory,
- * saat akan membuat claim note
- */
- INSERT INTO in_balance_receive_goods_item
- (receive_goods_item_id, tenant_id, ou_id, receive_goods_id, doc_no, doc_date, partner_id,
- po_id, po_no, po_date, po_item_id,
- qty_rcv, qty_return, po_uom_id, qty_int_rcv,
- qty_int_return, base_uom_id, status_item,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.po_id, A.po_no, A.po_date, A.po_item_id,
- SUM(A.qty_rcv), 0, A.po_uom_id, SUM(A.qty_int_rcv),
- 0, A.base_uom_id, vStatusRelease,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- GROUP BY A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.po_id, A.po_no, A.po_date, A.po_item_id, A.po_uom_id, A.base_uom_id;
- /*
- * create monthly price product
- */
- INSERT INTO tt_pu_monthly_price_product
- (session_id, tenant_id, ou_id, year_month_date,
- product_id, curr_code, amount, qty, base_uom_id,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, source_price,
- flg_tax_amount, tax_id, tax_percentage)
- SELECT pSessionId, A.tenant_id, A.ou_id, C.year_month_date,
- A.product_id, A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, A.curr_code), vRoundingModeNonTax)),
- SUM(A.qty_int_rcv), A.base_uom_id,
- A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- 'PURCHASING PO NO : '|| A.po_no || ' DATE : '|| A.po_date,
- B.flg_tax_amount, B.tax_id, B.tax_percentage
- FROM tt_pu_product_balance A, pu_po_item B, dt_date C
- WHERE A.session_id = pSessionId AND
- A.po_item_id = B.po_item_id AND
- A.doc_date = C.string_date
- GROUP BY A.tenant_id, A.ou_id, C.year_month_date, A.product_id, A.curr_code, A.base_uom_id,
- A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.po_no, A.po_date,
- B.flg_tax_amount, B.tax_id, B.tax_percentage;
- UPDATE pu_monthly_price_product SET curr_code = A.curr_code, amount = A.amount,
- qty = A.qty, ref_doc_type_id = A.ref_doc_type_id,
- ref_id = A.ref_id, ref_doc_no = A.ref_doc_no, ref_doc_date = A.ref_doc_date,
- source_price = A.source_price, flg_tax_amount = A.flg_tax_amount,
- tax_id = A.tax_id, tax_percentage = A.tax_percentage
- FROM tt_pu_monthly_price_product A
- WHERE A.session_id = pSessionId AND
- pu_monthly_price_product.tenant_id = A.tenant_id AND
- pu_monthly_price_product.ou_id = A.ou_id AND
- pu_monthly_price_product.year_month_date = A.year_month_date AND
- pu_monthly_price_product.product_id = A.product_id;
- INSERT INTO pu_monthly_price_product
- (tenant_id, ou_id, year_month_date, product_id,
- curr_code, amount, qty, base_uom_id, source_price,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
- "version", create_datetime, create_user_id, update_datetime, update_user_id,
- flg_tax_amount, tax_id, tax_percentage)
- SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
- A.curr_code, A.amount, A.qty, A.base_uom_id, A.source_price,
- A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
- 0, pDatetime, pUserId, pDatetime, pUserId,
- A.flg_tax_amount, A.tax_id, A.tax_percentage
- FROM tt_pu_monthly_price_product A
- WHERE A.session_id = pSessionId AND
- NOT EXISTS (SELECT 1 FROM pu_monthly_price_product B
- WHERE A.tenant_id = B.tenant_id AND
- A.ou_id = B.ou_id AND
- A.product_id = B.product_id AND
- A.year_month_date = B.year_month_date);
- INSERT INTO pu_log_monthly_price_product
- (tenant_id, ou_id, year_month_date, product_id,
- curr_code, amount, qty, base_uom_id, source_price,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
- "version", create_datetime, create_user_id, update_datetime, update_user_id,
- flg_tax_amount, tax_id, tax_percentage)
- SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
- A.curr_code, A.amount, A.qty, A.base_uom_id, A.source_price,
- A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
- 0, pDatetime, pUserId, pDatetime, pUserId,
- A.flg_tax_amount, A.tax_id, A.tax_percentage
- FROM tt_pu_monthly_price_product A
- WHERE A.session_id = pSessionId;
- IF vFlgAcceptTax = 'Y' THEN
- INSERT INTO pu_invoice
- (invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
- remark, partner_id, ou_legal_id, due_date, purchaser_id,
- curr_code, gross_amount, advance_amount, tax_amount, add_amount,
- total_amount, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vPurchInvId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date,
- A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, vPoDocType, vPoId,
- A.remark, A.partner_sell_id, vEmptyId, A.doc_date, A.purchaser_id,
- A.curr_code, SUM(C.item_amount) + SUM(D.gov_tax_amount), 0, SUM(D.gov_tax_amount), 0,
- SUM(C.item_amount), vStatusRelease, vWorkflowStatus,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_doc_no B, pu_po_balance_invoice C, pu_po_internal_tax D
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id AND
- B.ref_doc_type_id = vPurchInvDocType AND
- A.po_internal_id = D.po_internal_id AND
- C.invoice_id = vPurchInvId
- GROUP BY A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date, A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, A.remark,
- A.partner_sell_id, A.purchaser_id, A.curr_code;
- ELSE
- INSERT INTO pu_invoice
- (invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
- remark, partner_id, ou_legal_id, due_date, purchaser_id,
- curr_code, gross_amount, advance_amount, tax_amount, add_amount,
- total_amount, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vPurchInvId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date,
- A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, vPoDocType, vPoId,
- A.remark, A.partner_sell_id, vEmptyId, A.doc_date, A.purchaser_id,
- A.curr_code, SUM(C.item_amount), 0, 0, 0,
- SUM(C.item_amount), vStatusRelease, vWorkflowStatus,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_doc_no B, pu_po_balance_invoice C
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id AND
- B.ref_doc_type_id = vPurchInvDocType AND
- C.invoice_id = vPurchInvId
- GROUP BY A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date, A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, A.remark,
- A.partner_sell_id, A.purchaser_id, A.curr_code;
- END IF;
- INSERT INTO pu_invoice_item
- (tenant_id, invoice_id, line_no, ref_doc_type_id, ref_id,
- ref_item_id, ref_item_amount, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.invoice_id, ROW_NUMBER() OVER ( PARTITION BY A.invoice_id), A.ref_doc_type_id, A.ref_id,
- A.ref_item_id, A.item_amount, vEmptyValue,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_balance_invoice A
- WHERE A.invoice_id = vPurchInvId;
- IF vFlgAcceptTax = 'Y' THEN
- INSERT INTO pu_invoice_tax
- (tenant_id, invoice_id, tax_id, flg_amount, tax_percentage,
- base_amount, tax_amount, tax_no, tax_date, tax_curr_code, gov_tax_amount,
- remark, "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vPurchInvId, A.tax_id, A.flg_amount, A.tax_percentage,
- A.base_amount, A.tax_amount, A.tax_no, A.tax_date, A.tax_curr_code, A.gov_tax_amount,
- A.remark, 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal_tax A
- WHERE A.po_internal_id = pPoInternalId;
- -- Modified by David, 2 Januari 2015 add next val for using for insert into vat in
- SELECT nextval('fi_invoice_ap_balance_seq') INTO vInvoiceApBalanceId;
- INSERT INTO fi_invoice_ap_balance
- (invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
- doc_no, doc_date, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, partner_id, due_date,
- curr_code, amount, remark, payment_amount, flg_payment,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vInvoiceApBalanceId, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
- A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
- A.curr_code, A.total_amount, A.remark, A.total_amount, 'Y',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_invoice A
- WHERE A.invoice_id = vPurchInvId;
- INSERT INTO fi_invoice_tax_ap_balance
- (tenant_id, ou_id, doc_type_id, invoice_ap_balance_id,
- partner_id, tax_id, tax_no, tax_date,
- curr_code, tax_amount, tax_curr_code, gov_tax_amount,
- due_date, remark, payment_amount, flg_payment,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, vFakturPajakMasukan, C.invoice_ap_balance_id,
- A.partner_id, B.tax_id, B.tax_no, B.tax_date,
- A.curr_code, B.tax_amount, B.tax_curr_code, B.gov_tax_amount,
- A.due_date, B.remark, B.gov_tax_amount, 'Y',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_invoice A, pu_invoice_tax B, fi_invoice_ap_balance C
- WHERE A.invoice_id = vPurchInvId AND
- A.invoice_id = B.invoice_id AND
- A.tenant_id = C.tenant_id AND
- A.ou_id = C.ou_id AND
- A.partner_id = C.partner_id AND
- A.doc_type_id = C.doc_type_id AND
- A.invoice_id = C.invoice_ap_id;
- /**
- * David, 2 Januari 2015
- * Call function for insert tax data into fi_vat_in_reporting for tax
- * @see http://jleaf.org:8181/browse/ERPDB-235
- */
- PERFORM fi_insert_vat_in_for_reporting(pTenantId, pSessionId, vInvoiceApBalanceId, pUserId, pDatetime);
- ELSE
- INSERT INTO fi_invoice_ap_balance
- ( tenant_id, ou_id, doc_type_id, invoice_ap_id,
- doc_no, doc_date, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, partner_id, due_date,
- curr_code, amount, remark, payment_amount, flg_payment,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
- A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
- A.curr_code, A.total_amount, A.remark, A.total_amount, 'Y',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_invoice A
- WHERE A.invoice_id = vPurchInvId;
- END IF;
- INSERT INTO fi_payment_order
- (payment_order_id, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, partner_id, partner_bank_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, debit_amount,
- credit_amount, payment_amount, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vPayApId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date,
- A.ou_buy_id, A.partner_sell_id, vEmptyId, A.ext_doc_no, A.ext_doc_date,
- vEmptyId, vEmptyId, A.doc_date, A.curr_code, 0,
- C.gross_amount, C.gross_amount, A.remark, vStatusRelease, vWorkflowStatus,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_doc_no B, pu_invoice C
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id AND
- B.ref_doc_type_id = vPayApDocType AND
- C.invoice_id = vPurchInvId;
- INSERT INTO fi_payment_order_invoice
- (tenant_id, payment_order_id, ref_doc_type_id, ref_id,
- curr_code, debit_amount, credit_amount, payment_amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vPayApId, A.doc_type_id, A.invoice_ap_balance_id,
- A.curr_code, 0, A.amount, 0,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM fi_invoice_ap_balance A
- WHERE A.invoice_ap_id = vPurchInvId AND
- A.doc_type_id = vPurchInvDocType;
- INSERT INTO fi_payment_order_invoice
- (tenant_id, payment_order_id, ref_doc_type_id, ref_id,
- curr_code, debit_amount, credit_amount, payment_amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vPayApId, A.doc_type_id, A.invoice_tax_ap_balance_id,
- A.tax_curr_code, 0, A.gov_tax_amount, 0,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM fi_invoice_tax_ap_balance A, fi_invoice_ap_balance B
- WHERE A.invoice_ap_balance_id = B.invoice_ap_balance_id AND
- B.invoice_ap_id = vPurchInvId AND
- B.doc_type_id = vPurchInvDocType;
- INSERT INTO fi_payment_order_alloc_balance
- (payment_order_id, tenant_id, ou_id,
- credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
- debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount,
- flg_alloc, ref_alloc_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.payment_order_id, A.tenant_id, A.ou_id,
- B.ref_doc_type_id, A.doc_date, B.ref_id, B.curr_code, B.credit_amount,
- vEmptyId, vEmptyValue, vEmptyId, vEmptyValue, 0,
- 'C', vCbOutId, C.ref_doc_type_id, C.ref_doc_no , A.doc_date,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM fi_payment_order A, fi_payment_order_invoice B, pu_po_internal_doc_no C
- WHERE A.payment_order_id = vPayApId AND
- A.payment_order_id = B.payment_order_id AND
- C.po_internal_id = pPoInternalId AND
- C.ref_doc_type_id = vCbOutDocType;
- SELECT nextval('cb_trx_cashbank_balance_seq') INTO vTrxCashbankBalanceId;
- INSERT INTO cb_trx_cashbank_balance
- (trx_cashbank_balance_id, tenant_id, ou_id, doc_type_id, payment_id,
- payment_doc_no, payment_doc_date, payment_remark,
- partner_id, partner_bank_id, curr_code, amount,
- due_date, flg_payment, ref_doc_type_id, ref_id,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vTrxCashbankBalanceId, A.tenant_id, A.ou_id, A.doc_type_id, A.payment_order_id,
- A.doc_no, A.doc_date, A.remark,
- A.partner_id, A.partner_bank_id, A.curr_code, A.payment_amount,
- A.due_date, 'Y', vCbOutDocType, vCbOutId,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM fi_payment_order A
- WHERE A.payment_order_id = vPayApId AND
- A.payment_amount > 0;
- INSERT INTO cb_in_out_cashbank
- (in_out_cashbank_id, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, partner_id, type_in_out_cashbank, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, ref_curr_code, ref_amount, due_date,
- remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vCbOutId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date,
- A.ou_buy_id, A.partner_sell_id, 'O', A.ext_doc_no, A.ext_doc_date,
- vPayApDocType, vTrxCashbankBalanceId, A.curr_code, C.total_amount, A.doc_date,
- A.remark, vStatusRelease, vWorkflowStatus,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_doc_no B, pu_invoice C
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id AND
- B.ref_doc_type_id = vCbOutDocType AND
- C.invoice_id = vPurchInvId AND
- C.total_amount > 0;
- INSERT INTO cb_in_out_cashbank_payment
- (tenant_id, in_out_cashbank_id, cashbank_id,
- mode_payment, bank_payment, no_payment, date_payment,
- curr_code, cashbank_amount, payment_amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vCbOutId, A.cashbank_buy_id,
- 'CASH', vEmptyValue, vEmptyValue, vEmptyValue,
- A.curr_code, C.gross_amount, C.gross_amount,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_po_internal A, pu_po_internal_doc_no B, pu_invoice C
- WHERE A.po_internal_id = pPoInternalId AND
- A.po_internal_id = B.po_internal_id AND
- B.ref_doc_type_id = vCbOutDocType AND
- C.invoice_id = vPurchInvId AND
- C.gross_amount > 0;
- INSERT INTO cb_cashbank_balance
- (tenant_id, ou_id, cashbank_id, cash_bank_date,
- rec_type, curr_code, amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, D.ou_id, B.cashbank_id, A.doc_date,
- 'K', B.curr_code, 0,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM cb_in_out_cashbank A, cb_in_out_cashbank_payment B, m_cashbank_ou D
- WHERE A.in_out_cashbank_id = B.in_out_cashbank_id AND
- A.in_out_cashbank_id = vCbOutId AND
- D.cashbank_id = B.cashbank_id AND
- NOT EXISTS (SELECT 1 FROM cb_cashbank_balance C
- WHERE A.tenant_id = C.tenant_id AND
- D.ou_id = C.ou_id AND
- B.cashbank_id = C.cashbank_id AND
- A.doc_date = C.cash_bank_date AND
- C.rec_type = 'K');
- UPDATE cb_cashbank_balance D SET amount = D.amount + B.cashbank_amount,
- version = D.version + 1, update_datetime = pDatetime, update_user_id = pUserId
- FROM cb_in_out_cashbank A, cb_in_out_cashbank_payment B, m_cashbank_ou C
- WHERE A.in_out_cashbank_id = B.in_out_cashbank_id AND
- A.in_out_cashbank_id = vCbOutId AND
- D.cash_bank_date = A.doc_date AND
- D.tenant_id = A.tenant_id AND
- D.ou_id = C.ou_id AND
- B.cashbank_id = D.cashbank_id AND
- D.cashbank_id = C.cashbank_id AND
- D.rec_type = 'K';
- /*
- * membuat data transaksi jurnal Receive Goods :
- * 1. buat admin
- * 2. buat temlate jurnal
- */
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuBuyStructure).ou_bu_id, A.ou_id, (vDocJournalRg).journal_type, (vDocJournalRg).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
- FROM pu_receive_goods A
- WHERE A.receive_goods_id = vRgId;
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
- INSERT INTO gl_journal_trx
- (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
- ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vJournalTrxId, A.tenant_id, (vDocJournalRg).journal_type, A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date,
- (vOuBuyStructure).ou_bu_id, (vOuBuyStructure).ou_branch_id, (vOuBuyStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_receive_goods A, pu_po B
- WHERE A.receive_goods_id = vRgId AND
- A.ref_doc_type_id = B.doc_type_id AND
- A.ref_id = B.po_id;
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.receive_goods_item_id,
- A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
- vEmptyId, vSignDebit, vProductCOA, C.activity_gl_id,
- C.product_coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
- C.nett_item_amount,
- A.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_STOCK', B.remark
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
- WHERE A.receive_goods_id = vRgId AND
- A.receive_goods_id = B.receive_goods_id AND
- B.ref_id = C.po_item_id AND
- C.flg_stock = 'Y';
- INSERT INTO gl_journal_trx_item
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId;
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
- SUM(A.amount), A.journal_date, A.type_rate,
- 1, 1, 'ACCR_AP', vEmptyValue,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- /*
- * membuat data transaksi jurnal Purch Invoice:
- * 1. buat admin
- * 2. buat temlate jurnal
- */
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuBuyStructure).ou_bu_id, A.ou_id, (vDocJournalPurchInv).journal_type, (vDocJournalPurchInv).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
- FROM pu_invoice A
- WHERE A.invoice_id = vPurchInvId;
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
- INSERT INTO gl_journal_trx
- (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
- ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vJournalTrxId, A.tenant_id, (vDocJournalPurchInv).journal_type, A.doc_type_id, A.invoice_id, A.doc_no, A.doc_date,
- (vOuBuyStructure).ou_bu_id, (vOuBuyStructure).ou_branch_id, (vOuBuyStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_invoice A
- WHERE A.invoice_id = vPurchInvId;
- /*
- * journal detail item receive goods
- * tanggal rate menggunakan tanggal receive goods
- *
- */
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- C.doc_type_id, C.receive_goods_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
- B.ref_item_amount, C.doc_date, vTypeRate,
- 1, 1, 'ACCR_AP', B.remark
- FROM pu_invoice A, pu_invoice_item B, pu_receive_goods C
- WHERE A.invoice_id = vPurchInvId AND
- A.invoice_id = B.invoice_id AND
- B.ref_id = C.receive_goods_id;
- /*
- * journal detail tax purch invoice
- * tanggal rate menggunakan tanggal invoice
- */
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_tax_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vTaxCOA, vEmptyId,
- C.receive_coa_id, C.tax_curr_code, 0, vEmptyId,
- B.gov_tax_amount, A.doc_date, vTypeRate,
- 1, 1, 'VAT_IN', B.remark
- FROM pu_invoice A, pu_invoice_tax B, m_tax C
- WHERE A.invoice_id = vPurchInvId AND
- A.invoice_id = B.invoice_id AND
- B.tax_id = C.tax_id;
- /*
- * buat juournal offside untuk yg credit ap
- * dengan grouping data journal yang lain melakukan debit account lain
- */
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT A.session_id, B.tenant_id, B.journal_trx_id, 1,
- B.doc_type_id, B.doc_id,
- B.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_ap_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vEmptyId,
- SUM(A.amount), B.doc_date, vTypeRate,
- 1, 1, 'AP', B.remark
- FROM tt_journal_trx_item A, gl_journal_trx B
- WHERE A.session_id = pSessionId AND
- A.journal_trx_id = B.journal_trx_id AND
- B.journal_trx_id = vJournalTrxId AND
- A.sign_journal = vSignDebit AND
- A.journal_desc IN ('ACCR_AP','VAT_IN')
- GROUP BY A.session_id, B.tenant_id, B.journal_trx_id, B.doc_type_id, B.doc_id,
- B.partner_id, A.curr_code, B.remark;
- INSERT INTO gl_journal_trx_item
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- journal_desc IN ('ACCR_AP','VAT_IN');
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- journal_desc IN ('AP');
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- /*
- * membuat data transaksi jurnal Cash Bank Out AP:
- * 1. buat admin
- * 2. buat temlate jurnal
- */
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuBuyStructure).ou_bu_id, A.ou_id, (vDocJournalCbOut).journal_type, (vDocJournalCbOut).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
- FROM cb_in_out_cashbank A
- WHERE A.in_out_cashbank_id = vCbOutId;
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
- INSERT INTO gl_journal_trx
- (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
- ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vJournalTrxId, A.tenant_id, (vDocJournalCbOut).journal_type, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date,
- (vOuBuyStructure).ou_bu_id, (vOuBuyStructure).ou_branch_id, (vOuBuyStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.doc_date, A.ref_curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM cb_in_out_cashbank A
- WHERE A.in_out_cashbank_id = vCbOutId;
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- B.credit_doc_type_id, B.credit_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
- f_get_ap_coa_partner(A.tenant_id, A.partner_id), B.credit_curr_code, 0, vEmptyId,
- B.credit_amount, B.credit_doc_date, vTypeRate,
- 1, 1, 'AP', A.remark
- FROM cb_in_out_cashbank A, fi_payment_order_alloc_balance B, cb_trx_cashbank_balance C
- WHERE A.in_out_cashbank_id = vCBOutId AND
- A.ref_id = C.trx_cashbank_balance_id AND
- C.payment_id = B.payment_order_id AND
- B.flg_alloc = 'C';
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, A.in_out_cashbank_id,
- A.partner_id, vEmptyId, B.cashbank_id, vEmptyId,
- vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
- C.coa_id, B.curr_code, 0, vEmptyId,
- B.cashbank_amount, A.doc_date, vTypeRate,
- 1, 1, 'CASH_BANK', A.remark
- FROM cb_in_out_cashbank A, cb_in_out_cashbank_payment B, m_cashbank C
- WHERE A.in_out_cashbank_id = vCbOutId AND
- A.in_out_cashbank_id = B.in_out_cashbank_id AND
- B.cashbank_id = C.cashbank_id;
- INSERT INTO gl_journal_trx_item
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- AND A.journal_desc IN ('AP');
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- AND A.journal_desc = 'CASH_BANK';
- DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
- DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement