|
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
|
|
-- PAIDCHEQUE->TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN
|
|
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
|
|
------------------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION fn_get_all_paid_cheque_return() RETURNS SETOF paidcheque.tbl_paid_cheque_return AS $$
|
|
DECLARE
|
|
|
|
BEGIN
|
|
RETURN QUERY SELECT * FROM paidcheque.tbl_paid_cheque_return WHERE int_sys_action_id != -1;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
------------------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION fn_get_all_paid_cheque_return_with_action_user()
|
|
RETURNS TABLE (pk_paid_cheque_return_id BIGINT,
|
|
|
|
fk_documents_id BIGINT,
|
|
fk_paid_cheque_id BIGINT,
|
|
fk_paid_cheque_details_id BIGINT,
|
|
|
|
fk_bank_charge_id BIGINT,
|
|
fk_bank_cr_customer_id BIGINT,
|
|
fk_bank_expense_id BIGINT,
|
|
|
|
vhr_bank_currency VARCHAR,
|
|
dbl_bank_cur_roe DOUBLE PRECISION,
|
|
dbl_bank_cur_bank_charge DOUBLE PRECISION,
|
|
|
|
vhr_cust_currency VARCHAR,
|
|
dbl_cust_cur_roe DOUBLE PRECISION,
|
|
dbl_cust_cur_cust_charge DOUBLE PRECISION,
|
|
|
|
vhr_base_currency VARCHAR,
|
|
dbl_base_cur_bank_charge DOUBLE PRECISION,
|
|
dbl_base_cur_cust_charge DOUBLE PRECISION,
|
|
|
|
fk_service_tax_id BIGINT,
|
|
dbl_sys_perce_of_tax DOUBLE PRECISION,
|
|
dbl_perce_of_tax DOUBLE PRECISION,
|
|
dbl_ledg_cur_tax_base_amount DOUBLE PRECISION,
|
|
dbl_ledg_cur_tax_amount DOUBLE PRECISION,
|
|
|
|
dbl_base_cur_tax_base_amount DOUBLE PRECISION,
|
|
dbl_base_cur_tax_amount DOUBLE PRECISION) AS $$
|
|
DECLARE
|
|
|
|
BEGIN
|
|
RETURN QUERY SELECT mt.pk_paid_cheque_return_id,
|
|
|
|
mt.fk_documents_id,
|
|
mt.fk_paid_cheque_id,
|
|
mt.fk_paid_cheque_details_id,
|
|
|
|
mt.fk_bank_charge_id,
|
|
mt.fk_bank_cr_customer_id,
|
|
mt.fk_bank_expense_id,
|
|
|
|
mt.vhr_bank_currency,
|
|
mt.dbl_bank_cur_roe,
|
|
mt.dbl_bank_cur_bank_charge,
|
|
|
|
mt.vhr_cust_currency,
|
|
mt.dbl_cust_cur_roe,
|
|
mt.dbl_cust_cur_cust_charge,
|
|
|
|
mt.vhr_base_currency,
|
|
mt.dbl_base_cur_bank_charge,
|
|
mt.dbl_base_cur_cust_charge,
|
|
|
|
mt.fk_service_tax_id,
|
|
mt.dbl_sys_perce_of_tax,
|
|
mt.dbl_perce_of_tax,
|
|
mt.dbl_ledg_cur_tax_base_amount,
|
|
mt.dbl_ledg_cur_tax_amount,
|
|
|
|
mt.dbl_base_cur_tax_base_amount,
|
|
mt.dbl_base_cur_tax_amount
|
|
FROM paidcheque.tbl_paid_cheque_return mt
|
|
LEFT JOIN authentication.tbl_user u1
|
|
ON mt.fk_created_user_id = u1.pk_user_id
|
|
LEFT JOIN authentication.tbl_user u2
|
|
ON mt.fk_last_modified_user_id = u2.pk_user_id
|
|
LEFT JOIN authentication.tbl_user u3
|
|
ON mt.fk_deleted_user_id = u3.pk_user_id
|
|
WHERE mt.int_sys_action_id != -1
|
|
ORDER BY ;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
------------------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE PROCEDURE sp_insert_or_update_paid_cheque_return(jsnPaidChequeReturn JSONB, jsnSqlRes OUT JSON) AS $$
|
|
DECLARE
|
|
objSpOutRes TYP_SP_OUT_RES;
|
|
objFnCheckRes TYP_FN_CHECK_RES;
|
|
dtmCurrent TIMESTAMP WITH TIME ZONE;
|
|
BEGIN
|
|
SELECT NOW() INTO dtmCurrent;
|
|
|
|
-- // Duplication Checking in DB FUCTION(Insert and Update)
|
|
SELECT * FROM fn_check_data_uniqueness('paidcheque.tbl_paid_cheque_return'::VARCHAR, ''::VARCHAR, ::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT)::BIGINT) INTO objFnCheckRes;
|
|
IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
|
|
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
|
|
objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;
|
|
|
|
objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';
|
|
|
|
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
|
|
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
|
|
RETURN;
|
|
END IF;
|
|
|
|
IF CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT)::BIGINT = 0::BIGINT THEN
|
|
-- // Count Exced or other checking License related in DB FUCTION(Insert)
|
|
|
|
|
|
--jsnPaidChequeReturn.intSysActionId = 0;
|
|
--jsnPaidChequeReturn.binCreatedUserId = jsnPaidChequeReturn.linLoginUserId;
|
|
--jsnPaidChequeReturn.dtmCreated = jsnPaidChequeReturn.dtmCurrentDateTime; --SELECT NOW();
|
|
|
|
INSERT INTO paidcheque.tbl_paid_cheque_return (fk_documents_id,
|
|
fk_paid_cheque_id,
|
|
fk_paid_cheque_details_id,
|
|
|
|
fk_bank_charge_id,
|
|
fk_bank_cr_customer_id,
|
|
fk_bank_expense_id,
|
|
|
|
vhr_bank_currency,
|
|
dbl_bank_cur_roe,
|
|
dbl_bank_cur_bank_charge,
|
|
|
|
vhr_cust_currency,
|
|
dbl_cust_cur_roe,
|
|
dbl_cust_cur_cust_charge,
|
|
|
|
vhr_base_currency,
|
|
dbl_base_cur_bank_charge,
|
|
dbl_base_cur_cust_charge,
|
|
|
|
fk_service_tax_id,
|
|
dbl_sys_perce_of_tax,
|
|
dbl_perce_of_tax,
|
|
dbl_ledg_cur_tax_base_amount,
|
|
dbl_ledg_cur_tax_amount,
|
|
|
|
dbl_base_cur_tax_base_amount,
|
|
dbl_base_cur_tax_amount)
|
|
VALUES(CAST(jsnPaidChequeReturn->>'binDocumentsId' AS BIGINT),
|
|
CAST(jsnPaidChequeReturn->>'binPaidChequeId' AS BIGINT),
|
|
CAST(jsnPaidChequeReturn->>'binPaidChequeDetailsId' AS BIGINT),
|
|
|
|
CAST(jsnPaidChequeReturn->>'binBankChargeId' AS BIGINT),
|
|
CAST(jsnPaidChequeReturn->>'binBankCrCustomerId' AS BIGINT),
|
|
CAST(jsnPaidChequeReturn->>'binBankExpenseId' AS BIGINT),
|
|
|
|
jsnPaidChequeReturn->>'strBankCurrency',
|
|
CAST(jsnPaidChequeReturn->>'dblBankCurRoe' AS DOUBLE PRECISION),
|
|
CAST(jsnPaidChequeReturn->>'dblBankCurBankCharge' AS DOUBLE PRECISION),
|
|
|
|
jsnPaidChequeReturn->>'strCustCurrency',
|
|
CAST(jsnPaidChequeReturn->>'dblCustCurRoe' AS DOUBLE PRECISION),
|
|
CAST(jsnPaidChequeReturn->>'dblCustCurCustCharge' AS DOUBLE PRECISION),
|
|
|
|
jsnPaidChequeReturn->>'strBaseCurrency',
|
|
CAST(jsnPaidChequeReturn->>'dblBaseCurBankCharge' AS DOUBLE PRECISION),
|
|
CAST(jsnPaidChequeReturn->>'dblBaseCurCustCharge' AS DOUBLE PRECISION),
|
|
|
|
CAST(jsnPaidChequeReturn->>'binServiceTaxId' AS BIGINT),
|
|
CAST(jsnPaidChequeReturn->>'dblSysPerceOfTax' AS DOUBLE PRECISION),
|
|
CAST(jsnPaidChequeReturn->>'dblPerceOfTax' AS DOUBLE PRECISION),
|
|
CAST(jsnPaidChequeReturn->>'dblLedgCurTaxBaseAmount' AS DOUBLE PRECISION),
|
|
CAST(jsnPaidChequeReturn->>'dblLedgCurTaxAmount' AS DOUBLE PRECISION),
|
|
|
|
CAST(jsnPaidChequeReturn->>'dblBaseCurTaxBaseAmount' AS DOUBLE PRECISION),
|
|
CAST(jsnPaidChequeReturn->>'dblBaseCurTaxAmount' AS DOUBLE PRECISION)) RETURNING pk_paid_cheque_return_id INTO objSpOutRes.binSqlPk;
|
|
ELSE
|
|
-- // UPDATE Check already update another usersin DB FUCTION(Update and Delete)
|
|
SELECT * FROM fn_check_already_update_or_delete_another_user('paidcheque.tbl_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT)::BIGINT, CAST(jsnPaidChequeReturn->>'intSysActionId' AS INT)::INT) INTO objFnCheckRes;
|
|
IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
|
|
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
|
|
objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;
|
|
|
|
objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';
|
|
|
|
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
|
|
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
|
|
RETURN;
|
|
END IF;
|
|
|
|
--jsnPaidChequeReturn.intSysActionId = jsnPaidChequeReturn.intSysActionId + 1;
|
|
--jsnPaidChequeReturn.binLastModifiedUserId = jsnPaidChequeReturn.linLoginUserId;
|
|
--jsnPaidChequeReturn.dtmLastModified = jsnPaidChequeReturn.dtmCurrentDateTime; --SELECT NOW();
|
|
|
|
UPDATE paidcheque.tbl_paid_cheque_return SET fk_documents_id = CAST(jsnPaidChequeReturn->>'binDocumentsId' AS BIGINT),
|
|
fk_paid_cheque_id = CAST(jsnPaidChequeReturn->>'binPaidChequeId' AS BIGINT),
|
|
fk_paid_cheque_details_id = CAST(jsnPaidChequeReturn->>'binPaidChequeDetailsId' AS BIGINT),
|
|
|
|
fk_bank_charge_id = CAST(jsnPaidChequeReturn->>'binBankChargeId' AS BIGINT),
|
|
fk_bank_cr_customer_id = CAST(jsnPaidChequeReturn->>'binBankCrCustomerId' AS BIGINT),
|
|
fk_bank_expense_id = CAST(jsnPaidChequeReturn->>'binBankExpenseId' AS BIGINT),
|
|
|
|
vhr_bank_currency = jsnPaidChequeReturn->>'strBankCurrency',
|
|
dbl_bank_cur_roe = CAST(jsnPaidChequeReturn->>'dblBankCurRoe' AS DOUBLE PRECISION),
|
|
dbl_bank_cur_bank_charge = CAST(jsnPaidChequeReturn->>'dblBankCurBankCharge' AS DOUBLE PRECISION),
|
|
|
|
vhr_cust_currency = jsnPaidChequeReturn->>'strCustCurrency',
|
|
dbl_cust_cur_roe = CAST(jsnPaidChequeReturn->>'dblCustCurRoe' AS DOUBLE PRECISION),
|
|
dbl_cust_cur_cust_charge = CAST(jsnPaidChequeReturn->>'dblCustCurCustCharge' AS DOUBLE PRECISION),
|
|
|
|
vhr_base_currency = jsnPaidChequeReturn->>'strBaseCurrency',
|
|
dbl_base_cur_bank_charge = CAST(jsnPaidChequeReturn->>'dblBaseCurBankCharge' AS DOUBLE PRECISION),
|
|
dbl_base_cur_cust_charge = CAST(jsnPaidChequeReturn->>'dblBaseCurCustCharge' AS DOUBLE PRECISION),
|
|
|
|
fk_service_tax_id = CAST(jsnPaidChequeReturn->>'binServiceTaxId' AS BIGINT),
|
|
dbl_sys_perce_of_tax = CAST(jsnPaidChequeReturn->>'dblSysPerceOfTax' AS DOUBLE PRECISION),
|
|
dbl_perce_of_tax = CAST(jsnPaidChequeReturn->>'dblPerceOfTax' AS DOUBLE PRECISION),
|
|
dbl_ledg_cur_tax_base_amount = CAST(jsnPaidChequeReturn->>'dblLedgCurTaxBaseAmount' AS DOUBLE PRECISION),
|
|
dbl_ledg_cur_tax_amount = CAST(jsnPaidChequeReturn->>'dblLedgCurTaxAmount' AS DOUBLE PRECISION),
|
|
|
|
dbl_base_cur_tax_base_amount = CAST(jsnPaidChequeReturn->>'dblBaseCurTaxBaseAmount' AS DOUBLE PRECISION),
|
|
dbl_base_cur_tax_amount = CAST(jsnPaidChequeReturn->>'dblBaseCurTaxAmount' AS DOUBLE PRECISION)
|
|
WHERE pk_paid_cheque_return_id = CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT);
|
|
END IF;
|
|
|
|
--COMMIT;
|
|
objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
|
|
objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn Saved';
|
|
objSpOutRes.dtmCurrent = dtmCurrent;
|
|
|
|
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
|
|
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
|
|
RETURN;
|
|
EXCEPTION WHEN OTHERS THEN
|
|
ROLLBACK;
|
|
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
|
|
objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn Save Failed';
|
|
|
|
objSpOutRes.strSqlCustomErrorType := 'DATABASE_SYSTEM_ERROR';
|
|
|
|
GET STACKED DIAGNOSTICS
|
|
objSpOutRes.strSqlSysMessage := MESSAGE_TEXT,
|
|
objSpOutRes.strSqlSysExceptionDetail := PG_EXCEPTION_DETAIL,
|
|
objSpOutRes.strSqlSysExceptionHint := PG_EXCEPTION_CONTEXT;
|
|
|
|
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
|
|
RAISE EXCEPTION 'jsnSqlRes: %', jsnSqlRes;
|
|
RETURN;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
------------------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE PROCEDURE sp_delete_paid_cheque_return(binPaidChequeReturnId BIGINT, intSysActionId INT, binDeletedUserId BIGINT, jsnSqlRes OUT JSON) AS $$
|
|
DECLARE
|
|
objSpOutRes TYP_SP_OUT_RES;
|
|
objFnCheckRes TYP_FN_CHECK_RES;
|
|
dtmCurrent TIMESTAMP WITH TIME ZONE;
|
|
BEGIN
|
|
SELECT NOW() INTO dtmCurrent;
|
|
|
|
-- // FOREIGN KEY REFERENCES Checking in DB FUCTION(Delete)
|
|
SELECT * FROM fn_check_foreignkey_referance('tbl_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, binPaidChequeReturnId::BIGINT, '{}'::VARCHAR ARRAY) INTO objFnCheckRes;
|
|
IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
|
|
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
|
|
objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;
|
|
|
|
objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';
|
|
|
|
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
|
|
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
|
|
RETURN;
|
|
END IF;
|
|
|
|
-- // UPDATE Check already update another users in DB FUCTION(Update and Delete)
|
|
SELECT * FROM fn_check_already_update_or_delete_another_user('paidcheque.tbl_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(binPaidChequeReturnId AS BIGINT)::BIGINT, intSysActionId::INT) INTO objFnCheckRes;
|
|
IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
|
|
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
|
|
objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;
|
|
|
|
objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';
|
|
|
|
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
|
|
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
|
|
RETURN;
|
|
END IF;
|
|
|
|
--dtmDeleted = SELECT NOW();
|
|
UPDATE paidcheque.tbl_paid_cheque_return SET int_sys_action_id = -1::INT,
|
|
fk_deleted_user_id = binDeletedUserId,
|
|
dtm_deleted = dtmCurrent
|
|
WHERE pk_paid_cheque_return_id = binPaidChequeReturnId;
|
|
|
|
--COMMIT;
|
|
objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
|
|
objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn Deleted';
|
|
objSpOutRes.dtmCurrent = dtmCurrent;
|
|
|
|
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
|
|
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
|
|
RETURN;
|
|
EXCEPTION WHEN OTHERS THEN
|
|
ROLLBACK;
|
|
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
|
|
objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn Delete Failed';
|
|
|
|
objSpOutRes.strSqlCustomErrorType := 'DATABASE_SYSTEM_ERROR';
|
|
|
|
GET STACKED DIAGNOSTICS
|
|
objSpOutRes.strSqlSysMessage := MESSAGE_TEXT,
|
|
objSpOutRes.strSqlSysExceptionDetail := PG_EXCEPTION_DETAIL,
|
|
objSpOutRes.strSqlSysExceptionHint := PG_EXCEPTION_CONTEXT;
|
|
|
|
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
|
|
RAISE EXCEPTION 'jsnSqlRes: %', jsnSqlRes;
|
|
RETURN;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
