1 PACKAGE BODY jai_cmn_st_forms_pkg AS
2 /* $Header: jai_cmn_st_forms.plb 120.15.12020000.5 2013/03/27 04:46:16 mmurtuza ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename: jai_cmn_st_forms.plb
6
7 Change History:
8 Date Bug Remarks
9 --------- ---------- -------------------------------------------------------------
10 08-Jun-2005 Version 116.2 jai_cmn_st_forms -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
11 as required for CASE COMPLAINCE.
12
13 13-Jun-2005 4428980 File Version: 116.3
14 Ramananda for bug#4428980. Removal of SQL LITERALs is done
15
16 24-Jun-2005 4454818 Ramananda for bug#4454818 File Version: 116.4
17 ST Forms Impact: Uptake of ap_invoice_lines_all
18
19 10-May-2006 4949400 Sanjikum for Bug#4929400, File Version 120.2
20 1) Modified the cursor - c_get_not_validated_count
21 2) Changed the definition of variable - v_not_validated_count from number to VARCHAR2(1)
22 02-Jul-2007 6118321 brathod,File Version 120.5
23 FP: 11.5.9 - 12.0:FORWARD PORTING FROM 115 BUG 5763527
24
25 23-Nov-2010 10196549 Description: When the customer_trx_id of AR Invoice and Delivery ID
26 of ISO is same the record is not processed. customer_trx_id for AR
27 Invoice and Delivery ID for ISO Transaction are populated into
28 invoice_id column of JAI_CMN_ST_FORM_DTLS
29 Fix: Added trx_number check in cursor c_fetch_records
30
31 12-Jul-2012 14277151 Description: India - ST Forms Issue Processing does not pick up Invoices if they are
32 matched to a receipt with CST Taxes (PO corresponding to the Receipt does not have CST Taxes)
33 Fix: Tax Target Amount is calculated based on the Precedence of CST Taxes in PO.
34 Hence if the PO does not have CST Taxes and the Receipt has CST Taxes then Precedence
35 is not returned resulting in NULL Tax Target Amount. Hence eth Invoice is not picked for processing.
36 Added code to pick precedence from Receipt if PO does not have CST Taxes.
37
38 09-Aug-2012 14183827 Description: TAX TARGET AMOUNT IS INCORRECT FOR INCLUSIVE TAX
39 Fix: Modified cursor c_get_non_zero_precedence_amt to fetch tax data only if not an inclusive tax.
40 --------------------------------------------------------------------------------------*/
41
42 PROCEDURE generate_ap_forms
43 (
44 p_err_buf OUT NOCOPY varchar2,
45 P_ret_code OUT NOCOPY varchar2,
46 p_org_id IN number,
47 p_vendor_id IN number,
48 p_vendor_site_id IN number,
49 p_invoice_from_date IN date,
50 p_invoice_to_date IN date,
51 P_reprocess IN varchar2
52 )
53 is
54
55 cursor c_get_distrib_tax_details (p_invoice_id number, p_line_number number) IS /* p_distribution_no --uptake of ap_invoice_lines_all */
56 select tax_id, line_location_id, po_header_id, po_line_id, tax_amount,
57 parent_invoice_line_number --parent_invoice_distribution_id /* uptake of ap_invoice_lines_all */
58 ,recoverable_flag -- 5763527
59 from JAI_AP_MATCH_INV_TAXES
60 where invoice_id = p_invoice_id
61 and invoice_line_number = p_line_number; /* uptake of ap_invoice_lines_all */
62 /* and distribution_line_number = p_distribution_no;*/
63
64 cursor c_get_tax_details (p_tax_id number) is
65 select tax_type, tax_rate, stform_type
66 , mod_cr_percentage -- 5763527
67 from JAI_CMN_TAXES_ALL
68 where tax_id = p_tax_id;
69
70 v_mod_cr_pctg JAI_CMN_TAXES_ALL.mod_cr_percentage%type ;-- 5763527
71 lv_recoverable_flag JAI_AP_MATCH_INV_TAXES.recoverable_flag%type ;-- 5763527
72
73
74 cursor c_get_ven_info(p_invoice_id number) is
75 select vendor_id, vendor_site_id, org_id
76 from ap_invoices_all
77 where invoice_id = p_invoice_id;
78
79 cursor c_get_st_hdr_id(p_stform_type varchar2, p_vendor_id number, p_vendor_site_id number, p_org_id number) is
80 select st_hdr_id
81 from JAI_CMN_STFORM_HDRS_ALL
82 where party_type_flag = 'V'
83 and party_id = p_vendor_id
84 and party_site_id = p_vendor_site_id
85 and form_type = p_stform_type
86 and org_id = p_org_id;
87
88 cursor c_get_po_num(p_po_header_id number) is
89 select segment1, type_lookup_code
90 from po_headers_all
91 where po_header_id = p_po_header_id;
92
93 cursor c_get_focus_id(p_line_location_id number, p_line_id number) is
94 select line_focus_id
95 from JAI_PO_LINE_LOCATIONS
96 where line_location_id = p_line_location_id
97 and po_line_id = p_line_id;
98
99 cursor c_get_tax_ln_no_receipt (p_rcv_transaction_id number, p_tax_id number) is
100 select tax_line_no
101 from JAI_RCV_LINE_TAXES
102 where (shipment_header_id, shipment_line_id)
103 in
104 (select shipment_header_id, shipment_line_id
105 from rcv_transactions
106 where transaction_id = p_rcv_transaction_id
107 )
108 and tax_id = p_tax_id;
109
110 cursor get_tax_ln_no_po(p_po_line_location_id number, p_tax_id number) is
111 select tax_line_no
112 from JAI_PO_TAXES
113 where tax_id = p_tax_id
114 and line_location_id = p_po_line_location_id;
115
116 cursor c_get_match_org_loc(p_po_line_location_id number) is
117 select match_option, ship_to_organization_id, ship_to_location_id
118 from po_line_locations_all
119 where line_location_id = p_po_line_location_id;
120
121 /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
122 cursor c_get_rcv_transaction_id(p_invoice_id number, p_po_distribution_id number, cp_lt_lookup_code ap_invoice_lines_all.line_type_lookup_code%type ) is
123 select rcv_transaction_id
124 from ap_invoice_lines_all --ap_invoice_distributions_all /* uptake of ap_invoice_lines_all */
125 where invoice_id = p_invoice_id
126 and line_type_lookup_code = cp_lt_lookup_code --'ITEM'
127 and po_distribution_id = p_po_distribution_id;
128
129 cursor c_get_not_validated_count (p_invoice_id number) is
130 /*select count(1)
131 from ap_invoice_distributions_all
132 where invoice_id = p_invoice_id
133 and nvl(match_status_flag, 'N') <> 'A';*/
134 --commented the above and added the below by Sanjikum for Bug#4929400
135 SELECT 'Y'
136 FROM dual
137 WHERE exists (select '1'
138 from ap_invoice_distributions_all
139 where invoice_id = p_invoice_id
140 and nvl(match_status_flag, 'N') <> 'A');
141
142
143
144 v_tax_id JAI_CMN_TAXES_ALL.tax_id%type;
145 v_tax_type JAI_CMN_TAXES_ALL.tax_type%type;
146 v_tax_rate JAI_CMN_TAXES_ALL.tax_rate%type;
147 v_stform_type JAI_CMN_TAXES_ALL.stform_type%type;
148
149 v_vendor_id ap_invoices_all.vendor_id%type;
150 v_vendor_site_id ap_invoices_all.vendor_site_id%type;
151 v_org_id ap_invoices_all.org_id%type;
152
153 /*
154 Variable declaration changed by aiyer for the bug #3249375.
155 Changed the variable declaration from reference to ja_in_po_st_forms_hdr.form_issue_id to JAI_CMN_STFORM_HDRS_ALL.st_hdr_id.
156 This was required as the table ja_in_po_st_forms_hdr has been obsoleted.
157 */
158 v_st_hdr_id JAI_CMN_STFORM_HDRS_ALL.st_hdr_id%type;
159 v_po_line_location_id JAI_AP_MATCH_INV_TAXES.line_location_id%type;
160 v_po_header_id JAI_AP_MATCH_INV_TAXES.po_header_id%type;
161 v_po_num po_headers_all.segment1%type;
162 v_po_line_id JAI_AP_MATCH_INV_TAXES.po_line_id%type;
163 v_tax_line_no JAI_PO_TAXES.tax_line_no%type;
164 v_match_option po_line_locations_all.match_option%type;
165 v_ship_to_organization_id po_line_locations_all.ship_to_organization_id%type;
166 v_ship_to_location_id po_line_locations_all.ship_to_location_id%type;
167 v_type_lookup_code po_headers_all.type_lookup_code%type;
168 v_tax_amount JAI_AP_MATCH_INV_TAXES.tax_amount%type;
169 v_doc_type varchar2(10);
170 v_tax_target_amount number;
171 v_st_dtl_id number;
172 v_rcv_transaction_id ap_invoice_distributions_all.rcv_transaction_id%type;
173 v_parent_distribution_id ap_invoice_distributions_all.invoice_distribution_id%type;
174 v_debug char(1); -- := 'Y'; --Ramananda for File.Sql.35
175 v_uid number;
176
177 v_invoice_error_flag char(1);
178 v_error_flag char(1);
179
180 v_processed_inv_cnt number;
181 v_error_inv_cnt number;
182 v_st_lines_for_inv number;
183 v_error_message varchar2(300);
184
185 --v_not_validated_count number;
186 --commented the above and added the below by Sanjikum
187 v_not_validated_count VARCHAR2(1);
188
189 v_invoice_process_flag char(1);
190 v_not_processed_inv_cnt number;
191
192 lv_lt_lookup_code ap_invoice_distributions_all.line_type_lookup_code%type ;
193 lv_lt_inclu_lookup_code ap_invoice_distributions_all.line_type_lookup_code%type ; -- Added by Jia for Bug#9535954
194 v_parent_invoice_line_number JAI_AP_MATCH_INV_TAXES.parent_invoice_line_number%type ;
195
196
197 function getSTformsTaxBaseAmount /* uptake of ap_invoice_lines_all*/
198 (
199 p_invoice_id number,
200 p_line_number number, --p_invoice_distribution_id number,
201 p_tax_id number,
202 p_tax_amount number, -- bug#3094025
203 p_tax_rate number -- bug#3094025
204 )
205 return number is
206
207 v_po_distribution_id number;
208 v_po_line_location_id number;
209 v_po_header_id number;
210 v_po_line_id number;
211 v_rcv_transaction_id number;
212 v_precedence_1 number;
213 v_precedence_2 number;
214 v_precedence_3 number;
215 v_precedence_4 number;
216 v_precedence_5 number;
217 v_precedence_0 number;
218 v_precedence_non_0 number;
219
220 v_tax_base_amt number;
221 v_tax_base_amt_loop number; /*Added by nprashar for bug # 9830853*/
222 v_set_of_books_id ap_invoices_all.set_of_books_id%type;
223 v_invoice_currency_code ap_invoices_all.invoice_currency_code%type;
224 v_exchange_date ap_invoices_all.exchange_date%type;
225 v_exchange_rate_type ap_invoices_all.exchange_rate_type%type;
226 v_exchange_rate ap_invoices_all.exchange_rate%type;
227
228 v_invoice_distribution_id ap_invoice_distributions_all.invoice_distribution_id%type;
229
230 v_line_number ap_invoice_lines_all.line_number%type ;
231 v_match_type ap_invoice_lines_all.match_type%type; /*Bug 14277151*/
232
233 Type v_prec_check is table of number index by binary_integer; /*Added by nprashar for bug # 9830853*/
234 p_prec_check v_prec_check;
235
236 cursor c_get_invoice_currency_dtl is
237 select set_of_books_id, invoice_currency_code,exchange_date, exchange_rate_type, exchange_rate
238 from ap_invoices_all
239 where invoice_id = p_invoice_id;
240
241 /* uptake of ap_invoice_lines_all */
242 /*Bug 14277151 - Added Match Type*/
243 cursor c_get_inv_dist_details is
244 select line_number, po_distribution_id, rcv_transaction_id, amount, match_type /* invoice_distribution_id*/
245 from ap_invoice_lines_all --ap_invoice_distributions_all
246 where invoice_id = p_invoice_id
247 and line_number = p_line_number ;
248 /* and invoice_distribution_id = p_invoice_distribution_id ; */
249
250 cursor c_get_po_details (p_po_distribution_id number) is
251 select po_header_id, po_line_id, line_location_id
252 from po_distributions_all
253 where po_distribution_id = p_po_distribution_id;
254
255 /*Cursor Added by nprashar for bug # 12561167 */
256 cursor c_get_po_details_rcv (p_invoice_id_match number) is
257 select distinct po_header_id, po_line_id, line_location_id
258 from jai_ap_match_inv_taxes
259 where invoice_id = p_invoice_id_match
260 and parent_invoice_line_number = v_line_number;
261
262 -- precedences are always available in po taxes only (receipt taxes does not have it)
263 cursor c_get_tax_precedence (p_po_line_location_id number) is
264 select precedence_1, precedence_2 , precedence_3, precedence_4, precedence_5
265 from JAI_PO_TAXES
266 where tax_id = p_tax_id
267 and line_location_id = p_po_line_location_id;
268
269 /*Bug 14277151 - Start*/
270 cursor c_get_tax_precedence_rcv (p_rcv_transaction_id NUMBER)
271 IS
272 SELECT precedence_1, precedence_2, precedence_3, precedence_4, precedence_5
273 FROM jai_rcv_line_taxes
274 WHERE shipment_line_id IN (SELECT shipment_line_id
275 FROM RCV_TRANSACTIONS
276 WHERE transaction_id = p_rcv_transaction_id)
277 AND tax_id = p_tax_id;
278 /*Bug 14277151 - End*/
279
280 /* Modified by Ramananda for bug# due to uptake of ap_invoice_lines_all */
281 cursor c_get_non_zero_precedence_amt
282 (
283 p_precedence number,
284 /* p_precedence_2 number,
285 p_precedence_3 number,
286 p_precedence_4 number,
287 p_precedence_5 number,Commented by nprashar for bug # 9830853*/
288 p_po_header_id number,
289 p_po_line_id number,
290 p_line_location_id number,
291 p_po_distribution_id number,
292 p_parent_line_number number
293 )
294 is
295 select amount
296 from ap_invoice_lines_all
297 where invoice_id = p_invoice_id
298 and line_number =
299 ( select jmit.invoice_line_number
300 from JAI_AP_MATCH_INV_TAXES jmit
301 where jmit.invoice_id = p_invoice_id
302 and jmit.po_header_id = p_po_header_id
303 and jmit.po_line_id = p_po_line_id
304 and jmit.line_location_id = p_line_location_id
305 and nvl(jmit.po_distribution_id,1) = nvl(p_po_distribution_id,nvl(jmit.po_distribution_id,1)) /*Added by nprashar for bug # 12561167 */
306 and jmit.parent_invoice_line_number = p_parent_line_number
307 and jmit.tax_id = (select jpt.tax_id from JAI_PO_TAXES jpt
308 , jai_cmn_taxes_all jcta -- for bug 14183827 by anupgupt
309 Where jpt.line_location_id = p_line_location_id
310 and jpt.tax_line_no = p_precedence
311 and jpt.tax_id = jcta.tax_id -- for bug 14183827 by anupgupt
312 and nvl(jcta.inclusive_tax_flag,'N') <> 'Y' -- for bug 14183827 by anupgupt
313 and jpt.tax_id = jmit.tax_id));
314 /*in Commented by nprashar for bug # 9830853
315 (
316 select tax_id
317 from JAI_PO_TAXES
318 where line_location_id = p_line_location_id
319 and tax_line_no in
320 (p_precedence_1, p_precedence_2, p_precedence_3, p_precedence_4, p_precedence_5)
321 )
322 );*/
323
324 /*Cursor added by nprashar for bug #6043559, FP changes of bug # 5999535*/
325 CURSOR c_receipt_base_amt ( c_invoice_id NUMBER,
326 c_invoice_distribution_id NUMBER,
327 c_po_header_id NUMBER,
328 c_po_line_id NUMBER,
329 c_line_location_id NUMBER,
330 c_po_distribution_id NUMBER)
331 IS
332 select SUM(base_amount)
333 from jai_ap_match_inv_taxes
334 where invoice_id = c_invoice_id
335 and parent_invoice_distribution_id = c_invoice_distribution_id
336 and po_header_id = c_po_header_id
337 and po_line_id = c_po_line_id
338 and line_location_id = c_line_location_id
339 and nvl(po_distribution_id,1) = nvl(c_po_distribution_id,nvl(po_distribution_id,1)); /*Added NVL condition for bug # 12561167 */
340
341 begin
342 Fnd_File.put_line(Fnd_File.LOG, ' Start of getSTformsTaxBaseAmount');
343
344 open c_get_inv_dist_details;
345 fetch c_get_inv_dist_details into v_line_number, v_po_distribution_id, v_rcv_transaction_id, v_precedence_0, v_match_type;
346 --fetch c_get_inv_dist_details into v_invoice_distribution_id, v_po_distribution_id, v_rcv_transaction_id, v_precedence_0;
347 close c_get_inv_dist_details;
348
349 Fnd_File.put_line(Fnd_File.LOG, ' 0 precedence amount : ' || v_precedence_0 );
350
351 If v_po_distribution_id is not null then /*Added by nprashar for bug # 12561167 */
352 open c_get_po_details(v_po_distribution_id);
353 fetch c_get_po_details into v_po_header_id, v_po_line_id, v_po_line_location_id;
354 close c_get_po_details;
355 Else
356 Open c_get_po_details_rcv(p_invoice_id); /*Added by nprashar for bug # 12561167 */
357 fetch c_get_po_details_rcv into v_po_header_id, v_po_line_id, v_po_line_location_id;
358 Close c_get_po_details_rcv;
359 End IF;
360
361 open c_get_tax_precedence(v_po_line_location_id);
362 fetch c_get_tax_precedence into
363 v_precedence_1, v_precedence_2, v_precedence_3, v_precedence_4, v_precedence_5;
364 close c_get_tax_precedence;
365
366 /*Bug 14277151 - Start*/
367 IF v_match_type = 'ITEM_TO_RECEIPT'
368 AND v_rcv_transaction_id IS NOT NULL
369 THEN
370 OPEN c_get_tax_precedence_rcv(v_rcv_transaction_id);
371 FETCH c_get_tax_precedence_rcv INTO v_precedence_1, v_precedence_2, v_precedence_3, v_precedence_4, v_precedence_5;
372 CLOSE c_get_tax_precedence_rcv;
373 END IF;
374 /*Bug 14277151 - End*/
375
376 v_tax_base_amt := 0; /*Added by nprashar for bug # 9830853*/
377 -- following if was added by Aparajita while fixing bug#3094025.
378 -- It was observed that the 0 precedence amount was getting added always to tax base even if precedence 0 does
379 -- not exist.
380
381 if v_precedence_1 = 0 or v_precedence_2 = 0 or v_precedence_3 = 0 or v_precedence_4 = 0 or v_precedence_5 = 0 then
382 v_tax_base_amt := nvl(v_precedence_0, 0);
383 end if;
384
385 /*Addition start for bug # 9830853 by nprashar*/
386 p_prec_check(1) := v_precedence_1;
387 p_prec_check(2) := v_precedence_2;
388 p_prec_check(3) := v_precedence_3;
389 p_prec_check(4) := v_precedence_4;
390 p_prec_check(5) := v_precedence_5;
391
392 For p_value_check in p_prec_check.first .. p_prec_check.last
393 Loop /*Start of Loop*/
394 v_tax_base_amt_loop := 0;
395 If p_prec_check(p_value_check) <> 0 Then
396 Open c_get_non_zero_precedence_amt (p_prec_check(p_value_check),v_po_header_id, v_po_line_id,
397 v_po_line_location_id, v_po_distribution_id,v_line_number);
398 Fetch c_get_non_zero_precedence_amt into v_tax_base_amt_loop;
399 Close c_get_non_zero_precedence_amt;
400
401 IF nvl(v_tax_base_amt_loop,0) = 0 Then /*Tax id present in JAI_AP_MATCH_INV_TAXES
402 is not present in jai_po_taxes*/
403 Begin /*Added Begin End section for bug # 12561167 */
404 select jamit.tax_amount into v_tax_base_amt_loop
405 from JAI_AP_MATCH_INV_TAXES jamit
406 , jai_cmn_taxes_all jcta -- for bug 14183827 by anupgupt
407 Where jamit.invoice_id = p_invoice_id
408 and jamit.po_header_id = v_po_header_id
409 and jamit.po_line_id = v_po_line_id
410 and jamit.line_location_id = v_po_line_location_id
411 and nvl(jamit.po_distribution_id,1) = nvl(v_po_distribution_id,nvl(jamit.po_distribution_id,1)) /*Added by nprashar for bug # 12561167 */
412 and jamit.parent_invoice_line_number = v_line_number
413 and jamit.line_no = p_prec_check(p_value_check)
414 and jamit.tax_id = jcta.tax_id -- for bug 14183827 by anupgupt
415 and nvl(jcta.inclusive_tax_flag,'N') <> 'Y'; -- for bug 14183827 by anupgupt
416 Exception
417 When others Then
418 v_tax_base_amt_loop := 0;
419 End;
420 End If; /*End of Inner IF*/
421
422 v_tax_base_amt := v_tax_base_amt + v_tax_base_amt_loop;
423 End If; /*End of Outer IF*/
424 End Loop; /*End Of Loop*/
425 /*Ends here for bug # 9830853*/
426
427 /*Commented code by nprashar for bug # 9830853
428 open c_get_non_zero_precedence_amt
429 (v_precedence_1, v_precedence_2, v_precedence_3, v_precedence_4, v_precedence_5,
430 v_po_header_id, v_po_line_id, v_po_line_location_id, v_po_distribution_id,
431 v_line_number); --v_invoice_distribution_id);
432
433 fetch c_get_non_zero_precedence_amt into v_precedence_non_0;
434 close c_get_non_zero_precedence_amt;
435
436
437 v_tax_base_amt := v_tax_base_amt + nvl(v_precedence_non_0, 0);*/
438
439 open c_get_invoice_currency_dtl;
440 fetch c_get_invoice_currency_dtl into
441 v_set_of_books_id, v_invoice_currency_code, v_exchange_date, v_exchange_rate_type, v_exchange_rate;
442 close c_get_invoice_currency_dtl;
443
444 if v_invoice_currency_code <> 'INR' then
445 v_exchange_rate := jai_cmn_utils_pkg.currency_conversion(v_set_of_books_id, v_invoice_currency_code,
446 v_exchange_date, v_exchange_rate_type, v_exchange_rate);
447
448 v_tax_base_amt := v_tax_base_amt * v_exchange_rate;
449
450 end if;
451
452 v_tax_base_amt := round(v_tax_base_amt, 2);
453
454 --start addded by Aparajita for bug#3094025
455 if nvl(v_tax_base_amt, 0) = 0 then
456 -- the tax may no be there at po, have to calculate back if it is from receipt.
457 -- as precedence is not available
458 /*Commenting Starts Fnd_File.put_line(Fnd_File.LOG, ' Calculated tax base backward from tax amount and rate as PO tax details not found ');
459 v_tax_base_amt := ((p_tax_amount * 100) / p_tax_rate); Commenting Ends*/
460 /*Code added by nprashar for bug #6043559, FP changes of bug # 5999535*/
461 OPEN c_receipt_base_amt (c_invoice_id => p_invoice_id,
462 c_invoice_distribution_id => p_line_number,
463 c_po_header_id=> v_po_header_id,
464 c_po_line_id => v_po_line_id,
465 c_line_location_id => v_po_line_location_id,
466 c_po_distribution_id => v_po_distribution_id);
467 FETCH c_receipt_base_amt INTO v_tax_base_amt;
468 CLOSE c_receipt_base_amt;
469 Fnd_File.put_line(Fnd_File.LOG, ' Calculated tax base from receipt taxes '); /*Ends here*/
470 end if;
471 --End addded by Aparajita for bug#3094025
472
473
474 Fnd_File.put_line(Fnd_File.LOG, ' End of getSTformsTaxBaseAmount : ' || v_tax_base_amt);
475 return v_tax_base_amt;
476
477 exception
478 when others then
479 Fnd_File.put_line(Fnd_File.LOG, ' Error in getting tax base amount for ST forrms :' || sqlerrm);
480 Fnd_File.put_line(Fnd_File.LOG, ' Populated st forms tax target amount as 0');
481 return 0;
482 end getSTformsTaxBaseAmount;
483 -- end added by Aparajita for bug#3038566;
484
485
486
487 begin -- main
488
489 /*------------------------------------------------------------------------------------------
490 FILENAME: jai_cmn_st_forms_pkg.generate_ap_forms_p.sql
491 CHANGE HISTORY:
492
493 S.No Date Author and Details
494 1 17-oct-03 Aparajita for bug#3193849. Version#616.1
495
496 Created this procedure.
497
498 This procedure processes the invoices in the given period and
499 populates the ST forms records wherever applicable.
500
501 It checks the invoice distribution lines of type 'MISCLEEANEOUS'
502 for tax lines by localization for sales tax of having forms attached.
503
504 All the invoices as per the criteria provided are considered. If an invoice is
505 cancelled, this program does not process the invoice. Program checks for processed
506 line if any for such invoice, if found and if no form has been issued for such a line,
507 the line is deleted. Similar processing is also done for an invoice that is not validated.
508
509 If the option of re-process is given as Yes, every invoice is checked for already
510 processed line that is not issued and is deleted and processed again.
511
512
513 The distribution lines which are reversed are not considered. Only the lines processed
514 by localization are considered for this processing.
515
516
517 2. 11-Nov-2003 Aiyer - Bug #3249375 File Version 617.1
518 Changed the variable declaration from reference to ja_in_po_st_forms_hdr.form_issue_id to JAI_CMN_STFORM_HDRS_ALL.st_hdr_id.
519 This was required as the table ja_in_po_st_forms_hdr has been obsoleted.
520 As this table does not exist in the database any more, post application of IN60105D1 patchset hence deleting
521 the reference .
522
523 Dependency Due to This Bug:-
524 Can be applied only post application of IN60105D1.
525
526 3. 29-JULY-2008 JMEENA for bug#7214273
527 Added NVL with variable v_not_validated_count and reset to N before processing.
528
529 4. 19-Nov-2008 Changes by nprashar for bug # 6043559, FP changes of bug 5999535.
530 Changes done in procedure - process_ar_st_forms.process_ap_st_forms
531 Here added a new cursor - c_receipt_base_amt and added the code to open/fetch/close the same
532
533 5. 15-Jul-2010 Changes by Jia for bug#9535954
534 Issue: the ST form should not consider the inclusive tax
535 Fixed: Changes done in procedure - generate_ap_forms
536 modified the cursor - c_inv_distributions
537
538 Future Dependencies For the release Of this Object:-
539 ==================================================
540 (Please add a row in the section below only if your bug introduces a dependency due to
541 spec change/A new call to a object/A datamodel change)
542
543 ----------------------------------------------------------------------------------------------------------------------------------------------------
544 Current Version Current Bug Dependent Files Version Author Date Remarks
545 Of File On Bug/Patchset Dependent On
546 jai_cmn_st_forms_pkg.generate_ap_forms
547 ----------------------------------------------------------------------------------------------------------------------------------------------------
548
549 617.1 3249375 IN60105D1 Aiyer 11/Nov/2003 Can be applied only after IN60105D1 patchset
550 has been applied.
551
552
553 ------------------------------------------------------------------------------------------------------- */
554 v_debug := jai_constants.yes ; --Ramananda for File.Sql.35
555
556 if v_debug = 'Y' then
557 Fnd_File.put_line(Fnd_File.LOG, '**** ========================================== ****');
558 Fnd_File.put_line(Fnd_File.LOG, '**** Start procedure - jai_cmn_st_forms_pkg.generate_ap_forms ****');
559 end if;
560
561 v_uid := fnd_global.USER_ID;
562
563 v_error_flag := 'N';
564
565 v_processed_inv_cnt := 0;
566 v_error_inv_cnt := 0;
567 v_not_processed_inv_cnt := 0;
568
569 for c_invoices in
570 (
571 select invoice_id, invoice_num, cancelled_date
572 from ap_invoices_all a
573 where invoice_date between trunc(p_invoice_from_date) and trunc(p_invoice_to_date)
574 and ( (p_org_id is null) or (p_org_id is not null and org_id = p_org_id) )
575 and ( (p_vendor_id is null) or (p_vendor_id is not null and vendor_id = p_vendor_id) )
576 and ( (p_vendor_site_id is null)
577 or
578 (p_vendor_site_id is not null and vendor_site_id = p_vendor_site_id)
579 )
580 and exists (select '1'
581 from JAI_AP_MATCH_INV_TAXES
582 where invoice_id = a.invoice_id
583 and tax_id in (select tax_id from JAI_CMN_TAXES_ALL where stform_type is not null)
584 ) -- to ensure that loc taxes exists for the invoice and are of st forms type.
585
586 order by invoice_date asc
587 )
588 loop
589
590 begin
591
592 if v_debug = 'Y' then
593 Fnd_File.put_line(Fnd_File.LOG, ' ** Processing invoice (id) : '
594 || c_invoices.invoice_num || '('
595 || c_invoices.invoice_id || '}' );
596 end if;
597
598 v_invoice_process_flag := 'Y'; -- by default consider that invoice should be processed.
599
600 if c_invoices.cancelled_date is not null then
601
602 -- invoice is cancelled, delete any not issued ST forms record for the invoice.
603
604 v_invoice_process_flag := 'N'; -- this invoice should not be processed.
605 v_not_processed_inv_cnt := v_not_processed_inv_cnt + 1;
606
607 if v_debug = 'Y' then
608 Fnd_File.put_line(Fnd_File.LOG, ' Invoice is Cancelled - not processing' );
609 end if;
610
611
612 delete JAI_CMN_ST_FORM_DTLS a
613 where invoice_id = c_invoices.invoice_id
614 and issue_receipt_flag = 'I'
615 and not exists (select '1'
616 from JAI_CMN_ST_MATCH_DTLS
617 where st_hdr_id = a.st_hdr_id
618 and st_dtl_id = a.st_dtl_id
619 );
620
621 if v_debug = 'Y' then
622 Fnd_File.put_line(Fnd_File.LOG,
623 ' No of unmatched records deleetd from st forms for this invoice :'
624 || to_char(sql%rowcount) );
625 end if;
626
627 goto continue_with_next_inv;
628
629 end if ; -- invoice is cancelled.
630
631
632
633 -- control comes here only if invoice is not processed.
634 -- check if invoice is not validated.
635
636 --v_not_validated_count := 0; --commented by Sanjikum for Bug#4929400
637 v_not_validated_count:= 'N'; --Added for bug#7214273
638 open c_get_not_validated_count(c_invoices.invoice_id );
639 fetch c_get_not_validated_count into v_not_validated_count;
640 close c_get_not_validated_count;
641
642 --if v_not_validated_count > 0 then
643 --commented the above and added the below by Sanjikum for Bug#4929400
644 if NVL(v_not_validated_count,'N') = 'Y' then --Added NVL for bug#7214273
645
646 -- invoice is not validated
647
648 v_invoice_process_flag := 'N'; -- this invoice should not be processed.
649 v_not_processed_inv_cnt := v_not_processed_inv_cnt + 1;
650
651 if v_debug = 'Y' then
652 Fnd_File.put_line(Fnd_File.LOG, ' Invoice is not validated - not processing' );
653 end if;
654
655
656 delete JAI_CMN_ST_FORM_DTLS a
657 where invoice_id = c_invoices.invoice_id
658 and issue_receipt_flag = 'I'
659 and not exists (select '1'
660 from JAI_CMN_ST_MATCH_DTLS
661 where st_hdr_id = a.st_hdr_id
662 and st_dtl_id = a.st_dtl_id
663 );
664
665 if v_debug = 'Y' then
666 Fnd_File.put_line(Fnd_File.LOG,
667 ' No of unmatched records deleted from st forms for this invoice :'
668 || to_char(sql%rowcount) );
669 end if;
670
671 goto continue_with_next_inv;
672
673 end if; -- invoice is not validated
674
675
676 -- control comes here only if the invoice is not cancelled and is in a validated stage.
677 -- check if re-process option has been chosen.
678
679 if p_reprocess = 'Y' then
680
681 -- program should re - process all invoices as per the given input.
682 -- if invoice has already been processed then flush the st form records
683 -- for the invoice where no matching has been done.
684
685 delete JAI_CMN_ST_FORM_DTLS a
686 where invoice_id = c_invoices.invoice_id
687 and issue_receipt_flag = 'I'
688 and not exists (select '1'
689 from JAI_CMN_ST_MATCH_DTLS
690 where st_hdr_id = a.st_hdr_id
691 and st_dtl_id = a.st_dtl_id
692 );
693
694 if v_debug = 'Y' then
695 Fnd_File.put_line(Fnd_File.LOG,
696 ' Re-Process : No of unmatched records purged from st forms for this invoice :'
697 || to_char(sql%rowcount) );
698 end if;
699
700 end if; -- p_reprocess = 'Y'
701
702
703 v_st_lines_for_inv := 0;
704 v_error_message := null;
705
706 lv_lt_lookup_code := 'MISCELLANEOUS' ;
707 lv_lt_inclu_lookup_code := 'ITEM' ;
708
709 /* uptake of ap_invoice_lines_all */
710 for c_inv_distributions in
711 (
712 select invoice_id,
713 line_number, --distribution_line_number
714 --invoice_distribution_id,
715 po_distribution_id,
716 rcv_transaction_id
717 from ap_invoice_lines_all b --ap_invoice_distributions_all b
718 where invoice_id = c_invoices.invoice_id
719 and line_type_lookup_code = lv_lt_lookup_code --'MISCELLANEOUS' -- only tax lines /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
720 /* and nvl(reversal_flag, 'N') <> 'Y' */
721 /*Commented this condition for bug # 12561167 and po_distribution_id is not null*/
722 and not exists
723 (select '1'
724 from JAI_CMN_ST_FORM_DTLS
725 where invoice_id = b.invoice_id
726 and invoice_line_number = b.line_number
727 --where invoice_distribution_id = b.invoice_distribution_id
728 and issue_receipt_flag = 'I'
729 ) -- to ensure that the line is not processed twice.
730 -- This should not happen as the invoice is already checked for prior processing.
731 and exists
732 (select '1'
733 from JAI_AP_MATCH_INV_TAXES
734 where invoice_id = b.invoice_id
735 --and distribution_line_number = b.distribution_line_number
736 and invoice_line_number = b.line_number
737 and tax_id in (select tax_id from JAI_CMN_TAXES_ALL where stform_type is not null)
738 ) -- to ensure that the line is an india local tax line for a st form type tax
739 -- Added by Jia for Bug#9535954, Begin
740 ------------------------------------------------------------------------------------------------------
741 Union All
742 select invoice_id,
743 line_number,
744 po_distribution_id,
745 rcv_transaction_id
746 from ap_invoice_lines_all b
747 where invoice_id = c_invoices.invoice_id
748 and line_type_lookup_code = lv_lt_inclu_lookup_code
749 and po_distribution_id is not null
750 and not exists
751 (select '1'
752 from JAI_CMN_ST_FORM_DTLS
753 where invoice_id = b.invoice_id
754 and invoice_line_number = b.line_number
755 and issue_receipt_flag = 'I')
756 and exists
757 (select '1'
758 from JAI_AP_MATCH_INV_TAXES
759 where invoice_id = b.invoice_id
760 and invoice_line_number = b.line_number
761 and tax_id in
762 (select tax_id
763 from JAI_CMN_TAXES_ALL
764 where stform_type is not null
765 and inclusive_tax_flag = 'Y'))
766 ------------------------------------------------------------------------------------------------------
767 -- Added by Jia for Bug#9535954, End
768 order by line_number --distribution_line_number
769 )
770 loop
771
772 v_invoice_error_flag := 'N';
773
774
775 begin
776
777 -- get the tax id for the distribution line from table JAI_AP_MATCH_INV_TAXES
778
779 v_tax_id := null;
780 v_tax_type := null;
781 v_tax_rate := null;
782 v_stform_type:= null;
783 v_po_line_location_id := null;
784 v_po_header_id := null;
785 v_tax_amount := null;
786
787 /* uptake of ap_invoice_lines_all */
788 open c_get_distrib_tax_details( c_inv_distributions.invoice_id,
789 c_inv_distributions.line_number);
790 --c_inv_distributions.distribution_line_number);
791
792 fetch c_get_distrib_tax_details into v_tax_id, v_po_line_location_id,
793 v_po_header_id, v_po_line_id, v_tax_amount, v_parent_invoice_line_number
794 ,lv_recoverable_flag; -- 5763527
795 --v_parent_distribution_id;
796
797 close c_get_distrib_tax_details;
798
799 if v_tax_id is null then
800 goto continue_with_next_dist;
801 end if;
802
803 open c_get_tax_details(v_tax_id);
804 fetch c_get_tax_details into v_tax_type, v_tax_rate, v_stform_type
805 , v_mod_cr_pctg; -- 5763527;
806
807 close c_get_tax_details;
808
809 if v_tax_type is null then
810 goto continue_with_next_dist;
811 end if;
812
813 -- control comes here only when tax details exists .
814 -- check if st forms entry should have been done.
815
816 if ( v_tax_type IN ('CST', 'Sales Tax') and
817 v_stform_type is not null
818 -- and NVL(v_tax_rate,0) <> 0
819 )
820 then
821
822 open c_get_ven_info(c_inv_distributions.invoice_id);
823 fetch c_get_ven_info into v_vendor_id, v_vendor_site_id, v_org_id;
824 close c_get_ven_info;
825
826 v_st_hdr_id := null;
827
828 open c_get_st_hdr_id(v_stform_type, v_vendor_id, v_vendor_site_id, v_org_id);
829 fetch c_get_st_hdr_id into v_st_hdr_id;
830 close c_get_st_hdr_id;
831
832
833 if v_st_hdr_id is null then
834
835 --select JAI_CMN_STFORM_HDRS_ALL_S.nextval into v_st_hdr_id from dual;
836
837 insert into JAI_CMN_STFORM_HDRS_ALL
838 (
839 st_hdr_id,
840 party_id,
841 party_site_id,
842 form_type,
843 org_id,
844 party_type_flag,
845 creation_date,
846 created_by,
847 last_update_date,
848 last_updated_by
849 )
850 values
851 (
852 --v_st_hdr_id,
853 JAI_CMN_STFORM_HDRS_ALL_S.nextval,
854 v_vendor_id,
855 v_vendor_site_id,
856 v_stform_type,
857 v_org_id,
858 'V',
859 sysdate,
860 v_uid,
861 sysdate,
862 v_uid
863 ) returning st_hdr_id into v_st_hdr_id ;
864 end if;
865
866
867 -- enter ST forms details
868 -- no need to check if details exist as it has been checked in the first cursor.
869
870 v_po_num := null;
871 v_type_lookup_code := null;
872 open c_get_po_num(v_po_header_id);
873 fetch c_get_po_num into v_po_num, v_type_lookup_code;
874 close c_get_po_num;
875
876
877 v_match_option := null;
878 open c_get_match_org_loc(v_po_line_location_id);
879 fetch c_get_match_org_loc into
880 v_match_option, v_ship_to_organization_id, v_ship_to_location_id;
881 close c_get_match_org_loc;
882
883 v_doc_type := null;
884 if v_match_option = 'R' then
885 v_doc_type := 'RECEIPT';
886 else
887 if v_type_lookup_code = 'STANDARD' then
888 v_doc_type := 'STANDARD';
889 elsif v_type_lookup_code = 'BLANKET' then
890 v_doc_type := 'BLANKET';
891 else
892 v_doc_type := 'PLANNED';
893 end if;
894 end if;
895
896
897 v_tax_line_no := null;
898
899 if c_inv_distributions.rcv_transaction_id is null then
900 open get_tax_ln_no_po(v_po_line_location_id, v_tax_id);
901 fetch get_tax_ln_no_po into v_tax_line_no;
902 close get_tax_ln_no_po ;
903 else
904 open c_get_tax_ln_no_receipt(c_inv_distributions.rcv_transaction_id, v_tax_id);
905 fetch c_get_tax_ln_no_receipt into v_tax_line_no;
906 close c_get_tax_ln_no_receipt;
907 end if;
908
909 /* uptake of ap_invoice_lines_all */
910 v_tax_target_amount :=
911 getSTformsTaxBaseAmount
912 (
913 c_inv_distributions.invoice_id,
914 v_parent_invoice_line_number, --v_parent_distribution_id,
915 v_tax_id,
916 v_tax_amount,
917 v_tax_rate
918 );
919
920
921 --
922 -- 5763527
923 -- For partially recoverable taxes tax_target_amount needs to be apportioned
924 -- because there will be two lines one for recoverable and one for non recoverable
925 -- and for both the tax lines getSTFormsTaxBaseAmount will return same base amount
926 --
927 Fnd_File.put_line(Fnd_File.LOG, 'v_tax_target_amount='||v_tax_target_amount||',v_mod_cr_pctg='||v_mod_cr_pctg||',lv_recoverable_flag='||lv_recoverable_flag);
928 if v_mod_cr_pctg > 0 and v_mod_cr_pctg < 100 then
929 Fnd_File.put_line(Fnd_File.LOG, 'INSIDE IF1');
930 if lv_recoverable_flag = jai_constants.YES then
931 v_tax_target_amount := (v_tax_target_amount) * (v_mod_cr_pctg/100);
932 elsif lv_recoverable_flag = jai_constants.NO then
933 v_tax_target_amount := v_tax_target_amount * (1 - (v_mod_cr_pctg/100));
934 end if;
935
936 end if;
937 -- End 5763527
938 /* select JAI_CMN_ST_FORM_DTLS_S.nextval into v_st_dtl_id from dual; */
939
940 v_rcv_transaction_id := null;
941
942
943 if c_inv_distributions.rcv_transaction_id is null then
944 open c_get_rcv_transaction_id(c_inv_distributions.invoice_id, c_inv_distributions.po_distribution_id, 'ITEM');
945 fetch c_get_rcv_transaction_id into v_rcv_transaction_id;
946 close c_get_rcv_transaction_id;
947 else
948 v_rcv_transaction_id := c_inv_distributions.rcv_transaction_id;
949 end if;
950
951
952 insert into JAI_CMN_ST_FORM_DTLS
953 (
954 st_hdr_id,
955 st_dtl_id,
956 issue_receipt_flag,
957 header_id,
958 line_id,
959 tax_line_no,
960 tax_id,
961 po_num,
962 doc_type,
963 tax_target_amount,
964 po_line_location_id,
965 rcv_transaction_id,
966 invoice_id,
967 invoice_line_number, --invoice_distribution_id,
968 organization_id,
969 location_id,
970 creation_date,
971 created_by,
972 last_update_date,
973 last_updated_by,
974 last_update_login
975 )
976 values
977 (
978 v_st_hdr_id,
979 --v_st_dtl_id,
980 JAI_CMN_ST_FORM_DTLS_S.nextval,
981 'I',
982 v_po_header_id,
983 v_po_line_id,
984 v_tax_line_no,
985 v_tax_id,
986 v_po_num,
987 v_doc_type,
988 v_tax_target_amount,
989 v_po_line_location_id,
990 v_rcv_transaction_id, -- c_inv_distributions.rcv_transaction_id,
991 c_inv_distributions.invoice_id,
992 c_inv_distributions.line_number, --c_inv_distributions.invoice_distribution_id,
993 v_ship_to_organization_id,
994 v_ship_to_location_id,
995 sysdate,
996 v_uid,
997 sysdate,
998 v_uid,
999 v_uid
1000 ) returning st_dtl_id into v_st_dtl_id;
1001
1002 v_st_lines_for_inv := v_st_lines_for_inv + 1;
1003
1004 else
1005
1006 goto continue_with_next_dist;
1007
1008 end if;
1009
1010
1011 << continue_with_next_dist >>
1012 null;
1013
1014 exception
1015 when others then
1016 v_invoice_error_flag := 'Y';
1017 v_error_message := sqlerrm;
1018 goto continue_with_next_inv;
1019 end;
1020
1021 end loop; -- c_inv_distributions
1022
1023
1024 << continue_with_next_inv >>
1025
1026 if v_invoice_process_flag = 'N' then
1027
1028 commit;
1029
1030 elsif v_invoice_error_flag = 'Y' then
1031
1032 rollback;
1033 if v_debug = 'Y' then
1034 Fnd_File.put_line(Fnd_File.LOG,' Error :' || v_error_message);
1035 end if;
1036 v_error_inv_cnt := v_error_inv_cnt + 1;
1037 v_error_flag := 'Y';
1038
1039 else
1040
1041 commit;
1042 if v_debug = 'Y' then
1043 Fnd_File.put_line(Fnd_File.LOG,' Successful : No of ST forms lines created for the invoice - '
1044 || to_char(v_st_lines_for_inv) );
1045 end if;
1046 v_processed_inv_cnt := v_processed_inv_cnt + 1;
1047
1048 end if;
1049
1050 end;
1051
1052 end loop; -- c_invoices
1053
1054
1055 Fnd_File.put_line(Fnd_File.LOG, '**** ======================= S U M M A R Y ======================= ****');
1056 Fnd_File.put_line(Fnd_File.LOG, '**** No of invoices processed successfully :'
1057 || to_char(v_processed_inv_cnt) );
1058
1059 if v_not_processed_inv_cnt > 0 then
1060 Fnd_File.put_line(Fnd_File.LOG, '**** No of invoices not processed for status - cancelled /not validated status :'
1061 || to_char(v_not_processed_inv_cnt) );
1062 end if;
1063
1064
1065 if v_error_inv_cnt > 0 then
1066
1067 Fnd_File.put_line(Fnd_File.LOG, '**** No of invoices for which error is encountered :'
1068 || to_char(v_error_inv_cnt) );
1069
1070 p_ret_code := 1;
1071 p_err_buf := 'Please check the detailed log. '
1072 || 'Invoice processed successfully - ' || to_char(v_processed_inv_cnt)
1073 || '. Invoice errord out - ' || to_char(v_error_inv_cnt);
1074 end if;
1075
1076 exception
1077
1078 when others then
1079 p_ret_code := 2;
1080 p_err_buf := sqlerrm;
1081 Fnd_File.put_line(Fnd_File.LOG, '**** Exception from procedure ja_in_ap_populate_st_forms:' || sqlerrm);
1082
1083 end generate_ap_forms;
1084
1085 PROCEDURE generate_forms( errbuf OUT NOCOPY varchar2 ,
1086 ret_code OUT NOCOPY varchar2 ,
1087 p_from_date VARCHAR2, -- default SYSDATE , -- Added global variable gd_from_date in package spec. by Ramananda for File.Sql.35
1088 p_to_date VARCHAR2, -- default SYSDATE , -- Added global variable gd_to_date in package spec. by Ramananda for File.Sql.35
1089 p_all_orgs varchar2 ,
1090 p_org_id number ,
1091 p_party_type varchar2 ,
1092 p_party_id number default null ,
1093 p_party_site_id number default null ,
1094 p_reprocess varchar2, -- default 'N' , Added global variable gv_reprocess in package spec. by Ramananda for File.Sql.35
1095 P_Enable_Trace varchar2
1096 )
1097 is
1098 v_errbuf varchar2(255);
1099 v_ret_code varchar2(255);
1100 v_invoice_id ra_customer_trx_all.customer_trx_id%type;
1101 v_sp_org_id number; -- used to indicate whether to run concurrent program for a particular org id or all org ids
1102
1103
1104 -- trace generation logic
1105 CURSOR c_program_id(p_request_id IN NUMBER) IS
1106 SELECT concurrent_program_id, nvl(enable_trace,'N')
1107 FROM FND_CONCURRENT_REQUESTS
1108 WHERE REQUEST_ID = p_request_id;
1109
1110 CURSOR get_audsid IS
1111 SELECT a.sid, a.serial#, b.spid FROM v$session a,v$process b
1112 WHERE audsid = userenv('SESSIONID')
1113 AND a.paddr = b.addr;
1114
1115 CURSOR get_dbname IS
1116 SELECT name FROM v$database;
1117
1118 v_enable_trace FND_CONCURRENT_PROGRAMS.enable_trace%TYPE;
1119 v_program_id FND_CONCURRENT_PROGRAMS.concurrent_program_id%TYPE;
1120 audsid NUMBER; -- := userenv('SESSIONID'); --Ramananda for File.Sql.35
1121 sid NUMBER;
1122 serial NUMBER;
1123 spid VARCHAR2(9);
1124 name1 VARCHAR2(25);
1125 -- trc gen
1126
1127 /* Added by Ramananda for bug#4407165 */
1128 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_st_forms_pkg.generate_forms';
1129 ld_from_date DATE ;
1130 ld_to_date DATE ;
1131
1132 /* --------------------------------------------------------------------------------------------------------------------
1133
1134 Change History:
1135
1136 1. 21/10/2003 ssumaith - Bug # 3138194. Version#616.1
1137
1138 created the procedure and attached it with 'India ST forms Receipt processing'
1139 This procedure calls the AP procedure or AR procedure internally based on
1140 the party_type_flag being 'C' or 'V'
1141
1142 2. 19/03/2004 ssumaith - bug# 3360432. Version#619.1
1143
1144 when st forms receipt processing concurrent program is called, calling the procedure
1145 which does the processing for ISO orders also. Call to the procedure - jai_cmn_st_forms_pkg.generate_iso_forms
1146 is made as part of this fix when the party_type parameter is 'C' in addition to the call made
1147 for ST forms receipt - jai_cmn_st_forms_pkg.generate_ar_forms.
1148
1149 3. 05-Jul-2006 Aiyer for the bug 5369250, Version 120.3
1150 Issue:-
1151 The concurrent failes with the following error :-
1152 "FDPSTP failed due to ORA-01861: literal does not match format string ORA-06512: at line 1 "
1153
1154 Reason:-
1155 The procedure generate_forms has two parameters p_from_date and p_to_date which are of type date , however the concurrent program
1156 passes it in the canonical format and hence the failure.
1157
1158 Fix:-
1159 Modified the procedure generate_forms.
1160 Changed the datatype of p_from_date and p_to_date from date to varchar2 as this parameter.
1161 Also added the new parameters ld_start_date and ld_end_date. The values in p_from_date and p_to_date would be converted to date format and
1162 stored in these local variables
1163
1164 Dependency due to this fix:-
1165 None
1166
1167 4. 25-JUL-2006 Ramananda for bug#5376622, File Version 120.4
1168 Issue:-
1169 ST Form request has performance problem. The Request takes exceptionally long time when
1170 customer Name is not given in parameter
1171 Fix:-
1172 Cursor c_fetch_records in generate_ar_forms procedure has high cost. Query doesn't have
1173 a Localization table jai_ar_trxs. After adding a table jai_ar_trxs, cost is reduced,
1174 thus improving the performance
1175
1176
1177 ---------------------------------------------------------------------------------------------------------------- */
1178 begin
1179 /*
1180 ||aiyer for the bug 5369250
1181 ||convert from canonical to date
1182 */
1183 ld_from_date := fnd_date.canonical_to_date(p_from_date);
1184 ld_to_date := fnd_date.canonical_to_date(p_to_date) ;
1185
1186 audsid := userenv('SESSIONID'); --Ramananda for File.Sql.35
1187
1188 fnd_file.put_line(FND_FILE.LOG,'Entering Procedure - jai_cmn_st_forms_pkg.generate_forms');
1189 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_org_id : '|| p_org_id ||' Process All orgs ' || p_all_orgs || ' p_party_type :' || p_party_type );
1190 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_party_id : ' || p_party_id || ' p_party_site_id : ' || p_party_site_id);
1191 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_from_date :' || p_from_date ||' p_to_date ' || p_to_date || ' Trace ' || P_Enable_Trace);
1192
1193 if P_Enable_Trace = 'Y' or P_Enable_Trace = 'y' then
1194 execute immediate 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''' ;
1195
1196 OPEN c_program_id(FND_GLOBAL.CONC_REQUEST_ID);
1197 FETCH c_program_id INTO v_program_id, v_enable_trace;
1198 CLOSE c_program_id;
1199
1200 fnd_file.put_line(FND_FILE.LOG, 'v_program_id -> '||v_program_id
1201 ||', v_enable_trace -> '||v_enable_trace
1202 ||', request_id -> '||FND_GLOBAL.CONC_REQUEST_ID);
1203
1204 if v_enable_trace = 'Y' THEN
1205 OPEN get_audsid;
1206 FETCH get_audsid INTO sid, serial, spid;
1207 CLOSE get_audsid;
1208
1209 OPEN get_dbname;
1210 FETCH get_dbname INTO name1;
1211 CLOSE get_dbname;
1212
1213 fnd_file.put_line(FND_FILE.LOG,'TraceFile Name = '||lower(name1)||'_ora_'||spid||'.trc');
1214 end if;
1215
1216 -- trx gen ends here
1217 end if;
1218
1219 if p_all_orgs = 'Y' or p_all_orgs = 'y' then
1220 v_sp_org_id := NULL;
1221 else
1222 v_sp_org_id := p_org_id;
1223 end if;
1224
1225 /*
1226 At this point, if the parameter is to run for all org ids , then passing null as the parameter to the inner
1227 procedure. Otherwise passing the particular org id to be processed.
1228 */
1229
1230 if p_party_type = 'C' or p_party_type = 'c' then
1231
1232 jai_cmn_st_forms_pkg.generate_ar_forms(
1233 errbuf ,
1234 ret_code ,
1235 v_sp_org_id ,
1236 'C' ,
1237 p_party_id ,
1238 p_party_site_id ,
1239 ld_from_date ,
1240 ld_to_date
1241 );
1242
1243 jai_cmn_st_forms_pkg.generate_iso_forms( -- Bug#3360432
1244 errbuf ,
1245 ret_code ,
1246 v_sp_org_id ,
1247 p_party_type ,
1248 p_party_id ,
1249 p_party_site_id ,
1250 ld_from_date ,
1251 ld_to_date
1252 );
1253
1254 elsif p_party_type = 'V' or p_party_type = 'V' then
1255 jai_cmn_st_forms_pkg.generate_ap_forms(
1256 errbuf ,
1257 ret_code ,
1258 v_sp_org_id ,
1259 p_party_id ,
1260 p_party_site_id ,
1261 ld_from_date ,
1262 ld_to_date ,
1263 p_reprocess
1264 );
1265 end if;
1266
1267 if errbuf is not null then
1268 fnd_file.put_line(FND_FILE.LOG,'Encountered the Error ' || errbuf);
1269 end if;
1270
1271 /* Added by Ramananda for bug#4407165 */
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274 errbuf := null;
1275 ret_code := null;
1276
1277 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1278 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1279 app_exception.raise_exception;
1280
1281 end generate_forms;
1282
1283 PROCEDURE generate_iso_forms(
1284 errbuf OUT NOCOPY varchar2 ,
1285 ret_code OUT NOCOPY varchar2 ,
1286 p_org_id number ,
1287 p_party_type varchar2 ,
1288 p_party_id number default null ,
1289 p_party_site_id number default null ,
1290 p_from_date date, -- default SYSDATE , -- Added global variable gd_from_date in package spec. by Ramananda for File.Sql.35
1291 p_to_date date -- default SYSDATE -- Added global variable gd_to_date in package spec. by Ramananda for File.Sql.35
1292 )
1293 as
1294
1295 cursor c_check_hdr_record_exists(
1296 p_party_id number ,
1297 p_party_site_id number ,
1298 p_form_type varchar2 ,
1299 p_org_id number
1300 )
1301 is
1302 select st_hdr_id
1303 from JAI_CMN_STFORM_HDRS_ALL
1304 where party_id = p_party_id
1305 and party_site_id = p_party_site_id
1306 and form_type = p_form_type
1307 and org_id = p_org_id
1308 and party_type_flag = 'C';
1309
1310 cursor c_get_order_line_info(
1311 p_delivery_detail_id number
1312 )
1313 is
1314 select order_line_id ,
1315 order_header_id ,
1316 organization_id ,
1317 location_id
1318 from JAI_OM_WSH_LINES_ALL
1319 where delivery_detail_id = p_delivery_detail_id;
1320
1321 cursor c_get_order_info(
1322 p_order_header_id number
1323 )
1324 is
1325 select order_number
1326 from oe_order_headers_all
1327 where header_id = p_order_header_id;
1328
1329 cursor c_check_duplicate(p_delivery_id Number)
1330 is
1331 select 1
1332 from JAI_CMN_STFORM_HDRS_ALL hdr ,
1333 JAI_CMN_ST_FORM_DTLS dtl
1334 where hdr.st_hdr_id = dtl.st_hdr_id
1335 and dtl.invoice_id = p_delivery_id
1336 and hdr.party_type_flag = 'C';
1337
1338
1339 cursor c_fetch_records is
1340 SELECT wnd.delivery_id ,
1341 wdd.org_id ,
1342 wdd.source_header_number ,
1343 wdd.source_header_type_id ,
1344 wdd.source_header_type_name ,
1345 oeh.sold_to_org_id customer_id ,
1346 oeh.ship_to_org_id customer_site_id,
1347 jspl.excise_invoice_no
1348 FROM wsh_new_deliveries wnd ,
1349 wsh_delivery_details wdd ,
1350 JAI_OM_WSH_LINES_ALL jspl ,
1351 oe_order_headers_all oeh
1352 WHERE jspl.delivery_id = wnd.delivery_id
1353 AND wdd.delivery_Detail_id = jspl.delivery_detail_id
1354 AND wdd.source_header_id = oeh.header_id
1355 AND oeh.source_document_type_id = 10
1356 AND wdd.org_id = nvl(p_org_id, wdd.org_id) /*Added nvl clause by mmurtuza for bug 16521623 */
1357 AND oeh.sold_to_org_id = nvl(p_party_id,oeh.sold_to_org_id)
1358 AND oeh.ship_to_org_id = nvl(p_party_site_id,oeh.ship_to_org_id)
1359 AND trunc(jspl.creation_date) between p_from_date and p_to_date
1360 AND EXISTS
1361 (SELECT 1
1362 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
1363 JAI_CMN_TAXES_ALL jtc
1364 WHERE jtc.tax_id = jsptl.tax_id
1365 AND jsptl.delivery_detail_id = jspl.delivery_detail_id
1366 AND jtc.tax_type IN ( jai_constants.tax_type_sales, jai_constants.tax_type_cst) --('Sales Tax','CST') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1367 AND jtc.stform_type IS NOT NULL
1368 )
1369 AND NOT EXISTS
1370 (SELECT 1
1371 FROM JAI_CMN_ST_FORM_DTLS jstd
1372 WHERE jstd.header_id = oeh.header_id
1373 AND jstd.invoice_id = wnd.delivery_id
1374 AND jstd.line_id = jspl.order_line_id
1375 AND jstd.doc_type = 'ISO' /*Bug 10196549 - Clause required to distinguish transactions with same customer_trx_id and delivery_id*/
1376 AND jstd.order_flag = 'O'
1377 )
1378 GROUP BY wnd.delivery_id ,
1379 wdd.org_id ,
1380 wdd.source_header_number ,
1381 wdd.source_header_type_id ,
1382 wdd.source_header_type_name ,
1383 oeh.sold_to_org_id ,
1384 oeh.ship_to_org_id,
1385 jspl.excise_invoice_no;
1386
1387 cursor c_get_taxes(p_delivery_id number)
1388 is
1389 select
1390 jsptl.TAX_LINE_NO ,
1391 jsptl.delivery_detail_ID ,
1392 jsptl.PRECEDENCE_1 ,
1393 jsptl.PRECEDENCE_2 ,
1394 jsptl.PRECEDENCE_3 ,
1395 jsptl.PRECEDENCE_4 ,
1396 jsptl.PRECEDENCE_5 ,
1397 jsptl.TAX_ID ,
1398 jsptl.TAX_RATE ,
1399 jsptl.QTY_RATE ,
1400 jsptl.UOM ,
1401 jsptl.TAX_AMOUNT ,
1402 jsptl.base_tax_amount ,
1403 jtc.stform_type
1404 from JAI_OM_WSH_LINE_TAXES jsptl ,
1405 JAI_CMN_TAXES_ALL jtc
1406 where delivery_detail_id in
1407 (
1408 select delivery_detail_id
1409 from JAI_OM_WSH_LINES_ALL
1410 where delivery_id = p_delivery_id
1411 )
1412 and jtc.tax_id = jsptl.tax_id
1413 and jtc.tax_type in ( jai_constants.tax_type_sales, jai_constants.tax_type_cst) --('Sales Tax','CST') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1414 and jtc.stform_type is not null;
1415
1416 v_hdr_record_exists number;
1417 v_st_hdr_id number;
1418 v_st_line_id number;
1419 v_order_line_id number;
1420 v_order_hdr_id number;
1421 v_order_num number;
1422 v_trx_number number;
1423 v_trx_type_id number;
1424 v_base_tax_amt number;
1425 v_record_exists number;
1426 v_ret_code number;
1427 v_errbuf varchar2(255);
1428 v_sqlerrm varchar2(255);
1429 v_some_errors char(1); -- := '0'; -- used to identify if all was successful --Ramananda for File.Sql.35
1430 v_orgn_id number;
1431 v_locn_id number;
1432 v_order_flag char(1); -- := 'O'; --Ramananda for File.Sql.35
1433
1434 v_excise_invoice_no JAI_OM_WSH_LINES_ALL.excise_invoice_no%type;
1435
1436 /*
1437 the purpose of the following function is to calculate the base tax amounts
1438 This needs to be specifically used in cases such as 0%tax added manually.
1439 Need to pass the delivery_detail_id of the shipment line to which the tax is attached
1440 and the tax id for which the tax calculation needs to be done.
1441 */
1442
1443 function get_base_tax_amount(p_delivery_Detail_id number ,
1444 p_tax_id number) return number is
1445 cursor c_tax_amount (p_p1 number,p_p2 number,p_p3 number,p_p4 number,p_p5 number)is
1446 SELECT SUM(tax_amount)
1447 FROM JAI_OM_WSH_LINE_TAXES
1448 WHERE delivery_detail_id = p_delivery_Detail_id
1449 AND tax_line_no IN (p_p1,p_p2,p_p3,p_p4,p_p5);
1450
1451 cursor c_get_precedences
1452 is
1453 select precedence_1,
1454 precedence_2,
1455 precedence_3,
1456 precedence_4,
1457 precedence_5
1458 from JAI_OM_WSH_LINE_TAXES
1459 where delivery_detail_id = p_delivery_Detail_id
1460 and tax_id = p_tax_id;
1461
1462 cursor c_get_line_amount
1463 is
1464 select selling_price * quantity line_amount
1465 from JAI_OM_WSH_LINES_ALL
1466 where delivery_detail_id = p_delivery_Detail_id;
1467
1468 v_prec_rec c_get_precedences%rowtype;
1469
1470 v_tax_amount number;
1471 v_line_amount number;
1472
1473 begin
1474
1475
1476 open c_get_precedences;
1477 fetch c_get_precedences into v_prec_rec;
1478 close c_get_precedences;
1479
1480 open c_tax_amount(v_prec_rec.precedence_1,
1481 v_prec_rec.precedence_2,
1482 v_prec_rec.precedence_3,
1483 v_prec_rec.precedence_4,
1484 v_prec_rec.precedence_5);
1485 fetch c_tax_amount into v_tax_amount;
1486 close c_tax_amount;
1487
1488 if v_prec_rec.precedence_1 = 0 or v_prec_rec.precedence_2 = 0 or v_prec_rec.precedence_3 = 0
1489 or v_prec_rec.precedence_4 = 0 or v_prec_rec.precedence_5 = 0 then
1490 open c_get_line_amount;
1491 fetch c_get_line_amount into v_line_amount;
1492 close c_get_line_amount;
1493 end if;
1494 return (nvl(v_line_amount,0) + nvl(v_tax_amount,0));
1495
1496 end get_base_tax_amount ;
1497
1498 BEGIN
1499
1500 /*-----------------------------------------------------------------------------------------------------------------
1501 Change history for jai_cmn_st_forms_pkg.generate_iso_forms procedure
1502
1503 SlNo dd/mm/yyyy Author and Description of Changes
1504 -------------------------------------------------------------------------------------------------------------------
1505 1. 13/10/2003 ssumaith bug # 3360432 , FileVersion: 619.1
1506 this procedure does the actual population of ST form related data into the revised tables
1507 when processed for ISO orders post shipment.
1508 This procedure will be called from the wrapper program - jai_cmn_st_forms_pkg.generate_forms
1509 when party_type parameter = 'C'
1510
1511 Doc_Type field in the JAI_CMN_ST_FORM_DTLS is set to 'ISO'
1512
1513 Grouping in the form will be done based on excise invoice number, where excise invoice number is
1514 generated for the iso order. Delivery id in is used for this grouping, in case excise invoice
1515 number is not generated.
1516
1517 2. 27-MAr-2013 mmurtuza for bug 16521623
1518 Description: INDIA - CONCURRENT PROGRAM FOR ST FORMS NOT PROCESSING RECORDS
1519 Fix: Added NVL clause for condition wdd.org_id = p_org_id
1520 -----------------------------------------------------------------------------------------------------------------*/
1521
1522 v_some_errors := '0'; -- used to identify if all was successful --Ramananda for File.Sql.35
1523 v_order_flag := 'O'; --Ramananda for File.Sql.35
1524
1525 fnd_file.put_line(FND_FILE.LOG,'1 Entering procedure : jai_cmn_st_forms_pkg.generate_iso_forms' );
1526 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_org_id -> : '|| p_org_id || ' p_party_type -> :' || p_party_type );
1527 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_party_id -> : ' || p_party_id || ' p_party_site_id -> : ' || p_party_site_id);
1528 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_from_date -> :' || p_from_date ||' p_to_date -> :' || p_to_date);
1529
1530 FOR st_forms_rec IN c_fetch_records
1531 LOOP
1532
1533 /* duplicate check */
1534 fnd_file.put_line(FND_FILE.LOG,'1.0 Processing delivery : ' || st_forms_rec.delivery_id);
1535
1536 v_record_exists := null;
1537
1538 open c_check_duplicate(st_forms_rec.delivery_id);
1539 fetch c_check_duplicate into v_record_exists;
1540 close c_check_duplicate;
1541
1542 if v_record_exists is null then
1543 -- only if there are no records already retreived
1544 -- for the delivery do the processing
1545
1546 /*
1547 If the control comes here , it is assumed that the delivery which is being looped has st forms
1548 related taxes no check done here again.
1549 need to insert records into the st forms hdr and st forms detail tables.
1550 JAI_CMN_STFORM_HDRS_ALL
1551 JAI_CMN_ST_FORM_DTLS
1552 */
1553 fnd_file.put_line(FND_FILE.LOG,'1.1 Delivery : ' || st_forms_rec.delivery_id || ' not present in St form tables, hence processing.' );
1554
1555 FOR tax_rec IN c_get_taxes(st_forms_rec.delivery_id)
1556 LOOP
1557 BEGIN
1558
1559 v_hdr_record_exists := null;
1560 v_order_line_id := null;
1561 v_order_hdr_id := null;
1562 v_order_num := null;
1563 v_base_tax_amt := null;
1564
1565 open c_check_hdr_record_exists(
1566 st_forms_rec.customer_id ,
1567 st_forms_rec.customer_site_id ,
1568 tax_rec.stform_type ,
1569 st_forms_rec.org_id
1570 );
1571 fetch c_check_hdr_record_exists into v_hdr_record_exists;
1572 close c_check_hdr_record_exists;
1573
1574
1575 if v_hdr_record_exists is null then
1576
1577 /* no record exists in the st forms hdr table (JAI_CMN_STFORM_HDRS_ALL) for the combination of
1578 party , party site , form_type , org_id and party type.
1579 */
1580
1581
1582 INSERT INTO JAI_CMN_STFORM_HDRS_ALL(
1583 st_hdr_id ,
1584 party_id ,
1585 party_site_id ,
1586 form_type ,
1587 creation_date ,
1588 created_by ,
1589 last_update_date ,
1590 last_updated_by ,
1591 last_update_login ,
1592 org_id ,
1593 party_type_flag
1594 ) values (
1595 JAI_CMN_STFORM_HDRS_ALL_S.nextval ,
1596 st_forms_rec.customer_id ,
1597 st_forms_rec.customer_site_id ,
1598 tax_rec.stform_type ,
1599 sysdate ,
1600 fnd_global.user_id ,
1601 sysdate ,
1602 fnd_global.user_id ,
1603 fnd_global.login_id ,
1604 st_forms_rec.org_id ,
1605 'C'
1606 ) RETURNING st_hdr_id INTO v_st_hdr_id;
1607 fnd_file.put_line(FND_FILE.LOG,' inserting into JAI_CMN_STFORM_HDRS_ALL table with header id '|| v_st_hdr_id);
1608 ELSE
1609
1610 v_st_hdr_id := v_hdr_record_exists;
1611 fnd_file.put_line(FND_FILE.LOG,'header record found . Header id is : ' ||v_st_hdr_id);
1612
1613
1614 END IF;
1615
1616
1617 open c_get_order_line_info(tax_rec.delivery_detail_id);
1618 fetch c_get_order_line_info into v_order_line_id, v_order_hdr_id,v_orgn_id , v_locn_id;
1619 close c_get_order_line_info;
1620
1621 open c_get_order_info (v_order_hdr_id);
1622 fetch c_get_order_info into v_order_num;
1623 close c_get_order_info;
1624
1625 v_order_flag := 'O';
1626
1627
1628 /*if tax_rec.tax_rate > 0 and tax_rec.base_tax_amount is not null then
1629 v_base_tax_amt := (tax_rec.tax_amount * 100) / tax_rec.tax_rate;
1630 end if;
1631 */
1632
1633 IF tax_rec.base_tax_amount IS NULL THEN
1634 v_base_tax_amt := get_base_tax_amount(tax_rec.delivery_detail_id, tax_rec.tax_id);
1635 IF v_base_tax_amt IS NULL THEN
1636 v_base_tax_amt :=0;
1637 end if;
1638 END IF;
1639
1640
1641
1642 INSERT INTO JAI_CMN_ST_FORM_DTLS(
1643 ST_HDR_ID ,
1644 ST_DTL_ID ,
1645 HEADER_ID , -- order header id
1646 LINE_ID , -- order line id
1647 TAX_ID ,
1648 TAX_LINE_NO ,
1649 INVOICE_ID , -- delivery id
1650 ISSUE_RECEIPT_FLAG ,
1651 TAX_TARGET_AMOUNT ,
1652 MATCHED_AMOUNT ,
1653 ORDER_FLAG , -- 'O'
1654 ORDER_NUMBER , -- sales order number
1655 TRX_TYPE_ID ,
1656 TRX_NUMBER , -- excise invoice number, if null then delivery
1657 organization_id ,
1658 location_id ,
1659 doc_type , -- Hard coded Value 'ISO' passed
1660 CREATION_DATE ,
1661 CREATED_BY ,
1662 LAST_UPDATE_DATE ,
1663 LAST_UPDATED_BY ,
1664 LAST_UPDATE_LOGIN
1665 ) values (
1666 v_st_hdr_id ,
1667 JAI_CMN_ST_FORM_DTLS_S.nextval ,
1668 v_order_hdr_id ,
1669 v_order_line_id ,
1670 tax_rec.tax_id ,
1671 tax_rec.tax_line_no ,
1672 st_forms_rec.delivery_id ,
1673 'R' ,
1674 nvl(tax_rec.base_tax_amount
1675 ,v_base_tax_amt) ,
1676 NULL , -- matched amount
1677 v_order_flag ,
1678 v_order_num ,
1679 st_forms_rec.source_header_type_id ,
1680 nvl(st_forms_rec.excise_invoice_no, st_forms_rec.delivery_id) , -- need to confirm it after discussion
1681 v_orgn_id ,
1682 v_locn_id ,
1683 'ISO' ,
1684 sysdate ,
1685 fnd_global.user_id ,
1686 sysdate ,
1687 fnd_global.user_id ,
1688 fnd_global.login_id
1689
1690 ) RETURNING st_dtl_id INTO v_st_line_id;
1691 fnd_file.put_line(FND_FILE.LOG,' inserting into JAI_CMN_ST_FORM_DTLS table with detail id :' ||v_st_line_id );
1692 v_base_tax_amt :=NULL;
1693
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 fnd_file.put_line(FND_FILE.LOG,'Encountered Error when processing Delivery : ' || st_forms_Rec.delivery_id );
1697 fnd_file.put_line(FND_FILE.LOG,'Error reported is : ' || sqlerrm );
1698 v_some_errors := '1';
1699 rollback;
1700 EXIT; -- So that this delivery_id is not processed further
1701 END;
1702
1703 END LOOP; -- tax loop
1704
1705 END IF;
1706
1707 v_orgn_id := Null;
1708 v_locn_id := Null;
1709
1710 COMMIT; -- commit for every delivery
1711
1712 END LOOP; -- delivery loop
1713
1714 if v_some_errors = '1' then
1715 v_ret_code := '1'; -- signal completion with warnings -- some deliveries could not be processed successfully
1716 ret_code := v_ret_code;
1717 else
1718 v_Ret_code := '0' ;-- signal normal completion.
1719 ret_code := v_ret_code;
1720 end if;
1721
1722 EXCEPTION
1723 WHEN OTHERS THEN
1724 v_sqlerrm := substr(sqlerrm,1,255);
1725 errbuf := v_sqlerrm;
1726 v_ret_code := 2; -- signal error
1727 ret_code := v_ret_code;
1728 end generate_iso_forms;
1729
1730 procedure generate_ar_forms(
1731 errbuf OUT NOCOPY varchar2 ,
1732 ret_code OUT NOCOPY varchar2 ,
1733 p_org_id number ,
1734 p_party_type varchar2 ,
1735 p_party_id number default null ,
1736 p_party_site_id number default null ,
1737 p_from_date date, -- default SYSDATE , -- Added global variable gd_from_date in package spec. by Ramananda for File.Sql.35
1738 p_to_date date -- default SYSDATE -- Added global variable gd_to_date in package spec. by Ramananda for File.Sql.35
1739 )
1740 as
1741
1742 cursor c_check_hdr_record_exists(
1743 p_party_id number ,
1744 p_party_site_id number ,
1745 p_form_type varchar2 ,
1746 p_org_id number
1747 )
1748 is
1749 select st_hdr_id
1750 from JAI_CMN_STFORM_HDRS_ALL
1751 where party_id = p_party_id
1752 and party_site_id = p_party_site_id
1753 and form_type = p_form_type
1754 and org_id = p_org_id
1755 and party_type_flag = 'C';
1756
1757 cursor c_get_order_line_info(
1758 p_customer_trx_line_id number
1759 )
1760 is
1761 select interface_line_attribute6
1762 from ra_customer_trx_lines_all
1763 where customer_Trx_line_id = p_customer_trx_line_id;
1764
1765 cursor c_get_order_hdr(
1766 p_order_line_id number
1767 )
1768 is
1769 select header_id
1770 from oe_order_lines_all
1771 where line_id = p_order_line_id;
1772
1773 cursor c_get_order_info(
1774 p_order_header_id number
1775 )
1776 is
1777 select order_number
1778 from oe_order_headers_all
1779 where header_id = p_order_header_id;
1780
1781 cursor c_check_duplicate(p_invoice_id Number, p_trx_number varchar2) /*Added trx_number parameter for bug # 9113108 */
1782 is
1783 select 1
1784 from JAI_CMN_STFORM_HDRS_ALL hdr ,
1785 JAI_CMN_ST_FORM_DTLS dtl
1786 where hdr.st_hdr_id = dtl.st_hdr_id
1787 and dtl.invoice_id = p_invoice_id
1788 and dtl.TRX_NUMBER = p_trx_number /*Added trx_number parameter for bug # 9113108 */
1789 and hdr.party_type_flag = 'C';
1790
1791
1792 cursor c_fetch_records is
1793 select trx.customer_trx_id , trx.org_id , trx.trx_number , trx.cust_trx_type_id , trx.created_from,
1794 nvl(trx.bill_to_customer_id,trx.ship_to_customer_id) customer_id, nvl(trx.bill_to_site_use_id,trx.ship_to_site_use_id) customer_site_id,
1795 decode( trx_types.TYPE ,'INV','Invoice','CM','Credit Memo','DM','Debit Memo',trx_types.TYPE ) document_type /*JMEENA for bug#4932256( FP 4913641)*/
1796 from ra_customer_Trx_all trx ,
1797 ra_cust_trx_types_all trx_types ,
1798 jai_ar_trxs jtrx /* Added for bug#5376622 */
1799 where
1800 trx.customer_trx_id = jtrx.customer_trx_id AND
1801 (trx.bill_to_customer_id = nvl(p_party_id,trx.bill_to_customer_id)
1802 OR
1803 trx.ship_to_customer_id = nvl(p_party_id,trx.ship_to_customer_id)) AND
1804 (trx.bill_to_site_use_id = nvl(p_party_site_id,trx.bill_to_site_use_id)
1805 OR
1806 trx.ship_to_site_use_id = nvl(p_party_site_id,trx.ship_to_site_use_id)) AND
1807 trx.org_id = nvl(p_org_id,trx.org_id) AND
1808 NOT EXISTS
1809 (SELECT 1
1810 FROM JAI_CMN_ST_FORM_DTLS a ,
1811 JAI_CMN_STFORM_HDRS_ALL b
1812 WHERE b.party_id = nvl(p_party_id,b.party_id) AND
1813 b.party_site_id = nvl(p_party_site_id,b.party_site_id) AND
1814 b.party_type_flag = 'C' AND
1815 a.st_hdr_id = b.st_hdr_id AND
1816 a.invoice_id = trx.customer_trx_id AND
1817 a.trx_number = trx.trx_number /*Bug 10196549 - Clause required to distinguish transactions with same customer_trx_id and delivery_id*/
1818 ) AND
1819 EXISTS
1820 (SELECT 1
1821 FROM JAI_AR_TRX_LINES trx_lines
1822 WHERE customer_trx_id = trx.customer_trx_id AND
1823 EXISTS
1824 (SELECT 1
1825 FROM JAI_AR_TRX_TAX_LINES tax_lines ,
1826 JAI_CMN_TAXES_ALL jtc
1827 WHERE link_to_cust_trx_line_id = trx_lines.customer_trx_line_id AND
1828 tax_type IN ( jai_constants.tax_type_sales, jai_constants.tax_type_cst) --('Sales Tax','CST') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1829 AND jtc.tax_id = tax_lines.tax_id AND
1830 jtc.stform_type IS NOT NULL
1831 )
1832 and trx.complete_flag ='Y'
1833 AND trx_date between p_from_date and p_to_date
1834 and trx_types.type in ('INV' ,'CM','DM')/*JMEENA for bug#4932256 (FP 4913641) . Added CM and DM*/
1835 and trx_types.cust_Trx_type_id = trx.cust_trx_type_id
1836 and trx_types.org_id = trx.org_id
1837 );
1838
1839
1840 cursor c_get_taxes(p_invoice_id number)
1841 is
1842 select
1843 jtxn.TAX_LINE_NO ,
1844 jtxn.CUSTOMER_TRX_LINE_ID ,
1845 jtxn.LINK_TO_CUST_TRX_LINE_ID ,
1846 jtxn.PRECEDENCE_1 ,
1847 jtxn.PRECEDENCE_2 ,
1848 jtxn.PRECEDENCE_3 ,
1849 jtxn.PRECEDENCE_4 ,
1850 jtxn.PRECEDENCE_5 ,
1851 jtxn.TAX_ID ,
1852 jtxn.TAX_RATE ,
1853 jtxn.QTY_RATE ,
1854 jtxn.UOM ,
1855 jtxn.TAX_AMOUNT ,
1856 jtxn.INVOICE_CLASS ,
1857 jtxn.base_tax_amount ,
1858 jtc.stform_type
1859 from JAI_AR_TRX_TAX_LINES jtxn ,
1860 JAI_CMN_TAXES_ALL jtc
1861 where link_to_cust_Trx_line_id in
1862 (
1863 select customer_Trx_line_id
1864 from JAI_AR_TRX_LINES
1865 where customer_trx_id = p_invoice_id
1866 )
1867 and jtc.tax_id = jtxn.tax_id
1868 and jtc.tax_type in ( jai_constants.tax_type_sales, jai_constants.tax_type_cst) --('Sales Tax','CST') /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1869 and jtc.stform_type is not null;
1870
1871 cursor c_orgn_locn (p_invoice_id number)
1872 is
1873 select organization_id , location_id
1874 from JAI_AR_TRXS
1875 where customer_trx_id = p_invoice_id;
1876
1877 cursor c_trx_type(p_cust_trx_type_id number) is
1878 select type
1879 from ra_cust_trx_types_all
1880 where cust_trx_type_id = p_cust_trx_type_id;
1881
1882 v_hdr_record_exists number;
1883 v_st_hdr_id number;
1884 v_st_line_id number;
1885 v_order_line_id number;
1886 v_order_hdr_id number;
1887 v_order_num number;
1888 v_trx_number number;
1889 v_trx_type_id number;
1890 v_base_tax_amt number;
1891 v_record_exists number;
1892 v_ret_code number;
1893 v_errbuf varchar2(255);
1894 v_sqlerrm varchar2(255);
1895 v_created_from ra_customer_trx_all.created_from%type;
1896 const_manual constant ra_customer_trx_all.created_from%type := 'ARXTWMAI';
1897 const_autoinv constant ra_customer_trx_all.created_from%type := 'RAXTRX';
1898 v_some_errors char(1); -- := '0'; -- used to identify if all was successful --Ramananda for File.Sql.35
1899 v_orgn_id number;
1900 v_locn_id number;
1901 v_trx_type ra_cust_trx_types_all.type%type;
1902 v_order_flag char(1); --:= 'O'; --Ramananda for File.Sql.35
1903
1904 /*
1905 the purpose of the following function is to calculate the base tax amounts
1906 This needs to be specifically used in cases such as 0%tax added manually.
1907 Need to pass the Customer_trx_line_id of the invoice line to which the tax is attached
1908 and the tax id for which the tax calculation needs to be done.
1909 */
1910 function get_base_tax_amount(p_Link_to_line_id number , p_tax_id number) return number is
1911 cursor c_tax_amount is
1912 SELECT SUM(tax_amount)
1913 FROM JAI_AR_TRX_TAX_LINES
1914 WHERE link_to_cust_trx_line_id = p_Link_to_line_id
1915 AND tax_line_no IN
1916 (
1917 SELECT precedence_1
1918 FROM JAI_AR_TRX_TAX_LINES
1919 WHERE link_to_cust_trx_line_id = p_Link_to_line_id
1920 and precedence_1 is not null
1921 AND tax_id = p_tax_id
1922 UNION
1923 SELECT precedence_2
1924 FROM JAI_AR_TRX_TAX_LINES
1925 WHERE link_to_cust_trx_line_id = p_Link_to_line_id
1926 AND tax_id = p_tax_id
1927 and precedence_1 is not null
1928 UNION
1929 SELECT precedence_3
1930 FROM JAI_AR_TRX_TAX_LINES
1931 WHERE link_to_cust_trx_line_id = p_Link_to_line_id
1932 AND tax_id = p_tax_id
1933 and precedence_1 is not null
1934 UNION
1935 SELECT precedence_4
1936 FROM JAI_AR_TRX_TAX_LINES
1937 WHERE link_to_cust_trx_line_id = p_Link_to_line_id
1938 and precedence_1 is not null
1939 AND tax_id = p_tax_id
1940 UNION
1941 SELECT precedence_5
1942 FROM JAI_AR_TRX_TAX_LINES
1943 WHERE link_to_cust_trx_line_id = p_Link_to_line_id
1944 and precedence_1 is not null
1945 AND tax_id = p_tax_id
1946 );
1947
1948 cursor c_get_precedences
1949 is
1950 select precedence_1,
1951 precedence_2,
1952 precedence_3,
1953 precedence_4,
1954 precedence_5
1955 from JAI_AR_TRX_TAX_LINES
1956 where link_to_cust_trx_line_id = p_Link_to_line_id
1957 and tax_id = p_tax_id;
1958
1959 cursor c_get_line_amount
1960 is select line_amount
1961 from JAI_AR_TRX_LINES
1962 where customer_trx_line_id = p_link_to_line_id;
1963
1964 v_prec_rec c_get_precedences%rowtype;
1965
1966 v_tax_amount number;
1967 v_line_amount number;
1968 begin
1969
1970
1971 open c_tax_amount;
1972 fetch c_tax_amount into v_tax_amount;
1973 close c_tax_amount;
1974
1975 open c_get_precedences;
1976 fetch c_get_precedences into v_prec_rec;
1977 close c_get_precedences;
1978
1979 if v_prec_rec.precedence_1 = 0 or v_prec_rec.precedence_2 = 0 or v_prec_rec.precedence_3 = 0
1980 or v_prec_rec.precedence_4 = 0 or v_prec_rec.precedence_5 = 0 then
1981 open c_get_line_amount;
1982 fetch c_get_line_amount into v_line_amount;
1983 close c_get_line_amount;
1984 end if;
1985 return (nvl(v_line_amount,0) + nvl(v_tax_amount,0));
1986 end;
1987
1988
1989 BEGIN
1990
1991 /*-----------------------------------------------------------------------------------------------------------------
1992 Change history for jai_cmn_st_forms_pkg.generate_ar_forms procedure
1993
1994 SlNo dd/mm/yyyy Author and Description of Changes
1995 -------------------------------------------------------------------------------------------------------------------
1996 1. 13/10/2003 ssumaith bug # 3138194, FileVersion: 616.1
1997 this procedure does the actual population of ST form related data into the revised tables
1998 when processed for an AR invoice which is in a completed state.
1999
2000 2. 13/02/2004 Vijay Shankar for bug # 3441533, FileVersion: 618.1
2001 v_hdr_record_exists variable which is used to check whether header is inserted or not is retaining its value
2002 in the loop. This is fixed by assigning NULL at the start of the loop so that it wont retain its value.
2003 Also the code SELECT <sequence_name>.nextval INTO <var> FROM DUAL is modified to populate <var> during INSERT
2004 Statement. Usage of v_success variable is removed
2005
2006 3. 18/11/2008 JMEENA for bug#4932256 (FP 4913641)
2007 Issue: Debit Notes and Credit Notes are not considered for AR ST Forms tracking in AR
2008 Fix: Modified the cursor c_fetch_records to fetch CM and DM also
2009 -----------------------------------------------------------------------------------------------------------------*/
2010
2011 v_some_errors := '0'; -- used to identify if all was successful --Ramananda for File.Sql.35
2012 v_order_flag := 'O'; --Ramananda for File.Sql.35
2013
2014 fnd_file.put_line(FND_FILE.LOG,'1 Entering procedure : jai_cmn_st_forms_pkg.generate_ar_forms' );
2015 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_org_id -> : '|| p_org_id || ' p_party_type -> :' || p_party_type );
2016 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_party_id -> : ' || p_party_id || ' p_party_site_id -> : ' || p_party_site_id);
2017 fnd_file.put_line(FND_FILE.LOG,'Parameters - p_from_date -> :' || p_from_date ||' p_to_date -> :' || p_to_date);
2018
2019 FOR st_forms_rec IN c_fetch_records
2020 LOOP
2021
2022 /* duplicate check */
2023 fnd_file.put_line(FND_FILE.LOG,'1.0 Processing invoice : '||st_forms_rec.document_type||':' || st_forms_rec.customer_trx_id);
2024
2025 v_record_exists := null;
2026 -- Added Parameter st_forms_rec.trx_number for bug#9113108 by nprashar
2027 open c_check_duplicate(st_forms_rec.customer_trx_id, st_forms_rec.trx_number);
2028 fetch c_check_duplicate into v_record_exists;
2029 close c_check_duplicate;
2030
2031 if v_record_exists is null then
2032 -- only if there are no records already retreived
2033 -- for the invoice do the processing
2034
2035 /*
2036 If the control comes here , it is assumed that the invoice which is being looped has st forms related taxes
2037 no check done here again.
2038 need to insert records into the st forms hdr and st forms detail tables.
2039 JAI_CMN_STFORM_HDRS_ALL
2040 JAI_CMN_ST_FORM_DTLS
2041 */
2042 fnd_file.put_line(FND_FILE.LOG,'1.1 Invoice : '||st_forms_rec.document_type||':' || st_forms_rec.customer_trx_id || ' not present in St form tables, hence processing.' );
2043
2044 FOR tax_rec IN c_get_taxes(st_forms_rec.customer_trx_id)
2045 LOOP
2046 BEGIN
2047
2048 --Start, Vijay Shankar for bug # 3441533
2049 v_hdr_record_exists := null;
2050 v_order_line_id := null;
2051 v_order_hdr_id := null;
2052 v_order_num := null;
2053 v_base_tax_amt := null;
2054 --End, Vijay Shankar for bug # 3441533
2055
2056 open c_check_hdr_record_exists(
2057 st_forms_rec.customer_id ,
2058 st_forms_rec.customer_site_id ,
2059 tax_rec.stform_type ,
2060 st_forms_rec.org_id
2061 );
2062 fetch c_check_hdr_record_exists into v_hdr_record_exists;
2063 close c_check_hdr_record_exists;
2064
2065
2066 if v_hdr_record_exists is null then
2067
2068 /* no record exists in the st forms hdr table (JAI_CMN_STFORM_HDRS_ALL) for the combination of
2069 party , party site , form_type , org_id and party type.
2070 */
2071
2072 fnd_file.put_line(FND_FILE.LOG,'before inserting into JAI_CMN_STFORM_HDRS_ALL table');
2073 INSERT INTO JAI_CMN_STFORM_HDRS_ALL(
2074 st_hdr_id ,
2075 party_id ,
2076 party_site_id ,
2077 form_type ,
2078 creation_date ,
2079 created_by ,
2080 last_update_date ,
2081 last_updated_by ,
2082 last_update_login ,
2083 org_id ,
2084 party_type_flag
2085 ) values (
2086 JAI_CMN_STFORM_HDRS_ALL_S.nextval ,
2087 st_forms_rec.customer_id ,
2088 st_forms_rec.customer_site_id ,
2089 tax_rec.stform_type ,
2090 sysdate ,
2091 fnd_global.user_id ,
2092 sysdate ,
2093 fnd_global.user_id ,
2094 fnd_global.login_id ,
2095 st_forms_rec.org_id ,
2096 'C'
2097 ) RETURNING st_hdr_id INTO v_st_hdr_id;
2098
2099 ELSE
2100
2101 v_st_hdr_id := v_hdr_record_exists;
2102
2103 END IF;
2104
2105 if st_forms_Rec.created_from = const_Autoinv then
2106
2107 /* fetching order details for autoinvoice. */
2108 open c_get_order_line_info(tax_rec.link_to_cust_trx_line_id);
2109 fetch c_get_order_line_info into v_order_line_id;
2110 close c_get_order_line_info;
2111
2112 open c_get_order_hdr(v_order_line_id);
2113 fetch c_get_order_hdr into v_order_hdr_id;
2114 close c_get_order_hdr;
2115
2116 open c_get_order_info (v_order_hdr_id);
2117 fetch c_get_order_info into v_order_num;
2118 close c_get_order_info;
2119
2120 v_order_flag := 'O';
2121
2122 elsif st_forms_Rec.created_from = const_manual then
2123
2124 v_order_hdr_id := st_forms_Rec.customer_Trx_id;
2125 v_order_line_id := tax_rec.customer_trx_line_id;
2126
2127 v_order_flag := 'I';
2128 v_order_num := NULL;
2129
2130 end if;
2131
2132 v_order_line_id := tax_rec.link_to_cust_trx_line_id;
2133 -- based on input from NPAI
2134 -- either from Autoinvoiced invoice or a manual ar invoice ,
2135 -- we need to populate customer trx line id of the line to which the Tax is attached from
2136 -- JAI_AR_TRX_TAX_LINES
2137
2138 if tax_rec.tax_rate > 0 and tax_rec.base_tax_amount is not null then
2139 v_base_tax_amt := (tax_rec.tax_amount * 100) / tax_rec.tax_rate;
2140 end if;
2141
2142 IF v_base_tax_amt IS NULL THEN
2143 v_base_tax_amt := get_base_tax_amount(tax_rec.link_to_cust_trx_line_id, tax_rec.tax_id);
2144 IF v_base_tax_amt IS NULL THEN
2145 v_base_tax_amt :=0;
2146 end if;
2147 END IF;
2148
2149 if v_orgn_id is null then
2150 open c_orgn_locn(st_forms_rec.customer_trx_id);
2151 fetch c_orgn_locn into v_orgn_id , v_locn_id;
2152 close c_orgn_locn;
2153 end if;
2154
2155 fnd_file.put_line(FND_FILE.LOG,'before inserting into JAI_CMN_ST_FORM_DTLS table');
2156
2157 INSERT INTO JAI_CMN_ST_FORM_DTLS(
2158 ST_HDR_ID ,
2159 ST_DTL_ID ,
2160 HEADER_ID , -- order header id
2161 LINE_ID , -- order line id
2162 TAX_ID ,
2163 TAX_LINE_NO ,
2164 INVOICE_ID , -- customer trx id
2165 ISSUE_RECEIPT_FLAG ,
2166 TAX_TARGET_AMOUNT ,
2167 MATCHED_AMOUNT ,
2168 ORDER_FLAG , -- 'O'
2169 ORDER_NUMBER , -- sales order number
2170 TRX_TYPE_ID ,
2171 TRX_NUMBER , -- invoice num
2172 organization_id ,
2173 location_id ,
2174 CREATION_DATE ,
2175 CREATED_BY ,
2176 LAST_UPDATE_DATE ,
2177 LAST_UPDATED_BY ,
2178 LAST_UPDATE_LOGIN
2179 ) values (
2180 v_st_hdr_id ,
2181 JAI_CMN_ST_FORM_DTLS_S.nextval ,
2182 v_order_hdr_id ,
2183 v_order_line_id ,
2184 tax_rec.tax_id ,
2185 tax_rec.tax_line_no ,
2186 st_forms_rec.customer_Trx_id ,
2187 'R' ,
2188 nvl(tax_rec.base_tax_amount
2189 ,v_base_tax_amt) ,
2190 NULL , -- matched amount
2191 v_order_flag ,
2192 v_order_num ,
2193 st_forms_rec.cust_trx_type_id ,
2194 st_forms_rec.trx_number ,
2195 v_orgn_id ,
2196 v_locn_id ,
2197 sysdate ,
2198 fnd_global.user_id ,
2199 sysdate ,
2200 fnd_global.user_id ,
2201 fnd_global.login_id
2202
2203 ) RETURNING st_dtl_id INTO v_st_line_id;
2204
2205 v_base_tax_amt :=NULL;
2206
2207 EXCEPTION
2208 WHEN OTHERS THEN
2209 fnd_file.put_line(FND_FILE.LOG,'Encountered Error when processing Invoice : ' ||st_forms_rec.document_type||':'|| st_forms_Rec.customer_Trx_id );
2210 fnd_file.put_line(FND_FILE.LOG,'Error reported is : ' || sqlerrm );
2211 v_some_errors := '1';
2212 rollback;
2213 EXIT; -- So that this CUSTOMER_TRX_ID is not processed further
2214 END;
2215
2216 END LOOP; -- tax loop
2217
2218 END IF;
2219
2220 v_orgn_id := Null;
2221 v_locn_id := Null;
2222
2223 COMMIT; -- commit for every invoice
2224
2225 END LOOP; -- invoice loop
2226
2227 if v_some_errors = '1' then
2228 v_ret_code := '1'; -- signal completion with warnings -- some invoices could not be processed successfully
2229 ret_code := v_ret_code;
2230 else
2231 v_Ret_code := '0' ;-- signal normal completion.
2232 ret_code := v_ret_code;
2233 end if;
2234
2235 EXCEPTION
2236 WHEN OTHERS THEN
2237 v_sqlerrm := substr(sqlerrm,1,255);
2238 errbuf := v_sqlerrm;
2239 v_ret_code := 2; -- signal error
2240 ret_code := v_ret_code;
2241 end generate_ar_forms;
2242
2243
2244 END jai_cmn_st_forms_pkg ;
[Home] [Help]