[Home] [Help]
PACKAGE BODY: APPS.PO_REQ_TEMPLATE_SV
Source
1 PACKAGE BODY po_req_template_sv AS
2 /* $Header: POXRQT1B.pls 120.3.12020000.2 2013/02/11 01:34:51 vegajula ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: insert_line
7
8 ===========================================================================*/
9 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
10
11 c_log_head CONSTANT VARCHAR2(35) := 'po.plsql.po_req_template_sv.';
12 PROCEDURE insert_line (x_rowid IN OUT NOCOPY VARCHAR2,
13 x_express_name IN VARCHAR2,
14 x_sequence_num IN NUMBER,
15 x_creation_date IN DATE,
16 x_last_update_date IN DATE,
17 x_po_header_id IN NUMBER,
18 x_po_line_id IN NUMBER,
19 x_created_by IN NUMBER,
20 x_last_update_login IN NUMBER,
21 x_item_id IN NUMBER,
22 x_line_type_id IN NUMBER,
23 x_item_revision IN VARCHAR2,
24 x_category_id IN NUMBER,
25 x_unit_meas_lookup_code IN VARCHAR2,
26 x_suggested_quantity IN NUMBER, --KitSupport FPJ
27 x_unit_price IN NUMBER,
28 x_suggested_vendor_id IN NUMBER,
29 x_suggested_vendor_site_id IN NUMBER,
30 x_suggested_vendor_contact_id IN NUMBER,
31 x_suggested_vendor_prod_code IN VARCHAR2,
32 x_suggested_buyer_id IN NUMBER,
33 x_rfq_required_flag IN VARCHAR2,
34 x_vendor_source_context IN VARCHAR2,
35 x_source_type_code IN VARCHAR2,
36 x_source_organization_id IN NUMBER,
37 x_source_subinventory IN VARCHAR2,
38 x_item_description IN VARCHAR2,
39 x_attribute_category IN VARCHAR2,
40 x_attribute1 IN VARCHAR2,
41 x_attribute2 IN VARCHAR2,
42 x_attribute3 IN VARCHAR2,
43 x_attribute4 IN VARCHAR2,
44 x_attribute5 IN VARCHAR2,
45 x_attribute6 IN VARCHAR2,
46 x_attribute7 IN VARCHAR2,
47 x_attribute8 IN VARCHAR2,
48 x_attribute9 IN VARCHAR2,
49 x_attribute10 IN VARCHAR2,
50 x_attribute11 IN VARCHAR2,
51 x_attribute12 IN VARCHAR2,
52 x_attribute13 IN VARCHAR2,
53 x_attribute14 IN VARCHAR2,
54 x_attribute15 IN VARCHAR2,
55 x_amount IN NUMBER, -- <SERVICES FPJ>
56 x_negotiated_by_preparer_flag IN VARCHAR2, --<DBI FPJ>
57 p_org_id IN NUMBER DEFAULT NULL -- <R12 MOAC>
58 )
59 IS
60 x_progress VARCHAR2(3) := '';
61
62 l_ip_category_id PO_LINES_ALL.ip_category_id%TYPE; -- <Unified Catalog R12>
63
64 CURSOR C IS
65 SELECT rowid
66 FROM PO_REQEXPRESS_LINES
67 WHERE express_name = x_express_name
68 AND sequence_num = x_sequence_num;
69 BEGIN
70
71 IF (x_express_name IS NOT NULL AND
72 x_sequence_num IS NOT NULL AND
73 x_created_by IS NOT NULL) THEN
74
75 x_progress := '010';
76 -- <Unified Catalog R12 Start>
77 -- Default the IP_CATEGORY_ID
78 PO_ATTRIBUTE_VALUES_PVT.get_ip_category_id
79 (
80 p_po_category_id => x_category_id
81 , x_ip_category_id => l_ip_category_id -- OUT
82 );
83 -- <Unified Catalog R12 End>
84
85 x_progress := '015';
86 --dbms_output.put_line('Before insert');
87
88 -- <SERVICES FPJ>
89 -- Added Amount to the INSERT statement
90 INSERT INTO PO_REQEXPRESS_LINES (
91 express_name,
92 sequence_num,
93 last_update_date,
94 last_updated_by,
95 creation_date,
96 po_header_id,
97 po_line_id,
98 created_by,
99 last_update_login,
100 item_id,
101 line_type_id,
102 item_revision,
103 category_id,
104 unit_meas_lookup_code,
105 suggested_quantity, --KitSupport FPJ
106 unit_price,
107 suggested_vendor_id,
108 suggested_vendor_site_id,
109 suggested_vendor_contact_id,
110 suggested_vendor_product_code,
111 suggested_buyer_id,
112 rfq_required_flag,
113 vendor_source_context,
114 source_type_code,
115 source_organization_id,
116 source_subinventory,
117 item_description,
118 attribute_category,
119 attribute1,
120 attribute2,
121 attribute3,
122 attribute4,
123 attribute5,
124 attribute6,
125 attribute7,
126 attribute8,
127 attribute9,
128 attribute10,
129 attribute11,
130 attribute12,
131 attribute13,
132 attribute14,
133 attribute15,
134 amount,
135 negotiated_by_preparer_flag,--<DBI FPJ>
136 Org_Id, -- <R12 MOAC>
137 ip_category_id -- <Unified Catalog R12>
138 )
139 VALUES (
140 x_express_name,
141 x_sequence_num,
142 x_last_update_date,
143 x_created_by,
144 x_creation_date,
145 x_po_header_id,
146 x_po_line_id,
147 x_created_by,
148 x_last_update_login,
149 x_item_id,
150 x_line_type_id,
151 x_item_revision,
152 x_category_id,
153 x_unit_meas_lookup_code,
154 x_suggested_quantity, --KitSupport FPJ
155 x_unit_price,
156 x_suggested_vendor_id,
157 x_suggested_vendor_site_id,
158 x_suggested_vendor_contact_id,
159 x_suggested_vendor_prod_code,
160 x_suggested_buyer_id,
161 x_rfq_required_flag,
162 x_vendor_source_context,
163 x_source_type_code,
164 x_source_organization_id,
165 x_source_subinventory,
166 x_item_description,
167 x_attribute_category,
168 x_attribute1,
169 x_attribute2,
170 x_attribute3,
171 x_attribute4,
172 x_attribute5,
173 x_attribute6,
174 x_attribute7,
175 x_attribute8,
176 x_attribute9,
177 x_attribute10,
178 x_attribute11,
179 x_attribute12,
180 x_attribute13,
181 x_attribute14,
182 x_attribute15,
183 x_amount,
184 x_negotiated_by_preparer_flag, --<DBI FPJ>
185 p_org_id, -- <R12 MOAC>
186 l_ip_category_id -- <Unified Catalog R12>
187 );
188
189 --dbms_output.put_line('After insert');
190 x_progress := '020';
191
192 OPEN C;
193 FETCH C INTO x_rowid;
194 if (C%NOTFOUND) then
195 CLOSE C;
196 Raise NO_DATA_FOUND;
197 end if;
198 CLOSE C;
199
200 -- <Unified Catalog R12 Start>
201 -- Create default Attr and TLP rows for this PO Line
202 PO_ATTRIBUTE_VALUES_PVT.create_default_attributes
203 (
204 p_doc_type => 'REQ_TEMPLATE',
205 p_po_line_id => NULL,
206 p_req_template_name => x_express_name,
207 p_req_template_line_num => x_sequence_num,
208 p_ip_category_id => l_ip_category_id,
209 p_inventory_item_id => x_item_id,
210 p_org_id => p_org_id,
211 p_description => x_item_description
212 );
213 -- <Unified Catalog R12 End>
214
215 END IF;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 --dbms_output.put_line('Exception in insert_lines');
220 PO_MESSAGE_S.SQL_ERROR('INSERT_HEADER', x_progress, sqlcode);
221 RAISE;
222 END;
223
224 /*===========================================================================
225
226 PROCEDURE NAME: update_line
227
228 ===========================================================================*/
229
230 PROCEDURE update_line (x_rowid VARCHAR2,
231 x_express_name VARCHAR2,
232 x_sequence_num NUMBER,
233 x_last_updated_by NUMBER,
234 x_last_update_date DATE,
235 x_po_header_id NUMBER,
236 x_po_line_id NUMBER,
237 x_last_update_login NUMBER,
238 x_item_id NUMBER,
239 x_line_type_id NUMBER,
240 x_item_revision VARCHAR2,
241 x_category_id NUMBER,
242 x_unit_meas_lookup_code VARCHAR2,
243 x_suggested_quantity NUMBER, --KitSupport FPJ
244 x_unit_price NUMBER,
245 x_suggested_vendor_id NUMBER,
246 x_suggested_vendor_site_id NUMBER,
247 x_suggested_vendor_contact_id NUMBER,
248 x_suggested_vendor_prod_code VARCHAR2,
249 x_suggested_buyer_id NUMBER,
250 x_rfq_required_flag VARCHAR2,
251 x_vendor_source_context VARCHAR2,
252 x_source_type_code VARCHAR2,
253 x_source_organization_id NUMBER,
254 x_source_subinventory VARCHAR2,
255 x_item_description VARCHAR2,
256 x_attribute_category VARCHAR2,
257 x_attribute1 VARCHAR2,
258 x_attribute2 VARCHAR2,
259 x_attribute3 VARCHAR2,
260 x_attribute4 VARCHAR2,
261 x_attribute5 VARCHAR2,
262 x_attribute6 VARCHAR2,
263 x_attribute7 VARCHAR2,
264 x_attribute8 VARCHAR2,
265 x_attribute9 VARCHAR2,
266 x_attribute10 VARCHAR2,
267 x_attribute11 VARCHAR2,
268 x_attribute12 VARCHAR2,
269 x_attribute13 VARCHAR2,
270 x_attribute14 VARCHAR2,
271 x_attribute15 VARCHAR2,
272 x_amount NUMBER, -- <SERVICES FPJ>
273 x_negotiated_by_preparer_flag VARCHAR2) --<DBI FPJ>
274 IS
275 x_progress VARCHAR2(3) := '';
276 l_ip_category_id PO_LINES_ALL.ip_category_id%TYPE; -- <Unified Catalog R12>
277 BEGIN
278 x_progress := '010';
279 --dbms_output.put_line('Before Update');
280
281 -- <Unified Catalog R12 Start>
282 -- Get the new IP_CATEGORY_ID
283 PO_ATTRIBUTE_VALUES_PVT.get_ip_category_id
284 (
285 p_po_category_id => x_category_id
286 , x_ip_category_id => l_ip_category_id -- OUT
287 );
288 -- <Unified Catalog R12 End>
289
290 -- <SERVICES FPJ>
291 -- Added Amount to the UPDATE statement
292 UPDATE PO_REQEXPRESS_LINES
293 SET express_name = x_express_name,
294 sequence_num = x_sequence_num,
295 last_update_date = x_last_update_date,
296 last_updated_by = x_last_updated_by,
297 po_header_id = x_po_header_id,
298 po_line_id = x_po_line_id,
299 last_update_login = x_last_update_login,
300 item_id = x_item_id,
301 line_type_id = x_line_type_id,
302 item_revision = x_item_revision,
303 category_id = x_category_id,
304 unit_meas_lookup_code = x_unit_meas_lookup_code,
305 suggested_quantity = x_suggested_quantity, --KitSupport FPJ
306 unit_price = x_unit_price,
307 suggested_vendor_id = x_suggested_vendor_id,
308 suggested_vendor_site_id = x_suggested_vendor_site_id,
309 suggested_vendor_contact_id = x_suggested_vendor_contact_id,
310 suggested_vendor_product_code = x_suggested_vendor_prod_code,
311 suggested_buyer_id = x_suggested_buyer_id,
312 rfq_required_flag = x_rfq_required_flag,
313 vendor_source_context = x_vendor_source_context,
314 source_type_code = x_source_type_code,
315 source_organization_id = x_source_organization_id,
316 source_subinventory = x_source_subinventory,
317 item_description = x_item_description,
318 attribute_category = x_attribute_category,
319 attribute1 = x_attribute1,
320 attribute2 = x_attribute2,
321 attribute3 = x_attribute3,
322 attribute4 = x_attribute4,
323 attribute5 = x_attribute5,
324 attribute6 = x_attribute6,
325 attribute7 = x_attribute7,
326 attribute8 = x_attribute8,
327 attribute9 = x_attribute9,
328 attribute10 = x_attribute10,
329 attribute11 = x_attribute11,
330 attribute12 = x_attribute12,
331 attribute13 = x_attribute13,
332 attribute14 = x_attribute14,
333 attribute15 = x_attribute15,
334 amount = x_amount,
335 negotiated_by_preparer_flag = x_negotiated_by_preparer_flag, --<DBI FPJ>
336 ip_category_id = l_ip_category_id --<Unified Catalog R12>
337 WHERE rowid = x_rowid;
338
339 IF (SQL%NOTFOUND) THEN
340 Raise NO_DATA_FOUND;
341 END IF;
342
343 --<Unified Catalog R12: Start>
344 PO_ATTRIBUTE_VALUES_PVT.update_attributes
345 (
346 p_doc_type => 'REQ_TEMPLATE'
347 , p_po_line_id => NULL
348 , p_req_template_name => x_express_name
349 , p_req_template_line_num => x_sequence_num
350 , p_org_id => PO_MOAC_UTILS_PVT.get_current_org_id
351 , p_ip_category_id => l_ip_category_id
352 , p_language => userenv('LANG')
353 , p_item_description => x_item_description
354 );
355 --<Unified Catalog R12: End>
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 --dbms_output.put_line('Exception in update_lines');
360 PO_MESSAGE_S.SQL_ERROR('UPDATE_LINES', x_progress, sqlcode);
361 RAISE;
362 END;
363
364 /*===========================================================================
365
366 PROCEDURE NAME: lock_line
367
368 ===========================================================================*/
369
370 PROCEDURE lock_line (x_rowid VARCHAR2,
371 x_express_name VARCHAR2,
372 x_sequence_num NUMBER,
373 x_po_header_id NUMBER,
374 x_po_line_id NUMBER,
375 x_item_id NUMBER,
376 x_line_type_id NUMBER,
377 x_item_revision VARCHAR2,
378 x_category_id NUMBER,
379 x_unit_meas_lookup_code VARCHAR2,
380 x_suggested_quantity NUMBER, --KitSupport FPJ
381 x_unit_price NUMBER,
382 x_suggested_vendor_id NUMBER,
383 x_suggested_vendor_site_id NUMBER,
384 x_suggested_vendor_contact_id NUMBER,
385 x_suggested_vendor_prod_code VARCHAR2,
386 x_suggested_buyer_id NUMBER,
387 x_rfq_required_flag VARCHAR2,
388 x_vendor_source_context VARCHAR2,
389 x_source_type_code VARCHAR2,
390 x_source_organization_id NUMBER,
391 x_source_subinventory VARCHAR2,
392 x_item_description VARCHAR2,
393 x_attribute_category VARCHAR2,
394 x_attribute1 VARCHAR2,
395 x_attribute2 VARCHAR2,
396 x_attribute3 VARCHAR2,
397 x_attribute4 VARCHAR2,
398 x_attribute5 VARCHAR2,
399 x_attribute6 VARCHAR2,
400 x_attribute7 VARCHAR2,
401 x_attribute8 VARCHAR2,
402 x_attribute9 VARCHAR2,
403 x_attribute10 VARCHAR2,
404 x_attribute11 VARCHAR2,
405 x_attribute12 VARCHAR2,
406 x_attribute13 VARCHAR2,
407 x_attribute14 VARCHAR2,
408 x_attribute15 VARCHAR2,
409 x_amount NUMBER) -- <SERVICES FPJ>
410 IS
411 CURSOR C IS
412 SELECT *
413 FROM PO_REQEXPRESS_LINES
414 WHERE rowid = x_rowid
415 FOR UPDATE of express_name NOWAIT;
416 Recinfo C%ROWTYPE;
417 l_api_name CONSTANT VARCHAR2(30) := 'lock_line';
418 BEGIN
419 OPEN C;
420 FETCH C INTO Recinfo;
421 IF (C%NOTFOUND) then
422 CLOSE C;
423 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
424 APP_EXCEPTION.Raise_Exception;
425 END IF;
426 CLOSE C;
427
428 -- <SERVICES FPJ>
429 -- Added Amount to the IF statement so that it can be locked
430 -- when necessary.
431 IF (
432 (Recinfo.express_name = X_express_name)
433 AND (Recinfo.sequence_num = x_sequence_num)
434 AND ( (Recinfo.po_header_id = X_po_header_id)
435 OR ( (Recinfo.po_header_id IS NULL)
436 AND (X_po_header_id IS NULL)))
437 AND ( (Recinfo.po_line_id = X_po_line_id)
438 OR ( (Recinfo.po_line_id IS NULL)
439 AND (X_po_line_id IS NULL)))
440 AND ( (Recinfo.item_id = X_item_id)
441 OR ( (Recinfo.item_id IS NULL)
442 AND (X_item_id IS NULL)))
443 AND ( (Recinfo.line_type_id = X_line_type_id)
444 OR ( (Recinfo.line_type_id IS NULL)
445 AND (X_line_type_id IS NULL)))
446 AND ( (TRIM(Recinfo.item_revision) = TRIM(X_item_revision))
447 OR ( (TRIM(Recinfo.item_revision) IS NULL)
448 AND (TRIM(X_item_revision) IS NULL)))
449 AND ( (Recinfo.category_id = X_category_id)
450 OR ( (Recinfo.category_id IS NULL)
451 AND (X_category_id IS NULL)))
452 AND ( (TRIM(Recinfo.unit_meas_lookup_code) =TRIM( X_unit_meas_lookup_code))
453 OR ( (TRIM(Recinfo.unit_meas_lookup_code) IS NULL)
454 AND (TRIM(X_unit_meas_lookup_code) IS NULL)))
455 AND ( (Recinfo.suggested_quantity = X_suggested_quantity) --KitSupport FPJ
456 OR ( (Recinfo.suggested_quantity IS NULL)
457 AND (X_suggested_quantity IS NULL)))
458 AND ( (Recinfo.unit_price = X_unit_price)
459 OR ( (Recinfo.unit_price IS NULL)
460 AND (X_unit_price IS NULL)))
461 AND ( (Recinfo.suggested_vendor_id = X_suggested_vendor_id)
462 OR ( (Recinfo.suggested_vendor_id IS NULL)
463 AND (X_suggested_vendor_id IS NULL)))
464 AND ( (Recinfo.suggested_vendor_site_id = X_suggested_vendor_site_id)
465 OR ( (Recinfo.suggested_vendor_site_id IS NULL)
466 AND (X_suggested_vendor_site_id IS NULL)))
467 AND ( (Recinfo.suggested_vendor_contact_id = X_suggested_vendor_contact_id)
468 OR ( (Recinfo.suggested_vendor_contact_id IS NULL)
469 AND (X_suggested_vendor_contact_id IS NULL)))
470 AND ( (TRIM(Recinfo.suggested_vendor_product_code) = TRIM(X_suggested_vendor_prod_code))
471 OR ( (TRIM(Recinfo.suggested_vendor_product_code) IS NULL)
472 AND (TRIM(X_suggested_vendor_prod_code) IS NULL)))
473 AND ( (Recinfo.suggested_buyer_id = X_suggested_buyer_id)
474 OR ( (Recinfo.suggested_buyer_id IS NULL)
475 AND (X_suggested_buyer_id IS NULL)))
476 AND ( (TRIM(Recinfo.rfq_required_flag) = TRIM(X_rfq_required_flag))
477 OR ( (TRIM(Recinfo.rfq_required_flag) IS NULL)
478 AND (TRIM(X_rfq_required_flag) IS NULL)))
479 AND ( (TRIM(Recinfo.vendor_source_context ) =TRIM(X_vendor_source_context))
480 OR ( (TRIM(Recinfo.vendor_source_context) IS NULL)
481 AND (TRIM(X_vendor_source_context) IS NULL)))
482 AND ( (TRIM(Recinfo.source_type_code) = TRIM(X_source_type_code))
483 OR ( (TRIM(Recinfo.source_type_code) IS NULL)
484 AND (TRIM(X_source_type_code) IS NULL)))
485 AND ( (TRIM(Recinfo.source_organization_id) = TRIM(X_source_organization_id))
486 OR ( (TRIM(Recinfo.source_organization_id) IS NULL)
487 AND (TRIM(X_source_organization_id) IS NULL)))
488 AND ( (TRIM(Recinfo.source_subinventory) = TRIM(X_source_subinventory))
489 OR ( (TRIM(Recinfo.source_subinventory) IS NULL)
490 AND (TRIM(X_source_subinventory) IS NULL)))
491 AND ( (TRIM(Recinfo.item_description) = TRIM(X_item_description))
492 OR ( (TRIM(Recinfo.item_description) IS NULL)
493 AND (TRIM(X_item_description) IS NULL)))
494 AND ( (TRIM(Recinfo.attribute_category) = TRIM(X_attribute_category))
495 OR ( (TRIM(Recinfo.attribute_category) IS NULL)
496 AND (TRIM(X_attribute_category) IS NULL)))
497 AND ( (TRIM(Recinfo.attribute1) = TRIM(X_attribute1))
498 OR ( (TRIM(Recinfo.attribute1) IS NULL)
499 AND (TRIM(X_attribute1) IS NULL)))
500 AND ( (TRIM(Recinfo.attribute2) = TRIM(X_attribute2))
501 OR ( (TRIM(Recinfo.attribute2) IS NULL)
502 AND (TRIM(X_attribute2) IS NULL)))
503 AND ( (TRIM(Recinfo.attribute3) = TRIM(X_attribute3))
504 OR ( (TRIM(Recinfo.attribute3) IS NULL)
505 AND (TRIM(X_attribute3) IS NULL)))
506 AND ( (TRIM(Recinfo.attribute4) = TRIM(X_attribute4))
507 OR ( (TRIM(Recinfo.attribute4) IS NULL)
508 AND (TRIM(X_attribute4) IS NULL)))
509 AND ( (TRIM(Recinfo.attribute5) = TRIM(X_attribute5))
510 OR ( (TRIM(Recinfo.attribute5) IS NULL)
511 AND (TRIM(X_attribute5) IS NULL)))
512 AND ( (TRIM(Recinfo.attribute6) = TRIM(X_attribute6))
513 OR ( (TRIM(Recinfo.attribute6) IS NULL)
514 AND (TRIM(X_attribute6) IS NULL)))
515 AND ( (TRIM(Recinfo.attribute7 )= TRIM(X_attribute7))
516 OR ( (TRIM(Recinfo.attribute7) IS NULL)
517 AND (TRIM(X_attribute7) IS NULL)))
518 AND ( (TRIM(Recinfo.attribute8) = TRIM(X_attribute8))
519 OR ( (TRIM(Recinfo.attribute8) IS NULL)
520 AND (TRIM(X_attribute8) IS NULL)))
521 AND ( (TRIM(Recinfo.attribute9) = TRIM(X_attribute9))
522 OR ( (TRIM(Recinfo.attribute9) IS NULL)
523 AND (TRIM(X_attribute9) IS NULL)))
524 AND ( (TRIM(Recinfo.attribute10) = TRIM(X_attribute10))
525 OR ( (TRIM(Recinfo.attribute10) IS NULL)
526 AND (TRIM(X_attribute10) IS NULL)))
527 AND ( (TRIM(Recinfo.attribute11) = TRIM(X_attribute11))
528 OR ( (TRIM(Recinfo.attribute11) IS NULL)
529 AND (TRIM(X_attribute11) IS NULL)))
530 AND ( (TRIM(Recinfo.attribute12) = TRIM(X_attribute12))
531 OR ( (TRIM(Recinfo.attribute12) IS NULL)
532 AND (TRIM(X_attribute12) IS NULL)))
533 AND ( (TRIM(Recinfo.attribute13) = TRIM(X_attribute13))
534 OR ( (TRIM(Recinfo.attribute13) IS NULL)
535 AND (TRIM(X_attribute13) IS NULL)))
536 AND ( (TRIM(Recinfo.attribute14) = TRIM(X_attribute14))
537 OR ( (TRIM(Recinfo.attribute14) IS NULL)
538 AND (TRIM(X_attribute14) IS NULL)))
539 AND ( (TRIM(Recinfo.attribute15) = TRIM(X_attribute15))
540 OR ( (TRIM(Recinfo.attribute15) IS NULL)
541 AND (TRIM(X_attribute15) IS NULL)))
542 AND ( (Recinfo.amount = x_amount)
543 OR ( (Recinfo.amount IS NULL)
544 AND (x_amount IS NULL)))
545 ) THEN
546 return;
547
548 ELSE
549 IF (g_fnd_debug = 'Y') THEN
550 IF (NVL(TRIM(x_express_name),'-999') <> NVL( TRIM(Recinfo.express_name),'-999')) THEN
551 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form express_name '||x_express_name ||' Database express_name '||Recinfo.express_name);
552 END IF;
553 IF (NVL(x_sequence_num,-999) <> NVL(Recinfo.sequence_num,-999)) THEN
554 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form sequence_num'||x_sequence_num ||' Database sequence_num '|| Recinfo.sequence_num);
555 END IF;
556 IF (NVL(x_po_header_id,-999) <> NVL(Recinfo.po_header_id,-999)) THEN
557 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form po_header_id'||x_po_header_id ||' Database po_header_id '|| Recinfo.po_header_id);
558 END IF;
559 IF (NVL(x_po_line_id,-999) <> NVL(Recinfo.po_line_id,-999)) THEN
560 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form po_line_id'||x_po_line_id ||' Database po_line_id '|| Recinfo.po_line_id);
561 END IF;
562 IF (NVL(x_item_id,-999) <> NVL(Recinfo.item_id,-999)) THEN
563 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form item_id'||x_item_id ||' Database item_id '|| Recinfo.item_id);
564 END IF;
565 IF (NVL(x_line_type_id,-999) <> NVL(Recinfo.line_type_id,-999)) THEN
566 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form line_type_id'||x_line_type_id ||' Database line_type_id '|| Recinfo.line_type_id);
567 END IF;
568 IF (NVL(TRIM(x_item_revision),'-999') <> NVL( TRIM(Recinfo.item_revision),'-999')) THEN
569 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form item_revision '||x_item_revision ||' Database item_revision '||Recinfo.item_revision);
570 END IF;
571 IF (NVL(x_category_id,-999) <> NVL(Recinfo.category_id,-999)) THEN
572 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form category_id'||x_category_id ||' Database category_id '|| Recinfo.category_id);
573 END IF;
574 IF (NVL(TRIM(x_unit_meas_lookup_code),'-999') <> NVL( TRIM(Recinfo.unit_meas_lookup_code),'-999')) THEN
575 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form unit_meas_lookup_code '||x_unit_meas_lookup_code ||' Database unit_meas_lookup_code '||Recinfo.unit_meas_lookup_code);
576 END IF;
577 IF (NVL(x_suggested_quantity,-999) <> NVL(Recinfo.suggested_quantity,-999)) THEN
578 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_quantity'||x_suggested_quantity ||' Database suggested_quantity '|| Recinfo.suggested_quantity);
579 END IF;
580 IF (NVL(x_unit_price,-999) <> NVL(Recinfo.unit_price,-999)) THEN
581 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form unit_price'||x_unit_price ||' Database unit_price '|| Recinfo.unit_price);
582 END IF;
583 IF (NVL(x_suggested_vendor_id,-999) <> NVL(Recinfo.suggested_vendor_id,-999)) THEN
584 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_vendor_id'||x_suggested_vendor_id ||' Database suggested_vendor_id '|| Recinfo.suggested_vendor_id);
585 END IF;
586 IF (NVL(x_suggested_vendor_site_id,-999) <> NVL(Recinfo.suggested_vendor_site_id,-999)) THEN
587 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_vendor_site_id'||x_suggested_vendor_site_id ||' Database suggested_vendor_site_id '|| Recinfo.suggested_vendor_site_id);
588 END IF;
589 IF (NVL(x_suggested_vendor_contact_id,-999) <> NVL(Recinfo.suggested_vendor_contact_id,-999)) THEN
590 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_vendor_contact_id'||x_suggested_vendor_contact_id ||' Database suggested_vendor_contact_id '|| Recinfo.suggested_vendor_contact_id);
591 END IF;
592 IF (NVL(TRIM(x_suggested_vendor_prod_code),'-999') <> NVL( TRIM(Recinfo.suggested_vendor_product_code),'-999')) THEN
593 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_vendor_prod_code '||x_suggested_vendor_prod_code ||' Database suggested_vendor_prod_code '||Recinfo.suggested_vendor_product_code);
594 END IF;
595 IF (NVL(x_suggested_buyer_id,-999) <> NVL(Recinfo.suggested_buyer_id,-999)) THEN
596 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_buyer_id'||x_suggested_buyer_id ||' Database suggested_buyer_id '|| Recinfo.suggested_buyer_id);
597 END IF;
598 IF (NVL(TRIM(x_rfq_required_flag),'-999') <> NVL( TRIM(Recinfo.rfq_required_flag),'-999')) THEN
599 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form rfq_required_flag '||x_rfq_required_flag ||' Database rfq_required_flag '||Recinfo.rfq_required_flag);
600 END IF;
601 IF (NVL(TRIM(x_vendor_source_context),'-999') <> NVL( TRIM(Recinfo.vendor_source_context),'-999')) THEN
602 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form vendor_source_context '||x_vendor_source_context ||' Database vendor_source_context '||Recinfo.vendor_source_context);
603 END IF;
604 IF (NVL(TRIM(x_source_type_code),'-999') <> NVL( TRIM(Recinfo.source_type_code),'-999')) THEN
605 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form source_type_code '||x_source_type_code ||' Database source_type_code '||Recinfo.source_type_code);
606 END IF;
607 IF (NVL(x_source_organization_id,-999) <> NVL(Recinfo.source_organization_id,-999)) THEN
608 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form source_organization_id'||x_source_organization_id ||' Database source_organization_id '|| Recinfo.source_organization_id);
609 END IF;
610 IF (NVL(TRIM(x_source_subinventory),'-999') <> NVL( TRIM(Recinfo.source_subinventory),'-999')) THEN
611 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form source_subinventory '||x_source_subinventory ||' Database source_subinventory '||Recinfo.source_subinventory);
612 END IF;
613 IF (NVL(TRIM(x_item_description),'-999') <> NVL( TRIM(Recinfo.item_description),'-999')) THEN
614 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form item_description '||x_item_description ||' Database item_description '||Recinfo.item_description);
615 END IF;
616 IF (NVL(TRIM(x_attribute_category),'-999') <> NVL( TRIM(Recinfo.attribute_category),'-999')) THEN
617 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute_category '||x_attribute_category ||' Database attribute_category '||Recinfo.attribute_category);
618 END IF;
619 IF (NVL(TRIM(x_attribute1),'-999') <> NVL( TRIM(Recinfo.attribute1),'-999')) THEN
620 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute1 '||x_attribute1 ||' Database attribute1 '||Recinfo.attribute1);
621 END IF;
622 IF (NVL(TRIM(x_attribute2),'-999') <> NVL( TRIM(Recinfo.attribute2),'-999')) THEN
623 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute2 '||x_attribute2 ||' Database attribute2 '||Recinfo.attribute2);
624 END IF;
625 IF (NVL(TRIM(x_attribute3),'-999') <> NVL( TRIM(Recinfo.attribute3),'-999')) THEN
626 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute3 '||x_attribute3 ||' Database attribute3 '||Recinfo.attribute3);
627 END IF;
628 IF (NVL(TRIM(x_attribute4),'-999') <> NVL( TRIM(Recinfo.attribute4),'-999')) THEN
629 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute4 '||x_attribute4 ||' Database attribute4 '||Recinfo.attribute4);
630 END IF;
631 IF (NVL(TRIM(x_attribute5),'-999') <> NVL( TRIM(Recinfo.attribute5),'-999')) THEN
632 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute5 '||x_attribute5 ||' Database attribute5 '||Recinfo.attribute5);
633 END IF;
634 IF (NVL(TRIM(x_attribute6),'-999') <> NVL( TRIM(Recinfo.attribute6),'-999')) THEN
635 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute6 '||x_attribute6 ||' Database attribute6 '||Recinfo.attribute6);
636 END IF;
637 IF (NVL(TRIM(x_attribute7),'-999') <> NVL( TRIM(Recinfo.attribute7),'-999')) THEN
638 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute7 '||x_attribute7 ||' Database attribute7 '||Recinfo.attribute7);
639 END IF;
640 IF (NVL(TRIM(x_attribute8),'-999') <> NVL( TRIM(Recinfo.attribute8),'-999')) THEN
641 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute8 '||x_attribute8 ||' Database attribute8 '||Recinfo.attribute8);
642 END IF;
643 IF (NVL(TRIM(x_attribute9),'-999') <> NVL( TRIM(Recinfo.attribute9),'-999')) THEN
644 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute9 '||x_attribute9 ||' Database attribute9 '||Recinfo.attribute9);
645 END IF;
646 IF (NVL(TRIM(x_attribute10),'-999') <> NVL( TRIM(Recinfo.attribute10),'-999')) THEN
647 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute10 '||x_attribute10 ||' Database attribute10 '||Recinfo.attribute10);
648 END IF;
649 IF (NVL(TRIM(x_attribute11),'-999') <> NVL( TRIM(Recinfo.attribute11),'-999')) THEN
650 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute11 '||x_attribute11 ||' Database attribute11 '||Recinfo.attribute11);
651 END IF;
652 IF (NVL(TRIM(x_attribute12),'-999') <> NVL( TRIM(Recinfo.attribute12),'-999')) THEN
653 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute12 '||x_attribute12 ||' Database attribute12 '||Recinfo.attribute12);
654 END IF;
655 IF (NVL(TRIM(x_attribute13),'-999') <> NVL( TRIM(Recinfo.attribute13),'-999')) THEN
656 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute13 '||x_attribute13 ||' Database attribute13 '||Recinfo.attribute13);
657 END IF;
658 IF (NVL(TRIM(x_attribute14),'-999') <> NVL( TRIM(Recinfo.attribute14),'-999')) THEN
659 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute14 '||x_attribute14 ||' Database attribute14 '||Recinfo.attribute14);
660 END IF;
661 IF (NVL(TRIM(x_attribute15),'-999') <> NVL( TRIM(Recinfo.attribute15),'-999')) THEN
662 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form attribute15 '||x_attribute15 ||' Database attribute15 '||Recinfo.attribute15);
663 END IF;
664 IF (NVL(x_amount,-999) <> NVL(Recinfo.amount,-999)) THEN
665 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form amount'||x_amount ||' Database amount '|| Recinfo.amount);
666 END IF;
667 END IF;
668 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
669 APP_EXCEPTION.RAISE_EXCEPTION;
670 END IF;
671
672 END;
673
674 /*===========================================================================
675
676 PROCEDURE NAME: delete_line
677
678 ===========================================================================*/
679
680 PROCEDURE delete_line (x_rowid VARCHAR2)
681 IS
682 -- <Unified Catalog R12 Start>
683 l_req_template_name PO_REQEXPRESS_LINES_ALL.express_name%TYPE;
684 l_req_template_line_num PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE;
685 l_org_id PO_REQEXPRESS_LINES_ALL.org_id%TYPE;
686 -- <Unified Catalog R12 End>
687 BEGIN
688
689 DELETE FROM PO_REQEXPRESS_LINES
690 WHERE rowid = X_Rowid
691 -- <Unified Catalog R12 Start>
692 RETURNING
693 express_name,
694 sequence_num,
695 org_id
696 INTO
697 l_req_template_name,
698 l_req_template_line_num,
699 l_org_id;
700 -- <Unified Catalog R12 End>
701
702 IF (SQL%NOTFOUND) THEN
703 Raise NO_DATA_FOUND;
704 END IF;
705
706 -- <Unified Catalog R12 Start>
707 -- Delete the Attr and TLP rows associated with this line.
708 PO_ATTRIBUTE_VALUES_PVT.delete_attributes
709 (
710 p_doc_type => 'REQ_TEMPLATE'
711 , p_req_template_name => l_req_template_name
712 , p_req_template_line_num => l_req_template_line_num
713 , p_org_id => l_org_id
714 );
715 -- <Unified Catalog R12 End>
716
717 END;
718
719
720 END PO_REQ_TEMPLATE_SV;