HRS_APPITM_03LV (SQL View)	Index Back
App Cont Items Lng w JPM Intgr Related Language View Used if JPM Integration is on, gets searchable content items. Used by Build Applicant Index to derive which content items are included in the verity search collection.

HRS_APPITM_03LV

(SQL View)

App Cont Items Lng w JPM Intgr

Related Language View Used if JPM Integration is on, gets searchable content items. 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 , jpmitml.language_cd , jpmitml.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 , ps_jpm_cat_itm_lng jpmitml 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.jpm_srch_flg = 'Y' 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) AND jpmitml.jpm_cat_type = jpmitm.jpm_cat_type AND jpmitml.jpm_cat_item_id = jpmitm.jpm_cat_item_id AND jpmitml.effdt = jpmitm.effdt

Related Language Record for HRS_APPITM_03VW

# 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code

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	LANGUAGE_CD	Character(3)	VARCHAR2(3) NOT NULL	Language Code
8	JPM_DESCR90	Character(90)	VARCHAR2(90) NOT NULL	jpm