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