FO_PNDGAPPL_VW2

(SQL View)
 Index Back

Pending Applicants List

Pending Applicants List View used to populate the grid data on the pending applicants list pagelet.

SELECT A.APPLID , B.EFFDT , D.BUSINESS_UNIT , D.SO_ID , D.SO_LINE , C.SALES_OPRID , B.NAME , A.APP_STATUS_CODE , CL.DESCR , CL.CUST_ID , CL.MANAGER_CONTACT_ID FROM PS_APPLICANT A , PS_APPL_DATA_EFFDT B , PS_RS_SO_HDR C , PS_FO_SO_LINE_MTCH D , PS_RS_SO_LINE CL WHERE A.APP_STATUS_CODE = 'A' AND A.APPLID = B.APPLID AND B.EFFDT = ( SELECT MAX(B_EF.EFFDT) FROM PS_APPL_DATA_EFFDT B_EF WHERE B_EF.APPLID = B.APPLID AND B_EF.EFFDT <= %CurrentDateIn) AND A.APPLID = D.CANDIDATE_ID AND D.CANDIDATE_TYPE = 'A' AND D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.SO_ID = C.SO_ID AND D.OFFER_RESULT NOT IN ('S','D') AND CL.BUSINESS_UNIT = D.BUSINESS_UNIT AND CL.SO_ID = D.SO_ID AND CL.SO_LINE = D.SO_LINE UNION SELECT E.APPLID , F.EFFDT , ' ' , ' ' , 0 , O.OPRID , F.NAME , E.APP_STATUS_CODE , ' ' , ' ' , ' ' FROM PS_APPLICANT E , PS_APPL_DATA_EFFDT F , PS_FO_APP_OWNERS O WHERE E.APP_STATUS_CODE = 'A' AND E.APPLID = F.APPLID AND F.EFFDT = ( SELECT MAX(F_EF.EFFDT) FROM PS_APPL_DATA_EFFDT F_EF WHERE F.APPLID = F_EF.APPLID AND F_EF.EFFDT <= %CurrentDateIn) AND O.APPLID = E.APPLID AND NOT EXISTS ( SELECT DISTINCT 'X' FROM PS_FO_SO_LINE_MTCH G WHERE G.CANDIDATE_TYPE = 'A' AND G.CANDIDATE_ID = E.APPLID) UNION SELECT H.APPLID , I.EFFDT , J.BUSINESS_UNIT , J.SO_ID , KL.SO_LINE , K.SALES_OPRID , I.NAME , H.APP_STATUS_CODE , KL.DESCR , KL.CUST_ID , KL.MANAGER_CONTACT_ID FROM PS_APPLICANT H , PS_APPL_DATA_EFFDT I , PS_FO_PNDG_ORD_TBL J , PS_RS_SO_HDR K , PS_RS_SO_LINE KL WHERE H.APP_STATUS_CODE = 'P' AND H.APPLID = I.APPLID AND I.EFFDT = ( SELECT MAX(I_EF.EFFDT) FROM PS_APPL_DATA_EFFDT I_EF WHERE I.APPLID = I_EF.APPLID AND I_EF.EFFDT <= %CurrentDateIn) AND J.APPLID = H.APPLID AND J.BUSINESS_UNIT = K.BUSINESS_UNIT AND J.SO_ID = K.SO_ID AND KL.BUSINESS_UNIT = J.BUSINESS_UNIT AND KL.SO_ID = J.SO_ID AND KL.SO_LINE = J.SO_LINE UNION SELECT L.APPLID , M.EFFDT , ' ' , ' ' , 0 , ' ' , M.NAME , L.APP_STATUS_CODE , ' ' , ' ' , ' ' FROM PS_APPLICANT L , PS_APPL_DATA_EFFDT M WHERE L.APPLID = M.APPLID AND L.APP_STATUS_CODE = 'P' AND M.EFFDT = ( SELECT MAX(M_EF.EFFDT) FROM PS_APPL_DATA_EFFDT M_EF WHERE M.APPLID = M_EF.APPLID AND M_EF.EFFDT <= %CurrentDateIn) AND NOT EXISTS ( SELECT DISTINCT 'X' FROM PS_FO_PNDG_ORD_TBL N WHERE N.APPLID = L.APPLID AND N.SO_ID <> ' ')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 APPLID Character(11) VARCHAR2(11) NOT NULL External Job Applicant ID

Prompt Table: APPLICANT

2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
4 SO_ID Character(15) VARCHAR2(15) NOT NULL Service Order ID
5 SO_LINE Number(3,0) SMALLINT NOT NULL Line Number
6 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
7 NAME Character(50) VARCHAR2(50) NOT NULL Name
8 APP_STATUS_CODE Character(1) VARCHAR2(1) NOT NULL Status Code
A=Active
D=Hired
H=Hire Pending
I=Applicant Invalid
P=Applicant Pending
9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
10 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
11 MANAGER_CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Order Contact