[Home] [Help]
PACKAGE BODY: APPS.JMF_GTA_ARTRX_PROC
Source
1 PACKAGE BODY JMF_GTA_ARTRX_PROC AS
2 --$Header: JMFRARTB.pls 120.36.12010000.14 2009/01/23 06:07:20 yaozhan ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation
5 --| Redwood Shores, California, USA
6 --| All rights reserved.
7 --+===========================================================================
8 --|
9 --| FILENAME :
10 --| JMFRARTB.pls
11 --|
12 --| DESCRIPTION:
13 --| This package provide the functionality to retrieve
14 --| transaction data from Oracle Receivable against the
15 --| condition defined in Setup Form.
16 --|
17 --|
18 --|
19 --| HISTORY:
20 --| 20-APR-2005: Jim Zheng
21 --| 30-sep-2005: Jim zheng modify because registration
22 --| issue.
23 --| 10-Oct_2005: Jim Zheng. Fix a collection init bug
24 --| Add some log clause for UT debug.
25 --| 11-Oct-2005: Jim Zheng. add a procedure
26 --| get_uom_name. Modify procedure Retrieve_AR_trxs
27 --| 19-Oct-2005: Jim Zheng. change select condition of get max amount when
28 --| the exception JMF_GTA_UNITPRICE_ERROR.
29 --| change the source of orginal currency amount from AR line
30 --| to ebtax
31 --| add l_tax_rate/100 when get tax rate
32 --| change the type of item_number
33 --| 20-Oct-2005: Jim Zheng. add debug message into procedure Generate_XML_output
34 --| 21-Oct-2005: Jim Zheng add xml element in generate_XML_output
35 --| 16-Nov-2005: Jim zheng Remove the complete flag check in Get_AR_SQL
36 --| 24-Nov-2005: Jim Zheng Change the select condition period_type in procedure Get_AR_SQL
37 --| 28-Nov-2005: Jim Zheng Change the select sql for get pervious Cust_trx_id of Credit memo
38 --| Retrieve_AR_Trxs
39 --| 28-Nov-2005: Jim Zheng Add complete flag into Dynamic sql Get_AR_SQL.
40 --| 28-Nov-2005: Jim Zheng Fix a bug of 'missing right parenthesis' in procedure get_AR_flex_Cond
41 --| 28-Nov-2005: Jim ZHeng Delete the 'return' when exception in procedure get_AR_Currency_Cond
42 --| 28-Nov-2005: Jim Zheng Add select condition in procedure get_inventory_item_Number
43 --| Because the number of attribute is from 1 to 30
44 --| 29-Nov-2005: Jim Zheng Fix a code bug of when check third party regi number. Retrieve_AR_Trxs
45 --| 29-Nov-2005: Jim Zheng Add a where condition app.display = 'Y' to credit memo check.Retrieve_AR_Trxs
46 --| 30-Nov-2005: Jim Zheng Change message name from JMF_GTA_CRMEMO_MISSING_ARINV to JMF_GTA_CRMEMO_MULREF_ARINV
47 --| 30-Nov-2005: Jim Zheng change code for Credit memo, When there are 0 or >1 reference invoice in GTA.
48 --| Retrieve_AR_TRXs
49 --| 01-Dec-2005: Jim Zheng Add UOM and Quantity Check for AR transaction and Credit memo in procedure Retrieve_AR_Trxs
50 --| 01-Dec-2005: Jim Zheng Chenge exception status to Warning when reference inv is 0 or >1 in GTA
51 --| Retrieve_AR_TRXs
52 --| 02-Dec-2005: Jim Zheng Add a item id check in procedure get_inv_item_model
53 --| 02-Dec-2005: Jim Zheng Don't throw a exception when the FP regi number is not exist in system option.
54 --| 02-Dec-2005: Jim Zheng Change the Gta_row number for Sucessful not for all in procedure Generate_XML_output
55 --| 08-Dec-2005: Jim Zheng Verify message of Retrieve_AR_TRXs
56 --| 08-Dec-2005: Jim Zheng Add log for support
57 --| 15-Dec-2005: Jim Zheng add gta invoice number for XML output in procedure Generate_XML_output
58 --| 26-Dec-2005: Jim Zheng change code in percedure Retrieve_AR_TRXS for fix permance issue.
59 --| 20-Jan-2005: Jim Zheng update code for credit memo line quantity issue. the quantity of credit memo stored in
60 --| different column with invoice
61 --| 24-Jam-2005: Jim Zheng Update code for credit memo uT, The invocie source is 'GT' when it is imported
62 --| into GTA from GT
63 --| 17-Feb-2006: Jogen Hu fix bug of error report when no AR transaction tax line (bug:5092042)
64 --| 21/03/2006 Jogen Hu Change data range from trunc
65 --| parameters to DB columns by bug 5107043
66 --| 04/04/2006 Jogen Hu Change Generate_XML_Output procedure
67 --| to add close unclosed cursor in bug 5135169
68 --| 12/04/2006 Jogen Hu Add function get_gta_number and modify generate_xml_output
69 --| against bug 5144561
70 --| 17/04/2006 Jogen Hu Change Generate_XML_Output procedure: "CurreneyCode"->"CurrencyCode"
71 --| against bug 5168003
72 --| 09/06/2006 Shujuan Yan Change the token value from
73 --| JMF_GTA_UNITPRICE_ERROR to
74 --| JMF_GTA_UNITPRICE_EXCEED in the procedure
75 --| Retrieve_AR_TRXs for bug 5263215
76 --| 09/06/2006 Shujuan Yan Add transaction number to GTA
77 --| invoice description in the procedure
78 --| Retrieve_AR_TRXs for bug 5255993
79 --| 11/06/2006 Shujuan Yan Change message code from
80 --| JMF_GTA_CRMEMO_MISSING_ARINV to
81 --| JMF_GTA_CRMEMO_MISSING_GTINV in the
82 --| procedure Retrieve_AR_TRXs for bug 5263308
83 --| 11/06/2006 Shujuan Yan Delete the process when else
84 --| l_item_inventry_id is not null in the
85 --| procedure Retrieve_AR_TRXs for bug 5224923
86 --| 12/06/2006 Shujuan Yan Modify the procedure
87 --| Retrieve_AR_TRXs, Get line_number from
88 --| ra_customer_trx_lines_all, Change Change
89 --| the token value by "fnd_message.set_token
90 --| ('NUM', l_customer_trx_line_number)"
91 --| instead of "fnd_message.set_token('NUM',
92 --| l_customer_trx_line_id)" for bug 5230712
93 --| 12/06/2006 Shujuan Yan Modify the procedure Retrieve
94 --| _AR_TRXs, Change the token value by
95 --| "fnd_message.set_token('ITEM', l_inventory
96 --| _item_name )" instead of "fnd_message.set
97 --| _token('ITEM', l_inventory_item_id)" for
98 -- bug 5230712
99 --| 29/06/2006 Shujuan Yan Modify the procedure Retrieve
100 --| _AR_TRXs,Add if l_ctt_class = 'CM' clause,
101 --| when transaction type is credit memo, get
102 --| the bank information according to the
103 --| corresponding invoice for bug 5263131
104 --| 29/06/2006 Shujuan Yan Modify the procedure Retrieve
105 --| _AR_TRXs, Get line_number from ra_customer
106 --| _trx_lines_all, Change the token value by
107 --| "fnd_message.set_token('NUM', l_customer_
108 --| trx_line_number)" instead of "fnd_message
109 --| .set_token('NUM', l_customer_trx_line_id)"
110 --| for bug 5258522
111 --| 29/06/2006 Shujuan Yan Modify Retrieve_AR_TRXs, get l_
112 --| tax_curr_unit_price from procedure Get_Info
113 --| _From_Ebtax of package JMF_GTA_TRX_UTIL, and
114 --| compare it with max amount for bug 5168900.
115 --| 12/07/2006 Shujuan Yan Added l_trx_line.item_description
116 --| := l_description when l_item_inventry_id is
117 --| null in the procedure Retrieve_AR_TRXs
118 --| for bug 5224923
119 --| 20/07/2006 Shujuan Yan Added the length of
120 --| l_inventory_item_name from 60 characters to
121 --| 240 characters for bug 5400805.
122 --| 08/08/2006 Shujuan Yan Modify Retrieve_AR_TRXs,
123 --| the variable unit_price should be assigned
124 --| the unit price of GTA currency for 5446456
125 --| 08/09/2006 Shujuan Yan in procedure Get_AR_FLEX_COND,
126 --| Added the sql condition 'l_ATTRIBUTE_COLUMN
127 --| IS NULL'for bug 5443909
128 --| 28/12/2007 Subba, Added the new procedure 'Get_Invoice_Type'.
129 --| This procedure returns the WHERE clause about
130 --| Invoice Type using the
131 --| Invoice Type,Transaction Type mapping
132 --| relationship defined in GTA System Option Form.
133 --| 24/11/2008 Brian, Disable the validation that Credit memo must
134 --| be associated with a VAT invoice for bug 7591365
135 --| This change is compliance with GT 6.10.
136 --| 25/11/2008 Brian,Remove the Credit Memo from the validation that
137 --| UOM cannot be null for the invoice line for bug 7594218
138 --| 10/12/2008 Yao Zhang fix bug 7629877. Remove Credit Memo validation for Special
139 --| and Recycle VAT.For Common VAT, Credit Memo Validation followed the following rules.
140 --| 1 On account Credit Memo can be transferd to GTA with warning
141 --| 2 Credit Memo credited with AR invoice which is not transfered to GTA can not be transfered
142 --| 3 Credit Memo credited with AR invoice which is transfered to GTA with split can be transfered with warning.
143 --| 4 Credit Memo credited with AR invoice which is transfered to GTA without split
144 --| can be transfered only when the GTA invoice is generated for the AR invoice.
145 --| Remove the validation that the UOM and Quantity cannot be null
146 --| for all the transactions.
147 --| 16/12/2008 Yao Zhang fix bug 7644235 CreditMemo should not be transfered or splited when
148 --| exceed the limition of max amount or max lines.
149 --| 24/12/2008 Yao Zhang fix bug 7667709 AR transfer to GTA program completed with warning,and output file
150 --| has error about:'The following tags were not closed: TransferReport'
151 --| 26/12/2008 Yao Zhang fix bug 7670543 CM CREDITING A INV THAT HAS COMPLETED WITHOUT SPLIT CANNOT BE TRANSFERRED
152 --| 30/12/2008 Yao Zhang fix bug 7675165 CREDIT MEMO TRANSFER TOG TA,THE WARNING MESSAGE IS DUPLICATED.
153 --| 05/01/2009 Yao Zhang fix bug 7684662 COMMON CM CAN BE TRANSFERED WITHOUT WARNING WHEN SEVERAL CMS TRANSFERED TOGETHER
154 --| 06/01/2009 Yao Zhang fix bug 7685610 Description cannot correctly be populated by
155 --| transfer program for the credit memo reference to Common VAT invoice, which
156 --| original transaction has been transferred to GTA without splitting and
157 --| corresponding GT invoice has been transferred back to GTA.
158 --| 20/Jan/2009 Yao Zhang fix bug 7721035 RECEIVABLE TO GOLDEN TAX INVOICE TRANSFER FAILED
159 --| 22/Jan/2009 Yao Zhang fix bug 7829039 ITEM NAME ON GTA INVOICE LINE IS NULL
160 --| 23/jan/2009 Yao Zhang fix bug 7758496 CreditMemo whose line num exceeds max line number limitation
161 --| should be transfered when sales list is enabled
162 --+===========================================================================+
163
164 --=============================================================================
165 -- PROCEDURE NAME:
166 -- log
167 -- TYPE:
168 -- private
169 --
170 -- DESCRIPTION :
171 -- This procedure log message
172 -- PARAMETERS :
173 -- p_level IN VARCHAR2
174 -- p_module IN VARCHAR2
175 -- p_message IN VARCHAR2
176 --
177 -- HISTORY:
178 -- 10-MAY-2005 : Jim.Zheng Create
179 --=============================================================================
180 PROCEDURE log(p_level IN VARCHAR2,
181 p_module IN VARCHAR2,
182 p_message IN VARCHAR2) IS
183 BEGIN
184 IF (p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
185 fnd_log.STRING(LOG_LEVEL => p_level,
186 MODULE => p_module,
187 MESSAGE => p_message);
188 END IF;
189 END;
190
191 --==========================================================================
192 -- PROCEDURE NAME:
193 -- retrive_valid_AR_TRXs
194 --
195 -- DESCRIPTION:
196 -- This procedure is for invoices transfer concurrent
197 -- implementation from Receivable to GTA
198 --
199 -- PARAMETERS:
200 -- In: P_ORG_ID NUMBER
201 -- P_transfer_rule NUMBER
202 -- p_conc_parameters JMF_GTA_TRX_UTIL.transferParas_rec_type
203 -- p_DEBUG VARCHAR2
204 -- OUT: errbuf varchar2
205 -- retcode VARCHAR2
206
207 -- DESIGN REFERENCES:
208 -- GTA-TRANSFER-PROGRAM-TD.doc
209 --
210 -- CHANGE HISTORY:
211 -- 20-APR-2005: Jim Zheng Created.
212 --===========================================================================
213 PROCEDURE Transfer_AR_To_GTA(errbuf OUT NOCOPY VARCHAR2,
214 retcode OUT NOCOPY VARCHAR2,
215 p_org_id IN NUMBER,
216 p_transfer_id IN NUMBER,
217 p_conc_parameters IN JMF_GTA_TRX_UTIL.transferParas_rec_type) IS
218 l_procedure_name VARCHAR2(30) := 'transfer_AR_to_GTA';
219 l_gta_trx_tbl_4ar jmf_gta_trx_util.trx_tbl_type := jmf_gta_trx_util.trx_tbl_type();
220 l_gta_trx_tbl_4gta jmf_gta_trx_util.trx_tbl_type := jmf_gta_trx_util.trx_tbl_type();
221 BEGIN
222 FND_LOG.G_CURRENT_RUNTIME_LEVEL := FND_LOG.LEVEL_STATEMENT;
223
224 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
225 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
226 G_MODULE_PREFIX || l_procedure_name,
227 'Begin Procedure. ');
228 END IF;
229
230 retrive_valid_AR_TRXs(p_org_id => p_org_id,
231 p_transfer_id => p_transfer_id,
232 p_conc_parameters => p_conc_parameters,
233 x_GTA_TRX_Tbl => l_GTA_trx_tbl_4AR);
234
235 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
236 log(FND_LOG.LEVEL_PROCEDURE,
237 G_MODULE_PREFIX || l_procedure_name,
238 'End Retrive_valid_ar_trx......');
242 END IF;
239 log(FND_LOG.LEVEL_PROCEDURE,
240 G_MODULE_PREFIX || l_procedure_name,
241 'l_gta_trx_tbl_4ar.count:' || l_GTA_trx_tbl_4AR.COUNT);
243
244 jmf_gta_split_trx_proc.split_Transactions(p_org_id => p_org_id,
245 p_transfer_id => p_transfer_id,
246 p_gta_trx_tbl => l_gta_trx_tbl_4ar,
247 x_gta_trx_tbl => l_gta_trx_tbl_4gta);
248 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249 log(FND_LOG.LEVEL_PROCEDURE,
250 G_MODULE_PREFIX || l_procedure_name,
251 'End Split_transactions......' || l_gta_trx_tbl_4gta.COUNT);
252 END IF;
253
254 jmf_gta_trx_util.create_TRXs(p_gta_trxs => l_gta_trx_tbl_4gta);
255
256 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257 log(FND_LOG.LEVEL_PROCEDURE,
258 G_MODULE_PREFIX || l_procedure_name,
259 'End Create_trxs......');
260 END IF;
261
262 --generated XML string from temporary table
263 --and put it out to concurrent output
264 generate_XML_output(p_org_id => p_org_id,
265 p_transfer_id => p_transfer_id,
266 p_conc_parameters => p_conc_parameters);
267
268 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
269 log(FND_LOG.LEVEL_PROCEDURE,
270 G_MODULE_PREFIX || l_procedure_name,
271 'End generate_XML_output......');
272 END IF;
273
274 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
275 FND_LOG.String(fnd_log.LEVEL_PROCEDURE,
276 G_MODULE_PREFIX || l_procedure_name,
277 'END procedure. ');
278 END IF;
279
280 EXCEPTION
281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
282 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
283 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
284 G_MODULE_PREFIX || l_procedure_name ||
285 '. UNEXPECTED_ERROR',
286 'Unexpected error' || SQLCODE || SQLERRM);
287 END IF;
288 RAISE;
289
290 WHEN OTHERS THEN
291 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
292 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
293 G_MODULE_PREFIX || l_procedure_name ||
294 '. OTHER_EXCEPTION ',
295 'Unknown error' || SQLCODE || SQLERRM);
296
297 END IF;
298 RAISE;
299 END transfer_AR_to_GTA;
300
301 --==========================================================================
302 -- PROCEDURE NAME:
303 -- get_gta_number
304 --
305 -- DESCRIPTION:
306 -- This function get concated GTA number by a AR trx ID
307 --
308 -- PARAMETERS:
309 -- p_ar_trxId IN NUMBER
310 -- RETURN:
311 -- varchar2 concated GTA number
312 --
313 -- DESIGN REFERENCES:
314 -- GTA-TRANSFER-PROGRAM-TD.doc
315 --
316 -- CHANGE HISTORY:
317 -- 12-APR-2006: Jogen Hu Created.
318 --===========================================================================
319 FUNCTION get_gta_number(p_ar_trxId IN NUMBER) RETURN VARCHAR2 IS
320 gta_trx_number VARCHAR2(2000);
321 CURSOR get_gta_inv_number_c(p_ra_trx_id IN NUMBER) IS
322 SELECT gta_trx_number
323 FROM jmf_gta_trx_headers_all
324 WHERE ra_trx_id = p_ra_trx_id;
325 BEGIN
326
327 FOR r_number IN get_gta_inv_number_c(p_ar_trxId) LOOP
328 gta_trx_number := gta_trx_number || ',' || r_number.gta_trx_number;
329 END LOOP;
330 RETURN gta_trx_number;
331
332 END get_gta_number;
333
334 --==========================================================================
335 -- PROCEDURE NAME:
336 -- Generate_XML_output
337 --
338 -- DESCRIPTION:
339 -- This procedure generate XML string as concurrent output
340 -- from temporary table
341 --
342 -- PARAMETERS:
343 -- In: P_ORG_ID NUMBER
344 -- p_transfer_id NUMBER
345 -- p_conc_parameters JMF_GTA_TRX_UTIL.transferParas_rec_type
346 --
347 -- DESIGN REFERENCES:
348 -- GTA-TRANSFER-PROGRAM-TD.doc
349 --
350 -- CHANGE HISTORY:
351 -- 20-APR-2005: Jim Zheng Created.
352 -- 24-Dec-2008: Yao Zhang Changed for bug 7667709
353 --===========================================================================
354
355 PROCEDURE Generate_XML_Output(p_org_id IN NUMBER,
356 p_transfer_id IN NUMBER,
357 p_conc_parameters IN JMF_GTA_TRX_UTIL.transferParas_rec_type) IS
358 l_currency VARCHAR2(30);
359 l_reportFailed xmltype;
360 l_FailedWithParameters xmltype;
361 l_parameter XMLType;
362 l_summary XMLType;
363 l_failed XMLType;
364 l_warning xmltype;
368 --l_date_format VARCHAR2(11):=fnd_profile.VALUE('ICX_DATE_FORMAT_MASK');
365 l_succeeded XMLType;
366 l_report_XML XMLType;
367
369
370 l_succ_rows NUMBER;
371 l_failed_rows NUMBER;
372 l_warning_rows NUMBER;
373 l_GTA_rows NUMBER;
374 l_succ_amount NUMBER;
375 l_failed_amount NUMBER;
376 l_warning_amount NUMBER;
377
378 l_transaction_id NUMBER;
379 l_gta_inv_number VARCHAR2(50);
380 l_gta_inv_num_all VARCHAR2(2000);
381
382 l_length NUMBER;
383
384 l_operation_unit hr_operating_units.name%TYPE;
385 l_transfer_rule jmf_gta_rule_headers_all.rule_name%TYPE;
386 l_final_output CLOB;
387 -- parameters
388 l_customer_num_from VARCHAR2(30) := nvl(p_conc_parameters.CUSTOMER_NUM_FROM,
389 ' ');
390 l_customer_num_to VARCHAR2(30) := nvl(p_conc_parameters.CUSTOMER_NUM_TO,
391 ' ');
392 l_customer_name_from VARCHAR2(360) := nvl(p_conc_parameters.CUSTOMER_NAME_FROM,
393 ' ');
394 l_customer_name_to VARCHAR2(360) := nvl(p_conc_parameters.CUSTOMER_NAME_TO,
395 ' ');
396 l_gl_period VARCHAR2(100) := nvl(p_conc_parameters.GL_PERIOD,
397 ' ');
398 --l_gl_date_from VARCHAR2(20) := nvl(to_char(p_conc_parameters.GL_DATE_FROM), ' ');
399 l_gl_date_from VARCHAR2(20) := nvl(JMF_GTA_TRX_UTIL.To_Xsd_Date_String(p_conc_parameters.GL_DATE_FROM),
400 ' ');
401 --l_gl_date_to VARCHAR2(20) := nvl(to_char(p_conc_parameters.GL_DATE_TO), ' ');
402 l_gl_date_to VARCHAR2(20) := nvl(JMF_GTA_TRX_UTIL.To_Xsd_Date_String(p_conc_parameters.GL_DATE_TO),
403 ' ');
404 l_trx_batch_from VARCHAR2(50) := nvl(p_conc_parameters.TRX_BATCH_FROM,
405 ' ');
406 l_trx_batch_to VARCHAR2(50) := nvl(p_conc_parameters.TRX_BATCH_TO,
407 ' ');
408 l_trx_number_from VARCHAR2(20) := nvl(p_conc_parameters.TRX_NUMBER_FROM,
409 ' ');
410 l_trx_number_to VARCHAR2(20) := nvl(p_conc_parameters.TRX_NUMBER_TO,
411 ' ');
412 --l_trx_date_from VARCHAR2(20) := nvl(to_char(p_conc_parameters.TRX_DATE_FROM), ' ');
413 l_trx_date_from VARCHAR2(20) := nvl(JMF_GTA_TRX_UTIL.To_Xsd_Date_String(p_conc_parameters.TRX_DATE_FROM),
414 ' ');
415 --l_trx_date_to VARCHAR2(20) := nvl(to_char(p_conc_parameters.TRX_DATE_TO), ' ');
416 l_trx_date_to VARCHAR2(20) := nvl(JMF_GTA_TRX_UTIL.To_Xsd_Date_String(p_conc_parameters.TRX_DATE_TO),
417 ' ');
418 l_doc_num_from VARCHAR2(30) := nvl(to_char(p_conc_parameters.DOC_NUM_FROM),
419 ' ');
420 l_doc_num_to VARCHAR2(30) := nvl(to_char(p_conc_parameters.DOC_NUM_TO),
421 ' ');
422
423 l_procedure_name VARCHAR2(30) := 'Generate_XML_output';
424
425 --12/04/2006 Jogen Hu bug 5144561
426 /*
427 -- for add the gta_inv_number
428 CURSOR report_temp_c
429 IS
430 SELECT
431 transaction_id
432 FROM
433 jmf_gta_transfer_temp
434 WHERE SUCCEEDED='Y';
435
436 -- for add the gta inv number
437 CURSOR get_gta_inv_number_c(p_ra_trx_id IN NUMBER)
438 IS
439 SELECT
440 gta_trx_number
441 FROM
442 jmf_gta_trx_headers_all
443 WHERE
444 ra_trx_id = p_ra_trx_id;
445 */
446 --12/04/2006 Jogen Hu bug 5144561
447
448 BEGIN
449
450 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
451 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
452 G_MODULE_PREFIX || l_procedure_name,
453 'Begin Procedure. ');
454 END IF;
455
456 SELECT GT_CURRENCY_CODE
457 INTO l_currency
458 FROM jmf_gta_system_parameters_all
459 WHERE org_id = p_org_id;
460
461 --get rult name by rule id
462 BEGIN
463 SELECT rule.rule_name
464 INTO l_transfer_rule
465 FROM jmf_gta_rule_headers_all rule
466 WHERE rule.rule_header_id = p_transfer_id;
467 EXCEPTION
468 WHEN no_data_found THEN
469
470 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
471 fnd_log.String(fnd_log.LEVEL_EXCEPTION,
472 G_MODULE_PREFIX || l_procedure_name,
473 'No data found ');
474 END IF;
475 RAISE;
476 END;
477
478 -- get org name by org id
479 BEGIN
480 SELECT OU.NAME
481 INTO l_operation_unit
482 FROM HR_ALL_ORGANIZATION_UNITS O, HR_ALL_ORGANIZATION_UNITS_TL OU
483 WHERE O.ORGANIZATION_ID = OU.ORGANIZATION_ID
484 AND OU.LANGUAGE = USERENV('LANG')
485 AND O.ORGANIZATION_ID = p_org_id;
486
487 EXCEPTION
488 WHEN no_data_found THEN
489
493 'No data found ');
490 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
491 fnd_log.String(fnd_log.LEVEL_EXCEPTION,
492 G_MODULE_PREFIX || l_procedure_name,
494 END IF;
495 RAISE;
496 END;
497
498 -- sum the count of successed , failed and warning trx
499 SELECT COUNT(*), SUM(nvl(amount, 0))
500 INTO l_succ_rows, l_succ_amount
501 FROM JMF_gta_transfer_temp
502 WHERE SUCCEEDED = 'Y';
503
504 --12/04/2006 Jogen Hu bug 5144561
505 /*
506 --begin insert the gta invoice number into temp
507 --get the transaction_id from temp table
508 OPEN report_temp_c;
509 LOOP
510 FETCH
511 report_temp_c
512 INTO
513 l_transaction_id;
514
515 EXIT WHEN report_temp_c%NOTFOUND;
516
517 fnd_file.PUT_LINE(fnd_file.log,'|||----111----||'||l_transaction_id);
518 -- init gta invoice number all
519 l_gta_inv_num_all := '';
520
521 --get the gta_inv_number
522 OPEN get_gta_inv_number_c(l_transaction_id);
523 LOOP
524 FETCH
525 get_gta_inv_number_c
526 INTO
527 l_gta_inv_number;
528
529 EXIT WHEN get_gta_inv_number_c%NOTFOUND;
530
531 fnd_file.PUT_LINE(fnd_file.log,'|||----222----||'||l_gta_inv_number);
532
533 IF l_gta_inv_num_all IS NULL
534 THEN
535 l_gta_inv_num_all := l_gta_inv_num_all;
536 ELSE
537 l_gta_inv_num_all := l_gta_inv_num_all||','||l_gta_inv_number;
538 END IF;
539
540 END LOOP;--OPEN get_gta_inv_number_c(l_transaction_id)
541 CLOSE get_gta_inv_number_c;--jogen Hu Apr-4, 2006 bug 5135169
542
543 BEGIN
544 UPDATE
545 jmf_gta_transfer_temp
546 SET
547 gta_invoice_num = l_gta_inv_num_all
548 WHERE
549 transaction_id = l_transaction_id;
550
551 EXCEPTION
552 WHEN OTHERS THEN
553 fnd_file.PUT_LINE(fnd_file.log,'|||----333----||'||l_transaction_id);
554
555 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
556 THEN
557 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
558 , G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
559 , 'Unknown error'||SQLCODE||SQLERRM);
560
561 END IF;
562
563 END;
564
565 END LOOP;--OPEN report_temp_c
566
567 CLOSE report_temp_c;--jogen Hu Apr-4, 2006 bug 5135169
568 */
569 --12/04/2006 Jogen Hu bug 5144561
570 --end insert the gta invoice number into temp
571
572 SELECT COUNT(*), SUM(nvl(amount, 0))
573 INTO l_failed_rows, l_failed_amount
574 FROM JMF_gta_transfer_temp
575 WHERE SUCCEEDED = 'N';
576
577 SELECT COUNT(*), SUM(nvl(amount, 0))
578 INTO l_warning_rows, l_warning_amount
579 FROM jmf_gta_transfer_temp
580 WHERE SUCCEEDED = 'W';
581
582 SELECT COUNT(*)
583 INTO l_GTA_rows
584 FROM jmf_gta_transfer_temp
585 WHERE SUCCEEDED = 'W'
586 OR SUCCEEDED = 'Y';
587
588 -- generate validate xml string
589 SELECT xmlelement("ReportFailed", 'N') INTO l_Reportfailed FROM dual;
590
591 SELECT xmlelement("FailedWithParameters", 'N')
592 INTO l_FailedWithParameters
593 FROM dual;
594
595 -- generate xmlsring of parameters of transfer program
596 SELECT xmlelement("Parameters",
597 xmlforest(l_operation_unit AS "OperationUnit",
598 l_transfer_rule AS "TransferRule",
599 l_customer_num_from AS "CustomerNumberFrom",
600 l_customer_num_to AS "CustomerNumberTo",
601 l_customer_name_from AS "CustomerNameFrom",
602 l_customer_name_to AS "CustomerNameTo",
603 l_gl_period AS "GLPeriod",
604 l_gl_date_from AS "GLDateFrom",
605 l_gl_date_to AS "GLDateTo",
606 l_trx_batch_from AS "TransactionBatchFrom",
607 l_trx_batch_to AS "TransactionBatchTo",
608 l_trx_number_from AS "TransactionNumberFrom",
609 l_trx_number_to AS "TransactionNumberTo",
610 l_trx_date_from AS "TransactionDateFrom",
611 l_trx_date_to AS "TransactionDateTo",
612 l_doc_num_from AS "DocNumberFrom",
613 l_doc_num_to AS "DocNumberTo"))
614 INTO l_parameter
615 FROM dual;
616
617 --generate summary section
618 SELECT xmlelement("Summary",
619 xmlforest(l_succ_rows AS "NumOfSucc",
620 l_failed_rows AS "NumOfFailed",
621 l_warning_rows AS "NumOfWarning",
622 l_GTA_rows AS "NumOfGTA",
623 l_succ_amount AS "AmountSucc",
624 l_failed_amount AS "AmountWarning",
628
625 l_warning_amount AS "AmountFail"))
626 INTO l_summary
627 FROM dual;
629 -- generate the xmltype for failed inv
630 SELECT XMLElement("Invoices",
631 xmlagg(xmlelement("Invoice",
632 xmlforest(seq AS "sequence",
633 Transaction_Num AS
634 "TransactionNum",
635 Transaction_Type AS
636 "TransactionType",
637 Customer_Name AS
638 "CustomerName",
639 Amount AS "Amount",
640 FailedReason AS
641 "FailedReason"))))
642 INTO l_failed
643 FROM JMF_gta_transfer_temp
644 WHERE SUCCEEDED = 'N';
645
646 -- generate the xmltype for warning inv
647 SELECT XMLElement("Invoices",
648 xmlagg(xmlelement("Invoice",
649 xmlforest(seq AS "sequence",
650 Transaction_Num AS
651 "TransactionNum",
652 Transaction_Type AS
653 "TransactionType",
654 Customer_Name AS
655 "CustomerName",
656 Amount AS "Amount",
657 FailedReason AS
658 "WarningReason"))))
659 INTO l_warning
660 FROM JMF_gta_transfer_temp
661 WHERE SUCCEEDED = 'W';
662
663 --generate the xmltype for succ inv
664 SELECT XMLElement("Invoices",
665 xmlagg(xmlelement("Invoice",
666 xmlforest(SEQ AS "sequence",
667 Transaction_Num AS
668 "TransactionNum",
669 Transaction_Type AS
670 "TransactionType",
671 Customer_Name AS
672 "CustomerName",
673 Amount AS "Amount"
674 --12/04/2006 Jogen Hu bug 5144561
675 /*gta_invoice_num AS "GTAInvoiceNum"*/,
676 get_gta_number(transaction_id) AS
677 "GTAInvoiceNum"
678 --12/04/2006 Jogen Hu bug 5144561
679 ))))
680 INTO l_succeeded
681 FROM JMF_gta_transfer_temp
682 WHERE SUCCEEDED = 'Y';
683
684 --generate the final report
685 SELECT xmlelement("TransferReport",
686 xmlforest(l_reportFailed AS "ReportFailed",
687 l_FailedWithParameters AS
688 "FailedWithParameters",
689 JMF_GTA_TRX_UTIL.To_Xsd_Date_String(SYSDATE) AS
690 "ReqDate"
691 --, to_char(SYSDATE, l_date_format) AS "ReqDate"
692 ,
693 l_currency AS "CurrencyCode",
694 l_parameter AS "Parameters",
695 l_summary AS "Summary",
696 l_failed AS "FailedInvoices",
697 l_warning AS "WarningInvoices",
698 l_succeeded AS "SuccInvoices"))
699 INTO l_report_XML
700 FROM dual;
701
702 -- concurrent output
703 JMF_GTA_TRX_UTIL.output_conc(l_report_XML.Getclobval);
704
705 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
706 JMF_GTA_TRX_UTIL.debug_output_conc(l_report_XML.Getclobval);
707 END IF;
708
709 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
710 fnd_log.String(fnd_log.LEVEL_PROCEDURE,
711 G_MODULE_PREFIX || l_procedure_name,
712 'END procedure. ');
713 END IF;
714
715 EXCEPTION
716 WHEN OTHERS THEN
717 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
718 FND_LOG.String(FND_LOG.LEVEL_UNEXPECTED,
719 G_MODULE_PREFIX || l_procedure_name ||
720 '. OTHER_EXCEPTION ',
721 'Unknown error' || SQLCODE || SQLERRM);
722
723 END IF;
724 RAISE;
725 END Generate_XML_output;
726
727 --==========================================================================
728 -- PROCEDURE NAME:
729 -- Retrive_Valid_AR_TRXs
730 --
731 -- DESCRIPTION:
732 -- This procedure retrive and validate AR transaction
733 --
737 -- p_conc_parameters IN JMF_GTA_TRX_UTIL.transferParas_rec_type
734 -- PARAMETERS:
735 -- p_org_id IN NUMBER
736 -- p_transfer_id IN NUMBER
738 -- x_GTA_TRX_Tbl OUT NOCOPY JMF_GTA_TRX_UTIL.TRX_TBL_TYPE
739 --
740 -- DESIGN REFERENCES:
741 -- GTA-TRANSFER-PROGRAM-TD.doc
742 --
743 -- CHANGE HISTORY:
744 -- 20-APR-2005: Jim Zheng Created.
745 -- 16-Dec-2008 Yao Zhang Changed for bug 7644235
746 -- 23-01-2008 Yao Zhang Changed for bug 7758496
747 --===========================================================================
748 PROCEDURE Retrive_Valid_AR_TRXs(p_org_id IN NUMBER,
749 p_transfer_id IN NUMBER,
750 p_conc_parameters IN JMF_GTA_TRX_UTIL.transferParas_rec_type,
751 x_GTA_TRX_Tbl OUT NOCOPY JMF_GTA_TRX_UTIL.TRX_TBL_TYPE) IS
752 l_sql_exec VARCHAR2(4000);
753 l_procedure_name VARCHAR2(30) := 'retrive_valid_AR_TRXs';
754 l_trxtype_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type := JMF_GTA_TRX_UTIL.Condition_para_tbl_type();
755 l_flex_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type := JMF_GTA_TRX_UTIL.Condition_para_tbl_type();
756 l_other_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type := JMF_GTA_TRX_UTIL.Condition_para_tbl_type();
757 l_currency_code jmf_gta_system_parameters_all.gt_currency_code%TYPE;
758 l_gta_trx_tbl jmf_gta_trx_util.trx_tbl_type:= jmf_gta_trx_util.trx_tbl_type();
759 l_invoice_type_code VARCHAR2(1);
760 BEGIN
761
762 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
763 FND_LOG.STRING(fnd_log.LEVEL_PROCEDURE,
764 G_MODULE_PREFIX || l_procedure_name,
765 'Begin Procedure. ');
766 END IF;
767
768 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
769 log(FND_LOG.LEVEL_PROCEDURE,
770 G_MODULE_PREFIX || l_procedure_name,
771 'Begin Retrive_valid_ar_trx......');
772 END IF;
773
774 GET_AR_SQL(P_ORG_ID => P_ORG_ID,
775 p_transfer_id => p_transfer_id,
776 p_conc_parameters => p_conc_parameters,
777 x_QUERY_SQL => l_sql_exec,
778 x_trxtype_parameter => l_trxtype_parameter,
779 x_flex_parameter => l_flex_parameter,
780 x_other_parameter => l_other_parameter,
781 x_currency_code => l_currency_code);
782
783 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
784 log(FND_LOG.LEVEL_PROCEDURE,
785 G_MODULE_PREFIX || l_procedure_name,
786 'End get AR sql......');
787 END IF;
788
789 Retrieve_AR_TRXs(p_org_id => p_org_id,
790 p_transfer_id => p_transfer_id,
791 P_query_SQL => l_sql_exec,
792 P_trxtype_query_para => l_trxtype_parameter,
793 p_flex_query_para => l_flex_parameter,
794 p_other_query_para => l_other_parameter,
795 p_currency_code => l_currency_code,
796 x_GTA_TRX_TBL => x_GTA_TRX_Tbl);
797
798 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
799 log(FND_LOG.LEVEL_PROCEDURE,
800 G_MODULE_PREFIX || l_procedure_name,
801 'End retrieve_ar_trxs......');
802 log(FND_LOG.LEVEL_PROCEDURE,
803 G_MODULE_PREFIX || l_procedure_name,
804 'l_GTA_TRX_Tbl:' || x_GTA_TRX_Tbl.COUNT);
805 END IF;
806
807 --following code is recovered by Yao Zhang for bug 7644235
808 -- file the credit memo which the max amount or max line exceed
809 --commented by subba for R12.1, becoz of new credit memo process..
810 l_gta_Trx_tbl:=x_GTA_TRX_Tbl;
811 jmf_gta_split_trx_proc.filter_credit_memo(p_org_id => p_org_id
812 , p_transfer_id => p_transfer_id--yao zhang changed for bug 7758496
813 , p_gta_trx_tbl => l_gta_Trx_tbl
814 , x_gta_Trx_tbl => x_gta_trx_tbl
815 );
816 -- recovered by Yao Zhang for bug 7644235 end
817 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
818 log(FND_LOG.LEVEL_PROCEDURE,
819 G_MODULE_PREFIX || l_procedure_name,
820 'End filter_credit_memo......');
821 END IF;
822
823 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
824 FND_LOG.STRING(fnd_log.LEVEL_PROCEDURE,
825 G_MODULE_PREFIX || l_procedure_name,
826 'END procedure. ');
830 WHEN OTHERS THEN
827 END IF;
828
829 EXCEPTION
831 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
832 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
833 G_MODULE_PREFIX || l_procedure_name ||
834 '. OTHER_EXCEPTION ',
835 'Unknown error' || SQLCODE || SQLERRM);
836 END IF;
837 RAISE;
838
839 End retrive_valid_AR_TRXs;
840
841 --==========================================================================
842 -- PROCEDURE NAME:
843 -- Get_AR_SQL
844 --
845 -- DESCRIPTION:
846 -- This procedure returns the SQL for Receivable
847 -- VAT transaction retrieval
848 --
849 -- PARAMETERS:
850 -- In: P_ORG_ID NUMBER
851 -- p_transfer_id VARCHAR2
852 -- p_conc_parameters JMF_GTA_TRX_UTIL.transferParas_rec_type
853 -- OUT: x_query_sql VARCHAR2
854 -- x_trxtype_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type
855 -- x_flex_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type
856 -- x_other_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type
857 -- x_currency_code VARCHAR2
858
859 -- DESIGN REFERENCES:
860 -- GTA-TRANSFER-PROGRAM-TD.doc
861 --
862 -- CHANGE HISTORY:
863 -- 20-APR-2005: Jim Zheng Created.
864 -- 24-Nov-2005: Jim Zheng Change the period_type select
865 -- condition in dynamic SQL
866 -- 28-Dec-2007: Subba Changed, included condition for invoice type
867 -- to support new tax regulation change in R12.1
868 --===========================================================================
869 PROCEDURE Get_AR_SQL(P_ORG_ID IN NUMBER,
870 p_transfer_id IN NUMBER,
871 p_conc_parameters IN JMF_GTA_TRX_UTIL.transferParas_rec_type,
872 x_query_sql OUT NOCOPY VARCHAR2,
873 x_trxtype_parameter OUT NOCOPY JMF_GTA_TRX_UTIL.Condition_para_tbl_type,
874 x_flex_parameter OUT NOCOPY JMF_GTA_TRX_UTIL.Condition_para_tbl_type,
875 x_other_parameter OUT NOCOPY JMF_GTA_TRX_UTIL.Condition_para_tbl_type,
876 x_currency_code OUT NOCOPY VARCHAR2) IS
877 l_select_sql VARCHAR2(4000);
878
879 l_TRX_TYPE_condition VARCHAR2(2000);
880 l_flex_condition VARCHAR2(2000);
881 l_other_condition VARCHAR2(2000);
882 l_currency_condition VARCHAR2(500);
883 l_invoice_type_condition VARCHAR2(2000); --Newly added by Subba for R12.1
884 l_error_string VARCHAR2(400);
885 l_procedure_name VARCHAR2(30) := 'Get_AR_SQL';
886
887 BEGIN
888
889 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
890 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
891 G_MODULE_PREFIX || l_procedure_name,
892 'Begin Procedure. ');
893 END IF;
894
895 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
896 log(FND_LOG.LEVEL_PROCEDURE,
897 G_MODULE_PREFIX || l_procedure_name,
898 'Begin get AR sql......');
899 log(FND_LOG.LEVEL_PROCEDURE,
900 G_MODULE_PREFIX || l_procedure_name,
901 'P_ORG_ID: ' || P_ORG_ID);
902 log(FND_LOG.LEVEL_PROCEDURE,
903 G_MODULE_PREFIX || l_procedure_name,
904 'p_transfer_id: ' || p_transfer_id);
905 END IF;
906
907 l_select_sql := 'SELECT h.customer_trx_id
908 FROM
909 ra_customer_trx_all h
910 , ra_cust_trx_types_all ctt
911 , ra_batches_all b
912 , Ra_Cust_Trx_Line_Gl_Dist_All gd
913 , Hz_Parties RAC_BILL_PARTY
914 , Hz_Cust_Accounts RAC_BILL
915 , GL_PERIODS GP
916 WHERE h.complete_flag = ''Y''
917 AND h.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID(+)
918 AND ctt.TYPE IN (''INV'', ''CM'', ''DM'')
919 AND h.batch_id = b.batch_id(+)
920 AND GD.CUSTOMER_TRX_ID = h.CUSTOMER_TRX_ID
921 AND GD.ACCOUNT_CLASS = ''REC''
922 AND GD.LATEST_REC_FLAG = ''Y''
923 AND h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
924 AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id
925 AND h.Org_Id = gd.Org_Id
926 AND h.Org_Id = ctt.Org_Id
927 AND h.Org_Id =:p_org_id
928 AND GP.PERIOD_SET_NAME = (SELECT period_set_name
929 FROM Gl_Sets_Of_Books
930 WHERE set_of_books_id = h.set_of_books_id)
931 AND gp.period_type = (SELECT accounted_period_type
935 AND gp.start_date <= gd.GL_DATE
932 FROM Gl_Sets_Of_Books
933 WHERE set_of_books_id = h.set_of_books_id)
934 AND gp.adjustment_period_flag = ''N''
936 AND gp.end_date >= gd.gl_date ';
937
938 -- generate dynamic sql for trxtype condition
939 GET_AR_TRXTYPE_COND(P_ORG_ID => P_ORG_ID,
940 p_transfer_id => p_transfer_id,
941 x_condition_sql => l_TRX_TYPE_condition,
942 x_query_parameter => x_trxtype_parameter);
943
944 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
945 log(FND_LOG.LEVEL_PROCEDURE,
946 G_MODULE_PREFIX || l_procedure_name,
947 'End GET_AR_TRXTYPE_COND......');
948 log(FND_LOG.LEVEL_PROCEDURE,
949 G_MODULE_PREFIX || l_procedure_name,
950 'l_TRX_TYPE_condition:' || l_TRX_TYPE_condition);
951 END IF;
952
953 -- generate dynamic sql for flex field condition
954 GET_AR_FLEX_COND(P_ORG_ID => P_ORG_ID,
955 p_transfer_id => p_transfer_id,
956 x_condition_sql => l_flex_condition,
957 x_query_parameter => x_flex_parameter);
958
959 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
960 log(FND_LOG.LEVEL_PROCEDURE,
961 G_MODULE_PREFIX || l_procedure_name,
962 'End GET_AR_FLEX_COND......');
963 log(FND_LOG.LEVEL_PROCEDURE,
964 G_MODULE_PREFIX || l_procedure_name,
965 'l_flex_condition:' || l_flex_condition);
966 END IF;
967
968 -- generate dynamic sql for parameter condition
969 GET_PARAM_COND(P_ORG_ID => P_ORG_ID,
970 p_transfer_id => p_transfer_id,
971 p_conc_parameters => p_conc_parameters,
972 x_condition_sql => l_other_condition,
973 x_query_parameter => x_other_parameter);
974
975 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
976 log(FND_LOG.LEVEL_PROCEDURE,
977 G_MODULE_PREFIX || l_procedure_name,
978 'End GET_param_COND......');
979 log(FND_LOG.LEVEL_PROCEDURE,
980 G_MODULE_PREFIX || l_procedure_name,
981 'l_other_condition:' || l_other_condition);
982 END IF;
983
984 --generate dynamic sql for currency code condition
985 Get_AR_Currency_Cond(p_ORG_ID => p_org_id,
986 p_transfer_id => p_transfer_id,
987 x_condition_sql => l_currency_condition,
988 x_currency_code => x_currency_code);
989
990 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
991 log(FND_LOG.LEVEL_PROCEDURE,
992 G_MODULE_PREFIX || l_procedure_name,
993 'End Get_AR_Currency_Cond......');
994 log(FND_LOG.LEVEL_PROCEDURE,
995 G_MODULE_PREFIX || l_procedure_name,
996 'l_currency_condition:' || l_currency_condition);
997 END IF;
998
999 --generate dynamic sql for invoice type, Added by Subba
1000
1001 Get_Invoice_Type(p_ORG_ID => p_org_id,
1002 p_transfer_id => p_transfer_id,
1003 x_condition_sql => l_invoice_type_condition);
1004
1005 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1006 log(FND_LOG.LEVEL_PROCEDURE,
1007 G_MODULE_PREFIX || l_procedure_name,
1008 'End Get_Invoice_Type......');
1009 log(FND_LOG.LEVEL_PROCEDURE,
1010 G_MODULE_PREFIX || l_procedure_name,
1011 'l_invoice_type_condition:' || l_invoice_type_condition);
1012 END IF;
1013
1014 -- concatenate dynamic sql
1015 -- l_trx_type_condition
1016 IF (l_TRX_TYPE_condition IS NOT NULL) THEN
1017 l_select_sql := l_select_sql || l_TRX_TYPE_condition;
1018 END IF;
1019
1020 -- l_flex_condition
1021 IF (l_flex_condition IS NOT NULL) THEN
1022 l_select_sql := l_select_sql || l_flex_condition;
1023 END IF;
1024
1025 -- l_other_condition
1026 IF (l_other_condition IS NOT NULL) THEN
1027 l_select_sql := l_select_sql || l_other_condition;
1028 END IF;
1029
1030 -- l_currency_condition
1031 IF (l_currency_condition IS NOT NULL) THEN
1032 l_select_sql := l_select_sql || l_currency_condition;
1033 END IF;
1034
1035 -- l_invoice_type_condition, added by Subba
1036 IF (l_invoice_type_condition IS NOT NULL) THEN
1037 l_select_sql := l_select_sql || l_invoice_type_condition;
1038 END IF;
1039
1040 x_query_sql := l_select_sql;
1041
1042 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1043 log(FND_LOG.LEVEL_PROCEDURE,
1044 G_MODULE_PREFIX || l_procedure_name,
1045 'l_select_sql:' || l_select_sql);
1046 END IF;
1047
1048 -- log output the ar sql
1049 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1050 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1051 G_MODULE_PREFIX || l_procedure_name,
1052 l_select_sql);
1053 END IF;
1054
1055 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1059 END IF;
1056 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1057 G_MODULE_PREFIX || l_procedure_name,
1058 'END procedure. ');
1060
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1064 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1065 G_MODULE_PREFIX || l_procedure_name ||
1066 '. OTHER_EXCEPTION ',
1067 'Unknown error' || SQLCODE || SQLERRM);
1068
1069 END IF;
1070 RAISE;
1071 END Get_AR_SQL;
1072
1073 --============================================================================
1074
1075 -- PROCEDURE NAME:
1076 -- Get_Invoice_Type
1077 --
1078 -- DESCRIPTION:
1079 -- This procedure returns the WHERE clause about Invoice Type using the
1080 -- Invoice Type,Transaction Type mapping relationship defined in GTA
1081 -- System Option.
1082 -- PARAMETERS:
1083 -- In: P_ORG_ID NUMBER
1084 -- p_transfer_id VARCHAR2
1085
1086 -- OUT: x_condition_sql Varchar2
1087 -- CHANGE HISTORY:
1088 -- 28-Dec-2007: Subba Created.
1089 -- 24-Dec-2008 Yao Zhang Changed for bug 7667709
1090 -- ===========================================================================
1091
1092 PROCEDURE Get_Invoice_Type(p_ORG_ID IN NUMBER,
1093 p_transfer_id IN NUMBER,
1094 x_condition_sql OUT NOCOPY VARCHAR2) IS
1095 l_procedure_name VARCHAR2(50) := 'Get_Invoice_Type';
1096 l_error_string VARCHAR2(2000);
1097 l_invoice_type_code jmf_gta_tax_limits_all.invoice_type%TYPE;
1098
1099 l_transaction_type_cnt NUMBER;
1100
1101 BEGIN
1102
1103 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1104 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1105 G_MODULE_PREFIX || l_procedure_name,
1106 'Begin Procedure. ');
1107 END IF;
1108
1109 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1110 log(FND_LOG.LEVEL_PROCEDURE,
1111 G_MODULE_PREFIX || l_procedure_name,
1112 'Begin Get_Invoice_Type......');
1113 log(FND_LOG.LEVEL_PROCEDURE,
1114 G_MODULE_PREFIX || l_procedure_name,
1115 'p_org_id:' || p_org_id || ' ' || 'p_transfer_id:' ||
1116 p_transfer_id);
1117 END IF;
1118
1119 -- select invoice type from transfer rule setup
1120
1121 -- BEGIN
1122
1123 SELECT jgrha.invoice_type
1124 INTO l_invoice_type_code
1125 FROM jmf_gta_rule_headers_all jgrha
1126 WHERE jgrha.rule_header_id = p_transfer_id;
1127
1128 /* EXCEPTION
1129 -- no data found , raise a data error
1130 WHEN no_data_found THEN
1131 fnd_message.SET_NAME('JMF', 'JMF_GTA_RULE_MISSING_ERROR');
1132 l_error_string := fnd_message.get();
1133 -- output error
1134 fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8" ?>
1135 <TransferReport>
1136 <ReportFailed>Y</ReportFailed>
1137 <ReportFailedMsg>'||l_error_string
1138 ||'</ReportFailedMsg>
1139 <TransferReport>');
1140
1141 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1142 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
1143 ,G_MODULE_PREFIX || l_procedure_name
1144 , 'no data found when select invoice_type.');
1145 END IF;
1146 RAISE;
1147 END;*/
1148
1149 -- if invoice type is A, set concatenating sql string as NULL
1150
1151 -- else get transaction type id by actual invoice type, if no data found, raise an
1152
1153 -- error.
1154
1155 IF l_invoice_type_code IS NOT NULL THEN
1156
1157 IF l_invoice_type_code = 'A' THEN
1158 x_condition_sql := NULL;
1159
1160 ELSE
1161 BEGIN
1162 SELECT count(jgtm.transaction_type_id)
1163 INTO l_transaction_type_cnt
1164 FROM jmf_gta_type_mappings jgtm, jmf_gta_tax_limits_all jgtla
1165 WHERE jgtla.limitation_id = jgtm.limitation_id
1166 AND jgtla.invoice_type = l_invoice_type_code
1167 AND jgtla.org_id = p_org_id;
1168
1169 EXCEPTION
1170
1171 -- no data found, raise a data error
1172 WHEN no_data_found THEN
1173 fnd_message.SET_NAME('JMF', 'JMF_GTA_TRX_TYP_MAP_MISSING');
1174 l_error_string := fnd_message.get();
1175 -- output error
1176 fnd_file.put_line(fnd_file.output,
1177 '<?xml version="1.0" encoding="UTF-8" ?>
1178 <TransferReport>
1179 <ReportFailed>Y</ReportFailed>
1180 <ReportFailedMsg>' ||
1184
1181 l_error_string ||
1182 '</ReportFailedMsg>
1183 </TransferReport>');--Modified by Yao Zhang for bug 7667709
1185
1186 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1187 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
1188 G_MODULE_PREFIX || l_procedure_name,
1189 'no data found when select invoice_type.');
1190 END IF;
1191 RAISE;
1192
1193 RETURN;
1194 END;
1195
1196 -- set concatenating sql where clause
1197 x_condition_sql := ' AND h.cust_trx_type_id IN
1198 (SELECT jgtm.transaction_type_id
1199 FROM jmf_gta_type_mappings jgtm
1200 ,jmf_gta_tax_limits_all jgtla
1201 WHERE jgtm.limitation_id = jgtla.limitation_id
1202 AND jgtla.invoice_type = ''' ||
1203 l_invoice_type_code || '''
1204 AND jgtla.org_id = :p_org_id)';
1205
1206 END IF; /*l_invoice_type_code = 'A'*/
1207
1208 END IF; /*l_invoice_type_code IS NOT NULL*/
1209
1210 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1211 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1212 G_MODULE_PREFIX || l_procedure_name,
1213 'END procedure. ');
1214 END IF;
1215 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1216 log(FND_LOG.LEVEL_PROCEDURE,
1217 G_MODULE_PREFIX || l_procedure_name,
1218 'End Get_Invoice_Type......');
1219 END IF;
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1223 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1224 G_MODULE_PREFIX || l_procedure_name ||
1225 '. OTHER_EXCEPTION ',
1226 'Unknown error' || SQLCODE || SQLERRM);
1227
1228 END IF;
1229 RAISE;
1230 END Get_Invoice_Type;
1231
1232 --==========================================================================
1233 -- PROCEDURE NAME:
1234 -- Get_AR_TrxType_Cond
1235 --
1236 -- DESCRIPTION:
1237 -- This procedure returns the WHERE clause
1238 -- about transaction type
1239 --
1240 -- PARAMETERS:
1241 -- In: P_ORG_ID NUMBER
1242 -- p_transfer_id VARCHAR2
1243
1244 -- OUT: x_condition_sql Varchar2
1245 -- x_query_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type
1246
1247 -- DESIGN REFERENCES:
1248 -- GTA-TRANSFER-PROGRAM-TD.doc
1249 --
1250 -- CHANGE HISTORY:
1251 -- 20-APR-2005: Jim Zheng Created.
1252 --===========================================================================
1253 PROCEDURE Get_AR_TrxType_Cond(p_ORG_ID IN NUMBER,
1254 p_transfer_id IN NUMBER,
1255 x_condition_sql OUT NOCOPY VARCHAR2,
1256 x_query_parameter OUT NOCOPY JMF_GTA_TRX_UTIL.Condition_para_tbl_type) IS
1257 l_procedure_name VARCHAR2(50) := 'Get_AR_TrxType_Cond';
1258 l_parameter_prefix VARCHAR2(10) := ':trxtype';
1259 l_parameter_suffix NUMBER;
1260 l_include_flag VARCHAR2(5);
1261 l_cust_trx_type_id VARCHAR2(30);
1262
1263 CURSOR trx_type_cond_i IS
1264 SELECT l.cust_trx_type_id
1265 FROM JMF_GTA_RULE_TRX_TYPES_ALL l
1266 WHERE l.rule_header_id = p_transfer_id
1267 AND l.condition_rule = 'I';
1268
1269 CURSOR trx_type_cond_e IS
1270 SELECT l.cust_trx_type_id
1271 FROM jmf_gta_rule_trx_types_all l
1272 WHERE l.rule_header_id = p_transfer_id
1273 AND l.condition_rule = 'E';
1274
1275 BEGIN
1276
1277 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1278 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1279 G_MODULE_PREFIX || l_procedure_name,
1280 'Begin Procedure. ');
1281 END IF;
1282
1283 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1284 log(FND_LOG.LEVEL_PROCEDURE,
1285 G_MODULE_PREFIX || l_procedure_name,
1286 'Begin Get_AR_TrxType_Cond......');
1287 END IF;
1288
1289 -- init the sql string and parameter table.
1290 x_query_parameter := JMF_GTA_TRX_UTIL.Condition_para_tbl_type();
1291 l_parameter_suffix := 0;
1292
1293 -- Generate the dynamic sql which condition_rule is 'I'
1294 OPEN trx_type_cond_i;
1295
1296 -- fetch first line because the first line is different with others
1297 FETCH trx_type_cond_i
1298 INTO l_cust_trx_type_id;
1299
1300 IF l_cust_trx_type_id IS NOT NULL THEN
1301 x_query_parameter.EXTEND;
1302 x_query_parameter(x_query_parameter.COUNT) := l_cust_trx_type_id;
1303 x_condition_sql := x_condition_sql || ' AND ( h.cust_trx_type_id = ' ||
1304 l_parameter_prefix || (l_parameter_suffix + 1);
1305 l_parameter_suffix := l_parameter_suffix + 1;
1309 FETCH trx_type_cond_i
1306 END IF;
1307
1308 LOOP
1310 INTO l_cust_trx_type_id;
1311 EXIT WHEN trx_type_cond_i%NOTFOUND;
1312 x_query_parameter.EXTEND;
1313 x_query_parameter(x_query_parameter.COUNT) := l_cust_trx_type_id;
1314
1315 x_condition_sql := x_condition_sql || ' OR ' ||
1316 ' h.cust_trx_type_id = ' || l_parameter_prefix ||
1317 (l_parameter_suffix + 1);
1318 l_parameter_suffix := l_parameter_suffix + 1;
1319 END LOOP;
1320
1321 -- add the right bracket
1322 IF x_condition_sql IS NOT NULL THEN
1323 x_condition_sql := x_condition_sql || ' ) ';
1324 END IF;
1325
1326 CLOSE trx_type_cond_i;
1327
1328 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1329 log(FND_LOG.LEVEL_PROCEDURE,
1330 G_MODULE_PREFIX || l_procedure_name,
1331 'End trx_type_cond_i......');
1332 END IF;
1333
1334 -- Generate the dynamic sql which condition_rule is 'I'
1335 OPEN trx_type_cond_e;
1336 LOOP
1337 FETCH trx_type_cond_e
1338 INTO l_cust_trx_type_id;
1339 EXIT WHEN trx_type_cond_e%NOTFOUND;
1340 x_query_parameter.EXTEND;
1341 x_query_parameter(x_query_parameter.COUNT) := l_cust_trx_type_id;
1342 x_condition_sql := x_condition_sql || ' AND (NOT ' ||
1343 'h.cust_trx_type_id = ' || l_parameter_prefix ||
1344 (l_parameter_suffix + 1) || ')';
1345 l_parameter_suffix := l_parameter_suffix + 1;
1346 END LOOP;
1347 CLOSE trx_type_cond_e;
1348
1349 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1350 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1351 G_MODULE_PREFIX || l_procedure_name,
1352 'END procedure. ');
1353 END IF;
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1358 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1359 G_MODULE_PREFIX || l_procedure_name ||
1360 '. OTHER_EXCEPTION ',
1361 'Unknown error' || SQLCODE || SQLERRM);
1362
1363 END IF;
1364 RAISE;
1365
1366 END Get_AR_TrxType_Cond;
1367
1368 --==========================================================================
1369 -- PROCEDURE NAME:
1370 -- Get_AR_FLEX_COND
1371 --
1372 -- DESCRIPTION:
1373 -- This procedure returns the WHERE clause
1374 -- about flexfield condition
1375 --
1376 -- PARAMETERS:
1377 -- In: P_ORG_ID NUMBER
1378 -- p_transfer_id VARCHAR2
1379
1380 -- OUT: x_condition_sql Varchar2
1381 -- x_query_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type
1382
1383 -- DESIGN REFERENCES:
1384 -- GTA-TRANSFER-PROGRAM-TD.doc
1385 --
1386 -- CHANGE HISTORY:
1387 -- 20-APR-2005: Jim Zheng Created.
1388 --===========================================================================
1389 PROCEDURE Get_AR_FLEX_COND(P_ORG_ID IN NUMBER,
1390 p_transfer_id IN NUMBER,
1391 x_condition_sql OUT NOCOPY VARCHAR2,
1392 x_query_parameter OUT NOCOPY JMF_GTA_TRX_UTIL.Condition_para_tbl_type) IS
1393 l_procedure_name VARCHAR2(50) := 'Get_AR_FLEX_COND';
1394 l_parameter_prefix VARCHAR2(10) := ':flex';
1395 l_parameter_suffix NUMBER;
1396
1397 l_include_flag VARCHAR2(5);
1398 l_CONTEXT_CODE JMF_GTA_RULE_DFFS_ALL.Context_Code%TYPE;
1399 l_ATTRIBUTE_COLUMN JMF_GTA_RULE_DFFS_ALL.Attribute_Column%TYPE;
1400 l_ATTRIBUTE_value JMF_GTA_RULE_DFFS_ALL.Attribute_Value%TYPE;
1401
1402 CURSOR flex_cond_i IS
1403 SELECT l.context_code, l.attribute_column, l.attribute_value
1404 FROM JMF_GTA_RULE_DFFS_ALL l
1405 WHERE l.org_id = P_ORG_ID
1406 AND l.rule_header_id = p_transfer_id
1407 AND l.condition_rule = 'I';
1408
1409 CURSOR flex_cond_e IS
1410 SELECT l.context_code, l.attribute_column, l.attribute_value
1411 FROM jmf_gta_rule_dffs_all l
1412 WHERE l.Org_Id = P_ORG_ID
1413 AND l.rule_header_id = p_transfer_id
1414 AND l.condition_rule = 'E';
1415
1416 BEGIN
1417
1418 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1419 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1420 G_MODULE_PREFIX || l_procedure_name,
1421 'Begin Procedure. ');
1422 END IF;
1423
1424 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1425 log(FND_LOG.LEVEL_PROCEDURE,
1426 G_MODULE_PREFIX || l_procedure_name,
1427 'begin get_ar_flex......');
1428 END IF;
1429
1430 x_query_parameter := JMF_GTA_TRX_UTIL.Condition_para_tbl_type();
1431 l_parameter_suffix := 0;
1432
1433 -- Generate the dynamic sql for flex filed which the condition rule is 'I'
1434 OPEN flex_cond_i;
1435 -- fetch the first line because the first line's dynamic sql
1436 --is different with the others
1437 FETCH flex_cond_i
1441 x_query_parameter.EXTEND;
1438 INTO l_CONTEXT_CODE, l_ATTRIBUTE_COLUMN, l_ATTRIBUTE_value;
1439
1440 IF l_context_code IS NOT NULL THEN
1442 x_query_parameter(x_query_parameter.COUNT) := l_context_code;
1443 x_query_parameter.EXTEND;
1444 x_query_parameter(x_query_parameter.COUNT) := l_attribute_value;
1445 x_condition_sql := x_condition_sql ||
1446 ' AND ( (h.attribute_category = ' ||
1447 l_parameter_prefix || (l_parameter_suffix + 1) ||
1448 ' AND h.' || l_ATTRIBUTE_COLUMN || ' = ' ||
1449 l_parameter_prefix || (l_parameter_suffix + 2) || ')';
1450 l_parameter_suffix := l_parameter_suffix + 2;
1451 END IF;
1452
1453 LOOP
1454 FETCH flex_cond_i
1455 INTO l_CONTEXT_CODE, l_ATTRIBUTE_COLUMN, l_ATTRIBUTE_value;
1456 EXIT WHEN flex_cond_i%NOTFOUND;
1457 x_query_parameter.EXTEND;
1458 x_query_parameter(x_query_parameter.COUNT) := l_context_code;
1459 x_query_parameter.EXTEND;
1460 x_query_parameter(x_query_parameter.COUNT) := l_attribute_value;
1461 x_condition_sql := x_condition_sql || ' OR (h.attribute_category = ' ||
1462 l_parameter_prefix || (l_parameter_suffix + 1) ||
1463 ' AND h.' || l_ATTRIBUTE_COLUMN || ' = ' ||
1464 l_parameter_prefix || (l_parameter_suffix + 2) || ')';
1465 l_parameter_suffix := l_parameter_suffix + 2;
1466
1467 END LOOP;
1468
1469 -- add the right bracket
1470 IF x_condition_sql IS NOT NULL THEN
1471 x_condition_sql := x_condition_sql || ')';
1472 END IF;
1473 CLOSE flex_cond_i;
1474
1475 -- Generate the dynamic sql for flex filed which the condition rule is 'E'
1476 OPEN flex_cond_e;
1477 LOOP
1478 FETCH flex_cond_e
1479 INTO l_CONTEXT_CODE, l_ATTRIBUTE_COLUMN, l_ATTRIBUTE_value;
1480 EXIT WHEN flex_cond_e%NOTFOUND;
1481 x_query_parameter.EXTEND;
1482 x_query_parameter(x_query_parameter.COUNT) := l_context_code;
1483 x_query_parameter.EXTEND;
1484 x_query_parameter(x_query_parameter.COUNT) := l_attribute_value;
1485
1486 x_condition_sql := x_condition_sql ||
1487 ' AND (NOT (h.attribute_category = ' ||
1488 l_parameter_prefix || (l_parameter_suffix + 1) ||
1489 ' AND h.' || l_ATTRIBUTE_COLUMN || '=' ||
1490 l_parameter_prefix || (l_parameter_suffix + 2) || ')';
1491 --Added by Shujuan for bug 5443909
1492 --When dff attribution is null, should transfer the invoice
1493 x_condition_sql := x_condition_sql || ' OR h.' ||
1494 l_ATTRIBUTE_COLUMN || ' IS NULL)';
1495 l_parameter_suffix := l_parameter_suffix + 2;
1496
1497 END LOOP;
1498
1499 CLOSE flex_cond_e;
1500
1501 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1502 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1503 G_MODULE_PREFIX || l_procedure_name,
1504 'END procedure. ');
1505 END IF;
1506
1507 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1508 log(FND_LOG.LEVEL_PROCEDURE,
1509 G_MODULE_PREFIX || l_procedure_name,
1510 'End get_ar_flex......');
1511 END IF;
1512
1513 EXCEPTION
1514 WHEN OTHERS THEN
1515 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1516 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1517 G_MODULE_PREFIX || l_procedure_name ||
1518 '. OTHER_EXCEPTION ',
1519 'Unknown error');
1520
1521 END IF;
1522 RAISE;
1523
1524 END Get_AR_FLEX_COND;
1525
1526 --==========================================================================
1527 -- PROCEDURE NAME:
1528 -- Get_Param_Cond
1529 --
1530 -- DESCRIPTION:
1531 -- This procedure returns the WHERE clause
1532 -- about request parameter and fixed condition
1533 --
1534 -- PARAMETERS:
1535 -- In: P_ORG_ID NUMBER
1536 -- p_transfer_id VARCHAR2
1537 -- p_conc_parameters JMF_GTA_TRX_UTIL.transferParas_rec_type
1538
1539 -- OUT: x_condition_sql Varchar2
1540 -- x_query_parameter JMF_GTA_TRX_UTIL.Condition_para_tbl_type
1541
1542 -- DESIGN REFERENCES:
1543 -- GTA-TRANSFER-PROGRAM-TD.doc
1544 --
1545 -- CHANGE HISTORY:
1546 -- 20-APR-2005: Jim Zheng Created.
1547 --
1548 --===========================================================================
1549 PROCEDURE Get_Param_Cond(P_ORG_ID IN NUMBER,
1550 p_transfer_id IN NUMBER,
1551 p_conc_parameters IN JMF_GTA_TRX_UTIL.transferParas_rec_type,
1552 x_condition_sql OUT NOCOPY VARCHAR2,
1553 x_query_parameter OUT NOCOPY JMF_GTA_TRX_UTIL.Condition_para_tbl_type) IS
1554 l_procedure_name VARCHAR2(30) := 'Get_Param_Cond';
1555 l_parameter_prefix VARCHAR2(10) := ':para';
1556 l_parameter_suffix NUMBER;
1557
1558 BEGIN
1562 G_MODULE_PREFIX || l_procedure_name,
1559
1560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1561 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1563 'Begin Procedure. ');
1564 END IF;
1565
1566 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1567 log(FND_LOG.LEVEL_PROCEDURE,
1568 G_MODULE_PREFIX || l_procedure_name,
1569 'begin get_param_cond......');
1570 END IF;
1571
1572 -- init
1573 x_condition_sql := '';
1574 x_query_parameter := JMF_GTA_TRX_UTIL.Condition_para_tbl_type();
1575 l_parameter_suffix := 0;
1576
1577 -- if the from parameter and to parameter is null
1578 --and don't add the condition to dynamic sql
1579 IF NOT (p_conc_parameters.CUSTOMER_NUM_FROM IS NULL AND
1580 p_conc_parameters.CUSTOMER_NUM_TO IS NULL) THEN
1581 x_query_parameter.EXTEND;
1582 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.CUSTOMER_NUM_FROM,
1583 ' ');
1584 x_query_parameter.EXTEND;
1585 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.CUSTOMER_NUM_TO,
1586 rpad('z', 30, 'z'));
1587
1588 x_condition_sql := x_condition_sql ||
1589 ' AND RAC_BILL.ACCOUNT_NUMBER BETWEEN ' ||
1590 l_parameter_prefix || (l_parameter_suffix + 1) ||
1591 ' AND ' || l_parameter_prefix ||
1592 (l_parameter_suffix + 2);
1593 l_parameter_suffix := l_parameter_suffix + 2;
1594 END IF;
1595
1596 IF NOT (p_conc_parameters.CUSTOMER_NAME_FROM IS NULL AND
1597 p_conc_parameters.CUSTOMER_NAME_TO IS NULL) THEN
1598 x_query_parameter.EXTEND;
1599 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.CUSTOMER_NAME_FROM,
1600 ' ');
1601 x_query_parameter.EXTEND;
1602 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.CUSTOMER_NAME_FROM,
1603 rpad('z', 30, 'z'));
1604
1605 x_condition_sql := x_condition_sql ||
1606 ' AND RAC_BILL_PARTY.Party_Name BETWEEN ' ||
1607 l_parameter_prefix || (l_parameter_suffix + 1) ||
1608 ' AND ' || l_parameter_prefix ||
1609 (l_parameter_suffix + 2);
1610 l_parameter_suffix := l_parameter_suffix + 2;
1611 END IF;
1612
1613 IF p_conc_parameters.GL_PERIOD IS NOT NULL THEN
1614 x_query_parameter.EXTEND;
1615 x_query_parameter(x_query_parameter.COUNT) := p_conc_parameters.GL_PERIOD;
1616
1617 x_condition_sql := x_condition_sql || ' AND GP.period_name = ' ||
1618 l_parameter_prefix || (l_parameter_suffix + 1);
1619 l_parameter_suffix := l_parameter_suffix + 1;
1620 END IF;
1621
1622 IF NOT (p_conc_parameters.GL_DATE_FROM IS NULL AND
1623 p_conc_parameters.GL_DATE_TO IS NULL) THEN
1624 x_query_parameter.EXTEND;
1625 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.GL_DATE_FROM,
1626 to_date('1900-01-01',
1627 'RRRR-MM-DD'));
1628 x_query_parameter.EXTEND;
1629 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.GL_DATE_TO,
1630 to_date('2100-01-01',
1631 'RRRR-MM-DD'));
1632
1633 --bug 5107043, Jogen Mar-22,2006
1634 x_condition_sql := x_condition_sql ||
1635 ' AND trunc(gd.gl_date,''DDD'') BETWEEN ' ||
1636 l_parameter_prefix || (l_parameter_suffix + 1) ||
1637 ' AND ' || l_parameter_prefix ||
1638 (l_parameter_suffix + 2);
1639
1640 l_parameter_suffix := l_parameter_suffix + 2;
1641 END IF;
1642
1643 IF NOT (p_conc_parameters.TRX_BATCH_FROM IS NULL AND
1644 p_conc_parameters.TRX_BATCH_TO IS NULL) THEN
1645 x_query_parameter.EXTEND;
1646 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.TRX_BATCH_FROM,
1647 ' ');
1648 x_query_parameter.EXTEND;
1649 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.TRX_BATCH_TO,
1650 rpad('z', 30, 'z'));
1651
1652 x_condition_sql := x_condition_sql || ' AND b.name BETWEEN ' ||
1653 l_parameter_prefix || (l_parameter_suffix + 1) ||
1654 ' AND ' || l_parameter_prefix ||
1655 (l_parameter_suffix + 2);
1656 l_parameter_suffix := l_parameter_suffix + 2;
1657 END IF;
1658
1659 IF NOT (p_conc_parameters.TRX_NUMBER_FROM IS NULL AND
1660 p_conc_parameters.TRX_NUMBER_TO IS NULL) THEN
1661 x_query_parameter.EXTEND;
1662 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.TRX_NUMBER_FROM,
1663 ' ');
1664 x_query_parameter.EXTEND;
1668 x_condition_sql := x_condition_sql || ' AND h.trx_number BETWEEN ' ||
1665 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.TRX_NUMBER_TO,
1666 rpad('z', 30, 'z'));
1667
1669 l_parameter_prefix || (l_parameter_suffix + 1) ||
1670 ' AND ' || l_parameter_prefix ||
1671 (l_parameter_suffix + 2);
1672
1673 l_parameter_suffix := l_parameter_suffix + 2;
1674 END IF;
1675
1676 IF NOT (p_conc_parameters.TRX_DATE_FROM IS NULL AND
1677 p_conc_parameters.TRX_DATE_TO IS NULL) THEN
1678 x_query_parameter.EXTEND;
1679 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.TRX_DATE_FROM,
1680 to_date('1900-01-01',
1681 'RRRR-MM-DD'));
1682 x_query_parameter.EXTEND;
1683 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.TRX_DATE_TO,
1684 to_date('2100-01-01',
1685 'RRRR-MM-DD'));
1686 --bug 5107043, Jogen Mar-22,2006
1687 x_condition_sql := x_condition_sql ||
1688 ' AND trunc(h.trx_date,''DDD'') BETWEEN ' ||
1689 l_parameter_prefix || (l_parameter_suffix + 1) ||
1690 ' AND ' || l_parameter_prefix ||
1691 (l_parameter_suffix + 2);
1692 l_parameter_suffix := l_parameter_suffix + 2;
1693 END IF;
1694
1695 IF NOT (p_conc_parameters.DOC_NUM_FROM IS NULL AND
1696 p_conc_parameters.DOC_NUM_TO IS NULL) THEN
1697 x_query_parameter.EXTEND;
1698 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.DOC_NUM_FROM,
1699 0);
1700 x_query_parameter.EXTEND;
1701 x_query_parameter(x_query_parameter.COUNT) := nvl(p_conc_parameters.DOC_NUM_TO,
1702 10E16);
1703
1704 x_condition_sql := x_condition_sql ||
1705 ' AND h.DOC_SEQUENCE_VALUE BETWEEN ' ||
1706 l_parameter_prefix || (l_parameter_suffix + 1) ||
1707 ' AND ' || l_parameter_prefix ||
1708 (l_parameter_suffix + 2);
1709
1710 END IF;
1711
1712 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1713 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1714 G_MODULE_PREFIX || l_procedure_name,
1715 'END procedure. ');
1716 END IF;
1717
1718 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1719 log(FND_LOG.LEVEL_PROCEDURE,
1720 G_MODULE_PREFIX || l_procedure_name,
1721 'End get_ar_flex......');
1722 END IF;
1723
1724 EXCEPTION
1725 WHEN OTHERS THEN
1726 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1727
1728 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1729 G_MODULE_PREFIX || l_procedure_name ||
1730 '.OTHER_EXCEPTION.',
1731 '.Unknown .error.' || SQLCODE || SQLERRM);
1732
1733 END IF;
1734 RAISE;
1735
1736 END Get_Param_Cond;
1737
1738 --==========================================================================
1739 -- PROCEDURE NAME:
1740 -- Get_AR_Currency_Cond
1741 --
1742 -- DESCRIPTION:
1743 -- This procedure returns the WHERE clause
1744 -- about transaction Currency code
1745 --
1746 -- PARAMETERS:
1747 -- In: P_ORG_ID NUMBER
1748 -- p_transfer_id VARCHAR2
1749
1750 -- OUT: x_condition_sql Varchar2
1751 -- x_currency_code VARCHAR2
1752
1753 -- DESIGN REFERENCES:
1754 -- GTA-TRANSFER-PROGRAM-TD.doc
1755 --
1756 -- CHANGE HISTORY:
1757 -- 17-AUG-2005: Jim Zheng Created.
1758 -- 24-Dec-2008 Yao Zhang Changed for bug 7667709
1759 --===========================================================================
1760 PROCEDURE Get_AR_Currency_Cond(p_ORG_ID IN NUMBER,
1761 p_transfer_id IN NUMBER,
1762 x_condition_sql OUT NOCOPY VARCHAR2,
1763 x_currency_code OUT NOCOPY VARCHAR2) IS
1764 l_procedure_name VARCHAR2(50) := 'Get_AR_Currency_Cond';
1765 l_error_string VARCHAR2(500);
1766
1767 l_parameter_token VARCHAR2(10) := ':currency';
1768 l_ar_currency_code ra_customer_trx_all.Invoice_Currency_Code%TYPE;
1769
1770 l_specific_currency_code jmf_gta_rule_headers_all.specific_currency_code%TYPE;
1771 l_gta_currency_code jmf_gta_system_parameters_all.gt_currency_code%TYPE;
1772
1773 -- if currency_option = A then the rule is transfer all AR trx
1774 -- if currency_option = G
1775 -- then the rule is transfer AR trx which currency code is same as gta
1776 -- if currency_option = O
1777 -- then the rule is transfer AR trx which currency code is not same as gta
1778 -- if currency_option = S
1779 -- then the rule is transfer AR trx which currency code is specific.
1780 l_gta_currency_option jmf_gta_rule_headers_all.currency_option%TYPE;
1781 BEGIN
1785 G_MODULE_PREFIX || l_procedure_name,
1782
1783 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1784 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1786 'Begin Procedure. ');
1787 END IF;
1788
1789 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1790 log(FND_LOG.LEVEL_PROCEDURE,
1791 G_MODULE_PREFIX || l_procedure_name,
1792 'Begin Get_AR_Currency_Cond......');
1793 log(FND_LOG.LEVEL_PROCEDURE,
1794 G_MODULE_PREFIX || l_procedure_name,
1795 'p_org_id:' || p_org_id || ' ' || 'p_transfer_id:' ||
1796 p_transfer_id);
1797 END IF;
1798
1799 --select currency option and specific currency code.
1800 BEGIN
1801 SELECT rule.currency_option, rule.specific_currency_code
1802 INTO l_gta_currency_option, l_specific_currency_code
1803 FROM jmf_gta_rule_headers_all rule
1804 WHERE rule.rule_header_id = p_transfer_id;
1805 EXCEPTION
1806 -- no data found , raise a data error
1807 WHEN no_data_found THEN
1808 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_ERROR');
1809 l_error_string := fnd_message.get();
1810
1811 -- output error
1812 fnd_file.put_line(fnd_file.output,
1813 '<?xml version="1.0" encoding="UTF-8" ?>
1814 <TransferReport>
1815 <ReportFailed>Y</ReportFailed>
1816 <ReportFailedMsg>' ||
1817 l_error_string ||
1818 '</ReportFailedMsg>
1819 </TransferReport>');--Modified by Yao Zhang for bug 7667709
1820
1821 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1822 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
1823 G_MODULE_PREFIX || l_procedure_name,
1824 'no data found when select sales_list_flag.');
1825 END IF;
1826
1827 RAISE;
1828 END;
1829
1830 -- get gta_currency code
1831 BEGIN
1832 SELECT op.gt_currency_code
1833 INTO l_gta_currency_code
1834 FROM jmf_gta_system_parameters_all op
1835 WHERE op.org_id = p_ORG_ID;
1836 EXCEPTION
1837 -- no data found , raise a data error
1838 WHEN no_data_found THEN
1839 fnd_message.SET_NAME('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
1840 fnd_message.set_token('Tax_Regis_Number', ' ');
1841 l_error_string := fnd_message.get();
1842
1843 -- output error
1844 fnd_file.put_line(fnd_file.output,
1845 '<?xml version="1.0" encoding="UTF-8" ?>
1846 <TransferReport>
1847 <ReportFailed>Y</ReportFailed>
1848 <ReportFailedMsg>' ||
1849 l_error_string ||
1850 '</ReportFailedMsg>
1851 </TransferReport>');--Modified By Yao Zhang for bug 7667709
1852
1853 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1854 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
1855 G_MODULE_PREFIX || l_procedure_name,
1856 'no data found when select sales_list_flag.');
1857 END IF;
1858
1859 RAISE;
1860 END;
1861
1862 IF l_gta_currency_option IS NOT NULL THEN
1863 IF l_gta_currency_option = 'A' THEN
1864 x_condition_sql := NULL;
1865 ELSIF l_gta_currency_option = 'G' THEN
1866 x_condition_sql := ' AND h.Invoice_Currency_Code = ' ||
1867 l_parameter_token;
1868 x_currency_code := l_gta_currency_code;
1869 ELSIF l_gta_currency_option = 'O' THEN
1870 x_condition_sql := ' AND (NOT h.Invoice_Currency_Code = ' ||
1871 l_parameter_token || ')';
1872 x_currency_code := l_gta_currency_code;
1873 ELSIF l_gta_currency_option = 'S' THEN
1874 x_condition_sql := ' AND h.Invoice_Currency_Code = ' ||
1875 l_parameter_token;
1876 x_currency_code := l_specific_currency_code;
1877 END IF;
1878 END IF;
1879
1880 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1881 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
1882 G_MODULE_PREFIX || l_procedure_name,
1883 'END procedure. ');
1884 END IF;
1885
1886 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1887 log(FND_LOG.LEVEL_PROCEDURE,
1888 G_MODULE_PREFIX || l_procedure_name,
1889 'End Get_AR_Currency_Cond......');
1890 END IF;
1891
1892 EXCEPTION
1893 WHEN OTHERS THEN
1894 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1895 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1896 G_MODULE_PREFIX || l_procedure_name ||
1897 '. OTHER_EXCEPTION ',
1898 'Unknown error' || SQLCODE || SQLERRM);
1899
1900 END IF;
1901 RAISE;
1902
1903 END Get_AR_Currency_Cond;
1904
1905 --==========================================================================
1909 -- DESCRIPTION:
1906 -- PROCEDURE NAME:
1907 -- Retrieve_AR_TRXs
1908 --
1910 -- This procedure retrieve Receivable VAT transaction
1911 --
1912 -- PARAMETERS:
1913 -- In: P_ORG_ID NUMBER
1914 -- p_transfer_id VARCHAR2
1915 -- P_trxtype_para JMF_GTA_TRX_UTIL.Condition_para_tbl_type
1916 -- p_flex_para JMF_GTA_TRX_UTIL.Condition_para_tbl_type
1917 -- p_other_para JMF_GTA_TRX_UTIL.Condition_para_tbl_type
1918 -- p_currency_code VARCHAR2
1919 -- OUT: x_GTA_Trx_Tbl JMF_GTA_TRX_UTIL.TRX_TBL_TYPE
1920
1921 -- DESIGN REFERENCES:
1922 -- GTA-TRANSFER-PROGRAM-TD.doc
1923 --
1924 -- CHANGE HISTORY:
1925 -- 20-APR-2005: Jim Zheng Created.
1926 -- 28-Dec-2007: Subba Changed for R12.1.
1927 -- 24-Nov-2008 Modified by Brian for bug 7591365
1928 -- 25-Nov-2008 Modified by Brian for bug 7594218
1929 -- 10-Dec-2008 Modified by Yao Zhang for bug 7629877
1930 -- 24-Dec-2008 Modified by Yao Zhang for bug 7667709
1931 -- 26-Dec-2008 Modified by Yao Zhang fix bug 7670543
1932 -- 30-Dec-2008 Modified by Yao Zhang fix bug 7675165
1933 -- 05-Jan-2009 Modified by Yao Zhang fix bug 7684662
1934 -- 06-Jan-2009 Modified by Yao Zhang fix bug 7685610
1935 -- 20-Jan-2009: Yao Zhang Modified for bug 7721035
1936 -- 22/Jan/2009 Yao Zhang modified for bug 7829039
1937 --===========================================================================
1938 PROCEDURE Retrieve_AR_TRXs(p_org_id IN NUMBER,
1939 p_transfer_id IN NUMBER,
1940 P_query_SQL IN VARCHAR2,
1941 P_trxtype_query_para IN JMF_GTA_TRX_UTIL.Condition_para_tbl_type,
1942 p_flex_query_para IN JMF_GTA_TRX_UTIL.Condition_para_tbl_type,
1943 p_other_query_para IN JMF_GTA_TRX_UTIL.Condition_para_tbl_type,
1944 p_currency_code IN VARCHAR2,
1945 x_GTA_TRX_TBL OUT NOCOPY JMF_GTA_TRX_UTIL.TRX_TBL_TYPE) IS
1946
1947 l_normal_exception EXCEPTION;
1948 l_repeat_exception EXCEPTION;
1949 l_no_tax_line_exception EXCEPTION;
1950 l_procedure_name VARCHAR2(30) := 'Retrieve_AR_TRXs';
1951 l_cursor NUMBER;
1952 l_sql_exec_ret NUMBER;
1953 l_trx_header JMF_GTA_TRX_UTIL.TRX_header_rec_TYPE;
1954 l_trx_header_init JMF_GTA_TRX_UTIL.TRX_header_rec_TYPE;
1955 l_trx_line JMF_GTA_TRX_UTIL.TRX_line_rec_TYPE;
1956 l_trx_line_init JMF_GTA_TRX_UTIL.TRX_line_rec_TYPE;
1957 l_trx_lines JMF_GTA_TRX_UTIL.TRX_line_tbl_TYPE := JMF_GTA_TRX_UTIL.TRX_line_tbl_TYPE();
1958 l_trx_rec JMF_GTA_TRX_UTIL.TRX_REC_TYPE;
1959 l_trx_rec_init JMF_GTA_TRX_UTIL.TRX_REC_TYPE;
1960 l_log_str VARCHAR2(4000);
1961 l_error_string VARCHAR2(1000);
1962 l_error_flag NUMBER := 0;
1963
1964 --credit memo
1965 l_gt_invoice_number Jmf_Gta_Trx_Headers_All.gt_invoice_number%TYPE; --VARCHAR2(30);
1966 l_gt_invoice_class Jmf_Gta_Trx_Headers_All.GT_INVOICE_CLASS%TYPE; --VARCHAR2(10);
1967 l_origin_trx_id NUMBER(15);
1968 l_gta_invoice_count NUMBER(15);
1969 l_gt_invoice_count NUMBER(15); --added by subba for credit memo check for R12.1
1970 l_ar_inv_cnt NUMBER(15);
1971 l_cm_excep VARCHAR2(15);
1972 l_cm_excep_ar VARCHAR2(15);
1973 --add begin by Yao Zhang for bug 7629877
1974 l_ar_inv_excep boolean;
1975 l_gt_inv_excep boolean;
1976 l_cm_warn boolean;
1977 l_pre_cus_trxid number;
1978 l_ar_invoice_count number;
1979 --add end by Yao Zhang or bug 7629877
1980 l_max_amount NUMBER;
1981 l_max_num_of_line NUMBER;
1982 l_vat_tax_type jmf_gta_system_parameters_all.vat_tax_type_code%TYPE;
1983 l_trx_line_split_flag jmf_gta_system_parameters_all.trx_line_split_flag%TYPE;
1984 l_gt_currency_code jmf_gta_system_parameters_all.gt_currency_code%TYPE;
1985 l_item_name_source_flag jmf_gta_system_parameters_all.item_name_source_flag%TYPE;
1986 l_cross_reference_type jmf_gta_system_parameters_all.cross_reference_type%TYPE;
1987 l_master_item_default_flag jmf_gta_system_parameters_all.master_item_default_flag%TYPE;
1988 l_latest_ref_default_flag jmf_gta_system_parameters_all.latest_ref_default_flag%TYPE;
1989 l_ra_line_context_code jmf_gta_system_parameters_all.ra_line_context_code%TYPE;
1990 l_ra_model_attribute_column jmf_gta_system_parameters_all.ra_model_attribute_column%TYPE;
1991 l_ra_tax_attribute_column jmf_gta_system_parameters_all.ra_tax_attribute_column%TYPE;
1992 l_inv_item_context_code jmf_gta_system_parameters_all.inv_item_context_code%TYPE;
1993 l_inv_model_attribute_column jmf_gta_system_parameters_all.inv_model_attribute_column%TYPE;
1994 l_inv_tax_attribute_column jmf_gta_system_parameters_all.Inv_Tax_Attribute_Column%TYPE;
1995 l_currency_code jmf_gta_system_parameters_all.gt_currency_code%TYPE;
1996 l_cross_rows NUMBER;
1997 --29-JUN-2006 Updated by shujuan
1998 l_cross_reference MTL_CROSS_REFERENCES_B.cross_reference%TYPE;
1999 l_sales_list_flag VARCHAR2(1);
2003
2000 -- 20-JUL-2006 Shujuan Added length from 60 to 240 for bug 5400805
2001 l_inventory_item_name mtl_system_items_b.description%TYPE;
2002 l_inventory_attribute_category mtl_system_items_b.attribute_category%TYPE;
2004 -- 29-JUN-2006 Added a parameter to store the unit price of tax currency
2005 -- by Shujuan for bug 5168900
2006 p_tax_curr_unit_price NUMBER;
2007
2008 -- a prefix of parameter in bind_variable command of dbms sql.
2009 l_arg VARCHAR2(10);
2010
2011 -- a flag which adjust the trx record is new,
2012 -- if dul_flag>1, the trx record is old ,else is new
2013 l_dul_flag NUMBER := 0;
2014
2015 l_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
2016 l_trx_number ra_customer_trx_all.trx_number%TYPE;
2017 l_gl_date Ra_Cust_Trx_Line_Gl_Dist_All.Gl_Date%TYPE;
2018 l_set_of_books_id ra_customer_trx_all.Set_Of_Books_Id%TYPE;
2019 l_bill_to_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
2020 l_trx_date ra_customer_trx_all.trx_date%TYPE;
2021 l_invoice_Currency_code ra_customer_trx_all.invoice_currency_code%TYPE;
2022 l_exchange_rate_type ra_customer_trx_all.exchange_rate_type%TYPE;
2023 l_exchange_rate ra_customer_trx_all.exchange_rate%TYPE;
2024 l_ctt_class ra_cust_trx_types_all.type%TYPE;
2025 l_period_name GL_PERIODS.Period_Name%TYPE;
2026 l_ct_reference ra_customer_trx_all.ct_reference%TYPE;
2027
2028 l_invoice_type jmf_gta_tax_limits_all.invoice_type%TYPE;
2029
2030 l_raa_bill_to_concat_address VARCHAR2(500);
2031 l_cust_addr_excep VARCHAR2(20); --added by subba for R12.1
2032 l_cust_phone_exp VARCHAR2(20);
2033 l_phone_number Hz_Contact_Points.Phone_Number%TYPE;
2034 l_apb_customer_bank_name CE_Bank_Branches_V.Bank_Name%TYPE;
2035 l_apb_bank_branch_name CE_Bank_Branches_V.Bank_Branch_Name%TYPE;
2036 l_apba_bank_account_num CE_Bank_Accounts.Bank_Account_num%TYPE;
2037 l_apba_bank_account_name CE_Bank_Accounts.Bank_Account_Name%TYPE;
2038
2039 l_rac_bill_to_customer_name Hz_Parties.PARTY_NAME%TYPE;
2040 l_rac_bill_to_customer_num Hz_Cust_Accounts.ACCOUNT_NUMBER%TYPE;
2041 l_bill_to_taxpayer_id Hz_Parties.JGZZ_FISCAL_CODE%TYPE;
2042
2043 l_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE;
2044 l_description ra_customer_trx_lines_all.description%TYPE;
2045 l_inventory_item_id ra_customer_trx_lines_all.inventory_item_id%TYPE;
2046 l_interface_line_context ra_customer_trx_lines_all.interface_line_context%TYPE;
2047 l_uom_code ra_customer_trx_lines_all.uom_code%TYPE;
2048 l_unit_of_measure mtl_units_of_measure_vl.unit_of_measure%TYPE;
2049 l_quantity_credited ra_customer_trx_lines_all.quantity_credited%TYPE;
2050
2051 --12/06/2006 Added a parameter to instore the transaction line number
2052 --by Shujuan Yan for bug 5230712
2053 l_customer_trx_line_number ra_customer_trx_lines_all.line_number%TYPE;
2054
2055 l_amount ra_customer_trx_lines_all.taxable_amount%TYPE;
2056
2057 -- for get bank info by customer id
2058 l_site_use_id hz_cust_site_uses.SITE_USE_ID%TYPE;
2059 l_cust_acct_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
2060
2061 l_cm_export_iv VARCHAR2(500);
2062 l_cm_export_nr VARCHAR2(500);
2063 l_index NUMBER; -- index of the nested table
2064 l_cust_trx_id_count NUMBER; -- the trx count of
2065 l_trx_id_cancel_count NUMBER; -- the trx count of Statuts is 'CANCEL'
2066 l_trx_line_num NUMBER;
2067 l_conc_succ BOOLEAN; -- the status of concurrent
2068 l_warning_count NUMBER;
2069
2070 -- for split test
2071 l_quantity_invoiced NUMBER;
2072 l_unit_selling_price NUMBER;
2073
2074 l_tax_line_count NUMBER;
2075 l_legal_entity_id ra_customer_trx_all.legal_entity_id%TYPE;
2076
2077 -- the data get from ebtax
2078 l_tax_amount_func_curr zx_lines.tax_amt_funcl_curr%TYPE;
2079 l_taxable_amount_func_curr zx_lines.taxable_amt_funcl_curr%TYPE;
2080 l_line_quantity zx_lines.trx_line_quantity%TYPE;
2081 l_tax_rate zx_lines.tax_rate%TYPE;
2082 l_unit_price zx_lines.unit_price%TYPE;
2083 l_fp_registration_number zx_lines.tax_registration_number%TYPE;
2084 l_tp_registration_number zx_lines.tax_registration_number%TYPE;
2085
2086 -- for get the error message from get_info_from_ebtax
2087 l_status NUMBER;
2088 l_proce_error_buffer VARCHAR2(180);
2089
2090 -- for check the third party tax registration number
2091 l_tp_regi_number_first zx_lines.tax_registration_number%TYPE;
2092 l_trx_line_index NUMBER;
2093 l_tp_regi_number zx_lines.tax_registration_number%TYPE;
2094 l_trx_typ ra_cust_trx_types_all.name%TYPE; --added by subba
2095 l_cm_desc1 varchar2(50);--added by Yao Zhang for bug 7685610
2096 l_cm_desc2 varchar2(50);--added by Yao Zhang for bug 7685610
2097 l_master_org HR_ORGANIZATION_UNITS.organization_id%TYPE; --yao zhang add for bug 7721035
2098
2099 CURSOR l_ra_lines(l_header_id IN NUMBER) IS
2100 SELECT l.customer_trx_line_id,
2101 l.description,
2102 l.inventory_item_id,
2103 l.interface_line_context,
2104 l.uom_code,
2105 l.revenue_amount,
2109 l.line_number --12/06/2006 line number,Added by Shujuan bug 5230712
2106 l.unit_selling_price,
2107 l.quantity_invoiced,
2108 l.quantity_credited,
2110 FROM ra_customer_trx_lines_all l
2111 WHERE l.line_type = 'LINE'
2112 AND l.customer_trx_id = l_header_id;
2113
2114 BEGIN
2115
2116 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2117 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
2118 G_MODULE_PREFIX || l_procedure_name,
2119 'Begin Procedure. ');
2120 END IF;
2121
2122 -- init x_GTA_TRX_TBL
2123 x_GTA_TRX_TBL := JMF_GTA_TRX_UTIL.TRX_TBL_TYPE();
2124
2125 l_log_str := p_query_sql;
2126 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2127 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2128 G_MODULE_PREFIX || l_procedure_name || '.DYNAMIC SQL ',
2129 l_log_str);
2130 END IF;
2131
2132 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2133 log(FND_LOG.LEVEL_PROCEDURE,
2134 G_MODULE_PREFIX || l_procedure_name,
2135 'Begin Retrieve_AR_TRXs......');
2136 END IF;
2137
2138 -- begin select parameters
2139 --select parameters from JMF_GTA_SYSTEM_PARAMETERS_ALL dependence org_id
2140 BEGIN
2141 SELECT vat_tax_type_code,
2142 trx_line_split_flag,
2143 gt_currency_code,
2144 item_name_source_flag,
2145 cross_reference_type,
2146 master_item_default_flag,
2147 latest_ref_default_flag,
2148 ra_line_context_code,
2149 ra_model_attribute_column,
2150 ra_tax_attribute_column,
2151 inv_item_context_code,
2152 inv_model_attribute_column,
2153 inv_tax_attribute_column,
2154 gt_currency_code
2155 INTO l_vat_tax_type,
2156 l_trx_line_split_flag,
2157 l_gt_currency_code,
2158 l_item_name_source_flag,
2159 l_cross_reference_type,
2160 l_master_item_default_flag,
2161 l_latest_ref_default_flag,
2162 l_ra_line_context_code,
2163 l_ra_model_attribute_column,
2164 l_ra_tax_attribute_column,
2165 l_inv_item_context_code,
2166 l_inv_model_attribute_column,
2167 l_inv_tax_attribute_column,
2168 l_currency_code
2169 FROM jmf_gta_system_parameters_all
2170 WHERE org_id = p_org_id;
2171
2172 EXCEPTION
2173 WHEN no_data_found THEN
2174 --report JMF_GTA_MISSING_ERROR
2175 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
2176 fnd_message.set_token('Tax_Regis_Number', ' ');
2177 l_error_string := fnd_message.get();
2178 -- output this error
2179 fnd_file.put_line(fnd_file.output,
2180 '<?xml version="1.0" encoding="UTF-8"?>
2181 <TransferReport>
2182 <ReportFailed>Y</ReportFailed>
2183 <ReportFailedMsg>' ||
2184 l_error_string ||
2185 '</ReportFailedMsg>
2186 </TransferReport>');--Modified By Yao Zhang for bug 7667709
2187
2188 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2189 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
2190 G_MODULE_PREFIX || l_procedure_name,
2191 l_error_string);
2192 END IF;
2193
2194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2195 RETURN;
2196 END;
2197
2198 -- select sales_list_flag from rule_table
2199 BEGIN
2200 SELECT sales_list_flag
2201 INTO l_sales_list_flag
2202 FROM jmf_gta_rule_headers_all
2203 WHERE jmf_gta_rule_headers_all.rule_header_id = p_transfer_id;
2204 EXCEPTION
2205 -- no data found , raise a data error
2206 WHEN no_data_found THEN
2207 fnd_message.SET_NAME('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
2208 fnd_message.set_token('Tax_Regis_Number', ' ');
2209 l_error_string := fnd_message.get();
2210 -- output error
2211 fnd_file.put_line(fnd_file.output,
2212 '<?xml version="1.0" encoding="UTF-8"?>
2213 <TransferReport>
2214 <ReportFailed>Y</ReportFailed>
2215 <ReportFailedMsg>' ||
2216 l_error_string ||
2217 '</ReportFailedMsg>
2218 </TransferReport>');--Modified By Yao Zhang for bug 7667709
2219
2220 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2221 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
2222 G_MODULE_PREFIX || l_procedure_name,
2223 'no data found when select sales_list_flag.');
2224 END IF;
2225 RAISE;
2226 RETURN;
2227 END;
2228 --end select parameters
2229
2230 --open cursor
2231 l_cursor := dbms_sql.open_cursor;
2232
2233 --parse the sql string;
2234 dbms_sql.parse(l_cursor, p_query_sql, dbms_sql.v7);
2238
2235
2236 -- bind variable;
2237 dbms_sql.bind_variable(l_cursor, 'p_org_id', p_org_id);
2239 -- bind trxtype_query_para
2240 l_index := p_trxtype_query_para.FIRST;
2241
2242 WHILE l_index IS NOT NULL LOOP
2243 l_arg := 'trxtype' || l_index;
2244 dbms_sql.bind_variable(l_cursor,
2245 l_arg,
2246 p_trxtype_query_para(l_index));
2247 l_index := P_trxtype_query_para.NEXT(l_index);
2248 END LOOP;
2249
2250 -- bind flex_query_para
2251 l_index := p_flex_query_para.FIRST;
2252 WHILE l_index IS NOT NULL LOOP
2253 l_arg := 'flex' || l_index;
2254 dbms_sql.bind_variable(l_cursor, l_arg, p_flex_query_para(l_index));
2255 l_index := p_flex_query_para.NEXT(l_index);
2256 END LOOP;
2257
2258 -- bind other_query_para
2259 l_index := p_other_query_para.FIRST;
2260 WHILE l_index IS NOT NULL LOOP
2261 l_arg := 'para' || l_index;
2262 dbms_sql.bind_variable(l_cursor, l_arg, p_other_query_para(l_index));
2263 l_index := p_other_query_para.NEXT(l_index);
2264 END LOOP;
2265
2266 -- bind the condition of currency code.
2267 IF p_currency_code IS NOT NULL THEN
2268 dbms_sql.bind_variable(l_cursor, 'currency', p_currency_code);
2269 END IF;
2270
2271 --define column
2272 dbms_sql.define_column(l_cursor, 1, l_customer_trx_id);
2273
2274 --EXECUTE!
2275 l_sql_exec_ret := dbms_sql.EXECUTE(l_cursor);
2276
2277 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2278 log(FND_LOG.LEVEL_PROCEDURE,
2279 G_MODULE_PREFIX || l_procedure_name,
2280 'Begin cursor loop......');
2281 END IF;
2282
2283 LOOP
2284 BEGIN
2285 -- for l_normal_exception
2286
2287 l_sql_exec_ret := dbms_sql.fetch_rows(l_cursor);
2288
2289 --initializing all the varibales required for exception
2290 l_ar_inv_excep := null;
2291 l_cm_excep := null;
2292 l_cust_addr_excep := null;
2293 l_cust_phone_exp := null;
2294 l_ar_inv_cnt := 0;
2295 l_gta_invoice_count := 0;
2296 l_gt_invoice_count := 0;
2297
2298 IF l_sql_exec_ret = 0 THEN
2299 EXIT;
2300 END IF;
2301
2302 -- init trx header and trx lines
2303 l_trx_header := l_trx_header_init;
2304 l_trx_lines := JMF_GTA_TRX_UTIL.TRX_line_tbl_TYPE();
2305 l_error_flag := 0;
2306
2307 -- get customer_trx_id of the AR_trx_header
2308 dbms_sql.column_value(l_cursor, 1, l_customer_trx_id);
2309
2310 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2311 log(FND_LOG.LEVEL_PROCEDURE,
2312 G_MODULE_PREFIX || l_procedure_name,
2313 '****************************************');
2314 log(FND_LOG.LEVEL_PROCEDURE,
2315 G_MODULE_PREFIX || l_procedure_name,
2316 'l_customer_trx_id : ' || l_customer_trx_id);
2317 log(FND_LOG.LEVEL_PROCEDURE,
2318 G_MODULE_PREFIX || l_procedure_name,
2319 '****************************************');
2320 END IF;
2321
2322 -- select this customer_trx_id in GTA, if there is existing of this id,
2323 -- Select the status of it, if the status of 'CANCEL',
2324 -- and Last_update_flag is 'Y', and version > 1; then delete the record
2325 -- and retransfer it , else if the cust_trx_id exist, don't transfer it,
2326 -- else if the cust_trx_id is not exist, transfer it.
2327 SELECT COUNT(*)
2328 INTO l_cust_trx_id_count
2329 FROM jmf_gta_trx_headers_all h
2330 WHERE h.ra_trx_id = l_customer_trx_id;
2331
2332 IF l_cust_trx_id_count > 0 THEN
2333 SELECT COUNT(*)
2334 INTO l_trx_id_cancel_count
2335 FROM jmf_gta_trx_headers_all h
2336 WHERE h.ra_trx_id = l_customer_trx_id
2337 AND h.latest_version_flag = 'Y'
2338 AND h.status = 'CANCEL'
2339 AND h.version > 1;
2340
2341 IF l_trx_id_cancel_count > 0 THEN
2342 DELETE jmf_gta_trx_headers_all h
2343 WHERE h.ra_trx_id = l_customer_trx_id
2344 AND h.latest_version_flag = 'Y'
2345 AND h.status = 'CANCEL'
2346 AND h.version > 1;
2347 ELSE
2348 RAISE l_repeat_exception;
2349 END IF;
2350
2351 END IF;
2352
2353 SELECT COUNT(*)
2354 INTO l_trx_line_num
2355 FROM ra_customer_trx_lines_all l
2356 WHERE l.customer_trx_id = l_customer_trx_id;
2357
2358 IF l_trx_line_num = 0 THEN
2359 RAISE l_repeat_exception;
2360 END IF;
2361
2362 -- begin select other information by customer_trx_id
2363
2364 -- select other columns and address, phone, etc.
2365 -- select header info
2366 BEGIN
2367 SELECT h.trx_number,
2368 gd.gl_date,
2369 h.set_of_books_id,
2370 h.bill_to_customer_id,
2371 h.trx_date,
2375 h.legal_entity_id,
2372 h.Invoice_Currency_Code,
2373 h.exchange_rate_type,
2374 h.exchange_rate,
2376 h.ct_reference,
2377 ctt.TYPE,
2378 gp.period_name
2379 INTO l_trx_number,
2380 l_gl_date,
2381 l_set_of_books_id,
2382 l_bill_to_customer_id,
2383 l_trx_date,
2384 l_invoice_Currency_code,
2385 l_exchange_rate_type,
2386 l_exchange_rate,
2387 l_legal_entity_id,
2388 l_ct_reference,
2389 l_ctt_class,
2390 l_period_name
2391 FROM ra_customer_trx_all h,
2392 ra_cust_trx_types_all ctt,
2393 ra_batches_all b,
2394 Ra_Cust_Trx_Line_Gl_Dist_All gd,
2395 Hz_Parties RAC_BILL_PARTY,
2396 Hz_Cust_Accounts RAC_BILL,
2397 GL_PERIODS GP -- period
2398 WHERE h.complete_flag = 'Y'
2399 AND h.CUST_TRX_TYPE_ID = ctt.CUST_TRX_TYPE_ID(+)
2400 AND ctt.TYPE IN ('INV', 'CM', 'DM')
2401 AND h.batch_id = b.batch_id(+)
2402 AND GD.CUSTOMER_TRX_ID = h.CUSTOMER_TRX_ID
2403 AND GD.ACCOUNT_CLASS = 'REC'
2404 AND GD.LATEST_REC_FLAG = 'Y'
2405 AND h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
2406 AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id
2407 AND h.Org_Id = gd.Org_Id
2408 AND h.Org_Id = ctt.Org_Id
2409 AND h.Org_Id = p_org_id
2410 AND GP.PERIOD_SET_NAME =
2411 (SELECT period_set_name
2412 FROM Gl_Sets_Of_Books
2413 WHERE set_of_books_id = h.set_of_books_id)
2414 AND gp.period_type =
2415 (SELECT accounted_period_type
2416 FROM Gl_Sets_Of_Books
2417 WHERE set_of_books_id = h.set_of_books_id)
2418 AND gp.adjustment_period_flag = 'N'
2419 AND gp.start_date <= gd.GL_DATE
2420 AND gp.end_date >= gd.gl_date
2421 AND h.customer_trx_id = l_customer_trx_id;
2422
2423 EXCEPTION
2424 WHEN no_data_found THEN
2425 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2426 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
2427 G_MODULE_PREFIX || l_procedure_name,
2428 'no date found when select header info');
2429 END IF;
2430 RAISE;
2431 END;
2432
2433 -- select l_raa_bill_to_concat_address
2434 BEGIN
2435 SELECT DECODE(RAA_BILL.CUST_ACCT_SITE_ID,
2436 NULL,
2437 NULL,
2438 ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS(RAA_BILL_LOC.ADDRESS_STYLE,
2439 RAA_BILL_LOC.ADDRESS1,
2440 RAA_BILL_LOC.ADDRESS2,
2441 RAA_BILL_LOC.ADDRESS3,
2442 RAA_BILL_LOC.ADDRESS4,
2443 RAA_BILL_LOC.CITY,
2444 RAA_BILL_LOC.COUNTY,
2445 RAA_BILL_LOC.STATE,
2446 RAA_BILL_LOC.PROVINCE,
2447 RAA_BILL_LOC.POSTAL_CODE,
2448 FT_BILL.TERRITORY_SHORT_NAME))
2449 INTO l_raa_bill_to_concat_address
2450 FROM HZ_CUST_SITE_USES_ALL SU_BILL,
2451 Hz_Cust_Acct_Sites_All RAA_BILL,
2452 HZ_PARTY_SITES RAA_BILL_PS,
2453 Hz_Locations RAA_BILL_LOC,
2454 FND_TERRITORIES_VL FT_BILL,
2455 ra_customer_trx_all h
2456 WHERE h.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID
2457 AND SU_BILL.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID
2458 AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID
2459 AND RAA_BILL_PS.LOCATION_ID = RAA_BILL_LOC.LOCATION_ID
2460 AND RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+)
2461 AND h.customer_trx_id = l_customer_trx_id;
2462 EXCEPTION
2463 WHEN no_data_found THEN
2464 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2465 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
2466 G_MODULE_PREFIX || l_procedure_name,
2467 'no date found when select l_raa_bill_to_concat_address');
2468 END IF;
2469 --modified by subba for R12.1
2470 -- RAISE;
2471 l_cust_addr_excep := 'true';
2472 END;
2473
2474 -- 29/06/2006 Deleted by Shujuan Yan for bug 5263131,
2475 -- Since if tansaction type is credit memo, the paying customer is null,
2476 -- can not get bank information.
2477 /*
2478 -- call util procedure to get bank info
2479 jmf_gta_trx_util.get_bank_info( p_customer_trx_id => l_customer_trx_id
2480 ,p_org_id => p_org_id
2484 ,x_bank_account_num => l_apba_bank_account_num
2481 ,x_bank_name => l_apb_customer_bank_name
2482 ,x_bank_branch_name => l_apb_bank_branch_name
2483 ,x_bank_account_name => l_apba_bank_account_name
2485 );
2486 */
2487 --29/06/2006 end for bug 5263131
2488
2489 -- select phone number
2490 BEGIN
2491 SELECT p.phone_number
2492 INTO l_phone_number
2493 FROM Hz_Contact_Points p,
2494 Hz_Cust_Accounts RAC_BILL,
2495 Hz_Parties RAC_BILL_PARTY,
2496 ra_customer_trx_all h
2497 WHERE -- h.bill_to_customer_id can find by customer trx id
2498 h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
2499 AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id
2500 AND RAC_BILL_PARTY.Party_Id = p.owner_table_id(+)
2501 AND p.owner_table_name(+) = 'HZ_PARTIES'
2502 AND p.CONTACT_POINT_TYPE(+) = 'PHONE'
2503 AND p.primary_flag(+) = 'Y'
2504 AND h.customer_trx_id = l_customer_trx_id;
2505
2506 EXCEPTION
2507 WHEN no_data_found THEN
2508 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2509 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
2510 G_MODULE_PREFIX || l_procedure_name,
2511 'no date found when select phone number');
2512 END IF;
2513
2514 --modified by subba for R12.1
2515 --RAISE;
2516 l_cust_phone_exp := 'true';
2517 END;
2518
2519 BEGIN
2520 --select rac information
2521 SELECT RAC_BILL_PARTY.PARTY_NAME,
2522 RAC_BILL.ACCOUNT_NUMBER,
2523 RAC_BILL_PARTY.JGZZ_FISCAL_CODE
2524 INTO l_rac_bill_to_customer_name,
2525 l_rac_bill_to_customer_num,
2526 l_bill_to_taxpayer_id
2527 FROM ra_customer_trx_all h,
2528 Hz_Cust_Accounts rac_bill,
2529 Hz_Parties rac_bill_party
2530 WHERE h.customer_trx_id = l_customer_trx_id
2531 AND h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
2532 AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id;
2533
2534 EXCEPTION
2535 WHEN no_data_found THEN
2536 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2537 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
2538 G_MODULE_PREFIX || l_procedure_name,
2539 'no data found when select rac information');
2540 END IF;
2541 RAISE;
2542 END;
2543 -- end select other information by customer_trx_id
2544
2545 --28/12/2007 changed the code by subba for R12.1, becoz we need to remove validation on credit memo
2546
2547 -- 11/06/2006 Changed message code from JMF_GTA_CRMEMO_MISSING_ARINV
2548 -- to JMF_GTA_CRMEMO_MISSING_GTINV by Shujuan Yan for bug 5263308
2549 -- begin creidt memo exception validate
2550
2551 -- Modified by brian for bug 7591365
2552 /*IF l_ctt_class = 'CM' THEN
2553
2554 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2555 THEN
2556 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX
2557 || l_procedure_name,
2558 '****************************************');
2559 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX
2560 || l_procedure_name,
2561 'is CM');
2562 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX
2563 || l_procedure_name,
2564 '****************************************');
2565 END IF;
2566 --checking whether CM is associated with AR invoice or not
2567
2568 SELECT count(*)
2569 INTO l_ar_inv_cnt
2570 FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
2571 , AR_PAYMENT_SCHEDULES_ALL PS_INV
2572 WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
2573 AND app.ORG_ID = p_org_id
2574 AND app.CUSTOMER_TRX_ID = l_customer_trx_id
2575 AND app.display = 'Y';
2576
2577 IF l_ar_inv_cnt = 0 THEN --no AR inv for CM
2578
2579 l_ar_inv_excep := 'true';
2580
2581
2582 ELSE
2583
2584 FOR l_ar_cur IN (SELECT PS_INV.CUSTOMER_TRX_ID trx_id
2585 FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
2586 , AR_PAYMENT_SCHEDULES_ALL PS_INV
2587 WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
2588 AND app.ORG_ID = p_org_id
2589 AND app.CUSTOMER_TRX_ID = l_customer_trx_id
2590 AND app.display = 'Y')
2591 LOOP
2592
2593 l_origin_trx_id := l_ar_cur.trx_id; --taking the AR trx id for which credit memo assigned for getting Bank info
2594
2598 count(*)
2595 --Try to select VAT invoice details for all AR invoices associated with credit memo.
2596
2597 SELECT
2599 INTO
2600 l_gta_invoice_count
2601
2602 FROM
2603 Jmf_Gta_Trx_Headers_All
2604 WHERE ra_trx_id=l_ar_cur.trx_id
2605 AND SOURCE = 'AR';
2606
2607 IF l_gta_invoice_count > 0 THEN --credit memo associated to AR invoice which is transferred to GTA
2608
2609 --checking AR invoice processed in Workbench or not
2610
2611 SELECT
2612 count(*)
2613 INTO
2614 l_gt_invoice_count
2615
2616 FROM
2617 Jmf_Gta_Trx_Headers_All
2618 WHERE ra_trx_id=l_ar_cur.trx_id
2619 AND SOURCE = 'GT';
2620
2621 IF l_gt_invoice_count = 0 THEN --AR trx is in workbench but VAT not generated.
2622
2623 l_cm_excep := 'true';
2624 EXIT;
2625
2626 END IF;--/*IF l_gt_invoice_number = 0 THEN
2627
2628 END IF; --/* IF l_gta_invoice_count > 0 THEN
2629
2630 END LOOP; --/* cusrsor end
2631
2632 END IF; --/*IF l_ar_inv_cnt = 0 THEN
2633
2634 END IF; --/*end if l_ctt_class= 'CM'
2635 */
2636
2637 -- Above commented by Brian for bug 7591365
2638
2639 -- end validate CM trx
2640 -- 11/06/2006 Ended for bug 5263308
2641
2642 --end relaxing validation on CM for R12.1
2643
2644 -- begin insert value into l_trx_header
2645 SELECT jmf_gta_trx_headers_all_s.NEXTVAL
2646 INTO l_trx_header.gta_trx_header_id
2647 FROM dual;
2648
2649 --29/06/2006 Added by Shujuan Yan for bug 5263131
2650 IF l_origin_trx_id IS NOT NULL THEN
2651 -- call util procedure to get bank info for 'CM',
2652 -- since paying customer is null, have to use the original invoice
2653 jmf_gta_trx_util.get_bank_info(p_customer_trx_id => l_origin_trx_id,
2654 p_org_id => p_org_id,
2655 x_bank_name => l_apb_customer_bank_name,
2656 x_bank_branch_name => l_apb_bank_branch_name,
2657 x_bank_account_name => l_apba_bank_account_name,
2658 x_bank_account_num => l_apba_bank_account_num);
2659 ELSE
2660
2661 jmf_gta_trx_util.get_bank_info(p_customer_trx_id => l_customer_trx_id,
2662 p_org_id => p_org_id,
2663 x_bank_name => l_apb_customer_bank_name,
2664 x_bank_branch_name => l_apb_bank_branch_name,
2665 x_bank_account_name => l_apba_bank_account_name,
2666 x_bank_account_num => l_apba_bank_account_num);
2667 END IF; /*l_origin_trx_id IS NOT NULL THEN*/
2668
2669 l_trx_header.ra_gl_date := l_gl_date;
2670 l_trx_header.ra_gl_period := l_period_name;
2671 l_trx_header.set_of_books_id := l_set_of_books_id;
2672 l_trx_header.bill_to_customer_id := l_bill_to_customer_id;
2673 l_trx_header.bill_to_customer_number := l_rac_bill_to_customer_num;
2674 l_trx_header.bill_to_customer_name := l_rac_bill_to_customer_name;
2675 l_trx_header.SOURCE := 'AR';
2676 l_trx_header.org_id := p_org_id;
2677 l_trx_header.rule_header_id := p_transfer_id;
2678
2679 l_trx_header.group_number := 1;
2680 l_trx_header.version := 1;
2681 l_trx_header.latest_version_flag := 'Y';
2682 l_trx_header.transaction_date := l_trx_date;
2683 l_trx_header.ra_trx_id := l_customer_trx_id;
2684 l_trx_header.ra_trx_number := l_trx_number;
2685
2686 --09/06/2006 Updated by Shujuan Yan for bug 5255993
2687 --Added transaction number to the header description
2688 --modified by subba for R12.1
2689
2690 --IF l_ctt_class <> 'CM'
2691 --THEN
2692 l_trx_header.description := l_ct_reference || l_trx_number;
2693 --END IF;--l_ctt_class <> 'CM'
2694
2695 l_trx_header.customer_address := l_raa_bill_to_concat_address;
2696 l_trx_header.customer_phone := l_phone_number;
2697 l_trx_header.customer_address_phone := l_raa_bill_to_concat_address || ' ' ||
2698 l_phone_number; -- a + b
2699 l_trx_header.bank_account_name := l_apb_customer_bank_name || ' ' ||
2700 l_apb_bank_branch_name;
2701 l_trx_header.bank_account_number := l_apba_bank_account_num;
2702 l_trx_header.bank_account_name_number := l_apb_customer_bank_name || ' ' ||
2703 l_apb_bank_branch_name || ' ' ||
2704 l_apba_bank_account_num;
2705 l_trx_header.legal_entity_id := l_legal_entity_id;
2706 l_trx_header.ra_currency_code := l_invoice_Currency_code;
2707 l_trx_header.conversion_date := l_trx_date;
2708 l_trx_header.status := 'DRAFT';
2712 l_trx_header.program_application_id := fnd_global.PROG_APPL_ID();
2709 l_trx_header.sales_list_flag := l_sales_list_flag;
2710 l_trx_header.cancel_flag := 'N';
2711 l_trx_header.request_id := fnd_global.CONC_REQUEST_ID();
2713 l_trx_header.program_id := fnd_global.CONC_PROGRAM_ID;
2714 l_trx_header.program_update_date := SYSDATE;
2715 l_trx_header.creation_date := SYSDATE;
2716 l_trx_header.created_by := fnd_global.LOGIN_ID();
2717 l_trx_header.last_update_date := SYSDATE;
2718 l_trx_header.last_updated_by := fnd_global.LOGIN_ID();
2719 l_trx_header.last_update_login := fnd_global.LOGIN_ID();
2720
2721 -- end insert data into trx_header
2722
2723 -- begin fetch lines, and insert all value into trx_line;
2724 -- init l_trx_lines
2725 l_trx_lines := JMF_GTA_TRX_UTIL.TRX_line_tbl_TYPE();
2726
2727 OPEN l_ra_lines(l_customer_trx_id);
2728 LOOP
2729
2730 -- init l_trx_line
2731 l_trx_line := l_trx_line_init;
2732
2733 FETCH l_ra_lines
2734 INTO l_customer_trx_line_id, l_description, l_inventory_item_id, l_interface_line_context, l_uom_code, l_amount, l_unit_selling_price, l_quantity_invoiced, l_quantity_credited, l_customer_trx_line_number;
2735
2736 EXIT WHEN l_ra_lines%NOTFOUND;
2737
2738 -- check the UOM and Quentity of The AR transaction Line,
2739 -- if one of it is null ,
2740 -- Skip this transaction. and show a message
2741
2742 -- Modified by Brian for bug 7594218
2743 --UOM is not mandatory for the transaction with class credit memo
2744 --*********************************************************************
2745 --commented by Yao Zhang begin for bug 7629877 for UOM can be null
2746 /*IF l_ctt_class <> 'CM'
2747 THEN
2748
2749 IF l_uom_code IS NULL
2750 OR (l_quantity_invoiced IS NULL AND l_quantity_credited IS NULL)
2751 THEN
2752
2753 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_FIELD');
2754 --fnd_message.set_token('NUM', l_customer_trx_line_id);
2755 l_error_string := fnd_message.GET();
2756
2757 -- begin log
2758 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2759 THEN
2760 log(FND_LOG.LEVEL_PROCEDURE,
2761 G_MODULE_PREFIX || l_procedure_name,
2762 'JMF_GTA_MISSING_FIELD');
2763 END IF;
2764 -- end log
2765
2766 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2767 THEN
2768 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
2769 G_MODULE_PREFIX || l_procedure_name,
2770 l_error_string);
2771 END IF;
2772 CLOSE l_ra_lines;
2773 RAISE l_normal_exception;
2774 END IF;
2775 END IF; --l_ctt_class <> 'CM'*/
2776 --commented by Yao Zhang end for bug 7629877
2777 --yao zhang fix bug 7829039 add the following code
2778 begin
2779 select parameter_value
2780 into l_master_org
2781 from OE_SYS_PARAMETERS_ALL
2782 where org_id = p_org_id
2783 and parameter_code = 'MASTER_ORGANIZATION_ID';
2784 EXCEPTION
2785 WHEN no_data_found THEN
2786 l_master_org := null;
2787 END;
2788 --yao zhang fix bug 7829039 add end
2789
2790 BEGIN
2791
2792 -- begin log
2793 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2794 log(FND_LOG.LEVEL_PROCEDURE,
2795 G_MODULE_PREFIX || l_procedure_name,
2796 '************************************');
2797 log(FND_LOG.LEVEL_PROCEDURE,
2798 G_MODULE_PREFIX || l_procedure_name,
2799 'l_customer_trx_line_id:' || l_customer_trx_line_id); --exception 05
2800 log(FND_LOG.LEVEL_PROCEDURE,
2801 G_MODULE_PREFIX || l_procedure_name,
2802 '************************************');
2803 END IF;
2804 -- end log
2805
2806 -- 29-JUN-2006 Modified by Shujuan for bug 5168900,
2807 -- Added return parameter p_tax_curr_unit_price
2808 -- in order to get the unit price of tax currency
2809 jmf_gta_trx_util.get_info_from_ebtax(p_org_id => p_org_id,
2810 p_trx_id => l_customer_trx_id,
2811 p_trx_line_id => l_customer_trx_line_id,
2812 p_tax_type_code => l_vat_tax_type,
2813 x_tax_amount => l_tax_amount_func_curr,
2814 x_taxable_amount => l_taxable_amount_func_curr,
2815 x_taxable_amount_org => l_amount,
2816 x_trx_line_quantity => l_line_quantity,
2817 x_tax_rate => l_tax_rate,
2821 x_tp_registration_number => l_tp_registration_number,
2818 x_unit_selling_price => l_unit_price,
2819 x_tax_curr_unit_price => p_tax_curr_unit_price,
2820 x_fp_registration_number => l_fp_registration_number,
2822 x_invoice_type => l_invoice_type, --added by subba for R12.1
2823 x_status => l_status,
2824 x_error_buffer => l_proce_error_buffer);
2825
2826 -- begin log
2827 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2828 log(FND_LOG.LEVEL_PROCEDURE,
2829 G_MODULE_PREFIX || l_procedure_name,
2830 'ebtax p_org_id:' || p_org_id);
2831 log(FND_LOG.LEVEL_PROCEDURE,
2832 G_MODULE_PREFIX || l_procedure_name,
2833 'ebtax l_customer_trx_id:' || l_customer_trx_id);
2834 log(FND_LOG.LEVEL_PROCEDURE,
2835 G_MODULE_PREFIX || l_procedure_name,
2836 'ebtax l_customer_trx_line_id:' || l_customer_trx_line_id);
2837 log(FND_LOG.LEVEL_PROCEDURE,
2838 G_MODULE_PREFIX || l_procedure_name,
2839 'ebtax l_vat_tax_type:' || l_vat_tax_type);
2840 log(FND_LOG.LEVEL_PROCEDURE,
2841 G_MODULE_PREFIX || l_procedure_name,
2842 'ebtax l_tax_amount_func_curr:' || l_tax_amount_func_curr);
2843 log(FND_LOG.LEVEL_PROCEDURE,
2844 G_MODULE_PREFIX || l_procedure_name,
2845 'ebtax l_taxable_amount_func_curr:' ||
2846 l_taxable_amount_func_curr);
2847 log(FND_LOG.LEVEL_PROCEDURE,
2848 G_MODULE_PREFIX || l_procedure_name,
2849 'ebtax l_line_quantity:' || l_line_quantity);
2850 log(FND_LOG.LEVEL_PROCEDURE,
2851 G_MODULE_PREFIX || l_procedure_name,
2852 'ebtax l_tax_rate:' || l_tax_rate);
2853 log(FND_LOG.LEVEL_PROCEDURE,
2854 G_MODULE_PREFIX || l_procedure_name,
2855 'ebtax l_unit_price:' || l_unit_price);
2856 log(FND_LOG.LEVEL_PROCEDURE,
2857 G_MODULE_PREFIX || l_procedure_name,
2858 'ebtax l_fp_registration_number:' ||
2859 l_fp_registration_number);
2860 log(FND_LOG.LEVEL_PROCEDURE,
2861 G_MODULE_PREFIX || l_procedure_name,
2862 'ebtax l_tp_registration_number:' ||
2863 l_tp_registration_number);
2864 log(FND_LOG.LEVEL_PROCEDURE,
2865 G_MODULE_PREFIX || l_procedure_name,
2866 'ebtax l_status:' || l_status);
2867 log(FND_LOG.LEVEL_PROCEDURE,
2868 G_MODULE_PREFIX || l_procedure_name,
2869 'ebtax l_proce_error_buffer:' || l_proce_error_buffer);
2870 END IF;
2871 -- end log
2872
2873 -- Yao zhang add for bug 7629877
2874 --For Common VAT type,validate CM invoice
2875 IF (l_invoice_type = '2' and l_ctt_class = 'CM')
2876 THEN
2877 --log begin
2878 l_ar_inv_excep := false;
2879 l_gt_inv_excep := false;
2880 l_cm_warn := false;
2881 l_pre_cus_trxid := null;
2882 l_ar_invoice_count := 0;
2883 l_gt_invoice_count := 0;
2884 IF (FND_LOG.LEVEL_EXCEPTION >=
2885 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2886 log(FND_LOG.LEVEL_PROCEDURE,
2887 G_MODULE_PREFIX || l_procedure_name,
2888 '****************************************');
2889 log(FND_LOG.LEVEL_PROCEDURE,
2890 G_MODULE_PREFIX || l_procedure_name,
2891 'is CM');
2892 log(FND_LOG.LEVEL_PROCEDURE,
2893 G_MODULE_PREFIX || l_procedure_name,
2894 '****************************************');
2895 END IF;
2896 --checking whether CM is associated with AR invoice or not
2897 select previous_customer_trx_id
2898 into l_pre_cus_trxid
2899 from ra_customer_trx_all
2900 where customer_trx_id = l_customer_trx_id;
2901 IF (l_pre_cus_trxid is not null) then
2902 -- this credit memo is credited with AR invoice
2903 --Select all AR invoices associated with credit memo.
2904 SELECT count(*)
2905 INTO l_ar_invoice_count
2906 FROM Jmf_Gta_Trx_Headers_All
2907 WHERE ra_trx_id = l_pre_cus_trxid
2908 AND SOURCE = 'AR';
2909
2910 IF l_ar_invoice_count = 0 THEN
2911 --AR invoice has not been transfered to GTA
2912 fnd_file.put_line(fnd_file.log,
2913 'AR invoice=' || l_ar_invoice_count);
2914 l_ar_inv_excep := true;
2915
2916 ELSIF l_ar_invoice_count >= 2
2917 THEN
2918 --credit memo associated to AR invoice which is transferred to GTA with split
2919 l_cm_warn := true;
2920 ELSIF l_ar_invoice_count = 1
2924 SELECT count(*)
2921 --credit memo associated to AR invoice which is transferred to GTA without split
2922 then
2923 --check whether the GT invoice has been imported.
2925 INTO l_gt_invoice_count
2926 FROM Jmf_Gta_Trx_Headers_All
2927 WHERE ra_trx_id = l_pre_cus_trxid
2928 AND source='GT'; --Yao Zhang Modified fix bug 7670543
2929
2930 IF l_gt_invoice_count = 0
2931 THEN
2932 --AR trx is in workbench but VAT not generated.
2933 l_gt_inv_excep := true;
2934 --Yao Zhang add begin for bug 7685610--
2935 ELSE--VAT is generated for AR transaction
2936 SELECT gt_invoice_number,gt_invoice_class
2937 INTO l_gt_invoice_number,l_gt_invoice_class
2938 FROM Jmf_Gta_Trx_Headers_All
2939 WHERE ra_trx_id = l_pre_cus_trxid
2940 AND source='GT';
2941 fnd_message.SET_NAME('JMF', 'JMF_GTA_CREDMEMO_EXPORT_IV');
2942 l_cm_desc1:=fnd_message.GET();
2943 fnd_message.SET_NAME('JMF', 'JMF_GTA_CREDMEMO_EXPORT_NR');
2944 l_cm_desc2:=fnd_message.GET();
2945 l_trx_header.description :=l_cm_desc1||l_gt_invoice_class||' '||l_cm_desc2||l_gt_invoice_number;
2946 --Yao Zhang add end for bug 7685610--
2947 END IF; --/*l_gt_invoice_count = 0 */
2948 END IF; --/* l_ar_invoice_count =0*/
2949
2950 ELSE
2951 l_cm_warn := true; --on account cm can be transfered with warning
2952 END IF; --/l_pre_cus_trxid is not null
2953
2954 IF (l_cm_warn = true) THEN
2955 --if credit memo not associated with any AR inv or associated with multi AR inv
2956
2957 fnd_message.SET_NAME('JMF', 'JMF_GTA_CRMEMO_DES_NULL');
2958 l_error_string := fnd_message.GET();
2959 IF (FND_LOG.LEVEL_UNEXPECTED >=
2960 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2961 log(FND_LOG.LEVEL_PROCEDURE,
2962 G_MODULE_PREFIX || l_procedure_name,
2963 'JMF_GTA_CRMEMO_DES_NULL');
2964 END IF;
2965 IF (FND_LOG.LEVEL_EXCEPTION >=
2966 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2967 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
2968 G_MODULE_PREFIX || l_procedure_name,
2969 l_error_string);
2970 END IF;
2971
2972 delete
2973 from jmf_gta_transfer_temp temp
2974 WHERE temp.transaction_id = l_customer_trx_id
2975 AND temp.succeeded = 'W'
2976 and temp.tax_reg_num=l_tp_registration_number;--Modified by Yao Zhang for bug 7684662
2977
2978
2979
2980 INSERT INTO jmf_gta_transfer_temp t
2981 (t.seq,
2982 t.transaction_id,
2983 t.succeeded,
2984 t.transaction_num,
2985 t.transaction_type,
2986 t.customer_name,
2987 t.amount,
2988 t.failedreason,
2989 t.gta_invoice_num,
2990 t.tax_reg_num)
2991 SELECT jmf_gta_transfer_temp_s.NEXTVAL,
2992 l_customer_trx_id,
2993 'W',
2994 l_trx_number,
2995 l_ctt_class,
2996 l_rac_bill_to_customer_name,
2997 NULL,
2998 l_error_string,
2999 NULL,
3000 l_tp_registration_number--added by Yao Zhang for bug 7644235
3001 -- to distinguish different tax reg number on trx lines.
3002 FROM dual;
3003
3004 END IF; -- IF (l_cm_warn = true)
3005
3006 IF (l_gt_inv_excep = true or l_ar_inv_excep = true) THEN
3007 --if credit memo associated with AR and in GTA but not VAT generated for non-common VAT
3008 --rasie correspoding AR transation not in GT warning.
3009 fnd_message.SET_NAME('JMF', 'JMF_GTA_CRMEMO_MISSING_GTINV');
3010 l_error_string := fnd_message.GET();
3011 IF (FND_LOG.LEVEL_UNEXPECTED >=
3012 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3013 log(FND_LOG.LEVEL_PROCEDURE,
3014 G_MODULE_PREFIX || l_procedure_name,
3015 'JMF_GTA_CRMEMO_MISSING_GTINV');
3016 END IF;
3017 IF (FND_LOG.LEVEL_EXCEPTION >=
3018 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3019 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3020 G_MODULE_PREFIX || l_procedure_name,
3021 l_error_string);
3022 END IF;
3023 CLOSE l_ra_lines;
3024 RAISE l_normal_exception; --Raise normal exception to skip this credit memo
3025 END IF; -- /*IF (l_cm_excep = 'true') THEN*/
3026
3027 END IF; --/(l_invoice_type = '2' and l_ctt_class = 'CM')
3028 --Yao Zhang add end for bug 7629877.
3029
3033 IF (l_invoice_type <> '2') THEN
3030 --following code added by subba for R12.1, check for the cust address,phone no, bank details exception
3031 --Raise exception if invoice type is not common.
3032
3034 --If invoice type is not common VAT
3035
3036 IF (l_cust_addr_excep = 'true') THEN
3037 --if customer address is null
3038
3039 IF (FND_LOG.LEVEL_EXCEPTION >=
3040 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3041 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3042 G_MODULE_PREFIX || l_procedure_name,
3043 'no data found when select cust address for non-common VAT invoice');
3044 END IF;
3045 l_error_string := 'no data found when select cust address for non-common VAT invoice';
3046 CLOSE l_ra_lines;
3047 RAISE l_normal_exception;
3048
3049 END IF; -- IF (l_cust_addr_excep = 'true') THEN
3050
3051 IF (l_cust_phone_exp = 'true') THEN
3052 --if customer phone is null
3053
3054 IF (FND_LOG.LEVEL_EXCEPTION >=
3055 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3056 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3057 G_MODULE_PREFIX || l_procedure_name,
3058 'no data found when select customer phone number for non-common VAT invoice');
3059 END IF;
3060 l_error_string := 'no data found when select customer phone number for non-common VAT invoice';
3061 CLOSE l_ra_lines;
3062 RAISE l_normal_exception;
3063
3064 END IF; /*IF (l_cust_phone_exp = 'true')*/
3065 --Yao Zhang Commented for bug 7629877 there is no necessary to check cm for non-common invoice
3066 /*IF (l_ar_inv_excep = 'true') THEN --if credit memo not associated with any AR inv for NON-Common VAT
3067
3068 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3069 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
3070 , G_MODULE_PREFIX || l_procedure_name
3071 , l_error_string);
3072 END IF;
3073
3074 l_error_string := 'No AR invoice associated with this credit memo';
3075 RAISE l_normal_exception;
3076
3077 END IF; /*IF (l_ar_inv_excep = 'true') THEN*/
3078
3079 /*IF (l_cm_excep = 'true') THEN --if credit memo associated with AR and in GTA but not VAT generated for NON-common VAT
3080
3081 --rasie correspoding AR transation not in GT warning.
3082
3083
3084 l_error_string := 'There is no corresponding VAT invoice in workbench to process this credit memo';
3085 -- begin log
3086 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3087 THEN
3088 log(FND_LOG.LEVEL_PROCEDURE,G_MODULE_PREFIX ||
3089 l_procedure_name,
3090 'JMF_GTA_CRMEMO_MISSING_GTAINV');
3091 END IF;
3092 -- end log
3093
3094 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3095 THEN
3096 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
3097 , G_MODULE_PREFIX || l_procedure_name
3098 , l_error_string);
3099 END IF;
3100
3101 RAISE l_normal_exception ; --Raise normal exception to skip this credit memo
3102
3103 END IF; /*IF (l_cm_excep = 'true') THEN*/
3104 --Yao Zhang Commented end for bug 7629877
3105
3106 END IF; /*IF (l_invoice_type <> 2) THEN*/
3107
3108 -- throw exception is l_status is -1 , 1 , or 2
3109 -- if status is -1 then the tax line count is 0 and skip this trx
3110 -- if status is 1 then the tax line is more than one
3111 -- or fp/tp registratioin number is null
3112 -- throw a exception and skip this trx
3113 -- is status is 2 then is a system opertion error , exit the program.
3114 IF l_status = -1 THEN
3115 RAISE l_no_tax_line_exception;
3116 ELSIF l_status = 1 THEN
3117 --29/06/2006 Updated by Shujuan Yan for bug 5258522
3118 --Should display line number instead of line id.
3119 --Use line number as token value
3120 --modifed by subba for R12.1
3121
3122 IF l_proce_error_buffer = 'JMF_GTA_MISSING_INVOICE_TYPE' THEN
3123
3124 SELECT name
3125 INTO l_trx_typ
3126 FROM ra_cust_trx_types_all rctt, ra_customer_trx_all rct
3127 WHERE rct.cust_trx_type_id = rctt.cust_trx_type_id(+)
3128 AND rct.org_id = rctt.org_id(+)
3129 AND rct.customer_trx_id = l_customer_trx_id
3130 AND rct.org_id = p_org_id;
3131
3132 fnd_message.SET_NAME('JMF', l_proce_error_buffer);
3133 fnd_message.set_token('TRX_TYP', l_trx_typ);
3134 fnd_message.set_token('TAX_REG_NUM',
3135 l_fp_registration_number);
3136 l_error_string := fnd_message.GET();
3137
3141 log(FND_LOG.LEVEL_PROCEDURE,
3138 -- begin log
3139 IF (FND_LOG.LEVEL_PROCEDURE >=
3140 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3142 G_MODULE_PREFIX || l_procedure_name,
3143 'exception missing transaction type association' ||
3144 l_proce_error_buffer);
3145 END IF;
3146 -- end log
3147
3148 ELSIF l_proce_error_buffer = 'JMF_GTA_TAX_ERROR_RECYCLE' THEN
3149
3150 fnd_message.SET_NAME('JMF', l_proce_error_buffer);
3151 l_error_string := fnd_message.GET();
3152
3153 -- begin log
3154 IF (FND_LOG.LEVEL_PROCEDURE >=
3155 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3156 log(FND_LOG.LEVEL_PROCEDURE,
3157 G_MODULE_PREFIX || l_procedure_name,
3158 'exception tax rate and tax amount for Recycle Invoice Type' ||
3159 l_proce_error_buffer);
3160 END IF;
3161 -- end log
3162 ELSE
3163
3164 fnd_message.SET_NAME('JMF', l_proce_error_buffer);
3165 fnd_message.set_token('NUM', l_customer_trx_line_number);
3166 fnd_message.set_token('TAXTYPE', l_vat_tax_type);
3167 l_error_string := fnd_message.GET();
3168
3169 -- begin log
3170 IF (FND_LOG.LEVEL_PROCEDURE >=
3171 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3172 log(FND_LOG.LEVEL_PROCEDURE,
3173 G_MODULE_PREFIX || l_procedure_name,
3174 'exception registration number' ||
3175 l_proce_error_buffer);
3176 END IF;
3177 -- end log
3178 END IF;
3179
3180 IF (FND_LOG.LEVEL_EXCEPTION >=
3181 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3182 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3183 G_MODULE_PREFIX || l_procedure_name,
3184 l_error_string);
3185 END IF;
3186 CLOSE l_ra_lines;
3187 RAISE l_normal_exception;
3188 ELSIF l_status = 2 THEN
3189 fnd_message.SET_NAME('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
3190 fnd_message.set_token('Tax_Regis_Number',
3191 l_fp_registration_number);
3192 l_error_string := fnd_message.get();
3193
3194 -- begin log
3195 IF (FND_LOG.LEVEL_PROCEDURE >=
3196 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3197 log(FND_LOG.LEVEL_PROCEDURE,
3198 G_MODULE_PREFIX || l_procedure_name,
3199 'JMF_GTA_SYS_CONFIG_MISSING');
3200 log(FND_LOG.LEVEL_PROCEDURE,
3201 G_MODULE_PREFIX || l_procedure_name,
3202 'First Party tax registartion number is not exist in System Option');
3203 END IF;
3204 -- end log
3205
3206 CLOSE l_ra_lines;
3207 RAISE l_normal_exception;
3208 END IF;
3209
3210 --unit price validate. This will throw a exception JMF_GTA_UNITPRICE_ERROR
3211 BEGIN
3212 SELECT limits.max_amount, limits.max_num_of_line
3213 INTO l_max_amount, l_max_num_of_line
3214 FROM jmf_gta_tax_limits_all limits
3215 WHERE limits.fp_tax_registration_number =
3216 l_fp_registration_number
3217 AND limits.invoice_type = l_invoice_type
3218 AND limits.org_id = p_org_id;
3219
3220 -- 29-JUN-2006 deleted by Shujuan for bug 5168900
3221 -- Since it is possible that the currency l_unit_selling_price is
3222 -- different with the currency l_max_amount.
3223 --IF l_unit_selling_price > l_max_amount
3224
3225 -- 09/06/2006 Updated by Shujuan Yan for bug 5263215
3226 -- Change message code from JMF_GTA_UNITPRICE_ERROR
3227 -- into JMF_GTA_UNITPRICE_EXCEED
3228 -- 12/06/2006 Updated by Shujuan Yan for bug 5230712
3229 -- Should display line number instead of line id.
3230 -- Use line number as token value
3231 -- 29-JUN-2006 Added by Shujuan for bug 5168900,
3232 -- Since the currency of tax unit price is same with
3233 -- the currency of max amount of GTA
3234 IF p_tax_curr_unit_price > l_max_amount THEN
3235
3236 fnd_message.SET_NAME('JMF', 'JMF_GTA_UNITPRICE_EXCEED');
3237 fnd_message.set_token('NUM', l_customer_trx_line_number);
3238 l_error_string := fnd_message.GET();
3239
3240 -- begin log
3241 IF (FND_LOG.LEVEL_PROCEDURE >=
3242 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3243 log(FND_LOG.LEVEL_PROCEDURE,
3244 G_MODULE_PREFIX || l_procedure_name,
3245 'JMF_GTA_UNITPRICE_EXCEED');
3246 END IF;
3247 -- end log
3248
3249 IF (FND_LOG.LEVEL_EXCEPTION >=
3250 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3254 END IF;
3251 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3252 G_MODULE_PREFIX || l_procedure_name,
3253 l_error_string);
3255 CLOSE l_ra_lines;
3256 RAISE l_normal_exception;
3257 END IF; /*(l_unit_price IS NOT NULL) AND l_unit_price > l_max_amount*/
3258 EXCEPTION
3259 WHEN no_data_found THEN
3260 IF (FND_LOG.LEVEL_UNEXPECTED >=
3261 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3262 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
3263 G_MODULE_PREFIX || l_procedure_name,
3264 'no data found');
3265 END IF;
3266 fnd_message.set_name('JMF', 'JMF_GTA_SYS_CONFIG_MISSING');
3267
3268 IF (FND_LOG.LEVEL_UNEXPECTED >=
3269 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3270 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
3271 G_MODULE_PREFIX || l_procedure_name,
3272 fnd_message.get());
3273
3274 END IF;
3275 RAISE;
3276 RETURN;
3277 END;
3278
3279 -- begin validate the flexfield
3280 -- Begin get item name , so l_trx_line.item_description
3281 -- 12/06/2006 Updated by Shujuan Yan for bug 5230712
3282 -- should display line number and item name instead of line id and item id.
3283 IF (l_item_name_source_flag = 'R') THEN
3284 -- if the description of AR is null then report a error
3285 IF (l_description IS NULL) THEN
3286 --report JMF_GTA_AR_DESC_MISS
3287 fnd_message.set_name('JMF', 'JMF_GTA_MISSING_FIELD');
3288 fnd_message.set_token('NUM', l_customer_trx_line_number);
3289 fnd_message.set_token('TrxNum',
3290 l_trx_header.gta_trx_header_id);
3291 l_error_string := fnd_message.get();
3292
3293 -- begin log
3294 IF (FND_LOG.LEVEL_PROCEDURE >=
3295 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3296 log(FND_LOG.LEVEL_PROCEDURE,
3297 G_MODULE_PREFIX || l_procedure_name,
3298 'JMF_GTA_MISSING_FIELD');
3299 END IF;
3300 -- end log
3301
3302 IF (FND_LOG.LEVEL_EXCEPTION >=
3303 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3304 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3305 G_MODULE_PREFIX || l_procedure_name,
3306 l_error_string);
3307 END IF;
3308
3309 CLOSE l_ra_lines;
3310 RAISE l_normal_exception;
3311
3312 ELSE
3313 /*(l_description IS NULL)*/
3314 l_trx_line.item_description := l_description;
3315
3316 END IF; /*(l_description IS NULL)*/
3317
3318 ELSE
3319 /* (l_ITEM_NAME_SOURCE_FLAG is not 'R')*/
3320 --get item name from Inventory Item cross reference or description
3321 IF l_inventory_item_id IS NOT NULL THEN
3322
3323 --The following code is added by Yao Zhang to retrive Item Validation Organization for ou
3324 --The following code is added by Yao Zhang to retrive Item Validation Organization for ou
3325 begin
3326 select parameter_value
3327 into l_master_org
3328 from OE_SYS_PARAMETERS_ALL
3329 where org_id = p_org_id
3330 and parameter_code = 'MASTER_ORGANIZATION_ID';
3331 EXCEPTION
3332 WHEN no_data_found THEN
3333 fnd_message.set_name('JMF', 'JMF_GTA_MISSING_CROSS_REF');
3334 fnd_message.set_token('NUM', l_customer_trx_line_number);
3335 l_error_string := fnd_message.get();
3336
3337 -- log
3338 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3339 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3340 G_MODULE_PREFIX || l_procedure_name,
3341 l_error_string);
3342 END IF;
3343
3344 CLOSE l_ra_lines;
3345 RAISE l_normal_exception;
3346 END;
3347 --yao zhang add end for bug
3348 --get record number in cross reference. Only retrive item cross reference on master inv org
3349 SELECT COUNT(*)
3350 INTO l_cross_rows
3351 FROM MTL_CROSS_REFERENCES
3352 WHERE (organization_id IS NULL OR
3353 organization_id = l_master_org)--yao zhang modified for bug 7721035
3354 AND inventory_item_id = l_inventory_item_id
3355 AND cross_reference_type = l_cross_reference_type;
3356
3357 --get latest cross reference
3358 IF l_cross_rows > 0 THEN
3359 SELECT MAX(cross_reference)
3360 INTO l_cross_reference
3361 FROM MTL_CROSS_REFERENCES
3362 WHERE (organization_id IS NULL OR
3363 organization_id = l_master_org)--yao zhang modified for bug 7721035
3364 AND inventory_item_id = l_inventory_item_id
3368 FROM MTL_CROSS_REFERENCES
3365 AND cross_reference_type = l_cross_reference_type
3366 AND last_update_date =
3367 (SELECT MAX(last_update_date)
3369 WHERE (organization_id IS NULL OR
3370 organization_id = l_master_org)--yao zhang modified for bug 7721035
3371 AND inventory_item_id = l_inventory_item_id
3372 AND cross_reference_type =
3373 l_cross_reference_type);
3374 ELSE
3375 /*l_cross_rows > 0*/
3376 l_cross_reference := null;
3377 END IF; /*l_cross_rows > 0*/
3378
3379 BEGIN
3380 SELECT DESCRIPTION, attribute_category
3381 INTO l_inventory_item_name,
3382 l_inventory_attribute_category
3383 FROM mtl_system_items_b
3384 WHERE organization_id = l_master_org--yao zhang modified for bug 7721035
3385 AND inventory_item_id = l_inventory_item_id;
3386
3387 EXCEPTION
3388 WHEN no_data_found THEN
3389 fnd_message.set_name('JMF',
3390 'JMF_GTA_MISSING_CROSS_REF');
3391 fnd_message.set_token('NUM',
3392 l_customer_trx_line_number);
3393 l_error_string := fnd_message.get();
3394
3395 -- log
3396 IF (FND_LOG.LEVEL_EXCEPTION >=
3397 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3398 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3399 G_MODULE_PREFIX || l_procedure_name,
3400 l_error_string);
3401 END IF;
3402
3403 CLOSE l_ra_lines;
3404 RAISE l_normal_exception;
3405 END;
3406 --multi-lines cross reference and setup not allow it
3407 IF (l_latest_ref_default_flag = 'N' AND l_cross_rows > 1) THEN
3408 --report JMF_GTA_MISSING_CROSS_REF
3409 fnd_message.SET_NAME('JMF', 'JMF_GTA_MULTIPLE_REF');
3410 fnd_message.SET_TOKEN('NUM', l_customer_trx_line_number);
3411 l_error_string := fnd_message.get();
3412
3413 -- log
3414 IF (FND_LOG.LEVEL_EXCEPTION >=
3415 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3416 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3417 G_MODULE_PREFIX || l_procedure_name,
3418 l_error_string);
3419 END IF;
3420 CLOSE l_ra_lines;
3421 RAISE l_normal_exception;
3422
3423 ELSE
3424 /*(l_latest_ref_default_flag ='N' AND l_cross_rows>1 )*/
3425 IF l_cross_reference IS NULL THEN
3426
3427 IF (l_MASTER_ITEM_DEFAULT_FLAG = 'Y') THEN
3428
3429 l_trx_line.item_description := l_inventory_item_name;
3430
3431 ELSE
3432 /*(l_MASTER_ITEM_DEFAULT_FLAG = 'Y') */
3433 fnd_message.SET_NAME('JMF',
3434 'JMF_GTA_MISSING_CROSS_REF');
3435 fnd_message.SET_TOKEN('NUM',
3436 l_customer_trx_line_number);
3437 l_error_string := fnd_message.get();
3438
3439 -- log
3440 IF (FND_LOG.LEVEL_EXCEPTION >=
3441 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3442 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3443 G_MODULE_PREFIX || l_procedure_name,
3444 l_error_string);
3445 END IF;
3446 CLOSE l_ra_lines;
3447 RAISE l_normal_exception;
3448 END IF; /*(l_MASTER_ITEM_DEFAULT_FLAG = 'Y') */
3449 ELSE
3450 /*l_cross_reference IS NULL*/
3451 l_trx_line.item_description := l_cross_reference;
3452 END IF; /*l_cross_reference IS NULL*/
3453 END IF; /*(l_latest_ref_default_flag ='N' AND l_cross_rows>1 )*/
3454 ELSE
3455 --12/07/2006 Added by Shujuan Yan for bug 5224923
3456 -- When item is not inventory item, item_description
3457 -- should be assigned the value of AR transaction line description.
3458 l_trx_line.item_description := l_description;
3459 --11/06/2006 deleted by Shujuan Yan for bug 5224923
3460 /* ELSE /*l_inventory_item_id IS NOT NULL*/
3461 /* fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_CROSS_REF');
3462 fnd_message.set_token('NUM', l_customer_trx_line_number);
3463 l_error_string := fnd_message.get;
3464
3465 -- log
3466 IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3467 THEN
3468 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
3469 , G_MODULE_PREFIX || l_procedure_name
3470 , l_error_string);
3471 END IF;
3472
3473 CLOSE l_ra_lines;
3474 RAISE l_normal_exception;
3475 END IF; /*l_inventory_item_id IS NOT NULL*/
3476 END IF; /*l_inventory_item_id IS NOT NULL*/
3477 END IF; /* (l_ITEM_NAME_SOURCE_FLAG is not 'R')*/
3478 -- End get item name
3479
3480 -- Begin get item model tax demination
3481 --get item model and tax denomination
3482 --12/06/2006 Updated by Shujuan Yan for bug 5230712
3483 --should display line number and item name instead of line id and item id.
3484 IF l_interface_line_context <> l_ra_line_context_code OR
3485 l_interface_line_context IS NULL THEN
3486 IF l_inventory_attribute_category <> l_inv_item_context_code THEN
3487 --report JMF_GTA_ARTRX_FLEX_MISSING
3488 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_MODEL');
3489 fnd_message.set_token('NUM', l_customer_trx_line_number);
3490 fnd_message.set_token('ITEM', l_inventory_item_name);
3491 l_error_string := fnd_message.get();
3492
3493 -- log
3494 IF (FND_LOG.LEVEL_EXCEPTION >=
3495 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3496 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3497 G_MODULE_PREFIX || l_procedure_name,
3498 l_error_string);
3499 END IF;
3500
3501 CLOSE l_ra_lines;
3502 RAISE l_normal_exception;
3503 ELSE
3504 /*l_interface_line_context <> l_inv_item_context_code*/
3505 IF l_inv_model_attribute_column IS NULL THEN
3506 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_MODEL');
3507 fnd_message.set_token('NUM', l_customer_trx_line_number);
3508 fnd_message.set_token('ITEM', l_inventory_item_name);
3509 l_error_string := fnd_message.get;
3510
3511 -- log
3512 IF (FND_LOG.LEVEL_EXCEPTION >=
3513 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3514 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3515 G_MODULE_PREFIX || l_procedure_name,
3516 l_error_string);
3517 END IF;
3518
3519 CLOSE l_ra_lines;
3520 RAISE l_normal_exception;
3521 ELSE
3522 /*l_inv_model_attribute_column IS NULL*/
3523 get_inv_item_model(p_item_master_org_id => l_master_org,--yao zhang changed fix bug 7829039
3524 p_inventory_item_id => l_inventory_item_id,
3525 p_attribute_column => l_inv_model_attribute_column,
3526 x_attribute_value => l_trx_line.item_model);
3527
3528 END IF; /*l_inv_model_attribute_column IS NULL*/
3529
3530 IF l_inv_tax_attribute_column IS NULL THEN
3531 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_TAX_DENOM');
3532 fnd_message.set_token('NUM', l_customer_trx_line_number);
3533 fnd_message.set_token('ITEM', l_inventory_item_name);
3534 l_error_string := fnd_message.get();
3535 -- log
3536 IF (FND_LOG.LEVEL_PROCEDURE >=
3537 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3541 END IF;
3538 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
3539 G_MODULE_PREFIX || l_procedure_name,
3540 l_error_string);
3542 CLOSE l_ra_lines;
3543 RAISE l_normal_exception;
3544
3545 ELSE
3546 get_inv_item_model(p_item_master_org_id => l_master_org,--yao zhang changed fix bug 7829039
3547 p_inventory_item_id => l_inventory_item_id,
3548 p_attribute_column => l_inv_tax_attribute_column,
3549 x_attribute_value => l_trx_line.item_tax_denomination);
3550
3551 END IF; --/* end if l_inv_tax_attribute_column IS NULL*/
3552 END IF; --/*l_column_type.attribute_category <> l_inv_item_context_code*/
3553
3554 ELSE
3555 /*l_interface_line_context <> l_ra_line_context_code*/
3556
3557 get_ra_item_model(p_ra_line_id => l_customer_trx_line_id,
3558 p_attribute_column => l_ra_model_attribute_column,
3559 x_attribute_value => l_trx_line.item_model);
3560
3561 -- begin log
3562 IF (FND_LOG.LEVEL_PROCEDURE >=
3563 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3564 log(FND_LOG.LEVEL_PROCEDURE,
3565 G_MODULE_PREFIX || l_procedure_name,
3566 'item_model:' || l_trx_line.item_model);
3567 END IF;
3568 -- end log
3569
3570 IF l_trx_line.item_model IS NULL THEN
3571 IF l_inventory_attribute_category <>
3572 l_inv_item_context_code THEN
3573 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_MODEL');
3574 fnd_message.set_token('NUM', l_customer_trx_line_number);
3575 fnd_message.set_token('ITEM', l_inventory_item_name);
3576 l_error_string := fnd_message.get();
3577
3578 -- log
3579 IF (FND_LOG.LEVEL_EXCEPTION >=
3580 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3581 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3582 G_MODULE_PREFIX || l_procedure_name,
3583 l_error_string);
3584 END IF;
3585 CLOSE l_ra_lines;
3586 RAISE l_normal_exception;
3587
3588 ELSE
3589 /*l_ra_model_attribute_column IS NULL*/
3590 get_inv_item_model(p_item_master_org_id => l_master_org,--yao zhang changed fix bug 7829039
3591 p_inventory_item_id => l_inventory_item_id,
3592 p_attribute_column => l_inv_model_attribute_column,
3593 x_attribute_value => l_trx_line.item_model);
3594 END IF; --/*l_column_type.attribute_category<>l_inv_item_context_code*/
3595
3596 END IF; -- /*end if l_ra_model_attribute_column IS NULL*/
3597
3598 -- begin get tax denmo
3599 get_ra_item_model(p_ra_line_id => l_customer_trx_line_id,
3600 p_attribute_column => l_ra_tax_attribute_column,
3601 x_attribute_value => l_trx_line.item_tax_denomination);
3602
3603 -- begin log
3604 IF (FND_LOG.LEVEL_PROCEDURE >=
3605 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3606 log(FND_LOG.LEVEL_PROCEDURE,
3607 G_MODULE_PREFIX || l_procedure_name,
3608 'item_tax_denomination:' ||
3609 l_trx_line.item_tax_denomination);
3610 END IF;
3611 -- end log
3612
3613 --12/06/2006 Updated by Shujuan Yan for bug 5230712
3614 --Should display line number and item name instead of line id and item id.
3615 IF l_trx_line.item_tax_denomination IS NULL THEN
3616 IF l_inventory_attribute_category <>
3617 l_inv_item_context_code THEN
3618 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_TAX_DENOM');
3619 fnd_message.set_token('NUM', l_customer_trx_line_number);
3620 fnd_message.set_token('ITEM', l_inventory_item_name);
3621
3622 l_error_string := fnd_message.get();
3623
3624 -- log
3625 IF (FND_LOG.LEVEL_EXCEPTION >=
3626 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3627 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
3628 G_MODULE_PREFIX || l_procedure_name,
3629 l_error_string);
3630 END IF;
3631 CLOSE l_ra_lines;
3632 RAISE l_normal_exception;
3633 ELSE
3634 get_inv_item_model(p_item_master_org_id => l_master_org,--yao zhang changed fix bug 7829039
3635 p_inventory_item_id => l_inventory_item_id,
3636 p_attribute_column => l_inv_tax_attribute_column,
3637 x_attribute_value => l_trx_line.item_tax_denomination);
3638 END IF; --l_column_type.attribute_category <> l_inv_item_context_code
3639
3640 END IF; -- l_ra_tax_attribute_column IS NULL
3641
3642 END IF; /*l_column_type.attribute_category <>l_ra_line_context_code*/
3643 --end validate flexfield
3644
3645 -- begin check the itme description and item tax denomination,
3646 -- if either of their is null, Then throw JMF_GTA_MESSING_TRX_DENOM
3647 -- message or JMF_GTA_MISSING_CROSS_REF message.
3648 -- 12/06/2006 Updated by Shujuan Yan for bug 5230712
3652 fnd_message.set_token('NUM', l_customer_trx_line_number);
3649 -- Should display line number and item name instead of line id and item id.
3650 IF l_trx_line.item_tax_denomination IS NULL THEN
3651 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_TAX_DENOM');
3653 fnd_message.set_token('ITEM', l_inventory_item_name);
3654 l_error_string := fnd_message.get();
3655 -- log
3656 IF (FND_LOG.LEVEL_UNEXPECTED >=
3657 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3658 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
3659 G_MODULE_PREFIX || l_procedure_name,
3660 l_error_string);
3661 END IF;
3662 CLOSE l_ra_lines;
3663 RAISE l_normal_exception;
3664
3665 END IF; /*l_trx_lline.itme_tax_denomination IS NULL*/
3666
3667 IF l_trx_line.item_description IS NULL THEN
3668 fnd_message.SET_NAME('JMF', 'JMF_GTA_MISSING_CROSS_REF');
3669 fnd_message.set_token('NUM', l_customer_trx_line_number);
3670 l_error_string := fnd_message.get();
3671 -- log
3672 IF (FND_LOG.LEVEL_UNEXPECTED >=
3673 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3674 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
3675 G_MODULE_PREFIX || l_procedure_name,
3676 l_error_string);
3677 END IF;
3678 CLOSE l_ra_lines;
3679 RAISE l_normal_exception;
3680
3681 END IF; /*l_trx_lline.itme_tax_denomination IS NULL*/
3682 -- end item description and item tax denomination check
3683
3684 --get item_inventory_code
3685 get_inventory_item_number(p_inventory_item_id => l_inventory_item_id,
3686 p_item_master_org_id => l_master_org,--yao zhang changed fix bug 7829039
3687 x_inventory_item_code => l_trx_line.item_number);
3688
3689 -- begin log
3690 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3691 log(FND_LOG.LEVEL_PROCEDURE,
3692 G_MODULE_PREFIX || l_procedure_name,
3693 'get_inventory_item_number end ');
3694 END IF;
3695 -- end log
3696
3697 -- the quantity stored in different column of ra_customer_trx_lines_all.
3698 IF l_ctt_class = 'CM' THEN
3699 l_trx_line.quantity := l_quantity_credited;
3700 ELSE
3701 l_trx_line.quantity := l_quantity_invoiced;
3702 END IF;
3703
3704 -- begin insert data into trx lines
3705 l_trx_line.org_id := p_org_id;
3706 l_trx_line.gta_trx_header_id := l_trx_header.gta_trx_header_id;
3707 l_trx_line.line_number := '1';
3708 l_trx_line.ar_trx_line_id := l_customer_trx_line_id;
3709 l_trx_line.inventory_item_id := l_inventory_item_id;
3710
3711 --EBTAX value
3712 l_trx_line.original_currency_amount := l_amount;
3713 l_trx_line.tax_rate := l_tax_rate;
3714 l_trx_line.uom := l_uom_code;
3715 -- get uom_name by uom_code
3716 -- modified by Brian for bug 7594218
3717 -- UOM may be null for transaction with class credit memo
3718 -- If UOM is null, get_uom_name needn't be called anymore
3719 -- IF l_ctt_class ='CM' AND l_uom_code IS not NULL
3720 if l_uom_code is not null THEN
3721 get_uom_name(p_uom_code => l_uom_code,
3722 x_uom_name => l_unit_of_measure);
3723 END IF; --l_cct_class ='CM' AND l_uom_code IS NULL
3724
3725 l_trx_line.uom_name := l_unit_of_measure;
3726 --12/06/2006 Updated by shujuan for bug 5446456
3727 l_trx_line.unit_price := ROUND(p_tax_curr_unit_price,
3728 2); --l_unit_price;
3729 l_trx_line.amount := ROUND(l_taxable_amount_func_curr,
3730 2);
3731 l_trx_line.tax_amount := l_tax_amount_func_curr;
3732 l_trx_line.fp_tax_registration_number := l_fp_registration_number;
3733 l_trx_line.tp_tax_registration_number := l_tp_registration_number;
3734
3735 l_trx_line.enabled_flag := 'Y';
3736 l_trx_line.request_id := fnd_global.CONC_REQUEST_ID();
3737 l_trx_line.program_applicaton_id := fnd_global.PROG_APPL_ID();
3738 l_trx_line.program_id := fnd_global.CONC_PROGRAM_ID;
3739 l_trx_line.program_update_date := SYSDATE;
3740 l_trx_line.creation_date := SYSDATE;
3741 l_trx_line.created_by := fnd_global.CONC_LOGIN_ID();
3742 l_trx_line.last_update_date := SYSDATE;
3743 l_trx_line.last_updated_by := fnd_global.LOGIN_ID();
3744 l_trx_line.last_update_login := fnd_global.CONC_LOGIN_ID();
3745
3746 -- begin log
3747 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3748 log(FND_LOG.LEVEL_PROCEDURE,
3749 G_MODULE_PREFIX || l_procedure_name,
3750 'Add a new line !!' || l_customer_trx_line_id);
3751 END IF;
3752 -- end log
3753
3754 l_trx_lines.EXTEND;
3755 l_trx_lines(l_trx_lines.COUNT) := l_trx_line;
3756 -- end insert data into trx_line
3757
3758 EXCEPTION
3759 WHEN l_no_tax_line_exception THEN
3760 IF (FND_LOG.LEVEL_UNEXPECTED >=
3761 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3765 'have not one tax line');
3762 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
3763 G_MODULE_PREFIX || l_procedure_name,
3764 'this line' || l_customer_trx_line_id ||
3766 log(FND_LOG.LEVEL_PROCEDURE,
3767 G_MODULE_PREFIX || l_procedure_name,
3768 'This line have not one tax line');
3769 END IF;
3770
3771 END;
3772
3773 END LOOP; /* l_ra_lines%NOTFOUND */
3774
3775 -- close cursor l_ra_lines
3776 CLOSE l_ra_lines;
3777
3778 -- check the tp_tax_registration_number of every line.
3779 -- if a trx have more then one tp_tax_regi_number, throw a exception
3780 IF l_trx_lines.COUNT > 0 THEN
3781 l_trx_line_index := l_trx_lines.FIRST;
3782 l_tp_regi_number_first := l_trx_lines(l_trx_line_index)
3783 .tp_tax_registration_number;
3784 WHILE l_trx_line_index IS NOT NULL LOOP
3785 l_tp_regi_number := l_trx_lines(l_trx_line_index)
3786 .tp_tax_registration_number;
3787 IF l_tp_regi_number <> l_tp_regi_number_first THEN
3788 fnd_message.SET_NAME('JMF', 'JMF_GTA_MULTI_TP_TAXREG');
3789 l_error_string := fnd_message.get;
3790 RAISE l_normal_exception;
3791 END IF; /*l_tp_regi_number <> l_tp_regi_number_first*/
3792
3793 l_trx_line_index := l_trx_lines.NEXT(l_trx_line_index);
3794 END LOOP;
3795
3796 --Jogen Hu 2006.2.17
3797 -- init record
3798 l_trx_rec := l_trx_rec_init;
3799 l_trx_rec.trx_header := l_trx_header;
3800 l_trx_rec.trx_lines := l_trx_lines;
3801 x_GTA_TRX_TBL.EXTEND;
3802 x_GTA_TRX_TBL(x_gta_trx_tbl.COUNT) := l_trx_rec;
3803 --Jogen Hu 2006.2.17
3804
3805 END IF; /*l_trx_lines.COUNT > 0*/
3806
3807 --Jogen Hu 2006.2.17
3808 /*
3809 -- init record
3810 l_trx_rec := l_trx_rec_init;
3811
3812 l_trx_rec.trx_header := l_trx_header;
3813 l_trx_rec.trx_lines := l_trx_lines;
3814 x_GTA_TRX_TBL.EXTEND;
3815 x_GTA_TRX_TBL(x_gta_trx_tbl.COUNT) := l_trx_rec;
3816 */
3817 --Jogen Hu 2006.2.17
3818 EXCEPTION
3819 WHEN l_normal_exception THEN
3820 --delete warning data from jmf_gta_transfer_temp
3821 DELETE jmf_gta_transfer_temp temp
3822 WHERE temp.transaction_id = l_customer_trx_id
3823 AND temp.succeeded = 'W';
3824
3825 INSERT INTO jmf_gta_transfer_temp t
3826 (t.seq,
3827 t.transaction_id,
3828 t.succeeded,
3829 t.transaction_num,
3830 t.transaction_type,
3831 t.customer_name,
3832 t.amount,
3833 t.failedreason,
3834 t.gta_invoice_num)
3835 SELECT jmf_gta_transfer_temp_s.NEXTVAL,
3836 l_customer_trx_id,
3837 'N',
3838 l_trx_number,
3839 l_ctt_class,
3840 l_rac_bill_to_customer_name,
3841 NULL,
3842 l_error_string,
3843 NULL
3844 FROM dual;
3845 WHEN l_repeat_exception THEN
3846 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3847 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
3848 G_MODULE_PREFIX || l_procedure_name,
3849 '. REPEAT_EXCEPTION ' || l_customer_trx_id);
3850 END IF;
3851
3852 WHEN OTHERS THEN
3853
3854 CLOSE l_ra_lines;
3855 RAISE;
3856 END;
3857
3858 END LOOP;
3859
3860 -- close dynamic sql cursor
3861 dbms_sql.close_cursor(l_cursor);
3862
3863 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3864 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
3865 G_MODULE_PREFIX || l_procedure_name,
3866 'END procedure. ');
3867 END IF;
3868
3869 EXCEPTION
3870 WHEN OTHERS THEN
3871 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3872 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
3873 G_MODULE_PREFIX || l_procedure_name ||
3874 '. OTHER_EXCEPTION',
3875 Sqlcode || Sqlerrm);
3876 END IF;
3877 RAISE;
3878
3879 END Retrieve_AR_TRXs;
3880
3881 --==========================================================================
3882 -- PROCEDURE NAME:
3883 -- get_inv_item_model
3884 --
3885 -- DESCRIPTION:
3886 -- This procedure get_item model
3887 -- by p_inventory_item_id and org_id
3888 --
3889 -- PARAMETERS: p_org_id IN NUMBER
3890 -- p_inventory_item_id IN NUMBER
3891 -- p_attribute_column IN VARCHAR2
3892 -- x_attribute_value OUT NOCOPY VARCHAR2
3893
3894 -- DESIGN REFERENCES:
3895 -- GTA-TRANSFER-PROGRAM-TD.doc
3896 --
3897 -- CHANGE HISTORY:
3898 -- 20-APR-2005: Jim Zheng Created.
3899 -- 22/Jan/2009 Yao Zhang modified for bug 7829039
3900 --===========================================================================
3901 PROCEDURE get_inv_item_model(p_item_master_org_id IN NUMBER,--yao zhang changed fix bug 7829039
3902 p_inventory_item_id IN NUMBER,
3906
3903 p_attribute_column IN VARCHAR2,
3904 x_attribute_value OUT NOCOPY VARCHAR2) IS
3905 l_procedure_name VARCHAR2(30) := 'get_item_model';
3907 l_inventory_attribute1 mtl_system_items_b.attribute1%TYPE;
3908 l_inventory_attribute2 mtl_system_items_b.attribute2%TYPE;
3909 l_inventory_attribute3 mtl_system_items_b.attribute3%TYPE;
3910 l_inventory_attribute4 mtl_system_items_b.attribute4%TYPE;
3911 l_inventory_attribute5 mtl_system_items_b.attribute5%TYPE;
3912 l_inventory_attribute6 mtl_system_items_b.attribute6%TYPE;
3913 l_inventory_attribute7 mtl_system_items_b.attribute7%TYPE;
3914 l_inventory_attribute8 mtl_system_items_b.attribute8%TYPE;
3915 l_inventory_attribute9 mtl_system_items_b.attribute9%TYPE;
3916 l_inventory_attribute10 mtl_system_items_b.attribute10%TYPE;
3917 l_inventory_attribute11 mtl_system_items_b.attribute11%TYPE;
3918 l_inventory_attribute12 mtl_system_items_b.attribute12%TYPE;
3919 l_inventory_attribute13 mtl_system_items_b.attribute13%TYPE;
3920 l_inventory_attribute14 mtl_system_items_b.attribute14%TYPE;
3921 l_inventory_attribute15 mtl_system_items_b.attribute15%TYPE;
3922 l_inventory_attribute16 mtl_system_items_b.attribute1%TYPE;
3923 l_inventory_attribute17 mtl_system_items_b.attribute2%TYPE;
3924 l_inventory_attribute18 mtl_system_items_b.attribute3%TYPE;
3925 l_inventory_attribute19 mtl_system_items_b.attribute4%TYPE;
3926 l_inventory_attribute20 mtl_system_items_b.attribute5%TYPE;
3927 l_inventory_attribute21 mtl_system_items_b.attribute6%TYPE;
3928 l_inventory_attribute22 mtl_system_items_b.attribute7%TYPE;
3929 l_inventory_attribute23 mtl_system_items_b.attribute8%TYPE;
3930 l_inventory_attribute24 mtl_system_items_b.attribute9%TYPE;
3931 l_inventory_attribute25 mtl_system_items_b.attribute10%TYPE;
3932 l_inventory_attribute26 mtl_system_items_b.attribute11%TYPE;
3933 l_inventory_attribute27 mtl_system_items_b.attribute12%TYPE;
3934 l_inventory_attribute28 mtl_system_items_b.attribute13%TYPE;
3935 l_inventory_attribute29 mtl_system_items_b.attribute14%TYPE;
3936 l_inventory_attribute30 mtl_system_items_b.attribute15%TYPE;
3937
3938 BEGIN
3939
3940 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3941 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
3942 G_MODULE_PREFIX || l_procedure_name,
3943 'Begin Procedure. ');
3944 END IF;
3945
3946 -- begin log
3947 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3948 log(FND_LOG.LEVEL_PROCEDURE,
3949 G_MODULE_PREFIX || l_procedure_name,
3950 'Begin get_inv_item_model');
3951 log(FND_LOG.LEVEL_PROCEDURE,
3952 G_MODULE_PREFIX || l_procedure_name,
3953 'p_item_master_org_id:' || p_item_master_org_id);
3954 log(FND_LOG.LEVEL_PROCEDURE,
3955 G_MODULE_PREFIX || l_procedure_name,
3956 'p_inventory_item_id:' || p_inventory_item_id);
3957 log(FND_LOG.LEVEL_PROCEDURE,
3958 G_MODULE_PREFIX || l_procedure_name,
3959 'p_attribute_column:' || p_attribute_column);
3960 END IF;
3961 -- end log
3962
3963 IF p_inventory_item_id IS NOT NULL THEN
3964
3965 BEGIN
3966 SELECT attribute1,
3967 attribute2,
3968 attribute3,
3969 attribute4,
3970 attribute5,
3971 attribute6,
3972 attribute7,
3973 attribute8,
3974 attribute9,
3975 attribute10,
3976 attribute11,
3977 attribute12,
3978 attribute13,
3979 attribute14,
3980 attribute15,
3981 attribute16,
3982 attribute17,
3983 attribute18,
3984 attribute19,
3985 attribute20,
3986 attribute21,
3987 attribute22,
3988 attribute23,
3989 attribute24,
3990 attribute25,
3991 attribute26,
3992 attribute27,
3993 attribute28,
3994 attribute29,
3995 attribute30
3996 INTO l_inventory_attribute1,
3997 l_inventory_attribute2,
3998 l_inventory_attribute3,
3999 l_inventory_attribute4,
4000 l_inventory_attribute5,
4001 l_inventory_attribute6,
4002 l_inventory_attribute7,
4003 l_inventory_attribute8,
4004 l_inventory_attribute9,
4005 l_inventory_attribute10,
4006 l_inventory_attribute11,
4007 l_inventory_attribute12,
4008 l_inventory_attribute13,
4009 l_inventory_attribute14,
4010 l_inventory_attribute15,
4011 l_inventory_attribute16,
4012 l_inventory_attribute17,
4013 l_inventory_attribute18,
4014 l_inventory_attribute19,
4015 l_inventory_attribute20,
4016 l_inventory_attribute21,
4017 l_inventory_attribute22,
4018 l_inventory_attribute23,
4019 l_inventory_attribute24,
4020 l_inventory_attribute25,
4021 l_inventory_attribute26,
4022 l_inventory_attribute27,
4023 l_inventory_attribute28,
4024 l_inventory_attribute29,
4025 l_inventory_attribute30
4026 FROM mtl_system_items_b
4027 WHERE organization_id = p_item_master_org_id--yao zhang modified for bug 7829039
4028 AND inventory_item_id = p_inventory_item_id;
4029 EXCEPTION
4030 WHEN no_data_found THEN
4034 'no date found ');
4031 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4032 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
4033 G_MODULE_PREFIX || l_procedure_name,
4035 END IF;
4036 RAISE;
4037 END;
4038
4039 ELSE
4040 -- return null
4041 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4042 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
4043 G_MODULE_PREFIX || l_procedure_name,
4044 'The item id is null ');
4045 log(FND_LOG.LEVEL_PROCEDURE,
4046 G_MODULE_PREFIX || l_procedure_name,
4047 l_procedure_name || ' The item id is null');
4048 END IF;
4049
4050 END IF;
4051
4052 IF p_attribute_column = 'ATTRIBUTE1' THEN
4053 x_attribute_value := l_inventory_attribute1;
4054 ELSIF p_attribute_column = 'ATTRIBUTE2' THEN
4055 x_attribute_value := l_inventory_attribute2;
4056 ELSIF p_attribute_column = 'ATTRIBUTE3' THEN
4057 x_attribute_value := l_inventory_attribute3;
4058 ELSIF p_attribute_column = 'ATTRIBUTE4' THEN
4059 x_attribute_value := l_inventory_attribute4;
4060 ELSIF p_attribute_column = 'ATTRIBUTE5' THEN
4061 x_attribute_value := l_inventory_attribute5;
4062 ELSIF p_attribute_column = 'ATTRIBUTE6' THEN
4063 x_attribute_value := l_inventory_attribute6;
4064 ELSIF p_attribute_column = 'ATTRIBUTE7' THEN
4065 x_attribute_value := l_inventory_attribute7;
4066 ELSIF p_attribute_column = 'ATTRIBUTE8' THEN
4067 x_attribute_value := l_inventory_attribute8;
4068 ELSIF p_attribute_column = 'ATTRIBUTE9' THEN
4069 x_attribute_value := l_inventory_attribute9;
4070 ELSIF p_attribute_column = 'ATTRIBUTE10' THEN
4071 x_attribute_value := l_inventory_attribute10;
4072 ELSIF p_attribute_column = 'ATTRIBUTE11' THEN
4073 x_attribute_value := l_inventory_attribute11;
4074 ELSIF p_attribute_column = 'ATTRIBUTE12' THEN
4075 x_attribute_value := l_inventory_attribute12;
4076 ELSIF p_attribute_column = 'ATTRIBUTE13' THEN
4077 x_attribute_value := l_inventory_attribute13;
4078 ELSIF p_attribute_column = 'ATTRIBUTE14' THEN
4079 x_attribute_value := l_inventory_attribute14;
4080 ELSIF p_attribute_column = 'ATTRIBUTE15' THEN
4081 x_attribute_value := l_inventory_attribute15;
4082 ELSIF p_attribute_column = 'ATTRIBUTE16' THEN
4083 x_attribute_value := l_inventory_attribute16;
4084 ELSIF p_attribute_column = 'ATTRIBUTE17' THEN
4085 x_attribute_value := l_inventory_attribute17;
4086 ELSIF p_attribute_column = 'ATTRIBUTE18' THEN
4087 x_attribute_value := l_inventory_attribute18;
4088 ELSIF p_attribute_column = 'ATTRIBUTE19' THEN
4089 x_attribute_value := l_inventory_attribute19;
4090 ELSIF p_attribute_column = 'ATTRIBUTE20' THEN
4091 x_attribute_value := l_inventory_attribute20;
4092 ELSIF p_attribute_column = 'ATTRIBUTE21' THEN
4093 x_attribute_value := l_inventory_attribute21;
4094 ELSIF p_attribute_column = 'ATTRIBUTE22' THEN
4095 x_attribute_value := l_inventory_attribute22;
4096 ELSIF p_attribute_column = 'ATTRIBUTE23' THEN
4097 x_attribute_value := l_inventory_attribute23;
4098 ELSIF p_attribute_column = 'ATTRIBUTE24' THEN
4099 x_attribute_value := l_inventory_attribute24;
4100 ELSIF p_attribute_column = 'ATTRIBUTE25' THEN
4101 x_attribute_value := l_inventory_attribute25;
4102 ELSIF p_attribute_column = 'ATTRIBUTE26' THEN
4103 x_attribute_value := l_inventory_attribute26;
4104 ELSIF p_attribute_column = 'ATTRIBUTE27' THEN
4105 x_attribute_value := l_inventory_attribute27;
4106 ELSIF p_attribute_column = 'ATTRIBUTE28' THEN
4107 x_attribute_value := l_inventory_attribute28;
4108 ELSIF p_attribute_column = 'ATTRIBUTE29' THEN
4109 x_attribute_value := l_inventory_attribute29;
4110 ELSIF p_attribute_column = 'ATTRIBUTE30' THEN
4111 x_attribute_value := l_inventory_attribute30;
4112 ELSE
4113
4114 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4115 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
4116 G_MODULE_PREFIX || l_procedure_name,
4117 'not found data in get_inv_item_model');
4118 END IF;
4119 END IF;
4120
4121 -- begin log
4122 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4123 log(FND_LOG.LEVEL_PROCEDURE,
4124 G_MODULE_PREFIX || l_procedure_name,
4125 'x_attribute_value:' || x_attribute_value);
4126 log(FND_LOG.LEVEL_PROCEDURE,
4127 G_MODULE_PREFIX || l_procedure_name,
4128 'End get_inv_item_model');
4129 END IF;
4130 -- end log
4131
4132 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4133 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
4134 G_MODULE_PREFIX || l_procedure_name,
4135 'END procedure. ');
4136 END IF;
4137
4138 EXCEPTION
4139 WHEN OTHERS THEN
4140 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4141 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
4142 G_MODULE_PREFIX || l_procedure_name ||
4143 '. OTHER_EXCEPTION ',
4144 Sqlcode || Sqlerrm);
4145 END IF;
4146 RAISE;
4147 END get_inv_item_model;
4148
4149 --==========================================================================
4150 -- PROCEDURE NAME:
4151 -- get_ra_item_model
4152 --
4153 -- DESCRIPTION:
4154 -- This procedure get_item model from ra line by ra_line_id and
4155 -- attribute_column. This procedure replace the dynamic sql
4156 --
4157 -- PARAMETERS:
4161
4158 -- p_ra_line_id IN NUMBER
4159 -- p_attribute_column IN VARCHAR2
4160 -- x_attribute_value OUT NOCOPY VARCHAR2
4162 -- DESIGN REFERENCES:
4163 -- GTA-TRANSFER-PROGRAM-TD.doc
4164 --
4165 -- CHANGE HISTORY:
4166 -- 20-APR-2005: Jim Zheng Created.
4167 --
4168 --===========================================================================
4169 PROCEDURE Get_Ra_Item_Model(p_ra_line_id IN NUMBER,
4170 p_attribute_column IN VARCHAR2,
4171 x_attribute_value OUT NOCOPY VARCHAR2) IS
4172 l_procedure_name VARCHAR2(50) := 'get_ra_item_model';
4173
4174 l_attribute1 ra_customer_trx_lines_all.Interface_Line_Attribute1%TYPE;
4175 l_attribute2 ra_customer_trx_lines_all.Interface_Line_Attribute2%TYPE;
4176 l_attribute3 ra_customer_trx_lines_all.Interface_Line_Attribute3%TYPE;
4177 l_attribute4 ra_customer_trx_lines_all.Interface_Line_Attribute4%TYPE;
4178 l_attribute5 ra_customer_trx_lines_all.Interface_Line_Attribute5%TYPE;
4179 l_attribute6 ra_customer_trx_lines_all.Interface_Line_Attribute6%TYPE;
4180 l_attribute7 ra_customer_trx_lines_all.Interface_Line_Attribute7%TYPE;
4181 l_attribute8 ra_customer_trx_lines_all.Interface_Line_Attribute8%TYPE;
4182 l_attribute9 ra_customer_trx_lines_all.Interface_Line_Attribute9%TYPE;
4183 l_attribute10 ra_customer_trx_lines_all.Interface_Line_Attribute10%TYPE;
4184 l_attribute11 ra_customer_trx_lines_all.Interface_Line_Attribute11%TYPE;
4185 l_attribute12 ra_customer_trx_lines_all.Interface_Line_Attribute12%TYPE;
4186 l_attribute13 ra_customer_trx_lines_all.Interface_Line_Attribute13%TYPE;
4187 l_attribute14 ra_customer_trx_lines_all.Interface_Line_Attribute14%TYPE;
4188 l_attribute15 ra_customer_trx_lines_all.Interface_Line_Attribute15%TYPE;
4189
4190 BEGIN
4191
4192 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4193 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
4194 G_MODULE_PREFIX || l_procedure_name,
4195 'Begin Procedure. ');
4196 END IF;
4197
4198 -- begin log
4199 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4200 log(FND_LOG.LEVEL_PROCEDURE,
4201 G_MODULE_PREFIX || l_procedure_name,
4202 'Begin Get_Ra_Item_Model');
4203 log(FND_LOG.LEVEL_PROCEDURE,
4204 G_MODULE_PREFIX || l_procedure_name,
4205 'p_ra_line_id:' || p_ra_line_id);
4206 log(FND_LOG.LEVEL_PROCEDURE,
4207 G_MODULE_PREFIX || l_procedure_name,
4208 'p_attribute_column:' || p_attribute_column);
4209 END IF;
4210 -- end log
4211
4212 BEGIN
4213 SELECT interface_line_attribute1,
4214 interface_line_attribute2,
4215 interface_line_attribute3,
4216 interface_line_attribute4,
4217 interface_line_attribute5,
4218 interface_line_attribute6,
4219 interface_line_attribute7,
4220 interface_line_attribute8,
4221 interface_line_attribute9,
4222 interface_line_attribute10,
4223 interface_line_attribute11,
4224 interface_line_attribute12,
4225 interface_line_attribute13,
4226 interface_line_attribute14,
4227 interface_line_attribute15
4228 INTO l_attribute1,
4229 l_attribute2,
4230 l_attribute3,
4231 l_attribute4,
4232 l_attribute5,
4233 l_attribute6,
4234 l_attribute7,
4235 l_attribute8,
4236 l_attribute9,
4237 l_attribute10,
4238 l_attribute11,
4239 l_attribute12,
4240 l_attribute13,
4241 l_attribute14,
4242 l_attribute15
4243 FROM ra_customer_trx_lines_all l
4244 WHERE l.customer_trx_line_id = p_ra_line_id;
4245
4246 EXCEPTION
4247 WHEN no_data_found THEN
4248 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4249 fnd_log.STRING(fnd_log.LEVEL_EXCEPTION,
4250 G_MODULE_PREFIX || l_procedure_name,
4251 'no date found ');
4252 END IF;
4253 RAISE;
4254 END;
4255
4256 IF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE1' THEN
4257 x_attribute_value := l_attribute1;
4258 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE2' THEN
4259 x_attribute_value := l_attribute2;
4260 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE3' THEN
4261 x_attribute_value := l_attribute3;
4262 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE4' THEN
4263 x_attribute_value := l_attribute4;
4264 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE5' THEN
4265 x_attribute_value := l_attribute5;
4266 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE6' THEN
4267 x_attribute_value := l_attribute6;
4268 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE7' THEN
4269 x_attribute_value := l_attribute7;
4270 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE8' THEN
4271 x_attribute_value := l_attribute8;
4272 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE9' THEN
4273 x_attribute_value := l_attribute9;
4274 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE10' THEN
4275 x_attribute_value := l_attribute10;
4276 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE11' THEN
4277 x_attribute_value := l_attribute11;
4278 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE12' THEN
4279 x_attribute_value := l_attribute12;
4280 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE13' THEN
4281 x_attribute_value := l_attribute13;
4285 x_attribute_value := l_attribute15;
4282 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE14' THEN
4283 x_attribute_value := l_attribute14;
4284 ELSIF p_attribute_column = 'INTERFACE_LINE_ATTRIBUTE15' THEN
4286 ELSE
4287 -- report a error
4288 x_attribute_value := NULL;
4289
4290 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4291 fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED,
4292 G_MODULE_PREFIX || l_procedure_name,
4293 'no data found in get_ra_item_model');
4294 END IF;
4295 END IF;
4296
4297 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4298 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
4299 G_MODULE_PREFIX || l_procedure_name,
4300 'END procedure. ');
4301 END IF;
4302
4303 -- begin log
4304 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4305 log(FND_LOG.LEVEL_PROCEDURE,
4306 G_MODULE_PREFIX || l_procedure_name,
4307 'End Get_Ra_Item_Model');
4308 END IF;
4309 -- end log
4310
4311 EXCEPTION
4312 WHEN OTHERS THEN
4313 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4314 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
4315 G_MODULE_PREFIX || l_procedure_name ||
4316 '. OTHER_EXCEPTION ',
4317 Sqlcode || Sqlerrm);
4318 END IF;
4319 RAISE;
4320 END get_ra_item_model;
4321
4322 --==========================================================================
4323 -- FUNCTION NAME:
4324 -- get_inventory_item_number
4325 --
4326 -- DESCRIPTION:
4327 -- This procedure get item number by inventory_item_id
4328 --
4329 -- PARAMETERS:
4330 -- p_inventory_item_id IN NUMBER
4331 -- p_org_id IN NUMBER
4332 -- x_inventory_item_code OUT NOCOPY VARCHAR2
4333 --
4334 -- DESIGN REFERENCES:
4335 -- GTA-TRANSFER-PROGRAM-TD.doc
4336 --
4337 -- CHANGE HISTORY:
4338 -- 20-APR-2005: Jim Zheng Created.
4339 -- 22/Jan/2009 Yao Zhang modified for bug 7829039
4340 --===========================================================================
4341 PROCEDURE Get_Inventory_Item_Number(p_inventory_item_id IN NUMBER,
4342 p_item_master_org_id IN NUMBER,--yao zhang changed fix bug 7829039
4343 x_inventory_item_code OUT NOCOPY VARCHAR2) IS
4344 l_inventory_item_code MTL_SYSTEM_ITEMS_B_KFV.concatenated_segments%TYPE;
4345 l_procedure_name VARCHAR2(50) := 'get_inventory_item_number';
4346 BEGIN
4347
4348 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4349 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
4350 G_MODULE_PREFIX || l_procedure_name,
4351 'Begin procedure. ');
4352 END IF;
4353
4354 -- begin log
4355 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4356 log(FND_LOG.LEVEL_PROCEDURE,
4357 G_MODULE_PREFIX || l_procedure_name,
4358 'Begin get_inventory_item_number');
4359 END IF;
4360 -- end log
4361
4362 IF p_inventory_item_id IS NULL THEN
4363 x_inventory_item_code := NULL;
4364 ELSE
4365 SELECT msv.concatenated_segments
4366 INTO l_inventory_item_code
4367 FROM MTL_SYSTEM_ITEMS_B_KFV msv
4368 WHERE msv.inventory_item_id = p_inventory_item_id--yao zhang changed fix bug 7829039
4369 AND msv.organization_id = p_item_master_org_id;
4370
4371 -- begin log
4372 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4373 log(FND_LOG.LEVEL_PROCEDURE,
4374 G_MODULE_PREFIX || l_procedure_name,
4375 'l_inventory_item_code:' || l_inventory_item_code);
4376 END IF;
4377 -- end log
4378
4379 x_inventory_item_code := l_inventory_item_code;
4380 END IF;
4381
4382 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4383 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
4384 G_MODULE_PREFIX || l_procedure_name,
4385 'END procedure. ');
4386 END IF;
4387
4388 -- begin log
4389 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4390 log(FND_LOG.LEVEL_PROCEDURE,
4391 G_MODULE_PREFIX || l_procedure_name,
4392 'End get_inventory_item_number');
4393 END IF;
4394 -- end log
4395
4396 EXCEPTION
4397 WHEN no_data_found THEN
4398 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4399 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
4400 G_MODULE_PREFIX || l_procedure_name ||
4401 '. OTHER_EXCEPTION ',
4402 'item_code not be found ' || Sqlcode || Sqlerrm);
4403 END IF;
4404 WHEN OTHERS THEN
4405 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4406 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
4407 G_MODULE_PREFIX || l_procedure_name ||
4408 '. OTHER_EXCEPTION ',
4409 Sqlcode || Sqlerrm);
4410 END IF;
4411 RAISE;
4412
4413 END get_inventory_item_number;
4414
4415 --==========================================================================
4416 -- FUNCTION NAME:
4417 -- get_uom_name
4418 --
4419 -- DESCRIPTION:
4420 -- This procedure get item number by inventory_item_id
4421 --
4422 -- PARAMETERS:
4423 -- p_uom_code IN VARCHAR2
4424 -- x_uom_name OUT NOCOPY VARCHAR2
4425 --
4426 -- DESIGN REFERENCES:
4427 -- GTA-TRANSFER-PROGRAM-TD.doc
4428 --
4429 -- CHANGE HISTORY:
4430 -- 11-Oct2005: Jim Zheng Created.
4431 --===========================================================================
4432 PROCEDURE get_uom_name(p_uom_code IN VARCHAR2,
4433 x_uom_name OUT NOCOPY VARCHAR2) IS
4434 l_unit_of_measure mtl_units_of_measure_tl.unit_of_measure%TYPE;
4435 l_procedure_name VARCHAR2(30) := 'get_uom_name';
4436 BEGIN
4437 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4438 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
4439 G_MODULE_PREFIX || l_procedure_name,
4440 'Begin procedure. ');
4441 END IF;
4442
4443 BEGIN
4444 SELECT uom.unit_of_measure
4445 INTO l_unit_of_measure
4446 FROM mtl_units_of_measure_tl uom
4447 WHERE uom.uom_code = p_uom_code
4448 AND uom.LANGUAGE = userenv('LANG');
4449
4450 EXCEPTION
4451 WHEN no_data_found THEN
4452 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4453 fnd_log.STRING(FND_LOG.LEVEL_EXCEPTION,
4454 G_MODULE_PREFIX || l_procedure_name,
4455 'no data found when select receiving_routing_id by line_location_id' ||
4456 SQLCODE || SQLERRM);
4457 END IF;
4458 RAISE;
4459 END;
4460
4461 x_uom_name := l_unit_of_measure;
4462
4463 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4464 fnd_log.STRING(fnd_log.LEVEL_PROCEDURE,
4465 G_MODULE_PREFIX || l_procedure_name,
4466 'END procedure. ');
4467 END IF;
4468
4469 EXCEPTION
4470
4471 WHEN OTHERS THEN
4472 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4473 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
4474 G_MODULE_PREFIX || l_procedure_name ||
4475 '. OTHER_EXCEPTION ',
4476 Sqlcode || Sqlerrm);
4477 END IF;
4478 RAISE;
4479 END;
4480
4481 END JMF_GTA_ARTRX_PROC;