Problème sur une jointure dans le nombre de réponses [Résolu]

[bpdelavega]

Bonjour,
Je débute sur SQL Oracle et je travaille sur un schema que je ne connais pas beaucoup. Je dois extraire des enregistrements pour un emailing, voici ma requête :

Code :

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
 
SELECT DISTINCT (NAME.title || ' ' || reservation_name.sguest_firstname || ' '|| reservation_name.sguest_name) AS client_nom,
	DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE') AS client_pays,
	   (CASE
		   WHEN opera.reservation_name.resort = 'OPERA'
			  THEN 'Best Western Premier Opera Diamond'
		   WHEN opera.reservation_name.resort = 'BATIGN'
			  THEN 'Opera Batignolles Hotel'
		   WHEN opera.reservation_name.resort = 'PPN'
			  THEN 'Comfort et Quality Hotel Centre del Mon'
		END
   ) AS client_hotel,
   TO_CHAR (trunc_actual_check_in_date,'DD/MM/YYYY') AS hotel_date_entree,
   TO_CHAR (trunc_actual_check_out_date,'DD/MM/YYYY') AS hotel_date_sortie,
   LOWER (phone_number) AS client_email,
   RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
   TRUNC (SYSDATE - TO_DATE (trunc_actual_check_out_date)) AS nb_jours
FROM opera.reservation_name 
 
JOIN OPERA.NAME ON RESERVATION_NAME.NAME_ID = NAME.NAME_ID 
JOIN OPERA.NAME_PHONE ON RESERVATION_NAME.NAME_ID = NAME_PHONE.NAME_ID 
JOIN OPERA.RESERVATION_SUMMARY ON RESERVATION_NAME.RESV_NAME_ID = RESERVATION_SUMMARY.EVENT_ID 
JOIN OPERA.RESORT_ORIGINS_OF_BOOKING ON (RESERVATION_SUMMARY.RESORT = RESORT_ORIGINS_OF_BOOKING.RESORT AND RESERVATION_SUMMARY.SOURCE_CODE = RESORT_ORIGINS_OF_BOOKING.SOURCE_CODE) 
 
