|
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
|
|
-- PAIDCHEQUE->TBL_CHEQUE_PRINT_SETTINGS TBL_CHEQUE_PRINT_SETTINGS TBL_CHEQUE_PRINT_SETTINGS TBL_CHEQUE_PRINT_SETTINGS
|
|
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
|
|
------------------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION fn_get_all_cheque_print_settings() RETURNS SETOF paidcheque.tbl_cheque_print_settings AS $$
|
|
DECLARE
|
|
|
|
BEGIN
|
|
RETURN QUERY SELECT * FROM paidcheque.tbl_cheque_print_settings WHERE int_sys_action_id != -1;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
------------------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION fn_get_all_cheque_print_settings_with_action_user()
|
|
RETURNS TABLE (pk_cheque_print_settings_id BIGINT,
|
|
|
|
vhr_cheque_print_name VARCHAR,
|
|
|
|
sin_payee_name_line1_x_cordinate SMALLINT,
|
|
sin_payee_name_line2_x_cordinate SMALLINT,
|
|
sin_date_x_cordinate SMALLINT,
|
|
sin_amount_x_cordinate SMALLINT,
|
|
sin_amount_in_words_line1_x_cordinate SMALLINT,
|
|
sin_amount_in_words_line2_x_cordinate SMALLINT,
|
|
|
|
sin_payee_name_line1_y_cordinate SMALLINT,
|
|
sin_payee_name_line2_y_cordinate SMALLINT,
|
|
sin_date_y_cordinate SMALLINT,
|
|
sin_amount_y_cordinate SMALLINT,
|
|
sin_amount_in_words_line1_y_cordinate SMALLINT,
|
|
sin_amount_in_words_line2_y_cordinate SMALLINT,
|
|
|
|
sin_payee_name_line1_characters SMALLINT,
|
|
sin_payee_name_line2_characters SMALLINT,
|
|
sin_date_characters SMALLINT,
|
|
sin_amount_characters SMALLINT,
|
|
sin_amount_in_words_line1_characters SMALLINT,
|
|
sin_amount_in_words_line2_characters SMALLINT,
|
|
|
|
sin_payee_name_line1_font_size SMALLINT,
|
|
sin_payee_name_line2_font_size SMALLINT,
|
|
sin_date_font_size SMALLINT,
|
|
sin_amount_font_size SMALLINT,
|
|
sin_amount_in_words_line1_font_size SMALLINT,
|
|
sin_amount_in_words_line2_font_size SMALLINT,
|
|
|
|
sin_payee_name_line1_bold SMALLINT,
|
|
sin_payee_name_line2_bold SMALLINT,
|
|
sin_date_bold SMALLINT,
|
|
sin_amount_bold SMALLINT,
|
|
sin_amount_in_words_line1_bold SMALLINT,
|
|
sin_amount_in_words_line2_bold SMALLINT,
|
|
|
|
vhr_payee_name_line1_value VARCHAR,
|
|
vhr_payee_name_line2_value VARCHAR,
|
|
vhr_date_value VARCHAR,
|
|
vhr_amount_value VARCHAR,
|
|
vhr_amount_in_words_line1_value VARCHAR,
|
|
vhr_amount_in_words_line2_value VARCHAR,
|
|
|
|
sin_ac_pay_x_cordinate SMALLINT,
|
|
sin_ac_pay_y_cordinate SMALLINT,
|
|
vhr_ac_pay_value VARCHAR,
|
|
sin_ac_pay_angle SMALLINT,
|
|
sin_ac_pay_font_size DEFAULT,
|
|
sin_ac_pay_bold SMALLINT) AS $$
|
|
DECLARE
|
|
|
|
BEGIN
|
|
RETURN QUERY SELECT mt.pk_cheque_print_settings_id,
|
|
|
|
mt.vhr_cheque_print_name,
|
|
|
|
mt.sin_payee_name_line1_x_cordinate,
|
|
mt.sin_payee_name_line2_x_cordinate,
|
|
mt.sin_date_x_cordinate,
|
|
mt.sin_amount_x_cordinate,
|
|
mt.sin_amount_in_words_line1_x_cordinate,
|
|
mt.sin_amount_in_words_line2_x_cordinate,
|
|
|
|
mt.sin_payee_name_line1_y_cordinate,
|
|
mt.sin_payee_name_line2_y_cordinate,
|
|
mt.sin_date_y_cordinate,
|
|
mt.sin_amount_y_cordinate,
|
|
mt.sin_amount_in_words_line1_y_cordinate,
|
|
mt.sin_amount_in_words_line2_y_cordinate,
|
|
|
|
mt.sin_payee_name_line1_characters,
|
|
mt.sin_payee_name_line2_characters,
|
|
mt.sin_date_characters,
|
|
mt.sin_amount_characters,
|
|
mt.sin_amount_in_words_line1_characters,
|
|
mt.sin_amount_in_words_line2_characters,
|
|
|
|
mt.sin_payee_name_line1_font_size,
|
|
mt.sin_payee_name_line2_font_size,
|
|
mt.sin_date_font_size,
|
|
mt.sin_amount_font_size,
|
|
mt.sin_amount_in_words_line1_font_size,
|
|
mt.sin_amount_in_words_line2_font_size,
|
|
|
|
mt.sin_payee_name_line1_bold,
|
|
mt.sin_payee_name_line2_bold,
|
|
mt.sin_date_bold,
|
|
mt.sin_amount_bold,
|
|
mt.sin_amount_in_words_line1_bold,
|
|
mt.sin_amount_in_words_line2_bold,
|
|
|
|
mt.vhr_payee_name_line1_value,
|
|
mt.vhr_payee_name_line2_value,
|
|
mt.vhr_date_value,
|
|
mt.vhr_amount_value,
|
|
mt.vhr_amount_in_words_line1_value,
|
|
mt.vhr_amount_in_words_line2_value,
|
|
|
|
mt.sin_ac_pay_x_cordinate,
|
|
mt.sin_ac_pay_y_cordinate,
|
|
mt.vhr_ac_pay_value,
|
|
mt.sin_ac_pay_angle,
|
|
mt.sin_ac_pay_font_size,
|
|
mt.sin_ac_pay_bold
|
|
FROM paidcheque.tbl_cheque_print_settings 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 vhr_cheque_print_name;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
------------------------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE PROCEDURE sp_insert_or_update_cheque_print_settings(jsnChequePrintSettings 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_cheque_print_settings'::VARCHAR, ''::VARCHAR, ::VARCHAR, 'pk_cheque_print_settings_id'::VARCHAR, CAST(jsnChequePrintSettings->>'binChequePrintSettingsId' 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(jsnChequePrintSettings->>'binChequePrintSettingsId' AS BIGINT)::BIGINT = 0::BIGINT THEN
|
|
-- // Count Exced or other checking License related in DB FUCTION(Insert)
|
|
|
|
|
|
--jsnChequePrintSettings.intSysActionId = 0;
|
|
--jsnChequePrintSettings.binCreatedUserId = jsnChequePrintSettings.linLoginUserId;
|
|
--jsnChequePrintSettings.dtmCreated = jsnChequePrintSettings.dtmCurrentDateTime; --SELECT NOW();
|
|
|
|
INSERT INTO paidcheque.tbl_cheque_print_settings (vhr_cheque_print_name,
|
|
|
|
sin_payee_name_line1_x_cordinate,
|
|
sin_payee_name_line2_x_cordinate,
|
|
sin_date_x_cordinate,
|
|
sin_amount_x_cordinate,
|
|
sin_amount_in_words_line1_x_cordinate,
|
|
sin_amount_in_words_line2_x_cordinate,
|
|
|
|
sin_payee_name_line1_y_cordinate,
|
|
sin_payee_name_line2_y_cordinate,
|
|
sin_date_y_cordinate,
|
|
sin_amount_y_cordinate,
|
|
sin_amount_in_words_line1_y_cordinate,
|
|
sin_amount_in_words_line2_y_cordinate,
|
|
|
|
sin_payee_name_line1_characters,
|
|
sin_payee_name_line2_characters,
|
|
sin_date_characters,
|
|
sin_amount_characters,
|
|
sin_amount_in_words_line1_characters,
|
|
sin_amount_in_words_line2_characters,
|
|
|
|
sin_payee_name_line1_font_size,
|
|
sin_payee_name_line2_font_size,
|
|
sin_date_font_size,
|
|
sin_amount_font_size,
|
|
sin_amount_in_words_line1_font_size,
|
|
sin_amount_in_words_line2_font_size,
|
|
|
|
sin_payee_name_line1_bold,
|
|
sin_payee_name_line2_bold,
|
|
sin_date_bold,
|
|
sin_amount_bold,
|
|
sin_amount_in_words_line1_bold,
|
|
sin_amount_in_words_line2_bold,
|
|
|
|
vhr_payee_name_line1_value,
|
|
vhr_payee_name_line2_value,
|
|
vhr_date_value,
|
|
vhr_amount_value,
|
|
vhr_amount_in_words_line1_value,
|
|
vhr_amount_in_words_line2_value,
|
|
|
|
sin_ac_pay_x_cordinate,
|
|
sin_ac_pay_y_cordinate,
|
|
vhr_ac_pay_value,
|
|
sin_ac_pay_angle,
|
|
sin_ac_pay_font_size,
|
|
sin_ac_pay_bold)
|
|
VALUES(jsnChequePrintSettings->>'strChequePrintName',
|
|
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine1XCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine2XCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinDateXCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountXCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1XCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2XCordinate' AS SMALLINT),
|
|
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine1YCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine2YCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinDateYCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountYCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1YCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2YCordinate' AS SMALLINT),
|
|
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine1Characters' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine2Characters' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinDateCharacters' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountCharacters' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1Characters' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2Characters' AS SMALLINT),
|
|
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine1FontSize' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine2FontSize' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinDateFontSize' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountFontSize' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1FontSize' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2FontSize' AS SMALLINT),
|
|
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine1Bold' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinPayeeNameLine2Bold' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinDateBold' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountBold' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1Bold' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2Bold' AS SMALLINT),
|
|
|
|
jsnChequePrintSettings->>'strPayeeNameLine1Value',
|
|
jsnChequePrintSettings->>'strPayeeNameLine2Value',
|
|
jsnChequePrintSettings->>'strDateValue',
|
|
jsnChequePrintSettings->>'strAmountValue',
|
|
jsnChequePrintSettings->>'strAmountInWordsLine1Value',
|
|
jsnChequePrintSettings->>'strAmountInWordsLine2Value',
|
|
|
|
CAST(jsnChequePrintSettings->>'sinAcPayXCordinate' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAcPayYCordinate' AS SMALLINT),
|
|
jsnChequePrintSettings->>'strAcPayValue',
|
|
CAST(jsnChequePrintSettings->>'sinAcPayAngle' AS SMALLINT),
|
|
CAST(jsnChequePrintSettings->>'sinAcPayFontSize' AS DEFAULT),
|
|
CAST(jsnChequePrintSettings->>'sinAcPayBold' AS SMALLINT)) RETURNING pk_cheque_print_settings_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_cheque_print_settings'::VARCHAR, 'pk_cheque_print_settings_id'::VARCHAR, CAST(jsnChequePrintSettings->>'binChequePrintSettingsId' AS BIGINT)::BIGINT, CAST(jsnChequePrintSettings->>'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;
|
|
|
|
--jsnChequePrintSettings.intSysActionId = jsnChequePrintSettings.intSysActionId + 1;
|
|
--jsnChequePrintSettings.binLastModifiedUserId = jsnChequePrintSettings.linLoginUserId;
|
|
--jsnChequePrintSettings.dtmLastModified = jsnChequePrintSettings.dtmCurrentDateTime; --SELECT NOW();
|
|
|
|
UPDATE paidcheque.tbl_cheque_print_settings SET vhr_cheque_print_name = jsnChequePrintSettings->>'strChequePrintName',
|
|
|
|
sin_payee_name_line1_x_cordinate = CAST(jsnChequePrintSettings->>'sinPayeeNameLine1XCordinate' AS SMALLINT),
|
|
sin_payee_name_line2_x_cordinate = CAST(jsnChequePrintSettings->>'sinPayeeNameLine2XCordinate' AS SMALLINT),
|
|
sin_date_x_cordinate = CAST(jsnChequePrintSettings->>'sinDateXCordinate' AS SMALLINT),
|
|
sin_amount_x_cordinate = CAST(jsnChequePrintSettings->>'sinAmountXCordinate' AS SMALLINT),
|
|
sin_amount_in_words_line1_x_cordinate = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1XCordinate' AS SMALLINT),
|
|
sin_amount_in_words_line2_x_cordinate = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2XCordinate' AS SMALLINT),
|
|
|
|
sin_payee_name_line1_y_cordinate = CAST(jsnChequePrintSettings->>'sinPayeeNameLine1YCordinate' AS SMALLINT),
|
|
sin_payee_name_line2_y_cordinate = CAST(jsnChequePrintSettings->>'sinPayeeNameLine2YCordinate' AS SMALLINT),
|
|
sin_date_y_cordinate = CAST(jsnChequePrintSettings->>'sinDateYCordinate' AS SMALLINT),
|
|
sin_amount_y_cordinate = CAST(jsnChequePrintSettings->>'sinAmountYCordinate' AS SMALLINT),
|
|
sin_amount_in_words_line1_y_cordinate = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1YCordinate' AS SMALLINT),
|
|
sin_amount_in_words_line2_y_cordinate = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2YCordinate' AS SMALLINT),
|
|
|
|
sin_payee_name_line1_characters = CAST(jsnChequePrintSettings->>'sinPayeeNameLine1Characters' AS SMALLINT),
|
|
sin_payee_name_line2_characters = CAST(jsnChequePrintSettings->>'sinPayeeNameLine2Characters' AS SMALLINT),
|
|
sin_date_characters = CAST(jsnChequePrintSettings->>'sinDateCharacters' AS SMALLINT),
|
|
sin_amount_characters = CAST(jsnChequePrintSettings->>'sinAmountCharacters' AS SMALLINT),
|
|
sin_amount_in_words_line1_characters = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1Characters' AS SMALLINT),
|
|
sin_amount_in_words_line2_characters = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2Characters' AS SMALLINT),
|
|
|
|
sin_payee_name_line1_font_size = CAST(jsnChequePrintSettings->>'sinPayeeNameLine1FontSize' AS SMALLINT),
|
|
sin_payee_name_line2_font_size = CAST(jsnChequePrintSettings->>'sinPayeeNameLine2FontSize' AS SMALLINT),
|
|
sin_date_font_size = CAST(jsnChequePrintSettings->>'sinDateFontSize' AS SMALLINT),
|
|
sin_amount_font_size = CAST(jsnChequePrintSettings->>'sinAmountFontSize' AS SMALLINT),
|
|
sin_amount_in_words_line1_font_size = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1FontSize' AS SMALLINT),
|
|
sin_amount_in_words_line2_font_size = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2FontSize' AS SMALLINT),
|
|
|
|
sin_payee_name_line1_bold = CAST(jsnChequePrintSettings->>'sinPayeeNameLine1Bold' AS SMALLINT),
|
|
sin_payee_name_line2_bold = CAST(jsnChequePrintSettings->>'sinPayeeNameLine2Bold' AS SMALLINT),
|
|
sin_date_bold = CAST(jsnChequePrintSettings->>'sinDateBold' AS SMALLINT),
|
|
sin_amount_bold = CAST(jsnChequePrintSettings->>'sinAmountBold' AS SMALLINT),
|
|
sin_amount_in_words_line1_bold = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine1Bold' AS SMALLINT),
|
|
sin_amount_in_words_line2_bold = CAST(jsnChequePrintSettings->>'sinAmountInWordsLine2Bold' AS SMALLINT),
|
|
|
|
vhr_payee_name_line1_value = jsnChequePrintSettings->>'strPayeeNameLine1Value',
|
|
vhr_payee_name_line2_value = jsnChequePrintSettings->>'strPayeeNameLine2Value',
|
|
vhr_date_value = jsnChequePrintSettings->>'strDateValue',
|
|
vhr_amount_value = jsnChequePrintSettings->>'strAmountValue',
|
|
vhr_amount_in_words_line1_value = jsnChequePrintSettings->>'strAmountInWordsLine1Value',
|
|
vhr_amount_in_words_line2_value = jsnChequePrintSettings->>'strAmountInWordsLine2Value',
|
|
|
|
sin_ac_pay_x_cordinate = CAST(jsnChequePrintSettings->>'sinAcPayXCordinate' AS SMALLINT),
|
|
sin_ac_pay_y_cordinate = CAST(jsnChequePrintSettings->>'sinAcPayYCordinate' AS SMALLINT),
|
|
vhr_ac_pay_value = jsnChequePrintSettings->>'strAcPayValue',
|
|
sin_ac_pay_angle = CAST(jsnChequePrintSettings->>'sinAcPayAngle' AS SMALLINT),
|
|
sin_ac_pay_font_size = CAST(jsnChequePrintSettings->>'sinAcPayFontSize' AS DEFAULT),
|
|
sin_ac_pay_bold = CAST(jsnChequePrintSettings->>'sinAcPayBold' AS SMALLINT)
|
|
WHERE pk_cheque_print_settings_id = CAST(jsnChequePrintSettings->>'binChequePrintSettingsId' AS BIGINT);
|
|
END IF;
|
|
|
|
--COMMIT;
|
|
objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
|
|
objSpOutRes.strSqlCustomMessage := 'ChequePrintSettings 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 := 'ChequePrintSettings 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_cheque_print_settings(binChequePrintSettingsId 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_cheque_print_settings'::VARCHAR, 'pk_cheque_print_settings_id'::VARCHAR, binChequePrintSettingsId::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_cheque_print_settings'::VARCHAR, 'pk_cheque_print_settings_id'::VARCHAR, CAST(binChequePrintSettingsId 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_cheque_print_settings SET int_sys_action_id = -1::INT,
|
|
fk_deleted_user_id = binDeletedUserId,
|
|
dtm_deleted = dtmCurrent
|
|
WHERE pk_cheque_print_settings_id = binChequePrintSettingsId;
|
|
|
|
--COMMIT;
|
|
objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
|
|
objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
|
|
objSpOutRes.strSqlCustomMessage := 'ChequePrintSettings 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 := 'ChequePrintSettings 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;
|
|
|
|
|
