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