1 package body QP_PB_INPUT_HEADERS_PKG as
2 /* $Header: QPXUPBTB.pls 120.5 2005/11/28 23:26:51 prarasto noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_PB_INPUT_HEADER_ID in NUMBER,
7 X_DLV_XML_FLAG in VARCHAR2,
8 X_PUB_TEMPLATE_CODE in VARCHAR2,
9 X_DLV_EMAIL_FLAG in VARCHAR2,
10 X_DLV_EMAIL_ADDRESSES in VARCHAR2,
11 X_DLV_PRINTER_FLAG in VARCHAR2,
12 X_DLV_PRINTER_NAME in VARCHAR2,
13 X_PUB_LANGUAGE in VARCHAR2,
14 X_PUB_TERRITORY in VARCHAR2,
15 X_GENERATION_TIME_CODE in VARCHAR2,
16 X_GEN_SCHEDULE_DATE in DATE,
17 X_REQUEST_ID in NUMBER,
18 X_PRICE_BOOK_TYPE_CODE in VARCHAR2,
19 X_PUBLISH_EXISTING_PB_FLAG in VARCHAR2,
20 X_REQUEST_TYPE_CODE in VARCHAR2,
21 X_PRODUCT_ATTRIBUTE in VARCHAR2,
22 X_PRODUCT_ATTR_VALUE in VARCHAR2,
23 X_EFFECTIVE_DATE in DATE,
24 X_ITEM_QUANTITY in NUMBER,
25 X_PRODUCT_CONTEXT in VARCHAR2,
26 X_CUSTOMER_CONTEXT in VARCHAR2,
27 X_CUSTOMER_ATTRIBUTE in VARCHAR2,
28 X_CUSTOMER_ATTR_VALUE in VARCHAR2,
29 X_DLV_XML_SITE_ID in NUMBER,
30 X_CURRENCY_CODE in VARCHAR2,
31 X_PRICE_BOOK_NAME in VARCHAR2,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER,
37 X_ORG_ID in NUMBER,
38 X_OPERATING_UNIT in VARCHAR2,
39 X_CUSTOMER_NAME IN VARCHAR2,
40 X_PRODUCT_NAME IN VARCHAR2,
41 X_GENERATION_TIME in VARCHAR2,
42 X_PRICE_BOOK_TYPE in VARCHAR2,
43 X_PRODUCT_ATTRIBUTE_NAME in VARCHAR2,
44 X_CUSTOMER_ATTRIBUTE_NAME in VARCHAR2,
45 X_LANGUAGE in VARCHAR2,
46 X_SOURCE_LANG in VARCHAR2,
47 X_PUB_OUTPUT_DOCUMENT_TYPE in VARCHAR2,
48 X_PRICING_PERSPECTIVE_CODE in VARCHAR2,
49 X_OVERWRITE_EXISTING_PB_FLAG in VARCHAR2,
50 X_REQUEST_ORIGINATION_CODE in VARCHAR2,
51 X_PUB_TEMPLATE_NAME in VARCHAR2,
52 X_CURRENCY in VARCHAR2,
53 X_LIMIT_PRODUCTS_BY in VARCHAR2,
54 X_PRICE_BASED_ON in VARCHAR2,
55 X_PL_AGR_BSA_ID in NUMBER,
56 X_LIMIT_PRODUCTS_BY_NAME in VARCHAR2,
57 X_PRICE_BASED_ON_NAME in VARCHAR2,
58 X_PL_AGR_BSA_NAME in VARCHAR2,
59 X_PRICING_PERSPECTIVE in VARCHAR2,
60 X_VALIDATION_ERROR_FLAG in VARCHAR2,
61 X_CUST_ACCOUNT_ID in NUMBER
62 ) is
63 cursor C is select ROWID from QP_PB_INPUT_HEADERS_B
64 where PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID
65 ;
66 begin
67 insert into QP_PB_INPUT_HEADERS_B (
68 DLV_XML_FLAG,
69 PUB_TEMPLATE_CODE,
70 DLV_EMAIL_FLAG,
71 DLV_EMAIL_ADDRESSES,
72 DLV_PRINTER_FLAG,
73 DLV_PRINTER_NAME,
74 PUB_LANGUAGE,
75 PUB_TERRITORY,
76 GENERATION_TIME_CODE,
77 GEN_SCHEDULE_DATE,
78 REQUEST_ID,
79 PRICE_BOOK_TYPE_CODE,
80 PUBLISH_EXISTING_PB_FLAG,
81 OVERWRITE_EXISTING_PB_FLAG,
82 REQUEST_ORIGINATION_CODE,
83 REQUEST_TYPE_CODE,
84 PRODUCT_ATTRIBUTE,
85 PRODUCT_ATTR_VALUE,
86 EFFECTIVE_DATE,
87 ITEM_QUANTITY,
88 PRODUCT_CONTEXT,
89 PB_INPUT_HEADER_ID,
90 CUSTOMER_CONTEXT,
91 CUSTOMER_ATTRIBUTE,
92 CUSTOMER_ATTR_VALUE,
93 DLV_XML_SITE_ID,
94 CURRENCY_CODE,
95 ORG_ID,
96 PUB_OUTPUT_DOCUMENT_TYPE,
97 PRICING_PERSPECTIVE_CODE,
98 CREATION_DATE,
99 CREATED_BY,
100 LAST_UPDATE_DATE,
101 LAST_UPDATED_BY,
102 LAST_UPDATE_LOGIN,
103 PL_AGR_BSA_ID,
104 PRICE_BASED_ON,
105 LIMIT_PRODUCTS_BY,
106 VALIDATION_ERROR_FLAG,
107 CUST_ACCOUNT_ID
108 ) values (
109 X_DLV_XML_FLAG,
110 X_PUB_TEMPLATE_CODE,
111 X_DLV_EMAIL_FLAG,
112 X_DLV_EMAIL_ADDRESSES,
113 X_DLV_PRINTER_FLAG,
114 X_DLV_PRINTER_NAME,
115 X_PUB_LANGUAGE,
116 X_PUB_TERRITORY,
117 X_GENERATION_TIME_CODE,
118 X_GEN_SCHEDULE_DATE,
119 X_REQUEST_ID,
120 X_PRICE_BOOK_TYPE_CODE,
121 X_PUBLISH_EXISTING_PB_FLAG,
122 X_OVERWRITE_EXISTING_PB_FLAG,
123 X_REQUEST_ORIGINATION_CODE,
124 X_REQUEST_TYPE_CODE,
125 X_PRODUCT_ATTRIBUTE,
126 X_PRODUCT_ATTR_VALUE,
127 X_EFFECTIVE_DATE,
128 X_ITEM_QUANTITY,
129 X_PRODUCT_CONTEXT,
130 X_PB_INPUT_HEADER_ID,
131 X_CUSTOMER_CONTEXT,
132 X_CUSTOMER_ATTRIBUTE,
133 X_CUSTOMER_ATTR_VALUE,
134 X_DLV_XML_SITE_ID,
135 X_CURRENCY_CODE,
136 X_ORG_ID,
137 X_PUB_OUTPUT_DOCUMENT_TYPE,
138 X_PRICING_PERSPECTIVE_CODE,
139 X_CREATION_DATE,
140 X_CREATED_BY,
141 X_LAST_UPDATE_DATE,
142 X_LAST_UPDATED_BY,
143 X_LAST_UPDATE_LOGIN,
144 X_PL_AGR_BSA_ID,
145 X_PRICE_BASED_ON,
146 X_LIMIT_PRODUCTS_BY,
147 X_VALIDATION_ERROR_FLAG,
148 X_CUST_ACCOUNT_ID
149 );
150
151 insert into QP_PB_INPUT_HEADERS_TL (
152 PRICE_BOOK_NAME,
153 PB_INPUT_HEADER_ID,
154 PL_AGR_BSA_NAME,
155 PUB_TEMPLATE_NAME,
156 LAST_UPDATE_LOGIN,
157 LAST_UPDATED_BY,
158 CREATION_DATE,
159 CREATED_BY,
160 LAST_UPDATE_DATE,
161 LANGUAGE,
162 SOURCE_LANG
163 ) select
164 X_PRICE_BOOK_NAME,
165 X_PB_INPUT_HEADER_ID,
166 X_PL_AGR_BSA_NAME,
167 X_PUB_TEMPLATE_NAME,
168 X_LAST_UPDATE_LOGIN,
169 X_LAST_UPDATED_BY,
170 X_CREATION_DATE,
171 X_CREATED_BY,
172 X_LAST_UPDATE_DATE,
173 L.LANGUAGE_CODE,
174 userenv('LANG')
175 from FND_LANGUAGES L
176 where L.INSTALLED_FLAG in ('I', 'B')
177 and not exists
178 (select NULL
179 from QP_PB_INPUT_HEADERS_TL T
180 where T.PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID
181 and T.LANGUAGE = L.LANGUAGE_CODE);
182
183 open c;
184 fetch c into X_ROWID;
185 if (c%notfound) then
186 close c;
187 raise no_data_found;
188 end if;
189 close c;
190
191 end INSERT_ROW;
192
193
194
195
196 procedure LOCK_ROW (
197 X_PB_INPUT_HEADER_ID in NUMBER,
198 X_DLV_XML_FLAG in VARCHAR2,
199 X_PUB_TEMPLATE_CODE in VARCHAR2,
200 X_DLV_EMAIL_FLAG in VARCHAR2,
201 X_DLV_EMAIL_ADDRESSES in VARCHAR2,
202 X_DLV_PRINTER_FLAG in VARCHAR2,
203 X_DLV_PRINTER_NAME in VARCHAR2,
204 X_PUB_LANGUAGE in VARCHAR2,
205 X_PUB_TERRITORY in VARCHAR2,
206 X_GENERATION_TIME_CODE in VARCHAR2,
207 X_GEN_SCHEDULE_DATE in DATE,
208 X_REQUEST_ID in NUMBER,
209 X_PRICE_BOOK_TYPE_CODE in VARCHAR2,
210 X_PUBLISH_EXISTING_PB_FLAG in VARCHAR2,
211 X_REQUEST_TYPE_CODE in VARCHAR2,
212 X_PRODUCT_ATTRIBUTE in VARCHAR2,
213 X_PRODUCT_ATTR_VALUE in VARCHAR2,
214 X_EFFECTIVE_DATE in DATE,
215 X_ITEM_QUANTITY in NUMBER,
216 X_PRODUCT_CONTEXT in VARCHAR2,
217 X_CUSTOMER_CONTEXT in VARCHAR2,
218 X_CUSTOMER_ATTRIBUTE in VARCHAR2,
219 X_CUSTOMER_ATTR_VALUE in VARCHAR2,
220 X_DLV_XML_SITE_ID in NUMBER,
221 X_CURRENCY_CODE in VARCHAR2,
222 X_PRICE_BOOK_NAME in VARCHAR2,
223 X_ORG_ID in NUMBER,
224 X_OPERATING_UNIT in VARCHAR2,
225 X_CUSTOMER_NAME IN VARCHAR2,
226 X_PRODUCT_NAME IN VARCHAR2,
227 X_GENERATION_TIME in VARCHAR2,
228 X_PRICE_BOOK_TYPE in VARCHAR2,
229 X_PRODUCT_ATTRIBUTE_NAME in VARCHAR2,
230 X_CUSTOMER_ATTRIBUTE_NAME in VARCHAR2,
231 X_LANGUAGE in VARCHAR2,
232 X_SOURCE_LANG in VARCHAR2,
233 X_PUB_OUTPUT_DOCUMENT_TYPE in VARCHAR2,
234 X_PRICING_PERSPECTIVE_CODE in VARCHAR2,
235 X_OVERWRITE_EXISTING_PB_FLAG in VARCHAR2,
236 X_REQUEST_ORIGINATION_CODE in VARCHAR2,
237 X_PUB_TEMPLATE_NAME in VARCHAR2,
238 X_CURRENCY in VARCHAR2,
239 X_LIMIT_PRODUCTS_BY in VARCHAR2,
240 X_PRICE_BASED_ON in VARCHAR2,
241 X_PL_AGR_BSA_ID in NUMBER,
242 X_LIMIT_PRODUCTS_BY_NAME in VARCHAR2,
243 X_PRICE_BASED_ON_NAME in VARCHAR2,
244 X_PL_AGR_BSA_NAME in VARCHAR2,
245 X_PRICING_PERSPECTIVE in VARCHAR2,
246 X_VALIDATION_ERROR_FLAG in VARCHAR2,
247 X_CUST_ACCOUNT_ID in NUMBER
248 ) is
249 cursor c is select
250 DLV_XML_FLAG,
251 PUB_TEMPLATE_CODE,
252 DLV_EMAIL_FLAG,
253 DLV_EMAIL_ADDRESSES,
254 DLV_PRINTER_FLAG,
255 DLV_PRINTER_NAME,
256 PUB_LANGUAGE,
257 PUB_TERRITORY,
258 GENERATION_TIME_CODE,
259 GEN_SCHEDULE_DATE,
260 REQUEST_ID,
261 PRICE_BOOK_TYPE_CODE,
262 PUBLISH_EXISTING_PB_FLAG,
263 OVERWRITE_EXISTING_PB_FLAG,
264 REQUEST_ORIGINATION_CODE,
265 REQUEST_TYPE_CODE,
266 PRODUCT_ATTRIBUTE,
267 PRODUCT_ATTR_VALUE,
268 EFFECTIVE_DATE,
269 ITEM_QUANTITY,
270 PRODUCT_CONTEXT,
271 CUSTOMER_CONTEXT,
272 CUSTOMER_ATTRIBUTE,
273 CUSTOMER_ATTR_VALUE,
274 DLV_XML_SITE_ID,
275 CURRENCY_CODE,
276 ORG_ID,
277 PUB_OUTPUT_DOCUMENT_TYPE,
278 PRICING_PERSPECTIVE_CODE,
279 VALIDATION_ERROR_FLAG,
280 CUST_ACCOUNT_ID
281 from QP_PB_INPUT_HEADERS_B
282 where PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID
283 for update of PB_INPUT_HEADER_ID nowait;
284 recinfo c%rowtype;
285
286 cursor c1 is select
287 PRICE_BOOK_NAME,
288 PL_AGR_BSA_NAME,
289 PUB_TEMPLATE_NAME,
290 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
291 from QP_PB_INPUT_HEADERS_TL
292 where PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID
293 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
294 for update of PB_INPUT_HEADER_ID nowait;
295 begin
296 open c;
297 fetch c into recinfo;
298 if (c%notfound) then
299 close c;
300 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
301 app_exception.raise_exception;
302 end if;
303 close c;
304 if (
305 ((recinfo.DLV_XML_FLAG = X_DLV_XML_FLAG)
306 OR ((recinfo.DLV_XML_FLAG is null) AND (X_DLV_XML_FLAG is null)))
307 AND ((recinfo.PUB_TEMPLATE_CODE = X_PUB_TEMPLATE_CODE)
308 OR ((recinfo.PUB_TEMPLATE_CODE is null) AND (X_PUB_TEMPLATE_CODE is null)))
309 AND ((recinfo.DLV_EMAIL_FLAG = X_DLV_EMAIL_FLAG)
310 OR ((recinfo.DLV_EMAIL_FLAG is null) AND (X_DLV_EMAIL_FLAG is null)))
311 AND ((recinfo.DLV_EMAIL_ADDRESSES= X_DLV_EMAIL_ADDRESSES)
312 OR ((recinfo.DLV_EMAIL_ADDRESSES is null) AND (X_DLV_EMAIL_ADDRESSES is null)))
313 AND ((recinfo.DLV_PRINTER_FLAG = X_DLV_PRINTER_FLAG)
314 OR ((recinfo.DLV_PRINTER_FLAG is null) AND (X_DLV_PRINTER_FLAG is null)))
315 AND ((recinfo.DLV_PRINTER_NAME = X_DLV_PRINTER_NAME)
316 OR ((recinfo.DLV_PRINTER_NAME is null) AND (X_DLV_PRINTER_NAME is null)))
317 AND ((recinfo.PUB_LANGUAGE = X_PUB_LANGUAGE)
318 OR ((recinfo.PUB_LANGUAGE is null) AND (X_PUB_LANGUAGE is null)))
319 AND ((recinfo.PUB_TERRITORY = X_PUB_TERRITORY)
320 OR ((recinfo.PUB_TERRITORY is null) AND (X_PUB_TERRITORY is null)))
321 AND ((recinfo.GENERATION_TIME_CODE = X_GENERATION_TIME_CODE)
322 OR ((recinfo.GENERATION_TIME_CODE is null) AND (X_GENERATION_TIME_CODE is null)))
323 AND ((recinfo.GEN_SCHEDULE_DATE = X_GEN_SCHEDULE_DATE)
324 OR ((recinfo.GEN_SCHEDULE_DATE is null) AND (X_GEN_SCHEDULE_DATE is null)))
325 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
326 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
327 AND (recinfo.PRICE_BOOK_TYPE_CODE = X_PRICE_BOOK_TYPE_CODE)
328 AND ((recinfo.PUBLISH_EXISTING_PB_FLAG = X_PUBLISH_EXISTING_PB_FLAG)
329 OR ((recinfo.PUBLISH_EXISTING_PB_FLAG is null) AND (X_PUBLISH_EXISTING_PB_FLAG is null)))
330 AND ((recinfo.OVERWRITE_EXISTING_PB_FLAG = X_OVERWRITE_EXISTING_PB_FLAG)
331 OR ((recinfo.OVERWRITE_EXISTING_PB_FLAG is null) AND (X_OVERWRITE_EXISTING_PB_FLAG is null)))
332 AND ((recinfo.REQUEST_ORIGINATION_CODE = X_REQUEST_ORIGINATION_CODE )
333 OR ((recinfo.REQUEST_ORIGINATION_CODE is null) AND (X_REQUEST_ORIGINATION_CODE is null)))
334 AND (recinfo.REQUEST_TYPE_CODE = X_REQUEST_TYPE_CODE)
335 AND ((recinfo.PRODUCT_ATTRIBUTE = X_PRODUCT_ATTRIBUTE)
336 OR ((recinfo.PRODUCT_ATTRIBUTE is null) AND (X_PRODUCT_ATTRIBUTE is null)))
337 AND ((recinfo.PRODUCT_ATTR_VALUE = X_PRODUCT_ATTR_VALUE)
338 OR ((recinfo.PRODUCT_ATTR_VALUE is null) AND (X_PRODUCT_ATTR_VALUE is null)))
339 AND (recinfo.EFFECTIVE_DATE = X_EFFECTIVE_DATE)
340 AND (recinfo.ITEM_QUANTITY = X_ITEM_QUANTITY)
341 AND ((recinfo.ORG_ID = X_ORG_ID)
342 OR ((recinfo.ORG_ID is null) AND (X_ORG_ID is null)))
343 AND ((recinfo.PRODUCT_CONTEXT = X_PRODUCT_CONTEXT)
344 OR ((recinfo.PRODUCT_CONTEXT is null) AND (X_PRODUCT_CONTEXT is null)))
345 AND (recinfo.CUSTOMER_CONTEXT = X_CUSTOMER_CONTEXT)
346 AND (recinfo.CUSTOMER_ATTRIBUTE = X_CUSTOMER_ATTRIBUTE)
347 AND ((recinfo.CUSTOMER_ATTR_VALUE = X_CUSTOMER_ATTR_VALUE)
348 OR ((recinfo.CUSTOMER_ATTR_VALUE is null) AND (X_CUSTOMER_ATTR_VALUE is null)))
349 AND ((recinfo.DLV_XML_SITE_ID = X_DLV_XML_SITE_ID)
350 OR ((recinfo.DLV_XML_SITE_ID is null) AND (X_DLV_XML_SITE_ID is null)))
351 AND ((recinfo.CURRENCY_CODE = X_CURRENCY_CODE)
352 OR ((recinfo.CURRENCY_CODE is null) AND (X_CURRENCY_CODE is null)))
353 AND ((recinfo.PRICING_PERSPECTIVE_CODE = X_PRICING_PERSPECTIVE_CODE)
354 OR ((recinfo.PRICING_PERSPECTIVE_CODE is null) AND (X_PRICING_PERSPECTIVE_CODE is null)))
355 AND ((recinfo.PUB_OUTPUT_DOCUMENT_TYPE = X_PUB_OUTPUT_DOCUMENT_TYPE)
356 OR ((recinfo.PUB_OUTPUT_DOCUMENT_TYPE is null) AND (X_PUB_OUTPUT_DOCUMENT_TYPE is null)))
357 AND ((recinfo.VALIDATION_ERROR_FLAG = X_VALIDATION_ERROR_FLAG)
358 OR ((recinfo.VALIDATION_ERROR_FLAG is null) AND (X_VALIDATION_ERROR_FLAG is null)))
359 AND ((recinfo.CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID)
360 OR ((recinfo.CUST_ACCOUNT_ID is null) AND (X_CUST_ACCOUNT_ID is null)))
361 ) then
362 null;
363 else
364 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
365 app_exception.raise_exception;
366 end if;
367
368 for tlinfo in c1 loop
369 if (tlinfo.BASELANG = 'Y') then
370 if (tlinfo.PRICE_BOOK_NAME = X_PRICE_BOOK_NAME) then
371 null;
372 else
373 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
374 app_exception.raise_exception;
375 end if;
376 end if;
377 end loop;
378 return;
379 end LOCK_ROW;
380
381
382
383 procedure UPDATE_ROW (
384 X_PB_INPUT_HEADER_ID in NUMBER,
385 X_DLV_XML_FLAG in VARCHAR2,
389 X_DLV_PRINTER_FLAG in VARCHAR2,
386 X_PUB_TEMPLATE_CODE in VARCHAR2,
387 X_DLV_EMAIL_FLAG in VARCHAR2,
388 X_DLV_EMAIL_ADDRESSES in VARCHAR2,
390 X_DLV_PRINTER_NAME in VARCHAR2,
391 X_PUB_LANGUAGE in VARCHAR2,
392 X_PUB_TERRITORY in VARCHAR2,
393 X_GENERATION_TIME_CODE in VARCHAR2,
394 X_GEN_SCHEDULE_DATE in DATE,
395 X_REQUEST_ID in NUMBER,
396 X_PRICE_BOOK_TYPE_CODE in VARCHAR2,
397 X_PUBLISH_EXISTING_PB_FLAG in VARCHAR2,
398 X_REQUEST_TYPE_CODE in VARCHAR2,
399 X_PRODUCT_ATTRIBUTE in VARCHAR2,
400 X_PRODUCT_ATTR_VALUE in VARCHAR2,
401 X_EFFECTIVE_DATE in DATE,
402 X_ITEM_QUANTITY in NUMBER,
403 X_PRODUCT_CONTEXT in VARCHAR2,
404 X_CUSTOMER_CONTEXT in VARCHAR2,
405 X_CUSTOMER_ATTRIBUTE in VARCHAR2,
406 X_CUSTOMER_ATTR_VALUE in VARCHAR2,
407 X_DLV_XML_SITE_ID in NUMBER,
408 X_CURRENCY_CODE in VARCHAR2,
409 X_PRICE_BOOK_NAME in VARCHAR2,
410 X_LAST_UPDATE_DATE in DATE,
411 X_LAST_UPDATED_BY in NUMBER,
412 X_LAST_UPDATE_LOGIN in NUMBER,
413 X_ORG_ID in NUMBER,
414 X_OPERATING_UNIT in VARCHAR2,
415 X_CUSTOMER_NAME IN VARCHAR2,
416 X_PRODUCT_NAME IN VARCHAR2,
417 X_GENERATION_TIME in VARCHAR2,
418 X_PRICE_BOOK_TYPE in VARCHAR2,
419 X_PRODUCT_ATTRIBUTE_NAME in VARCHAR2,
420 X_CUSTOMER_ATTRIBUTE_NAME in VARCHAR2,
421 X_LANGUAGE in VARCHAR2,
422 X_SOURCE_LANG in VARCHAR2,
423 X_PUB_OUTPUT_DOCUMENT_TYPE in VARCHAR2,
424 X_PRICING_PERSPECTIVE_CODE in VARCHAR2,
425 X_OVERWRITE_EXISTING_PB_FLAG in VARCHAR2,
426 X_REQUEST_ORIGINATION_CODE in VARCHAR2,
427 X_PUB_TEMPLATE_NAME in VARCHAR2,
428 X_CURRENCY in VARCHAR2,
429 X_LIMIT_PRODUCTS_BY in VARCHAR2,
430 X_PRICE_BASED_ON in VARCHAR2,
431 X_PL_AGR_BSA_ID in NUMBER,
432 X_LIMIT_PRODUCTS_BY_NAME in VARCHAR2,
433 X_PRICE_BASED_ON_NAME in VARCHAR2,
434 X_PL_AGR_BSA_NAME in VARCHAR2,
435 X_PRICING_PERSPECTIVE in VARCHAR2,
436 X_VALIDATION_ERROR_FLAG in VARCHAR2,
437 X_CUST_ACCOUNT_ID in NUMBER
438 ) is
439 begin
440 update QP_PB_INPUT_HEADERS_B set
441 DLV_XML_FLAG = X_DLV_XML_FLAG,
442 PUB_TEMPLATE_CODE = X_PUB_TEMPLATE_CODE ,
443 DLV_EMAIL_FLAG = X_DLV_EMAIL_FLAG ,
444 DLV_EMAIL_ADDRESSES = X_DLV_EMAIL_ADDRESSES ,
445 DLV_PRINTER_FLAG = X_DLV_PRINTER_FLAG ,
446 DLV_PRINTER_NAME = X_DLV_PRINTER_NAME ,
447 PUB_LANGUAGE = X_PUB_LANGUAGE ,
448 PUB_TERRITORY = X_PUB_TERRITORY ,
449 GENERATION_TIME_CODE = X_GENERATION_TIME_CODE,
450 GEN_SCHEDULE_DATE = X_GEN_SCHEDULE_DATE,
451 REQUEST_ID = X_REQUEST_ID,
452 PRICE_BOOK_TYPE_CODE = X_PRICE_BOOK_TYPE_CODE,
453 PUBLISH_EXISTING_PB_FLAG = X_PUBLISH_EXISTING_PB_FLAG,
454 OVERWRITE_EXISTING_PB_FLAG = X_OVERWRITE_EXISTING_PB_FLAG,
455 REQUEST_ORIGINATION_CODE = X_REQUEST_ORIGINATION_CODE,
456 REQUEST_TYPE_CODE = X_REQUEST_TYPE_CODE,
457 LIMIT_PRODUCTS_BY = X_LIMIT_PRODUCTS_BY,
458 PRODUCT_ATTRIBUTE = X_PRODUCT_ATTRIBUTE,
459 PRODUCT_ATTR_VALUE = X_PRODUCT_ATTR_VALUE,
460 PRICE_BASED_ON = X_PRICE_BASED_ON,
461 PL_AGR_BSA_ID = X_PL_AGR_BSA_ID,
462 EFFECTIVE_DATE = X_EFFECTIVE_DATE,
463 ITEM_QUANTITY = X_ITEM_QUANTITY,
464 PRODUCT_CONTEXT = X_PRODUCT_CONTEXT,
465 CUSTOMER_CONTEXT = X_CUSTOMER_CONTEXT,
466 CUSTOMER_ATTRIBUTE = X_CUSTOMER_ATTRIBUTE,
467 CUSTOMER_ATTR_VALUE = X_CUSTOMER_ATTR_VALUE,
468 DLV_XML_SITE_ID = X_DLV_XML_SITE_ID,
469 CURRENCY_CODE = X_CURRENCY_CODE,
470 ORG_ID = X_ORG_ID,
471 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
472 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
473 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
474 PUB_OUTPUT_DOCUMENT_TYPE = X_PUB_OUTPUT_DOCUMENT_TYPE ,
475 PRICING_PERSPECTIVE_CODE = X_PRICING_PERSPECTIVE_CODE,
476 VALIDATION_ERROR_FLAG = X_VALIDATION_ERROR_FLAG,
477 CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID
478 where PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID;
479
480 if (sql%notfound) then
481 raise no_data_found;
482 end if;
483
484 update QP_PB_INPUT_HEADERS_TL set
485 PRICE_BOOK_NAME = X_PRICE_BOOK_NAME,
486 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
487 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
488 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
489 PL_AGR_BSA_NAME = X_PL_AGR_BSA_NAME,
490 PUB_TEMPLATE_NAME = X_PUB_TEMPLATE_NAME,
491 SOURCE_LANG = userenv('LANG')
492 where PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID
493 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
494
495 if (sql%notfound) then
496 raise no_data_found;
497 end if;
498 end UPDATE_ROW;
499
500 procedure DELETE_ROW (
501 X_PB_INPUT_HEADER_ID in NUMBER
502 ) is
503 begin
504 delete from QP_PB_INPUT_HEADERS_TL
505 where PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID;
506
507 if (sql%notfound) then
508 raise no_data_found;
509 end if;
510
511 delete from QP_PB_INPUT_HEADERS_B
512 where PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID;
513
514 if (sql%notfound) then
515 raise no_data_found;
516 end if;
517 end DELETE_ROW;
518
519 procedure ADD_LANGUAGE
520 is
521 begin
522 delete from QP_PB_INPUT_HEADERS_TL T
523 where not exists
524 (select NULL
525 from QP_PB_INPUT_HEADERS_B B
526 where B.PB_INPUT_HEADER_ID = T.PB_INPUT_HEADER_ID
527 );
528
529 update QP_PB_INPUT_HEADERS_TL T set (
530 PRICE_BOOK_NAME,
531 PL_AGR_BSA_NAME,
532 PUB_TEMPLATE_NAME
533 ) = (select
534 B.PRICE_BOOK_NAME,
535 B.PL_AGR_BSA_NAME,
536 B.PUB_TEMPLATE_NAME
537 from QP_PB_INPUT_HEADERS_TL B
538 where B.PB_INPUT_HEADER_ID = T.PB_INPUT_HEADER_ID
539 and B.LANGUAGE = T.SOURCE_LANG)
543 ) in (select
540 where (
541 T.PB_INPUT_HEADER_ID,
542 T.LANGUAGE
544 SUBT.PB_INPUT_HEADER_ID,
545 SUBT.LANGUAGE
546 from QP_PB_INPUT_HEADERS_TL SUBB, QP_PB_INPUT_HEADERS_TL SUBT
547 where SUBB.PB_INPUT_HEADER_ID = SUBT.PB_INPUT_HEADER_ID
548 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
549 and (SUBB.PRICE_BOOK_NAME <> SUBT.PRICE_BOOK_NAME
550 or SUBB.PL_AGR_BSA_NAME <> SUBT.PL_AGR_BSA_NAME
551 or (SUBB.PL_AGR_BSA_NAME is null and SUBT.PL_AGR_BSA_NAME is not null)
552 or (SUBB.PL_AGR_BSA_NAME is not null and SUBT.PL_AGR_BSA_NAME is null)
553 ));
554
555 insert into QP_PB_INPUT_HEADERS_TL (
556 PRICE_BOOK_NAME,
557 PB_INPUT_HEADER_ID,
558 LAST_UPDATE_LOGIN,
559 LAST_UPDATED_BY,
560 PL_AGR_BSA_NAME,
561 PUB_TEMPLATE_NAME,
562 CREATION_DATE,
563 CREATED_BY,
564 LAST_UPDATE_DATE,
565 LANGUAGE,
566 SOURCE_LANG
567 ) select /*+ ORDERED */
568 B.PRICE_BOOK_NAME,
569 B.PB_INPUT_HEADER_ID,
570 B.LAST_UPDATE_LOGIN,
571 B.LAST_UPDATED_BY,
572 B.PL_AGR_BSA_NAME,
573 B.PUB_TEMPLATE_NAME,
574 B.CREATION_DATE,
575 B.CREATED_BY,
576 B.LAST_UPDATE_DATE,
577 L.LANGUAGE_CODE,
578 B.SOURCE_LANG
579 from QP_PB_INPUT_HEADERS_TL B, FND_LANGUAGES L
580 where L.INSTALLED_FLAG in ('I', 'B')
581 and B.LANGUAGE = userenv('LANG')
582 and not exists
583 (select NULL
584 from QP_PB_INPUT_HEADERS_TL T
585 where T.PB_INPUT_HEADER_ID = B.PB_INPUT_HEADER_ID
586 and T.LANGUAGE = L.LANGUAGE_CODE);
587 end ADD_LANGUAGE;
588
589 end QP_PB_INPUT_HEADERS_PKG;
[Home] [Help]