CUT-OFF SPOORING TAMBAH PARTNER

BEGIN

    DELETE FROM tt_fi_summary_monthly_ap

    ALTER TABLE tt_fi_summary_monthly_ap ADD COLUMN partner_id bigint

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', C.year_month_date, A.tenant_id,
        10, A.doc_type_id, A.invoice_ap_balance_id, A.curr_code,
        A.amount, A.amount, 0, A.partner_id
    FROM fi_invoice_ap_balance A, m_ou_structure B, dt_date C
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712';

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', C.year_month_date, A.tenant_id,
        10, A.doc_type_id, A.invoice_tax_ap_balance_id, A.tax_curr_code,
        A.gov_tax_amount, A.gov_tax_amount, 0, A.partner_id
    FROM fi_invoice_tax_ap_balance A, m_ou_structure B, dt_date C, fi_invoice_ap_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.invoice_ap_balance_id = D.invoice_ap_balance_id AND
        D.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712';

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', C.year_month_date, A.tenant_id,
        10, D.credit_doc_type_id, D.credit_id, D.credit_curr_code,
        0, 0, SUM(D.credit_amount), A.partner_id
    FROM fi_payment_order A, m_ou_structure B, dt_date C, fi_payment_order_alloc_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.payment_order_id = D.payment_order_id AND
        D.credit_id <> -99 AND
        D.flg_alloc <> 'C'
    GROUP BY A.tenant_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code, C.year_month_date, A.partner_id;

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', E.year_month_date, A.tenant_id,
        10, D.credit_doc_type_id, D.credit_id, D.credit_curr_code,
        0, 0, SUM(D.credit_amount), A.partner_id
    FROM fi_payment_order A, m_ou_structure B, fi_payment_order_alloc_balance D, dt_date E
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.payment_order_id = D.payment_order_id AND
        D.credit_id <> -99 AND
        D.flg_alloc = 'C' AND
        D.ref_alloc_id <> -99 AND
        D.ref_doc_date = E.string_date AND
        E.year_month_date between '201701' and '201712'
    GROUP BY A.tenant_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code, E.year_month_date, A.partner_id;

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', C.year_month_date, A.tenant_id,
        10, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
        0, 0, SUM(D.debit_amount) * -1, A.partner_id
    FROM fi_payment_order A, m_ou_structure B, dt_date C, fi_payment_order_alloc_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.payment_order_id = D.payment_order_id AND
        D.debit_id <> -99
    GROUP BY A.tenant_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code, C.year_month_date, A.partner_id;


    SELECT A.tenant_id, A.ou_id, A.doc_type_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id), B.doc_no, B.doc_date,
        A.invoice_id, A.curr_code, SUM(A.invoice_amount) AS invoice_amount, SUM(A.balance_amount) AS balance_amount, SUM(A.payment_amount) AS payment_amount
    FROM tt_fi_summary_monthly_ap A
    INNER JOIN fi_invoice_ap_balance B ON A.invoice_id = B.invoice_ap_balance_id AND B.doc_type_id = A.doc_type_id
    WHERE A.session_id = 'dsbstwn'
    GROUP BY A.tenant_id, A.ou_Id, A.doc_type_id, A.invoice_id, A.curr_code, B.doc_no, B.doc_date, A.partner_id
    HAVING SUM(A.balance_amount) - SUM(A.payment_amount) <> 0
    ORDER BY B.doc_date, B.doc_no;

ROLLBACK



