Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --edit, Putra 28 April 2015 -> tambah kolom ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, remark, partner_name pada tr_daily_cashbank
- --edit, Citrahadi 1 Okt 2015 -> tambah mutasi dari dokumen follow up cash advance settlement
- --edit, Thea 20 Mei 2016 -> perhitungan untuk beginning balance
- --Modified by Adrian, Sep 19, 2017, menambahkan filter status doc
- CREATE OR REPLACE FUNCTION r_report_daily_cashbank(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pOuId ALIAS FOR $6;
- pCashbankId ALIAS FOR $7;
- pDateStart ALIAS FOR $8;
- pDateEnd ALIAS FOR $9;
- pStatusDoc ALIAS FOR $10;
- vEmptyId bigint;
- vEmpty character varying(1);
- vCashBankInType character varying(1);
- vCashBankOutType character varying(1);
- vStatusReleased character varying(1);
- vStatusVoid character varying(1);
- vRecordTypeSaldoAwal character varying(1);
- vRecordTypeKredit character varying(1);
- vRecordTypeDebet character varying(1);
- vTglAwalCashbankBalance character varying(8);
- vDocTypePosShop bigint;
- vDocTypeReturnPosShop bigint;
- vDocTypePosShopInshop bigint;
- vDocTypeReturnPosShopInShop bigint;
- vDocTypeCashBankOut bigint;
- vDocTypeCashBankInOther bigint;
- vDocTypeCashBankInAr bigint;
- vDocTypeEdcSettlement bigint;
- vDocTypeFollowUpCashAdvanceSettlement bigint;
- vCount bigint;
- vStatusApprovedDoc character varying;
- vStatusApprovedDocAndOsCbOut character varying;
- vStatusApprovedDocAndOsCbIn character varying;
- vAllStatusDoc character varying;
- vStatusInProgress character varying;
- vStatusDraft character varying;
- vInProgress character varying;
- vApproved character varying;
- vDraft character varying;
- vNewLine character varying;
- BEGIN
- vEmptyId := -99;
- vEmpty := ' ';
- vCashBankInType := 'I';
- vCashBankOutType := 'O';
- vStatusReleased := 'R';
- vStatusVoid := 'V';
- vRecordTypeSaldoAwal := 'A';
- vRecordTypeKredit := 'K';
- vRecordTypeDebet := 'D';
- vTglAwalCashbankBalance := ' ';
- vDocTypePosShop := 401;
- vDocTypeReturnPosShop := 402;
- vDocTypePosShopInShop := 403;
- vDocTypeReturnPosShopInShop := 404;
- vDocTypeCashBankOut := 611;
- vDocTypeCashBankInOther := 623;
- vDocTypeCashBankInAr := 621;
- vDocTypeEdcSettlement := 622;
- vDocTypeFollowUpCashAdvanceSettlement := 641;
- vStatusApprovedDoc := 'approvedDoc';
- vStatusApprovedDocAndOsCbOut := 'approvedDocAndOutstandingCBOutOnly';
- vStatusApprovedDocAndOsCbIn := 'approvedDocAndOutstandingCBInOnly';
- vAllStatusDoc := 'allStatusDoc';
- vStatusInProgress := 'I';
- vStatusDraft := 'D';
- vInProgress := 'IN PROGRESS';
- vDraft := 'DRAFT';
- vApproved := 'APPROVED';
- vNewLine := E'\n';
- DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId;
- DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;
- SELECT MAX(A.cash_bank_date) INTO vTglAwalCashbankBalance
- FROM cb_cashbank_balance A
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.rec_type = vRecordTypeSaldoAwal
- AND A.cash_bank_date <= pDateStart;
- IF vTglAwalCashbankBalance IS null OR vTglAwalCashbankBalance IN ('', ' ') THEN
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, 1, -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(CASE WHEN rec_type = vRecordTypeKredit THEN A.amount * -1 ELSE A.amount END) AS sum_amount
- FROM cb_cashbank_balance A
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.cash_bank_date < pDateStart
- GROUP BY A.cashbank_id;
- ELSE
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
- 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(A.amount) AS sum_amount
- FROM cb_cashbank_balance A
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.cash_bank_date = vTglAwalCashbankBalance
- AND A.rec_type = vRecordTypeSaldoAwal
- GROUP BY A.cashbank_id;
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
- 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(CASE WHEN rec_type = vRecordTypeKredit THEN A.amount * -1 ELSE A.amount END) AS sum_amount
- FROM cb_cashbank_balance A
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.cash_bank_date >= vTglAwalCashbankBalance
- AND A.cash_bank_date < pDateStart
- AND A.rec_type <> vRecordTypeSaldoAwal
- GROUP BY A.cashbank_id;
- --ambil nilai untuk CB trf
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
- 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(A.transfer_amount)
- FROM cb_transfer_cashbank A
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.status_doc <> vStatusReleased
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- GROUP BY A.cashbank_id;
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
- 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(B.payment_amount)
- FROM cb_transfer_cashbank A
- RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id = B.transfer_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.status_doc <> vStatusReleased
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- GROUP BY A.cashbank_id;
- --ambil nilai untuk CB out
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(B.cashbank_amount)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- AND A.status_doc <> vStatusReleased
- AND A.doc_type_id = vDocTypeCashBankOut
- GROUP BY B.cashbank_id;
- --ambil nilai untuk FU CAS
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(B.cashbank_amount)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- AND A.status_doc <> vStatusReleased
- AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
- AND A.ref_amount > 0
- GROUP BY B.cashbank_id;
- --Adrian, Sep 19, 2017, ambil nilai untuk O/S CB out
- IF (pStatusDoc = vStatusApprovedDocAndOsCbOut)
- OR (pStatusDoc = vAllStatusDoc) THEN
- --ambil nilai untuk O/S CB trf
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
- 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(-1 * A.transfer_amount)
- FROM cb_transfer_cashbank A
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- GROUP BY A.cashbank_id;
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
- 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(-1 * B.payment_amount)
- FROM cb_transfer_cashbank A
- RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id = B.transfer_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- GROUP BY A.cashbank_id;
- --ambil nilai untuk O/S CB out
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(-1 * B.cashbank_amount)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeCashBankOut
- GROUP BY B.cashbank_id;
- --ambil nilai untuk O/S FU CAS
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(-1 * B.cashbank_amount)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
- AND A.ref_amount > 0
- GROUP BY B.cashbank_id;
- END IF;
- --Adrian, Sep 19, 2017, ambil nilai untuk O/S CB in
- IF (pStatusDoc = vStatusApprovedDocAndOsCbIn)
- OR (pStatusDoc = vAllStatusDoc) THEN
- --ambil nilai untuk O/S CB trf
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(A.receive_amount)
- FROM cb_transfer_cashbank_receive A
- INNER JOIN cb_transfer_cashbank B ON A.transfer_cashbank_id = B.transfer_cashbank_id
- WHERE B.tenant_id = pTenantId
- AND A.cashbank_to_id = pCashbankId
- AND B.status_doc IN (vStatusDraft, vStatusInProgress)
- AND B.doc_date >= vTglAwalCashbankBalance
- AND B.doc_date < pDateStart
- GROUP BY B.cashbank_id;
- --ambil nilai untuk O/S CB in AR
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(B.cashbank_amount) - SUM(COALESCE(C.cost_amount, 0))
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeCashBankInAr
- GROUP BY B.cashbank_id;
- --ambil nilai untuk O/S CB in other
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(B.cashbank_amount) - SUM(COALESCE(C.cost_amount, 0))
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeCashBankInOther
- GROUP BY B.cashbank_id;
- --ambil nilai untuk O/S EDC Settlement
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(B.cashbank_amount)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeEdcSettlement
- GROUP BY B.cashbank_id;
- --ambil nilai untuk O/S FU CAS
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, B.cashbank_id, -1,
- -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- SUM(B.cashbank_amount)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date >= vTglAwalCashbankBalance
- AND A.doc_date < pDateStart
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
- AND A.ref_amount < 0
- GROUP BY B.cashbank_id;
- END IF;
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, cashbank_id, 1,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- SUM(amount)
- FROM tr_daily_cashbank
- WHERE session_id = pSessionId
- AND tenant_id = pTenantId
- AND order_type_data = -1
- GROUP BY cashbank_id, order_type_data, doc_type_id, doc_type_desc, doc_no, doc_date, mode_payment, bank_payment, no_payment, date_payment;
- DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId AND order_type_data = -1;
- END IF;
- -- cb trf
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark)
- SELECT pSessionId, pTenantId, pCashbankId, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
- -1 * A.transfer_amount, vApproved || vNewLine || A.remark
- FROM cb_transfer_cashbank A
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.status_doc = vStatusReleased
- AND A.doc_date BETWEEN pDateStart AND pDateEnd;
- /*
- * PS, 27 Maret 2015
- * update amount pada tr_daily_cashbank, dikurangi dengan cost_amount dari cb_transfer_cashbank_cost
- * memakai RIGHT JOIN karena transaksi di cb_transfer_cashbank_cost bisa kosong untuk sebuah transfer cashbank
- */
- -- UPDATE tr_daily_cashbank C
- -- SET amount = C.amount - B.cost_amount
- -- FROM cb_transfer_cashbank A
- -- RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id=B.transfer_cashbank_id
- -- WHERE A.tenant_id = pTenantId
- -- AND A.cashbank_id = pCashbankId
- -- AND A.status_doc = vStatusReleased
- -- AND A.doc_date BETWEEN pDateStart AND pDateEnd;
- UPDATE tr_daily_cashbank C
- SET amount = C.amount - B.payment_amount
- FROM cb_transfer_cashbank A
- RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id=B.transfer_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.doc_no = C.doc_no
- AND A.doc_date = C.doc_date
- AND A.doc_type_id = C.doc_type_id
- AND A.status_doc = vStatusReleased
- AND A.doc_date BETWEEN pDateStart AND pDateEnd;
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark)
- SELECT pSessionId, pTenantId, pCashbankId, 2,
- B.doc_type_id, f_get_doc_desc(B.doc_type_id), B.doc_no, B.doc_date,
- A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
- A.receive_amount, vApproved || vNewLine || A.remark
- FROM cb_transfer_cashbank_receive A
- INNER JOIN cb_transfer_cashbank B ON A.transfer_cashbank_id = B.transfer_cashbank_id
- WHERE B.tenant_id = pTenantId
- AND A.cashbank_to_id = pCashbankId
- AND B.status_doc = vStatusReleased
- AND B.doc_date BETWEEN pDateStart AND pDateEnd;
- -- cash bank out
- /* Note : WTC & TKP 24 Mei 2016
- * tidak perlu join ke cb_in_out_cashbank_cost
- * dan untuk join ke cb_trx_cashbank_balance langsung di inner join aja
- */
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- (-1 * B.cashbank_amount), vApproved || vNewLine || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
- f_get_partner_name(A.partner_id) AS partner_name
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc = vStatusReleased
- AND A.doc_type_id = vDocTypeCashBankOut
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;
- -- cash bank in AR
- /* Note : WTC & TKP 24 Mei 2016
- * tidak perlu join ke cb_trx_cashbank_balance
- */
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- B.cashbank_amount - SUM(COALESCE(C.cost_amount, 0)),
- vApproved || vNewLine || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, f_get_partner_name(A.partner_id)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc = vStatusReleased
- AND A.doc_type_id = vDocTypeCashBankInAr
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;
- -- cash bank in other
- /* Note : WTC & TKP 24 Mei 2016
- * tidak perlu join ke cb_trx_cashbank_balance
- *
- * Note : Didit 24 Agustus 2016
- * Perhitungkan juga dokumen yang statusnya void karena hasil konversi
- */
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- B.cashbank_amount, vApproved || vNewLine || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
- f_get_partner_name(A.partner_id)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc IN (vStatusReleased, vStatusVoid)
- AND A.doc_type_id = vDocTypeCashBankInOther
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;
- -- edc settlement
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, A.doc_no, A.doc_date,
- B.cashbank_amount, vApproved || vNewLine || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
- f_get_partner_name(A.partner_id)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc = vStatusReleased
- AND A.doc_type_id = vDocTypeEdcSettlement
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;
- -- Added by Citrahadi, 2015-10-01
- -- follow up cash advance settlement
- -- ref_amount minus artinya follow up dgn cashbank in
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no,
- ref_doc_date, partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- CASE WHEN A.ref_amount < 0 THEN B.cashbank_amount ELSE (-1 * B.cashbank_amount) END, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no,
- D.payment_doc_date, f_get_partner_name(A.partner_id) AS partner_name
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc = vStatusReleased
- AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank,
- B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.ref_amount;
- --Adrian, Sep 19, 2017, O/S cash bank out
- IF (pStatusDoc = vStatusApprovedDocAndOsCbOut)
- OR (pStatusDoc = vAllStatusDoc) THEN
- -- O/S cb trf
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark)
- SELECT pSessionId, pTenantId, pCashbankId, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
- -1 * A.transfer_amount,
- CASE WHEN (A.status_doc = vStatusDraft) THEN
- vDraft
- ELSE
- vInProgress
- END
- || vNewLine || A.remark AS remark
- FROM cb_transfer_cashbank A
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_date BETWEEN pDateStart AND pDateEnd;
- UPDATE tr_daily_cashbank C
- SET amount = C.amount - B.payment_amount
- FROM cb_transfer_cashbank A
- RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id=B.transfer_cashbank_id
- WHERE A.tenant_id = pTenantId
- AND A.cashbank_id = pCashbankId
- AND A.doc_no = C.doc_no
- AND A.doc_date = C.doc_date
- AND A.doc_type_id = C.doc_type_id
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_date BETWEEN pDateStart AND pDateEnd;
- --O/S cash bank out
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- (-1 * B.cashbank_amount),
- CASE WHEN (A.status_doc = vStatusDraft) THEN
- vDraft
- ELSE
- vInProgress
- END
- || vNewLine || A.remark AS remark,
- A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
- f_get_partner_name(A.partner_id) AS partner_name
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeCashBankOut
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;
- --O/S follow up cash advance settlement
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no,
- ref_doc_date, partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- (-1 * B.cashbank_amount),
- CASE WHEN (A.status_doc = vStatusDraft) THEN
- vDraft
- ELSE
- vInProgress
- END
- || vNewLine || A.remark AS remark,
- A.ref_id, A.ref_doc_type_id, D.payment_doc_no,
- D.payment_doc_date, f_get_partner_name(A.partner_id) AS partner_name
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
- AND A.ref_amount > 0
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank,
- B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.ref_amount, A.status_doc;
- END IF;
- --Adrian, Sep 19, 2017, O/S cash bank in
- IF (pStatusDoc = vStatusApprovedDocAndOsCbIn)
- OR (pStatusDoc = vAllStatusDoc) THEN
- --O/S cb trf
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark)
- SELECT pSessionId, pTenantId, pCashbankId, 2,
- B.doc_type_id, f_get_doc_desc(B.doc_type_id), B.doc_no, B.doc_date,
- A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
- A.receive_amount,
- CASE WHEN (B.status_doc = vStatusDraft) THEN
- vDraft
- ELSE
- vInProgress
- END
- || vNewLine || A.remark AS remark
- FROM cb_transfer_cashbank_receive A
- INNER JOIN cb_transfer_cashbank B ON A.transfer_cashbank_id = B.transfer_cashbank_id
- WHERE B.tenant_id = pTenantId
- AND A.cashbank_to_id = pCashbankId
- AND B.status_doc IN (vStatusDraft, vStatusInProgress)
- AND B.doc_date BETWEEN pDateStart AND pDateEnd;
- --O/S cash bank in AR
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- B.cashbank_amount - SUM(COALESCE(C.cost_amount, 0)),
- CASE WHEN (A.status_doc = vStatusDraft) THEN
- vDraft
- ELSE
- vInProgress
- END
- || vNewLine || A.remark AS remark,
- A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, f_get_partner_name(A.partner_id)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeCashBankInAr
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;
- --O/S cash bank in other
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- B.cashbank_amount,
- CASE WHEN (A.status_doc = vStatusDraft) THEN
- vDraft
- ELSE
- vInProgress
- END
- || vNewLine || A.remark AS remark,
- A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
- f_get_partner_name(A.partner_id)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeCashBankInOther
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;
- -- edc settlement
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, A.doc_no, A.doc_date,
- B.cashbank_amount,
- CASE WHEN (A.status_doc = vStatusDraft) THEN
- vDraft
- ELSE
- vInProgress
- END
- || vNewLine || A.remark AS remark,
- A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
- f_get_partner_name(A.partner_id)
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeEdcSettlement
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;
- --O/S follow up cash advance settlement
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount, remark, ref_id, ref_doc_type_id, ref_doc_no,
- ref_doc_date, partner_name)
- SELECT pSessionId, pTenantId, B.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
- B.cashbank_amount,
- CASE WHEN (A.status_doc = vStatusDraft) THEN
- vDraft
- ELSE
- vInProgress
- END
- || vNewLine || A.remark AS remark,
- A.ref_id, A.ref_doc_type_id, D.payment_doc_no,
- D.payment_doc_date, f_get_partner_name(A.partner_id) AS partner_name
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
- WHERE A.tenant_id = pTenantId
- AND B.cashbank_id = pCashbankId
- AND A.doc_date BETWEEN pDateStart AND pDateEnd
- AND A.status_doc IN (vStatusDraft, vStatusInProgress)
- AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
- AND A.ref_amount < 0
- GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank,
- B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.ref_amount, A.status_doc;
- END IF;
- /*
- * ambil data transaksi penjualan pos dan return pos untuk cash payment
- */
- INSERT INTO tr_kartu_kas_by_doc_type
- (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
- doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
- amount_debit, amount_credit)
- SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
- A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
- SUM(B.conversion_amount), 0
- FROM i_trx_pos A, i_trx_pos_cash_payment B, m_document C, m_cashbank D, m_cashbank_ou E
- WHERE A.tenant_id = pTenantId AND
- --A.ou_id = pOuId AND
- A.doc_date BETWEEN pDateStart AND pDateEnd AND
- A.doc_type_id IN (vDocTypePosShop, vDocTypePosShopInShop) AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- A.process_no = B.process_no AND
- B.curr_payment_code = D.curr_code AND
- D.flg_cash_bank = 'C' AND
- D.cashbank_id = E.cashbank_id AND
- A.ou_id = E.ou_id AND
- A.doc_type_id = C.doc_type_id AND
- D.cashbank_id = pCashbankId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;
- INSERT INTO tr_kartu_kas_by_doc_type
- (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
- doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
- amount_debit, amount_credit)
- SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
- A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
- SUM(B.conversion_amount), 0
- FROM i_trx_pos A, i_trx_pos_cash_payment B, m_document C, m_cashbank D, m_cashbank_ou E
- WHERE A.tenant_id = pTenantId AND
- --A.ou_id = pOuId AND
- A.doc_date BETWEEN pDateStart AND pDateEnd AND
- A.doc_type_id IN (vDocTypeReturnPosShop, vDocTypeReturnPosShopInShop) AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- A.process_no = B.process_no AND
- B.curr_payment_code = D.curr_code AND
- D.flg_cash_bank = 'C' AND
- D.cashbank_id = E.cashbank_id AND
- A.ou_id = E.ou_id AND
- A.doc_type_id = C.doc_type_id AND
- D.cashbank_id = pCashbankId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;
- /*
- * ambil data transaksi penjualan pos dan return pos untuk pengembalian cash
- */
- INSERT INTO tr_kartu_kas_by_doc_type
- (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
- doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
- amount_debit, amount_credit)
- SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
- A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
- 0, SUM(A.total_refund)
- FROM i_trx_pos A, m_document C, m_cashbank D, m_cashbank_ou E
- WHERE A.tenant_id = pTenantId AND
- --A.ou_id = pOuId AND
- A.doc_date BETWEEN pDateStart AND pDateEnd AND
- A.doc_type_id IN (vDocTypePosShop, vDocTypePosShopInShop) AND
- A.curr_code = D.curr_code AND
- D.flg_cash_bank = 'C' AND
- D.cashbank_id = E.cashbank_id AND
- A.ou_id = E.ou_id AND
- A.doc_type_id = C.doc_type_id AND
- D.cashbank_id = pCashbankId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;
- INSERT INTO tr_kartu_kas_by_doc_type
- (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
- doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
- amount_debit, amount_credit)
- SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
- A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
- 0, SUM(A.total_refund)
- FROM i_trx_pos A, m_document C, m_cashbank D, m_cashbank_ou E
- WHERE A.tenant_id = pTenantId AND
- --A.ou_id = pOuId AND
- A.doc_date BETWEEN pDateStart AND pDateEnd AND
- A.doc_type_id IN (vDocTypeReturnPosShop, vDocTypeReturnPosShopInShop) AND
- A.curr_code = D.curr_code AND
- D.flg_cash_bank = 'C' AND
- D.cashbank_id = E.cashbank_id AND
- A.ou_id = E.ou_id AND
- A.doc_type_id = C.doc_type_id AND
- D.cashbank_id = pCashbankId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
- 'CASH', ' ', ' ', ' ',
- SUM(A.amount_debit) - SUM(A.amount_credit)
- FROM tr_kartu_kas_by_doc_type A
- WHERE A.session_id = pSessionId
- GROUP BY A.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date;
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_id, doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, A.cashbank_id, 2,
- A.doc_type_id, f_get_doc_desc(A.doc_type_id), 'VOID ' || A.doc_no, A.doc_date,
- 'CASH', ' ', ' ', ' ',
- -1 * (SUM(A.amount_debit) - SUM(A.amount_credit))
- FROM tr_kartu_kas_by_doc_type A
- WHERE A.session_id = pSessionId
- AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
- WHERE A.tenant_id = B.tenant_id AND
- A.doc_no = B.doc_no AND
- A.doc_date = B.doc_date AND
- A.ou_id = B.ou_id)
- GROUP BY A.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date;
- SELECT COUNT(1) INTO vCount
- FROM tr_daily_cashbank
- WHERE session_id = pSessionId AND order_type_data = 1;
- IF vCount = 0 THEN
- INSERT INTO tr_daily_cashbank
- (session_id, tenant_id, cashbank_id, order_type_data,
- doc_type_desc, doc_no, doc_date,
- mode_payment, bank_payment, no_payment, date_payment,
- amount)
- SELECT pSessionId, pTenantId, pCashbankId, 1, 'Beginning Balance', 'Beginning Balance', pDateStart,
- ' ', ' ', ' ', ' ',
- 0;
- END IF;
- Open pRefHeader FOR
- SELECT pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name, pDateStart AS date_start, pDateEnd AS date_end,
- pDateStart AS date_trx, f_get_username(pUserId) AS username, pDatetime AS datetime,
- pCashbankId AS cashbank_id, A.cashbank_code AS cashbank_code, A.cashbank_name AS cashbank_name,
- A.bank_code AS bank_code, A.account_no AS account_no, A.account_name1 AS account_name1, A.account_name2 AS account_name2,
- A.curr_code AS curr_code
- FROM m_cashbank A WHERE A.cashbank_id = pCashBankId;
- RETURN NEXT pRefHeader;
- Open pRefDetail FOR
- SELECT A.doc_type_desc AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no,
- A.mode_payment AS mode_payment, A.bank_payment AS bank_payment,
- A.ref_doc_date AS ref_doc_date, A.ref_doc_no AS ref_doc_no,
- CASE WHEN (A.no_payment = '-') THEN '' ELSE A.no_payment END AS no_payment,
- CASE WHEN (A.amount > 0 ) THEN A.amount ELSE 0 END AS debit,
- CASE WHEN (A.amount < 0 ) THEN -1 * A.amount ELSE 0 END AS credit,
- SUM(A.amount) OVER (ORDER BY A.order_type_data, A.doc_date, A.doc_no) AS amount,
- A.remark, A.partner_name,
- (CASE WHEN length(TRIM(A.remark)) = 0 THEN (CEIL(1/36.0))*21 ELSE ((CEIL(length(SPLIT_PART(TRIM(A.remark), vNewLine, 2))/36.0))*21)+21 END) AS height
- FROM tr_daily_cashbank A
- WHERE A.session_id = pSessionId
- AND A.tenant_id = pTenantId
- GROUP BY A.doc_type_desc, A.doc_date, A.doc_no, A.mode_payment, A.bank_payment, A.ref_doc_date, A.ref_doc_no, A.order_type_data, A.amount, A.remark, A.partner_name, A.no_payment
- ORDER BY A.order_type_data, A.doc_date, A.doc_no;
- RETURN NEXT pRefDetail;
- DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId;
- DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Add Comment
Please, Sign In to add comment