[Home] [Help]
PACKAGE BODY: APPS.AR_GTA_TRX_LINES_ALL_PKG
Source
1 PACKAGE BODY AR_GTA_TRX_LINES_ALL_PKG AS
2 --$Header: ARGUGLAB.pls 120.0.12010000.3 2010/01/19 09:22:24 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| ARUGLAB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package provides table handers for |
13 --| table AR_GTA_TRX_LINES_ALL,these handlers |
14 --| will be called by 'Golden Tax Workbench' form and 'Golden Tax |
15 --| invoie import' program to operate data in table |
16 --| AR_GTA_TRX_LINES_ALL |
17 --| |
18 --| PROCEDURE LIST |
19 --| PROCEDURE Insert_Row |
20 --| PROCEDURE Update_Row |
21 --| PROCEDURE Lock_Row |
22 --| PROCEDURE Delete_Row |
23 --| |
24 --| HISTORY |
25 --| 05/17/05 Donghai Wang Created |
26 --| 10/19/05 Donghai Wang Update the procedure Lock_Row
27 --| 16/Jun/2009 Yao Zhang Modified for bug#8605196 to support discount line|
28 --| 20/Jul/2009 yao Zhang Add new procedure query_row to query trx lines.
29 --| 21/Aug/2009 Allen Yang Modified procedure Lock_Row for bug |
30 --| 8670529 (related 12.0.6 bug: 8663110) |
31 --+======================================================================*/
32
33 --==========================================================================
34 -- PROCEDURE NAME:
35 --
36 -- Insert_Row Public
37 --
38 -- DESCRIPTION:
39 --
40 -- This procedure is to insert data that are passed in by parameters into
41 -- table AR_GTA_TRX_LINES_ALL to create a new record
42 --
43 -- PARAMETERS:
44 -- In: p_org_id Identifier of operating unit
45 -- p_gta_trx_header_id Identifier of GTA invoice header
46 -- p_gta_trx_line_id Identifier of GTA invoice line
47 -- p_matched_flag A flag to identify if a GT line
48 -- can match GTA line
49 -- p_line_number Line number
50 -- p_ar_trx_line_id Identifier of AR transaction lines
51 -- p_inventory_item_id Identifier of Item
52 -- p_item_number Item number
53 -- p_item_description Item description
54 -- p_item_model Item Model
55 -- p_item_tax_denomination Tax denomination for a item
56 -- p_tax_rate Tax rate
57 -- p_uom Unit of measure
58 -- p_uom_name Name for unit of measure
59 -- p_quantity Quantity
60 -- p_price_flag Flag to identify if unit procie with tax
61 -- p_unit_price Unit price without tax
62 -- p_unit_tax_price Unit price with tax
63 -- p_amount Amount without tax
64 -- p_original_currency_amount Amount without tax in original
65 -- curency code of invoice
66 -- p_tax_amount Amount with tax
67 -- p_discount_flag A flag to identify amount of
68 -- invoice line with discount or not
69 -- p_enabled_flag A flag to indicate if the line should
70 -- be imported to GT system
71 -- p_request_id Conc request id
72 -- p_program_application_id Program application id
73 -- p_program_id Program id
74 -- p_program_update_date Program update date
75 -- p_attribute_category Attribute category of
76 -- descriptive flexfield
77 -- p_attribute1 Attribute1
78 -- p_attribute2 Attribute2
79 -- p_attribute3 Attribute3
80 -- p_attribute4 Attribute4
81 -- p_attribute5 Attribute5
82 -- p_attribute6 Attribute6
83 -- p_attribute7 Attribute7
84 -- p_attribute8 Attribute8
85 -- p_attribute9 Attribute9
86 -- p_attribute10 Attribute10
87 -- p_attribute11 Attribute11
88 -- p_attribute12 Attribute12
89 -- p_attribute13 Attribute13
90 -- p_attribute14 Attribute14
91 -- p_attribute15 Attribute15
92 -- p_creation_date Creation date
93 -- p_created_by Identifier of user that creates
94 -- the record
95 -- p_last_update_date Last update date of the record
96 -- p_last_updated_by Last update by
97 -- p_last_update_login Last update login
98 -- p_discount_amount discount amount
99 -- p_discount_tax_amount discount tax amount
100 -- p_discount_rate discount rate
101 --
102 -- In Out: p_row_id Row id of a table record
103 --
104 --
105 -- DESIGN REFERENCES:
106 -- GTA_Workbench_Form_TD.doc
107 --
108 -- CHANGE HISTORY:
109 --
110 -- 17-MAY-2005 Donghai Wang created
111 -- 16-Jun-2009 Yao Zhang Fix bug#8605196 ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
112 -- ER1 Support discount lines
113 --===========================================================================
114 PROCEDURE Insert_Row
115 (p_rowid IN OUT NOCOPY VARCHAR2
116 ,p_org_id IN NUMBER
117 ,p_gta_trx_header_id IN NUMBER
118 ,p_gta_trx_line_id IN NUMBER
119 ,p_matched_flag IN VARCHAR2
120 ,p_line_number IN VARCHAR2
121 ,p_ar_trx_line_id IN NUMBER
122 ,p_inventory_item_id IN NUMBER
123 ,p_item_number IN VARCHAR2
124 ,p_item_description IN VARCHAR2
125 ,p_item_model IN VARCHAR2
126 ,p_item_tax_denomination IN VARCHAR2
127 ,p_tax_rate IN NUMBER
128 ,p_uom IN VARCHAR2
129 ,p_uom_name IN VARCHAR2
130 ,p_quantity IN NUMBER
131 ,p_price_flag IN VARCHAR2
132 ,p_unit_price IN NUMBER
133 ,p_unit_tax_price IN NUMBER
134 ,p_amount IN NUMBER
135 ,p_original_currency_amount IN NUMBER
136 ,p_tax_amount IN NUMBER
137 ,p_discount_flag IN VARCHAR2
138 ,p_enabled_flag IN VARCHAR2
139 ,p_request_id IN NUMBER
140 ,p_program_application_id IN NUMBER
141 ,p_program_id IN NUMBER
142 ,p_program_update_date IN DATE
143 ,p_attribute_category IN VARCHAR2
144 ,p_attribute1 IN VARCHAR2
145 ,p_attribute2 IN VARCHAR2
146 ,p_attribute3 IN VARCHAR2
147 ,p_attribute4 IN VARCHAR2
148 ,p_attribute5 IN VARCHAR2
149 ,p_attribute6 IN VARCHAR2
150 ,p_attribute7 IN VARCHAR2
151 ,p_attribute8 IN VARCHAR2
152 ,p_attribute9 IN VARCHAR2
153 ,p_attribute10 IN VARCHAR2
154 ,p_attribute11 IN VARCHAR2
155 ,p_attribute12 IN VARCHAR2
156 ,p_attribute13 IN VARCHAR2
157 ,p_attribute14 IN VARCHAR2
158 ,p_attribute15 IN VARCHAR2
159 ,p_creation_date IN DATE
160 ,p_created_by IN NUMBER
161 ,p_last_update_date IN DATE
162 ,p_last_updated_by IN NUMBER
163 ,p_last_update_login IN NUMBER
164 --Yao Zhang add for bug#8605196 to support discount line
165 ,p_discount_amount IN NUMBER
166 ,p_discount_tax_amount IN NUMBER
167 ,p_discount_rate IN NUMBER
168 )
169 IS
170 l_procedure_name VARCHAR2(100) :='Insert_Row';
171 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
172 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
173 CURSOR C IS
174 SELECT
175 ROWID
176 FROM
177 ar_gta_trx_lines_all
178 WHERE gta_trx_line_id=p_gta_trx_line_id;
179
180 BEGIN
181
182 --log for debug
183 IF( l_proc_level >= l_dbg_level)
184 THEN
185
186 FND_LOG.String(l_proc_level
187 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
188 ,'Enter procedure'
189 );
190
191 END IF; --( l_proc_level >= l_dbg_level)
192
193 --Insert data into table AR_GTA_TRX_LINES_ALL
194 INSERT INTO ar_gta_trx_lines_all(
195 org_id
196 ,gta_trx_header_id
197 ,gta_trx_line_id
198 ,matched_flag
199 ,line_number
200 ,ar_trx_line_id
201 ,inventory_item_id
202 ,item_number
203 ,item_description
204 ,item_model
205 ,item_tax_denomination
206 ,tax_rate
207 ,uom
208 ,uom_name
209 ,quantity
210 ,price_flag
211 ,unit_price
212 ,unit_tax_price
213 ,amount
214 ,original_currency_amount
215 ,tax_amount
216 ,discount_flag
217 ,enabled_flag
218 ,request_id
219 ,program_application_id
220 ,program_id
221 ,program_update_date
222 ,attribute_category
223 ,attribute1
224 ,attribute2
225 ,attribute3
226 ,attribute4
227 ,attribute5
228 ,attribute6
229 ,attribute7
230 ,attribute8
231 ,attribute9
232 ,attribute10
233 ,attribute11
234 ,attribute12
235 ,attribute13
236 ,attribute14
237 ,attribute15
238 ,creation_date
239 ,created_by
240 ,last_update_date
241 ,last_updated_by
242 ,last_update_login
243 --yao zhang add for bug#8605196 to support discount line
244 ,discount_amount
245 ,discount_tax_amount
246 ,discount_rate
247 )
248 VALUES(
249 p_org_id
250 ,p_gta_trx_header_id
251 ,p_gta_trx_line_id
252 ,p_matched_flag
253 ,p_line_number
254 ,p_ar_trx_line_id
255 ,p_inventory_item_id
256 ,p_item_number
257 ,p_item_description
258 ,p_item_model
259 ,p_item_tax_denomination
260 ,p_tax_rate
261 ,p_uom
262 ,p_uom_name
263 ,p_quantity
264 ,p_price_flag
265 ,p_unit_price
266 ,p_unit_tax_price
267 ,p_amount
268 ,p_original_currency_amount
269 ,p_tax_amount
270 ,p_discount_flag
271 ,p_enabled_flag
272 ,p_request_id
273 ,p_program_application_id
274 ,p_program_id
275 ,p_program_update_date
276 ,p_attribute_category
277 ,p_attribute1
278 ,p_attribute2
279 ,p_attribute3
280 ,p_attribute4
281 ,p_attribute5
282 ,p_attribute6
283 ,p_attribute7
284 ,p_attribute8
285 ,p_attribute9
286 ,p_attribute10
287 ,p_attribute11
288 ,p_attribute12
289 ,p_attribute13
290 ,p_attribute14
291 ,p_attribute15
292 ,p_creation_date
293 ,p_created_by
294 ,p_last_update_date
295 ,p_last_updated_by
296 ,p_last_update_login
297 --yao zhang add for bug#8605196 to support discount line
298 ,p_discount_amount
299 ,p_discount_tax_amount
300 ,p_discount_rate
301 );
302
303 --In case of insert failed, raise error
304 OPEN c;
305 FETCH c INTO p_rowid;
306 IF (c%NOTFOUND)
307 THEN
308 CLOSE c;
309 RAISE NO_DATA_FOUND;
310 END IF; --(c%NOTFOUND)
311 CLOSE C;
312 --The following code is add by Yao Zhang for exception handle
313 EXCEPTION
314 WHEN OTHERS THEN
315 fnd_file.PUT_LINE(fnd_file.LOG,'Exception from insert row'||SQLCODE || SQLERRM);
316 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
317 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
318 G_MODULE_PREFIX || l_procedure_name ||
319 '. OTHER_EXCEPTION ',
320 'Unknown error' || SQLCODE || SQLERRM);
321
322 END IF;
323 RAISE;
324 --log for debug
325 IF( l_proc_level >= l_dbg_level)
326 THEN
327
328 FND_LOG.String(l_proc_level
329 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
330 ,'Exit procedure'
334 END Insert_Row;
331 );
332
333 END IF; --( l_proc_level >= l_dbg_level)
335
336
337 --==========================================================================
338 -- PROCEDURE NAME:
339 --
340 -- Update_Row Public
341 --
342 -- DESCRIPTION:
343 --
344 -- This procedure is used to update data in table AR_GTA_TRX_LINES_ALL
345 -- according to parameters passed in
346 --
347 -- PARAMETERS:
348 -- In: p_org_id Identifier of operating unit
349 -- p_gta_trx_header_id Identifier of GTA invoice header
350 -- p_gta_trx_line_id Identifier of GTA invoice line
351 -- p_line_number Line number
352 -- p_item_number Item number
353 -- p_item_description Item description
354 -- p_item_model Item Model
355 -- p_item_tax_denomination Tax denomination for a item
356 -- p_tax_rate Tax rate
357 -- p_uom_name Name for unit of measure
358 -- p_quantity Quantity
359 -- p_unit_price Unit price without tax
360 -- p_amount Amount without tax
361 -- p_original_currency_amount Amount without tax in original
362 -- curency code of invoice
363 -- p_enabled_flag A flag to indicate if the line should
364 -- be imported to GT system
365 -- p_request_id Conc request id
366 -- p_program_application_id Program application id
367 -- p_program_id Program id
368 -- p_program_update_date Program update date
369 -- p_attribute_category Attribute category of
370 -- descriptive flexfield
371 -- p_attribute1 Attribute1
372 -- p_attribute2 Attribute2
373 -- p_attribute3 Attribute3
374 -- p_attribute4 Attribute4
375 -- p_attribute5 Attribute5
376 -- p_attribute6 Attribute6
377 -- p_attribute7 Attribute7
378 -- p_attribute8 Attribute8
379 -- p_attribute9 Attribute9
380 -- p_attribute10 Attribute10
381 -- p_attribute11 Attribute11
382 -- p_attribute12 Attribute12
383 -- p_attribute13 Attribute13
384 -- p_attribute14 Attribute14
385 -- p_attribute15 Attribute15
386 -- p_creation_date Creation date
387 -- p_created_by Identifier of user that creates
388 -- the record
389 -- p_last_update_date Last update date of the record
390 -- p_last_updated_by Last update by
391 -- p_last_update_login Last update login
392 --
393 -- In Out: p_row_id Row id of a table record
394 --
395 --
396 -- DESIGN REFERENCES:
397 -- GTA_Workbench_Form_TD.doc
398 --
399 -- CHANGE HISTORY:
400 --
401 -- 17-MAY-2005 Donghai Wang created
402 --
403 --===========================================================================
404 PROCEDURE Update_Row
405 (p_rowid IN OUT NOCOPY VARCHAR2
406 ,p_org_id IN NUMBER
407 ,p_gta_trx_header_id IN NUMBER
408 ,p_gta_trx_line_id IN NUMBER
409 ,p_line_number IN VARCHAR2
410 ,p_item_number IN VARCHAR2
411 ,p_item_description IN VARCHAR2
412 ,p_item_model IN VARCHAR2
413 ,p_item_tax_denomination IN VARCHAR2
414 ,p_tax_rate IN NUMBER
415 ,p_uom_name IN VARCHAR2
416 ,p_quantity IN NUMBER
417 ,p_unit_price IN NUMBER
418 ,p_amount IN NUMBER
419 ,p_original_currency_amount IN NUMBER
420 ,p_enabled_flag IN VARCHAR2
421 ,p_request_id IN NUMBER
422 ,p_program_application_id IN NUMBER
423 ,p_program_id IN NUMBER
424 ,p_program_update_date IN DATE
425 ,p_attribute_category IN VARCHAR2
426 ,p_attribute1 IN VARCHAR2
427 ,p_attribute2 IN VARCHAR2
428 ,p_attribute3 IN VARCHAR2
429 ,p_attribute4 IN VARCHAR2
430 ,p_attribute5 IN VARCHAR2
431 ,p_attribute6 IN VARCHAR2
432 ,p_attribute7 IN VARCHAR2
433 ,p_attribute8 IN VARCHAR2
434 ,p_attribute9 IN VARCHAR2
435 ,p_attribute10 IN VARCHAR2
436 ,p_attribute11 IN VARCHAR2
437 ,p_attribute12 IN VARCHAR2
438 ,p_attribute13 IN VARCHAR2
439 ,p_attribute14 IN VARCHAR2
440 ,p_attribute15 IN VARCHAR2
441 ,p_creation_date IN DATE
442 ,p_created_by IN NUMBER
443 ,p_last_update_date IN DATE
444 ,p_last_updated_by IN NUMBER
445 ,p_last_update_login IN NUMBER
446 )
447 IS
448 l_procedure_name VARCHAR2(100) :='Update_Row';
449 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
450 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
451 BEGIN
452
453 --log for debug
454 IF( l_proc_level >= l_dbg_level)
455 THEN
456
460 );
457 FND_LOG.String(l_proc_level
458 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
459 ,'Enter procedure'
461
462 END IF; --( l_proc_level >= l_dbg_level)
463
464 --Update data on table AR_GTA_TRX_LINES_ALL
465 UPDATE ar_gta_trx_lines_all
466 SET
467 org_id = p_org_id
468 ,gta_trx_header_id = p_gta_trx_header_id
469 ,gta_trx_line_id = p_gta_trx_line_id
470 ,line_number = p_line_number
471 ,item_number = p_item_number
472 ,item_description = p_item_description
473 ,item_model = p_item_model
474 ,item_tax_denomination = p_item_tax_denomination
475 ,tax_rate = p_tax_rate
476 ,uom_name = p_uom_name
477 ,quantity = p_quantity
478 ,unit_price = p_unit_price
479 ,amount = p_amount
480 ,original_currency_amount = p_original_currency_amount
481 ,enabled_flag = p_enabled_flag
482 ,request_id = p_request_id
483 ,program_application_id = p_program_application_id
484 ,program_id = p_program_id
485 ,program_update_date = p_program_update_date
486 ,attribute_category = p_attribute_category
487 ,attribute1 = p_attribute1
488 ,attribute2 = p_attribute2
489 ,attribute3 = p_attribute3
490 ,attribute4 = p_attribute4
491 ,attribute5 = p_attribute5
492 ,attribute6 = p_attribute6
493 ,attribute7 = p_attribute7
494 ,attribute8 = p_attribute8
495 ,attribute9 = p_attribute9
496 ,attribute10 = p_attribute10
497 ,attribute11 = p_attribute11
498 ,attribute12 = p_attribute12
499 ,attribute13 = p_attribute13
500 ,attribute14 = p_attribute14
501 ,attribute15 = p_attribute15
502 ,creation_date = p_creation_date
503 ,created_by = p_created_by
504 ,last_update_date = p_last_update_date
505 ,last_updated_by = p_last_updated_by
506 ,last_update_login = p_last_update_login
507 WHERE ROWID=p_rowid;
508
509 --In case of update failed, raise error
510 IF (SQL%NOTFOUND)
511 THEN
512 RAISE NO_DATA_FOUND;
513 END IF; --(SQL%NOTFOUND)
514
515 --log for debug
516 IF( l_proc_level >= l_dbg_level)
517 THEN
518
519 FND_LOG.String(l_proc_level
520 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
521 ,'Exit procedure'
522 );
523
524 END IF; --( l_proc_level >= l_dbg_level)
525 END Update_Row;
526
527 --==========================================================================
528 -- PROCEDURE NAME:
529 --
530 -- Lock_Row Public
531 --
532 -- DESCRIPTION:
533 --
534 -- This procedure is used to update implement lock on row level on table
535 -- AR_GTA_TRX_LINES_ALL
536 --
537 -- PARAMETERS:
538 -- In: p_org_id Identifier of operating unit
539 -- p_gta_trx_header_id Identifier of GTA invoice header
540 -- p_gta_trx_line_id Identifier of GTA invoice line
541 -- p_line_number Line number
542 -- p_item_number Item number
543 -- p_item_description Item description
544 -- p_item_model Item Model
545 -- p_item_tax_denomination Tax denomination for a item
546 -- p_tax_rate Tax rate
547 -- p_uom_name Name for unit of measure
548 -- p_quantity Quantity
549 -- p_unit_price Unit price without tax
550 -- p_amount Amount without tax
551 -- p_original_currency_amount Amount without tax in original
552 -- curency code of invoice
553 -- p_enabled_flag A flag to indicate if the line should
554 -- be imported to GT system
555 -- p_request_id Conc request id
556 -- p_program_application_id Program application id
557 -- p_program_id Program id
558 -- p_program_update_date Program update date
559 -- p_attribute_category Attribute category of
560 -- descriptive flexfield
561 -- p_attribute1 Attribute1
562 -- p_attribute2 Attribute2
563 -- p_attribute3 Attribute3
564 -- p_attribute4 Attribute4
565 -- p_attribute5 Attribute5
566 -- p_attribute6 Attribute6
567 -- p_attribute7 Attribute7
568 -- p_attribute8 Attribute8
569 -- p_attribute9 Attribute9
570 -- p_attribute10 Attribute10
571 -- p_attribute11 Attribute11
572 -- p_attribute12 Attribute12
573 -- p_attribute13 Attribute13
574 -- p_attribute14 Attribute14
575 -- p_attribute15 Attribute15
576 -- p_creation_date Creation date
577 -- p_created_by Identifier of user that creates
578 -- the record
579 -- p_last_update_date Last update date of the record
580 -- p_last_updated_by Last update by
581 -- p_last_update_login Last update login
582 --
583 -- In Out: p_row_id Row id of a table record
584 --
585 --
586 -- DESIGN REFERENCES:
587 -- GTA_Workbench_Form_TD.doc
588 --
589 -- CHANGE HISTORY:
590 --
591 -- 17-MAY-2005 Donghai Wang created
592 -- 19-OCT-2005 Donghai Wang call SQL function 'RTRIM' for columns with
593 -- Varchar2 data datatype to truncate
594 -- tail null character
595 -- 21-Aug-2009 Allen Yang modified for bug 8670529 (related 12.0.6
596 -- bug: 8663110)
597 --=========================================================================
598 PROCEDURE Lock_Row
599 (p_rowid IN OUT NOCOPY VARCHAR2
600 ,p_org_id IN NUMBER
601 ,p_gta_trx_header_id IN NUMBER
602 ,p_gta_trx_line_id IN NUMBER
603 ,p_line_number IN VARCHAR2
604 ,p_item_number IN VARCHAR2
605 ,p_item_description IN VARCHAR2
606 ,p_item_model IN VARCHAR2
607 ,p_item_tax_denomination IN VARCHAR2
608 ,p_tax_rate IN NUMBER
609 ,p_uom_name IN VARCHAR2
610 ,p_quantity IN NUMBER
611 ,p_unit_price IN NUMBER
612 ,p_amount IN NUMBER
613 ,p_original_currency_amount IN NUMBER
614 ,p_enabled_flag IN VARCHAR2
615 ,p_request_id IN NUMBER
616 ,p_program_application_id IN NUMBER
617 ,p_program_id IN NUMBER
618 ,p_program_update_date IN DATE
619 ,p_attribute_category IN VARCHAR2
620 ,p_attribute1 IN VARCHAR2
621 ,p_attribute2 IN VARCHAR2
622 ,p_attribute3 IN VARCHAR2
623 ,p_attribute4 IN VARCHAR2
624 ,p_attribute5 IN VARCHAR2
625 ,p_attribute6 IN VARCHAR2
626 ,p_attribute7 IN VARCHAR2
627 ,p_attribute8 IN VARCHAR2
628 ,p_attribute9 IN VARCHAR2
629 ,p_attribute10 IN VARCHAR2
630 ,p_attribute11 IN VARCHAR2
631 ,p_attribute12 IN VARCHAR2
632 ,p_attribute13 IN VARCHAR2
633 ,p_attribute14 IN VARCHAR2
634 ,p_attribute15 IN VARCHAR2
635 ,p_creation_date IN DATE
636 ,p_created_by IN NUMBER
637 ,p_last_update_date IN DATE
638 ,p_last_updated_by IN NUMBER
639 ,p_last_update_login IN NUMBER
640 )
641 IS
642 l_procedure_name VARCHAR2(100) :='Lock_Row';
643 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
644 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
645
646 CURSOR c IS
647 SELECT
648 *
649 FROM
650 ar_gta_trx_lines_all
651 WHERE ROWID=p_rowid
652 FOR UPDATE OF gta_trx_line_id NOWAIT;
653
654 recinfo c%ROWTYPE;
655 BEGIN
656
657 --log for debug
658 IF( l_proc_level >= l_dbg_level)
659 THEN
660
661 FND_LOG.String(l_proc_level
662 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
663 ,'Begin procedure'
664 );
665
666 END IF; --( l_proc_level >= l_dbg_level)
667
668 --If a record has been deleted as form tries to excute dml operation
669 --on that record,then raise error to form
670 OPEN c;
671 FETCH c INTO recinfo;
672
673 IF (c%NOTFOUND)
674 THEN
675 CLOSE c;
676 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
677 APP_EXCEPTION.Raise_Exception;
678 END IF; --(c%NOTFOUND)
679
680 CLOSE c;
681
682
683 IF (
684 (recinfo.org_id=p_org_id)
685 AND
686 (recinfo.gta_trx_header_id=p_gta_trx_header_id)
687 AND
688 (recinfo.gta_trx_line_id=p_gta_trx_line_id)
689 AND
690 (rtrim(recinfo.line_number)=p_line_number)
691 AND
692 (
693 (rtrim(recinfo.item_number)=p_item_number)
694 OR
695 (
696 (rtrim(recinfo.item_number) IS NULL)
697 AND
698 (p_item_number IS NULL)
699 )
700 )
701 AND
702 (rtrim(recinfo.item_description)=p_item_description)
703 AND
704 (
705 (rtrim(recinfo.item_model)=p_item_model)
706 OR
707 (
708 (rtrim(recinfo.item_model) IS NULL)
709 AND
710 (p_item_model IS NULL)
711 )
712 )
713 AND
714 (rtrim(recinfo.item_tax_denomination)=p_item_tax_denomination)
715 AND
716 (recinfo.tax_rate=p_tax_rate)
717 AND
718 (
719 -- Modified by Allen Yang for bug 8670529 (related 12.0.6 bug:
720 -- 8670529) on 21/Aug/2009
721 -------------------------------------------------------------
722 --(rtrim(recinfo.uom_name)=p_uom_name)
723 (rtrim(recinfo.uom)=p_uom_name)
724 OR
725 (
726 --(rtrim(recinfo.uom_name) IS NULL)
727 (rtrim(recinfo.uom) IS NULL)
728 AND
729 (p_uom_name IS NULL)
730 )
731 -------------------------------------------------------------
732 )
733 AND
734 (recinfo.quantity=p_quantity)
735 AND
736 (
737 (recinfo.unit_price=p_unit_price)
738 OR
739 (
740 (recinfo.unit_price IS NULL)
741 AND
742 (p_unit_price IS NULL)
743 )
744 )
745 AND
746 (
747 (recinfo.amount=p_amount)
748 OR
749 (
750 (recinfo.amount IS NULL)
751 AND
752 (p_amount IS NULL)
753 )
754 )
755 AND
756 (
757 (recinfo.original_currency_amount=p_original_currency_amount)
758 OR
759 (
760 (recinfo.original_currency_amount IS NULL)
761 AND
762 (p_original_currency_amount IS NULL)
763 )
764 )
765 AND
766 (
767 (rtrim(recinfo.enabled_flag)=p_enabled_flag)
768 OR
769 (
770 (rtrim(recinfo.enabled_flag) IS NULL)
771 AND
772 (p_enabled_flag IS NULL)
773 )
774 )
775 AND
776 (recinfo.created_by=p_created_by)
777 AND
778 (recinfo.creation_date=p_creation_date)
779 AND
780 (recinfo.last_update_date=p_last_update_date)
781 AND
782 (recinfo.last_updated_by=p_last_updated_by)
783 AND
784 (
785 (recinfo.last_update_login=p_last_update_login)
786 OR
787 (
788 (recinfo.last_update_login IS NULL)
789 AND
790 (p_last_update_login IS NULL)
791 )
792 )
793 AND
794 (
795 (recinfo.request_id=p_request_id)
796 OR
797 (
798 (recinfo.request_id IS NULL)
799 AND
800 (p_request_id IS NULL)
801 )
802 )
803 AND
804 (
805 (recinfo.program_application_id=p_program_application_id)
806 OR
807 (
808 (recinfo.program_application_id IS NULL)
809 AND
810 (p_program_application_id IS NULL)
811 )
812 )
813 AND
814 (
815 (recinfo.program_id=p_program_id)
816 OR
817 (
818 (recinfo.program_id IS NULL)
819 AND
820 (p_program_id IS NULL)
821 )
822 )
823 AND
824 (
825 (recinfo.program_update_date=p_program_update_date)
826 OR
827 (
828 (recinfo.program_update_date IS NULL)
829 AND
830 (p_program_update_date IS NULL)
831 )
832 )
833 AND
834 (
835 (recinfo.attribute_category=p_attribute_category)
836 OR
837 (
838 (recinfo.attribute_category IS NULL)
839 AND
840 (p_attribute_category IS NULL)
841 )
842 )
843 AND
844 (
845 (recinfo.attribute1=p_attribute1)
846 OR
847 (
848 (recinfo.attribute1 IS NULL)
849 AND
850 (p_attribute1 IS NULL)
851 )
852 )
853 AND
854 (
855 (recinfo.attribute2=p_attribute2)
856 OR
857 (
858 (recinfo.attribute2 IS NULL)
859 AND
860 (p_attribute2 IS NULL)
861 )
862 )
863 AND
864 (
865 (recinfo.attribute3=p_attribute3)
866 OR
867 (
868 (recinfo.attribute3 IS NULL)
869 AND
870 (p_attribute3 IS NULL)
871 )
872 )
873 AND
874 (
875 (recinfo.attribute4=p_attribute4)
876 OR
877 (
878 (recinfo.attribute4 IS NULL)
879 AND
880 (p_attribute4 IS NULL)
881 )
882 )
883 AND
884 (
885 (recinfo.attribute5=p_attribute5)
886 OR
887 (
888 (recinfo.attribute5 IS NULL)
889 AND
890 (p_attribute5 IS NULL)
891 )
892 )
893 AND
894 (
895 (recinfo.attribute6=p_attribute6)
896 OR
897 (
898 (recinfo.attribute6 IS NULL)
899 AND
900 (p_attribute6 IS NULL)
901 )
902 )
903 AND
904 (
905 (recinfo.attribute7=p_attribute7)
906 OR
907 (
908 (recinfo.attribute7 IS NULL)
909 AND
910 (p_attribute7 IS NULL)
911 )
912 )
913 AND
914 (
915 (recinfo.attribute8=p_attribute8)
916 OR
917 (
918 (recinfo.attribute8 IS NULL)
919 AND
920 (p_attribute8 IS NULL)
921 )
922 )
923 AND
924 (
925 (recinfo.attribute9=p_attribute9)
926 OR
927 (
928 (recinfo.attribute9 IS NULL)
929 AND
930 (p_attribute9 IS NULL)
931 )
932 )
933 AND
934 (
935 (recinfo.attribute10=p_attribute10)
936 OR
937 (
938 (recinfo.attribute10 IS NULL)
939 AND
940 (p_attribute10 IS NULL)
941 )
942 )
943 AND
944 (
945 (recinfo.attribute11=p_attribute11)
946 OR
947 (
948 (recinfo.attribute11 IS NULL)
949 AND
950 (p_attribute11 IS NULL)
951 )
952 )
953 AND
954 (
955 (recinfo.attribute12=p_attribute12)
956 OR
957 (
958 (recinfo.attribute12 IS NULL)
959 AND
960 (p_attribute12 IS NULL)
961 )
962 )
963 AND
964 (
965 (recinfo.attribute13=p_attribute13)
966 OR
967 (
968 (recinfo.attribute13 IS NULL)
969 AND
970 (p_attribute13 IS NULL)
971 )
972 )
973 AND
974 (
975 (recinfo.attribute14=p_attribute14)
976 OR
977 (
978 (recinfo.attribute14 IS NULL)
979 AND
980 (p_attribute14 IS NULL)
981 )
982 )
983 AND
984 (
985 (recinfo.attribute15=p_attribute15)
986 OR
987 (
988 (recinfo.attribute15 IS NULL)
989 AND
990 (p_attribute15 IS NULL)
991 )
992 )
993 )
994
995 THEN
996 RETURN;
997 ELSE
998 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
999 APP_EXCEPTION.Raise_Exception;
1000 END IF; --((recinfo.org_id=p_org_id)...
1001
1002 --log for debug
1003 IF( l_proc_level >= l_dbg_level)
1004 THEN
1005
1006 FND_LOG.String(l_proc_level
1007 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
1008 ,'Exit procedure'
1009 );
1010
1011 END IF; --( l_proc_level >= l_dbg_level)
1012 END Lock_Row;
1013
1014
1015 --==========================================================================
1016 -- PROCEDURE NAME:
1017 --
1018 -- Delete_Row Public
1019 --
1020 -- DESCRIPTION:
1021 --
1022 -- This procedure is used to delete record from table
1023 -- AR_GTA_TRX_LINES_ALL
1024 --
1025 -- PARAMETERS:
1026 --
1027 -- In Out: p_row_id Row id of a table record
1028 --
1029 --
1030 -- DESIGN REFERENCES:
1031 -- GTA_Workbench_Form_TD.doc
1032 --
1033 -- CHANGE HISTORY:
1034 --
1035 -- 17-MAY-2005 Donghai Wang created
1036 --
1037 --===========================================================================
1038 PROCEDURE Delete_Row
1039 (p_rowid IN OUT NOCOPY VARCHAR2
1040 )
1041 IS
1042 l_procedure_name VARCHAR2(100) :='Delete_Row';
1043 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1044 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1045 BEGIN
1046
1047 --log for debug
1048 IF( l_proc_level >= l_dbg_level)
1049 THEN
1050
1051 FND_LOG.String(l_proc_level
1052 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
1053 ,'Begin procedure'
1054 );
1055
1056 END IF; --( l_proc_level >= l_dbg_level)
1057
1058 --Delete row from table AR_GTA_TRX_LINES_ALL
1059 DELETE
1060 FROM AR_GTA_TRX_LINES_ALL
1061 WHERE ROWID = p_rowid;
1062
1063 --In case of delete failed,raise error
1064 IF (SQL%NOTFOUND) THEN
1065 RAISE NO_DATA_FOUND;
1066 END IF; --(SQL%NOTFOUND)
1067
1068 --log for debug
1069 IF( l_proc_level >= l_dbg_level)
1070 THEN
1071
1072 FND_LOG.String(l_proc_level
1073 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
1074 ,'Exit procedure'
1075 );
1076
1077 END IF; --( l_proc_level >= l_dbg_level)
1078 END Delete_Row;
1079 --==========================================================================
1080 -- PROCEDURE NAME:
1081 --
1082 -- Query_Row Public
1083 --
1084 -- DESCRIPTION:
1085 --
1086 -- This procedure is used to retrieve record by parameter p_header
1087 -- from table AR_GTA_TRX_LINES_ALL
1088 --
1089 -- PARAMETERS:
1090 -- In: p_trx_line_id Identifier of GTA invoice header
1091 --
1092 -- Out: x_trx_line_rec trx_line_rec_type record to store a row fetched from
1093 -- table AR_GTA_TRX_HEADERS_ALL
1094 -- DESIGN REFERENCES:
1095 -- GTA_12.1.2_Technical_Design.doc
1096 --
1097 -- CHANGE HISTORY:
1098 --
1099 -- 30-Jun-2009 Yao Zhang created
1100 --===========================================================================
1101 PROCEDURE Query_Row
1102 (p_trx_line_id IN NUMBER
1103 ,x_trx_line_rec OUT NOCOPY AR_GTA_TRX_UTIL.Trx_Line_Rec_Type
1104 )
1105 IS
1106 l_procedure_name VARCHAR2(100) :='Query_Row';
1107 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1108 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1109 BEGIN
1110
1111 --log for debug
1112 IF( l_proc_level >= l_dbg_level)
1113 THEN
1114
1115 FND_LOG.String(l_proc_level
1116 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
1117 ,'Begin procedure'
1118 );
1119
1120 END IF; --( l_proc_level >= l_dbg_level)
1121 SELECT org_id,
1122 gta_trx_header_id,
1123 gta_trx_line_id,
1124 matched_flag,
1125 line_number,
1126 ar_trx_line_id,
1127 inventory_item_id,
1128 item_number,
1129 item_description,
1130 item_model,
1131 item_tax_denomination,
1132 tax_rate,
1133 uom,
1134 uom_name,
1135 quantity,
1136 price_flag,
1137 unit_price,
1138 amount,
1139 original_currency_amount,
1140 tax_amount,
1141 discount_flag,
1142 enabled_flag,
1143 attribute_category,
1144 attribute1,
1145 attribute2,
1146 attribute3,
1147 attribute4,
1148 attribute5,
1149 attribute6,
1150 attribute7,
1151 attribute8,
1152 attribute9,
1153 attribute10,
1154 attribute11,
1155 attribute12,
1156 attribute13,
1157 attribute14,
1158 attribute15,
1159 last_update_date,
1160 last_updated_by,
1161 creation_date,
1162 created_by,
1163 last_update_login,
1164 program_id,
1165 program_application_id,
1166 program_update_date,
1167 request_id,
1168 discount_amount,
1169 discount_tax_amount,
1170 discount_rate
1171 INTO x_trx_line_rec.org_id,
1172 x_trx_line_rec.gta_trx_header_id,
1173 x_trx_line_rec.gta_trx_line_id,
1174 x_trx_line_rec.matched_flag,
1175 x_trx_line_rec.line_number,
1176 x_trx_line_rec.ar_trx_line_id,
1177 x_trx_line_rec.inventory_item_id,
1178 x_trx_line_rec.item_number,
1179 x_trx_line_rec.item_description,
1180 x_trx_line_rec.item_model,
1181 x_trx_line_rec.item_tax_denomination,
1182 x_trx_line_rec.tax_rate,
1183 x_trx_line_rec.uom,
1184 x_trx_line_rec.uom_name,
1185 x_trx_line_rec.quantity,
1186 x_trx_line_rec.price_flag,
1187 x_trx_line_rec.unit_price,
1188 x_trx_line_rec.amount,
1189 x_trx_line_rec.original_currency_amount,
1190 x_trx_line_rec.tax_amount,
1191 x_trx_line_rec.discount_flag,
1192 x_trx_line_rec.enabled_flag,
1193 x_trx_line_rec.attribute_category,
1194 x_trx_line_rec.attribute1,
1195 x_trx_line_rec.attribute2,
1196 x_trx_line_rec.attribute3,
1197 x_trx_line_rec.attribute4,
1198 x_trx_line_rec.attribute5,
1199 x_trx_line_rec.attribute6,
1200 x_trx_line_rec.attribute7,
1201 x_trx_line_rec.attribute8,
1202 x_trx_line_rec.attribute9,
1203 x_trx_line_rec.attribute10,
1204 x_trx_line_rec.attribute11,
1205 x_trx_line_rec.attribute12,
1206 x_trx_line_rec.attribute13,
1207 x_trx_line_rec.attribute14,
1208 x_trx_line_rec.attribute15,
1209 x_trx_line_rec.last_update_date,
1210 x_trx_line_rec.last_updated_by,
1211 x_trx_line_rec.creation_date,
1212 x_trx_line_rec.created_by,
1213 x_trx_line_rec.last_update_login,
1214 x_trx_line_rec.program_id,
1215 x_trx_line_rec.program_applicaton_id,
1216 x_trx_line_rec.program_update_date,
1217 x_trx_line_rec.request_id,
1218 x_trx_line_rec.discount_amount,
1219 x_trx_line_rec.discount_tax_amount,
1220 x_trx_line_rec.discount_rate
1221 FROM ar_gta_trx_lines_all
1222 WHERE gta_trx_line_id = p_trx_line_id;
1223 --log for debug
1224 IF( l_proc_level >= l_dbg_level)
1225 THEN
1226
1227 FND_LOG.String(l_proc_level
1228 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
1229 ,'Exit procedure'
1230 );
1231
1232 END IF; --( l_proc_level >= l_dbg_level)
1233 END Query_Row;
1234
1235 END AR_GTA_TRX_LINES_ALL_PKG;