-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- GDSLCC->TBL_GDS_CUSTOM_FORMAT TBL_GDS_CUSTOM_FORMAT TBL_GDS_CUSTOM_FORMAT TBL_GDS_CUSTOM_FORMAT -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ------------------------------------------------------------------------------------------ CREATE OR REPLACE FUNCTION fn_get_all_gds_custom_format() RETURNS SETOF gdslcc.tbl_gds_custom_format AS $$ DECLARE BEGIN RETURN QUERY SELECT * FROM gdslcc.tbl_gds_custom_format WHERE int_sys_action_id != -1; END; $$ LANGUAGE plpgsql; ------------------------------------------------------------------------------------------ CREATE OR REPLACE FUNCTION fn_get_all_gds_custom_format_with_action_user() RETURNS TABLE (pk_gds_custom_format_id BIGINT, sin_category SMALLINT, sin_sub_category SMALLINT, vhr_sys_attribute_name VARCHAR, fk_corporate_custom_data_id BIGINT, fk_service_custom_data_id BIGINT, vhr_amadeus_format VARCHAR, vhr_galileo_format VARCHAR, vhr_sabre_format VARCHAR, vhr_worldspan_format VARCHAR, vhr_farelogix_format VARCHAR, vhr_abacus_format VARCHAR, vhr_travelport_format VARCHAR, int_sys_action_id INT, fk_created_user_id BIGINT, vhr_created_user VARCHAR, dtm_created TIMESTAMP WITH TIME ZONE, fk_last_modified_user_id BIGINT, vhr_last_modified_user VARCHAR, dtm_last_modified TIMESTAMP WITH TIME ZONE, fk_deleted_user_id BIGINT, vhr_deleted_user VARCHAR, dtm_deleted TIMESTAMP WITH TIME ZONE) AS $$ DECLARE BEGIN RETURN QUERY SELECT mt.pk_gds_custom_format_id, mt.sin_category, mt.sin_sub_category, mt.vhr_sys_attribute_name, mt.fk_corporate_custom_data_id, mt.fk_service_custom_data_id, mt.vhr_amadeus_format, mt.vhr_galileo_format, mt.vhr_sabre_format, mt.vhr_worldspan_format, mt.vhr_farelogix_format, mt.vhr_abacus_format, mt.vhr_travelport_format, mt.int_sys_action_id, mt.fk_created_user_id, u1.vhr_login_name AS vhr_created_user, mt.dtm_created, mt.fk_last_modified_user_id, u2.vhr_login_name AS vhr_last_modified_user, mt.dtm_last_modified, mt.fk_deleted_user_id, u3.vhr_login_name AS vhr_deleted_user, mt.dtm_deleted FROM gdslcc.tbl_gds_custom_format 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_sys_attribute_name; END; $$ LANGUAGE plpgsql; ------------------------------------------------------------------------------------------ CREATE OR REPLACE PROCEDURE sp_insert_or_update_gds_custom_format(jsnGdsCustomFormat 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('gdslcc.tbl_gds_custom_format'::VARCHAR, ''::VARCHAR, ::VARCHAR, 'pk_gds_custom_format_id'::VARCHAR, CAST(jsnGdsCustomFormat->>'binGdsCustomFormatId' 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(jsnGdsCustomFormat->>'binGdsCustomFormatId' AS BIGINT)::BIGINT = 0::BIGINT THEN -- // Count Exced or other checking License related in DB FUCTION(Insert) --jsnGdsCustomFormat.intSysActionId = 0; --jsnGdsCustomFormat.binCreatedUserId = jsnGdsCustomFormat.linLoginUserId; --jsnGdsCustomFormat.dtmCreated = jsnGdsCustomFormat.dtmCurrentDateTime; --SELECT NOW(); INSERT INTO gdslcc.tbl_gds_custom_format (sin_category, sin_sub_category, vhr_sys_attribute_name, fk_corporate_custom_data_id, fk_service_custom_data_id, vhr_amadeus_format, vhr_galileo_format, vhr_sabre_format, vhr_worldspan_format, vhr_farelogix_format, vhr_abacus_format, vhr_travelport_format, int_sys_action_id, fk_created_user_id, dtm_created) VALUES(CAST(jsnGdsCustomFormat->>'sinCategory' AS SMALLINT), CAST(jsnGdsCustomFormat->>'sinSubCategory' AS SMALLINT), jsnGdsCustomFormat->>'strSysAttributeName', CAST(jsnGdsCustomFormat->>'binCorporateCustomDataId' AS BIGINT), CAST(jsnGdsCustomFormat->>'binServiceCustomDataId' AS BIGINT), jsnGdsCustomFormat->>'strAmadeusFormat', jsnGdsCustomFormat->>'strGalileoFormat', jsnGdsCustomFormat->>'strSabreFormat', jsnGdsCustomFormat->>'strWorldspanFormat', jsnGdsCustomFormat->>'strFarelogixFormat', jsnGdsCustomFormat->>'strAbacusFormat', jsnGdsCustomFormat->>'strTravelportFormat', 0, CAST(jsnGdsCustomFormat->>'binCreatedUserId' AS BIGINT), dtmCurrent) RETURNING pk_gds_custom_format_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('gdslcc.tbl_gds_custom_format'::VARCHAR, 'pk_gds_custom_format_id'::VARCHAR, CAST(jsnGdsCustomFormat->>'binGdsCustomFormatId' AS BIGINT)::BIGINT, CAST(jsnGdsCustomFormat->>'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; --jsnGdsCustomFormat.intSysActionId = jsnGdsCustomFormat.intSysActionId + 1; --jsnGdsCustomFormat.binLastModifiedUserId = jsnGdsCustomFormat.linLoginUserId; --jsnGdsCustomFormat.dtmLastModified = jsnGdsCustomFormat.dtmCurrentDateTime; --SELECT NOW(); UPDATE gdslcc.tbl_gds_custom_format SET sin_category = CAST(jsnGdsCustomFormat->>'sinCategory' AS SMALLINT), sin_sub_category = CAST(jsnGdsCustomFormat->>'sinSubCategory' AS SMALLINT), vhr_sys_attribute_name = jsnGdsCustomFormat->>'strSysAttributeName', fk_corporate_custom_data_id = CAST(jsnGdsCustomFormat->>'binCorporateCustomDataId' AS BIGINT), fk_service_custom_data_id = CAST(jsnGdsCustomFormat->>'binServiceCustomDataId' AS BIGINT), vhr_amadeus_format = jsnGdsCustomFormat->>'strAmadeusFormat', vhr_galileo_format = jsnGdsCustomFormat->>'strGalileoFormat', vhr_sabre_format = jsnGdsCustomFormat->>'strSabreFormat', vhr_worldspan_format = jsnGdsCustomFormat->>'strWorldspanFormat', vhr_farelogix_format = jsnGdsCustomFormat->>'strFarelogixFormat', vhr_abacus_format = jsnGdsCustomFormat->>'strAbacusFormat', vhr_travelport_format = jsnGdsCustomFormat->>'strTravelportFormat', int_sys_action_id = int_sys_action_id + 1, fk_last_modified_user_id = CAST(jsnGdsCustomFormat->>'binLastModifiedUserId' AS BIGINT), dtm_last_modified = dtmCurrent WHERE pk_gds_custom_format_id = CAST(jsnGdsCustomFormat->>'binGdsCustomFormatId' AS BIGINT); END IF; --COMMIT; objSpOutRes.sinSqlCustomStatus := 1::SMALLINT; objSpOutRes.strSqlCustomStatusCode := 'SUCCESS'; objSpOutRes.strSqlCustomMessage := 'GdsCustomFormat 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 := 'GdsCustomFormat 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_gds_custom_format(binGdsCustomFormatId 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_gds_custom_format'::VARCHAR, 'pk_gds_custom_format_id'::VARCHAR, binGdsCustomFormatId::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('gdslcc.tbl_gds_custom_format'::VARCHAR, 'pk_gds_custom_format_id'::VARCHAR, CAST(binGdsCustomFormatId 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 gdslcc.tbl_gds_custom_format SET int_sys_action_id = -1::INT, fk_deleted_user_id = binDeletedUserId, dtm_deleted = dtmCurrent WHERE pk_gds_custom_format_id = binGdsCustomFormatId; --COMMIT; objSpOutRes.sinSqlCustomStatus := 1::SMALLINT; objSpOutRes.strSqlCustomStatusCode := 'SUCCESS'; objSpOutRes.strSqlCustomMessage := 'GdsCustomFormat 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 := 'GdsCustomFormat 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;