-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- CORPORATE->TBL_CORPORATE_BOOKING_RULE TBL_CORPORATE_BOOKING_RULE TBL_CORPORATE_BOOKING_RULE TBL_CORPORATE_BOOKING_RULE -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ------------------------------------------------------------------------------------------ CREATE OR REPLACE FUNCTION fn_get_all_corporate_booking_rule() RETURNS SETOF corporate.tbl_corporate_booking_rule AS $$ DECLARE BEGIN RETURN QUERY SELECT * FROM corporate.tbl_corporate_booking_rule WHERE int_sys_action_id != -1; END; $$ LANGUAGE plpgsql; ------------------------------------------------------------------------------------------ CREATE OR REPLACE FUNCTION fn_get_all_corporate_booking_rule_with_action_user() RETURNS TABLE (pk_corporate_booking_rule_id BIGINT, vhr_rule_name VARCHAR, fk_customer_id BIGINT, sin_allowed_all_customers SMALLINT, txt_remarks TEXT, sin_record_status SMALLINT, dbl_air_slab_from DOUBLE PRECISION, dbl_air_slab_to DOUBLE PRECISION, sin_oneway_trip SMALLINT, sin_round_trip SMALLINT, sin_multi_trip SMALLINT, sin_if_exclude_cabin_class SMALLINT, arb_fk_cabin_class BIGINT[], art_booking_class_chrs TEXT[], sin_if_exclude_booking_class_chrs SMALLINT, sin_if_exclude_source_airport SMALLINT, arb_fk_source_airport_ids BIGINT[], sin_if_exclude_destination_airport SMALLINT, arb_fk_destination_airport_ids BIGINT[], sin_code_sharing_type SMALLINT, arb_fk_code_sharing_airline_master_ids BIGINT[], arb_fk_code_sharing_airline_category_ids BIGINT[], arb_fk_code_sharing_airline_group_ids BIGINT[], arb_fk_code_sharing_airline_type_ids BIGINT[], arb_fk_code_sharing_airline_family_ids BIGINT[], sin_no_of_stops SMALLINT, arb_fk_ticket_type_ids BIGINT[], sin_if_exclude_airline SMALLINT, arb_fk_airline_master_ids BIGINT[], arb_fk_airline_category_ids BIGINT[], arb_fk_airline_group_ids BIGINT[], arb_fk_airline_type_ids BIGINT[], arb_fk_airline_family_ids BIGINT[], dbl_hotel_slab_from DOUBLE PRECISION, dbl_hotel_slab_to DOUBLE PRECISION, sin_star_rate SMALLINT, sin_if_exclude_hotel SMALLINT, arb_fk_hotel_master_ids BIGINT[], arb_fk_hotel_category_ids BIGINT[], arb_fk_hotel_group_ids BIGINT[], arb_fk_hotel_type_ids BIGINT[], arb_fk_hotel_family_ids BIGINT[], arb_fk_room_type_ids BIGINT[], arb_fk_meals_plan_ids BIGINT[], 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_corporate_booking_rule_id, mt.vhr_rule_name, mt.fk_customer_id, mt.sin_allowed_all_customers, mt.txt_remarks, mt.sin_record_status, mt.dbl_air_slab_from, mt.dbl_air_slab_to, mt.sin_oneway_trip, mt.sin_round_trip, mt.sin_multi_trip, mt.sin_if_exclude_cabin_class, mt.arb_fk_cabin_class, mt.art_booking_class_chrs, mt.sin_if_exclude_booking_class_chrs, mt.sin_if_exclude_source_airport, mt.arb_fk_source_airport_ids, mt.sin_if_exclude_destination_airport, mt.arb_fk_destination_airport_ids, mt.sin_code_sharing_type, mt.arb_fk_code_sharing_airline_master_ids, mt.arb_fk_code_sharing_airline_category_ids, mt.arb_fk_code_sharing_airline_group_ids, mt.arb_fk_code_sharing_airline_type_ids, mt.arb_fk_code_sharing_airline_family_ids, mt.sin_no_of_stops, mt.arb_fk_ticket_type_ids, mt.sin_if_exclude_airline, mt.arb_fk_airline_master_ids, mt.arb_fk_airline_category_ids, mt.arb_fk_airline_group_ids, mt.arb_fk_airline_type_ids, mt.arb_fk_airline_family_ids, mt.dbl_hotel_slab_from, mt.dbl_hotel_slab_to, mt.sin_star_rate, mt.sin_if_exclude_hotel, mt.arb_fk_hotel_master_ids, mt.arb_fk_hotel_category_ids, mt.arb_fk_hotel_group_ids, mt.arb_fk_hotel_type_ids, mt.arb_fk_hotel_family_ids, mt.arb_fk_room_type_ids, mt.arb_fk_meals_plan_ids, 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 corporate.tbl_corporate_booking_rule 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_rule_name; END; $$ LANGUAGE plpgsql; ------------------------------------------------------------------------------------------ CREATE OR REPLACE PROCEDURE sp_insert_or_update_corporate_booking_rule(jsnCorporateBookingRule 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('corporate.tbl_corporate_booking_rule'::VARCHAR, 'sin_code_sharing_type'::VARCHAR, jsnCorporateBookingRule->>'sinCodeSharingType'::VARCHAR, 'pk_corporate_booking_rule_id'::VARCHAR, CAST(jsnCorporateBookingRule->>'binCorporateBookingRuleId' 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(jsnCorporateBookingRule->>'binCorporateBookingRuleId' AS BIGINT)::BIGINT = 0::BIGINT THEN -- // Count Exced or other checking License related in DB FUCTION(Insert) --jsnCorporateBookingRule.intSysActionId = 0; --jsnCorporateBookingRule.binCreatedUserId = jsnCorporateBookingRule.linLoginUserId; --jsnCorporateBookingRule.dtmCreated = jsnCorporateBookingRule.dtmCurrentDateTime; --SELECT NOW(); INSERT INTO corporate.tbl_corporate_booking_rule (vhr_rule_name, fk_customer_id, sin_allowed_all_customers, txt_remarks, sin_record_status, dbl_air_slab_from, dbl_air_slab_to, sin_oneway_trip, sin_round_trip, sin_multi_trip, sin_if_exclude_cabin_class, arb_fk_cabin_class, art_booking_class_chrs, sin_if_exclude_booking_class_chrs, sin_if_exclude_source_airport, arb_fk_source_airport_ids, sin_if_exclude_destination_airport, arb_fk_destination_airport_ids, sin_code_sharing_type, arb_fk_code_sharing_airline_master_ids, arb_fk_code_sharing_airline_category_ids, arb_fk_code_sharing_airline_group_ids, arb_fk_code_sharing_airline_type_ids, arb_fk_code_sharing_airline_family_ids, sin_no_of_stops, arb_fk_ticket_type_ids, sin_if_exclude_airline, arb_fk_airline_master_ids, arb_fk_airline_category_ids, arb_fk_airline_group_ids, arb_fk_airline_type_ids, arb_fk_airline_family_ids, dbl_hotel_slab_from, dbl_hotel_slab_to, sin_star_rate, sin_if_exclude_hotel, arb_fk_hotel_master_ids, arb_fk_hotel_category_ids, arb_fk_hotel_group_ids, arb_fk_hotel_type_ids, arb_fk_hotel_family_ids, arb_fk_room_type_ids, arb_fk_meals_plan_ids, int_sys_action_id, fk_created_user_id, dtm_created) VALUES(jsnCorporateBookingRule->>'strRuleName', CAST(jsnCorporateBookingRule->>'binCustomerId' AS BIGINT), CAST(jsnCorporateBookingRule->>'sinAllowedAllCustomers' AS SMALLINT), CAST(jsnCorporateBookingRule->>'strRemarks' AS TEXT), CAST(jsnCorporateBookingRule->>'sinRecordStatus' AS SMALLINT), CAST(jsnCorporateBookingRule->>'dblAirSlabFrom' AS DOUBLE PRECISION), CAST(jsnCorporateBookingRule->>'dblAirSlabTo' AS DOUBLE PRECISION), CAST(jsnCorporateBookingRule->>'sinOnewayTrip' AS SMALLINT), CAST(jsnCorporateBookingRule->>'sinRoundTrip' AS SMALLINT), CAST(jsnCorporateBookingRule->>'sinMultiTrip' AS SMALLINT), CAST(jsnCorporateBookingRule->>'sinIfExcludeCabinClass' AS SMALLINT), STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCabinClass', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArtBookingClassChrs', ',')::TEXT[], CAST(jsnCorporateBookingRule->>'sinIfExcludeBookingClassChrs' AS SMALLINT), CAST(jsnCorporateBookingRule->>'sinIfExcludeSourceAirport' AS SMALLINT), STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkSourceAirportIds', ',')::BIGINT[], CAST(jsnCorporateBookingRule->>'sinIfExcludeDestinationAirport' AS SMALLINT), STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkDestinationAirportIds', ',')::BIGINT[], CAST(jsnCorporateBookingRule->>'sinCodeSharingType' AS SMALLINT), STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineMasterIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineCategoryIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineGroupIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineTypeIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineFamilyIds', ',')::BIGINT[], CAST(jsnCorporateBookingRule->>'sinNoOfStops' AS SMALLINT), STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkTicketTypeIds', ',')::BIGINT[], CAST(jsnCorporateBookingRule->>'sinIfExcludeAirline' AS SMALLINT), STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineMasterIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineCategoryIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineGroupIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineTypeIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineFamilyIds', ',')::BIGINT[], CAST(jsnCorporateBookingRule->>'dblHotelSlabFrom' AS DOUBLE PRECISION), CAST(jsnCorporateBookingRule->>'dblHotelSlabTo' AS DOUBLE PRECISION), CAST(jsnCorporateBookingRule->>'sinStarRate' AS SMALLINT), CAST(jsnCorporateBookingRule->>'sinIfExcludeHotel' AS SMALLINT), STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelMasterIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelCategoryIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelGroupIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelTypeIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelFamilyIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkRoomTypeIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkMealsPlanIds', ',')::BIGINT[], 0, CAST(jsnCorporateBookingRule->>'binCreatedUserId' AS BIGINT), dtmCurrent) RETURNING pk_corporate_booking_rule_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('corporate.tbl_corporate_booking_rule'::VARCHAR, 'pk_corporate_booking_rule_id'::VARCHAR, CAST(jsnCorporateBookingRule->>'binCorporateBookingRuleId' AS BIGINT)::BIGINT, CAST(jsnCorporateBookingRule->>'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; --jsnCorporateBookingRule.intSysActionId = jsnCorporateBookingRule.intSysActionId + 1; --jsnCorporateBookingRule.binLastModifiedUserId = jsnCorporateBookingRule.linLoginUserId; --jsnCorporateBookingRule.dtmLastModified = jsnCorporateBookingRule.dtmCurrentDateTime; --SELECT NOW(); UPDATE corporate.tbl_corporate_booking_rule SET vhr_rule_name = jsnCorporateBookingRule->>'strRuleName', fk_customer_id = CAST(jsnCorporateBookingRule->>'binCustomerId' AS BIGINT), sin_allowed_all_customers = CAST(jsnCorporateBookingRule->>'sinAllowedAllCustomers' AS SMALLINT), txt_remarks = jsnCorporateBookingRule->>'strRemarks', sin_record_status = CAST(jsnCorporateBookingRule->>'sinRecordStatus' AS SMALLINT), dbl_air_slab_from = CAST(jsnCorporateBookingRule->>'dblAirSlabFrom' AS DOUBLE PRECISION), dbl_air_slab_to = CAST(jsnCorporateBookingRule->>'dblAirSlabTo' AS DOUBLE PRECISION), sin_oneway_trip = CAST(jsnCorporateBookingRule->>'sinOnewayTrip' AS SMALLINT), sin_round_trip = CAST(jsnCorporateBookingRule->>'sinRoundTrip' AS SMALLINT), sin_multi_trip = CAST(jsnCorporateBookingRule->>'sinMultiTrip' AS SMALLINT), sin_if_exclude_cabin_class = CAST(jsnCorporateBookingRule->>'sinIfExcludeCabinClass' AS SMALLINT), arb_fk_cabin_class = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCabinClass', ',')::BIGINT[], art_booking_class_chrs = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArtBookingClassChrs', ',')::TEXT[], sin_if_exclude_booking_class_chrs = CAST(jsnCorporateBookingRule->>'sinIfExcludeBookingClassChrs' AS SMALLINT), sin_if_exclude_source_airport = CAST(jsnCorporateBookingRule->>'sinIfExcludeSourceAirport' AS SMALLINT), arb_fk_source_airport_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkSourceAirportIds', ',')::BIGINT[], sin_if_exclude_destination_airport = CAST(jsnCorporateBookingRule->>'sinIfExcludeDestinationAirport' AS SMALLINT), arb_fk_destination_airport_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkDestinationAirportIds', ',')::BIGINT[], sin_code_sharing_type = CAST(jsnCorporateBookingRule->>'sinCodeSharingType' AS SMALLINT), arb_fk_code_sharing_airline_master_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineMasterIds', ',')::BIGINT[], arb_fk_code_sharing_airline_category_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineCategoryIds', ',')::BIGINT[], arb_fk_code_sharing_airline_group_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineGroupIds', ',')::BIGINT[], arb_fk_code_sharing_airline_type_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineTypeIds', ',')::BIGINT[], arb_fk_code_sharing_airline_family_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkCodeSharingAirlineFamilyIds', ',')::BIGINT[], sin_no_of_stops = CAST(jsnCorporateBookingRule->>'sinNoOfStops' AS SMALLINT), arb_fk_ticket_type_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkTicketTypeIds', ',')::BIGINT[], sin_if_exclude_airline = CAST(jsnCorporateBookingRule->>'sinIfExcludeAirline' AS SMALLINT), arb_fk_airline_master_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineMasterIds', ',')::BIGINT[], arb_fk_airline_category_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineCategoryIds', ',')::BIGINT[], arb_fk_airline_group_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineGroupIds', ',')::BIGINT[], arb_fk_airline_type_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineTypeIds', ',')::BIGINT[], arb_fk_airline_family_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkAirlineFamilyIds', ',')::BIGINT[], dbl_hotel_slab_from = CAST(jsnCorporateBookingRule->>'dblHotelSlabFrom' AS DOUBLE PRECISION), dbl_hotel_slab_to = CAST(jsnCorporateBookingRule->>'dblHotelSlabTo' AS DOUBLE PRECISION), sin_star_rate = CAST(jsnCorporateBookingRule->>'sinStarRate' AS SMALLINT), sin_if_exclude_hotel = CAST(jsnCorporateBookingRule->>'sinIfExcludeHotel' AS SMALLINT), arb_fk_hotel_master_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelMasterIds', ',')::BIGINT[], arb_fk_hotel_category_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelCategoryIds', ',')::BIGINT[], arb_fk_hotel_group_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelGroupIds', ',')::BIGINT[], arb_fk_hotel_type_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelTypeIds', ',')::BIGINT[], arb_fk_hotel_family_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkHotelFamilyIds', ',')::BIGINT[], arb_fk_room_type_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkRoomTypeIds', ',')::BIGINT[], arb_fk_meals_plan_ids = STRING_TO_ARRAY(jsnCorporateBookingRule->>'strDbArlFkMealsPlanIds', ',')::BIGINT[], int_sys_action_id = int_sys_action_id + 1, fk_last_modified_user_id = CAST(jsnCorporateBookingRule->>'binLastModifiedUserId' AS BIGINT), dtm_last_modified = dtmCurrent WHERE pk_corporate_booking_rule_id = CAST(jsnCorporateBookingRule->>'binCorporateBookingRuleId' AS BIGINT); END IF; --COMMIT; objSpOutRes.sinSqlCustomStatus := 1::SMALLINT; objSpOutRes.strSqlCustomStatusCode := 'SUCCESS'; objSpOutRes.strSqlCustomMessage := 'CorporateBookingRule 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 := 'CorporateBookingRule 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_corporate_booking_rule(binCorporateBookingRuleId 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_corporate_booking_rule'::VARCHAR, 'pk_corporate_booking_rule_id'::VARCHAR, binCorporateBookingRuleId::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('corporate.tbl_corporate_booking_rule'::VARCHAR, 'pk_corporate_booking_rule_id'::VARCHAR, CAST(binCorporateBookingRuleId 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 corporate.tbl_corporate_booking_rule SET int_sys_action_id = -1::INT, fk_deleted_user_id = binDeletedUserId, dtm_deleted = dtmCurrent WHERE pk_corporate_booking_rule_id = binCorporateBookingRuleId; --COMMIT; objSpOutRes.sinSqlCustomStatus := 1::SMALLINT; objSpOutRes.strSqlCustomStatusCode := 'SUCCESS'; objSpOutRes.strSqlCustomMessage := 'CorporateBookingRule 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 := 'CorporateBookingRule 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;