eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_CUST_CLASS_DENORM SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 107


procedure insert_class_codes(ERRBUF  OUT NOCOPY Varchar2,
	                      RETCODE OUT NOCOPY Varchar2,
		              p_class_category in varchar2) IS

BEGIN

    RETCODE := 0;

Line: 117


	-- insert first level nodes

	INSERT  INTO HZ_CLASS_CODE_DENORM (
	CLASS_CATEGORY,
	CLASS_CODE,
	CLASS_CODE_MEANING,
	CLASS_CODE_DESCRIPTION,
	LANGUAGE,
	CONCAT_CLASS_CODE,
	CONCAT_CLASS_CODE_MEANING,
	CODE_LEVEL,
	START_DATE_ACTIVE,
	END_DATE_ACTIVE,
	ENABLED_FLAG,
	SELECTABLE_FLAG,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	LAST_UPDATE_DATE,
	REQUEST_ID,
	PROGRAM_ID,
	PROGRAM_APPLICATION_ID,
	PROGRAM_UPDATE_DATE
)
SELECT
CC.CLASS_CATEGORY,
LV.LOOKUP_CODE,
LV.MEANING ,
LV.DESCRIPTION,
LT.LANGUAGE,
LV.LOOKUP_CODE,
LV.MEANING ,
1,
LV.START_DATE_ACTIVE,
LV.END_DATE_ACTIVE,
LV.ENABLED_FLAG,
'Y',
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
SYSDATE,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE
FROM
FND_LOOKUP_TYPES_TL LT,
FND_LOOKUP_VALUES LV,
HZ_CLASS_CATEGORIES CC,
HZ_CLASS_CATEGORY_USES CCU
WHERE	LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
AND     LT.VIEW_APPLICATION_ID = 222
AND     LV.VIEW_APPLICATION_ID = 222
AND 	CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
AND	 LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
AND 	LT.LANGUAGE = LV.LANGUAGE
--AND 	CC.ALLOW_MULTI_PARENT_FLAG = 'N'
AND 	CCU.OWNER_TABLE='HZ_PARTIES'
AND 	NOT EXISTS(
		SELECT 'X'
		FROM HZ_CLASS_CODE_RELATIONS CCR
		WHERE LV.LOOKUP_CODE = CCR.SUB_CLASS_CODE
		AND CCR.CLASS_CATEGORY = LT.LOOKUP_TYPE
		AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate));

