Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION f_generate_jurnal_delivery_order(character varying,bigint,bigint,bigint,character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pInvoiceArId ALIAS FOR $3;
- pUserId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- -- For Journal
- vEmptyId bigint;
- vSignDebt character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(5);
- vActivityCOA character varying(10);
- vSystemCOA character varying(10);
- vTaxCOA character varying(10);
- vEmptyValue character varying(1);
- vFlgNo character varying(1);
- vEmptyString character varying;
- vStatusDraft character varying(1);
- vCreditNoteDoc bigint;
- vDebtNoteDoc bigint;
- vDownPaymentDoc bigint;
- vFakturPajakKeluaran bigint;
- vNotaReturPajakKeluaran bigint;
- vAdvanceInvArDoc bigint;
- vDocTypeId bigint;
- vJournalTrxId bigint;
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- BEGIN
- vCreditNoteDoc := 251;
- vDebtNoteDoc := 241;
- vDownPaymentDoc := 242;
- vAdvanceInvArDoc := 243;
- vFakturPajakKeluaran := 281;
- vNotaReturPajakKeluaran := 282;
- vEmptyId := -99;
- vEmptyValue := ' ';
- vEmptyString := '';
- vStatusDraft := 'D';
- vSignDebt := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vActivityCOA := 'ACTIVITY';
- vSystemCOA := 'SYSTEM';
- vTaxCOA := 'TAX';
- vFlgNo := 'N';
- SELECT A.doc_type_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
- FROM fi_invoice_ar A
- WHERE A.invoice_ar_id = pInvoiceArId INTO result;
- vDocTypeId := result.doc_type_id;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- /*
- * 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), 'DAILY', pDatetime, pUserId)
- FROM fi_invoice_ar A
- WHERE A.invoice_ar_id = pInvoiceArId;
- 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.invoice_ar_id, A.doc_no, A.doc_date,
- (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM fi_invoice_ar A
- WHERE A.invoice_ar_id = pInvoiceArId;
- IF vDocTypeId = vCreditNoteDoc THEN
- -- a1. Nota Kredit AR
- -- D m_activity_gl ACTIVITY AR_COST_DEBIT (amount > 0)
- -- D PajakPertambahanNilai - m_tax TAX VAT_OUT gov tax amount
- -- K m_activity_gl ACTIVITY AR_COST_CREDIT (amount < 0)
- -- D PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_COST_CREDIT
- -- K PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_COST_DEBIT, VAT_OUT
- -- D m_activity_gl ACTIVITY AR_COST_DEBIT (amount > 0)
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_cost_id,
- A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
- -- NK, 30 Agustus 2014 - add segment_id
- B.segment_id, vSignDebt, vActivityCOA, B.activity_gl_id,
- D.coa_id, A.curr_code, 0, vEmptyId,
- B.add_amount, A.doc_date, vTypeRate,
- 1, 1, 'AR_COST_DEBIT', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
- WHERE A.invoice_ar_id = pInvoiceArId AND
- B.add_amount > 0;
- -- K m_activity_gl ACTIVITY AR_COST_CREDIT (amount < 0)
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_cost_id,
- A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
- -- NK, 30 Agustus 2014 - add segment_id
- B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
- D.coa_id, A.curr_code, 0, vEmptyId,
- -1 * B.add_amount, A.doc_date, vTypeRate,
- 1, 1, 'AR_COST_CREDIT', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
- WHERE A.invoice_ar_id = pInvoiceArId AND
- B.add_amount < 0;
- -- D PajakPertambahanNilai - m_tax TAX VAT_OUT gov tax amount
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_tax_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebt, vTaxCOA, vEmptyId,
- D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
- B.gov_tax_amount, A.doc_date, vTypeRate,
- 1, 1, 'VAT_OUT', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_tax B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_tax D ON B.tax_id = D.tax_id
- WHERE A.invoice_ar_id = pInvoiceArId;
- -- K PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_COST_DEBIT, VAT_IN
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT A.session_id, A.tenant_id, A.journal_trx_id, 1,
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
- SUM(A.amount), A.journal_date, vTypeRate,
- 1, 1, 'AR', vEmptyValue
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc IN ('AR_COST_DEBIT','VAT_OUT')
- GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
- A.partner_id, A.curr_code, A.journal_date;
- -- D PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_COST_CREDIT
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT A.session_id, A.tenant_id, A.journal_trx_id, 1,
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
- SUM(A.amount), A.journal_date, vTypeRate,
- 1, 1, 'AR', vEmptyValue
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc IN ('AR_COST_CREDIT')
- GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
- A.partner_id, A.curr_code, A.journal_date;
- -- END of insert tt_journal_trx_item for Nota Kredit
- ELSE IF vDocTypeId = vDownPaymentDoc THEN
- -- K m_activity_gl SYSTEM AR_ADVANCE
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_cost_id,
- A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
- -- NK, 30 Agustus 2014 - add segment_id
- B.segment_id, vSignCredit, vSystemCOA, B.activity_gl_id,
- D.coa_id, A.curr_code, 0, vEmptyId,
- B.add_amount, A.doc_date, vTypeRate,
- 1, 1, 'AR_ADVANCE', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
- WHERE A.invoice_ar_id = pInvoiceArId;
- -- D PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_ADVANCE
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT A.session_id, A.tenant_id, A.journal_trx_id, 1,
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
- SUM(A.amount), A.journal_date, vTypeRate,
- 1, 1, 'AR', vEmptyValue
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc IN ('AR_ADVANCE')
- GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
- A.partner_id, A.curr_code, A.journal_date;
- -- END of insert tt_journal_trx_item for DownPayment
- ELSE IF vDocTypeId = vAdvanceInvArDoc THEN
- /*
- INSERT INTO sl_so_balance_advance_invoice
- (tenant_id, ou_id, partner_id, so_id, ref_doc_type_id, ref_id,
- advance_curr_code, advance_amount, flg_invoice, flg_invoice_temp, invoice_id,
- version, create_datetime, create_user_id, update_datetime, update_user_id,
- ref_doc_no, ref_doc_date, tax_id, tax_percentage, tax_amount )
- SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id, A.doc_type_id, A.invoice_ar_id,
- A.curr_code, B.base_amount, 'N', 'N', vEmptyId,
- 1, A.update_datetime, A.update_user_id, A.update_datetime, A.update_user_id,
- A.doc_no, A.doc_date, B.tax_id, B.tax_percentage, B.tax_amount
- FROM fi_invoice_ar A, fi_invoice_ar_tax B
- WHERE A.invoice_ar_id = pInvoiceArId AND A.invoice_ar_id = B.invoice_ar_id;
- */
- -- K m_activity_gl SYSTEM AR_ADVANCE
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_cost_id,
- A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
- B.segment_id, vSignCredit, vSystemCOA, B.activity_gl_id,
- D.coa_id, A.curr_code, 0, vEmptyId,
- B.add_amount, A.doc_date, vTypeRate,
- 1, 1, 'AR_ADVANCE', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
- WHERE A.invoice_ar_id = pInvoiceArId;
- -- K PajakPertambahanNilai - m_tax TAX VAT_OUT gov tax amount
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_tax_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
- D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
- B.gov_tax_amount, A.doc_date, vTypeRate,
- 1, 1, 'VAT_OUT', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_tax B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_tax D ON B.tax_id = D.tax_id
- WHERE A.invoice_ar_id = pInvoiceArId;
- -- D PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_ADVANCE
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT A.session_id, A.tenant_id, A.journal_trx_id, 1,
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
- SUM(A.amount), A.journal_date, vTypeRate,
- 1, 1, 'AR', vEmptyValue
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc IN ('AR_ADVANCE', 'VAT_OUT')
- GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
- A.partner_id, A.curr_code, A.journal_date;
- -- END of insert tt_journal_trx_item for DownPayment
- ELSE
- -- b. Nota Debet
- -- K m_activity_gl ACTIVITY AR_COST_CREDIT (amount > 0)
- -- K PajakPertambahanNilai - m_tax TAX VAT_OUT gov tax amount
- -- D m_activity_gl ACTIVITY AR_COST_DEBIT (amount < 0)
- -- K PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_COST_DEBIT
- -- D PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_COST_CREDIT, VAT_OUT
- -- K m_activity_gl ACTIVITY AR_COST_CREDIT (amount > 0)
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_cost_id,
- A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
- -- NK, 30 Agustus 2014 - add segment_id
- B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
- D.coa_id, A.curr_code, 0, vEmptyId,
- B.add_amount, A.doc_date, vTypeRate,
- 1, 1, 'AR_COST_CREDIT', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
- WHERE A.invoice_ar_id = pInvoiceArId AND
- B.add_amount > 0;
- -- D m_activity_gl ACTIVITY AR_COST_DEBIT (amount < 0)
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_cost_id,
- A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
- -- NK, 30 Agustus 2014 - add segment_id
- B.segment_id, vSignDebt, vActivityCOA, B.activity_gl_id,
- D.coa_id, A.curr_code, 0, vEmptyId,
- -1 * B.add_amount, A.doc_date, vTypeRate,
- 1, 1, 'AR_COST_DEBIT', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
- WHERE A.invoice_ar_id = pInvoiceArId AND
- B.add_amount < 0;
- -- K PajakPertambahanNilai - m_tax TAX VAT_OUT gov tax amount
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.invoice_ar_tax_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
- D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
- B.gov_tax_amount, A.doc_date, vTypeRate,
- 1, 1, 'VAT_OUT', B.remark
- FROM fi_invoice_ar A
- INNER JOIN fi_invoice_ar_tax B ON A.invoice_ar_id = B.invoice_ar_id
- INNER JOIN m_tax D ON B.tax_id = D.tax_id
- WHERE A.invoice_ar_id = pInvoiceArId;
- -- D PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_COST_CREDIT, VAT_OUT
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT A.session_id, A.tenant_id, A.journal_trx_id, 1,
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
- SUM(A.amount), A.journal_date, vTypeRate,
- 1, 1, 'AR', vEmptyValue
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc IN ('AR_COST_CREDIT','VAT_OUT')
- GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
- A.partner_id, A.curr_code, A.journal_date;
- -- K PiutangDagang - m_type_partner SYSTEM AR jumlah nilai dari AR_COST_DEBIT
- INSERT INTO tt_journal_trx_item
- ( session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT A.session_id, A.tenant_id, A.journal_trx_id, 1,
- vEmptyId, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
- SUM(A.amount), A.journal_date, vTypeRate,
- 1, 1, 'AR', vEmptyValue
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc IN ('AR_COST_DEBIT')
- GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
- A.partner_id, A.curr_code, A.journal_date;
- -- END of insert tt_journal_trx_item for Nota Debet
- END IF;
- END IF;
- END IF;
- -- d. Summary ke gl_journal_trx_item dan gl_journal_trx_mapping
- INSERT INTO gl_journal_trx_item
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- journal_desc IN ('AR_COST_CREDIT','AR_COST_DEBIT', 'VAT_OUT', 'AR_ADVANCE');
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- journal_desc IN ('AR');
- 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