BEGIN

    DELETE FROM tt_fi_summary_monthly_ap

    ALTER TABLE tt_fi_summary_monthly_ap ADD COLUMN partner_id bigint

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        10, A.doc_type_id, A.invoice_ap_balance_id, A.curr_code,
        A.amount, A.amount, 0, A.partner_id
    FROM fi_invoice_ap_balance A, m_ou_structure B, dt_date C
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712';

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        10, A.doc_type_id, A.invoice_tax_ap_balance_id, A.tax_curr_code,
        A.gov_tax_amount, A.gov_tax_amount, 0, A.partner_id
    FROM fi_invoice_tax_ap_balance A, m_ou_structure B, dt_date C, fi_invoice_ap_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.invoice_ap_balance_id = D.invoice_ap_balance_id AND
        D.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712';

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        10, D.credit_doc_type_id, D.credit_id, D.credit_curr_code,
        0, 0, SUM(D.credit_amount), A.partner_id
    FROM fi_payment_order A, m_ou_structure B, dt_date C, fi_payment_order_alloc_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.payment_order_id = D.payment_order_id AND
        D.credit_id <> -99 AND
        D.flg_alloc <> 'C'
    GROUP BY A.tenant_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code, C.year_month_date, A.partner_id;

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', E.year_month_date, A.tenant_id,
        10, D.credit_doc_type_id, D.credit_id, D.credit_curr_code,
        0, 0, SUM(D.credit_amount), A.partner_id
    FROM fi_payment_order A, m_ou_structure B, fi_payment_order_alloc_balance D, dt_date E
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.payment_order_id = D.payment_order_id AND
        D.credit_id <> -99 AND
        D.flg_alloc = 'C' AND
        D.ref_alloc_id <> -99 AND
        D.ref_doc_date = E.string_date AND
        E.year_month_date between '201701' and '201712'
    GROUP BY A.tenant_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code, E.year_month_date, A.partner_id;

    INSERT INTO tt_fi_summary_monthly_ap
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        10, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
        0, 0, SUM(D.debit_amount) * -1, A.partner_id
    FROM fi_payment_order A, m_ou_structure B, dt_date C, fi_payment_order_alloc_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.payment_order_id = D.payment_order_id AND
        D.debit_id <> -99
    GROUP BY A.tenant_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code, C.year_month_date, A.partner_id;

    SELECT A.tenant_id, A.ou_id, A.doc_type_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id), B.doc_no, B.doc_date,
        A.invoice_id, A.curr_code, SUM(A.invoice_amount) AS invoice_amount, SUM(A.balance_amount) AS balance_amount, SUM(A.payment_amount) AS payment_amount
    FROM tt_fi_summary_monthly_ap A
    INNER JOIN fi_invoice_ap_balance B ON A.invoice_id = B.invoice_ap_balance_id AND B.doc_type_id = A.doc_type_id
    WHERE A.session_id = 'dsbach'
    GROUP BY A.tenant_id, A.ou_Id, A.doc_type_id, A.invoice_id, A.curr_code, B.doc_no, B.doc_date, A.partner_id
    --ORDER BY B.doc_date, B.doc_no;
    HAVING SUM(A.balance_amount) - SUM(A.payment_amount) <> 0
    ORDER BY B.doc_date, B.doc_no;

ROLLBACK



BEGIN

    DELETE FROM tt_fi_summary_monthly_ar

    ALTER TABLE tt_fi_summary_monthly_ar ADD COLUMN partner_id bigint

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', C.year_month_date, A.tenant_id,
        10, A.doc_type_id, A.invoice_ar_balance_id, A.curr_code,
        A.amount, A.amount, 0, A.partner_id
    FROM fi_invoice_ar_balance A, m_ou_structure B, dt_date C
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712';

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', C.year_month_date, A.tenant_id,
        10, A.doc_type_id, A.invoice_tax_ar_balance_id, A.tax_curr_code,
        A.gov_tax_amount, A.gov_tax_amount, 0, A.partner_id
    FROM fi_invoice_tax_ar_balance A, m_ou_structure B, dt_date C, fi_invoice_ar_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.invoice_ar_balance_id = D.invoice_ar_balance_id AND
        D.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712';

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbtwn', C.year_month_date, A.tenant_id,
        10, D.credit_doc_type_id, D.credit_id, D.credit_curr_code,
        0, 0, SUM(D.credit_amount) * -1, A.partner_id
    FROM fi_allocation_ar A, m_ou_structure B, dt_date C, fi_allocation_ar_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.credit_id <> -99 AND
        D.flg_alloc = 'A'
    GROUP BY A.tenant_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code, C.year_month_date, A.partner_id;

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', C.year_month_date, A.tenant_id,
        10, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
        0, 0, SUM(D.debit_amount), A.partner_id
    FROM fi_allocation_ar A, m_ou_structure B, dt_date C, fi_allocation_ar_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.debit_id <> -99 AND
        D.flg_alloc = 'A'
    GROUP BY A.tenant_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code, C.year_month_date, A.partner_id;

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbstwn', C.year_month_date, A.tenant_id,
        10, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
        0, 0, SUM(D.debit_amount), A.partner_id
    FROM fi_allocation_ar A, m_ou_structure B, dt_date C, fi_allocation_ar_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.debit_id <> -99 AND
        D.flg_alloc = 'C'
    GROUP BY A.tenant_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code, C.year_month_date, A.partner_id;

    SELECT A.tenant_id, A.ou_id, A.doc_type_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id), B.doc_no, B.doc_date,
        A.invoice_id, A.curr_code, SUM(A.invoice_amount) AS invoice_amount, SUM(A.balance_amount) AS balance_amount, SUM(A.payment_amount) AS payment_amount
    FROM tt_fi_summary_monthly_ar A
    INNER JOIN fi_invoice_ar_balance B ON A.invoice_id = B.invoice_ar_balance_id
    WHERE A.session_id = 'dsbstwn'
        and B.doc_date >= '20170101'
    GROUP BY A.tenant_id, A.ou_Id, A.doc_type_id, A.invoice_id, A.curr_code, B.doc_no, B.doc_date, A.partner_id
    HAVING SUM(A.balance_amount) - SUM(A.payment_amount) <> 0
    ORDER BY B.doc_date, B.doc_no;