Line: 188


	INSERT  INTO HZ_CLASS_CODE_DENORM (
	CLASS_CATEGORY,
	CLASS_CODE,
	CLASS_CODE_MEANING,
	CLASS_CODE_DESCRIPTION,
	LANGUAGE,
	CONCAT_CLASS_CODE,
	CONCAT_CLASS_CODE_MEANING,
	CODE_LEVEL,
	START_DATE_ACTIVE,
	END_DATE_ACTIVE,
	ENABLED_FLAG,
	SELECTABLE_FLAG,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	LAST_UPDATE_DATE,
	REQUEST_ID,
	PROGRAM_ID,
	PROGRAM_APPLICATION_ID,
	PROGRAM_UPDATE_DATE
)
SELECT
CC.CLASS_CATEGORY,
CCR.SUB_CLASS_CODE,
LV.MEANING,
LV.DESCRIPTION,
LT.LANGUAGE,
DENORM.CONCAT_CLASS_CODE||NVL(CC.DELIMITER,'/')||CCR.SUB_CLASS_CODE,
DENORM.CONCAT_CLASS_CODE_MEANING||NVL(CC.DELIMITER,'/')||LV.MEANING,
i,
LV.START_DATE_ACTIVE,
LV.END_DATE_ACTIVE,
LV.ENABLED_FLAG,
'Y',
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
SYSDATE,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE
FROM
FND_LOOKUP_TYPES_TL LT,
FND_LOOKUP_VALUES LV,
HZ_CLASS_CATEGORIES CC,
HZ_CLASS_CATEGORY_USES CCU,
HZ_CLASS_CODE_RELATIONS CCR,
HZ_CLASS_CODE_DENORM DENORM
WHERE 	LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
AND     LT.VIEW_APPLICATION_ID = 222
AND     LV.VIEW_APPLICATION_ID = 222
AND 	CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
AND	DENORM.CLASS_CATEGORY = CCR.CLASS_CATEGORY
AND	CCU.CLASS_CATEGORY = CCR.CLASS_CATEGORY
AND 	LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
AND 	LT.LANGUAGE = LV.LANGUAGE
AND     DENORM.LANGUAGE = LT.LANGUAGE
--AND 	CC.ALLOW_MULTI_PARENT_FLAG = 'N'
AND 	CCU.OWNER_TABLE='HZ_PARTIES'
AND 	DENORM.CLASS_CODE = CCR.CLASS_CODE
AND 	CCR.SUB_CLASS_CODE = LV.LOOKUP_CODE
AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate)
AND 	DENORM.CODE_LEVEL = i-1
UNION
SELECT
CC.CLASS_CATEGORY,
CCR.SUB_CLASS_CODE,
LV.MEANING,
LV.DESCRIPTION,
LT.LANGUAGE,
DENORM.CONCAT_CLASS_CODE||NVL(CC.DELIMITER,'/')||CCR.SUB_CLASS_CODE,
DENORM.CONCAT_CLASS_CODE_MEANING||NVL(CC.DELIMITER,'/')||LV.MEANING,
i,
LV.START_DATE_ACTIVE,
LV.END_DATE_ACTIVE,
LV.ENABLED_FLAG,
'Y',
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
SYSDATE,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE
FROM
FND_LOOKUP_TYPES_TL LT,
FND_LOOKUP_VALUES LV,
HZ_CLASS_CATEGORIES CC,
HZ_CLASS_CATEGORY_USES CCU,
HZ_CLASS_CODE_RELATIONS CCR,
HZ_CLASS_CODE_DENORM DENORM
WHERE 	LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
AND     LT.VIEW_APPLICATION_ID = 222
AND     LV.VIEW_APPLICATION_ID = 222
AND 	CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
AND	DENORM.CLASS_CATEGORY = CCR.CLASS_CATEGORY
AND	CCU.CLASS_CATEGORY = CCR.CLASS_CATEGORY
AND 	LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
AND 	LT.LANGUAGE = LV.LANGUAGE
AND     DENORM.LANGUAGE = LT.LANGUAGE
--AND 	CC.ALLOW_MULTI_PARENT_FLAG = 'N'
AND 	CCU.OWNER_TABLE='HZ_PARTIES'
AND 	DENORM.CLASS_CODE = CCR.CLASS_CODE
AND 	CCR.SUB_CLASS_CODE = LV.LOOKUP_CODE
AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate)
AND 	DENORM.CODE_LEVEL = i -1;

Line: 308


	-- set selectable_flag based on allow_leaf_node_only_flag

     /* Bug 2657352. Removed hz_class_code_relations rel1 and fnd_lookup_values lv from the join.
      *              Furthermore considered the date range of the relationships in
      *              hz_class_code_relations
      *
      *	update hz_class_code_denorm denorm
      *	set selectable_flag ='N'
      *	where exists (select 'x'
      *		      from  hz_class_code_relations rel1,
      *				 hz_class_code_relations rel2,
      *				  fnd_lookup_values lv,
      *				 hz_class_categories cc
      *				where lv.lookup_type = denorm.class_category
      *				 and lv.VIEW_APPLICATION_ID = 222
      *				 and denorm.class_category= rel1.class_category
      *				 and denorm.class_category= rel2.class_category
      *				 and cc.class_category = denorm.class_category
      *				 and  (rel1.sub_class_code = denorm.class_code
      *				 or lv.lookup_code = rel1.sub_class_code)
      *				 and rel2.class_code = denorm.class_code
      *				 and cc.allow_leaf_node_only_flag = 'Y');

Line: 332


      	UPDATE hz_class_code_denorm denorm
      	SET    selectable_flag ='N'
      	WHERE EXISTS
	      (SELECT 'X'
      	       FROM   hz_class_code_relations rel1,
                      hz_class_categories cc
      	       WHERE  denorm.class_category =  rel1.class_category    AND
      		      cc.class_category     =  denorm.class_category  AND
      	              rel1.class_code       =  denorm.class_code      AND
		      SYSDATE               >= rel1.start_date_active AND
		      SYSDATE               <= NVL( rel1.end_date_active , SYSDATE + 1) AND
      		      cc.allow_leaf_node_only_flag = 'Y');

Line: 347


	update hz_class_categories
        set frozen_flag = 'Y'
        where (frozen_flag = 'N' or frozen_flag is null);

Line: 353


	-- insert first level nodes

	INSERT  INTO HZ_CLASS_CODE_DENORM (
	CLASS_CATEGORY,
	CLASS_CODE,
	CLASS_CODE_MEANING,
	CLASS_CODE_DESCRIPTION,
	LANGUAGE,
	CONCAT_CLASS_CODE,
	CONCAT_CLASS_CODE_MEANING,
	CODE_LEVEL,
	START_DATE_ACTIVE,
	END_DATE_ACTIVE,
	ENABLED_FLAG,
	SELECTABLE_FLAG,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	LAST_UPDATE_DATE,
	REQUEST_ID,
	PROGRAM_ID,
	PROGRAM_APPLICATION_ID,
	PROGRAM_UPDATE_DATE
)
SELECT
CC.CLASS_CATEGORY,
LV.LOOKUP_CODE,
LV.MEANING ,
LV.DESCRIPTION,
LT.LANGUAGE,
LV.LOOKUP_CODE,
LV.MEANING ,
1,
LV.START_DATE_ACTIVE,
LV.END_DATE_ACTIVE,
LV.ENABLED_FLAG,
'Y',
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
SYSDATE,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE
FROM
FND_LOOKUP_TYPES_TL LT,
FND_LOOKUP_VALUES LV,
HZ_CLASS_CATEGORIES CC,
HZ_CLASS_CATEGORY_USES CCU
WHERE	LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
AND     LT.VIEW_APPLICATION_ID = 222
AND     LV.VIEW_APPLICATION_ID = 222
AND 	CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
AND	 LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
AND 	LT.LANGUAGE = LV.LANGUAGE
--AND 	CC.ALLOW_MULTI_PARENT_FLAG = 'N'
AND 	CCU.OWNER_TABLE='HZ_PARTIES'
AND CC.CLASS_CATEGORY = P_CLASS_CATEGORY
AND 	NOT EXISTS(
		SELECT 'X'
		FROM HZ_CLASS_CODE_RELATIONS CCR
		WHERE LV.LOOKUP_CODE = CCR.SUB_CLASS_CODE
		AND CCR.CLASS_CATEGORY = LT.LOOKUP_TYPE
		AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate));

Line: 426


	INSERT  INTO HZ_CLASS_CODE_DENORM (
	CLASS_CATEGORY,
	CLASS_CODE,
	CLASS_CODE_MEANING,
	CLASS_CODE_DESCRIPTION,
	LANGUAGE,
	CONCAT_CLASS_CODE,
	CONCAT_CLASS_CODE_MEANING,
	CODE_LEVEL,
	START_DATE_ACTIVE,
	END_DATE_ACTIVE,
	ENABLED_FLAG,
	SELECTABLE_FLAG,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	LAST_UPDATE_DATE,
	REQUEST_ID,
	PROGRAM_ID,
	PROGRAM_APPLICATION_ID,
	PROGRAM_UPDATE_DATE
)
SELECT
CC.CLASS_CATEGORY,
CCR.SUB_CLASS_CODE,
LV.MEANING,
LV.DESCRIPTION,
LT.LANGUAGE,
DENORM.CONCAT_CLASS_CODE||NVL(CC.DELIMITER,'/')||CCR.SUB_CLASS_CODE,
DENORM.CONCAT_CLASS_CODE_MEANING||NVL(CC.DELIMITER,'/')||LV.MEANING,
i,
LV.START_DATE_ACTIVE,
LV.END_DATE_ACTIVE,
LV.ENABLED_FLAG,
'Y',
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
SYSDATE,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE
FROM
FND_LOOKUP_TYPES_TL LT,
FND_LOOKUP_VALUES LV,
HZ_CLASS_CATEGORIES CC,
HZ_CLASS_CATEGORY_USES CCU,
HZ_CLASS_CODE_RELATIONS CCR,
HZ_CLASS_CODE_DENORM DENORM
WHERE 	LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
AND     LT.VIEW_APPLICATION_ID = 222
AND     LV.VIEW_APPLICATION_ID = 222
AND 	CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
AND	DENORM.CLASS_CATEGORY = CCR.CLASS_CATEGORY
AND	CCU.CLASS_CATEGORY = CCR.CLASS_CATEGORY
AND 	LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
AND 	LT.LANGUAGE = LV.LANGUAGE
AND     DENORM.LANGUAGE = LT.LANGUAGE
--AND 	CC.ALLOW_MULTI_PARENT_FLAG = 'N'
AND 	CCU.OWNER_TABLE='HZ_PARTIES'
AND 	DENORM.CLASS_CODE = CCR.CLASS_CODE
AND 	CCR.SUB_CLASS_CODE = LV.LOOKUP_CODE
AND CC.CLASS_CATEGORY = P_CLASS_CATEGORY
AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate)
AND 	DENORM.CODE_LEVEL = i-1
UNION
SELECT
CC.CLASS_CATEGORY,
CCR.SUB_CLASS_CODE,
LV.MEANING,
LV.DESCRIPTION,
LT.LANGUAGE,
DENORM.CONCAT_CLASS_CODE||NVL(CC.DELIMITER,'/')||CCR.SUB_CLASS_CODE,
DENORM.CONCAT_CLASS_CODE_MEANING||NVL(CC.DELIMITER,'/')||LV.MEANING,
i,
LV.START_DATE_ACTIVE,
LV.END_DATE_ACTIVE,
LV.ENABLED_FLAG,
'Y',
NVL(FND_GLOBAL.USER_ID,-1),
SYSDATE,
NVL(FND_GLOBAL.USER_ID,-1),
NVL(FND_GLOBAL.LOGIN_ID,-1),
SYSDATE,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE
FROM
FND_LOOKUP_TYPES_TL LT,
FND_LOOKUP_VALUES LV,
HZ_CLASS_CATEGORIES CC,
HZ_CLASS_CATEGORY_USES CCU,
HZ_CLASS_CODE_RELATIONS CCR,
HZ_CLASS_CODE_DENORM DENORM
WHERE 	LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
AND     LT.VIEW_APPLICATION_ID = 222
AND     LV.VIEW_APPLICATION_ID = 222
AND 	CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
AND	DENORM.CLASS_CATEGORY = CCR.CLASS_CATEGORY
AND	CCU.CLASS_CATEGORY = CCR.CLASS_CATEGORY
AND 	LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
AND 	LT.LANGUAGE = LV.LANGUAGE
AND     DENORM.LANGUAGE = LT.LANGUAGE
--AND 	CC.ALLOW_MULTI_PARENT_FLAG = 'N'
AND 	CCU.OWNER_TABLE='HZ_PARTIES'
AND 	DENORM.CLASS_CODE = CCR.CLASS_CODE
AND 	CCR.SUB_CLASS_CODE = LV.LOOKUP_CODE
AND CC.CLASS_CATEGORY = P_CLASS_CATEGORY
AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate)
AND 	DENORM.CODE_LEVEL = i -1;

Line: 549


	-- set selectable_flag based on allow_leaf_node_only_flag

    /* Bug 2657352.Removed joins to hz_class_code_relations rel1, fnd_lookup_values lv,
     *             hz_class_categories.
     *             Considered date range in hz_class_code_relations
     *             Performed the update only if allow_leaf_node_flag is 'Y'.
     *             Used an anonymous block so that resources for variable
     *             l_allow_leaf_node_only_flag are released after this block.
     *
     *	update hz_class_code_denorm denorm
     *	set selectable_flag ='N'
     *	where denorm.class_category = p_class_category
     *	and   exists (select 'x'
     *		      from  hz_class_code_relations rel1,
     *				 hz_class_code_relations rel2,
     *				  fnd_lookup_values lv,
     *				 hz_class_categories cc
     *				where lv.lookup_type = denorm.class_category
     *				 and lv.VIEW_APPLICATION_ID = 222
     *				 and denorm.class_category= rel1.class_category
     *				 and denorm.class_category= rel2.class_category
     *				 and cc.class_category = denorm.class_category
     *				 AND CC.CLASS_CATEGORY = P_CLASS_CATEGORY
     *				 and  (rel1.sub_class_code = denorm.class_code
     *				 or lv.lookup_code = rel1.sub_class_code)
     *				 and rel2.class_code = denorm.class_code
     *				 and cc.allow_leaf_node_only_flag = 'Y');

Line: 581


         SELECT allow_leaf_node_only_flag
         INTO   l_allow_leaf_node_only_flag
         FROM   HZ_CLASS_CATEGORIES
         WHERE  class_category = p_class_category;

Line: 589


             UPDATE hz_class_code_denorm denorm
             SET    selectable_flag ='N'
             WHERE  denorm.class_category = p_class_category AND
	            EXISTS
		        (SELECT 'x'
		         FROM   hz_class_code_relations rel1
			 WHERE  denorm.class_category = rel1.class_category AND
				rel1.class_code = denorm.class_code AND
				SYSDATE >= rel1.start_date_active AND
				SYSDATE <= NVL(rel1.end_date_active, SYSDATE + 1)
       		        );

Line: 604


	update hz_class_categories
        set frozen_flag = 'Y'
        where class_category = p_class_category
        and (frozen_flag = 'N' or frozen_flag is null);

Line: 619


	--Write_Log(G_DEBUG_CONCURRENT, 1, 'Error in insert_class_codes: '||SQLCODE);

Line: 622


END insert_class_codes;

Line: 660


		delete from hz_class_code_denorm where class_category = p_class_category;

Line: 664


          insert_class_codes(ERRBUF, RETCODE, p_class_category);