1 PACKAGE BODY AR_GTA_TRX_HEADERS_ALL_PKG AS
2 --$Header: ARGUGHAB.pls 120.0.12010000.3 2010/01/19 09:12:38 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| ARUGHAB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package provides table handers for |
13 --| table AR_GTA_TRX_HEADERS_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_HEADERS_ALL |
17 --| |
18 --| PROCEDURE LIST |
19 --| PROCEDURE Insert_Row |
23 --| PROCEDURE Query_Row |
20 --| PROCEDURE Update_Row |
21 --| PROCEDURE Lock_Row |
22 --| PROCEDURE Delete_Row |
24 --| |
25 --| HISTORY |
26 --| 05/17/05 Donghai Wang Created |
27 --| 06/21/05 Jogen Hu Added procedure Query_Row |
28 --| 09/26/05 Donghai Wang Add parameters for procedures |
29 --| 'Insert_Row','Update_Row' and |
30 --| 'Lock_Row'
31 --| 01/02/08 Subba Modified for R12.1,
32 --| Added invoice_type parameter to all |
33 --| procedures. |
34 -- 13/Jul/2009 Allen Yang added procedure Unmerge_Row for bug 8605196:
35 -- ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
36 --+======================================================================*/
37
38 --==========================================================================
39 -- PROCEDURE NAME:
40 --
41 -- Insert_Row Public
42 --
43 -- DESCRIPTION:
44 --
45 -- This procedure is to insert data that are passed in by parameters into
46 -- table AR_GTA_TRX_HEADERS_ALL to create a new record
47 --
48 -- PARAMETERS:
49 -- In: p_ra_gl_date GL date
50 -- p_ra_gl_period GL Period
51 -- p_set_of_books_id Identifier of a GL book
52 -- p_bill_to_customer_id Identifier of bill to customer
53 -- p_bill_to_customer_number Customer number
54 -- p_bill_to_customer_name Customer name
55 -- p_source Source of GTA invoice, alternative
56 -- value is 'AR' or 'GT'
57 -- p_org_id Identifier of operating unit
58 -- p_legal_entity_id Identifier of legal entity
59 -- p_rule_header_id Identifier of transfer rule
60 -- p_gta_trx_header_id Identifier of GTA invoice header
61 -- p_gta_trx_numbe Number of GTA invoice
62 -- p_group_number Identifier of group split
63 -- p_version Version of a GTA invoice
64 -- p_latest_version_flag Flag to identify if a GTA invoice
65 -- is the one with maximum version number
66 -- p_transaction_date AR transaction date
67 -- p_ra_trx_id Identifier of AR transaction
68 -- p_ra_trx_number Number of AR transaction
69 -- p_description Description of a GTA invoice
70 -- p_customer_address Customer address
71 -- p_customer_phone Phone number of a customer
72 -- p_customer_address_phone Address and phone number of a customer
73 -- p_bank_account_name Bank account name
74 -- p_bank_account_number Bank account number
75 -- p_bank_account_name_number Bank account name and number
76 -- p_fp_tax_registration_number Tax Registration Number of First Party
77 -- p_tp_tax_registration_number Tax Registratioin Number of Third Party
78 -- p_ra_currency_code Currency code of an AR transaction
79 -- p_conversion_type Conversion type of currency
80 -- p_conversion_date Currency conversion date
81 -- p_conversion_rate exchange rate of currency
82 -- p_gta_batch_number Batch number of GTA invoices
83 -- p_gt_invoice_number GT invoice number
84 -- p_gt_invoice_date GT invoice date
85 -- p_gt_invoice_net_amount Net amount of a GT invoice
86 -- P_gt_invoice_tax_amount Tax amount of a GT invoice
87 -- p_status Status of GTA invoice or GT invoice,
88 -- acceptable values is :'DRAFT',
89 -- 'GENERATED',
90 -- 'CANCELLED',
91 -- 'FAILED',
92 -- 'COMPLETED'
93 -- p_sales_list_flag Flag to identify if a GTA invoice is
94 -- sale list enbaled
95 -- p_cancel_flag Flag to identify if a GT invoice is
96 -- Cancelled or not
97 -- p_gt_invoice_type Type of GT invoice
98 -- p_gt_invoice_class Class of GT invoice
99 -- p_gt_tax_month Tax month of GT invoice
100 -- p_issuer_name Issuer name of GT invoice
101 -- p_reviewer_name Reviewer name of GT invoice
102 -- p_payee_name Payee name of GT invoice
103 -- p_tax_code Tax code
104 -- p_generator_id Generator id
105 -- p_export_request_id Conc request id of GTA invoice
106 -- export program
107 -- p_request_id Conc request id
108 -- p_program_application_id Program application id
109 -- p_program_id Program id
110 -- p_program_update_date Program update date
111 -- p_attribute_category Attribute category of
112 -- descriptive flexfield
113 -- p_attribute1 Attribute1
114 -- p_attribute2 Attribute2
118 -- p_attribute6 Attribute6
115 -- p_attribute3 Attribute3
116 -- p_attribute4 Attribute4
117 -- p_attribute5 Attribute5
119 -- p_attribute7 Attribute7
120 -- p_attribute8 Attribute8
121 -- p_attribute9 Attribute9
122 -- p_attribute10 Attribute10
123 -- p_attribute11 Attribute11
124 -- p_attribute12 Attribute12
125 -- p_attribute13 Attribute13
126 -- p_attribute14 Attribute14
127 -- p_attribute15 Attribute15
128 -- p_creation_date Creation date
129 -- p_created_by Identifier of user that creates
130 -- the record
131 -- p_last_update_date Last update date of the record
132 -- p_last_updated_by Last update by
133 -- p_last_update_login Last update login
134 -- p_invoice_type Invoice type --added by subba for R12.1
135 --
136 -- In Out: p_row_id Row id of a table record
137 --
138 --
139 -- DESIGN REFERENCES:
140 -- GTA_Workbench_Form_TD.doc
141 --
142 -- CHANGE HISTORY:
143 --
144 -- 17-MAY-2005 Donghai Wang created
145 -- 26-SEP-2005 Donghai Wang add three parameters:
146 -- p_legal_entity_id
147 -- p_fp_tax_registration_number
148 -- p_tp_tax_registration_number
149 -- 02-JAN-2008 Subba added p_invoice_type parameter
150 -- 20-Jul-2009 Yao Zhang modified for bug#8605196 consolidate invoices
151 --===========================================================================
152 PROCEDURE Insert_Row
153 (p_row_id IN OUT NOCOPY VARCHAR2
154 ,p_ra_gl_date IN DATE
155 ,P_ra_gl_period IN VARCHAR
156 ,p_set_of_books_id IN NUMBER
157 ,p_bill_to_customer_id IN NUMBER
158 ,p_bill_to_customer_number IN VARCHAR2
159 ,p_bill_to_customer_name IN VARCHAR2
160 ,p_source IN VARCHAR2
161 ,p_org_id IN NUMBER
162 ,p_legal_entity_id IN NUMBER
163 ,p_rule_header_id IN NUMBER
164 ,p_gta_trx_header_id IN NUMBER
165 ,p_gta_trx_number IN VARCHAR2
166 ,p_group_number IN NUMBER
167 ,p_version IN NUMBER
168 ,p_latest_version_flag IN VARCHAR2
169 ,p_transaction_date IN DATE
170 ,p_ra_trx_id IN NUMBER
171 ,p_ra_trx_number IN VARCHAR2
172 ,p_description IN VARCHAR2
173 ,p_customer_address IN VARCHAR2
174 ,p_customer_phone IN VARCHAR2
175 ,p_customer_address_phone IN VARCHAR2
176 ,p_bank_account_name IN VARCHAR2
177 ,p_bank_account_number IN VARCHAR2
178 ,p_bank_account_name_number IN VARCHAR2
179 ,p_fp_tax_registration_number IN VARCHAR2
180 ,p_tp_tax_registration_number IN VARCHAR2
181 ,p_ra_currency_code IN VARCHAR2
182 ,p_conversion_type IN VARCHAR2
183 ,p_conversion_date IN DATE
184 ,p_conversion_rate IN NUMBER
185 ,p_gta_batch_number IN VARCHAR2
186 ,p_gt_invoice_number IN VARCHAR2
187 ,p_gt_invoice_date IN DATE
188 ,p_gt_invoice_net_amount IN NUMBER
189 ,P_gt_invoice_tax_amount IN NUMBER
190 ,p_status IN VARCHAR2
191 ,p_sales_list_flag IN VARCHAR2
192 ,p_cancel_flag IN VARCHAR2
193 ,p_gt_invoice_type IN VARCHAR2
194 ,p_gt_invoice_class IN VARCHAR2
195 ,p_gt_tax_month IN VARCHAR2
196 ,p_issuer_name IN VARCHAR2
197 ,p_reviewer_name IN VARCHAR2
198 ,p_payee_name IN VARCHAR2
199 ,p_tax_code IN VARCHAR2
200 ,p_tax_rate IN NUMBER
201 ,p_generator_id IN NUMBER
202 ,p_export_request_id IN NUMBER
203 ,p_request_id IN NUMBER
204 ,p_program_application_id IN NUMBER
205 ,p_program_id IN NUMBER
206 ,p_program_update_date IN DATE
207 ,p_attribute_category IN VARCHAR2
208 ,p_attribute1 IN VARCHAR2
209 ,p_attribute2 IN VARCHAR2
210 ,p_attribute3 IN VARCHAR2
211 ,p_attribute4 IN VARCHAR2
212 ,p_attribute5 IN VARCHAR2
213 ,p_attribute6 IN VARCHAR2
214 ,p_attribute7 IN VARCHAR2
215 ,p_attribute8 IN VARCHAR2
216 ,p_attribute9 IN VARCHAR2
217 ,p_attribute10 IN VARCHAR2
218 ,p_attribute11 IN VARCHAR2
219 ,p_attribute12 IN VARCHAR2
220 ,p_attribute13 IN VARCHAR2
221 ,p_attribute14 IN VARCHAR2
222 ,p_attribute15 IN VARCHAR2
223 ,p_creation_date IN DATE
224 ,p_created_by IN NUMBER
225 ,p_last_update_date IN DATE
226 ,p_last_updated_by IN NUMBER
227 ,p_last_update_login IN NUMBER
228 ,p_invoice_type IN VARCHAR2 --added by subba for R12.1
229 --Yao Zhang add begin for bug#8605196 consolidate invoices
230 ,p_consolidation_id IN NUMBER
231 ,p_consolidation_flag IN VARCHAR2
232 ,p_consolidation_trx_num IN VARCHAR2
233 --Yao Zhang add end for bug#8605196 consolidate invoices
234 )
235 IS
236 l_procedure_name VARCHAR2(100) :='Insert_Row';
237 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
238 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
239 CURSOR C IS
240 SELECT
241 ROWID
242 FROM
243 ar_gta_trx_headers_all
244 WHERE
245 gta_trx_header_id=p_gta_trx_header_id;
246 BEGIN
247
248 --log for debug
249 IF( l_proc_level >= l_dbg_level)
250 THEN
251
252 FND_LOG.String(l_proc_level
253 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
254 ,'Enter procedure'
255 );
256
257 END IF; --( l_proc_level >= l_dbg_level)
258
259
260 --Insert data into table AR_GTA_TRX_HEADERS_ALL
261 INSERT INTO ar_gta_trx_headers_all(
262 ra_gl_date
263 ,ra_gl_period
264 ,set_of_books_id
265 ,bill_to_customer_id
266 ,bill_to_customer_number
267 ,bill_to_customer_name
268 ,source
269 ,org_id
270 ,legal_entity_id
271 ,rule_header_id
272 ,gta_trx_header_id
273 ,gta_trx_number
274 ,group_number
275 ,version
276 ,latest_version_flag
277 ,transaction_date
278 ,ra_trx_id
279 ,ra_trx_number
280 ,description
281 ,customer_address
282 ,customer_phone
283 ,customer_address_phone
284 ,bank_account_name
285 ,bank_account_number
286 ,bank_account_name_number
287 ,fp_tax_registration_number
288 ,tp_tax_registration_number
289 ,ra_currency_code
290 ,conversion_type
291 ,conversion_date
292 ,conversion_rate
293 ,gta_batch_number
294 ,gt_invoice_number
295 ,gt_invoice_date
296 ,gt_invoice_net_amount
297 ,gt_invoice_tax_amount
298 ,status
299 ,sales_list_flag
300 ,cancel_flag
301 ,gt_invoice_type
302 ,gt_invoice_class
303 ,gt_tax_month
304 ,issuer_name
305 ,reviewer_name
306 ,payee_name
307 ,tax_code
308 ,tax_rate
309 ,generator_id
310 ,export_request_id
311 ,request_id
312 ,program_application_id
313 ,program_id
314 ,program_update_date
315 ,attribute_category
316 ,attribute1
317 ,attribute2
318 ,attribute3
319 ,attribute4
320 ,attribute5
321 ,attribute6
322 ,attribute7
323 ,attribute8
324 ,attribute9
325 ,attribute10
326 ,attribute11
327 ,attribute12
328 ,attribute13
329 ,attribute14
330 ,attribute15
331 ,creation_date
332 ,created_by
333 ,last_update_date
334 ,last_updated_by
335 ,last_update_login
336 ,invoice_type --added by subba for R12.1
337 --Yao Zhang add begin for bug#8605196 consolidate invoices
338 ,consolidation_flag
339 ,consolidation_id
340 ,consolidation_trx_num
341 --Yao Zhang add end for bug#8605196 consolidate invoices
342 )
343 VALUES(
344 p_ra_gl_date
345 ,P_ra_gl_period
346 ,p_set_of_books_id
347 ,p_bill_to_customer_id
348 ,p_bill_to_customer_number
349 ,p_bill_to_customer_name
350 ,p_source
351 ,p_org_id
352 ,p_legal_entity_id
353 ,p_rule_header_id
354 ,p_gta_trx_header_id
355 ,p_gta_trx_number
356 ,p_group_number
357 ,p_version
358 ,p_latest_version_flag
359 ,p_transaction_date
360 ,p_ra_trx_id
361 ,p_ra_trx_number
362 ,p_description
363 ,p_customer_address
364 ,p_customer_phone
365 ,p_customer_address_phone
366 ,p_bank_account_name
367 ,p_bank_account_number
368 ,p_bank_account_name_number
369 ,p_fp_tax_registration_number
370 ,p_tp_tax_registration_number
371 ,p_ra_currency_code
372 ,p_conversion_type
373 ,p_conversion_date
374 ,p_conversion_rate
375 ,p_gta_batch_number
376 ,p_gt_invoice_number
377 ,p_gt_invoice_date
378 ,p_gt_invoice_net_amount
379 ,P_gt_invoice_tax_amount
380 ,p_status
381 ,p_sales_list_flag
382 ,p_cancel_flag
383 ,p_gt_invoice_type
384 ,p_gt_invoice_class
385 ,p_gt_tax_month
386 ,p_issuer_name
387 ,p_reviewer_name
388 ,p_payee_name
389 ,p_tax_code
390 ,p_tax_rate
391 ,p_generator_id
392 ,p_export_request_id
393 ,p_request_id
394 ,p_program_application_id
395 ,p_program_id
396 ,p_program_update_date
397 ,p_attribute_category
398 ,p_attribute1
399 ,p_attribute2
400 ,p_attribute3
401 ,p_attribute4
402 ,p_attribute5
403 ,p_attribute6
404 ,p_attribute7
405 ,p_attribute8
406 ,p_attribute9
407 ,p_attribute10
408 ,p_attribute11
409 ,p_attribute12
410 ,p_attribute13
411 ,p_attribute14
412 ,p_attribute15
413 ,p_creation_date
414 ,p_created_by
415 ,p_last_update_date
416 ,p_last_updated_by
417 ,p_last_update_login
418 ,p_invoice_type --added by subba for R12.1
419 --Yao Zhang add begin for bug#8605196 consolidate invoices
420 ,p_consolidation_flag
421 ,p_consolidation_id
422 ,p_consolidation_trx_num
423 --Yao Zhang add end for bug#8605196 consolidate invoices
424 );
425
426 --In case of insert failed, raise error
427 OPEN c;
428 FETCH c INTO p_row_id;
429 IF (c%NOTFOUND)
430 THEN
431 CLOSE c;
432 RAISE NO_DATA_FOUND;
433 END IF; --(c%NOTFOUND)
434 CLOSE C;
435 --The following code is add by Yao Zhang for exception handle
436 EXCEPTION
437 WHEN OTHERS THEN
438 fnd_file.PUT_LINE(fnd_file.LOG,'Exception from header insert row'||SQLCODE || SQLERRM);
439 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
440 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
441 G_MODULE_PREFIX || l_procedure_name ||
442 '. OTHER_EXCEPTION ',
443 'Unknown error' || SQLCODE || SQLERRM);
444
445 END IF;
446 RAISE;
447 --log for debug
448 IF( l_proc_level >= l_dbg_level)
449 THEN
450
451 FND_LOG.String(l_proc_level
452 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
453 ,'Exit procedure'
454 );
455
456 END IF; --( l_proc_level >= l_dbg_level)
457
458 END Insert_Row;
459
460 --==========================================================================
461 -- PROCEDURE NAME:
462 --
463 -- Update_Row Public
464 --
465 -- DESCRIPTION:
466 --
467 -- This procedure is used to update data in table AR_GTA_TRX_HEADERS_ALL
468 -- according to parameters passed in
469 --
470 -- PARAMETERS:
471 -- In: p_ra_gl_date GL date
472 -- p_ra_gl_period GL Period
473 -- p_set_of_books_id Identifier of a GL book
474 -- p_bill_to_customer_id Identifier of bill to customer
475 -- p_bill_to_customer_number Customer number
476 -- p_bill_to_customer_name Customer name
477 -- p_source Source of GTA invoice, alternative
478 -- value is 'AR' or 'GT'
479 -- p_org_id Identifier of operating unit
480 -- p_legal_entity_id Identifier of legal entity id
481 -- p_rule_header_id Identifier of transfer rule
482 -- p_gta_trx_header_id Identifier of GTA invoice header
483 -- p_gta_trx_numbe Number of GTA invoice
484 -- p_group_number Identifier of group split
485 -- p_version Version of a GTA invoice
486 -- p_latest_version_flag Flag to identify if a GTA invoice
487 -- is the one with maximum version number
488 -- p_transaction_date AR transaction date
489 -- p_ra_trx_id Identifier of AR transaction
490 -- p_ra_trx_number Number of AR transaction
491 -- p_description Description of a GTA invoice
492 -- p_customer_address Customer address
493 -- p_customer_phone Phone number of a customer
494 -- p_customer_address_phone Address and phone number of a customer
495 -- p_bank_account_name Bank account name
496 -- p_bank_account_number Bank account number
497 -- p_bank_account_name_number Bank account name and number
498 -- p_fp_tax_registration_number Tax Registration Number of First Party
499 -- p_tp_tax_registration_number Tax Registration Number of Third Party
500 -- p_ra_currency_code Currency code of an AR transaction
501 -- p_conversion_type Conversion type of currency
502 -- p_conversion_date Currency conversion date
503 -- p_conversion_rate Exchange rate of currency
504 -- p_gta_batch_number Batch number of GTA invoices
505 -- p_gt_invoice_number GT invoice number
506 -- p_gt_invoice_date GT invoice date
507 -- p_gt_invoice_net_amount Net amount of a GT invoice
508 -- P_gt_invoice_tax_amount Tax amount of a GT invoice
509 -- p_status Status of GTA invoice or GT invoice,
510 -- acceptable values is :'DRAFT',
511 -- 'GENERATED',
512 -- 'CANCELLED',
513 -- 'FAILED',
514 -- 'COMPLETED'
515 -- p_sales_list_flag Flag to identify if a GTA invoice is
516 -- sale list enbaled
517 -- p_cancel_flag Flag to identify if a GT invoice is
518 -- Cancelled or not
519 -- p_gt_invoice_type Type of GT invoice
520 -- p_gt_invoice_class Class of GT invoice
521 -- p_gt_tax_month Tax month of GT invoice
522 -- p_issuer_name Issuer name of GT invoice
523 -- p_reviewer_name Reviewer name of GT invoice
524 -- p_payee_name Payee name of GT invoice
525 -- p_tax_code Tax code
526 -- p_generator_id Generator id
527 -- p_export_request_id Conc request id of GTA invoice
528 -- export program
529 -- p_request_id Conc request id
530 -- p_program_application_id Program application id
531 -- p_program_id Program id
532 -- p_program_update_date Program update date
533 -- p_attribute_category Attribute category of
534 -- descriptive flexfield
535 -- p_attribute1 Attribute1
536 -- p_attribute2 Attribute2
537 -- p_attribute3 Attribute3
538 -- p_attribute4 Attribute4
539 -- p_attribute5 Attribute5
540 -- p_attribute6 Attribute6
541 -- p_attribute7 Attribute7
542 -- p_attribute8 Attribute8
543 -- p_attribute9 Attribute9
544 -- p_attribute10 Attribute10
545 -- p_attribute11 Attribute11
546 -- p_attribute12 Attribute12
547 -- p_attribute13 Attribute13
548 -- p_attribute14 Attribute14
549 -- p_attribute15 Attribute15
550 -- p_creation_date Creation date
551 -- p_created_by Identifier of user that creates
552 -- the record
553 -- p_last_update_date Last update date of the record
554 -- p_last_updated_by Last update by
555 -- p_last_update_login Last update login
556 -- p_invoice_type Invoice type --added by Subba for R12.1.
557 --
558 -- In Out: p_row_id Row id of a table record
559 --
560 --
561 -- DESIGN REFERENCES:
562 -- GTA_Workbench_Form_TD.doc
563 --
564 -- CHANGE HISTORY:
565 --
566 -- 17-MAY-2005 Donghai Wang created
567 -- 26-SEP-2005 Donghai Wang add three parameters:
568 -- p_legal_entity_id
569 -- p_fp_tax_registration_number
570 -- p_tp_tax_registration_number
571 -- 02-JAN-2008 Subba added p_invoice_type parameter
572 --===========================================================================
573 PROCEDURE Update_Row
574 (p_row_id IN OUT NOCOPY VARCHAR2
575 ,p_ra_gl_date IN DATE
576 ,P_ra_gl_period IN VARCHAR
577 ,p_set_of_books_id IN NUMBER
578 ,p_bill_to_customer_id IN NUMBER
579 ,p_bill_to_customer_number IN VARCHAR2
580 ,p_bill_to_customer_name IN VARCHAR2
581 ,p_source IN VARCHAR2
582 ,p_org_id IN NUMBER
583 ,p_legal_entity_id IN NUMBER
584 ,p_rule_header_id IN NUMBER
585 ,p_gta_trx_header_id IN NUMBER
586 ,p_gta_trx_number IN VARCHAR2
587 ,p_group_number IN NUMBER
588 ,p_version IN NUMBER
589 ,p_latest_version_flag IN VARCHAR2
590 ,p_transaction_date IN DATE
591 ,p_ra_trx_id IN NUMBER
592 ,p_ra_trx_number IN VARCHAR2
593 ,p_description IN VARCHAR2
594 ,p_customer_address IN VARCHAR2
595 ,p_customer_phone IN VARCHAR2
596 ,p_customer_address_phone IN VARCHAR2
597 ,p_bank_account_name IN VARCHAR2
598 ,p_bank_account_number IN VARCHAR2
599 ,p_bank_account_name_number IN VARCHAR2
600 ,p_fp_tax_registration_number IN VARCHAR2
601 ,p_tp_tax_registration_number IN VARCHAR2
602 ,p_ra_currency_code IN VARCHAR2
603 ,p_conversion_type IN VARCHAR2
604 ,p_conversion_date IN DATE
605 ,p_conversion_rate IN NUMBER
606 ,p_gta_batch_number IN VARCHAR2
607 ,p_gt_invoice_number IN VARCHAR2
608 ,p_gt_invoice_date IN DATE
609 ,p_gt_invoice_net_amount IN NUMBER
610 ,P_gt_invoice_tax_amount IN NUMBER
611 ,p_status IN VARCHAR2
612 ,p_sales_list_flag IN VARCHAR2
613 ,p_cancel_flag IN VARCHAR2
614 ,p_gt_invoice_type IN VARCHAR2
615 ,p_gt_invoice_class IN VARCHAR2
616 ,p_gt_tax_month IN VARCHAR2
617 ,p_issuer_name IN VARCHAR2
618 ,p_reviewer_name IN VARCHAR2
619 ,p_payee_name IN VARCHAR2
620 ,p_tax_code IN VARCHAR2
621 ,p_tax_rate IN NUMBER
622 ,p_generator_id IN NUMBER
623 ,p_export_request_id IN NUMBER
624 ,p_request_id IN NUMBER
625 ,p_program_application_id IN NUMBER
626 ,p_program_id IN NUMBER
627 ,p_program_update_date IN DATE
628 ,p_attribute_category IN VARCHAR2
629 ,p_attribute1 IN VARCHAR2
630 ,p_attribute2 IN VARCHAR2
631 ,p_attribute3 IN VARCHAR2
632 ,p_attribute4 IN VARCHAR2
633 ,p_attribute5 IN VARCHAR2
634 ,p_attribute6 IN VARCHAR2
635 ,p_attribute7 IN VARCHAR2
636 ,p_attribute8 IN VARCHAR2
637 ,p_attribute9 IN VARCHAR2
638 ,p_attribute10 IN VARCHAR2
639 ,p_attribute11 IN VARCHAR2
640 ,p_attribute12 IN VARCHAR2
641 ,p_attribute13 IN VARCHAR2
642 ,p_attribute14 IN VARCHAR2
643 ,p_attribute15 IN VARCHAR2
644 ,p_creation_date IN DATE
645 ,p_created_by IN NUMBER
646 ,p_last_update_date IN DATE
647 ,p_last_updated_by IN NUMBER
648 ,p_last_update_login IN NUMBER
649 ,p_invoice_type IN VARCHAR2 --added by subba for R12.1
650 )
651 IS
652 l_procedure_name VARCHAR2(100) :='Update_Row';
653 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
657 --log for debug
654 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
655 BEGIN
656
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 ,'Enter procedure'
664 );
665
666 END IF; --( l_proc_level >= l_dbg_level)
667
668 --Update data on table AR_GTA_TRX_HEADERS_ALL
669 UPDATE ar_gta_trx_headers_all
670 SET
671 ra_gl_date = p_ra_gl_date
672 ,ra_gl_period = p_ra_gl_period
673 ,set_of_books_id = p_set_of_books_id
674 ,bill_to_customer_id = p_bill_to_customer_id
675 ,bill_to_customer_number = p_bill_to_customer_number
676 ,bill_to_customer_name = p_bill_to_customer_name
677 ,source = p_source
678 ,org_id = p_org_id
679 ,legal_entity_id = p_legal_entity_id
680 ,rule_header_id = p_rule_header_id
681 ,gta_trx_header_id = p_gta_trx_header_id
682 ,gta_trx_number = p_gta_trx_number
683 ,group_number = p_group_number
684 ,version = p_version
685 ,latest_version_flag = p_latest_version_flag
686 ,transaction_date = p_transaction_date
687 ,ra_trx_id = p_ra_trx_id
688 ,ra_trx_number = p_ra_trx_number
689 ,description = p_description
690 ,customer_address = p_customer_address
691 ,customer_phone = p_customer_phone
692 ,customer_address_phone = p_customer_address_phone
693 ,bank_account_name = p_bank_account_name
694 ,bank_account_number = p_bank_account_number
695 ,bank_account_name_number = p_bank_account_name_number
696 ,fp_tax_registration_number = p_fp_tax_registration_number
697 ,tp_tax_registration_number = p_tp_tax_registration_number
698 ,ra_currency_code = p_ra_currency_code
699 ,conversion_type = p_conversion_type
700 ,conversion_date = p_conversion_date
701 ,conversion_rate = p_conversion_rate
702 ,gta_batch_number = p_gta_batch_number
703 ,gt_invoice_number = p_gt_invoice_number
704 ,gt_invoice_date = p_gt_invoice_date
705 ,gt_invoice_net_amount = p_gt_invoice_net_amount
706 ,gt_invoice_tax_amount = P_gt_invoice_tax_amount
707 ,status = p_status
708 ,sales_list_flag = p_sales_list_flag
709 ,cancel_flag = p_cancel_flag
710 ,gt_invoice_type = p_gt_invoice_type
711 ,gt_invoice_class = p_gt_invoice_class
712 ,gt_tax_month = p_gt_tax_month
713 ,issuer_name = p_issuer_name
714 ,reviewer_name = p_reviewer_name
715 ,payee_name = p_payee_name
716 ,tax_code = p_tax_code
717 ,tax_rate = p_tax_rate
718 ,generator_id = p_generator_id
719 ,export_request_id = p_export_request_id
720 ,request_id = p_request_id
724 ,attribute_category = p_attribute_category
721 ,program_application_id = p_program_application_id
722 ,program_id = p_program_id
723 ,program_update_date = p_program_update_date
725 ,attribute1 = p_attribute1
726 ,attribute2 = p_attribute2
727 ,attribute3 = p_attribute3
728 ,attribute4 = p_attribute4
729 ,attribute5 = p_attribute5
730 ,attribute6 = p_attribute6
731 ,attribute7 = p_attribute7
732 ,attribute8 = p_attribute8
733 ,attribute9 = p_attribute9
734 ,attribute10 = p_attribute10
735 ,attribute11 = p_attribute11
736 ,attribute12 = p_attribute12
737 ,attribute13 = p_attribute13
738 ,attribute14 = p_attribute14
739 ,attribute15 = p_attribute15
740 ,creation_date = p_creation_date
741 ,created_by = p_created_by
742 ,last_update_date = p_last_update_date
743 ,last_updated_by = p_last_updated_by
744 ,last_update_login = p_last_update_login
745 ,invoice_type = p_invoice_type --added by subba for R12.1
746 WHERE ROWID=p_row_id;
747
748 --In case of update failed, raise error
749 IF (SQL%NOTFOUND)
750 THEN
751 RAISE NO_DATA_FOUND;
752 END IF; --(SQL%NOTFOUND)
753
754 --log for debug
755 IF( l_proc_level >= l_dbg_level)
756 THEN
757
758 FND_LOG.String(l_proc_level
759 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
760 ,'Exit procedure'
761 );
762
763 END IF; --( l_proc_level >= l_dbg_level)
764
765
766 END Update_Row;
767
768
769
770 --==========================================================================
771 -- PROCEDURE NAME:
772 --
773 -- Lock_Row Public
774 --
775 -- DESCRIPTION:
776 --
777 -- This procedure is used to update implement lock on row level on table
778 -- AR_GTA_TRX_HEADERS_ALL
779 --
780 -- PARAMETERS:
781 -- In: p_ra_gl_date GL date
782 -- p_ra_gl_period GL Period
783 -- p_set_of_books_id Identifier of a GL book
784 -- p_bill_to_customer_id Identifier of bill to customer
785 -- p_bill_to_customer_number Customer number
786 -- p_bill_to_customer_name Customer name
787 -- p_source Source of GTA invoice, alternative
788 -- value is 'AR' or 'GT'
789 -- p_org_id Identifier of operating unit
790 -- p_legal_entity_id Idnetifier of Legal Entity
791 -- p_rule_header_id Identifier of transfer rule
792 -- p_gta_trx_header_id identifier of GTA invoice header
793 -- p_gta_trx_numbe Number of GTA invoice
794 -- p_group_number Identifier of group split
795 -- p_version Version of a GTA invoice
796 -- p_latest_version_flag Flag to identify if a GTA invoice
797 -- is the one with maximum version number
798 -- p_transaction_date AR transaction date
799 -- p_ra_trx_id Identifier of AR transaction
800 -- p_ra_trx_number Number of AR transaction
801 -- p_description Description of a GTA invoice
802 -- p_customer_address Customer address
803 -- p_customer_phone Phone number of a customer
804 -- p_customer_address_phone Address and phone number of a customer
805 -- p_bank_account_name Bank account name
806 -- p_bank_account_number Bank account number
807 -- p_bank_account_name_number Bank account name and number
808 -- p_fp_tax_registration_number Tax Registration Number of First Party
809 -- p_tp_tax_registration_number Tax Registration Number of Third Party
810 -- p_ra_currency_code Currency code of an AR transaction
811 -- p_conversion_type Conversion type of currency
812 -- p_conversion_date Currency conversion date
813 -- p_conversion_rate Exchange rate of currency
814 -- p_gta_batch_number Batch number of GTA invoices
815 -- p_gt_invoice_number GT invoice number
816 -- p_gt_invoice_date GT invoice date
817 -- p_gt_invoice_net_amount Net amount of a GT invoice
818 -- P_gt_invoice_tax_amount Tax amount of a GT invoice
819 -- p_status Status of GTA invoice or GT invoice,
820 -- acceptable values is :'DRAFT',
821 -- 'GENERATED',
822 -- 'CANCELLED',
823 -- 'FAILED',
824 -- 'COMPLETED'
825 -- p_sales_list_flag Flag to identify if a GTA invoice is
826 -- sale list enbaled
827 -- p_cancel_flag Flag to identify if a GT invoice is
831 -- p_gt_tax_month Tax month of GT invoice
828 -- Cancelled or not
829 -- p_gt_invoice_type Type of GT invoice
830 -- p_gt_invoice_class Class of GT invoice
832 -- p_issuer_name Issuer name of GT invoice
833 -- p_reviewer_name Reviewer name of GT invoice
834 -- p_payee_name Payee name of GT invoice
835 -- p_tax_code Tax code
836 -- p_generator_id Generator id
837 -- p_export_request_id Conc request id of GTA invoice
838 -- export program
842 -- p_program_update_date Program update date
839 -- p_request_id Conc request id
840 -- p_program_application_id Program application id
841 -- p_program_id Program id
843 -- p_attribute_category Attribute category of
844 -- descriptive flexfield
845 -- p_attribute1 Attribute1
846 -- p_attribute2 Attribute2
847 -- p_attribute3 Attribute3
848 -- p_attribute4 Attribute4
849 -- p_attribute5 Attribute5
850 -- p_attribute6 Attribute6
851 -- p_attribute7 Attribute7
852 -- p_attribute8 Attribute8
853 -- p_attribute9 Attribute9
854 -- p_attribute10 Attribute10
855 -- p_attribute11 Attribute11
856 -- p_attribute12 Attribute12
857 -- p_attribute13 Attribute13
858 -- p_attribute14 Attribute14
859 -- p_attribute15 Attribute15
860 -- p_creation_date Creation date
861 -- p_created_by Identifier of user that creates
862 -- the record
863 -- p_last_update_date Last update date of the record
864 -- p_last_updated_by Last update by
865 -- p_last_update_login Last update login
866 -- p_invoice_type Invoice type --added by subba for R12.1
867 --
868 -- In Out: p_row_id Row id of a table record
869 --
870 --
871 -- DESIGN REFERENCES:
872 -- GTA_Workbench_Form_TD.doc
873 --
874 -- CHANGE HISTORY:
875 --
876 -- 17-MAY-2005 Donghai Wang created
877 -- 26-SEP-2005 Donghai Wang add three parameters:
878 -- p_legal_entity_id
879 -- p_fp_tax_registration_number
880 -- p_tp_tax_registration_number
881 -- 02-JAN-2008 Subba added p_invoice_type parameter.
882 -- 13-Jul-2009 Allen Yang modified for bug 8605196: ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
883 --===========================================================================
884 PROCEDURE Lock_Row
885 (p_row_id IN OUT NOCOPY VARCHAR2
886 ,p_ra_gl_date IN DATE
887 ,P_ra_gl_period IN VARCHAR
888 ,p_set_of_books_id IN NUMBER
889 ,p_bill_to_customer_id IN NUMBER
890 ,p_bill_to_customer_number IN VARCHAR2
891 ,p_bill_to_customer_name IN VARCHAR2
892 ,p_source IN VARCHAR2
893 ,p_org_id IN NUMBER
894 ,p_legal_entity_id IN NUMBER
895 ,p_rule_header_id IN NUMBER
896 ,p_gta_trx_header_id IN NUMBER
897 ,p_gta_trx_number IN VARCHAR2
898 ,p_group_number IN NUMBER
899 ,p_version IN NUMBER
900 ,p_latest_version_flag IN VARCHAR2
901 ,p_transaction_date IN DATE
902 ,p_ra_trx_id IN NUMBER
903 ,p_ra_trx_number IN VARCHAR2
904 ,p_description IN VARCHAR2
905 ,p_customer_address IN VARCHAR2
906 ,p_customer_phone IN VARCHAR2
907 ,p_customer_address_phone IN VARCHAR2
908 ,p_bank_account_name IN VARCHAR2
909 ,p_bank_account_number IN VARCHAR2
910 ,p_bank_account_name_number IN VARCHAR2
911 ,p_fp_tax_registration_number IN VARCHAR2
912 ,p_tp_tax_registration_number IN VARCHAR2
913 ,p_ra_currency_code IN VARCHAR2
914 ,p_conversion_type IN VARCHAR2
915 ,p_conversion_date IN DATE
916 ,p_conversion_rate IN NUMBER
917 ,p_gta_batch_number IN VARCHAR2
918 ,p_gt_invoice_number IN VARCHAR2
919 ,p_gt_invoice_date IN DATE
920 ,p_gt_invoice_net_amount IN NUMBER
921 ,P_gt_invoice_tax_amount IN NUMBER
922 ,p_status IN VARCHAR2
923 ,p_sales_list_flag IN VARCHAR2
924 ,p_cancel_flag IN VARCHAR2
925 ,p_gt_invoice_type IN VARCHAR2
926 ,p_gt_invoice_class IN VARCHAR2
927 ,p_gt_tax_month IN VARCHAR2
928 ,p_issuer_name IN VARCHAR2
929 ,p_reviewer_name IN VARCHAR2
930 ,p_payee_name IN VARCHAR2
931 ,p_tax_code IN VARCHAR2
932 ,p_tax_rate IN NUMBER
933 ,p_generator_id IN NUMBER
934 ,p_export_request_id IN NUMBER
935 ,p_request_id IN NUMBER
936 ,p_program_application_id IN NUMBER
937 ,p_program_id IN NUMBER
938 ,p_program_update_date IN DATE
939 ,p_attribute_category IN VARCHAR2
940 ,p_attribute1 IN VARCHAR2
941 ,p_attribute2 IN VARCHAR2
942 ,p_attribute3 IN VARCHAR2
943 ,p_attribute4 IN VARCHAR2
944 ,p_attribute5 IN VARCHAR2
945 ,p_attribute6 IN VARCHAR2
946 ,p_attribute7 IN VARCHAR2
947 ,p_attribute8 IN VARCHAR2
948 ,p_attribute9 IN VARCHAR2
949 ,p_attribute10 IN VARCHAR2
950 ,p_attribute11 IN VARCHAR2
951 ,p_attribute12 IN VARCHAR2
952 ,p_attribute13 IN VARCHAR2
953 ,p_attribute14 IN VARCHAR2
954 ,p_attribute15 IN VARCHAR2
955 ,p_creation_date IN DATE
956 ,p_created_by IN NUMBER
957 ,p_last_update_date IN DATE
958 ,p_last_updated_by IN NUMBER
959 ,p_last_update_login IN NUMBER
960 ,p_invoice_type IN VARCHAR2 --added by subba for R12.1
961 )
962 IS
963 l_procedure_name VARCHAR2(100) :='Lock_Row';
964 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
965 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
966
967 CURSOR c IS
968 SELECT
969 *
970 FROM
971 ar_gta_trx_headers_all
972 WHERE ROWID=p_row_id
973 FOR UPDATE OF gta_trx_header_id NOWAIT;
974
975 recinfo c%ROWTYPE;
976 BEGIN
977
978 --log for debug
979 IF( l_proc_level >= l_dbg_level)
980 THEN
981
982 FND_LOG.String(l_proc_level
983 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
984 ,'Begin procedure'
985 );
986
990 --on that record,then raise error to form
987 END IF; --( l_proc_level >= l_dbg_level)
988
989 --If a record has been deleted as form tries to excute dml operation
991 OPEN c;
992 FETCH c INTO recinfo;
993 IF (c%NOTFOUND)
994 THEN
995 CLOSE c;
996 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
997 APP_EXCEPTION.Raise_Exception;
998 END IF; --(c%NOTFOUND)
999 CLOSE c;
1000
1001 --To judge if a record has been changed by other programs as the form
1002 --tries to execute DML operation on that record,if 'Yes', raise error,
1003 --else the form will be able to do DML operation on the record.
1004 IF (
1005 (recinfo.ra_gl_date=p_ra_gl_date)
1006 AND
1007 (rtrim(recinfo.ra_gl_period)=p_ra_gl_period)
1008 AND
1009 (recinfo.set_of_books_id=p_set_of_books_id)
1010 AND
1011 (recinfo.bill_to_customer_id=p_bill_to_customer_id)
1012 AND
1013 (
1014 (rtrim(recinfo.bill_to_customer_number)=p_bill_to_customer_number)
1015 OR
1016 (
1017 (rtrim(recinfo.bill_to_customer_number) IS NULL)
1018 AND
1019 (p_bill_to_customer_number IS NULL)
1020 )
1021 )
1022 AND
1023 (rtrim(recinfo.bill_to_customer_name)=p_bill_to_customer_name)
1024 AND
1025 (rtrim(recinfo.SOURCE)=p_source)
1026 AND
1027 (recinfo.org_id=p_org_id)
1028 AND
1029 (recinfo.legal_entity_id=p_legal_entity_id)
1030 -- modified by Allen Yang 13/Jul/2009 for bug 8605196: ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
1031 AND
1032 --(recinfo.rule_header_id=p_rule_header_id)
1033 (
1034 (recinfo.rule_header_id=p_rule_header_id)
1035 OR
1036 (
1037 (recinfo.rule_header_id IS NULL)
1038 AND
1039 (p_rule_header_id IS NULL)
1040 )
1041 )
1042 -- end modified by allen
1043 AND
1044 (recinfo.gta_trx_header_id=p_gta_trx_header_id)
1045 AND
1046 (rtrim(recinfo.gta_trx_number)=p_gta_trx_number)
1047 AND
1048 (
1049 (rtrim(recinfo.group_number)=p_group_number)
1050 OR
1051 (
1052 (rtrim(recinfo.group_number) IS NULL)
1053 AND
1054 (p_group_number IS NULL)
1055 )
1056 )
1057 AND
1058 (recinfo.version=p_version)
1059 AND
1060 (rtrim(recinfo.latest_version_flag)=p_latest_version_flag)
1061 AND
1062 (recinfo.transaction_date=p_transaction_date)
1063 -- modified by Allen Yang 13/Jul/2009 for bug 8605196: ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
1064 AND
1065 --(recinfo.ra_trx_id=p_ra_trx_id)
1066 (
1067 (recinfo.ra_trx_id=p_ra_trx_id)
1068 OR
1069 (
1070 (recinfo.ra_trx_id IS NULL)
1071 AND
1072 (p_ra_trx_id IS NULL)
1073 )
1074 )
1075 -- end modified by allen
1076 AND
1077 (
1078 (rtrim(recinfo.ra_trx_number)=p_ra_trx_number)
1079 OR
1080 (
1081 (rtrim(recinfo.ra_trx_number) IS NULL)
1082 AND
1083 (p_ra_trx_number IS NULL)
1084 )
1085 )
1086 AND
1087 (
1088 (rtrim(recinfo.description)=p_description)
1089 OR
1090 (
1091 (rtrim(recinfo.description) IS NULL)
1092 AND
1093 (p_description IS NULL)
1094 )
1095 )
1096 AND
1097 (
1098 (rtrim(recinfo.customer_address)=p_customer_address)
1099 OR
1100 (
1101 (rtrim(recinfo.customer_address) IS NULL)
1102 AND
1103 (p_customer_address IS NULL)
1104 )
1105 )
1106 AND
1107 (
1108 (rtrim(recinfo.customer_phone)=p_customer_phone)
1109 OR
1110 (
1111 (rtrim(recinfo.customer_phone) IS NULL)
1112 AND
1113 (p_customer_phone IS NULL)
1114 )
1115 )
1116 AND
1117 (
1118 (rtrim(recinfo.customer_address_phone)=rtrim(p_customer_address_phone))
1119 OR
1120 (
1121 (recinfo.customer_address_phone IS NULL)
1122 AND
1123 (p_customer_address_phone IS NULL)
1124 )
1125 )
1126 AND
1127 (
1128 (rtrim(recinfo.bank_account_name)=p_bank_account_name)
1129 OR
1130 (
1131 (rtrim(recinfo.bank_account_name) IS NULL)
1132 AND
1133 (p_bank_account_name IS NULL)
1134 )
1135 )
1136 AND
1137 (
1138 (rtrim(recinfo.bank_account_number)=p_bank_account_number)
1139 OR
1140 (
1141 (rtrim(recinfo.bank_account_number) IS NULL)
1142 AND
1143 (p_bank_account_number IS NULL)
1144 )
1145 )
1146 AND
1147 (
1148 (rtrim(recinfo.bank_account_name_number)=p_bank_account_name_number)
1149 OR
1150 (
1151 (rtrim(recinfo.bank_account_name_number) IS NULL)
1152 AND
1153 (p_bank_account_name_number IS NULL)
1154 )
1155 )
1156 AND
1157 (
1158 (rtrim(recinfo.fp_tax_registration_number)=p_fp_tax_registration_number)
1159 OR
1160 (
1161 (rtrim(recinfo.fp_tax_registration_number) IS NULL)
1162 AND
1163 (p_fp_tax_registration_number IS NULL)
1164 )
1165 )
1166 AND
1167 (
1168 (rtrim(recinfo.tp_tax_registration_number)=p_tp_tax_registration_number)
1169 OR
1170 (
1171 (rtrim(recinfo.tp_tax_registration_number) IS NULL)
1172 AND
1173 (p_tp_tax_registration_number IS NULL)
1174 )
1175 )
1176 AND
1177 (
1178 (rtrim(recinfo.ra_currency_code)=p_ra_currency_code)
1179 OR
1180 (
1181 (rtrim(recinfo.ra_currency_code) IS NULL)
1182 AND
1183 (p_ra_currency_code IS NULL)
1184 )
1185 )
1186 AND
1187 (
1188 (rtrim(recinfo.conversion_type)=p_conversion_type)
1189 OR
1190 (
1191 (rtrim(recinfo.conversion_type) IS NULL)
1192 AND
1193 (p_conversion_type IS NULL)
1194 )
1195 )
1196 AND
1197 (
1198 (recinfo.conversion_date=p_conversion_date)
1199 OR
1200 (
1201 (recinfo.conversion_date IS NULL)
1202 AND
1203 (p_conversion_date IS NULL)
1204 )
1205 )
1206 AND
1207 (
1208 (recinfo.conversion_rate=p_conversion_rate)
1209 OR
1210 (
1211 (recinfo.conversion_rate IS NULL)
1212 AND
1213 (p_conversion_rate IS NULL)
1214 )
1215 )
1216 AND
1217 (
1218 (rtrim(recinfo.gta_batch_number)=p_gta_batch_number)
1219 OR
1220 (
1221 (rtrim(recinfo.gta_batch_number) IS NULL)
1222 AND
1223 (p_gta_batch_number IS NULL)
1224 )
1225 )
1226 AND
1227 (
1228 (rtrim(recinfo.gt_invoice_number)=p_gt_invoice_number)
1229 OR
1230 (
1231 (rtrim(recinfo.gt_invoice_number) IS NULL)
1232 AND
1233 (p_gt_invoice_number IS NULL)
1234 )
1235 )
1236 AND
1237 (
1238 (recinfo.gt_invoice_date=p_gt_invoice_date)
1239 OR
1240 (
1241 (recinfo.gt_invoice_date IS NULL)
1242 AND
1243 (p_gt_invoice_date IS NULL)
1244 )
1245 )
1246 AND
1247 (
1248 (recinfo.gt_invoice_net_amount=p_gt_invoice_net_amount)
1249 OR
1250 (
1251 (recinfo.gt_invoice_net_amount IS NULL)
1252 AND
1253 (p_gt_invoice_net_amount IS NULL)
1254 )
1255 )
1256 AND
1257 (
1258 (recinfo.gt_invoice_tax_amount=p_gt_invoice_tax_amount)
1259 OR
1260 (
1261 (recinfo.gt_invoice_tax_amount IS NULL)
1262 AND
1263 (p_gt_invoice_tax_amount IS NULL)
1264 )
1265 )
1266 AND
1267 (rtrim(recinfo.status)=p_status)
1268 AND
1269 (
1270 (rtrim(recinfo.sales_list_flag)=p_sales_list_flag)
1271 OR
1272 (
1273 (rtrim(recinfo.sales_list_flag) IS NULL)
1274 AND
1275 (p_sales_list_flag IS NULL)
1276 )
1277 )
1278 AND
1279 (
1280 (recinfo.cancel_flag=p_cancel_flag)
1281 OR
1282 (
1283 (recinfo.cancel_flag IS NULL)
1284 AND
1285 (p_cancel_flag IS NULL)
1286 )
1287 )
1288 AND
1289 (
1290 (rtrim(recinfo.gt_invoice_type)=p_gt_invoice_type)
1291 OR
1292 (
1293 (rtrim(recinfo.gt_invoice_type) IS NULL)
1294 AND
1295 (p_gt_invoice_type IS NULL)
1296 )
1297 )
1298 AND
1299 (
1300 (rtrim(recinfo.gt_invoice_class)=p_gt_invoice_class)
1301 OR
1302 (
1303 (rtrim(recinfo.gt_invoice_class) IS NULL)
1304 AND
1305 (p_gt_invoice_class IS NULL)
1306 )
1307 )
1308 AND
1309 (
1310 (rtrim(recinfo.gt_tax_month)=p_gt_tax_month)
1311 OR
1312 (
1313 (rtrim(recinfo.gt_tax_month) IS NULL)
1314 AND
1315 (p_gt_tax_month IS NULL)
1316 )
1317 )
1318 AND
1319 (
1320 (rtrim(recinfo.issuer_name)=p_issuer_name)
1321 OR
1322 (
1323 (rtrim(recinfo.issuer_name) IS NULL)
1324 AND
1325 (p_issuer_name IS NULL)
1326 )
1327 )
1328 AND
1329 (
1330 (rtrim(recinfo.reviewer_name)=p_reviewer_name)
1331 OR
1332 (
1333 (rtrim(recinfo.reviewer_name) IS NULL)
1334 AND
1335 (p_reviewer_name IS NULL)
1336 )
1337 )
1338 AND
1339 (
1340 (rtrim(recinfo.payee_name)=p_payee_name)
1341 OR
1342 (
1343 (rtrim(recinfo.payee_name) IS NULL)
1344 AND
1345 (p_payee_name IS NULL)
1346 )
1347 )
1348 AND
1349 (
1350 (recinfo.tax_code=p_tax_code)
1351 OR
1352 (
1353 (rtrim(recinfo.tax_code) IS NULL)
1354 AND
1355 (p_tax_code IS NULL)
1356 )
1357 )
1358 AND
1359 (
1360 (recinfo.tax_rate=p_tax_rate)
1361 OR
1362 (
1363 (recinfo.tax_rate IS NULL)
1364 AND
1365 (p_tax_rate IS NULL)
1366 )
1367 )
1368 AND
1369 (
1370 (recinfo.generator_id=p_generator_id)
1371 OR
1372 (
1373 (recinfo.generator_id IS NULL)
1374 AND
1375 (p_generator_id IS NULL)
1376 )
1377 )
1378 AND
1379 (
1380 (recinfo.export_request_id=p_export_request_id)
1381 OR
1382 (
1383 (recinfo.export_request_id IS NULL)
1384 AND
1385 (p_export_request_id IS NULL)
1386 )
1387 )
1388 AND
1389 (recinfo.creation_date=p_creation_date)
1390 AND
1391 (recinfo.created_by=p_created_by)
1392 AND
1393 (recinfo.last_update_date=p_last_update_date)
1394 AND
1395 (recinfo.last_updated_by=p_last_updated_by)
1396 AND
1397 (
1398 (recinfo.last_update_login=p_last_update_login)
1399 OR
1400 (
1401 (recinfo.last_update_login IS NULL)
1402 AND
1403 (p_last_update_login IS NULL)
1404 )
1405 )
1406 AND
1407 (
1408 (recinfo.request_id=p_request_id)
1409 OR
1410 (
1411 (recinfo.request_id IS NULL)
1412 AND
1413 (p_request_id IS NULL)
1414 )
1415 )
1416 AND
1417 (
1418 (recinfo.program_application_id=p_program_application_id)
1419 OR
1420 (
1421 (recinfo.program_application_id IS NULL)
1422 AND
1423 (p_program_application_id IS NULL)
1424 )
1425 )
1426 AND
1427 (
1428 (recinfo.program_id=p_program_id)
1429 OR
1430 (
1431 (recinfo.program_id IS NULL)
1432 AND
1433 (p_program_id IS NULL)
1434 )
1435 )
1436 AND
1437 (
1438 (recinfo.program_update_date=p_program_update_date)
1439 OR
1440 (
1441 (recinfo.program_update_date IS NULL)
1442 AND
1443 (p_program_update_date IS NULL)
1444 )
1445 )
1446 AND
1447 (
1448 (rtrim(recinfo.attribute_category)=p_attribute_category)
1449 OR
1450 (
1451 (rtrim(recinfo.attribute_category) IS NULL)
1452 AND
1453 (p_attribute_category IS NULL)
1454 )
1455 )
1456 AND
1457 (
1458 (rtrim(recinfo.attribute1)=p_attribute1)
1459 OR
1460 (
1461 (rtrim(recinfo.attribute1) IS NULL)
1462 AND
1463 (p_attribute1 IS NULL)
1464 )
1465 )
1466 AND
1467 (
1468 (rtrim(recinfo.attribute2)=p_attribute2)
1469 OR
1470 (
1471 (rtrim(recinfo.attribute2) IS NULL)
1472 AND
1473 (p_attribute2 IS NULL)
1474 )
1475 )
1476 AND
1477 (
1478 (rtrim(recinfo.attribute3)=p_attribute3)
1479 OR
1480 (
1481 (rtrim(recinfo.attribute3) IS NULL)
1482 AND
1483 (p_attribute3 IS NULL)
1484 )
1485 )
1486 AND
1487 (
1488 (rtrim(recinfo.attribute4)=p_attribute4)
1489 OR
1490 (
1491 (rtrim(recinfo.attribute4) IS NULL)
1492 AND
1493 (p_attribute4 IS NULL)
1494 )
1495 )
1496 AND
1497 (
1498 (rtrim(recinfo.attribute5)=p_attribute5)
1499 OR
1500 (
1501 (rtrim(recinfo.attribute5) IS NULL)
1502 AND
1503 (p_attribute5 IS NULL)
1504 )
1505 )
1506 AND
1507 (
1508 (rtrim(recinfo.attribute6)=p_attribute6)
1509 OR
1510 (
1511 (rtrim(recinfo.attribute6) IS NULL)
1512 AND
1513 (p_attribute6 IS NULL)
1514 )
1515 )
1516 AND
1517 (
1518 (rtrim(recinfo.attribute7)=p_attribute7)
1519 OR
1520 (
1521 (rtrim(recinfo.attribute7) IS NULL)
1522 AND
1523 (p_attribute7 IS NULL)
1524 )
1525 )
1526 AND
1527 (
1528 (rtrim(recinfo.attribute8)=p_attribute8)
1529 OR
1530 (
1531 (rtrim(recinfo.attribute8) IS NULL)
1532 AND
1533 (p_attribute8 IS NULL)
1534 )
1535 )
1536 AND
1537 (
1538 (rtrim(recinfo.attribute9)=p_attribute9)
1539 OR
1540 (
1541 (rtrim(recinfo.attribute9) IS NULL)
1542 AND
1543 (p_attribute9 IS NULL)
1544 )
1545 )
1546 AND
1547 (
1548 (rtrim(recinfo.attribute10)=p_attribute10)
1549 OR
1550 (
1551 (rtrim(recinfo.attribute10) IS NULL)
1552 AND
1553 (p_attribute10 IS NULL)
1554 )
1555 )
1556 AND
1557 (
1558 (rtrim(recinfo.attribute11)=p_attribute11)
1559 OR
1560 (
1561 (rtrim(recinfo.attribute11) IS NULL)
1562 AND
1563 (p_attribute11 IS NULL)
1564 )
1565 )
1566 AND
1567 (
1568 (rtrim(recinfo.attribute12)=p_attribute12)
1569 OR
1570 (
1571 (rtrim(recinfo.attribute12) IS NULL)
1572 AND
1573 (p_attribute12 IS NULL)
1574 )
1575 )
1576 AND
1577 (
1578 (rtrim(recinfo.attribute13)=p_attribute13)
1579 OR
1580 (
1581 (rtrim(recinfo.attribute13) IS NULL)
1582 AND
1583 (p_attribute13 IS NULL)
1584 )
1585 )
1586 AND
1587 (
1588 (rtrim(recinfo.attribute14)=p_attribute14)
1589 OR
1590 (
1591 (rtrim(recinfo.attribute14) IS NULL)
1592 AND
1593 (p_attribute14 IS NULL)
1594 )
1595 )
1596 AND
1597 (
1598 (rtrim(recinfo.attribute15)=p_attribute15)
1599 OR
1600 (
1601 (rtrim(recinfo.attribute15) IS NULL)
1602 AND
1603 (p_attribute15 IS NULL)
1604 )
1605 )
1606 AND (rtrim(recinfo.invoice_type) = p_invoice_type) --added by subba for R12.1
1607 )
1608 THEN
1609 RETURN;
1610 ELSE
1611 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1612 APP_EXCEPTION.Raise_Exception;
1613 END IF; --((recinfo.ra_gl_date=p_ra_gl_date) ...
1614
1615 --log for debug
1616 IF( l_proc_level >= l_dbg_level)
1617 THEN
1618
1619 FND_LOG.String(l_proc_level
1620 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
1621 ,'Exit procedure'
1622 );
1623
1624 END IF; --( l_proc_level >= l_dbg_level)
1625 END Lock_Row;
1626
1627 --==========================================================================
1628 -- PROCEDURE NAME:
1629 --
1630 -- Delete_Row Public
1631 --
1632 -- DESCRIPTION:
1633 --
1634 -- This procedure is used to delete record from table
1635 -- AR_GTA_TRX_HEADERS_ALL
1636 --
1637 -- PARAMETERS:
1638 -- In:
1639 --
1640 -- In Out: p_row_id Row id of a table record
1641 --
1642 --
1643 -- DESIGN REFERENCES:
1644 -- GTA_Workbench_Form_TD.doc
1645 --
1646 -- CHANGE HISTORY:
1647 --
1648 -- 17-MAY-2005 Donghai Wang created
1649 -- 13/Jul/2009 Allen Yang modified for bug 8605196:
1650 -- ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
1651 -- 05/Sep/2009 Allen Yang modified for bug 8882568
1652 --===========================================================================
1653 PROCEDURE Delete_Row
1654 (p_rowid IN OUT NOCOPY VARCHAR2
1655 )
1656 IS
1657 l_procedure_name VARCHAR2(100) :='Delete_Row';
1658 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1659 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1660 -- added by Allen Yang for bug 8605196: ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
1661 -----------------------------------------------------------------------------------
1662 l_consolidation_flag AR_GTA_TRX_HEADERS_ALL.CONSOLIDATION_FLAG%TYPE;
1663 l_consol_trx_num AR_GTA_TRX_HEADERS_ALL.CONSOLIDATION_TRX_NUM%TYPE;
1664 -----------------------------------------------------------------------------------
1665 BEGIN
1666
1667 --log for debug
1668 IF( l_proc_level >= l_dbg_level)
1669 THEN
1670
1671 FND_LOG.String(l_proc_level
1672 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
1673 ,'Begin procedure'
1674 );
1675
1676 END IF; --( l_proc_level >= l_dbg_level)
1677
1678 -- added by Allen Yang for bug 8605196: ENHANCEMENT FOR GOLDEN TAX ADAPTER R12.1.2
1682 , consolidation_flag
1679 ----------------------------------------------------------------------------------
1680 -- get current consolidation invoice number
1681 SELECT gta_trx_number
1683 INTO l_consol_trx_num
1684 ,l_consolidation_flag
1685 FROM AR_GTA_TRX_HEADERS_ALL
1686 WHERE rowid = p_rowid;
1687
1688 -- set all consolidated invoices as stats 'DRAFT' and consolidation_flag NULL
1689 IF NVL(l_consolidation_flag, ' ') = '0'
1690 THEN
1691 UPDATE AR_GTA_TRX_HEADERS_ALL
1692 SET STATUS = 'DRAFT'
1693 ,CONSOLIDATION_FLAG = NULL
1694 ,CONSOLIDATION_TRX_NUM = NULL
1695 -- modified by Allen Yang for bug #8882568 concurrency control 05-Sep-2009
1696 -------------------------------------------------------------------
1697 ,CONSOLIDATION_REQUEST_ID = NULL
1698 ,CONSOLIDATION_ID = NULL
1699 -------------------------------------------------------------------
1700 WHERE CONSOLIDATION_TRX_NUM = l_consol_trx_num
1701 AND CONSOLIDATION_FLAG = '1';
1702 END IF; -- NVL(l_consolidation_flag, ' ') = '0'
1703 --------------------------------------------------------------------------
1704
1705 --Delete row from table AR_GTA_TRX_HEADERS_ALL
1706 DELETE
1707 FROM
1708 ar_gta_trx_headers_all
1709 WHERE rowid = p_rowid;
1710
1711 --In case of delete failed,raise error
1712 IF (SQL%NOTFOUND)
1713 THEN
1714 RAISE NO_DATA_FOUND;
1715 END IF; --(SQL%NOTFOUND)
1716
1717 --log for debug
1718 IF( l_proc_level >= l_dbg_level)
1719 THEN
1720
1721 FND_LOG.String(l_proc_level
1722 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
1723 ,'Exit procedure'
1724 );
1725
1726 END IF; --( l_proc_level >= l_dbg_level)
1727
1728 END Delete_Row;
1729
1730 --==========================================================================
1731 -- PROCEDURE NAME:
1732 --
1733 -- Query_Row Public
1734 --
1735 -- DESCRIPTION:
1736 --
1737 -- This procedure is used to retrieve record by parameter p_header
1738 -- from table AR_GTA_TRX_HEADERS_ALL
1739 --
1740 -- PARAMETERS:
1741 -- In: p_header_id Identifier of GTA invoice header
1742 --
1743 -- Out: x_trx_header_rec record to store a row fetched from
1744 -- table AR_GTA_TRX_HEADERS_ALL
1745 --
1746 --
1747 -- DESIGN REFERENCES:
1748 -- GTA-Txt-Interface-TD.doc
1749 --
1750 -- CHANGE HISTORY:
1751 --
1752 -- 21-Jun-2005 Jogen Hu created
1753 -- 26-Sep-2005 Donghai Wang Add three columns 'LEGAL_ENTITY_ID'
1754 -- 'FP_TAX_REGISTRATION_NUMBER
1755 -- 'TP_TAX_REGISTRATON_NUMBER
1756 -- 02-JAN-2008 Subba Added new column 'invoice_type' for R12.1
1757 --===========================================================================
1758 PROCEDURE Query_Row
1759 (p_header_id IN NUMBER
1760 ,x_trx_header_rec OUT NOCOPY AR_GTA_TRX_UTIL.Trx_Header_Rec_Type
1761 )
1762 IS
1763 l_procedure_name VARCHAR2(100) :='Query_Row';
1764 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1765 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1766 BEGIN
1767
1768 --log for debug
1769 IF( l_proc_level >= l_dbg_level)
1770 THEN
1771
1772 FND_LOG.String(l_proc_level
1773 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
1774 ,'Begin procedure'
1775 );
1776
1777 END IF; --( l_proc_level >= l_dbg_level)
1778
1779 --Retrive data from AR_GTA_TRX_HEADERS_ALL and store them into PL/SQL record
1780 SELECT
1781 ra_gl_date
1782 ,ra_gl_period
1783 ,set_of_books_id
1784 ,bill_to_customer_id
1785 ,bill_to_customer_number
1786 ,bill_to_customer_name
1787 ,source
1788 ,org_id
1789 ,legal_entity_id
1790 ,rule_header_id
1791 ,gta_trx_header_id
1792 ,gta_trx_number
1793 ,group_number
1794 ,version
1795 ,latest_version_flag
1796 ,transaction_date
1797 ,ra_trx_id
1798 ,ra_trx_number
1799 ,description
1800 ,customer_address
1801 ,customer_phone
1802 ,customer_address_phone
1803 ,bank_account_name
1804 ,bank_account_number
1805 ,bank_account_name_number
1806 ,fp_tax_registration_number
1807 ,tp_tax_registration_number
1808 ,ra_currency_code
1809 ,conversion_type
1810 ,conversion_date
1811 ,conversion_rate
1812 ,gta_batch_number
1813 ,gt_invoice_number
1814 ,gt_invoice_date
1815 ,gt_invoice_net_amount
1816 ,gt_invoice_tax_amount
1817 ,status
1818 ,sales_list_flag
1819 ,cancel_flag
1820 ,gt_invoice_type
1821 ,gt_invoice_class
1822 ,gt_tax_month
1823 ,issuer_name
1824 ,reviewer_name
1825 ,payee_name
1826 ,tax_code
1827 ,tax_rate
1828 ,generator_id
1829 ,request_id
1830 ,program_application_id
1831 ,program_id
1832 ,program_update_date
1833 ,attribute_category
1834 ,attribute1
1835 ,attribute2
1836 ,attribute3
1837 ,attribute4
1838 ,attribute5
1839 ,attribute6
1840 ,attribute7
1841 ,attribute8
1842 ,attribute9
1843 ,attribute10
1844 ,attribute11
1845 ,attribute12
1846 ,attribute13
1847 ,attribute14
1848 ,attribute15
1849 ,creation_date
1850 ,created_by
1851 ,last_update_date
1852 ,last_updated_by
1853 ,last_update_login
1854 ,invoice_type --added by subba for R12.1
1855 --Yao Zhang add for bug8605196
1856 ,consolidation_flag
1857 ,consolidation_id
1858 ,consolidation_trx_num
1859 INTO
1860 x_trx_header_rec.ra_gl_date
1861 ,x_trx_header_rec.ra_gl_period
1862 ,x_trx_header_rec.set_of_books_id
1863 ,x_trx_header_rec.bill_to_customer_id
1864 ,x_trx_header_rec.bill_to_customer_number
1865 ,x_trx_header_rec.bill_to_customer_name
1866 ,x_trx_header_rec.source
1867 ,x_trx_header_rec.org_id
1868 ,x_trx_header_rec.legal_entity_id
1869 ,x_trx_header_rec.rule_header_id
1870 ,x_trx_header_rec.gta_trx_header_id
1871 ,x_trx_header_rec.gta_trx_number
1872 ,x_trx_header_rec.group_number
1873 ,x_trx_header_rec.version
1874 ,x_trx_header_rec.latest_version_flag
1875 ,x_trx_header_rec.transaction_date
1876 ,x_trx_header_rec.ra_trx_id
1877 ,x_trx_header_rec.ra_trx_number
1878 ,x_trx_header_rec.description
1879 ,x_trx_header_rec.customer_address
1880 ,x_trx_header_rec.customer_phone
1881 ,x_trx_header_rec.customer_address_phone
1882 ,x_trx_header_rec.bank_account_name
1883 ,x_trx_header_rec.bank_account_number
1884 ,x_trx_header_rec.bank_account_name_number
1885 ,x_trx_header_rec.fp_tax_registration_number
1886 ,x_trx_header_rec.tp_tax_registration_number
1887 ,x_trx_header_rec.ra_currency_code
1888 ,x_trx_header_rec.conversion_type
1889 ,x_trx_header_rec.conversion_date
1890 ,x_trx_header_rec.conversion_rate
1891 ,x_trx_header_rec.gta_batch_number
1892 ,x_trx_header_rec.gt_invoice_number
1893 ,x_trx_header_rec.gt_invoice_date
1894 ,x_trx_header_rec.gt_invoice_net_amount
1895 ,x_trx_header_rec.gt_invoice_tax_amount
1896 ,x_trx_header_rec.status
1897 ,x_trx_header_rec.sales_list_flag
1898 ,x_trx_header_rec.cancel_flag
1899 ,x_trx_header_rec.gt_invoice_type
1900 ,x_trx_header_rec.gt_invoice_class
1901 ,x_trx_header_rec.gt_tax_month
1902 ,x_trx_header_rec.issuer_name
1903 ,x_trx_header_rec.reviewer_name
1904 ,x_trx_header_rec.payee_name
1905 ,x_trx_header_rec.tax_code
1906 ,x_trx_header_rec.tax_rate
1907 ,x_trx_header_rec.generator_id
1908 ,x_trx_header_rec.request_id
1909 ,x_trx_header_rec.program_application_id
1910 ,x_trx_header_rec.program_id
1911 ,x_trx_header_rec.program_update_date
1912 ,x_trx_header_rec.attribute_category
1913 ,x_trx_header_rec.attribute1
1914 ,x_trx_header_rec.attribute2
1915 ,x_trx_header_rec.attribute3
1916 ,x_trx_header_rec.attribute4
1917 ,x_trx_header_rec.attribute5
1918 ,x_trx_header_rec.attribute6
1919 ,x_trx_header_rec.attribute7
1920 ,x_trx_header_rec.attribute8
1921 ,x_trx_header_rec.attribute9
1922 ,x_trx_header_rec.attribute10
1923 ,x_trx_header_rec.attribute11
1924 ,x_trx_header_rec.attribute12
1925 ,x_trx_header_rec.attribute13
1926 ,x_trx_header_rec.attribute14
1927 ,x_trx_header_rec.attribute15
1928 ,x_trx_header_rec.creation_date
1929 ,x_trx_header_rec.created_by
1930 ,x_trx_header_rec.last_update_date
1931 ,x_trx_header_rec.last_updated_by
1932 ,x_trx_header_rec.last_update_login
1933 ,x_trx_header_rec.invoice_type --added by subba for R12.1
1934 --Yao Zhang add for bug8605196
1935 ,x_trx_header_rec.consolidation_flag
1936 ,x_trx_header_rec.consolidation_id
1937 ,x_trx_header_rec.consolidation_trx_num
1938
1939 FROM
1940 ar_gta_trx_headers_all
1941 WHERE gta_trx_header_id=p_header_id;
1942
1943 --log for debug
1944 IF( l_proc_level >= l_dbg_level)
1945 THEN
1946
1947 FND_LOG.String(l_proc_level
1948 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
1949 ,'Exit procedure'
1950 );
1951
1952 END IF; --( l_proc_level >= l_dbg_level)
1953
1954 END query_row;
1955
1956 --==========================================================================
1957 -- PROCEDURE NAME:
1958 --
1959 -- Unmerge_Row Public
1960 --
1961 -- DESCRIPTION:
1962 --
1963 -- This procedure is to delete current consolidation invoice and
1964 -- change status of its consolidated invoices to 'DRAFT'.
1965 --
1966 --
1967 -- PARAMETERS:
1968 --
1969 -- In Out: p_rowid Row id of a table record
1970 --
1971 --
1972 -- DESIGN REFERENCES:
1973 -- GTA_12.1.2_Technical_Design.doc
1974 --
1975 -- CHANGE HISTORY:
1976 --
1977 -- 13-Jul-2009 Allen Yang created
1978 -- 05-Sep-2009 Allen Yang modified for bug 8882568
1979 --===========================================================================
1980 PROCEDURE Unmerge_Row
1981 (p_rowid IN OUT NOCOPY VARCHAR2
1982 )
1983 IS
1984 l_procedure_name VARCHAR2(100) :='Unmerge_Row';
1985 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1986 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1987 l_consol_trx_num AR_GTA_TRX_HEADERS_ALL.GTA_TRX_NUMBER%TYPE;
1988 BEGIN
1989
1990 --log for debug
1991 IF( l_proc_level >= l_dbg_level)
1992 THEN
1993 FND_LOG.String(l_proc_level
1994 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.begin'
1995 ,'Begin procedure'
1996 );
1997 END IF; --( l_proc_level >= l_dbg_level)
1998
1999 -- get current consolidation invoice number
2000 SELECT gta_trx_number
2001 INTO l_consol_trx_num
2002 FROM AR_GTA_TRX_HEADERS_ALL
2003 WHERE rowid = p_rowid;
2004
2005 -- set status of consolidated invoices as 'DRAFT' and consolidation_flag as null
2006 UPDATE AR_GTA_TRX_HEADERS_ALL
2007 SET STATUS = 'DRAFT'
2008 , CONSOLIDATION_FLAG = NULL
2009 , CONSOLIDATION_TRX_NUM = NULL
2010 -- added by Allen Yang for bug #8882568 concurrency control 05-Sep-2009
2011 -------------------------------------------------------------------
2012 , CONSOLIDATION_REQUEST_ID = NULL
2013 , CONSOLIDATION_ID = NULL
2014 -------------------------------------------------------------------
2015 WHERE CONSOLIDATION_TRX_NUM = l_consol_trx_num
2016 AND CONSOLIDATION_FLAG = '1';
2017
2018 -- need to delete data from AR_GTA_TRX_HEADERS_V first?????
2019 --Delete row from table AR_GTA_TRX_HEADERS_ALL
2020 /* will delete_record in form trigger, so remove the logic here
2021 DELETE
2022 FROM
2023 ar_gta_trx_headers_all
2024 WHERE rowid = p_rowid;
2025 */
2026 --In case of update failed,raise error
2027 IF (SQL%NOTFOUND)
2028 THEN
2029 RAISE NO_DATA_FOUND;
2030 END IF; --(SQL%NOTFOUND)
2031
2032 --log for debug
2033 IF( l_proc_level >= l_dbg_level)
2034 THEN
2035 FND_LOG.String(l_proc_level
2036 ,G_MODULE_PREFIX||'.'||l_procedure_name||'.end'
2037 ,'Exit procedure'
2038 );
2039 END IF; --( l_proc_level >= l_dbg_level)
2040 END Unmerge_Row;
2041
2042
2043 END ar_gta_trx_headers_all_pkg;
[Home] [Help]