1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| ; WITH SR1 as (
SELECT DISTINCT VTD.ID_TIERS,VTD.TIERS_DESC,VTD.ID_TOUR,VTE.DATE_PROCHAINE
FROM V_TOURNEE_D VTD, V_TOURNEE_E VTE
WHERE
VTD.TYPE_TIER = 'CLIENT'
AND VTD.ID_TOUR = VTE.ID_TOUR
AND VTE.DATE_PROCHAINE = TO_CHAR(SYSDATE +1,'DD MM YYYY')
AND VTD.ID_TOUR NOT LIKE 'DEP%'
AND VTD.ID_TOUR NOT LIKE '%SEM%'
AND VTD.ID_TIERS = ANY ('0177','4656','0727','2655','0172','0256','0705','0710','0591','0680','0798','0699','0684','0681','0682','0677','0703','0311','0270','1138','0106','0158','0411','2289','2258','3105','0091','0139','0105','0799','0082','0303','0627','0181','0182','0188','0215','0765','0669','0419','0368','0183','1464','1463','1461','1462','0271','0272','0365','0268','2029','0363','0343','4737','0324','0321','0391','0404','0308','0307','0638','1312','0302','0099','0085','0538','0539','0390','4736','0381','0083','0084','0229','0392','0323','4711','4714','4721','4701','0372','0373','0377','0374','0520','1379','0335','0243','0375','0379','0398','0412','4712','0791','0792','0789','0788','0192','1532','1542','1543','1620','1617','1555','1962','1281','1107','1360','1361','1363','1364','1369','1366','1376','1324','1129','1151','1442','1200','1160','1171','1284','0361','1157','1131','1115','1028','1072','1070','1110','2275','1114','2312','2313','1426','1405','1410','1415','1404','1432','1417','1440','1400','1331','1077','1323','1322','1346','1347','1316','1326','1290','1396','1302','1211','1215','1241','1254','1256','1257','1258','1120','1262','2552','1243','1370','1102','1180','1188','1197','1304','1111','1105','4190','4426','4130','4065','4075','4078','4270','4230','4400','4408','4407','4410','4415','4405','4124','4112','4150','4102','4101','4090','4066','4089','4170','4160','4171','4425','4420','4141','4120','4104','4072','4076','4073','4071','4145','4093','4034','4203','4316','4340','4056','4305','4083','4217','4201','4235','4204','4211','4213','4321','4086','4085','4336','4322','4330','4220','4228','4246','4067','4240','4245','4250','4218','4219','4255','4260','4335','4345','4320','4346')
) , SR2 as (
SELECT ID_ORDRE, TO_CHAR(VEC.DATE_ORDRE,'DD MM YYYY') AS QUAND ,ID_CLIENT AS CLIENT, STATUT
FROM V_ENTETE_COMMANDE VEC
WHERE
(VEC.ID_SITE_RATTACHE = 'PLE') AND(VEC.STATUT <> 'CONSTRUCTION')
AND (VEC.ID_ORDRE LIKE 'PV%')
AND(VEC.ID_ORDRE_RECEPTION IS NULL)
AND VEC.DATE_ORDRE > TO_CHAR(SYSDATE +0,'DD MM YYYY')
)
select *
from SR1
JOIN SR2
on SR1.ID_TIERS = SR2.ID_CLIENT |
Partager