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