HRS_APPITM_04LV (SQL View)	Index Back
App Cont Items Lng no JPM Intg Related Language View Used if JPM Integration is off, gets content items with field name = 'JPM_CATG_ITEM_ID'. Used by Build Applicant Index to derive which content items are included in the verity search collection.

HRS_APPITM_04LV

(SQL View)

App Cont Items Lng no JPM Intg

Related Language View Used if JPM Integration is off, gets content items with field name = 'JPM_CATG_ITEM_ID'. 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.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) AND jpmitml.jpm_cat_type = jpmitm.jpm_cat_type AND jpmitml.jpm_cat_item_id = jpmitml.jpm_cat_item_id AND jpmitml.effdt = jpmitml.effdt

Related Language Record for HRS_APPITM_04VW

# 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