simple_calc

CREATE OR REPLACE FUNCTION sl_automatic_create_sales_invoice_from_temp_sales_invoice(
    character varying,
    bigint,
    bigint,
    character varying,
    bigint,
    bigint)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId              alias for $1;
    pTenantId               alias for $2;
    pUserId                 alias for $3;
    pDatetime               alias for $4;
    pSoId                   alias for $5;
    pDoId                   alias for $6;

    vParamKey               character varying(30) := 'salesInvoiceId';
    vProcessName            character varying(30) := 'sl_submit_sales_invoice';
    vEmptyId                bigint := -99;
    vEmptyString            character varying(1) :='';
    vInvoiceId              bigint;
    vDocTypeSalesInvoice    bigint:= 321;
    vDocTypeSalesOrder      bigint:= 301;
    vFlowInvoiceId          bigint;
    vDueDate                bigint := 90;
    vSchemeSalesInvoice     character varying:= 'FC01';
    vFlagYes                character varying:= 'Y';
    vWorkFlowStatusApproved character varying:= 'APPROVED';
    vStatusDocApproved      character varying:= 'R';
    vInvoiceTempId          bigint;
    vReceiptDate                character varying(8);
    vStatusRejectedAll  character varying:= 'DITOLAK_SEMUA';
  vStatusFailOnDelivery character varying:='FAIL_ON_DELIVERY';

BEGIN

    SELECT NEXTVAL('sl_invoice_seq') INTO vInvoiceId;

    SELECT invoice_temp_id INTO vInvoiceTempId
    FROM sl_invoice_temp
    WHERE ref_id = pSoId
    AND ref_doc_type_id = vDocTypeSalesOrder
    AND status_doc = vStatusDocApproved;

    SELECT A.receipt_date INTO vReceiptDate
    FROM in_finalisasi_pengiriman_item A
    INNER JOIN sl_do B ON A.ref_id = B.do_id AND A.ref_doc_type_id = B.doc_type_id
    WHERE A.receipt_status <> vStatusFailOnDelivery AND
    A.receipt_status <> vStatusRejectedAll AND
    B.do_id = pDoId
    UNION
    SELECT A.doc_date
    FROM in_do_receipt A
    INNER JOIN sl_do B ON A.ref_id = B.do_id AND A.ref_doc_type_id = B.doc_type_id
    AND B.do_id = pDoId;

    SELECT TO_CHAR(to_date(vReceiptDate, 'YYYYMMDD') + B.term_of_payment * interval '1 days ', 'YYYYMMDD') INTO vDueDate
    FROM sl_do A
    INNER JOIN sl_so B ON A.ref_id = B.so_id
    WHERE A.do_id = pDoId;

    --insert ke dalam sl invoice (UBBAH
    INSERT INTO sl_invoice(
            invoice_id,tenant_id, doc_type_id, doc_no, doc_date, ou_id,
            ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id,
            ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
            advance_amount, tax_amount, add_amount, total_amount, status_doc,
            workflow_status, version, create_datetime, create_user_id, update_datetime,
            update_user_id, ref_inv_temp_id, discount_amount)
    SELECT  vInvoiceId, pTenantId, vDocTypeSalesInvoice, A.inv_doc_no, A.inv_doc_date, A.ou_id,
            A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
            A.ou_legal_id, A.vDueDate, A.salesman_id, A.curr_code, ROUND(A.gross_amount,0),
            A.advance_amount, ROUND(A.tax_amount,0), A.add_amount, ROUND(A.total_amount,0), A.status_doc,
            A.workflow_status, A.version, pDatetime, pUserId, pDatetime,
            pUserId, A.invoice_temp_id, A.discount_amount
    FROM    sl_invoice_temp A
    WHERE   A.invoice_temp_id = vInvoiceTempId;

    --insert ke dalam sl invoice item
    INSERT INTO sl_invoice_item(
            tenant_id, invoice_id, line_no, ref_doc_type_id,
            ref_id, ref_item_id, ref_item_amount, remark, version, create_datetime,
            create_user_id, update_datetime, update_user_id, do_receipt_item_id)
    SELECT  pTenantId, vInvoiceId, ROW_NUMBER() OVER (PARTITION BY vInvoiceId), A.ref_doc_type_id,
            A.ref_id, A.ref_item_id, ROUND(A.ref_item_amount,0), A.remark, A.version, pDatetime,
            pUserId, pDatetime, pUserId, A.do_receipt_item_id
    FROM    sl_invoice_temp_item A
    WHERE   A.ref_id = pDoId
    AND     A.invoice_temp_id = vInvoiceTempId;

    --insert ke dalam sl invoice tax
    INSERT INTO sl_invoice_tax(
            tenant_id, invoice_id, tax_id, flg_amount, tax_percentage,
            base_amount, tax_amount, tax_no, tax_date, tax_curr_code, gov_tax_amount,
            remark, version, create_datetime, create_user_id, update_datetime,
            update_user_id, advance_amount, total_tax_disc_amount)
    SELECT  pTenantId, vInvoiceId, A.tax_id, A.flg_amount, A.tax_percentage,
            ROUND(A.base_amount,0), ROUND(A.tax_amount,0), A.tax_no, A.tax_date, A.tax_curr_code, ROUND(A.gov_tax_amount,0),
            A.remark, A.version, pDatetime, pUserId, pDatetime,
            pUserId, A.advance_amount, ROUND(A.total_tax_disc_amount,0)
    FROM    sl_invoice_temp_tax A
    WHERE   A.invoice_temp_id = vInvoiceTempId;

    --perform generate process message for submit doc sales invoice
    --insert process message
    PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessName, vInvoiceId ||'_'||A.doc_no,
                pDatetime, vParamKey, vInvoiceId::character varying, pUserId)
    FROM sl_invoice A
    WHERE A.invoice_id = vInvoiceId;

    -- Mendapatkan default approval flow ID yang dipakai dari sysconfig untuk DO
    SELECT awe_flow_id INTO vFlowInvoiceId
    FROM awe_flow
    WHERE scheme = vSchemeSalesInvoice
    AND flg_validate = vFlagYes
    AND active = vFlagYes
    AND tenant_id = pTenantId;

    --insert awe_currdoc_status SI
    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  vInvoiceId||'_'||A.doc_no, A.tenant_id, vSchemeSalesInvoice, vInvoiceId, A.doc_no, A.doc_date, vWorkFlowStatusApproved,
            A.remark, A.create_user_id, vEmptyId, 'R', 'SALES INVOICE'||A.doc_no,
            '{}', vFlowInvoiceId, pDatetime, pUserId, vEmptyId,
            pDatetime, pUserId, vEmptyId, 0
    FROM sl_invoice A
    WHERE A.invoice_id = vInvoiceId;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /