1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| select PROP_CREATED_DATE ,
TVS_CHANNEL.TVS_DESCRIPTION CHANNEL,
CASE OVM_CATEGORY WHEN '9' then 'BIKE'
WHEN '10' then 'BIKE'
WHEN '11' then 'BIKE'
ELSE 'CAR'
END AS PRODUCT, TVS_ACTION.TVS_DESCRIPTION BROKER, APV_ACTION_REFERENCE
from ad_proposals
inner join ad_proposal_versions on PROP_REFERENCE = APV_PROP_REFERENCE
inner join ad_proposal_objects on POB_APV_PROP_REFERENCE = APV_PROP_REFERENCE
and POB_APV_VERSION_NUMBER = APV_VERSION_NUMBER
inner join ov_motors on OVM_OBJ_ID = POB_OVM_OBJ_ID
and POB_OVM_NUMBER = OVM_NUMBER
INNER JOIN table_values TVS_CHANNEL on TVS_CHANNEL.TVS_CODE = PROP_CHANNEL and TVS_CHANNEL.TVS_TAD_CODE = 'CHANNEL'
left outer join table_values TVS_ACTION on TVS_ACTION.TVS_CODE = APV_ACTION_REFERENCE and TVS_ACTION.TVS_TAD_CODE = 'EBROKERID'
where APV_CREATED_DATE >= add_months(trunc(sysdate),-1)
and APV_VERSION_NUMBER = 1
and APV_ORIGIN_TYPE = 'NBV' |
Partager