1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
| whenever SQLERROR exit SQL.SQLCODE
conn user/"mdp"@bdc
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
set colsep ';'
set echo off
set heading off
set feedback off
set pages 0
set line 96000
set long 96000
set sqlprompt ''
spool G:\Extract\SDESK\SLM\DEMANDES_CLOSES_XXXXXX.csv
prompt NUMBERPRGN;SA_ASSET_TAG;SA_JOURNAL_UPDATES;STATUS;DESCRIPTION;SA_CAL_ADMIN;SA_CAL_DISPO;ASSIGNED_DEPT;PHASE_START_DATE;RDERED_DATE;UPDATE_DATE;SA_TARGET_COMPLETION_ORIG;TARGET_COMPLETION;PART_NO;PART_DESC;SA_OPEN_GROUP;SA_UPDATE_GROUP;NORMAL_LEAD_TIME;TARGET_LEAD_TIME;PHASE_START_DATE;CLOSE_DATE;SA_DUREE_SUSP;ACTUAL_DATE;SA_BASE_FACTM_EXPLOITE_PAR;SA_BASE_PLATEAU_SERVEUR;LAST_NAME FIRST_NAME
SELECT
T1.NUMBERPRGN||';'||
T1.SA_ASSET_TAG||';'||
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T1.DESCRIPTION,chr(10),'-'),chr(13),'-'),chr(9),'-'),',',' '),';',' ')||';'||
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T1.SA_JOURNAL_UPDATES,chr(10),'-'),chr(13),'-'),chr(9),'-'),',',' '),';',' ')||';'||
T1.STATUS||';'||
T1.SA_CAL_ADMIN||';'||
T1.SA_CAL_DISPO||';'||
T1.ASSIGNED_DEPT||';'||
' '||' '||
T1.PHASE_START_DATE||';'||
' '||' '||
T1.ORDERED_DATE||';'||
' '||' '||
T1.UPDATE_DATE||';'||
' '||' '||
T1.SA_TARGET_COMPLETION_ORIG||';'||
' '||' '||
T1.TARGET_COMPLETION||';'||
T1.PART_NO||';'||
REPLACE(T1.PART_DESC,',',' ')||';'||
T1.SA_OPEN_GROUP||';'||
T1.SA_UPDATE_GROUP||';'||
' '||' '||
T1.NORMAL_LEAD_TIME||';'||
' '||' '||
T1.TARGET_LEAD_TIME||';'||
' '||' '||
T1.PHASE_START_DATE||';'||
' '||' '||
T1.CLOSE_DATE||';'||
T1.SA_DUREE_SUSP||';'||
' '||' '||
T1.ACTUAL_DATE||';'||
T3.SA_BASE_FACTM_EXPLOITE_PAR||';'||
T3.SA_BASE_PLATEAU_SERVEUR||';'||
T2.LAST_NAME||' '||
T2.FIRST_NAME LIGNE
FROM
DEMANDES T1
LEFT OUTER JOIN CONTACT T2 ON T1.ASSIGNED_TO = T2.CONTACT_NAME
LEFT OUTER JOIN DEVICE T3 ON T1.SA_ASSET_TAG = T3.NETWORK_NAME
WHERE
T1.CLOSE_DATE >='01/01/2015' and T1.CLOSE_DATE < '01/07/2015' and (T1.STATUS ='closed' or T1.STATUS ='cancelled') and T1.ASSIGNED_DEPT like '%SERVEUR%';
spool off |
