Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * modify by TKP, 16 Nov 2015
- * menambahkan qty correction
- *
- * qty missing dan rejected akan kembali ke gudang keluar
- * qty correction akan masuk ke gudang terima
- * qty lost akan tetap dianggap hilang
- *
- */
- CREATE OR REPLACE FUNCTION in_submit_transfer_in_receipt(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vTrfInReceiptId bigint;
- vUserId bigint;
- vEmptyId bigint;
- vDatetime character varying(14);
- vFlagYes character varying(1);
- vFlagNo character varying(1);
- vFlagLost character varying(1);
- vFlagMissing character varying(1);
- vFlagRejected character varying(1);
- vFlagCorrection character varying(1);
- vStatusRelease character varying(1);
- vStatusDraft character varying(1);
- vOutletFromId bigint;
- vOutletToId bigint;
- vDbVersion character varying(10);
- vEmptyValue character varying(1);
- vActivityCOA character varying(20);
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vProductCOA character varying(10);
- vParentOuId bigint;
- vJournalTrxId bigint;
- vCountLost bigint;
- vGoodsTransferInReceiptLostDocTypeId bigint;
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- BEGIN
- vFlagYes := 'Y';
- vFlagNo := 'N';
- vStatusRelease := 'R';
- vStatusDraft := 'D';
- vFlagLost := 'L';
- vFlagMissing := 'M';
- vFlagRejected := 'R';
- vFlagCorrection := 'C';
- vEmptyId := -99;
- vOutletFromId := -99;
- vOutletToId := -99;
- vDbVersion := '1.0';
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vProductCOA := 'PRODUCT';
- vActivityCOA := 'ACTIVITY';
- vCountLost := 0;
- vEmptyValue := ' ';
- vGoodsTransferInReceiptLostDocTypeId := 537;
- DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'in_submit_transfer_in_receipt' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vTrfInReceiptId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'trfInReceiptId';
- 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';
- /*
- * 1. update status doc in_inventory
- * 2. update flag receipt untuk in_balance_transfer_in_item
- * 3. insert data ke in_log_product_balance_stock (rejected dan missing, correction, dan lost)
- * 4. insert / update data ke in_product_balance_stock (rejected dan missing, correction)
- * 5. buat data log untuk update stok outlet jika ou from nya ini adalah ou outlet
- * 6. buat data log untuk update stok outlet jika ou to nya ini adalah ou outlet
- * 7. buat jurnal (jika ada yg lost)
- */
- SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(vGoodsTransferInReceiptLostDocTypeId) as doc
- FROM in_inventory A
- WHERE A.inventory_id = vTrfInReceiptId INTO result;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- /*
- * 1. update status doc in_inventory
- */
- UPDATE in_inventory SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- WHERE inventory_id = vTrfInReceiptId;
- /*
- * 2. update flag receipt untuk in_balance_transfer_in_item
- */
- UPDATE in_balance_transfer_in_item
- SET flg_receipt = vFlagYes, update_datetime = vDatetime, update_user_id = vUserId
- WHERE receipt_id = vTrfInReceiptId;
- /*
- * 3. a. insert data ke in_log_product_balance_stock (kembalikan stok seperti tidak pernah goods transfer out)
- * missing and rejected
- */
- 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, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
- WHERE A.inventory_id = vTrfInReceiptId AND
- A.inventory_id = B.inventory_id AND
- B.reason_receipt_code IN (vFlagMissing,vFlagRejected) AND
- A.warehouse_from_id = C.warehouse_id
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /*
- * 3. b. insert data ke in_log_product_balance_stock untuk yang correction
- * correction
- */
- 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, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
- WHERE A.inventory_id = vTrfInReceiptId AND
- A.inventory_id = B.inventory_id AND
- B.reason_receipt_code = vFlagCorrection AND
- A.warehouse_to_id = C.warehouse_id
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
- /*
- * 3. c. insert data ke in_log_product_balance_stock untuk yang lost dengan referensi doc type khusus (qty * -1)
- * lost
- */
- INSERT INTO in_goods_transfer_lost_item_balance
- (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, C.ou_id, vGoodsTransferInReceiptLostDocTypeId, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(-1 * B.qty_realization),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
- WHERE A.inventory_id = vTrfInReceiptId AND
- A.inventory_id = B.inventory_id AND
- B.reason_receipt_code = vFlagLost AND
- A.warehouse_from_id = C.warehouse_id
- GROUP BY A.tenant_id, C.ou_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /*
- * 4. insert / update data ke in_product_balance_stock
- *
- * a. create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock for missing and rejected
- */
- INSERT INTO tt_in_product_balance_summary_stock
- (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
- B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item_receipt B
- WHERE A.inventory_id = B.inventory_id
- AND A.inventory_id = vTrfInReceiptId
- AND B.reason_receipt_code IN (vFlagMissing,vFlagRejected)
- GROUP BY A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
- /*
- * b. create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock for correction
- *
- */
- INSERT INTO tt_in_product_balance_summary_stock
- (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status,
- B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item_receipt B
- WHERE A.inventory_id = B.inventory_id
- AND A.inventory_id = vTrfInReceiptId
- AND B.reason_receipt_code = vFlagCorrection
- GROUP BY A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
- -- update product balance stock
- UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId,
- version = version + 1
- FROM tt_in_product_balance_summary_stock A
- WHERE A.session_id = pSessionId AND
- A.inventory_id = vTrfInReceiptId 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_id = A.product_id AND
- in_product_balance_stock.product_balance_id = A.product_balance_id AND
- in_product_balance_stock.product_status = A.product_status;
- /*
- * c. insert data in_product_balance_stock for missing and rejected
- */
- 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_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A
- INNER JOIN in_inventory_item_receipt B ON A.inventory_id = B.inventory_id
- WHERE A.inventory_id = vTrfInReceiptId AND
- B.reason_receipt_code IN (vFlagMissing,vFlagRejected) AND
- NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
- WHERE A.tenant_id = C.tenant_id AND
- A.warehouse_from_id = C.warehouse_id AND
- B.product_id = C.product_id AND
- B.product_balance_id = C.product_balance_id AND
- B.product_status = C.product_status)
- GROUP BY A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
- /*
- * d. insert data in_product_balance_stock for correction
- */
- 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_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A
- INNER JOIN in_inventory_item_receipt B ON A.inventory_id = B.inventory_id
- WHERE A.inventory_id = vTrfInReceiptId AND
- B.reason_receipt_code = vFlagCorrection AND
- NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
- WHERE A.tenant_id = C.tenant_id AND
- A.warehouse_to_id = C.warehouse_id AND
- B.product_id = C.product_id AND
- B.product_balance_id = C.product_balance_id AND
- B.product_status = C.product_status)
- GROUP BY A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
- /*
- * 5. buat data log untuk update stok outlet jika ou from ini adalah ou outlet
- */
- -- cek dulu apakah ou from yang digunakan ini merupakan ou outlet
- IF EXISTS(SELECT 1 FROM in_inventory A INNER JOIN i_outlet B ON A.ou_from_id = B.ou_id WHERE A.inventory_id = vTrfInReceiptId) THEN
- SELECT B.outlet_id INTO vOutletFromId
- FROM in_inventory A
- INNER JOIN i_outlet B ON A.ou_from_id = B.ou_id
- WHERE A.inventory_id = vTrfInReceiptId;
- END IF;
- -- jika outlet, maka harus tulis data log agar outlet bisa lakukan update
- IF vOutletFromId <> vEmptyId THEN
- vDbVersion := f_get_value_system_config_by_param_code(pTenantId, 'DbVersion');
- INSERT INTO i_trx_data_log(
- tenant_id, ou_id, doc_date, db_version, type_data,
- mode_log, data_log, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_from_id, A.doc_date, vDbVersion, 'in_log_product_balance_stock',
- 'A', '{"tenantId":'||B.tenant_id||',"createDateTime":"'||B.create_datetime||'","baseUomId":'||B.base_uom_id||',"ouId":'||B.ou_id||',"docNo":"'||B.doc_no||'","refId":'||B.ref_id||',"partnerId":'||B.partner_id||',"qty":'||B.qty||',"warehouseId":'||B.warehouse_id||',"version":'||B.version||',"productId":'||B.product_id||',"updateUserId":'||B.update_user_id||',"productStatus":"'||B.product_status||'","docDate":"'||B.doc_date||'","updateDateTime":"'||B.update_datetime||'","productBalanceId":'||B.product_balance_id||',"createUserId":'||B.create_user_id||',"docTypeId":'||B.doc_type_id||'}',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A
- INNER JOIN in_log_product_balance_stock B ON B.tenant_id = A.tenant_id AND B.ou_id = A.ou_from_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date AND B.warehouse_id = A.warehouse_from_id
- WHERE A.inventory_id = vTrfInReceiptId;
- END IF;
- /*
- * 6. buat data log untuk update stok outlet jika ou to nya ini adalah ou outlet
- */
- -- cek dulu apakah ou to yang digunakan ini merupakan ou outlet
- IF EXISTS(SELECT 1 FROM in_inventory A INNER JOIN i_outlet B ON A.ou_to_id = B.ou_id WHERE A.inventory_id = vTrfInReceiptId) THEN
- SELECT B.outlet_id INTO vOutletToId
- FROM in_inventory A
- INNER JOIN i_outlet B ON A.ou_from_id = B.ou_id
- WHERE A.inventory_id = vTrfInReceiptId;
- END IF;
- -- jika outlet (sudah cek di poin 5), maka harus tulis data log agar outlet bisa lakukan update
- IF vOutletToId <> vEmptyId THEN
- vDbVersion := f_get_value_system_config_by_param_code(pTenantId, 'DbVersion');
- INSERT INTO i_trx_data_log(
- tenant_id, ou_id, doc_date, db_version, type_data,
- mode_log, data_log, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_to_id, A.doc_date, vDbVersion, 'in_log_product_balance_stock',
- 'A', '{"tenantId":'||B.tenant_id||',"createDateTime":"'||B.create_datetime||'","baseUomId":'||B.base_uom_id||',"ouId":'||B.ou_id||',"docNo":"'||B.doc_no||'","refId":'||B.ref_id||',"partnerId":'||B.partner_id||',"qty":'||B.qty||',"warehouseId":'||B.warehouse_id||',"version":'||B.version||',"productId":'||B.product_id||',"updateUserId":'||B.update_user_id||',"productStatus":"'||B.product_status||'","docDate":"'||B.doc_date||'","updateDateTime":"'||B.update_datetime||'","productBalanceId":'||B.product_balance_id||',"createUserId":'||B.create_user_id||',"docTypeId":'||B.doc_type_id||'}',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A
- INNER JOIN in_log_product_balance_stock B ON B.tenant_id = A.tenant_id AND B.ou_id = A.ou_to_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date AND B.warehouse_id = A.warehouse_to_id
- WHERE A.inventory_id = vTrfInReceiptId;
- END IF;
- /*
- * journal goods transfer in receipt
- * Credit Inventory (Stock = PRODUCT_STOCK)
- * Debit Lost Stock (lost amount = LOST_STOCK)
- *
- * Mapping ke account activity gl
- */
- /*
- * membuat data transaksi jurnal :
- * 1. buat admin
- * 2. buat temlate jurnal
- */
- SELECT COUNT(1) INTO vCountLost
- FROM in_inventory_item_receipt A
- WHERE A.tenant_id = pTenantId
- AND A.inventory_id = vTrfInReceiptId
- AND A.reason_receipt_code = vFlagLost;
- IF vCountLost > 0 THEN
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
- FROM in_inventory A
- WHERE A.inventory_id = vTrfInReceiptId;
- 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, (vDocJournal).journal_type, vGoodsTransferInReceiptLostDocTypeId, A.inventory_id, A.doc_no, A.doc_date,
- (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A
- WHERE A.inventory_id = vTrfInReceiptId;
- 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,
- vGoodsTransferInReceiptLostDocTypeId, B.inventory_item_receipt_id,
- A.partner_id, B.product_id, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vProductCOA, vEmptyId,
- f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
- 0, A.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_STOCK', B.remark
- FROM in_inventory A, in_inventory_item_receipt B
- WHERE A.inventory_id = vTrfInReceiptId AND
- A.inventory_id = B.inventory_id AND
- B.reason_receipt_code = vFlagLost;
- 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,
- vEmptyId, vEmptyId,
- vEmptyId, vEmptyId, vEmptyId, A.ou_rc_id,
- A.segment_id, vSignDebit, vActivityCOA, A.activity_gl_id,
- D.coa_id, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
- 0, A.doc_date, vTypeRate,
- 1, 1, 'LOST_STOCK', vEmptyValue
- FROM in_inventory A, in_inventory_item_receipt B, m_activity_gl D
- WHERE A.inventory_id = vTrfInReceiptId AND
- A.inventory_id = B.inventory_id AND
- A.activity_gl_id = D.activity_gl_id AND
- B.reason_receipt_code = vFlagLost
- GROUP BY A.tenant_id, A.segment_id, A.ou_rc_id, D.coa_id, A.doc_date, A.activity_gl_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, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc = 'PRODUCT_STOCK';
- 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, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc = 'LOST_STOCK';
- END IF;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement