HRS_APPITM_04VW (SQL View)	Index Back
App Content Items No JPM Intgr Used if JPM Integration is off, gets content items with field name = 'JPM_CATG_ITEM_ID' or JPM_ADHOC_FLG = Y. Used by Build Applicant Index to derive which content items are included in the verity search collection.

HRS_APPITM_04VW

(SQL View)

App Content Items No JPM Intgr

Used if JPM Integration is off, gets content items with field name = 'JPM_CATG_ITEM_ID' or JPM_ADHOC_FLG = Y. Used by Build Applicant Index to derive which content items are included in the verity search collection.

SELECT DISTINCT appitm.hrs_person_id , appitm.hrs_profile_seq , appitm.jpm_cat_type , appitm.jpm_cat_item_id , appitm.jpm_cat_item_qual , appitm.jpm_cat_item_qual2 , appitm.jpm_profile_id , jpmitm.jpm_descr90 FROM PS_HRS_APPIDXDT_TM TM , ps_hrs_app_items appitm , ps_jpm_cat_items jpmitm , ps_jpm_jp_props props , ps_jpm_jp_section sect , ps_jpm_cat_types cat , ps_jpm_jp_pri_type jpmcfg , PS_JPM_JP_ROLES jpmrol WHERE TM.HRS_PERSON_ID=appitm.hrs_person_id AND TM.HRS_PROFILE_SEQ = appitm.hrs_profile_seq AND jpmitm.jpm_cat_type = appitm.jpm_cat_type AND jpmitm.jpm_cat_item_id = appitm.jpm_cat_item_id AND jpmitm.effdt = ( SELECT MAX(effdt) FROM ps_jpm_cat_items_i jpmitm1 WHERE jpmitm1.jpm_cat_type = jpmitm.jpm_cat_type AND jpmitm1.jpm_cat_item_id = jpmitm.jpm_cat_item_id AND jpmitm1.effdt <= %CurrentDateIn) AND jpmitm.eff_status = 'A' AND cat.jpm_cat_type = jpmitm.jpm_cat_type AND props.jpm_jp_type = jpmrol.jpm_jp_type AND props.FIELDNAME = 'JPM_CAT_ITEM_ID' AND sect.jpm_jp_type = jpmrol.jpm_jp_type AND sect.jpm_jp_section_id = jpmrol.jpm_jp_section_id AND jpmrol.effdt = ( SELECT MAX(effdt) FROM PS_JPM_JP_ROLES jpmrol2 WHERE jpmrol2.jpm_jp_type = jpmrol.jpm_jp_type AND jpmrol2.jpm_jp_section_id = jpmrol.jpm_jp_section_id AND jpmrol2.jpm_role = jpmrol.jpm_role AND jpmrol2.effdt <= %CurrentDateIn) AND jpmrol.jpm_role = 'EMP' AND jpmrol.jpm_scrty_modify = 'Y' AND sect.jpm_jp_type = jpmcfg.jpm_pri_pers_type AND sect.jpm_cat_type = cat.jpm_cat_type AND sect.effdt = ( SELECT MAX(effdt) FROM ps_jpm_jp_section sect2 WHERE sect2.jpm_jp_type = sect.jpm_jp_type AND sect2.jpm_jp_section_id = sect.jpm_jp_section_id AND sect2.effdt <= %CurrentDateIn) AND props.jpm_jp_type = jpmcfg.jpm_pri_pers_type AND props.jpm_jp_type = sect.jpm_jp_type AND props.jpm_jp_section_id = sect.jpm_jp_section_id AND props.effdt = ( SELECT MAX(effdt) FROM ps_jpm_jp_props props1 WHERE props1.jpm_jp_type = props.jpm_jp_type AND props1.jpm_jp_section_id = props.jpm_jp_section_id AND props1.fieldname = props.fieldname AND props1.effdt <= %CurrentDateIn) AND sect.effdt = ( SELECT MAX(effdt) FROM ps_jpm_jp_section sect1 WHERE sect1.jpm_jp_type = sect.jpm_jp_type AND sect1.jpm_jp_section_id = sect.jpm_jp_section_id AND sect1.effdt <= %CurrentDateIn) UNION SELECT DISTINCT appitm.hrs_person_id , appitm.hrs_profile_seq , appitm.jpm_cat_type , appitm.jpm_cat_item_id , appitm.jpm_cat_item_qual , appitm.jpm_cat_item_qual2 , appitm.jpm_profile_id , appitm.jpm_adhoc_descr FROM PS_HRS_APPIDXDT_TM TM , ps_hrs_app_items appitm , ps_jpm_jp_props props , ps_jpm_jp_section sect , ps_jpm_cat_types cat , ps_jpm_jp_pri_type jpmcfg , PS_JPM_JP_ROLES jpmrol WHERE TM.HRS_PERSON_ID=appitm.hrs_person_id AND TM.HRS_PROFILE_SEQ = appitm.hrs_profile_seq AND cat.jpm_cat_type = appitm.jpm_cat_type AND cat.JPM_ADHOC_FLG = 'Y' AND props.jpm_jp_type = jpmrol.jpm_jp_type AND props.FIELDNAME = 'JPM_ADHOC_DESCR' AND sect.jpm_jp_type = jpmrol.jpm_jp_type AND sect.jpm_jp_section_id = jpmrol.jpm_jp_section_id AND jpmrol.effdt = ( SELECT MAX(effdt) FROM PS_JPM_JP_ROLES jpmrol2 WHERE jpmrol2.jpm_jp_type = jpmrol.jpm_jp_type AND jpmrol2.jpm_jp_section_id = jpmrol.jpm_jp_section_id AND jpmrol2.jpm_role = jpmrol.jpm_role AND jpmrol2.effdt <= %CurrentDateIn) AND jpmrol.jpm_role = 'EMP' AND jpmrol.jpm_scrty_modify = 'Y' AND sect.jpm_jp_type = jpmcfg.jpm_pri_pers_type AND sect.jpm_cat_type = cat.jpm_cat_type AND sect.effdt = ( SELECT MAX(effdt) FROM ps_jpm_jp_section sect2 WHERE sect2.jpm_jp_type = sect.jpm_jp_type AND sect2.jpm_jp_section_id = sect.jpm_jp_section_id AND sect2.effdt <= %CurrentDateIn) AND props.jpm_jp_type = jpmcfg.jpm_pri_pers_type AND props.jpm_jp_type = sect.jpm_jp_type AND props.jpm_jp_section_id = sect.jpm_jp_section_id AND props.effdt = ( SELECT MAX(effdt) FROM ps_jpm_jp_props props1 WHERE props1.jpm_jp_type = props.jpm_jp_type AND props1.jpm_jp_section_id = props.jpm_jp_section_id AND props1.fieldname = props.fieldname AND props1.effdt <= %CurrentDateIn) AND sect.effdt = ( SELECT MAX(effdt) FROM ps_jpm_jp_section sect1 WHERE sect1.jpm_jp_type = sect.jpm_jp_type AND sect1.jpm_jp_section_id = sect.jpm_jp_section_id AND sect1.effdt <= %CurrentDateIn)

