Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- */
- CREATE OR REPLACE FUNCTION in_submit_return_note_from_generate(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vReturnNoteId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- vFlagInvoice character varying(1);
- vEmptyId bigint;
- vStatusRelease character varying(1);
- vEmptyValue character varying(1);
- vStatusFinal character varying(1);
- vStatusDraft character varying(1);
- vFlagYes character varying(1);
- vFlagNo character varying(1);
- vEmptyString character varying(1);
- vJournalType character varying(20);
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vProductCOA character varying(10);
- vSystemCOA character varying(10);
- vParentOuId bigint;
- vJournalTrxId bigint;
- vNol numeric;
- vOuId bigint;
- vOuWarehouseId bigint;
- vDocTypeId bigint;
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- vOuStructureJournalItem OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vReturnNoteDocTypeId bigint;
- vRoundingModeNonTax character varying(5);
- vTaxPercentage numeric;
- vTaxId bigint;
- vPartnerIdMagento bigint;
- vReturnNotePartnerId bigint;
- vReturnNoteDocDate character varying(8);
- vPkpStartDate character varying(8);
- vSalesOrderDocTypeId bigint;
- vClaimNoteId bigint;
- vClaimNoteDocTypeId bigint;
- vClaimNoteDocNo character varying(30);
- vClaimNoteNumId bigint;
- vEmpty character varying(1);
- vEmptyIdString character varying;
- vRemarkPrefix text;
- vWorkflowApproved character varying;
- vReceiveGoodsDocTypeId bigint;
- vRoleIdForGenerateDoc bigint := -99;
- vUserIdForGenerateDoc bigint := -99;
- vClaimNoteFlowId bigint := -99;
- vClaimNoteScheme character varying := 'EA02';
- vFlgUserRole character varying := 'R';
- vParamUserIdForGenerateDoc character varying := 'user.id.for.return.note.for.finance';
- vParamRoleIdForGenerateDoc character varying := 'role.id.for.return.note.for.finance';
- BEGIN
- vFlagInvoice := 'N';
- vEmptyId := -99;
- vEmptyValue := ' ';
- vStatusRelease := 'R';
- vStatusFinal := 'F';
- vStatusDraft := 'D';
- vFlagYes := 'Y';
- vFlagNo := 'N';
- vEmptyString := ' ';
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vProductCOA := 'PRODUCT';
- vSystemCOA := 'SYSTEM';
- vNol := 0;
- vDocTypeId := 311;
- vReturnNoteDocTypeId := 502;
- vSalesOrderDocTypeId := 301;
- vClaimNoteDocTypeId := 511;
- vEmpty := '';
- vWorkflowApproved := 'APPROVED';
- vReceiveGoodsDocTypeId := 111;
- vEmptyIdString = '-99';
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- -- Menampung nilai tax_id dan tax_percentage dari table m_tax ke variable vTaxId dan vTaxPercentage
- SELECT tax_id, percentage INTO vTaxId, vTaxPercentage FROM m_tax WHERE tenant_id = pTenantId and tax_code = 'PPn';
- -- Menampung nilai partner_id magento dari sysconfig ke variable vPartnerIdMagento
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'partner.id.e.commerce')::bigint INTO vPartnerIdMagento;
- -- Menampung nilai pkp start date dari sysconfig ke variable vPkpStartDate
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'pkp.start.date') INTO vPkpStartDate;
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'in_submit_return_note_from_generate' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vReturnNoteId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'returnNoteId';
- 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';
- -- Menampung nilai doc_date return note dan partner_id return note ke dalam variable vReturnNoteDocDate dan vReturnNotePartnerId
- SELECT A.doc_date, B.partner_bill_to_id INTO vReturnNoteDocDate, vReturnNotePartnerId
- FROM in_inventory A
- INNER JOIN sl_so B ON a.ref_doc_type_id = 301 AND A.ref_id = B.so_id
- WHERE A.inventory_id = vReturnNoteId;
- DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
- /*
- * 1.update status doc in_inventory
- * 2.update data ke in_product_balance_stock / insert data ke in_product-balance_stock
- * 3.insert data ke in_log_product_balance_stock
- * 4.update data sl_so_balance_item
- * 5.insert data sl_log_so_balance_item
- * 6.insert data sl_so_balance_invoice
- * 7.insert data sl_so_balance_invoice_tax
- * 8.update data in_balance_do_item
- * 9.add trx jurnal
- */
- SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, '[AUTO GENERATED FROM '|| A.doc_no || ' | ' || A.doc_date ||']' AS remark_prefix
- FROM in_inventory A
- WHERE A.inventory_id = vReturnNoteId INTO result;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- vRemarkPrefix := result.remark_prefix;
- UPDATE in_inventory SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
- WHERE inventory_id = vReturnNoteId;
- /*
- * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
- */
- 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 B
- WHERE A.inventory_id = B.inventory_id
- AND A.inventory_id = vReturnNoteId
- 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;
- /*
- * insert data in_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, A.ou_to_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 B
- WHERE A.inventory_id = vReturnNoteId AND
- A.inventory_id = B.inventory_id
- GROUP BY A.tenant_id, A.ou_to_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;
- /*
- Added Julius Tanuwijaya, 16 Nov 2016
- 1. buat sysConfig -> pkp.start.date : diisi dengan 20161001
- 2. Ambil nilai syconfig tampung ke variabel vPkpStartDate
- 3. Ambil doc_date dari return note yang disubmit tampung ke variabel vReturnNoteDate
- 4. Ambil dari m_tax untuk tenant id bersangkutan dgn tax code PPn, tampung vUsedTaxId, dan vUsedTaxPercentage
- 5. Ambil partner_id dari dokumen return note tampung ke vReturnNotePartnerId
- 6. Ambil partner_id eCommerce (MAGENTO) dari sysconfig tampung ke vMagentoPartnerId
- 7. IF vReturnNotePartnerId = vMagentoPartnerId AND vReturnNoteDate >= vPkpStartDate THEN
- --custom code
- -- INSERT ke tt_in_so_balance_item
- flg_tax_amount selalu Yes (Y)
- tax_id pakai vUsedTaxId
- tax_percentage pakai vUsedTaxPercentage
- ELSE
- -- default code insert ke tt_in_so_balance_item
- END IF
- */
- /*
- * insert data temporer tt_in_so_balance_item
- */
- IF vReturnNotePartnerId = vPartnerIdMagento AND vReturnNoteDocDate >= vPkpStartDate THEN
- -- INSERT ke tt_in_so_balance_item
- -- flg_tax_amount selalu Yes (Y)
- -- tax_id pakai vUsedTaxId
- -- tax_percentage pakai vUsedTaxPercentage
- INSERT INTO tt_in_so_balance_item
- (session_id, tenant_id, ou_id, doc_type_id,
- doc_no, doc_date, inventory_id, partner_id,
- inventory_item_id, so_id, do_id, do_item_id,
- qty_return, base_uom_id, remark,
- so_item_id, curr_code, price,
- flg_tax_amount, qty_so, qty_int_so,
- so_uom_id, tax_id, tax_percentage)
- SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
- A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
- B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
- SUM(B.qty_realization), B.base_uom_id, A.remark,
- -99, A.curr_code, C.dpp_price,
- vFlagYes, 0, 0,
- B.base_uom_id, vTaxId, vTaxPercentage
- FROM in_inventory A, in_inventory_item B, tt_generate_return_note_return_purch_invoice C, sl_so E
- WHERE A.inventory_id = vReturnNoteId AND
- A.inventory_id = B.inventory_id AND
- C.return_note_item_id = B.inventory_item_id AND
- C.so_id = E.so_id
- GROUP BY A.inventory_id, B.inventory_item_id, E.partner_bill_to_id,
- B.inventory_item_id, C.dpp_price;
- ELSE
- -- default code insert ke tt_in_so_balance_item
- INSERT INTO tt_in_so_balance_item
- (session_id, tenant_id, ou_id, doc_type_id,
- doc_no, doc_date, inventory_id, partner_id,
- inventory_item_id, so_id, do_id, do_item_id,
- qty_return, base_uom_id, remark,
- so_item_id, curr_code, price,
- flg_tax_amount, qty_so, qty_int_so,
- so_uom_id, tax_id, tax_percentage)
- SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
- A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
- B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
- SUM(B.qty_realization), B.base_uom_id, A.remark,
- D.so_item_id, D.curr_code, D.gross_sell_price,
- D.flg_tax_amount, D.qty_so, D.qty_int,
- D.so_uom_id, D.tax_id, D.tax_percentage
- FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
- WHERE A.inventory_id = vReturnNoteId AND
- A.inventory_id = B.inventory_id AND
- B.ref_item_id = C.do_item_id AND
- C.so_item_id = D.so_item_id AND
- D.so_id = E.so_id
- GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
- A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
- B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
- B.base_uom_id, D.so_item_id, D.curr_code, D.nett_sell_price, D.qty_so, D.qty_int, D.so_uom_id, D.tax_id ;
- END IF;
- /*
- * buat data sl_so_balance_invoice
- */
- INSERT INTO sl_so_balance_invoice
- (tenant_id, ou_id, partner_id, so_id,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
- curr_code, price_so, item_amount, flg_invoice, invoice_id,
- regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
- A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, SUM(A.qty_return), A.so_uom_id,
- A.curr_code, A.price, SUM(A.qty_return) * A.price, vFlagInvoice, vEmptyId,
- 0, 0, 0, 0,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_in_so_balance_item A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, A.so_uom_id, A.curr_code, A.price;
- /*
- * buat data sl_so_balance_invoice_tax
- */
- INSERT INTO sl_so_balance_invoice_tax
- (tenant_id, ou_id, partner_id, so_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.so_id,
- A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id, D.flg_amount,
- A.tax_percentage, A.curr_code, SUM(A.qty_return * A.price),
- f_tax_rounding(A.tenant_id, SUM(A.qty_return * A.price), A.tax_percentage), vFlagInvoice, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_in_so_balance_item A, m_tax D, tt_generate_return_note_return_purch_invoice B
- WHERE A.session_id = pSessionId AND
- A.tax_id = D.tax_id AND
- A.so_item_id = B.so_item_id AND
- A.inventory_item_id = B.inventory_item_id AND
- B.tax_price <> 0
- GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id,
- D.flg_amount, A.tax_percentage, A.curr_code;
- /*
- * journal return note
- * Debit Inventory = dari nilai COGS
- * Credit HPP
- */
- /*
- * membuat data transaksi jurnal :
- * 1. buat admin
- * 2. buat temlate jurnal
- */
- 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 = vReturnNoteId;
- 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, A.doc_type_id, 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 = vReturnNoteId;
- 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.inventory_item_id,
- A.partner_id, B.product_id, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, 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 B
- WHERE A.inventory_id = vReturnNoteId AND
- A.inventory_id = B.inventory_id;
- /* NK, 1 Feb 2014, journal HPP tidak perlu break down sampai ke product
- 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.inventory_item_id,
- A.partner_id, B.product_id, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
- 0 , A.doc_date, vTypeRate,
- 1, 1, 'HPP', B.remark
- FROM in_inventory A, in_inventory_item B
- WHERE A.inventory_id = vReturnNoteId AND
- A.inventory_id = B.inventory_id;
- */
- /*
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id, ou_id, sub_ou_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, B.journal_trx_id, 1,
- A.doc_type_id, A.inventory_item_id, B.ou_id, B.sub_ou_id,
- vEmptyId, C.product_id, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, A.qty_return, A.base_uom_id,
- f_get_amount_before_tax((A.qty_return * A.qty_so * A.price) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage,0),
- A.doc_date, vTypeRate,
- 1, 1, 'HPP', A.remark
- FROM tt_in_so_balance_item A, gl_journal_trx B, in_inventory_item C
- WHERE A.session_id = pSessionId AND
- B.journal_trx_id = vJournalTrxId AND
- A.inventory_item_id = C.inventory_item_id;
- 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 = 'HPP';
- */
- 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 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,
- (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
- 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 ),
- vEmptyId, vEmptyId,
- vEmptyId, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
- 0, A.journal_date, A.type_rate,
- 1, 1, 'COGS', vEmptyValue,
- 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.journal_date, A.type_rate;
- ========================================================================
- BELUM TAHU BAWAH INI DIGIMANAKAN (belum dilakukan perubahan)
- ========================================================================
- --Jika Return Note adalah for Finance, buat data Claim Note
- IF EXISTS (
- SELECT (1)
- FROM in_return_note_for_finance A
- WHERE A.inventory_id = vReturnNoteId
- AND A.flg_for_finance = vFlagYes
- ) THEN
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- DELETE FROM tt_in_po_balance_item WHERE session_id = pSessionId;
- SELECT NEXTVAL('in_inventory_seq') INTO vClaimNoteId;
- SELECT CAST(A.process_parameter_value AS character varying(30)) INTO vClaimNoteDocNo FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'CLAIM_NOTE_NO';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vClaimNoteNumId FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'CLAIM_NOTE_NUM_ID';
- INSERT INTO in_inventory(
- inventory_id, tenant_id, doc_type_id, doc_no, doc_date, ou_from_id,
- ou_to_id, partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id,
- ref_id, remark, warehouse_from_id, warehouse_to_id, transfer_date,
- receive_date, activity_gl_id, ou_rc_id, no_vehicle, flg_delivery,
- delivery_code, status_doc, workflow_status, version, create_datetime,
- create_user_id, update_datetime, update_user_id, segment_id,
- group_product_id)
- SELECT vClaimNoteId, A.tenant_id, vClaimNoteDocTypeId, vClaimNoteDocNo, A.doc_date, C.ou_id,
- C.ou_id, C.partner_id, A.doc_no, A.doc_date, C.doc_type_id,
- C.po_id, vRemarkPrefix || A.remark, A.warehouse_from_id, A.warehouse_to_id, A.transfer_date,
- A.receive_date, vEmptyId, vEmptyId, A.no_vehicle, A.flg_delivery,
- A.delivery_code, vStatusRelease, vWorkflowApproved, 0, vDatetime,
- vUserId, vDatetime, vUserId, vEmptyId,
- vEmptyId
- FROM in_inventory A
- INNER JOIN in_return_note_for_finance B
- ON A.inventory_id = B.inventory_id
- INNER JOIN pu_po C
- ON B.po_id = C.po_id
- WHERE A.inventory_id = vReturnNoteId;
- INSERT INTO in_inventory_item(
- tenant_id, inventory_id, line_no, product_id,
- product_balance_id, product_status, serial_number, product_expired_date,
- product_year_made, lot_number, ref_doc_type_id, ref_id, ref_item_id,
- curr_code, amount, base_uom_id, qty_request, qty_realization,
- remark, version, create_datetime, create_user_id, update_datetime,
- update_user_id)
- SELECT A.tenant_id, vClaimNoteId, D.line_no, E.product_id,
- E.product_balance_id, F.product_status, G.serial_number, G.product_expired_date,
- G.product_year_made, G.lot_number, vReceiveGoodsDocTypeId, C.receive_goods_id, C.receive_goods_item_id,
- vEmptyIdString, 0, C.base_uom_id,
- CASE WHEN H.qty < E.qty THEN
- H.qty
- ELSE
- E.qty
- END,
- A.qty_return,
- D.remark, 0, vDatetime, vUserId, vDatetime,
- vUserId
- FROM tt_in_so_balance_item A
- INNER JOIN pu_po_item B
- ON A.so_item_id = B.ref_id
- INNER JOIN in_balance_receive_goods_item C
- ON B.po_item_id = C.po_item_id
- AND C.status_item = 'I'
- INNER JOIN in_inventory_item D
- ON A.inventory_item_id = D.inventory_item_id
- INNER JOIN in_log_product_balance_stock E
- ON C.tenant_id = E.tenant_id
- AND E.ref_id = C.receive_goods_id
- AND C.doc_date = E.doc_date
- AND B.product_id = E.product_id
- AND E.qty > 0
- INNER JOIN tt_in_product_balance_summary_stock F
- ON F.tenant_id = E.tenant_id
- --AND F.warehouse_id = E.warehouse_id
- AND F.product_id = E.product_id
- AND F.product_balance_id = E.product_balance_id
- INNER JOIN in_product_balance G
- ON F.product_balance_id = G.product_balance_id
- INNER JOIN in_product_balance_stock H
- ON H.tenant_id = F.tenant_id
- AND H.warehouse_id = F.warehouse_id
- AND H.product_id = F.product_id
- AND H.product_balance_id = F.product_balance_id
- AND H.product_status = F.product_status
- WHERE A.session_id = pSessionId
- AND B.ref_doc_type_id = vSalesOrderDocTypeId
- AND E.doc_type_id = vReceiveGoodsDocTypeId
- AND F.session_id = pSessionId
- AND F.inventory_id = vReturnNoteId;
- /*
- * 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 = vReturnNoteId 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;
- INSERT INTO in_inventory_logistic(
- tenant_id, inventory_id, partner_ship_address_id,
- partner_ship_cp_id, cp_name, cp_phone1, cp_phone2, remark, due_date,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vClaimNoteId, B.partner_address_id,
- C.partner_cp_id, C.cp_name, C.phone1, C.phone2, A.remark, A.transfer_date,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A
- INNER JOIN m_partner_address B
- ON A.tenant_id = B.tenant_id AND A.partner_id = B.partner_id
- INNER JOIN m_partner_cp C
- ON A.tenant_id = C.tenant_id AND A.partner_id = C.partner_id
- WHERE A.inventory_id = vClaimNoteId
- AND B.active = vFlagYes
- AND B.flg_default = vFlagYes
- AND C.active = vFlagYes
- AND C.flg_responsibility = vFlagYes;
- SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
- FROM in_inventory A
- WHERE A.inventory_id = vClaimNoteId INTO result;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- 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) * -1,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = vClaimNoteId AND
- A.inventory_id = B.inventory_id AND
- C.warehouse_id = A.warehouse_from_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;
- INSERT INTO tt_in_po_balance_item
- (session_id, tenant_id, ou_id, doc_type_id,
- doc_no, doc_date, inventory_id, partner_id,
- inventory_item_id, po_id, receive_goods_id, receive_goods_item_id,
- qty_return, base_uom_id, remark,
- po_item_id, curr_code, price,
- flg_tax_amount, qty_po, qty_int_po,
- po_uom_id, tax_id, tax_percentage)
- SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
- A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
- B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
- SUM(B.qty_realization), B.base_uom_id, A.remark,
- D.po_item_id, D.curr_code, D.gross_price_po - D.discount_amount,
- D.flg_tax_amount, D.qty_po, D.qty_int,
- D.po_uom_id, D.tax_id, D.tax_percentage
- FROM in_inventory A, in_inventory_item B, in_balance_receive_goods_item C, pu_po_item D
- WHERE A.inventory_id = vClaimNoteId AND
- A.inventory_id = B.inventory_id AND
- B.ref_item_id = C.receive_goods_item_id AND
- C.po_item_id = D.po_item_id
- GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
- A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
- B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
- B.base_uom_id, D.po_item_id, D.curr_code, D.nett_price_po, D.qty_po, D.qty_int, D.po_uom_id, D.tax_id ;
- UPDATE pu_po_balance_item SET qty_return = pu_po_balance_item.qty_return + ((A.qty_return * A.qty_po) / A.qty_int_po), qty_int_return = pu_po_balance_item.qty_int_return + A.qty_return, update_datetime = vDatetime, update_user_id = vUserId
- FROM tt_in_po_balance_item A
- WHERE A.session_id = pSessionId AND
- pu_po_balance_item.po_item_id = A.po_item_id;
- 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, A.doc_type_id, A.inventory_id, A.inventory_item_id,
- ((A.qty_return * A.qty_po) / A.qty_int_po ), A.po_uom_id, A.qty_return, A.base_uom_id, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_in_po_balance_item A
- WHERE A.session_id = pSessionId;
- 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.po_id,
- A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.receive_goods_item_id, SUM((A.qty_return * A.qty_po) / A.qty_int_po), A.po_uom_id,
- A.curr_code, A.price,
- SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
- vFlagInvoice, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_in_po_balance_item A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.receive_goods_item_id, A.po_uom_id, A.curr_code, A.price;
- 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.po_id,
- A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id, D.flg_amount,
- A.tax_percentage, A.curr_code,
- SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
- f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage),
- vFlagInvoice, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_in_po_balance_item A, m_tax D
- WHERE A.session_id = pSessionId AND
- A.tax_id = D.tax_id
- GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id,
- D.flg_amount, A.tax_percentage, A.curr_code;
- UPDATE in_balance_receive_goods_item SET status_item = vStatusFinal
- FROM tt_in_po_balance_item A
- WHERE A.session_id = pSessionId AND
- in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id AND
- in_balance_receive_goods_item.qty_rcv -in_balance_receive_goods_item.qty_return <= 0;
- UPDATE in_balance_receive_goods_item SET status_item = vStatusRelease
- FROM tt_in_po_balance_item A
- WHERE A.session_id = pSessionId AND
- in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id AND
- in_balance_receive_goods_item.qty_rcv -in_balance_receive_goods_item.qty_return > 0;
- SELECT A.ou_from_id, B.ou_id INTO vOuId, vOuWarehouseId
- FROM in_inventory A
- INNER JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
- WHERE A.inventory_id = vClaimNoteId;
- IF (vOuId <> vOuWarehouseId) THEN
- SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
- vOuStructureJournalItem := result.ou_structure;
- ELSE
- vOuStructureJournalItem := ROW(-99, -99, -99);
- END IF;
- 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), 'DAILY', vDatetime, vUserId)
- FROM in_inventory A
- WHERE A.inventory_id = vClaimNoteId;
- 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, A.doc_type_id, 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 = vClaimNoteId;
- 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.inventory_item_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,
- f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax),
- C.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_STOCK', B.remark
- FROM tt_in_po_balance_item A, in_inventory_item B, pu_receive_goods C
- WHERE A.inventory_id = vClaimNoteId AND
- A.inventory_item_id = B.inventory_item_id AND
- A.receive_goods_id = C.receive_goods_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, A.inventory_item_id,
- A.partner_id, C.product_id, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, A.qty_return, A.base_uom_id,
- f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax),
- A.doc_date, vTypeRate,
- 1, 1, 'ACCR_AP', A.remark
- FROM tt_in_po_balance_item A, in_inventory_item C
- WHERE A.session_id = pSessionId AND
- A.inventory_item_id = C.inventory_item_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 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,
- (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
- 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 = 'ACCR_AP';
- INSERT INTO pu_po_balance_invoice_ext_invoice_doc_type
- (tenant_id, ou_id, po_id, ref_doc_type_id, ref_id,
- ref_item_id, invoice_id, invoice_doc_type_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id, A.inventory_id,
- A.receive_goods_item_id, vEmptyId, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_in_po_balance_item A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
- A.receive_goods_item_id, A.po_uom_id, A.curr_code, A.price;
- INSERT INTO pu_po_balance_invoice_tax_ext
- (tenant_id, ou_id, po_id, ref_doc_type_id, ref_id,
- ref_item_id, tax_id, invoice_id, invoice_doc_type_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id, A.inventory_id,
- A.receive_goods_item_id, A.tax_id, vEmptyId, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_in_po_balance_item A, m_tax D
- WHERE A.session_id = pSessionId AND
- A.tax_id = D.tax_id
- GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id,
- D.flg_amount, A.tax_percentage, A.curr_code;
- /**
- * GENERATE APPROVAL
- */
- -- Set user id from sysconfig
- SELECT f_get_value_system_config_by_param_code(pTenantId, vParamUserIdForGenerateDoc)::bigint INTO vUserIdForGenerateDoc;
- -- Set role id from sysconfig
- SELECT f_get_value_system_config_by_param_code(pTenantId, vParamRoleIdForGenerateDoc)::bigint INTO vRoleIdForGenerateDoc;
- -- Mendapatkan default approval flow ID
- SELECT awe_flow_id INTO vClaimNoteFlowId
- FROM awe_flow
- WHERE scheme = vClaimNoteScheme AND
- flg_validate = vFlagYes AND
- active = vFlagYes;
- -- Generate data awe_currdoc_status
- INSERT INTO awe_currdoc_status(
- req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
- remark, current_user_id, current_role_id, flg_user_role, label,
- data, flow_id, create_datetime, create_user_id, create_role_id,
- update_datetime, update_user_id, update_role_id, version)
- SELECT A.inventory_id||'_'||A.doc_no, A.tenant_id, vClaimNoteScheme, A.inventory_id, A.doc_no, A.doc_date, vWorkflowApproved,
- A.remark, vUserIdForGenerateDoc, vRoleIdForGenerateDoc, vFlgUserRole, 'CLAIM NOTE '||A.doc_no,
- '{}', vClaimNoteFlowId, vDatetime, vUserIdForGenerateDoc, vRoleIdForGenerateDoc,
- vDatetime, vUserIdForGenerateDoc, vRoleIdForGenerateDoc, 0
- FROM in_inventory A
- WHERE A.inventory_id = vClaimNoteId;
- -- Generate data awe_historydoc
- INSERT INTO awe_historydoc(
- tenant_id, req_id, doc_id, scheme, user_id, role_id,
- activity, previous_state, next_state, remark, next_user_id, next_role_id,
- flg_user_role, activity_datetime, version)
- SELECT pTenantId, B.req_id, A.inventory_id, vClaimNoteScheme, vUserIdForGenerateDoc, vRoleIdForGenerateDoc,
- 'AUTO GENERATE', '', '', A.remark, vEmptyId, vEmptyId,
- vFlgUserRole, vDateTime, 0
- FROM in_inventory A
- INNER JOIN awe_currdoc_status B ON B.doc_id = A.inventory_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date
- WHERE A.tenant_id = pTenantId
- AND A.inventory_id = vClaimNoteId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- DELETE FROM tt_in_po_balance_item WHERE session_id = pSessionId;
- -- Insert ke tabel mapping
- INSERT INTO in_ref_mapping_return_note_for_finance(
- so_id, po_id, return_note_id, return_note_doc_type_id, return_note_doc_no,
- claim_note_id, claim_note_doc_type_id, claim_note_doc_no,
- create_datetime, create_user_id, update_datetime, update_user_id, version)
- SELECT A.so_id, A.po_id, A.inventory_id, B.doc_type_id, B.doc_no,
- vClaimNoteId, vClaimNoteDocTypeId, vClaimNoteDocNo,
- vDatetime, vUserId, vDatetime, vUserId, 0
- FROM in_return_note_for_finance A
- INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
- WHERE A.inventory_id = vReturnNoteId
- AND A.flg_for_finance = vFlagYes;
- END IF;
- DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement