Open canhvinhdev opened 3 months ago
cisBankService.pcb.NCBService.UserID = NCBCDS1 cisBankService.pcb.NCBService.UserPassword = aBC@123456
create or replace PACKAGE BODY "PKG_REF_CODE" AS v_parameter NVARCHAR2 (1500); PROCEDURE pr_list_category (p_status sys_refcode.status%TYPE , p_ref_group sys_refcode.ref_group%TYPE, p_ref_code sys_refcode.ref_code%TYPE, p_ref_name_vn sys_refcode.ref_name_vn%TYPE, p_ref_name_en sys_refcode.ref_name_en%TYPE, p_description sys_refcode.description%TYPE, p_par1 sys_refcode.par1%TYPE, p_par2 sys_refcode.par2%TYPE, p_par3 sys_refcode.par3%TYPE, p_par4 sys_refcode.par4%TYPE, p_par5 sys_refcode.par5%TYPE, p_par6 sys_refcode.par6%TYPE, p_par7 sys_refcode.par7%TYPE, p_par8 sys_refcode.par8%TYPE, p_par9 sys_refcode.par9%TYPE, p_user varchar2, p_out OUT types.ref_cursor) AS V_GROUP_NAME VARCHAR2(1000); V_PRODUCT_LIST VARCHAR2(1000); V_REF_GROUP VARCHAR2(100); V_BRANCH_REPORT VARCHAR2(1000); BEGIN v_parameter := 'p_user:' || p_user || '|V_BRANCH_REPORT:' || V_BRANCH_REPORT || '|p_ref_group:' || p_ref_group;
pkg_system_log.pr_log_info ('pr_list_category', 'BEGIN', v_parameter);
if p_ref_group='LS_BRANCH' then --Danh sach Branch theo quyen du lieu
SELECT MAX(NVL(A.MEMBER_VIEW_REPORT,'N/A')||','||A.MEMBER_CODE)
INTO V_BRANCH_REPORT
FROM SYS_USER A
WHERE A.USER_NAME = lower(P_USER);
v_parameter := 'p_user:'|| p_user ||'|V_BRANCH_REPORT:'|| V_BRANCH_REPORT ||'|p_ref_group:'|| p_ref_group;
pkg_system_log.pr_log_info ('pr_list_category', 'BEGIN', v_parameter);
OPEN p_out FOR
WITH DATA_PERMISION AS(
SELECT DISTINCT TRIM(REGEXP_SUBSTR(BRCODE, '[^,]+', 1, LEVEL)) BRANCH
FROM (SELECT V_BRANCH_REPORT BRCODE FROM DUAL) T
CONNECT BY INSTR(BRCODE, ',', 1, LEVEL - 1) > 0 )
SELECT refCode.*, sysRef.REF_NAME_VN STATUS_STR
FROM V_BRANCH refCode
LEFT JOIN SYS_REFCODE sysRef on refCode.STATUS = sysRef.REF_CODE and sysRef.REF_GROUP = 'LS_STATUS'
WHERE (p_status is null or refCode.status = p_status)
AND (p_ref_code is null or (refCode.ref_code) LIKE '%' || (p_ref_code) || '%')-- 4/5/2021 Bo tinh nang tim kiem ko dau
AND (p_ref_name_vn is null or refCode.ref_name_vn LIKE '%' || p_ref_name_vn || '%')
AND (p_ref_name_en is null or refCode.ref_name_en LIKE '%' || p_ref_name_en || '%')
AND (p_description is null or refCode.description LIKE '%' || p_description || '%')
AND (p_par1 is null or refCode.par1 = p_par1)
AND (p_par2 is null or refCode.par2 = p_par2)
AND (p_par3 is null or refCode.par3 = p_par3)
AND (p_par4 is null or refCode.par4 = p_par4)
AND (p_par5 is null or refCode.par5 = p_par5)
AND (p_par6 is null or refCode.par6 = p_par6)
AND (p_par7 is null or refCode.par7 = p_par7)
AND (p_par8 is null or refCode.par8 = p_par8)
AND (p_par9 is null or refCode.par9 = p_par9)
AND (EXISTS (SELECT * FROM DATA_PERMISION XX WHERE refCode.REF_CODE_PERMISSION LIKE '%,'||XX.BRANCH||',%') OR P_USER IS NULL)
;
ELSIF p_ref_group='LS_BRANCH_ALL' then --Danh sach toan bo Branch
v_parameter := 'p_user:'|| p_user ||'|V_BRANCH_REPORT:'|| V_BRANCH_REPORT ||'|p_ref_group:'|| p_ref_group;
pkg_system_log.pr_log_info ('pr_list_category', 'BEGIN', v_parameter);
OPEN p_out FOR
SELECT refCode.*, sysRef.REF_NAME_VN STATUS_STR
FROM V_BRANCH refCode
LEFT JOIN SYS_REFCODE sysRef on refCode.STATUS = sysRef.REF_CODE and sysRef.REF_GROUP = 'LS_STATUS'
WHERE (p_status is null or refCode.status = p_status)
AND (p_ref_code is null or (refCode.ref_code) LIKE '%' || (p_ref_code) || '%')-- 4/5/2021 Bo tinh nang tim kiem ko dau
AND (p_ref_name_vn is null or refCode.ref_name_vn LIKE '%' || p_ref_name_vn || '%')
AND (p_ref_name_en is null or refCode.ref_name_en LIKE '%' || p_ref_name_en || '%')
AND (p_description is null or refCode.description LIKE '%' || p_description || '%')
AND (p_par1 is null or refCode.par1 = p_par1)
AND (p_par2 is null or refCode.par2 = p_par2)
AND (p_par3 is null or refCode.par3 = p_par3)
AND (p_par4 is null or refCode.par4 = p_par4)
AND (p_par5 is null or refCode.par5 = p_par5)
AND (p_par6 is null or refCode.par6 = p_par6)
AND (p_par7 is null or refCode.par7 = p_par7)
AND (p_par8 is null or refCode.par8 = p_par8)
AND (p_par9 is null or refCode.par9 = p_par9);
-- start quan li don vi
elsif p_ref_group='LS_BRANCH_NEW' then
OPEN p_out FOR
SELECT refCode.*, sysRef.REF_NAME_VN STATUS_STR, sysRef1.PAR2 REF_NAME_STR
FROM SYS_REFCODE refCode
LEFT JOIN SYS_REFCODE sysRef on refCode.STATUS = sysRef.REF_CODE and sysRef.REF_GROUP = 'LS_STATUS'
LEFT JOIN SYS_REFCODE sysRef1 on refCode.PAR1 = sysRef1.REF_CODE and sysRef1.REF_GROUP = 'LS_BRANCH' and sysRef1.STATUS = '1'
WHERE refCode.REF_GROUP='LS_BRANCH'
AND (p_status is null or refCode.status = p_status)
AND (p_ref_code is null or (refCode.ref_code) LIKE '%' || (p_ref_code) || '%') -- 4/5/2021 Bo tinh nang tim kiem ko dau
AND (p_ref_name_vn is null or (refCode.ref_name_vn) LIKE '%' || (p_ref_name_vn) || '%')-- 4/5/2021 Bo tinh nang tim kiem ko dau
-- AND (p_ref_name_vn is null or refCode.ref_name_vn LIKE '%' || p_ref_name_vn || '%') AND (p_ref_name_en is null or refCode.ref_name_en LIKE '%' || p_ref_name_en || '%') AND (p_description is null or pkg_utility.removesignvietnamess(refCode.description) LIKE '%' || pkg_utility.removesignvietnamess(p_description) || '%') AND (p_par1 is null or refCode.par1 = p_par1) AND (p_par2 is null or (refCode.par2) LIKE '%' || (p_par2) || '%') AND (p_par3 is null or refCode.par3 = p_par3) AND (p_par4 is null or refCode.par4 = p_par4) AND (p_par5 is null or refCode.par5 = p_par5) AND (p_par6 is null or refCode.par6 = p_par6) AND (p_par7 is null or refCode.par7 = p_par7) AND (p_par8 is null or refCode.par8 = p_par8) AND (p_par9 is null or refCode.par9 = p_par9) order by refCode.CREATER_DATE desc; -- end quan li don vi else -- Cac du lieu khac V_REF_GROUP := p_ref_group; IF p_ref_group='LS_PRODUCT' THEN SELECT ','|| listagg(','||NVL(GROUP_NAME,'')||',',', ') within group(order by GROUP_NAME) ||',' INTO V_GROUP_NAME FROM SYS_USER A WHERE A.USER_NAME = P_USER;
SELECT ','|| listagg(','||NVL(PAR2,'')||',',', ') within group(order by PAR2) ||',' INTO V_PRODUCT_LIST
FROM SYS_GROUPS A
WHERE V_GROUP_NAME LIKE '%,'|| A.GROUP_NAME ||',%';
END IF;
IF p_ref_group='LS_PRODUCT_ALL' THEN
V_REF_GROUP:='LS_PRODUCT';
END IF;
OPEN p_out FOR
SELECT refCode.*, sysRef.REF_NAME_VN STATUS_STR
FROM SYS_REFCODE refCode
LEFT JOIN SYS_REFCODE sysRef on refCode.STATUS = sysRef.REF_CODE and sysRef.REF_GROUP = 'LS_STATUS'
WHERE (p_status is null or refCode.status = p_status)
AND (V_REF_GROUP is null or refCode.ref_group = V_REF_GROUP)
AND (p_ref_code is null or (refCode.ref_code) LIKE '%' || (p_ref_code) || '%') -- 4/5/2021 Bo tinh nang tim kiem ko dau
AND (p_ref_name_vn is null or (refCode.ref_name_vn) LIKE '%' || (p_ref_name_vn) || '%')-- 4/5/2021 Bo tinh nang tim kiem ko dau
-- AND (p_ref_name_vn is null or refCode.ref_name_vn LIKE '%' || p_ref_name_vn || '%') AND (p_ref_name_en is null or refCode.ref_name_en LIKE '%' || p_ref_name_en || '%') AND (p_description is null or pkg_utility.removesignvietnamess(refCode.description) LIKE '%' || pkg_utility.removesignvietnamess(p_description) || '%') AND (p_par1 is null or refCode.par1 = p_par1) AND (p_par2 is null or (refCode.par2) LIKE '%' || (p_par2) || '%') AND (p_par3 is null or refCode.par3 = p_par3) AND (p_par4 is null or refCode.par4 = p_par4) AND (p_par5 is null or refCode.par5 = p_par5) AND (p_par6 is null or refCode.par6 = p_par6) AND (p_par7 is null or refCode.par7 = p_par7) AND (p_par8 is null or refCode.par8 = p_par8) AND (p_par9 is null or refCode.par9 = p_par9) AND (V_PRODUCT_LIST like '%,'|| refCode.REF_CODE ||',%' OR V_REF_GROUP<>'LS_PRODUCT' OR p_ref_group='LS_PRODUCT_ALL') order by refCode.CREATER_DATE desc;
end if;
END pr_list_category;
PROCEDURE pr_category_info(p_ref_code sys_refcode.ref_code%TYPE,
p_ref_group sys_refcode.ref_group%TYPE,
p_out OUT types.ref_cursor)
AS
BEGIN
OPEN p_out FOR select * from SYS_REFCODE
Where ref_code = p_ref_code and REF_GROUP = p_ref_group;
END pr_category_info;
PROCEDURE pr_create_category(p_application_id SYS_REFCODE.APPLICATION_ID%TYPE,
p_creater_date SYS_REFCODE.CREATER_DATE%TYPE,
p_ref_code SYS_REFCODE.REF_CODE%TYPE,
p_ref_group SYS_REFCODE.REF_GROUP%TYPE,
p_ref_name_en SYS_REFCODE.REF_NAME_EN%TYPE,
p_ref_name_vn SYS_REFCODE.REF_NAME_VN%TYPE,
p_update_date SYS_REFCODE.UPDATE_DATE%TYPE,
p_user_creater SYS_REFCODE.USER_CREATER%TYPE,
p_user_update SYS_REFCODE.USER_UPDATE%TYPE,
p_priority SYS_REFCODE.PRIORITY%TYPE,
p_description SYS_REFCODE.DESCRIPTION%TYPE,
p_par1 SYS_REFCODE.PAR1%TYPE,
p_par2 SYS_REFCODE.PAR2%TYPE,
p_par3 SYS_REFCODE.PAR3%TYPE,
p_par4 SYS_REFCODE.PAR4%TYPE,
p_par5 SYS_REFCODE.PAR5%TYPE,
p_par6 SYS_REFCODE.PAR6%TYPE,
p_par7 SYS_REFCODE.PAR7%TYPE,
p_par8 SYS_REFCODE.PAR8%TYPE,
p_par9 SYS_REFCODE.PAR9%TYPE,
p_allowdelete SYS_REFCODE.ALLOWDELETE%TYPE,
p_update_reason SYS_REFCODE.UPDATE_REASON%TYPE,
p_status SYS_REFCODE.STATUS%TYPE,
p_create_reason SYS_REFCODE.CREATE_REASON%TYPE,
p_user IN VARCHAR2,
p_client_ip IN VARCHAR2,
p_user_agent IN VARCHAR2,
p_out OUT types.ref_cursor)
AS
BEGIN
INSERT INTO SYS_REFCODE (APPLICATION_ID,
CREATER_DATE,
REF_CODE,
REF_GROUP,
REF_NAME_EN,
REF_NAME_VN,
--UPDATE_DATE,
USER_CREATER,
--USER_UPDATE,
PRIORITY,
DESCRIPTION,
PAR1,
PAR2,
PAR3,
PAR4,
PAR5,
PAR6,
PAR7,
PAR8,
PAR9,
ALLOWDELETE,
UPDATE_REASON,
STATUS,
CREATE_REASON)
VALUES(p_application_id , --APPLICATION_ID
sysdate , --CREATER_DATE
p_ref_code , --REF_CODE
p_ref_group , --REF_GROUP
p_ref_name_en , --REF_NAME_EN
p_ref_name_vn , --REF_NAME_VN
--p_update_date , --UPDATE_DATE
p_user , --USER_CREATER
-- p_user_update , --USER_UPDATE
p_priority , --PRIORITY
p_description , --DESCRIPTION
p_par1 , --PAR1
p_par2 , --PAR2
p_par3 , --PAR3
p_par4 , --PAR4
p_par5 , --PAR5
p_par6 , --PAR6
p_par7 , --PAR7
p_par8 , --PAR8
p_par9 , --PAR9
p_allowdelete , --ALLOWDELETE
p_update_reason , --UPDATE_REASON
p_status , --STATUS
p_create_reason --CREATE_REASON
);
OPEN p_out FOR select * from SYS_REFCODE
Where ref_code = p_ref_code and REF_GROUP = p_ref_group;
END pr_create_category;
PROCEDURE pr_update_category(p_application_id SYS_REFCODE.APPLICATION_ID%TYPE,
p_creater_date SYS_REFCODE.CREATER_DATE%TYPE,
p_ref_code SYS_REFCODE.REF_CODE%TYPE,
p_ref_group SYS_REFCODE.REF_GROUP%TYPE,
p_ref_name_en SYS_REFCODE.REF_NAME_EN%TYPE,
p_ref_name_vn SYS_REFCODE.REF_NAME_VN%TYPE,
p_update_date SYS_REFCODE.UPDATE_DATE%TYPE,
p_user_creater SYS_REFCODE.USER_CREATER%TYPE,
p_user_update SYS_REFCODE.USER_UPDATE%TYPE,
p_priority SYS_REFCODE.PRIORITY%TYPE,
p_description SYS_REFCODE.DESCRIPTION%TYPE,
p_par1 SYS_REFCODE.PAR1%TYPE,
p_par2 SYS_REFCODE.PAR2%TYPE,
p_par3 SYS_REFCODE.PAR3%TYPE,
p_par4 SYS_REFCODE.PAR4%TYPE,
p_par5 SYS_REFCODE.PAR5%TYPE,
p_par6 SYS_REFCODE.PAR6%TYPE,
p_par7 SYS_REFCODE.PAR7%TYPE,
p_par8 SYS_REFCODE.PAR8%TYPE,
p_par9 SYS_REFCODE.PAR9%TYPE,
p_allowdelete SYS_REFCODE.ALLOWDELETE%TYPE,
p_update_reason SYS_REFCODE.UPDATE_REASON%TYPE,
p_status SYS_REFCODE.STATUS%TYPE,
p_create_reason SYS_REFCODE.CREATE_REASON%TYPE,
p_user IN VARCHAR2,
p_client_ip IN VARCHAR2,
p_user_agent IN VARCHAR2,
p_out OUT types.ref_cursor)
AS
BEGIN
UPDATE sys_refcode
SET APPLICATION_ID=p_application_id,
REF_NAME_EN=p_ref_name_en,
REF_NAME_VN=p_ref_name_vn,
UPDATE_DATE=sysdate,
USER_UPDATE=p_user,
PRIORITY=p_priority,
DESCRIPTION=p_description,
PAR1=p_par1,
PAR2=p_par2,
PAR3=p_par3,
PAR4=p_par4,
PAR5=p_par5,
PAR6=p_par6,
PAR7=p_par7,
PAR8=p_par8,
PAR9=p_par9,
ALLOWDELETE=p_allowdelete,
UPDATE_REASON=p_update_reason,
STATUS=p_status,
CREATE_REASON=p_create_reason
WHERE ref_code = p_ref_code and ref_group = p_ref_group;
IF p_ref_group='LS_BRANCH' THEN
UPDATE SYS_REFCODE SET STATUS=p_status WHERE REF_CODE=p_ref_code AND REF_GROUP='LS_BRANCH';
IF p_status = '0' THEN
UPDATE SYS_REFCODE SET STATUS = p_status
WHERE REF_CODE IN (
select A.REF_CODE from (SELECT REF_CODE, PAR1, CONNECT_BY_ISCYCLE AS is_cycle
FROM SYS_REFCODE where REF_GROUP = 'LS_BRANCH'
START WITH PAR1 = p_ref_code
CONNECT BY NOCYCLE PRIOR REF_CODE = PAR1) A
);
END IF;
end if;
OPEN p_out FOR select * from SYS_REFCODE
Where ref_code = p_ref_code and REF_GROUP = p_ref_group;
END pr_update_category;
PROCEDURE pr_list_product_category (p_status sys_refcode.status%TYPE ,
p_ref_group sys_refcode.ref_group%TYPE,
p_ref_code sys_refcode.ref_code%TYPE,
p_ref_name_vn sys_refcode.ref_name_vn%TYPE,
p_ref_name_en sys_refcode.ref_name_en%TYPE,
p_description sys_refcode.description%TYPE,
p_par1 sys_refcode.par1%TYPE,
p_par2 sys_refcode.par2%TYPE,
p_par3 sys_refcode.par3%TYPE,
p_par4 sys_refcode.par4%TYPE,
p_par5 sys_refcode.par5%TYPE,
p_par6 sys_refcode.par6%TYPE,
p_par7 sys_refcode.par7%TYPE,
p_par8 sys_refcode.par8%TYPE,
p_par9 sys_refcode.par9%TYPE,
p_out OUT types.ref_cursor)
AS
BEGIN
OPEN p_out FOR
SELECT refCode.*, sysRef.REF_NAME_VN STATUS_STR, sysRefCusType.REF_NAME_VN CUSTOMER_TYPE_STR
FROM SYS_REFCODE refCode
LEFT JOIN SYS_REFCODE sysRef on refCode.STATUS = sysRef.REF_CODE and sysRef.REF_GROUP = 'LS_STATUS'
LEFT JOIN SYS_REFCODE sysRefCusType on REFCODE.PAR2 = sysRefCusType.REF_CODE and sysRefCusType.REF_GROUP = 'LOAI_KH'
WHERE (p_status is null or refCode.status = p_status)
AND (p_ref_group is null or refCode.ref_group = p_ref_group)
AND (p_ref_code is null or (refCode.ref_code) LIKE '%' || (p_ref_code) || '%') -- 4/5/2021 Bo tinh nang tim kiem ko dau
AND (p_ref_name_vn is null or pkg_utility.removesignvietnamess(refCode.ref_name_vn) LIKE '%' || pkg_utility.removesignvietnamess(p_ref_name_vn) || '%')
AND (p_ref_name_en is null or refCode.ref_name_en LIKE '%' || p_ref_name_en || '%')
AND (p_description is null or refCode.description LIKE '%' || p_description || '%')
AND (p_par1 is null or refCode.par1 = p_par1)
AND (p_par2 is null or refCode.par2 = p_par2)
AND (p_par3 is null or refCode.par3 = p_par3)
AND (p_par4 is null or refCode.par4 = p_par4)
AND (p_par5 is null or refCode.par5 = p_par5)
AND (p_par6 is null or refCode.par6 = p_par6)
AND (p_par7 is null or refCode.par7 = p_par7)
AND (p_par8 is null or refCode.par8 = p_par8)
AND (p_par9 is null or refCode.par9 = p_par9)
order by refCode.par1, refCode.ref_code;
END pr_list_product_category;
PROCEDURE pr_category_his ( p_ref_group sys_refcode.ref_group%TYPE, p_ref_code sys_refcode.ref_code%TYPE, p_user IN VARCHAR2, p_client_ip IN VARCHAR2, p_user_agent IN VARCHAR2, p_out OUT types.ref_cursor) AS BEGIN IF p_ref_group = 'CIS_PRICE' THEN OPEN p_out FOR SELECT A.PRICE_CODE REF_CODE ,TO_CHAR(A.EFFECTIVE_DATE,'DD/MM/YYYY') PAR1 ,TO_CHAR(A.EXPIRATION_DATE,'DD/MM/YYYY') PAR2 ,PKG_UTILITY.FORMAT_NUMBER(A.NORMAL_PRICE) PAR3 ,PKG_UTILITY.FORMAT_NUMBER(A.NON_NORMAL_PRICE) PAR4 ,A.UPDATE_DATE UPDATE_DATE ,A.USER_UPDATE USER_UPDATE FROM SYS_PRICE_HIS A WHERE A.ID=p_ref_code order by a.id_his desc; ELSE OPEN p_out FOR SELECT * FROM SYS_REFCODE_HIS WHERE LOWER(ref_group) = LOWER(p_ref_group) AND LOWER(ref_code) = LOWER(p_ref_code) order by ACTION_DT desc; END IF; END;
PROCEDURE pr_get_sys_ref_name_info ( p_ref_group sys_refcode.ref_group%TYPE,
p_ref_code sys_refcode.REF_CODE%TYPE,
p_par2 sys_refcode.PAR2%TYPE,
p_action in varchar2,
p_out OUT types.ref_cursor)
AS
BEGIN
IF p_action = 'ADJ' THEN
OPEN p_out FOR
SELECT *
FROM SYS_REFCODE
WHERE LOWER(ref_group) = LOWER(p_ref_group)
AND LOWER(PAR2) = LOWER(p_par2) AND REF_CODE != p_ref_code;
ELSE
OPEN p_out FOR
SELECT *
FROM SYS_REFCODE
WHERE LOWER(ref_group) = LOWER(p_ref_group)
AND LOWER(PAR2) = LOWER(p_par2);
END IF;
END;
END PKG_REF_CODE;
curl --location ' http://10.1.62.130:7811/accountService/v1/getCustomerInfo' \ --header 'Content-Type: application/json' \ --data-raw '{ "getCustomerInfoReq": { "header": { "common": { "serviceVersion": "1", "messageId": "f87b93f6-e5e3-49d2-a181-ebd786337b35", "transactionId": "3d3c9d9d-e5f8-4bd7-95d8-d6dfb1e7bc91", "messageTimestamp": "2024-05-28T10:57:13.032Z" }, "client": { "sourceAppID": "MB", "targetAppIDs": "T24", "userDetail": { "userID": "NCBCDS1", "userPassword": "aBC@123456" } } }, "bodyReq": { "functionCode": "API.CDS.GET.CUSTOMER.INFO", "legalID": "087090000048", "legalName": "ID.CARD" } } }'