-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 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;