Function Generate Sales Invoice

 CREATE OR REPLACE FUNCTION f_generate_jurnal_sales_invoice(character varying,bigint,character varying,character varying,
    bigint,character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pDateForm           ALIAS FOR $3;
    pDateTo             ALIAS FOR $4;
    pUserId             ALIAS FOR $5;
    pDatetime           ALIAS FOR $6;

    -- For Journal
    vEmptyId                bigint;
    vSignDebt               character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(5);
    vStatusDraft            character varying(1);


    vProcessId              bigint;
    vSalesInvoiceId         bigint;
    vSalesInvoiceTempId     bigint;
    vInvoiceArBalanceId     bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vFlagInvoice            character varying(1);
    vFlagPayment            character varying(1);
    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vStatusDraft            character varying(1);
    vStatusFinal            character varying(1);
    vEmptyValue             character varying(1);
    vEmptyString            character varying;
    vJournalType            character varying(20);
    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vActivityCOA            character varying(10);
    vSystemCOA              character varying(10);
    vTaxCOA                 character varying(10);
    vParentOuId             bigint;
    vJournalTrxId           bigint;
    vOuStructureDo          OU_BU_STRUCTURE%ROWTYPE;

    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    result                  RECORD;
    vFakturPajakKeluaran    bigint;
    vInvArBalanceId         bigint;
    vInvTaxArBalanceId      bigint;
    vSalesInvDate           character varying(8);
    vSlsInvTempDocTypeId    bigint;
    vDoDocTypeId            bigint;
    vRegularDiscount        bigint;
    vPromoDiscount          bigint;
    vOuId                   bigint;
    vOuWarehouseId          bigint;
    vCoaIdGIT               bigint;
    vProductCOA             character varying(10);
    vRoundingModeNonTax     character varying(5);
    vDiffAdjRegularDiscAmount       numeric;
    vDiffAdjPromoDiscAmount         numeric;
    vMaxItemAmount          numeric;
    vMaxAdjPromoItemAmount  numeric;
    vInProgress                 character varying(1);
    vYes                        character varying(1);
    vNo                         character varying(1);
    vNol                        numeric;
    vReleased                   character varying(1);
    vFlgGunggung                character varying(1);

    vJointDppPpn            character varying(1);

BEGIN

   vFlagInvoice := 'Y';
    vFlagPayment := 'N';
    vEmptyId := -99;
    vStatusRelease := 'R';
    vStatusDraft := 'D';
    vStatusFinal := 'F';
    vEmptyValue := ' ';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vActivityCOA := 'ACTIVITY';
    vSystemCOA := 'SYSTEM';
    vProductCOA := 'PRODUCT';
    vTaxCOA := 'TAX';
    vFakturPajakKeluaran := 281;
    vSlsInvTempDocTypeId := 361;
    vDoDocTypeId := 311;
    vInProgress := 'I';
    vYes := 'Y';
    vNo := 'N';
    vNol := 0;
    vReleased := 'R';
    vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');
    vEmptyString := '';
    vCoaIdGIT:=f_get_system_coa_by_group_coa(pTenantId, 'PersediaanInTransit');

--
--  SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, ref_inv_temp_id as salesInvTempId, A.doc_date
--  FROM sl_invoice A
--  WHERE A.invoice_id = vSalesInvoiceId INTO result;
--
--  vOuStructure := result.ou;
--  vDocJournal := result.doc;
--
--  SELECT A.ou_id, B.ou_id INTO vOuId, vOuWarehouseId
--  FROM sl_do A
--  INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
--  WHERE A.do_id = vDoId;
--
--  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;
--
--  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), 'MONTHLY', pDatetime, pUserId)
--  FROM sl_do A
--  WHERE A.do_id = vDoId;

    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_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 sl_invoice A
    WHERE A.doc_no BETWEEN pDateForm AND pDateTo;


    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_item_id,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), A.curr_code, 0, vEmptyId,
        B.ref_item_amount, A.doc_date, vTypeRate,
        1, 1, 'SALES_INCOME', B.remark
    FROM sl_invoice A, sl_invoice_item B
    WHERE A.doc_no BETWEEN pDateForm AND pDateTo
        AND A.invoice_id = B.invoice_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
    FROM(
        SELECT A.tenant_id, 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,
            vOuStructure.ou_branch_id AS ou_branch_id, vOuStructure.ou_sub_bu_id AS ou_sub_bu_id
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            journal_desc IN ('SALES_INCOME','SALES_COST','VAT_OUT','AR_ADVANCE')
        UNION ALL
        -- tambahan jounal cogs
        SELECT A.tenant_id, vJournalTrxId,
            vEmptyId, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebit, 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.doc_date, vTypeRate,
            1, 1, 'COGS', vEmptyString,
            vOuStructureDo.ou_branch_id AS ou_branch_id, vOuStructureDo.ou_sub_bu_id AS ou_sub_bu_id
        FROM sl_invoice A
        WHERE A.doc_no BETWEEN pDateForm AND pDateTo
    ) A;

    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,
    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
    FROM (
        SELECT A.tenant_id, 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,
            vOuStructure.ou_branch_id AS ou_branch_id, vOuStructure.ou_sub_bu_id AS ou_sub_bu_id
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            journal_desc IN ('AR')
        UNION ALL
        SELECT h.tenant_id, vJournalTrxId,
            g.ref_doc_type_id, g.ref_item_id,
            h.partner_id, C.product_id, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vProductCOA, vEmptyId,
            vCoaIdGIT, g.curr_code, SUM(g.qty_dlv_so), g.so_uom_id,
            0, h.doc_date, vTypeRate,
            1, 1, 'PRODUCT_STOCK_IN_TRANSIT', C.remark,
            vOuStructureDo.ou_branch_id AS ou_branch_id, vOuStructureDo.ou_sub_bu_id AS ou_sub_bu_id
        FROM sl_invoice_item a
        INNER JOIN sl_invoice h ON a.tenant_id = h.tenant_id
            AND a.invoice_id = h.invoice_id
        INNER JOIN sl_do b ON a.tenant_id = b.tenant_id
            AND a.ref_id = b.do_id
        INNER JOIN sl_do_item c ON a.tenant_id = c.tenant_id AND a.ref_item_id = c.do_item_id
        INNER JOIN sl_so_balance_invoice g ON a.tenant_id = g.tenant_id
            AND g.ou_id = h.ou_id
            AND g.so_id = b.ref_id
            AND g.ref_item_id = a.ref_item_id
            AND g.ref_doc_type_id = 311
            AND g.ref_id = b.do_id
            AND a.do_receipt_item_id = g.do_receipt_item_id
        WHERE A.doc_no BETWEEN pDateForm AND pDateTo
        GROUP BY h.tenant_id, g.ref_doc_type_id, g.ref_item_id,
                h.partner_id, C.product_id, g.curr_code,
                g.so_uom_id, h.doc_date,C.remark
    ) A;


    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,
    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
    FROM (
        SELECT A.tenant_id, 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,
            vOuStructure.ou_branch_id AS ou_branch_id, vOuStructure.ou_sub_bu_id AS ou_sub_bu_id
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            journal_desc IN ('AR')
        UNION ALL
        SELECT h.tenant_id, vJournalTrxId,
            g.ref_doc_type_id, g.ref_item_id,
            h.partner_id, C.product_id, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vProductCOA, vEmptyId,
            vCoaIdGIT, g.curr_code, SUM(g.qty_dlv_so), g.so_uom_id,
            0, h.doc_date, vTypeRate,
            1, 1, 'PRODUCT_STOCK_IN_TRANSIT', C.remark,
            vOuStructureDo.ou_branch_id AS ou_branch_id, vOuStructureDo.ou_sub_bu_id AS ou_sub_bu_id
        FROM sl_invoice_item a
        INNER JOIN sl_invoice h ON a.tenant_id = h.tenant_id
            AND a.invoice_id = h.invoice_id
        INNER JOIN sl_do b ON a.tenant_id = b.tenant_id
            AND a.ref_id = b.do_id
        INNER JOIN sl_do_item c ON a.tenant_id = c.tenant_id AND a.ref_item_id = c.do_item_id
        INNER JOIN sl_so_balance_invoice g ON a.tenant_id = g.tenant_id
            AND g.ou_id = h.ou_id
            AND g.so_id = b.ref_id
            AND g.ref_item_id = a.ref_item_id
            AND g.ref_doc_type_id = 311
            AND g.ref_id = b.do_id
            AND a.do_receipt_item_id = g.do_receipt_item_id
        WHERE A.doc_no BETWEEN pDateForm AND pDateTo
        GROUP BY h.tenant_id, g.ref_doc_type_id, g.ref_item_id,
                h.partner_id, C.product_id, g.curr_code,
                g.so_uom_id, h.doc_date,C.remark
    ) A;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /