SAP (Documents/Screen shots/Complete Solution): RE: RE:[peopletools-l] Max Completion Date

Reply from rlynds on Jan 3 at 5:14 PM

Looking at the code, I see the bind variable :1 being used for both a character and numeric field. I am guessing that the value in your parameter is not numeric.
LM_CI_ID is numeric
LM_CRSE_CODE is a character field

---------------Original Message---------------
From: malmoula
Sent: Tuesday, January 03, 2012 5:06 PM
Subject: Max Completion Date

Thanks. I tried the above and got the following error. Any idea what it means?

Error in running query because of SQL Error, Code=1722, Message=ORA-01722: invalid number (50,380)

Query SQL:
SELECT F.LM_HR_EMPLID, G.NAME, A.LM_HR_EMPLID, B.NAME, C.LM_PER_ORG, D.LM_STTS, TO_CHAR(D.LM_ENRL_DT,'YYYY-MM-DD'), TO_CHAR(D.LM_COMPL_DT,'YYYY-MM-DD'), E.LM_CS_LONG_NM, E.LM_CRSE_CODE, TO_CHAR(SYSDATE,'YYYY-MM-DD'),E.LM_CI_ID,TO_CHAR(E.EFFDT,'YYYY-MM-DD')
FROM PS_LM_PERSON A, PS_LM_PERSON_NAME B, PS_LM_PERSON_ATTRB C, PS_LM_ENRLMT D, PS_LM_CI_TBL E, PS_LM_PERSON F, PS_LM_PERSON_NAME G
WHERE A.LM_PERSON_ID = B.LM_PERSON_ID
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_LM_PERSON_NAME B_ED
WHERE B.LM_PERSON_ID = B_ED.LM_PERSON_ID
AND B.LM_NAME_TYPE = B_ED.LM_NAME_TYPE
AND B_ED.EFFDT <= SYSDATE)
AND B.LM_PERSON_ID = C.LM_PERSON_ID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_LM_PERSON_ATTRB C_ED
WHERE C.LM_PERSON_ID = C_ED.LM_PERSON_ID
AND C.LM_EMPL_RCD = C_ED.LM_EMPL_RCD
AND C_ED.EFFDT <= SYSDATE)
AND C.LM_PERSON_ID = D.LM_PERSON_ID
AND C.LM_EMPL_RCD = D.LM_EMPL_RCD
AND E.LM_CI_ID = D.LM_CI_ID
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_LM_CI_TBL E_ED
WHERE E.LM_CI_ID = E_ED.LM_CI_ID
AND E_ED.EFFDT <= SYSDATE)
AND F.LM_PERSON_ID = C.LM_MANAGER_ID
AND F.LM_PERSON_ID = G.LM_PERSON_ID
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_LM_PERSON_NAME G_ED
WHERE G.LM_PERSON_ID = G_ED.LM_PERSON_ID
AND G.LM_NAME_TYPE = G_ED.LM_NAME_TYPE
AND G_ED.EFFDT <= SYSDATE)
AND D.LM_COMPL_DT = (SELECT MAX( H.LM_COMPL_DT)
FROM PS_LM_ENRLMT H
WHERE D.LM_PERSON_ID = H.LM_PERSON_ID
AND H.LM_CI_ID = (SELECT I.LM_CI_ID
FROM PS_LM_CI_TBL I
WHERE I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_LM_CI_TBL I_ED
WHERE I.LM_CI_ID = I_ED.LM_CI_ID
AND I_ED.EFFDT <= SYSDATE)
AND I.LM_CI_ID = :1
AND I.LM_CI_ID = H.LM_CI_ID)
HAVING MAX( H.LM_COMPL_DT) BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD'))
AND E.LM_CRSE_CODE = :1
ORDER BY 2, 3, 8

Reply to this email to post your response.

__.____._

Manage Settings | Unsubscribe | Create FAQ | Send Feedback