[Home] [Help]
Skip to content
PACKAGE BODY: APPS.JAI_CMN_GST_INV_GEN_PKG
Source
1 package body jai_cmn_gst_inv_gen_pkg AS
2 /* $Header: jai_gst_inv_gen.plb 120.1 2010/09/07 07:58:34 boboli noship $ */
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| jai_gst_inv_gen.plb |
10 --| |
11 --| DESCRIPTION |
12 --| This package is mainly used for generate GST |
13 --| invoice number and process accounting for GST |
14 --| |
15 --| TDD REFERENCE |
16 --| Take referce TDD_12_2_FIN_JAI_Enhanced_GST.doc |
17 --| |
18 --| |
19 --| PURPOSE |
20 --| PROCEDURE process |
21 --| FUNCTION check_reg_dealer |
22 --| |
23 --| HISTORY |
24 --| 02-Sep-2010 Created by Bo Li for bug#10043656 |
25 --| GST Enhancement for R12.1.3 |
26 --| |
27 --+======================================================================*/
28
29 FUNCTION check_reg_dealer ( pn_customer_id NUMBER ,
30 pn_site_use_id NUMBER ) return boolean
31
32 IS
33 ln_address_id NUMBER;
34 lv_regno JAI_CMN_CUS_ADDRESSES.vat_Reg_no%type;
35
36
37 CURSOR c_get_address is
38 SELECT hzcas.cust_acct_site_id
39 FROM hz_cust_site_uses_all hzcsu ,
40 hz_cust_acct_sites_all hzcas
41 WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
42 AND hzcsu.site_use_id = pn_site_use_id
43 AND hzcas.cust_account_id = pn_customer_id ;
44
45 CURSOR c_regno (pn_address_id NUMBER) IS
49 AND address_id = pn_address_id;
46 SELECT vat_Reg_no
47 FROM JAI_CMN_CUS_ADDRESSES
48 WHERE customer_id = pn_customer_id
50
51 BEGIN
52
53 open c_get_address;
54 fetch c_get_address into ln_address_id;
55 close c_get_address;
56 IF ln_address_id IS NOT NULL THEN
57
58 open c_regno (ln_address_id);
59 fetch c_regno into lv_regno;
60 close c_regno;
61 END IF;
62
63 IF lv_regno IS NULL THEN
64 return (false);
65 ELSE
66 return (true);
67 END IF;
68
69 END check_reg_dealer;
70
71 PROCEDURE process (
72 retcode OUT NOCOPY VARCHAR2,
73 errbuf OUT NOCOPY VARCHAR2,
74 p_organization_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE,
75 p_location_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
76 p_order_number_from OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE,
77 p_order_number_to OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE,
78 p_delivery_id_from JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE,
79 p_delivery_id_to JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE,
80 pv_delivery_date_from VARCHAR2,
81 pv_delivery_date_to VARCHAR2,
82 p_process_action VARCHAR2,
83 p_single_invoice_num VARCHAR2,
84 p_override_invoice_date VARCHAR2,
85 p_debug VARCHAR2
86 )
87 IS
88 /*************************************************************************************************************************************
89 Purpose:-
90 || It processes single / multiple deliveries based on the parameters entered.
91 || In a loop , each delivery is processed and two tasks are done based upon the p_process_action parameter
92 || If the p_process_action = 'Generate Invoice Number' or p_process_action = 'All' then the subsection a) happens.
93 || If the p_process_action = 'Process Accounting' or p_process_action = 'All' then the subsection b) happens.
94 ||
95 || a) make a call to an api to generate gst invoice number depending on various settings
96 || a.1) If the Parameter p_single_invoice_num is set to 'Y' , then for all the deliveries of a cust / cust site a single
97 || invoice number is generated. The call happen to the procedure to generate the gst invoice number just once
98 || and the same value retained for this record set of same cust / cust site
99 || a.2) If the parameter p_single_invoice_num is set to 'N' , then for each delivery a seperate gst invoice number
100 || will be generated.
101 || If the generate gst invoice number api returns error , then the subsection b will not be processed and the delivery
102 || will be flagged as errored in the table JAI_RGM_GST_INVOICE_GEN_T for the delivery_id
103 ||
104 || b) make a call to the api to process accounting
105 ||
106 || b.1 If it returns success then if the p_process_action = 'All' then flag both the fields GST_INV_GEN_STATUS
107 || and GST_ACCT_STATUS are to be set to completed - 'C'
108 || commit the delivery and continue with the next delivery
109 ||
110 || If it returns error (either expected error or unexpected error) and if the p_process_action = 'All' then
111 || flag the fields GST_INV_GEN_STATUS and GST_ACCT_STATUS as - Errored 'E'
112 || Rollback the delivery and continue with the next delivery.
113 ||
114
115 **************************************************************************************************************************************/
116
117 lv_acct_process_flag VARCHAR2(10);
118 lv_inv_gen_process_flag VARCHAR2(10);
119 lv_inv_gen_process_message VARCHAR2(1996);
120 lv_acct_process_message VARCHAR2(1996);
121
122 ln_failure_delivery_ctr NUMBER;
123 ln_success_delivery_Ctr NUMBER;
124 ln_regime_id NUMBER;
125 lv_debug VARCHAR2(5);
126 ln_order_type_id JAI_OM_WSH_LINES_ALL.ORDER_TYPE_ID%TYPE;
127 lv_inv_num_already_generated VARCHAR2(10);
128 ln_batch_id NUMBER;
129
130 ln_current_party_id NUMBER;
131 ln_current_party_site_id NUMBER;
132 lv_party_has_changed VARCHAR2(10);
133 ln_conc_progam_id NUMBER;
134 ln_conc_request_id NUMBER;
135 ln_conc_prog_appl_id NUMBER;
136 lb_completion_status BOOLEAN;
137 ld_override_invoice_date DATE;
138 lv_doc_type_class varchar2(2);
139
140
141 lv_SQLStmt VARCHAR2(2000);
142 v_main_rec_cur MainRec_Cur;
143 mainrec MainRecord;
144 ln_current_order_number NUMBER;
145 lv_order_has_changed VARCHAR2(10);
146 lv_p_source VARCHAR2(30);
147
148
149 sql_stmt_shippable VARCHAR2(2000);
150 sql_stmt_all VARCHAR2(2000);
151 ln_interface_status NUMBER;
152 lv_inv_gen_status VARCHAR2(1);
153
154 /*srjayara for bug 4702156*/
155
156 /*
157 || The following cursor is added to check that all the delivery lines in the delivery are inventory interfaced
158 */
159 CURSOR c_check_interface_status (cp_delivery_id NUMBER) IS
160 SELECT 1
161 FROM
162 wsh_delivery_details wdd ,
163 wsh_new_deliveries wnd ,
167 wda.Delivery_Id = wnd.Delivery_Id AND
164 wsh_delivery_assignments wda
165 WHERE
166 wdd.delivery_detail_id = wda.delivery_detail_id AND
168 wnd.Delivery_Id = cp_delivery_id AND
169 wdd.source_code = 'OE' AND
170 NVL(wdd.inv_interfaced_flag,'N') <> 'Y';
171
172
173 CURSOR c_inv_gen_status (cp_delivery_id JAI_RGM_GST_INVOICE_GEN_T.delivery_id%type,
174 cp_order_line_id JAI_RGM_GST_INVOICE_GEN_T.order_line_id%type) IS
175 SELECT gst_inv_gen_status
176 FROM JAI_RGM_GST_INVOICE_GEN_T
177 WHERE delivery_id = NVL(cp_delivery_id, -1)
178 OR order_line_id = NVL(cp_order_line_id, -1);
179
180 /*end bug 4702156*/
181
182 -- Harshita for Bug 4918870
183 p_delivery_date_from DATE DEFAULT fnd_date.canonical_to_date(pv_delivery_date_from);
184 p_delivery_date_to DATE DEFAULT fnd_date.canonical_to_date(pv_delivery_date_to);
185
186
187 ln_cgst_regime_id jai_rgm_definitions.regime_id%TYPE := gn_cgst_regime_id;
188 ln_sgst_regime_id jai_rgm_definitions.regime_id%TYPE := gn_sgst_regime_id;
189 lv_invoice_number VARCHAR2(240);
190
191
192 BEGIN
193 /*
194 ||convert from canonical to date format
195 */
196 ld_override_invoice_date := fnd_date.canonical_to_date(p_override_invoice_date);
197 lv_inv_num_already_generated := jai_constants.value_false;
198 lv_debug := NVL(P_DEBUG,jai_constants.no);
199 ln_current_party_id := -9999;
200 ln_current_party_site_id := -9999;
201 ln_current_order_number := -9999;
202
203 IF lv_debug = 'Y' THEN
204 Fnd_File.PUT_LINE(Fnd_File.LOG, ' 1. Entered in the proc with parameters :');
205 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_ORGANIZATION_ID :' || P_ORGANIZATION_ID);
206 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_LOCATION_ID :' || P_LOCATION_ID);
207 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_ORDER_NUMBER_FROM :' || P_ORDER_NUMBER_FROM);
208 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_ORDER_NUMBER_TO :' || P_ORDER_NUMBER_TO);
209 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_ID_FROM :' || P_DELIVERY_ID_FROM);
210 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_ID_TO :' || P_DELIVERY_ID_TO);
211 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_DATE_FROM :' || P_DELIVERY_DATE_FROM);
212 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_DELIVERY_DATE_TO : ' || P_DELIVERY_DATE_TO);
213 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_PROCESS_ACTION :' || P_PROCESS_ACTION);
214 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_SINGLE_INVOICE_NUM :' || P_SINGLE_INVOICE_NUM);
215 Fnd_File.PUT_LINE(Fnd_File.LOG, ' P_OVERRIDE_INVOICE_DATE :' ||P_OVERRIDE_INVOICE_DATE);
216 END IF;
217 ln_conc_progam_id := FND_GLOBAL.conc_program_id;
218 ln_conc_request_id := FND_GLOBAL.conc_request_id;
219 ln_conc_prog_appl_id := FND_GLOBAL.prog_appl_id;
220 lv_inv_gen_process_flag := jai_constants.successful;
221 lv_acct_process_flag := jai_constants.successful;
222 lv_inv_gen_process_message := NULL;
223 lv_acct_process_message := NULL;
224 ln_batch_id := ln_conc_request_id;
225
226 ln_failure_delivery_ctr :=0;
227 ln_success_delivery_Ctr :=0;
228 ln_interface_status :=0;
229
230 IF P_PROCESS_ACTION IS NULL THEN
231 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ P_PROCESS_ACTION parameter IS NULL Hence returning +++ ' );
232 RETURN;
233 END IF;
234
235 /* only for the below two cases, non-shippable items will be ignored and don't be
236 processed. So different SQL queries are used here.
237 1. p_delivery_id_from and p_delivery_id_to are both NOT NULL
238 2. p_delivery_id_from IS NOT NULL OR p_delivery_id_to IS NOT NULL and
239 p_order_number_from/p_order_number_to are both NULL.
240 */
241 sql_stmt_shippable
242 := 'SELECT delivery_id , delivery_date , organization_id , location_id , '||
243 'party_id , party_site_id , party_type , '||
244 'gst_invoice_no, gst_inv_gen_status, gst_acct_status,'||
245 'order_line_id, order_number ' ||
246 'FROM JAI_RGM_GST_INVOICE_GEN_T jrigt ' ||
247 'WHERE (delivery_id BETWEEN NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
248 'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id)) '||
249 'AND EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS wdd '||
250 ',WSH_DELIVERY_ASSIGNMENTS wda '||
251 ',OE_ORDER_HEADERS_ALL ooha '||
252 'WHERE ooha.order_number BETWEEN '||
253 'NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) AND '||
254 'NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number) '||
255 'AND ooha.header_id = wdd.source_header_id '||
256 'AND wda.delivery_detail_id = wdd.delivery_detail_id '||
257 'AND wda.delivery_id = jrigt.delivery_id) ' ||
258 'AND (TRUNC(delivery_date) BETWEEN '||
259 'NVL(TRUNC(TO_DATE('''||pv_delivery_date_from||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date) AND '||
260 'NVL(TRUNC(TO_DATE('''||pv_delivery_date_to||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date)) '||
261 'AND organization_id = NVL('||NVL(TO_CHAR(p_organization_id), 'NULL')||',organization_id) '||
262 'AND location_id = NVL('||NVL(TO_CHAR(p_location_id), 'NULL')||',location_id) ' ||
263 'AND (gst_inv_gen_status <> ''C'' OR /*gst_acct_status <> ''C''*/)'||
264 'ORDER BY party_id , party_type, party_site_id';
265
269 'gst_invoice_no, gst_inv_gen_status, gst_acct_status,'||
266 sql_stmt_all
267 := 'SELECT delivery_id , delivery_date , organization_id , location_id , '||
268 'party_id , party_site_id , party_type , '||
270 'order_line_id, order_number ' ||
271 'FROM JAI_RGM_GST_INVOICE_GEN_T jrigt ' ||
272 'WHERE (delivery_id IS NULL OR (delivery_id BETWEEN '||
273 'NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
274 'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id) '||
275 'AND EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS wdd '||
276 ',WSH_DELIVERY_ASSIGNMENTS wda '||
277 ',OE_ORDER_HEADERS_ALL ooha '||
278 'WHERE ooha.order_number BETWEEN '||
279 'NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) AND '||
280 'NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number) '||
281 'AND ooha.header_id = wdd.source_header_id '||
282 'AND wda.delivery_detail_id = wdd.delivery_detail_id '||
283 'AND wda.delivery_id = jrigt.delivery_id))) ' ||
284 'AND (order_number IS NULL '||
285 'OR order_number BETWEEN NVL('||NVL(TO_CHAR(p_order_number_from), 'NULL')||',order_number) ' ||
286 ' AND NVL('||NVL(TO_CHAR(p_order_number_to), 'NULL')||',order_number)) ' ||
287 'AND (TRUNC(delivery_date) BETWEEN '||
288 'NVL(TRUNC(TO_DATE('''||pv_delivery_date_from||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date) AND '||
289 'NVL(TRUNC(TO_DATE('''||pv_delivery_date_to||''' ,''yyyy-MM-dd HH24:MI:SS'')'||'),delivery_date)) '||
290 'AND organization_id = NVL('||NVL(TO_CHAR(p_organization_id), 'NULL')||',organization_id) '||
291 'AND location_id = NVL('||NVL(TO_CHAR(p_location_id), 'NULL')||',location_id) '||
292 'AND (gst_inv_gen_status <> ''C'' /*OR gst_acct_status <> ''C''*/)'||
293 'ORDER BY party_id , party_type, party_site_id, order_number NULLS FIRST';
294
295
296 IF (p_delivery_id_from IS NOT NULL AND p_delivery_id_to IS NOT NULL) OR
297 ((p_delivery_id_from IS NOT NULL OR p_delivery_id_to IS NOT NULL) AND
298 (p_order_number_from IS NULL AND p_order_number_to IS NULL))
299 THEN
300 lv_SQLStmt := sql_stmt_shippable;
301 IF lv_debug = 'Y'
302 THEN
303 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Query SQL for shippable items only: '||lv_SQLStmt);
304 END IF; -- lv_debug = 'Y'
305 ELSE
306 lv_SQLStmt := sql_stmt_all;
307 IF lv_debug = 'Y'
308 THEN
309 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Query SQL for shippable and non-shippable items: '||lv_SQLStmt);
310 END IF; -- lv_debug = 'Y'
311 END IF; -- p_delivery_id_from IS NOT NULL AND p_delivery_id_to IS NOT NULL
312
313 OPEN v_main_rec_cur FOR lv_SQLStmt;
314 LOOP
315 FETCH v_main_rec_cur INTO mainrec;
316 EXIT WHEN v_main_rec_cur%NOTFOUND;
317
318 --Fnd_File.PUT_LINE(Fnd_File.LOG, 'delivery_id: '||mainrec.delivery_id||' order_line_id: '||mainrec.order_line_id);
319
320 -- start processing records
321 IF mainrec.delivery_id IS NOT NULL -- for shippable line
322 THEN
323 lv_p_source := jai_constants.source_wsh;
324 ln_interface_status:= 0;
325 OPEN c_check_interface_status(mainrec.delivery_id);
326 FETCH c_check_interface_status into ln_interface_status;
327 CLOSE c_check_interface_status;
328
329 IF ln_interface_status = 1
330 THEN
331 Fnd_File.PUT_LINE(Fnd_File.LOG,
332 'Delivery - ' || mainrec.delivery_id ||
333 ' Cannot be processed because all delivery details'||
334 ' are not inventory interfaced');
335 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
336 goto NEXTDELIVERY;
337 END IF; -- ln_interface_status = 1
338 IF lv_debug = 'Y' THEN
339 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Processing Delivery - ' || mainrec.delivery_id);
340 END IF; -- lv_debug = 'Y'
341 ELSE -- else for non-shippable line
342 lv_p_source := jai_constants.source_nsh;
343 IF lv_debug = 'Y' THEN
344 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Processing Non-shippable Order Line - ' ||
345 mainrec.order_line_id);
346 END IF; -- lv_debug = 'Y'
347 END IF; -- mainrec.delivery_id IS NOT NULL
348
349 IF check_reg_dealer(mainrec.party_id,mainrec.party_site_id)
350 THEN
351 lv_doc_type_class :='O';
352 ELSE
353 lv_doc_type_class :='UO'; /*made it to UO from VO */
354 END IF; -- check_reg_dealer(mainrec.party_id,mainrec.party_site_id)
355
356 IF lv_Debug = 'Y'
357 THEN
358 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_current_party_id : ' || ln_current_party_id);
359 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.party_id : ' || mainrec.party_id);
360 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_current_party_site_id : ' || ln_current_party_site_id);
361 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.party_site_id :' || mainrec.party_site_id);
362 Fnd_File.PUT_LINE(Fnd_File.LOG, ' ln_current_order_number : ' || ln_current_order_number);
366
363 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.order_number :' || mainrec.order_number);
364 Fnd_File.PUT_LINE(Fnd_File.LOG, 'lv_doc_type_class:' || lv_doc_type_class);
365 END IF; -- lv_Debug = 'Y'
367 IF ln_current_party_id <> mainrec.party_id OR
368 ln_current_party_site_id <> mainrec.party_site_id
369 THEN
370 /*
371 || There has been a change either in the party id or the party site id .
372 || Hence a new loop needs to start
373 */
374 ln_current_party_id := mainrec.party_id;
375 ln_current_party_site_id := mainrec.party_site_id;
376 lv_party_has_changed := jai_constants.value_true;
377 lv_inv_num_already_generated :=jai_constants.value_false;--9772724
378 ELSE
379 lv_party_has_changed := jai_constants.value_false;
380 END IF; -- ln_current_party_id <> mainrec.party_id OR ... ...
381
382
383 OPEN c_inv_gen_status(mainrec.delivery_id,
384 mainrec.order_line_id);
385 FETCH c_inv_gen_status INTO lv_inv_gen_status ;
386 CLOSE c_inv_gen_status ;
387
388
389 IF lv_debug = 'Y' THEN
390 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_gen_status = '||lv_inv_gen_status );
391 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Regime:CGST:SGST Regime Id - ' ||ln_cgst_regime_id||':'||ln_sgst_regime_id);
392 Fnd_File.PUT_LINE(Fnd_File.LOG, ' Global:CGST:SGST Regime Id - ' ||gn_cgst_regime_id||':'||gn_sgst_regime_id);
393 END IF;
394
395
396 /* IF (((ln_regime_id = ln_cgst_regime_id) and (mainrec.cgst_inv_gen_status = 'C' OR lv_inv_gen_status = 'C') ) OR
397 ((ln_regime_id = ln_sgst_regime_id) and (mainrec.sgst_inv_gen_status = 'C' OR lv_inv_gen_status = 'C') ) )
398 THEN
399 GOTO Processaccounting;
400 END IF;*/
401 ------------------------------------------
402 --Added by Bo Li for GST End
403
404
405 -- here comes the detail logic of GST generation for shippable and non-shippable lines
406 IF mainrec.delivery_id IS NOT NULL -- current is shippable line
407 THEN
408 IF NVL(p_single_invoice_num,jai_constants.No) = jai_constants.yes -- single invoice number is true
409 THEN
410 IF NVL(lv_party_has_changed,jai_constants.value_false) = jai_constants.value_true -- party has changed
411 THEN
412 /* generate new GST invoice number by document sequence;
413 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
414 2. if successful, update GST invoice number to JAI_OM_WSH_LINES_ALL, and update
415 table JAI_RGM_GST_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number
416 , vat_inv_gen_status => 'C');
417 */
418 IF lv_Debug = 'Y'
419 THEN
420 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_party_has_changed :' || lv_party_has_changed);
421 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_num_already_generated :' || lv_inv_num_already_generated);
422 END IF; -- lv_Debug = 'Y'
423 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
424 THEN
425 IF lv_inv_num_already_generated = jai_constants.value_false
426 THEN
427 IF lv_Debug = 'Y' THEN
428 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with ln_order_type_id' || ln_order_type_id || 'date ' || mainrec.delivery_date );
429 END IF; -- lv_Debug = 'Y'
430 jai_cmn_rgm_setup_pkg.Gen_Invoice_number(p_regime_id => ln_cgst_regime_id
431 , p_organization_id => mainrec.organization_id
432 , p_location_id => mainrec.location_id
433 , p_date => mainrec.delivery_date
434 , p_doc_class => lv_doc_type_class
435 , p_doc_type_id => ln_order_type_id
436 , P_invoice_number => lv_invoice_number
437 , p_process_flag => lv_inv_gen_process_flag
438 , p_process_msg => lv_inv_gen_process_message
439 );
440 IF lv_Debug = 'Y' THEN
441 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after call with lv_invoice_number:' || lv_invoice_number ||','|| lv_inv_gen_process_flag ||','||lv_inv_gen_process_message);
442 END IF; -- lv_Debug = 'Y'
443
444 -- check the return status and update the JAI_OM_WSH_LINES_ALL table to set the vat invoice number
445 IF lv_inv_gen_process_flag = jai_constants.successful
446 THEN
447 IF lv_invoice_number IS NOT NULL
448 THEN
449 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
450 lv_inv_num_already_generated := jai_constants.value_true;
451 UPDATE JAI_OM_WSH_LINES_ALL
452 SET GST_INVOICE_NO = lv_invoice_number
453 , GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
454 , LAST_UPDATE_DATE = sysdate
455 , LAST_UPDATE_LOGIN = fnd_global.login_id
456 , LAST_UPDATED_BY = fnd_global.user_id
460 SET gst_invoice_no =lv_invoice_number
457 WHERE DELIVERY_ID = mainrec.delivery_id;
458
459 UPDATE JAI_RGM_GST_INVOICE_GEN_T
461 , gst_inv_gen_status ='C'
462 , gst_inv_gen_err_message = NULL
463 , request_id = ln_conc_request_id
464 , program_id = ln_conc_progam_id
465 , program_application_id = ln_conc_prog_appl_id
466 , last_update_login = fnd_global.conc_login_id
467 , last_update_date = sysdate
468 WHERE delivery_id = mainrec.delivery_id;
469 ELSE
470 lv_inv_gen_process_flag := jai_constants.unexpected_error;
471 lv_acct_process_flag := jai_constants.expected_error;
472 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
473 END IF; -- lv_vat_invoice_number IS NOT NULL
474 ELSE
475 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
476 END IF; -- lv_inv_gen_process_flag = jai_constants.successful
477 END IF; -- lv_inv_num_already_generated = jai_constants.value_false
478 END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
479
480 ELSE -- party not change
481 /* 1. use existing GST invoice number for this record;
482 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_GST_INVOICE_GEN_T;
483 */
484 IF lv_invoice_number IS NOT NULL
485 THEN
486 -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current delivery.
487 UPDATE JAI_OM_WSH_LINES_ALL
488 SET GST_INVOICE_NO = lv_invoice_number,
489 GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
490 last_update_date = sysdate,
491 last_update_login = fnd_global.login_id,
492 last_updated_by = fnd_global.user_id
493 WHERE delivery_id IN (SELECT delivery_id
494 FROM JAI_RGM_GST_INVOICE_GEN_T jrigt
495 WHERE party_id = ln_current_party_id
496 AND party_site_id = ln_current_party_site_id
497 AND party_type = mainrec.party_type
498 AND gst_inv_gen_status <> 'C'
499 AND delivery_id BETWEEN NVL(P_DELIVERY_ID_FROM,delivery_id)
500 AND NVL(P_DELIVERY_ID_TO,delivery_id)
501 AND EXISTS (SELECT 1
502 FROM WSH_DELIVERY_ASSIGNMENTS wda
503 , WSH_DELIVERY_DETAILS wdd
504 , OE_ORDER_HEADERS_ALL ooha
505 WHERE wda.delivery_id = jrigt.delivery_id
506 AND wda.delivery_detail_id = wdd.delivery_detail_id
507 AND wdd.source_header_id = ooha.header_id
508 AND ooha.order_number BETWEEN
509 NVL(p_order_number_from, ooha.order_number) AND
510 NVL(p_order_number_to, ooha.order_number))
511 AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
512 AND NVL(P_DELIVERY_DATE_TO,delivery_date));
513
514 UPDATE JAI_RGM_GST_INVOICE_GEN_T
515 SET gst_invoice_no = lv_invoice_number,
516 gst_inv_gen_status = 'C',
517 gst_inv_gen_err_message = NULL,
518 request_id = ln_conc_request_id,
519 program_id = ln_conc_progam_id,
520 program_application_id = ln_conc_prog_appl_id,
521 last_update_login = fnd_global.conc_login_id
522 WHERE delivery_id IN (SELECT delivery_id
523 FROM JAI_RGM_GST_INVOICE_GEN_T jrigt
524 WHERE party_id = ln_current_party_id
525 AND party_site_id = ln_current_party_site_id
526 AND party_type = mainrec.party_type
527 AND gst_inv_gen_status <> 'C'
528 AND delivery_id BETWEEN NVL(P_DELIVERY_ID_FROM,delivery_id)
529 AND NVL(P_DELIVERY_ID_TO,delivery_id)
530 AND EXISTS (SELECT 1
531 FROM WSH_DELIVERY_ASSIGNMENTS wda
532 , WSH_DELIVERY_DETAILS wdd
533 , OE_ORDER_HEADERS_ALL ooha
534 WHERE wda.delivery_id = jrigt.delivery_id
535 AND wda.delivery_detail_id = wdd.delivery_detail_id
536 AND wdd.source_header_id = ooha.header_id
540 AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
537 AND ooha.order_number BETWEEN
538 NVL(p_order_number_from, ooha.order_number) AND
539 NVL(p_order_number_to, ooha.order_number))
541 AND NVL(P_DELIVERY_DATE_TO,delivery_date));
542
543 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + sql%rowcount ;
544
545 IF lv_Debug = 'Y' THEN
546 Fnd_File.PUT_LINE(Fnd_File.LOG, 'No. of Deliveries updated in jai_vat_processing_t: ' || SQL%ROWCOUNT);
547 END IF; -- lv_Debug = 'Y'
548 END IF; -- lv_invoice_number IS NOT NULL
549 END IF; -- lv_party_has_changed
550 ELSE -- single invoice number is false
551 /* generate new GST invoice number by document sequence;
552 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
553 2. if successful, update GST invoice number to JAI_OM_WSH_LINES_ALL, and update
554 table JAI_RGM_GST_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
555 vat_inv_gen_status => 'C');
556 */
557 IF lv_Debug = 'Y' THEN
558 Fnd_File.PUT_LINE(Fnd_File.LOG, 'In the Else when p_single_invoice is not Y ');
559 END IF;
560 /*
561 || This is the Else Part of the IF p_single_invoice_num = 'Y' THEN
562 || In this comes the code that is needed for different generating GST invoice number for every delivery
563 */
564 IF lv_Debug = 'Y' THEN
565 Fnd_File.PUT_LINE(Fnd_File.LOG, '+++ before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number In the Else when p_single_invoice is not Y with order type = '
566 || ln_order_type_id || ' +++ ' );
567
568
569 END IF; -- lv_Debug = 'Y'
570 jai_cmn_rgm_setup_pkg.Gen_Invoice_number( p_regime_id => ln_cgst_regime_id
571 , p_organization_id => mainrec.organization_id
572 , p_location_id => mainrec.location_id
573 , p_date => mainrec.delivery_date
574 , p_doc_class => lv_doc_type_class
575 , p_doc_type_id => ln_order_type_id
576 , P_invoice_number => lv_invoice_number
577 , p_process_flag => lv_inv_gen_process_flag
578 , p_process_msg => lv_inv_gen_process_message
579 );
580 IF lv_Debug = 'Y' THEN
581 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ after call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number In the Else when p_single_invoice is not Y with lv_invoice_number = '
582 || lv_invoice_number || '+++');
583 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ after call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with lv_inv_gen_process_flag = '
584 || lv_inv_gen_process_flag || 'lv_inv_gen_process_message '|| lv_inv_gen_process_message || '+++');
585 END IF; -- lv_Debug = 'Y'
586 IF lv_inv_gen_process_flag = jai_constants.successful
587 THEN
588
589 IF lv_invoice_number IS NOT NULL
590 THEN
591 fnd_file.put_line(fnd_file.log,'ln_regime_id:'||ln_regime_id);
592 fnd_file.put_line(fnd_file.log,'ln_cgst_regime_id:'||ln_cgst_regime_id);
593 fnd_file.put_line(fnd_file.log,'ln_cgst_regime_id:'||ln_sgst_regime_id);
594 fnd_file.put_line(fnd_file.log,'mainrec.delivery_id:'||mainrec.delivery_id);
595 fnd_file.put_line(fnd_file.log,'mainrec.lv_invoice_number:'||lv_invoice_number);
596 fnd_file.put_line(fnd_file.log,'ld_override_invoice_date:'||ld_override_invoice_date);
597
598 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
599
600 UPDATE JAI_OM_WSH_LINES_ALL
601 SET GST_INVOICE_NO =lv_invoice_number,
602 GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
603 last_update_date = sysdate,
604 last_update_login = fnd_global.login_id,
605 last_updated_by = fnd_global.user_id
606 WHERE delivery_id = mainrec.delivery_id;
607
608 UPDATE JAI_RGM_GST_INVOICE_GEN_T
609 SET gst_invoice_no = lv_invoice_number,
610 gst_inv_gen_status = 'C',
611 gst_inv_gen_err_message = NULL,
612 request_id = ln_conc_request_id,
613 program_id = ln_conc_progam_id,
614 program_application_id = ln_conc_prog_appl_id,
615 last_update_login = fnd_global.conc_login_id,
616 last_update_date = sysdate
617 WHERE delivery_id = mainrec.delivery_id;
618 commit;
619 ELSE
620
621 lv_inv_gen_process_flag := jai_constants.unexpected_error;
622 lv_inv_gen_process_message := 'No GST Invoice Number Generated';
623 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
627 UPDATE JAI_RGM_GST_INVOICE_GEN_T
624 END IF; -- lv_vat_invoice_number IS NOT NULL
625 ELSE
626
628 SET gst_inv_gen_status = 'E',
629 gst_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
630 request_id = ln_conc_request_id,
631 program_id = ln_conc_progam_id,
632 program_application_id = ln_conc_prog_appl_id,
633 last_update_login = fnd_global.conc_login_id,
634 last_update_date = sysdate
635 WHERE delivery_id = mainrec.delivery_id;
636 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
637 END IF; -- lv_inv_gen_process_flag = jai_constants.successful
638 END IF; -- NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.yes
639 -- below logic is for non-shippable lines
640 ELSE -- current line is non-shippable line
641 IF ln_current_order_number <> mainrec.order_number
642 THEN
643 /*
644 || There has been a change in order number .
645 || Hence a new loop needs to start
646 */
647 ln_current_order_number := mainrec.order_number;
648 lv_order_has_changed := jai_constants.value_true;
649 ELSE
650 lv_order_has_changed := jai_constants.value_false;
651 END IF; -- ln_current_order_number <> mainrec.order_number
652
653 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_party_has_changed :' || lv_party_has_changed);
654 Fnd_File.PUT_LINE(Fnd_File.LOG, ' p_single_invoice_num :' || p_single_invoice_num);
655
656 IF NVL(p_single_invoice_num,jai_constants.No) = jai_constants.yes -- single invoice number is true
657 THEN
658 IF NVL(lv_party_has_changed,jai_constants.value_false) = jai_constants.value_true -- party has changed
659 THEN
660 /* generate new GST invoice number by document sequence;
661 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
662 2. if successful, update GST invoice number to JAI_OM_WSH_LINES_ALL, and update
663 table JAI_RGM_GST_INVOICE_GEN_T (vat_invoice_no => lv_invoice_number,
664 vat_inv_gen_status => 'C');
665 */
666 IF lv_Debug = 'Y'
667 THEN
668 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_party_has_changed :' || lv_party_has_changed);
669 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_num_already_generated :' || lv_inv_num_already_generated);
670 END IF; -- lv_Debug = 'Y'
671 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
672 THEN
673 --IF lv_inv_num_already_generated = jai_constants.value_false
674 --THEN
675 IF lv_Debug = 'Y' THEN
676 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with ln_order_type_id' || ln_order_type_id || 'date ' || mainrec.delivery_date );
677 END IF; -- lv_Debug = 'Y'
678 jai_cmn_rgm_setup_pkg.Gen_Invoice_number( p_regime_id => ln_cgst_regime_id
679 , p_organization_id => mainrec.organization_id
680 , p_location_id => mainrec.location_id
681 , p_date => mainrec.delivery_date
682 , p_doc_class => lv_doc_type_class
683 , p_doc_type_id => ln_order_type_id
684 , P_invoice_number => lv_invoice_number
685 , p_process_flag => lv_inv_gen_process_flag
686 , p_process_msg => lv_inv_gen_process_message
687 );
688 IF lv_Debug = 'Y' THEN
689 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after call with lv_invoice_number:' || lv_invoice_number || lv_inv_gen_process_flag ||lv_inv_gen_process_message);
690 END IF; -- lv_Debug = 'Y'
691
692 -- check the return status and update the JAI_OM_WSH_LINES_ALL table to set the vat invoice number
693 IF lv_inv_gen_process_flag = jai_constants.successful
694 THEN
695 IF lv_invoice_number IS NOT NULL
696 THEN
697 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
698 lv_inv_num_already_generated := jai_constants.value_true;
699 UPDATE JAI_OM_WSH_LINES_ALL
700 SET GST_INVOICE_NO = lv_invoice_number
701 , GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
702 , LAST_UPDATE_DATE = sysdate
703 , LAST_UPDATE_LOGIN = fnd_global.login_id
704 , LAST_UPDATED_BY = fnd_global.user_id
705 WHERE order_line_id = mainrec.order_line_id
706 AND delivery_id IS NULL;
707
708 UPDATE JAI_RGM_GST_INVOICE_GEN_T
709 SET gst_invoice_no = lv_invoice_number
710 , gst_inv_gen_status = 'C'
711 , gst_inv_gen_err_message = NULL
712 , request_id = ln_conc_request_id
713 , program_id = ln_conc_progam_id
717 WHERE order_line_id = mainrec.order_line_id;
714 , program_application_id = ln_conc_prog_appl_id
715 , last_update_login = fnd_global.conc_login_id
716 , last_update_date = sysdate
718 ELSE
719 lv_inv_gen_process_flag := jai_constants.unexpected_error;
720 lv_acct_process_flag := jai_constants.expected_error;
721 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
722 END IF; -- lv_vat_invoice_number IS NOT NULL
723 ELSE
724 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
725 END IF; -- lv_inv_gen_process_flag = jai_constants.successful
726 --END IF; -- lv_inv_num_already_generated = jai_constants.value_false
727 END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
728 ELSE -- party not change
729 /* 1. use existing invoice number for this record;
730 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_GST_INVOICE_GEN_T;
731 */
732 IF lv_invoice_number IS NOT NULL THEN
733 -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current non-shippable line.
734 UPDATE JAI_OM_WSH_LINES_ALL
735 SET GST_INVOICE_NO = lv_invoice_number
736 , GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
737 , last_update_date = sysdate
738 , last_update_login = fnd_global.login_id
739 , last_updated_by = fnd_global.user_id
740 WHERE order_line_id = mainrec.order_line_id
741 AND delivery_id IS NULL;
742
743 UPDATE JAI_RGM_GST_INVOICE_GEN_T
744 SET gst_invoice_no = lv_invoice_number
745 , gst_inv_gen_status = 'C'
746 , request_id = ln_conc_request_id
747 , program_id = ln_conc_progam_id
748 , program_application_id = ln_conc_prog_appl_id
749 , last_update_login = fnd_global.conc_login_id
750 WHERE order_line_id = mainrec.order_line_id;
751
752 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1 ;
753 END IF; -- lv_invoice_number IS NOT NULL
754 END IF; -- lv_party_has_changed
755 ELSE -- single invoice number is false
756 -- if single invoice number option is 'NO', then generate VAT invoice by Order Numbers
757
758 IF NVL(lv_order_has_changed,jai_constants.value_false) = jai_constants.value_true -- order number has changed
759 THEN
760 /* generate new GST invoice number by document sequence;
761 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
762 2. if successful, update GST invoice number to JAI_OM_WSH_LINES_ALL, and update
763 table JAI_RGM_GST_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
764 vat_inv_gen_status => 'C');
765 */
766 IF lv_Debug = 'Y'
767 THEN
768 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_order_has_changed :' || lv_order_has_changed);
769 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_num_already_generated :' || lv_inv_num_already_generated);
770 END IF; -- lv_Debug = 'Y'
771 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
772 THEN
773 --IF lv_inv_num_already_generated = jai_constants.value_false
774 --THEN
775 IF lv_Debug = 'Y' THEN
776 Fnd_File.PUT_LINE(Fnd_File.LOG, ' before call to jai_cmn_rgm_setup_pkg.Gen_Invoice_number with ln_order_type_id' || ln_order_type_id || 'date ' || mainrec.delivery_date );
777 END IF; -- lv_Debug = 'Y'
778 jai_cmn_rgm_setup_pkg.Gen_Invoice_number( p_regime_id => ln_cgst_regime_id
779 , p_organization_id => mainrec.organization_id
780 , p_location_id => mainrec.location_id
781 , p_date => mainrec.delivery_date
782 , p_doc_class => lv_doc_type_class
783 , p_doc_type_id => ln_order_type_id
784 , P_invoice_number => lv_invoice_number
785 , p_process_flag => lv_inv_gen_process_flag
786 , p_process_msg => lv_inv_gen_process_message
787 );
788 IF lv_Debug = 'Y' THEN
789 Fnd_File.PUT_LINE(Fnd_File.LOG, ' after call with lv_invoice_number:' || lv_invoice_number || lv_inv_gen_process_flag ||lv_inv_gen_process_message);
790 END IF; -- lv_Debug = 'Y'
791
792 -- check the return status and update the JAI_OM_WSH_LINES_ALL table to set the vat invoice number
793 IF lv_inv_gen_process_flag = jai_constants.successful
794 THEN
795 IF lv_invoice_number IS NOT NULL
796 THEN
797
798 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
799 lv_inv_num_already_generated := jai_constants.value_true;
800 UPDATE JAI_OM_WSH_LINES_ALL
801 SET GST_INVOICE_NO = lv_invoice_number
805 , LAST_UPDATED_BY = fnd_global.user_id
802 , GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
803 , LAST_UPDATE_DATE = sysdate
804 , LAST_UPDATE_LOGIN = fnd_global.login_id
806 WHERE order_line_id = mainrec.order_line_id
807 AND delivery_id IS NULL;
808
809 UPDATE JAI_RGM_GST_INVOICE_GEN_T
810 SET gst_invoice_no = lv_invoice_number
811 ,gst_inv_gen_status = 'C'
812 ,gst_inv_gen_err_message = NULL
813 , request_id = ln_conc_request_id
814 , program_id = ln_conc_progam_id
815 , program_application_id = ln_conc_prog_appl_id
816 , last_update_login = fnd_global.conc_login_id
817 , last_update_date = sysdate
818 WHERE order_line_id = mainrec.order_line_id;
819 ELSE
820 lv_inv_gen_process_flag := jai_constants.unexpected_error;
821 lv_acct_process_flag := jai_constants.expected_error;
822 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
823 END IF; -- lv_invoice_number IS NOT NULL
824
825
826 ELSE
827 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
828 END IF; -- lv_inv_gen_process_flag = jai_constants.successful
829 --END IF; -- lv_inv_num_already_generated = jai_constants.value_false
830 END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
831 ELSE -- order number not change
832 /* 1. use existing gst invoice number for this record;
833 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_GST_INVOICE_GEN_T;
834 */
835 IF lv_invoice_number IS NOT NULL THEN
836 -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current delivery.
837 UPDATE JAI_OM_WSH_LINES_ALL
838 SET GST_INVOICE_NO = lv_invoice_number
839 , GST_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate)
840 , last_update_date = sysdate
841 , last_update_login = fnd_global.login_id
842 , last_updated_by = fnd_global.user_id
843 WHERE order_line_id = mainrec.order_line_id
844 AND delivery_id IS NULL;
845
846 UPDATE JAI_RGM_GST_INVOICE_GEN_T
847 SET gst_invoice_no = lv_invoice_number
848 , gst_inv_gen_status = 'C'
849 , gst_inv_gen_err_message = NULL
850 , request_id = ln_conc_request_id
851 , program_id = ln_conc_progam_id
852 , program_application_id = ln_conc_prog_appl_id
853 , last_update_login = fnd_global.conc_login_id
854 WHERE order_line_id = mainrec.order_line_id;
855
856 ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
857 END IF; -- lv_vat_invoice_number IS NOT NULL
858 END IF; -- lv_order_has_changed
859 END IF; -- p_single_invoice_num
860
861
862 END IF; -- IF mainrec.delivery_id IS NOT NULL
863
864 -- modified by Allen Yang for for bug 9485355 (12.1.3 non-shippable Enhancement), end
865
866 -- Now process the om-ar accounting if it is needed
867
868 /* <<Processaccounting>>
869
870 IF lv_debug = 'Y' THEN
871 Fnd_File.PUT_LINE(Fnd_File.LOG, ' In process accounting section with p_process_action = ' || p_process_action);
872 END IF;
873
874 IF p_process_action in (jai_constants.om_action_gen_inv_n_accnt,jai_constants.om_action_gen_accounting) THEN
875 \*
876 || Only In case the parameter p_process_action in ('PROCESS ALL','PROCESS ACCOUNTING') AND
877 ||
878 *\
879 IF lv_Debug = 'Y' THEN
880 Fnd_File.PUT_LINE(Fnd_File.LOG, ' mainrec.vat_acct_status : ' || mainrec.vat_acct_status || ' lv_inv_gen_process_flag : ' || lv_inv_gen_process_flag );
881 END IF;
882 IF mainrec.vat_acct_status = 'C' THEN
883 GOTO NEXTDELIVERY;
884 END IF;
885 IF lv_Debug = 'Y' THEN
886 Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_inv_gen_process_flag = ' || lv_inv_gen_process_flag );
887 END IF;
888 IF lv_inv_gen_process_flag = jai_constants.successful THEN
889
890 IF lv_vat_invoice_number IS NULL THEN
891 lv_vat_invoice_number:= mainrec.vat_invoice_no;
892 END IF;
893
894 IF p_process_action = jai_constants.om_action_gen_accounting
895 THEN
896 lv_vat_invoice_number := mainrec.vat_invoice_no;
897 END IF;
898
899 IF lv_Debug = 'Y' THEN
900 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Before Call to jai_cmn_rgm_vat_accnt_pkg.process_order_invoice ');
901 END IF;
902 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice(
903 P_REGIME_ID => ln_regime_id ,
904 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
905 P_SOURCE => lv_p_source,
909 P_LOCATION_ID => mainrec.location_id ,
906 -- P_SOURCE => jai_constants.source_wsh ,
907 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
908 P_ORGANIZATION_ID => mainrec.organization_id,
910 P_DELIVERY_ID => mainrec.delivery_id ,
911 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
912 P_ORDER_LINE_ID => mainrec.order_line_id ,
913 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
914 P_CUSTOMER_TRX_ID => NULL ,
915 P_VAT_INVOICE_NO => lv_vat_invoice_number ,
916 P_TRANSACTION_TYPE => jai_cmn_rgm_vat_accnt_pkg.gv_transaction_type_dflt,
917 P_DEFAULT_INVOICE_DATE => NVL(ld_override_invoice_date,SYSDATE),
918 P_BATCH_ID => ln_batch_id ,
919 P_CALLED_FROM => 'jai_cmn_rgm_processing_pkg.PROCESS',
920 P_DEBUG => lv_debug ,
921 P_PROCESS_FLAG => lv_acct_process_flag ,
922 P_PROCESS_MESSAGE => lv_acct_process_message
923 );
924 IF lv_Debug = 'Y' THEN
925 Fnd_File.PUT_LINE(Fnd_File.LOG, 'after Call to jai_cmn_rgm_vat_accnt_pkg.process_order_invoice with status = ' || lv_acct_process_flag);
926 END IF;
927 IF lv_acct_process_flag = jai_constants.successful THEN
928 \*
929 || If the control comes here it means that Accounting got processed successfully.
930 || Check here if Delivery for successfully processed and invoice got successfully processed
931 || and only then do a commit
932 *\
933 IF lv_inv_gen_process_flag = jai_constants.successful AND lv_acct_process_flag = jai_constants.successful THEN
934 \*
935 || Both the activities have been succesfully completed
936 || Can commit the changes made to the delivery.
937 *\
938 UPDATE JAI_RGM_GST_INVOICE_GEN_T
939 SET vat_acct_status = 'C',
940 vat_inv_gen_err_message = NULL, \*following columns added by srjayara for bug 4702156*\
941 request_id = ln_conc_request_id,
942 program_id = ln_conc_progam_id,
943 program_application_id = ln_conc_prog_appl_id,
944 last_update_login = fnd_global.conc_login_id,
945 last_update_date = sysdate
946 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
947 -- WHERE delivery_id = mainrec.delivery_id;
948 WHERE delivery_id = NVL(mainrec.delivery_id, -1)
949 OR order_line_id = NVL(mainrec.order_line_id, -1);
950 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
951 COMMIT;
952 END IF;
953 ELSE
954 ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
955 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Error Encountered after call to process_order_invoice is ' || lv_acct_process_message);
956 END IF;
957
958 IF lv_inv_gen_process_flag <> jai_constants.successful OR lv_acct_process_flag <> jai_constants.successful THEN
959
960 \*
961 || There have been some errors which have happened during accounting
962 *\
963 ROLLBACK;
964
965 IF lv_inv_gen_process_flag <> jai_constants.successful THEN
966 UPDATE JAI_RGM_GST_INVOICE_GEN_T
967 SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
968 vat_inv_gen_status = 'E',
969 request_id = ln_conc_request_id, \*following columns added by srjayara for bug 4702156*\
970 program_id = ln_conc_progam_id,
971 program_application_id = ln_conc_prog_appl_id,
972 last_update_login = fnd_global.conc_login_id,
973 last_update_date = sysdate
974 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
975 -- WHERE delivery_id = mainrec.delivery_id;
976 WHERE delivery_id = NVL(mainrec.delivery_id, -1)
977 OR order_line_id = NVL(mainrec.order_line_id, -1);
978 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
979 END IF;
980
981 IF lv_acct_process_flag <> jai_constants.successful THEN
982 UPDATE JAI_RGM_GST_INVOICE_GEN_T
983 SET vat_acct_err_message = substr(lv_acct_process_message,1,1000),
984 vat_acct_status = 'E',
985 request_id = ln_conc_request_id, \*following columns added by srjayara for bug 4702156*\
986 program_id = ln_conc_progam_id,
987 program_application_id = ln_conc_prog_appl_id,
988 last_update_login = fnd_global.conc_login_id,
989 last_update_date = sysdate
990 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
991 -- WHERE delivery_id = mainrec.delivery_id;
992 WHERE delivery_id = NVL(mainrec.delivery_id, -1)
993 OR order_line_id = NVL(mainrec.order_line_id, -1);
994 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
995 END IF;
996 COMMIT;
997 END IF;
998
999 END IF; \* END IF For IF lv_inv_gen_process_flag = jai_constants.successful THEN *\
1000 END IF; \* END IF For IF p_process_action in ('ALL','PROCESS ACCOUNTING') THEN *\*/
1001 <<NEXTDELIVERY>>
1002 NULL;
1003 END LOOP;
1004
1005
1006 -- need to purse JAI_RGM_GST_INVOICE_GEN_T at the end of this concurrent
1007 -- Need to do
1008 /* DELETE FROM JAI_RGM_GST_INVOICE_GEN_T
1009 WHERE vat_inv_gen_status = 'C'
1010 AND vat_acct_status = 'C';*/
1011
1012
1013 COMMIT;
1014 /*
1015 || Coding here to mark the status of the concurrent and generating statictics.
1016 ||
1017 */
1018 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ Number of Successful deliveries : ' || ln_success_delivery_Ctr || '+++');
1019 Fnd_File.PUT_LINE(Fnd_File.LOG, ' +++ Number of Failed deliveries : ' || ln_failure_delivery_ctr || '+++');
1020
1021 IF ln_failure_delivery_ctr > 0 AND ln_success_delivery_Ctr > 0 then
1022 /*
1023 || Atleast one delivery failed Atleast one delivery Succeeded
1024 || Signal completion with warning
1025 */
1026 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', NVL(lv_acct_process_message,lv_inv_gen_process_message));
1027 retcode := '1';
1028 END IF;
1029 IF ln_failure_delivery_ctr = 0 AND ln_success_delivery_Ctr > 0 then
1030 /*
1031 || Atleast one delivery Succeeded and none failed
1032 || Signal completion with success
1033 */
1034 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', NULL);
1035 retcode := '0';
1036 END IF;
1037 IF ln_failure_delivery_ctr > 0 AND ln_success_delivery_Ctr = 0 then
1038 /*
1039 || Atleast one delivery failed and No delivery Succeeded
1040 || Signal completion with error
1041 */
1042 retcode := '2';
1043 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NVL(lv_acct_process_message,lv_inv_gen_process_message));
1044 END IF;
1045 IF ln_failure_delivery_ctr = 0 AND ln_success_delivery_Ctr = 0 then
1046 /*
1047 || No delivery failed and No delivery Succeeded
1048 || Signal completion with Success
1049 */
1050 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', NULL);
1051 retcode := '0';
1052 END IF;
1053
1054
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 RETCODE := '2';
1058 Fnd_File.PUT_LINE(Fnd_File.LOG,'Unexpected Error occured in procedure jai_cmn_rgm_processing_pkg.process '||substr(sqlerrm,1,300));
1059 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',substr(sqlerrm,1,1000));
1060 ERRBUF := substr(sqlerrm,1,1000);
1061 lv_inv_gen_process_flag := jai_constants.unexpected_error;
1062 lv_acct_process_flag := jai_constants.unexpected_error;
1063 lv_inv_gen_process_message := sqlerrm;
1064 lv_acct_process_message := sqlerrm;
1065
1066 END PROCESS;
1067
1068 BEGIN
1069
1070 gn_cgst_regime_id := JAI_GST_GENERAL_PKG.get_regime_id(jai_constants.cgst_regime);
1071 gn_sgst_regime_id := JAI_GST_GENERAL_PKG.get_regime_id(jai_constants.sgst_regime);
1072
1073 end jai_cmn_gst_inv_gen_pkg;