SQLize | PHPize | SQLtest

drop function audit_log(timestamp, timestamp, integer, integer, integer, varchar); create function audit_log(p_from_date timestamp without time zone, p_to_date timestamp without time zone, p_type integer, p_action integer, p_user integer, p_branch_code character varying) returns TABLE ( "Operation Date Time" timestamp without time zone, "Maker Username" character varying, "Action" integer, "Checker Response Date" timestamp without time zone, "Checker Username" character varying, "Checker Response" integer, "Ebiz Response Date" timestamp without time zone, "Ebiz Username" character varying, "Ebiz Response" integer, "User Name" character varying ) language plpgsql as $$ BEGIN IF (p_type = 1) THEN BEGIN RETURN QUERY SELECT "OPERATION_DATE", "MAKER_ID", "OPERATION_TYPE", "CHECKER_RESPONSE_DATE", "CHECKER_ID", "CHECKER_RESPONSE_STATUS", "EBIZ_RESPONSE_DATE", "EBIZ_ID", "EBIZ_RESPONSE_STATUS", "USER_NAME" FROM (SELECT distinct t."ID", t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = t."MAKER_ID") "MAKER_ID", t."OPERATION_TYPE"::integer, t."CHECKER_RESPONSE_DATE"::timestamp without time zone as "CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID"))::character varying as "CHECKER_ID", t."CHECKER_RESPONSE_STATUS"::integer as "CHECKER_RESPONSE_STATUS", t."EBIZ_RESPONSE_DATE"::timestamp without time zone as "EBIZ_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."EBIZ_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID"))::character varying as "EBIZ_ID", t."EBIZ_RESPONSE_STATUS"::integer as "EBIZ_RESPONSE_STATUS", (CASE WHEN (t."CB_CUSTOMER_NUMBER" IS NULL OR t."CB_CUSTOMER_NUMBER" = '') THEN 'N/A' ELSE t."CB_CUSTOMER_NUMBER" END)::character varying as "USER_NAME" FROM abbl."NOTIFICATION" n INNER JOIN abbl."CUSTOMER_REGISTRATION" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = p_type AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0)) r; END; END IF; IF (p_type = 2) THEN BEGIN RETURN QUERY SELECT t."OPERATION_DATE"::timestamp without time zone as "OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = n."MAKER_ID") as "MAKER_ID", t."OPERATION_TYPE", t."CHECKED_DATE" as "CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) as "CHECKER_ID", t."RESPONSE_STATUS" as "CHECKER_RESPONSE_STATUS", null::timestamp without time zone as "EBIZ_RESPONSE_DATE", null::character varying as "EBIZ_ID", 0::integer as "EBIZ_RESPONSE_STATUS", 'N/A'::character varying as "USER_NAME" FROM abbl."NOTIFICATION" n INNER JOIN abbl."SYSTEM_USER_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = p_type AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0); END; END IF; IF (p_type = 5) THEN BEGIN RETURN QUERY SELECT t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = n."MAKER_ID"), t."OPERATION_TYPE", t."CHECKED_DATE" as "CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")), t."RESPONSE_STATUS", null::timestamp without time zone as "EBIZ_RESPONSE_DATE", null::character varying as "EBIZ_ID", 0::integer as "EBIZ_RESPONSE_STATUS", 'N/A'::character varying as "USER_NAME" FROM abbl."NOTIFICATION" n INNER JOIN abbl."PROMOTIONAL_CONTENT_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = p_type AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0); END; END IF; IF (p_type = 6) THEN BEGIN RETURN QUERY SELECT "OPERATION_DATE", "MAKER_ID", "OPERATION_TYPE", "CHECKER_RESPONSE_DATE", "CHECKER_ID", "CHECKER_RESPONSE_STATUS", "EBIZ_RESPONSE_DATE", "EBIZ_ID", "EBIZ_RESPONSE_STATUS", "USER_NAME" FROM (SELECT distinct t."ID", -- (SELECT "CUSTOMER_NUMBER" FROM abbl."CUSTOMER_ACCOUNT" ca WHERE ca."ID" = t."CUSTOMER_ID") "USER_NAME", t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = t."MAKER_ID") "MAKER_ID", t."OPERATION_TYPE"::integer, t."CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "CHECKER_ID", t."CHECKER_RESPONSE_STATUS"::integer AS "CHECKER_RESPONSE_STATUS", t."EBIZ_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."EBIZ_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "EBIZ_ID", t."EBIZ_RESPONSE_STATUS"::integer, (SELECT "CB_CUSTOMER_NUMBER" FROM abbl."CUSTOMER_ACCOUNT" ca WHERE ca."ID" = t."CUSTOMER_ID") "USER_NAME" FROM abbl."NOTIFICATION" n INNER JOIN abbl."CUSTOMER_INFO_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = p_type AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0)) r; END; END IF; IF (p_type = 18) THEN BEGIN RETURN QUERY SELECT "OPERATION_DATE", "MAKER_ID", "OPERATION_TYPE", "CHECKER_RESPONSE_DATE", "CHECKER_ID", "CHECKER_RESPONSE_STATUS", "EBIZ_RESPONSE_DATE", "EBIZ_ID", "EBIZ_RESPONSE_STATUS", "USER_NAME" FROM (SELECT distinct t."ID", -- (SELECT "CUSTOMER_NUMBER" FROM abbl."CUSTOMER_ACCOUNT" ca WHERE ca."ID" = t."CUSTOMER_ACCOUNT_ID") "USER_NAME", t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = t."MAKER_ID") "MAKER_ID", t."OPERATION_TYPE"::integer, t."CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "CHECKER_ID", t."CHECKER_RESPONSE_STATUS"::integer, t."EBIZ_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."EBIZ_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "EBIZ_ID", t."EBIZ_RESPONSE_STATUS"::integer, (SELECT "CB_CUSTOMER_NUMBER" FROM abbl."CUSTOMER_ACCOUNT" ca WHERE ca."ID" = t."CUSTOMER_ACCOUNT_ID") "USER_NAME" FROM abbl."NOTIFICATION" n INNER JOIN abbl."RESET_CUSTOMER_PASSWORD_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = 18 AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0)) r; END; END IF; IF (p_type = 24) THEN BEGIN RETURN QUERY (SELECT t."REQ_INIT_DATE" AS "OPERATION_DATE", 'N/A'::character varying as "MAKER_ID",--t."NAME"::character varying AS "USER_ID", null::integer AS "OPERATION_TYPE", n."OPERATION_DATE" AS "CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = n."OPERATION_BY"), t."STATUS" AS "CHECKER_RESPONSE_STATUS", null::timestamp without time zone as "EBIZ_RESPONSE_DATE", null::character varying as "EBIZ_ID", null::integer as "EBIZ_RESPONSE_STATUS", (SELECT "CB_CUSTOMER_NUMBER" FROM abbl."CUSTOMER_ACCOUNT" ca WHERE ca."ID" = t."CUSTOMER_ID") "USER_NAME" FROM abbl."NON_CHECKER_MAKER_LOG" n INNER JOIN abbl."CALL_BACK_REQUEST" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = 24 AND n."OPERATION_DATE" >= P_FROM_DATE AND n."OPERATION_DATE" <= P_TO_DATE /*AND (t."CUSTOMER_ID" = (SELECT CA."CUSTOMER_ID" FROM "CUSTOMER_ACCOUNT" CA WHERE CA."CUSTOMER_ID" = t."CUSTOMER_ID" AND CA."BRANCH_SOL_ID" = p_branch_code::smallint) OR p_branch_code = '0')*/ AND (n."OPERATION_BY" in (select "ID" from "SYSTEM_USER" s where s."BRANCH_CODE" = p_branch_code) OR p_branch_code = '0') AND (n."OPERATION_BY" = p_user OR p_user = 0)); END; END IF; IF (p_type = 48) THEN BEGIN RETURN QUERY SELECT t."OPERATION_DATE"::timestamp without time zone, (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = t."MAKER_ID") as "MAKER_ID", t."OPERATION_TYPE":: integer, null::timestamp without time zone as "CHECKER_RESPONSE_DATE", null::character varying as "CHECKER_ID", 0::integer as "CHECKER_RESPONSE_STATUS", null::timestamp without time zone as "EBIZ_RESPONSE_DATE", null::character varying as "EBIZ_ID", 0::integer as "EBIZ_RESPONSE_STATUS", t."CB_CUSTOMER_NUMBER"::character varying as "USER_NAME" FROM abbl."NON_CHECKER_MAKER_LOG" n INNER JOIN abbl."CUSTOMER_INFO_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = p_type AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (T."BRANCH_SOL_ID"::CHARACTER varying = p_branch_code OR p_branch_code = '0') AND t."DIRECT" = 'Y' AND ((SELECT "SYSTEM_USER"."ID" FROM abbl."SYSTEM_USER" WHERE "ID" = t."MAKER_ID") = p_user OR p_user = 0); END; END IF; IF (p_type = 0) THEN BEGIN RETURN QUERY (SELECT "OPERATION_DATE", "MAKER_ID", "OPERATION_TYPE", "CHECKER_RESPONSE_DATE", "CHECKER_ID", "CHECKER_RESPONSE_STATUS", "EBIZ_RESPONSE_DATE", "EBIZ_ID", "EBIZ_RESPONSE_STATUS", "USER_NAME" FROM (SELECT t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = t."MAKER_ID") "MAKER_ID", t."OPERATION_TYPE"::integer, t."CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "CHECKER_ID", t."CHECKER_RESPONSE_STATUS"::integer, t."EBIZ_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."EBIZ_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "EBIZ_ID", t."EBIZ_RESPONSE_STATUS"::integer, (CASE WHEN (t."ACCOUNT_NO" IS NULL OR t."ACCOUNT_NO" = '') THEN 'N/A' ELSE SUBSTRING(t."ACCOUNT_NO", 5, 6) END)::character varying as "USER_NAME" FROM abbl."NOTIFICATION" n INNER JOIN abbl."CUSTOMER_REGISTRATION" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = 1 AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0)) r) UNION (SELECT t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = n."MAKER_ID") "MAKER_ID", t."OPERATION_TYPE", t."CHECKED_DATE" as "CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "CHECKER_ID", t."RESPONSE_STATUS" as "CHECKER_RESPONSE_STATUS", null::timestamp without time zone as "EBIZ_RESPONSE_DATE", null::character varying as "EBIZ_ID", null::integer as "EBIZ_RESPONSE_STATUS", 'N/A' "USER_NAME" FROM abbl."NOTIFICATION" n INNER JOIN abbl."SYSTEM_USER_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = 2 AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0)) UNION (SELECT t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = n."MAKER_ID") "MAKER_ID", t."OPERATION_TYPE", t."CHECKED_DATE" as "CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "CHECKER_ID", t."RESPONSE_STATUS" as "CHECKER_RESPONSE_STATUS", null::timestamp without time zone as "EBIZ_RESPONSE_DATE", null::character varying as "EBIZ_ID", null::integer as "EBIZ_RESPONSE_STATUS", 'N/A' "USER_NAME" FROM abbl."NOTIFICATION" n INNER JOIN abbl."PROMOTIONAL_CONTENT_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = 5 AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0)) UNION (SELECT "OPERATION_DATE", "MAKER_ID", "OPERATION_TYPE", "CHECKER_RESPONSE_DATE", "CHECKER_ID", "CHECKER_RESPONSE_STATUS", "EBIZ_RESPONSE_DATE", "EBIZ_ID", "EBIZ_RESPONSE_STATUS", "USER_NAME" FROM (SELECT (SELECT "CB_CUSTOMER_NUMBER" FROM abbl."CUSTOMER_ACCOUNT" ca WHERE ca."ID" = t."CUSTOMER_ID") "USER_NAME", t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = t."MAKER_ID") "MAKER_ID", t."OPERATION_TYPE"::integer, t."CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "CHECKER_ID", t."CHECKER_RESPONSE_STATUS"::integer, t."EBIZ_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."EBIZ_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "EBIZ_ID", t."EBIZ_RESPONSE_STATUS"::integer FROM abbl."NOTIFICATION" n INNER JOIN abbl."CUSTOMER_INFO_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = 6 AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0)) r) UNION (SELECT "OPERATION_DATE", "MAKER_ID", "OPERATION_TYPE", "CHECKER_RESPONSE_DATE", "CHECKER_ID", "CHECKER_RESPONSE_STATUS", "EBIZ_RESPONSE_DATE", "EBIZ_ID", "EBIZ_RESPONSE_STATUS", "USER_NAME" FROM (SELECT distinct t."ID", (SELECT "CB_CUSTOMER_NUMBER" FROM abbl."CUSTOMER_ACCOUNT" ca WHERE ca."ID" = t."CUSTOMER_ACCOUNT_ID") "USER_NAME", t."OPERATION_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = t."MAKER_ID") "MAKER_ID", t."OPERATION_TYPE"::integer, t."CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."CHECKER_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "CHECKER_ID", t."CHECKER_RESPONSE_STATUS", t."EBIZ_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" in (SELECT tn."CHECKER_ID" from abbl."NOTIFICATION" tn where tn."TO_USER_ROLE_ID" = t."EBIZ_ID" and tn."STATUS" = 'C' and tn."TYPE" = p_type and tn."REF_TABLE_ID" = t."ID")) "EBIZ_ID", t."EBIZ_RESPONSE_STATUS"::integer FROM abbl."NOTIFICATION" n INNER JOIN abbl."RESET_CUSTOMER_PASSWORD_TEMP" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = 18 AND t."OPERATION_DATE" >= P_FROM_DATE AND t."OPERATION_DATE" <= P_TO_DATE AND (t."OPERATION_TYPE" = p_action OR p_action = 0) AND (n."BRANCH_CODE" = p_branch_code OR p_branch_code = '0') AND (n."MAKER_ID" = p_user OR n."CHECKER_ID" = p_user OR p_user = 0)) r) UNION (SELECT (SELECT "CB_CUSTOMER_NUMBER" FROM abbl."CUSTOMER_ACCOUNT" ca WHERE ca."ID" = t."CUSTOMER_ID") "USER_NAME", t."REQ_INIT_DATE" AS "OPERATION_DATE", 'N/A'::character varying as "MAKER_ID",--t."NAME"::character varying AS "MAKER_ID", null::integer AS "OPERATION_TYPE", n."OPERATION_DATE" AS "CHECKER_RESPONSE_DATE", (SELECT "USER_ID" FROM abbl."SYSTEM_USER" WHERE "ID" = n."OPERATION_BY") "CHECKER_ID", t."STATUS" AS "CHECKER_RESPONSE_STATUS", null::timestamp without time zone as "EBIZ_RESPONSE_DATE", null::character varying as "EBIZ_ID", null::integer as "EBIZ_RESPONSE_STATUS" FROM abbl."NON_CHECKER_MAKER_LOG" n INNER JOIN abbl."CALL_BACK_REQUEST" t ON n."REF_TABLE_ID" = t."ID" WHERE n."TYPE" = 24 AND n."OPERATION_DATE" >= P_FROM_DATE AND n."OPERATION_DATE" <= P_TO_DATE /*AND (t."CUSTOMER_ID" = (SELECT CA."CUSTOMER_ID" FROM "CUSTOMER_ACCOUNT" CA WHERE CA."CUSTOMER_ID" = t."CUSTOMER_ID" AND CA."BRANCH_SOL_ID" = p_branch_code::smallint) OR p_branch_code = '0')*/ AND (n."OPERATION_BY" in (select "ID" from "SYSTEM_USER" s where s."BRANCH_CODE" = p_branch_code) OR p_branch_code = '0') AND (n."OPERATION_BY" = p_user OR p_user = 0)); END; END IF; END; $$;