[Home] [Help]
PACKAGE BODY: APPS.AR_GTA_CONC_PROG
Source
1 PACKAGE BODY AR_GTA_CONC_PROG AS
2 --$Header: ARGCCPGB.pls 120.0.12010000.4 2010/01/19 03:13:24 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| ARCCPGB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is the a collection of procedures which |
13 --| called by concurrent programs |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Transfer_AR_Transactions |
17 --| PROCEDURE Purge_Invoice |
18 --| PROCEDURE Run_AR_GT_Mapping |
19 --| PROCEDURE Import_GT_Invoices |
20 --| PROCEDURE Transfer_Invoices_to_GT |
21 --| PROCEDURE Export_Invoices_from_Workbench |
22 --| PROCEDURE Discrepancy_Report |
23 --| PROCEDURE Item_Export |
24 --| PROCEDURE Transfer_Customers_To_GT |
25 --| PROCEDURE Consolidate_Invoices |
26 --| PROCEDURE Run_Consolidation_Mapping |
27 --| PROCEDURE Populate_Invoice_Type |
28 --| PROCEDURE Populate_Invoice_Type_Header |
29 --| |
30 --| HISTORY |
31 --| 20-APR-2005: Jim Zheng |
32 --| 08-MAY-2005: Qiang Li |
33 --| 20-MAY-2005: Jogen Hu add Import_GT_invoices |
34 --| Transfer_Invoices_to_GT |
35 --| Transfer_Trxs_from_workbench |
36 --| 13-Jun-2005: Donghai Wang add procedure Discrepancy_Report |
37 --| and Item_Export |
38 --| 01-Jul-2005: Jim Zheng Update after code review, |
39 --| chang parameter type. |
40 --| 25-Aug-2005: Jogen Hu for import invoices, |
41 --| move clearing temporary table from |
42 --| AR_GTA_TXT_OPERATOR_PROC into this |
43 --| 28-Sep-2005: Jonge Hu change transfer_invoices_to_gt |
44 --| 18-Oct-2005: Donghai Wang Update 'Transrfer_Invoices_To_GT' |
45 --| procedure to adjust order of |
46 --| paramerts |
47 --| 16-Nov-2005: Jim Zheng Change the output of gta_not_Enable |
48 --| 16-Nov-2005: Qiang Li Change Purge_invoice,run_AR_GT_mappi|
49 --| ng, to set their status to warnning |
50 --| 23-Nov-2005: Donghai Wang Update procedure Discrepancy_Report,|
51 --| set its status to 'Warnning' when |
52 --| Profile 'GTA Not Enabled' is set to |
53 --| 'No' |
54 --| 30-Nov-2005: Qiang Li Change set_of_books_id to ledger_id |
55 --| 01-Dec-2005: Qiang Li Use function Get_AR_Batch_Source_Name
56 --| to translate source id to source name
57 --| in Run_AR_GT_Mapping procedure |
58 --| 06-Mar-2006: Donghai Wang Update Discrepancy_Report and |
59 --| Item_Eport for adding fnd log |
60 --| 26-Apr-2006: Qiang Li Update the PROCEDURE Purge_Invoice |
61 --| 14-Sep-2006: Qiang Li Update the PROCEDURE Purge_Invoice |
62 --| 20-Jul-2009: Yao Zhang Add procedure Consolidate_Invoices |
63 --| |
64 --| 25-Jul-2009: Allen Yang Add procedure run_consolidation_mapping|
65 --| for bug 8605196: ENHANCEMENT FOR |
66 --| GOLDEN TAX ADAPTER R12.1.2
67 --| 08-Aug-2009: Yao Zhang Fix bug#8770356, add parameter org_id to
68 --| procedure consolidate_invoices |
69 --| 16-Aug-2009: Allen Yang Add procedures Populate_Invoice_Type |
70 --| and Populate_Invoice_Type_Header to |
71 --| do data migration from 12.0 to 12.1.X|
72 --| 02-Sep-2009 Allen Yang modified procedure |
73 --| Run_Consolidation_Mapping for bug |
74 --| 8848798 |
75 --| 19-Oct-2009 Allen Yang modified procedure import_gt_invoices|
76 --| for bug 9008021 |
77 --+======================================================================*/
78
79 --==========================================================================
80 -- PROCEDURE NAME:
81 --
82 -- Transfer_AR_Transactions Public
83 --
84 -- DESCRIPTION:
85 --
86 -- This procedure is the main program for transfer program.
87 --
88 -- PARAMETERS:
89 -- In: p_transfer_id Transfer rule id
90 -- p_customer_num_from Customer number from
91 -- p_customer_num_to Customer number to
92 -- p_customer_name_from Customer name from
93 -- p_customer_name_to Customer name to
94 -- p_gl_period GL period
95 -- p_gl_date_from GL date from
96 -- p_gl_date_to GL date to
97 -- p_trx_batch_from Batch number from
98 -- p_trx_batch_to Batch number to
99 -- p_trx_number_from Trx number from
100 -- p_trx_number_to Trx number to
101 -- p_trx_date_from Trx date from
102 -- p_trx_date_to Trx date to
103 -- p_doc_num_from Doc number from
104 -- p_doc_num_to Doc number to
105 -- Out: errbuf
106 -- retcode
107 --
108 -- DESIGN REFERENCES:
109 -- GTA-TRANSFER-PROGRAM-TD.doc
110 --
111 -- CHANGE HISTORY:
112 --
113 -- 05-MAY-2005: Jim.Zheng Created
114 --
115 --===========================================================================
116 PROCEDURE transfer_ar_transactions
117 (errbuf OUT NOCOPY VARCHAR2
118 ,retcode OUT NOCOPY VARCHAR2
119 ,p_transfer_id IN VARCHAR2
120 ,p_customer_num_from IN VARCHAR2
121 ,p_customer_num_to IN VARCHAR2
122 ,p_customer_name_from IN VARCHAR2
123 ,p_customer_name_to IN VARCHAR2
124 ,p_gl_period IN VARCHAR2
125 ,p_gl_date_from IN VARCHAR2
126 ,p_gl_date_to IN VARCHAR2
127 ,p_trx_batch_from IN VARCHAR2
128 ,p_trx_batch_to IN VARCHAR2
129 ,p_trx_number_from IN VARCHAR2
130 ,p_trx_number_to IN VARCHAR2
131 ,p_trx_date_from IN VARCHAR2
132 ,p_trx_date_to IN VARCHAR2
133 ,p_doc_num_from IN NUMBER
134 ,p_doc_num_to IN NUMBER
135 ) IS
136 l_procedure_name VARCHAR2(50) := 'transfer_AR_to_GTA';
137 l_parameters ar_gta_trx_util.transferparas_rec_type;
138 l_ar_gta_gta_not_enabled VARCHAR2(4000);
139 l_conc_succ BOOLEAN;
140 l_errbuf VARCHAR2(4000);
141 l_retcode VARCHAR2(4000);
142 l_org_id NUMBER := mo_global.get_current_org_id;
143
144 BEGIN
145 --begin procedure
146 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
147 THEN
148 fnd_log.STRING(fnd_log.level_procedure
149 ,g_module_prefix || l_procedure_name
150 ,'Begin Procedure. ');
151 END IF;
152
153 -- wrap parameter to record
154 IF (fnd_profile.VALUE('AR_GTA_ENABLED') = 'Y')
155 THEN
156 l_parameters.customer_num_from := p_customer_num_from;
157 l_parameters.customer_num_to := p_customer_num_to;
158 l_parameters.customer_name_from := p_customer_name_from;
159 l_parameters.customer_name_to := p_customer_name_to;
160 l_parameters.gl_period := p_gl_period;
161 l_parameters.gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
162 l_parameters.gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
163 l_parameters.trx_batch_from := p_trx_batch_from;
164 l_parameters.trx_batch_to := p_trx_batch_to;
165 l_parameters.trx_number_from := p_trx_number_from;
166 l_parameters.trx_number_to := p_trx_number_to;
167 l_parameters.trx_date_from := fnd_date.canonical_to_date(p_trx_date_from);
168 l_parameters.trx_date_to := fnd_date.canonical_to_date(p_trx_date_to);
169 l_parameters.doc_num_from := p_doc_num_from;
170 l_parameters.doc_num_to := p_doc_num_to;
171
172 -- call AR_GTA_ARTRX_PROC_JIM.transfer_AR_to_GTA
173 ar_gta_artrx_proc.transfer_ar_to_gta(errbuf => l_errbuf
174 ,retcode => l_retcode
175 ,p_org_id => l_org_id
176 ,p_transfer_id => p_transfer_id
177 ,p_conc_parameters => l_parameters);
178
179 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
180 THEN
181 fnd_log.STRING(fnd_log.level_procedure
182 ,g_module_prefix || l_procedure_name
183 ,'errbuf is : ' || l_errbuf || ' retcode is :' ||
184 l_retcode);
185 END IF;
186
187 ELSE
188 -- report AR_GTA_DISABLE_ERROR in xml format
189 -- set concurrent status to WARNING
190 fnd_message.set_name('AR'
191 ,'AR_GTA_GTA_NOT_ENABLE');
192 l_ar_gta_gta_not_enabled := '<TransferReport>
193 <ReportFailed>Y</ReportFailed>
194 <ReportFailedMsg>' ||
195 fnd_message.get ||
196 '</ReportFailedMsg>
197 <FailedWithParameters>Y</FailedWithParameters>
198 </TransferReport>';
199
200 fnd_file.put_line(fnd_file.output
201 ,l_ar_gta_gta_not_enabled);
202
203 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
204 ,message => l_ar_gta_gta_not_enabled);
205 RETURN;
206 END IF;
207 -- end procedure
208 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
209 THEN
210 fnd_log.STRING(fnd_log.level_procedure
211 ,g_module_prefix || l_procedure_name
212 ,'End Procedure. ');
213 END IF;
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
218 THEN
219 fnd_log.STRING(fnd_log.level_unexpected
220 ,g_module_prefix || l_procedure_name ||
221 '. OTHER_EXCEPTION '
222 ,SQLCODE || SQLERRM);
223 END IF;
224 RAISE;
225
226 END transfer_ar_transactions;
227
228 --==========================================================================
229 -- PROCEDURE NAME:
230 --
231 -- Purge_Invoice Public
232 --
233 -- DESCRIPTION:
234 --
235 -- This procedure is the main program for purge program,
236 -- it search eligible records in GTA invoice tables first,
237 -- if find any, then invoke corresponding table handlers to
238 -- remove these records from db.
239 --
240 -- PARAMETERS:
241 -- In: p_ledger_id Ledger identifier
242 -- p_customer_name Customer name
243 -- p_gl_date_from GL date low range
244 -- p_gl_date_to GL date high range
245 --
246 -- Out: errbuf
247 -- retcode
248 --
249 -- DESIGN REFERENCES:
250 -- GTA-PURGE-PROGRAM-TD.doc
251 --
252 -- CHANGE HISTORY:
253 --
254 -- 8-MAY-2005: Qiang Li Created.
255 -- 16-Nov-2005:Qiang Li When GTA profile is not enabled,set concurrent
256 -- status to 'Warnning'
257 -- 30-Nov-2005: Qiang Li Change set_of_books_id to ledger_id
258 -- 26-Jun-2006: Qiang Li Remove the condition of GL period is closed
259 -- 14-Sep-2006: Qiang Li Use function To_Xsd_Date_String to replace
260 -- format_date, according to new XML template standards
261 --===========================================================================
262
263 PROCEDURE purge_invoice
264 (errbuf OUT NOCOPY VARCHAR2
265 ,retcode OUT NOCOPY VARCHAR2
266 ,p_ledger_id IN NUMBER
267 ,p_customer_name IN VARCHAR2
268 ,p_gl_date_from IN VARCHAR2
269 ,p_gl_date_to IN VARCHAR2
270 )
271 IS
272 l_org_id NUMBER := mo_global.get_current_org_id;
273 l_procedure_name VARCHAR2(30) := 'Purge_Invoice';
274 l_gl_date_from DATE;
275 l_gl_date_to DATE;
276 l_dbg_msg VARCHAR2(1000);
277 l_gta_header_count NUMBER;
278 l_gt_header_count NUMBER;
279 l_gta_line_count NUMBER;
280 l_gt_line_count NUMBER;
281 l_line_count NUMBER;
282 l_gta_trx_header_id ar_gta_trx_headers_all.gta_trx_header_id%TYPE;
283 l_source ar_gta_trx_headers_all.SOURCE%TYPE;
284 l_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
285 l_output_msg VARCHAR2(2000);
286 l_no_data_flag VARCHAR2(1) := 'N';
287 l_report xmltype;
288 l_summary xmltype;
289 l_parameter xmltype;
290 l_ar_gta_enabled fnd_profile_option_values.profile_option_value%TYPE := NULL;
291 l_ar_gta_gta_not_enabled VARCHAR2(500);
292 l_no_data_message VARCHAR2(500);
293 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
294 l_proc_level NUMBER := fnd_log.level_procedure;
295 l_conc_succ BOOLEAN;
296
297
298
299 CURSOR c_gta_header IS
300 SELECT
301 jgth.gta_trx_header_id
302 ,jgth.SOURCE
303 ,rct.customer_trx_id
304 FROM
305 ar_gta_trx_headers_all jgth
306 ,ra_customer_trx_all rct
307 WHERE jgth.org_id = l_org_id
308 AND jgth.status IN ('FAILED', 'COMPLETED', 'CANCELLED')
309 AND jgth.bill_to_customer_name LIKE nvl(p_customer_name,'%')
310 AND jgth.ra_gl_date >= l_gl_date_from
311 AND jgth.ra_gl_date <= l_gl_date_to
312 AND jgth.ra_trx_id = rct.customer_trx_id(+)
313 FOR UPDATE;
314
315 BEGIN
316
317 --logging for debug
318 IF (l_proc_level >= l_dbg_level)
319 THEN
320 fnd_log.STRING(l_proc_level
321 ,g_module_prefix || l_procedure_name || '.begin'
322 ,'enter procedure');
323 END IF; /*IF (l_proc_level>=l_dbg_level)*/
324
325 -- Data conversion
326 l_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
327 l_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
328 l_gta_header_count := 0;
329 l_gt_header_count := 0;
330 l_gta_line_count := 0;
331 l_gt_line_count := 0;
332
333 SELECT xmlelement("Parameters"
334 ,xmlforest(ar_gta_trx_util.get_operatingunit(l_org_id) AS
335 "OperationUnit"
336 ,p_customer_name AS
337 "ARCustomerName"
338 ,ar_gta_trx_util.To_Xsd_Date_String(l_gl_date_from) AS
339 "ARTrxGLDateFrom"
340 ,ar_gta_trx_util.To_Xsd_Date_String(l_gl_date_to) AS
341 "ARTrxGLDateTo"))
342 INTO l_parameter
343 FROM dual;
344
345 fnd_profile.get('AR_GTA_ENABLED'
346 ,l_ar_gta_enabled);
347
348 IF nvl(l_ar_gta_enabled
349 ,'N') = 'N'
350 THEN
351 fnd_message.set_name('AR'
352 ,'AR_GTA_GTA_NOT_ENABLE');
353 l_ar_gta_gta_not_enabled := fnd_message.get();
354
355 -- Output the context of l_ar_gta_gta_not_enabled
356 SELECT xmlelement("PurgeReport"
357 ,xmlconcat(xmlelement("ReportFailed"
358 ,'Y')
359 ,xmlelement("FailedWithParameters"
360 ,'N')
361 ,xmlelement("RepDate"
362 ,ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
363 ,xmlelement("ReportFailedMsg"
364 ,l_ar_gta_gta_not_enabled)
365 ,l_parameter))
366 INTO l_report
367 FROM dual;
368
369 ar_gta_trx_util.output_conc(l_report.getclobval());
370 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
371 ,message => l_ar_gta_gta_not_enabled);
372 RETURN;
373 END IF; /*IF NVL(l_ar_gta_enabled,'N')<>'Y'*/
374
375
376
377 --Main process for purge
378 OPEN c_gta_header;
379 FETCH c_gta_header
380 INTO l_gta_trx_header_id, l_source, l_customer_trx_id;
381
382 WHILE c_gta_header%FOUND
383 LOOP
384 IF l_customer_trx_id IS NULL
385 THEN
386 SELECT
387 COUNT(*)
388 INTO
389 l_line_count
390 FROM
391 ar_gta_trx_lines_all
392 WHERE gta_trx_header_id = l_gta_trx_header_id;
393
394 -- delete GTA and gt invoices inclunding headers and lines
395 -- according to GTA_TRX_HEADER_ID
396 ar_gta_trx_util.delete_header_line_cascade(p_gta_trx_header_id => l_gta_trx_header_id);
397
398 IF l_source = 'AR' --count deleted GTA transaction headers and lines
399 THEN
400 l_gta_header_count := l_gta_header_count + 1;
401 l_gta_line_count := l_gta_line_count + l_line_count;
402
403 ELSIF l_source = 'GT' --count deleted GT transaction headers and lines
404 THEN
405 l_gt_header_count := l_gt_header_count + 1;
406 l_gt_line_count := l_gt_line_count + l_line_count;
407 END IF; /*IF l_source='AR'*/
408
409 l_line_count := 0;
410 END IF; /*IF l_customer_trx_id IS NULL*/
411
412 FETCH c_gta_header
413 INTO l_gta_trx_header_id, l_source, l_customer_trx_id;
414 END LOOP; /*WHILE c_gta_header%FOUND*/
415
416 CLOSE c_gta_header;
417
418 SELECT xmlelement("Summary"
419 ,xmlforest(l_gta_header_count AS "GTATrxHeaderPurged"
420 ,l_gta_line_count AS "GTATrxLinePurged"
421 ,l_gt_header_count AS "GTTrxHeaderPurged"
422 ,l_gt_line_count AS "GTTrxLinePurged"))
423 INTO l_summary
424 FROM dual;
425
426 --Generate Reports Xml Data
427 SELECT xmlelement("PurgeReport"
428 ,xmlconcat(xmlelement("ReportFailed"
429 ,'N')
430 ,xmlelement("FailedWithParameters"
431 ,'N')
432 ,xmlelement("RepDate"
433 ,ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
434 ,l_parameter
435 ,l_summary))
436 INTO l_report
437 FROM dual;
438
439 ar_gta_trx_util.output_conc(l_report.getclobval());
440
441 --logging for debug
442 IF (l_proc_level >= l_dbg_level)
443 THEN
444 fnd_log.STRING(l_proc_level
445 ,g_module_prefix || l_procedure_name || '.end'
446 ,'end procedure');
447 END IF; /*IF (l_proc_level>=l_dbg_level)*/
448
449 COMMIT;
450 EXCEPTION
451 WHEN OTHERS THEN
452 IF fnd_log.level_unexpected >= l_dbg_level
453 THEN
454 fnd_log.STRING(fnd_log.level_unexpected
455 ,g_module_prefix || l_procedure_name || '.OTHER_EXCEPTION'
456 ,SQLCODE || SQLERRM);
457 END IF;
458 RAISE;
459 ROLLBACK;
460 END purge_invoice;
461
462 --==========================================================================
463 -- PROCEDURE NAME:
464 --
465 -- Run_AR_GT_Mapping Public
466 --
467 -- DESCRIPTION:
468 --
469 -- This Concurrent program Generate Mapping Report Data
470 --
471 -- PARAMETERS:
472 -- In: p_fp_tax_reg_num First Party Tax Registration Number
473 -- p_trx_source Transaction source,GT or AR
474 -- P_Customer_Id Customer id
475 -- p_gt_inv_num_from GT Invoice Number low range
476 -- p_gt_inv_num_to GT Invoice Number high range
477 -- p_gt_inv_date_from GT Invoice Date low range
478 -- p_gt_inv_date_to GT Invoice Date high range
479 -- p_ar_inv_num_from AR Invoice Number low range
480 -- p_ar_inv_num_to AR Invoice Number high range
481 -- p_ar_inv_date_from AR Invoice Date low range
482 -- p_ar_inv_date_to AR Invoice Date high range
483 --
484 -- Out: errbuf
485 -- retcode
486 --
487 -- DESIGN REFERENCES:
488 -- GTA_REPORTS_TD.doc
489 --
490 -- CHANGE HISTORY:
491 --
492 -- 8-MAY-2005: Qiang Li Created.
493 -- 27-Sep-2005:Qiang Li Add a new parameter fp_tax_reg_number
494 -- 16-Nov-2005:Qiang Li When GTA profile is not enabled,set concurrent
495 -- status to 'Warnning'
496 --
497 --===========================================================================
498 PROCEDURE run_ar_gt_mapping
499 (errbuf OUT NOCOPY VARCHAR2
500 ,retcode OUT NOCOPY VARCHAR2
501 ,p_fp_tax_reg_num IN VARCHAR2
502 ,p_trx_source IN NUMBER
503 ,p_customer_id IN VARCHAR2
504 ,p_gt_inv_num_from IN VARCHAR2
505 ,p_gt_inv_num_to IN VARCHAR2
506 ,p_gt_inv_date_from IN VARCHAR2
507 ,p_gt_inv_date_to IN VARCHAR2
508 ,p_ar_inv_num_from IN VARCHAR2
509 ,p_ar_inv_num_to IN VARCHAR2
510 ,p_ar_inv_date_from IN VARCHAR2
511 ,p_ar_inv_date_to IN VARCHAR2
512 )
513 IS
514 l_procedure_name VARCHAR2(30) := 'run_AR_GT_Mapping';
515 l_gt_inv_date_from DATE;
516 l_gt_inv_date_to DATE;
517 l_ar_inv_date_from DATE;
518 l_ar_inv_date_to DATE;
519 l_ar_gta_enabled fnd_profile_option_values.profile_option_value%TYPE := NULL;
520 l_ar_gta_gta_not_enabled VARCHAR2(500);
521 l_report xmltype;
522 l_parameter xmltype;
523 l_dbg_msg VARCHAR2(500);
524 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
525 l_proc_level NUMBER := fnd_log.level_procedure;
526 l_org_id NUMBER := mo_global.get_current_org_id;
527 l_conc_succ BOOLEAN;
528 BEGIN
529 --logging for debug
530 IF (l_proc_level >= l_dbg_level)
531 THEN
532 fnd_log.STRING(l_proc_level
533 ,g_module_prefix || l_procedure_name || '.begin'
534 ,'enter procedure');
535 END IF;
536
537 l_gt_inv_date_from := fnd_date.canonical_to_date(p_gt_inv_date_from);
538 l_gt_inv_date_to := fnd_date.canonical_to_date(p_gt_inv_date_to);
539 l_ar_inv_date_from := fnd_date.canonical_to_date(p_ar_inv_date_from);
540 l_ar_inv_date_to := fnd_date.canonical_to_date(p_ar_inv_date_to);
541
542 fnd_profile.get('AR_GTA_ENABLED'
543 ,l_ar_gta_enabled);
544 IF nvl(l_ar_gta_enabled
545 ,'N') = 'N'
546 THEN
547 SELECT xmlelement("Parameters"
548 ,xmlforest(ar_gta_trx_util.get_operatingunit(l_org_id)
549 AS "OperationUnit"
550 ,p_fp_tax_reg_num
551 AS "TaxRegistrationNumber"
552 ,ar_gta_trx_util.Get_AR_Batch_Source_Name
553 ( l_org_id
554 , p_trx_source)
555 AS "TransactionSource"
556 ,ar_gta_trx_util.get_customer_name(p_customer_id)
557 AS "ARCustomerName"
558 ,p_gt_inv_num_from
559 AS "GTInvoiceNumFrom"
560 ,p_gt_inv_num_to
561 AS "GTInvoiceNumTo"
562 ,ar_gta_trx_util.To_Xsd_Date_String(l_gt_inv_date_from)
563 AS "GTDateFrom"
564 ,ar_gta_trx_util.To_Xsd_Date_String(l_gt_inv_date_to)
565 AS "GTDateTo"
566 ,p_ar_inv_num_from
567 AS "ARTrxNumberFrom"
568 ,p_ar_inv_num_to
569 AS "ARTrxNumberTo"
570 ,ar_gta_trx_util.To_Xsd_Date_String(l_ar_inv_date_from)
571 AS "ARTrxDateFrom"
572 ,ar_gta_trx_util.To_Xsd_Date_String(l_ar_inv_date_to)
573 AS "ARTrxDateTo"))
574 INTO l_parameter
575 FROM dual;
576
577 fnd_message.set_name('AR'
578 ,'AR_GTA_GTA_NOT_ENABLE');
579 l_ar_gta_gta_not_enabled := fnd_message.get();
580
581 -- Output the context of l_ar_gta_gta_not_enabled
582 SELECT xmlelement("MappingReport"
583 ,xmlconcat(xmlelement("ReportFailed"
584 ,'Y')
585 ,xmlelement("FailedWithParameters"
586 ,'N')
587 ,xmlelement("RepDate"
588 ,ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
589 ,xmlelement("ReportFailedMsg"
590 ,l_ar_gta_gta_not_enabled)
591 ,l_parameter))
592 INTO l_report
593 FROM dual;
594
595 ar_gta_trx_util.output_conc(l_report.getclobval());
596 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
597 ,message => l_ar_gta_gta_not_enabled);
598 ELSE
599 NULL;
600
601 ar_gta_reports_pkg.generate_mapping_rep(p_org_id => l_org_id
602 ,p_fp_tax_reg_num => p_fp_tax_reg_num
603 ,p_trx_source => p_trx_source
604 ,p_customer_id => p_customer_id
605 ,p_gt_inv_num_from => p_gt_inv_num_from
606 ,p_gt_inv_num_to => p_gt_inv_num_to
607 ,p_gt_inv_date_from => l_gt_inv_date_from
608 ,p_gt_inv_date_to => l_gt_inv_date_to
609 ,p_ar_inv_num_from => p_ar_inv_num_from
610 ,p_ar_inv_num_to => p_ar_inv_num_to
611 ,p_ar_inv_date_from => l_ar_inv_date_from
612 ,p_ar_inv_date_to => l_ar_inv_date_to);
613
614 END IF;
615
616 --logging for debug
617 IF (l_proc_level >= l_dbg_level)
618 THEN
619 fnd_log.STRING(l_proc_level
620 ,g_module_prefix || l_procedure_name || '.end'
621 ,'end procedure');
622 END IF;
623
624 EXCEPTION
625 WHEN OTHERS THEN
626 IF fnd_log.level_unexpected >= l_dbg_level
627 THEN
628 fnd_log.STRING(fnd_log.level_unexpected
629 ,g_module_prefix || l_procedure_name || '.OTHER_EXCEPTION'
630 ,SQLCODE || SQLERRM);
631 END IF;
632 RAISE;
633
634 END run_ar_gt_mapping;
635
636 --==========================================================================
637 -- PROCEDURE NAME:
638 --
639 -- Import_GT_Invoices Public
640 --
641 -- DESCRIPTION:
642 --
643 -- This procedure is program of SRS concurrent for import
644 -- flat file exported from Golden Tax system
645 --
646 -- PARAMETERS:
647 -- In:
648 --
649 -- Out: errbuf
650 -- retcode
651 --
652 -- DESIGN REFERENCES:
653 -- GTA-Txt-Interface-TD.doc
654 --
655 -- CHANGE HISTORY:
656 --
657 -- 20-MAY-2005: Jogen Hu Created
658 --
659 -- 15-AUG-2005: Jogen Hu Move clear temporary table from
660 -- package ar_gta_txt_operator_proc
661 -- 19-Oct-2009: Allen Yang Modified for bug 9008021
662 --===========================================================================
663 PROCEDURE import_gt_invoices
664 (errbuf OUT NOCOPY VARCHAR2
665 ,retcode OUT NOCOPY VARCHAR2
666 )
667 IS
668 l_procedure_name VARCHAR2(30) := 'Import_GT_invoices';
669 -- modified by Allen Yang 19-Oct-2009 for bug 9008021
670 -----------------------------------------------------
671 --l_ar_gta_gta_not_enabled VARCHAR2(300);
672 l_ar_gta_gta_not_enabled VARCHAR2(3000);
673 -----------------------------------------------------
674 l_conc_succ BOOLEAN;
675
676 BEGIN
677 --procedure begin
678 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
679 THEN
680 fnd_log.STRING(fnd_log.level_procedure
681 ,g_module_prefix || l_procedure_name || '.begin'
682 ,'Procedure begin');
683 END IF;
684
685 IF fnd_profile.VALUE('AR_GTA_ENABLED') = 'N'
686 THEN
687 fnd_message.set_name('AR'
688 ,'AR_GTA_GTA_NOT_ENABLE');
689
690 l_ar_gta_gta_not_enabled := '<ImportReport>
691 <ReportFailed>Y</ReportFailed>
692 <ReportFailedMsg>' ||
693 fnd_message.get ||
694 '</ReportFailedMsg>
695 <FailedWithParameters>Y</FailedWithParameters>
696 </ImportReport>';
697
698 -- Output the context of l_ar_gta_gta_not_enabled
699 fnd_file.put_line(fnd_file.output
700 ,l_ar_gta_gta_not_enabled);
701 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
702 ,message => l_ar_gta_gta_not_enabled);
703
704 ar_gta_txt_operator_proc.Clear_Imp_Temp_Table;
705 RETURN;
706 END IF;
707
708 ar_gta_txt_operator_proc.import_invoices;
709
710 ar_gta_txt_operator_proc.Clear_Imp_Temp_Table;
711
712 --procedure end
713 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
714 THEN
715 fnd_log.STRING(fnd_log.level_procedure
716 ,g_module_prefix || l_procedure_name || '.end'
717 ,'Procedure end');
718 END IF;
719
720 EXCEPTION
721 WHEN OTHERS THEN
722 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
723 THEN
724 fnd_log.STRING(fnd_log.level_unexpected
725 ,g_module_prefix || l_procedure_name ||
726 '.OTHER_EXCEPTION '
727 ,SQLCODE || SQLERRM);
728 END IF;
729
730 ar_gta_txt_operator_proc.Clear_Imp_Temp_Table;
731
732 RAISE;
733 END import_gt_invoices;
734
735 --==========================================================================
736 -- PROCEDURE NAME:
737 --
738 -- Transfer_Invoices_to_GT Public
739 --
740 -- DESCRIPTION:
741 --
742 -- This procedure is a SRS concurrent program which exports GTA
743 -- invoices to the flat file Its output will be printed on concurrent
744 -- output and will be save as flat file by users.
745 --
746 -- PARAMETERS:
747 -- In: p_regeneration IN VARCHAR2
748 -- p_fp_tax_reg_num in varchar2
749 -- p_new_batch_dummy IN VARCHAR2
750 -- p_regeneration_dummy IN VARCHAR2
751 -- p_transfer_rule_id IN NUMBER
752 -- p_batch_number IN VARCHAR2
753 -- p_customer_id_from_number IN NUMBER
754 -- p_customer_id_from_name IN NUMBER
755 -- p_cust_id_from_taxpayer IN NUMBER
756 -- p_ar_trx_num_from IN VARCHAR2
757 -- p_ar_trx_num_to IN VARCHAR2
758 -- p_ar_trx_date_from IN VARCHAR2
759 -- p_ar_trx_date_to IN VARCHAR2
760 -- p_ar_trx_gl_date_from IN VARCHAR2
761 -- p_ar_trx_gl_date_to IN VARCHAR2
762 -- p_ar_trx_batch_from IN VARCHAR2
763 -- p_ar_trx_batch_to IN VARCHAR2
764 -- p_trx_class IN VARCHAR2
765 -- p_batch_id IN VARCHAR2
766 --
767 -- Out: errbuf
768 -- retcode
769 --
770 -- DESIGN REFERENCES:
771 -- GTA-Txt-Interface-TD.doc
772 --
773 -- CHANGE HISTORY:
774 --
775 -- 20-MAY-2005: Jogen Hu Created
776 -- 28-Sep-2005: Jogen Hu add parameter p_fp_tax_reg_num
777 -- 18-Oct-2005: Donghai Wang move the parameter 'p_fp_tax_reg_num'
778 -- behind the parameter 'p_regeneration_dummy'
779 --
780 --===========================================================================
781 PROCEDURE transfer_invoices_to_gt
782 (errbuf OUT NOCOPY VARCHAR2
783 ,retcode OUT NOCOPY VARCHAR2
784 ,p_regeneration IN VARCHAR2
785 ,p_new_batch_dummy IN VARCHAR2
786 ,p_regeneration_dummy IN VARCHAR2
787 ,p_fp_tax_reg_num IN VARCHAR2
788 ,p_transfer_rule_id IN NUMBER
789 ,p_batch_number IN VARCHAR2
790 ,p_customer_id_from_number IN NUMBER
791 ,p_customer_id_from_name IN NUMBER
792 ,p_cust_id_from_taxpayer IN NUMBER
793 ,p_ar_trx_num_from IN VARCHAR2
794 ,p_ar_trx_num_to IN VARCHAR2
795 ,p_ar_trx_date_from IN VARCHAR2
796 ,p_ar_trx_date_to IN VARCHAR2
797 ,p_ar_trx_gl_date_from IN VARCHAR2
798 ,p_ar_trx_gl_date_to IN VARCHAR2
799 ,p_ar_trx_batch_from IN VARCHAR2
800 ,p_ar_trx_batch_to IN VARCHAR2
801 ,p_trx_class IN VARCHAR2
802 ,p_batch_id IN VARCHAR2
803 ,p_invoice_type IN VARCHAR2
804 )
805 IS
806 l_procedure_name VARCHAR2(30) := 'Transfer_Invoices_to_GT';
807 l_org_id NUMBER;
808 l_ar_trx_date_from DATE;
809 l_ar_trx_date_to DATE;
810 l_ar_trx_gl_date_from DATE;
811 l_ar_trx_gl_date_to DATE;
812 l_ar_gta_gta_not_enabled VARCHAR2(1000);
813 l_conc_succ BOOLEAN;
814
815 BEGIN
816 --procedure begin
817 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
818 THEN
819 fnd_log.STRING(fnd_log.level_procedure
820 ,g_module_prefix || l_procedure_name || '.begin'
821 ,'Procedure begin');
822 END IF;
823
824 IF fnd_profile.VALUE('AR_GTA_ENABLED') = 'N'
825 THEN
826 fnd_message.set_name('AR'
827 ,'AR_GTA_GTA_NOT_ENABLE');
828 l_ar_gta_gta_not_enabled := '//' || fnd_message.get;
829
830 -- Output the context of l_ar_gta_gta_not_enabled
831 fnd_file.put_line(fnd_file.output
832 ,l_ar_gta_gta_not_enabled);
833 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
834 ,message => l_ar_gta_gta_not_enabled);
835 RETURN;
836 END IF;
837
838 l_ar_trx_date_from := fnd_date.canonical_to_date(p_ar_trx_date_from);
839 l_ar_trx_date_to := fnd_date.canonical_to_date(p_ar_trx_date_to);
840 l_ar_trx_gl_date_from := fnd_date.canonical_to_date(p_ar_trx_gl_date_from);
841 l_ar_trx_gl_date_to := fnd_date.canonical_to_date(p_ar_trx_gl_date_to);
842
843 l_org_id := mo_global.get_current_org_id;
844
845 AR_GTA_TXT_OPERATOR_PROC.Export_Invoices_From_Conc(p_org_id => l_org_id
846 ,p_regeneration => p_regeneration
847 ,p_fp_tax_reg_number => p_fp_tax_reg_num
848 ,p_transfer_rule_id => p_transfer_rule_id
849 ,p_batch_number => p_batch_number
850 ,p_customer_id_from_number => p_customer_id_from_number
851 ,p_customer_id_from_name => p_customer_id_from_name
852 ,p_cust_id_from_taxpayer => p_cust_id_from_taxpayer
853 ,p_ar_trx_num_from => p_ar_trx_num_from
854 ,p_ar_trx_num_to => p_ar_trx_num_to
855 ,p_ar_trx_date_from => l_ar_trx_date_from
856 ,p_ar_trx_date_to => l_ar_trx_date_to
857 ,p_ar_trx_gl_date_from => l_ar_trx_gl_date_from
858 ,p_ar_trx_gl_date_to => l_ar_trx_gl_date_to
859 ,p_ar_trx_batch_from => p_ar_trx_batch_from
860 ,p_ar_trx_batch_to => p_ar_trx_batch_to
861 ,p_trx_class => p_trx_class
862 ,p_batch_id => p_batch_id
863 ,p_invoice_type_id => p_invoice_type
864 );
865 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
866 THEN
867 fnd_log.STRING(fnd_log.level_procedure
868 ,g_module_prefix || l_procedure_name || '.end'
869 ,'Procedure end');
870 END IF;
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
875 THEN
876 fnd_log.STRING(fnd_log.level_unexpected
877 ,g_module_prefix || l_procedure_name ||
878 '.OTHER_EXCEPTION '
879 ,SQLCODE || SQLERRM);
880 END IF;
881 RAISE;
882
883 END transfer_invoices_to_gt;
884
885 --==========================================================================
886 -- PROCEDURE NAME:
887 --
888 -- Export_Invoices_from_Workbench Public
889 --
890 -- DESCRIPTION:
891 --
892 -- This procedure is a SRS concurrent program which exports VAT
893 -- invoices from GTA to flat file and is invoked in workbench
894 --
895 -- PARAMETERS:
896 -- In: p_org_id IN NUMBER
897 -- p_generator_ID IN NUMBER
898 -- p_batch_number IN VARCHAR2
899 --
900 -- Out: errbuf
901 -- retcode
902 --
903 -- DESIGN REFERENCES:
904 -- GTA-Txt-Interface-TD.doc
905 --
906 -- CHANGE HISTORY:
907 --
908 -- 20-MAY-2005: Jogen Hu Created
909 --
910 --==========================================================================
911 PROCEDURE transfer_trxs_from_workbench
912 (errbuf OUT NOCOPY VARCHAR2
913 ,retcode OUT NOCOPY VARCHAR2
914 ,p_org_id IN NUMBER
915 ,p_generator_id IN NUMBER
916 ,p_batch_number IN VARCHAR2
917 )
918 IS
919 l_procedure_name VARCHAR2(30) := 'Transfer_Trxs_from_workbench';
920 l_ar_gta_gta_not_enabled VARCHAR2(300);
921 l_conc_succ BOOLEAN;
922
923 BEGIN
924 --procedure begin
925 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
926 THEN
927 fnd_log.STRING(fnd_log.level_procedure
928 ,g_module_prefix || l_procedure_name || '.begin'
929 ,'Procedure begin');
930 END IF;
931
932 IF fnd_profile.VALUE('AR_GTA_ENABLED') = 'N'
933 THEN
934 fnd_message.set_name('AR'
935 ,'AR_GTA_GTA_NOT_ENABLE');
936 l_ar_gta_gta_not_enabled := '//' || fnd_message.get;
937
938 -- Output the context of l_ar_gta_gta_not_enabled
939 fnd_file.put_line(fnd_file.output
940 ,l_ar_gta_gta_not_enabled);
941 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
942 ,message => l_ar_gta_gta_not_enabled);
943 RETURN;
944 END IF;
945
946 ar_gta_txt_operator_proc.export_invoices_from_workbench(p_org_id => p_org_id
947 ,p_generator_id => p_generator_id
948 ,p_batch_id => p_batch_number);
949
950 --procedure end
951 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
952 THEN
953 fnd_log.STRING(fnd_log.level_procedure
954 ,g_module_prefix || l_procedure_name || '.end'
955 ,'Procedure end');
956 END IF;
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
961 THEN
962 fnd_log.STRING(fnd_log.level_unexpected
963 ,g_module_prefix || l_procedure_name ||
964 '.OTHER_EXCEPTION '
965 ,SQLCODE || SQLERRM);
966 END IF;
967 RAISE;
968 END transfer_trxs_from_workbench;
969
970 --==========================================================================
971 -- PROCEDURE NAME:
972 --
973 -- Discrepancy_Report Public
974 --
975 -- DESCRIPTION:
976 --
977 -- This procedure is called by concurren program 'Golden Tax
978 -- Discrepancy Report' to generte discrepancy report.
979 --
980 -- PARAMETERS:
981 -- In: p_gta_batch_num_from GTA invoice batch number low range
982 -- p_gta_batch_num_to GTA invoice batch number high range
983 -- p_ar_transaction_type AR transaction type
984 -- p_cust_num_from Customer number low range
985 -- p_cust_num_to Customer number high range
986 -- p_cust_name_id Identifier of customer
987 -- p_gl_period GL period name
988 -- p_gl_date_from GL date low range
989 -- p_gl_date_to GL date high range
990 -- p_ar_trx_batch_from AR transaction batch name low range
991 -- p_ar_trx_batch_to AR transaction batch name high range
992 -- P_ar_trx_num_from AR transaction number low range
993 -- P_ar_trx_num_to AR transaction number high range
994 -- p_ar_trx_date_from AR transaction date low range
995 -- p_ar_trx_date_to AR transaction date high range
996 -- p_ar_doc_num_from AR document sequnce number low range
997 -- p_ar_doc_num_to AR document sequnce number high range
998 -- p_original_curr_code Original currency code
999 -- p_primary_sales Identifier of primary salesperson
1000 --
1001 -- Out: errbuf
1002 -- retcode
1003 --
1004 -- DESIGN REFERENCES:
1005 -- GTA_REPORTS_TD.doc
1006 --
1007 -- CHANGE HISTORY:
1008 --
1009 -- 13-Jun-2005: Donghai Wang Created
1010 -- 06-Mar-2006: Donghai Wang Add fnd log
1011 --
1012 --==========================================================================
1013 PROCEDURE discrepancy_report
1014 (errbuf OUT NOCOPY VARCHAR2
1015 ,retcode OUT NOCOPY VARCHAR2
1016 ,p_gta_batch_num_from IN VARCHAR2
1017 ,p_gta_batch_num_to IN VARCHAR2
1018 ,p_ar_transaction_type IN NUMBER
1019 ,p_cust_num_from IN VARCHAR2
1020 ,p_cust_num_to IN VARCHAR2
1021 ,p_cust_name_id IN NUMBER
1022 ,p_gl_period IN VARCHAR2
1023 ,p_gl_date_from IN VARCHAR2
1024 ,p_gl_date_to IN VARCHAR2
1025 ,p_ar_trx_batch_from IN VARCHAR2
1026 ,p_ar_trx_batch_to IN VARCHAR2
1027 ,p_ar_trx_num_from IN VARCHAR2
1028 ,p_ar_trx_num_to IN VARCHAR2
1029 ,p_ar_trx_date_from IN VARCHAR2
1030 ,p_ar_trx_date_to IN VARCHAR2
1031 ,p_ar_doc_num_from IN VARCHAR2
1032 ,p_ar_doc_num_to IN VARCHAR2
1033 ,p_original_curr_code IN VARCHAR2
1034 ,p_primary_sales IN NUMBER
1035 )
1036 IS
1037 l_ar_gta_enabled VARCHAR2(10);
1038 l_dbg_msg VARCHAR2(500);
1039 l_ar_gta_not_enabled_msg VARCHAR2(1000);
1040 l_report_xml XMLTYPE;
1041 l_procedure_name VARCHAR2(50);
1042 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1043 l_proc_level NUMBER := fnd_log.level_procedure;
1044 l_org_id hr_all_organization_units.organization_id%TYPE;
1045 l_conc_succ BOOLEAN;
1046 BEGIN
1047
1048
1049
1050 l_procedure_name := 'Discrepancy_Report';
1051
1052 --log for debug
1053 IF (l_proc_level >= l_dbg_level)
1054 THEN
1055 fnd_log.STRING(l_proc_level
1056 ,g_module_prefix || '.' || l_procedure_name || '.begin'
1057 ,'Enter procedure');
1058
1059 fnd_log.STRING(l_proc_level
1060 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1061 ,'p_gta_batch_num_from '||p_gta_batch_num_from);
1062
1063 fnd_log.STRING(l_proc_level
1064 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1065 ,'p_gta_batch_num_to '||p_gta_batch_num_to);
1066
1067 fnd_log.STRING(l_proc_level
1068 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1069 ,'p_ar_transaction_type '||p_ar_transaction_type);
1070
1071 fnd_log.STRING(l_proc_level
1072 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1073 ,'p_cust_num_from '||p_cust_num_from);
1074
1075 fnd_log.STRING(l_proc_level
1076 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1077 ,'p_cust_num_to '||p_cust_num_to);
1078
1079 fnd_log.STRING(l_proc_level
1080 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1081 ,'p_cust_name_id '||p_cust_name_id);
1082
1083 fnd_log.STRING(l_proc_level
1084 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1085 ,'p_gl_period '||p_gl_period);
1086
1087 fnd_log.STRING(l_proc_level
1088 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1089 ,'p_gl_date_from '||p_gl_date_from);
1090
1091 fnd_log.STRING(l_proc_level
1092 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1093 ,'p_gl_date_to '||p_gl_date_to);
1094
1095 fnd_log.STRING(l_proc_level
1096 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1097 ,'p_ar_trx_batch_from '||p_ar_trx_batch_from);
1098
1099 fnd_log.STRING(l_proc_level
1100 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1101 ,'p_ar_trx_batch_to '||p_ar_trx_batch_to);
1102
1103 fnd_log.STRING(l_proc_level
1104 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1105 ,'p_ar_trx_num_from '||p_ar_trx_num_from);
1106
1107 fnd_log.STRING(l_proc_level
1108 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1109 ,'p_ar_trx_num_to '||p_ar_trx_num_to);
1110
1111 fnd_log.STRING(l_proc_level
1112 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1113 ,'p_ar_trx_date_from '||p_ar_trx_date_from);
1114
1115 fnd_log.STRING(l_proc_level
1116 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1117 ,'p_ar_trx_date_to '||p_ar_trx_date_to);
1118
1119 fnd_log.STRING(l_proc_level
1120 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1121 ,'p_ar_doc_num_from '||p_ar_doc_num_from);
1122
1123 fnd_log.STRING(l_proc_level
1124 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1125 ,'p_ar_doc_num_to '||p_ar_doc_num_to);
1126
1127 fnd_log.STRING(l_proc_level
1128 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1129 ,'p_original_curr_code '||p_original_curr_code);
1130
1131 fnd_log.STRING(l_proc_level
1132 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1133 ,'p_primary_sales '||p_primary_sales);
1134 END IF; --( l_proc_level >= l_dbg_level )
1135
1136
1137 --To Get value of profile AR:Golden Tax Enabled
1138 l_ar_gta_enabled := fnd_profile.VALUE(NAME => 'AR_GTA_ENABLED');
1139
1140 IF (l_ar_gta_enabled IS NULL)
1141 OR --The profile AR:Golden Tax Enabled is Null or set to 'N'
1142 (l_ar_gta_enabled = 'N')
1143 THEN
1144
1145 --Display error message
1146 fnd_message.set_name('AR'
1147 ,'AR_GTA_GTA_NOT_ENABLE');
1148 l_ar_gta_not_enabled_msg := fnd_message.get;
1149 SELECT xmlelement("DiscrepancyReport"
1150 ,xmlforest('Y' AS "ReportFailed"
1151 ,l_ar_gta_not_enabled_msg AS
1152 "ReportFailedMsg"
1153 ,'N' AS "FailedWithParameters"))
1154 INTO l_report_xml
1155 FROM dual;
1156
1157 --output error message to concurrent output
1158 fnd_file.put_line(fnd_file.output
1159 ,l_report_xml.getstringval());
1160
1161 --Set concurrent status to 'WARNING'
1162 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1163 ,message => l_ar_gta_not_enabled_msg);
1164 ELSE
1165 --To get org id of current session
1166 l_org_id:=MO_GLOBAL.Get_Current_Org_Id;
1167
1168 --To call discrepancy report main program
1169 ar_gta_reports_pkg.generate_discrepancy_rep(p_org_id => l_org_id
1170 ,p_gta_batch_num_from => p_gta_batch_num_from
1171 ,p_gta_batch_num_to => p_gta_batch_num_to
1172 ,p_ar_transaction_type => p_ar_transaction_type
1173 ,p_cust_num_from => p_cust_num_from
1174 ,p_cust_num_to => p_cust_num_to
1175 ,p_cust_name_id => p_cust_name_id
1176 ,p_gl_period => p_gl_period
1177 ,p_gl_date_from => p_gl_date_from
1178 ,p_gl_date_to => p_gl_date_to
1179 ,p_ar_trx_batch_from => p_ar_trx_batch_from
1180 ,p_ar_trx_batch_to => p_ar_trx_batch_to
1181 ,p_ar_trx_num_from => p_ar_trx_num_from
1182 ,p_ar_trx_num_to => p_ar_trx_num_to
1183 ,p_ar_trx_date_from => p_ar_trx_date_from
1184 ,p_ar_trx_date_to => p_ar_trx_date_to
1185 ,p_ar_doc_num_from => p_ar_doc_num_from
1186 ,p_ar_doc_num_to => p_ar_doc_num_to
1187 ,p_original_curr_code => p_original_curr_code
1188 ,p_primary_sales => p_primary_sales);
1189 END IF; --(l_ar_gta_enabled IS NULL) OR (l_ar_gta_enabled='N')
1190
1191 --log for debug
1192 IF (l_proc_level >= l_dbg_level)
1193 THEN
1194 fnd_log.STRING(l_proc_level
1195 ,g_module_prefix || '.' || l_procedure_name || '.end'
1196 ,'Exit procedure');
1197 END IF; --( l_proc_level >= l_dbg_level )
1198
1199 EXCEPTION
1200 WHEN OTHERS THEN
1201 IF (fnd_log.level_unexpected >= l_dbg_level)
1202 THEN
1203 fnd_log.STRING(fnd_log.level_unexpected
1204 ,g_module_prefix || l_procedure_name ||
1205 '.OTHER_EXCEPTION '
1206 ,SQLCODE || SQLERRM);
1207 END IF;
1208
1209 RAISE;
1210
1211 END discrepancy_report;
1212
1213 --==========================================================================
1214 -- PROCEDURE NAME:
1215 --
1216 -- Item_Export Public
1217 --
1218 -- DESCRIPTION:
1219 --
1220 -- This procedure is to export item information to a flat file
1221 --
1222 -- PARAMETERS:
1223 -- In: p_master_org_id Identifier of INV master organization
1224 -- p_item_num_from Item number low range
1225 -- p_item_num_to Item number high range
1226 -- p_category_set_id Identifier of item category set
1227 -- p_category_structure_id Structure id of item category
1228 -- p_item_category_from Item category low range
1229 -- p_item_category_to Item category high range
1230 -- p_item_name_source Source to deciede where item name is gotten
1231 -- p_dummy Dummy parameter
1232 -- p_cross_reference_type Cross reference
1233 -- p_item_status Status of an item
1234 -- p_creation_date_from Item creation date low range
1235 -- p_creation_date_to Item creation date high range
1236 --
1237 -- Out: errbuf
1238 -- retcode
1239 --
1240 -- DESIGN REFERENCES:
1241 -- GTA-Txt-Interface-TD.doc
1242 --
1243 -- CHANGE HISTORY:
1244 --
1245 -- 13-Jun-2005: Donghai Wang Created
1246 -- 06-Mar-2006: Donghai Wang Add fnd log
1247 --
1248 --==========================================================================
1249 PROCEDURE item_export
1250 (errbuf OUT NOCOPY VARCHAR2
1251 ,retcode OUT NOCOPY VARCHAR2
1252 ,p_master_org_id IN NUMBER
1253 ,p_item_num_from IN VARCHAR2
1254 ,p_item_num_to IN VARCHAR2
1255 ,p_category_set_id IN NUMBER
1256 ,p_category_structure_id IN NUMBER
1257 ,p_item_category_from IN VARCHAR2
1258 ,p_item_category_to IN VARCHAR2
1259 ,p_item_name_source IN VARCHAR2
1260 ,p_dummy IN VARCHAR2
1261 ,p_cross_reference_type IN VARCHAR2
1262 ,p_item_status IN VARCHAR2
1263 ,p_creation_date_from IN VARCHAR2
1264 ,p_creation_date_to IN VARCHAR2
1265 )
1266 IS
1267 l_procedure_name VARCHAR2(50);
1268 l_ar_gta_enabled VARCHAR2(10);
1269 l_dbg_msg VARCHAR2(500);
1270 l_ar_gta_not_enabled_msg VARCHAR2(1000);
1271 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1272 l_proc_level NUMBER := fnd_log.level_procedure;
1273 l_org_id hr_all_organization_units.organization_id%TYPE;
1274 l_conc_succ BOOLEAN;
1275
1276 BEGIN
1277 l_procedure_name := 'Item_Export';
1278 --log for debug
1279 IF (l_proc_level >= l_dbg_level)
1280 THEN
1281 fnd_log.STRING(l_proc_level
1282 ,g_module_prefix || '.' || l_procedure_name || '.begin'
1283 ,'Enter procedure');
1284
1285 fnd_log.STRING(l_proc_level
1286 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1287 ,'p_master_org_id '||p_master_org_id);
1288
1289 fnd_log.STRING(l_proc_level
1290 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1291 ,'p_item_num_from '||p_item_num_from);
1292
1293 fnd_log.STRING(l_proc_level
1294 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1295 ,'p_item_num_to '||p_item_num_to);
1296
1297 fnd_log.STRING(l_proc_level
1298 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1299 ,'p_category_set_id '||p_category_set_id);
1300
1301 fnd_log.STRING(l_proc_level
1302 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1303 ,'p_category_structure_id '||p_category_structure_id);
1304
1305 fnd_log.STRING(l_proc_level
1306 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1307 ,'p_item_category_from '||p_item_category_from);
1308
1309 fnd_log.STRING(l_proc_level
1310 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1311 ,'p_item_category_to '||p_item_category_to);
1312
1313 fnd_log.STRING(l_proc_level
1314 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1315 ,'p_item_name_source '||p_item_name_source);
1316
1317 fnd_log.STRING(l_proc_level
1318 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1319 ,'p_dummy '||p_dummy);
1320
1321 fnd_log.STRING(l_proc_level
1322 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1323 ,'p_cross_reference_type '||p_cross_reference_type);
1324
1325 fnd_log.STRING(l_proc_level
1326 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1327 ,'p_item_status '||p_item_status);
1328
1329 fnd_log.STRING(l_proc_level
1330 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1331 ,'p_creation_date_from '||p_creation_date_from);
1332
1333 fnd_log.STRING(l_proc_level
1334 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1335 ,'p_creation_date_to '||p_creation_date_to);
1336
1337
1338 END IF; --( l_proc_level >= l_dbg_level )
1339
1340 --To Get value of profile AR:Golden Tax Enabled
1341 l_ar_gta_enabled := fnd_profile.VALUE(NAME => 'AR_GTA_ENABLED');
1342
1343 IF (l_ar_gta_enabled IS NULL)
1344 OR --The profile AR:Golden Tax Enabled is Null or set to 'N'
1345 (l_ar_gta_enabled = 'N')
1346 THEN
1347
1348 --Display error message
1349 fnd_message.set_name('AR'
1350 ,'AR_GTA_GTA_NOT_ENABLE');
1351 l_ar_gta_not_enabled_msg := fnd_message.get;
1352 fnd_file.put_line(fnd_file.output
1353 ,l_ar_gta_not_enabled_msg);
1354
1355 --Set concurrent status to 'WARNING'
1356 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1357 ,message => l_ar_gta_not_enabled_msg);
1358 ELSE
1359
1360 --To get org id of current session
1361 l_org_id:=MO_GLOBAL.Get_Current_Org_Id;
1362
1363 --To call item export main program
1364 ar_gta_txt_operator_proc.export_items(p_org_id => l_org_id
1365 ,p_master_org_id => p_master_org_id
1366 ,p_item_num_from => p_item_num_from
1367 ,p_item_num_to => p_item_num_to
1368 ,p_category_set_id => p_category_set_id
1369 ,p_category_structure_id => p_category_structure_id
1370 ,p_item_category_from => p_item_category_from
1371 ,p_item_category_to => p_item_category_to
1372 ,p_item_name_source => p_item_name_source
1373 ,p_cross_reference_type => p_cross_reference_type
1374 ,p_item_status => p_item_status
1375 ,p_creation_date_from => p_creation_date_from
1376 ,p_creation_date_to => p_creation_date_to);
1377 END IF; --(l_ar_gta_enabled IS NULL) OR (l_ar_gta_enabled='N')
1378
1379 --log for debug
1380 IF (l_proc_level >= l_dbg_level)
1381 THEN
1382 fnd_log.STRING(l_proc_level
1383 ,g_module_prefix || '.' || l_procedure_name || '.end'
1384 ,'Exit procedure');
1385 END IF; --( l_proc_level >= l_dbg_level )
1386
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1390 THEN
1391 fnd_log.STRING(fnd_log.level_unexpected
1392 ,g_module_prefix || l_procedure_name ||
1393 '. OTHER_EXCEPTION '
1394 ,'Unknown error' || SQLCODE || SQLERRM);
1395
1396 END IF;
1397 RAISE;
1398
1399 END item_export;
1400
1401 --==========================================================================
1402 -- PROCEDURE NAME:
1403 --
1404 -- Transfer_Customers_To_GT Public
1405 --
1406 -- DESCRIPTION:
1407 --
1408 -- This procedure convert AR customers information into a flat file
1409 --
1410 -- PARAMETERS:
1411 -- In: p_customer_num_from IN VARCHAR2
1412 -- p_customer_num_to IN VARCHAR2
1413 -- p_customer_name_from IN VARCHAR2
1414 -- p_customer_name_to IN VARCHAR2
1415 -- p_taxpayee_id IN VARCHAR2
1416 -- p_creation_date_from IN VARCHAR2
1417 -- p_creation_date_to IN VARCHAR2
1418 --
1419 -- Out: errbuf
1420 -- retcode
1421 --
1422 -- DESIGN REFERENCES:
1423 -- GTA-Txt-Interface-TD.doc
1424 --
1425 -- CHANGE HISTORY:
1426 --
1427 -- 20-MAY-2005: Jim.Zheng Created.
1428 -- 26_Jun-005 Jim Zheng update , chanage the Date parameter to Varchar2
1429 -- 16-Nov-2005 Jim Zheng update , change the output of gta_not_enable
1430 --==========================================================================
1431 PROCEDURE transfer_customers_to_gt
1432 (errbuf OUT NOCOPY VARCHAR2
1433 ,retcode OUT NOCOPY VARCHAR2
1434 ,p_customer_num_from IN VARCHAR2
1435 ,p_customer_num_to IN VARCHAR2
1436 ,p_customer_name_from IN VARCHAR2
1437 ,p_customer_name_to IN VARCHAR2
1438 --,p_taxpayee_id IN VARCHAR2
1439 ,p_creation_date_from IN VARCHAR2
1440 ,p_creation_date_to IN VARCHAR2
1441 )
1442 IS
1443
1444 l_ar_gta_gta_not_enabled VARCHAR2(1000);
1445 l_procedure_name VARCHAR2(50) := 'transfer_customers_to_GT';
1446 l_conc_succ BOOLEAN;
1447 l_org_id NUMBER := mo_global.get_current_org_id;
1448 BEGIN
1449 -- procedure begin
1450 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1451 THEN
1452 fnd_log.STRING(fnd_log.level_procedure
1453 ,g_module_prefix || l_procedure_name
1454 ,'Begin Procedure. ');
1455 END IF; /*FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL*/
1456
1457 IF (fnd_profile.VALUE('AR_GTA_ENABLED') = 'Y')
1458 THEN
1459 ar_gta_txt_operator_proc.export_customers(p_org_id => l_org_id
1460 ,p_customer_num_from => p_customer_num_from
1461 ,p_customer_num_to => p_customer_num_to
1462 ,p_customer_name_from => p_customer_name_from
1463 ,p_customer_name_to => p_customer_name_to
1464 --,p_taxpayee_id => p_taxpayee_id
1465 ,p_creation_date_from => fnd_date.canonical_to_date(p_creation_date_from)
1466 ,p_creation_date_to => fnd_date.canonical_to_date(p_creation_date_to));
1467
1468 ELSE
1469 /*FND_PROFILE.VALUE('GTA_ENABLED')='Y'*/
1470 -- report AR_GTA_DISABLE_ERROR in xml format
1471 -- set concurrent status to WARNING
1472 fnd_message.set_name('AR'
1473 ,'AR_GTA_GTA_NOT_ENABLE');
1474 l_ar_gta_gta_not_enabled := fnd_message.get;
1475
1476 fnd_file.put_line(fnd_file.output
1477 ,l_ar_gta_gta_not_enabled);
1478
1479 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1480 ,message => l_ar_gta_gta_not_enabled);
1481 RETURN;
1482 END IF; /*FND_PROFILE.VALUE('GTA_ENABLED')='Y'*/
1483 -- procedure end
1484 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1485 THEN
1486 fnd_log.STRING(fnd_log.level_procedure
1487 ,g_module_prefix || l_procedure_name
1488 ,'End Procedure. ');
1489 END IF; /*FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL*/
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1494 THEN
1495 fnd_log.STRING(fnd_log.level_unexpected
1496 ,g_module_prefix || l_procedure_name ||
1497 '. OTHER_EXCEPTION '
1498 ,'Unknown error' || SQLCODE || SQLERRM);
1499
1500 END IF;
1501 RAISE;
1502 END transfer_customers_to_gt;
1503 --=============================================================================
1504 -- PROCEDURE NAME:
1505 -- Consolidate_Invoices
1506 -- TYPE:
1507 -- PUBLIC
1508 --
1509 -- DESCRIPTION: This is the entrance procedure to merge invoice.
1510 --
1511 -- PARAMETERS:
1512 -- IN : p_same_pri_same_dis same price and same discoout
1513 -- p_same_pri_diff_dis same price with different discount
1514 -- p_diff_pri different price
1515 -- p_sales_list_flag salese_list_flag
1516 -- p_consolidation_id consolidation id
1517 --
1518 -- HISTORY:
1519 -- 30-Jun-2009 : Yao Zhang Create
1520 -- 08-Aug-2009 : Yao Zhang Modified for bug#8770356
1521 --=============================================================================
1522 PROCEDURE Consolidate_Invoices
1523 (errbuf OUT NOCOPY VARCHAR2
1524 ,retcode OUT NOCOPY VARCHAR2
1525 ,p_consolidation_id IN NUMBER
1526 ,p_same_pri_same_dis IN VARCHAR2
1527 ,p_same_pri_diff_dis IN VARCHAR2
1528 ,p_diff_pri IN VARCHAR2
1529 ,p_sales_list_flag IN VARCHAR2
1530 ,p_org_id IN NUMBER --Yao Zhang add for bug#8770356
1531 )
1532 IS
1533 l_procedure_name VARCHAR2(30):='Consolidate_Invoices';
1534 l_ar_gta_gta_not_enabled Varchar2(200);
1535 l_conc_succ BOOLEAN;
1536 l_consol_paras ar_gta_trx_util.consolparas_rec_type;
1537
1538
1539 BEGIN
1540 fnd_file.PUT_LINE(fnd_file.LOG,'Begin Procedure.'||l_procedure_name);
1541 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1542 THEN
1543 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1544 , G_MODULE_PREFIX ||'.'|| l_procedure_name
1545 ,'Begin Procedure. ');
1546 END IF;
1547 IF (fnd_profile.VALUE('AR_GTA_ENABLED') = 'Y')
1548 THEN
1549 l_consol_paras.consolidation_id := p_consolidation_id;
1550 l_consol_paras.same_pri_same_dis := p_same_pri_same_dis;
1551 l_consol_paras.same_pri_diff_dis := p_same_pri_diff_dis;
1552 l_consol_paras.diff_pri := p_diff_pri;
1553 l_consol_paras.sales_list_flag := p_sales_list_flag;
1554 l_consol_paras.org_id := p_org_id;--Yao Zhang add for bug#8770356
1555
1556 --consolidate invoices
1557 AR_GTA_CONSOLIDATE_PROC.Create_consol_inv(p_consolidation_paras=>l_consol_paras);
1558 --generate xml output
1559 AR_GTA_CONSOLIDATE_PROC.Generate_XML_Output(p_consolidation_paras=>l_consol_paras);
1560
1561 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1562 THEN
1563 fnd_log.STRING(fnd_log.level_procedure
1564 ,g_module_prefix || l_procedure_name
1565 ||'. OTHER_EXCEPTION ',
1566 'Unknown error' || SQLCODE || SQLERRM);
1567 END IF;
1568
1569 ELSE
1570 -- report AR_GTA_DISABLE_ERROR in xml format
1571 -- set concurrent status to WARNING
1572 fnd_message.set_name('AR'
1573 ,'AR_GTA_GTA_NOT_ENABLE');
1574 l_ar_gta_gta_not_enabled := '<ConsolidationReport>
1575 <ReportFailed>Y</ReportFailed>
1576 <ReportFailedMsg>' ||
1577 fnd_message.get ||
1578 '</ReportFailedMsg>
1579 <FailedWithParameters>Y</FailedWithParameters>
1580 </ConsolidationReport>';
1581
1582 fnd_file.put_line(fnd_file.output
1583 ,l_ar_gta_gta_not_enabled);
1584
1585 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1586 ,message => l_ar_gta_gta_not_enabled);
1587 RETURN;
1588 END IF;
1589
1590 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1591 THEN
1592 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
1593 , G_MODULE_PREFIX ||'.'|| l_procedure_name
1594 ,'End Procedure. ');
1595 END IF;
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1599 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
1600 G_MODULE_PREFIX || l_procedure_name ||
1601 '. OTHER_EXCEPTION ',
1602 'Unknown error' || SQLCODE || SQLERRM);
1603
1604 END IF;
1605 RAISE;
1606 END;
1607
1608 --=============================================================================
1609 -- PROCEDURE NAME:
1610 -- Run_Consolidation_Mapping
1611 -- TYPE:
1612 -- PUBLIC
1613 --
1614 -- DESCRIPTION: This is the entrance procedure for invoice consolidation mapping report.
1615 --
1616 -- PARAMETERS:
1617 -- IN : p_gl_period GL period
1618 -- p_customer_num_from customer number from
1619 -- p_customer_num_to customer number to
1620 -- p_customer_name_from customer name from
1621 -- p_customer_name_to customer name to
1622 -- p_consol_trx_num_from consolidated invoice number from
1623 -- p_consol_trx_num_to consolidated invoice number to
1624 -- p_invoice_type invoice type
1625 --
1626 -- HISTORY:
1627 -- 25-Jul-2009 : Allen Yang created
1628 -- 02-Sep-2009 : Allen Yang modified for bug 8848798
1629 --=============================================================================
1630 PROCEDURE Run_Consolidation_Mapping
1631 (errbuf OUT NOCOPY VARCHAR2
1632 ,retcode OUT NOCOPY VARCHAR2
1633 ,p_gl_period IN VARCHAR2
1634 ,p_customer_num_from IN VARCHAR2
1635 ,p_customer_num_to IN VARCHAR2
1636 ,p_customer_name_from IN VARCHAR2
1637 ,p_customer_name_to IN VARCHAR2
1638 ,p_consol_trx_num_from IN VARCHAR2
1639 ,p_consol_trx_num_to IN VARCHAR2
1640 ,p_invoice_type IN VARCHAR2
1641 )
1642 IS
1643 l_procedure_name VARCHAR2(30) := 'Run_Consolidation_Mapping';
1644 l_ar_gta_enabled fnd_profile_option_values.profile_option_value%TYPE := NULL;
1645 l_ar_gta_gta_not_enabled VARCHAR2(500);
1646 l_report xmltype;
1647 l_parameter xmltype;
1648 l_dbg_msg VARCHAR2(500);
1649 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1650 l_proc_level NUMBER := fnd_log.level_procedure;
1651 l_org_id NUMBER := mo_global.get_current_org_id;
1652 l_conc_succ BOOLEAN;
1653
1654
1655 BEGIN
1656 --logging for debug
1657 IF (l_proc_level >= l_dbg_level)
1658 THEN
1659 fnd_log.STRING(l_proc_level
1660 ,g_module_prefix || l_procedure_name || '.begin'
1661 ,'enter procedure');
1662 END IF;
1663
1664 fnd_profile.get('AR_GTA_ENABLED', l_ar_gta_enabled);
1665
1666 IF NVL(l_ar_gta_enabled ,'N') = 'N'
1667 THEN
1668 /* commented by Allen Yang 02-Sep-2009 for bug 8848798
1669 SELECT xmlelement("Parameters"
1670 ,xmlforest(ar_gta_trx_util.get_operatingunit(l_org_id)
1671 AS "OperationUnit"
1672 , p_gl_period AS "GLPeriod"
1673 , p_customer_num_from AS "CustomerNumFrom"
1674 , p_customer_num_to AS "CustomerNumTo"
1675 , p_customer_name_from AS "CustomerNameFrom"
1676 , p_customer_name_to AS "CustomerNameTo"
1677 , p_consol_trx_num_from AS "ConsolidationTrxNumFrom"
1678 , p_consol_trx_num_to AS "ConsolidationTrxNumTo"
1679 , p_invoice_type AS "InvoiceType"))
1680 INTO l_parameter
1681 FROM dual;
1682 */
1683 fnd_message.set_name('AR', 'AR_GTA_GTA_NOT_ENABLE');
1684 -- modified by Allen Yang 02-Sep-2009 for bug 8848798
1685 ------------------------------------------------------------------------
1686 /*
1687 l_ar_gta_gta_not_enabled := fnd_message.get();
1688 -- Output the context of l_ar_gta_gta_not_enabled
1689 SELECT xmlelement("MappingReport"
1690 ,xmlconcat(xmlelement("ReportFailed", 'Y')
1691 ,xmlelement("FailedWithParameters",'N')
1692 ,xmlelement("RepDate",ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
1693 ,xmlelement("ReportFailedMsg",l_ar_gta_gta_not_enabled)
1694 ,l_parameter))
1695 INTO l_report
1696 FROM dual;
1697
1698 ar_gta_trx_util.output_conc(l_report.getclobval());
1699 */
1700 l_ar_gta_gta_not_enabled := '<ConsolidationMappingReport>
1701 <ReportFailed>Y</ReportFailed>
1702 <FailedWithParameters>Y</FailedWithParameters>
1703 <FailedMsgWithParameters>' ||
1704 fnd_message.get ||
1705 '</FailedMsgWithParameters>
1706 </ConsolidationMappingReport>';
1707 fnd_file.put_line(fnd_file.output
1708 ,l_ar_gta_gta_not_enabled);
1709 --------------------------------------------------------------------------
1710 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1711 ,message => l_ar_gta_gta_not_enabled);
1712 ELSE
1713 ar_gta_reports_pkg.Generate_Consol_Mapping_Rep
1714 (p_org_id => l_org_id
1715 , p_gl_period => p_gl_period
1716 , p_customer_num_from => p_customer_num_from
1717 , p_customer_num_to => p_customer_num_to
1718 , p_customer_name_from => p_customer_name_from
1719 , p_customer_name_to => p_customer_name_to
1720 , p_consol_trx_num_from => p_consol_trx_num_from
1721 , p_consol_trx_num_to => p_consol_trx_num_to
1722 , p_invoice_type => p_invoice_type);
1723 END IF; --NVL(l_ar_gta_enabled ,'N') = 'N'
1724
1725 --logging for debug
1726 IF (l_proc_level >= l_dbg_level)
1727 THEN
1728 fnd_log.STRING(l_proc_level
1729 ,g_module_prefix || l_procedure_name || '.end'
1730 ,'end procedure');
1731 END IF;
1732
1733 EXCEPTION
1734 WHEN OTHERS THEN
1735 IF fnd_log.level_unexpected >= l_dbg_level
1736 THEN
1737 fnd_log.STRING(fnd_log.level_unexpected
1738 ,g_module_prefix || l_procedure_name || '.OTHER_EXCEPTION'
1739 ,SQLCODE || SQLERRM);
1740 END IF;
1741 RAISE;
1742 END Run_Consolidation_Mapping;
1743
1744 --==========================================================================
1745 -- PROCEDURE NAME:
1746 --
1747 -- Populate_Invoice_Type Public
1748 --
1749 -- DESCRIPTION:
1750 --
1751 -- In R12.1.1, there were 2 sql files need be manually run to migrate the
1752 -- setup and transaction data from GTA 12.0 to GTA 12.1.
1753 -- In R12.1.2, we convert this two sql into concurrent programs which
1754 -- can be run by user from UI.
1755 -- This procedure is to populate data to INVOICE_TYPE column for
1756 -- Transfer Rule and System Option tables.
1757 -- PARAMETERS:
1758 -- In:
1759 --
1760 -- Out: errbuf
1761 -- retcode
1762 --
1763 -- DESIGN REFERENCES:
1764 -- GTA_12.1.2_Technical_Design.doc
1765 --
1766 -- CHANGE HISTORY:
1767 --
1768 -- 16-Aug-2009: Allen Yang Created
1769 --
1770 --===========================================================================
1771 PROCEDURE Populate_Invoice_Type
1772 (errbuf OUT NOCOPY VARCHAR2
1773 ,retcode OUT NOCOPY VARCHAR2
1774 )
1775 IS
1776 l_procedure_name VARCHAR2(30) := 'Populate_Invoice_Type';
1777 l_ar_gta_gta_not_enabled VARCHAR2(300);
1778 l_conc_succ BOOLEAN;
1779
1780 l_org_id NUMBER := mo_global.get_current_org_id;
1781
1782 BEGIN
1783 --procedure begin
1784 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1785 THEN
1786 fnd_log.STRING(fnd_log.level_procedure
1787 ,g_module_prefix || l_procedure_name || '.begin'
1788 ,'Procedure begin');
1789 END IF;
1790
1791 IF fnd_profile.VALUE('AR_GTA_ENABLED') = 'N'
1792 THEN
1793 fnd_message.set_name('AR'
1794 ,'AR_GTA_GTA_NOT_ENABLE');
1795
1796 l_ar_gta_gta_not_enabled := fnd_message.get;
1797
1798 -- Output the context of l_ar_gta_gta_not_enabled
1799 fnd_file.put_line(fnd_file.output
1800 ,l_ar_gta_gta_not_enabled);
1801 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1802 ,message => l_ar_gta_gta_not_enabled);
1803
1804 RETURN;
1805 END IF;
1806
1807 ar_gta_trx_util.Populate_Invoice_Type(l_org_id);
1808
1809 --procedure end
1810 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1811 THEN
1812 fnd_log.STRING(fnd_log.level_procedure
1813 ,g_module_prefix || l_procedure_name || '.end'
1814 ,'Procedure end');
1815 END IF;
1816
1817 EXCEPTION
1818 WHEN OTHERS THEN
1819 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1820 THEN
1821 fnd_log.STRING(fnd_log.level_unexpected
1822 ,g_module_prefix || l_procedure_name ||
1823 '.OTHER_EXCEPTION '
1824 ,SQLCODE || SQLERRM);
1825 END IF;
1826 RAISE;
1827 END Populate_Invoice_Type;
1828
1829 --==========================================================================
1830 -- PROCEDURE NAME:
1831 --
1832 -- Populate_Invoice_Type_Header Public
1833 --
1834 -- DESCRIPTION:
1835 --
1836 -- In R12.1.1, there were 2 sql files need be manually run to migrate the
1837 -- setup and transaction data from GTA 12.0 to GTA 12.1.
1838 -- In R12.1.2, we convert this two sql into concurrent programs which
1839 -- can be run by user from UI.
1840 -- This procedure is to populate data to INVOICE_TYPE column for
1841 -- GTA Invoice Header table.
1842 -- PARAMETERS:
1843 -- In:
1844 --
1845 -- Out: errbuf
1846 -- retcode
1847 --
1848 -- DESIGN REFERENCES:
1849 -- GTA_12.1.2_Technical_Design.doc
1850 --
1851 -- CHANGE HISTORY:
1852 --
1853 -- 16-Aug-2009: Allen Yang Created
1854 --
1855 --===========================================================================
1856 PROCEDURE Populate_Invoice_Type_Header
1857 (errbuf OUT NOCOPY VARCHAR2
1858 ,retcode OUT NOCOPY VARCHAR2
1859 )
1860 IS
1861 l_procedure_name VARCHAR2(30) := 'Populate_Invoice_Type_Header';
1862 l_ar_gta_gta_not_enabled VARCHAR2(300);
1863 l_conc_succ BOOLEAN;
1864
1865 l_org_id NUMBER := mo_global.get_current_org_id;
1866
1867 BEGIN
1868 --procedure begin
1869 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1870 THEN
1871 fnd_log.STRING(fnd_log.level_procedure
1872 ,g_module_prefix || l_procedure_name || '.begin'
1873 ,'Procedure begin');
1874 END IF;
1875
1876 IF fnd_profile.VALUE('AR_GTA_ENABLED') = 'N'
1877 THEN
1878 fnd_message.set_name('AR'
1879 ,'AR_GTA_GTA_NOT_ENABLE');
1880
1881 l_ar_gta_gta_not_enabled := fnd_message.get;
1882
1883 -- Output the context of l_ar_gta_gta_not_enabled
1884 fnd_file.put_line(fnd_file.output
1885 ,l_ar_gta_gta_not_enabled);
1886 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1887 ,message => l_ar_gta_gta_not_enabled);
1888
1889 RETURN;
1890 END IF;
1891
1892 ar_gta_trx_util.Populate_Invoice_Type_Header(l_org_id);
1893
1894 --procedure end
1895 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1896 THEN
1897 fnd_log.STRING(fnd_log.level_procedure
1898 ,g_module_prefix || l_procedure_name || '.end'
1899 ,'Procedure end');
1900 END IF;
1901
1902 EXCEPTION
1903 WHEN OTHERS THEN
1904 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1905 THEN
1906 fnd_log.STRING(fnd_log.level_unexpected
1907 ,g_module_prefix || l_procedure_name ||
1908 '.OTHER_EXCEPTION '
1909 ,SQLCODE || SQLERRM);
1910 END IF;
1911 RAISE;
1912 END Populate_Invoice_Type_Header;
1913
1914 END AR_GTA_CONC_PROG;