SELECT HST1.LM_LRQ_ID , HST1.SEQNUM , 0 , A.LM_CATG_ID , C.LANGUAGE_CD , D.LM_TITLE , C.LM_CS_LONG_NM FROM PS_LM_LRN_REQ A , PS_LM_CATG_LANG C , PS_LM_LRQ_TRK_HST HST1 , PS_LM_LRN_REQ_LANG D WHERE A.LM_CATG_ID = C.LM_CATG_ID AND HST1.LM_LRQ_ID = A.LM_LRQ_ID AND A.LM_LRQ_ID = D.LM_LRQ_ID AND C.LANGUAGE_CD = D.LANGUAGE_CD UNION SELECT HST2.LM_LRQ_ID , HST2.SEQNUM , 0 , A1.LM_CATG_ID , D1.LANGUAGE_CD , D1.LM_TITLE , C1.LM_CS_LONG_NM FROM PS_LM_LRN_REQ A1 , PS_LM_CATG_TBL C1 , PS_LM_LRQ_TRK_HST HST2 , PS_LM_LRN_REQ_LANG D1 WHERE A1.LM_CATG_ID = C1.LM_CATG_ID AND HST2.LM_LRQ_ID = A1.LM_LRQ_ID AND A1.LM_LRQ_ID = D1.LM_LRQ_ID AND NOT EXISTS ( SELECT 'X' FROM PS_LM_CATG_LANG H WHERE H.LM_CATG_ID = C1.LM_CATG_ID AND H.LANGUAGE_CD = D1.LANGUAGE_CD) UNION SELECT HST3.LM_LRQ_ID , HST3.SEQNUM , 0 , A2.LM_CATG_ID , C2.LANGUAGE_CD , A2.LM_TITLE , C2.LM_CS_LONG_NM FROM PS_LM_LRN_REQ A2 , PS_LM_CATG_LANG C2 , PS_LM_LRQ_TRK_HST HST3 WHERE A2.LM_CATG_ID = C2.LM_CATG_ID AND HST3.LM_LRQ_ID = A2.LM_LRQ_ID AND NOT EXISTS ( SELECT 'X' FROM PS_LM_LRN_REQ_LANG G WHERE G.LM_LRQ_ID = A2.LM_LRQ_ID AND G.LANGUAGE_CD = C2.LANGUAGE_CD) UNION SELECT HST4.LM_LRQ_ID , HST4.SEQNUM , A3.LM_CI_ID , 0 , D3.LANGUAGE_CD , F3.LM_TITLE , D3.LM_CS_LONG_NM FROM PS_LM_LRN_REQ A3 , PS_LM_CI_TBL C3 , PS_LM_LRQ_TRK_HST HST4 , PS_LM_CI_LANG D3 , PS_LM_LRN_REQ_LANG F3 WHERE HST4.LM_LRQ_ID = A3.LM_LRQ_ID AND A3.LM_CI_ID = C3.LM_CI_ID AND C3.LM_CI_ID = D3.LM_CI_ID AND A3.LM_LRQ_ID = F3.LM_LRQ_ID AND %CurrentDateIn BETWEEN C3.EFFDT AND C3.LM_END_EFFDT AND D3.LANGUAGE_CD = F3.LANGUAGE_CD AND C3.LM_CI_STATUS = '10' UNION SELECT HST5.LM_LRQ_ID , HST5.SEQNUM , E6.LM_CI_ID , 0 , F6.LANGUAGE_CD , F6.LM_TITLE , E6.LM_CS_LONG_NM FROM PS_LM_LRN_REQ D6 , PS_LM_CI_TBL E6 , PS_LM_LRQ_TRK_HST HST5 , PS_LM_LRN_REQ_LANG F6 WHERE HST5.LM_LRQ_ID = D6.LM_LRQ_ID AND D6.LM_CI_ID = E6.LM_CI_ID AND D6.LM_LRQ_ID = F6.LM_LRQ_ID AND %CurrentDateIn BETWEEN E6.EFFDT AND E6.LM_END_EFFDT AND E6.LM_CI_STATUS = '10' AND NOT EXISTS ( SELECT 'X' FROM PS_LM_CI_LANG H3 WHERE H3.LM_CI_ID = E6.LM_CI_ID AND H3.LANGUAGE_CD = F6.LANGUAGE_CD) UNION SELECT HST6.LM_LRQ_ID , HST6.SEQNUM , E5.LM_CI_ID , 0 , F5.LANGUAGE_CD , D5.LM_TITLE , F5.LM_CS_LONG_NM FROM PS_LM_LRN_REQ D5 , PS_LM_CI_TBL E5 , PS_LM_LRQ_TRK_HST HST6 , PS_LM_CI_LANG F5 WHERE HST6.LM_LRQ_ID = D5.LM_LRQ_ID AND D5.LM_CI_ID = E5.LM_CI_ID AND E5.LM_CI_ID = F5.LM_CI_ID AND %CurrentDateIn BETWEEN E5.EFFDT AND E5.LM_END_EFFDT AND E5.LM_CI_STATUS = '10' AND NOT EXISTS ( SELECT 'X' FROM PS_LM_LRN_REQ_LANG H5 WHERE H5.LM_LRQ_ID = D5.LM_LRQ_ID AND H5.LANGUAGE_CD = F5.LANGUAGE_CD)
|