Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --pu_submit_goods_purchasing
- -- DROP FUNCTION pu_submit_goods_purchasing(bigint, character varying, character varying);
- CREATE OR REPLACE FUNCTION pu_submit_goods_purchasing(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vPoId bigint;
- vGoodsPurchasingId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- vStatusRelease character varying(1);
- vStatusFinal character varying(1);
- vEmptyValue character varying(1);
- vFlagInvoice character varying(1);
- vFlagInvoiceY character varying(1);
- vStatusDraft character varying(1);
- vSignJournalD character varying(1);
- vSignJournalC character varying(1);
- vFlagPayment character varying(1);
- vFlgSourceCoa character varying(7);
- vTypeRate character varying(3);
- vEmptyId bigint;
- vPurchaseOrderDocTypeId bigint;
- vJournalTrxId bigint;
- vJournalTrxIdNew bigint;
- vReceiveGoodsDocTypeId bigint;
- vInvoiceApBalanceId bigint;
- vRoundingModeNonTax character varying(5);
- vProductStatus character varying(50);
- vGoodsPurchasingDocTypeId bigint;
- vJournalReceiveGoodsId bigint;
- vTypeDocumentJournal bigint;
- vJointDppPpn character varying(1);
- vDocJournalRg DOC_JOURNAL%ROWTYPE;
- vDocJournalPi DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vDocTypeReceiveGoodsId bigint;
- vDocTypePurchaseInvoiceId bigint;
- BEGIN
- vFlagInvoice := 'N';
- vFlagInvoiceY := 'Y';
- vFlagPayment := 'N';
- vStatusRelease := 'R';
- vStatusFinal := 'F';
- vStatusDraft := 'D';
- vSignJournalD := 'D';
- vSignJournalC := 'C';
- vFlgSourceCoa := 'PRODUCT';
- vTypeRate := 'COM';
- vEmptyId := -99;
- vEmptyValue := ' ';
- vDocTypeReceiveGoodsId := 111;
- vDocTypePurchaseInvoiceId := 131;
- --vJournalReceiveGoodsId := 111;
- vReceiveGoodsDocTypeId := 111;
- vGoodsPurchasingDocTypeId := 160;
- vTypeDocumentJournal := 131;
- vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');
- vProductStatus := 'GOOD';
- /*
- SELECT product_status_code INTO vProductStatus
- FROM m_product_status
- WHERE tenant_id = pTenantId AND flg_buy = 'Y';
- */
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'pu_submit_goods_purchasing' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vGoodsPurchasingId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'goodsPurchasingId';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'userId';
- SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'datetime';
- DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
- UPDATE pu_po SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- WHERE po_id = vGoodsPurchasingId;
- 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 pTenantId, A.ou_id, A.partner_id, A.ref_id,
- A.doc_type_id, A.po_id, A.doc_no, A.doc_date, B.po_item_id, B.qty_po, B.po_uom_id,
- B.curr_code, B.nett_price_po,
- f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
- vFlagInvoiceY, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON A.po_id = B.po_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.po_id = B.po_id
- AND A.tenant_id = pTenantId;
- --FILTER LAINNYA
- 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 pTenantId, A.ou_id, A.partner_id, A.po_id,
- A.doc_type_id, A.po_id, B.po_item_id, B.tax_id, C.flg_amount,
- B.tax_percentage, B.curr_code,
- f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
- f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage),
- vFlagInvoiceY, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON A.po_id = B.po_id
- INNER JOIN m_tax C
- ON B.tax_id = C.tax_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.po_id = B.po_id
- AND B.tax_id = C.tax_id
- AND A.tenant_id = pTenantId;
- --FILTER LAINNYA
- --Insert ke tt_pu_product_balance produck nya belum terdaftar di product 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, B.warehouse_id, vEmptyId, pTenantId, B.ou_id, A.product_id,
- A.serial_number, A.lot_number, A.expired_date, A.year_made,
- vEmptyId, B.doc_date, B.partner_id,
- B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
- A.po_id, B.doc_no, B.doc_date, A.po_item_id,
- C.curr_code, C.gross_price_po, SUM(C.qty_po), C.po_uom_id, SUM(C.qty_int), C.base_uom_id, C.flg_stock
- FROM pu_po_item_product A
- INNER JOIN pu_po B
- ON A.po_id = B.po_id
- INNER JOIN pu_po_item C
- ON C.po_item_id = A.po_item_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- AND NOT EXISTS
- (
- SELECT 1
- FROM in_product_balance Z
- WHERE Z.tenant_id = pTenantId
- AND Z.product_id = A.product_id
- AND Z.serial_number = A.serial_number
- AND Z.lot_number = A.lot_number
- )
- GROUP BY B.warehouse_id, A.tenant_id, B.ou_id, A.product_id, B.doc_date, B.partner_id,
- B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
- A.po_id, B.doc_no, B.doc_date, A.po_item_id,
- C.curr_code, C.gross_price_po, C.po_uom_id, C.base_uom_id, C.flg_stock;
- --Insert ke tt_pu_product_balance produck nya terdaftar di product 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, B.warehouse_id, vEmptyId, A.tenant_id, B.ou_id, A.product_id,
- A.serial_number, A.lot_number, A.expired_date, A.year_made,
- vEmptyId, B.doc_date, B.partner_id,
- B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
- A.po_id, B.doc_no, B.doc_date, A.po_item_id,
- C.curr_code, C.gross_price_po, SUM(C.qty_po), C.po_uom_id, SUM(C.qty_int), C.base_uom_id, C.flg_stock
- FROM pu_po_item_product A
- INNER JOIN pu_po B
- ON B.po_id = A.po_id
- INNER JOIN pu_po_item C
- ON C.po_item_id = A.po_item_id
- INNER JOIN in_product_balance D
- ON D.tenant_id = A.tenant_id
- AND D.product_id = A.product_id
- AND D.serial_number = A.serial_number
- AND D.lot_number = A.lot_number
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- GROUP BY B.warehouse_id, A.tenant_id, B.ou_id, A.product_id, B.doc_date, B.partner_id,
- B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
- A.po_id, B.doc_no, B.doc_date, A.po_item_id,
- C.curr_code, C.gross_price_po, C.po_uom_id, C.base_uom_id, C.flg_stock;
- /*
- * update product_balance_id dari in_product_balance
- */
- UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
- FROM in_product_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.product_id = A.product_id AND
- tt_pu_product_balance.serial_number = A.serial_number AND
- tt_pu_product_balance.lot_number = A.lot_number;
- --Insert ke in_product_balance yang id nya masih emptyId/ tidak ada di in product balance--
- INSERT INTO in_product_balance
- (tenant_id, product_id,
- serial_number, lot_number, product_expired_date, product_year_made,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.product_id,
- A.serial_number, A.lot_number, A.product_expired_date, A.product_year_made,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId AND
- A.product_balance_id = vEmptyId
- GROUP BY A.tenant_id, A.product_id, serial_number, lot_number, product_expired_date, product_year_made;
- /*
- * update product_balance_id yang masih empty, ambil dari in_product_balance yang sebelumnya sudah diinsert
- */
- UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
- FROM in_product_balance A
- WHERE tt_pu_product_balance.session_id = pSessionId AND
- tt_pu_product_balance.product_balance_id = vEmptyId AND
- tt_pu_product_balance.tenant_id = A.tenant_id AND
- tt_pu_product_balance.product_id = A.product_id AND
- tt_pu_product_balance.serial_number = A.serial_number AND
- tt_pu_product_balance.lot_number = A.lot_number;
- --Insert ke in_product_price_balance dengan product balance yang sudah diinsert sebelumnya--
- 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(B.qty_po * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
- SUM(B.qty_int), A.base_uom_id,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_pu_product_balance A
- INNER JOIN pu_po_item B
- ON A.po_id = B.po_id
- WHERE A.session_id = pSessionId AND
- A.po_id = B.po_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;
- 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;
- /*
- * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
- */
- INSERT INTO tt_pu_product_balance_summary_stock
- (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
- product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
- SELECT pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
- vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
- A.po_uom_id, A.base_uom_id, A.flg_stock;
- /*
- * update product_balance_stock, yang sudah ada di in_product_balance_stock
- */
- UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv,
- update_datetime = vDatetime, update_user_id = vUserId,
- version = version + 1
- FROM tt_pu_product_balance_summary_stock 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 yang datanya belum ada
- */
- 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, vDatetime, vUserId, vDatetime, vUserId
- 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, vDatetime, vUserId, vDatetime, vUserId
- 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, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_pu_product_balance A, m_warehouse_ou B
- WHERE A.session_id = pSessionId AND
- A.flg_stock = 'Y' AND
- A.warehouse_id = B.warehouse_id
- 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, A.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, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId AND
- A.flg_stock = 'Y'
- GROUP BY A.tenant_id, A.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, vDatetime, vUserId, vDatetime, vUserId
- 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(vReceiveGoodsDocTypeId, 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
- */
- 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, SUM(A.amount), SUM(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, vDatetime, vUserId, vDatetime, vUserId,
- 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)
- GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id, A.curr_code, A.base_uom_id, A.source_price,
- A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
- 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, SUM(A.amount), SUM(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, vDatetime, vUserId, vDatetime, vUserId,
- A.flg_tax_amount, A.tax_id, A.tax_percentage
- FROM tt_pu_monthly_price_product A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
- A.curr_code, A.base_uom_id, A.source_price, A.ref_doc_type_id, A.ref_id,
- A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
- /*
- * membuat data transaksi jurnal
- */
- SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(vDocTypePurchaseInvoiceId) as doc_pi, f_get_document_journal(vDocTypeReceiveGoodsId) as doc_rg
- FROM pu_po A
- WHERE A.po_id = vGoodsPurchasingId INTO result;
- vOuStructure := result.ou;
- vDocJournalPi := result.doc_pi;
- vDocJournalRg := result.doc_rg;
- -- Sebagai Journal Purchase Invoice
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournalPi).journal_type, (vDocJournalPi).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
- FROM pu_po A
- WHERE A.po_id = vGoodsPurchasingId;
- -- Sebagai Journal Goods Receive
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournalRg).journal_type, (vDocJournalRg).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
- FROM pu_po A
- WHERE A.po_id = vGoodsPurchasingId;
- 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, ref_doc_no, ref_doc_date,
- due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vJournalTrxId, pTenantId, (f_get_document_journal(vReceiveGoodsDocTypeId)).journal_type, vReceiveGoodsDocTypeId, A.po_id, A.doc_no, A.doc_date,
- (f_get_ou_bu_structure(A.ou_id)).ou_bu_id, (f_get_ou_bu_structure(A.ou_id)).ou_branch_id, (f_get_ou_bu_structure(A.ou_id)).ou_sub_bu_id,
- A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
- A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
- A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_po A
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId;
- --A.ref_doc_type_id = B.doc_type_id AND
- --A.ref_id = B.po_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,
- ou_branch_id, ou_sub_bu_id)
- SELECT pTenantId, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId ),
- A.doc_type_id, B.po_item_id,
- A.partner_id, B.product_id, vEmptyId, B.ou_rc_id,
- vEmptyId, vSignJournalD, 'PRODUCT', B.activity_gl_id,
- B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id,
- SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
- A.doc_date, 'COM',
- 1, 1, 'PRODUCT_STOCK', B.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId,
- vEmptyId, vEmptyId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON B.po_id = A.po_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- AND B.flg_stock = 'Y'
- GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
- B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
- /*------------------------------------------------*/
- 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, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId ),
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignJournalC, 'SYSTEM', vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'),
- A.curr_code, 0, vEmptyId,
- SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
- A.doc_date, 'COM',
- 1, 1, 'ACCR_AP', B.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON B.po_id = A.po_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- AND B.flg_stock = 'Y'
- GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
- A.curr_code, B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
- /*------------------------------------------------*/
- SELECT nextval('fi_invoice_ap_balance_seq') INTO vInvoiceApBalanceId;
- IF vJointDppPpn = 'N' THEN
- 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, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
- A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
- vEmptyId, vEmptyId, A.partner_id, A.doc_date,
- A.curr_code, SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
- A.remark, 0, vFlagPayment,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON B.po_id = A.po_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
- A.curr_code, A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
- 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,
- gov_base_amount )
- SELECT pTenantId, A.ou_id, A.doc_type_id, vInvoiceApBalanceId,
- A.partner_id, B.tax_id, B.tax_no, B.tax_date,
- A.curr_code,
- SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
- A.curr_code,
- SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
- A.doc_date, A.remark, 0, vFlagPayment,
- 0, vDatetime, vUserId, vDatetime, vUserId,
- SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage))
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON B.po_id = A.po_id
- INNER JOIN m_tax C
- ON C.tax_id = B.tax_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId;
- perform fi_insert_vat_in_for_efaktur(pTenantId, pSessionId, vGoodsPurchasingId, vUserId, vDatetime);
- ELSE
- 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, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
- A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
- vEmptyId, vEmptyId, A.partner_id, A.doc_date, A.curr_code,
- SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax))
- + SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), COALESCE(B.tax_percentage,0))),
- A.remark, 0, vFlagPayment,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON B.po_id = A.po_id
- LEFT JOIN m_tax C
- ON C.tax_id = B.tax_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
- A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
- /*
- 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, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
- A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
- vEmptyId, vEmptyId, A.partner_id, A.doc_date, A.curr_code,
- SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax))
- + SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
- A.remark, 0, vFlagPayment,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON B.po_id = A.po_id
- INNER JOIN m_tax C
- ON C.tax_id = B.tax_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
- A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
- */
- perform fi_insert_vat_in_invoice_ap_for_efaktur(pTenantId, pSessionId, vGoodsPurchasingId, vUserId, vDatetime);
- END IF;
- /*
- * Insert ke journal gl
- */
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxIdNew;
- 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, ref_doc_no, ref_doc_date,
- due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vJournalTrxIdNew, pTenantId, (f_get_document_journal(vTypeDocumentJournal)).journal_type,
- vTypeDocumentJournal, A.po_id, A.doc_no, A.doc_date,
- (f_get_ou_bu_structure(A.ou_id)).ou_bu_id, (f_get_ou_bu_structure(A.ou_id)).ou_branch_id, (f_get_ou_bu_structure(A.ou_id)).ou_sub_bu_id,
- A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
- A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
- A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM pu_po A
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId;
- --Insert untuk item 1--
- 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,
- gl_curr_code, gl_amount )
- SELECT pSessionId, pTenantId, vJournalTrxIdNew, 1,
- A.doc_type_id, B.po_item_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignJournalD, 'SYSTEM', vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), B.curr_code, 0, vEmptyId,
- SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
- A.doc_date, 'COM',
- 1, 1, 'ACCR_AP', B.remark,
- vEmptyValue, vEmptyId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON B.po_id = A.po_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- AND B.flg_stock = 'Y'
- GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
- B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
- --Insert untuk item 2--
- 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,
- gl_curr_code, gl_amount )
- SELECT pSessionId, pTenantId, vJournalTrxIdNew, 1,
- A.doc_type_id, B.po_id,
- A.partner_id, B.product_id, vEmptyId, vEmptyId,
- vEmptyId, vSignJournalD, 'TAX', vEmptyId,
- C.receive_coa_id, B.curr_code, 0, vEmptyId,
- SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
- A.doc_date, 'COM',
- 1, 1, 'VAT_IN', B.remark,
- vEmptyId, vEmptyId
- FROM pu_po A
- INNER JOIN pu_po_item B
- ON B.po_id = A.po_id
- INNER JOIN m_tax C
- ON C.tax_id = B.tax_id
- WHERE A.po_id = vGoodsPurchasingId
- AND A.tenant_id = pTenantId
- AND B.flg_stock = 'Y'
- GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
- C.receive_coa_id, B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
- 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,
- ou_branch_id, ou_sub_bu_id)
- SELECT pTenantId, 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, vDatetime, vUserId, vDatetime, vUserId,
- vEmptyId, vEmptyId
- 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 pTenantId, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignJournalC, 'SYSTEM', vEmptyId,
- f_get_ap_coa_partner(A.tenant_id, A.partner_id),
- A.curr_code, 0, vEmptyId,
- SUM(A.amount),
- A.journal_date, 'COM',
- 1, 1, 'AP', A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- 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, A.remark;
- --Delete table temp tt_pu_product_balance--
- DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Add Comment
Please, Sign In to add comment