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