[Home] [Help]
PACKAGE BODY: APPS.ZX_TRX_DETAIL
Source
1 PACKAGE BODY ZX_TRX_DETAIL AS
2 /* $Header: zxritsimdetailb.pls 120.50.12010000.1 2008/07/28 13:37:16 appldev ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 TYPE var1_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
11 trx_line_type_tab var1_tab;
12
13 PROCEDURE get_error_msg(
14 p_trx_id IN NUMBER,
15 p_application_id IN NUMBER,
16 p_entity_code IN VARCHAR2,
17 p_event_class_code IN VARCHAR2,
18 p_return_status IN VARCHAR2,
19 x_msg_data OUT NOCOPY VARCHAR2);
20
21
22 PROCEDURE perform_purge(
23 p_count IN NUMBER,
24 p_application_id_tbl IN APPLICATION_ID_TBL,
25 p_entity_code_tbl IN ENTITY_CODE_TBL,
26 p_event_class_code_tbl IN EVENT_CLASS_CODE_TBL,
27 p_trx_id_tbl IN TRX_ID_TBL,
28 p_return_status OUT NOCOPY VARCHAR2,
29 p_error_buffer OUT NOCOPY VARCHAR2);
30
31 PROCEDURE Insert_row
32 (p_Rowid IN OUT NOCOPY VARCHAR2,
33 p_internal_organization_id NUMBER,
34 p_internal_org_location_id NUMBER,
35 p_application_id NUMBER,
36 p_entity_code VARCHAR2,
37 p_event_class_code VARCHAR2,
38 p_event_type_code VARCHAR2,
39 p_trx_id NUMBER,
40 p_trx_date DATE,
41 p_trx_doc_revision VARCHAR2,
42 p_ledger_id NUMBER,
43 p_trx_currency_code VARCHAR2,
44 p_currency_conversion_date DATE,
45 p_currency_conversion_rate NUMBER,
46 p_currency_conversion_type VARCHAR2,
47 p_minimum_accountable_unit NUMBER,
48 p_precision NUMBER,
49 p_legal_entity_id NUMBER,
50 p_rounding_ship_to_party_id NUMBER,
51 p_rounding_ship_from_party_id NUMBER,
52 p_rounding_bill_to_party_id NUMBER,
53 p_rounding_bill_from_party_id NUMBER,
54 p_rndg_ship_to_party_site_id NUMBER,
55 p_rndg_ship_from_pty_site_id NUMBER, --reduced size p_rndg_ship_from_party_site_id
56 p_rndg_bill_to_party_site_id NUMBER,
57 p_rndg_bill_from_pty_site_id NUMBER, --reduced size p_rndg_bill_from_party_site_id
58 p_establishment_id NUMBER,
59 p_related_doc_application_id NUMBER,
60 p_related_doc_entity_code VARCHAR2,
61 p_related_doc_evt_class_code VARCHAR2, --reduced size p_related_doc_event_class_code
62 p_related_doc_trx_id NUMBER,
63 p_related_doc_number VARCHAR2,
64 p_related_doc_date DATE,
65 p_default_taxation_country VARCHAR2,
66 p_quote_flag VARCHAR2,
67 p_trx_number VARCHAR2,
68 p_trx_description VARCHAR2,
69 p_trx_communicated_date DATE,
70 p_batch_source_id NUMBER,
71 p_batch_source_name VARCHAR2,
72 --p_doc_seq_id NUMBER,
73 --p_doc_seq_name VARCHAR2,
74 --p_doc_seq_value VARCHAR2,
75 p_trx_due_date DATE,
76 p_trx_type_description VARCHAR2,
77 p_billing_trad_partner_name VARCHAR2, --reduced size p_billing_trading_partner_name
78 p_billing_trad_partner_number VARCHAR2, --reduced size p_billing_trading_partner_number
79 p_billing_tp_tax_report_flg VARCHAR2, --reduced size p_Billing_Tp_Tax_Reporting_Flag
80 p_billing_tp_taxpayer_id VARCHAR2,
81 p_document_sub_type VARCHAR2,
82 p_supplier_tax_invoice_number VARCHAR2,
83 p_supplier_tax_invoice_date DATE,--
84 p_supplier_exchange_rate NUMBER,
85 p_tax_invoice_date DATE,
86 p_tax_invoice_number VARCHAR2,
87 p_tax_event_class_code VARCHAR2,
88 p_tax_event_type_code VARCHAR2,
89 p_doc_event_status VARCHAR2,
90 p_rdng_ship_to_pty_tx_prof_id NUMBER,
91 p_rdng_ship_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_prof_id
92 p_rdng_bill_to_pty_tx_prof_id NUMBER,
93 p_rdng_bill_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_prof_id
94 p_rdng_ship_to_pty_tx_p_st_id NUMBER,
95 p_rdng_ship_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_p_st_id
96 p_rdng_bill_to_pty_tx_p_st_id NUMBER,
97 p_rdng_bill_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_p_st_id
98 p_trx_level_type VARCHAR2,
99 p_trx_line_id NUMBER,
100 p_line_level_action VARCHAR2,
101 p_trx_shipping_date DATE,
102 p_trx_receipt_date DATE,
103 p_trx_line_type VARCHAR2,
104 p_trx_line_date DATE,
105 p_trx_business_category VARCHAR2,
106 p_line_intended_use VARCHAR2,
107 p_user_defined_fisc_class VARCHAR2,
108 p_line_amt NUMBER,
109 p_trx_line_quantity NUMBER,
110 p_unit_price NUMBER,
111 p_exempt_certificate_number VARCHAR2,
112 p_exempt_reason VARCHAR2,
113 p_cash_discount NUMBER,
114 p_volume_discount NUMBER,
115 p_trading_discount NUMBER,
116 p_transfer_charge NUMBER,
117 p_transportation_charge NUMBER,
118 p_insurance_charge NUMBER,
119 p_other_charge NUMBER,
120 p_product_id NUMBER,
121 p_product_fisc_classification VARCHAR2,
122 p_product_org_id NUMBER,
123 p_uom_code VARCHAR2,
124 p_product_type VARCHAR2,
125 p_product_code VARCHAR2,
126 p_product_category VARCHAR2,
127 p_trx_sic_code VARCHAR2,
128 p_fob_point VARCHAR2,
129 p_ship_to_party_id NUMBER,
130 p_ship_from_party_id NUMBER,
131 p_poa_party_id NUMBER,
132 p_poo_party_id NUMBER,
133 p_bill_to_party_id NUMBER,
134 p_bill_from_party_id NUMBER,
135 p_merchant_party_id NUMBER,
136 p_ship_to_party_site_id NUMBER,
137 p_ship_from_party_site_id NUMBER,
138 p_poa_party_site_id NUMBER,
139 p_poo_party_site_id NUMBER,
140 p_bill_to_party_site_id NUMBER,
141 p_bill_from_party_site_id NUMBER,
142 p_ship_to_location_id NUMBER,
143 p_ship_from_location_id NUMBER,
144 p_poa_location_id NUMBER,
145 p_poo_location_id NUMBER,
146 p_bill_to_location_id NUMBER,
147 p_bill_from_location_id NUMBER,
148 p_account_ccid NUMBER,
149 p_account_string VARCHAR2,
150 p_merchant_party_country VARCHAR2,
151 p_ref_doc_application_id NUMBER,
152 p_ref_doc_entity_code VARCHAR2,
153 p_ref_doc_event_class_code VARCHAR2,
154 p_ref_doc_trx_id NUMBER,
155 p_ref_doc_line_id NUMBER,
156 p_ref_doc_line_quantity NUMBER,
157 p_applied_from_application_id NUMBER,
158 p_applied_from_entity_code VARCHAR2,
159 p_applied_from_evt_class_code VARCHAR2, --reduced size p_applied_from_event_class_code
160 p_applied_from_trx_id NUMBER,
161 p_applied_from_line_id NUMBER,
162 p_adjusted_doc_application_id NUMBER,
163 p_adjusted_doc_entity_code VARCHAR2,
164 p_adj_doc_event_class_code VARCHAR2, --reduced size p_adjusted_doc_event_class_code
165 p_adjusted_doc_trx_id NUMBER,
166 p_adjusted_doc_line_id NUMBER,
167 p_adjusted_doc_number VARCHAR2,
168 p_adjusted_doc_date DATE,
169 p_applied_to_application_id NUMBER,
170 p_applied_to_entity_code VARCHAR2,
171 p_applied_to_event_class_code VARCHAR2,
172 p_applied_to_trx_id NUMBER,
173 p_applied_to_trx_line_id NUMBER,
174 p_trx_id_level2 NUMBER,
175 p_trx_id_level3 NUMBER,
176 p_trx_id_level4 NUMBER,
177 p_trx_id_level5 NUMBER,
178 p_trx_id_level6 NUMBER,
179 p_trx_line_number NUMBER,
180 p_trx_line_description VARCHAR2,
181 p_product_description VARCHAR2,
182 p_trx_waybill_number VARCHAR2,
183 p_trx_line_gl_date DATE,
184 p_merchant_party_name VARCHAR2,
185 p_merchant_party_doc_number VARCHAR2, --reduced size p_merchant_party_document_number
186 p_merchant_party_reference VARCHAR2,
187 p_merchant_party_taxpayer_id VARCHAR2,
188 p_merchant_pty_tax_reg_number VARCHAR2, --reduced size p_merchant_party_tax_reg_number
189 p_paying_party_id NUMBER,
190 p_own_hq_party_id NUMBER,
191 p_trading_hq_party_id NUMBER,
192 p_poi_party_id NUMBER,
193 p_pod_party_id NUMBER,
194 p_title_transfer_party_id NUMBER,
195 p_paying_party_site_id NUMBER,
196 p_own_hq_party_site_id NUMBER,
197 p_trading_hq_party_site_id NUMBER,
198 p_poi_party_site_id NUMBER,
199 p_pod_party_site_id NUMBER,
200 p_title_transfer_pty_site_id NUMBER, --reduced size p_title_transfer_party_site_id
201 p_paying_location_id NUMBER,
202 p_own_hq_location_id NUMBER,
203 p_trading_hq_location_id NUMBER,
204 p_poc_location_id NUMBER,
205 p_poi_location_id NUMBER,
206 p_pod_location_id NUMBER,
207 p_title_transfer_location_id NUMBER,
208 p_banking_tp_taxpayer_id VARCHAR2,
209 p_assessable_value NUMBER,
210 p_asset_flag VARCHAR2,
211 p_asset_number VARCHAR2,
212 p_asset_accum_depreciation NUMBER,
213 p_asset_type VARCHAR2,
214 p_asset_cost NUMBER,
215 p_ship_to_party_tax_prof_id NUMBER,
216 p_ship_from_party_tax_prof_id NUMBER,
217 p_poa_party_tax_prof_id NUMBER,
218 p_poo_party_tax_prof_id NUMBER,
219 p_paying_party_tax_prof_id NUMBER,
220 p_own_hq_party_tax_prof_id NUMBER,
221 p_trading_hq_pty_tax_prof_id NUMBER, --reduced size p_trading_hq_party_tax_prof_id
222 p_poi_party_tax_prof_id NUMBER,
223 p_pod_party_tax_prof_id NUMBER,
224 p_bill_to_party_tax_prof_id NUMBER,
225 p_bill_from_party_tax_prof_id NUMBER,
226 p_title_trans_pty_tax_prof_id NUMBER, --reduced size p_title_trans_party_tax_prof_id
227 p_ship_to_site_tax_prof_id NUMBER,
228 p_ship_from_site_tax_prof_id NUMBER,
229 p_poa_site_tax_prof_id NUMBER,
230 p_poo_site_tax_prof_id NUMBER,
231 p_paying_site_tax_prof_id NUMBER,
232 p_own_hq_site_tax_prof_id NUMBER,
233 p_trading_hq_site_tax_prof_id NUMBER,
234 p_poi_site_tax_prof_id NUMBER,
235 p_pod_site_tax_prof_id NUMBER,
236 p_bill_to_site_tax_prof_id NUMBER,
237 p_bill_from_site_tax_prof_id NUMBER,
238 p_title_trn_site_tax_prof_id NUMBER, --reduced size p_title_trans_site_tax_prof_id
239 p_merchant_party_tax_prof_id NUMBER,
240 p_line_amt_includes_tax_flag VARCHAR2,
241 p_historical_flag VARCHAR2,
242 p_tax_classification_code VARCHAR2,
243 p_ctrl_hdr_tx_appl_flag VARCHAR2,
244 p_ctrl_total_line_tx_amt NUMBER,
245 p_tax_regime_id NUMBER,
246 p_tax_regime_code VARCHAR2,
247 p_tax_id NUMBER,
248 p_tax VARCHAR2,
249 p_tax_status_id NUMBER,
250 p_tax_status_code VARCHAR2,
251 p_tax_rate_id NUMBER,
252 p_tax_rate_code VARCHAR2,
253 p_tax_rate NUMBER,
254 p_tax_line_amt NUMBER,
255 p_line_class VARCHAR2,
256 p_input_tax_classif_code VARCHAR2,
257 p_output_tax_classif_code VARCHAR2,
258 p_ref_doc_trx_level_type VARCHAR2,
259 p_applied_to_trx_level_type VARCHAR2,
260 p_applied_from_trx_level_type VARCHAR2,
261 p_adjusted_doc_trx_level_type VARCHAR2,
262 p_exemption_control_flag VARCHAR2,
263 p_exempt_reason_code VARCHAR2,
264 p_receivables_trx_type_id NUMBER,
265 p_object_version_number NUMBER,
266 p_created_by NUMBER,
267 p_creation_date DATE,
268 p_last_updated_by NUMBER,
269 p_last_update_date DATE,
270 p_last_update_login NUMBER) IS
271
272 l_return_status VARCHAR2(1000);
273 l_msg_count NUMBER;
274 l_msg_data VARCHAR2(1000);
275 sid NUMBER;
276 p_error_buffer VARCHAR2(100);
277 l_tax_event_type_code VARCHAR2(30);
278
279 CURSOR C IS
280 SELECT rowid
281 FROM zx_transaction_lines
282 WHERE APPLICATION_ID = p_application_id
283 AND ENTITY_CODE = p_entity_code
284 AND EVENT_CLASS_CODE = p_event_class_code
285 AND TRX_ID = p_trx_id
286 AND TRX_LINE_ID = p_trx_line_id
287 AND TRX_LEVEL_TYPE = p_trx_level_type;
288
289 BEGIN
290
291 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
292
293 IF (g_level_procedure >= g_current_runtime_level ) THEN
294 FND_LOG.STRING(g_level_procedure,
295 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Row.BEGIN',
296 'ZX_TRX_DETAIL: Insert_Row (+)');
297 FND_LOG.STRING(g_level_procedure,
298 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Row',
299 'Insert into ZX_TRANSACTION_LINES (+)');
300 END IF;
301
302 --IF p_trx_line_number IS NOT NULL THEN
303 /*
304 UPDATE ZX_TRANSACTION
305 SET EVENT_TYPE_CODE = p_event_type_code
306 WHERE APPLICATION_ID = p_application_id
307 AND ENTITY_CODE = p_entity_code
308 AND EVENT_CLASS_CODE = p_event_class_code
309 AND TRX_ID = p_trx_id;
310 */
311 INSERT INTO ZX_TRANSACTION_LINES (--SUBSCRIBER_ID,
312 --INTERNAL_ORGANIZATION_ID,
313 APPLICATION_ID,
314 ENTITY_CODE,
315 EVENT_CLASS_CODE,
316 --EVENT_TYPE_CODE,
317 TRX_LINE_ID,
318 TRX_LINE_NUMBER,
319 TRX_ID,
320 TRX_LEVEL_TYPE,
321 TRX_LINE_TYPE,
322 TRX_LINE_DATE,
323 TRX_BUSINESS_CATEGORY,
324 LINE_INTENDED_USE,
325 USER_DEFINED_FISC_CLASS,
326 LINE_AMT_INCLUDES_TAX_FLAG,
327 LINE_AMT,
328 TRX_LINE_QUANTITY,
329 UNIT_PRICE,
330 PRODUCT_ID,
331 PRODUCT_FISC_CLASSIFICATION,
332 PRODUCT_ORG_ID,
333 UOM_CODE,
334 PRODUCT_TYPE,
335 PRODUCT_CODE,
336 PRODUCT_CATEGORY,
337 MERCHANT_PARTY_ID,
338 ACCOUNT_CCID,
339 ACCOUNT_STRING,
340 REF_DOC_LINE_ID,
341 REF_DOC_LINE_QUANTITY,
342 REF_DOC_APPLICATION_ID,
343 REF_DOC_ENTITY_CODE,
344 REF_DOC_EVENT_CLASS_CODE,
345 REF_DOC_TRX_ID,
346 APPLIED_FROM_LINE_ID,
347 APPLIED_FROM_APPLICATION_ID,
348 APPLIED_FROM_ENTITY_CODE,
349 APPLIED_FROM_EVENT_CLASS_CODE,
350 APPLIED_FROM_TRX_ID,
351 ADJUSTED_DOC_LINE_ID,
352 ADJUSTED_DOC_DATE,
353 ADJUSTED_DOC_APPLICATION_ID,
354 ADJUSTED_DOC_ENTITY_CODE,
355 ADJUSTED_DOC_EVENT_CLASS_CODE,
356 ADJUSTED_DOC_TRX_ID,
357 TRX_LINE_DESCRIPTION,
358 PRODUCT_DESCRIPTION,
359 --TRX_COMMUNICATED_DATE,
360 TRX_LINE_GL_DATE,
361 --DOC_SEQ_ID,
362 --DOC_SEQ_NAME,
363 --DOC_SEQ_VALUE,
364 --RECEIVABLES_TRX_TYPE_ID,
365 --BATCH_SOURCE_NAME,
366 LINE_LEVEL_ACTION,
367 Historical_Flag,
368 --TRX_DATE,
369 --LEDGER_ID,
370 --MINIMUM_ACCOUNTABLE_UNIT,
371 --PRECISION,
372 --LEGAL_ENTITY_ID,
373 BILL_FROM_PARTY_SITE_ID,
374 BILL_TO_PARTY_SITE_ID,
375 SHIP_FROM_PARTY_SITE_ID,
376 SHIP_TO_PARTY_SITE_ID,
377 SHIP_TO_PARTY_ID,
378 SHIP_FROM_PARTY_ID,
379 BILL_TO_PARTY_ID,
380 BILL_FROM_PARTY_ID,
381 SHIP_TO_LOCATION_ID,
382 SHIP_FROM_LOCATION_ID,
383 BILL_TO_LOCATION_ID,
384 BILL_FROM_LOCATION_ID,
385 POA_LOCATION_ID,
386 POO_LOCATION_ID,
387 PAYING_LOCATION_ID,
388 OWN_HQ_LOCATION_ID,
389 TRADING_HQ_LOCATION_ID,
390 POC_LOCATION_ID,
391 POI_LOCATION_ID,
392 POD_LOCATION_ID,
393 TAX_REGIME_ID,
394 TAX_REGIME_CODE,
395 TAX_ID,
396 TAX,
397 TAX_STATUS_ID,
398 TAX_STATUS_CODE,
399 TAX_RATE_ID,
400 TAX_RATE_CODE,
401 TAX_RATE,
402 TAX_LINE_AMT,
403 LINE_CLASS,
404 INPUT_TAX_CLASSIFICATION_CODE,
405 OUTPUT_TAX_CLASSIFICATION_CODE,
406 REF_DOC_TRX_LEVEL_TYPE,
407 APPLIED_TO_TRX_LEVEL_TYPE,
408 APPLIED_FROM_TRX_LEVEL_TYPE,
409 ADJUSTED_DOC_TRX_LEVEL_TYPE,
410 EXEMPTION_CONTROL_FLAG,
411 EXEMPT_REASON_CODE,
412 EXEMPT_CERTIFICATE_NUMBER,
413 EXEMPT_REASON,
414 CASH_DISCOUNT,
415 VOLUME_DISCOUNT,
416 TRADING_DISCOUNT,
417 TRANSFER_CHARGE,
418 TRANSPORTATION_CHARGE,
419 INSURANCE_CHARGE,
420 OTHER_CHARGE,
421 RECEIVABLES_TRX_TYPE_ID,
422 CTRL_HDR_TX_APPL_FLAG,
423 CTRL_TOTAL_LINE_TX_AMT,
424 OBJECT_VERSION_NUMBER,
425 CREATED_BY,
426 CREATION_DATE,
427 LAST_UPDATED_BY,
428 LAST_UPDATE_DATE,
429 LAST_UPDATE_LOGIN)
430 VALUES (--p_subscriber_id,
431 --p_internal_organization_id,
432 p_application_id,
433 p_entity_code,
434 p_event_class_code,
435 --p_event_type_code,
436 p_trx_line_id,
437 p_trx_line_number,
438 p_trx_id,
439 p_trx_level_type,
440 p_trx_line_type,
441 p_trx_line_date,
442 p_trx_business_category,
443 p_line_intended_use,
444 p_user_defined_fisc_class,
445 p_Line_Amt_Includes_Tax_Flag,
446 p_line_amt,
447 p_trx_line_quantity,
448 p_unit_price,
449 p_product_id,
450 p_product_fisc_classification,
451 p_product_org_id,
452 p_uom_code,
453 p_product_type,
454 p_product_code,
455 p_product_category,
456 p_merchant_party_id,
457 p_account_ccid,
458 p_account_string,
459 p_ref_doc_line_id,
460 p_ref_doc_line_quantity,
461 p_ref_doc_application_id,
462 p_ref_doc_entity_code,
463 p_ref_doc_event_class_code,
464 p_ref_doc_trx_id,
465 p_applied_from_line_id,
466 p_applied_from_application_id,
467 p_applied_from_entity_code, --resized
468 p_applied_from_evt_class_code,--resized
469 p_applied_from_trx_id,
470 p_adjusted_doc_line_id,
471 p_adjusted_doc_date,
472 p_adjusted_doc_application_id,
473 p_adjusted_doc_entity_code,
474 p_adj_doc_event_class_code, --resized
475 p_adjusted_doc_trx_id,
476 p_trx_line_description,
477 p_product_description,
478 --p_trx_communicated_date,
479 p_trx_line_gl_date,
480 --p_doc_seq_id,
481 --p_doc_seq_name,
482 --p_doc_seq_value,
483 --p_receivables_trx_type_id,
484 --p_batch_source_name,
485 p_line_level_action,
486 p_Historical_Flag,
487 --p_trx_date,
488 --p_ledger_id,
489 --p_minimum_accountable_unit,
490 --p_precision,
491 --p_legal_entity_id,
492 p_bill_from_party_site_id,
493 p_bill_to_party_site_id,
494 p_ship_from_party_site_id,
495 p_ship_to_party_site_id,
496 p_ship_to_party_id,
497 p_ship_from_party_id,
498 p_bill_to_party_id,
499 p_bill_from_party_id,
500 p_ship_to_location_id,
501 p_ship_from_location_id,
502 p_bill_to_location_id,
503 p_bill_from_location_id,
504 p_poa_location_id,
505 p_poo_location_id,
506 p_paying_location_id,
507 p_own_hq_location_id,
508 p_trading_hq_location_id,
509 p_poc_location_id,
510 p_poi_location_id,
511 p_pod_location_id,
512 p_tax_regime_id,
513 p_tax_regime_code,
514 p_tax_id,
515 p_tax,
516 p_tax_status_id,
517 p_tax_status_code,
518 p_tax_rate_id,
519 p_tax_rate_code,
520 p_tax_rate,
521 p_tax_line_amt,
522 p_line_class,
523 p_input_tax_classif_code,
524 p_output_tax_classif_code,
525 p_ref_doc_trx_level_type,
526 p_applied_to_trx_level_type,
527 p_applied_from_trx_level_type,
528 p_adjusted_doc_trx_level_type,
529 p_exemption_control_flag,
530 p_exempt_reason_code,
531 p_exempt_certificate_number,
532 p_exempt_reason,
533 p_cash_discount,
534 p_volume_discount,
535 p_trading_discount,
536 p_transfer_charge,
537 p_transportation_charge,
538 p_insurance_charge,
539 p_other_charge,
540 p_receivables_trx_type_id,
541 p_ctrl_hdr_tx_appl_flag,
542 p_ctrl_total_line_tx_amt,
543 1, --p_object_version_number,
544 p_created_by,
545 p_creation_date,
546 p_last_updated_by,
547 p_last_update_date,
548 p_last_update_login);
549
550 OPEN C;
551 FETCH C INTO p_Rowid;
552 IF (C%NOTFOUND) THEN
553 CLOSE C;
554 Raise NO_DATA_FOUND;
555 END IF;
556 CLOSE C;
557
558 IF (g_level_procedure >= g_current_runtime_level ) THEN
559 FND_LOG.STRING(g_level_procedure,
560 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Row',
561 'Insert into ZX_TRANSACTION_LINES (-)');
562 FND_LOG.STRING(g_level_procedure,
563 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Row.END',
564 'ZX_TRX_DETAIL: Insert_Row (-)');
565 END IF;
566
567 END Insert_row;
568
569 PROCEDURE Update_row
570 (p_Rowid VARCHAR2,
571 p_internal_organization_id NUMBER,
572 p_internal_org_location_id NUMBER,
573 p_application_id NUMBER,
574 p_entity_code VARCHAR2,
575 p_event_class_code VARCHAR2,
576 p_event_type_code VARCHAR2,
577 p_trx_id NUMBER,
578 p_trx_date DATE,
579 p_trx_doc_revision VARCHAR2,
580 p_ledger_id NUMBER,
581 p_trx_currency_code VARCHAR2,
582 p_currency_conversion_date DATE,
583 p_currency_conversion_rate NUMBER,
584 p_currency_conversion_type VARCHAR2,
585 p_minimum_accountable_unit NUMBER,
586 p_precision NUMBER,
587 p_legal_entity_id NUMBER,
588 p_rounding_ship_to_party_id NUMBER,
589 p_rounding_ship_from_party_id NUMBER,
590 p_rounding_bill_to_party_id NUMBER,
591 p_rounding_bill_from_party_id NUMBER,
592 p_rndg_ship_to_party_site_id NUMBER,
593 p_rndg_ship_from_pty_site_id NUMBER, --reduced size p_rndg_ship_from_party_site_id
594 p_rndg_bill_to_party_site_id NUMBER,
595 p_rndg_bill_from_pty_site_id NUMBER, --reduced size p_rndg_bill_from_party_site_id
596 p_establishment_id NUMBER,
597 p_related_doc_application_id NUMBER,
598 p_related_doc_entity_code VARCHAR2,
599 p_related_doc_evt_class_code VARCHAR2, --reduced size p_related_doc_event_class_code
600 p_related_doc_trx_id NUMBER,
601 p_related_doc_number VARCHAR2,
602 p_related_doc_date DATE,
603 p_default_taxation_country VARCHAR2,
604 p_quote_flag VARCHAR2,
605 p_trx_number VARCHAR2,
606 p_trx_description VARCHAR2,
607 p_trx_communicated_date DATE,
608 p_batch_source_id NUMBER,
609 p_batch_source_name VARCHAR2,
610 --p_doc_seq_id NUMBER,
611 --p_doc_seq_name VARCHAR2,
612 --p_doc_seq_value VARCHAR2,
613 p_trx_due_date DATE,
614 p_trx_type_description VARCHAR2,
615 p_billing_trad_partner_name VARCHAR2, --reduced size p_billing_trading_partner_name
616 p_billing_trad_partner_number VARCHAR2, --reduced size p_billing_trading_partner_number
617 p_billing_tp_tax_report_flg VARCHAR2, --reduced size p_Billing_Tp_Tax_Reporting_Flag
618 p_billing_tp_taxpayer_id VARCHAR2,
619 p_document_sub_type VARCHAR2,
620 p_supplier_tax_invoice_number VARCHAR2,
621 p_supplier_tax_invoice_date DATE,
622 p_supplier_exchange_rate NUMBER,
623 p_tax_invoice_date DATE,
624 p_tax_invoice_number VARCHAR2,
625 p_tax_event_class_code VARCHAR2,
626 p_tax_event_type_code VARCHAR2,
627 p_doc_event_status VARCHAR2,
628 p_rdng_ship_to_pty_tx_prof_id NUMBER,
629 p_rdng_ship_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_prof_id
630 p_rdng_bill_to_pty_tx_prof_id NUMBER,
631 p_rdng_bill_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_prof_id
632 p_rdng_ship_to_pty_tx_p_st_id NUMBER,
633 p_rdng_ship_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_p_st_id
634 p_rdng_bill_to_pty_tx_p_st_id NUMBER,
635 p_rdng_bill_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_p_st_id
636 p_trx_level_type VARCHAR2,
637 p_trx_line_id NUMBER,
638 p_line_level_action VARCHAR2,
639 p_trx_shipping_date DATE,
640 p_trx_receipt_date DATE,
641 p_trx_line_type VARCHAR2,
642 p_trx_line_date DATE,
643 p_trx_business_category VARCHAR2,
644 p_line_intended_use VARCHAR2,
645 p_user_defined_fisc_class VARCHAR2,
646 p_line_amt NUMBER,
647 p_trx_line_quantity NUMBER,
648 p_unit_price NUMBER,
649 p_exempt_certificate_number VARCHAR2,
650 p_exempt_reason VARCHAR2,
651 p_cash_discount NUMBER,
652 p_volume_discount NUMBER,
653 p_trading_discount NUMBER,
654 p_transfer_charge NUMBER,
655 p_transportation_charge NUMBER,
656 p_insurance_charge NUMBER,
657 p_other_charge NUMBER,
658 p_product_id NUMBER,
659 p_product_fisc_classification VARCHAR2,
660 p_product_org_id NUMBER,
661 p_uom_code VARCHAR2,
662 p_product_type VARCHAR2,
663 p_product_code VARCHAR2,
664 p_product_category VARCHAR2,
665 p_trx_sic_code VARCHAR2,
666 p_fob_point VARCHAR2,
667 p_ship_to_party_id NUMBER,
668 p_ship_from_party_id NUMBER,
669 p_poa_party_id NUMBER,
670 p_poo_party_id NUMBER,
671 p_bill_to_party_id NUMBER,
672 p_bill_from_party_id NUMBER,
673 p_merchant_party_id NUMBER,
674 p_ship_to_party_site_id NUMBER,
675 p_ship_from_party_site_id NUMBER,
676 p_poa_party_site_id NUMBER,
677 p_poo_party_site_id NUMBER,
678 p_bill_to_party_site_id NUMBER,
679 p_bill_from_party_site_id NUMBER,
680 p_ship_to_location_id NUMBER,
681 p_ship_from_location_id NUMBER,
682 p_poa_location_id NUMBER,
683 p_poo_location_id NUMBER,
684 p_bill_to_location_id NUMBER,
685 p_bill_from_location_id NUMBER,
686 p_account_ccid NUMBER,
687 p_account_string VARCHAR2,
688 p_merchant_party_country VARCHAR2,
689 p_ref_doc_application_id NUMBER,
690 p_ref_doc_entity_code VARCHAR2,
691 p_ref_doc_event_class_code VARCHAR2,
692 p_ref_doc_trx_id NUMBER,
693 p_ref_doc_line_id NUMBER,
694 p_ref_doc_line_quantity NUMBER,
695 p_applied_from_application_id NUMBER,
696 p_applied_from_entity_code VARCHAR2,
697 p_applied_from_evt_class_code VARCHAR2, --reduced size p_applied_from_event_class_code
698 p_applied_from_trx_id NUMBER,
699 p_applied_from_line_id NUMBER,
700 p_adjusted_doc_application_id NUMBER,
701 p_adjusted_doc_entity_code VARCHAR2,
702 p_adj_doc_event_class_code VARCHAR2, --reduced size p_adjusted_doc_event_class_code
703 p_adjusted_doc_trx_id NUMBER,
704 p_adjusted_doc_line_id NUMBER,
705 p_adjusted_doc_number VARCHAR2,
706 p_adjusted_doc_date DATE,
707 p_applied_to_application_id NUMBER,
708 p_applied_to_entity_code VARCHAR2,
709 p_applied_to_event_class_code VARCHAR2,
710 p_applied_to_trx_id NUMBER,
711 p_applied_to_trx_line_id NUMBER,
712 p_trx_id_level2 NUMBER,
713 p_trx_id_level3 NUMBER,
714 p_trx_id_level4 NUMBER,
715 p_trx_id_level5 NUMBER,
716 p_trx_id_level6 NUMBER,
717 p_trx_line_number NUMBER,
718 p_trx_line_description VARCHAR2,
719 p_product_description VARCHAR2,
720 p_trx_waybill_number VARCHAR2,
721 p_trx_line_gl_date DATE,
722 p_merchant_party_name VARCHAR2,
723 p_merchant_party_doc_number VARCHAR2, --reduced size p_merchant_party_document_number
724 p_merchant_party_reference VARCHAR2,
725 p_merchant_party_taxpayer_id VARCHAR2,
726 p_merchant_pty_tax_reg_number VARCHAR2, --reduced size p_merchant_party_tax_reg_number
727 p_paying_party_id NUMBER,
728 p_own_hq_party_id NUMBER,
729 p_trading_hq_party_id NUMBER,
730 p_poi_party_id NUMBER,
731 p_pod_party_id NUMBER,
732 p_title_transfer_party_id NUMBER,
733 p_paying_party_site_id NUMBER,
734 p_own_hq_party_site_id NUMBER,
735 p_trading_hq_party_site_id NUMBER,
736 p_poi_party_site_id NUMBER,
737 p_pod_party_site_id NUMBER,
738 p_title_transfer_pty_site_id NUMBER, --reduced size p_title_transfer_party_site_id
739 p_paying_location_id NUMBER,
740 p_own_hq_location_id NUMBER,
741 p_trading_hq_location_id NUMBER,
742 p_poc_location_id NUMBER,
743 p_poi_location_id NUMBER,
744 p_pod_location_id NUMBER,
745 p_title_transfer_location_id NUMBER,
746 p_banking_tp_taxpayer_id VARCHAR2,
747 p_assessable_value NUMBER,
748 p_asset_flag VARCHAR2,
749 p_asset_number VARCHAR2,
750 p_asset_accum_depreciation NUMBER,
751 p_asset_type VARCHAR2,
752 p_asset_cost NUMBER,
753 p_ship_to_party_tax_prof_id NUMBER,
754 p_ship_from_party_tax_prof_id NUMBER,
755 p_poa_party_tax_prof_id NUMBER,
756 p_poo_party_tax_prof_id NUMBER,
757 p_paying_party_tax_prof_id NUMBER,
758 p_own_hq_party_tax_prof_id NUMBER,
759 p_trading_hq_pty_tax_prof_id NUMBER, --reduced size p_trading_hq_party_tax_prof_id
760 p_poi_party_tax_prof_id NUMBER,
761 p_pod_party_tax_prof_id NUMBER,
762 p_bill_to_party_tax_prof_id NUMBER,
763 p_bill_from_party_tax_prof_id NUMBER,
764 p_title_trans_pty_tax_prof_id NUMBER, --reduced size p_title_trans_party_tax_prof_id
765 p_ship_to_site_tax_prof_id NUMBER,
766 p_ship_from_site_tax_prof_id NUMBER,
767 p_poa_site_tax_prof_id NUMBER,
768 p_poo_site_tax_prof_id NUMBER,
769 p_paying_site_tax_prof_id NUMBER,
770 p_own_hq_site_tax_prof_id NUMBER,
771 p_trading_hq_site_tax_prof_id NUMBER,
772 p_poi_site_tax_prof_id NUMBER,
773 p_pod_site_tax_prof_id NUMBER,
774 p_bill_to_site_tax_prof_id NUMBER,
775 p_bill_from_site_tax_prof_id NUMBER,
776 p_title_trn_site_tax_prof_id NUMBER, --reduced size p_title_trans_site_tax_prof_id
777 p_merchant_party_tax_prof_id NUMBER,
778 p_line_amt_includes_tax_flag VARCHAR2,
779 p_historical_flag VARCHAR2,
780 p_tax_classification_code VARCHAR2,
781 p_ctrl_hdr_tx_appl_flag VARCHAR2,
782 p_ctrl_total_line_tx_amt NUMBER,
783 p_tax_regime_id NUMBER,
784 p_tax_regime_code VARCHAR2,
785 p_tax_id NUMBER,
786 p_tax VARCHAR2,
787 p_tax_status_id NUMBER,
788 p_tax_status_code VARCHAR2,
789 p_tax_rate_id NUMBER,
790 p_tax_rate_code VARCHAR2,
791 p_tax_rate NUMBER,
792 p_tax_line_amt NUMBER,
793 p_line_class VARCHAR2,
794 p_input_tax_classif_code VARCHAR2,
795 p_output_tax_classif_code VARCHAR2,
796 p_ref_doc_trx_level_type VARCHAR2,
797 p_applied_to_trx_level_type VARCHAR2,
798 p_applied_from_trx_level_type VARCHAR2,
799 p_adjusted_doc_trx_level_type VARCHAR2,
800 p_exemption_control_flag VARCHAR2,
801 p_exempt_reason_code VARCHAR2,
802 p_receivables_trx_type_id NUMBER,
803 p_object_version_number NUMBER,
804 p_created_by NUMBER,
805 p_creation_date DATE,
806 p_last_updated_by NUMBER,
807 p_last_update_date DATE,
808 p_last_update_login NUMBER) IS
809
810 l_return_status VARCHAR2(30);
811 l_msg_count NUMBER;
812 l_msg_data VARCHAR2(240);
813 p_error_buffer VARCHAR2(100);
814
815 BEGIN
816 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
817
818 IF (g_level_procedure >= g_current_runtime_level ) THEN
819 FND_LOG.STRING(g_level_procedure,
820 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Row.BEGIN',
821 'ZX_TRX_DETAIL: Update_Row (+)');
822 FND_LOG.STRING(g_level_procedure,
823 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Row',
824 'Update ZX_TRANSACTION_LINES (+)');
825 END IF;
826
827
828 UPDATE ZX_TRANSACTION_LINES
829 SET TRX_LINE_DESCRIPTION = p_trx_line_description,
830 TRX_LINE_TYPE = p_trx_line_type,
831 PRODUCT_ID = p_product_id,
832 PRODUCT_ORG_ID = p_product_org_id,
833 PRODUCT_CODE = p_product_code,
834 PRODUCT_TYPE = p_product_type,
835 PRODUCT_DESCRIPTION = p_product_description,
836 TRX_LINE_QUANTITY = p_trx_line_quantity,
837 UOM_CODE = p_uom_code,
838 UNIT_PRICE = p_unit_price,
839 LINE_AMT = p_line_amt,
840 PRODUCT_CATEGORY = p_product_category,
841 TRX_LINE_DATE = p_trx_line_date,
842 LINE_INTENDED_USE = p_line_intended_use,
843 USER_DEFINED_FISC_CLASS = p_user_defined_fisc_class,
844 TRX_BUSINESS_CATEGORY = p_trx_business_category,
845 ACCOUNT_CCID = p_account_ccid,
846 ACCOUNT_STRING = p_account_string,
847 TRX_LINE_GL_DATE = p_trx_line_gl_date,
848 LINE_LEVEL_ACTION = p_line_level_action,
849 MERCHANT_PARTY_ID = p_merchant_party_id,
850 MERCHANT_PARTY_COUNTRY = p_merchant_party_country,
851 BILL_FROM_PARTY_SITE_ID = p_bill_from_party_site_id,
852 BILL_TO_PARTY_SITE_ID = p_bill_to_party_site_id,
853 SHIP_FROM_PARTY_SITE_ID = p_ship_from_party_site_id,
854 SHIP_TO_PARTY_SITE_ID = p_ship_to_party_site_id,
855 SHIP_TO_PARTY_ID = p_ship_to_party_id,
856 SHIP_FROM_PARTY_ID = p_ship_from_party_id,
857 BILL_TO_PARTY_ID = p_bill_to_party_id,
858 BILL_FROM_PARTY_ID = p_bill_from_party_id,
859 SHIP_TO_LOCATION_ID = p_ship_to_location_id,
860 SHIP_FROM_LOCATION_ID = p_ship_from_location_id,
861 BILL_TO_LOCATION_ID = p_bill_to_location_id,
862 BILL_FROM_LOCATION_ID = p_bill_from_location_id,
863 POA_LOCATION_ID = p_poa_location_id,
864 POO_LOCATION_ID = p_poo_location_id,
865 PAYING_LOCATION_ID = p_paying_location_id,
866 OWN_HQ_LOCATION_ID = p_own_hq_location_id,
867 TRADING_HQ_LOCATION_ID = p_trading_hq_location_id,
868 POC_LOCATION_ID = p_poc_location_id,
869 POI_LOCATION_ID = p_poi_location_id,
870 POD_LOCATION_ID = p_pod_location_id,
871 POA_PARTY_ID = p_poa_party_id,
872 POO_PARTY_ID = p_poo_party_id,
873 POA_PARTY_SITE_ID = p_poa_party_site_id,
874 POO_PARTY_SITE_ID = p_poo_party_site_id,
875 REF_DOC_APPLICATION_ID = p_ref_doc_application_id,
876 REF_DOC_ENTITY_CODE = p_ref_doc_entity_code,
877 REF_DOC_EVENT_CLASS_CODE = p_ref_doc_event_class_code,
878 REF_DOC_TRX_ID = p_ref_doc_trx_id,
879 REF_DOC_LINE_ID = p_ref_doc_line_id,
880 REF_DOC_LINE_QUANTITY = p_ref_doc_line_quantity,
881 ADJUSTED_DOC_APPLICATION_ID = p_adjusted_doc_application_id,
882 ADJUSTED_DOC_ENTITY_CODE = p_adjusted_doc_entity_code,
883 ADJUSTED_DOC_EVENT_CLASS_CODE = p_adj_doc_event_class_code,
884 ADJUSTED_DOC_TRX_ID = p_adjusted_doc_trx_id,
885 ADJUSTED_DOC_LINE_ID = p_adjusted_doc_line_id,
886 ADJUSTED_DOC_DATE = p_adjusted_doc_date,
887 APPLIED_FROM_APPLICATION_ID = p_applied_from_application_id,
888 APPLIED_FROM_ENTITY_CODE = p_applied_from_entity_code,
889 APPLIED_FROM_EVENT_CLASS_CODE = p_applied_from_evt_class_code,
890 APPLIED_FROM_TRX_ID = p_applied_from_trx_id,
891 APPLIED_FROM_LINE_ID = p_applied_from_line_id,
892 APPLIED_TO_APPLICATION_ID = p_applied_to_application_id,
893 APPLIED_TO_ENTITY_CODE = p_applied_to_entity_code,
894 APPLIED_TO_EVENT_CLASS_CODE = p_applied_to_event_class_code,
895 APPLIED_TO_TRX_ID = p_applied_to_trx_id,
896 APPLIED_TO_TRX_LINE_ID = p_applied_to_trx_line_id,
897 PRODUCT_FISC_CLASSIFICATION = p_product_fisc_classification,
898 LINE_CLASS = p_line_class,
899 INPUT_TAX_CLASSIFICATION_CODE = p_input_tax_classif_code,
900 OUTPUT_TAX_CLASSIFICATION_CODE = p_output_tax_classif_code,
901 REF_DOC_TRX_LEVEL_TYPE = p_ref_doc_trx_level_type,
902 APPLIED_TO_TRX_LEVEL_TYPE = p_applied_to_trx_level_type,
903 APPLIED_FROM_TRX_LEVEL_TYPE = p_applied_from_trx_level_type,
904 ADJUSTED_DOC_TRX_LEVEL_TYPE = p_adjusted_doc_trx_level_type,
905 EXEMPTION_CONTROL_FLAG = p_exemption_control_flag,
906 EXEMPT_REASON_CODE = p_exempt_reason_code,
907 EXEMPT_CERTIFICATE_NUMBER = p_exempt_certificate_number,
908 EXEMPT_REASON = p_exempt_reason,
909 CASH_DISCOUNT = p_cash_discount,
910 VOLUME_DISCOUNT = p_volume_discount,
911 TRADING_DISCOUNT = p_trading_discount,
912 TRANSFER_CHARGE = p_transfer_charge,
913 TRANSPORTATION_CHARGE = p_transportation_charge,
914 INSURANCE_CHARGE = p_insurance_charge,
915 OTHER_CHARGE = p_other_charge,
916 RECEIVABLES_TRX_TYPE_ID = p_receivables_trx_type_id,
917 CTRL_HDR_TX_APPL_FLAG = p_ctrl_hdr_tx_appl_flag,
918 CTRL_TOTAL_LINE_TX_AMT = p_ctrl_total_line_tx_amt,
919 LINE_AMT_INCLUDES_TAX_FLAG = p_line_amt_includes_tax_flag,
920 HISTORICAL_FLAG = p_historical_flag,
921 OBJECT_VERSION_NUMBER = NVL(p_object_version_number, OBJECT_VERSION_NUMBER + 1),
922 CREATED_BY = p_created_by,
923 CREATION_DATE = p_creation_date,
924 LAST_UPDATED_BY = p_last_updated_by,
925 LAST_UPDATE_DATE = p_last_update_date,
926 LAST_UPDATE_LOGIN = p_last_update_login
927 WHERE APPLICATION_ID = p_application_id
928 AND ENTITY_CODE = p_entity_code
929 AND EVENT_CLASS_CODE = p_event_class_code
930 AND TRX_ID = p_trx_id
931 AND TRX_LEVEL_TYPE = p_trx_level_type
932 AND TRX_LINE_ID = p_trx_line_id;
933
934 IF (g_level_procedure >= g_current_runtime_level ) THEN
935 FND_LOG.STRING(g_level_procedure,
936 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Row',
937 'Update ZX_TRANSACTION_LINES (-)');
938 FND_LOG.STRING(g_level_procedure,
939 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Row.END',
940 'ZX_TRX_DETAIL: Update_Row (-)');
941 END IF;
942
943 END Update_row;
944
945 PROCEDURE Delete_row
946 (p_Rowid VARCHAR2,
947 p_internal_organization_id NUMBER,
948 p_application_id NUMBER,
949 p_entity_code VARCHAR2,
950 p_event_class_code VARCHAR2,
951 p_event_type_code VARCHAR2,
952 p_trx_id NUMBER,
953 p_trx_level_type VARCHAR2,
954 p_trx_line_id NUMBER
955 ) IS
956
957 l_return_status VARCHAR2(30);
958 l_msg_count NUMBER;
959 l_msg_data VARCHAR2(240);
960 l_error_buffer VARCHAR2(100);
961 l_transaction_line_rec ZX_API_PUB.transaction_line_rec_type;
962
963 BEGIN
964 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
965
966 IF (g_level_procedure >= g_current_runtime_level ) THEN
967 FND_LOG.STRING(g_level_procedure,
968 'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row.BEGIN',
969 'ZX_TRX_DETAIL: Delete_Row (+)');
970 END IF;
971
972 IF (g_level_procedure >= g_current_runtime_level ) THEN
973 FND_LOG.STRING(g_level_procedure,
974 'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row',
975 'Deleting ZX_TRANSACTION_LINES');
976 END IF;
977
978 DELETE ZX_TRANSACTION_LINES
979 WHERE APPLICATION_ID = p_application_id
980 AND ENTITY_CODE = p_entity_code
981 AND EVENT_CLASS_CODE = p_event_class_code
982 AND TRX_ID = p_trx_id
983 AND TRX_LEVEL_TYPE = p_trx_level_type
984 AND TRX_LINE_ID = p_trx_line_id;
985
986 IF (g_level_procedure >= g_current_runtime_level ) THEN
987 FND_LOG.STRING(g_level_procedure,
988 'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row',
989 'Deleted ZX_TRANSACTION_LINES');
990 FND_LOG.STRING(g_level_procedure,
991 'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row',
992 'Calling ZX_API_PUB.Mark_tax_lines_deleted');
993 END IF;
994
995 l_transaction_line_rec.INTERNAL_ORGANIZATION_ID := p_internal_organization_id;
996 l_transaction_line_rec.APPLICATION_ID := p_application_id;
997 l_transaction_line_rec.ENTITY_CODE := p_entity_code;
998 l_transaction_line_rec.EVENT_CLASS_CODE := p_event_class_code;
999 l_transaction_line_rec.EVENT_TYPE_CODE := p_event_type_code;
1000 l_transaction_line_rec.TRX_ID := p_trx_id;
1001 l_transaction_line_rec.TRX_LEVEL_TYPE := p_trx_level_type;
1002 l_transaction_line_rec.TRX_LINE_ID := p_trx_line_id;
1003
1004 ZX_API_PUB.Mark_tax_lines_deleted
1005 (p_api_version => 1.0,
1006 p_init_msg_list => NULL,
1007 p_commit => NULL,
1008 p_validation_level => NULL,
1009 x_return_status => l_return_status,
1010 x_msg_count => l_msg_count,
1011 x_msg_data => l_msg_data,
1012 p_transaction_line_rec => l_transaction_line_rec);
1013
1014
1015 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1016 RETURN;
1017 END IF;
1018
1019 IF (g_level_procedure >= g_current_runtime_level ) THEN
1020 FND_LOG.STRING(g_level_procedure,
1021 'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row.END',
1022 'ZX_TRX_DETAIL: Delete_Row (-)');
1023 END IF;
1024
1025 END Delete_row;
1026
1027 PROCEDURE Lock_row
1028 (p_Rowid VARCHAR2,
1029 p_internal_organization_id NUMBER,
1030 p_internal_org_location_id NUMBER,
1031 p_application_id NUMBER,
1032 p_entity_code VARCHAR2,
1033 p_event_class_code VARCHAR2,
1034 p_event_type_code VARCHAR2,
1035 p_trx_id NUMBER,
1036 p_trx_date DATE,
1037 p_trx_doc_revision VARCHAR2,
1038 p_ledger_id NUMBER,
1039 p_trx_currency_code VARCHAR2,
1040 p_currency_conversion_date DATE,
1041 p_currency_conversion_rate NUMBER,
1042 p_currency_conversion_type VARCHAR2,
1043 p_minimum_accountable_unit NUMBER,
1044 p_precision NUMBER,
1045 p_legal_entity_id NUMBER,
1046 p_rounding_ship_to_party_id NUMBER,
1047 p_rounding_ship_from_party_id NUMBER,
1048 p_rounding_bill_to_party_id NUMBER,
1049 p_rounding_bill_from_party_id NUMBER,
1050 p_rndg_ship_to_party_site_id NUMBER,
1051 p_rndg_ship_from_pty_site_id NUMBER, --reduced size p_rndg_ship_from_party_site_id
1052 p_rndg_bill_to_party_site_id NUMBER,
1053 p_rndg_bill_from_pty_site_id NUMBER, --reduced size p_rndg_bill_from_party_site_id
1054 p_establishment_id NUMBER,
1055 p_related_doc_application_id NUMBER,
1056 p_related_doc_entity_code VARCHAR2,
1057 p_related_doc_evt_class_code VARCHAR2, --reduced size p_related_doc_event_class_code
1058 p_related_doc_trx_id NUMBER,
1059 p_related_doc_number VARCHAR2,
1060 p_related_doc_date DATE,
1061 p_default_taxation_country VARCHAR2,
1062 p_quote_flag VARCHAR2,
1063 p_trx_number VARCHAR2,
1064 p_trx_description VARCHAR2,
1065 p_trx_communicated_date DATE,
1066 p_batch_source_id NUMBER,
1067 p_batch_source_name VARCHAR2,
1068 --p_doc_seq_id NUMBER,
1069 --p_doc_seq_name VARCHAR2,
1070 --p_doc_seq_value VARCHAR2,
1071 p_trx_due_date DATE,
1072 p_trx_type_description VARCHAR2,
1073 p_billing_trad_partner_name VARCHAR2, --reduced size p_billing_trading_partner_name
1074 p_billing_trad_partner_number VARCHAR2, --reduced size p_billing_trading_partner_number
1075 p_billing_tp_tax_report_flg VARCHAR2, --reduced size p_Billing_Tp_Tax_Reporting_Flag
1076 p_billing_tp_taxpayer_id VARCHAR2,
1077 p_document_sub_type VARCHAR2,
1078 p_supplier_tax_invoice_number VARCHAR2,
1079 p_supplier_tax_invoice_date DATE,--
1080 p_supplier_exchange_rate NUMBER,
1081 p_tax_invoice_date DATE,
1082 p_tax_invoice_number VARCHAR2,
1083 p_tax_event_class_code VARCHAR2,
1084 p_tax_event_type_code VARCHAR2,
1085 p_doc_event_status VARCHAR2,
1086 p_rdng_ship_to_pty_tx_prof_id NUMBER,
1087 p_rdng_ship_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_prof_id
1088 p_rdng_bill_to_pty_tx_prof_id NUMBER,
1089 p_rdng_bill_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_prof_id
1090 p_rdng_ship_to_pty_tx_p_st_id NUMBER,
1091 p_rdng_ship_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_p_st_id
1092 p_rdng_bill_to_pty_tx_p_st_id NUMBER,
1093 p_rdng_bill_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_p_st_id
1094 p_trx_level_type VARCHAR2,
1095 p_trx_line_id NUMBER,
1096 p_line_level_action VARCHAR2,
1097 p_trx_shipping_date DATE,
1098 p_trx_receipt_date DATE,
1099 p_trx_line_type VARCHAR2,
1100 p_trx_line_date DATE,
1101 p_trx_business_category VARCHAR2,
1102 p_line_intended_use VARCHAR2,
1103 p_user_defined_fisc_class VARCHAR2,
1104 p_line_amt NUMBER,
1105 p_trx_line_quantity NUMBER,
1106 p_unit_price NUMBER,
1107 p_exempt_certificate_number VARCHAR2,
1108 p_exempt_reason VARCHAR2,
1109 p_cash_discount NUMBER,
1110 p_volume_discount NUMBER,
1111 p_trading_discount NUMBER,
1112 p_transfer_charge NUMBER,
1113 p_transportation_charge NUMBER,
1114 p_insurance_charge NUMBER,
1115 p_other_charge NUMBER,
1116 p_product_id NUMBER,
1117 p_product_fisc_classification VARCHAR2,
1118 p_product_org_id NUMBER,
1119 p_uom_code VARCHAR2,
1120 p_product_type VARCHAR2,
1121 p_product_code VARCHAR2,
1122 p_product_category VARCHAR2,
1123 p_trx_sic_code VARCHAR2,
1124 p_fob_point VARCHAR2,
1125 p_ship_to_party_id NUMBER,
1126 p_ship_from_party_id NUMBER,
1127 p_poa_party_id NUMBER,
1128 p_poo_party_id NUMBER,
1129 p_bill_to_party_id NUMBER,
1130 p_bill_from_party_id NUMBER,
1131 p_merchant_party_id NUMBER,
1132 p_ship_to_party_site_id NUMBER,
1133 p_ship_from_party_site_id NUMBER,
1134 p_poa_party_site_id NUMBER,
1135 p_poo_party_site_id NUMBER,
1136 p_bill_to_party_site_id NUMBER,
1137 p_bill_from_party_site_id NUMBER,
1138 p_ship_to_location_id NUMBER,
1139 p_ship_from_location_id NUMBER,
1140 p_poa_location_id NUMBER,
1141 p_poo_location_id NUMBER,
1142 p_bill_to_location_id NUMBER,
1143 p_bill_from_location_id NUMBER,
1144 p_account_ccid NUMBER,
1145 p_account_string VARCHAR2,
1146 p_merchant_party_country VARCHAR2,
1147 p_ref_doc_application_id NUMBER,
1148 p_ref_doc_entity_code VARCHAR2,
1149 p_ref_doc_event_class_code VARCHAR2,
1150 p_ref_doc_trx_id NUMBER,
1151 p_ref_doc_line_id NUMBER,
1152 p_ref_doc_line_quantity NUMBER,
1153 p_applied_from_application_id NUMBER,
1154 p_applied_from_entity_code VARCHAR2,
1155 p_applied_from_evt_class_code VARCHAR2, --reduced size p_applied_from_event_class_code
1156 p_applied_from_trx_id NUMBER,
1157 p_applied_from_line_id NUMBER,
1158 p_adjusted_doc_application_id NUMBER,
1159 p_adjusted_doc_entity_code VARCHAR2,
1160 p_adj_doc_event_class_code VARCHAR2, --reduced size p_adjusted_doc_event_class_code
1161 p_adjusted_doc_trx_id NUMBER,
1162 p_adjusted_doc_line_id NUMBER,
1163 p_adjusted_doc_number VARCHAR2,
1164 p_adjusted_doc_date DATE,
1165 p_applied_to_application_id NUMBER,
1166 p_applied_to_entity_code VARCHAR2,
1167 p_applied_to_event_class_code VARCHAR2,
1168 p_applied_to_trx_id NUMBER,
1169 p_applied_to_trx_line_id NUMBER,
1170 p_trx_id_level2 NUMBER,
1171 p_trx_id_level3 NUMBER,
1172 p_trx_id_level4 NUMBER,
1173 p_trx_id_level5 NUMBER,
1174 p_trx_id_level6 NUMBER,
1175 p_trx_line_number NUMBER,
1176 p_trx_line_description VARCHAR2,
1177 p_product_description VARCHAR2,
1178 p_trx_waybill_number VARCHAR2,
1179 p_trx_line_gl_date DATE,
1180 p_merchant_party_name VARCHAR2,
1181 p_merchant_party_doc_number VARCHAR2, --reduced size p_merchant_party_document_number
1182 p_merchant_party_reference VARCHAR2,
1183 p_merchant_party_taxpayer_id VARCHAR2,
1184 p_merchant_pty_tax_reg_number VARCHAR2, --reduced size p_merchant_party_tax_reg_number
1185 p_paying_party_id NUMBER,
1186 p_own_hq_party_id NUMBER,
1187 p_trading_hq_party_id NUMBER,
1188 p_poi_party_id NUMBER,
1189 p_pod_party_id NUMBER,
1190 p_title_transfer_party_id NUMBER,
1191 p_paying_party_site_id NUMBER,
1192 p_own_hq_party_site_id NUMBER,
1193 p_trading_hq_party_site_id NUMBER,
1194 p_poi_party_site_id NUMBER,
1195 p_pod_party_site_id NUMBER,
1196 p_title_transfer_pty_site_id NUMBER, --reduced size p_title_transfer_party_site_id
1197 p_paying_location_id NUMBER,
1198 p_own_hq_location_id NUMBER,
1199 p_trading_hq_location_id NUMBER,
1200 p_poc_location_id NUMBER,
1201 p_poi_location_id NUMBER,
1202 p_pod_location_id NUMBER,
1203 p_title_transfer_location_id NUMBER,
1204 p_banking_tp_taxpayer_id VARCHAR2,
1205 p_assessable_value NUMBER,
1206 p_asset_flag VARCHAR2,
1207 p_asset_number VARCHAR2,
1208 p_asset_accum_depreciation NUMBER,
1209 p_asset_type VARCHAR2,
1210 p_asset_cost NUMBER,
1211 p_ship_to_party_tax_prof_id NUMBER,
1212 p_ship_from_party_tax_prof_id NUMBER,
1213 p_poa_party_tax_prof_id NUMBER,
1214 p_poo_party_tax_prof_id NUMBER,
1215 p_paying_party_tax_prof_id NUMBER,
1216 p_own_hq_party_tax_prof_id NUMBER,
1217 p_trading_hq_pty_tax_prof_id NUMBER, --reduced size p_trading_hq_party_tax_prof_id
1218 p_poi_party_tax_prof_id NUMBER,
1219 p_pod_party_tax_prof_id NUMBER,
1220 p_bill_to_party_tax_prof_id NUMBER,
1221 p_bill_from_party_tax_prof_id NUMBER,
1222 p_title_trans_pty_tax_prof_id NUMBER, --reduced size p_title_trans_party_tax_prof_id
1223 p_ship_to_site_tax_prof_id NUMBER,
1224 p_ship_from_site_tax_prof_id NUMBER,
1225 p_poa_site_tax_prof_id NUMBER,
1226 p_poo_site_tax_prof_id NUMBER,
1227 p_paying_site_tax_prof_id NUMBER,
1228 p_own_hq_site_tax_prof_id NUMBER,
1229 p_trading_hq_site_tax_prof_id NUMBER,
1230 p_poi_site_tax_prof_id NUMBER,
1231 p_pod_site_tax_prof_id NUMBER,
1232 p_bill_to_site_tax_prof_id NUMBER,
1233 p_bill_from_site_tax_prof_id NUMBER,
1234 p_title_trn_site_tax_prof_id NUMBER, --reduced size p_title_trans_site_tax_prof_id
1235 p_merchant_party_tax_prof_id NUMBER,
1236 p_line_amt_includes_tax_flag VARCHAR2,
1237 p_historical_flag VARCHAR2,
1238 p_tax_classification_code VARCHAR2,
1239 p_ctrl_hdr_tx_appl_flag VARCHAR2,
1240 p_ctrl_total_line_tx_amt NUMBER,
1241 p_tax_regime_id NUMBER,
1242 p_tax_regime_code VARCHAR2,
1243 p_tax_id NUMBER,
1244 p_tax VARCHAR2,
1245 p_tax_status_id NUMBER,
1246 p_tax_status_code VARCHAR2,
1247 p_tax_rate_id NUMBER,
1248 p_tax_rate_code VARCHAR2,
1249 p_tax_rate NUMBER,
1250 p_tax_line_amt NUMBER,
1251 p_line_class VARCHAR2,
1252 p_input_tax_classif_code VARCHAR2,
1253 p_output_tax_classif_code VARCHAR2,
1254 p_ref_doc_trx_level_type VARCHAR2,
1255 p_applied_to_trx_level_type VARCHAR2,
1256 p_applied_from_trx_level_type VARCHAR2,
1257 p_adjusted_doc_trx_level_type VARCHAR2,
1258 p_exemption_control_flag VARCHAR2,
1259 p_exempt_reason_code VARCHAR2,
1260 p_receivables_trx_type_id NUMBER,
1261 p_object_version_number NUMBER,
1262 p_created_by NUMBER,
1263 p_creation_date DATE,
1264 p_last_updated_by NUMBER,
1265 p_last_update_date DATE,
1266 p_last_update_login NUMBER) IS
1267
1268 CURSOR C IS
1269 SELECT APPLICATION_ID,
1270 ENTITY_CODE,
1271 EVENT_CLASS_CODE,
1272 TRX_LINE_ID,
1273 TRX_LINE_NUMBER,
1274 TRX_ID,
1275 TRX_LEVEL_TYPE,
1276 TRX_LINE_TYPE,
1277 TRX_LINE_DATE,
1278 TRX_BUSINESS_CATEGORY,
1279 LINE_INTENDED_USE,
1280 USER_DEFINED_FISC_CLASS,
1281 LINE_AMT_INCLUDES_TAX_FLAG,
1282 LINE_AMT,
1283 TRX_LINE_QUANTITY,
1284 UNIT_PRICE,
1285 PRODUCT_ID,
1286 PRODUCT_FISC_CLASSIFICATION,
1287 PRODUCT_ORG_ID,
1288 UOM_CODE,
1289 PRODUCT_TYPE,
1290 PRODUCT_CODE,
1291 PRODUCT_CATEGORY,
1292 MERCHANT_PARTY_ID,
1293 ACCOUNT_CCID,
1294 ACCOUNT_STRING,
1295 REF_DOC_LINE_ID,
1296 REF_DOC_LINE_QUANTITY,
1297 REF_DOC_APPLICATION_ID,
1298 REF_DOC_ENTITY_CODE,
1299 REF_DOC_EVENT_CLASS_CODE,
1300 REF_DOC_TRX_ID,
1301 APPLIED_FROM_LINE_ID,
1302 APPLIED_FROM_APPLICATION_ID,
1303 APPLIED_FROM_ENTITY_CODE,
1304 APPLIED_FROM_EVENT_CLASS_CODE,
1305 APPLIED_FROM_TRX_ID,
1306 ADJUSTED_DOC_LINE_ID,
1307 ADJUSTED_DOC_DATE,
1308 ADJUSTED_DOC_APPLICATION_ID,
1309 ADJUSTED_DOC_ENTITY_CODE,
1310 ADJUSTED_DOC_EVENT_CLASS_CODE,
1311 ADJUSTED_DOC_TRX_ID,
1312 TRX_LINE_DESCRIPTION,
1313 PRODUCT_DESCRIPTION,
1314 TRX_LINE_GL_DATE,
1315 LINE_LEVEL_ACTION,
1316 HISTORICAL_FLAG,
1317 BILL_FROM_PARTY_SITE_ID,
1318 BILL_TO_PARTY_SITE_ID,
1319 SHIP_FROM_PARTY_SITE_ID,
1320 SHIP_TO_PARTY_SITE_ID,
1321 SHIP_TO_PARTY_ID,
1322 SHIP_FROM_PARTY_ID,
1323 BILL_TO_PARTY_ID,
1324 BILL_FROM_PARTY_ID,
1325 SHIP_TO_LOCATION_ID,
1326 SHIP_FROM_LOCATION_ID,
1327 BILL_TO_LOCATION_ID,
1328 BILL_FROM_LOCATION_ID,
1329 POA_LOCATION_ID,
1330 POO_LOCATION_ID,
1331 PAYING_LOCATION_ID,
1332 OWN_HQ_LOCATION_ID,
1333 TRADING_HQ_LOCATION_ID,
1334 POC_LOCATION_ID,
1335 POI_LOCATION_ID,
1336 POD_LOCATION_ID,
1337 TAX_REGIME_ID,
1338 TAX_REGIME_CODE,
1339 TAX_ID,
1340 TAX,
1341 TAX_STATUS_ID,
1342 TAX_STATUS_CODE,
1343 TAX_RATE_ID,
1344 TAX_RATE_CODE,
1345 TAX_RATE,
1346 TAX_LINE_AMT,
1347 LINE_CLASS,
1348 INPUT_TAX_CLASSIFICATION_CODE,
1349 OUTPUT_TAX_CLASSIFICATION_CODE,
1350 REF_DOC_TRX_LEVEL_TYPE,
1351 APPLIED_TO_TRX_LEVEL_TYPE,
1352 APPLIED_FROM_TRX_LEVEL_TYPE,
1353 ADJUSTED_DOC_TRX_LEVEL_TYPE,
1354 EXEMPTION_CONTROL_FLAG,
1355 EXEMPT_REASON_CODE,
1356 EXEMPT_CERTIFICATE_NUMBER,
1357 EXEMPT_REASON,
1358 CASH_DISCOUNT,
1359 VOLUME_DISCOUNT,
1360 TRADING_DISCOUNT,
1361 TRANSFER_CHARGE,
1362 TRANSPORTATION_CHARGE,
1363 INSURANCE_CHARGE,
1364 OTHER_CHARGE,
1365 RECEIVABLES_TRX_TYPE_ID,
1366 CTRL_HDR_TX_APPL_FLAG,
1367 CTRL_TOTAL_LINE_TX_AMT,
1368 OBJECT_VERSION_NUMBER,
1369 CREATED_BY,
1370 CREATION_DATE,
1371 LAST_UPDATED_BY,
1372 LAST_UPDATE_DATE,
1373 LAST_UPDATE_LOGIN
1374 FROM ZX_TRANSACTION_LINES
1375 WHERE APPLICATION_ID = p_application_id
1376 AND ENTITY_CODE = p_entity_code
1377 AND EVENT_CLASS_CODE = p_event_class_code
1378 AND TRX_LINE_ID = p_trx_line_id
1379 AND TRX_LEVEL_TYPE = p_trx_level_type
1380 AND TRX_ID = p_trx_id
1381 FOR UPDATE OF APPLICATION_ID,
1382 ENTITY_CODE,
1383 EVENT_CLASS_CODE,
1384 TRX_LINE_ID,
1385 TRX_LEVEL_TYPE,
1386 TRX_ID
1387 NOWAIT;
1388
1389 Recinfo C%ROWTYPE;
1390 debug_info VARCHAR2(100);
1391 p_error_buffer VARCHAR2(100);
1392
1393 BEGIN
1394
1395 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1396 IF (g_level_procedure >= g_current_runtime_level ) THEN
1397 FND_LOG.STRING(g_level_procedure,
1398 'ZX.PLSQL.ZX_TRX_DETAIL.Lock_row.BEGIN',
1399 'ZX_TRX_DETAIL: Lock_row (+)');
1400 END IF;
1401
1402 debug_info := 'Open cursor C';
1403 OPEN C;
1404 debug_info := 'Fetch cursor C';
1405 FETCH C INTO Recinfo;
1406
1407 IF (C%NOTFOUND) THEN
1408 debug_info := 'Close cursor C - DATA NOTFOUND';
1409 CLOSE C;
1410 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1411 APP_EXCEPTION.Raise_Exception;
1412 END IF;
1413
1414 debug_info := 'Close cursor C';
1415 CLOSE C;
1416
1417 IF ((Recinfo.APPLICATION_ID = p_APPLICATION_ID) AND
1418 (Recinfo.ENTITY_CODE = p_ENTITY_CODE) AND
1419 (Recinfo.EVENT_CLASS_CODE = p_EVENT_CLASS_CODE) AND
1420 (Recinfo.TRX_LINE_ID = p_TRX_LINE_ID) AND
1421 (Recinfo.TRX_LINE_NUMBER = p_TRX_LINE_NUMBER) AND
1422 (Recinfo.TRX_ID = p_TRX_ID) AND
1423 (Recinfo.TRX_LEVEL_TYPE = p_TRX_LEVEL_TYPE) AND
1424 ((Recinfo.TRX_LINE_TYPE = p_TRX_LINE_TYPE) OR
1425 ((Recinfo.TRX_LINE_TYPE IS NULL) AND
1426 (p_TRX_LINE_TYPE IS NULL))) AND
1427 ((Recinfo.TRX_LINE_DATE = p_TRX_LINE_DATE) OR
1428 ((Recinfo.TRX_LINE_DATE IS NULL) AND
1429 (p_TRX_LINE_DATE IS NULL))) AND
1430 ((Recinfo.TRX_BUSINESS_CATEGORY = p_TRX_BUSINESS_CATEGORY) OR
1431 ((Recinfo.TRX_BUSINESS_CATEGORY IS NULL) AND
1432 (p_TRX_BUSINESS_CATEGORY IS NULL))) AND
1433 ((Recinfo.LINE_INTENDED_USE = p_LINE_INTENDED_USE) OR
1434 ((Recinfo.LINE_INTENDED_USE IS NULL) AND
1435 (p_LINE_INTENDED_USE IS NULL))) AND
1436 ((Recinfo.USER_DEFINED_FISC_CLASS = p_USER_DEFINED_FISC_CLASS) OR
1437 ((Recinfo.USER_DEFINED_FISC_CLASS IS NULL) AND
1438 (p_USER_DEFINED_FISC_CLASS IS NULL))) AND
1439 ((Recinfo.LINE_AMT_INCLUDES_TAX_FLAG = p_LINE_AMT_INCLUDES_TAX_FLAG) OR
1440 ((Recinfo.LINE_AMT_INCLUDES_TAX_FLAG IS NULL) AND
1441 (p_LINE_AMT_INCLUDES_TAX_FLAG IS NULL))) AND
1442 ((Recinfo.LINE_AMT = p_LINE_AMT) OR
1443 ((Recinfo.LINE_AMT IS NULL) AND
1444 (p_LINE_AMT IS NULL))) AND
1445 ((Recinfo.TRX_LINE_QUANTITY = p_TRX_LINE_QUANTITY) OR
1446 ((Recinfo.TRX_LINE_QUANTITY IS NULL) AND
1447 (p_TRX_LINE_QUANTITY IS NULL))) AND
1448 ((Recinfo.UNIT_PRICE = p_UNIT_PRICE) OR
1449 ((Recinfo.UNIT_PRICE IS NULL) AND
1450 (p_UNIT_PRICE IS NULL))) AND
1451 ((Recinfo.PRODUCT_ID = p_PRODUCT_ID) OR
1452 ((Recinfo.PRODUCT_ID IS NULL) AND
1453 (p_PRODUCT_ID IS NULL))) AND
1454 ((Recinfo.PRODUCT_FISC_CLASSIFICATION = p_PRODUCT_FISC_CLASSIFICATION) OR
1455 ((Recinfo.PRODUCT_FISC_CLASSIFICATION IS NULL) AND
1456 (p_PRODUCT_FISC_CLASSIFICATION IS NULL))) AND
1457 ((Recinfo.PRODUCT_ORG_ID = p_PRODUCT_ORG_ID) OR
1458 ((Recinfo.PRODUCT_ORG_ID IS NULL) AND
1459 (p_PRODUCT_ORG_ID IS NULL))) AND
1460 ((Recinfo.UOM_CODE = p_UOM_CODE) OR
1461 ((Recinfo.UOM_CODE IS NULL) AND
1462 (p_UOM_CODE IS NULL))) AND
1463 ((Recinfo.PRODUCT_TYPE = p_PRODUCT_TYPE) OR
1464 ((Recinfo.PRODUCT_TYPE IS NULL) AND
1465 (p_PRODUCT_TYPE IS NULL))) AND
1466 ((Recinfo.PRODUCT_CODE = p_PRODUCT_CODE) OR
1467 ((Recinfo.PRODUCT_CODE IS NULL) AND
1468 (p_PRODUCT_CODE IS NULL))) AND
1469 ((Recinfo.PRODUCT_CATEGORY = p_PRODUCT_CATEGORY) OR
1470 ((Recinfo.PRODUCT_CATEGORY IS NULL) AND
1471 (p_PRODUCT_CATEGORY IS NULL))) AND
1472 ((Recinfo.MERCHANT_PARTY_ID = p_MERCHANT_PARTY_ID) OR
1473 ((Recinfo.MERCHANT_PARTY_ID IS NULL) AND
1474 (p_MERCHANT_PARTY_ID IS NULL))) AND
1475 ((Recinfo.ACCOUNT_CCID = p_ACCOUNT_CCID) OR
1476 ((Recinfo.ACCOUNT_CCID IS NULL) AND
1477 (p_ACCOUNT_CCID IS NULL))) AND
1478 ((Recinfo.ACCOUNT_STRING = p_ACCOUNT_STRING) OR
1479 ((Recinfo.ACCOUNT_STRING IS NULL) AND
1480 (p_ACCOUNT_STRING IS NULL))) AND
1481 ((Recinfo.REF_DOC_LINE_ID = p_REF_DOC_LINE_ID) OR
1482 ((Recinfo.REF_DOC_LINE_ID IS NULL) AND
1483 (p_REF_DOC_LINE_ID IS NULL))) AND
1484 ((Recinfo.REF_DOC_LINE_QUANTITY = p_REF_DOC_LINE_QUANTITY) OR
1485 ((Recinfo.REF_DOC_LINE_QUANTITY IS NULL) AND
1486 (p_REF_DOC_LINE_QUANTITY IS NULL))) AND
1487 ((Recinfo.REF_DOC_APPLICATION_ID = p_REF_DOC_APPLICATION_ID) OR
1488 ((Recinfo.REF_DOC_APPLICATION_ID IS NULL) AND
1489 (p_REF_DOC_APPLICATION_ID IS NULL))) AND
1490 ((Recinfo.REF_DOC_ENTITY_CODE = p_REF_DOC_ENTITY_CODE) OR
1491 ((Recinfo.REF_DOC_ENTITY_CODE IS NULL) AND
1492 (p_REF_DOC_ENTITY_CODE IS NULL))) AND
1493 ((Recinfo.REF_DOC_EVENT_CLASS_CODE = p_REF_DOC_EVENT_CLASS_CODE) OR
1494 ((Recinfo.REF_DOC_EVENT_CLASS_CODE IS NULL) AND
1495 (p_REF_DOC_EVENT_CLASS_CODE IS NULL))) AND
1496 ((Recinfo.REF_DOC_TRX_ID = p_REF_DOC_TRX_ID) OR
1497 ((Recinfo.REF_DOC_TRX_ID IS NULL) AND
1498 (p_REF_DOC_TRX_ID IS NULL))) AND
1499 ((Recinfo.APPLIED_FROM_LINE_ID = p_APPLIED_FROM_LINE_ID) OR
1500 ((Recinfo.APPLIED_FROM_LINE_ID IS NULL) AND
1501 (p_APPLIED_FROM_LINE_ID IS NULL))) AND
1502 ((Recinfo.APPLIED_FROM_APPLICATION_ID = p_APPLIED_FROM_APPLICATION_ID) OR
1503 ((Recinfo.APPLIED_FROM_APPLICATION_ID IS NULL) AND
1504 (p_APPLIED_FROM_APPLICATION_ID IS NULL))) AND
1505 ((Recinfo.APPLIED_FROM_ENTITY_CODE = p_APPLIED_FROM_ENTITY_CODE) OR
1506 ((Recinfo.APPLIED_FROM_ENTITY_CODE IS NULL) AND
1507 (p_APPLIED_FROM_ENTITY_CODE IS NULL))) AND
1508 ((Recinfo.APPLIED_FROM_EVENT_CLASS_CODE = p_APPLIED_FROM_EVT_CLASS_CODE) OR
1509 ((Recinfo.APPLIED_FROM_EVENT_CLASS_CODE IS NULL) AND
1510 (p_APPLIED_FROM_EVT_CLASS_CODE IS NULL))) AND
1511 ((Recinfo.APPLIED_FROM_TRX_ID = p_APPLIED_FROM_TRX_ID) OR
1512 ((Recinfo.APPLIED_FROM_TRX_ID IS NULL) AND
1513 (p_APPLIED_FROM_TRX_ID IS NULL))) AND
1514 ((Recinfo.ADJUSTED_DOC_LINE_ID = p_ADJUSTED_DOC_LINE_ID) OR
1515 ((Recinfo.ADJUSTED_DOC_LINE_ID IS NULL) AND
1516 (p_ADJUSTED_DOC_LINE_ID IS NULL))) AND
1517 ((Recinfo.ADJUSTED_DOC_DATE = p_ADJUSTED_DOC_DATE) OR
1518 ((Recinfo.ADJUSTED_DOC_DATE IS NULL) AND
1519 (p_ADJUSTED_DOC_DATE IS NULL))) AND
1520 ((Recinfo.ADJUSTED_DOC_APPLICATION_ID = p_ADJUSTED_DOC_APPLICATION_ID) OR
1521 ((Recinfo.ADJUSTED_DOC_APPLICATION_ID IS NULL) AND
1522 (p_ADJUSTED_DOC_APPLICATION_ID IS NULL))) AND
1523 ((Recinfo.ADJUSTED_DOC_ENTITY_CODE = p_ADJUSTED_DOC_ENTITY_CODE) OR
1524 ((Recinfo.ADJUSTED_DOC_ENTITY_CODE IS NULL) AND
1525 (p_ADJUSTED_DOC_ENTITY_CODE IS NULL))) AND
1526 ((Recinfo.ADJUSTED_DOC_EVENT_CLASS_CODE = p_ADJ_DOC_EVENT_CLASS_CODE) OR
1527 ((Recinfo.ADJUSTED_DOC_EVENT_CLASS_CODE IS NULL) AND
1528 (p_ADJ_DOC_EVENT_CLASS_CODE IS NULL))) AND
1529 ((Recinfo.ADJUSTED_DOC_TRX_ID = p_ADJUSTED_DOC_TRX_ID) OR
1530 ((Recinfo.ADJUSTED_DOC_TRX_ID IS NULL) AND
1531 (p_ADJUSTED_DOC_TRX_ID IS NULL))) AND
1532 ((Recinfo.TRX_LINE_DESCRIPTION = p_TRX_LINE_DESCRIPTION) OR
1533 ((Recinfo.TRX_LINE_DESCRIPTION IS NULL) AND
1534 (p_TRX_LINE_DESCRIPTION IS NULL))) AND
1535 ((Recinfo.PRODUCT_DESCRIPTION = p_PRODUCT_DESCRIPTION) OR
1536 ((Recinfo.PRODUCT_DESCRIPTION IS NULL) AND
1537 (p_PRODUCT_DESCRIPTION IS NULL))) AND
1538 ((Recinfo.TRX_LINE_GL_DATE = p_TRX_LINE_GL_DATE) OR
1539 ((Recinfo.TRX_LINE_GL_DATE IS NULL) AND
1540 (p_TRX_LINE_GL_DATE IS NULL))) AND
1541 ((Recinfo.LINE_LEVEL_ACTION = p_LINE_LEVEL_ACTION) OR
1542 ((Recinfo.LINE_LEVEL_ACTION IS NULL) AND
1543 (p_LINE_LEVEL_ACTION IS NULL))) AND
1544 ((Recinfo.Historical_Flag = p_Historical_Flag) OR
1545 ((Recinfo.Historical_Flag IS NULL) AND
1546 (p_Historical_Flag IS NULL))) AND
1547 ((Recinfo.BILL_FROM_PARTY_SITE_ID = p_BILL_FROM_PARTY_SITE_ID) OR
1548 ((Recinfo.BILL_FROM_PARTY_SITE_ID IS NULL) AND
1549 (p_BILL_FROM_PARTY_SITE_ID IS NULL))) AND
1550 ((Recinfo.BILL_TO_PARTY_SITE_ID = p_BILL_TO_PARTY_SITE_ID) OR
1551 ((Recinfo.BILL_TO_PARTY_SITE_ID IS NULL) AND
1552 (p_BILL_TO_PARTY_SITE_ID IS NULL))) AND
1553 ((Recinfo.SHIP_FROM_PARTY_SITE_ID = p_SHIP_FROM_PARTY_SITE_ID) OR
1554 ((Recinfo.SHIP_FROM_PARTY_SITE_ID IS NULL) AND
1555 (p_SHIP_FROM_PARTY_SITE_ID IS NULL))) AND
1556 ((Recinfo.SHIP_TO_PARTY_SITE_ID = p_SHIP_TO_PARTY_SITE_ID) OR
1557 ((Recinfo.SHIP_TO_PARTY_SITE_ID IS NULL) AND
1558 (p_SHIP_TO_PARTY_SITE_ID IS NULL))) AND
1559 ((Recinfo.SHIP_TO_PARTY_ID = p_SHIP_TO_PARTY_ID) OR
1560 ((Recinfo.SHIP_TO_PARTY_ID IS NULL) AND
1561 (p_SHIP_TO_PARTY_ID IS NULL))) AND
1562 ((Recinfo.SHIP_FROM_PARTY_ID = p_SHIP_FROM_PARTY_ID) OR
1563 ((Recinfo.SHIP_FROM_PARTY_ID IS NULL) AND
1564 (p_SHIP_FROM_PARTY_ID IS NULL))) AND
1565 ((Recinfo.BILL_TO_PARTY_ID = p_BILL_TO_PARTY_ID) OR
1566 ((Recinfo.BILL_TO_PARTY_ID IS NULL) AND
1567 (p_BILL_TO_PARTY_ID IS NULL))) AND
1568 ((Recinfo.BILL_FROM_PARTY_ID = p_BILL_FROM_PARTY_ID) OR
1569 ((Recinfo.BILL_FROM_PARTY_ID IS NULL) AND
1570 (p_BILL_FROM_PARTY_ID IS NULL))) AND
1571 ((Recinfo.SHIP_TO_LOCATION_ID = p_SHIP_TO_LOCATION_ID) OR
1572 ((Recinfo.SHIP_TO_LOCATION_ID IS NULL) AND
1573 (p_SHIP_TO_LOCATION_ID IS NULL))) AND
1574 ((Recinfo.SHIP_FROM_LOCATION_ID = p_SHIP_FROM_LOCATION_ID) OR
1575 ((Recinfo.SHIP_FROM_LOCATION_ID IS NULL) AND
1576 (p_SHIP_FROM_LOCATION_ID IS NULL))) AND
1577 ((Recinfo.BILL_TO_LOCATION_ID = p_BILL_TO_LOCATION_ID) OR
1578 ((Recinfo.BILL_TO_LOCATION_ID IS NULL) AND
1579 (p_BILL_TO_LOCATION_ID IS NULL))) AND
1580 ((Recinfo.BILL_FROM_LOCATION_ID = p_BILL_FROM_LOCATION_ID) OR
1581 ((Recinfo.BILL_FROM_LOCATION_ID IS NULL) AND
1582 (p_BILL_FROM_LOCATION_ID IS NULL))) AND
1583 ((Recinfo.POA_LOCATION_ID = p_POA_LOCATION_ID) OR
1584 ((Recinfo.POA_LOCATION_ID IS NULL) AND
1585 (p_POA_LOCATION_ID IS NULL))) AND
1586 ((Recinfo.POO_LOCATION_ID = p_POO_LOCATION_ID) OR
1587 ((Recinfo.POO_LOCATION_ID IS NULL) AND
1588 (p_POO_LOCATION_ID IS NULL))) AND
1589 ((Recinfo.PAYING_LOCATION_ID = p_PAYING_LOCATION_ID) OR
1590 ((Recinfo.PAYING_LOCATION_ID IS NULL) AND
1591 (p_PAYING_LOCATION_ID IS NULL))) AND
1592 ((Recinfo.OWN_HQ_LOCATION_ID = p_OWN_HQ_LOCATION_ID) OR
1593 ((Recinfo.OWN_HQ_LOCATION_ID IS NULL) AND
1594 (p_OWN_HQ_LOCATION_ID IS NULL))) AND
1595 ((Recinfo.TRADING_HQ_LOCATION_ID = p_TRADING_HQ_LOCATION_ID) OR
1596 ((Recinfo.TRADING_HQ_LOCATION_ID IS NULL) AND
1597 (p_TRADING_HQ_LOCATION_ID IS NULL))) AND
1598 ((Recinfo.POC_LOCATION_ID = p_POC_LOCATION_ID) OR
1599 ((Recinfo.POC_LOCATION_ID IS NULL) AND
1600 (p_POC_LOCATION_ID IS NULL))) AND
1601 ((Recinfo.POI_LOCATION_ID = p_POI_LOCATION_ID) OR
1602 ((Recinfo.POI_LOCATION_ID IS NULL) AND
1603 (p_POI_LOCATION_ID IS NULL))) AND
1604 ((Recinfo.POD_LOCATION_ID = p_POD_LOCATION_ID) OR
1605 ((Recinfo.POD_LOCATION_ID IS NULL) AND
1606 (p_POD_LOCATION_ID IS NULL))) AND
1607 ((Recinfo.TAX_REGIME_ID = p_TAX_REGIME_ID) OR
1608 ((Recinfo.TAX_REGIME_ID IS NULL) AND
1609 (p_TAX_REGIME_ID IS NULL))) AND
1610 ((Recinfo.TAX_REGIME_CODE = p_TAX_REGIME_CODE) OR
1611 ((Recinfo.TAX_REGIME_CODE IS NULL) AND
1612 (p_TAX_REGIME_CODE IS NULL))) AND
1613 ((Recinfo.TAX_ID = p_TAX_ID) OR
1614 ((Recinfo.TAX_ID IS NULL) AND
1615 (p_TAX_ID IS NULL))) AND
1616 ((Recinfo.TAX = p_TAX) OR
1617 ((Recinfo.TAX IS NULL) AND
1618 (p_TAX IS NULL))) AND
1619 ((Recinfo.TAX_STATUS_ID = p_TAX_STATUS_ID) OR
1620 ((Recinfo.TAX_STATUS_ID IS NULL) AND
1621 (p_TAX_STATUS_ID IS NULL))) AND
1622 ((Recinfo.TAX_STATUS_CODE = p_TAX_STATUS_CODE) OR
1623 ((Recinfo.TAX_STATUS_CODE IS NULL) AND
1624 (p_TAX_STATUS_CODE IS NULL))) AND
1625 ((Recinfo.TAX_RATE_ID = p_TAX_RATE_ID) OR
1626 ((Recinfo.TAX_RATE_ID IS NULL) AND
1627 (p_TAX_RATE_ID IS NULL))) AND
1628 ((Recinfo.TAX_RATE_CODE = p_TAX_RATE_CODE) OR
1629 ((Recinfo.TAX_RATE_CODE IS NULL) AND
1630 (p_TAX_RATE_CODE IS NULL))) AND
1631 ((Recinfo.TAX_RATE = p_TAX_RATE) OR
1632 ((Recinfo.TAX_RATE IS NULL) AND
1633 (p_TAX_RATE IS NULL))) AND
1634 ((Recinfo.TAX_LINE_AMT = p_TAX_LINE_AMT) OR
1635 ((Recinfo.TAX_LINE_AMT IS NULL) AND
1636 (p_TAX_LINE_AMT IS NULL))) AND
1637 ((Recinfo.LINE_CLASS = p_LINE_CLASS ) OR
1638 ((Recinfo.LINE_CLASS IS NULL) AND
1639 (p_LINE_CLASS IS NULL))) AND
1640 ((Recinfo.INPUT_TAX_CLASSIFICATION_CODE = p_INPUT_TAX_CLASSIF_CODE) OR
1641 ((Recinfo.INPUT_TAX_CLASSIFICATION_CODE IS NULL) AND
1642 (p_INPUT_TAX_CLASSIF_CODE IS NULL))) AND
1643 ((Recinfo.OUTPUT_TAX_CLASSIFICATION_CODE = p_OUTPUT_TAX_CLASSIF_CODE) OR
1644 ((Recinfo.OUTPUT_TAX_CLASSIFICATION_CODE IS NULL) AND
1645 (p_OUTPUT_TAX_CLASSIF_CODE IS NULL))) AND
1646 ((Recinfo.REF_DOC_TRX_LEVEL_TYPE = p_REF_DOC_TRX_LEVEL_TYPE ) OR
1647 ((Recinfo.REF_DOC_TRX_LEVEL_TYPE IS NULL) AND
1648 (p_REF_DOC_TRX_LEVEL_TYPE IS NULL))) AND
1649 ((Recinfo.APPLIED_TO_TRX_LEVEL_TYPE = p_APPLIED_TO_TRX_LEVEL_TYPE ) OR
1650 ((Recinfo.APPLIED_TO_TRX_LEVEL_TYPE IS NULL) AND
1651 (p_APPLIED_TO_TRX_LEVEL_TYPE IS NULL))) AND
1652 ((Recinfo.APPLIED_FROM_TRX_LEVEL_TYPE = p_APPLIED_FROM_TRX_LEVEL_TYPE ) OR
1653 ((Recinfo.APPLIED_FROM_TRX_LEVEL_TYPE IS NULL) AND
1654 (p_APPLIED_FROM_TRX_LEVEL_TYPE IS NULL))) AND
1655 ((Recinfo.ADJUSTED_DOC_TRX_LEVEL_TYPE = p_ADJUSTED_DOC_TRX_LEVEL_TYPE ) OR
1656 ((Recinfo.ADJUSTED_DOC_TRX_LEVEL_TYPE IS NULL) AND
1657 (p_ADJUSTED_DOC_TRX_LEVEL_TYPE IS NULL))) AND
1658 ((Recinfo.EXEMPTION_CONTROL_FLAG = p_EXEMPTION_CONTROL_FLAG ) OR
1659 ((Recinfo.EXEMPTION_CONTROL_FLAG IS NULL) AND
1660 (p_EXEMPTION_CONTROL_FLAG IS NULL))) AND
1661 ((Recinfo.EXEMPT_REASON_CODE = p_EXEMPT_REASON_CODE ) OR
1662 ((Recinfo.EXEMPT_REASON_CODE IS NULL) AND
1663 (p_EXEMPT_REASON_CODE IS NULL))) AND
1664 ((Recinfo.EXEMPT_CERTIFICATE_NUMBER = p_EXEMPT_CERTIFICATE_NUMBER) OR
1665 ((Recinfo.EXEMPT_CERTIFICATE_NUMBER IS NULL) AND
1666 (p_EXEMPT_CERTIFICATE_NUMBER IS NULL))) AND
1667 ((Recinfo.EXEMPT_REASON = p_EXEMPT_REASON ) OR
1668 ((Recinfo.EXEMPT_REASON IS NULL) AND
1669 (p_EXEMPT_REASON IS NULL))) AND
1670 ((Recinfo.CASH_DISCOUNT = p_CASH_DISCOUNT ) OR
1671 ((Recinfo.CASH_DISCOUNT IS NULL) AND
1672 (p_CASH_DISCOUNT IS NULL))) AND
1673 ((Recinfo.VOLUME_DISCOUNT = p_VOLUME_DISCOUNT ) OR
1674 ((Recinfo.VOLUME_DISCOUNT IS NULL) AND
1675 (p_VOLUME_DISCOUNT IS NULL))) AND
1676 ((Recinfo.TRADING_DISCOUNT = p_TRADING_DISCOUNT ) OR
1677 ((Recinfo.TRADING_DISCOUNT IS NULL) AND
1678 (p_TRADING_DISCOUNT IS NULL))) AND
1679 ((Recinfo.TRANSFER_CHARGE = p_TRANSFER_CHARGE ) OR
1680 ((Recinfo.TRANSFER_CHARGE IS NULL) AND
1681 (p_TRANSFER_CHARGE IS NULL))) AND
1682 ((Recinfo.TRANSPORTATION_CHARGE = p_TRANSPORTATION_CHARGE ) OR
1683 ((Recinfo.TRANSPORTATION_CHARGE IS NULL) AND
1684 (p_TRANSPORTATION_CHARGE IS NULL))) AND
1685 ((Recinfo.INSURANCE_CHARGE = p_INSURANCE_CHARGE ) OR
1686 ((Recinfo.INSURANCE_CHARGE IS NULL) AND
1687 (p_INSURANCE_CHARGE IS NULL))) AND
1688 ((Recinfo.OTHER_CHARGE = p_OTHER_CHARGE ) OR
1689 ((Recinfo.OTHER_CHARGE IS NULL) AND
1690 (p_OTHER_CHARGE IS NULL))) AND
1691 ((Recinfo.RECEIVABLES_TRX_TYPE_ID = p_RECEIVABLES_TRX_TYPE_ID ) OR
1692 ((Recinfo.RECEIVABLES_TRX_TYPE_ID IS NULL) AND
1693 (p_RECEIVABLES_TRX_TYPE_ID IS NULL))) AND
1694 ((Recinfo.CTRL_HDR_TX_APPL_FLAG = p_CTRL_HDR_TX_APPL_FLAG ) OR
1695 ((Recinfo.CTRL_HDR_TX_APPL_FLAG IS NULL) AND
1696 (p_CTRL_HDR_TX_APPL_FLAG IS NULL))) AND
1697 ((Recinfo.CTRL_TOTAL_LINE_TX_AMT = p_CTRL_TOTAL_LINE_TX_AMT ) OR
1698 ((Recinfo.CTRL_TOTAL_LINE_TX_AMT IS NULL) AND
1699 (p_CTRL_TOTAL_LINE_TX_AMT IS NULL))) AND
1700 (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER) AND
1701 (Recinfo.CREATED_BY = p_CREATED_BY) AND
1702 (Recinfo.CREATION_DATE = p_CREATION_DATE) AND
1703 (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) AND
1704 (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) AND
1705 ((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR
1706 ((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND
1707 (p_LAST_UPDATE_LOGIN IS NULL))) ) THEN
1708 return;
1709 ELSE
1710 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1711 APP_EXCEPTION.Raise_Exception;
1712 END IF;
1713
1714 IF (g_level_procedure >= g_current_runtime_level ) THEN
1715 FND_LOG.STRING(g_level_procedure,
1716 'ZX.PLSQL.ZX_TRX_DETAIL.Lock_row.END',
1717 'ZX_TRX_DETAIL: Lock_row (-)');
1718 END IF;
1719
1720 EXCEPTION
1721 WHEN OTHERS THEN
1722 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1723
1724 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
1725 FND_MSG_PUB.Add;
1726
1727 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1728 FND_LOG.STRING(g_level_unexpected,
1729 'ZX.PLSQL.ZX_TRX_DETAIL.Lock_Row',
1730 p_error_buffer);
1731 END IF;
1732
1733 END Lock_Row;
1734
1735 PROCEDURE Insert_Temporary_Table
1736 (p_application_id NUMBER,
1737 p_entity_code VARCHAR2,
1738 p_event_class_code VARCHAR2,
1739 p_trx_id NUMBER,
1740 p_event_type_code VARCHAR2,
1741 p_ledger_id NUMBER,
1742 p_reporting_currency_code VARCHAR2,
1743 p_currency_conversion_date DATE,
1744 p_currency_conversion_type VARCHAR2,
1745 p_currency_conversion_rate NUMBER,
1746 p_minimum_accountable_unit NUMBER,
1747 p_status VARCHAR2 DEFAULT NULL,
1748 p_precision NUMBER,
1749 p_line_level_action VARCHAR2,
1750 x_return_status OUT NOCOPY VARCHAR2,
1751 x_msg_data OUT NOCOPY VARCHAR2 ) IS
1752
1753 l_return_status VARCHAR2(30);
1754 l_msg_count NUMBER;
1755 l_msg_data VARCHAR2(5000);
1756 l_msg_string VARCHAR2(5000);
1757 sid NUMBER;
1758 l_error_buffer VARCHAR2(256);
1759 l_tax_event_type_code VARCHAR2(30);
1760 debug_info VARCHAR2(100);
1761 l_trx_line_type VARCHAR2(30);
1762 l_count NUMBER;
1763 l_trx_lines_sync NUMBER;
1764 l_trx_lines NUMBER;
1765 l_sync_trx_rec ZX_API_PUB.sync_trx_rec_type;
1766 l_sync_trx_lines_rec ZX_API_PUB.sync_trx_lines_rec_type;
1767 i NUMBER;
1768
1769 CURSOR sync_trx_lines_rec IS
1770 SELECT APPLICATION_ID,
1771 ENTITY_CODE,
1772 EVENT_CLASS_CODE,
1773 TRX_ID,
1774 TRX_LEVEL_TYPE,
1775 TRX_LINE_ID,
1776 NULL TRX_WAYBILL_NUMBER,
1777 TRX_LINE_DESCRIPTION,
1778 PRODUCT_DESCRIPTION,
1779 TRX_LINE_GL_DATE,
1780 NULL MERCHANT_PARTY_NAME,
1781 NULL MERCHANT_PARTY_DOCUMENT_NUMBER,
1782 NULL MERCHANT_PARTY_REFERENCE,
1783 NULL MERCHANT_PARTY_TAXPAYER_ID,
1784 NULL MERCHANT_PARTY_TAX_REG_NUMBER,
1785 NULL ASSET_NUMBER
1786 FROM ZX_TRANSACTION_LINES
1787 WHERE APPLICATION_ID = p_application_id
1788 AND ENTITY_CODE = p_entity_code
1789 AND EVENT_CLASS_CODE = p_event_class_code
1790 AND TRX_ID = p_trx_id
1791 AND TRX_LINE_TYPE <> 'TAX';
1792
1793 BEGIN
1794
1795 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1796
1797 IF (g_level_procedure >= g_current_runtime_level ) THEN
1798 FND_LOG.STRING(g_level_procedure,
1799 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table.BEGIN',
1800 'ZX_TRX_DETAIL: Insert_Temporary_Table (+)');
1801 FND_LOG.STRING(g_level_procedure,
1802 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
1803 'Event: Event Type Code :'||p_event_type_code);
1804
1805 END IF;
1806
1807
1808 IF (g_level_procedure >= g_current_runtime_level ) THEN
1809 FND_LOG.STRING(g_level_procedure,
1810 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Tables',
1811 'Insert into zx_trx_headers_gt (+)');
1812 END IF;
1813
1814 INSERT INTO ZX_TRX_HEADERS_GT (INTERNAL_ORGANIZATION_ID,
1815 INTERNAL_ORG_LOCATION_ID,
1816 APPLICATION_ID,
1817 ENTITY_CODE,
1818 EVENT_CLASS_CODE,
1819 EVENT_TYPE_CODE,
1820 TRX_ID,
1821 TRX_DATE,
1822 --TRX_DOC_REVISION,
1823 LEDGER_ID,
1824 TRX_CURRENCY_CODE,
1825 CURRENCY_CONVERSION_DATE,
1826 CURRENCY_CONVERSION_RATE,
1827 CURRENCY_CONVERSION_TYPE,
1828 MINIMUM_ACCOUNTABLE_UNIT,
1829 PRECISION,
1830 LEGAL_ENTITY_ID,
1831 ROUNDING_SHIP_TO_PARTY_ID,
1832 ROUNDING_SHIP_FROM_PARTY_ID,
1833 ROUNDING_BILL_TO_PARTY_ID,
1834 ROUNDING_BILL_FROM_PARTY_ID,
1835 RNDG_SHIP_TO_PARTY_SITE_ID,
1836 RNDG_SHIP_FROM_PARTY_SITE_ID,
1837 RNDG_BILL_TO_PARTY_SITE_ID,
1838 RNDG_BILL_FROM_PARTY_SITE_ID,
1839 ESTABLISHMENT_ID,
1840 RECEIVABLES_TRX_TYPE_ID,
1841 --RELATED_DOC_APPLICATION_ID,
1842 --RELATED_DOC_ENTITY_CODE,
1843 --RELATED_DOC_EVENT_CLASS_CODE,
1844 --RELATED_DOC_TRX_ID,
1845 --REL_DOC_HDR_TRX_USER_KEY1,
1846 --REL_DOC_HDR_TRX_USER_KEY2,
1847 --REL_DOC_HDR_TRX_USER_KEY3,
1848 --REL_DOC_HDR_TRX_USER_KEY4,
1849 --REL_DOC_HDR_TRX_USER_KEY5,
1850 --REL_DOC_HDR_TRX_USER_KEY6,
1851 --RELATED_DOC_NUMBER,
1852 --RELATED_DOC_DATE,
1853 DEFAULT_TAXATION_COUNTRY,
1854 Quote_Flag,
1855 CTRL_TOTAL_HDR_TX_AMT,
1856 TRX_NUMBER,
1857 TRX_DESCRIPTION,
1858 --TRX_COMMUNICATED_DATE,
1859 --BATCH_SOURCE_ID,
1860 --BATCH_SOURCE_NAME,
1861 --DOC_SEQ_ID,
1862 --DOC_SEQ_NAME,
1863 --DOC_SEQ_VALUE,
1864 --TRX_DUE_DATE,
1865 --TRX_TYPE_DESCRIPTION,
1866 --BILLING_TRADING_PARTNER_NAME,
1867 --BILLING_TRADING_PARTNER_NUMBER,
1868 --Billing_Tp_Tax_Reporting_Flag,
1869 --BILLING_TP_TAXPAYER_ID,
1870 DOCUMENT_SUB_TYPE,
1871 SUPPLIER_TAX_INVOICE_NUMBER,
1872 SUPPLIER_TAX_INVOICE_DATE,
1873 SUPPLIER_EXCHANGE_RATE,
1874 TAX_INVOICE_DATE,
1875 TAX_INVOICE_NUMBER,
1876 FIRST_PTY_ORG_ID,
1877 PORT_OF_ENTRY_CODE,
1878 TAX_REPORTING_FLAG,
1879 SHIP_TO_CUST_ACCT_SITE_USE_ID,
1880 BILL_TO_CUST_ACCT_SITE_USE_ID,
1881 PROVNL_TAX_DETERMINATION_DATE,
1882 APPLIED_TO_TRX_NUMBER,
1883 SHIP_THIRD_PTY_ACCT_ID,
1884 BILL_THIRD_PTY_ACCT_ID,
1885 SHIP_THIRD_PTY_ACCT_SITE_ID,
1886 BILL_THIRD_PTY_ACCT_SITE_ID,
1887 VALIDATION_CHECK_FLAG,
1888 --TAX_EVENT_CLASS_CODE,
1889 TAX_EVENT_TYPE_CODE
1890 --DOC_EVENT_STATUS,
1891 --RDNG_SHIP_TO_PTY_TX_PROF_ID,
1892 --RDNG_SHIP_FROM_PTY_TX_PROF_ID,
1893 --RDNG_BILL_TO_PTY_TX_PROF_ID,
1894 --RDNG_BILL_FROM_PTY_TX_PROF_ID,
1895 --RDNG_SHIP_TO_PTY_TX_P_ST_ID,
1896 --RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
1897 --RDNG_BILL_TO_PTY_TX_P_ST_ID,
1898 --RDNG_BILL_FROM_PTY_TX_P_ST_ID
1899 )
1900 SELECT internal_organization_id,
1901 internal_org_location_id,
1902 application_id,
1903 entity_code,
1904 event_class_code,
1905 event_type_code,
1906 trx_id,
1907 trx_date,
1908 --p_trx_doc_revision,
1909 ledger_id,
1910 trx_currency_code,
1911 currency_conversion_date,
1912 currency_conversion_rate,
1913 currency_conversion_type,
1914 minimum_accountable_unit,
1915 precision,
1916 legal_entity_id,
1917 rounding_ship_to_party_id,
1918 rounding_ship_from_party_id,
1919 rounding_bill_to_party_id,
1920 rounding_bill_from_party_id,
1921 rndg_ship_to_party_site_id,
1922 rndg_ship_from_party_site_id,
1923 rndg_bill_to_party_site_id,
1924 rndg_bill_from_party_site_id,
1925 establishment_id,
1926 receivables_trx_type_id,
1927 --p_related_doc_application_id,
1928 --p_related_doc_entity_code,
1929 --p_related_doc_evt_class_code, --reduced size p_related_doc_event_class_code
1930 --p_related_doc_trx_id,
1931 --p_rel_doc_hdr_trx_user_key1,
1932 --p_rel_doc_hdr_trx_user_key2,
1933 --p_rel_doc_hdr_trx_user_key3,
1934 --p_rel_doc_hdr_trx_user_key4,
1935 --p_rel_doc_hdr_trx_user_key5,
1936 --p_rel_doc_hdr_trx_user_key6,
1937 --p_related_doc_number,
1938 --p_related_doc_date,
1939 default_taxation_country,
1940 Quote_Flag,
1941 ctrl_total_hdr_tx_amt,
1942 trx_number,
1943 trx_description,
1944 --p_trx_communicated_date,
1945 --p_batch_source_id,
1946 --p_batch_source_name,
1947 --p_doc_seq_id,
1948 --p_doc_seq_name,
1949 --p_doc_seq_value,
1950 --p_trx_due_date,
1951 --p_trx_type_description,
1952 --p_billing_trad_partner_name, --reduced size p_billing_trading_partner_name
1953 --p_billing_trad_partner_number, --reduced size p_billing_trading_partner_number
1954 --p_billing_tp_tax_report_flg, --reduced size p_Billing_Tp_Tax_Reporting_Flag
1955 --p_billing_tp_taxpayer_id,
1956 document_sub_type,
1957 supplier_tax_invoice_number,
1958 supplier_tax_invoice_date,
1959 supplier_exchange_rate,
1960 tax_invoice_date,
1961 tax_invoice_number,
1962 first_pty_org_id,
1963 port_of_entry_code,
1964 tax_reporting_flag,
1965 ship_to_cust_acct_site_use_id,
1966 bill_to_cust_acct_site_use_id,
1967 provnl_tax_determination_date,
1968 applied_to_trx_number,
1969 ship_third_pty_acct_id,
1970 bill_third_pty_acct_id,
1971 ship_third_pty_acct_site_id,
1972 bill_third_pty_acct_site_id,
1973 validation_check_flag,
1974 --p_tax_event_class_code,
1975 tax_event_type_code--p_tax_event_type_code,
1976 --p_doc_event_status,
1977 --p_rdng_ship_to_pty_tx_prof_id,
1978 --p_rdng_ship_fr_pty_tx_prof_id, --reduced size p_rdng_ship_from_pty_tx_prof_id
1979 --p_rdng_bill_to_pty_tx_prof_id,
1980 --p_rdng_bill_fr_pty_tx_prof_id, --reduced size p_rdng_bill_from_pty_tx_prof_id
1981 --p_rdng_ship_to_pty_tx_p_st_id,
1982 --p_rdng_ship_fr_pty_tx_p_st_id, --reduced size p_rdng_ship_from_pty_tx_p_st_id
1983 --p_rdng_bill_to_pty_tx_p_st_id,
1984 --p_rdng_bill_fr_pty_tx_p_st_id); --reduced size p_rdng_bill_from_pty_tx_p_st_id
1985 FROM ZX_TRANSACTION
1986 WHERE APPLICATION_ID = p_application_id
1987 AND ENTITY_CODE = p_entity_code
1988 AND EVENT_CLASS_CODE = p_event_class_code
1989 AND TRX_ID = p_trx_id;
1990
1991 IF (g_level_procedure >= g_current_runtime_level ) THEN
1992 FND_LOG.STRING(g_level_procedure,
1993 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
1994 'Insert into zx_trx_headers_gt (-)');
1995 END IF;
1996
1997 IF (g_level_procedure >= g_current_runtime_level ) THEN
1998 FND_LOG.STRING(g_level_procedure,
1999 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2000 'Insert into ZX_TRANSACTION_LINES_GT (+)');
2001 END IF;
2002
2003 INSERT INTO ZX_TRANSACTION_LINES_GT (APPLICATION_ID,
2004 ENTITY_CODE,
2005 EVENT_CLASS_CODE,
2006 TRX_ID,
2007 TRX_LEVEL_TYPE,
2008 TRX_LINE_ID,
2009 LINE_LEVEL_ACTION,
2010 TRX_SHIPPING_DATE,
2011 TRX_RECEIPT_DATE,
2012 TRX_LINE_TYPE,
2013 TRX_LINE_DATE,
2014 TRX_BUSINESS_CATEGORY,
2015 LINE_INTENDED_USE,
2016 USER_DEFINED_FISC_CLASS,
2017 LINE_AMT,
2018 TRX_LINE_QUANTITY,
2019 UNIT_PRICE,
2020 EXEMPT_CERTIFICATE_NUMBER,
2021 EXEMPT_REASON,
2022 CASH_DISCOUNT,
2023 VOLUME_DISCOUNT,
2024 TRADING_DISCOUNT,
2025 TRANSFER_CHARGE,
2026 TRANSPORTATION_CHARGE,
2027 INSURANCE_CHARGE,
2028 OTHER_CHARGE,
2029 PRODUCT_ID,
2030 PRODUCT_FISC_CLASSIFICATION,
2031 PRODUCT_ORG_ID,
2032 UOM_CODE,
2033 PRODUCT_TYPE,
2034 PRODUCT_CODE,
2035 PRODUCT_CATEGORY,
2036 --TRX_SIC_CODE,
2037 --FOB_POINT,
2038 SHIP_TO_PARTY_ID,
2039 SHIP_FROM_PARTY_ID,
2040 POA_PARTY_ID,
2041 POO_PARTY_ID,
2042 BILL_TO_PARTY_ID,
2043 BILL_FROM_PARTY_ID,
2044 MERCHANT_PARTY_ID,
2045 SHIP_TO_PARTY_SITE_ID,
2046 SHIP_FROM_PARTY_SITE_ID,
2047 POA_PARTY_SITE_ID,
2048 POO_PARTY_SITE_ID,
2049 BILL_TO_PARTY_SITE_ID,
2050 BILL_FROM_PARTY_SITE_ID,
2051 SHIP_TO_LOCATION_ID,
2052 SHIP_FROM_LOCATION_ID,
2053 POA_LOCATION_ID,
2054 POO_LOCATION_ID,
2055 BILL_TO_LOCATION_ID,
2056 BILL_FROM_LOCATION_ID,
2057 ACCOUNT_CCID,
2058 ACCOUNT_STRING,
2059 MERCHANT_PARTY_COUNTRY,
2060 REF_DOC_APPLICATION_ID,
2061 REF_DOC_ENTITY_CODE,
2062 REF_DOC_EVENT_CLASS_CODE,
2063 REF_DOC_TRX_ID,
2064 REF_DOC_LINE_ID,
2065 REF_DOC_LINE_QUANTITY,
2066 APPLIED_FROM_APPLICATION_ID,
2067 APPLIED_FROM_ENTITY_CODE,
2068 APPLIED_FROM_EVENT_CLASS_CODE,
2069 APPLIED_FROM_TRX_ID,
2070 APPLIED_FROM_LINE_ID,
2071 ADJUSTED_DOC_APPLICATION_ID,
2072 ADJUSTED_DOC_ENTITY_CODE,
2073 ADJUSTED_DOC_EVENT_CLASS_CODE,
2074 ADJUSTED_DOC_TRX_ID,
2075 ADJUSTED_DOC_LINE_ID,
2076 --ADJUSTED_DOC_NUMBER,
2077 ADJUSTED_DOC_DATE,
2078 APPLIED_TO_APPLICATION_ID,
2079 APPLIED_TO_ENTITY_CODE,
2080 APPLIED_TO_EVENT_CLASS_CODE,
2081 APPLIED_TO_TRX_ID,
2082 APPLIED_TO_TRX_LINE_ID,
2083 --TRX_ID_LEVEL2,
2084 --TRX_ID_LEVEL3,
2085 --TRX_ID_LEVEL4,
2086 --TRX_ID_LEVEL5,
2087 --TRX_ID_LEVEL6,
2088 TRX_LINE_NUMBER,
2089 TRX_LINE_DESCRIPTION,
2090 PRODUCT_DESCRIPTION,
2091 --TRX_WAYBILL_NUMBER,
2092 TRX_LINE_GL_DATE,
2093 --MERCHANT_PARTY_NAME,
2094 --MERCHANT_PARTY_DOCUMENT_NUMBER,
2095 --MERCHANT_PARTY_REFERENCE,
2096 --MERCHANT_PARTY_TAXPAYER_ID,
2097 --MERCHANT_PARTY_TAX_REG_NUMBER,
2098 --PAYING_PARTY_ID,
2099 --OWN_HQ_PARTY_ID,
2100 --TRADING_HQ_PARTY_ID,
2101 --POI_PARTY_ID,
2102 --POD_PARTY_ID,
2103 --TITLE_TRANSFER_PARTY_ID,
2104 --PAYING_PARTY_SITE_ID,
2105 --OWN_HQ_PARTY_SITE_ID,
2106 --TRADING_HQ_PARTY_SITE_ID,
2107 --POI_PARTY_SITE_ID,
2108 --POD_PARTY_SITE_ID,
2109 --TITLE_TRANSFER_PARTY_SITE_ID,
2110 PAYING_LOCATION_ID,
2111 OWN_HQ_LOCATION_ID,
2112 TRADING_HQ_LOCATION_ID,
2113 POC_LOCATION_ID,
2114 POI_LOCATION_ID,
2115 POD_LOCATION_ID,
2116 --TITLE_TRANSFER_LOCATION_ID,
2117 --BANKING_TP_TAXPAYER_ID,
2118 --ASSESSABLE_VALUE,
2119 --ASSET_FLAG,
2120 --ASSET_NUMBER,
2121 --ASSET_ACCUM_DEPRECIATION,
2122 --ASSET_TYPE,
2123 --ASSET_COST,
2124 --NUMERIC1,
2125 --NUMERIC2,
2126 --NUMERIC3,
2127 --NUMERIC4,
2128 --NUMERIC5,
2129 --NUMERIC6,
2130 --NUMERIC7,
2131 --NUMERIC8,
2132 --NUMERIC9,
2133 --NUMERIC10,
2134 --CHAR1,
2135 --CHAR2,
2136 --CHAR3,
2137 --CHAR4,
2138 --CHAR5,
2139 --CHAR6,
2140 --CHAR7,
2141 --CHAR8,
2142 --CHAR9,
2143 --CHAR10,
2144 --DATE1,
2145 --DATE2,
2146 --DATE3,
2147 --DATE4,
2148 --DATE5,
2149 --DATE6,
2150 --DATE7,
2151 --DATE8,
2152 --DATE9,
2153 --DATE10,
2154 --SHIP_TO_PARTY_TAX_PROF_ID,
2155 --SHIP_FROM_PARTY_TAX_PROF_ID,
2156 --POA_PARTY_TAX_PROF_ID,
2157 --POO_PARTY_TAX_PROF_ID,
2158 --PAYING_PARTY_TAX_PROF_ID,
2159 --OWN_HQ_PARTY_TAX_PROF_ID,
2160 --TRADING_HQ_PARTY_TAX_PROF_ID,
2161 --POI_PARTY_TAX_PROF_ID,
2162 --POD_PARTY_TAX_PROF_ID,
2163 --BILL_TO_PARTY_TAX_PROF_ID,
2164 --BILL_FROM_PARTY_TAX_PROF_ID,
2165 --TITLE_TRANS_PARTY_TAX_PROF_ID,
2166 --SHIP_TO_SITE_TAX_PROF_ID,
2167 --SHIP_FROM_SITE_TAX_PROF_ID,
2168 --POA_SITE_TAX_PROF_ID,
2169 --POO_SITE_TAX_PROF_ID,
2170 --PAYING_SITE_TAX_PROF_ID,
2171 --OWN_HQ_SITE_TAX_PROF_ID,
2172 --TRADING_HQ_SITE_TAX_PROF_ID,
2173 --POI_SITE_TAX_PROF_ID,
2174 --POD_SITE_TAX_PROF_ID,
2175 --BILL_TO_SITE_TAX_PROF_ID,
2176 --BILL_FROM_SITE_TAX_PROF_ID,
2177 --TITLE_TRANS_SITE_TAX_PROF_ID,
2178 --MERCHANT_PARTY_TAX_PROF_ID,
2179 LINE_CLASS,
2180 INPUT_TAX_CLASSIFICATION_CODE,
2181 OUTPUT_TAX_CLASSIFICATION_CODE,
2182 REF_DOC_TRX_LEVEL_TYPE,
2183 APPLIED_TO_TRX_LEVEL_TYPE,
2184 APPLIED_FROM_TRX_LEVEL_TYPE,
2185 ADJUSTED_DOC_TRX_LEVEL_TYPE,
2186 EXEMPTION_CONTROL_FLAG,
2187 EXEMPT_REASON_CODE,
2188 --RECEIVABLES_TRX_TYPE_ID, bug#4288610
2189 CTRL_HDR_TX_APPL_FLAG,
2190 CTRL_TOTAL_LINE_TX_AMT,
2191 LINE_AMT_INCLUDES_TAX_FLAG,
2192 HISTORICAL_FLAG
2193 --TAX_CLASSIFICATION_CODE
2194 )
2195 SELECT application_id,
2196 entity_code,
2197 event_class_code,
2198 trx_id,
2199 trx_level_type,
2200 trx_line_id,
2201 line_level_action,
2202 trx_shipping_date,
2203 trx_receipt_date,
2204 trx_line_type,
2205 trx_line_date,
2206 trx_business_category,
2207 line_intended_use,
2208 user_defined_fisc_class,
2209 line_amt,
2210 trx_line_quantity,
2211 unit_price,
2212 exempt_certificate_number,
2213 exempt_reason,
2214 cash_discount,
2215 volume_discount,
2216 trading_discount,
2217 transfer_charge,
2218 transportation_charge,
2219 insurance_charge,
2220 other_charge,
2221 product_id,
2222 product_fisc_classification,
2223 product_org_id,
2224 uom_code,
2225 DECODE(product_type, 'MEMOS', NULL,
2226 product_type) product_type,
2227 product_code,
2228 product_category,
2229 --p_trx_sic_code,
2230 --p_fob_point,
2231 ship_to_party_id,
2232 ship_from_party_id,
2233 poa_party_id,
2234 poo_party_id,
2235 bill_to_party_id,
2236 bill_from_party_id,
2237 merchant_party_id,
2238 ship_to_party_site_id,
2239 ship_from_party_site_id,
2240 poa_party_site_id,
2241 poo_party_site_id,
2242 bill_to_party_site_id,
2243 bill_from_party_site_id,
2244 ship_to_location_id,
2245 ship_from_location_id,
2246 poa_location_id,
2247 poo_location_id,
2248 bill_to_location_id,
2249 bill_from_location_id,
2250 account_ccid,
2251 account_string,
2252 merchant_party_country,
2253 ref_doc_application_id,
2254 ref_doc_entity_code,
2255 ref_doc_event_class_code,
2256 ref_doc_trx_id,
2257 ref_doc_line_id,
2258 ref_doc_line_quantity,
2259 applied_from_application_id,
2260 applied_from_entity_code,
2261 applied_from_event_class_code,
2262 applied_from_trx_id,
2263 applied_from_line_id,
2264 adjusted_doc_application_id,
2265 adjusted_doc_entity_code,
2266 adjusted_doc_event_class_code,
2267 adjusted_doc_trx_id,
2268 adjusted_doc_line_id,
2269 --adjusted_doc_number,
2270 adjusted_doc_date,
2271 applied_to_application_id,
2272 applied_to_entity_code,
2273 applied_to_event_class_code,
2274 applied_to_trx_id,
2275 applied_to_trx_line_id,
2276 --p_trx_id_level2,
2277 --p_trx_id_level3,
2278 --p_trx_id_level4,
2279 --p_trx_id_level5,
2280 --p_trx_id_level6,
2281 trx_line_number,
2282 trx_line_description,
2283 product_description,
2284 --trx_waybill_number,
2285 trx_line_gl_date,
2286 --p_merchant_party_name,
2287 --p_merchant_party_doc_number,
2288 --p_merchant_party_reference,
2289 --p_merchant_party_taxpayer_id,
2290 --p_merchant_pty_tax_reg_number,
2291 --p_paying_party_id,
2292 --p_own_hq_party_id,
2293 --p_trading_hq_party_id,
2294 --p_poi_party_id,
2295 --p_pod_party_id,
2296 --p_title_transfer_party_id,
2297 --p_paying_party_site_id,
2298 --p_own_hq_party_site_id,
2299 --p_trading_hq_party_site_id,
2300 --p_poi_party_site_id,
2301 --p_pod_party_site_id,
2302 --p_title_transfer_pty_site_id,
2303 paying_location_id,
2304 own_hq_location_id,
2305 trading_hq_location_id,
2306 poc_location_id,
2307 poi_location_id,
2308 pod_location_id,
2309 --p_title_transfer_location_id,
2310 --p_banking_tp_taxpayer_id,
2311 --p_assessable_value,
2312 --p_asset_flag,
2313 --p_asset_number,
2314 --p_asset_accum_depreciation,
2315 --p_asset_type,
2316 --p_asset_cost,
2317 --p_numeric1,
2318 --p_numeric2,
2319 --p_numeric3,
2320 --p_numeric4,
2321 --p_numeric5,
2322 --p_numeric6,
2323 --p_numeric7,
2324 --p_numeric8,
2325 --p_numeric9,
2326 --p_numeric10,
2327 --p_char1,
2328 --p_char2,
2329 --p_char3,
2330 --p_char4,
2331 --p_char5,
2332 --p_char6,
2333 --p_char7,
2334 --p_char8,
2335 --p_char9,
2336 --p_char10,
2337 --p_date1,
2338 --p_date2,
2339 --p_date3,
2340 --p_date4,
2341 --p_date5,
2342 --p_date6,
2343 --p_date7,
2344 --p_date8,
2345 --p_date9,
2346 --p_date10,
2347 --p_ship_to_party_tax_prof_id,
2348 --p_ship_from_party_tax_prof_id,
2349 --p_poa_party_tax_prof_id,
2350 --p_poo_party_tax_prof_id,
2351 --p_paying_party_tax_prof_id,
2352 --p_own_hq_party_tax_prof_id,
2353 --p_trading_hq_pty_tax_prof_id,
2354 --p_poi_party_tax_prof_id,
2355 --p_pod_party_tax_prof_id,
2356 --p_bill_to_party_tax_prof_id,
2357 --p_bill_from_party_tax_prof_id,
2358 --p_title_trans_pty_tax_prof_id,
2359 --p_ship_to_site_tax_prof_id,
2360 --p_ship_from_site_tax_prof_id,
2361 --p_poa_site_tax_prof_id,
2362 --p_poo_site_tax_prof_id,
2363 --p_paying_site_tax_prof_id,
2364 --p_own_hq_site_tax_prof_id,
2365 --p_trading_hq_site_tax_prof_id,
2366 --p_poi_site_tax_prof_id,
2367 --p_pod_site_tax_prof_id,
2368 --p_bill_to_site_tax_prof_id,
2369 --p_bill_from_site_tax_prof_id,
2370 --p_title_trn_site_tax_prof_id,
2371 --p_merchant_party_tax_prof_id,
2372 line_class,
2373 input_tax_classification_code,
2374 output_tax_classification_code,
2375 ref_doc_trx_level_type,
2376 applied_to_trx_level_type,
2377 applied_from_trx_level_type,
2378 adjusted_doc_trx_level_type,
2379 nvl(exemption_control_flag,'S'), -- Bug 5211670
2380 exempt_reason_code,
2381 --receivables_trx_type_id, will add later
2382 ctrl_hdr_tx_appl_flag,
2383 ctrl_total_line_tx_amt,
2384 line_amt_includes_tax_flag,
2385 historical_flag
2386 --p_tax_classification_code
2387 FROM ZX_TRANSACTION_LINES
2388 WHERE APPLICATION_ID = p_application_id
2389 AND ENTITY_CODE = p_entity_code
2390 AND EVENT_CLASS_CODE = p_event_class_code
2391 AND TRX_ID = p_trx_id
2392 AND TRX_LINE_TYPE <> 'TAX';
2393
2394
2395 IF (g_level_procedure >= g_current_runtime_level ) THEN
2396 FND_LOG.STRING(g_level_procedure,
2397 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2398 'Insert to ZX_TRANSACTION_LINES_GT, count : ' ||
2399 TO_CHAR(SQL%ROWCOUNT));
2400 FND_LOG.STRING(g_level_procedure,
2401 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2402 'Insert into ZX_TRANSACTION_LINES_GT (-)');
2403 END IF;
2404
2405 IF p_status = 'SIMULATOR_IMPORT' THEN
2406 RETURN;
2407 END IF;
2408
2409
2410 IF p_event_type_code = 'STANDARD UPDATED' AND
2411 p_line_level_action = 'SYNCHRONIZE' THEN
2412
2413 SELECT APPLICATION_ID,
2414 ENTITY_CODE,
2415 EVENT_CLASS_CODE,
2416 EVENT_TYPE_CODE,
2417 TRX_ID,
2418 TRX_NUMBER,
2419 TRX_DESCRIPTION,
2420 NULL TRX_COMMUNICATED_DATE,
2421 NULL BATCH_SOURCE_ID,
2422 NULL BATCH_SOURCE_NAME,
2423 NULL DOC_SEQ_ID,
2424 NULL DOC_SEQ_NAME,
2425 NULL DOC_SEQ_VALUE,
2426 NULL TRX_DUE_DATE,
2427 NULL TRX_TYPE_DESCRIPTION,
2428 NULL SUPPLIER_TAX_INVOICE_NUMBER,
2429 NULL SUPPLIER_TAX_INVOICE_DATE,
2430 NULL SUPPLIER_EXCHANGE_RATE,
2431 NULL TAX_INVOICE_DATE,
2432 NULL TAX_INVOICE_NUMBER,
2433 NULL PORT_OF_ENTRY_CODE
2434 INTO l_sync_trx_rec.APPLICATION_ID,
2435 l_sync_trx_rec.ENTITY_CODE,
2436 l_sync_trx_rec.EVENT_CLASS_CODE,
2437 l_sync_trx_rec.EVENT_TYPE_CODE,
2438 l_sync_trx_rec.TRX_ID,
2439 l_sync_trx_rec.TRX_NUMBER,
2440 l_sync_trx_rec.TRX_DESCRIPTION,
2441 l_sync_trx_rec.TRX_COMMUNICATED_DATE,
2442 l_sync_trx_rec.BATCH_SOURCE_ID,
2443 l_sync_trx_rec.BATCH_SOURCE_NAME,
2444 l_sync_trx_rec.DOC_SEQ_ID,
2445 l_sync_trx_rec.DOC_SEQ_NAME,
2446 l_sync_trx_rec.DOC_SEQ_VALUE,
2447 l_sync_trx_rec.TRX_DUE_DATE,
2448 l_sync_trx_rec.TRX_TYPE_DESCRIPTION,
2449 l_sync_trx_rec.SUPPLIER_TAX_INVOICE_NUMBER,
2450 l_sync_trx_rec.SUPPLIER_TAX_INVOICE_DATE,
2451 l_sync_trx_rec.SUPPLIER_EXCHANGE_RATE,
2452 l_sync_trx_rec.TAX_INVOICE_DATE,
2453 l_sync_trx_rec.TAX_INVOICE_NUMBER,
2454 l_sync_trx_rec.PORT_OF_ENTRY_CODE
2455 FROM ZX_TRANSACTION
2456 WHERE APPLICATION_ID = p_application_id
2457 AND ENTITY_CODE = p_entity_code
2458 AND EVENT_CLASS_CODE = p_event_class_code
2459 AND TRX_ID = p_trx_id;
2460
2461 OPEN sync_trx_lines_rec;
2462 debug_info := 'Fetch cursor sync_trx_lines_rec';
2463
2464 i :=i + 1;
2465
2466 FETCH sync_trx_lines_rec INTO l_sync_trx_lines_rec.APPLICATION_ID(i),
2467 l_sync_trx_lines_rec.ENTITY_CODE(i),
2468 l_sync_trx_lines_rec.EVENT_CLASS_CODE(i),
2469 l_sync_trx_lines_rec.TRX_ID(i),
2470 l_sync_trx_lines_rec.TRX_LEVEL_TYPE(i),
2471 l_sync_trx_lines_rec.TRX_LINE_ID(i),
2472 l_sync_trx_lines_rec.TRX_WAYBILL_NUMBER(i),
2473 l_sync_trx_lines_rec.TRX_LINE_DESCRIPTION(i),
2474 l_sync_trx_lines_rec.PRODUCT_DESCRIPTION(i),
2475 l_sync_trx_lines_rec.TRX_LINE_GL_DATE(i),
2476 l_sync_trx_lines_rec.MERCHANT_PARTY_NAME(i),
2477 l_sync_trx_lines_rec.MERCHANT_PARTY_DOCUMENT_NUMBER(i),
2478 l_sync_trx_lines_rec.MERCHANT_PARTY_REFERENCE(i),
2479 l_sync_trx_lines_rec.MERCHANT_PARTY_TAXPAYER_ID(i),
2480 l_sync_trx_lines_rec.MERCHANT_PARTY_TAX_REG_NUMBER(i),
2481 l_sync_trx_lines_rec.ASSET_NUMBER(i);
2482
2483 IF (sync_trx_lines_rec%NOTFOUND) THEN
2484 debug_info := 'Close cursor C - DATA NOTFOUND';
2485 CLOSE sync_trx_lines_rec;
2486 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
2487 APP_EXCEPTION.Raise_Exception;
2488 END IF;
2489
2490 debug_info := 'Close cursor sync_trx_lines_rec';
2491 CLOSE sync_trx_lines_rec;
2492
2493 END IF;
2494
2495 BEGIN
2496
2497 IF p_status = 'SIMULATOR_CONDITIONS' THEN
2498 IF (g_level_procedure >= g_current_runtime_level ) THEN
2499
2500 FND_LOG.STRING(g_level_procedure,
2501 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2502 'API ZX_SIM_CONDITIONS_PKG.create_sim_conditions (+)');
2503 END IF;
2504
2505 ZX_SIM_CONDITIONS_PKG.create_sim_conditions(p_return_status => l_return_status,
2506 p_error_buffer => l_msg_data);
2507
2508 IF (g_level_procedure >= g_current_runtime_level ) THEN
2509
2510 FND_LOG.STRING(g_level_procedure,
2511 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2512 'API ZX_SIM_CONDITIONS_PKG.create_sim_conditions (-)');
2513 END IF;
2514
2515
2516 ELSIF p_event_type_code = 'STANDARD UPDATED' AND
2517 p_line_level_action = 'SYNCHRONIZE' THEN
2518
2519 IF (g_level_procedure >= g_current_runtime_level ) THEN
2520 FND_LOG.STRING(g_level_procedure,
2521 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2522 'API zx_api_pub.synchronize_tax_repository (+)');
2523 END IF;
2524
2525 BEGIN
2526 SELECT COUNT(*)
2527 INTO l_trx_lines_sync
2528 FROM ZX_TRANSACTION_LINES
2529 WHERE LINE_LEVEL_ACTION = 'SYNCHORONIZE'
2530 AND APPLICATION_ID = p_application_id
2531 AND ENTITY_CODE = p_entity_code
2532 AND EVENT_CLASS_CODE = p_event_class_code
2533 AND TRX_ID = p_trx_id;
2534
2535 SELECT COUNT(*)
2536 INTO l_trx_lines
2537 FROM ZX_TRANSACTION_LINES
2538 WHERE APPLICATION_ID = p_application_id
2539 AND ENTITY_CODE = p_entity_code
2540 AND EVENT_CLASS_CODE = p_event_class_code
2541 AND TRX_ID = p_trx_id;
2542
2543 IF l_trx_lines > l_trx_lines_sync and l_trx_lines_sync <> 0 THEN
2544
2545
2546 ZX_API_PUB.CALCULATE_TAX(p_api_version => 1.0,
2547 p_init_msg_list => FND_API.G_TRUE,
2548 p_commit => NULL,
2549 p_validation_level => NULL,
2550 x_return_status => l_return_status,
2551 x_msg_count => l_msg_count,
2552 x_msg_data => l_msg_data);
2553
2554 IF (g_level_procedure >= g_current_runtime_level ) THEN
2555 FND_LOG.STRING(g_level_procedure,
2556 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2557 'After calling ZX_API_PUB.CALCULATE_TAX from 1,' ||
2558 'l_return_status : ' || TO_CHAR(l_return_status) ||
2559 ' ' || 'l_msg_count :' || TO_CHAR(l_msg_count));
2560 END IF;
2561
2562 x_return_status := l_return_status;
2563
2564 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2565
2566 IF l_msg_count = 1 THEN
2567 x_msg_data := l_msg_data;
2568 ELSE
2569 -- need to fetch more messages
2570
2571 get_error_msg(
2572 p_trx_id,
2573 p_application_id,
2574 p_entity_code,
2575 p_event_class_code,
2576 l_return_status,
2577 x_msg_data);
2578 END IF;
2579 END IF;
2580
2581 mark_reporting_only_flag(
2582 p_trx_id,
2583 p_application_id,
2584 p_entity_code,
2585 p_event_class_code,
2586 l_return_status,
2587 l_error_buffer,
2588 'N');
2589
2590 ELSIF l_trx_lines = l_trx_lines_sync THEN
2591
2592 ZX_API_PUB.SYNCHRONIZE_TAX_REPOSITORY (p_api_version => 1.0,
2593 p_init_msg_list => FND_API.G_TRUE,
2594 p_commit => NULL,
2595 p_validation_level => NULL,
2596 x_return_status => l_return_status,
2597 x_msg_count => l_msg_count,
2598 x_msg_data => l_msg_data,
2599 p_sync_trx_rec => NULL,
2600 p_sync_trx_lines_tbl => NULL);
2601
2602 ZX_API_PUB.CALCULATE_TAX(p_api_version => 1.0,
2603 p_init_msg_list => FND_API.G_TRUE,
2604 p_commit => NULL,
2605 p_validation_level => NULL,
2606 x_return_status => l_return_status,
2607 x_msg_count => l_msg_count,
2608 x_msg_data => l_msg_data);
2609
2610
2611 IF (g_level_procedure >= g_current_runtime_level ) THEN
2612 FND_LOG.STRING(g_level_procedure,
2613 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2614 'After calling ZX_API_PUB.CALCULATE_TAX from 2, ' ||
2615 'l_return_status : ' || TO_CHAR(l_return_status) ||
2616 ' ' || 'l_msg_count :' || TO_CHAR(l_msg_count));
2617 END IF;
2618
2619 x_return_status := l_return_status;
2620
2621 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2622
2623 IF l_msg_count = 1 THEN
2624 x_msg_data := l_msg_data;
2625 ELSE
2626 -- need to fetch more messages
2627
2628 get_error_msg(
2629 p_trx_id,
2630 p_application_id,
2631 p_entity_code,
2632 p_event_class_code,
2633 l_return_status,
2634 x_msg_data);
2635 END IF;
2636 END IF;
2637
2638 END IF;
2639 END;
2640
2641 IF (g_level_procedure >= g_current_runtime_level ) THEN
2642 FND_LOG.STRING(g_level_procedure,
2643 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2644 'API zx_api_pub.synchronize_tax_repository (-)');
2645 END IF;
2646
2647 ELSE
2648
2649 IF (g_level_procedure >= g_current_runtime_level ) THEN
2650 FND_LOG.STRING(g_level_procedure,
2651 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2652 'API zx_api_pub.calculate_tax for CREATE (+)');
2653 END IF;
2654
2655
2656 ZX_API_PUB.CALCULATE_TAX(p_api_version => 1.0,
2657 p_init_msg_list => FND_API.G_TRUE,
2658 p_commit => NULL,
2659 p_validation_level => NULL,
2660 x_return_status => l_return_status,
2661 x_msg_count => l_msg_count,
2662 x_msg_data => l_msg_data);
2663
2664 IF (g_level_procedure >= g_current_runtime_level ) THEN
2665 FND_LOG.STRING(g_level_procedure,
2666 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2667 'After calling ZX_API_PUB.CALCULATE_TAX from 3,' ||
2668 'l_return_status : ' || TO_CHAR(l_return_status) ||
2669 ' ' || 'l_msg_count :' || TO_CHAR(l_msg_count));
2670 END IF;
2671
2672 x_return_status := l_return_status;
2673
2674 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2675
2676 IF l_msg_count = 1 THEN
2677 x_msg_data := l_msg_data;
2678 ELSE
2679 -- need to fetch more messages
2680
2681 get_error_msg(
2682 p_trx_id,
2683 p_application_id,
2684 p_entity_code,
2685 p_event_class_code,
2686 l_return_status,
2687 x_msg_data);
2688 END IF;
2689 END IF;
2690
2691 mark_reporting_only_flag(
2692 p_trx_id,
2693 p_application_id,
2694 p_entity_code,
2695 p_event_class_code,
2696 l_return_status,
2697 l_error_buffer,
2698 'N');
2699
2700 IF (g_level_procedure >= g_current_runtime_level ) THEN
2701 FND_LOG.STRING(g_level_procedure,
2702 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2703 'API zx_api_pub.calculate_tax for CREATE (-)');
2704 END IF;
2705
2706
2707 END IF;
2708
2709 IF l_return_status = 'S' THEN
2710
2711 IF (g_level_procedure >= g_current_runtime_level ) THEN
2712 FND_LOG.STRING(g_level_procedure,
2713 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2714 'Update_Transaction_Lines (+)');
2715 END IF;
2716
2717 IF (g_level_procedure >= g_current_runtime_level ) THEN
2718 FND_LOG.STRING(g_level_procedure,
2719 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2720 'Update_Transaction_Lines (-)');
2721 END IF;
2722
2723 END IF;
2724
2725 IF (g_level_procedure >= g_current_runtime_level ) THEN
2726 FND_LOG.STRING(g_level_procedure,
2727 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2728 'Return Status = ' || l_return_status);
2729 END IF;
2730 END;
2731
2732 IF (g_level_procedure >= g_current_runtime_level ) THEN
2733 FND_LOG.STRING(g_level_procedure,
2734 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table.END',
2735 'ZX_TRX_DETAIL: Insert_Temporary_Table (-)');
2736 END IF;
2737
2738 EXCEPTION
2739 WHEN OTHERS THEN
2740 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2741 l_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2742
2743 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
2744 FND_MSG_PUB.Add;
2745
2746 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2747 FND_LOG.STRING(g_level_unexpected,
2748 'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
2749 l_error_buffer);
2750 END IF;
2751 END Insert_Temporary_Table;
2752
2753 /******************************************************
2754 PROCEDURE Update_Transaction_Lines
2755 (p_application_id NUMBER,
2756 p_entity_code VARCHAR2,
2757 p_event_class_code VARCHAR2,
2758 p_trx_id NUMBER) IS
2759
2760 l_return_status VARCHAR2(1000);
2761 l_msg_count NUMBER;
2762 l_msg_data VARCHAR2(1000);
2763 sid NUMBER;
2764 p_error_buffer VARCHAR2(100);
2765 l_tax_event_type_code VARCHAR2(30);
2766 debug_info VARCHAR2(100);
2767
2768 CURSOR TRX_LINE_TAX IS
2769 SELECT TLS.TRX_LINE_ID,
2770 TLS.TRX_LINE_NUMBER
2771 FROM ZX_TRANSACTION_LINES TLS
2772 WHERE TLS.APPLICATION_ID = p_application_id
2773 AND TLS.ENTITY_CODE = p_entity_code
2774 AND TLS.EVENT_CLASS_CODE = p_event_class_code
2775 AND TLS.TRX_ID = p_trx_id
2776 AND TLS.TRX_LINE_TYPE = 'TAX';
2777
2778 Recinfo TRX_LINE_TAX%ROWTYPE;
2779
2780 BEGIN
2781
2782 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2783 IF (g_level_procedure >= g_current_runtime_level ) THEN
2784 FND_LOG.STRING(g_level_procedure,
2785 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines.BEGIN',
2786 'ZX_TRX_DETAIL: Update_Transaction_Lines (+)');
2787 END IF;
2788
2789 debug_info := 'Open cursor TRX_LINE_TAX';
2790 OPEN TRX_LINE_TAX;
2791 debug_info := 'Fetch cursor TRX_LINE_TAX';
2792
2793 IF (g_level_procedure >= g_current_runtime_level ) THEN
2794 FND_LOG.STRING(g_level_procedure,
2795 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
2796 'Update ZX_TRANSACTION_LINES (+)');
2797 END IF;
2798
2799 IF (g_level_procedure >= g_current_runtime_level ) THEN
2800 FND_LOG.STRING(g_level_procedure,
2801 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
2802 'Delete ZX_TRANSACTION_LINES (+)');
2803 END IF;
2804
2805 DELETE FROM ZX_TRANSACTION_LINES
2806 WHERE APPLICATION_ID = p_application_id
2807 AND ENTITY_CODE = p_entity_code
2808 AND EVENT_CLASS_CODE = p_event_class_code
2809 AND TRX_ID = p_trx_id
2810 AND TRX_LINE_TYPE = 'TAX';
2811
2812 --commit;
2813
2814 IF (g_level_procedure >= g_current_runtime_level ) THEN
2815 FND_LOG.STRING(g_level_procedure,
2816 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
2817 'Delete ZX_TRANSACTION_LINES (-)');
2818 END IF;
2819
2820 LOOP
2821
2822 FETCH TRX_LINE_TAX INTO Recinfo;
2823
2824 IF (TRX_LINE_TAX%NOTFOUND) THEN
2825 debug_info := 'Close cursor TRX_LINE_TAX - DATA NOTFOUND';
2826 CLOSE TRX_LINE_TAX;
2827 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
2828 APP_EXCEPTION.Raise_Exception;
2829 END IF;
2830
2831 IF (g_level_procedure >= g_current_runtime_level ) THEN
2832 FND_LOG.STRING(g_level_procedure,
2833 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
2834 'Insert ZX_TRANSACTION_LINES (+)');
2835 END IF;
2836
2837 INSERT INTO ZX_TRANSACTION_LINES (APPLICATION_ID,
2838 ENTITY_CODE,
2839 EVENT_CLASS_CODE,
2840 TRX_LINE_ID,
2841 TRX_LINE_NUMBER,
2842 TRX_ID,
2843 TRX_LEVEL_TYPE,
2844 TRX_LINE_TYPE,
2845 TRX_LINE_DATE,
2846 HISTORICAL_FLAG,
2847 TAX_REGIME_ID,
2848 TAX_REGIME_CODE,
2849 TAX_ID,
2850 TAX,
2851 TAX_STATUS_ID,
2852 TAX_STATUS_CODE,
2853 TAX_RATE_ID,
2854 TAX_RATE_CODE,
2855 TAX_RATE,
2856 TAX_LINE_AMT)
2857 SELECT APPLICATION_ID,
2858 ENTITY_CODE,
2859 EVENT_CLASS_CODE,
2860 Recinfo.TRX_LINE_ID,
2861 Recinfo.TRX_LINE_NUMBER,
2862 TRX_ID,
2863 TRX_LEVEL_TYPE,
2864 'TAX' TRX_LINE_TYPE,
2865 TRX_LINE_DATE,
2866 HISTORICAL_FLAG,
2867 TAX_REGIME_ID,
2868 TAX_REGIME_CODE,
2869 TAX_ID,
2870 TAX,
2871 TAX_STATUS_ID,
2872 TAX_STATUS_CODE,
2873 TAX_RATE_ID,
2874 TAX_RATE_CODE,
2875 TAX_RATE,
2876 LINE_AMT
2877 FROM ZX_LINES
2878 WHERE APPLICATION_ID = p_application_id
2879 AND ENTITY_CODE = p_entity_code
2880 AND EVENT_CLASS_CODE = p_event_class_code
2881 AND TRX_ID = p_trx_id;
2882 END LOOP;
2883
2884 debug_info := 'Close cursor TRX_LINE_TAX';
2885 CLOSE TRX_LINE_TAX;
2886
2887 IF (g_level_procedure >= g_current_runtime_level ) THEN
2888 FND_LOG.STRING(g_level_procedure,
2889 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
2890 'Insert ZX_TRANSACTION_LINES (-)');
2891 END IF;
2892
2893 IF (g_level_procedure >= g_current_runtime_level ) THEN
2894 FND_LOG.STRING(g_level_procedure,
2895 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
2896 'Update ZX_TRANSACTION_LINES (-)');
2897 FND_LOG.STRING(g_level_procedure,
2898 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines.END',
2899 'ZX_TRX_DETAIL: Update_Transaction_Lines (-)');
2900 END IF;
2901
2902 EXCEPTION
2903 WHEN OTHERS THEN
2904 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2905 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2906
2907 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
2908 FND_MSG_PUB.Add;
2909
2910 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2911 FND_LOG.STRING(g_level_unexpected,
2912 'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
2913 p_error_buffer);
2914 END IF;
2915 END Update_Transaction_Lines;
2916 ************************************************************/
2917
2918
2919 -----------------------------------------------------------------------
2920 -- PUBLIC PROCEDURE
2921 -- mark_reporting_only_flag
2922 --
2923 -- DESCRIPTION
2924 --
2925 -- This procedure marks the reporting_only_flag to 'N' or 'Y' so
2926 -- these lines will be reported based on the flag value.
2927 --
2928 --
2929
2930 PROCEDURE mark_reporting_only_flag(
2931 p_trx_id IN NUMBER,
2932 p_application_id IN NUMBER,
2933 p_entity_code IN VARCHAR2,
2934 p_event_class_code IN VARCHAR2,
2935 p_return_status OUT NOCOPY VARCHAR2,
2936 p_error_buffer OUT NOCOPY VARCHAR2,
2937 p_reporting_flag IN VARCHAR2)
2938 IS
2939
2940 BEGIN
2941
2942 IF (g_level_statement >= g_current_runtime_level ) THEN
2943 FND_LOG.STRING(g_level_statement,
2944 'ZX.PLSQL.ZX_TRX_DETAIL.mark_reporting_only_flag.BEGIN',
2945 'ZX_TRX_DETAIL: mark_reporting_only_flag(+)');
2946 END IF;
2947
2948 p_return_status := FND_API.G_RET_STS_SUCCESS;
2949
2950 UPDATE ZX_LINES
2951 SET
2952 REPORTING_ONLY_FLAG = p_reporting_flag ,
2953 LAST_UPDATED_BY = fnd_global.user_id ,
2954 LAST_UPDATE_DATE = SYSDATE ,
2955 LAST_UPDATE_LOGIN = fnd_global.conc_login_id ,
2956 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
2957 WHERE APPLICATION_ID = p_application_id
2958 AND ENTITY_CODE = p_entity_code
2959 AND EVENT_CLASS_CODE = p_event_class_code
2960 AND TRX_ID = p_trx_id;
2961
2962 IF (p_reporting_flag= 'N' ) THEN
2963
2964 UPDATE ZX_LINES_SUMMARY
2965 SET REPORTING_ONLY_FLAG = 'N'
2966 WHERE APPLICATION_ID = p_application_id
2967 AND ENTITY_CODE = p_entity_code
2968 AND EVENT_CLASS_CODE = p_event_class_code
2969 AND TRX_ID = p_trx_id;
2970
2971 END IF;
2972
2973 IF (g_level_procedure >= g_current_runtime_level ) THEN
2974 FND_LOG.STRING(g_level_procedure,
2975 'ZX.PLSQL.ZX_TRX_DETAIL.mark_reporting_only_flag.END',
2976 'ZX_TRX_DETAIL: mark_reporting_only_flag (-)');
2977 END IF;
2978
2979 EXCEPTION
2980 WHEN OTHERS THEN
2981 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2982 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2983 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
2984 FND_MSG_PUB.Add;
2985
2986 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2987 FND_LOG.STRING(g_level_unexpected,
2988 'ZX.PLSQL.ZX_TRX_DETAIL.mark_reporting_only_flag',
2989 p_error_buffer);
2990 END IF;
2991
2992 END mark_reporting_only_flag;
2993
2994 -----------------------------------------------------------------------
2995 -- PRIVATE PROCEDURE
2996 -- perform_purge
2997 --
2998 -- DESCRIPTION
2999 --
3000 -- This procedure deletes all tax lines related created by
3001 -- Tax Simulator.
3002 --
3003 --
3004 PROCEDURE perform_purge(
3005 p_count IN NUMBER,
3006 p_application_id_tbl IN APPLICATION_ID_TBL,
3007 p_entity_code_tbl IN ENTITY_CODE_TBL,
3008 p_event_class_code_tbl IN EVENT_CLASS_CODE_TBL,
3009 p_trx_id_tbl IN TRX_ID_TBL,
3010 p_return_status OUT NOCOPY VARCHAR2,
3011 p_error_buffer OUT NOCOPY VARCHAR2)
3012 IS
3013 BEGIN
3014
3015 IF (g_level_statement >= g_current_runtime_level ) THEN
3016 FND_LOG.STRING(g_level_statement,
3017 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge.BEGIN',
3018 'ZX_TRX_DETAIL: perform_purge(+)');
3019 END IF;
3020
3021 p_return_status := FND_API.G_RET_STS_SUCCESS;
3022
3023 IF (g_level_statement >= g_current_runtime_level ) THEN
3024 FND_LOG.STRING(g_level_statement,
3025 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge',
3026 'Deleting rows from ZX_LINES');
3027 END IF;
3028
3029 FORALL i IN 1 .. p_count
3030 DELETE ZX_LINES
3031 WHERE APPLICATION_ID = p_application_id_tbl(i)
3032 AND ENTITY_CODE = p_entity_code_tbl(i)
3033 AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
3034 AND TRX_ID = p_trx_id_tbl(i);
3035
3036 IF (g_level_statement >= g_current_runtime_level ) THEN
3037 FND_LOG.STRING(g_level_statement,
3038 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge',
3039 'Deleting rows from ZX_LINES_SUMMARY');
3040 END IF;
3041
3042 FORALL i IN 1 .. p_count
3043 DELETE ZX_LINES_SUMMARY
3044 WHERE APPLICATION_ID = p_application_id_tbl(i)
3045 AND ENTITY_CODE = p_entity_code_tbl(i)
3046 AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
3047 AND TRX_ID = p_trx_id_tbl(i);
3048
3049 IF (g_level_statement >= g_current_runtime_level ) THEN
3050 FND_LOG.STRING(g_level_statement,
3051 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge',
3052 'Deleting rows from ZX_REC_NREC_DIST');
3053 END IF;
3054
3055 FORALL i IN 1 .. p_count
3056 DELETE ZX_REC_NREC_DIST
3057 WHERE APPLICATION_ID = p_application_id_tbl(i)
3058 AND ENTITY_CODE = p_entity_code_tbl(i)
3059 AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
3060 AND TRX_ID = p_trx_id_tbl(i);
3061
3062 IF (g_level_statement >= g_current_runtime_level ) THEN
3063 FND_LOG.STRING(g_level_statement,
3064 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge',
3065 'Deleting rows from ZX_LINES_DET_FACTORS');
3066 END IF;
3067
3068 FORALL i IN 1 .. p_count
3069 DELETE ZX_LINES_DET_FACTORS
3070 WHERE APPLICATION_ID = p_application_id_tbl(i)
3071 AND ENTITY_CODE = p_entity_code_tbl(i)
3072 AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
3073 AND TRX_ID = p_trx_id_tbl(i);
3074
3075 IF (g_level_statement >= g_current_runtime_level ) THEN
3076 FND_LOG.STRING(g_level_statement,
3077 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge',
3078 'Deleting rows from ZX_TRANSACTION');
3079 END IF;
3080
3081 FORALL i IN 1 .. p_count
3082 DELETE ZX_TRANSACTION
3083 WHERE APPLICATION_ID = p_application_id_tbl(i)
3084 AND ENTITY_CODE = p_entity_code_tbl(i)
3085 AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
3086 AND TRX_ID = p_trx_id_tbl(i);
3087
3088 IF (g_level_statement >= g_current_runtime_level ) THEN
3089 FND_LOG.STRING(g_level_statement,
3090 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge',
3091 'Deleting rows from ZX_TRANSACTION_LINES');
3092 END IF;
3093
3094 FORALL i IN 1 .. p_count
3095 DELETE ZX_TRANSACTION_LINES
3096 WHERE APPLICATION_ID = p_application_id_tbl(i)
3097 AND ENTITY_CODE = p_entity_code_tbl(i)
3098 AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
3099 AND TRX_ID = p_trx_id_tbl(i);
3100
3101 IF (g_level_statement >= g_current_runtime_level ) THEN
3102 FND_LOG.STRING(g_level_statement,
3103 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge',
3104 'Deleting rows from ZX_SIM_TRX_DISTS');
3105 END IF;
3106
3107 FORALL i IN 1 .. p_count
3108 DELETE ZX_SIM_TRX_DISTS
3109 WHERE APPLICATION_ID = p_application_id_tbl(i)
3110 AND ENTITY_CODE = p_entity_code_tbl(i)
3111 AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
3112 AND TRX_ID = p_trx_id_tbl(i);
3113
3114 IF (g_level_procedure >= g_current_runtime_level ) THEN
3115 FND_LOG.STRING(g_level_procedure,
3116 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge.END',
3117 'ZX_TRX_DETAIL: perform_purge (-)');
3118 END IF;
3119
3120 EXCEPTION
3121 WHEN OTHERS THEN
3122 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3123 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3124 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
3125 FND_MSG_PUB.Add;
3126
3127 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3128 FND_LOG.STRING(g_level_unexpected,
3129 'ZX.PLSQL.ZX_TRX_DETAIL.perform_purge',
3130 p_error_buffer);
3131 END IF;
3132
3133 END perform_purge;
3134
3135 -----------------------------------------------------------------------
3136 -- PUBLIC PROCEDURE
3137 -- process_purge
3138 --
3139 -- DESCRIPTION
3140 --
3141 -- This procedure purges all tax lines related data
3142 -- based on the transaction id passed in
3143 --
3144 --
3145 PROCEDURE process_purge(
3146 p_trx_id NUMBER)
3147
3148 IS
3149 l_application_id_tbl APPLICATION_ID_TBL;
3150 l_entity_code_tbl ENTITY_CODE_TBL;
3151 l_event_class_code_tbl EVENT_CLASS_CODE_TBL;
3152 l_trx_id_tbl TRX_ID_TBL;
3153 l_count NUMBER;
3154 l_return_status VARCHAR2(30);
3155 l_error_buffer VARCHAR2(256);
3156
3157 CURSOR get_purge_single_csr
3158 (c_trx_id NUMBER)
3159 IS
3160 SELECT APPLICATION_ID,
3161 ENTITY_CODE,
3162 EVENT_CLASS_CODE,
3163 TRX_ID
3164 FROM ZX_SIM_PURGE
3165 WHERE TRX_ID = c_trx_id;
3166
3167 BEGIN
3168
3169 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3170
3171 IF (g_level_statement >= g_current_runtime_level ) THEN
3172 FND_LOG.STRING(g_level_statement,
3173 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge.BEGIN',
3174 'ZX_TRX_DETAIL: process_purge(+)');
3175 FND_LOG.STRING(g_level_statement,
3176 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge',
3177 'p_trx_id = ' || TO_CHAR(p_trx_id));
3178 END IF;
3179
3180 l_return_status := FND_API.G_RET_STS_SUCCESS;
3181
3182 OPEN get_purge_single_csr(p_trx_id);
3183 FETCH get_purge_single_csr INTO
3184 l_application_id_tbl(1),
3185 l_entity_code_tbl(1),
3186 l_event_class_code_tbl(1),
3187 l_trx_id_tbl(1);
3188 CLOSE get_purge_single_csr;
3189
3190 l_count := 1;
3191 perform_purge(
3192 l_count,
3193 l_application_id_tbl,
3194 l_entity_code_tbl,
3195 l_event_class_code_tbl,
3196 l_trx_id_tbl,
3197 l_return_status,
3198 l_error_buffer);
3199
3200 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3201 --
3202 -- delete row from ZX_SIM_PURGE table
3203 --
3204 IF (g_level_procedure >= g_current_runtime_level ) THEN
3205 FND_LOG.STRING(g_level_procedure,
3206 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge',
3207 'Deleting from ZX_SIM_PURGE table');
3208 END IF;
3209
3210 DELETE ZX_SIM_PURGE
3211 WHERE TRX_ID = p_trx_id;
3212 END IF;
3213
3214 IF (g_level_procedure >= g_current_runtime_level ) THEN
3215 FND_LOG.STRING(g_level_procedure,
3216 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge.END',
3217 'ZX_TRX_DETAIL: process_purge (-)');
3218 END IF;
3219
3220 EXCEPTION
3221 WHEN OTHERS THEN
3222 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3223 l_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3224 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
3225 FND_MSG_PUB.Add;
3226
3227 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3228 FND_LOG.STRING(g_level_unexpected,
3229 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge',
3230 l_error_buffer);
3231 END IF;
3232
3233 END process_purge;
3234
3235 -----------------------------------------------------------------------
3236 -- PUBLIC PROCEDURE
3237 -- process_purge
3238 --
3239 -- DESCRIPTION
3240 --
3241 -- This procedure purges all tax lines related data
3242 -- created by Tax Simulator
3243 --
3244 --
3245 PROCEDURE process_purge (
3246 errbuf OUT NOCOPY varchar2,
3247 retcode OUT NOCOPY number )
3248 IS
3249 l_application_id_tbl APPLICATION_ID_TBL;
3250 l_entity_code_tbl ENTITY_CODE_TBL;
3251 l_event_class_code_tbl EVENT_CLASS_CODE_TBL;
3252 l_trx_id_tbl TRX_ID_TBL;
3253 l_count NUMBER;
3254 l_return_status VARCHAR2(30);
3255 l_error_buffer VARCHAR2(256);
3256
3257 CURSOR get_purge_all_csr
3258 IS
3259 SELECT APPLICATION_ID,
3260 ENTITY_CODE,
3261 EVENT_CLASS_CODE,
3262 TRX_ID
3263 FROM ZX_SIM_PURGE;
3264
3265 BEGIN
3266
3267 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3268
3269 IF (g_level_statement >= g_current_runtime_level ) THEN
3270 FND_LOG.STRING(g_level_statement,
3271 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge.BEGIN',
3272 'ZX_TRX_DETAIL: process_purge(+)');
3273 END IF;
3274
3275 l_return_status := FND_API.G_RET_STS_SUCCESS;
3276
3277 OPEN get_purge_all_csr;
3278 LOOP
3279 FETCH get_purge_all_csr BULK COLLECT INTO
3280 l_application_id_tbl,
3281 l_entity_code_tbl,
3282 l_event_class_code_tbl,
3283 l_trx_id_tbl
3284 LIMIT C_LINES_PER_COMMIT;
3285
3286 l_count := l_trx_id_tbl.COUNT;
3287
3288 IF (g_level_statement >= g_current_runtime_level ) THEN
3289 FND_LOG.STRING(g_level_statement,
3290 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge',
3291 'l_count = ' || TO_CHAR(l_count));
3292 END IF;
3293
3294 IF l_count > 0 THEN
3295
3296 perform_purge(
3297 l_count,
3298 l_application_id_tbl,
3299 l_entity_code_tbl,
3300 l_event_class_code_tbl,
3301 l_trx_id_tbl,
3302 l_return_status,
3303 l_error_buffer);
3304
3305 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3306 EXIT;
3307 END IF;
3308 ELSE
3309 --
3310 -- no more records to process
3311 --
3312 EXIT;
3313 END IF;
3314 END LOOP;
3315
3316 CLOSE get_purge_all_csr;
3317
3318 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3319 --
3320 -- delete ZX_SIM_PURGE table
3321 --
3322 IF (g_level_procedure >= g_current_runtime_level ) THEN
3323 FND_LOG.STRING(g_level_procedure,
3324 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge',
3325 'Deleting ZX_SIM_PURGE table');
3326 END IF;
3327
3328 DELETE ZX_SIM_PURGE;
3329
3330 END IF;
3331
3332 IF (g_level_procedure >= g_current_runtime_level ) THEN
3333 FND_LOG.STRING(g_level_procedure,
3334 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge.END',
3335 'ZX_TRX_DETAIL: process_purge (-)');
3336 END IF;
3337
3338 EXCEPTION
3339 WHEN OTHERS THEN
3340 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3341 l_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3342 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
3343 FND_MSG_PUB.Add;
3344
3345 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3346 FND_LOG.STRING(g_level_unexpected,
3347 'ZX.PLSQL.ZX_TRX_DETAIL.process_purge',
3348 l_error_buffer);
3349 END IF;
3350
3351 END process_purge;
3352
3353 -----------------------------------------------------------------------
3354 -- PUBLIC PROCEDURE
3355 -- process_import_doc_with_tax
3356 --
3357 -- DESCRIPTION
3358 --
3359 -- This procedure processes import document with tax
3360 --
3361 --
3362 PROCEDURE process_import_doc_with_tax
3363 (p_application_id NUMBER,
3364 p_entity_code VARCHAR2,
3365 p_event_class_code VARCHAR2,
3366 p_trx_id NUMBER)
3367 IS
3368 l_msg_count NUMBER;
3369 l_return_status VARCHAR2(30);
3370 l_error_buffer VARCHAR2(256);
3371 l_msg_data VARCHAR2(1000);
3372
3373 BEGIN
3374 IF (g_level_procedure >= g_current_runtime_level ) THEN
3375 FND_LOG.STRING(g_level_procedure,
3376 'ZX.PLSQL.ZX_TRX_DETAIL.process_import_doc_with_tax.BEGIN',
3377 'ZX_TRX_DETAIL: process_import_doc_with_tax(+)');
3378 FND_LOG.STRING(g_level_procedure,
3379 'ZX.PLSQL.ZX_TRX_DETAIL.process_import_doc_with_tax',
3380 'Inserting into ZX_IMPORT_TAX_LINES_GT (+)');
3381 END IF;
3382
3383 INSERT INTO ZX_IMPORT_TAX_LINES_GT (
3384 SUMMARY_TAX_LINE_NUMBER,
3385 INTERNAL_ORGANIZATION_ID,
3386 APPLICATION_ID,
3387 ENTITY_CODE,
3388 EVENT_CLASS_CODE,
3389 TRX_ID,
3390 TAX_LINE_ALLOCATION_FLAG,
3391 TAX_REGIME_CODE,
3392 TAX,
3393 TAX_STATUS_CODE,
3394 TAX_RATE_CODE,
3395 TAX_RATE,
3396 TAX_AMT)
3397 SELECT TRL.TRX_LINE_NUMBER,
3398 TRX.INTERNAL_ORGANIZATION_ID,
3399 TRL.APPLICATION_ID,
3400 TRL.ENTITY_CODE,
3401 TRL.EVENT_CLASS_CODE,
3402 TRL.TRX_ID,
3403 'N',
3404 TRL.TAX_REGIME_CODE,
3405 TRL.TAX,
3406 TRL.TAX_STATUS_CODE,
3407 TRL.TAX_RATE_CODE,
3408 TRL.TAX_RATE,
3409 TRL.TAX_LINE_AMT
3410 FROM ZX_TRANSACTION_LINES TRL,
3411 ZX_TRANSACTION TRX
3412 WHERE TRL.TRX_ID = TRX.trx_id
3413 AND TRL.APPLICATION_ID = p_application_id
3414 AND TRL.ENTITY_CODE = p_entity_code
3415 AND TRL.EVENT_CLASS_CODE = p_event_class_code
3416 AND TRL.TRX_ID = p_trx_id
3417 AND TRL.TRX_LINE_TYPE = 'TAX';
3418
3419 IF (g_level_procedure >= g_current_runtime_level ) THEN
3420
3421 FND_LOG.STRING(g_level_procedure,
3422 'ZX.PLSQL.ZX_TRX_DETAIL.process_import_doc_with_tax',
3423 'Inserted into ZX_IMPORT_TAX_LINES_GT (-)');
3424 FND_LOG.STRING(g_level_procedure,
3425 'ZX.PLSQL.ZX_TRX_DETAIL.process_import_doc_with_tax',
3426 'calling ZX_API_PUB.IMPORT_DOCUMENT_WITH_TAX (+)');
3427 END IF;
3428
3429
3430 ZX_API_PUB.IMPORT_DOCUMENT_WITH_TAX(
3431 p_api_version => 1.0,
3432 p_init_msg_list => NULL,
3433 p_commit => NULL,
3434 p_validation_level => NULL,
3435 x_return_status => l_return_status,
3436 x_msg_count => l_msg_count,
3437 x_msg_data => l_msg_data);
3438 IF (g_level_procedure >= g_current_runtime_level ) THEN
3439 FND_LOG.STRING(g_level_procedure,
3440 'ZX.PLSQL.ZX_TRX_DETAIL.process_import_doc_with_tax',
3441 'calling ZX_API_PUB.IMPORT_DOCUMENT_WITH_TAX (-)');
3442
3443 FND_LOG.STRING(g_level_procedure,
3444 'ZX.PLSQL.ZX_TRX_DETAIL.process_import_doc_with_tax.END',
3445 'ZX_TRX_DETAIL: process_import_doc_with_tax(-)');
3446 END IF;
3447
3448
3449 EXCEPTION
3450 WHEN OTHERS THEN
3451 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3452 l_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3453 FND_MESSAGE.SET_NAME('ZX','ZX_UNEXPECTED_ERROR');
3454 FND_MSG_PUB.Add;
3455
3456 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3457 FND_LOG.STRING(g_level_unexpected,
3458 'ZX.PLSQL.ZX_TRX_DETAIL.process_import_doc_with_tax',
3459 l_error_buffer);
3460 END IF;
3461
3462 END process_import_doc_with_tax;
3463
3464 -----------------------------------------------------------------------
3465 -- PRIVATE PROCEDURE
3466 -- get_error_msg
3467 --
3468 -- DESCRIPTION
3469 --
3470 -- This procedure gets all error messages returned from internal
3471 -- services during tax calculation proccess.
3472 --
3473 --
3474 PROCEDURE get_error_msg(
3475 p_trx_id IN NUMBER,
3476 p_application_id IN NUMBER,
3477 p_entity_code IN VARCHAR2,
3478 p_event_class_code IN VARCHAR2,
3479 p_return_status IN VARCHAR2,
3480 x_msg_data OUT NOCOPY VARCHAR2)
3481
3482 IS
3483
3484 l_count NUMBER;
3485 l_error_msg ZX_ERRORS_GT.message_text%TYPE;
3486
3487 TYPE msg_string_tbl IS TABLE OF
3488 ZX_ERRORS_GT.message_text%TYPE
3489 INDEX BY BINARY_INTEGER;
3490
3491 l_msg_string_tbl MSG_STRING_TBL;
3492
3493 CURSOR c_get_error_msg(
3494 c_trx_id NUMBER,
3495 c_event_class_code VARCHAR2,
3496 c_application_id NUMBER,
3497 c_entity_code VARCHAR2)
3498 IS
3499 SELECT message_text
3500 FROM ZX_ERRORS_GT
3501 WHERE trx_id = c_trx_id
3502 AND event_class_code = c_event_class_code
3503 AND application_id = c_application_id
3504 AND entity_code = c_entity_code;
3505
3506
3507 BEGIN
3508 IF (g_level_procedure >= g_current_runtime_level ) THEN
3509 FND_LOG.STRING(g_level_procedure,
3510 'ZX.PLSQL.ZX_TRX_DETAIL.get_error_msg.BEGIN',
3511 'ZX_TRX_DETAIL: get_error_msg(+)');
3512 END IF;
3513
3514 x_msg_data := NULL;
3515
3516 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3517 LOOP
3518 l_error_msg := FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT,
3519 FND_API.G_FALSE);
3520
3521 IF l_error_msg IS NULL THEN
3522 EXIT;
3523 ELSE
3524 x_msg_data := x_msg_data ||'. '|| l_error_msg;
3525 END IF;
3526 END LOOP;
3527 ELSIF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3528 --
3529 -- should be FND_API.G_RET_STS_ERROR but
3530 -- currently many are set at wrong
3531 -- status of FND_API.G_RET_STS_UNEXP_ERROR
3532 --
3533 --
3534 -- need to fetch error messages from zx_errors_gt
3535 --
3536 OPEN c_get_error_msg(
3537 p_trx_id,
3538 p_event_class_code,
3539 p_application_id,
3540 p_entity_code);
3541 LOOP
3542 FETCH c_get_error_msg BULK COLLECT
3543 INTO l_msg_string_tbl
3544 LIMIT C_LINES_PER_COMMIT;
3545
3546 l_count := l_msg_string_tbl.COUNT;
3547
3548 IF (g_level_procedure >= g_current_runtime_level ) THEN
3549 FND_LOG.STRING(g_level_procedure,
3550 'ZX.PLSQL.ZX_TRX_DETAIL.get_error_msg',
3551 'Count in zx_errors_gt: ' ||
3552 TO_CHAR(l_count));
3553 END IF;
3554
3555 IF l_count > 0 THEN
3556 FOR i IN 1.. l_count LOOP
3557 x_msg_data := x_msg_data || '. ' ||
3558 l_msg_string_tbl(i);
3559 END LOOP;
3560 ELSE
3561 --
3562 -- no more records to process
3563 --
3564 CLOSE c_get_error_msg;
3565 EXIT;
3566 END IF;
3567 END LOOP;
3568 END IF;
3569
3570 IF (g_level_procedure >= g_current_runtime_level ) THEN
3571 FND_LOG.STRING(g_level_procedure,
3572 'ZX.PLSQL.ZX_TRX_DETAIL.get_error_msg.END',
3573 'ZX_TRX_DETAIL: get_error_msg(-)');
3574 END IF;
3575
3576 EXCEPTION
3577 WHEN OTHERS THEN
3578 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3579 FND_LOG.STRING(g_level_unexpected,
3580 'ZX.PLSQL.ZX_TRX_DETAIL.get_error_msg',
3581 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
3582 END IF;
3583
3584 END get_error_msg;
3585
3586
3587 END ZX_TRX_DETAIL;