Related Language Record: HRS_APPITM_04LV

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id
Prompt Table: HRS_APPLICANT

2 HRS_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece

3 JPM_CAT_TYPE Character(12) VARCHAR2(12) NOT NULL The name of the type of items that will be used in a Catalog and or in a Profile.
Prompt Table: JPM_CAT_TYPE_VW

4 JPM_CAT_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Catalog Item Id. Identifies a unique catalog item defintion within a catalog type.
Prompt Table: JPM_CAT_ITEMS

5 JPM_CAT_ITEM_QUAL Character(12) VARCHAR2(12) NOT NULL Low Order Item key for Catalog Items when appearing in a Profile. For most Catalog Types, this will be blank - but is available if an Item needs to have multiple instances for a given Effdt in a Profile. Example: for CatalogType = Compentency, there can be multiple instances for a particular Competency, each representing a different evaluation source (Self-Eval, Mgr Eval, Approved/Official, Learning, Recruiting).

6 JPM_CAT_ITEM_QUAL2 Character(12) VARCHAR2(12) NOT NULL Low Order Item key for Catalog Items when appearing in a Profile. For most Catalog Types, this will be blank - but is available if an Item needs to have multiple instances for a given Effdt in a Profile. Example: for CatalogType = Compentency, there can be multiple instances for a particular Competency, each representing a different evaluation source (Self-Eval, Mgr Eval, Approved/Official, Learning, Recruiting).

7 JPM_PROFILE_ID Character(12) VARCHAR2(12) NOT NULL The id, autoassigned or user assigned of the profile. This id is used to group items and other related material together into one coherent profile.

8 JPM_DESCR90 Character(90) VARCHAR2(90) NOT NULL jpm

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	HRS_PERSON_ID	Number(15,0)	DECIMAL(15) NOT NULL	Person Id Prompt Table: HRS_APPLICANT
2	HRS_PROFILE_SEQ	Number(3,0)	SMALLINT NOT NULL	Profile sequece
3	JPM_CAT_TYPE	Character(12)	VARCHAR2(12) NOT NULL	The name of the type of items that will be used in a Catalog and or in a Profile. Prompt Table: JPM_CAT_TYPE_VW
4	JPM_CAT_ITEM_ID	Character(12)	VARCHAR2(12) NOT NULL	Catalog Item Id. Identifies a unique catalog item defintion within a catalog type. Prompt Table: JPM_CAT_ITEMS
5	JPM_CAT_ITEM_QUAL	Character(12)	VARCHAR2(12) NOT NULL	Low Order Item key for Catalog Items when appearing in a Profile. For most Catalog Types, this will be blank - but is available if an Item needs to have multiple instances for a given Effdt in a Profile. Example: for CatalogType = Compentency, there can be multiple instances for a particular Competency, each representing a different evaluation source (Self-Eval, Mgr Eval, Approved/Official, Learning, Recruiting).
6	JPM_CAT_ITEM_QUAL2	Character(12)	VARCHAR2(12) NOT NULL	Low Order Item key for Catalog Items when appearing in a Profile. For most Catalog Types, this will be blank - but is available if an Item needs to have multiple instances for a given Effdt in a Profile. Example: for CatalogType = Compentency, there can be multiple instances for a particular Competency, each representing a different evaluation source (Self-Eval, Mgr Eval, Approved/Official, Learning, Recruiting).
7	JPM_PROFILE_ID	Character(12)	VARCHAR2(12) NOT NULL	The id, autoassigned or user assigned of the profile. This id is used to group items and other related material together into one coherent profile.
8	JPM_DESCR90	Character(90)	VARCHAR2(90) NOT NULL	jpm