WHERE resv_status = 'CHECKED OUT'
AND TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND REGEXP_LIKE (LOWER(phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND TRUNC_ACTUAL_CHECK_OUT_DATE >= to_date('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;

J'obtiens environs 400 lignes, mais quand je fais cette requête :

Code :

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
 
SELECT DISTINCT (NAME.title || ' ' || reservation_name.sguest_firstname || ' '|| reservation_name.sguest_name) AS client_nom,
	DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE') AS client_pays,
	   (CASE
		   WHEN opera.reservation_name.resort = 'OPERA'
			  THEN 'Best Western Premier Opera Diamond'
		   WHEN opera.reservation_name.resort = 'BATIGN'
			  THEN 'Opera Batignolles Hotel'
		   WHEN opera.reservation_name.resort = 'PPN'
			  THEN 'Comfort et Quality Hotel Centre del Mon'
		END
   ) AS client_hotel,
   TO_CHAR (trunc_actual_check_in_date,'DD/MM/YYYY') AS hotel_date_entree,
   TO_CHAR (trunc_actual_check_out_date,'DD/MM/YYYY') AS hotel_date_sortie,
   LOWER (phone_number) AS client_email,
   --RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
   TRUNC (SYSDATE - TO_DATE (trunc_actual_check_out_date)) AS nb_jours
FROM opera.reservation_name 
 
JOIN OPERA.NAME ON RESERVATION_NAME.NAME_ID = NAME.NAME_ID 
JOIN OPERA.NAME_PHONE ON RESERVATION_NAME.NAME_ID = NAME_PHONE.NAME_ID 
--JOIN OPERA.RESERVATION_SUMMARY ON RESERVATION_NAME.RESV_NAME_ID = RESERVATION_SUMMARY.EVENT_ID 
--JOIN OPERA.RESORT_ORIGINS_OF_BOOKING ON (RESERVATION_SUMMARY.RESORT = RESORT_ORIGINS_OF_BOOKING.RESORT AND RESERVATION_SUMMARY.SOURCE_CODE = RESORT_ORIGINS_OF_BOOKING.SOURCE_CODE) 
 
WHERE resv_status = 'CHECKED OUT'
AND TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND REGEXP_LIKE (LOWER(phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND TRUNC_ACTUAL_CHECK_OUT_DATE >= to_date('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;

j'en obtiens plus de 4000. Le problème est que j'ai besoin des infos mis en commentaires, j'ai besoin d'une aide pour utiliser la données en commentaires donc d'utiliser les deux jointures, sans que cela réduise mon nombre de réponse (comme avec un left join ou right join) mais j'ai du mal à les utiliser.

D'avance merci pour votre aide.

[attila771]

BOnjour,

INNER JOIN est utilisé pour faire une jointure stricte, c'est à dire que si tu n'arrives pas à passer de la table A vers la Table B alors aucun ligne n'est ramenée

LEFT OUTER join permet d'avoir les infos de la table A même si tu n'as pas de lien avec la table B.

Code :

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
 
SELECT DISTINCT (NAME.title || ' ' || reservation_name.sguest_firstname || ' '|| reservation_name.sguest_name) AS client_nom,
	DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE') AS client_pays,
	   (CASE
		   WHEN opera.reservation_name.resort = 'OPERA'
			  THEN 'Best Western Premier Opera Diamond'
		   WHEN opera.reservation_name.resort = 'BATIGN'
			  THEN 'Opera Batignolles Hotel'
		   WHEN opera.reservation_name.resort = 'PPN'
			  THEN 'Comfort et Quality Hotel Centre del Mon'
		END
   ) AS client_hotel,
   TO_CHAR (trunc_actual_check_in_date,'DD/MM/YYYY') AS hotel_date_entree,
   TO_CHAR (trunc_actual_check_out_date,'DD/MM/YYYY') AS hotel_date_sortie,
   LOWER (phone_number) AS client_email,
   RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
   TRUNC (SYSDATE - TO_DATE (trunc_actual_check_out_date)) AS nb_jours
FROM opera.reservation_name 
 
INNER JOIN OPERA.NAME ON RESERVATION_NAME.NAME_ID = NAME.NAME_ID 
INNER JOIN OPERA.NAME_PHONE ON RESERVATION_NAME.NAME_ID = NAME_PHONE.NAME_ID 
LEFT OUTER JOIN OPERA.RESERVATION_SUMMARY ON RESERVATION_NAME.RESV_NAME_ID = RESERVATION_SUMMARY.EVENT_ID 
LEFT OUTER JOIN OPERA.RESORT_ORIGINS_OF_BOOKING ON (RESERVATION_SUMMARY.RESORT = RESORT_ORIGINS_OF_BOOKING.RESORT AND RESERVATION_SUMMARY.SOURCE_CODE = RESORT_ORIGINS_OF_BOOKING.SOURCE_CODE) 
WHERE resv_status = 'CHECKED OUT'
AND TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND REGEXP_LIKE (LOWER(phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND TRUNC_ACTUAL_CHECK_OUT_DATE >= to_date('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;

Pour ton cas tu vas avoir la valeur NULL dans la colonne RESORT_ORIGINS_OF_BOOKING.DESCRIPTION dans le cas où la jointure ne trouve aucune ligne correspondante

[bpdelavega]

Impeccable attila771, c'est exactement ce que je me disais, mais j'avais zappé cette histoire de outer, je m'en sers jamais je fais plutôt des left ou des right join, merci beaucoup

[bpdelavega]

Fausse joie, j'ai fait comme tu m'as dit ça produit le même résultat, environ 400 lignes, quand je le remets en commentaire, plus de 4000

[attila771]

Je cherche mais je ne vois pas pourquoi cela te ramène moins de résultat...

Sinon pas mal la colonne phone_number où il y a de stocké une adresse mail

[bpdelavega]

c'est vrai c'est pas terrible , mais je pense que les concepteurs de l'appli, n'avait pas prévu de récupérer l'email au départ, et ils ont utilisé le champs car ils ne voulaient pas trop modifier leur applis.

Sinon pour le nombre d'enregistrement, moi aussi je ne vois pas, le plus surprenant, il ne me ramène pas de valeur nulle, comme si le outer n'était pas pris en compte, d'ailleurs dans mes souvenirs sur Oracle on met += ou =+ je ne me souviens plus, ça fonctionne peut être mieux, je vais faire un test

[bpdelavega]

ça fait toujours pareil, mais c'est clair que le problème vient de la jointure, dès que je le mets en commentaire j'ai mes 4000 lignes

[al1_24]

Et la requête que tu nous montres est bien complète ?
Il n'y aucune restriction (dans la clause WHERE) qui s'appuie sur les colonnes des tables ajoutées en jointure externe ?

[bpdelavega]

je remet l'intégralité de la requête qui ne me donne que 421 lignes
J'ai remplacé le outer left par le =+

Code :

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
 
SELECT DISTINCT (NAME.title || ' ' || reservation_name.sguest_firstname || ' '|| reservation_name.sguest_name) AS client_nom,
    DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE') AS client_pays,
       (CASE
           WHEN opera.reservation_name.resort = 'OPERA'
              THEN 'Best Western Premier Opera Diamond'
           WHEN opera.reservation_name.resort = 'BATIGN'
              THEN 'Opera Batignolles Hotel'
           WHEN opera.reservation_name.resort = 'PPN'
              THEN 'Comfort et Quality Hotel Centre del Mon'
        END
   ) AS client_hotel,
   TO_CHAR (trunc_actual_check_in_date,'DD/MM/YYYY') AS hotel_date_entree,
   TO_CHAR (trunc_actual_check_out_date,'DD/MM/YYYY') AS hotel_date_sortie,
   LOWER (phone_number) AS client_email,
   --RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
   TRUNC (SYSDATE - TO_DATE (trunc_actual_check_out_date)) AS nb_jours
FROM opera.reservation_name 
 
INNER JOIN OPERA.NAME ON RESERVATION_NAME.NAME_ID = NAME.NAME_ID 
INNER JOIN OPERA.NAME_PHONE ON RESERVATION_NAME.NAME_ID = NAME_PHONE.NAME_ID 
JOIN OPERA.RESERVATION_SUMMARY ON RESERVATION_NAME.RESV_NAME_ID =+ RESERVATION_SUMMARY.EVENT_ID 
JOIN OPERA.RESORT_ORIGINS_OF_BOOKING ON (RESERVATION_SUMMARY.RESORT =+ RESORT_ORIGINS_OF_BOOKING.RESORT AND RESERVATION_SUMMARY.SOURCE_CODE =+ RESORT_ORIGINS_OF_BOOKING.SOURCE_CODE) 
WHERE resv_status = 'CHECKED OUT'
AND TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND REGEXP_LIKE (LOWER(phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND TRUNC_ACTUAL_CHECK_OUT_DATE >= to_date('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;

[ORA-007]

Le mieux serait d'aliaser les tables puis de réfléchir à ce que l'on veut obtenir.

La requete telle que écrite plus bas possède 2 jointures ouvertes :

la premiere de RN vers RS
La deuxieme de RS vers RO

Pour rappel le sens LEFT ou RIGHT est déterminé par la premiere des conditions décrite dans le ON.

Apres il faudrait suffixer les champs de ta clause where avec les alias de tes tables pour determiner ceux qui portent sur des champs des tables RS et RO.

Si oui, alors Oracle va forcément oter les valeurs nulles et donc le résultat sera le même qu'une jointure stricte.

Code :

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
SELECT DISTINCT 
   (NA.title || ' ' || RN.sguest_firstname || ' '|| RN.sguest_name) AS client_nom,
   DECODE (??.LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE') 						  AS client_pays,
   CASE
        WHEN RN.resort = 'OPERA'
             THEN 'Best Western Premier Opera Diamond'
        WHEN RN.resort = 'BATIGN'
             THEN 'Opera Batignolles Hotel'
        WHEN RN.resort = 'PPN'
             THEN 'Comfort et Quality Hotel Centre del Mon'
   END 												   client_hotel,
   TO_CHAR (??.trunc_actual_check_in_date,'DD/MM/YYYY') 						   AS hotel_date_entree,
   TO_CHAR (??.trunc_actual_check_out_date,'DD/MM/YYYY') 						   AS hotel_date_sortie,
   LOWER (??.phone_number) 									   AS client_email,
   --RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
   TRUNC (SYSDATE - TO_DATE (??.trunc_actual_check_out_date)) 					   AS nb_jours
FROM 	       	    opera.reservation_name 	    RN
    INNER JOIN 	    OPERA.NAME 	      		    NA ON  RN.NAME_ID = NA.NAME_ID 
    INNER JOIN 	    OPERA.NAME_PHONE       	    PH ON  RN.NAME_ID = PH.NAME_ID 
    LEFT OUTER JOIN OPERA.RESERVATION_SUMMARY 	    RS ON  RN.RESV_NAME_ID = RS.EVENT_ID 
    LEFT OUTER JOIN OPERA.RESORT_ORIGINS_OF_BOOKING RO ON  RS.RESORT 	  = RO.RESORT 
    						       AND RS.SOURCE_CODE = RO.SOURCE_CODE 
WHERE 1=1
AND   ??.resv_status = 'CHECKED OUT'
AND   TRUNC( SYSDATE - TO_DATE(??.TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND   REGEXP_LIKE (LOWER(??.phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND   ??.TRUNC_ACTUAL_CHECK_OUT_DATE >= to_date('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;

[bpdelavega]

Merci ORA007,
Oui je comprends, ton explication est très fine, au niveau de la syntaxe, mais lorsque j'utilise ta requête, j'ai des messages d'erreurs, voici ce que j'ai tenté de faire en essayant de respecter ce que tu m'as dit :

Code :

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
 
SELECT DISTINCT 
   (NAME.title || ' ' || reservation_name.sguest_firstname || ' '|| reservation_name.sguest_name) AS client_nom,
   DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE')                           AS client_pays,
   CASE
        WHEN opera.reservation_name.resort = 'OPERA'
             THEN 'Best Western Premier Opera Diamond'
        WHEN opera.reservation_name.resort = 'BATIGN'
             THEN 'Opera Batignolles Hotel'
        WHEN opera.reservation_name.resort = 'PPN'
             THEN 'Comfort et Quality Hotel Centre del Mon'
   END                                                    client_hotel,
   TO_CHAR (trunc_actual_check_in_date,'DD/MM/YYYY')                            AS hotel_date_entree,
   TO_CHAR (trunc_actual_check_out_date,'DD/MM/YYYY')                            AS hotel_date_sortie,
   LOWER (phone_number)                                        AS client_email,
   --RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
   TRUNC (SYSDATE - TO_DATE (trunc_actual_check_out_date))                        AS nb_jours
FROM                    opera.reservation_name         RN
    INNER JOIN         OPERA.NAME                       NA ON  RN.NAME_ID = NA.NAME_ID 
    INNER JOIN         OPERA.NAME_PHONE               PH ON  RN.NAME_ID = PH.NAME_ID 
    LEFT OUTER JOIN OPERA.RESERVATION_SUMMARY         RS ON  RN.RESV_NAME_ID = RS.EVENT_ID 
    LEFT OUTER JOIN OPERA.RESORT_ORIGINS_OF_BOOKING RO ON  RS.RESORT       = RO.RESORT 
                                   AND RS.SOURCE_CODE = RO.SOURCE_CODE 
WHERE 1=1
AND   NA.resv_status = 'CHECKED OUT'
AND   TRUNC( SYSDATE - TO_DATE(NA.TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND   REGEXP_LIKE (LOWER(NA.phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND   NA.TRUNC_ACTUAL_CHECK_OUT_DATE >= to_date('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;

[ORA-007]

Bonjour,

Il faut aussi substituer les tables par leur alias dans la clause SELECT.

Après pour déterminer la clause WHERE la plus restrictive, met les toutes en commentaires, compte le nombre de lignes, puis rajoutes en une, compte , etc ..

[attila771]

Bonjour,

Si tu ajoutes des alias, il faut mettre cette alias partout il me semble. Donc mettre RN.sguest_firstname à la place de reservation_name.sguest_firstname ou encore RN.resort à la place de opera.reservation_name.resort

ça donne un truc comme ça normalement

Code :

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
SELECT DISTINCT 
   (NA.TITLE || ' ' || RN.SGUEST_FIRSTNAME || ' '|| RN.SGUEST_NAME) AS CLIENT_NOM,
   DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE')                           AS client_pays, -- ALIAS ICI VER QUELLE TABLE?
   CASE
        WHEN RN.resort = 'OPERA'
             THEN 'Best Western Premier Opera Diamond'
        WHEN RN.resort = 'BATIGN'
             THEN 'Opera Batignolles Hotel'
        WHEN RN.resort = 'PPN'
             THEN 'Comfort et Quality Hotel Centre del Mon'
   end                                                    CLIENT_HOTEL,
   TO_CHAR (NA.TRUNC_ACTUAL_CHECK_IN_DATE,'DD/MM/YYYY')                            AS HOTEL_DATE_ENTREE,
   TO_CHAR (NA.TRUNC_ACTUAL_CHECK_OUT_DATE,'DD/MM/YYYY')                            AS HOTEL_DATE_SORTIE,
   LOWER (NA.phone_number)                                        AS client_email,
   RO.DESCRIPTION AS ORIGINE_RESERVATION,
   TRUNC (SYSDATE - TO_DATE (NA.trunc_actual_check_out_date))                        AS nb_jours
FROM OPERA.RESERVATION_NAME         RN
    INNER JOIN         OPERA.name                       NA ON   NA.NAME_ID =RN.NAME_ID
    INNER JOIN         OPERA.NAME_PHONE               PH ON  PH.NAME_ID=RN.NAME_ID  
    LEFT OUTER JOIN OPERA.RESERVATION_SUMMARY         RS ON  RS.EVENT_ID=RN.RESV_NAME_ID   
    LEFT OUTER JOIN OPERA.RESORT_ORIGINS_OF_BOOKING RO ON  RO.RESORT=RS.RESORT   AND RO.SOURCE_CODE =RS.SOURCE_CODE  
WHERE 1=1
AND   NA.resv_status = 'CHECKED OUT'
AND   TRUNC( SYSDATE - TO_DATE(NA.TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND   REGEXP_LIKE (LOWER(NA.phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND   NA.TRUNC_ACTUAL_CHECK_OUT_DATE >= TO_DATE('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;

[bpdelavega]

J'ai une erreur, voici la requête :

Code :

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
 
SELECT DISTINCT 
   (NA.TITLE || ' ' || RN.SGUEST_FIRSTNAME || ' '|| RN.SGUEST_NAME) AS CLIENT_NOM,
   DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE')                           AS client_pays, -- ALIAS ICI VER QUELLE TABLE?
   CASE
        WHEN RN.resort = 'OPERA'
             THEN 'Best Western Premier Opera Diamond'
        WHEN RN.resort = 'BATIGN'
             THEN 'Opera Batignolles Hotel'
        WHEN RN.resort = 'PPN'
             THEN 'Comfort et Quality Hotel Centre del Mon'
   end                                                    CLIENT_HOTEL,
   TO_CHAR (NA.TRUNC_ACTUAL_CHECK_IN_DATE,'DD/MM/YYYY')                            AS HOTEL_DATE_ENTREE,
   TO_CHAR (NA.TRUNC_ACTUAL_CHECK_OUT_DATE,'DD/MM/YYYY')                            AS HOTEL_DATE_SORTIE,
   LOWER (NA.phone_number)                                        AS client_email,
   RO.DESCRIPTION AS ORIGINE_RESERVATION,
   TRUNC (SYSDATE - TO_DATE (NA.trunc_actual_check_out_date))                        AS nb_jours
FROM OPERA.RESERVATION_NAME         RN
    INNER JOIN         OPERA.name                       NA ON   NA.NAME_ID =RN.NAME_ID
    INNER JOIN         OPERA.NAME_PHONE               PH ON  PH.NAME_ID=RN.NAME_ID  
    LEFT OUTER JOIN OPERA.RESERVATION_SUMMARY         RS ON  RS.EVENT_ID=RN.RESV_NAME_ID   
    LEFT OUTER JOIN OPERA.RESORT_ORIGINS_OF_BOOKING RO ON  RO.RESORT=RS.RESORT   AND RO.SOURCE_CODE =RS.SOURCE_CODE  
WHERE 1=1
AND   NA.resv_status = 'CHECKED OUT'
AND   TRUNC( SYSDATE - TO_DATE(NA.TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND   REGEXP_LIKE (LOWER(NA.phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND   NA.TRUNC_ACTUAL_CHECK_OUT_DATE >= TO_DATE('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC

Et voici l'erreur :

ORA-00904: "NA"."TRUNC_ACTUAL_CHECK_OUT_DATE": invalid identifier

[attila771]

Tu peux me donner la structure de tes tables ( script de création) ça sera plus simple pour faire la requête et si possible quelque données bidon

[ORA-007]

ORA-00904: string: invalid identifier
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

Conclusion ... le champ TRUNC_ACTUAL_CHECK_OUT_DATE n'est pas positionné sur la table NA

[bpdelavega]

Table NAME :

Code :

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
 
CREATE TABLE OPERA.NAME
(
  NAME_ID                      NUMBER           NOT NULL,
  NAME_CODE                    VARCHAR2(20 BYTE),
  INSERT_USER                  NUMBER           NOT NULL,
  INSERT_DATE                  DATE             NOT NULL,
  UPDATE_USER                  NUMBER           NOT NULL,
  UPDATE_DATE                  DATE             NOT NULL,
  PRIMARY_NAME_ID              NUMBER,
  REPEAT_GUEST_ID              VARCHAR2(20 BYTE),
  MAIL_LIST                    VARCHAR2(1 BYTE),
  MAIL_TYPE                    VARCHAR2(2000 BYTE),
  FOLLOW_ON                    VARCHAR2(20 BYTE),
  BUSINESS_TITLE               VARCHAR2(80 BYTE),
  INACTIVE_DATE                DATE,
  ARC_UPDATE_DATE              DATE,
  UPDATE_FAX_DATE              DATE,
  BIRTH_DATE                   DATE,
  COLLECTION_USER_ID           NUMBER,
  COMPANY                      VARCHAR2(80 BYTE),
  SOUND_EX_COMPANY             VARCHAR2(10 BYTE),
  LEGAL_COMPANY                VARCHAR2(80 BYTE),
  FIRST                        VARCHAR2(40 BYTE),
  MIDDLE                       VARCHAR2(40 BYTE),
  LAST                         VARCHAR2(40 BYTE),
  NICKNAME                     VARCHAR2(40 BYTE),
  TITLE                        VARCHAR2(40 BYTE),
  SOUND_EX_LAST                VARCHAR2(10 BYTE),
  EXTERNAL_REFERENCE_REQU      VARCHAR2(20 BYTE),
  VIP_STATUS                   VARCHAR2(20 BYTE),
  VIP_AUTHORIZATION            VARCHAR2(20 BYTE),
  BILLING_PROFILE_CODE         VARCHAR2(20 BYTE),
  RATE_STRUCTURE               VARCHAR2(20 BYTE),
  NAME_COMMENT                 VARCHAR2(2000 BYTE),
  TOUR_OPERATOR_TYPE           VARCHAR2(20 BYTE),
  REGION                       VARCHAR2(20 BYTE),
  TYPE_OF_1099                 VARCHAR2(20 BYTE),
  TAX1_NO                      VARCHAR2(20 BYTE),
  COMPANY_NAME_ID              NUMBER,
  EXTERNAL_REFERENCE_REQUIRED  VARCHAR2(20 BYTE),
  VENDOR_ID                    NUMBER,
  VENDOR_SITE_ID               NUMBER,
  ARC_OFFICE_TYPE              VARCHAR2(20 BYTE),
  TAX2_NO                      VARCHAR2(20 BYTE),
  ARC_MAIL_FLAG                VARCHAR2(20 BYTE),
  NAME2                        VARCHAR2(80 BYTE),
  NAME3                        VARCHAR2(80 BYTE),
  SALESREP                     VARCHAR2(10 BYTE),
  TRACECODE                    VARCHAR2(20 BYTE),
  GEOGRAPHIC_REGION            VARCHAR2(20 BYTE),
  GUEST_CLASSIFICATION         VARCHAR2(20 BYTE),
  PRIMARY_ADDRESS_ID           NUMBER,
  PRIMARY_PHONE_ID             NUMBER,
  TAX_EXEMPT_STATUS            VARCHAR2(20 BYTE),
  GDS_NAME                     VARCHAR2(2000 BYTE),
  GDS_TRANSACTION_NO           VARCHAR2(50 BYTE),
  NATIONALITY                  VARCHAR2(20 BYTE),
  LANGUAGE                     VARCHAR2(20 BYTE),
  SALUTATION                   VARCHAR2(60 BYTE),
  PASSPORT                     VARCHAR2(40 BYTE),
  HISTORY_YN                   VARCHAR2(1 BYTE),
  RESV_CONTACT                 VARCHAR2(40 BYTE),
  CONTRACT_NO                  VARCHAR2(40 BYTE),
  CONTRACT_RECV_DATE           DATE,
  ACCT_CONTACT                 VARCHAR2(40 BYTE),
  PRIORITY                     VARCHAR2(20 BYTE),
  INDUSTRY_CODE                VARCHAR2(2000 BYTE),
  ROOMS_POTENTIAL              VARCHAR2(2000 BYTE),
  COMPETITION_CODE             VARCHAR2(2000 BYTE),
  SCOPE                        VARCHAR2(2000 BYTE),
  SCOPE_CITY                   VARCHAR2(2000 BYTE),
  TERRITORY                    VARCHAR2(20 BYTE),
  ACTIONCODE                   VARCHAR2(2000 BYTE),
  ACTIVE_YN                    VARCHAR2(1 BYTE),
  MASTER_ACCOUNT_YN            VARCHAR2(1 BYTE),
  NAME_TYPE                    VARCHAR2(20 BYTE) NOT NULL,
  SNAME                        VARCHAR2(80 BYTE) NOT NULL,
  NAME_TAX_TYPE                VARCHAR2(20 BYTE),
  SFIRST                       VARCHAR2(40 BYTE),
  AR_NO                        VARCHAR2(20 BYTE),
  AVAILABILITY_OVERRIDE        VARCHAR2(1 BYTE),
  BILLING_CODE                 VARCHAR2(20 BYTE),
  CASH_BL_IND                  VARCHAR2(1 BYTE),
  BL_MSG                       VARCHAR2(2000 BYTE),
  CURRENCY_CODE                VARCHAR2(20 BYTE),
  COMMISSION_CODE              VARCHAR2(20 BYTE),
  HOLD_CODE                    VARCHAR2(20 BYTE),
  INTEREST                     VARCHAR2(2000 BYTE),
  SUMM_REF_CC                  VARCHAR2(20 BYTE),
  IATA_COMP_TYPE               VARCHAR2(20 BYTE),
  SREP_CODE                    VARCHAR2(10 BYTE),
  ACCOUNTSOURCE                VARCHAR2(2000 BYTE),
  MARKETS                      VARCHAR2(2000 BYTE),
  PRODUCT_INTEREST             VARCHAR2(2000 BYTE),
  KEYWORD                      VARCHAR2(40 BYTE),
  LETTER_GREETING              VARCHAR2(100 BYTE),
  INFLUENCE                    VARCHAR2(2000 BYTE),
  DEPT_ID                      VARCHAR2(10 BYTE),
  DEPARTMENT                   VARCHAR2(200 BYTE),
  CONTACT_YN                   VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL,
  ACCOUNT_TYPE                 VARCHAR2(20 BYTE),
  DOWNLOAD_RESORT              VARCHAR2(20 BYTE),
  DOWNLOAD_SREP                NUMBER,
  DOWNLOAD_DATE                DATE,
  UPLOAD_DATE                  DATE,
  LAPTOP_CHANGE                NUMBER(2),
  CRS_NAMEID                   NUMBER,
  COMM_PAY_CENTRAL             VARCHAR2(1 BYTE),
  CC_PROFILE_YN                VARCHAR2(1 BYTE),
  GENDER                       VARCHAR2(1 BYTE),
  BIRTH_PLACE                  VARCHAR2(80 BYTE),
  BIRTH_COUNTRY                VARCHAR2(20 BYTE),
  PROFESSION                   VARCHAR2(80 BYTE),
  ID_TYPE                      VARCHAR2(40 BYTE),
  ID_NUMBER                    VARCHAR2(80 BYTE),
  ID_DATE                      DATE,
  ID_PLACE                     VARCHAR2(80 BYTE),
  ID_COUNTRY                   VARCHAR2(20 BYTE),
  UDFC01                       VARCHAR2(200 BYTE),
  UDFC02                       VARCHAR2(200 BYTE),
  UDFC03                       VARCHAR2(200 BYTE),
  UDFC04                       VARCHAR2(200 BYTE),
  UDFC05                       VARCHAR2(200 BYTE),
  UDFC06                       VARCHAR2(200 BYTE),
  UDFC07                       VARCHAR2(200 BYTE),
  UDFC08                       VARCHAR2(200 BYTE),
  UDFC09                       VARCHAR2(200 BYTE),
  UDFC10                       VARCHAR2(200 BYTE),
  UDFC11                       VARCHAR2(200 BYTE),
  UDFC12                       VARCHAR2(200 BYTE),
  UDFC13                       VARCHAR2(200 BYTE),
  UDFC14                       VARCHAR2(200 BYTE),
  UDFC15                       VARCHAR2(200 BYTE),
  UDFC16                       VARCHAR2(200 BYTE),
  UDFC17                       VARCHAR2(200 BYTE),
  UDFC18                       VARCHAR2(200 BYTE),
  UDFC19                       VARCHAR2(200 BYTE),
  UDFC20                       VARCHAR2(200 BYTE),
  UDFC21                       VARCHAR2(200 BYTE),
  UDFC22                       VARCHAR2(200 BYTE),
  UDFC23                       VARCHAR2(200 BYTE),
  UDFC24                       VARCHAR2(200 BYTE),
  UDFC25                       VARCHAR2(200 BYTE),
  UDFC26                       VARCHAR2(200 BYTE),
  UDFC27                       VARCHAR2(200 BYTE),
  UDFC28                       VARCHAR2(200 BYTE),
  UDFC29                       VARCHAR2(200 BYTE),
  UDFC30                       VARCHAR2(200 BYTE),
  UDFC31                       VARCHAR2(200 BYTE),
  UDFC32                       VARCHAR2(200 BYTE),
  UDFC33                       VARCHAR2(200 BYTE),
  UDFC34                       VARCHAR2(200 BYTE),
  UDFC35                       VARCHAR2(200 BYTE),
  UDFC36                       VARCHAR2(200 BYTE),
  UDFC37                       VARCHAR2(200 BYTE),
  UDFC38                       VARCHAR2(200 BYTE),
  UDFC39                       VARCHAR2(200 BYTE),
  UDFC40                       VARCHAR2(200 BYTE),
  UDFN01                       NUMBER,
  UDFN02                       NUMBER,
  UDFN03                       NUMBER,
  UDFN04                       NUMBER,
  UDFN05                       NUMBER,
  UDFN06                       NUMBER,
  UDFN07                       NUMBER,
  UDFN08                       NUMBER,
  UDFN09                       NUMBER,
  UDFN10                       NUMBER,
  UDFN11                       NUMBER,
  UDFN12                       NUMBER,
  UDFN13                       NUMBER,
  UDFN14                       NUMBER,
  UDFN15                       NUMBER,
  UDFN16                       NUMBER,
  UDFN17                       NUMBER,
  UDFN18                       NUMBER,
  UDFN19                       NUMBER,
  UDFN20                       NUMBER,
  UDFN21                       NUMBER,
  UDFN22                       NUMBER,
  UDFN23                       NUMBER,
  UDFN24                       NUMBER,
  UDFN25                       NUMBER,
  UDFN26                       NUMBER,
  UDFN27                       NUMBER,
  UDFN28                       NUMBER,
  UDFN29                       NUMBER,
  UDFN30                       NUMBER,
  UDFN31                       NUMBER,
  UDFN32                       NUMBER,
  UDFN33                       NUMBER,
  UDFN34                       NUMBER,
  UDFN35                       NUMBER,
  UDFN36                       NUMBER,
  UDFN37                       NUMBER,
  UDFN38                       NUMBER,
  UDFN39                       NUMBER,
  UDFN40                       NUMBER,
  UDFD01                       DATE,
  UDFD02                       DATE,
  UDFD03                       DATE,
  UDFD04                       DATE,
  UDFD05                       DATE,
  UDFD06                       DATE,
  UDFD07                       DATE,
  UDFD08                       DATE,
  UDFD09                       DATE,
  UDFD10                       DATE,
  UDFD11                       DATE,
  UDFD12                       DATE,
  UDFD13                       DATE,
  UDFD14                       DATE,
  UDFD15                       DATE,
  UDFD16                       DATE,
  UDFD17                       DATE,
  UDFD18                       DATE,
  UDFD19                       DATE,
  UDFD20                       DATE,
  PAYMENT_DUE_DAYS             NUMBER,
  SUFFIX                       VARCHAR2(40 BYTE),
  EXTERNAL_ID                  VARCHAR2(20 BYTE),
  GUEST_PRIV_YN                VARCHAR2(1 BYTE),
  EMAIL_YN                     VARCHAR2(1 BYTE),
  MAIL_YN                      VARCHAR2(1 BYTE),
  INDEX_NAME                   VARCHAR2(80 BYTE),
  XLAST_NAME                   VARCHAR2(40 BYTE),
  XFIRST_NAME                  VARCHAR2(40 BYTE),
  XCOMPANY_NAME                VARCHAR2(80 BYTE),
  XTITLE                       VARCHAR2(40 BYTE),
  XSALUTATION                  VARCHAR2(60 BYTE),
  SXNAME                       VARCHAR2(80 BYTE),
  SXFIRST_NAME                 VARCHAR2(80 BYTE),
  LAST_UPDATED_RESORT          VARCHAR2(20 BYTE),
  ENVELOPE_GREETING            VARCHAR2(100 BYTE),
  XENVELOPE_GREETING           VARCHAR2(200 BYTE),
  DIRECT_BILL_BATCH_TYPE       VARCHAR2(40 BYTE),
  RESORT_REGISTERED            VARCHAR2(20 BYTE),
  TAX_OFFICE                   VARCHAR2(30 BYTE),
  TAX_TYPE                     VARCHAR2(20 BYTE),
  TAX_CATEGORY                 VARCHAR2(20 BYTE),
  PREFERRED_ROOM_NO            VARCHAR2(20 BYTE),
  PHONE_YN                     VARCHAR2(1 BYTE),
  SMS_YN                       VARCHAR2(1 BYTE),
  PROTECTED                    VARCHAR2(20 BYTE),
  XLANGUAGE                    VARCHAR2(20 BYTE),
  MARKET_RESEARCH_YN           VARCHAR2(1 BYTE),
  THIRD_PARTY_YN               VARCHAR2(1 BYTE),
  AUTOENROLL_MEMBER_YN         VARCHAR2(1 BYTE),
  CHAIN_CODE                   VARCHAR2(20 BYTE),
  CREDIT_RATING                VARCHAR2(20 BYTE),
  TITLE_SUFFIX                 NUMBER,
  COMPANY_GROUP_ID             VARCHAR2(40 BYTE),
  INACTIVE_REASON              VARCHAR2(2000 BYTE),
  IATA_CONSORTIA               VARCHAR2(40 BYTE),
  INCLUDE_IN_1099_YN           VARCHAR2(1 BYTE),
  PSUEDO_PROFILE_YN            VARCHAR2(1 BYTE),
  PROFILE_PRIVACY_FLG          VARCHAR2(1 BYTE),
  REPLACE_ADDRESS              VARCHAR2(1 BYTE)
)
TABLESPACE NAMEDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          2M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
COMMENT ON COLUMN OPERA.NAME.TAX_CATEGORY IS 'Tax Category';
 
COMMENT ON COLUMN OPERA.NAME.CREDIT_RATING IS 'Credit rating.';
 
COMMENT ON COLUMN OPERA.NAME.TITLE_SUFFIX IS 'Stores the suffix value of the selected title code.  This will be used for Processing to External System.';
 
COMMENT ON COLUMN OPERA.NAME.COMPANY_GROUP_ID IS 'The company group or company group user ID in hierarchical format';
 
COMMENT ON COLUMN OPERA.NAME.INACTIVE_REASON IS 'Reason, why record was inactivated.';
 
COMMENT ON COLUMN OPERA.NAME.IATA_CONSORTIA IS 'Consortia for the IATA number.';
 
COMMENT ON COLUMN OPERA.NAME.INCLUDE_IN_1099_YN IS 'Include travel agents/sources profile in 1099 reporting ?Y/N';
 
COMMENT ON COLUMN OPERA.NAME.PROFILE_PRIVACY_FLG IS 'Profile privacy flag determine if the profile is marked as private for a property.';
 
COMMENT ON COLUMN OPERA.NAME.REPLACE_ADDRESS IS 'User option to replace address in ORS with one from PMS.';
 
COMMENT ON COLUMN OPERA.NAME.BIRTH_COUNTRY IS 'Country of Birth';
 
COMMENT ON COLUMN OPERA.NAME.PROFESSION IS 'The profession of the Individual';
 
COMMENT ON COLUMN OPERA.NAME.ID_TYPE IS 'Identification Type. Eg Passport, Driving License etc';
 
COMMENT ON COLUMN OPERA.NAME.ID_NUMBER IS 'Identification Number';
 
COMMENT ON COLUMN OPERA.NAME.ID_DATE IS 'Issued date of Identification';
 
COMMENT ON COLUMN OPERA.NAME.ID_PLACE IS 'The place where ID was issued';
 
COMMENT ON COLUMN OPERA.NAME.ID_COUNTRY IS 'The country where ID was issued';

Table NAME_PHONE

Code :

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
 
CREATE TABLE OPERA.NAME_PHONE
(
  PHONE_ID                 NUMBER               NOT NULL,
  NAME_ID                  NUMBER               NOT NULL,
  PHONE_TYPE               VARCHAR2(20 BYTE),
  PHONE_ROLE               VARCHAR2(20 BYTE)    DEFAULT 'BUSINESS'            NOT NULL,
  PHONE_NUMBER             VARCHAR2(2000 BYTE)  NOT NULL,
  INSERT_DATE              DATE                 NOT NULL,
  INSERT_USER              NUMBER               NOT NULL,
  UPDATE_DATE              DATE                 NOT NULL,
  UPDATE_USER              NUMBER               NOT NULL,
  INACTIVE_DATE            DATE,
  END_DATE                 DATE,
  BEGIN_DATE               DATE,
  ADDRESS_ID               NUMBER,
  PRIMARY_YN               VARCHAR2(1 BYTE),
  DISPLAY_SEQ              NUMBER,
  LAPTOP_CHANGE            NUMBER(2),
  INDEX_PHONE              VARCHAR2(100 BYTE),
  EXTENSION                VARCHAR2(20 BYTE),
  EMAIL_FORMAT             VARCHAR2(20 BYTE),
  SHARE_EMAIL_YN           VARCHAR2(1 BYTE)     DEFAULT 'N',
  DEFAULT_CONFIRMATION_YN  VARCHAR2(1 BYTE),
  EMAIL_LANGUAGE           VARCHAR2(20 BYTE)
)
TABLESPACE NAMEDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
COMMENT ON COLUMN OPERA.NAME_PHONE.PHONE_ID IS 'The primary key for this table.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.NAME_ID IS 'The reference to the name that owns this phone.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.PHONE_TYPE IS 'The type of this phone number.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.PHONE_ROLE IS 'Role in which this phone type belongs to.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.PHONE_NUMBER IS 'The phone number for this record';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.INSERT_DATE IS 'The date the record was created';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.INSERT_USER IS 'The user that created the record';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.UPDATE_DATE IS 'The date the record was modified';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.UPDATE_USER IS 'The user that modified the record';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.INACTIVE_DATE IS 'The date the record was marked as inactive';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.END_DATE IS 'The date this record becomes invalid for use in the system. User enterable.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.BEGIN_DATE IS 'Not used.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.ADDRESS_ID IS 'Not used.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.PRIMARY_YN IS 'Indicates the primary telephone number in the case of multiple phone numbers on a profile.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.DISPLAY_SEQ IS 'Order in which the phone numbers should be displayed.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.LAPTOP_CHANGE IS 'Code to synchronize with Laptop';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.EXTENSION IS 'Telephone Extension.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.EMAIL_FORMAT IS 'Format type for email messages: HTML, PLAIN text.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.SHARE_EMAIL_YN IS 'Used for uploading the email to holidex if the value is Y.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.DEFAULT_CONFIRMATION_YN IS 'Phone_role to be used for default confirmation.';
 
COMMENT ON COLUMN OPERA.NAME_PHONE.EMAIL_LANGUAGE IS 'Optional language for e-mail.';
 
 
CREATE INDEX OPERA.NAME_PHONE_IND1 ON OPERA.NAME_PHONE
(INDEX_PHONE)
LOGGING
TABLESPACE NAMEINDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE UNIQUE INDEX OPERA.NAME_PHONE_PK ON OPERA.NAME_PHONE
(PHONE_ID)
LOGGING
TABLESPACE NAMEINDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE UNIQUE INDEX OPERA.NAME_PHONE_UNIQUE ON OPERA.NAME_PHONE
(NAME_ID, PHONE_ID, PRIMARY_YN)
LOGGING
TABLESPACE NAMEINDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE OR REPLACE TRIGGER OPERA.name_phonenumber_biu
BEFORE INSERT  OR UPDATE
  OF phone_number
ON name_phone
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
  IF NOT SC_LAPTOP_TRG_INTERFACE.isLaptopDataXfer THEN
    IF LENGTH(:NEW.PHONE_NUMBER) > 40 AND :new.phone_role NOT IN ('EMAIL', 'WEBPAGE') then
       :NEW.PHONE_NUMBER := substr(:NEW.PHONE_NUMBER,1,40);
    END IF;
    IF LENGTH(:NEW.PHONE_NUMBER) > 200 AND :new.phone_role IN ('EMAIL', 'WEBPAGE') then
       :NEW.PHONE_NUMBER := substr(:NEW.PHONE_NUMBER,1,200);
    END IF;
  END IF;
End;
/
 
 
CREATE OR REPLACE TRIGGER OPERA.name_phone_biu
BEFORE INSERT  OR UPDATE
ON name_phone
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   IF nvl(:new.phone_number,'~') != nvl(:old.phone_number,'~') then
     :new.index_phone := reverse_string(:new.phone_number,'Y');  -- index phone for match and merge
   end IF;
    IF pms_prform_i.phone_upd_flg = 'N' THEN
      pms_prform_i.phone_name_id := :NEW.name_id;
      pms_prform_i.phone_role := :NEW.phone_role;
      IF :NEW.primary_yn = 'Y' THEN
        pms_prform_i.phone_id   := :NEW.phone_id;
      END IF;
    END IF;
END;
/
 
 
CREATE OR REPLACE TRIGGER OPERA.name_phone_set_primary_phone
AFTER INSERT  OR UPDATE
ON name_phone
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
  IF NOT SC_LAPTOP_TRG_INTERFACE.isLaptopDataXfer THEN
    IF PMS_PRFORM_i.phone_UPD_FLG = 'N' THEN
      pms_prform_i.set_primary_phone(pms_prform_i.phone_name_id,pms_prform_i.phone_id,pms_prform_i.phone_role);
      pms_prform_i.phone_name_id := NULL;
      pms_prform_i.phone_id      := NULL;
      pms_prform_i.phone_role    := NULL;
    END IF;
  END IF;
END;
/
 
 
CREATE OR REPLACE TRIGGER OPERA.name_phone_pms2crs_events
  AFTER DELETE
  ON name_phone
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
BEGIN
  IF NOT SC_LAPTOP_TRG_INTERFACE.isLaptopDataXfer THEN
    IF DELETING THEN
      UPDATE activity_link
      SET    email_id = NULL
      WHERE  link_id  = :old.name_id
      AND    email_id = :old.phone_id;
    END IF;
  END IF;
END;
/
 
 
DROP SYNONYM OXI.NAME_PHONE;
 
CREATE SYNONYM OXI.NAME_PHONE FOR OPERA.NAME_PHONE;
 
 
DROP SYNONYM OXIHUB.NAME_PHONE;
 
CREATE SYNONYM OXIHUB.NAME_PHONE FOR OPERA.NAME_PHONE;
 
 
ALTER TABLE OPERA.NAME_PHONE ADD (
  CONSTRAINT NAME_PHONE_CK1
 CHECK (PRIMARY_YN IN ('Y','N') ),
  CONSTRAINT NAME_PHONE_CK2
 CHECK (PRIMARY_YN IN ('Y','N') ),
  CONSTRAINT NAME_PHONE_CK3
 CHECK (PRIMARY_YN IN ('Y','N') ),
  CONSTRAINT NAME_PHONE_PK
 PRIMARY KEY
 (PHONE_ID)
    USING INDEX 
    TABLESPACE NAMEINDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT NAME_PHONE_UNIQUE
 UNIQUE (NAME_ID, PHONE_ID, PRIMARY_YN)
    USING INDEX 
    TABLESPACE NAMEINDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));
 
ALTER TABLE OPERA.NAME_PHONE ADD (
  CONSTRAINT NAME_ID_FK4 
 FOREIGN KEY (NAME_ID) 
 REFERENCES OPERA.NAME (NAME_ID)
    ON DELETE CASCADE);

Table RESERVATION_SUMMARY

Code :

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
 
CREATE TABLE OPERA.RESERVATION_SUMMARY
(
  ID                     NUMBER                 NOT NULL,
  RESORT                 VARCHAR2(20 BYTE)      NOT NULL,
  EVENT_TYPE             VARCHAR2(1 BYTE)       NOT NULL,
  EVENT_ID               VARCHAR2(20 BYTE)      NOT NULL,
  CONSIDERED_DATE        DATE,
  ROOM_CATEGORY          VARCHAR2(20 BYTE),
  ROOM_CLASS             VARCHAR2(20 BYTE),
  MARKET_CODE            VARCHAR2(20 BYTE),
  SOURCE_CODE            VARCHAR2(20 BYTE),
  RATE_CODE              VARCHAR2(20 BYTE),
  REGION_CODE            VARCHAR2(20 BYTE),
  GROUP_ID               NUMBER,
  RESV_TYPE              VARCHAR2(1 BYTE),
  RESV_INV_TYPE          VARCHAR2(1 BYTE),
  PSUEDO_ROOM_YN         VARCHAR2(1 BYTE),
  ARR_ROOMS              NUMBER,
  ADULTS                 NUMBER,
  CHILDREN               NUMBER,
  DEP_ROOMS              NUMBER,
  NO_ROOMS               NUMBER,
  GROSS_RATE             NUMBER,
  NET_ROOM_REVENUE       NUMBER,
  EXTRA_REVENUE          NUMBER,
  OO_ROOMS               NUMBER,
  OS_ROOMS               NUMBER,
  REMAINING_BLOCK_ROOMS  NUMBER,
  PICKEDUP_BLOCK_ROOMS   NUMBER,
  SINGLE_OCCUPANCY       NUMBER,
  MULTIPLE_OCCUPANCY     NUMBER,
  BLOCK_STATUS           VARCHAR2(20 BYTE),
  ARR_PERSONS            NUMBER,
  DEP_PERSONS            NUMBER,
  WL_ROOMS               NUMBER,
  WL_PERSONS             NUMBER,
  DAY_USE_ROOMS          NUMBER,
  DAY_USE_PERSONS        NUMBER,
  BOOKING_STATUS         VARCHAR2(20 BYTE),
  RESV_STATUS            VARCHAR2(20 BYTE),
  DAY_USE_YN             VARCHAR2(1 BYTE)       DEFAULT 'N',
  CHANNEL                VARCHAR2(40 BYTE),
  COUNTRY                VARCHAR2(20 BYTE),
  NATIONALITY            VARCHAR2(20 BYTE),
  CRIBS                  NUMBER,
  EXTRA_BEDS             NUMBER,
  ADULTS_TAX_FREE        NUMBER,
  CHILDREN_TAX_FREE      NUMBER,
  RATE_CATEGORY          VARCHAR2(20 BYTE),
  RATE_CLASS             VARCHAR2(20 BYTE),
  ROOM_REVENUE           NUMBER(38,12),
  FOOD_REVENUE           NUMBER(38,12),
  OTHER_REVENUE          NUMBER(38,12),
  TOTAL_REVENUE          NUMBER(38,12),
  NON_REVENUE            NUMBER(38,12),
  ALLOTMENT_HEADER_ID    NUMBER,
  ROOM_REVENUE_TAX       NUMBER,
  FOOD_REVENUE_TAX       NUMBER,
  OTHER_REVENUE_TAX      NUMBER,
  TOTAL_REVENUE_TAX      NUMBER,
  NON_REVENUE_TAX        NUMBER,
  CITY                   VARCHAR2(40 BYTE),
  ZIP_CODE               VARCHAR2(15 BYTE),
  DISTRICT               VARCHAR2(10 BYTE),
  STATE                  VARCHAR2(20 BYTE),
  CHILDREN1              NUMBER,
  CHILDREN2              NUMBER,
  CHILDREN3              NUMBER,
  CHILDREN4              NUMBER,
  CHILDREN5              NUMBER,
  OWNER_FF_FLAG          VARCHAR2(1 BYTE),
  OWNER_RENTAL_FLAG      VARCHAR2(1 BYTE),
  FC_GROSS_RATE          NUMBER(38,12),
  FC_NET_ROOM_REVENUE    NUMBER(38,12),
  FC_EXTRA_REVENUE       NUMBER(38,12),
  FC_ROOM_REVENUE        NUMBER(38,12),
  FC_FOOD_REVENUE        NUMBER(38,12),
  FC_OTHER_REVENUE       NUMBER(38,12),
  FC_TOTAL_REVENUE       NUMBER(38,12),
  FC_NON_REVENUE         NUMBER(38,12),
  FC_ROOM_REVENUE_TAX    NUMBER(38,12),
  FC_FOOD_REVENUE_TAX    NUMBER(38,12),
  FC_OTHER_REVENUE_TAX   NUMBER(38,12),
  FC_TOTAL_REVENUE_TAX   NUMBER(38,12),
  FC_NON_REVENUE_TAX     NUMBER(38,12),
  CURRENCY_CODE          VARCHAR2(20 BYTE),
  EXCHANGE_DATE          DATE,
  UPDATE_BUSINESS_DATE   DATE,
  UPDATE_DATE            DATE,
  CENTRAL_CURRENCY_CODE  VARCHAR2(20 BYTE),
  CENTRAL_EXCHANGE_RATE  NUMBER,
  GENDER                 VARCHAR2(1 BYTE),
  VIP_STATUS             VARCHAR2(20 BYTE),
  QUANTITY               NUMBER,
  TRUNC_BEGIN_DATE       DATE,
  TRUNC_END_DATE         DATE,
  BUSINESS_DATE_CREATED  DATE,
  RES_INSERT_SOURCE      VARCHAR2(20 BYTE),
  PARENT_COMPANY_ID      NUMBER,
  AGENT_ID               NUMBER,
  TURNDOWN_STATUS        VARCHAR2(2 BYTE)
)
TABLESPACE LOGDATA
PCTUSED    0
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          8M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.DAY_USE_YN IS 'Day use reservation ''Y'' or ''N''';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CHANNEL IS 'Channel Code';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.COUNTRY IS 'Country Code';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.NATIONALITY IS 'Nationality Code';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CRIBS IS 'Number of cribs';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.EXTRA_BEDS IS 'Number of Extra Beds';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ADULTS_TAX_FREE IS 'Number of tax free adults';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CHILDREN_TAX_FREE IS 'Number of tax free children';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ROOM_REVENUE IS 'Total net room revenue amount for the current records key value.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FOOD_REVENUE IS 'Total net food/beverage revenue amount for the current records key value.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.OTHER_REVENUE IS 'Total of net revenue for the current records key value, that does not fall under room/food/beverage category.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.TOTAL_REVENUE IS 'Total net revenue amount for the current records key value.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.NON_REVENUE IS 'Total non-revenue amount for the current key value.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ROOM_REVENUE_TAX IS 'Tax amount generated by room revenue.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FOOD_REVENUE_TAX IS 'Tax amount generated by food and beverage revenue.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.OTHER_REVENUE_TAX IS 'Tax amount generated by other revenue.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.TOTAL_REVENUE_TAX IS 'Total tax amount.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.NON_REVENUE_TAX IS 'Tax amount generated by non-revenue.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CITY IS 'City of the guest address.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ZIP_CODE IS 'Postal code of the guest address.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.DISTRICT IS 'District the city or postal code belongs to.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.STATE IS 'State the city or postal code belongs to.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CHILDREN1 IS 'Number of persons in rate bucket 1.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CHILDREN2 IS 'Number of persons in rate bucket 2.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CHILDREN3 IS 'Number of persons in rate bucket 3.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CHILDREN4 IS 'Number of persons in rate bucket 4.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CHILDREN5 IS 'Number of persons in rate bucket 5.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.OWNER_FF_FLAG IS 'Indicates [O]wner or [F]riends/family reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.OWNER_RENTAL_FLAG IS 'Indicates [O]wner or [R]ental Room reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_GROSS_RATE IS 'Gross rate converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_NET_ROOM_REVENUE IS 'Net room revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_EXTRA_REVENUE IS 'Extra revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_ROOM_REVENUE IS 'Room Revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_FOOD_REVENUE IS 'Food and Beverage Revenue converted to the currency of the reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_OTHER_REVENUE IS 'Other Revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_TOTAL_REVENUE IS 'Total Revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_NON_REVENUE IS 'Non Revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_ROOM_REVENUE_TAX IS 'Tax amount generated by room revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_FOOD_REVENUE_TAX IS 'Tax amount generated by food and beverage revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_OTHER_REVENUE_TAX IS 'Tax amount generated by other revenue converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_TOTAL_REVENUE_TAX IS 'Total tax amount converted to the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.FC_NON_REVENUE_TAX IS 'Tax amount generated by non-revenue.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CURRENCY_CODE IS 'Foreign currency code if the rate is not quoted in local currency.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.EXCHANGE_DATE IS 'Date that should be used when converting between local currency and the currency of the reservation.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.UPDATE_BUSINESS_DATE IS 'Business Date when this record was last Inserted / updated.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.UPDATE_DATE IS 'System Date and Time when this record was last inserted / Updated.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CENTRAL_CURRENCY_CODE IS 'Central Currency Code used for currency conversion.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CENTRAL_EXCHANGE_RATE IS 'Exchange Rate between Central Currency and Property Currency Code.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.GENDER IS 'Indicates gender of reservation guest.  Either [M]ale,  [F]emale or [U]nknown.  NULL will be treated as [U].';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.VIP_STATUS IS 'VIP status of the guest.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.QUANTITY IS 'Number of rooms for a reservation; component rooms (suites) count as one room.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.TURNDOWN_STATUS IS 'Turndown status of the room per reservation per day.  [C]ompleted, [NR] - Not required, [R]equested.';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ID IS 'Internal';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.RESORT IS 'The property that the record belongs to';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.EVENT_TYPE IS 'Type of event';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.EVENT_ID IS 'Internal';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CONSIDERED_DATE IS 'Date';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ROOM_CATEGORY IS 'Internal number for Room Type';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ROOM_CLASS IS 'Room Class';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.MARKET_CODE IS 'Market code on the reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.SOURCE_CODE IS 'Source code on the reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.RATE_CODE IS 'Rate code on the reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.REGION_CODE IS 'Region code';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.GROUP_ID IS 'Internal group number';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.RESV_TYPE IS 'Reservation type (Block, Individual)';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.RESV_INV_TYPE IS 'Deduct or non-deduct reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.PSUEDO_ROOM_YN IS 'Type of room (psuedo=y)';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ARR_ROOMS IS 'Number of rooms on the reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ADULTS IS 'Number of Adults';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.CHILDREN IS 'Number of children on the reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.DEP_ROOMS IS 'Number of rooms departing';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.NO_ROOMS IS 'Number of Rooms';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.GROSS_RATE IS 'Total rate';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.NET_ROOM_REVENUE IS 'Gross revenue minus tax';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.EXTRA_REVENUE IS 'Amount of extra revenue';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.OO_ROOMS IS 'Number of Out of Order Rooms';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.OS_ROOMS IS 'Number of Out of Service Rooms';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.REMAINING_BLOCK_ROOMS IS 'Available block rooms';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.PICKEDUP_BLOCK_ROOMS IS 'Number of block rooms picked up';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.SINGLE_OCCUPANCY IS 'Number of adults = 1';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.MULTIPLE_OCCUPANCY IS 'Number of adults greater than 1';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.BLOCK_STATUS IS 'Status of the block';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.ARR_PERSONS IS 'Number of Adults and Children on the reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.DEP_PERSONS IS 'Number of persons departing';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.WL_ROOMS IS 'Number of rooms waitlisted';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.WL_PERSONS IS 'Number of adults and children on the waitlist';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.DAY_USE_ROOMS IS 'Number of rooms marked as dayuse';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.DAY_USE_PERSONS IS 'Number of adults and children on the reservation';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.BOOKING_STATUS IS 'Booking status of the block';
 
COMMENT ON COLUMN OPERA.RESERVATION_SUMMARY.RESV_STATUS IS 'Internal status of the reservation';
 
 
CREATE UNIQUE INDEX OPERA.RESERVATION_SUMMARY_EVENT_UK ON OPERA.RESERVATION_SUMMARY
(EVENT_ID, EVENT_TYPE, RESORT, CONSIDERED_DATE, ROOM_CATEGORY)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND1 ON OPERA.RESERVATION_SUMMARY
(EVENT_TYPE, RESORT, EVENT_ID, CONSIDERED_DATE)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          512K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND10 ON OPERA.RESERVATION_SUMMARY
(EVENT_TYPE, CONSIDERED_DATE, RESORT, RESV_INV_TYPE)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND2 ON OPERA.RESERVATION_SUMMARY
(RESORT, CONSIDERED_DATE, ROOM_CLASS)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND3 ON OPERA.RESERVATION_SUMMARY
(CONSIDERED_DATE, EVENT_TYPE, RESORT, ROOM_CLASS)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND4 ON OPERA.RESERVATION_SUMMARY
(CONSIDERED_DATE, EVENT_TYPE, RESV_STATUS, ROOM_CLASS, RESORT)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND6 ON OPERA.RESERVATION_SUMMARY
(EVENT_TYPE, EVENT_ID, ROOM_CATEGORY, CONSIDERED_DATE)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND7 ON OPERA.RESERVATION_SUMMARY
(EVENT_TYPE, RESORT, ALLOTMENT_HEADER_ID, CONSIDERED_DATE)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND8 ON OPERA.RESERVATION_SUMMARY
(RESORT, CONSIDERED_DATE, EVENT_TYPE, RESV_INV_TYPE)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_IND9 ON OPERA.RESERVATION_SUMMARY
(RESORT, EVENT_TYPE, CONSIDERED_DATE, RESV_INV_TYPE)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESERVATION_SUMMARY_MKT ON OPERA.RESERVATION_SUMMARY
(RESORT, CONSIDERED_DATE, MARKET_CODE)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESV_SUMMARY_MARKET_CODE_IDX ON OPERA.RESERVATION_SUMMARY
(MARKET_CODE)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   3
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX OPERA.RESV_SUMMARY_UPDBUSDATE_IDX ON OPERA.RESERVATION_SUMMARY
(UPDATE_BUSINESS_DATE, RESORT)
LOGGING
TABLESPACE LOGINDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE OR REPLACE TRIGGER OPERA.upsolds
BEFORE INSERT  OR DELETE  OR UPDATE
ON reservation_summary
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  nRoomIncrement   NUMBER := 0;
  nRec             NUMBER;
  vResort          VARCHAR2(20);
  dConsidered_date DATE;
  vRoom_category   VARCHAR2(20);
  vStatus          BOOLEAN := FALSE;
  v_Rec   reservation_summary%ROWTYPE;
BEGIN
   gem.trace_on('upsolds Trigger');
   IF (:NEW.event_type = 'R' OR :OLD.event_type = 'R' ) AND pms_p.hurdle_rate IS NOT NULL AND instr(pms_p.hurdle_rate,'OPUS') = 0 THEN
     IF INSERTING THEN
       IF :NEW.resv_inv_type = 'D' AND :NEW.resv_type = 'I' AND :NEW.psuedo_room_yn = 'N' THEN
         nRoomIncrement   := :NEW.no_rooms;
         vResort          := :NEW.resort;
         dConsidered_date := :NEW.considered_date;
         vRoom_category   := :NEW.room_category;
       END IF;
     ELSIF UPDATING THEN
        IF :NEW.psuedo_room_yn = 'N' THEN
          IF NVL(:NEW.no_rooms,0) != NVL(:OLD.no_rooms,0) AND :NEW.resv_inv_type = 'D' THEN
            nRoomIncrement   := (nvl(:NEW.no_rooms,0) - nvl(:OLD.no_rooms,0));
            vStatus          := TRUE;
          END IF;
          IF (:NEW.resv_type <> :OLD.resv_type AND :NEW.resv_inv_type = 'D') THEN
            IF :NEW.resv_type = 'I' AND :OLD.resv_type = 'B' THEN
              nRoomIncrement   := nvl(:NEW.no_rooms,0);
              vStatus          := TRUE;
            ELSIF :NEW.resv_type = 'B' AND :OLD.resv_type = 'I' THEN
              nRoomIncrement   := ( nvl(:OLD.no_rooms,0) * (-1) );
              vStatus          := TRUE;
            END IF;
          END IF;
          IF :NEW.resv_inv_type <> :OLD.resv_inv_type AND :NEW.resv_type = 'I' THEN
            IF :NEW.resv_inv_type IN ('C','D') AND :old.resv_inv_type IN ('T','W') THEN
              nRoomIncrement   := nvl(:NEW.no_rooms,0);
              vStatus          := TRUE;
            END IF;
            IF :NEW.resv_inv_type IN ('T','W') AND :old.resv_inv_type IN ('C','D') THEN
              nRoomIncrement   := ( nvl(:NEW.no_rooms,0) * (-1) );
              vStatus          := TRUE;
            END IF;
          END IF;
        ELSIF  :NEW.psuedo_room_yn = 'Y' AND (:NEW.psuedo_room_yn <> :OLD.psuedo_room_yn)
               AND :NEW.resv_type = 'I' AND :NEW.resv_inv_type = 'D' THEN
           nRoomIncrement   := ( nvl(:NEW.no_rooms,0) * (-1) );
           vStatus          := TRUE;
        END IF; -- :NEW.psuedo_room_yn = 'N'
        IF vStatus THEN
          vResort          := :NEW.resort;
          dConsidered_date := :NEW.considered_date;
          vRoom_category   := :NEW.room_category;
        END IF;
     ELSIF DELETING THEN
       IF :OLD.resv_inv_type = 'D' AND :OLD.resv_type = 'I' AND :OLD.psuedo_room_yn = 'N' THEN
         nRoomIncrement   := ((nvl(:OLD.no_rooms,0)) * (-1));
         vResort          := :OLD.resort;
         dConsidered_date := :OLD.considered_date;
         vRoom_category   := :OLD.room_category;
       END IF;
     END IF;
     IF NVL(nRoomIncrement,0) <> 0 THEN
        IF hurdle_rates_upsold.PopulateTabs THEN
           nRec := NVL(hurdle_rates_upsold.resort_tab.LAST,0)+1;
           hurdle_rates_upsold.resort_tab(nRec)          :=  vResort;
           hurdle_rates_upsold.considered_date_tab(nRec) :=  dConsidered_date;
           hurdle_rates_upsold.room_category_tab(nRec)   :=  vRoom_category;
           hurdle_rates_upsold.no_rooms_tab(nRec)        :=  nRoomIncrement;
        ELSE
          UPDATE_UPSOLDS(nvl(:NEW.resort,:OLD.resort), nvl(:NEW.considered_date,:OLD.considered_date), nvl(:NEW.room_category,:OLD.room_category), nRoomIncrement);
        END IF;
     END IF;
   END IF;
   IF INSERTING OR UPDATING THEN
      Data_Mart_Api.Get_Pms_Central_Rate(In_Resort => :new.Resort, In_Date => :new.considered_date, Out_Central_Rate => v_Rec.Central_Exchange_Rate, Out_Central_Currency => v_Rec.Central_Currency_Code);
      IF v_Rec.Central_Exchange_Rate IS NOT NULL AND v_Rec.Central_Currency_Code IS NOT NULL then
         :new.Central_Exchange_Rate := v_Rec.Central_Exchange_Rate ;
         :new.Central_Currency_Code := v_Rec.Central_Currency_Code ;
      end IF;
      :new.update_business_date := trunc(pms_p.business_date());
      :new.update_date          := sysdate();
   END IF;
   Gem.Trace_Off;
  EXCEPTION
    WHEN OTHERS THEN
      Gem.Handle_Exceptions;
END;
/
 
 
CREATE OR REPLACE TRIGGER OPERA.Reservation_Summary_Trg
  AFTER INSERT OR DELETE OR UPDATE ON Reservation_Summary
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
DECLARE
  v_Change_Type VARCHAR2(20);
  o_Rec         Datamart_Fact.Resv_Summary_Rec;
  n_Rec         Datamart_Fact.Resv_Summary_Rec;
BEGIN
  Gem.Trace_On('Trigger: Reservation_Summary_Trg');
  IF Datamart_Fact.Enable_Logging_Yn = 'Y' THEN
    IF Updating THEN
      v_Change_Type := 'UPDATING';
    ELSIF Inserting THEN
      v_Change_Type := 'INSERTING';
    ELSIF Deleting THEN
      v_Change_Type := 'DELETING';
    END IF;
    IF Updating OR
       Deleting THEN
      o_Rec.Id                    := :OLD.Id;
      o_Rec.Resort                := :OLD.Resort;
      o_Rec.Event_Type            := :OLD.Event_Type;
      o_Rec.Event_Id              := :OLD.Event_Id;
      o_Rec.Considered_Date       := nvl(:OLD.Considered_Date, Pms_p.Business_Date);
      o_Rec.Room_Category         := :OLD.Room_Category;
      o_Rec.Room_Class            := :OLD.Room_Class;
      o_Rec.Market_Code           := :OLD.Market_Code;
      o_Rec.Source_Code           := :OLD.Source_Code;
      o_Rec.Rate_Code             := :OLD.Rate_Code;
      o_Rec.Region_Code           := :OLD.Region_Code;
      o_Rec.Group_Id              := :OLD.Group_Id;
      o_Rec.Resv_Type             := :OLD.Resv_Type;
      o_Rec.Resv_Inv_Type         := :OLD.Resv_Inv_Type;
      o_Rec.Psuedo_Room_Yn        := :OLD.Psuedo_Room_Yn;
      o_Rec.Arr_Rooms             := :OLD.Arr_Rooms;
      o_Rec.Adults                := :OLD.Adults;
      o_Rec.Children              := :OLD.Children;
      o_Rec.Dep_Rooms             := :OLD.Dep_Rooms;
      o_Rec.No_Rooms              := :OLD.No_Rooms;
      o_Rec.Gross_Rate            := :OLD.Gross_Rate;
      o_Rec.Net_Room_Revenue      := :OLD.Net_Room_Revenue;
      o_Rec.Extra_Revenue         := :OLD.Extra_Revenue;
      o_Rec.Oo_Rooms              := :OLD.Oo_Rooms;
      o_Rec.Os_Rooms              := :OLD.Os_Rooms;
      o_Rec.Remaining_Block_Rooms := :OLD.Remaining_Block_Rooms;
      o_Rec.Pickedup_Block_Rooms  := :OLD.Pickedup_Block_Rooms;
      o_Rec.Single_Occupancy      := :OLD.Single_Occupancy;
      o_Rec.Multiple_Occupancy    := :OLD.Multiple_Occupancy;
      o_Rec.Block_Status          := :OLD.Block_Status;
      o_Rec.Arr_Persons           := :OLD.Arr_Persons;
      o_Rec.Dep_Persons           := :OLD.Dep_Persons;
      o_Rec.Wl_Rooms              := :OLD.Wl_Rooms;
      o_Rec.Wl_Persons            := :OLD.Wl_Persons;
      o_Rec.Day_Use_Rooms         := :OLD.Day_Use_Rooms;
      o_Rec.Day_Use_Persons       := :OLD.Day_Use_Persons;
      o_Rec.Booking_Status        := :OLD.Booking_Status;
      o_Rec.Resv_Status           := :OLD.Resv_Status;
      o_Rec.Day_Use_Yn            := :OLD.Day_Use_Yn;
      o_Rec.Channel               := :OLD.Channel;
      o_Rec.Country               := :OLD.Country;
      o_Rec.Nationality           := :OLD.Nationality;
      o_Rec.Cribs                 := :OLD.Cribs;
      o_Rec.Extra_Beds            := :OLD.Extra_Beds;
      o_Rec.Adults_Tax_Free       := :OLD.Adults_Tax_Free;
      o_Rec.Children_Tax_Free     := :OLD.Children_Tax_Free;
      o_Rec.Rate_Category         := :OLD.Rate_Category;
      o_Rec.Rate_Class            := :OLD.Rate_Class;
      o_Rec.Room_Revenue          := :OLD.Room_Revenue;
      o_Rec.Food_Revenue          := :OLD.Food_Revenue;
      o_Rec.Other_Revenue         := :OLD.Other_Revenue;
      o_Rec.Total_Revenue         := :OLD.Total_Revenue;
      o_Rec.Non_Revenue           := :OLD.Non_Revenue;
      o_Rec.Allotment_Header_Id   := :OLD.Allotment_Header_Id;
      o_Rec.Room_Revenue_Tax      := :OLD.Room_Revenue_Tax;
      o_Rec.Food_Revenue_Tax      := :OLD.Food_Revenue_Tax;
      o_Rec.Other_Revenue_Tax     := :OLD.Other_Revenue_Tax;
      o_Rec.Total_Revenue_Tax     := :OLD.Total_Revenue_Tax;
      o_Rec.Non_Revenue_Tax       := :OLD.Non_Revenue_Tax;
      o_Rec.City                  := :OLD.City;
      o_Rec.Zip_Code              := :OLD.Zip_Code;
      o_Rec.District              := :OLD.District;
      o_Rec.State                 := :OLD.State;
      o_Rec.Children1             := :OLD.Children1;
      o_Rec.Children2             := :OLD.Children2;
      o_Rec.Children3             := :OLD.Children3;
      o_Rec.Children4             := :OLD.Children4;
      o_Rec.Children5             := :OLD.Children5;
      o_Rec.Owner_Ff_Flag         := :OLD.Owner_Ff_Flag;
      o_Rec.Owner_Rental_Flag     := :OLD.Owner_Rental_Flag;
      o_Rec.Fc_Gross_Rate         := :OLD.Fc_Gross_Rate;
      o_Rec.Fc_Net_Room_Revenue   := :OLD.Fc_Net_Room_Revenue;
      o_Rec.Fc_Extra_Revenue      := :OLD.Fc_Extra_Revenue;
      o_Rec.Fc_Room_Revenue       := :OLD.Fc_Room_Revenue;
      o_Rec.Fc_Food_Revenue       := :OLD.Fc_Food_Revenue;
      o_Rec.Fc_Other_Revenue      := :OLD.Fc_Other_Revenue;
      o_Rec.Fc_Total_Revenue      := :OLD.Fc_Total_Revenue;
      o_Rec.Fc_Non_Revenue        := :OLD.Fc_Non_Revenue;
      o_Rec.Fc_Room_Revenue_Tax   := :OLD.Fc_Room_Revenue_Tax;
      o_Rec.Fc_Food_Revenue_Tax   := :OLD.Fc_Food_Revenue_Tax;
      o_Rec.Fc_Other_Revenue_Tax  := :OLD.Fc_Other_Revenue_Tax;
      o_Rec.Fc_Total_Revenue_Tax  := :OLD.Fc_Total_Revenue_Tax;
      o_Rec.Fc_Non_Revenue_Tax    := :OLD.Fc_Non_Revenue_Tax;
      o_Rec.Currency_Code         := :OLD.Currency_Code;
      o_Rec.Exchange_Date         := :OLD.Exchange_Date;
      o_Rec.Update_Business_Date  := nvl(:OLD.Update_Business_Date, Pms_p.Business_Date);
      o_Rec.Update_Date           := nvl(:OLD.Update_Date,sysdate);
      o_Rec.Central_Currency_Code := :OLD.Central_Currency_Code;
      o_Rec.Central_Exchange_Rate := :OLD.Central_Exchange_Rate;
      o_Rec.gender                := :OLD.gender;
      o_Rec.vip_status            := :OLD.vip_status;
      o_Rec.quantity              := :OLD.quantity;
      o_Rec.Trunc_Begin_Date      := :OLD.Trunc_Begin_Date;
      o_Rec.Trunc_End_Date        := :OLD.Trunc_End_Date;
      o_Rec.Business_Date_Created := :OLD.Business_Date_Created;
      o_Rec.Res_Insert_Source     := :OLD.Res_Insert_Source;
      o_Rec.Parent_Company_Id     := :OLD.Parent_Company_Id;
      o_Rec.Agent_Id     := :OLD.Agent_Id;
    END IF;
    IF Updating OR
       Inserting THEN
      n_Rec.Id                    := :NEW.Id;
      n_Rec.Resort                := :NEW.Resort;
      n_Rec.Event_Type            := :NEW.Event_Type;
      n_Rec.Event_Id              := :NEW.Event_Id;
      n_Rec.Considered_Date       := nvl(:NEW.Considered_Date,Pms_p.Business_Date) ;
      n_Rec.Room_Category         := :NEW.Room_Category;
      n_Rec.Room_Class            := :NEW.Room_Class;
      n_Rec.Market_Code           := :NEW.Market_Code;
      n_Rec.Source_Code           := :NEW.Source_Code;
      n_Rec.Rate_Code             := :NEW.Rate_Code;
      n_Rec.Region_Code           := :NEW.Region_Code;
      n_Rec.Group_Id              := :NEW.Group_Id;
      n_Rec.Resv_Type             := :NEW.Resv_Type;
      n_Rec.Resv_Inv_Type         := :NEW.Resv_Inv_Type;
      n_Rec.Psuedo_Room_Yn        := :NEW.Psuedo_Room_Yn;
      n_Rec.Arr_Rooms             := :NEW.Arr_Rooms;
      n_Rec.Adults                := :NEW.Adults;
      n_Rec.Children              := :NEW.Children;
      n_Rec.Dep_Rooms             := :NEW.Dep_Rooms;
      n_Rec.No_Rooms              := :NEW.No_Rooms;
      n_Rec.Gross_Rate            := :NEW.Gross_Rate;
      n_Rec.Net_Room_Revenue      := :NEW.Net_Room_Revenue;
      n_Rec.Extra_Revenue         := :NEW.Extra_Revenue;
      n_Rec.Oo_Rooms              := :NEW.Oo_Rooms;
      n_Rec.Os_Rooms              := :NEW.Os_Rooms;
      n_Rec.Remaining_Block_Rooms := :NEW.Remaining_Block_Rooms;
      n_Rec.Pickedup_Block_Rooms  := :NEW.Pickedup_Block_Rooms;
      n_Rec.Single_Occupancy      := :NEW.Single_Occupancy;
      n_Rec.Multiple_Occupancy    := :NEW.Multiple_Occupancy;
      n_Rec.Block_Status          := :NEW.Block_Status;
      n_Rec.Arr_Persons           := :NEW.Arr_Persons;
      n_Rec.Dep_Persons           := :NEW.Dep_Persons;
      n_Rec.Wl_Rooms              := :NEW.Wl_Rooms;
      n_Rec.Wl_Persons            := :NEW.Wl_Persons;
      n_Rec.Day_Use_Rooms         := :NEW.Day_Use_Rooms;
      n_Rec.Day_Use_Persons       := :NEW.Day_Use_Persons;
      n_Rec.Booking_Status        := :NEW.Booking_Status;
      n_Rec.Resv_Status           := :NEW.Resv_Status;
      n_Rec.Day_Use_Yn            := :NEW.Day_Use_Yn;
      n_Rec.Channel               := :NEW.Channel;
      n_Rec.Country               := :NEW.Country;
      n_Rec.Nationality           := :NEW.Nationality;
      n_Rec.Cribs                 := :NEW.Cribs;
      n_Rec.Extra_Beds            := :NEW.Extra_Beds;
      n_Rec.Adults_Tax_Free       := :NEW.Adults_Tax_Free;
      n_Rec.Children_Tax_Free     := :NEW.Children_Tax_Free;
      n_Rec.Rate_Category         := :NEW.Rate_Category;
      n_Rec.Rate_Class            := :NEW.Rate_Class;
      n_Rec.Room_Revenue          := :NEW.Room_Revenue;
      n_Rec.Food_Revenue          := :NEW.Food_Revenue;
      n_Rec.Other_Revenue         := :NEW.Other_Revenue;
      n_Rec.Total_Revenue         := :NEW.Total_Revenue;
      n_Rec.Non_Revenue           := :NEW.Non_Revenue;
      n_Rec.Allotment_Header_Id   := :NEW.Allotment_Header_Id;
      n_Rec.Room_Revenue_Tax      := :NEW.Room_Revenue_Tax;
      n_Rec.Food_Revenue_Tax      := :NEW.Food_Revenue_Tax;
      n_Rec.Other_Revenue_Tax     := :NEW.Other_Revenue_Tax;
      n_Rec.Total_Revenue_Tax     := :NEW.Total_Revenue_Tax;
      n_Rec.Non_Revenue_Tax       := :NEW.Non_Revenue_Tax;
      n_Rec.City                  := :NEW.City;
      n_Rec.Zip_Code              := :NEW.Zip_Code;
      n_Rec.District              := :NEW.District;
      n_Rec.State                 := :NEW.State;
      n_Rec.Children1             := :NEW.Children1;
      n_Rec.Children2             := :NEW.Children2;
      n_Rec.Children3             := :NEW.Children3;
      n_Rec.Children4             := :NEW.Children4;
      n_Rec.Children5             := :NEW.Children5;
      n_Rec.Owner_Ff_Flag         := :NEW.Owner_Ff_Flag;
      n_Rec.Owner_Rental_Flag     := :NEW.Owner_Rental_Flag;
      n_Rec.Fc_Gross_Rate         := :NEW.Fc_Gross_Rate;
      n_Rec.Fc_Net_Room_Revenue   := :NEW.Fc_Net_Room_Revenue;
      n_Rec.Fc_Extra_Revenue      := :NEW.Fc_Extra_Revenue;
      n_Rec.Fc_Room_Revenue       := :NEW.Fc_Room_Revenue;
      n_Rec.Fc_Food_Revenue       := :NEW.Fc_Food_Revenue;
      n_Rec.Fc_Other_Revenue      := :NEW.Fc_Other_Revenue;
      n_Rec.Fc_Total_Revenue      := :NEW.Fc_Total_Revenue;
      n_Rec.Fc_Non_Revenue        := :NEW.Fc_Non_Revenue;
      n_Rec.Fc_Room_Revenue_Tax   := :NEW.Fc_Room_Revenue_Tax;
      n_Rec.Fc_Food_Revenue_Tax   := :NEW.Fc_Food_Revenue_Tax;
      n_Rec.Fc_Other_Revenue_Tax  := :NEW.Fc_Other_Revenue_Tax;
      n_Rec.Fc_Total_Revenue_Tax  := :NEW.Fc_Total_Revenue_Tax;
      n_Rec.Fc_Non_Revenue_Tax    := :NEW.Fc_Non_Revenue_Tax;
      n_Rec.Currency_Code         := :NEW.Currency_Code;
      n_Rec.Exchange_Date         := :NEW.Exchange_Date;
      n_Rec.Update_Business_Date  := Pms_p.Business_Date;
      n_Rec.Update_Date           := nvl(:NEW.Update_Date, sysdate);
      n_Rec.Central_Currency_Code := :NEW.Central_Currency_Code;
      n_Rec.Central_Exchange_Rate := :NEW.Central_Exchange_Rate;
      n_Rec.gender                := :NEW.gender;
      n_Rec.vip_status            := :NEW.vip_status;
      n_Rec.quantity              := :NEW.quantity;
      n_Rec.Trunc_Begin_Date      := :NEW.Trunc_Begin_Date;
      n_Rec.Trunc_End_Date        := :NEW.Trunc_End_Date;
      n_Rec.Business_Date_Created := :NEW.Business_Date_Created;
      n_Rec.Res_Insert_Source     := :NEW.Res_Insert_Source;
      n_Rec.Parent_Company_Id     := :NEW.Parent_Company_Id;
      n_Rec.Agent_Id     := :NEW.Agent_Id;
    END IF;
    Datamart_Fact.Process_Resv_Summary_Changes(In_Change_Type => v_Change_Type, In_Old_Rec => o_Rec, In_New_Rec => n_Rec);
  END IF; -- datamart_fact.Enable_Logging_Yn = 'Y' then
  Gem.Trace_Off;
EXCEPTION
  WHEN OTHERS THEN
    Gem.Handle_Exceptions;
END;
/
 
 
DROP SYNONYM OXI.RESERVATION_SUMMARY;
 
CREATE SYNONYM OXI.RESERVATION_SUMMARY FOR OPERA.RESERVATION_SUMMARY;
 
 
DROP SYNONYM OXIHUB.RESERVATION_SUMMARY;
 
CREATE SYNONYM OXIHUB.RESERVATION_SUMMARY FOR OPERA.RESERVATION_SUMMARY;
 
 
ALTER TABLE OPERA.RESERVATION_SUMMARY ADD (
  CONSTRAINT AVCON_1230480_DAY_U_000
 CHECK (DAY_USE_YN IN ('Y', 'N')),
  CONSTRAINT AVCON_864375_EVENT_000
 CHECK (EVENT_TYPE IN ('H', 'B', 'R')),
  CONSTRAINT AVCON_864375_RESV__000
 CHECK (RESV_TYPE IN ('B', 'I')));

[attila771]

Il manque 2 tables

[ORA-007]

Bonjour,

je ne vois pas le champ TRUNC_ACTUAL_CHECK_OUT_DATE ... et il manque la description de la table RESORT_ORIGINS_OF_BOOKING et de RESERVATION_NAME.

[bpdelavega]

Désolé tu as parfaitement raison !
Table RESORT_ORIGINS_OF_BOOKING

Code :

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
CREATE TABLE OPERA.RESERVATION_SUMMARY
(
  ID                     NUMBER                 NOT NULL,
  RESORT                 VARCHAR2(20 BYTE)      NOT NULL,
  EVENT_TYPE             VARCHAR2(1 BYTE)       NOT NULL,
  EVENT_ID               VARCHAR2(20 BYTE)      NOT NULL,
  CONSIDERED_DATE        DATE,
  ROOM_CATEGORY          VARCHAR2(20 BYTE),
  ROOM_CLASS             VARCHAR2(20 BYTE),
  MARKET_CODE            VARCHAR2(20 BYTE),
  SOURCE_CODE            VARCHAR2(20 BYTE),
  RATE_CODE              VARCHAR2(20 BYTE),
  REGION_CODE            VARCHAR2(20 BYTE),
  GROUP_ID               NUMBER,
  RESV_TYPE              VARCHAR2(1 BYTE),
  RESV_INV_TYPE          VARCHAR2(1 BYTE),
  PSUEDO_ROOM_YN         VARCHAR2(1 BYTE),
  ARR_ROOMS              NUMBER,
  ADULTS                 NUMBER,
  CHILDREN               NUMBER,
  DEP_ROOMS              NUMBER,
  NO_ROOMS               NUMBER,
  GROSS_RATE             NUMBER,
  NET_ROOM_REVENUE       NUMBER,
  EXTRA_REVENUE          NUMBER,
  OO_ROOMS               NUMBER,
  OS_ROOMS               NUMBER,
  REMAINING_BLOCK_ROOMS  NUMBER,
  PICKEDUP_BLOCK_ROOMS   NUMBER,
  SINGLE_OCCUPANCY       NUMBER,
  MULTIPLE_OCCUPANCY     NUMBER,
  BLOCK_STATUS           VARCHAR2(20 BYTE),
  ARR_PERSONS            NUMBER,
  DEP_PERSONS            NUMBER,
  WL_ROOMS               NUMBER,
  WL_PERSONS             NUMBER,
  DAY_USE_ROOMS          NUMBER,
  DAY_USE_PERSONS        NUMBER,
  BOOKING_STATUS         VARCHAR2(20 BYTE),
  RESV_STATUS            VARCHAR2(20 BYTE),
  DAY_USE_YN             VARCHAR2(1 BYTE)       DEFAULT 'N',
  CHANNEL                VARCHAR2(40 BYTE),
  COUNTRY                VARCHAR2(20 BYTE),
  NATIONALITY            VARCHAR2(20 BYTE),
  CRIBS                  NUMBER,
  EXTRA_BEDS             NUMBER,
  ADULTS_TAX_FREE        NUMBER,
  CHILDREN_TAX_FREE      NUMBER,
  RATE_CATEGORY          VARCHAR2(20 BYTE),
  RATE_CLASS             VARCHAR2(20 BYTE),
  ROOM_REVENUE           NUMBER(38,12),
  FOOD_REVENUE           NUMBER(38,12),
  OTHER_REVENUE          NUMBER(38,12),
  TOTAL_REVENUE          NUMBER(38,12),
  NON_REVENUE            NUMBER(38,12),
  ALLOTMENT_HEADER_ID    NUMBER,
  ROOM_REVENUE_TAX       NUMBER,
  FOOD_REVENUE_TAX       NUMBER,
  OTHER_REVENUE_TAX      NUMBER,
  TOTAL_REVENUE_TAX      NUMBER,
  NON_REVENUE_TAX        NUMBER,
  CITY                   VARCHAR2(40 BYTE),
  ZIP_CODE               VARCHAR2(15 BYTE),
  DISTRICT               VARCHAR2(10 BYTE),
  STATE                  VARCHAR2(20 BYTE),
  CHILDREN1              NUMBER,
  CHILDREN2              NUMBER,
  CHILDREN3              NUMBER,
  CHILDREN4              NUMBER,
  CHILDREN5              NUMBER,
  OWNER_FF_FLAG          VARCHAR2(1 BYTE),
  OWNER_RENTAL_FLAG      VARCHAR2(1 BYTE),
  FC_GROSS_RATE          NUMBER(38,12),
  FC_NET_ROOM_REVENUE    NUMBER(38,12),
  FC_EXTRA_REVENUE       NUMBER(38,12),
  FC_ROOM_REVENUE        NUMBER(38,12),
  FC_FOOD_REVENUE        NUMBER(38,12),
  FC_OTHER_REVENUE       NUMBER(38,12),
  FC_TOTAL_REVENUE       NUMBER(38,12),
  FC_NON_REVENUE         NUMBER(38,12),
  FC_ROOM_REVENUE_TAX    NUMBER(38,12),
  FC_FOOD_REVENUE_TAX    NUMBER(38,12),
  FC_OTHER_REVENUE_TAX   NUMBER(38,12),
  FC_TOTAL_REVENUE_TAX   NUMBER(38,12),
  FC_NON_REVENUE_TAX     NUMBER(38,12),
  CURRENCY_CODE          VARCHAR2(20 BYTE),
  EXCHANGE_DATE          DATE,
  UPDATE_BUSINESS_DATE   DATE,
  UPDATE_DATE            DATE,
  CENTRAL_CURRENCY_CODE  VARCHAR2(20 BYTE),
  CENTRAL_EXCHANGE_RATE  NUMBER,
  GENDER                 VARCHAR2(1 BYTE),
  VIP_STATUS             VARCHAR2(20 BYTE),
  QUANTITY               NUMBER,
  TRUNC_BEGIN_DATE       DATE,
  TRUNC_END_DATE         DATE,
  BUSINESS_DATE_CREATED  DATE,
  RES_INSERT_SOURCE      VARCHAR2(20 BYTE),
  PARENT_COMPANY_ID      NUMBER,
  AGENT_ID               NUMBER,
  TURNDOWN_STATUS        VARCHAR2(2 BYTE)
)

Table RESERVATION_NAME

Code :

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
 
CREATE TABLE OPERA.RESERVATION_NAME
(
  RESORT                       VARCHAR2(20 BYTE) NOT NULL,
  RESV_NAME_ID                 NUMBER           NOT NULL,
  NAME_ID                      NUMBER           NOT NULL,
  NAME_USAGE_TYPE              VARCHAR2(20 BYTE) DEFAULT 'DG' NOT NULL,
  CONTACT_NAME_ID              NUMBER,
  INSERT_DATE                  DATE             NOT NULL,
  INSERT_USER                  NUMBER           NOT NULL,
  UPDATE_USER                  NUMBER           NOT NULL,
  UPDATE_DATE                  DATE             NOT NULL,
  RESV_STATUS                  VARCHAR2(20 BYTE) NOT NULL,
  COMMISSION_CODE              VARCHAR2(20 BYTE),
  ADDRESS_ID                   NUMBER,
  PHONE_ID                     NUMBER,
  FAX_YN                       VARCHAR2(1 BYTE),
  MAIL_YN                      VARCHAR2(1 BYTE),
  PRINT_RATE_YN                VARCHAR2(1 BYTE),
  REPORT_ID                    VARCHAR2(20 BYTE),
  RESV_NO                      NUMBER,
  CONFIRMATION_NO              VARCHAR2(50 BYTE),
  BEGIN_DATE                   DATE,
  END_DATE                     DATE,
  FAX_ID                       NUMBER,
  EMAIL_ID                     NUMBER,
  EMAIL_YN                     VARCHAR2(1 BYTE),
  CONSUMER_YN                  VARCHAR2(1 BYTE) DEFAULT 'Y',
  CREDIT_CARD_ID               NUMBER,
  FINANCIALLY_RESPONSIBLE_YN   VARCHAR2(1 BYTE) DEFAULT 'Y',
  PAYMENT_METHOD               VARCHAR2(50 BYTE),
  INTERMEDIARY_YN              VARCHAR2(1 BYTE) DEFAULT 'Y',
  POSTING_ALLOWED_YN           VARCHAR2(1 BYTE),
  DISPLAY_COLOR                VARCHAR2(20 BYTE),
  ACTUAL_CHECK_IN_DATE         DATE,
  TRUNC_ACTUAL_CHECK_IN_DATE   DATE,
  ACTUAL_CHECK_OUT_DATE        DATE,
  TRUNC_ACTUAL_CHECK_OUT_DATE  DATE,
  CREDIT_LIMIT                 NUMBER,
  AUTHORIZED_BY                NUMBER,
  PARENT_RESV_NAME_ID          NUMBER,
  CANCELLATION_NO              VARCHAR2(50 BYTE),
  CANCELLATION_REASON_CODE     VARCHAR2(20 BYTE),
  CANCELLATION_REASON_DESC     VARCHAR2(2000 BYTE),
  ARRIVAL_TRANSPORT_TYPE       VARCHAR2(20 BYTE),
  ARRIVAL_STATION_CODE         VARCHAR2(20 BYTE),
  ARRIVAL_CARRIER_CODE         VARCHAR2(20 BYTE),
  ARRIVAL_TRANSPORT_CODE       VARCHAR2(20 BYTE),
  ARRIVAL_DATE_TIME            DATE,
  ARRIVAL_ESTIMATE_TIME        DATE,
  ARRIVAL_TRANPORTATION_YN     VARCHAR2(1 BYTE) DEFAULT 'N',
  ARRIVAL_COMMENTS             VARCHAR2(2000 BYTE),
  DEPARTURE_TRANSPORT_TYPE     VARCHAR2(20 BYTE),
  DEPARTURE_STATION_CODE       VARCHAR2(20 BYTE),
  DEPARTURE_CARRIER_CODE       VARCHAR2(20 BYTE),
  DEPARTURE_TRANSPORT_CODE     VARCHAR2(20 BYTE),
  DEPARTURE_DATE_TIME          DATE,
  DEPARTURE_ESTIMATE_TIME      DATE,
  DEPARTURE_TRANSPORTATION_YN  VARCHAR2(1 BYTE) DEFAULT 'N',
  DEPARTURE_COMMENTS           VARCHAR2(2000 BYTE),
  CANCELLATION_DATE            DATE,
  GUARANTEE_CODE               VARCHAR2(20 BYTE),
  WL_REASON_DESCRIPTION        VARCHAR2(2000 BYTE),
  WL_REASON_CODE               VARCHAR2(20 BYTE),
  WL_PRIORITY                  VARCHAR2(20 BYTE),
  DO_NOT_MOVE_ROOM             VARCHAR2(1 BYTE),
  EXTERNAL_REFERENCE           VARCHAR2(50 BYTE),
  PARTY_CODE                   VARCHAR2(50 BYTE),
  WALKIN_YN                    VARCHAR2(1 BYTE),
  ORIGINAL_END_DATE            DATE,
  APPROVAL_AMOUNT_CALC_METHOD  NUMBER,
  AMOUNT_PERCENT               NUMBER,
  NAME_TAX_TYPE                VARCHAR2(20 BYTE),
  TAX_EXEMPT_NO                VARCHAR2(20 BYTE),
  ROOM_FEATURES                VARCHAR2(2000 BYTE),
  WL_TELEPHONE_NO              VARCHAR2(20 BYTE),
  VIDEO_CHECKOUT_YN            VARCHAR2(1 BYTE),
  DISCOUNT_AMT                 NUMBER,
  DISCOUNT_PRCNT               NUMBER,
  DISCOUNT_REASON_CODE         VARCHAR2(20 BYTE),
  COMMISSION_PAID              NUMBER           DEFAULT 0,
  COMMISSION_HOLD_CODE         VARCHAR2(20 BYTE),
  TRUNC_BEGIN_DATE             DATE,
  TRUNC_END_DATE               DATE,
  SGUEST_NAME                  VARCHAR2(80 BYTE),
  MEMBERSHIP_ID                NUMBER,
  UDFC01                       VARCHAR2(200 BYTE),
  UDFC02                       VARCHAR2(200 BYTE),
  UDFC03                       VARCHAR2(200 BYTE),
  UDFC04                       VARCHAR2(200 BYTE),
  UDFC05                       VARCHAR2(200 BYTE),
  UDFC06                       VARCHAR2(200 BYTE),
  UDFC07                       VARCHAR2(200 BYTE),
  UDFC08                       VARCHAR2(200 BYTE),
  UDFC09                       VARCHAR2(200 BYTE),
  UDFC10                       VARCHAR2(200 BYTE),
  UDFC11                       VARCHAR2(200 BYTE),
  UDFC12                       VARCHAR2(200 BYTE),
  UDFC13                       VARCHAR2(200 BYTE),
  UDFC14                       VARCHAR2(200 BYTE),
  UDFC15                       VARCHAR2(200 BYTE),
  UDFC16                       VARCHAR2(200 BYTE),
  UDFC17                       VARCHAR2(200 BYTE),
  UDFC18                       VARCHAR2(200 BYTE),
  UDFC19                       VARCHAR2(200 BYTE),
  UDFC20                       VARCHAR2(200 BYTE),
  UDFC21                       VARCHAR2(200 BYTE),
  UDFC22                       VARCHAR2(200 BYTE),
  UDFC23                       VARCHAR2(200 BYTE),
  UDFC24                       VARCHAR2(200 BYTE),
  UDFC25                       VARCHAR2(200 BYTE),
  UDFC26                       VARCHAR2(200 BYTE),
  UDFC27                       VARCHAR2(200 BYTE),
  UDFC28                       VARCHAR2(200 BYTE),
  UDFC29                       VARCHAR2(200 BYTE),
  UDFC30                       VARCHAR2(200 BYTE),
  UDFC31                       VARCHAR2(200 BYTE),
  UDFC32                       VARCHAR2(200 BYTE),
  UDFC33                       VARCHAR2(200 BYTE),
  UDFC34                       VARCHAR2(200 BYTE),
  UDFC35                       VARCHAR2(200 BYTE),
  UDFC36                       VARCHAR2(200 BYTE),
  UDFC37                       VARCHAR2(200 BYTE),
  UDFC38                       VARCHAR2(200 BYTE),
  UDFC39                       VARCHAR2(200 BYTE),
  UDFC40                       VARCHAR2(200 BYTE),
  UDFN01                       NUMBER,
  UDFN02                       NUMBER,
  UDFN03                       NUMBER,
  UDFN04                       NUMBER,
  UDFN05                       NUMBER,
  UDFN06                       NUMBER,
  UDFN07                       NUMBER,
  UDFN08                       NUMBER,
  UDFN09                       NUMBER,
  UDFN10                       NUMBER,
  UDFN11                       NUMBER,
  UDFN12                       NUMBER,
  UDFN13                       NUMBER,
  UDFN14                       NUMBER,
  UDFN15                       NUMBER,
  UDFN16                       NUMBER,
  UDFN17                       NUMBER,
  UDFN18                       NUMBER,
  UDFN19                       NUMBER,
  UDFN20                       NUMBER,
  UDFN21                       NUMBER,
  UDFN22                       NUMBER,
  UDFN23                       NUMBER,
  UDFN24                       NUMBER,
  UDFN25                       NUMBER,
  UDFN26                       NUMBER,
  UDFN27                       NUMBER,
  UDFN28                       NUMBER,
  UDFN29                       NUMBER,
  UDFN30                       NUMBER,
  UDFN31                       NUMBER,
  UDFN32                       NUMBER,
  UDFN33                       NUMBER,
  UDFN34                       NUMBER,
  UDFN35                       NUMBER,
  UDFN36                       NUMBER,
  UDFN37                       NUMBER,
  UDFN38                       NUMBER,
  UDFN39                       NUMBER,
  UDFN40                       NUMBER,
  UDFD01                       DATE,
  UDFD02                       DATE,
  UDFD03                       DATE,
  UDFD04                       DATE,
  UDFD05                       DATE,
  UDFD06                       DATE,
  UDFD07                       DATE,
  UDFD08                       DATE,
  UDFD09                       DATE,
  UDFD10                       DATE,
  UDFD11                       DATE,
  UDFD12                       DATE,
  UDFD13                       DATE,
  UDFD14                       DATE,
  UDFD15                       DATE,
  UDFD16                       DATE,
  UDFD17                       DATE,
  UDFD18                       DATE,
  UDFD19                       DATE,
  UDFD20                       DATE,
  INSERT_ACTION_INSTANCE_ID    NUMBER,
  DML_SEQ_NO                   NUMBER,
  BUSINESS_DATE_CREATED        DATE,
  TURNDOWN_YN                  VARCHAR2(1 BYTE),
  ROOM_INSTRUCTIONS            VARCHAR2(500 BYTE),
  ROOM_SERVICE_TIME            VARCHAR2(20 BYTE),
  EVENT_ID                     NUMBER,
  REVENUE_TYPE_CODE            VARCHAR2(2000 BYTE),
  HURDLE                       NUMBER,
  HURDLE_OVERRIDE              VARCHAR2(1 BYTE),
  RATEABLE_VALUE               NUMBER,
  RESTRICTION_OVERRIDE         VARCHAR2(1 BYTE),
  YIELDABLE_YN                 VARCHAR2(1 BYTE),
  SGUEST_FIRSTNAME             VARCHAR2(80 BYTE),
  GUEST_LAST_NAME              VARCHAR2(80 BYTE),
  GUEST_FIRST_NAME             VARCHAR2(80 BYTE),
  GUEST_LAST_NAME_SDX          VARCHAR2(80 BYTE),
  GUEST_FIRST_NAME_SDX         VARCHAR2(80 BYTE),
  CHANNEL                      VARCHAR2(40 BYTE),
  SHARE_SEQ_NO                 NUMBER,
  GUEST_SIGNATURE              VARCHAR2(3000 BYTE),
  EXTENSION_ID                 NUMBER,
  RESV_CONTACT_ID              NUMBER,
  BILLING_CONTACT_ID           NUMBER,
  RES_INSERT_SOURCE            VARCHAR2(20 BYTE),
  RES_INSERT_SOURCE_TYPE       VARCHAR2(40 BYTE),
  MASTER_SHARE                 VARCHAR2(1 BYTE),
  REGISTRATION_CARD_NO         VARCHAR2(40 BYTE),
  TIAD                         VARCHAR2(20 BYTE),
  PURPOSE_OF_STAY              VARCHAR2(20 BYTE),
  REINSTATE_DATE               DATE,
  PURGE_DATE                   DATE,
  LAST_DIRECT_BILL_BATCH_DATE  DATE,
  LAST_PERIODIC_FOLIO_DATE     DATE,
  PERIODIC_FOLIO_FREQ          NUMBER,
  CONFIRMATION_LEG_NO          NUMBER,
  GUEST_STATUS                 VARCHAR2(20 BYTE),
  GUEST_TYPE                   VARCHAR2(20 BYTE),
  CHECKIN_DURATION             NUMBER,
  AUTHORIZER_ID                NUMBER,
  LAST_ONLINE_PRINT_SEQ        NUMBER,
  ENTRY_POINT                  VARCHAR2(40 BYTE),
  ENTRY_DATE                   DATE,
  FOLIO_TEXT1                  VARCHAR2(50 BYTE),
  FOLIO_TEXT2                  VARCHAR2(50 BYTE),
  PSEUDO_MEM_TYPE              VARCHAR2(20 BYTE),
  PSEUDO_MEM_TOTAL_POINTS      NUMBER,
  COMP_TYPE_CODE               VARCHAR2(20 BYTE),
  UNI_CARD_ID                  VARCHAR2(20 BYTE),
  EXP_CHECKINRES_ID            NUMBER,
  ORIGINAL_BEGIN_DATE          DATE,
  OWNER_FF_FLAG                VARCHAR2(1 BYTE),
  COMMISSION_PAYOUT_TO         VARCHAR2(1 BYTE),
  PRE_CHARGING_YN              VARCHAR2(1 BYTE),
  POST_CHARGING_YN             VARCHAR2(1 BYTE),
  POST_CO_FLAG                 VARCHAR2(1 BYTE),
  FOLIO_CLOSE_DATE             DATE,
  SCHEDULE_CHECKOUT_YN         VARCHAR2(1 BYTE),
  CUSTOM_REFERENCE             VARCHAR2(50 BYTE),
  GUARANTEE_CODE_PRE_CI        VARCHAR2(20 BYTE),
  AWARD_MEMBERSHIP_ID          NUMBER,
  ESIGNED_REG_CARD_NAME        VARCHAR2(200 BYTE),
  STATISTICAL_ROOM_TYPE        NUMBER,
  STATISTICAL_RATE_TIER        NUMBER,
  YM_CODE                      VARCHAR2(20 BYTE),
  TAX_REGISTRATION_NO          NUMBER,
  VISA_NUMBER                  VARCHAR2(40 BYTE),
  VISA_ISSUE_DATE              DATE,
  VISA_EXPIRATION_DATE         DATE,
  TAX_NO_OF_STAYS              NUMBER,
  KEY_VALID_UNTIL              DATE,
  KEY_PIN_CODE                 VARCHAR2(20 BYTE),
  PRE_REGISTERED_YN            VARCHAR2(1 BYTE),
  ASB_PRORATED_YN              VARCHAR2(1 BYTE)
)