oracle sql and package

主题: 查询被分配给我的名单

SELECT T1.ROW_ID "id",

T1.STATUS_CD "status",

T1.LAST_UPD + 1 / 3 "lastUpd",

T2.NAME "accountName",

T2.X_ORG_NUMBER "orgNumber",

T3.ROW_ID "assignorId",

T3.LOGIN "assignorLogin",

T4.LAST_NAME "assignorName",

T5.ROW_ID "campaignId",

T5.NAME "campaignName",

T5.X_PA_PROD_LN "productTypeName",

T8.DESC_TEXT "productName",

T6.ROW_ID "assignerId",

T6.LOGIN "assignerLogin",

T7.LAST_NAME "assignerName",

COUNT(T1.ROW_ID) OVER() "totalRecordNum"

FROM SIEBEL.CX_CAMPAIGN_IMP T1,

SIEBEL.S_ORG_EXT T2,

SIEBEL.S_USER T3,

SIEBEL.S_CONTACT T4,

SIEBEL.S_SRC T5,

SIEBEL.S_USER T6,

SIEBEL.S_CONTACT T7,

SIEBEL.S_PROD_INT T8

WHERE T1.X_LOGIN_ID = T3.LOGIN

AND T1.STATUS_CD = '已分配'

AND T3.ROW_ID = T4.PAR_ROW_ID

AND T1.ACCOUNT_ID = T2.ROW_ID

AND (T5.X_PA_STATUS_CD != '活动完成' AND T5.X_PA_STATUS_CD != '活动取消')

AND T1.CAMPAIGN_ID = T5.ROW_ID

AND T1.X_ASSIGNER_LOGIN_ID = T6.LOGIN(+)

AND T6.ROW_ID = T7.PAR_ROW_ID(+)

AND T5.PROD_ID = T8.ROW_ID(+)

AND T3.LOGIN = 'ZHENGHUILING'

ORDER BY T1.LAST_UPD DESC