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