ROLLBACK






BEGIN

    DELETE FROM tt_fi_summary_monthly_ar

    ALTER TABLE tt_fi_summary_monthly_ar ADD COLUMN partner_id bigint

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        17, A.doc_type_id, A.invoice_ar_balance_id, A.curr_code,
        A.amount, A.amount, 0, A.partner_id
    FROM fi_invoice_ar_balance A, m_ou_structure B, dt_date C
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712';

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        17, A.doc_type_id, A.invoice_tax_ar_balance_id, A.tax_curr_code,
        A.gov_tax_amount, A.gov_tax_amount, 0, A.partner_id
    FROM fi_invoice_tax_ar_balance A, m_ou_structure B, dt_date C, fi_invoice_ar_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.invoice_ar_balance_id = D.invoice_ar_balance_id AND
        D.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712';

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        17, D.credit_doc_type_id, D.credit_id, D.credit_curr_code,
        0, 0, SUM(D.credit_amount) * -1, A.partner_id
    FROM fi_allocation_ar A, m_ou_structure B, dt_date C, fi_allocation_ar_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.credit_id <> -99 AND
        D.flg_alloc = 'A'
    GROUP BY A.tenant_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code, C.year_month_date, A.partner_id;

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        17, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
        0, 0, SUM(D.debit_amount), A.partner_id
    FROM fi_allocation_ar A, m_ou_structure B, dt_date C, fi_allocation_ar_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.debit_id <> -99 AND
        D.flg_alloc = 'A'
    GROUP BY A.tenant_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code, C.year_month_date, A.partner_id;

    INSERT INTO tt_fi_summary_monthly_ar
    (session_id, date_year_month, tenant_id,
    ou_id, doc_type_id, invoice_id, curr_code,
    invoice_amount, balance_amount, payment_amount, partner_id)
    SELECT 'dsbach', C.year_month_date, A.tenant_id,
        17, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
        0, 0, SUM(D.debit_amount), A.partner_id
    FROM fi_allocation_ar A, m_ou_structure B, dt_date C, fi_allocation_ar_balance D
    WHERE A.tenant_id = 10 AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = 17 AND
        A.doc_date = C.string_date AND
        C.year_month_date between '201701' and '201712' AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.debit_id <> -99 AND
        D.flg_alloc = 'C'
    GROUP BY A.tenant_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code, C.year_month_date, A.partner_id;

    SELECT A.tenant_id, A.ou_id, A.doc_type_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id), B.doc_no, B.doc_date,
        A.invoice_id, A.curr_code, SUM(A.invoice_amount) AS invoice_amount, SUM(A.balance_amount) AS balance_amount, SUM(A.payment_amount) AS payment_amount
    FROM tt_fi_summary_monthly_ar A
    INNER JOIN fi_invoice_ar_balance B ON A.invoice_id = B.invoice_ar_balance_id
    WHERE A.session_id = 'dsbach'
        and B.doc_date >= '20170101'
    GROUP BY A.tenant_id, A.ou_Id, A.doc_type_id, A.invoice_id, A.curr_code, B.doc_no, B.doc_date, A.partner_id
    HAVING SUM(A.balance_amount) - SUM(A.payment_amount) <> 0
    ORDER BY B.doc_date, B.doc_no;

ROLLBACK