[Home] [Help]
PACKAGE BODY: APPS.JAI_TAX_PROCESSING_PKG
Source
1 package body JAI_TAX_PROCESSING_PKG as
2 /* $Header: jai_tax_prc.plb 120.41.12020000.3 2013/05/07 09:56:09 cholei noship $ */
3
4 /*---------------------------------------------------------------------------------------------------------------------------------+
5 | Created By : zhiwei.xin |
6 | Creation Date : 04/May/2012 |
7 | Bug Number/ER Name : ZX Integration |
8 | SubProgram Name : calculate_tax |
9 | Type : PROCEDURE |
10 | Purpose : Calculate JAI tax when validate AP invoice. |
11 | TDD Reference : Section 10.1 |
12 | Assumptions : |
13 | Called From : ZX_SRVC_TYP_PKG.validate_document_for_tax |
14 |--------------------------------------------------------------------------------------------------------------------------------|
15 | parameters IN/OUT Type Required Description and Purpose |
16 | ------------ -------- ------ ---------- ------------------------- |
17 ---------------------------------------------------------------------------------------------------------------------------------*/
18
19 /* ======================================================================*
20 | Global Data Types |
21 * ======================================================================*/
22
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_TAX_PROCESSING_PKG';
24 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
25 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
26 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
27 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
28 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
29 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
30
31 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
32 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
33 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
34 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
35 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
36 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
37 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
38 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'JAI.PLSQL.JAI_TAX_PROCESSING_PKG.';
39
40 GN_SET_BOOK_ID number;
41 GV_JAI_EXISTS boolean := false;
42
43 /*-------------------------------------------------------------------------------------------------------------------------------+
44 | Created By : zhiwei.xin |
45 | Creation Date : 04/May/2012 |
46 | Bug Number/ER Name : ZX Integration |
47 | SubProgram Name : calculate_tax |
48 | Type : PROCEDURE |
49 | Purpose : Calculate JAI tax when perform Prepayment Unapplication |
50 | TDD Reference : Section 10.3 |
51 | Assumptions : |
52 | Called From : ZX_NEW_SERVICES_PKG.cancel_tax_lines |
53 |--------------------------------------------------------------------------------------------------------------------------------|
54 | parameters IN/OUT Type Required Description and Purpose |
55 | ------------ -------- ------ ---------- ------------------------- |
56 | p_transaction_rec IN RECORD yes zx transaction record |
57 | p_line_level_action IN VARCHAR2 yes line level action |
58 | p_trx_line_id IN NUMBER yes invoice id |
59 | x_return_status IN VARCHAR2 yes Return status |
60 ---------------------------------------------------------------------------------------------------------------------------------*/
61 procedure calculate_tax(p_transaction_rec ZX_API_PUB.transaction_rec_type,
62 p_event_class_rec ZX_API_PUB.event_class_rec_type DEFAULT NULL,
63 p_line_level_action VARCHAR2,
64 p_trx_line_id NUMBER
65 ,x_return_status OUT NOCOPY VARCHAR2) is --Added x_return_status by Chong for displaying message to ZX 2012/09/02
66
67 lv_action VARCHAR2(20);
68 lv_return_message VARCHAR2(2000);
69 lv_return_code VARCHAR2(100);
70 le_error EXCEPTION;
71 l_api_name CONSTANT VARCHAR2(30) := 'CALCULATE_TAX()';
72
73 -- added by zhiwei.xin for ZX integration of OM part on 2-AUG-2012 begin.
74 rec_jsl JAI_OM_OE_SO_LINES%ROWTYPE;
75 rec_jrl JAI_OM_OE_RMA_LINES%ROWTYPE;
76 rec_old_ool OE_ORDER_LINES_ALL%ROWTYPE;
77 -- added by zhiwei.xin for ZX integration of OM part on 2-AUG-2012 end.
78
79
80 --get distribution lines in one invoice
81 cursor c_get_inv_dists(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
82 is
83 select *
84 from ap_invoice_distributions_all
85 where invoice_id = cp_trx_id;
86
87 -- cursor to get prepay distribution lines for unapplication
88 cursor c_get_inv_dist_line(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
89 is
90 select aida.*
91 from ap_invoice_distributions_all aida
92 where aida.invoice_id = cp_trx_id
93 and aida.line_type_lookup_code = 'PREPAY'
94 and aida.amount > 0
95 and aida.prepay_distribution_id is not null
96 and not exists (select 1
97 from jai_ap_tds_prepayments
98 where invoice_id = cp_trx_id
99 and invoice_distribution_id_prepay = aida.parent_reversal_id
100 and unapply_flag = 'Y');
101
102 --Added by Cholei for bug#14189751 begin
103 ------------------------------------------------------------------------------------
104 -- cursor to get invoice distribution line with match status flag
105 cursor c_get_inv_dist(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
106 is
107 select invoice_id
108 ,invoice_distribution_id
109 ,match_status_flag
110 from ap_invoice_distributions_all
111 where invoice_id = cp_trx_id
112 and match_status_flag is not null;
113 ------------------------------------------------------------------------------------
114 --Added by Cholei for bug#14189751 end
115
116 --Added by Zhiwei.xin for ZX integration of OM part begin
117 -- cursor to get newly created SO
118 cursor c_order_lines_entry(cp_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE)
119 is
120 select *
121 from OE_ORDER_LINES_ALL oola
122 where oola.HEADER_ID = cp_header_id
123 and not exists (select 1
124 from JAI_OM_OE_RMA_LINES joorl
125 where joorl.RMA_HEADER_ID = cp_header_id
126 and oola.LINE_ID = joorl.RMA_LINE_ID)
127 and not exists (select 1
128 from JAI_OM_OE_SO_LINES joosl
129 where joosl.HEADER_ID = cp_header_id
130 and oola.LINE_ID = joosl.LINE_ID);
131
132 -- cursor to get existing SO Lines
133 cursor c_order_lines_update(cp_header_id OE_ORDER_LINES.HEADER_ID%TYPE)
134 is
135 select *
136 from OE_ORDER_LINES oola
137 where oola.HEADER_ID = cp_header_id
138 and ( exists (select 1
139 from JAI_OM_OE_SO_LINES joosl
140 where joosl.HEADER_ID = cp_header_id
141 and oola.LINE_ID = joosl.LINE_ID)
142 or exists (select 1
143 from JAI_OM_OE_RMA_LINES joorl
144 where joorl.RMA_HEADER_ID = cp_header_id
145 and oola.LINE_ID = joorl.RMA_LINE_ID));
146
147 cursor c_jai_so_lines (cp_header_id JAI_OM_OE_SO_LINES.HEADER_ID%TYPE,
148 cp_line_id JAI_OM_OE_SO_LINES.LINE_ID%TYPE)
149 is
150 select *
151 from JAI_OM_OE_SO_LINES
152 where HEADER_ID = cp_header_id
153 and LINE_ID = cp_line_id;
154
155 cursor c_jai_rma_lines (cp_header_id JAI_OM_OE_SO_LINES.HEADER_ID%TYPE,
156 cp_line_id JAI_OM_OE_SO_LINES.LINE_ID%TYPE)
157 is
158 select *
159 from JAI_OM_OE_RMA_LINES
160 where RMA_HEADER_ID = cp_header_id
161 and RMA_LINE_ID = cp_line_id;
162 --Added by Zhiwei.xin for ZX integration of OM part end.
163
164 --Added by Junjian for ZX integration of Invoice lines discard begin
165 cursor c_get_inv_line(cp_invoice_id AP_INVOICE_LINES_ALL.INVOICE_ID%TYPE,
166 cp_line_id AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
167 is
168 select * from AP_INVOICE_LINES_ALL
169 where invoice_id = cp_invoice_id
170 and line_number= cp_line_id;
171
172 t_ap_inv_line_rec c_get_inv_line%rowtype;
173 lv_codepath varchar2(2000);
174 --Added by Junjian for ZX integration of Invoice lines discard end
175
176 begin
177 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
178 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
179 end if;
180
181 if nvl(p_event_class_rec.application_id, p_transaction_rec.application_id) = 200 then -- called from AP
182
183 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_ERROR, G_MODULE_NAME||l_api_name, p_event_class_rec.event_class_code);
184 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_ERROR, G_MODULE_NAME||l_api_name, p_event_class_rec.EVENT_TYPE_CODE );
185 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_ERROR, G_MODULE_NAME||l_api_name, p_event_class_rec.tax_event_type_code);
186
187 /**Prepayment unapplication*/
188 if p_line_level_action in ('UNAPPLY_FROM') and p_trx_line_id is not NULL then -- Prepayment Unapply Action
189 lv_action := JAI_CONSTANTS.inserting;
190
191 --Added by Cholei for bug#14189751 begin
192 ------------------------------------------------------------------------------------
193 /*
194 Need update the latest match status flag before perform prepay application,
195 otherwise jai_ap_tds_generation_pkg.status_update_chk_validate will prevent from unappying.
196 */
197 FOR inv_dist_rec in c_get_inv_dist(p_transaction_rec.TRX_ID)
198 LOOP
199 update jai_ap_tds_inv_taxes
200 set match_status_flag = inv_dist_rec.match_status_flag
201 where invoice_id = inv_dist_rec.invoice_id
202 and invoice_distribution_id = inv_dist_rec.invoice_distribution_id;
203 END LOOP;
204 ------------------------------------------------------------------------------------
205 --Added by Cholei for bug#14189751 end
206
207 /*
208 Fetch prepay distribution lines for prepay unapplication (amount > 0).
209 Datas (with unapply_flag = 'Y') in JAI_AP_TDS_PREPAYMENT indicate the prepay distributions had already
210 been unapplied, so they should not be fetched out for processing.
211 */
212
213 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
214 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call unpply for invoice: ' || p_transaction_rec.TRX_ID );
215 end if;
216
217 FOR ap_inv_dist_rec in c_get_inv_dist_line(p_transaction_rec.TRX_ID)
218 LOOP
219
220 JAI_AP_IDA_TRIGGER_DTC_PKG.BRIUD_DTC_T1 (
221 pr_old => null ,
222 pr_new => ap_inv_dist_rec ,
223 pv_action => lv_action ,
224 pv_return_code => lv_return_code ,
225 pv_return_message => lv_return_message
226 );
227
228 IF lv_return_code <> jai_constants.successful then
229 RAISE le_error;
230 END IF;
231
232
233 END LOOP;
234
235 /**Invoice validation*/
236 elsif p_event_class_rec.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES') and
237 p_event_class_rec.tax_event_type_code in ('VALIDATE') then -- Validate Action
238
239 lv_action := JAI_CONSTANTS.updating;
240
241 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
242 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call validation for invoice: ' || NVL(p_event_class_rec.TRX_ID , p_transaction_rec.TRX_ID));
243 end if;
244
245 for ap_inv_dist_rec in c_get_inv_dists(NVL(p_event_class_rec.TRX_ID , p_transaction_rec.TRX_ID))
246 loop
247
248 --Added by Junjian for ZX intergration on 07-Sep-2012 begin
249 --Check if the tax from PO/Recepit is copied
250 /* lv_codepath := null;
251 jai_ap_match_tax_proc_pkg.tax_check_at_inv_validate
252 (
253 p_invoice_id => ap_inv_dist_rec.invoice_id,
254 p_invoice_line_number => ap_inv_dist_rec.invoice_line_number,
255 p_process_flag => lv_return_code,
256 p_process_message => lv_return_message,
257 p_codepath => lv_codepath
258 );
259 if nvl(lv_return_code, 'N') = 'E' then
260 RAISE le_error;
261 end if;
262 */
263 --Added by Junjian for ZX intergration on 07-Sep-2012 end
264
265 JAI_AP_IDA_TRIGGER_DTC_PKG.BRIUD_DTC_T1(pr_old => null,
266 pr_new => ap_inv_dist_rec,
267 pv_action => lv_action,
268 pv_return_code => lv_return_code,
269 pv_return_message => lv_return_message);
270
271 if lv_return_code <> JAI_CONSTANTS.successful then
272 RAISE le_error;
273 end if;
274 end loop;
275 --Added by Qiong for AP Open Interface Begin
276
277 elsif p_event_class_rec.event_class_code in ('STANDARD INVOICES') and -- AP Open Interface
278 ((p_event_class_rec.EVENT_TYPE_CODE in ('STANDARD CREATED') and
279 p_event_class_rec.tax_event_type_code in ('CREATE')
280 ) OR
281 (p_event_class_rec.tax_event_type_code IN ('UPDATE') AND --Added update by Chong for open interface issue 2013/04/09
282 p_event_class_rec.event_type_code IN ('STANDARD UPDATED')
283 )
284 ) THEN
285
286 jai_tax_processing_pkg.open_interface
287 ( p_event_class_rec => p_event_class_rec,
288 pv_return_status => x_return_status
289 );
290
291 --Added by Qiong for AP Open Interface End
292 end if;
293 --Added by Junjian for ZX Integration of AP Invoice line discard begin
294 IF p_line_level_action IN ('DISCARD') AND p_transaction_rec.TRX_ID IS NOT NULL
295 AND p_trx_line_id is not NULL THEN
296 open c_get_inv_line(p_transaction_rec.TRX_ID,p_trx_line_id);
297 fetch c_get_inv_line into t_ap_inv_line_rec;
298 close c_get_inv_line;
299 --only process for matched invoice.
300 IF t_ap_inv_line_rec.match_type IN( 'ITEM_TO_RECEIPT', 'ITEM_TO_PO') THEN
301 jai_ap_match_tax_proc_pkg.process_discard(t_ap_inv_line_rec);
302 END IF;
303 END IF;
304 --Added by Junjian for ZX Integration of AP Invoice line discard end
305
306
307
308 --Added by Wenqiong for PO Integration with ZX begin
309 ELSIF nvl(p_event_class_rec.application_id, p_transaction_rec.application_id) = 201 then -- called from PO
310 IF p_event_class_rec.application_id = 201 THEN
311 IF p_event_class_rec.entity_code = 'PURCHASE_ORDER'
312 AND p_event_class_rec.event_class_code = 'PO_PA'
313 AND p_event_class_rec.event_type_code IN ('PO_PA_CREATED','PO_PA_ADJUSTED')
314 THEN
315 IF p_event_class_rec.event_type_code = 'PO_PA_CREATED' THEN
316 lv_action := JAI_CONSTANTS.inserting;
317 ELSIF p_event_class_rec.event_type_code = 'PO_PA_ADJUSTED' THEN
318 lv_action := JAI_CONSTANTS.updating;
319 END IF;
320 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
321 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call jai_po_proc_pkg.process_po with trx_id ' || p_event_class_rec.trx_id ||', action:'||lv_action);
322 end if;
323 jai_po_proc_pkg.process_po(p_event_class_rec.trx_id, lv_action);
324
325 ELSIF p_event_class_rec.entity_code = 'RELEASE'
326 AND p_event_class_rec.event_class_code = 'RELEASE'
327 AND p_event_class_rec.event_type_code IN ('RELEASE_CREATED','RELEASE_ADJUSTED')
328 THEN
329 IF p_event_class_rec.event_type_code = 'RELEASE_CREATED' THEN
330 lv_action := JAI_CONSTANTS.inserting;
331 ELSIF p_event_class_rec.event_type_code = 'RELEASE_ADJUSTED' THEN
332 lv_action := JAI_CONSTANTS.updating;
333 END IF;
334 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
335 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call jai_po_proc_pkg.process_release with trx_id ' || p_event_class_rec.trx_id ||', action:'||lv_action);
336 end if;
337 jai_po_proc_pkg.process_release(p_event_class_rec.trx_id, lv_action);
338 ELSIF p_event_class_rec.entity_code = 'REQUISITION'
339 AND p_event_class_rec.event_class_code = 'REQUISITION'
340 AND p_event_class_rec.event_type_code IN ('REQ_CREATED') THEN
341 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
342 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call jai_po_proc_pkg.process_requisition with trx_id ' || p_event_class_rec.trx_id );
343 end if;
344
345 jai_po_proc_pkg.process_requisition(p_event_class_rec.trx_id);
346 END IF;
347 ELSIF p_transaction_rec.application_id = 201 THEN--Indicates PO Module
348 IF p_transaction_rec.entity_code = 'PURCHASE_ORDER'AND p_transaction_rec.event_class_code = 'PO_PA' THEN
349 IF p_transaction_rec.event_type_code IN( 'PO_PA_ADJUSTED') THEN
350 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
351 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call jai_po_proc_pkg.delete_po_taxes and delete_jai_po_lines with p_trx_line_id ' || p_trx_line_id );
352 end if;
353 jai_po_proc_pkg.delete_po_taxes(pn_line_loc_id => p_trx_line_id );
354 jai_po_proc_pkg.delete_jai_po_lines(pn_line_loc_id => p_trx_line_id);
355 ELSIF p_transaction_rec.event_type_code IN( 'PO_PA_CANCELLED') THEN
356 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
357 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call jai_po_proc_pkg.cancel_po with TRX_ID' || p_transaction_rec.TRX_ID );
358 end if;
359 jai_po_proc_pkg.cancel_po(p_transaction_rec.TRX_ID);
360 END IF;
361
362 END IF;
363
364 END IF;
365 --Added by Wenqiong for PO Integration with ZX end
366
367 -- added by zhiwei.xin for ZX Integration of OM part begin.
368 ELSIF (p_event_class_rec.application_id = 660) THEN --Indicates OM Module
369 IF p_event_class_rec.tax_event_class_code IN ('SALES_TRANSACTION') AND
370 p_event_class_rec.tax_event_type_code IN ('CREATE') THEN
371
372 -- for order creation
373 lv_action := JAI_CONSTANTS.inserting;
374 FOR rec_ool in c_order_lines_entry (p_event_class_rec.trx_id)
375 LOOP
376 jai_om_tax_processing_pkg.default_tax (
377 pr_old => null ,
378 pr_new => rec_ool ,
379 pv_action => lv_action ,
380 pv_return_code => lv_return_code ,
381 pv_return_message => lv_return_message
382 );
383 if lv_return_code <> JAI_CONSTANTS.successful then
384 RAISE le_error;
385 end if;
386 jai_om_tax_processing_pkg.populate_tax (
387 pr_old => null ,
388 pr_new => rec_ool ,
389 pv_action => lv_action ,
390 pv_return_code => lv_return_code ,
391 pv_return_message => lv_return_message
392 );
393 if lv_return_code <> JAI_CONSTANTS.successful then
394 RAISE le_error;
395 end if;
396
397 -- added by zhiwei.xin for Trigger Replacement bug #15968958 on 20-Dec-2012 begin
398
399 jai_om_tax_processing_pkg.order_line_validation(p_rec_old => null,
400 p_rec_new => rec_ool,
401 p_action => lv_action,
402 px_return_code => lv_return_code,
403 px_return_message => lv_return_message);
404 if lv_return_code <> JAI_CONSTANTS.successful then
405 RAISE le_error;
406 end if;
407 -- added by zhiwei.xin for Trigger Replacement bug #15968958 on 20-Dec-2012 end.
408
409 END LOOP;
410 -- for order update
411 lv_action := jai_constants.updating;
412 FOR rec_ool in c_order_lines_update (p_event_class_rec.trx_id)
413 LOOP
414
415
416 if (rec_ool.LINE_CATEGORY_CODE = 'ORDER') then
417
418 open c_jai_so_lines (rec_ool.HEADER_ID, rec_ool.LINE_ID);
419 fetch c_jai_so_lines into rec_jsl;
420 close c_jai_so_lines;
421
422 rec_old_ool.LINE_NUMBER := rec_jsl.LINE_NUMBER;
423 rec_old_ool.SHIPMENT_NUMBER := rec_jsl.SHIPMENT_LINE_NUMBER;
424 rec_old_ool.INVENTORY_ITEM_ID := rec_jsl.INVENTORY_ITEM_ID;
425 rec_old_ool.SHIP_TO_ORG_ID := rec_jsl.SHIP_TO_ORG_ID;
426 rec_old_ool.ORDERED_QUANTITY := rec_jsl.QUANTITY;
427 rec_old_ool.UNIT_SELLING_PRICE := rec_jsl.SELLING_PRICE;
428
429 else
430
431 open c_jai_rma_lines (rec_ool.HEADER_ID, rec_ool.LINE_ID);
432 fetch c_jai_rma_lines into rec_jrl;
433 close c_jai_rma_lines;
434
435 rec_old_ool.LINE_NUMBER := rec_ool.LINE_NUMBER;
436 rec_old_ool.SHIPMENT_NUMBER := rec_ool.SHIPMENT_NUMBER;
437 rec_old_ool.INVENTORY_ITEM_ID := rec_jrl.INVENTORY_ITEM_ID;
438 rec_old_ool.SHIP_TO_ORG_ID := rec_jrl.SHIP_TO_ORG_ID;
439 rec_old_ool.ORDERED_QUANTITY := rec_jrl.QUANTITY;
440 rec_old_ool.UNIT_SELLING_PRICE := rec_jrl.SELLING_PRICE;
441
442 end if;
443
444 jai_om_tax_processing_pkg.default_tax (
445 pr_old => rec_old_ool ,
446 pr_new => rec_ool ,
447 pv_action => lv_action ,
448 pv_return_code => lv_return_code ,
449 pv_return_message => lv_return_message
450 );
451
452 if lv_return_code <> JAI_CONSTANTS.successful then
453 RAISE le_error;
454 end if;
455
456 IF ( nvl(rec_ool.ordered_quantity,0) <> nvl(rec_old_ool.ordered_quantity,0) OR
457 nvl(rec_ool.unit_selling_price,0) <> nvl(rec_old_ool.unit_selling_price,0) OR
458 (
459 ( rec_ool.inventory_item_id <> rec_old_ool.inventory_item_id) AND
460 ( rec_ool.line_category_code = 'RETURN' ) AND
461 ( rec_ool.return_context IS NULL )
462 )
463 )
464 THEN
465
466 jai_om_tax_processing_pkg.update_tax (
467 pr_old => rec_old_ool ,
468 pr_new => rec_ool ,
469 pv_action => lv_action ,
470 pv_return_code => lv_return_code ,
471 pv_return_message => lv_return_message
472 );
473 if lv_return_code <> JAI_CONSTANTS.successful then
474 RAISE le_error;
475 end if;
476
477 END IF;
478
479 if (
480 (
481 ( NVL(rec_ool.ship_to_org_id,0) <> NVL(rec_old_ool.ship_to_org_id,0) OR
482 rec_old_ool.ship_to_org_id IS NULL
483 ) AND
484 ( nvl(rec_ool.flow_status_code,'###') NOT IN ('CANCELLED','CLOSED') )
485 ) OR
486 ( nvl(rec_old_ool.ordered_quantity, 0) = 0 AND
487 nvl(rec_ool.ordered_quantity, 0) <> 0
488 ) OR
489 ( rec_old_ool.inventory_item_id <> rec_ool.inventory_item_id ) OR
490 ( nvl(rec_old_ool.line_number,-9999) <> nvl(rec_ool.line_number,-9998) ) OR
491 ( nvl(rec_old_ool.shipment_number,-9999) <> nvl(rec_ool.shipment_number,-9998) )
492 )
493 then
494 /*
495 1. When inventory item has been changed in the base apps screen then, the
496 sales order form should also update the same and redefault the taxes. This
497 has been achieved ny deleteing the order and tax lines in the
498 ja_in_oe_order_lines_aiu_trg trigger and then the inserts the same in the
499 normal course of execution.
500 2. Copy of Legacy Rma return order to another return order has also been made
501 possible due to this fix.
502 3. When ever a copied line is split then the line number and shipment line
503 number were not getting updated in the Localised Sales Order Line.
504 */
505 jai_om_tax_processing_pkg.populate_tax (
506 pr_old => rec_old_ool ,
507 pr_new => rec_ool ,
508 pv_action => lv_action ,
509 pv_return_code => lv_return_code ,
510 pv_return_message => lv_return_message
511 );
512
513 if lv_return_code <> JAI_CONSTANTS.successful then
514 RAISE le_error;
515 end if;
516
517 -- added by zhiwei.xin for Trigger Replacement bug #15968958 on 20-Dec-2012 begin
518
519 jai_om_tax_processing_pkg.order_line_validation(p_rec_old => rec_old_ool,
520 p_rec_new => rec_ool,
521 p_action => lv_action,
522 px_return_code => lv_return_code,
523 px_return_message => lv_return_message);
524 if lv_return_code <> JAI_CONSTANTS.successful then
525 RAISE le_error;
526 end if;
527 -- added by zhiwei.xin for Trigger Replacement bug #15968958 on 20-Dec-2012 end.
528
529 end if;
530 END LOOP;
531 END IF;
532 -- added by zhiwei.xin for ZX Integration of OM part end.
533 --Added by qinglei for ZX Integration of Manual AR begin
534 ELSIF nvl(p_event_class_rec.application_id, p_transaction_rec.application_id) = 222 then
535 jai_ar_tax_processing_pkg.calculate_tax(p_transaction_rec,
536 p_event_class_rec,
537 null,
538 null,
539 lv_return_code,
540 lv_return_message);
541 --Added by qinglei for ZX Integration of Manual AR end
542
543 --Added by Qinglei for bug#14627780 begin
544 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
545 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END', 'after jai_ar_tax_processing_pkg.calculate_tax. lv_return_code = '||lv_return_code||
546 ', lv_return_message = '||lv_return_message);
547 END IF;
548 IF lv_return_code <> JAI_CONSTANTS.successful THEN
549 RAISE le_error;
550 END IF;
551 --Added by Qinglei for bug#14627780 end
552 END IF;
553
554 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
555 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
556 end if;
557 exception
558 when le_error then
559 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
560 fnd_message.set_token ('MSG_TEXT', lv_return_message);
561 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.CALCULATE_TAX', lv_return_message);
562 /*Commented by Chong for for displaying message to ZX 2012/09/02
563 app_exception.raise_exception (
564 EXCEPTION_TYPE => 'APP' ,
565 EXCEPTION_CODE => -20001 ,
566 EXCEPTION_TEXT => lv_return_message
567 );
568 */
569 x_return_status := FND_API.G_RET_STS_ERROR; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
570 fnd_msg_pub.add; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
571 when others then
572 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
573 fnd_message.set_token ('MSG_TEXT', 'Encountered the error in JAI_TAX_PROCESSING_PKG.CALCULATE_TAX' || substr(sqlerrm,1,1900));
574 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.CALCULATE_TAX', substr(sqlerrm,1,1900));
575 fnd_msg_pub.add; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
576 --Changed by Zhiwei for ZX comments to return same status even unexpected error begin
577 ---------------------------------------------------------------------------------------------
578 /*app_exception.raise_exception (
579 EXCEPTION_TYPE => 'APP',
580 EXCEPTION_CODE => -20001 ,
581 EXCEPTION_TEXT => 'Encountered the error in procedure JAI_TAX_PROCESSING_PKG.CALCULATE_TAX' || substr(sqlerrm,1,1900)
582 );*/
583 x_return_status := FND_API.G_RET_STS_ERROR;
584 ----------------------------------------------------------------------------------------------
585 --Changed by Zhiwei for ZX comments to return same status even unexpected error end
586
587 end calculate_tax;
588
589 /*-------------------------------------------------------------------------------------------------------------------------------+
590 | Created By : zhiwei.xin |
591 | Creation Date : 04/May/2012 |
592 | Bug Number/ER Name : ZX Integration |
593 | SubProgram Name : determine_recovery |
594 | Type : PROCEDURE |
595 | Purpose : Calculate JAI tax when perform Prepayment Application |
596 | TDD Reference : Section 10.2 |
597 | Assumptions : |
598 | Called From : ZX_SRVC_TYP_PKG.determine_recovery |
599 |--------------------------------------------------------------------------------------------------------------------------------|
600 | parameters IN/OUT Type Required Description and Purpose |
601 | x_return_status IN VARCHAR2 yes Return status |
602 ---------------------------------------------------------------------------------------------------------------------------------*/
603 procedure determine_recovery(p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE
604 ,x_return_status OUT NOCOPY VARCHAR2) is --Added x_return_status by Chong for displaying message to ZX 2012/09/02
605
606 lv_action VARCHAR2(20);
607 lv_return_message VARCHAR2(2000);
608 lv_return_code VARCHAR2(100);
609 le_error EXCEPTION;
610 l_api_name CONSTANT VARCHAR2(30) := 'DETERMINE_RECOVERY()';
611
612 -- cursor to get invoice distribution line with match status flag
613 cursor c_get_inv_dist(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
614 is
615 select *
616 from ap_invoice_distributions_all
617 where invoice_id = cp_trx_id
618 and match_status_flag is not null
619 and line_type_lookup_code <> 'PREPAY'; --Added by Chong for bug#13802244, sync flag should exclude prepayements line
620
621 -- cursor to get invoice distribution line for prepay
622 cursor c_get_inv_dist_prepay(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
623 is
624 select aida.*
625 from ap_invoice_distributions_all aida
626 where aida.invoice_id = cp_trx_id
627 and line_type_lookup_code = 'PREPAY'
628 and amount < 0
629 and prepay_distribution_id is not null
630 and not exists (select 1
631 from jai_ap_tds_prepayments
632 where invoice_id = cp_trx_id
633 and invoice_distribution_id_prepay = aida.invoice_distribution_id);
634
635 begin
636 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
637 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
638 end if;
639
640 if p_event_class_rec.application_id = 200 then -- called from AP
641
642 if p_event_class_rec.event_class_code in ('STANDARD INVOICES') and
643 p_event_class_rec.tax_event_type_code in ('DISTRIBUTE') then -- Prepayment Application Action
644
645 lv_action := JAI_CONSTANTS.inserting;
646
647 /*
648 Need update the latest match status flag before perform prepay application,
649 otherwise jai_ap_tds_generation_pkg.status_update_chk_validate will prevent from Appying.
650 */
651 FOR inv_dist_rec in c_get_inv_dist(p_event_class_rec.TRX_ID)
652 LOOP
653 update jai_ap_tds_inv_taxes
654 set match_status_flag = inv_dist_rec.match_status_flag
655 where invoice_id = inv_dist_rec.invoice_id
656 and (invoice_distribution_id = inv_dist_rec.invoice_distribution_id
657 or invoice_distribution_id = 1
658 );
659 END LOOP;
660
661 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
662 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, 'Call application for invoice: ' || p_event_class_rec.TRX_ID );
663 end if;
664 /*
665 Fetch prepay distribution lines for prepay application (amount < 0).
666 Datas in JAI_AP_TDS_PREPAYMENT indicate the prepay distributions had already been applied,
667 so they should not be fetched out for processing.
668 */
669 FOR inv_dist_prepay_rec in c_get_inv_dist_prepay(p_event_class_rec.TRX_ID)
670 LOOP
671
672 JAI_AP_IDA_TRIGGER_DTC_PKG.BRIUD_DTC_T1 (
673 pr_old => null ,
674 pr_new => inv_dist_prepay_rec ,
675 pv_action => lv_action ,
676 pv_return_code => lv_return_code ,
677 pv_return_message => lv_return_message
678 );
679
680 IF lv_return_code <> jai_constants.successful then
681 RAISE le_error;
682 END IF;
683
684 END LOOP;
685
686 end if;
687
688 end if;
689
690 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
691 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
692 end if;
693 exception
694 when le_error then
695 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
696 fnd_message.set_token ('MSG_TEXT', lv_return_message);
697 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.DETERMINE_RECOVERY', lv_return_message);
698 x_return_status := FND_API.G_RET_STS_ERROR; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
699 fnd_msg_pub.add; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
700 /*Commented by Chong for for displaying message to ZX 2012/09/02
701 app_exception.raise_exception (
702 EXCEPTION_TYPE => 'APP' ,
703 EXCEPTION_CODE => -20001 ,
704 EXCEPTION_TEXT => lv_return_message
705 );
706 */
707 when others then
708 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
709 fnd_message.set_token ('MSG_TEXT', 'Encountered the error in JAI_TAX_PROCESSING_PKG.DETERMINE_RECOVERY' || substr(sqlerrm,1,1900));
710 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.DETERMINE_RECOVERY', substr(sqlerrm,1,1900));
711 fnd_msg_pub.add; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
712 --Changed by Zhiwei for ZX comments to return same status even unexpected error begin
713 ---------------------------------------------------------------------------------------------
714 /*app_exception.raise_exception (
715 EXCEPTION_TYPE => 'APP',
716 EXCEPTION_CODE => -20001 ,
717 EXCEPTION_TEXT => 'Encountered the error in procedure JAI_TAX_PROCESSING_PKG.DETERMINE_RECOVERY' || substr(sqlerrm,1,1900)
718 );*/
719 x_return_status := FND_API.G_RET_STS_ERROR;
720 ----------------------------------------------------------------------------------------------
721 --Changed by Zhiwei for ZX comments to return same status even unexpected error end
722 end determine_recovery;
723 /*-------------------------------------------------------------------------------------------------------------------------------+
724 | Created By : Chong |
725 | Creation Date : 06/JUN/2012 |
726 | Bug Number/ER Name : ZX Integration |
727 | SubProgram Name : check_jai_exists_in_zx |
728 | Type : PROCEDURE |
729 | Purpose : Check if call from JAI program by India Legal entity |
730 | TDD Reference : Section 10.2 |
731 | Assumptions : |
732 | Called From : ZX |
733 | Return Value : Ture(India legal entity) / False(not India legal entity |
734 |--------------------------------------------------------------------------------------------------------------------------------|
735 | parameters IN/OUT Type Required Description and Purpose |
736 | pn_set_of_books_id IN Number yes Check Legal Entity ID |
737 | pn_application_id IN Number no use it when global strut is null |
738 | pv_entity_code IN VARCHAR2 no use it when check PO module |
739 | pn_trx_id IN Number no use it when global strut is null |
740 ---------------------------------------------------------------------------------------------------------------------------------*/
741 function check_jai_exists_in_zx(pn_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE,
742 pn_application_id NUMBER DEFAULT NULL, --Added by Cholei for bug#14189751
743 pv_entity_code VARCHAR2 DEFAULT NULL, --Added by Wenqiong for PO integration
744 pn_trx_id NUMBER DEFAULT NULL --Added by Cholei for bug#14189751
745 )return boolean
746 is
747 cursor c_get_ledger_id(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
748 is
749 select set_of_books_id
750 from ap_invoices_all
751 where invoice_id = cp_trx_id;
752
753 cursor c_get_ledger_id_ar(cp_trx_id ra_customer_trx_all.customer_trx_id%TYPE) is
754 select set_of_books_id
755 from ra_customer_trx_all
756 where customer_trx_id = cp_trx_id;
757
758 --added by zhiwei.xin for OM begin
759 cursor c_get_om_org_id(cp_trx_id OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE)
760 is
761 select org_id
762 from oe_order_headers_all
763 where header_id = cp_trx_id;
764 --added by zhiwei.xin for OM end.
765
766 --Added by Wenqiong for PO integration begin
767 CURSOR c_get_req_org_id(cp_trx_id PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID%TYPE ) IS
768 SELECT org_id
769 FROM PO_REQUISITION_HEADERS_ALL
770 WHERE REQUISITION_HEADER_ID = cp_trx_id;
771
772 CURSOR c_get_po_org_id(cp_trx_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE ) IS
773 SELECT org_id
774 FROM PO_HEADERS_ALL
775 WHERE PO_HEADER_ID = cp_trx_id;
776
777 cursor c_fetch_sob_from_hrou(cp_org_id in number)
778 is
779 select set_of_books_id
780 from hr_operating_units
781 Where organization_id = cp_org_id;
782 --Added by Wenqiong for PO integration end
783
784 lb_jai_exists boolean;
785 l_ledger_id number;
786 ln_org_id NUMBER;--Added by Wenqiong for PO integration
787 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_JAI_EXISTS_IN_ZX()';
788 begin
789 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
790 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
791 end if;
792
793 l_ledger_id := pn_set_of_books_id;
794
795 if l_ledger_id is null then --if ledger id is not defined.
796 if pn_application_id = 200 then -- called from AP --Updated by Cholei for bug#14189751
797 --derive ledger id from invoice id
798 open c_get_ledger_id(pn_trx_id); ----Updated by Cholei for bug#14189751
799 fetch c_get_ledger_id into l_ledger_id;
800 close c_get_ledger_id;
801
802 elsif pn_application_id = 222 then
803 open c_get_ledger_id_ar(pn_trx_id);
804 fetch c_get_ledger_id_ar into l_ledger_id;
805 close c_get_ledger_id_ar;
806
807 -- added by zhiwei.xin for OM begin
808 elsif pn_application_id = 660 then
809 open c_get_om_org_id(pn_trx_id);
810 fetch c_get_om_org_id into ln_org_id; --Updated by Chong from l_ledger_id 20130419
811 close c_get_ledger_id;
812 IF nvl(ln_org_id,-1) <> -1 THEN
813 OPEN c_fetch_sob_from_hrou(ln_org_id);
814 FETCH c_fetch_sob_from_hrou INTO l_ledger_id;
815 CLOSE c_fetch_sob_from_hrou;
816 END IF;
817 -- added by zhiwei.xin for OM end.
818
819 --Added by Wenqiong for PO integration begin
820 ELSIF pn_application_id =201 OR pn_application_id IS NULL THEN
821 IF pv_entity_code = 'REQUISITION' THEN
822 OPEN c_get_req_org_id(pn_trx_id);
823 FETCH c_get_req_org_id INTO ln_org_id;
824 CLOSE c_get_req_org_id;
825 ELSE
826 OPEN c_get_po_org_id(pn_trx_id);
827 FETCH c_get_po_org_id INTO ln_org_id;
828 CLOSE c_get_po_org_id;
829 END IF;
830 IF nvl(ln_org_id,-1) <> -1 THEN
831 OPEN c_fetch_sob_from_hrou(ln_org_id);
832 FETCH c_fetch_sob_from_hrou INTO l_ledger_id;
833 CLOSE c_fetch_sob_from_hrou;
834 END IF;
835 --Added by Wenqiong for PO integration end
836
837 end if;
838 end if;
839
840 if (nvl(GN_SET_BOOK_ID,-999) = nvl(l_ledger_id,-998))then
841 return GV_JAI_EXISTS;
842 else
843 lb_jai_exists := JAI_CMN_UTILS_PKG.CHECK_JAI_EXISTS(p_calling_object => 'CALCULATE_TAX'
844 ,p_set_of_books_id => l_ledger_id
845 );
846
847 GV_JAI_EXISTS := lb_jai_exists;
848 GN_SET_BOOK_ID := l_ledger_id;
849
850 end if;
851
852 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
853 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
854 end if;
855
856 return GV_JAI_EXISTS;
857 end check_jai_exists_in_zx;
858
859 /*-------------------------------------------------------------------------------------------------------------------------------+
860 | Created By : zhiwei.hou |
861 | Creation Date : 02/Aug/2012 |
862 | Bug Number/ER Name : ZX Integration |
863 | SubProgram Name : IMPORT_DOCUMENT_WITH_TAX |
864 | Type : PROCEDURE |
865 | Purpose : Import JAI tax to JAI table |
866 | TDD Reference : |
867 | Assumptions : |
868 | Called From : ZX_SRVC_TYP_PKG.import |
869 |--------------------------------------------------------------------------------------------------------------------------------|
870 | parameters IN/OUT Type Required Description and Purpose |
871 | ------------ -------- ------ ---------- ------------------------- |
872 | |
873 | |
874 | |
875 ---------------------------------------------------------------------------------------------------------------------------------*/
876 procedure IMPORT_DOCUMENT_WITH_TAX(
877 p_event_class_rec ZX_API_PUB.event_class_rec_type ,
878 x_return_status OUT NOCOPY VARCHAR2
879 )
880 is
881 lb_jai_exists BOOLEAN;
882 lv_action VARCHAR2(20);
883 t_ra_customer_trx_rec RA_CUSTOMER_TRX_ALL%ROWTYPE;
884 t_ra_customer_trx_lines_rec RA_CUSTOMER_TRX_LINES_ALL%ROWTYPE;
885 lv_return_message VARCHAR2(2000);
886 lv_return_code VARCHAR2(100);
887 ln_trx_line_id NUMBER;
888 le_error EXCEPTION;
889 le_jai_not_exists EXCEPTION;
890 l_api_name CONSTANT VARCHAR2(30) := 'IMPORT_DOCUMENT_WITH_TAX()';
891 CURSOR c_ra_customer_trx(p_customer_trx_id NUMBER)
892 IS
893 SELECT *
894 FROM RA_CUSTOMER_TRX_ALL
895 WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
896
897 CURSOR c_ra_customer_trx_lines(p_customer_trx_line_id NUMBER)
898 IS
899 SELECT *
900 FROM RA_CUSTOMER_TRX_LINES_ALL
901 WHERE CUSTOMER_TRX_LINE_ID = p_customer_trx_line_id;
902
903
904 CURSOR c_jai_ar_trx_lines(p_customer_trx_line_id NUMBER)
905 IS
906 SELECT *
907 FROM JAI_AR_TRX_LINES
908 WHERE CUSTOMER_TRX_LINE_ID = p_customer_trx_line_id;
909
910 CURSOR c_ra_cust_trx_lines(p_customer_trx_id NUMBER)
911 IS
912 SELECT customer_trx_line_id
913 FROM RA_CUSTOMER_TRX_LINES_ALL
914 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
915 AND LINE_TYPE = 'LINE';
916
917
918 t_rec_lines c_ra_customer_trx_lines%rowtype;
919 t_rec_jai_lines c_jai_ar_trx_lines%rowtype;
920
921 begin
922
923 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
924 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
925 end if;
926
927 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
928 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.in procedure', ' p_event_class_rec.application_id: '||p_event_class_rec.application_id||';'
929 || 'p_event_class_rec.entity_code :' ||p_event_class_rec.entity_code||';'
930 || 'p_event_class_rec.TAX_EVENT_CLASS_CODE :' ||p_event_class_rec.TAX_EVENT_CLASS_CODE||';'
931 || 'p_event_class_rec.trx_id :' ||p_event_class_rec.trx_id||';'
932
933 );
934 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.in procedure', ' global_struction_pkg.application_id: '||ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.application_id||';'
935 || 'global_struction_pkg.entity_code :' ||ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.entity_code||';'
936 || 'global_struction_pkg.TAX_EVENT_CLASS_CODE :' ||ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.TAX_EVENT_CLASS_CODE||';'
937 || 'global_struction_pkg.trx_id :' ||ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.trx_id||';'
938
939 );
940 end if;
941
942
943
944 IF p_event_class_rec.application_id = 222 --Indicates AR Module
945 --IF ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.application_id = 222 --Indicates AR Module
946 THEN
947 NULL;
948 --IF ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.entity_code = 'TRANSACTIONS'
949 IF p_event_class_rec.entity_code = 'TRANSACTIONS'
950 --AND ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.TAX_EVENT_CLASS_CODE = 'SALES_TRANSACTION'
951 AND p_event_class_rec.TAX_EVENT_CLASS_CODE = 'SALES_TRANSACTION'
952 THEN
953 lv_action := JAI_CONSTANTS.inserting;
954
955
956
957 --OPEN c_ra_customer_trx(ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.trx_id);
958 OPEN c_ra_customer_trx(p_event_class_rec.trx_id);
959 FETCH c_ra_customer_trx INTO t_ra_customer_trx_rec;
960 CLOSE c_ra_customer_trx;
961
962 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX for transaction ' || t_ra_customer_trx_rec.customer_trx_id);
963
964 --FOR rec_trx_lines in c_ra_cust_trx_lines(ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.trx_id)
965 FOR rec_trx_lines in c_ra_cust_trx_lines(p_event_class_rec.trx_id)
966 LOOP
967
968 /*FOR i IN 1 .. ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.application_id.count
969 LOOP*/
970
971 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX into loop for transaction ' || t_ra_customer_trx_rec.customer_trx_id);
972
973
974 ln_trx_line_id := rec_trx_lines.customer_trx_line_id;
975
976 /*SELECT ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_line_id(i)
977 INTO ln_trx_line_id
978 FROM DUAL
979 WHERE ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trx_id(i) = ZX_GLOBAL_STRUCTURES_PKG.G_EVENT_CLASS_REC.trx_id
980 AND ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.entity_code(i) = 'TRANSACTIONS'
981 --AND ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.event_class_code(i) = 'INVOICE'
982 --AND ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.event_type_code(i) = 'INV_CREATE'
983 ;*/
984
985 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX get trx line id ' || ln_trx_line_id);
986
987
988 open c_ra_customer_trx_lines(ln_trx_line_id);
989 fetch c_ra_customer_trx_lines into t_rec_lines;
990 close c_ra_customer_trx_lines;
991
992
993
994
995 if t_rec_lines.line_type = 'LINE'
996 and JAI_AR_RCTLA_TRIGGER_PKG.is_this_projects_context(t_rec_lines.interface_line_context)
997 then
998
999 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX into import_projects_taxes() ');
1000
1001 JAI_AR_IMPORT_POPU_PKG.import_projects_taxes
1002 (
1003 r_new => t_rec_lines
1004 , r_old => null
1005 , pv_action => lv_action
1006 , pv_err_msg => lv_return_message
1007 , pv_err_flg => lv_return_code
1008 );
1009
1010 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX out import_projects_taxes() ');
1011 fnd_file.put_line(FND_FILE.LOG,'lv_return_code ' || lv_return_code);
1012 fnd_file.put_line(FND_FILE.LOG,'lv_return_message ' || lv_return_message);
1013
1014 IF lv_return_code <> jai_constants.successful then
1015 raise le_error ;
1016 end if;
1017
1018 end if;
1019
1020
1021
1022 if t_rec_lines.line_type = 'LINE' then
1023
1024 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX into pop_jai_inv_and_suppl() ');
1025
1026 JAI_AR_IMPORT_POPU_PKG.pop_jai_inv_and_suppl(
1027 --JAI_AR_RCTLA_TRIGGER_PKG.ARI_T2(
1028 pr_old => NULL
1029 ,pr_new => t_rec_lines
1030 ,pv_action => lv_action
1031 ,pv_return_code => lv_return_code
1032 ,pv_return_message => lv_return_message
1033 );
1034 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX out pop_jai_inv_and_suppl() ');
1035 fnd_file.put_line(FND_FILE.LOG,'lv_return_code ' || lv_return_code);
1036 fnd_file.put_line(FND_FILE.LOG,'lv_return_message ' || lv_return_message);
1037
1038
1039 IF lv_return_code <> jai_constants.successful then
1040 RAISE le_error;
1041 END IF;
1042 end if;
1043 if t_rec_lines.line_type = 'LINE' then
1044
1045 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX into pop_jai_rmacm() ');
1046
1047 JAI_AR_IMPORT_POPU_PKG.pop_jai_rmacm(
1048 --JAI_AR_RCTLA_IM_PKG.ARI_T3(
1049 pr_old => NULL
1050 ,pr_new => t_rec_lines
1051 ,pv_action => lv_action
1052 ,pv_return_code => lv_return_code
1053 ,pv_return_message => lv_return_message
1054 );
1055 fnd_file.put_line(FND_FILE.LOG,'Process in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX out pop_jai_rmacm() ');
1056 fnd_file.put_line(FND_FILE.LOG,'lv_return_code ' || lv_return_code);
1057 fnd_file.put_line(FND_FILE.LOG,'lv_return_message ' || lv_return_message);
1058
1059 IF lv_return_code <> jai_constants.successful then
1060 RAISE le_error;
1061 END IF;
1062 end if;
1063
1064
1065
1066 END LOOP;
1067 END IF;
1068
1069 END IF;
1070
1071 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
1072 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
1073 end if;
1074 exception
1075 when le_error then
1076 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
1077 fnd_message.set_token ('MSG_TEXT', lv_return_message);
1078 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX', lv_return_message);
1079 fnd_file.put_line(FND_FILE.LOG,'Encountered an error in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX ' || lv_return_message);
1080
1081 /*app_exception.raise_exception (
1082 EXCEPTION_TYPE => 'APP' ,
1083 EXCEPTION_CODE => -20001 ,
1084 EXCEPTION_TEXT => lv_return_message
1085 );*/
1086 x_return_status := FND_API.G_RET_STS_ERROR; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
1087 fnd_msg_pub.add; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
1088
1089 when others then
1090 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
1091 fnd_message.set_token ('MSG_TEXT', 'Encountered the error in JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX' || substr(sqlerrm,1,1900));
1092 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX', substr(sqlerrm,1,1900));
1093 /*app_exception.raise_exception (
1094 EXCEPTION_TYPE => 'APP',
1095 EXCEPTION_CODE => -20001 ,
1096 EXCEPTION_TEXT => 'Encountered the error in procedure JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX' || substr(sqlerrm,1,1900)
1097 );*/
1098 fnd_file.put_line(FND_FILE.LOG,'Encountered an error in JAI.PLSQL.JAI_TAX_PROCESSING_PKG.IMPORT_DOCUMENT_WITH_TAX ' || substr(sqlerrm,1,1900));
1099 x_return_status := FND_API.G_RET_STS_ERROR; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
1100 fnd_msg_pub.add; --Added x_return_status by Chong for displaying message to ZX 2012/09/02
1101
1102 end IMPORT_DOCUMENT_WITH_TAX;
1103
1104 /*-------------------------------------------------------------------------------------------------------------------------------+
1105 | Created By : zhiwei.hou |
1106 | Creation Date : 02/Aug/2012 |
1107 | Bug Number/ER Name : ZX Integration |
1108 | SubProgram Name : PROCESS_COMPLETE |
1109 | Type : PROCEDURE |
1110 | Purpose : Updatae complete flag and populate temp table. |
1111 | TDD Reference : |
1112 | Assumptions : |
1113 | Called From : jai_ar_match_tax_pkg.process_order_line |
1114 |--------------------------------------------------------------------------------------------------------------------------------|
1115 | parameters IN/OUT Type Required Description and Purpose |
1116 | ------------ -------- ------ ---------- ------------------------- |
1117 | |
1118 | |
1119 | |
1120 ---------------------------------------------------------------------------------------------------------------------------------*/
1121
1122 PROCEDURE PROCESS_COMPLETE(
1123 pn_customer_trx_id number
1124 )
1125 IS
1126 lb_jai_exists BOOLEAN;
1127 lv_action VARCHAR2(20);
1128 t_jai_ar_trx_rec JAI_AR_TRXS%ROWTYPE;
1129 t_ra_customer_trx_rec RA_CUSTOMER_TRX_ALL%ROWTYPE;
1130 t_ra_customer_trx_lines_rec RA_CUSTOMER_TRX_LINES_ALL%ROWTYPE;
1131 lv_return_message VARCHAR2(2000);
1132 lv_return_code VARCHAR2(100);
1133 ln_trx_line_id NUMBER;
1134 le_error EXCEPTION;
1135 le_jai_not_exists EXCEPTION;
1136 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_COMPLETE()';
1137
1138 CURSOR c_ra_customer_trx(p_customer_trx_id NUMBER)
1139 IS
1140 SELECT *
1141 FROM RA_CUSTOMER_TRX_ALL
1142 WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
1143
1144 CURSOR c_jai_ar_trx(p_customer_trx_id NUMBER)
1145 IS
1146 SELECT *
1147 FROM JAI_AR_TRXS
1148 WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
1149
1150 CURSOR c_ra_customer_trx_lines(p_customer_trx_line_id NUMBER)
1151 IS
1152 SELECT *
1153 FROM RA_CUSTOMER_TRX_LINES_ALL
1154 WHERE CUSTOMER_TRX_LINE_ID = p_customer_trx_line_id;
1155
1156 lv_remark1 varchar2(10);
1157 lv_remark2 varchar2(10);
1158
1159 BEGIN
1160
1161 lv_action := JAI_CONSTANTS.updating;
1162
1163
1164 OPEN c_ra_customer_trx(pn_customer_trx_id);
1165 FETCH c_ra_customer_trx INTO t_ra_customer_trx_rec;
1166 CLOSE c_ra_customer_trx;
1167
1168 open c_jai_ar_trx(pn_customer_trx_id);
1169 fetch c_jai_ar_trx into t_jai_ar_trx_rec;
1170 close c_jai_ar_trx;
1171
1172
1173 if(t_ra_customer_trx_rec.complete_flag = 'Y')then
1174
1175 --JAI_AR_RCTA_TRIGGER_PKG.ARU_T1(
1176 JAI_AR_IMPORT_SYNC_PKG.gen_vat_for_inv(
1177 pr_old => NULL
1178 ,pr_new => t_ra_customer_trx_rec
1179 ,pv_action => lv_action
1180 ,pv_return_code => lv_return_code
1181 ,pv_return_message => lv_return_message
1182 );
1183
1184
1185 IF lv_return_code <> jai_constants.successful then
1186 RAISE le_error;
1187 END IF;
1188
1189 end if;
1190
1191
1192 if(t_ra_customer_trx_rec.trx_number <> nvl(t_jai_ar_trx_rec.trx_number,'$$')
1193 or t_ra_customer_trx_rec.CUSTOMER_TRX_ID <> nvl(t_jai_ar_trx_rec.CUSTOMER_TRX_ID,-999))then
1194 JAI_AR_IMPORT_SYNC_PKG.update_trx_number(
1195 --JAI_AR_RCTA_TRIGGER_PKG.ARU_T2(
1196 pr_old => t_jai_ar_trx_rec
1197 ,pr_new => t_ra_customer_trx_rec
1198 ,pv_action => lv_action
1199 ,pv_return_code => lv_return_code
1200 ,pv_return_message => lv_return_message
1201 );
1202
1203
1204 IF lv_return_code <> jai_constants.successful then
1205 RAISE le_error;
1206 END IF;
1207 end if;
1208 if(t_ra_customer_trx_rec.complete_flag = 'Y')then
1209 JAI_AR_IMPORT_SYNC_PKG.update_complete_flag(
1210 --JAI_AR_RCTA_TRIGGER_PKG.ARU_T4(
1211 pr_old => t_jai_ar_trx_rec
1212 ,pr_new => t_ra_customer_trx_rec
1213 ,pv_action => lv_action
1214 ,pv_return_code => lv_return_code
1215 ,pv_return_message => lv_return_message
1216 );
1217
1218
1219 IF lv_return_code <> jai_constants.successful then
1220 RAISE le_error;
1221 END IF;
1222
1223 JAI_AR_IMPORT_SYNC_PKG.process_cm_vat_repo(
1224 --JAI_AR_RCTA_TRIGGER_PKG.ARU_T8(
1225 pr_old => t_jai_ar_trx_rec
1226 ,pr_new => t_ra_customer_trx_rec
1227 ,pv_action => lv_action
1228 ,pv_return_code => lv_return_code
1229 ,pv_return_message => lv_return_message
1230 );
1231
1232 IF lv_return_code <> jai_constants.successful then
1233 RAISE le_error;
1234 END IF;
1235
1236 end if;
1237
1238 /*if(nvl(t_ra_customer_trx_rec.complete_flag,'$') = 'N'
1239 and
1240 t_ra_customer_trx_rec.trx_date <> nvl(t_jai_ar_trx_rec.trx_date,'0000')
1241 )then
1242 JAI_AR_IMPORT_SYNC_PKG.check_tax_lines(
1243 --JAI_AR_RCTA_TRIGGER_PKG.ARU_T5(
1244 pr_old => t_jai_ar_trx_rec
1245 ,pr_new => t_ra_customer_trx_rec
1246 ,pv_action => lv_action
1247 ,pv_return_code => lv_return_code
1248 ,pv_return_message => lv_return_message
1249 );
1250
1251
1252
1253 IF lv_return_code <> jai_constants.successful then
1254 RAISE le_error;
1255 END IF;
1256 end if;*/
1257
1258 IF ( NVL(t_jai_ar_trx_rec.COMPLETE_FLAG,'N') = 'N' AND NVL(t_ra_customer_trx_rec.COMPLETE_FLAG,'$') ='Y' ) -- COMPLETION
1259 OR ( NVL(t_jai_ar_trx_rec.COMPLETE_FLAG,'N') = 'Y' AND NVL(t_ra_customer_trx_rec.COMPLETE_FLAG,'$') ='N' ) -- INCOMPLETION
1260 THEN
1261 JAI_AR_IMPORT_SYNC_PKG.process_tcs_tax(
1262 --JAI_AR_RCTA_TRIGGER_PKG.ARU_T7 (
1263 pr_old => t_jai_ar_trx_rec ,
1264 pr_new => t_ra_customer_trx_rec ,
1265 pv_action => lv_action ,
1266 pv_return_code => lv_return_code ,
1267 pv_return_message => lv_return_message
1268 );
1269 IF lv_return_code <> jai_constants.successful then
1270 RAISE le_error;
1271 END IF;
1272 end if;
1273
1274
1275
1276
1277 EXCEPTION
1278 when le_error then
1279 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
1280 fnd_message.set_token ('MSG_TEXT', lv_return_message);
1281 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE', lv_return_message);
1282 app_exception.raise_exception (
1283 EXCEPTION_TYPE => 'APP' ,
1284 EXCEPTION_CODE => -20001 ,
1285 EXCEPTION_TEXT => lv_return_message
1286 );
1287 when others then
1288 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
1289 fnd_message.set_token ('MSG_TEXT', 'Encountered the error in JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE' || substr(sqlerrm,1,1900));
1290 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE', substr(sqlerrm,1,1900));
1291 app_exception.raise_exception (
1292 EXCEPTION_TYPE => 'APP',
1293 EXCEPTION_CODE => -20001 ,
1294 EXCEPTION_TEXT => 'Encountered the error in procedure JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE' || substr(sqlerrm,1,1900)
1295 );
1296
1297
1298 END PROCESS_COMPLETE;
1299
1300 /*-------------------------------------------------------------------------------------------------------------------------------+
1301 | Created By : Chong |
1302 | Creation Date : 16/Aug/2012 |
1303 | Bug Number/ER Name : ZX Dtc cancellation |
1304 | SubProgram Name : ap_dtc_cancel_invoice |
1305 | Type : PROCEDURE |
1306 | Purpose : Updatae complete flag and populate temp table. |
1307 | TDD Reference : |
1308 | Assumptions : |
1309 | Called From : JAI_AP_STND_TAX_PROCESS.Populate_Stnd_Inv_Taxes |
1310 |--------------------------------------------------------------------------------------------------------------------------------|
1311 | parameters IN/OUT Type Required Description and Purpose |
1312 | ------------ -------- ------ ---------- ------------------------- |
1313 | pn_invoice_id IN NUMBER YES cancel target invoice ID |
1314 | |
1315 ---------------------------------------------------------------------------------------------------------------------------------*/
1316 PROCEDURE ap_dtc_cancel_invoice(
1317 pn_invoice_id NUMBER
1318 )
1319 IS
1320
1321 CURSOR c_get_invoice_info(cp_invoice_id NUMBER)
1322 IS
1323 SELECT aia.invoice_id
1324 , aia.source
1325 , aia.vendor_id
1326 FROM ap_invoices_all aia
1327 WHERE aia.invoice_id = cp_invoice_id;
1328
1329 CURSOR c_chk_tds_vendor(cp_vendor_id NUMBER)
1330 IS
1331 SELECT count(1) cnt
1332 FROM jai_ap_tds_thhold_grps
1333 WHERE vendor_id = cp_vendor_id;
1334
1335 CURSOR c_tds_invoice_id(cp_invoice_id NUMBER)
1336 IS
1337 SELECT invoice_to_tds_authority_id invoice_id
1338 , invoice_to_tds_authority_num invoice_num
1339 FROM jai_ap_tds_thhold_trxs
1340 WHERE invoice_id = cp_invoice_id;
1341
1342 CURSOR c_payment_status(cp_invoice_id NUMBER)
1343 IS
1344 SELECT payment_status_flag
1345 FROM ap_invoices_all
1346 WHERE invoice_id = cp_invoice_id;
1347
1348 r_invoice_info c_get_invoice_info%ROWTYPE;
1349 r_tds_invoice_id c_tds_invoice_id%ROWTYPE;
1350 r_payment_status c_payment_status%ROWTYPE;
1351 r_chk_tds_vendor c_chk_tds_vendor%ROWTYPE;
1352 l_api_name CONSTANT VARCHAR2(30) := 'ap_dtc_cancel_invoice()';
1353 lv_errbuf VARCHAR2(1000);
1354 lv_retcode VARCHAR2(1);
1355
1356 BEGIN
1357 --log for debug
1358 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || '.' || l_api_name ||'.BEGIN', G_PKG_NAME || ': '|| l_api_name || '()+');
1359 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || '.' || l_api_name || '.parameters', 'pn_invoice_id: ' || pn_invoice_id );
1360
1361
1362 OPEN c_get_invoice_info(pn_invoice_id);
1363 FETCH c_get_invoice_info INTO r_invoice_info;
1364 CLOSE c_get_invoice_info;
1365
1366 IF r_invoice_info.source <> 'INDIA TDS' then
1367
1368 OPEN c_chk_tds_vendor(r_invoice_info.vendor_id);
1369 FETCH c_chk_tds_vendor INTO r_chk_tds_vendor;
1370 CLOSE c_chk_tds_vendor;
1371
1372 IF r_chk_tds_vendor.cnt > 0 THEN
1373 OPEN c_tds_invoice_id(pn_invoice_id);
1374 FETCH c_tds_invoice_id INTO r_tds_invoice_id;
1375 CLOSE c_tds_invoice_id;
1376
1377
1378 jai_ap_dtc_cancellation_pkg.process_invoice_cancellation(errbuf => lv_errbuf
1379 ,retcode => lv_retcode
1380 ,p_invoice_id => pn_invoice_id);
1381 IF lv_retcode IS NOT NULL THEN
1382 Fnd_Message.Set_Name('JA','JAI_GENERIC_MSG');
1383 Fnd_Message.Set_Token('MSG_TEXT', lv_errbuf);
1384 Fnd_Message.RAISE_ERROR();
1385 END IF;
1386
1387
1388 END IF;
1389
1390 END IF;
1391
1392 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME || '.' || l_api_name ||'.END', G_PKG_NAME || ': '|| l_api_name || '()-');
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
1396 fnd_message.set_token ('MSG_TEXT', 'Encountered the error in JAI_TAX_PROCESSING_PKG.ap_dtc_cancel_invoice: ' || substr(sqlerrm,1,1900));
1397 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_TAX_PROCESSING_PKG.ap_dtc_cancel_invoice', substr(sqlerrm,1,1900));
1398 app_exception.raise_exception;
1399
1400 END ap_dtc_cancel_invoice;
1401
1402 /*-------------------------------------------------------------------------------------------------------------------------------+
1403 | Created By : Qiong.Liu |
1404 | Creation Date : 6/Sep/2012 |
1405 | Bug Number/ER Name : ZX AP accounting |
1406 | SubProgram Name : CREATE_ACCOUNTING |
1407 | Type : PROCEDURE |
1408 | Purpose : Call CREATE_ACCOUNTING to insert jai journal entry to gl interface table |
1409 | TDD Reference : Section 8.5 |
1410 | Assumptions : |
1411 | Called From : ZX_SRVC_TYP_PKG.synchronize_tax |
1412 |--------------------------------------------------------------------------------------------------------------------------------|
1413 | parameters IN/OUT Type Required Description and Purpose |
1414 | ------------ -------- ------ ---------- ------------------------- |
1415 | pv_return_status OUT VARCHAR2 YES Return status |
1416 | |
1417 ---------------------------------------------------------------------------------------------------------------------------------*/
1418 PROCEDURE CREATE_ACCOUNTING(pv_return_status OUT NOCOPY VARCHAR2) IS
1419
1420 -- cursor to get invoice distributions
1421 cursor c_get_inv_dists(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
1422 is
1423 select aida.*
1424 from ap_invoice_distributions_all aida
1425 where aida.invoice_id = cp_trx_id;
1426
1427 le_error exception;
1428 lv_action VARCHAR2(100);
1429 lv_return_code VARCHAR2(100);
1430 lv_return_message VARCHAR2(2000);
1431
1432
1433 BEGIN
1434 IF (zx_global_structures_pkg.g_event_class_rec.application_id = 200) THEN --Indicates AP Module
1435
1436 IF zx_global_structures_pkg.g_event_class_rec.event_class_code IN ('STANDARD INVOICES' ) AND
1437 zx_global_structures_pkg.g_event_class_rec.tax_event_type_code IN ('VALIDATE' ) AND
1438 zx_global_structures_pkg.g_event_class_rec.event_type_code IN ('STANDARD VALIDATED TAX')
1439 THEN
1440
1441 lv_action := JAI_CONSTANTS.updating;
1442
1443 FOR ap_inv_dist_rec in c_get_inv_dists(zx_global_structures_pkg.g_event_class_rec.trx_id)LOOP
1444 JAI_TAX_PROCESSING_PKG.create_accounting(
1445 pr_rec => ap_inv_dist_rec ,
1446 pv_action => lv_action ,
1447 pv_return_code => lv_return_code ,
1448 pv_return_message => lv_return_message
1449 );
1450 IF lv_return_code <> jai_constants.successful THEN
1451 RAISE le_error;
1452 END IF;
1453
1454 END LOOP;
1455 END IF;
1456 END IF;
1457 EXCEPTION
1458 WHEN le_error THEN
1459
1460 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
1461 fnd_message.set_token ('MSG_TEXT', lv_return_message);
1462 pv_return_status := FND_API.G_RET_STS_ERROR;
1463 fnd_msg_pub.add;
1464
1465 WHEN OTHERS THEN
1466 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
1467 fnd_message.set_token ('MSG_TEXT', 'Encountered the error in JAI_AP_TAX_PROCESSING. CREATE_ACCOUNTING ' || substr(sqlerrm,1,1900));
1468 fnd_msg_pub.add;
1469 --Changed by Zhiwei for ZX comments to return same status even unexpected error begin
1470 ---------------------------------------------------------------------------------------------
1471 /*app_exception.raise_exception (
1472 EXCEPTION_TYPE => 'APP',
1473 EXCEPTION_CODE => -20001 ,
1474 EXCEPTION_TEXT => 'Encountered the error in procedure JAI_TAX_PROCESSING_PKG.CREATE_ACCOUNTING' || substr(sqlerrm,1,1900)
1475 );*/
1476 pv_return_status := FND_API.G_RET_STS_ERROR;
1477 ----------------------------------------------------------------------------------------------
1478 --Changed by Zhiwei for ZX comments to return same status even unexpected error end
1479 END CREATE_ACCOUNTING ;
1480
1481 /*-------------------------------------------------------------------------------------------------------------------------------+
1482 | Created By : Qiong.Liu |
1483 | Creation Date : 04/May/2012 |
1484 | Bug Number/ER Name : ZX Integration for AP create accounting |
1485 | SubProgram Name : |
1486 | Type : PROCEDURE |
1487 | Purpose : Insert jai journal entry to gl interface table |
1488 | TDD Reference : Section 8.5 |
1489 | Assumptions : |
1490 | Called From : JAI_TAX_PROCESSING_PKG.create_accounting |
1491 |--------------------------------------------------------------------------------------------------------------------------------|
1492 | parameters IN/OUT Type Required Description and Purpose |
1493 | ------------ -------- ------ ---------- ------------------------- |
1494 | pr_rec IN RECORD yes ap distribution record |
1495 | pv_action IN VARCHAR2 yes distribution level action |
1496 | pv_return_code IN NUMBER yes Return status |
1497 | pv_return_message IN VARCHAR2 yes Return message |
1498 ---------------------------------------------------------------------------------------------------------------------------------*/
1499 PROCEDURE CREATE_ACCOUNTING
1500 ( pr_rec ap_invoice_distributions_all%rowtype ,
1501 pv_action varchar2 ,
1502 pv_return_code out nocopy varchar2 ,
1503 pv_return_message out nocopy varchar2
1504 )
1505 IS
1506
1507
1508 Cursor check_loc_tax(ln_invoice_id number) is
1509 select 'Y'
1510 from JAI_AP_MATCH_INV_TAXES
1511 where invoice_id = ln_invoice_id ;
1512
1513 lv_exists varchar2(1) := 'N' ;
1514
1515 lv_log_file_name VARCHAR2(50) := 'ja_in_ap_aida_after_trg.log';
1516 lv_utl_location VARCHAR2(512);
1517 lv_myfilehandle UTL_FILE.FILE_TYPE;
1518 lv_debug VARCHAR2(1) := 'N';
1519
1520 lb_result BOOLEAN;
1521 ln_request_id Number;
1522
1523 CURSOR get_invoice_type_cur IS
1524 SELECT invoice_type_lookup_code
1525 FROM ap_invoices_all
1526 WHERE invoice_id = pr_rec.invoice_id;
1527
1528 CURSOR get_tax_type_cur IS
1529 SELECT jcta.tax_type
1530 FROM jai_cmn_document_taxes jcdt
1531 , jai_cmn_taxes_all jcta
1532 , jai_ap_invoice_lines jail
1533 , jai_rgm_registrations jrr
1534 , jai_rgm_definitions jrd
1535 WHERE jcdt.source_doc_id = pr_rec.invoice_id
1536 AND jail.invoice_id = pr_rec.invoice_id
1537 AND jail.invoice_line_number = jcdt.source_doc_line_id
1538 AND jail.invoice_line_number = pr_rec.invoice_line_number
1539 AND jcdt.modvat_flag = 'Y'
1540 AND jcdt.tax_id = jcta.tax_id
1541 AND jcta.tax_type = jrr.attribute_code
1542 AND jrr.regime_id = jrd.regime_id
1543 AND jrr.registration_type = jai_constants.regn_type_tax_types
1544 AND jrd.regime_code = jai_constants.service_regime;
1545
1546 CURSOR get_matched_tax_cur IS
1547 SELECT jamt.tax_amount,jcta.tax_type
1548 FROM jai_ap_match_inv_taxes jamt
1549 , jai_cmn_taxes_all jcta
1550 , jai_rgm_registrations jrr
1551 , jai_rgm_definitions jrd
1552 WHERE jamt.invoice_id = pr_rec.invoice_id
1553 AND jamt.invoice_line_number = pr_rec.invoice_line_number
1554 AND jamt.tax_id=jcta.tax_id
1555 AND jamt.recoverable_flag = 'Y'
1556 AND jcta.tax_type = jrr.attribute_code
1557 AND jrr.regime_id = jrd.regime_id
1558 AND jrr.registration_type = jai_constants.regn_type_tax_types
1559 AND jrd.regime_code = jai_constants.service_regime;
1560
1561 CURSOR get_match_item_cur IS
1562 SELECT line.match_type,line.line_number
1563 FROM ap_invoice_lines_all line, jai_ap_match_inv_taxes jamt
1564 WHERE line.invoice_id = pr_rec.invoice_id
1565 and jamt.invoice_id=line.invoice_id
1566 and jamt.invoice_distribution_id=pr_rec.invoice_distribution_id
1567 and jamt.po_distribution_id=pr_rec.po_distribution_id
1568 and line.line_number=jamt.parent_invoice_line_number
1569 -- AND line.po_distribution_id = line.po_distribution_id
1570 AND line.line_type_lookup_code ='ITEM'
1571 AND line.match_type IS NOT NULL;
1572
1573
1574 CURSOR get_curr_dtls_cur IS
1575 SELECT exchange_date
1576 , exchange_rate
1577 , exchange_rate_type
1578 , invoice_date
1579 , invoice_currency_code
1580 FROM ap_invoices_all
1581 WHERE invoice_id = pr_rec.invoice_id ;
1582
1583 CURSOR get_regime_id_cur IS
1584 SELECT regime_id
1585 FROM jai_rgm_definitions
1586 WHERE regime_code = jai_constants.service_regime;
1587
1588 CURSOR get_org_cur IS
1589 SELECT organization_id,location_id
1590 FROM jai_ap_invoice_lines
1591 WHERE invoice_id = pr_rec.invoice_id
1592 AND invoice_line_number = (SELECT parent_invoice_line_number
1593 FROM jai_ap_invoice_lines jail
1594 WHERE jail.invoice_line_number = pr_rec.invoice_line_number
1595 AND jail.invoice_id = pr_rec.invoice_id)
1596 AND parent_invoice_line_number IS NULL;
1597
1598 CURSOR get_po_matched_org_cur(pn_line_number NUMBER) IS
1599 SELECT po.ship_to_organization_id, po.ship_to_location_id
1600 FROM po_line_locations_all po,ap_invoice_lines_all ap
1601 WHERE po.line_location_id = ap.po_line_location_id
1602 AND ap.invoice_id = pr_rec.invoice_id
1603 AND ap.line_number = pn_line_number;
1604
1605 CURSOR get_rcpt_matched_org_cur(pn_line_number NUMBER) IS
1606 SELECT rcv.organization_id, rcv.location_id
1607 FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
1608 WHERE ap.rcv_transaction_id = rcv.transaction_id
1609 AND ap.invoice_id = pr_rec.invoice_id
1610 AND ap.line_number = pn_line_number;
1611
1612 CURSOR c_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
1613 select to_date(attribute_value, 'DD/MM/YYYY')
1614 from JAI_RGM_ORG_REGNS_V
1615 where regime_id = p_regime_id
1616 and organization_id = p_organization_id
1617 and location_id = p_location_id
1618 AND attribute_code = 'EFF_DATE_ST_PT'
1619 AND attribute_type_code = 'OTHERS'
1620 AND registration_type = 'OTHERS'
1621 AND (NOT EXISTS
1622 (select '1'
1623 from JAI_RGM_ORG_REGNS_V
1624 where regime_id = p_regime_id
1625 and attribute_code IN 'INV_ORG_CLASSIFICATION'
1626 and attribute_value <> 'ORGANIZATION'
1627 and organization_id = p_organization_id
1628 and location_id = p_location_id)
1629 OR
1630 NOT EXISTS
1631 (select '1'
1632 from JAI_RGM_ORG_REGNS_V
1633 where regime_id = p_regime_id
1634 and attribute_code IN 'SERVICE TYPE'
1635 and attribute_value <> 'OTHER'
1636 and organization_id = p_organization_id
1637 and location_id = p_location_id)
1638 );
1639
1640 CURSOR get_cancell_date_cur IS
1641 SELECT cancelled_date
1642 FROM ap_invoices_all
1643 WHERE invoice_id = pr_rec.invoice_id;
1644
1645 cursor get_posted_flag( P_INVOICE_DISTRIBUTION_ID NUMBER ) IS
1646 select 'Y'
1647 from jai_cmn_journal_entries jje
1648 where jje.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
1649 AND jje.source_trx_id = P_INVOICE_DISTRIBUTION_ID;
1650
1651
1652 lv_posted_flag VARCHAR2(1);
1653 lv_same_period_flag VARCHAR2(1);
1654
1655 lv_process_message VARCHAR2(200);
1656 ln_regime_id NUMBER;
1657 lv_regime_code VARCHAR2(30) := jai_constants.service_regime;
1658 lv_tax_type VARCHAR2(30);
1659 ln_organization_id NUMBER;
1660 ln_location_id NUMBER;
1661 ln_charge_account_id NUMBER;
1662 ld_transaction_date DATE;
1663 ld_gl_date DATE;
1664 ld_accrual_date DATE;
1665 lv_called_from VARCHAR2(80) ;
1666 lv_currency_code VARCHAR2(30) := 'INR';
1667 ld_curr_conv_date DATE;
1668 lv_curr_conv_type VARCHAR2(30);
1669 ln_curr_conv_rate NUMBER;
1670 ln_entered_amt NUMBER;
1671 ln_accounted_amt NUMBER;
1672 lv_invoice_type VARCHAR2(30);
1673 ln_dist_line_amount NUMBER := pr_rec.amount;
1674 ln_invoice_id NUMBER := pr_rec.invoice_id;
1675 ln_invoice_dist_id NUMBER := pr_rec.invoice_distribution_id;
1676 lv_match_type VARCHAR2(50);
1677 ln_item_line_num NUMBER;
1678 lv_event VARCHAR2(10);
1679 lv_account_name VARCHAR2(30);
1680 lv_source_trx_type VARCHAR2(30);
1681 ld_canceled_date DATE;
1682 ln_round_accounted_amt NUMBER;
1683 ln_round_ent_amt NUMBER;
1684 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ACCOUNTING()';
1685
1686 FUNCTION check_same_period_func
1687 RETURN VARCHAR2
1688 IS
1689 PRAGMA AUTONOMOUS_TRANSACTION;
1690
1691 CURSOR check_same_period_cur IS
1692 SELECT 'Y'
1693 FROM ap_invoice_distributions_all
1694 WHERE invoice_id = pr_rec.invoice_id
1695 AND invoice_distribution_id = pr_rec.parent_reversal_id
1696 AND period_name = pr_rec.period_name;
1697
1698 lv_same_period varchar2(1);
1699
1700 BEGIN
1701
1702 open check_same_period_cur;
1703 fetch check_same_period_cur into lv_same_period;
1704 close check_same_period_cur;
1705
1706 return lv_same_period;
1707
1708 END check_same_period_func;
1709
1710 BEGIN
1711 pv_return_code := jai_constants.successful ;
1712
1713 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
1714 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
1715 end if;
1716
1717
1718 If pv_action = jai_constants.updating Then
1719
1720 ld_canceled_date := NULL;
1721 OPEN get_cancell_date_cur;
1722 FETCH get_cancell_date_cur INTO ld_canceled_date;
1723 CLOSE get_cancell_date_cur;
1724
1725 OPEN get_posted_flag( pr_rec.INVOICE_DISTRIBUTION_ID );
1726 fetch get_posted_flag INTO lv_posted_flag;
1727 CLOSE get_posted_flag;
1728
1729 IF nvl(lv_posted_flag,'N') = 'N' AND pr_rec.posted_flag = 'Y' AND pr_rec.parent_reversal_id is NULL
1730 AND ld_canceled_date is NULL AND nvl(pr_rec.reversal_flag, 'N') = 'N' THEN
1731 lv_event := 'ACCOUNTING';
1732 lv_called_from := 'AP_ACCOUNTING';
1733 lv_source_trx_type := 'INVOICE_ACCOUNTING';
1734 ld_gl_date := pr_rec.accounting_date;
1735
1736 ELSIF nvl(pr_rec.cancellation_flag, 'N') = 'Y' AND pr_rec.parent_reversal_id is not NULL AND nvl(lv_posted_flag,'N') = 'N' AND pr_rec.posted_flag = 'Y' THEN
1737 lv_event := 'CANCEL';
1738 lv_called_from := 'AP_CANCELLATION';
1739 lv_source_trx_type := 'INVOICE_CANCELLATION';
1740 ld_gl_date := pr_rec.accounting_date;
1741 ELSE
1742 RETURN;
1743 END IF;
1744
1745
1746 IF pr_rec.line_type_lookup_code = 'MISCELLANEOUS' AND ln_dist_line_amount <> 0 THEN
1747 IF jai_cmn_reverse_charge_pkg.is_reverse_charge_dist(pr_rec.invoice_distribution_id) = 'Y' THEN
1748 RETURN;
1749 END IF;
1750
1751
1752 OPEN get_invoice_type_cur;
1753 FETCH get_invoice_type_cur INTO lv_invoice_type;
1754 CLOSE get_invoice_type_cur;
1755
1756 IF lv_invoice_type IN ('STANDARD', 'CREDIT', 'DEBIT', 'QUICKDEFAULT', 'QUICKMATCH', 'MIXED') THEN
1757 OPEN get_regime_id_cur;
1758 FETCH get_regime_id_cur INTO ln_regime_id;
1759 CLOSE get_regime_id_cur;
1760
1761 OPEN get_curr_dtls_cur;
1762 FETCH get_curr_dtls_cur INTO ld_curr_conv_date, ln_curr_conv_rate, lv_curr_conv_type,
1763 ld_transaction_date,lv_currency_code;
1764 CLOSE get_curr_dtls_cur;
1765
1766
1767 IF pr_rec.po_distribution_id IS NOT NULL THEN
1768
1769 OPEN get_match_item_cur;
1770 FETCH get_match_item_cur INTO lv_match_type, ln_item_line_num;
1771 CLOSE get_match_item_cur;
1772
1773
1774 IF lv_match_type IN('ITEM_TO_PO','ITEM_TO_RECEIPT') THEN
1775 OPEN get_matched_tax_cur;
1776 FETCH get_matched_tax_cur INTO ln_dist_line_amount,lv_tax_type;
1777 CLOSE get_matched_tax_cur;
1778
1779 IF lv_match_type = 'ITEM_TO_PO' THEN
1780 OPEN get_po_matched_org_cur(ln_item_line_num);
1781 FETCH get_po_matched_org_cur INTO ln_organization_id,ln_location_id;
1782 CLOSE get_po_matched_org_cur;
1783 ELSE
1784 OPEN get_rcpt_matched_org_cur(ln_item_line_num);
1785 FETCH get_rcpt_matched_org_cur INTO ln_organization_id,ln_location_id;
1786 CLOSE get_rcpt_matched_org_cur;
1787 IF ln_organization_id IS NULL OR ln_location_id IS NULL THEN
1788 OPEN get_po_matched_org_cur(ln_item_line_num);
1789 FETCH get_po_matched_org_cur INTO ln_organization_id,ln_location_id;
1790 CLOSE get_po_matched_org_cur;
1791 END IF;/*ln_organization_id IS NULL OR ln_location_id IS NULL */
1792 END IF;/*lv_match_type = 'ITEM_TO_PO' */
1793 END IF;/*lv_match_type IN('ITEM_TO_PO','ITEM_TO_RECEIPT')*/
1794 ELSE
1795 OPEN get_tax_type_cur;
1796 FETCH get_tax_type_cur INTO lv_tax_type;
1797 CLOSE get_tax_type_cur;
1798
1799 OPEN get_org_cur;
1800 FETCH get_org_cur INTO ln_organization_id,ln_location_id;
1801 CLOSE get_org_cur;
1802
1803 END IF;/*pr_rec.po_distribution_id IS NOT NULL */
1804
1805 OPEN c_get_st_accrual_date(ln_regime_id, ln_organization_id, ln_location_id);
1806 FETCH c_get_st_accrual_date INTO ld_accrual_date;
1807 CLOSE c_get_st_accrual_date;
1808
1809 IF pr_rec.accounting_date >= ld_accrual_date THEN
1810 ln_entered_amt := round(ln_dist_line_amount, jai_constants.service_rgm_rnd_factor);
1811 ln_accounted_amt := ln_entered_amt * nvl(ln_curr_conv_rate, 1);
1812
1813 IF lv_tax_type IS NOT NULL THEN
1814 IF lv_invoice_type IN ('CREDIT', 'DEBIT') THEN
1815 IF lv_event = 'ACCOUNTING' THEN
1816 lv_account_name := jai_constants.liability_interim;
1817 ELSE
1818 lv_account_name := jai_constants.liability;
1819 END IF;
1820 ln_entered_amt := abs(ln_entered_amt);
1821 ln_accounted_amt := abs(ln_accounted_amt);
1822 ELSE
1823 IF lv_event = 'ACCOUNTING' THEN
1824 lv_account_name := jai_constants.recovery;
1825 ELSE
1826 lv_account_name := jai_constants.recovery_interim;
1827 END IF;
1828 ln_entered_amt := abs(ln_entered_amt);
1829 ln_accounted_amt := abs(ln_accounted_amt);
1830 END IF;
1831 ln_charge_account_id := jai_cmn_rgm_recording_pkg.get_account(
1832 p_regime_id => ln_regime_id
1833 , p_organization_type => jai_constants.service_tax_orgn_type
1834 , p_organization_id => ln_organization_id
1835 , p_location_id => ln_location_id
1836 , p_tax_type => lv_tax_type
1837 , p_account_name => lv_account_name);
1838
1839 IF ln_charge_account_id IS NULL THEN
1840
1841 lv_process_message := 'Recovery/Liability Accounts are not defined at the regime registration. Review Invoice ID: ' || pr_rec.invoice_id;
1842 RAISE_APPLICATION_ERROR(-20007, 'Error - trigger ja_in_ap_aida_after_trg on ap_invoice_distributions_all: '
1843 ||lv_process_message);
1844 END IF;
1845
1846 jai_cmn_rgm_recording_pkg.post_accounting(
1847 p_regime_code => lv_regime_code,
1848 p_tax_type => lv_tax_type,
1849 p_organization_type => jai_constants.service_tax_orgn_type,
1850 p_organization_id => ln_organization_id,
1851 p_source => jai_constants.source_ap,
1852 p_source_trx_type => lv_source_trx_type,
1853 p_source_table_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
1854 p_source_document_id => ln_invoice_dist_id,
1855 p_code_combination_id => ln_charge_account_id,
1856 p_entered_cr => NULL,
1857 p_entered_dr => ln_entered_amt,
1858 p_accounted_cr => NULL,
1859 p_accounted_dr => ln_accounted_amt,
1860 p_accounting_date => ld_gl_date,
1861 p_transaction_date => ld_transaction_date,
1862 p_calling_object => lv_called_from,
1863 p_repository_name => 'invoice_distribution_id',
1864 p_repository_id => NULL,
1865 p_reference_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
1866 p_reference_id => ln_invoice_dist_id,
1867 p_currency_code => lv_currency_code,
1868 p_curr_conv_date => ld_curr_conv_date,
1869 p_curr_conv_type => lv_curr_conv_type,
1870 p_curr_conv_rate => ln_curr_conv_rate
1871 );
1872
1873 IF lv_invoice_type IN ('CREDIT', 'DEBIT') THEN
1874 IF lv_event = 'ACCOUNTING' THEN
1875 lv_account_name := jai_constants.liability;
1876 ELSE
1877 lv_account_name := jai_constants.liability_interim;
1878 END IF;
1879 ELSE
1880 IF lv_event = 'ACCOUNTING' THEN
1881 lv_account_name := jai_constants.recovery_interim;
1882 ELSE
1883 IF lv_invoice_type = 'STANDARD' THEN
1884 lv_same_period_flag := check_same_period_func;
1885 IF nvl(lv_same_period_flag, 'N') = 'N' THEN
1886 lv_account_name := jai_constants.liability;
1887 ELSE
1888 lv_account_name := jai_constants.recovery;
1889 END IF;
1890 ELSE
1891 lv_account_name := jai_constants.recovery;
1892 END IF;
1893 END IF;
1894 END IF;
1895
1896 ln_charge_account_id := jai_cmn_rgm_recording_pkg.get_account(
1897 p_regime_id => ln_regime_id
1898 , p_organization_type => jai_constants.service_tax_orgn_type
1899 , p_organization_id => ln_organization_id
1900 , p_location_id => ln_location_id
1901 , p_tax_type => lv_tax_type
1902 , p_account_name => lv_account_name);
1903 IF ln_charge_account_id IS NULL THEN
1904
1905 lv_process_message := 'Recovery/Liability Accounts are not defined at the regime registration. Review Invoice ID: ' || pr_rec.invoice_id;
1906 RAISE_APPLICATION_ERROR(-20007, 'Error - trigger ja_in_ap_aida_after_trg on ap_invoice_distributions_all: '
1907 ||lv_process_message);
1908 END IF;
1909
1910 jai_cmn_rgm_recording_pkg.post_accounting(
1911 p_regime_code => lv_regime_code,
1912 p_tax_type => lv_tax_type,
1913 p_organization_type => jai_constants.service_tax_orgn_type,
1914 p_organization_id => ln_organization_id,
1915 p_source => jai_constants.source_ap,
1916 p_source_trx_type => lv_source_trx_type,
1917 p_source_table_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
1918 p_source_document_id => ln_invoice_dist_id,
1919 p_code_combination_id => ln_charge_account_id,
1920 p_entered_cr => ln_entered_amt,
1921 p_entered_dr => NULL,
1922 p_accounted_cr => ln_accounted_amt,
1923 p_accounted_dr => NULL,
1924 p_accounting_date => ld_gl_date,
1925 p_transaction_date => ld_transaction_date,
1926 p_calling_object => lv_called_from,
1927 p_repository_name => 'invoice_distribution_id',
1928 p_repository_id => NULL,
1929 p_reference_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
1930 p_reference_id => ln_invoice_dist_id,
1931 p_currency_code => lv_currency_code,
1932 p_curr_conv_date => ld_curr_conv_date,
1933 p_curr_conv_type => lv_curr_conv_type,
1934 p_curr_conv_rate => ln_curr_conv_rate
1935 );
1936
1937 END IF;/*tax_type is null*/
1938 END IF;/*ld_transaction_date >= ld_accrual_date*/
1939 END IF;/* lv_invoice_type IN ('STANDARD', 'CREDIT',....)*/
1940 END IF; /*pr_rec.line_type_lookup_code = 'MISCELLANEOUS' AND ln_dist_line_amount > 0 */
1941 End if; /* updating */
1942
1943 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
1944 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()+');
1945 end if;
1946
1947 EXCEPTION
1948 WHEN OTHERS THEN
1949 pv_return_code := jai_constants.unexpected_error;
1950 pv_return_message := substr(sqlerrm,1,1900);
1951 if ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
1952 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.EXCEPTION', G_PKG_NAME || ': '||l_api_name||'()+'||' EXCEPTION Message:'||pv_return_message);
1953 end if;
1954 app_exception.raise_exception (
1955 EXCEPTION_TYPE => 'APP',
1956 EXCEPTION_CODE => -20001 ,
1957 EXCEPTION_TEXT => 'Encountered the error in procedure JAI_AP_TAX_PROCESSING.CALCULATE_TAX' || substr(sqlerrm,1,1900)
1958 );
1959
1960 END CREATE_ACCOUNTING ;
1961
1962 /*-------------------------------------------------------------------------------------------------------------------------------+
1963 | Created By : Qiong.Liu |
1964 | Creation Date : 16/Oct/2012 |
1965 | Bug Number/ER Name : ZX AP Open Interface |
1966 | SubProgram Name : Open_Interface |
1967 | Type : PROCEDURE |
1968 | Purpose : Process JAI tax for AP Open Interface |
1969 | TDD Reference : Section 8.6 |
1970 | Assumptions : |
1971 | Called From : ZX_API_PUB.Calculate_tax |
1972 |--------------------------------------------------------------------------------------------------------------------------------|
1973 | parameters IN/OUT Type Required Description and Purpose |
1974 | ------------ -------- ------ ---------- ------------------------- |
1975 | p_transaction_rec IN RECORD yes zx transaction record |
1976 | pv_return_status OUT VARCHAR2 YES Return status |
1977 | |
1978 ---------------------------------------------------------------------------------------------------------------------------------*/
1979 PROCEDURE OPEN_INTERFACE
1980 ( p_event_class_rec ZX_API_PUB.event_class_rec_type,
1981 pv_return_status OUT NOCOPY VARCHAR2
1982 ) IS
1983
1984 -- cursor to get invoice detail
1985 cursor c_get_invs(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
1986 IS
1987 select ai.*
1988 from ap_invoices_all ai
1989 where ai.invoice_id = cp_trx_id;
1990
1991 -- cursor to get PO/RCV match invoice line detail
1992 cursor c_get_inv_lines(cp_invoice_id ap_invoices_all.INVOICE_ID%TYPE)
1993 IS
1994 select ail.*
1995 from ap_invoice_lines_all ail
1996 where ail.invoice_id = cp_invoice_id
1997 and ail.match_type in ('ITEM_TO_RECEIPT', 'ITEM_TO_PO', 'PO_PRICE_ADJUSTMENT')
1998 and ail.discarded_flag = 'N';
1999
2000 -- cursor to get invoice payment schedules
2001 cursor c_get_inv_pss(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE)
2002 IS
2003 select apsa.*
2004 from ap_payment_schedules_all apsa
2005 where apsa.invoice_id = cp_trx_id;
2006
2007 cursor c_jai_ap_tds_thhold_trxs(cp_invoice_id number) IS
2008 select parent_inv_payment_priority
2009 from jai_ap_tds_thhold_trxs
2010 where invoice_to_vendor_id = cp_invoice_id;
2011
2012 cursor c_match_tax
2013 ( cp_match_type VARCHAR2,
2014 cp_trx_id VARCHAR2,
2015 cp_vendor_id NUMBER) IS
2016 select *
2017 from JAI_PO_RCV_TAXES_V jpr
2018 where jpr.match_type = cp_match_type
2019 and jpr.trx_id = cp_trx_id
2020 and jpr.vendor_id = cp_vendor_id;
2021
2022 cursor c_get_total_line_amount(cp_trx_id ap_invoices_all.INVOICE_ID%TYPE) is
2023 select sum(nvl(amount,0))
2024 from ap_invoice_lines_all
2025 where invoice_id=cp_trx_id
2026 and line_type_lookup_code in ('ITEM', 'MISCELLANEOUS', 'FREIGHT') --Added by Chong for bug#16743694 2013/05/07
2027 ;
2028
2029 ln_parent_inv_payment_priority jai_ap_tds_thhold_trxs.parent_inv_payment_priority%type;
2030 inv_rec AP_INVOICES_ALL%ROWTYPE;
2031 inv_line_rec AP_INVOICE_LINES_ALL%ROWTYPE;
2032 inv_dist_rec AP_INVOICE_DISTRIBUTIONS_ALL%ROWTYPE;
2033 inv_ps_rec AP_PAYMENT_SCHEDULES%ROWTYPE;
2034 lv_action VARCHAR2(100);
2035 lv_return_code VARCHAR2(100);
2036 lv_return_message VARCHAR2(2000);
2037 lv_process_flag varchar2(2);
2038 lv_process_message varchar2(2000);
2039 p_codepath varchar2(2000);
2040 ln_index number := 1;
2041 l_tax_line_tbl jai_ap_match_tax_proc_pkg.l_tax_line_tbl_type;
2042 lv_match_type VARCHAR2(20);
2043 lv_trx_id VARCHAR2(30);
2044 lv_receipt_code VARCHAR2(20);
2045 ln_shipment_line_id NUMBER;
2046 ln_shipment_header_id NUMBER;
2047 ln_vendor_id NUMBER;
2048 lv_shipment_num rcv_headers_interface.shipment_num%type;
2049 ln_rounding_factor NUMBER;
2050 ln_apported_tax_amount NUMBER;
2051 ln_mod_cr_percentage NUMBER;
2052 le_error exception;
2053 l_count NUMBER;
2054 l_total_line_amount NUMBER;
2055 l_api_name CONSTANT VARCHAR2(30) := 'OPEN_INTERFACE()';
2056
2057 BEGIN
2058
2059 IF (p_event_class_rec.application_id = 200) THEN --Indicates AP Module
2060
2061 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name, p_event_class_rec.trx_id );
2062 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name, p_event_class_rec.event_class_code );
2063 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name, p_event_class_rec.tax_event_type_code );
2064 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name, p_event_class_rec.event_type_code );
2065 IF p_event_class_rec.event_class_code IN ('STANDARD INVOICES') AND
2066 ((p_event_class_rec.tax_event_type_code IN ('CREATE') AND
2067 p_event_class_rec.event_type_code IN ('STANDARD CREATED')
2068 ) OR
2069 (p_event_class_rec.tax_event_type_code IN ('UPDATE') AND --Added update by Chong for open interface issue 2013/04/09
2070 p_event_class_rec.event_type_code IN ('STANDARD UPDATED')
2071 )
2072 ) THEN
2073
2074 lv_action := JAI_CONSTANTS.inserting;
2075
2076 OPEN c_get_invs(p_event_class_rec.trx_id);
2077 FETCH c_get_invs into inv_rec;
2078 CLOSE c_get_invs;
2079 fnd_file.put_line(fnd_file.log,'In JAI_AP_TAX_PROCESSING.OPEN_INTERFACEIN before call JAI_AP_OI_PROCESSING_PKG.PROCESS_INVOICE invoice_id=' ||p_event_class_rec.trx_id);
2080 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name, 'In JAI_AP_TAX_PROCESSING.OPEN_INTERFACEIN before call JAI_AP_OI_PROCESSING_PKG.PROCESS_INVOICE invoice_id=' ||p_event_class_rec.trx_id );
2081 JAI_AP_OI_PROCESSING_PKG.PROCESS_INVOICE(
2082 pr_rec => inv_rec,
2083 pv_action => lv_action,
2084 pv_return_code => lv_return_code,
2085 pv_return_message => lv_return_message
2086 );
2087 IF lv_return_code <> jai_constants.successful THEN
2088 RAISE le_error;
2089 END IF;
2090
2091
2092 FOR inv_ps_rec IN c_get_inv_pss(p_event_class_rec.trx_id)
2093 LOOP
2094 fnd_file.put_line(fnd_file.log,'In JAI_AP_TAX_PROCESSING.OPEN_INTERFACEIN at LOOP c_get_inv_pss invoice_id=' ||p_event_class_rec.trx_id);
2095 IF inv_rec.invoice_type_lookup_code = 'CREDIT' and inv_rec.source = 'INDIA TDS' then
2096
2097 open c_jai_ap_tds_thhold_trxs(p_event_class_rec.trx_id);
2098 fetch c_jai_ap_tds_thhold_trxs into ln_parent_inv_payment_priority;
2099 close c_jai_ap_tds_thhold_trxs;
2100
2101 IF ln_parent_inv_payment_priority is not null THEN
2102 update ap_payment_schedules_all
2103 set payment_priority = ln_parent_inv_payment_priority
2104 where payment_num = inv_ps_rec.payment_num
2105 and invoice_id = inv_rec.invoice_id;
2106 END IF;
2107
2108 End IF;
2109 END LOOP;
2110
2111 For inv_lines_rec in c_get_inv_lines(p_event_class_rec.trx_id) loop
2112 fnd_file.put_line(fnd_file.log,'In JAI_AP_TAX_PROCESSING.OPEN_INTERFACEIN at LOOP c_get_inv_lines LINE_NUMBER=' || inv_lines_rec.LINE_NUMBER);
2113 jai_ap_match_tax_proc_pkg.tax_check_at_inv_validate(inv_lines_rec.INVOICE_ID,
2114 inv_lines_rec.LINE_NUMBER,
2115 lv_process_flag,
2116 lv_process_message,
2117 p_codepath
2118 );
2119 if lv_process_flag <> 'S' then
2120 JAI_AP_OI_PROCESSING_PKG.pre_data_for_ap_line
2121 (
2122 pn_invoice_id => inv_lines_rec.invoice_id,
2123 pn_line_number => inv_lines_rec.line_number,
2124 pv_return_code => lv_return_code,
2125 pv_return_message => lv_return_message
2126 );
2127 IF lv_return_code <> jai_constants.successful then
2128 RAISE le_error;
2129 END IF;
2130 end if;
2131
2132 END LOOP;
2133
2134 OPEN c_get_total_line_amount(p_event_class_rec.trx_id);
2135 FETCH c_get_total_line_amount into l_total_line_amount;
2136 CLOSE c_get_total_line_amount;
2137
2138 update ap_invoices_all
2139 set invoice_amount = nvl(l_total_line_amount,0)
2140 where invoice_id= p_event_class_rec.trx_id;
2141
2142 END IF;
2143
2144 END IF;
2145
2146 EXCEPTION
2147 WHEN le_error THEN
2148
2149 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
2150 fnd_message.set_token ('MSG_TEXT', lv_return_message);
2151 pv_return_status := FND_API.G_RET_STS_ERROR;
2152 fnd_msg_pub.add;
2153 fnd_file.put_line(fnd_file.log,lv_return_message);
2154
2155 WHEN OTHERS THEN
2156 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
2157 fnd_message.set_token ('MSG_TEXT', 'Encountered the error in JAI_AP_TAX_PROCESSING.OPEN_INTERFACE ' || substr(sqlerrm,1,1900));
2158 fnd_msg_pub.add;
2159 pv_return_status := FND_API.G_RET_STS_ERROR;
2160 fnd_file.put_line(fnd_file.log,'Encountered the error in JAI_AP_TAX_PROCESSING.OPEN_INTERFACE ' || substr(sqlerrm,1,1900));
2161
2162 END OPEN_INTERFACE;
2163
2164
2165 end JAI_TAX_PROCESSING_PKG;