Project

General

Profile

Feature #268 » sql.sql

Junaid M, 03/07/2024 06:09 PM

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

(2-2/10)