Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION in_submit_do_receipt_from_webclinic(character varying, bigint, bigint, bigint, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pDoReceiptId ALIAS FOR $3;
- pUserId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- vFlagInvoice character varying(1);
- vEmptyId bigint;
- vStatusRelease character varying(1);
- vStatusDraft character varying(1);
- vStatusFinal character varying(1);
- vStatusVoid character varying(1);
- vEmptyValue character varying(1);
- vProductStatus character varying(5);
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vProductCOA character varying(10);
- vSystemCOA character varying(10);
- vSoId bigint;
- vDoId bigint;
- vUnfinishedItem bigint;
- vParentOuId bigint;
- vJournalTrxId bigint;
- vJournalType character varying(20);
- vOuId bigint;
- vOuWarehouseId bigint;
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- vOuStructureJournalItem OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vDoReceiptDocTypeId bigint;
- vRoundingModeNonTax character varying(5);
- vDoDocTypeId bigint;
- vSoDocTypeId bigint;
- vSlsInvTempId bigint;
- vSlsInvTempDocTypeId bigint;
- vNo character varying(5);
- pUserIdForGenerateDoc BIGINT;
- vRoleIdForGenerateDoc BIGINT;
- vSchema CHARACTER VARYING := 'EA25';
- vYes character varying := 'Y';
- vWorkflowStatus CHARACTER VARYING := 'APPROVED';
- vParamUserIdForGenerateDoc CHARACTER VARYING := 'created.user.document.for.webclinic';
- vParamRoleIdForGenerateDoc CHARACTER VARYING := 'created.role.document.for.webclinic';
- vFlowId BIGINT;
- vFlgUserRole CHARACTER VARYING := 'R';
- vCurrentDateTime character varying;
- BEGIN
- vFlagInvoice := 'N';
- vEmptyId := -99;
- vStatusRelease := 'R';
- vStatusVoid := 'V';
- vStatusDraft := 'D';
- vStatusFinal := 'F';
- vEmptyValue := ' ';
- vProductStatus := 'GOOD';
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vProductCOA := 'PRODUCT';
- vSystemCOA := 'SYSTEM';
- vUnfinishedItem := 0;
- vNo := 'N';
- vDoReceiptDocTypeId = 525;
- vDoDocTypeId := 311;
- vSoDocTypeId := 301;
- vSlsInvTempDocTypeId := 361;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- -- Set user id from sysconfig
- SELECT f_get_value_system_config_by_param_code(pTenantId, vParamUserIdForGenerateDoc)::bigint INTO pUserIdForGenerateDoc;
- -- Set role id from sysconfig
- SELECT f_get_value_system_config_by_param_code(pTenantId, vParamRoleIdForGenerateDoc)::bigint INTO vRoleIdForGenerateDoc;
- SELECT f_datetime((extract(epoch from now())*1000)::bigint) INTO vCurrentDateTime;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- /*
- * 1. update status doc in_do_receipt
- * 2. add sl_log_so_balance_item
- * 3. add sl_so_balance_invoice
- * 4. add sl_so_balance_invoice_tax
- * 5. update sl_so_balance_invoice untuk Do Id
- * 6. update sl_so_balance_invoice_tax untuk Do Id
- * 7. void sales invoice temporer untuk So Id
- * 8. delete data sales invoice temporer dari fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
- * 9. add in_log_product_balance_stock
- * 10. update in_product_balance_stock
- * 11. update in_balance_do_item
- * 12. update sl_so_balance_item - status dan qty
- * 13. update status sl_so. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
- * 14. add gl_journal_trx
- * 15. add gl_journal_trx_item
- * 16. add gl_journal_trx_mapping
- *
- */
- SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
- FROM in_do_receipt A
- WHERE A.do_receipt_id = pDoReceiptId INTO result;
- vDoId := result.ref_id;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- SELECT ref_id INTO vSoId
- FROM sl_do
- WHERE do_id = vDoId;
- /*
- * update status doc in_do_receipt menjadi Release
- */
- UPDATE in_do_receipt SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
- WHERE do_receipt_id = pDoReceiptId;
- /*
- * buat data log sl_log_so_balance_item
- */
- INSERT INTO sl_log_so_balance_item
- (tenant_id, so_id, so_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, C.so_id, C.so_item_id, A.doc_type_id, A.do_receipt_id, B.do_receipt_item_id,
- B.qty_return * D.qty_so / D.qty_int, D.so_uom_id, B.qty_return, B.uom_id, B.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D
- WHERE A.do_receipt_id = pDoReceiptId AND
- A.do_receipt_id = B.do_receipt_id AND
- B.ref_id = C.do_item_id AND
- C.so_item_id = D.so_item_id AND
- B.qty_return > 0;
- /*
- * buat data sl_so_balance_Invoice
- * supaya dapat ditarik saat buat sales 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,
- do_receipt_item_id)
- SELECT A.tenant_id, A.ou_id, E.partner_bill_to_id, C.so_id,
- A.ref_doc_type_id, A.ref_id, F.doc_no, F.doc_date, B.ref_id, -1 * B.qty_return * D.qty_so / D.qty_int, D.so_uom_id,
- D.curr_code, f_get_price_before_tax_and_disc(D.gross_sell_price, ((G.regular_disc_amount / G.qty_dlv_so) + (G.promo_disc_amount / G.qty_dlv_so)), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, D.curr_code), vRoundingModeNonTax),
- -1 * f_get_amount_before_tax_and_disc((B.qty_return * D.qty_so / D.qty_int) * D.gross_sell_price, (G.regular_disc_amount + G.promo_disc_amount) * (B.qty_return * D.qty_so / D.qty_int) / G.qty_dlv_so, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, D.curr_code), vRoundingModeNonTax),
- vFlagInvoice, vEmptyId,
- -1 * G.regular_disc_amount * (B.qty_return * D.qty_so / D.qty_int) / G.qty_dlv_so, 0, 0, 0,
- 0, pDatetime, pUserId, pDatetime, pUserId,
- B.do_receipt_item_id
- FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D, sl_so E, sl_do F, sl_so_balance_invoice G
- WHERE A.do_receipt_id = pDoReceiptId AND
- A.do_receipt_id = B.do_receipt_id AND
- B.ref_id = C.do_item_id AND
- C.so_item_id = D.so_item_id AND
- D.so_id = E.so_id AND
- A.ref_id = F.do_id AND
- G.ref_id = F.do_id AND
- G.ref_item_id = C.do_item_id AND
- B.qty_return > 0 AND G.do_receipt_item_id = -99;
- /*
- * buat data sl_so_balance_invoice_tax
- * supaya dapat ditarik saat buat sales invoice
- */
- 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,
- do_receipt_item_id)
- SELECT A.tenant_id, A.ou_id, F.partner_bill_to_id, C.so_id,
- A.ref_doc_type_id, A.ref_id, B.ref_id, D.tax_id, E.flg_amount,
- D.tax_percentage, D.curr_code,
- -1 * f_get_amount_before_tax_and_disc((B.qty_return * D.qty_so / D.qty_int) * D.gross_sell_price, (B.qty_return * D.qty_so / D.qty_int) * D.discount_amount, D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, D.curr_code), vRoundingModeNonTax),
- -1 * f_tax_rounding(A.tenant_id, f_get_amount_before_tax((B.qty_return * D.qty_so / D.qty_int) * (D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, D.curr_code), vRoundingModeNonTax), D.tax_percentage),
- vFlagInvoice, vEmptyId,
- 0, pDatetime, pUserId, pDatetime, pUserId,
- B.do_receipt_item_id
- FROM in_do_receipt A, in_do_receipt_item B, in_balance_do_item C, sl_so_item D, m_tax E, sl_so F
- WHERE A.do_receipt_id = pDoReceiptId AND
- A.do_receipt_id = B.do_receipt_id AND
- B.ref_id = C.do_item_id AND
- C.so_item_id = D.so_item_id AND
- D.tax_id = E.tax_id AND
- D.so_id = F.so_id AND
- B.qty_return > 0;
- /*
- * update sl_so_balance_invoice, dan sl_so_balance_invoice_tax
- * yang sudah dibuat oleh sales invoice temporer
- */
- UPDATE sl_so_balance_invoice SET flg_invoice = vFlagInvoice, flg_invoice_temp = vFlagInvoice, invoice_id = vEmptyId
- WHERE ref_id = vDoId AND
- ref_doc_type_id = vDoDocTypeId;
- UPDATE sl_so_balance_invoice_tax SET flg_invoice = vFlagInvoice, invoice_id = vEmptyId
- WHERE ref_id = vDoId AND
- ref_doc_type_id = vDoDocTypeId;
- /*
- * void data sales invoice temporer yang menggunakan DO Item id yang sama dng Do Receipt :
- * 1. cari invoice temp id yang memiliki data DO Item yang sama dengan DO Receipt
- * 2. update status doc invoice temp sesuai id yang diperoleh di langkah ke 1
- */
- SELECT A.invoice_temp_id INTO vSlsInvTempId
- FROM sl_invoice_temp_item A, in_do_receipt_item B, in_do_receipt C, sl_invoice_temp D
- WHERE A.ref_doc_type_id = B.ref_doc_type_id AND
- A.ref_id = vDoId AND
- A.ref_item_id = B.ref_id AND
- B.do_receipt_id = C.do_receipt_id AND
- C.ref_id = A.ref_id AND
- B.do_receipt_id = pDoReceiptId AND
- A.invoice_temp_id = D.invoice_temp_id AND
- D.status_doc <> vStatusVoid;
- UPDATE sl_invoice_temp SET status_doc = vStatusVoid, update_datetime = pDatetime, update_user_id = pUserId
- WHERE invoice_temp_id = vSlsInvTempId;
- /*
- * advance invoice harus diupdate juga supaya bisa dipakai lagi
- */
- UPDATE sl_so_balance_advance_invoice B SET flg_invoice = vFlagInvoice, flg_invoice_temp = vFlagInvoice, invoice_id = vEmptyId
- WHERE B.so_id = vSoId
- AND EXISTS (
- SELECT 1 FROM sl_invoice_temp_advance A
- WHERE A.ref_id = B.ref_id
- AND A.ref_doc_type_id = B.ref_doc_type_id
- AND A.invoice_temp_id = vSlsInvTempId
- );
- /*
- * nomor faktur pajak boleh digunakan kembali untuk temp sales invoice yang di void gara2 DO Receipt
- *
- */
- UPDATE m_gen_tax_number A
- SET invoice_id = vEmptyId, invoice_doc_type_id = vEmptyId, invoice_doc_no = vEmptyValue, invoice_doc_date = vEmptyValue
- FROM sl_invoice_temp B
- WHERE A.invoice_doc_no = B.inv_doc_no
- AND A.invoice_doc_date = B.inv_doc_date
- AND A.invoice_doc_type_id = B.doc_type_id
- AND A.invoice_id = vSlsInvTempId
- AND B.invoice_temp_id = vSlsInvTempId;
- /*
- * delete data sales invoice temporer dari fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
- */
- DELETE FROM fi_invoice_tax_ar_balance A
- WHERE A.tenant_id = pTenantId AND
- EXISTS (
- SELECT 1 FROM fi_invoice_ar_balance B
- WHERE A.tenant_id = B.tenant_id
- AND A.invoice_ar_balance_id = B.invoice_ar_balance_id
- AND B.doc_type_id = vSlsInvTempDocTypeId
- AND B.invoice_ar_id = vSlsInvTempId
- );
- DELETE FROM fi_invoice_ar_balance A
- WHERE A.tenant_id = pTenantId AND
- A.doc_type_id = vSlsInvTempDocTypeId AND
- A.invoice_ar_id = vSlsInvTempId;
- /*
- * buat data log product balance stock
- * ref item id = do_receipt_id
- */
- 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, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
- C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id, SUM(C.qty_return),
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C, sl_do D, m_warehouse_ou E
- WHERE A.do_receipt_id = pDoReceiptId AND
- A.do_receipt_id = B.do_receipt_id AND
- B.do_receipt_item_id = C.do_receipt_item_id AND
- A.ref_id = D.do_id AND
- A.warehouse_id = E.warehouse_id
- GROUP BY A.tenant_id, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
- C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id;
- /*
- * update in_product_balance_stock
- * mod by Didit, 30 Des 2016
- * perbaiki jika ada 1 produk yang sama (product dan product balance) tapi ada lebih dari 1 record di tabel itemnya akan menjadi salah
- */
- WITH tt_in_product_balance_summary AS (
- SELECT C.product_id, C.tenant_id, A.warehouse_id, C.product_balance_id, C.product_status, SUM(C.qty_return) AS qty_return
- FROM in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C
- WHERE A.do_receipt_id = pDoReceiptId
- AND A.do_receipt_id = B.do_receipt_id
- AND B.do_receipt_item_id = C.do_receipt_item_id
- AND C.qty_return > 0
- GROUP BY C.product_id, C.tenant_id, A.warehouse_id, C.product_balance_id, C.product_status
- )
- UPDATE in_product_balance_stock SET qty = qty + A.qty_return, update_datetime = pDatetime, update_user_id = pUserId, version = version + 1
- FROM tt_in_product_balance_summary A
- WHERE 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 = A.product_status;
- /*
- * update data balance do item sehubungan dengan product yang dikembalian/tidak diterima oleh customer,
- * sehingga saat akan membuat return note, hanya barang yang memang diterima oleh customer
- */
- UPDATE in_balance_do_item SET qty_dlv_int = qty_dlv_int - A.qty_return,
- qty_dlv = in_balance_do_item.qty_dlv - (A.qty_return * in_balance_do_item.qty_dlv / in_balance_do_item.qty_dlv_int),
- status_item = vStatusRelease, update_datetime = pDatetime, update_user_id = pUserId
- FROM in_do_receipt_item A
- WHERE A.do_receipt_id = pDoReceiptId AND
- in_balance_do_item.do_item_id = A.ref_id AND
- A.qty_return > 0;
- UPDATE in_balance_do_item
- SET status_item = vStatusRelease,
- update_datetime = pDatetime,
- update_user_id = pUserId
- FROM in_do_receipt A
- WHERE A.do_receipt_id = pDoReceiptId AND
- in_balance_do_item.do_id = A.ref_id AND
- A.ref_doc_type_id = vDoDocTypeId;
- /*
- * update status SO seperti semula
- */
- UPDATE sl_so
- SET status_doc = flg_old_status,
- flg_old_status = vEmptyValue,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- version = version + 1
- WHERE so_id = vSoId;
- /*
- * update flg invoice so balance inv
- */
- UPDATE sl_so_balance_invoice
- SET flg_invoice = vNo,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- version = version + 1
- WHERE so_id = vSoId
- AND ref_id = vDoId;
- /*
- * update status so balance item
- */
- UPDATE sl_so_balance_item B
- SET status_item = B.flg_old_status,
- flg_old_status = vEmptyValue,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- version = B.version + 1
- FROM sl_so A
- INNER JOIN sl_so_item C ON A.so_id = C.so_id
- INNER JOIN sl_do_item D ON D.ref_id = C.so_item_id
- WHERE B.so_item_id = C.so_item_id
- AND A.so_id = vSoId
- AND D.do_id = vDoId;
- /*
- * status item SO akan berubah menjadi Release, karena ada nya barang yang dikembalikan oleh customer
- */
- UPDATE sl_so_balance_item SET qty_dlv = sl_so_balance_item.qty_dlv - (A.qty_return * sl_so_balance_item.qty_so / sl_so_balance_item.qty_so_int),
- qty_dlv_int = sl_so_balance_item.qty_dlv_int - A.qty_return,
- status_item = vStatusRelease
- FROM in_do_receipt_item A, in_balance_do_item B
- WHERE A.do_receipt_id = pDoReceiptId AND
- A.ref_id = B.do_item_id AND
- sl_so_balance_item.so_item_id = B.so_item_id AND
- A.qty_return > 0;
- UPDATE sl_so SET status_doc = vStatusRelease
- WHERE so_id = vSoId;
- /*
- * @author TKP, 9 Jun 2016
- * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
- * jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id didapat pada f_get_ou_bu_structure;
- */
- SELECT A.ou_id, B.ou_id INTO vOuId, vOuWarehouseId
- FROM in_do_receipt A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- WHERE A.do_receipt_id = pDoReceiptId;
- 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;
- /*
- * 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_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', pDatetime, pUserId)
- FROM in_do_receipt A
- WHERE A.do_receipt_id = pDoReceiptId;
- 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.do_receipt_id, A.doc_no, A.doc_date,
- (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, B.partner_ship_to_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.doc_date, C.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM in_do_receipt A, sl_do B, sl_so C
- WHERE A.do_receipt_id = pDoReceiptId AND
- A.ref_id = B.do_id AND
- B.ref_id = C.so_id;
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.do_receipt_item_id,
- C.partner_ship_to_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_return, B.uom_id,
- 0, A.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_STOCK', B.remark
- FROM in_do_receipt A, in_do_receipt_item B, sl_do C
- WHERE A.do_receipt_id = pDoReceiptId AND
- A.do_receipt_id = B.do_receipt_id AND
- A.ref_id = C.do_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, pDatetime, pUserId, pDatetime, pUserId,
- (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId;
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- vEmptyId, vEmptyId,
- 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, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
- -- Mendapatkan default approval flow ID so
- SELECT awe_flow_id INTO vFlowId
- FROM awe_flow
- WHERE scheme = vSchema AND
- flg_validate = vYes AND
- active = vYes;
- -- Generate data awe_currdoc_status so
- 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.do_receipt_id||'_'||A.doc_no, A.tenant_id, vSchema, A.do_receipt_id, A.doc_no, A.doc_date, vWorkflowStatus,
- A.remark, pUserIdForGenerateDoc, vRoleIdForGenerateDoc, vFlgUserRole, 'Delivery Order Receipt '||A.doc_no,
- '{}', vFlowId, vCurrentDateTime, pUserIdForGenerateDoc, vRoleIdForGenerateDoc,
- vCurrentDateTime, pUserIdForGenerateDoc, vRoleIdForGenerateDoc, 0
- FROM in_do_receipt A
- WHERE A.do_receipt_id = pDoReceiptId;
- -- Generate data awe_historydoc so
- 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.do_receipt_id, vSchema, pUserIdForGenerateDoc, vRoleIdForGenerateDoc,
- 'AUTO GENERATE', '', '', A.remark, vEmptyId, vEmptyId,
- vFlgUserRole, vCurrentDateTime, 0
- FROM in_do_receipt A
- INNER JOIN awe_currdoc_status B ON B.doc_id = A.do_receipt_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date
- WHERE A.do_receipt_id = pDoReceiptId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement