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

HRS_JOITM04_LVW

(SQL View)

Job Open Items Lng no JPM Intg

Related Language View Used if JPM Integration is off, gets job opening content items with field name = 'JPM_CATG_ITEM_ID'. Used by Job Applicant Index to derive which content items are included in the verity search collection.

SELECT DISTINCT joitm.hrs_job_opening_id , joitm.hrs_jo_rqmt_seq , joitm.jpm_cat_type , joitm.jpm_cat_item_id , joitm.jpm_cat_item_qual , joitm.jpm_cat_item_qual2 , jpmitml.language_cd , jpmitml.jpm_descr90 FROM ps_hrs_jo_items joitm , 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 jpmitm.jpm_cat_type = joitm.jpm_cat_type AND jpmitm.jpm_cat_item_id = joitm.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_JOITM04_VW

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone

2 HRS_JO_RQMT_SEQ Number(3,0) SMALLINT NOT NULL Job opening requirement sequence

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.

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_JOB_OPENING_ID	Number(15,0)	DECIMAL(15) NOT NULL	Job Opening Clone
2	HRS_JO_RQMT_SEQ	Number(3,0)	SMALLINT NOT NULL	Job opening requirement sequence
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.
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