1 package body QPR_PN_REQUEST_HDRS_PKG as
2 /* $Header: QPRUPRHB.pls 120.0 2007/12/24 20:04:39 vinnaray noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_REQUEST_HEADER_ID in NUMBER,
6 X_SOURCE_REF_HDR_LONG_DESC in VARCHAR2,
7 -- X_ORG_SHORT_DESC in VARCHAR2,
8 -- X_ORG_LONG_DESC in VARCHAR2,
9 X_CUSTOMER_SHORT_DESC in VARCHAR2,
10 X_CUSTOMER_LONG_DESC in VARCHAR2,
11 --X_CONTRACT_SHORT_DESC in VARCHAR2,
12 --X_CONTRACT_LONG_DESC in VARCHAR2,
13 X_SALES_REP_SHORT_DESC in VARCHAR2,
14 X_SALES_REP_LONG_DESC in VARCHAR2,
15 X_SALES_CHANNEL_SHORT_DESC in VARCHAR2,
16 X_SALES_CHANNEL_LONG_DESC in VARCHAR2,
17 X_FREIGHT_TERMS_SHORT_DESC in VARCHAR2,
18 X_FREIGHT_TERMS_LONG_DESC in VARCHAR2,
19 X_PAYMENT_TERMS_SHORT_DESC in VARCHAR2,
20 X_PAYMENT_TERMS_LONG_DESC in VARCHAR2,
21 X_COMMENTS in VARCHAR2,
22 X_MEASURE1_NUMBER in NUMBER,
23 X_MEASURE2_NUMBER in NUMBER,
24 X_MEASURE3_NUMBER in NUMBER,
25 X_MEASURE4_NUMBER in NUMBER,
26 X_MEASURE5_NUMBER in NUMBER,
27 X_MEASURE6_NUMBER in NUMBER,
28 X_MEASURE7_NUMBER in NUMBER,
29 X_MEASURE8_NUMBER in NUMBER,
30 X_MEASURE9_NUMBER in NUMBER,
31 X_MEASURE10_NUMBER in NUMBER,
32 X_MEASURE1_CHAR in VARCHAR2,
33 X_MEASURE2_CHAR in VARCHAR2,
34 X_MEASURE3_CHAR in VARCHAR2,
35 X_MEASURE4_CHAR in VARCHAR2,
36 X_MEASURE5_CHAR in VARCHAR2,
37 X_MEASURE6_CHAR in VARCHAR2,
38 X_MEASURE7_CHAR in VARCHAR2,
39 X_MEASURE8_CHAR in VARCHAR2,
40 X_MEASURE9_CHAR in VARCHAR2,
41 X_MEASURE10_CHAR in VARCHAR2,
42 -- X_PROGRAM_LOGIN_ID in NUMBER,
43 -- X_REQUEST_ID in NUMBER,
44 X_INSTANCE_ID in NUMBER,
45 X_SIMULATION_FLAG in VARCHAR2,
46 X_CUSTOMER_SK in VARCHAR2,
47 X_SALES_CHANNEL_SK in VARCHAR2,
48 X_SALES_REP_SK in VARCHAR2,
49 X_PN_INT_HEADER_ID in NUMBER,
50 X_SOURCE_ID in NUMBER,
51 X_SOURCE_REF_HDR_ID in NUMBER,
52 X_CUSTOMER_ID in NUMBER,
53 --X_CONTRACT_ID in NUMBER,
54 X_SALES_REP_ID in NUMBER,
55 X_PAYMENT_TERMS_ID in NUMBER,
56 X_INVOICE_TO_PARTY_SITE_ID in NUMBER,
57 X_SALES_REP_EMAIL in VARCHAR2,
58 X_SALES_CHANNEL_CODE in VARCHAR2,
59 X_DEAL_EXPIRY_DATE in DATE,
60 X_DEAL_CREATION_DATE in DATE,
61 X_INVOICE_TO_PARTY_SITE_ADDRES in VARCHAR2,
62 --X_PRIORITY in VARCHAR2,
63 X_CURRENCY_SHORT_DESC in VARCHAR2,
64 X_CURRENCY_LONG_DESC in VARCHAR2,
65 X_SOURCE_SHORT_DESC in VARCHAR2,
66 X_SOURCE_LONG_DESC in VARCHAR2,
67 X_SOURCE_REF_HDR_SHORT_DESC in VARCHAR2,
68 X_REFERENCE_NAME in VARCHAR2,
69 X_CREATION_DATE in DATE,
70 X_CREATED_BY in NUMBER,
71 X_LAST_UPDATE_DATE in DATE,
72 X_LAST_UPDATED_BY in NUMBER,
73 X_LAST_UPDATE_LOGIN in NUMBER
74 ) is
75 cursor C is select ROWID from QPR_PN_REQUEST_HDRS_B
76 where REQUEST_HEADER_ID = X_REQUEST_HEADER_ID
77 ;
78 begin
79 insert into QPR_PN_REQUEST_HDRS_B (
80 SOURCE_REF_HDR_LONG_DESC,
81 --ORG_SHORT_DESC,
82 --ORG_LONG_DESC,
83 CUSTOMER_SHORT_DESC,
84 CUSTOMER_LONG_DESC,
85 --CONTRACT_SHORT_DESC,
86 --CONTRACT_LONG_DESC,
87 SALES_REP_SHORT_DESC,
88 SALES_REP_LONG_DESC,
89 SALES_CHANNEL_SHORT_DESC,
90 SALES_CHANNEL_LONG_DESC,
91 FREIGHT_TERMS_SHORT_DESC,
92 FREIGHT_TERMS_LONG_DESC,
93 PAYMENT_TERMS_SHORT_DESC,
94 PAYMENT_TERMS_LONG_DESC,
95 COMMENTS,
96 MEASURE1_NUMBER,
97 MEASURE2_NUMBER,
98 MEASURE3_NUMBER,
99 MEASURE4_NUMBER,
100 MEASURE5_NUMBER,
101 MEASURE6_NUMBER,
102 MEASURE7_NUMBER,
103 MEASURE8_NUMBER,
104 MEASURE9_NUMBER,
105 MEASURE10_NUMBER,
106 MEASURE1_CHAR,
107 MEASURE2_CHAR,
108 MEASURE3_CHAR,
109 MEASURE4_CHAR,
110 MEASURE5_CHAR,
111 MEASURE6_CHAR,
112 MEASURE7_CHAR,
113 MEASURE8_CHAR,
114 MEASURE9_CHAR,
115 MEASURE10_CHAR,
116 --PROGRAM_LOGIN_ID,
117 --REQUEST_ID,
118 INSTANCE_ID,
119 SIMULATION_FLAG,
120 REQUEST_HEADER_ID,
121 CUSTOMER_SK,
122 SALES_CHANNEL_SK,
123 SALES_REP_SK,
124 PN_INT_HEADER_ID,
125 SOURCE_ID,
126 SOURCE_REF_HDR_ID,
127 CUSTOMER_ID,
128 --CONTRACT_ID,
129 SALES_REP_ID,
130 PAYMENT_TERMS_ID,
131 INVOICE_TO_PARTY_SITE_ID,
132 SALES_REP_EMAIL,
133 SALES_CHANNEL_CODE,
134 DEAL_EXPIRY_DATE,
135 DEAL_CREATION_DATE,
136 INVOICE_TO_PARTY_SITE_ADDRESS,
137 --PRIORITY,
138 CURRENCY_SHORT_DESC,
139 CURRENCY_LONG_DESC,
140 SOURCE_SHORT_DESC,
141 SOURCE_LONG_DESC,
142 SOURCE_REF_HDR_SHORT_DESC,
143 CREATION_DATE,
144 CREATED_BY,
145 LAST_UPDATE_DATE,
146 LAST_UPDATED_BY,
147 LAST_UPDATE_LOGIN
148 ) values (
149 X_SOURCE_REF_HDR_LONG_DESC,
150 --X_ORG_SHORT_DESC,
151 --X_ORG_LONG_DESC,
152 X_CUSTOMER_SHORT_DESC,
153 X_CUSTOMER_LONG_DESC,
154 --X_CONTRACT_SHORT_DESC,
155 --X_CONTRACT_LONG_DESC,
156 X_SALES_REP_SHORT_DESC,
157 X_SALES_REP_LONG_DESC,
158 X_SALES_CHANNEL_SHORT_DESC,
159 X_SALES_CHANNEL_LONG_DESC,
160 X_FREIGHT_TERMS_SHORT_DESC,
161 X_FREIGHT_TERMS_LONG_DESC,
162 X_PAYMENT_TERMS_SHORT_DESC,
163 X_PAYMENT_TERMS_LONG_DESC,
164 X_COMMENTS,
165 X_MEASURE1_NUMBER,
166 X_MEASURE2_NUMBER,
167 X_MEASURE3_NUMBER,
168 X_MEASURE4_NUMBER,
169 X_MEASURE5_NUMBER,
170 X_MEASURE6_NUMBER,
171 X_MEASURE7_NUMBER,
172 X_MEASURE8_NUMBER,
173 X_MEASURE9_NUMBER,
174 X_MEASURE10_NUMBER,
175 X_MEASURE1_CHAR,
176 X_MEASURE2_CHAR,
177 X_MEASURE3_CHAR,
178 X_MEASURE4_CHAR,
179 X_MEASURE5_CHAR,
180 X_MEASURE6_CHAR,
181 X_MEASURE7_CHAR,
182 X_MEASURE8_CHAR,
183 X_MEASURE9_CHAR,
184 X_MEASURE10_CHAR,
185 --X_PROGRAM_LOGIN_ID,
186 --X_REQUEST_ID,
187 X_INSTANCE_ID,
188 X_SIMULATION_FLAG,
189 X_REQUEST_HEADER_ID,
190 X_CUSTOMER_SK,
191 X_SALES_CHANNEL_SK,
192 X_SALES_REP_SK,
193 X_PN_INT_HEADER_ID,
194 X_SOURCE_ID,
195 X_SOURCE_REF_HDR_ID,
196 X_CUSTOMER_ID,
197 --X_CONTRACT_ID,
198 X_SALES_REP_ID,
199 X_PAYMENT_TERMS_ID,
200 X_INVOICE_TO_PARTY_SITE_ID,
201 X_SALES_REP_EMAIL,
202 X_SALES_CHANNEL_CODE,
203 X_DEAL_EXPIRY_DATE,
204 X_DEAL_CREATION_DATE,
205 X_INVOICE_TO_PARTY_SITE_ADDRES,
206 --X_PRIORITY,
207 X_CURRENCY_SHORT_DESC,
208 X_CURRENCY_LONG_DESC,
209 X_SOURCE_SHORT_DESC,
210 X_SOURCE_LONG_DESC,
211 X_SOURCE_REF_HDR_SHORT_DESC,
212 X_CREATION_DATE,
213 X_CREATED_BY,
214 X_LAST_UPDATE_DATE,
215 X_LAST_UPDATED_BY,
216 X_LAST_UPDATE_LOGIN
217 );
218
219 insert into QPR_PN_REQUEST_HDRS_TL (
220 REQUEST_HEADER_ID,
221 REFERENCE_NAME,
222 CREATION_DATE,
223 CREATED_BY,
224 LAST_UPDATE_DATE,
225 LAST_UPDATED_BY,
226 LAST_UPDATE_LOGIN,
227 --PROGRAM_ID,
228 --PROGRAM_LOGIN_ID,
229 --PROGRAM_APPLICATION_ID,
230 --REQUEST_ID,
231 LANGUAGE,
232 SOURCE_LANG
233 ) select
234 X_REQUEST_HEADER_ID,
235 X_REFERENCE_NAME,
236 X_CREATION_DATE,
237 X_CREATED_BY,
238 X_LAST_UPDATE_DATE,
239 X_LAST_UPDATED_BY,
240 X_LAST_UPDATE_LOGIN,
241 --X_PROGRAM_ID,
242 --X_PROGRAM_LOGIN_ID,
243 --X_PROGRAM_APPLICATION_ID,
244 --X_REQUEST_ID,
245 L.LANGUAGE_CODE,
246 userenv('LANG')
247 from FND_LANGUAGES L
248 where L.INSTALLED_FLAG in ('I', 'B')
249 and not exists
250 (select NULL
251 from QPR_PN_REQUEST_HDRS_TL T
252 where T.REQUEST_HEADER_ID = X_REQUEST_HEADER_ID
253 and T.LANGUAGE = L.LANGUAGE_CODE);
254
255 open c;
256 fetch c into X_ROWID;
257 if (c%notfound) then
258 close c;
259 raise no_data_found;
260 end if;
261 close c;
262
263 end INSERT_ROW;
264
265 procedure LOCK_ROW (
266 X_REQUEST_HEADER_ID in NUMBER,
267 X_SOURCE_REF_HDR_LONG_DESC in VARCHAR2,
268 -- X_ORG_SHORT_DESC in VARCHAR2,
269 -- X_ORG_LONG_DESC in VARCHAR2,
270 X_CUSTOMER_SHORT_DESC in VARCHAR2,
271 X_CUSTOMER_LONG_DESC in VARCHAR2,
272 --X_CONTRACT_SHORT_DESC in VARCHAR2,
276 X_SALES_CHANNEL_SHORT_DESC in VARCHAR2,
273 --X_CONTRACT_LONG_DESC in VARCHAR2,
274 X_SALES_REP_SHORT_DESC in VARCHAR2,
275 X_SALES_REP_LONG_DESC in VARCHAR2,
277 X_SALES_CHANNEL_LONG_DESC in VARCHAR2,
278 X_FREIGHT_TERMS_SHORT_DESC in VARCHAR2,
279 X_FREIGHT_TERMS_LONG_DESC in VARCHAR2,
280 X_PAYMENT_TERMS_SHORT_DESC in VARCHAR2,
281 X_PAYMENT_TERMS_LONG_DESC in VARCHAR2,
282 X_COMMENTS in VARCHAR2,
283 X_MEASURE1_NUMBER in NUMBER,
284 X_MEASURE2_NUMBER in NUMBER,
285 X_MEASURE3_NUMBER in NUMBER,
286 X_MEASURE4_NUMBER in NUMBER,
287 X_MEASURE5_NUMBER in NUMBER,
288 X_MEASURE6_NUMBER in NUMBER,
289 X_MEASURE7_NUMBER in NUMBER,
290 X_MEASURE8_NUMBER in NUMBER,
291 X_MEASURE9_NUMBER in NUMBER,
292 X_MEASURE10_NUMBER in NUMBER,
293 X_MEASURE1_CHAR in VARCHAR2,
294 X_MEASURE2_CHAR in VARCHAR2,
295 X_MEASURE3_CHAR in VARCHAR2,
296 X_MEASURE4_CHAR in VARCHAR2,
297 X_MEASURE5_CHAR in VARCHAR2,
298 X_MEASURE6_CHAR in VARCHAR2,
299 X_MEASURE7_CHAR in VARCHAR2,
300 X_MEASURE8_CHAR in VARCHAR2,
301 X_MEASURE9_CHAR in VARCHAR2,
302 X_MEASURE10_CHAR in VARCHAR2,
303 -- X_PROGRAM_LOGIN_ID in NUMBER,
304 -- X_REQUEST_ID in NUMBER,
305 X_INSTANCE_ID in NUMBER,
306 X_SIMULATION_FLAG in VARCHAR2,
307 X_CUSTOMER_SK in VARCHAR2,
308 X_SALES_CHANNEL_SK in VARCHAR2,
309 X_SALES_REP_SK in VARCHAR2,
310 X_PN_INT_HEADER_ID in NUMBER,
311 X_SOURCE_ID in NUMBER,
312 X_SOURCE_REF_HDR_ID in NUMBER,
313 X_CUSTOMER_ID in NUMBER,
314 --X_CONTRACT_ID in NUMBER,
315 X_SALES_REP_ID in NUMBER,
316 X_PAYMENT_TERMS_ID in NUMBER,
317 X_INVOICE_TO_PARTY_SITE_ID in NUMBER,
318 X_SALES_REP_EMAIL in VARCHAR2,
319 X_SALES_CHANNEL_CODE in VARCHAR2,
320 X_DEAL_EXPIRY_DATE in DATE,
321 X_DEAL_CREATION_DATE in DATE,
322 X_INVOICE_TO_PARTY_SITE_ADDRES in VARCHAR2,
323 --X_PRIORITY in VARCHAR2,
324 X_CURRENCY_SHORT_DESC in VARCHAR2,
325 X_CURRENCY_LONG_DESC in VARCHAR2,
326 X_SOURCE_SHORT_DESC in VARCHAR2,
327 X_SOURCE_LONG_DESC in VARCHAR2,
328 X_SOURCE_REF_HDR_SHORT_DESC in VARCHAR2,
329 X_REFERENCE_NAME in VARCHAR2
330 ) is
331 cursor c is select
332 SOURCE_REF_HDR_LONG_DESC,
333 --ORG_SHORT_DESC,
334 --ORG_LONG_DESC,
335 CUSTOMER_SHORT_DESC,
336 CUSTOMER_LONG_DESC,
337 --CONTRACT_SHORT_DESC,
338 --CONTRACT_LONG_DESC,
339 SALES_REP_SHORT_DESC,
340 SALES_REP_LONG_DESC,
341 SALES_CHANNEL_SHORT_DESC,
342 SALES_CHANNEL_LONG_DESC,
343 FREIGHT_TERMS_SHORT_DESC,
344 FREIGHT_TERMS_LONG_DESC,
345 PAYMENT_TERMS_SHORT_DESC,
346 PAYMENT_TERMS_LONG_DESC,
347 COMMENTS,
348 MEASURE1_NUMBER,
349 MEASURE2_NUMBER,
350 MEASURE3_NUMBER,
351 MEASURE4_NUMBER,
352 MEASURE5_NUMBER,
353 MEASURE6_NUMBER,
354 MEASURE7_NUMBER,
355 MEASURE8_NUMBER,
356 MEASURE9_NUMBER,
357 MEASURE10_NUMBER,
358 MEASURE1_CHAR,
359 MEASURE2_CHAR,
360 MEASURE3_CHAR,
361 MEASURE4_CHAR,
362 MEASURE5_CHAR,
363 MEASURE6_CHAR,
364 MEASURE7_CHAR,
365 MEASURE8_CHAR,
366 MEASURE9_CHAR,
367 MEASURE10_CHAR,
368 --PROGRAM_LOGIN_ID,
369 --REQUEST_ID,
370 INSTANCE_ID,
371 SIMULATION_FLAG,
372 CUSTOMER_SK,
373 SALES_CHANNEL_SK,
374 SALES_REP_SK,
375 PN_INT_HEADER_ID,
376 SOURCE_ID,
377 SOURCE_REF_HDR_ID,
378 CUSTOMER_ID,
379 --CONTRACT_ID,
380 SALES_REP_ID,
381 PAYMENT_TERMS_ID,
382 INVOICE_TO_PARTY_SITE_ID,
383 SALES_REP_EMAIL,
384 SALES_CHANNEL_CODE,
385 DEAL_EXPIRY_DATE,
386 DEAL_CREATION_DATE,
387 INVOICE_TO_PARTY_SITE_ADDRESS,
388 --PRIORITY,
389 CURRENCY_SHORT_DESC,
390 CURRENCY_LONG_DESC,
391 SOURCE_SHORT_DESC,
392 SOURCE_LONG_DESC,
393 SOURCE_REF_HDR_SHORT_DESC
394 from QPR_PN_REQUEST_HDRS_B
395 where REQUEST_HEADER_ID = X_REQUEST_HEADER_ID
396 for update of REQUEST_HEADER_ID nowait;
397 recinfo c%rowtype;
398
399 cursor c1 is select
400 REFERENCE_NAME,
401 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
402 from QPR_PN_REQUEST_HDRS_TL
403 where REQUEST_HEADER_ID = X_REQUEST_HEADER_ID
404 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
405 for update of REQUEST_HEADER_ID nowait;
406 begin
407 open c;
408 fetch c into recinfo;
409 if (sql%notfound) then
410 close c;
411 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
412 app_exception.raise_exception;
413 end if;
417 -- AND ((recinfo.ORG_SHORT_DESC = X_ORG_SHORT_DESC)
414 close c;
415 if ( ((recinfo.SOURCE_REF_HDR_LONG_DESC = X_SOURCE_REF_HDR_LONG_DESC)
416 OR ((recinfo.SOURCE_REF_HDR_LONG_DESC is null) AND (X_SOURCE_REF_HDR_LONG_DESC is null)))
418 -- OR ((recinfo.ORG_SHORT_DESC is null) AND (X_ORG_SHORT_DESC is null)))
419 -- AND ((recinfo.ORG_LONG_DESC = X_ORG_LONG_DESC)
420 -- OR ((recinfo.ORG_LONG_DESC is null) AND (X_ORG_LONG_DESC is null)))
421 AND ((recinfo.CUSTOMER_SHORT_DESC = X_CUSTOMER_SHORT_DESC)
422 OR ((recinfo.CUSTOMER_SHORT_DESC is null) AND (X_CUSTOMER_SHORT_DESC is null)))
423 AND ((recinfo.CUSTOMER_LONG_DESC = X_CUSTOMER_LONG_DESC)
424 OR ((recinfo.CUSTOMER_LONG_DESC is null) AND (X_CUSTOMER_LONG_DESC is null)))
425 -- AND ((recinfo.CONTRACT_SHORT_DESC = X_CONTRACT_SHORT_DESC)
426 -- OR ((recinfo.CONTRACT_SHORT_DESC is null) AND (X_CONTRACT_SHORT_DESC is null)))
427 -- AND ((recinfo.CONTRACT_LONG_DESC = X_CONTRACT_LONG_DESC)
428 -- OR ((recinfo.CONTRACT_LONG_DESC is null) AND (X_CONTRACT_LONG_DESC is null)))
429 AND ((recinfo.SALES_REP_SHORT_DESC = X_SALES_REP_SHORT_DESC)
430 OR ((recinfo.SALES_REP_SHORT_DESC is null) AND (X_SALES_REP_SHORT_DESC is null)))
431 AND ((recinfo.SALES_REP_LONG_DESC = X_SALES_REP_LONG_DESC)
432 OR ((recinfo.SALES_REP_LONG_DESC is null) AND (X_SALES_REP_LONG_DESC is null)))
433 AND ((recinfo.SALES_CHANNEL_SHORT_DESC = X_SALES_CHANNEL_SHORT_DESC)
434 OR ((recinfo.SALES_CHANNEL_SHORT_DESC is null) AND (X_SALES_CHANNEL_SHORT_DESC is null)))
435 AND ((recinfo.SALES_CHANNEL_LONG_DESC = X_SALES_CHANNEL_LONG_DESC)
436 OR ((recinfo.SALES_CHANNEL_LONG_DESC is null) AND (X_SALES_CHANNEL_LONG_DESC is null)))
437 AND ((recinfo.FREIGHT_TERMS_SHORT_DESC = X_FREIGHT_TERMS_SHORT_DESC)
438 OR ((recinfo.FREIGHT_TERMS_SHORT_DESC is null) AND (X_FREIGHT_TERMS_SHORT_DESC is null)))
439 AND ((recinfo.FREIGHT_TERMS_LONG_DESC = X_FREIGHT_TERMS_LONG_DESC)
440 OR ((recinfo.FREIGHT_TERMS_LONG_DESC is null) AND (X_FREIGHT_TERMS_LONG_DESC is null)))
441 AND ((recinfo.PAYMENT_TERMS_SHORT_DESC = X_PAYMENT_TERMS_SHORT_DESC)
442 OR ((recinfo.PAYMENT_TERMS_SHORT_DESC is null) AND (X_PAYMENT_TERMS_SHORT_DESC is null)))
443 AND ((recinfo.PAYMENT_TERMS_LONG_DESC = X_PAYMENT_TERMS_LONG_DESC)
444 OR ((recinfo.PAYMENT_TERMS_LONG_DESC is null) AND (X_PAYMENT_TERMS_LONG_DESC is null)))
445 AND ((recinfo.COMMENTS = X_COMMENTS)
446 OR ((recinfo.COMMENTS is null) AND (X_COMMENTS is null)))
447 AND ((recinfo.MEASURE1_NUMBER = X_MEASURE1_NUMBER)
448 OR ((recinfo.MEASURE1_NUMBER is null) AND (X_MEASURE1_NUMBER is null)))
449 AND ((recinfo.MEASURE2_NUMBER = X_MEASURE2_NUMBER)
450 OR ((recinfo.MEASURE2_NUMBER is null) AND (X_MEASURE2_NUMBER is null)))
451 AND ((recinfo.MEASURE3_NUMBER = X_MEASURE3_NUMBER)
452 OR ((recinfo.MEASURE3_NUMBER is null) AND (X_MEASURE3_NUMBER is null)))
453 AND ((recinfo.MEASURE4_NUMBER = X_MEASURE4_NUMBER)
454 OR ((recinfo.MEASURE4_NUMBER is null) AND (X_MEASURE4_NUMBER is null)))
455 AND ((recinfo.MEASURE5_NUMBER = X_MEASURE5_NUMBER)
456 OR ((recinfo.MEASURE5_NUMBER is null) AND (X_MEASURE5_NUMBER is null)))
457 AND ((recinfo.MEASURE6_NUMBER = X_MEASURE6_NUMBER)
458 OR ((recinfo.MEASURE6_NUMBER is null) AND (X_MEASURE6_NUMBER is null)))
459 AND ((recinfo.MEASURE7_NUMBER = X_MEASURE7_NUMBER)
460 OR ((recinfo.MEASURE7_NUMBER is null) AND (X_MEASURE7_NUMBER is null)))
461 AND ((recinfo.MEASURE8_NUMBER = X_MEASURE8_NUMBER)
462 OR ((recinfo.MEASURE8_NUMBER is null) AND (X_MEASURE8_NUMBER is null)))
463 AND ((recinfo.MEASURE9_NUMBER = X_MEASURE9_NUMBER)
464 OR ((recinfo.MEASURE9_NUMBER is null) AND (X_MEASURE9_NUMBER is null)))
465 AND ((recinfo.MEASURE10_NUMBER = X_MEASURE10_NUMBER)
466 OR ((recinfo.MEASURE10_NUMBER is null) AND (X_MEASURE10_NUMBER is null)))
467 AND ((recinfo.MEASURE1_CHAR = X_MEASURE1_CHAR)
468 OR ((recinfo.MEASURE1_CHAR is null) AND (X_MEASURE1_CHAR is null)))
469 AND ((recinfo.MEASURE2_CHAR = X_MEASURE2_CHAR)
470 OR ((recinfo.MEASURE2_CHAR is null) AND (X_MEASURE2_CHAR is null)))
471 AND ((recinfo.MEASURE3_CHAR = X_MEASURE3_CHAR)
472 OR ((recinfo.MEASURE3_CHAR is null) AND (X_MEASURE3_CHAR is null)))
473 AND ((recinfo.MEASURE4_CHAR = X_MEASURE4_CHAR)
474 OR ((recinfo.MEASURE4_CHAR is null) AND (X_MEASURE4_CHAR is null)))
475 AND ((recinfo.MEASURE5_CHAR = X_MEASURE5_CHAR)
476 OR ((recinfo.MEASURE5_CHAR is null) AND (X_MEASURE5_CHAR is null)))
477 AND ((recinfo.MEASURE6_CHAR = X_MEASURE6_CHAR)
478 OR ((recinfo.MEASURE6_CHAR is null) AND (X_MEASURE6_CHAR is null)))
479 AND ((recinfo.MEASURE7_CHAR = X_MEASURE7_CHAR)
480 OR ((recinfo.MEASURE7_CHAR is null) AND (X_MEASURE7_CHAR is null)))
481 AND ((recinfo.MEASURE8_CHAR = X_MEASURE8_CHAR)
482 OR ((recinfo.MEASURE8_CHAR is null) AND (X_MEASURE8_CHAR is null)))
483 AND ((recinfo.MEASURE9_CHAR = X_MEASURE9_CHAR)
484 OR ((recinfo.MEASURE9_CHAR is null) AND (X_MEASURE9_CHAR is null)))
485 AND ((recinfo.MEASURE10_CHAR = X_MEASURE10_CHAR)
486 OR ((recinfo.MEASURE10_CHAR is null) AND (X_MEASURE10_CHAR is null)))
487 -- AND ((recinfo.PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID)
488 -- OR ((recinfo.PROGRAM_LOGIN_ID is null) AND (X_PROGRAM_LOGIN_ID is null)))
489 -- AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
490 -- OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
491 AND ((recinfo.INSTANCE_ID = X_INSTANCE_ID)
495 AND ((recinfo.CUSTOMER_SK = X_CUSTOMER_SK)
492 OR ((recinfo.INSTANCE_ID is null) AND (X_INSTANCE_ID is null)))
493 AND ((recinfo.SIMULATION_FLAG = X_SIMULATION_FLAG)
494 OR ((recinfo.SIMULATION_FLAG is null) AND (X_SIMULATION_FLAG is null)))
496 OR ((recinfo.CUSTOMER_SK is null) AND (X_CUSTOMER_SK is null)))
497 AND ((recinfo.SALES_CHANNEL_SK = X_SALES_CHANNEL_SK)
498 OR ((recinfo.SALES_CHANNEL_SK is null) AND (X_SALES_CHANNEL_SK is null)))
499 AND ((recinfo.SALES_REP_SK = X_SALES_REP_SK)
500 OR ((recinfo.SALES_REP_SK is null) AND (X_SALES_REP_SK is null)))
501 AND ((recinfo.PN_INT_HEADER_ID = X_PN_INT_HEADER_ID)
502 OR ((recinfo.PN_INT_HEADER_ID is null) AND (X_PN_INT_HEADER_ID is null)))
503 AND ((recinfo.SOURCE_ID = X_SOURCE_ID)
504 OR ((recinfo.SOURCE_ID is null) AND (X_SOURCE_ID is null)))
505 AND (recinfo.SOURCE_REF_HDR_ID = X_SOURCE_REF_HDR_ID)
506 AND (recinfo.CUSTOMER_ID = X_CUSTOMER_ID)
507 -- AND ((recinfo.CONTRACT_ID = X_CONTRACT_ID)
508 -- OR ((recinfo.CONTRACT_ID is null) AND (X_CONTRACT_ID is null)))
509 AND ((recinfo.SALES_REP_ID = X_SALES_REP_ID)
510 OR ((recinfo.SALES_REP_ID is null) AND (X_SALES_REP_ID is null)))
511 AND ((recinfo.PAYMENT_TERMS_ID = X_PAYMENT_TERMS_ID)
512 OR ((recinfo.PAYMENT_TERMS_ID is null) AND (X_PAYMENT_TERMS_ID is null)))
513 AND ((recinfo.INVOICE_TO_PARTY_SITE_ID = X_INVOICE_TO_PARTY_SITE_ID)
514 OR ((recinfo.INVOICE_TO_PARTY_SITE_ID is null) AND (X_INVOICE_TO_PARTY_SITE_ID is null)))
515 AND ((recinfo.SALES_REP_EMAIL = X_SALES_REP_EMAIL)
516 OR ((recinfo.SALES_REP_EMAIL is null) AND (X_SALES_REP_EMAIL is null)))
517 AND ((recinfo.SALES_CHANNEL_CODE = X_SALES_CHANNEL_CODE)
518 OR ((recinfo.SALES_CHANNEL_CODE is null) AND (X_SALES_CHANNEL_CODE is null)))
519 AND ((recinfo.DEAL_EXPIRY_DATE = X_DEAL_EXPIRY_DATE)
520 OR ((recinfo.DEAL_EXPIRY_DATE is null) AND (X_DEAL_EXPIRY_DATE is null)))
521 AND ((recinfo.DEAL_CREATION_DATE = X_DEAL_CREATION_DATE)
522 OR ((recinfo.DEAL_CREATION_DATE is null) AND (X_DEAL_CREATION_DATE is null)))
523 AND ((recinfo.INVOICE_TO_PARTY_SITE_ADDRESS = X_INVOICE_TO_PARTY_SITE_ADDRES)
524 OR ((recinfo.INVOICE_TO_PARTY_SITE_ADDRESS is null) AND (X_INVOICE_TO_PARTY_SITE_ADDRES is null)))
525 -- AND ((recinfo.PRIORITY = X_PRIORITY)
526 -- OR ((recinfo.PRIORITY is null) AND (X_PRIORITY is null)))
527 AND ((recinfo.CURRENCY_SHORT_DESC = X_CURRENCY_SHORT_DESC)
528 OR ((recinfo.CURRENCY_SHORT_DESC is null) AND (X_CURRENCY_SHORT_DESC is null)))
529 AND ((recinfo.CURRENCY_LONG_DESC = X_CURRENCY_LONG_DESC)
530 OR ((recinfo.CURRENCY_LONG_DESC is null) AND (X_CURRENCY_LONG_DESC is null)))
531 AND ((recinfo.SOURCE_SHORT_DESC = X_SOURCE_SHORT_DESC)
532 OR ((recinfo.SOURCE_SHORT_DESC is null) AND (X_SOURCE_SHORT_DESC is null)))
533 AND ((recinfo.SOURCE_LONG_DESC = X_SOURCE_LONG_DESC)
534 OR ((recinfo.SOURCE_LONG_DESC is null) AND (X_SOURCE_LONG_DESC is null)))
535 AND ((recinfo.SOURCE_REF_HDR_SHORT_DESC = X_SOURCE_REF_HDR_SHORT_DESC)
536 OR ((recinfo.SOURCE_REF_HDR_SHORT_DESC is null) AND (X_SOURCE_REF_HDR_SHORT_DESC is null)))
537 ) then
538 null;
539 else
540 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
541 app_exception.raise_exception;
542 end if;
543
544 for tlinfo in c1 loop
545 if (tlinfo.BASELANG = 'Y') then
546 if ( (tlinfo.REFERENCE_NAME = X_REFERENCE_NAME)
547 ) then
548 null;
549 else
550 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
551 app_exception.raise_exception;
552 end if;
553 end if;
554 end loop;
555 return;
556 end LOCK_ROW;
557
558 procedure UPDATE_ROW (
559 X_REQUEST_HEADER_ID in NUMBER,
560 X_SOURCE_REF_HDR_LONG_DESC in VARCHAR2,
561 -- X_ORG_SHORT_DESC in VARCHAR2,
562 -- X_ORG_LONG_DESC in VARCHAR2,
563 X_CUSTOMER_SHORT_DESC in VARCHAR2,
564 X_CUSTOMER_LONG_DESC in VARCHAR2,
565 --X_CONTRACT_SHORT_DESC in VARCHAR2,
566 --X_CONTRACT_LONG_DESC in VARCHAR2,
567 X_SALES_REP_SHORT_DESC in VARCHAR2,
568 X_SALES_REP_LONG_DESC in VARCHAR2,
569 X_SALES_CHANNEL_SHORT_DESC in VARCHAR2,
570 X_SALES_CHANNEL_LONG_DESC in VARCHAR2,
571 X_FREIGHT_TERMS_SHORT_DESC in VARCHAR2,
572 X_FREIGHT_TERMS_LONG_DESC in VARCHAR2,
573 X_PAYMENT_TERMS_SHORT_DESC in VARCHAR2,
574 X_PAYMENT_TERMS_LONG_DESC in VARCHAR2,
575 X_COMMENTS in VARCHAR2,
576 X_MEASURE1_NUMBER in NUMBER,
577 X_MEASURE2_NUMBER in NUMBER,
578 X_MEASURE3_NUMBER in NUMBER,
579 X_MEASURE4_NUMBER in NUMBER,
580 X_MEASURE5_NUMBER in NUMBER,
581 X_MEASURE6_NUMBER in NUMBER,
582 X_MEASURE7_NUMBER in NUMBER,
583 X_MEASURE8_NUMBER in NUMBER,
584 X_MEASURE9_NUMBER in NUMBER,
585 X_MEASURE10_NUMBER in NUMBER,
586 X_MEASURE1_CHAR in VARCHAR2,
587 X_MEASURE2_CHAR in VARCHAR2,
588 X_MEASURE3_CHAR in VARCHAR2,
589 X_MEASURE4_CHAR in VARCHAR2,
590 X_MEASURE5_CHAR in VARCHAR2,
591 X_MEASURE6_CHAR in VARCHAR2,
592 X_MEASURE7_CHAR in VARCHAR2,
593 X_MEASURE8_CHAR in VARCHAR2,
594 X_MEASURE9_CHAR in VARCHAR2,
595 X_MEASURE10_CHAR in VARCHAR2,
596 -- X_PROGRAM_LOGIN_ID in NUMBER,
597 -- X_REQUEST_ID in NUMBER,
598 X_INSTANCE_ID in NUMBER,
599 X_SIMULATION_FLAG in VARCHAR2,
600 X_CUSTOMER_SK in VARCHAR2,
601 X_SALES_CHANNEL_SK in VARCHAR2,
602 X_SALES_REP_SK in VARCHAR2,
603 X_PN_INT_HEADER_ID in NUMBER,
604 X_SOURCE_ID in NUMBER,
605 X_SOURCE_REF_HDR_ID in NUMBER,
606 X_CUSTOMER_ID in NUMBER,
607 --X_CONTRACT_ID in NUMBER,
608 X_SALES_REP_ID in NUMBER,
609 X_PAYMENT_TERMS_ID in NUMBER,
610 X_INVOICE_TO_PARTY_SITE_ID in NUMBER,
611 X_SALES_REP_EMAIL in VARCHAR2,
615 X_INVOICE_TO_PARTY_SITE_ADDRES in VARCHAR2,
612 X_SALES_CHANNEL_CODE in VARCHAR2,
613 X_DEAL_EXPIRY_DATE in DATE,
614 X_DEAL_CREATION_DATE in DATE,
616 --X_PRIORITY in VARCHAR2,
617 X_CURRENCY_SHORT_DESC in VARCHAR2,
618 X_CURRENCY_LONG_DESC in VARCHAR2,
619 X_SOURCE_SHORT_DESC in VARCHAR2,
620 X_SOURCE_LONG_DESC in VARCHAR2,
621 X_SOURCE_REF_HDR_SHORT_DESC in VARCHAR2,
622 X_REFERENCE_NAME in VARCHAR2,
623 X_LAST_UPDATE_DATE in DATE,
624 X_LAST_UPDATED_BY in NUMBER,
625 X_LAST_UPDATE_LOGIN in NUMBER
626 ) is
627 begin
628 update QPR_PN_REQUEST_HDRS_B set
629 SOURCE_REF_HDR_LONG_DESC = X_SOURCE_REF_HDR_LONG_DESC,
630 --ORG_SHORT_DESC = X_ORG_SHORT_DESC,
631 --ORG_LONG_DESC = X_ORG_LONG_DESC,
632 CUSTOMER_SHORT_DESC = X_CUSTOMER_SHORT_DESC,
633 CUSTOMER_LONG_DESC = X_CUSTOMER_LONG_DESC,
634 --CONTRACT_SHORT_DESC = X_CONTRACT_SHORT_DESC,
635 --CONTRACT_LONG_DESC = X_CONTRACT_LONG_DESC,
636 SALES_REP_SHORT_DESC = X_SALES_REP_SHORT_DESC,
637 SALES_REP_LONG_DESC = X_SALES_REP_LONG_DESC,
638 SALES_CHANNEL_SHORT_DESC = X_SALES_CHANNEL_SHORT_DESC,
639 SALES_CHANNEL_LONG_DESC = X_SALES_CHANNEL_LONG_DESC,
640 FREIGHT_TERMS_SHORT_DESC = X_FREIGHT_TERMS_SHORT_DESC,
641 FREIGHT_TERMS_LONG_DESC = X_FREIGHT_TERMS_LONG_DESC,
642 PAYMENT_TERMS_SHORT_DESC = X_PAYMENT_TERMS_SHORT_DESC,
643 PAYMENT_TERMS_LONG_DESC = X_PAYMENT_TERMS_LONG_DESC,
644 COMMENTS = X_COMMENTS,
645 MEASURE1_NUMBER = X_MEASURE1_NUMBER,
646 MEASURE2_NUMBER = X_MEASURE2_NUMBER,
647 MEASURE3_NUMBER = X_MEASURE3_NUMBER,
648 MEASURE4_NUMBER = X_MEASURE4_NUMBER,
649 MEASURE5_NUMBER = X_MEASURE5_NUMBER,
650 MEASURE6_NUMBER = X_MEASURE6_NUMBER,
651 MEASURE7_NUMBER = X_MEASURE7_NUMBER,
652 MEASURE8_NUMBER = X_MEASURE8_NUMBER,
653 MEASURE9_NUMBER = X_MEASURE9_NUMBER,
654 MEASURE10_NUMBER = X_MEASURE10_NUMBER,
655 MEASURE1_CHAR = X_MEASURE1_CHAR,
656 MEASURE2_CHAR = X_MEASURE2_CHAR,
657 MEASURE3_CHAR = X_MEASURE3_CHAR,
658 MEASURE4_CHAR = X_MEASURE4_CHAR,
659 MEASURE5_CHAR = X_MEASURE5_CHAR,
660 MEASURE6_CHAR = X_MEASURE6_CHAR,
661 MEASURE7_CHAR = X_MEASURE7_CHAR,
662 MEASURE8_CHAR = X_MEASURE8_CHAR,
663 MEASURE9_CHAR = X_MEASURE9_CHAR,
664 MEASURE10_CHAR = X_MEASURE10_CHAR,
665 --PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
666 --REQUEST_ID = X_REQUEST_ID,
667 INSTANCE_ID = X_INSTANCE_ID,
668 SIMULATION_FLAG = X_SIMULATION_FLAG,
669 CUSTOMER_SK = X_CUSTOMER_SK,
670 SALES_CHANNEL_SK = X_SALES_CHANNEL_SK,
671 SALES_REP_SK = X_SALES_REP_SK,
672 PN_INT_HEADER_ID = X_PN_INT_HEADER_ID,
673 SOURCE_ID = X_SOURCE_ID,
674 SOURCE_REF_HDR_ID = X_SOURCE_REF_HDR_ID,
675 CUSTOMER_ID = X_CUSTOMER_ID,
676 --CONTRACT_ID = X_CONTRACT_ID,
677 SALES_REP_ID = X_SALES_REP_ID,
678 PAYMENT_TERMS_ID = X_PAYMENT_TERMS_ID,
679 INVOICE_TO_PARTY_SITE_ID = X_INVOICE_TO_PARTY_SITE_ID,
680 SALES_REP_EMAIL = X_SALES_REP_EMAIL,
681 SALES_CHANNEL_CODE = X_SALES_CHANNEL_CODE,
682 DEAL_EXPIRY_DATE = X_DEAL_EXPIRY_DATE,
683 DEAL_CREATION_DATE = X_DEAL_CREATION_DATE,
684 INVOICE_TO_PARTY_SITE_ADDRESS = X_INVOICE_TO_PARTY_SITE_ADDRES,
685 --PRIORITY = X_PRIORITY,
686 CURRENCY_SHORT_DESC = X_CURRENCY_SHORT_DESC,
687 CURRENCY_LONG_DESC = X_CURRENCY_LONG_DESC,
688 SOURCE_SHORT_DESC = X_SOURCE_SHORT_DESC,
689 SOURCE_LONG_DESC = X_SOURCE_LONG_DESC,
690 SOURCE_REF_HDR_SHORT_DESC = X_SOURCE_REF_HDR_SHORT_DESC,
691 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
692 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
693 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
694 where REQUEST_HEADER_ID = X_REQUEST_HEADER_ID;
695
696 if (sql%notfound) then
697 raise no_data_found;
698 end if;
699
700 update QPR_PN_REQUEST_HDRS_TL set
701 REFERENCE_NAME = X_REFERENCE_NAME,
702 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
703 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
704 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
705 SOURCE_LANG = userenv('LANG')
706 where REQUEST_HEADER_ID = X_REQUEST_HEADER_ID
707 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
708
709 if (sql%notfound) then
710 raise no_data_found;
711 end if;
712 end UPDATE_ROW;
713
714 procedure DELETE_ROW (
715 X_REQUEST_HEADER_ID in NUMBER
716 ) is
717 begin
718 delete from QPR_PN_REQUEST_HDRS_TL
719 where REQUEST_HEADER_ID = X_REQUEST_HEADER_ID;
720
721 if (sql%notfound) then
722 raise no_data_found;
723 end if;
724
725 delete from QPR_PN_REQUEST_HDRS_B
726 where REQUEST_HEADER_ID = X_REQUEST_HEADER_ID;
727
728 if (sql%notfound) then
729 raise no_data_found;
730 end if;
731 end DELETE_ROW;
732
733 procedure ADD_LANGUAGE
734 is
735 begin
736 delete from QPR_PN_REQUEST_HDRS_TL T
737 where not exists
738 (select NULL
739 from QPR_PN_REQUEST_HDRS_B B
740 where B.REQUEST_HEADER_ID = T.REQUEST_HEADER_ID
741 );
742
743 update QPR_PN_REQUEST_HDRS_TL T set (
744 REFERENCE_NAME
745 ) = (select
746 B.REFERENCE_NAME
747 from QPR_PN_REQUEST_HDRS_TL B
748 where B.REQUEST_HEADER_ID = T.REQUEST_HEADER_ID
749 and B.LANGUAGE = T.SOURCE_LANG)
750 where (
751 T.REQUEST_HEADER_ID,
752 T.LANGUAGE
753 ) in (select
754 SUBT.REQUEST_HEADER_ID,
755 SUBT.LANGUAGE
756 from QPR_PN_REQUEST_HDRS_TL SUBB, QPR_PN_REQUEST_HDRS_TL SUBT
757 where SUBB.REQUEST_HEADER_ID = SUBT.REQUEST_HEADER_ID
758 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
759 and (SUBB.REFERENCE_NAME <> SUBT.REFERENCE_NAME
760 ));
761
762 insert into QPR_PN_REQUEST_HDRS_TL (
763 REQUEST_HEADER_ID,
764 REFERENCE_NAME,
765 CREATION_DATE,
766 CREATED_BY,
767 LAST_UPDATE_DATE,
768 LAST_UPDATED_BY,
769 LAST_UPDATE_LOGIN,
770 --PROGRAM_ID,
771 --PROGRAM_LOGIN_ID,
772 --PROGRAM_APPLICATION_ID,
773 --REQUEST_ID,
774 LANGUAGE,
775 SOURCE_LANG
776 ) select /*+ ORDERED */
777 B.REQUEST_HEADER_ID,
778 B.REFERENCE_NAME,
779 B.CREATION_DATE,
780 B.CREATED_BY,
781 B.LAST_UPDATE_DATE,
782 B.LAST_UPDATED_BY,
783 B.LAST_UPDATE_LOGIN,
784 --B.PROGRAM_ID,
785 --B.PROGRAM_LOGIN_ID,
786 --B.PROGRAM_APPLICATION_ID,
787 --B.REQUEST_ID,
788 L.LANGUAGE_CODE,
789 B.SOURCE_LANG
790 from QPR_PN_REQUEST_HDRS_TL B, FND_LANGUAGES L
791 where L.INSTALLED_FLAG in ('I', 'B')
792 and B.LANGUAGE = userenv('LANG')
793 and not exists
794 (select NULL
795 from QPR_PN_REQUEST_HDRS_TL T
796 where T.REQUEST_HEADER_ID = B.REQUEST_HEADER_ID
797 and T.LANGUAGE = L.LANGUAGE_CODE);
798 end ADD_LANGUAGE;
799
800 end QPR_PN_REQUEST_HDRS_PKG;
[Home] [Help]