[Home] [Help]
PACKAGE BODY: APPS.JMF_GTA_CONC_PROG
Source
1 PACKAGE BODY JMF_GTA_CONC_PROG AS
2 --$Header: JMFCCPGB.pls 120.12.12010000.2 2008/11/17 13:31:37 dwang ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JMFCCPGB.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 --| |
26 --| HISTORY |
27 --| 20-APR-2005: Jim Zheng |
28 --| 08-MAY-2005: Qiang Li |
29 --| 20-MAY-2005: Jogen Hu add Import_GT_invoices |
30 --| Transfer_Invoices_to_GT |
31 --| Transfer_Trxs_from_workbench |
32 --| 13-Jun-2005: Donghai Wang add procedure Discrepancy_Report |
33 --| and Item_Export |
34 --| 01-Jul-2005: Jim Zheng Update after code review, |
35 --| chang parameter type. |
36 --| 25-Aug-2005: Jogen Hu for import invoices, |
37 --| move clearing temporary table from |
38 --| JMF_GTA_TXT_OPERATOR_PROC into this |
39 --| 28-Sep-2005: Jonge Hu change transfer_invoices_to_gt |
40 --| 18-Oct-2005: Donghai Wang Update 'Transrfer_Invoices_To_GT' |
41 --| procedure to adjust order of |
42 --| paramerts |
43 --| 16-Nov-2005: Jim Zheng Change the output of gta_not_Enable |
44 --| 16-Nov-2005: Qiang Li Change Purge_invoice,run_AR_GT_mappi|
45 --| ng, to set their status to warnning |
46 --| 23-Nov-2005: Donghai Wang Update procedure Discrepancy_Report,|
47 --| set its status to 'Warnning' when |
48 --| Profile 'GTA Not Enabled' is set to |
49 --| 'No' |
50 --| 30-Nov-2005: Qiang Li Change set_of_books_id to ledger_id |
51 --| 01-Dec-2005: Qiang Li Use function Get_AR_Batch_Source_Name
52 --| to translate source id to source name
53 --| in Run_AR_GT_Mapping procedure |
54 --| 06-Mar-2006: Donghai Wang Update Discrepancy_Report and |
55 --| Item_Eport for adding fnd log |
56 --| 26-Apr-2006: Qiang Li Update the PROCEDURE Purge_Invoice |
57 --| 14-Sep-2006: Qiang Li Update the PROCEDURE Purge_Invoice | |
58 --+======================================================================*/
59
60 --==========================================================================
61 -- PROCEDURE NAME:
62 --
63 -- Transfer_AR_Transactions Public
64 --
65 -- DESCRIPTION:
66 --
67 -- This procedure is the main program for transfer program.
68 --
69 -- PARAMETERS:
70 -- In: p_transfer_id Transfer rule id
71 -- p_customer_num_from Customer number from
72 -- p_customer_num_to Customer number to
73 -- p_customer_name_from Customer name from
74 -- p_customer_name_to Customer name to
75 -- p_gl_period GL period
76 -- p_gl_date_from GL date from
77 -- p_gl_date_to GL date to
78 -- p_trx_batch_from Batch number from
79 -- p_trx_batch_to Batch number to
80 -- p_trx_number_from Trx number from
81 -- p_trx_number_to Trx number to
82 -- p_trx_date_from Trx date from
83 -- p_trx_date_to Trx date to
84 -- p_doc_num_from Doc number from
85 -- p_doc_num_to Doc number to
86 -- Out: errbuf
87 -- retcode
88 --
89 -- DESIGN REFERENCES:
90 -- GTA-TRANSFER-PROGRAM-TD.doc
91 --
92 -- CHANGE HISTORY:
93 --
94 -- 05-MAY-2005: Jim.Zheng Created
95 --
96 --===========================================================================
97 PROCEDURE transfer_ar_transactions
98 (errbuf OUT NOCOPY VARCHAR2
99 ,retcode OUT NOCOPY VARCHAR2
100 ,p_transfer_id IN VARCHAR2
101 ,p_customer_num_from IN VARCHAR2
102 ,p_customer_num_to IN VARCHAR2
103 ,p_customer_name_from IN VARCHAR2
104 ,p_customer_name_to IN VARCHAR2
105 ,p_gl_period IN VARCHAR2
106 ,p_gl_date_from IN VARCHAR2
107 ,p_gl_date_to IN VARCHAR2
108 ,p_trx_batch_from IN VARCHAR2
109 ,p_trx_batch_to IN VARCHAR2
110 ,p_trx_number_from IN VARCHAR2
111 ,p_trx_number_to IN VARCHAR2
112 ,p_trx_date_from IN VARCHAR2
113 ,p_trx_date_to IN VARCHAR2
114 ,p_doc_num_from IN NUMBER
115 ,p_doc_num_to IN NUMBER
116 ) IS
117 l_procedure_name VARCHAR2(50) := 'transfer_AR_to_GTA';
118 l_parameters jmf_gta_trx_util.transferparas_rec_type;
119 l_jmf_gta_gta_not_enabled VARCHAR2(4000);
120 l_conc_succ BOOLEAN;
121 l_errbuf VARCHAR2(4000);
122 l_retcode VARCHAR2(4000);
123 l_org_id NUMBER := mo_global.get_current_org_id;
124
125 BEGIN
126 --begin procedure
127 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
128 THEN
129 fnd_log.STRING(fnd_log.level_procedure
130 ,g_module_prefix || l_procedure_name
131 ,'Begin Procedure. ');
132 END IF;
133
134 -- wrap parameter to record
135 IF (fnd_profile.VALUE('JMF_GTA_ENABLED') = 'Y')
136 THEN
137 l_parameters.customer_num_from := p_customer_num_from;
138 l_parameters.customer_num_to := p_customer_num_to;
139 l_parameters.customer_name_from := p_customer_name_from;
140 l_parameters.customer_name_to := p_customer_name_to;
141 l_parameters.gl_period := p_gl_period;
142 l_parameters.gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
143 l_parameters.gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
144 l_parameters.trx_batch_from := p_trx_batch_from;
145 l_parameters.trx_batch_to := p_trx_batch_to;
146 l_parameters.trx_number_from := p_trx_number_from;
147 l_parameters.trx_number_to := p_trx_number_to;
148 l_parameters.trx_date_from := fnd_date.canonical_to_date(p_trx_date_from);
149 l_parameters.trx_date_to := fnd_date.canonical_to_date(p_trx_date_to);
150 l_parameters.doc_num_from := p_doc_num_from;
151 l_parameters.doc_num_to := p_doc_num_to;
152
153 -- call JMF_GTA_ARTRX_PROC_JIM.transfer_AR_to_GTA
154 jmf_gta_artrx_proc.transfer_ar_to_gta(errbuf => l_errbuf
155 ,retcode => l_retcode
156 ,p_org_id => l_org_id
157 ,p_transfer_id => p_transfer_id
158 ,p_conc_parameters => l_parameters);
159
160 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
161 THEN
162 fnd_log.STRING(fnd_log.level_procedure
163 ,g_module_prefix || l_procedure_name
164 ,'errbuf is : ' || l_errbuf || ' retcode is :' ||
165 l_retcode);
166 END IF;
167
168 ELSE
169 -- report JMF_GTA_DISABLE_ERROR in xml format
170 -- set concurrent status to WARNING
171 fnd_message.set_name('JMF'
172 ,'JMF_GTA_GTA_NOT_ENABLE');
173 l_jmf_gta_gta_not_enabled := '<TransferReport>
174 <ReportFailed>Y</ReportFailed>
175 <ReportFailedMsg>' ||
176 fnd_message.get ||
177 '</ReportFailedMsg>
178 <FailedWithParameters>Y</FailedWithParameters>
179 </TransferReport>';
180
181 fnd_file.put_line(fnd_file.output
182 ,l_jmf_gta_gta_not_enabled);
183
184 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
185 ,message => l_jmf_gta_gta_not_enabled);
186 RETURN;
187 END IF;
188 -- end procedure
189 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
190 THEN
191 fnd_log.STRING(fnd_log.level_procedure
192 ,g_module_prefix || l_procedure_name
193 ,'End Procedure. ');
194 END IF;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
199 THEN
200 fnd_log.STRING(fnd_log.level_unexpected
201 ,g_module_prefix || l_procedure_name ||
202 '. OTHER_EXCEPTION '
203 ,SQLCODE || SQLERRM);
204 END IF;
205 RAISE;
206
207 END transfer_ar_transactions;
208
209 --==========================================================================
210 -- PROCEDURE NAME:
211 --
212 -- Purge_Invoice Public
213 --
214 -- DESCRIPTION:
215 --
216 -- This procedure is the main program for purge program,
217 -- it search eligible records in GTA invoice tables first,
218 -- if find any, then invoke corresponding table handlers to
219 -- remove these records from db.
220 --
221 -- PARAMETERS:
222 -- In: p_ledger_id Ledger identifier
223 -- p_customer_name Customer name
224 -- p_gl_date_from GL date low range
225 -- p_gl_date_to GL date high range
226 --
227 -- Out: errbuf
228 -- retcode
229 --
230 -- DESIGN REFERENCES:
231 -- GTA-PURGE-PROGRAM-TD.doc
232 --
233 -- CHANGE HISTORY:
234 --
235 -- 8-MAY-2005: Qiang Li Created.
236 -- 16-Nov-2005:Qiang Li When GTA profile is not enabled,set concurrent
237 -- status to 'Warnning'
238 -- 30-Nov-2005: Qiang Li Change set_of_books_id to ledger_id
239 -- 26-Jun-2006: Qiang Li Remove the condition of GL period is closed
240 -- 14-Sep-2006: Qiang Li Use function To_Xsd_Date_String to replace
241 -- format_date, according to new XML template standards
242 --===========================================================================
243
244 PROCEDURE purge_invoice
245 (errbuf OUT NOCOPY VARCHAR2
246 ,retcode OUT NOCOPY VARCHAR2
247 ,p_ledger_id IN NUMBER
248 ,p_customer_name IN VARCHAR2
249 ,p_gl_date_from IN VARCHAR2
250 ,p_gl_date_to IN VARCHAR2
251 )
252 IS
253 l_org_id NUMBER := mo_global.get_current_org_id;
254 l_procedure_name VARCHAR2(30) := 'Purge_Invoice';
255 l_gl_date_from DATE;
256 l_gl_date_to DATE;
257 l_dbg_msg VARCHAR2(1000);
258 l_gta_header_count NUMBER;
259 l_gt_header_count NUMBER;
260 l_gta_line_count NUMBER;
261 l_gt_line_count NUMBER;
262 l_line_count NUMBER;
263 l_gta_trx_header_id jmf_gta_trx_headers_all.gta_trx_header_id%TYPE;
264 l_source jmf_gta_trx_headers_all.SOURCE%TYPE;
265 l_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
266 l_output_msg VARCHAR2(2000);
267 l_no_data_flag VARCHAR2(1) := 'N';
268 l_report xmltype;
269 l_summary xmltype;
270 l_parameter xmltype;
271 l_jmf_gta_enabled fnd_profile_option_values.profile_option_value%TYPE := NULL;
272 l_jmf_gta_gta_not_enabled VARCHAR2(500);
273 l_no_data_message VARCHAR2(500);
274 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
275 l_proc_level NUMBER := fnd_log.level_procedure;
276 l_conc_succ BOOLEAN;
277
278
279
280 CURSOR c_gta_header IS
281 SELECT
282 jgth.gta_trx_header_id
283 ,jgth.SOURCE
284 ,rct.customer_trx_id
285 FROM
286 jmf_gta_trx_headers_all jgth
287 ,ra_customer_trx_all rct
288 WHERE jgth.org_id = l_org_id
289 AND jgth.status IN ('FAILED', 'COMPLETED', 'CANCELLED')
290 AND jgth.bill_to_customer_name LIKE nvl(p_customer_name,'%')
291 AND jgth.ra_gl_date >= l_gl_date_from
292 AND jgth.ra_gl_date <= l_gl_date_to
293 AND jgth.ra_trx_id = rct.customer_trx_id(+)
294 FOR UPDATE;
295
296 BEGIN
297
298 --logging for debug
299 IF (l_proc_level >= l_dbg_level)
300 THEN
301 fnd_log.STRING(l_proc_level
302 ,g_module_prefix || l_procedure_name || '.begin'
303 ,'enter procedure');
304 END IF; /*IF (l_proc_level>=l_dbg_level)*/
305
306 -- Data conversion
307 l_gl_date_from := fnd_date.canonical_to_date(p_gl_date_from);
308 l_gl_date_to := fnd_date.canonical_to_date(p_gl_date_to);
309 l_gta_header_count := 0;
310 l_gt_header_count := 0;
311 l_gta_line_count := 0;
312 l_gt_line_count := 0;
313
314 SELECT xmlelement("Parameters"
315 ,xmlforest(jmf_gta_trx_util.get_operatingunit(l_org_id) AS
316 "OperationUnit"
317 ,p_customer_name AS
318 "ARCustomerName"
319 ,jmf_gta_trx_util.To_Xsd_Date_String(l_gl_date_from) AS
320 "ARTrxGLDateFrom"
321 ,jmf_gta_trx_util.To_Xsd_Date_String(l_gl_date_to) AS
322 "ARTrxGLDateTo"))
323 INTO l_parameter
324 FROM dual;
325
326 fnd_profile.get('JMF_GTA_ENABLED'
327 ,l_jmf_gta_enabled);
328
329 IF nvl(l_jmf_gta_enabled
330 ,'N') = 'N'
331 THEN
332 fnd_message.set_name('JMF'
333 ,'JMF_GTA_GTA_NOT_ENABLE');
334 l_jmf_gta_gta_not_enabled := fnd_message.get();
335
336 -- Output the context of l_jmf_gta_gta_not_enabled
337 SELECT xmlelement("PurgeReport"
338 ,xmlconcat(xmlelement("ReportFailed"
339 ,'Y')
340 ,xmlelement("FailedWithParameters"
341 ,'N')
345 ,l_jmf_gta_gta_not_enabled)
342 ,xmlelement("RepDate"
343 ,jmf_gta_trx_util.To_Xsd_Date_String(SYSDATE))
344 ,xmlelement("ReportFailedMsg"
346 ,l_parameter))
347 INTO l_report
348 FROM dual;
349
350 jmf_gta_trx_util.output_conc(l_report.getclobval());
351 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
352 ,message => l_jmf_gta_gta_not_enabled);
353 RETURN;
354 END IF; /*IF NVL(l_jmf_gta_enabled,'N')<>'Y'*/
355
356
357
358 --Main process for purge
359 OPEN c_gta_header;
360 FETCH c_gta_header
361 INTO l_gta_trx_header_id, l_source, l_customer_trx_id;
362
363 WHILE c_gta_header%FOUND
364 LOOP
365 IF l_customer_trx_id IS NULL
366 THEN
367 SELECT
368 COUNT(*)
369 INTO
370 l_line_count
371 FROM
372 jmf_gta_trx_lines_all
373 WHERE gta_trx_header_id = l_gta_trx_header_id;
374
375 -- delete GTA and gt invoices inclunding headers and lines
376 -- according to GTA_TRX_HEADER_ID
377 jmf_gta_trx_util.delete_header_line_cascade(p_gta_trx_header_id => l_gta_trx_header_id);
378
379 IF l_source = 'AR' --count deleted GTA transaction headers and lines
380 THEN
381 l_gta_header_count := l_gta_header_count + 1;
382 l_gta_line_count := l_gta_line_count + l_line_count;
383
384 ELSIF l_source = 'GT' --count deleted GT transaction headers and lines
385 THEN
386 l_gt_header_count := l_gt_header_count + 1;
387 l_gt_line_count := l_gt_line_count + l_line_count;
388 END IF; /*IF l_source='AR'*/
389
390 l_line_count := 0;
391 END IF; /*IF l_customer_trx_id IS NULL*/
392
393 FETCH c_gta_header
394 INTO l_gta_trx_header_id, l_source, l_customer_trx_id;
395 END LOOP; /*WHILE c_gta_header%FOUND*/
396
397 CLOSE c_gta_header;
398
399 SELECT xmlelement("Summary"
400 ,xmlforest(l_gta_header_count AS "GTATrxHeaderPurged"
401 ,l_gta_line_count AS "GTATrxLinePurged"
402 ,l_gt_header_count AS "GTTrxHeaderPurged"
403 ,l_gt_line_count AS "GTTrxLinePurged"))
404 INTO l_summary
405 FROM dual;
406
407 --Generate Reports Xml Data
408 SELECT xmlelement("PurgeReport"
409 ,xmlconcat(xmlelement("ReportFailed"
410 ,'N')
411 ,xmlelement("FailedWithParameters"
412 ,'N')
413 ,xmlelement("RepDate"
414 ,jmf_gta_trx_util.To_Xsd_Date_String(SYSDATE))
415 ,l_parameter
416 ,l_summary))
417 INTO l_report
418 FROM dual;
419
420 jmf_gta_trx_util.output_conc(l_report.getclobval());
421
422 --logging for debug
423 IF (l_proc_level >= l_dbg_level)
424 THEN
425 fnd_log.STRING(l_proc_level
426 ,g_module_prefix || l_procedure_name || '.end'
427 ,'end procedure');
428 END IF; /*IF (l_proc_level>=l_dbg_level)*/
429
430 COMMIT;
431 EXCEPTION
432 WHEN OTHERS THEN
433 IF fnd_log.level_unexpected >= l_dbg_level
434 THEN
435 fnd_log.STRING(fnd_log.level_unexpected
436 ,g_module_prefix || l_procedure_name || '.OTHER_EXCEPTION'
437 ,SQLCODE || SQLERRM);
438 END IF;
439 RAISE;
440 ROLLBACK;
441 END purge_invoice;
442
443 --==========================================================================
444 -- PROCEDURE NAME:
445 --
446 -- Run_AR_GT_Mapping Public
447 --
448 -- DESCRIPTION:
449 --
450 -- This Concurrent program Generate Mapping Report Data
451 --
452 -- PARAMETERS:
453 -- In: p_fp_tax_reg_num First Party Tax Registration Number
454 -- p_trx_source Transaction source,GT or AR
455 -- P_Customer_Id Customer id
456 -- p_gt_inv_num_from GT Invoice Number low range
457 -- p_gt_inv_num_to GT Invoice Number high range
458 -- p_gt_inv_date_from GT Invoice Date low range
459 -- p_gt_inv_date_to GT Invoice Date high range
460 -- p_ar_inv_num_from AR Invoice Number low range
461 -- p_ar_inv_num_to AR Invoice Number high range
462 -- p_ar_inv_date_from AR Invoice Date low range
463 -- p_ar_inv_date_to AR Invoice Date high range
464 --
465 -- Out: errbuf
466 -- retcode
467 --
468 -- DESIGN REFERENCES:
469 -- GTA_REPORTS_TD.doc
470 --
471 -- CHANGE HISTORY:
472 --
473 -- 8-MAY-2005: Qiang Li Created.
474 -- 27-Sep-2005:Qiang Li Add a new parameter fp_tax_reg_number
475 -- 16-Nov-2005:Qiang Li When GTA profile is not enabled,set concurrent
476 -- status to 'Warnning'
477 --
481 ,retcode OUT NOCOPY VARCHAR2
478 --===========================================================================
479 PROCEDURE run_ar_gt_mapping
480 (errbuf OUT NOCOPY VARCHAR2
482 ,p_fp_tax_reg_num IN VARCHAR2
483 ,p_trx_source IN NUMBER
484 ,p_customer_id IN VARCHAR2
485 ,p_gt_inv_num_from IN VARCHAR2
486 ,p_gt_inv_num_to IN VARCHAR2
487 ,p_gt_inv_date_from IN VARCHAR2
488 ,p_gt_inv_date_to IN VARCHAR2
489 ,p_ar_inv_num_from IN VARCHAR2
490 ,p_ar_inv_num_to IN VARCHAR2
491 ,p_ar_inv_date_from IN VARCHAR2
492 ,p_ar_inv_date_to IN VARCHAR2
493 )
494 IS
495 l_procedure_name VARCHAR2(30) := 'run_AR_GT_Mapping';
496 l_gt_inv_date_from DATE;
497 l_gt_inv_date_to DATE;
498 l_ar_inv_date_from DATE;
499 l_ar_inv_date_to DATE;
500 l_jmf_gta_enabled fnd_profile_option_values.profile_option_value%TYPE := NULL;
501 l_jmf_gta_gta_not_enabled VARCHAR2(500);
502 l_report xmltype;
503 l_parameter xmltype;
504 l_dbg_msg VARCHAR2(500);
505 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
506 l_proc_level NUMBER := fnd_log.level_procedure;
507 l_org_id NUMBER := mo_global.get_current_org_id;
508 l_conc_succ BOOLEAN;
509 BEGIN
510 --logging for debug
511 IF (l_proc_level >= l_dbg_level)
512 THEN
513 fnd_log.STRING(l_proc_level
514 ,g_module_prefix || l_procedure_name || '.begin'
515 ,'enter procedure');
516 END IF;
517
518 l_gt_inv_date_from := fnd_date.canonical_to_date(p_gt_inv_date_from);
519 l_gt_inv_date_to := fnd_date.canonical_to_date(p_gt_inv_date_to);
520 l_ar_inv_date_from := fnd_date.canonical_to_date(p_ar_inv_date_from);
521 l_ar_inv_date_to := fnd_date.canonical_to_date(p_ar_inv_date_to);
522
523 fnd_profile.get('JMF_GTA_ENABLED'
524 ,l_jmf_gta_enabled);
525 IF nvl(l_jmf_gta_enabled
526 ,'N') = 'N'
527 THEN
528 SELECT xmlelement("Parameters"
529 ,xmlforest(jmf_gta_trx_util.get_operatingunit(l_org_id)
530 AS "OperationUnit"
531 ,p_fp_tax_reg_num
532 AS "TaxRegistrationNumber"
533 ,jmf_gta_trx_util.Get_AR_Batch_Source_Name
534 ( l_org_id
535 , p_trx_source)
536 AS "TransactionSource"
537 ,jmf_gta_trx_util.get_customer_name(p_customer_id)
538 AS "ARCustomerName"
539 ,p_gt_inv_num_from
540 AS "GTInvoiceNumFrom"
541 ,p_gt_inv_num_to
542 AS "GTInvoiceNumTo"
543 ,jmf_gta_trx_util.To_Xsd_Date_String(l_gt_inv_date_from)
544 AS "GTDateFrom"
545 ,jmf_gta_trx_util.To_Xsd_Date_String(l_gt_inv_date_to)
546 AS "GTDateTo"
547 ,p_ar_inv_num_from
548 AS "ARTrxNumberFrom"
549 ,p_ar_inv_num_to
550 AS "ARTrxNumberTo"
551 ,jmf_gta_trx_util.To_Xsd_Date_String(l_ar_inv_date_from)
552 AS "ARTrxDateFrom"
553 ,jmf_gta_trx_util.To_Xsd_Date_String(l_ar_inv_date_to)
554 AS "ARTrxDateTo"))
555 INTO l_parameter
556 FROM dual;
557
558 fnd_message.set_name('JMF'
559 ,'JMF_GTA_GTA_NOT_ENABLE');
560 l_jmf_gta_gta_not_enabled := fnd_message.get();
561
562 -- Output the context of l_jmf_gta_gta_not_enabled
563 SELECT xmlelement("MappingReport"
564 ,xmlconcat(xmlelement("ReportFailed"
565 ,'Y')
566 ,xmlelement("FailedWithParameters"
567 ,'N')
568 ,xmlelement("RepDate"
569 ,jmf_gta_trx_util.To_Xsd_Date_String(SYSDATE))
570 ,xmlelement("ReportFailedMsg"
571 ,l_jmf_gta_gta_not_enabled)
572 ,l_parameter))
573 INTO l_report
574 FROM dual;
575
576 jmf_gta_trx_util.output_conc(l_report.getclobval());
577 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
578 ,message => l_jmf_gta_gta_not_enabled);
579 ELSE
580 NULL;
581
582 jmf_gta_reports_pkg.generate_mapping_rep(p_org_id => l_org_id
583 ,p_fp_tax_reg_num => p_fp_tax_reg_num
584 ,p_trx_source => p_trx_source
585 ,p_customer_id => p_customer_id
586 ,p_gt_inv_num_from => p_gt_inv_num_from
587 ,p_gt_inv_num_to => p_gt_inv_num_to
591 ,p_ar_inv_num_to => p_ar_inv_num_to
588 ,p_gt_inv_date_from => l_gt_inv_date_from
589 ,p_gt_inv_date_to => l_gt_inv_date_to
590 ,p_ar_inv_num_from => p_ar_inv_num_from
592 ,p_ar_inv_date_from => l_ar_inv_date_from
593 ,p_ar_inv_date_to => l_ar_inv_date_to);
594
595 END IF;
596
597 --logging for debug
598 IF (l_proc_level >= l_dbg_level)
599 THEN
600 fnd_log.STRING(l_proc_level
601 ,g_module_prefix || l_procedure_name || '.end'
602 ,'end procedure');
603 END IF;
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 IF fnd_log.level_unexpected >= l_dbg_level
608 THEN
609 fnd_log.STRING(fnd_log.level_unexpected
610 ,g_module_prefix || l_procedure_name || '.OTHER_EXCEPTION'
611 ,SQLCODE || SQLERRM);
612 END IF;
613 RAISE;
614
615 END run_ar_gt_mapping;
616
617 --==========================================================================
618 -- PROCEDURE NAME:
619 --
620 -- Import_GT_Invoices Public
621 --
622 -- DESCRIPTION:
623 --
624 -- This procedure is program of SRS concurrent for import
625 -- flat file exported from Golden Tax system
626 --
627 -- PARAMETERS:
628 -- In:
629 --
630 -- Out: errbuf
631 -- retcode
632 --
633 -- DESIGN REFERENCES:
634 -- GTA-Txt-Interface-TD.doc
635 --
636 -- CHANGE HISTORY:
637 --
638 -- 20-MAY-2005: Jogen Hu Created
639 --
640 -- 15-AUG-2005: Jogen Hu Move clear temporary table from
641 -- package jmf_gta_txt_operator_proc
642 --===========================================================================
643 PROCEDURE import_gt_invoices
644 (errbuf OUT NOCOPY VARCHAR2
645 ,retcode OUT NOCOPY VARCHAR2
646 )
647 IS
648 l_procedure_name VARCHAR2(30) := 'Import_GT_invoices';
649 l_jmf_gta_gta_not_enabled VARCHAR2(300);
650 l_conc_succ BOOLEAN;
651
652 BEGIN
653 --procedure begin
654 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
655 THEN
656 fnd_log.STRING(fnd_log.level_procedure
657 ,g_module_prefix || l_procedure_name || '.begin'
658 ,'Procedure begin');
659 END IF;
660
661 IF fnd_profile.VALUE('JMF_GTA_ENABLED') = 'N'
662 THEN
663 fnd_message.set_name('JMF'
664 ,'JMF_GTA_GTA_NOT_ENABLE');
665
666 l_jmf_gta_gta_not_enabled := '<ImportReport>
667 <ReportFailed>Y</ReportFailed>
668 <ReportFailedMsg>' ||
669 fnd_message.get ||
670 '</ReportFailedMsg>
671 <FailedWithParameters>Y</FailedWithParameters>
672 </ImportReport>';
673
674 -- Output the context of l_jmf_gta_gta_not_enabled
675 fnd_file.put_line(fnd_file.output
676 ,l_jmf_gta_gta_not_enabled);
677 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
678 ,message => l_jmf_gta_gta_not_enabled);
679
680 jmf_gta_txt_operator_proc.Clear_Imp_Temp_Table;
681 RETURN;
682 END IF;
683
684 jmf_gta_txt_operator_proc.import_invoices;
685
686 jmf_gta_txt_operator_proc.Clear_Imp_Temp_Table;
687
688 --procedure end
689 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
690 THEN
691 fnd_log.STRING(fnd_log.level_procedure
692 ,g_module_prefix || l_procedure_name || '.end'
693 ,'Procedure end');
694 END IF;
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
699 THEN
700 fnd_log.STRING(fnd_log.level_unexpected
701 ,g_module_prefix || l_procedure_name ||
702 '.OTHER_EXCEPTION '
703 ,SQLCODE || SQLERRM);
704 END IF;
705
706 jmf_gta_txt_operator_proc.Clear_Imp_Temp_Table;
707
708 RAISE;
709 END import_gt_invoices;
710
711 --==========================================================================
712 -- PROCEDURE NAME:
713 --
714 -- Transfer_Invoices_to_GT Public
715 --
716 -- DESCRIPTION:
717 --
718 -- This procedure is a SRS concurrent program which exports GTA
719 -- invoices to the flat file Its output will be printed on concurrent
720 -- output and will be save as flat file by users.
721 --
722 -- PARAMETERS:
723 -- In: p_regeneration IN VARCHAR2
724 -- p_fp_tax_reg_num in varchar2
725 -- p_new_batch_dummy IN VARCHAR2
726 -- p_regeneration_dummy IN VARCHAR2
727 -- p_transfer_rule_id IN NUMBER
731 -- p_cust_id_from_taxpayer IN NUMBER
728 -- p_batch_number IN VARCHAR2
729 -- p_customer_id_from_number IN NUMBER
730 -- p_customer_id_from_name IN NUMBER
732 -- p_ar_trx_num_from IN VARCHAR2
733 -- p_ar_trx_num_to IN VARCHAR2
734 -- p_ar_trx_date_from IN VARCHAR2
735 -- p_ar_trx_date_to IN VARCHAR2
736 -- p_ar_trx_gl_date_from IN VARCHAR2
737 -- p_ar_trx_gl_date_to IN VARCHAR2
738 -- p_ar_trx_batch_from IN VARCHAR2
739 -- p_ar_trx_batch_to IN VARCHAR2
740 -- p_trx_class IN VARCHAR2
741 -- p_batch_id IN VARCHAR2
742 --
743 -- Out: errbuf
744 -- retcode
745 --
746 -- DESIGN REFERENCES:
747 -- GTA-Txt-Interface-TD.doc
748 --
749 -- CHANGE HISTORY:
750 --
751 -- 20-MAY-2005: Jogen Hu Created
752 -- 28-Sep-2005: Jogen Hu add parameter p_fp_tax_reg_num
753 -- 18-Oct-2005: Donghai Wang move the parameter 'p_fp_tax_reg_num'
754 -- behind the parameter 'p_regeneration_dummy'
755 --
756 --===========================================================================
757 PROCEDURE transfer_invoices_to_gt
758 (errbuf OUT NOCOPY VARCHAR2
759 ,retcode OUT NOCOPY VARCHAR2
760 ,p_regeneration IN VARCHAR2
761 ,p_new_batch_dummy IN VARCHAR2
762 ,p_regeneration_dummy IN VARCHAR2
763 ,p_fp_tax_reg_num IN VARCHAR2
764 ,p_transfer_rule_id IN NUMBER
765 ,p_batch_number IN VARCHAR2
766 ,p_customer_id_from_number IN NUMBER
767 ,p_customer_id_from_name IN NUMBER
768 ,p_cust_id_from_taxpayer IN NUMBER
769 ,p_ar_trx_num_from IN VARCHAR2
770 ,p_ar_trx_num_to IN VARCHAR2
771 ,p_ar_trx_date_from IN VARCHAR2
772 ,p_ar_trx_date_to IN VARCHAR2
773 ,p_ar_trx_gl_date_from IN VARCHAR2
774 ,p_ar_trx_gl_date_to IN VARCHAR2
775 ,p_ar_trx_batch_from IN VARCHAR2
776 ,p_ar_trx_batch_to IN VARCHAR2
777 ,p_trx_class IN VARCHAR2
778 ,p_batch_id IN VARCHAR2
779 ,p_invoice_type IN VARCHAR2
780 )
781 IS
782 l_procedure_name VARCHAR2(30) := 'Transfer_Invoices_to_GT';
783 l_org_id NUMBER;
784 l_ar_trx_date_from DATE;
785 l_ar_trx_date_to DATE;
786 l_ar_trx_gl_date_from DATE;
787 l_ar_trx_gl_date_to DATE;
788 l_jmf_gta_gta_not_enabled VARCHAR2(1000);
789 l_conc_succ BOOLEAN;
790
791 BEGIN
792 --procedure begin
793 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
794 THEN
795 fnd_log.STRING(fnd_log.level_procedure
796 ,g_module_prefix || l_procedure_name || '.begin'
797 ,'Procedure begin');
798 END IF;
799
800 IF fnd_profile.VALUE('JMF_GTA_ENABLED') = 'N'
801 THEN
802 fnd_message.set_name('JMF'
803 ,'JMF_GTA_GTA_NOT_ENABLE');
804 l_jmf_gta_gta_not_enabled := '//' || fnd_message.get;
805
806 -- Output the context of l_jmf_gta_gta_not_enabled
807 fnd_file.put_line(fnd_file.output
808 ,l_jmf_gta_gta_not_enabled);
809 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
810 ,message => l_jmf_gta_gta_not_enabled);
811 RETURN;
812 END IF;
813
814 l_ar_trx_date_from := fnd_date.canonical_to_date(p_ar_trx_date_from);
815 l_ar_trx_date_to := fnd_date.canonical_to_date(p_ar_trx_date_to);
816 l_ar_trx_gl_date_from := fnd_date.canonical_to_date(p_ar_trx_gl_date_from);
817 l_ar_trx_gl_date_to := fnd_date.canonical_to_date(p_ar_trx_gl_date_to);
818
819 l_org_id := mo_global.get_current_org_id;
820
821 JMF_GTA_TXT_OPERATOR_PROC.Export_Invoices_From_Conc(p_org_id => l_org_id
822 ,p_regeneration => p_regeneration
823 ,p_fp_tax_reg_number => p_fp_tax_reg_num
824 ,p_transfer_rule_id => p_transfer_rule_id
825 ,p_batch_number => p_batch_number
826 ,p_customer_id_from_number => p_customer_id_from_number
827 ,p_customer_id_from_name => p_customer_id_from_name
828 ,p_cust_id_from_taxpayer => p_cust_id_from_taxpayer
829 ,p_ar_trx_num_from => p_ar_trx_num_from
830 ,p_ar_trx_num_to => p_ar_trx_num_to
831 ,p_ar_trx_date_from => l_ar_trx_date_from
835 ,p_ar_trx_batch_from => p_ar_trx_batch_from
832 ,p_ar_trx_date_to => l_ar_trx_date_to
833 ,p_ar_trx_gl_date_from => l_ar_trx_gl_date_from
834 ,p_ar_trx_gl_date_to => l_ar_trx_gl_date_to
836 ,p_ar_trx_batch_to => p_ar_trx_batch_to
837 ,p_trx_class => p_trx_class
838 ,p_batch_id => p_batch_id
839 ,p_invoice_type_id => p_invoice_type
840 );
841 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
842 THEN
843 fnd_log.STRING(fnd_log.level_procedure
844 ,g_module_prefix || l_procedure_name || '.end'
845 ,'Procedure end');
846 END IF;
847
848 EXCEPTION
849 WHEN OTHERS THEN
850 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
851 THEN
852 fnd_log.STRING(fnd_log.level_unexpected
853 ,g_module_prefix || l_procedure_name ||
854 '.OTHER_EXCEPTION '
855 ,SQLCODE || SQLERRM);
856 END IF;
857 RAISE;
858
859 END transfer_invoices_to_gt;
860
861 --==========================================================================
862 -- PROCEDURE NAME:
863 --
864 -- Export_Invoices_from_Workbench Public
865 --
866 -- DESCRIPTION:
867 --
868 -- This procedure is a SRS concurrent program which exports VAT
869 -- invoices from GTA to flat file and is invoked in workbench
870 --
871 -- PARAMETERS:
872 -- In: p_org_id IN NUMBER
873 -- p_generator_ID IN NUMBER
874 -- p_batch_number IN VARCHAR2
875 --
876 -- Out: errbuf
877 -- retcode
878 --
879 -- DESIGN REFERENCES:
880 -- GTA-Txt-Interface-TD.doc
881 --
882 -- CHANGE HISTORY:
883 --
884 -- 20-MAY-2005: Jogen Hu Created
885 --
886 --==========================================================================
887 PROCEDURE transfer_trxs_from_workbench
888 (errbuf OUT NOCOPY VARCHAR2
889 ,retcode OUT NOCOPY VARCHAR2
890 ,p_org_id IN NUMBER
891 ,p_generator_id IN NUMBER
892 ,p_batch_number IN VARCHAR2
893 )
894 IS
895 l_procedure_name VARCHAR2(30) := 'Transfer_Trxs_from_workbench';
896 l_jmf_gta_gta_not_enabled VARCHAR2(300);
897 l_conc_succ BOOLEAN;
898
899 BEGIN
900 --procedure begin
901 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
902 THEN
903 fnd_log.STRING(fnd_log.level_procedure
904 ,g_module_prefix || l_procedure_name || '.begin'
905 ,'Procedure begin');
906 END IF;
907
908 IF fnd_profile.VALUE('JMF_GTA_ENABLED') = 'N'
909 THEN
910 fnd_message.set_name('JMF'
911 ,'JMF_GTA_GTA_NOT_ENABLE');
912 l_jmf_gta_gta_not_enabled := '//' || fnd_message.get;
913
914 -- Output the context of l_jmf_gta_gta_not_enabled
915 fnd_file.put_line(fnd_file.output
916 ,l_jmf_gta_gta_not_enabled);
917 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
918 ,message => l_jmf_gta_gta_not_enabled);
919 RETURN;
920 END IF;
921
922 jmf_gta_txt_operator_proc.export_invoices_from_workbench(p_org_id => p_org_id
923 ,p_generator_id => p_generator_id
924 ,p_batch_id => p_batch_number);
925
926 --procedure end
927 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
928 THEN
929 fnd_log.STRING(fnd_log.level_procedure
930 ,g_module_prefix || l_procedure_name || '.end'
931 ,'Procedure end');
932 END IF;
933
934 EXCEPTION
935 WHEN OTHERS THEN
936 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
937 THEN
938 fnd_log.STRING(fnd_log.level_unexpected
939 ,g_module_prefix || l_procedure_name ||
940 '.OTHER_EXCEPTION '
941 ,SQLCODE || SQLERRM);
942 END IF;
943 RAISE;
944 END transfer_trxs_from_workbench;
945
946 --==========================================================================
947 -- PROCEDURE NAME:
948 --
949 -- Discrepancy_Report Public
950 --
951 -- DESCRIPTION:
952 --
953 -- This procedure is called by concurren program 'Golden Tax
954 -- Discrepancy Report' to generte discrepancy report.
955 --
956 -- PARAMETERS:
957 -- In: p_gta_batch_num_from GTA invoice batch number low range
958 -- p_gta_batch_num_to GTA invoice batch number high range
959 -- p_ar_transaction_type AR transaction type
963 -- p_gl_period GL period name
960 -- p_cust_num_from Customer number low range
961 -- p_cust_num_to Customer number high range
962 -- p_cust_name_id Identifier of customer
964 -- p_gl_date_from GL date low range
965 -- p_gl_date_to GL date high range
966 -- p_ar_trx_batch_from AR transaction batch name low range
967 -- p_ar_trx_batch_to AR transaction batch name high range
968 -- P_ar_trx_num_from AR transaction number low range
969 -- P_ar_trx_num_to AR transaction number high range
970 -- p_ar_trx_date_from AR transaction date low range
971 -- p_ar_trx_date_to AR transaction date high range
972 -- p_ar_doc_num_from AR document sequnce number low range
973 -- p_ar_doc_num_to AR document sequnce number high range
974 -- p_original_curr_code Original currency code
975 -- p_primary_sales Identifier of primary salesperson
976 --
977 -- Out: errbuf
978 -- retcode
979 --
980 -- DESIGN REFERENCES:
981 -- GTA_REPORTS_TD.doc
982 --
983 -- CHANGE HISTORY:
984 --
985 -- 13-Jun-2005: Donghai Wang Created
986 -- 06-Mar-2006: Donghai Wang Add fnd log
987 --
988 --==========================================================================
989 PROCEDURE discrepancy_report
990 (errbuf OUT NOCOPY VARCHAR2
991 ,retcode OUT NOCOPY VARCHAR2
992 ,p_gta_batch_num_from IN VARCHAR2
993 ,p_gta_batch_num_to IN VARCHAR2
994 ,p_ar_transaction_type IN NUMBER
995 ,p_cust_num_from IN VARCHAR2
996 ,p_cust_num_to IN VARCHAR2
997 ,p_cust_name_id IN NUMBER
998 ,p_gl_period IN VARCHAR2
999 ,p_gl_date_from IN VARCHAR2
1000 ,p_gl_date_to IN VARCHAR2
1001 ,p_ar_trx_batch_from IN VARCHAR2
1002 ,p_ar_trx_batch_to IN VARCHAR2
1003 ,p_ar_trx_num_from IN VARCHAR2
1004 ,p_ar_trx_num_to IN VARCHAR2
1005 ,p_ar_trx_date_from IN VARCHAR2
1006 ,p_ar_trx_date_to IN VARCHAR2
1007 ,p_ar_doc_num_from IN VARCHAR2
1008 ,p_ar_doc_num_to IN VARCHAR2
1009 ,p_original_curr_code IN VARCHAR2
1010 ,p_primary_sales IN NUMBER
1011 )
1012 IS
1013 l_jmf_gta_enabled VARCHAR2(10);
1014 l_dbg_msg VARCHAR2(500);
1015 l_jmf_gta_not_enabled_msg VARCHAR2(1000);
1016 l_report_xml XMLTYPE;
1017 l_procedure_name VARCHAR2(50);
1018 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1019 l_proc_level NUMBER := fnd_log.level_procedure;
1020 l_org_id hr_all_organization_units.organization_id%TYPE;
1021 l_conc_succ BOOLEAN;
1022 BEGIN
1023
1024
1025
1026 l_procedure_name := 'Discrepancy_Report';
1027
1028 --log for debug
1029 IF (l_proc_level >= l_dbg_level)
1030 THEN
1031 fnd_log.STRING(l_proc_level
1032 ,g_module_prefix || '.' || l_procedure_name || '.begin'
1033 ,'Enter procedure');
1034
1035 fnd_log.STRING(l_proc_level
1036 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1037 ,'p_gta_batch_num_from '||p_gta_batch_num_from);
1038
1039 fnd_log.STRING(l_proc_level
1040 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1041 ,'p_gta_batch_num_to '||p_gta_batch_num_to);
1042
1043 fnd_log.STRING(l_proc_level
1044 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1045 ,'p_ar_transaction_type '||p_ar_transaction_type);
1046
1047 fnd_log.STRING(l_proc_level
1048 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1049 ,'p_cust_num_from '||p_cust_num_from);
1050
1051 fnd_log.STRING(l_proc_level
1052 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1053 ,'p_cust_num_to '||p_cust_num_to);
1054
1055 fnd_log.STRING(l_proc_level
1056 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1057 ,'p_cust_name_id '||p_cust_name_id);
1058
1059 fnd_log.STRING(l_proc_level
1060 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1061 ,'p_gl_period '||p_gl_period);
1062
1063 fnd_log.STRING(l_proc_level
1064 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1065 ,'p_gl_date_from '||p_gl_date_from);
1066
1067 fnd_log.STRING(l_proc_level
1068 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1069 ,'p_gl_date_to '||p_gl_date_to);
1070
1071 fnd_log.STRING(l_proc_level
1072 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1073 ,'p_ar_trx_batch_from '||p_ar_trx_batch_from);
1074
1075 fnd_log.STRING(l_proc_level
1076 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1077 ,'p_ar_trx_batch_to '||p_ar_trx_batch_to);
1078
1079 fnd_log.STRING(l_proc_level
1083 fnd_log.STRING(l_proc_level
1080 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1081 ,'p_ar_trx_num_from '||p_ar_trx_num_from);
1082
1084 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1085 ,'p_ar_trx_num_to '||p_ar_trx_num_to);
1086
1087 fnd_log.STRING(l_proc_level
1088 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1089 ,'p_ar_trx_date_from '||p_ar_trx_date_from);
1090
1091 fnd_log.STRING(l_proc_level
1092 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1093 ,'p_ar_trx_date_to '||p_ar_trx_date_to);
1094
1095 fnd_log.STRING(l_proc_level
1096 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1097 ,'p_ar_doc_num_from '||p_ar_doc_num_from);
1098
1099 fnd_log.STRING(l_proc_level
1100 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1101 ,'p_ar_doc_num_to '||p_ar_doc_num_to);
1102
1103 fnd_log.STRING(l_proc_level
1104 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1105 ,'p_original_curr_code '||p_original_curr_code);
1106
1107 fnd_log.STRING(l_proc_level
1108 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1109 ,'p_primary_sales '||p_primary_sales);
1110 END IF; --( l_proc_level >= l_dbg_level )
1111
1112
1113 --To Get value of profile JMF:Golden Tax Enabled
1114 l_jmf_gta_enabled := fnd_profile.VALUE(NAME => 'JMF_GTA_ENABLED');
1115
1116 IF (l_jmf_gta_enabled IS NULL)
1117 OR --The profile JMF:Golden Tax Enabled is Null or set to 'N'
1118 (l_jmf_gta_enabled = 'N')
1119 THEN
1120
1121 --Display error message
1122 fnd_message.set_name('JMF'
1123 ,'JMF_GTA_GTA_NOT_ENABLE');
1124 l_jmf_gta_not_enabled_msg := fnd_message.get;
1125 SELECT xmlelement("DiscrepancyReport"
1126 ,xmlforest('Y' AS "ReportFailed"
1127 ,l_jmf_gta_not_enabled_msg AS
1128 "ReportFailedMsg"
1129 ,'N' AS "FailedWithParameters"))
1130 INTO l_report_xml
1131 FROM dual;
1132
1133 --output error message to concurrent output
1134 fnd_file.put_line(fnd_file.output
1135 ,l_report_xml.getstringval());
1136
1137 --Set concurrent status to 'WARNING'
1138 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1139 ,message => l_jmf_gta_not_enabled_msg);
1140 ELSE
1141 --To get org id of current session
1142 l_org_id:=MO_GLOBAL.Get_Current_Org_Id;
1143
1144 --To call discrepancy report main program
1145 jmf_gta_reports_pkg.generate_discrepancy_rep(p_org_id => l_org_id
1146 ,p_gta_batch_num_from => p_gta_batch_num_from
1147 ,p_gta_batch_num_to => p_gta_batch_num_to
1148 ,p_ar_transaction_type => p_ar_transaction_type
1149 ,p_cust_num_from => p_cust_num_from
1150 ,p_cust_num_to => p_cust_num_to
1151 ,p_cust_name_id => p_cust_name_id
1152 ,p_gl_period => p_gl_period
1153 ,p_gl_date_from => p_gl_date_from
1154 ,p_gl_date_to => p_gl_date_to
1155 ,p_ar_trx_batch_from => p_ar_trx_batch_from
1156 ,p_ar_trx_batch_to => p_ar_trx_batch_to
1157 ,p_ar_trx_num_from => p_ar_trx_num_from
1158 ,p_ar_trx_num_to => p_ar_trx_num_to
1159 ,p_ar_trx_date_from => p_ar_trx_date_from
1160 ,p_ar_trx_date_to => p_ar_trx_date_to
1161 ,p_ar_doc_num_from => p_ar_doc_num_from
1162 ,p_ar_doc_num_to => p_ar_doc_num_to
1163 ,p_original_curr_code => p_original_curr_code
1164 ,p_primary_sales => p_primary_sales);
1165 END IF; --(l_jmf_gta_enabled IS NULL) OR (l_jmf_gta_enabled='N')
1166
1167 --log for debug
1168 IF (l_proc_level >= l_dbg_level)
1169 THEN
1170 fnd_log.STRING(l_proc_level
1171 ,g_module_prefix || '.' || l_procedure_name || '.end'
1172 ,'Exit procedure');
1173 END IF; --( l_proc_level >= l_dbg_level )
1174
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 IF (fnd_log.level_unexpected >= l_dbg_level)
1178 THEN
1179 fnd_log.STRING(fnd_log.level_unexpected
1180 ,g_module_prefix || l_procedure_name ||
1181 '.OTHER_EXCEPTION '
1182 ,SQLCODE || SQLERRM);
1183 END IF;
1184
1185 RAISE;
1186
1187 END discrepancy_report;
1188
1192 -- Item_Export Public
1189 --==========================================================================
1190 -- PROCEDURE NAME:
1191 --
1193 --
1194 -- DESCRIPTION:
1195 --
1196 -- This procedure is to export item information to a flat file
1197 --
1198 -- PARAMETERS:
1199 -- In: p_master_org_id Identifier of INV master organization
1200 -- p_item_num_from Item number low range
1201 -- p_item_num_to Item number high range
1202 -- p_category_set_id Identifier of item category set
1203 -- p_category_structure_id Structure id of item category
1204 -- p_item_category_from Item category low range
1205 -- p_item_category_to Item category high range
1206 -- p_item_name_source Source to deciede where item name is gotten
1207 -- p_dummy Dummy parameter
1208 -- p_cross_reference_type Cross reference
1209 -- p_item_status Status of an item
1210 -- p_creation_date_from Item creation date low range
1211 -- p_creation_date_to Item creation date high range
1212 --
1213 -- Out: errbuf
1214 -- retcode
1215 --
1216 -- DESIGN REFERENCES:
1217 -- GTA-Txt-Interface-TD.doc
1218 --
1219 -- CHANGE HISTORY:
1220 --
1221 -- 13-Jun-2005: Donghai Wang Created
1222 -- 06-Mar-2006: Donghai Wang Add fnd log
1223 --
1224 --==========================================================================
1225 PROCEDURE item_export
1226 (errbuf OUT NOCOPY VARCHAR2
1227 ,retcode OUT NOCOPY VARCHAR2
1228 ,p_master_org_id IN NUMBER
1229 ,p_item_num_from IN VARCHAR2
1230 ,p_item_num_to IN VARCHAR2
1231 ,p_category_set_id IN NUMBER
1232 ,p_category_structure_id IN NUMBER
1233 ,p_item_category_from IN VARCHAR2
1234 ,p_item_category_to IN VARCHAR2
1235 ,p_item_name_source IN VARCHAR2
1236 ,p_dummy IN VARCHAR2
1237 ,p_cross_reference_type IN VARCHAR2
1238 ,p_item_status IN VARCHAR2
1239 ,p_creation_date_from IN VARCHAR2
1240 ,p_creation_date_to IN VARCHAR2
1241 )
1242 IS
1243 l_procedure_name VARCHAR2(50);
1244 l_jmf_gta_enabled VARCHAR2(10);
1245 l_dbg_msg VARCHAR2(500);
1246 l_jmf_gta_not_enabled_msg VARCHAR2(1000);
1247 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
1248 l_proc_level NUMBER := fnd_log.level_procedure;
1249 l_org_id hr_all_organization_units.organization_id%TYPE;
1250 l_conc_succ BOOLEAN;
1251
1252 BEGIN
1253 l_procedure_name := 'Item_Export';
1254 --log for debug
1255 IF (l_proc_level >= l_dbg_level)
1256 THEN
1257 fnd_log.STRING(l_proc_level
1258 ,g_module_prefix || '.' || l_procedure_name || '.begin'
1259 ,'Enter procedure');
1260
1261 fnd_log.STRING(l_proc_level
1262 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1263 ,'p_master_org_id '||p_master_org_id);
1264
1265 fnd_log.STRING(l_proc_level
1266 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1267 ,'p_item_num_from '||p_item_num_from);
1268
1269 fnd_log.STRING(l_proc_level
1270 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1271 ,'p_item_num_to '||p_item_num_to);
1272
1273 fnd_log.STRING(l_proc_level
1274 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1275 ,'p_category_set_id '||p_category_set_id);
1276
1277 fnd_log.STRING(l_proc_level
1278 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1279 ,'p_category_structure_id '||p_category_structure_id);
1280
1281 fnd_log.STRING(l_proc_level
1282 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1283 ,'p_item_category_from '||p_item_category_from);
1284
1285 fnd_log.STRING(l_proc_level
1286 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1287 ,'p_item_category_to '||p_item_category_to);
1288
1289 fnd_log.STRING(l_proc_level
1290 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1291 ,'p_item_name_source '||p_item_name_source);
1292
1293 fnd_log.STRING(l_proc_level
1294 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1295 ,'p_dummy '||p_dummy);
1296
1297 fnd_log.STRING(l_proc_level
1298 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1299 ,'p_cross_reference_type '||p_cross_reference_type);
1300
1301 fnd_log.STRING(l_proc_level
1302 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1303 ,'p_item_status '||p_item_status);
1304
1305 fnd_log.STRING(l_proc_level
1306 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1307 ,'p_creation_date_from '||p_creation_date_from);
1308
1309 fnd_log.STRING(l_proc_level
1310 ,g_module_prefix || '.' || l_procedure_name || '.parameters'
1311 ,'p_creation_date_to '||p_creation_date_to);
1312
1313
1314 END IF; --( l_proc_level >= l_dbg_level )
1315
1316 --To Get value of profile JMF:Golden Tax Enabled
1317 l_jmf_gta_enabled := fnd_profile.VALUE(NAME => 'JMF_GTA_ENABLED');
1318
1319 IF (l_jmf_gta_enabled IS NULL)
1320 OR --The profile JMF:Golden Tax Enabled is Null or set to 'N'
1321 (l_jmf_gta_enabled = 'N')
1322 THEN
1323
1324 --Display error message
1325 fnd_message.set_name('JMF'
1326 ,'JMF_GTA_GTA_NOT_ENABLE');
1327 l_jmf_gta_not_enabled_msg := fnd_message.get;
1328 fnd_file.put_line(fnd_file.output
1329 ,l_jmf_gta_not_enabled_msg);
1330
1331 --Set concurrent status to 'WARNING'
1332 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1333 ,message => l_jmf_gta_not_enabled_msg);
1334 ELSE
1335
1336 --To get org id of current session
1337 l_org_id:=MO_GLOBAL.Get_Current_Org_Id;
1338
1339 --To call item export main program
1340 jmf_gta_txt_operator_proc.export_items(p_org_id => l_org_id
1344 ,p_category_set_id => p_category_set_id
1341 ,p_master_org_id => p_master_org_id
1342 ,p_item_num_from => p_item_num_from
1343 ,p_item_num_to => p_item_num_to
1345 ,p_category_structure_id => p_category_structure_id
1346 ,p_item_category_from => p_item_category_from
1347 ,p_item_category_to => p_item_category_to
1348 ,p_item_name_source => p_item_name_source
1349 ,p_cross_reference_type => p_cross_reference_type
1350 ,p_item_status => p_item_status
1351 ,p_creation_date_from => p_creation_date_from
1352 ,p_creation_date_to => p_creation_date_to);
1353 END IF; --(l_jmf_gta_enabled IS NULL) OR (l_jmf_gta_enabled='N')
1354
1355 --log for debug
1356 IF (l_proc_level >= l_dbg_level)
1357 THEN
1358 fnd_log.STRING(l_proc_level
1359 ,g_module_prefix || '.' || l_procedure_name || '.end'
1360 ,'Exit procedure');
1361 END IF; --( l_proc_level >= l_dbg_level )
1362
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1366 THEN
1367 fnd_log.STRING(fnd_log.level_unexpected
1368 ,g_module_prefix || l_procedure_name ||
1369 '. OTHER_EXCEPTION '
1370 ,'Unknown error' || SQLCODE || SQLERRM);
1371
1372 END IF;
1373 RAISE;
1374
1375 END item_export;
1376
1377 --==========================================================================
1378 -- PROCEDURE NAME:
1379 --
1380 -- Transfer_Customers_To_GT Public
1381 --
1382 -- DESCRIPTION:
1383 --
1384 -- This procedure convert AR customers information into a flat file
1385 --
1386 -- PARAMETERS:
1387 -- In: p_customer_num_from IN VARCHAR2
1388 -- p_customer_num_to IN VARCHAR2
1389 -- p_customer_name_from IN VARCHAR2
1390 -- p_customer_name_to IN VARCHAR2
1391 -- p_taxpayee_id IN VARCHAR2
1392 -- p_creation_date_from IN VARCHAR2
1393 -- p_creation_date_to IN VARCHAR2
1394 --
1395 -- Out: errbuf
1396 -- retcode
1397 --
1398 -- DESIGN REFERENCES:
1399 -- GTA-Txt-Interface-TD.doc
1400 --
1401 -- CHANGE HISTORY:
1402 --
1403 -- 20-MAY-2005: Jim.Zheng Created.
1404 -- 26_Jun-005 Jim Zheng update , chanage the Date parameter to Varchar2
1405 -- 16-Nov-2005 Jim Zheng update , change the output of gta_not_enable
1406 --==========================================================================
1407 PROCEDURE transfer_customers_to_gt
1408 (errbuf OUT NOCOPY VARCHAR2
1409 ,retcode OUT NOCOPY VARCHAR2
1410 ,p_customer_num_from IN VARCHAR2
1411 ,p_customer_num_to IN VARCHAR2
1412 ,p_customer_name_from IN VARCHAR2
1413 ,p_customer_name_to IN VARCHAR2
1414 --,p_taxpayee_id IN VARCHAR2
1415 ,p_creation_date_from IN VARCHAR2
1416 ,p_creation_date_to IN VARCHAR2
1417 )
1418 IS
1419
1420 l_jmf_gta_gta_not_enabled VARCHAR2(1000);
1421 l_procedure_name VARCHAR2(50) := 'transfer_customers_to_GT';
1422 l_conc_succ BOOLEAN;
1423 l_org_id NUMBER := mo_global.get_current_org_id;
1424 BEGIN
1425 -- procedure begin
1426 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1427 THEN
1428 fnd_log.STRING(fnd_log.level_procedure
1429 ,g_module_prefix || l_procedure_name
1430 ,'Begin Procedure. ');
1431 END IF; /*FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL*/
1432
1433 IF (fnd_profile.VALUE('JMF_GTA_ENABLED') = 'Y')
1434 THEN
1435 jmf_gta_txt_operator_proc.export_customers(p_org_id => l_org_id
1436 ,p_customer_num_from => p_customer_num_from
1437 ,p_customer_num_to => p_customer_num_to
1438 ,p_customer_name_from => p_customer_name_from
1439 ,p_customer_name_to => p_customer_name_to
1440 --,p_taxpayee_id => p_taxpayee_id
1441 ,p_creation_date_from => fnd_date.canonical_to_date(p_creation_date_from)
1442 ,p_creation_date_to => fnd_date.canonical_to_date(p_creation_date_to));
1443
1444 ELSE
1445 /*FND_PROFILE.VALUE('GTA_ENABLED')='Y'*/
1446 -- report JMF_GTA_DISABLE_ERROR in xml format
1447 -- set concurrent status to WARNING
1448 fnd_message.set_name('JMF'
1449 ,'JMF_GTA_GTA_NOT_ENABLE');
1450 l_jmf_gta_gta_not_enabled := fnd_message.get;
1451
1452 fnd_file.put_line(fnd_file.output
1453 ,l_jmf_gta_gta_not_enabled);
1454
1455 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1456 ,message => l_jmf_gta_gta_not_enabled);
1457 RETURN;
1458 END IF; /*FND_PROFILE.VALUE('GTA_ENABLED')='Y'*/
1459 -- procedure end
1460 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1461 THEN
1462 fnd_log.STRING(fnd_log.level_procedure
1463 ,g_module_prefix || l_procedure_name
1464 ,'End Procedure. ');
1465 END IF; /*FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL*/
1466
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1470 THEN
1471 fnd_log.STRING(fnd_log.level_unexpected
1472 ,g_module_prefix || l_procedure_name ||
1473 '. OTHER_EXCEPTION '
1474 ,'Unknown error' || SQLCODE || SQLERRM);
1475
1476 END IF;
1477 RAISE;
1478 END transfer_customers_to_gt;
1479
1480 END JMF_GTA_CONC_PROG;