-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- @@@@@@@@@@@@@@@@@ TARGET->TBL_SET_TARGET TBL_SET_TARGET TBL_SET_TARGET @@@@@@@@@@@@@@@@@ -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ------------------------------------------------------------------------------------------ CREATE OR REPLACE FUNCTION fn_get_all_set_target() RETURNS SETOF target.tbl_set_target AS $$ DECLARE BEGIN RETURN QUERY SELECT * FROM target.tbl_set_target WHERE int_sys_action_id != -1; END; $$ LANGUAGE plpgsql; ------------------------------------------------------------------------------------------ CREATE OR REPLACE FUNCTION fn_get_all_set_target_with_action_user() RETURNS TABLE (pk_set_target_id BIGINT, sin_target_type SMALLINT, fk_account_id BIGINT, fk_counter_staff_id BIGINT, fk_branch_id BIGINT, fk_airline_master_id BIGINT, fk_hotel_master_id BIGINT, fk_financial_year_id BIGINT, dbl_revenue_target DOUBLE PRECISION, vhr_revenue_target_based_on VARCHAR, dbl_yield_target DOUBLE PRECISION, vhr_yield_target_based_on VARCHAR, arb_consider_fk_branch_ids BIGINT[], arb_consider_fk_service_ids BIGINT[], arb_fk_package_category_ids BIGINT[], sin_less_refund SMALLINT, sin_splitup_type SMALLINT, sin_month_wise_splitup SMALLINT, 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_set_target_id, mt.sin_target_type, mt.fk_account_id, mt.fk_counter_staff_id, mt.fk_branch_id, mt.fk_airline_master_id, mt.fk_hotel_master_id, mt.fk_financial_year_id, mt.dbl_revenue_target, mt.vhr_revenue_target_based_on, mt.dbl_yield_target, mt.vhr_yield_target_based_on, mt.arb_consider_fk_branch_ids, mt.arb_consider_fk_service_ids, mt.arb_fk_package_category_ids, mt.sin_less_refund, mt.sin_splitup_type, mt.sin_month_wise_splitup, 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 target.tbl_set_target 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_set_target(jsnSetTarget 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('target.tbl_set_target'::VARCHAR, ''::VARCHAR, ::VARCHAR, 'pk_set_target_id'::VARCHAR, CAST(jsnSetTarget->>'binSetTargetId' 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(jsnSetTarget->>'binSetTargetId' AS BIGINT)::BIGINT = 0::BIGINT THEN -- // Count Exced or other checking License related in DB FUCTION(Insert) --jsnSetTarget.intSysActionId = 0; --jsnSetTarget.binCreatedUserId = jsnSetTarget.linLoginUserId; --jsnSetTarget.dtmCreated = jsnSetTarget.dtmCurrentDateTime; --SELECT NOW(); INSERT INTO target.tbl_set_target (sin_target_type, fk_account_id, fk_counter_staff_id, fk_branch_id, fk_airline_master_id, fk_hotel_master_id, fk_financial_year_id, dbl_revenue_target, vhr_revenue_target_based_on, dbl_yield_target, vhr_yield_target_based_on, arb_consider_fk_branch_ids, arb_consider_fk_service_ids, arb_fk_package_category_ids, sin_less_refund, sin_splitup_type, sin_month_wise_splitup, int_sys_action_id, fk_created_user_id, dtm_created) VALUES(CAST(jsnSetTarget->>'sinTargetType' AS SMALLINT), CAST(jsnSetTarget->>'binAccountId' AS BIGINT), CAST(jsnSetTarget->>'binCounterStaffId' AS BIGINT), CAST(jsnSetTarget->>'binBranchId' AS BIGINT), CAST(jsnSetTarget->>'binAirlineMasterId' AS BIGINT), CAST(jsnSetTarget->>'binHotelMasterId' AS BIGINT), CAST(jsnSetTarget->>'binFinancialYearId' AS BIGINT), CAST(jsnSetTarget->>'dblRevenueTarget' AS DOUBLE PRECISION), jsnSetTarget->>'strRevenueTargetBasedOn', CAST(jsnSetTarget->>'dblYieldTarget' AS DOUBLE PRECISION), jsnSetTarget->>'strYieldTargetBasedOn', STRING_TO_ARRAY(jsnSetTarget->>'strDbArlConsiderFkBranchIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnSetTarget->>'strDbArlConsiderFkServiceIds', ',')::BIGINT[], STRING_TO_ARRAY(jsnSetTarget->>'strDbArlFkPackageCategoryIds', ',')::BIGINT[], CAST(jsnSetTarget->>'sinLessRefund' AS SMALLINT), CAST(jsnSetTarget->>'sinSplitupType' AS SMALLINT), CAST(jsnSetTarget->>'sinMonthWiseSplitup' AS SMALLINT), 0, CAST(jsnSetTarget->>'binCreatedUserId' AS BIGINT), dtmCurrent) RETURNING pk_set_target_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('target.tbl_set_target'::VARCHAR, 'pk_set_target_id'::VARCHAR, CAST(jsnSetTarget->>'binSetTargetId' AS BIGINT)::BIGINT, CAST(jsnSetTarget->>'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; --jsnSetTarget.intSysActionId = jsnSetTarget.intSysActionId + 1; --jsnSetTarget.binLastModifiedUserId = jsnSetTarget.linLoginUserId; --jsnSetTarget.dtmLastModified = jsnSetTarget.dtmCurrentDateTime; --SELECT NOW(); UPDATE target.tbl_set_target SET sin_target_type = CAST(jsnSetTarget->>'sinTargetType' AS SMALLINT), fk_account_id = CAST(jsnSetTarget->>'binAccountId' AS BIGINT), fk_counter_staff_id = CAST(jsnSetTarget->>'binCounterStaffId' AS BIGINT), fk_branch_id = CAST(jsnSetTarget->>'binBranchId' AS BIGINT), fk_airline_master_id = CAST(jsnSetTarget->>'binAirlineMasterId' AS BIGINT), fk_hotel_master_id = CAST(jsnSetTarget->>'binHotelMasterId' AS BIGINT), fk_financial_year_id = CAST(jsnSetTarget->>'binFinancialYearId' AS BIGINT), dbl_revenue_target = CAST(jsnSetTarget->>'dblRevenueTarget' AS DOUBLE PRECISION), vhr_revenue_target_based_on = jsnSetTarget->>'strRevenueTargetBasedOn', dbl_yield_target = CAST(jsnSetTarget->>'dblYieldTarget' AS DOUBLE PRECISION), vhr_yield_target_based_on = jsnSetTarget->>'strYieldTargetBasedOn', arb_consider_fk_branch_ids = STRING_TO_ARRAY(jsnSetTarget->>'strDbArlConsiderFkBranchIds', ',')::BIGINT[], arb_consider_fk_service_ids = STRING_TO_ARRAY(jsnSetTarget->>'strDbArlConsiderFkServiceIds', ',')::BIGINT[], arb_fk_package_category_ids = STRING_TO_ARRAY(jsnSetTarget->>'strDbArlFkPackageCategoryIds', ',')::BIGINT[], sin_less_refund = CAST(jsnSetTarget->>'sinLessRefund' AS SMALLINT), sin_splitup_type = CAST(jsnSetTarget->>'sinSplitupType' AS SMALLINT), sin_month_wise_splitup = CAST(jsnSetTarget->>'sinMonthWiseSplitup' AS SMALLINT), int_sys_action_id = int_sys_action_id + 1, fk_last_modified_user_id = CAST(jsnSetTarget->>'binLastModifiedUserId' AS BIGINT), dtm_last_modified = dtmCurrent WHERE pk_set_target_id = CAST(jsnSetTarget->>'binSetTargetId' AS BIGINT); END IF; --COMMIT; objSpOutRes.sinSqlCustomStatus := 1::SMALLINT; objSpOutRes.strSqlCustomStatusCode := 'SUCCESS'; objSpOutRes.strSqlCustomMessage := 'SetTarget 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 := 'SetTarget 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_set_target(binSetTargetId 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_set_target'::VARCHAR, 'pk_set_target_id'::VARCHAR, binSetTargetId::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('target.tbl_set_target'::VARCHAR, 'pk_set_target_id'::VARCHAR, CAST(binSetTargetId 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 target.tbl_set_target SET int_sys_action_id = -1::INT, fk_deleted_user_id = binDeletedUserId, dtm_deleted = dtmCurrent WHERE pk_set_target_id = binSetTargetId; --COMMIT; objSpOutRes.sinSqlCustomStatus := 1::SMALLINT; objSpOutRes.strSqlCustomStatusCode := 'SUCCESS'; objSpOutRes.strSqlCustomMessage := 'SetTarget 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 := 'SetTarget 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;