[Home] [Help]
PACKAGE BODY: APPS.ZX_CORE_REP_EXTRACT_PKG
Source
1 PACKAGE BODY ZX_CORE_REP_EXTRACT_PKG AS
2 /* $Header: zxricoreplugpvtb.pls 120.70.12020000.8 2013/02/11 07:19:35 msakalab ship $ */
3
4
5 -----------------------------------------
6 --Private Methods Declarations
7 -----------------------------------------
8
9 TYPE ATTRIBUTE1_TBL IS TABLE OF
10 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
11
12 TYPE ATTRIBUTE2_TBL IS TABLE OF
13 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
14
15 TYPE ATTRIBUTE3_TBL IS TABLE OF
16 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE3%TYPE INDEX BY BINARY_INTEGER;
17
18 TYPE ATTRIBUTE4_TBL IS TABLE OF
19 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
20
21 TYPE ATTRIBUTE5_TBL IS TABLE OF
22 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
23
24 TYPE ATTRIBUTE6_TBL IS TABLE OF
25 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
26
27 TYPE ATTRIBUTE7_TBL IS TABLE OF
28 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE7%TYPE INDEX BY BINARY_INTEGER;
29
30 TYPE ATTRIBUTE10_TBL IS TABLE OF
31 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE10%TYPE INDEX BY BINARY_INTEGER;
32
33 TYPE ATTRIBUTE11_TBL IS TABLE OF
34 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE11%TYPE INDEX BY BINARY_INTEGER;
35
36 --Bug 5251425
37 TYPE TRX_CURRENCY_DESC_TBL IS TABLE OF
38 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE9%TYPE INDEX BY BINARY_INTEGER;
39
40 TYPE batch_name_tbl IS TABLE OF
41 ZX_REP_TRX_JX_EXT_T.ATTRIBUTE8%TYPE INDEX BY BINARY_INTEGER;
42
43 TYPE CCID_TBL IS TABLE OF
44 ZX_REP_ACTG_EXT_T.ACTG_LINE_CCID%TYPE INDEX BY BINARY_INTEGER;
45
46 TYPE NUMBER_TBL is table of number index by binary_integer;
47
48 TYPE NUMERIC1_TBL IS TABLE OF
49 ZX_REP_TRX_JX_EXT_T.NUMERIC1%TYPE INDEX BY BINARY_INTEGER;
50
51 TYPE NUMERIC2_TBL IS TABLE OF
52 ZX_REP_TRX_JX_EXT_T.NUMERIC2%TYPE INDEX BY BINARY_INTEGER;
53
54 --Bug 9031051
55 TYPE NUMERIC5_TBL IS TABLE OF
56 ZX_REP_TRX_JX_EXT_T.NUMERIC5%TYPE INDEX BY BINARY_INTEGER;
57
58 TYPE ACC_CCID_TBL IS TABLE OF ZX_ACCOUNTS.TAX_ACCOUNT_CCID%TYPE;
59
60 -----------------------------------------
61 --Private Type
62
63 ----------------------------------------
64 --
65 PROCEDURE get_org_vat_num (
66 --p_report_name IN VARCHAR2,
67 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
68 -- p_request_id IN NUMBER,
69 p_establishment_id IN NUMBER,
70 p_org_vat_num_tbl OUT NOCOPY ATTRIBUTE2_TBL);
71
72 PROCEDURE get_territory_info(
73 --p_report_name IN VARCHAR2,
74 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
75 p_country_code_tbl IN ZX_EXTRACT_PKG.billing_tp_country_tbl,
76 p_territory_short_name_tbl OUT NOCOPY ATTRIBUTE2_TBL,
77 p_alternate_territory_name_tbl OUT NOCOPY ATTRIBUTE3_TBL);
78
79 PROCEDURE adjustment_tax_code(
80 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
81 p_tax_rate_code_tbl IN ZX_EXTRACT_PKG.tax_rate_code_tbl,
82 p_adj_tax_code_tbl OUT NOCOPY ATTRIBUTE1_TBL);
83
84 --Private Procedures Included for the Bug 5251425
85 PROCEDURE GET_CREATED_BY
86 (
87 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
88 p_trx_id_tbl IN ZX_EXTRACT_PKG.trx_id_tbl,
89 p_created_by_tbl OUT NOCOPY ATTRIBUTE6_TBL
90 ) ;
91
92 PROCEDURE GET_OU_DESC
93 (
94 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
95 p_internal_organization_id_tbl IN ZX_EXTRACT_PKG.internal_organization_id_tbl,
96 p_ou_desc_tbl OUT NOCOPY ATTRIBUTE7_TBL
97 ) ;
98
99 PROCEDURE GET_MATCH
100 (
101 p_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
102 p_acc_ccid_tbl ACC_CCID_TBL ,
103 p_match_tbl OUT NOCOPY ATTRIBUTE5_TBL
104 );
105
106 PROCEDURE GET_RECEIVED_AMOUNTS
107 (
108 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
109 p_trx_id_tbl IN ZX_EXTRACT_PKG.trx_id_tbl,
110 p_org_id_tbl IN zx_extract_pkg.INTERNAL_ORGANIZATION_ID_TBL ,
111 p_amount_received_tbl OUT NOCOPY NUMERIC2_TBL,
112 p_tax_received_tbl OUT NOCOPY NUMERIC1_TBL
113 );
114
115 -- Declare global varibles for FND log messages
116
117 g_current_runtime_level NUMBER;
118 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
119 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
120 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
121 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
122 g_error_buffer VARCHAR2(100);
123
124 -- Public APIs
125
126 PROCEDURE populate_core_ap(
127 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
128 )
129 IS
130
131 p_request_id NUMBER;
132 p_report_name VARCHAR2(30);
133 p_legal_entity_id NUMBER;
134 p_product VARCHAR2(30);
135 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
136 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
137 l_country_code_tbl ZX_EXTRACT_PKG.BILLING_TP_COUNTRY_TBL;
138 l_country_code_reg_num_tbl ATTRIBUTE1_TBL;
139 l_org_vat_num_tbl ATTRIBUTE2_TBL;
140 l_territory_short_name_tbl ATTRIBUTE2_TBL;
141 l_alternate_territory_name_tbl ATTRIBUTE3_TBL;
142 l_establishment_id NUMBER;
143 l_tax_rate_code_tbl ZX_EXTRACT_PKG.TAX_RATE_CODE_TBL;
144 l_adj_tax_code_tbl ATTRIBUTE1_TBL;
145
146 --Bug 5251425
147 l_trx_currency_code_tbl zx_extract_pkg.trx_currency_code_tbl;
148 l_trx_currency_desc_tbl trx_currency_desc_tbl;
149 l_batch_name_tbl batch_name_tbl;
150 l_err_msg varchar2(120);
151 l_created_by_tbl attribute6_tbl;
152 l_ou_desc_tbl attribute7_tbl;
153 l_internal_organization_id_tbl zx_extract_pkg.internal_organization_id_tbl;
154 l_acc_ccid_tbl acc_ccid_tbl;
155 l_match_tbl attribute5_tbl;
156 p_posting_status VARCHAR2(30);
157
158 CURSOR establishment_id_csr(c_legal_entity_id number) IS
159 SELECT xle_etb.establishment_id
160 FROM zx_party_tax_profile ptp,
161 xle_etb_profiles xle_etb
162 WHERE ptp.party_id = xle_etb.party_id
163 AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
164 AND xle_etb.legal_entity_id = c_legal_entity_id
165 AND xle_etb.main_establishment_flag = 'Y';
166
167 BEGIN
168
169 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
170 -- g_level_statement := FND_LOG.LEVEL_STATEMENT;
171 -- g_level_procedure := FND_LOG.LEVEL_PROCEDURE;
172
173 IF (g_level_procedure >= g_current_runtime_level ) THEN
174 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP.BEGIN',
175 'ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP(+)');
176 END IF;
177
178 p_request_id := P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
179 p_report_name := P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME;
180 p_legal_entity_id := P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID;
181 p_product := P_TRL_GLOBAL_VARIABLES_REC.PRODUCT;
182 P_POSTING_STATUS := P_TRL_GLOBAL_VARIABLES_REC.POSTING_STATUS;
183
184 IF (g_level_statement >= g_current_runtime_level ) THEN
185 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
186 'p_report_name : '||P_REPORT_NAME);
187 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
188 'p_request_id : '||p_request_id);
189 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
190 'p_legal_entity_id : '||p_legal_entity_id);
191 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
192 'p_product : '||p_product);
193 END IF;
194
195
196 IF P_REPORT_NAME = 'ZXXTATAT' THEN
197 BEGIN
198
199 UPDATE zx_rep_trx_detail_t temp
200 SET (temp.TAXABLE_AMT,temp.TAXABLE_AMT_FUNCL_CURR)
201 =
202 (SELECT
203 SUM(nvl(lnk.UNROUNDED_ENTERED_DR,0)- nvl(lnk.UNROUNDED_ENTERED_CR,0)
204 ) taxable_amt,
205 SUM(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0)- nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)
206 ) taxable_act_amt
207 FROM zx_rep_trx_detail_t dtl,
208 ap_prepay_app_dists pre_dist,
209 xla_distribution_links lnk,
210 xla_ae_headers aeh,
211 xla_ae_lines ael
212 WHERE dtl.detail_tax_line_id = temp.detail_tax_line_id
213 AND pre_dist.prepay_app_distribution_id = dtl.taxable_item_source_id
214 AND pre_dist.prepay_dist_lookup_code = 'PREPAY APPL'
215 AND lnk.application_id = 200
216 AND lnk.source_distribution_type = 'AP_PREPAY'
217 AND lnk.source_distribution_id_num_1 = pre_dist.prepay_app_dist_id
218 AND lnk.event_id = pre_dist.accounting_event_id
219 AND lnk.ae_line_num = ael.ae_line_num
220 AND aeh.ae_header_id = lnk.ae_header_id
221 AND aeh.ledger_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
222 AND aeh.balance_type_code = 'A'
223 AND aeh.application_id = lnk.application_id
224 AND ael.ae_header_id = aeh.ae_header_id
225 AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY','EXCHANGE_RATE_VARIANCE')
226 AND ael.application_id = aeh.application_id
227 )
228 WHERE temp.request_id = p_request_id
229 AND temp.trx_line_class = 'PREPAY_APPLICATION'
230 AND temp.extract_source_ledger = 'AP'
231 AND temp.entity_code = 'AP_INVOICES'
232 AND temp.application_id = 200
233 AND NOT EXISTS (select 1
234 from AP_INVOICE_DISTRIBUTIONS_ALL dist1
235 where dist1.invoice_distribution_id = temp.taxable_item_source_id
236 AND NVL(dist1.historical_flag,'N') = 'Y');
237
238 IF (g_level_statement >= g_current_runtime_level ) THEN
239 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
240 'Updated Taxable Amount for prepayment Applications for '||to_char(SQL%ROWCOUNT)||' records.');
241 END IF;
242
243 -- Upgraded prepay applications new code replaces SLA based queries --
244
245 IF P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEDGER_ID IS NULL THEN
246 UPDATE zx_rep_trx_detail_t dtl
247 SET (dtl.TAXABLE_AMT,dtl.TAXABLE_AMT_FUNCL_CURR)
248 = (SELECT dist.amount, nvl(dist.base_amount,dist.amount)
249 FROM AP_INVOICE_DISTRIBUTIONS_ALL dist
250 WHERE dist.invoice_distribution_id = dtl.taxable_item_source_id)
251 WHERE dtl.request_id = p_request_id
252 AND dtl.trx_line_class = 'PREPAY_APPLICATION'
253 AND dtl.extract_source_ledger = 'AP'
254 AND dtl.entity_code = 'AP_INVOICES'
255 AND dtl.application_id = 200
256 AND NVL(dtl.historical_flag,'N') = 'Y'
257 AND EXISTS (select 1
258 from AP_INVOICE_DISTRIBUTIONS_ALL dist1
259 where dist1.invoice_distribution_id = dtl.taxable_item_source_id
260 AND NVL(dist1.historical_flag,'N') = 'Y');
261
262 ELSE
263
264 UPDATE zx_rep_trx_detail_t dtl
265 SET (dtl.TAXABLE_AMT,dtl.TAXABLE_AMT_FUNCL_CURR)
266 = (SELECT sum(mc.amount), sum(nvl(mc.base_amount,mc.amount))
267 FROM AP_INVOICE_DISTRIBUTIONS_ALL dist,
268 AP_MC_INVOICE_DISTS mc
269 WHERE dtl.taxable_item_source_id = dist.invoice_distribution_id
270 AND dist.invoice_distribution_id = mc.invoice_distribution_id
271 AND mc.set_of_books_id = p_trl_global_variables_rec.reporting_ledger_id)
272 WHERE dtl.request_id = p_request_id
273 AND dtl.trx_line_class = 'PREPAY_APPLICATION'
274 AND dtl.extract_source_ledger = 'AP'
275 AND dtl.entity_code = 'AP_INVOICES'
276 AND dtl.application_id = 200
277 AND NVL(dtl.historical_flag,'N') = 'Y'
278 AND EXISTS (select 1
279 from AP_INVOICE_DISTRIBUTIONS_ALL dist1
280 where dist1.invoice_distribution_id = dtl.taxable_item_source_id
281 AND NVL(dist1.historical_flag,'N') = 'Y');
282
283 END IF;
284 IF (g_level_statement >= g_current_runtime_level ) THEN
285 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
286 'Updated Taxable Amount for Upgraded prepayment Applications for '||to_char(SQL%ROWCOUNT)||' records.');
287 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
288 'Reporting_Ledger_id : '||to_char(p_trl_global_variables_rec.reporting_ledger_id) );
289 END IF;
290
291 --- Upgraded Prepay Applications --
292
293
294 INSERT INTO zx_rep_trx_jx_ext_t
295 (detail_tax_line_ext_id,
296 detail_tax_line_id,
297 attribute1,
298 created_by,
299 creation_date,
300 last_updated_by,
301 last_update_date,
302 last_update_login,
303 request_id)
304 (SELECT zx_rep_trx_jx_ext_t_s.nextval,
305 dtl.detail_tax_line_id,
306 'Yes', --fl.meaning,
307 dtl.created_by,
308 dtl.creation_date,
309 dtl.last_updated_by,
310 dtl.last_update_date,
311 dtl.last_update_login,
312 p_request_id
313 FROM zx_rep_trx_detail_t dtl
314 WHERE EXISTS (select distinct ah.invoice_id
315 FROM ap_holds_all ah
316 WHERE ah.invoice_id = dtl.trx_id
317 AND ah.release_lookup_code IS NULL )
318 AND dtl.request_id = p_request_id);
319
320 IF (g_level_statement >= g_current_runtime_level ) THEN
321 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
322 'Insertion for Hold , ext.attribute1 : '||to_char(SQL%ROWCOUNT) );
323 END IF;
324
325 --Bug 5251425 : To get the C_TRX_CURRENCY_DESC ( ext.attribute9) and the C_BATCH_NAME ( ext.attribute8 ) for the Invoice .
326 SELECT
327 dtl.detail_tax_line_id,
328 dtl.trx_id,
329 dtl.trx_currency_code,
330 fcv.name,
331 ab.batch_name,
332 acc.TAX_ACCOUNT_CCID
333 BULK COLLECT INTO
334 l_detail_tax_line_id_tbl,
335 l_trx_id_tbl,
336 l_trx_currency_code_tbl,
337 l_trx_currency_desc_tbl,
338 l_batch_name_tbl,
339 l_acc_ccid_tbl
340 FROM
341 zx_rep_trx_detail_t dtl,
342 fnd_currencies_vl fcv,
343 ap_invoices_all ai,
344 ap_batches_all ab,
345 zx_rates_b rates,
346 zx_accounts acc
347 WHERE
348 dtl.request_id = p_request_id
349 AND dtl.trx_currency_code = fcv.currency_code
350 AND dtl.trx_id = ai.invoice_id
351 AND ai.batch_id = ab.batch_id(+)
352 AND dtl.tax_rate_id = rates.tax_rate_id(+)
353 AND acc.TAX_ACCOUNT_ENTITY_ID(+) = rates.tax_rate_id;
354
355 IF (g_level_statement >= g_current_runtime_level ) THEN
356 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
357 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
358 END IF;
359 --Get details for C_MATCH
360
361 GET_MATCH(
362 l_detail_tax_line_id_tbl,
363 l_acc_ccid_tbl,
364 l_match_tbl
365 );
366
367 IF (g_level_statement >= g_current_runtime_level ) THEN
368 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
369 'Before insertion into zx_rep_trx_jx_ext_t for report '||p_report_name );
370 END IF;
371
372 FORALL i IN nvl(l_detail_tax_line_id_tbl.FIRST,1)..nvl(l_detail_tax_line_id_tbl.LAST,0)
373 MERGE INTO zx_rep_trx_jx_ext_t ext
374 USING ( SELECT 1 FROM dual ) T
375 ON ( ext.detail_tax_line_id = l_detail_tax_line_id_tbl(i))
376 WHEN MATCHED THEN UPDATE SET ext.ATTRIBUTE9 = l_trx_currency_desc_tbl(i),
377 ext.attribute8 = l_batch_name_tbl(i),
378 ext.attribute5 = l_match_tbl(i)
379 WHEN NOT MATCHED THEN
380 INSERT (
381 detail_tax_line_ext_id,
382 detail_tax_line_id,
383 attribute9,
384 attribute8,
385 attribute5,
386 created_by,
387 creation_date,
388 last_updated_by,
389 last_update_date,
390 last_update_login,
391 request_id
392 )
393 VALUES ( ZX_MIGRATE_UTIL.get_next_seqid('ZX_REP_TRX_JX_EXT_T_S'),
394 l_detail_tax_line_id_tbl(i),
395 l_trx_currency_desc_tbl(i),
396 l_batch_name_tbl(i),
397 l_match_tbl(i),
398 fnd_global.user_id,
399 sysdate,
400 fnd_global.user_id,
401 sysdate,
402 fnd_global.login_id,
403 p_request_id
404 );
405 IF (g_level_statement >= g_current_runtime_level ) THEN
406 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
407 'Update for ERV/IPV , ext.numeric1 : ' );
408 END IF;
409
410 DELETE FROM zx_rep_actg_ext_t acct1
411 WHERE acct1.request_id = p_request_id
412 AND acct1.actg_ext_line_id <> (SELECT MIN(actg_ext_line_id)
413 FROM zx_rep_actg_ext_t acct2
414 WHERE acct2.actg_header_id= acct1.actg_header_id
415 and acct2.actg_event_id = acct1.actg_event_id
416 AND acct2.actg_source_id = acct1.actg_source_id
417 AND acct2.detail_tax_line_id = acct1.detail_tax_line_id
418 AND acct2.request_id = acct1.request_id
419 GROUP BY acct2.actg_header_id, acct2.actg_event_id,
420 acct2.actg_source_id,acct2.detail_tax_line_id
421 HAVING COUNT( DISTINCT acct2.actg_ext_line_id) >=2);
422
423 IF (g_level_statement >= g_current_runtime_level ) THEN
424 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
425 'Deleted duplicate rows from zx_rep_actg_ext_t: '||to_char(SQL%ROWCOUNT) );
426 END IF;
427
428 update zx_rep_actg_ext_t act
429 set act.period_name = (select apd.period_name
430 from ap_invoice_distributions_all apd,
431 zx_rep_trx_detail_t dtl
432 where dtl.trx_id = apd.invoice_id
433 and dtl.TAXABLE_ITEM_SOURCE_ID = apd.invoice_distribution_id
434 and act.detail_tax_line_id = dtl.detail_tax_line_id
435 and dtl.request_id = p_request_id)
436 where act.request_id = p_request_id
437 and act.period_name is null;
438
439 IF (g_level_statement >= g_current_runtime_level ) THEN
440 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
441 'After Updating period name on zx_rep_actg_ext_t table ');
442 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
443 'Number of rows updated: '||to_char(SQL%ROWCOUNT));
444 END IF;
445
446 UPDATE zx_rep_trx_jx_ext_t ext
447 SET ext.numeric1 =
448 (SELECT sum(nvl(lnk.UNROUNDED_accounted_DR,0)-nvl(lnk.UNROUNDED_accounted_CR,0))
449 FROM zx_rep_trx_detail_t dtl,
450 zx_rep_actg_ext_t act_ext,
451 ap_invoice_distributions_all ap_dist,
452 po_distributions_all pod,
453 xla_ae_headers aeh,
454 xla_ae_lines ael,
455 xla_distribution_links lnk
456 WHERE dtl.ref_doc_application_id = 201
457 and dtl.request_id = p_request_id
458 and act_ext.detail_tax_line_id = dtl.detail_tax_line_id
459 and dtl.detail_tax_line_id = ext.detail_tax_line_id
460 and ap_dist.line_type_lookup_code in('IPV','ERV')
461 and ap_dist.invoice_id = dtl.trx_id
462 and ap_dist.invoice_line_number = dtl.trx_line_id
463 and ap_dist.related_id = dtl.taxable_item_source_id
464 and ap_dist.invoice_distribution_id <> ap_dist.related_id
465 and aeh.application_id = 200
466 and aeh.ledger_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
467 and aeh.event_id = ap_dist.accounting_event_id
468 and ael.ae_header_id = aeh.ae_header_id
469 and ael.application_id = aeh.application_id
470 and lnk.application_id = aeh.application_id
471 and lnk.ae_header_id = aeh.ae_header_id
472 and lnk.event_id = aeh.event_id
473 and lnk.source_distribution_type = 'AP_INV_DIST'
474 and lnk.source_distribution_id_num_1 = ap_dist.invoice_distribution_id
475 and lnk.ae_line_num = ael.ae_line_num
476 and ap_dist.po_distribution_id = pod.po_distribution_id
477 and aeh.balance_type_code = 'A'
478 and ((pod.accrue_on_receipt_flag = 'Y' and ael.accounting_class_code in ('EXCHANGE_RATE_VARIANCE','IPV'))
479 OR (NVL(pod.accrue_on_receipt_flag, 'N') = 'N' and ael.accounting_class_code in ('ITEM EXPENSE') ) )
480 )
481 where ext.request_id = p_request_id;
482
483 IF (g_level_statement >= g_current_runtime_level ) THEN
484 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
485 'Number of rows updated in table zx_rep_trx_jx_ext_t: '||to_char(SQL%ROWCOUNT) );
486 END IF;
487
488 UPDATE zx_rep_trx_detail_t dtl
489 SET dtl.TAXABLE_AMT = 0,
490 dtl.TAXABLE_AMT_FUNCL_CURR = 0
491 WHERE dtl.request_id = p_request_id
492 AND NOT EXISTS
493 (SELECT /*+ FIRST_ROWS(1) */ 1
494 FROM AP_INVOICE_DISTRIBUTIONS_ALL item,
495 AP_INVOICE_DISTRIBUTIONS_ALL tax
496 WHERE tax.DETAIL_TAX_DIST_ID = DTL.ACTG_SOURCE_ID
497 AND item.PERIOD_NAME = tax.PERIOD_NAME
498 AND item.INVOICE_ID = tax.INVOICE_ID
499 and item.ORG_ID = tax.ORG_ID
500 and item.INVOICE_DISTRIBUTION_ID = tax.CHARGE_APPLICABLE_TO_DIST_ID
501 AND item.INVOICE_DISTRIBUTION_ID = DTL.TAXABLE_ITEM_SOURCE_ID
502 AND ROWNUM=1
503 )
504 AND EXISTS
505 (SELECT 1
506 FROM ZX_REC_NREC_DIST
507 WHERE REC_NREC_TAX_DIST_ID = DTL.ACTG_SOURCE_ID
508 );
509
510 IF (g_level_statement >= g_current_runtime_level ) THEN
511 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
512 'Number of rows updated to Amt as 0 in table zx_rep_trx_detail_t: '||to_char(SQL%ROWCOUNT));
513 END IF;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 IF (g_level_statement >= g_current_runtime_level ) THEN
518 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
519 'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
520 END IF;
521 END;
522 END IF;
523
524 IF P_REPORT_NAME = 'JGVAT' THEN
525 BEGIN
526 IF (g_level_statement >= g_current_runtime_level ) THEN
527 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
528 'Report Name: JGVAT ' );
529 END IF;
530
531 DELETE FROM zx_rep_actg_ext_t acct1
532 WHERE acct1.request_id = p_request_id
533 AND acct1.actg_ext_line_id <> (SELECT MIN(actg_ext_line_id)
534 FROM zx_rep_actg_ext_t acct2
535 WHERE acct2.actg_header_id= acct1.actg_header_id
536 and acct2.actg_event_id = acct1.actg_event_id
537 AND acct2.actg_source_id = acct1.actg_source_id
538 AND acct2.detail_tax_line_id = acct1.detail_tax_line_id
539 AND acct2.request_id = acct1.request_id
540 GROUP BY acct2.actg_header_id, acct2.actg_event_id,
541 acct2.actg_source_id,acct2.detail_tax_line_id
542 HAVING COUNT( DISTINCT acct2.actg_ext_line_id) >=2);
543
544 IF (g_level_statement >= g_current_runtime_level ) THEN
545 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
546 'Deleted duplicate rows from zx_rep_actg_ext_t: '||to_char(SQL%ROWCOUNT) );
547 END IF;
548
549
550 IF p_trl_global_variables_rec.reporting_ledger_id IS NOT NULL THEN
551 IF (g_level_statement >= g_current_runtime_level ) THEN
552 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
553 'Update zx_rep_trx_detail_t.taxable_amt_funcl_curr for IPV issue' );
554 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
555 'Reporting_ledger_id : '||to_char(p_trl_global_variables_rec.reporting_ledger_id) );
556 END IF;
557
558 UPDATE zx_rep_trx_detail_t dtl1
559 SET dtl1.taxable_amt_funcl_curr = nvl(dtl1.taxable_amt_funcl_curr,0)
560 + NVL(( SELECT sum(nvl(lnk.UNROUNDED_accounted_DR,0)-nvl(lnk.UNROUNDED_accounted_CR,0)) *
561 decode(sign(nvl(dtl1.taxable_amt_funcl_curr,0)),0,1,sign(nvl(dtl1.taxable_amt_funcl_curr,0)))
562 FROM zx_rep_trx_detail_t dtl2,
563 zx_rep_actg_ext_t act_ext,
564 AP_INVOICE_DISTRIBUTIONS_ALL AP_DIST,
565 xla_distribution_links lnk,
566 xla_ae_headers aeh,
567 xla_ae_lines ael
568 WHERE ap_dist.invoice_id=dtl2.trx_id
569 and ap_dist.invoice_line_number=dtl2.trx_line_id
570 and ap_dist.related_id=dtl2.taxable_item_source_id
571 and ap_dist.invoice_distribution_id <> ap_dist.related_id
572 and ap_dist.line_type_lookup_code in ('IPV','ERV')
573 and dtl2.detail_tax_line_id=act_ext.detail_tax_line_id
574 and lnk.application_id=200
575 and lnk.source_distribution_type='AP_INV_DIST'
576 and lnk.source_distribution_id_num_1=ap_dist.invoice_distribution_id
577 and lnk.event_id=ap_dist.accounting_event_id
578 and lnk.ae_header_id=act_ext.actg_header_id
579 and lnk.event_id=act_ext.actg_event_id
580 and lnk.ae_line_num=ael.ae_line_num
581 and aeh.ae_header_id=ael.ae_header_id
582 and aeh.ledger_id=p_trl_global_variables_rec.reporting_ledger_id
583 and aeh.ae_header_id=lnk.ae_header_id
584 and aeh.application_id=lnk.application_id
585 and ael.application_id=aeh.application_id
586 and ael.accounting_class_code not in ('LIABILITY','NRTAX','RTAX')
587 and dtl2.request_id = p_request_id
588 and dtl2.detail_tax_line_id = dtl1.detail_tax_line_id
589 ),0),
590 dtl1.taxable_amt = nvl(dtl1.taxable_amt,0)
591 + NVL(( SELECT sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)-nvl(lnk.UNROUNDED_ENTERED_CR,0)) *
592 decode(sign(nvl(dtl1.taxable_amt,0)),0,1,sign(nvl(dtl1.taxable_amt,0)))
593 FROM zx_rep_trx_detail_t dtl2,
594 zx_rep_actg_ext_t act_ext,
595 AP_INVOICE_DISTRIBUTIONS_ALL AP_DIST,
596 xla_distribution_links lnk,
597 xla_ae_headers aeh,
598 xla_ae_lines ael
599 WHERE ap_dist.invoice_id=dtl2.trx_id
600 and ap_dist.invoice_line_number=dtl2.trx_line_id
601 and ap_dist.related_id=dtl2.taxable_item_source_id
602 and ap_dist.invoice_distribution_id <> ap_dist.related_id
603 and ap_dist.line_type_lookup_code in ('IPV','ERV')
604 and dtl2.detail_tax_line_id=act_ext.detail_tax_line_id
605 and lnk.application_id=200
606 and lnk.source_distribution_type='AP_INV_DIST'
607 and lnk.source_distribution_id_num_1=ap_dist.invoice_distribution_id
608 and lnk.event_id=ap_dist.accounting_event_id
609 and lnk.ae_header_id=act_ext.actg_header_id
610 and lnk.event_id=act_ext.actg_event_id
611 and lnk.ae_line_num=ael.ae_line_num
612 and aeh.ae_header_id=ael.ae_header_id
613 and aeh.ledger_id=p_trl_global_variables_rec.reporting_ledger_id
614 and aeh.ae_header_id=lnk.ae_header_id
615 and aeh.application_id=lnk.application_id
616 and ael.application_id=aeh.application_id
617 and ael.accounting_class_code not in ('LIABILITY','NRTAX','RTAX')
618 and dtl2.request_id = p_request_id
619 and dtl2.detail_tax_line_id = dtl1.detail_tax_line_id
620 ),0)
621 where dtl1.request_id = p_request_id
622 AND dtl1.application_id = 200
623 and dtl1.ref_doc_application_id = 201;
624
625 IF (g_level_statement >= g_current_runtime_level ) THEN
626 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
627 'Number of rows updated : '||to_char(SQL%ROWCOUNT) );
628 END IF;
629 ELSE
630
631 IF (g_level_statement >= g_current_runtime_level ) THEN
632 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
633 'Update zx_rep_trx_detail_t.taxable_amt_funcl_curr with AP Dist base_amount' );
634 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
635 'Primar ledger id : '||to_char(p_trl_global_variables_rec.ledger_id) );
636 END IF;
637
638 UPDATE zx_rep_trx_detail_t dtl1
639 SET dtl1.taxable_amt_funcl_curr =
640 decode(sign(nvl(dtl1.taxable_amt_funcl_curr,0)),0,1,sign(nvl(dtl1.taxable_amt_funcl_curr,0)))
641 *
642 abs((select SUM(NVL(ap_dist.base_amount, ap_dist.amount))
643 from ap_invoice_distributions_all ap_dist
644 where ap_dist.invoice_distribution_id = dtl1.taxable_item_source_id)
645 + NVL((select SUM(NVL(ap_dist1.base_amount, ap_dist1.amount))
646 from ap_invoice_distributions_all ap_dist1
647 where ap_dist1.invoice_id=dtl1.trx_id
648 and ap_dist1.invoice_line_number=dtl1.trx_line_id
649 and ap_dist1.related_id=dtl1.taxable_item_source_id
650 and AP_DIST1.INVOICE_DISTRIBUTION_ID <> AP_DIST1.RELATED_ID
651 and ap_dist1.line_type_lookup_code in ('IPV','ERV')),0)),
652 dtl1.taxable_amt =
653 decode(sign(nvl(dtl1.taxable_amt,0)),0,1,sign(nvl(dtl1.taxable_amt,0)))
654 *
655 abs((select SUM(ap_dist.amount)
656 from ap_invoice_distributions_all ap_dist
657 where ap_dist.invoice_distribution_id = dtl1.taxable_item_source_id)
658 + NVL((select SUM(ap_dist1.amount)
659 from ap_invoice_distributions_all ap_dist1
660 where ap_dist1.invoice_id=dtl1.trx_id
661 and ap_dist1.invoice_line_number=dtl1.trx_line_id
662 and ap_dist1.related_id=dtl1.taxable_item_source_id
663 and ap_dist1.invoice_distribution_id <> ap_dist1.related_id
664 and ap_dist1.line_type_lookup_code in ('IPV','ERV')),0))
665 WHERE dtl1.ledger_id=p_trl_global_variables_rec.ledger_id
666 AND dtl1.request_id = p_request_id
667 AND dtl1.application_id = 200
668 AND dtl1.ref_doc_application_id = 201;
669
670 IF (g_level_statement >= g_current_runtime_level ) THEN
671 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
672 'Update zx_rep_trx_detail_t.taxable_amt_funcl_curr with AP Dist base_amount' );
673 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
674 'Primar ledger id : '||to_char(p_trl_global_variables_rec.ledger_id) );
675 END IF;
676
677
678 END IF;
679
680 -- This update added to populate recovery rate for migrated AP invoices --
681 UPDATE ZX_REP_TRX_DETAIL_T DTL
682 SET DTL.TAX_RECOVERY_RATE =
683 ( SELECT AP_DIST.REC_NREC_RATE
684 FROM AP_INVOICE_DISTRIBUTIONS_ALL AP_DIST
685 WHERE AP_DIST.INVOICE_ID=DTL.TRX_ID
686 AND AP_DIST.DETAIL_TAX_DIST_ID =DTL.ACTG_SOURCE_ID
687 AND AP_DIST.LINE_TYPE_LOOKUP_CODE IN ('REC_TAX','NONREC_TAX')
688 --AND AP_DIST.HISTORICAL_FLAG = 'Y' -- Commented the condition for Bug#12878223
689 AND AP_DIST.ORG_ID = DTL.INTERNAL_ORGANIZATION_ID
690 )
691 WHERE DTL.REQUEST_ID = P_REQUEST_ID
692 AND DTL.HISTORICAL_FLAG = 'Y'
693 AND NVL(DTL.OFFSET_FLAG,'N') = 'N'
694 AND DTL.OFFSET_TAX_RATE_CODE IS NULL
695 AND DTL.APPLICATION_ID = 200;
696
697 IF (g_level_statement >= g_current_runtime_level ) THEN
698 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
699 'Number of rows updated For Recovery Rate: '||to_char(SQL%ROWCOUNT) );
700 END IF;
701
702 -- Bug 12814237, This is for the migrated AP invoices where in 11i 2 tax distributions
703 -- carry same tax_code_id and recoverability,but with different tax amount. Use the proration logic,
704 -- similar to 11i, to stamp the recovery rate
705
706 UPDATE ZX_REP_TRX_DETAIL_T DTL
707 SET DTL.TAX_RECOVERY_RATE = (SELECT (ZX_DIST.rec_nrec_tax_amt/ZX_DIST.trx_line_dist_tax_amt)*100
708 FROM zx_rec_nrec_dist ZX_DIST
709 WHERE DTL.EXTRACT_SOURCE_LEDGER = 'AP'
710 AND ZX_DIST.rec_nrec_tax_dist_id = DTL.actg_source_id
711 AND abs(ZX_DIST.trx_line_dist_tax_amt) > abs(ZX_DIST.rec_nrec_tax_amt)
712 AND ZX_DIST.rec_nrec_tax_amt <> 0
713 )
714 WHERE DTL.REQUEST_ID = P_REQUEST_ID
715 AND DTL.HISTORICAL_FLAG = 'Y'
716 AND EXISTS(SELECT 1 FROM zx_rec_nrec_dist ZX_DIST1
717 WHERE ZX_DIST1.rec_nrec_tax_dist_id = DTL.actg_source_id
718 AND abs(ZX_DIST1.trx_line_dist_tax_amt) > abs(ZX_DIST1.rec_nrec_tax_amt)
719 AND ZX_DIST1.rec_nrec_tax_amt <> 0
720 );
721
722 IF (g_level_statement >= g_current_runtime_level ) THEN
723 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
724 'Number of rows updated For Recovery Rate - 2: '||to_char(SQL%ROWCOUNT) );
725 END IF;
726
727
728
729 EXCEPTION
730 WHEN OTHERS THEN
731 IF (g_level_statement >= g_current_runtime_level ) THEN
732 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
733 'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
734 END IF;
735 END;
736 END IF; /* end of condition if report_name = 'JGVAT' */
737
738 IF P_REPORT_NAME = 'ZXXTAVAR' THEN --Bug 5251425
739 BEGIN
740 OPEN establishment_id_csr (p_legal_entity_id);
741 FETCH establishment_id_csr INTO l_establishment_id;
742
743 IF (g_level_statement >= g_current_runtime_level ) THEN
744 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
745 'l_establishment_id : '||l_establishment_id);
746 END IF;
747
748 -- ------------------------------------------------ --
749 -- Get filtered tax lines --
750 -- in this case, you need to group the lines by trx --
751 -- ------------------------------------------------ --
752
753 SELECT dtl.detail_tax_line_id,
754 dtl.trx_id,
755 dtl.billing_tp_country,
756 dtl.internal_organization_id --Bug 5251425
757 BULK COLLECT INTO l_detail_tax_line_id_tbl,
758 l_trx_id_tbl,
759 l_country_code_tbl,
760 l_internal_organization_id_tbl --Bug 5251425
761 FROM zx_reporting_types_b rep_type,
762 zx_reporting_codes_b rep_code,
763 zx_report_codes_assoc rep_ass,
764 zx_party_tax_profile ptp,
765 xle_etb_profiles xle_pf ,
766 zx_rep_trx_detail_t dtl
767 WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
768 AND rep_type.reporting_type_code = 'MEMBER STATE'
769 AND rep_code.reporting_code_id = rep_ass.reporting_code_id
770 AND rep_ass.entity_code = 'ZX_PARTY_TAX_PROFILE'
771 AND rep_ass.entity_id = ptp.party_tax_profile_id
772 AND ptp.party_id = xle_pf.party_id
773 AND ptp.Party_Type_Code = 'LEGAL_ESTABLISHMENT'
774 AND xle_pf.establishment_id = l_establishment_id
775 AND xle_pf.establishment_id = dtl.establishment_id
776 AND rep_code.reporting_code_char_value <> dtl.billing_tp_country
777 AND dtl.request_id = p_request_id
778 AND EXISTS
779 (
780 SELECT 1
781 FROM zx_reporting_codes_b code
782 WHERE code.reporting_type_id = rep_code.reporting_type_id
783 and code.reporting_code_char_value = dtl.billing_tp_country
784 );
785
786 IF (g_level_statement >= g_current_runtime_level ) THEN
787 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
788 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
789 END IF;
790
791 -- Note : Need to add xle_associations_v view to the above query for establishment_id join
792
793 -- Get territory Info --
794 IF ( nvl(l_detail_tax_line_id_tbl.count,0) > 0 ) THEN
795
796 GET_TERRITORY_INFO( l_detail_tax_line_id_tbl,
797 l_country_code_tbl,
798 l_territory_short_name_tbl,
799 l_alternate_territory_name_tbl);
800
801 IF (g_level_statement >= g_current_runtime_level ) THEN
802 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
803 'After getting the territory info.' );
804 END IF;
805
806 GET_ORG_VAT_NUM ( l_detail_tax_line_id_tbl,
807 l_establishment_id,
808 l_org_vat_num_tbl);
809
810 IF (g_level_statement >= g_current_runtime_level ) THEN
811 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
812 'After getting the org_vat_num' );
813 END IF;
814
815
816 --Bug 5251425 : To derive the C_CREATED_BY(zx_rep_trx_detail_t.attribute6)
817 -- and C_ATTRIBUTE3(Operating Unit Desc) into (zx_rep_trx_detail_t.attribute7)
818
819 GET_CREATED_BY(
820 l_detail_tax_line_id_tbl,
821 l_trx_id_tbl,
822 l_created_by_tbl
823 );
824
825 IF (g_level_statement >= g_current_runtime_level ) THEN
826 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
827 'After getting the created_by for invoice' );
828 END IF;
829
830
831 GET_OU_DESC(
832 l_detail_tax_line_id_tbl,
833 l_internal_organization_id_tbl,
834 l_ou_desc_tbl
835 );
836
837 IF (g_level_statement >= g_current_runtime_level ) THEN
838 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
839 'After getting the OU Description.' );
840 END IF;
841
842
843 FORALL i in nvl(l_detail_tax_line_id_tbl.first,1)..nvl(l_detail_tax_line_id_tbl.last,0)
844
845 INSERT INTO zx_rep_trx_jx_ext_t
846 (detail_tax_line_ext_id,
847 detail_tax_line_id,
848 attribute1,
849 attribute2,
850 attribute3,
851 attribute6,--Bug 5251425
852 attribute7,--Bug 5251425
853 created_by,
854 creation_date,
855 last_updated_by,
856 last_update_date,
857 last_update_login,
858 request_id)
859 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
860 l_detail_tax_line_id_tbl(i),
861 l_org_vat_num_tbl(i),
862 l_territory_short_name_tbl(i),
863 l_alternate_territory_name_tbl(i),
864 l_created_by_tbl(i),--Bug 5251425
865 l_ou_desc_tbl(i),--Bug 5251425
866 fnd_global.user_id,
867 sysdate,
868 fnd_global.user_id,
869 sysdate,
870 fnd_global.login_id,
871 p_request_id);
872
873 IF (g_level_statement >= g_current_runtime_level ) THEN
874 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
875 'After insertion into zx_rep_trx_jx_ext_t for report '||p_report_name );
876 END IF;
877
878 END IF ;
879 -- Delete Unwanted lines from Detail ITF
880
881 DELETE FROM zx_rep_trx_detail_t itf
882 WHERE itf.request_id = p_request_id
883 AND NOT EXISTS ( SELECT 1
884 FROM zx_rep_trx_jx_ext_t ext
885 WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
886
887 IF (g_level_statement >= g_current_runtime_level ) THEN
888 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
889 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
890 END IF;
891
892 EXCEPTION
893 WHEN OTHERS THEN
894 NULL;
895 IF (g_level_statement >= g_current_runtime_level ) THEN
896 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
897 'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
898 END IF;
899
900 END;
901 END IF;
902
903 -- This plug-in call extracts adjustment tax code for Italy, Poland, Portugal countries requirement
904
905 IF P_REPORT_NAME = 'RXZXPFTR' THEN
906 IF P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
907 IF P_POSTING_STATUS = 'TRANSFERRED' THEN
908 DELETE FROM zx_rep_actg_ext_t
909 WHERE request_id = p_request_id
910 AND NVL(gl_transfer_flag,'N') <>'Y';
911
912 IF ( g_level_statement>= g_current_runtime_level ) THEN
913 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AP',
914 'No of Unposted transactions deleted from zx_rep_actg_ext_t: '|| sql%rowcount);
915 END IF;
916
917 DELETE FROM zx_rep_trx_detail_t dtl
918 WHERE dtl.request_id = p_request_id
919 AND NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
920 WHERE act.detail_tax_line_id = dtl.detail_tax_line_id) ;
921
922 IF ( g_level_statement>= g_current_runtime_level ) THEN
923 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AP',
924 'No of rows deleted from zx_rep_trx_detail_t: '|| sql%rowcount);
925 END IF;
926 ELSIF P_POSTING_STATUS = 'NOT_TRANSFERRED' THEN
927 DELETE FROM zx_rep_actg_ext_t
928 WHERE request_id = p_request_id
929 AND NVL(gl_transfer_flag,'N') = 'Y';
930 IF ( g_level_statement>= g_current_runtime_level ) THEN
931 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AP',
932 'No of Unposted transactions deleted from zx_rep_actg_ext_t: '|| sql%rowcount);
933 END IF;
934 DELETE FROM zx_rep_trx_detail_t dtl
935 WHERE dtl.request_id = p_request_id
936 AND NVL(dtl.posted_flag,'N') = 'A'
937 AND NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
938 WHERE act.detail_tax_line_id = dtl.detail_tax_line_id);
939
940 IF ( g_level_statement>= g_current_runtime_level ) THEN
941 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AP',
942 'No of rows deleted from zx_rep_trx_detail_t: '|| sql%rowcount);
943 END IF;
944 END IF; /* END P_POSTING_STATUS */
945
946 IF P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEDGER_ID IS NULL THEN
947 DELETE FROM zx_rep_actg_ext_t acct1
948 WHERE acct1.request_id = p_request_id
949 AND acct1.actg_ext_line_id <> (SELECT MIN(actg_ext_line_id)
950 FROM zx_rep_actg_ext_t acct2
951 WHERE acct2.actg_header_id= acct1.actg_header_id
952 and acct2.actg_event_id = acct1.actg_event_id
953 AND acct2.actg_source_id = acct1.actg_source_id
954 AND acct2.detail_tax_line_id = acct1.detail_tax_line_id
955 AND acct2.request_id = acct1.request_id
956 GROUP BY acct2.actg_header_id, acct2.actg_event_id,
957 acct2.actg_source_id,acct2.detail_tax_line_id
958 HAVING COUNT( DISTINCT acct2.actg_ext_line_id) >=2);
959
960 IF (g_level_statement >= g_current_runtime_level ) THEN
961 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
962 'For Primary Ledger Deleted duplicate rows from zx_rep_actg_ext_t: '||to_char(SQL%ROWCOUNT) );
963 END IF;
964 END IF; -- Primary Ledger Id --
965
966 END IF; -- Summary Level --
967
968 END IF; /* END P_REPORT_NAME RXZXPFTR */
969
970 IF P_REPORT_NAME <> 'JGVAT' THEN
971
972 IF p_product = 'AR' or p_product = 'ALL' THEN
973
974 BEGIN
975
976 SELECT dtl.detail_tax_line_id,
977 dtl.tax_rate_code
978 BULK COLLECT INTO l_detail_tax_line_id_tbl,
979 l_tax_rate_code_tbl
980 FROM zx_rep_trx_detail_t dtl
981 WHERE dtl.request_id = p_request_id;
982
983 IF (g_level_statement >= g_current_runtime_level ) THEN
984 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
985 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
986 END IF;
987
988 adjustment_tax_code(
989 l_detail_tax_line_id_tbl,
990 l_tax_rate_code_tbl,
991 l_adj_tax_code_tbl);
992
993 IF (g_level_statement >= g_current_runtime_level ) THEN
994 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
995 'After getting adjustment tax code for Italy, Poland, Portugal countries');
996 END IF;
997
998 FORALL i in l_detail_tax_line_id_tbl.first..l_detail_tax_line_id_tbl.last
999 INSERT INTO zx_rep_trx_jx_ext_t
1000 (detail_tax_line_ext_id,
1001 detail_tax_line_id,
1002 attribute1,
1003 created_by,
1004 creation_date,
1005 last_updated_by,
1006 last_update_date,
1007 last_update_login,
1008 request_id)
1009 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1010 l_detail_tax_line_id_tbl(i),
1011 l_adj_tax_code_tbl(i),
1012 fnd_global.user_id,
1013 sysdate,
1014 fnd_global.user_id,
1015 sysdate,
1016 fnd_global.login_id,
1017 p_request_id);
1018
1019 IF (g_level_statement >= g_current_runtime_level ) THEN
1020 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
1021 'After insertion into zx_rep_trx_jx_ext_t ');
1022 END IF;
1023
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 NULL;
1027 IF (g_level_statement >= g_current_runtime_level ) THEN
1028 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
1029 'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
1030 END IF;
1031 END;
1032 END IF;
1033 END IF;
1034 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1035 IF (g_level_procedure >= g_current_runtime_level ) THEN
1036 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP.BEGIN',
1037 'ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP(-)');
1038 END IF;
1039 null;
1040
1041 END populate_core_ap;
1042
1043
1044 PROCEDURE get_territory_info
1045 (
1046 --p_report_name IN VARCHAR2,
1047 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
1048 p_country_code_tbl IN ZX_EXTRACT_PKG.billing_tp_country_tbl,
1049 p_territory_short_name_tbl OUT NOCOPY ATTRIBUTE2_TBL,
1050 p_alternate_territory_name_tbl OUT NOCOPY ATTRIBUTE3_TBL
1051 )
1052 IS
1053
1054 BEGIN
1055
1056 FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
1057 BEGIN
1058 SELECT ft.territory_short_name,
1059 nvl(ft.alternate_territory_code, ft.territory_code)
1060 INTO p_territory_short_name_tbl(i),
1061 p_alternate_territory_name_tbl(i)
1062 FROM fnd_territories_vl ft
1063 WHERE ft.territory_code = p_country_code_tbl(i);
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 p_territory_short_name_tbl(i) := NULL ;
1067 p_alternate_territory_name_tbl(i) := NULL ;
1068 IF ( g_level_statement>= g_current_runtime_level ) THEN
1069 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
1070 'Error Message : '||substrb(SQLERRM,1,120) );
1071 END IF;
1072 END;
1073 END LOOP;
1074
1075 END get_territory_info;
1076
1077 PROCEDURE get_org_vat_num (
1078 --p_report_name IN VARCHAR2,
1079 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
1080 -- p_request_id IN NUMBER,
1081 p_establishment_id IN NUMBER,
1082 p_org_vat_num_tbl OUT NOCOPY ATTRIBUTE2_TBL)
1083 IS
1084 BEGIN
1085
1086 FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
1087 BEGIN
1088 SELECT rep_code.reporting_code_char_value
1089 INTO p_org_vat_num_tbl(i)
1090 FROM zx_reporting_types_b rep_type,
1091 zx_reporting_codes_b rep_code,
1092 zx_report_codes_assoc rep_ass,
1093 zx_party_tax_profile ptp,
1094 xle_etb_profiles xle_pf
1095 -- zx_rep_trx_detail_t dtl
1096 WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
1097 AND rep_type.reporting_type_code = 'FSO_REG_NUM'
1098 AND rep_code.reporting_code_id = rep_ass.reporting_code_id
1099 AND rep_ass.entity_code = 'ZX_PARTY_TAX_PROFILE'
1100 AND rep_ass.entity_id = ptp.party_tax_profile_id
1101 AND ptp.party_id = xle_pf.party_id
1102 AND ptp.Party_Type_Code = 'LEGAL_ESTABLISHMENT'
1103 AND xle_pf.establishment_id = p_establishment_id;
1104 -- AND rep_code.reporting_code_char_value <> dtl.billing_tp_country;
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 p_org_vat_num_tbl(i) := NULL ;
1108 IF ( g_level_statement>= g_current_runtime_level ) THEN
1109 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
1110 'Error Message : '||substrb(SQLERRM,1,120) );
1111 END IF;
1112 END ;
1113 END LOOP;
1114 END get_org_vat_num;
1115
1116
1117 PROCEDURE adjustment_tax_code(
1118 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
1119 p_tax_rate_code_tbl IN ZX_EXTRACT_PKG.tax_rate_code_tbl,
1120 p_adj_tax_code_tbl OUT NOCOPY ATTRIBUTE1_TBL)
1121 IS
1122 BEGIN
1123 FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
1124 BEGIN
1125 SELECT rep_code.reporting_code_char_value
1126 INTO p_adj_tax_code_tbl(i)
1127 FROM zx_reporting_types_b rep_type,
1128 zx_reporting_codes_b rep_code,
1129 zx_report_codes_assoc rep_ass,
1130 zx_rates_b zx_rate
1131 WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
1132 AND rep_type.reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE'
1133 AND rep_code.reporting_code_id = rep_ass.reporting_code_id
1134 AND rep_ass.entity_code = 'ZX_RATES'
1135 AND rep_ass.entity_id = zx_rate.tax_rate_id
1136 AND zx_rate.tax_rate_code = p_tax_rate_code_tbl(i);
1137 EXCEPTION
1138 WHEN OTHERS THEN
1139 p_adj_tax_code_tbl(i) := NULL ;
1140 IF ( g_level_statement>= g_current_runtime_level ) THEN
1141 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
1142 'Error Message : '||substrb(SQLERRM,1,120) );
1143 END IF;
1144 END ;
1145 END LOOP;
1146 END;
1147
1148 PROCEDURE RETRIEVE_GEO_VALUE( p_event_class_mapping_id IN ZX_LINES_DET_FACTORS.event_class_mapping_id%type,
1149 p_trx_id IN ZX_LINES_DET_FACTORS.trx_id%type,
1150 p_trx_line_id IN ZX_LINES_DET_FACTORS.trx_line_id%type,
1151 p_trx_level_type IN ZX_LINES_DET_FACTORS.trx_level_type%type,
1152 p_location_type IN VARCHAR2,
1153 p_location_id IN ZX_LINES_DET_FACTORS.ship_to_location_id%type,
1154 p_geography_type IN VARCHAR2,
1155 x_geography_value OUT NOCOPY VARCHAR2,
1156 x_geo_val_found OUT NOCOPY BOOLEAN) IS
1157
1158 hash_string varchar2(1000);
1159 TABLE_SIZE BINARY_INTEGER := 65636;
1160 TABLEIDX binary_integer;
1161 loc_info_idx binary_integer;
1162 HASH_VALUE binary_integer;
1163
1164 BEGIN
1165
1166 hash_string := to_char(p_event_class_mapping_id)||'|'||
1167 to_char(p_trx_id)||'|'||
1168 to_char(p_trx_line_id)||'|'||
1169 p_trx_level_type||'|'||
1170 p_location_type||'|'||
1171 to_char(p_location_id)||'|'||
1172 p_geography_type;
1173
1174 TABLEIDX := dbms_utility.get_hash_value(hash_string,1,TABLE_SIZE);
1175
1176 IF (ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl.EXISTS(TABLEIDX)) THEN
1177 loc_info_idx := ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl(TABLEIDX);
1178 x_geography_value := ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.geography_value(loc_info_idx);
1179 x_geo_val_found := TRUE;
1180 ELSE
1181 x_geo_val_found := FALSE;
1182 END IF;
1183
1184 end RETRIEVE_GEO_VALUE;
1185
1186 --Bug 5251425
1187 PROCEDURE GET_CREATED_BY
1188 (
1189 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
1190 p_trx_id_tbl IN ZX_EXTRACT_PKG.trx_id_tbl,
1191 p_created_by_tbl OUT NOCOPY ATTRIBUTE6_TBL
1192 )
1193 IS
1194
1195 BEGIN
1196
1197 FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
1198 BEGIN
1199 SELECT fu.user_name
1200 INTO p_created_by_tbl(i)
1201 FROM ap_invoices_all ai,
1202 fnd_user fu
1203 WHERE ai.invoice_id = p_trx_id_tbl(i)
1204 AND fu.user_id = ai.created_by ;
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 p_created_by_tbl(i) := NULL ;
1208 IF ( g_level_statement>= g_current_runtime_level ) THEN
1209 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1210 'Error Message : '||substrb(SQLERRM,1,120) );
1211 END IF;
1212 END;
1213 END LOOP;
1214
1215 END GET_CREATED_BY;
1216
1217 --Bug 5251425
1218 PROCEDURE GET_OU_DESC
1219 (
1220 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
1221 p_internal_organization_id_tbl IN ZX_EXTRACT_PKG.internal_organization_id_tbl,
1222 p_ou_desc_tbl OUT NOCOPY ATTRIBUTE7_TBL
1223 )
1224 IS
1225
1226 BEGIN
1227
1228 FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
1229 BEGIN
1230 SELECT hou.NAME
1231 INTO p_ou_desc_tbl(i)
1232 FROM hr_operating_units hou
1233 WHERE hou.organization_id = p_internal_organization_id_tbl(i);
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 p_ou_desc_tbl(i) := NULL ;
1237 IF ( g_level_statement>= g_current_runtime_level ) THEN
1238 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1239 'Error Message : '||substrb(SQLERRM,1,120) );
1240 END IF;
1241 END;
1242 END LOOP;
1243
1244 END GET_OU_DESC;
1245 --Bug 5251425
1246 PROCEDURE GET_MATCH(
1247 p_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
1248 p_acc_ccid_tbl ACC_CCID_TBL ,
1249 p_match_tbl OUT NOCOPY ATTRIBUTE5_TBL
1250 )
1251 IS
1252 l_nls_no VARCHAR(10);
1253 BEGIN
1254 SELECT
1255 ln.meaning
1256 INTO l_nls_no
1257 FROM
1258 fnd_lookups ln, ap_lookup_codes la
1259 WHERE
1260 ln.lookup_type = 'YES_NO'
1261 AND ln.lookup_code = 'N'
1262 AND la.lookup_type = 'NLS REPORT PARAMETER'
1263 AND la.lookup_code = 'ALL';
1264
1265 FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
1266 BEGIN
1267 SELECT Decode(p_ACC_CCID_TBL(i), act.ACTG_LINE_CCID,NULL,l_nls_no )
1268 INTO p_match_tbl(i)
1269 FROM ZX_REP_ACTG_EXT_T act
1270 WHERE act.detail_tax_line_id = p_detail_tax_line_id_tbl(i);
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 p_match_tbl(i) := null;
1274 IF ( g_level_statement>= g_current_runtime_level ) THEN
1275 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1276 'Error Message : '||substrb(SQLERRM,1,120) );
1277 END IF;
1278 END;
1279 END LOOP;
1280 END GET_MATCH;
1281
1282 --To get the Received amounts for the report ZXXCDE
1283 PROCEDURE GET_RECEIVED_AMOUNTS
1284 (
1285 p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
1286 p_trx_id_tbl IN ZX_EXTRACT_PKG.trx_id_tbl,
1287 p_org_id_tbl IN zx_extract_pkg.INTERNAL_ORGANIZATION_ID_TBL ,
1288 p_amount_received_tbl OUT NOCOPY NUMERIC2_TBL,
1289 p_tax_received_tbl OUT NOCOPY NUMERIC1_TBL
1290 )
1291 IS
1292
1293 l_amount_recvd_tbl NUMERIC2_TBL;
1294 l_tax_amount_rcvd_tbl NUMERIC1_TBL;
1295
1296 BEGIN
1297
1298 FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
1299
1300 IF l_amount_recvd_tbl.EXISTS(p_trx_id_tbl(i)) THEN
1301 null;
1302 ELSE
1303 l_amount_recvd_tbl(p_trx_id_tbl(i)) := null;
1304 END IF;
1305
1306 IF l_tax_amount_rcvd_tbl.EXISTS(p_trx_id_tbl(i)) THEN
1307 null;
1308 ELSE
1309 l_tax_amount_rcvd_tbl(p_trx_id_tbl(i)) := null;
1310 END IF;
1311
1312 --get amount received for that trx without any proration done , populate the amount only once per trx_id
1313 -- in the extension table
1314 IF l_amount_recvd_tbl(p_trx_id_tbl(i)) is NULL THEN
1315
1316 BEGIN
1317 SELECT SUM(nvl(amount_applied,0)) ,sum(nvl(tax_applied,0))
1318 INTO l_amount_recvd_tbl(p_trx_id_tbl(i)),l_tax_amount_rcvd_tbl(p_trx_id_tbl(i))
1319 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1320 WHERE applied_customer_trx_id = p_trx_id_tbl(i)
1321 AND org_id = p_org_id_tbl(i)
1322 AND status = 'APP'
1323 AND application_type = 'CASH';
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 l_amount_recvd_tbl(p_trx_id_tbl(i)) := 0;
1327 l_tax_amount_rcvd_tbl(p_trx_id_tbl(i)) := 0;
1328
1329 IF ( g_level_statement>= g_current_runtime_level ) THEN
1330 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1331 'Error Message : '||substrb(SQLERRM,1,120) );
1332 END IF;
1333 END ;
1334 p_amount_received_tbl(i) := l_amount_recvd_tbl(p_trx_id_tbl(i));
1335 p_tax_received_tbl(i) := l_tax_amount_rcvd_tbl(p_trx_id_tbl(i)) ;
1336 ELSE
1337 p_amount_received_tbl(i) := 0 ;
1338 p_tax_received_tbl(i) := 0 ;
1339 END IF ;
1340 END LOOP ;
1341
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 NULL ;
1345 END ;
1346
1347
1348 /*======================================================================================+
1349 | PROCEDURE |
1350 | POPULATE_TAX_JURIS_FOR_USSTR |
1351 | Type : Private |
1352 | Pre-req : None |
1353 | Function : |
1354 | This procedure extracts State, County, and City of taxing jurisdiction |
1355 | for each tax line into table |
1356 | |
1357 | Called from XX_XX_EXTRACT_PKG.XXXXXXXX |
1358 | |
1359 | Parameters : |
1360 | IN : P_TRL_GLOBAL_VARIABLES_REC IN VARCHAR2 |
1361 | |
1362 | MODIFICATION HISTORY |
1363 | 20-Jun-05 Santosh Vaze created |
1364 | |
1365 +======================================================================================*/
1366
1367
1368 PROCEDURE POPULATE_CORE_AR(
1369 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
1370 )
1371 IS
1372
1373 P_REPORT_NAME VARCHAR2(30);
1374 P_POSTING_STATUS VARCHAR2(30);
1375 P_REQUEST_ID NUMBER;
1376 l_place_of_supply ZX_LINES.place_of_supply_type_code%type;
1377 l_location_type_tbl ZX_TCM_GEO_JUR_PKG.location_type_tbl_type;
1378 l_location_id_tbl ZX_TCM_GEO_JUR_PKG.location_id_tbl_type;
1379 l_geo_val_found BOOLEAN;
1380 x_return_status VARCHAR2(1);
1381 prev_event_class_mapping_id NUMBER;
1382
1383 TYPE DETAIL_TAX_LINE_ID_TBL IS TABLE OF
1384 ZX_REP_TRX_DETAIL_T.DETAIL_TAX_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
1385 TYPE EVENT_CLASS_MAPPING_ID_TBL IS TABLE OF
1386 ZX_REP_TRX_DETAIL_T.EVENT_CLASS_MAPPING_ID%TYPE INDEX BY BINARY_INTEGER;
1387 TYPE TRX_ID_TBL IS TABLE OF
1388 ZX_REP_TRX_DETAIL_T.TRX_ID%TYPE INDEX BY BINARY_INTEGER;
1389 TYPE TRX_LINE_ID_TBL IS TABLE OF
1390 ZX_REP_TRX_DETAIL_T.TRX_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
1391 TYPE TRX_LEVEL_TYPE_TBL IS TABLE OF
1392 ZX_REP_TRX_DETAIL_T.TRX_LEVEL_TYPE%TYPE INDEX BY BINARY_INTEGER;
1393 TYPE POS_TYPE_CODE_TBL IS TABLE OF
1394 ZX_REP_TRX_DETAIL_T.PLACE_OF_SUPPLY_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
1395 TYPE SHIP_TO_LOCATION_ID_TBL IS TABLE OF
1396 ZX_REP_TRX_DETAIL_T.SHIP_TO_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
1397 TYPE SHIP_FROM_LOCATION_ID_TBL IS TABLE OF
1398 ZX_REP_TRX_DETAIL_T.SHIP_FROM_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
1399 TYPE BILL_TO_LOCATION_ID_TBL IS TABLE OF
1400 ZX_REP_TRX_DETAIL_T.BILL_TO_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
1401 TYPE BILL_FROM_LOCATION_ID_TBL IS TABLE OF
1402 ZX_REP_TRX_DETAIL_T.BILL_FROM_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
1403 TYPE POA_LOCATION_ID_TBL IS TABLE OF
1404 ZX_REP_TRX_DETAIL_T.POA_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
1405 TYPE POO_LOCATION_ID_TBL IS TABLE OF
1406 ZX_REP_TRX_DETAIL_T.POO_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
1407 TYPE DEF_POS_TYPE_CODE_TBL IS TABLE OF
1408 ZX_REP_TRX_DETAIL_T.DEF_PLACE_OF_SUPPLY_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
1409 TYPE GEO_VAL_TBL IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1410 TYPE PERIOD_NAME_TBL IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
1411
1412 l_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
1413 l_event_class_mapping_id_tbl EVENT_CLASS_MAPPING_ID_TBL;
1414 l_trx_id_tbl ZX_EXTRACT_PKG.TRX_ID_TBL;
1415 l_trx_line_id_tbl TRX_LINE_ID_TBL;
1416 l_trx_level_type_tbl TRX_LEVEL_TYPE_TBL;
1417 l_pos_type_code_tbl POS_TYPE_CODE_TBL;
1418 l_ship_to_location_id_tbl SHIP_TO_LOCATION_ID_TBL;
1419 l_ship_from_location_id_tbl SHIP_FROM_LOCATION_ID_TBL;
1420 l_bill_to_location_id_tbl BILL_TO_LOCATION_ID_TBL;
1421 l_bill_from_location_id_tbl BILL_FROM_LOCATION_ID_TBL;
1422 l_poa_location_id_tbl POA_LOCATION_ID_TBL;
1423 l_poo_location_id_tbl POO_LOCATION_ID_TBL;
1424 l_def_pos_type_code_tbl DEF_POS_TYPE_CODE_TBL;
1425 l_state_tbl GEO_VAL_TBL;
1426 l_county_tbl GEO_VAL_TBL;
1427 l_city_tbl GEO_VAL_TBL;
1428 l_end_period_tbl PERIOD_NAME_TBL;
1429
1430 --Bug 5251425 : Variable Declarations for the Bug.
1431 l_ledger_id_tbl zx_extract_pkg.ledger_id_tbl;
1432 l_period_net_dr_tbl number_tbl;
1433 l_period_net_cr_tbl number_tbl;
1434 l_gl_activity_tbl number_tbl;
1435 t_gl_activity_tbl number_tbl;
1436 l_ccid_tbl ccid_tbl ;
1437
1438 l_amount_received_tbl NUMERIC2_TBL;
1439 l_tax_received_tbl NUMERIC1_TBL;
1440
1441 l_bal_seg_prompt_tbl ATTRIBUTE1_TBL;
1442
1443 l_set_of_books_id gl_sets_of_books.set_of_books_id%type;
1444 l_period_from gl_period_statuses.period_name%type;
1445 l_period_to gl_period_statuses.period_name%type;
1446
1447 --Bug 9031051
1448 l_reporting_code_tbl attribute2_tbl;
1449 l_reporting_code_char_tbl ATTRIBUTE10_TBL;
1450 l_adj_trx_id_tbl ZX_EXTRACT_PKG.ADJUSTED_DOC_TRX_ID_TBL;
1451 l_adj_trx_line_id_tbl ZX_EXTRACT_PKG.applied_to_trx_line_id_tbl;
1452 l_adj_application_id_tbl ZX_EXTRACT_PKG.ADJUSTED_DOC_APPL_ID_TBL;
1453 l_adj_event_class_code_tbl ZX_EXTRACT_PKG.ADJUSTED_DOC_EVENT_CLS_CD_TBL;
1454 l_event_class_code_tbl ZX_EXTRACT_PKG.EVENT_CLASS_CODE_TBL;
1455 l_adj_entity_code_tbl ZX_EXTRACT_PKG.ADJUSTED_DOC_ENTITY_CODE_TBL;
1456 l_adj_doc_date_tbl ZX_EXTRACT_PKG.ADJUSTED_DOC_DATE_TBL;
1457 l_out_of_period_adj_tbl attribute5_tbl;
1458 l_func_curr_line_amt_tbl numeric5_tbl;
1459 l_country_code_reg_num_tbl attribute1_tbl;
1460 l_country_code_tbl ZX_EXTRACT_PKG.BILLING_TP_COUNTRY_TBL;
1461 l_tax_reg_num_tbl attribute6_tbl;
1462 l_territory_short_name_tbl ATTRIBUTE2_TBL;
1463 l_alternate_territory_name_tbl ATTRIBUTE3_TBL;
1464 l_adj_tax_invoice_tbl ZX_EXTRACT_PKG.TAX_INVOICE_DATE_TBL;
1465 l_adj_gl_date_tbl ZX_EXTRACT_PKG.GL_DATE_TBL;
1466 l_adj_trx_date_tbl ZX_EXTRACT_PKG.TRX_DATE_TBL;
1467 l_org_id_tbl ZX_EXTRACT_PKG.INTERNAL_ORGANIZATION_ID_TBL;
1468 l_bill_to_tax_reg_num_tbl ZX_EXTRACT_PKG.BILLING_TP_TAX_REG_NUM_TBL;
1469 l_bill_to_site_tax_reg_num_tbl ZX_EXTRACT_PKG.BILLING_TP_SITE_TX_REG_NUM_TBL;
1470 l_ship_to_site_tax_reg_num_tbl ZX_EXTRACT_PKG.SHIPPING_TP_SITE_TX_RG_NUM_TBL;
1471 l_billing_tp_name_tbl ZX_EXTRACT_PKG.BILLING_TP_NAME_TBL;
1472 l_shipping_tp_name_tbl ZX_EXTRACT_PKG.SHIPPING_TP_NAME_TBL;
1473 l_adj_trx_class_tbl ZX_EXTRACT_PKG.TRX_CLASS_MNG_TBL;
1474 l_adj_trx_class_mng_tbl ZX_EXTRACT_PKG.TRX_CLASS_MNG_TBL;
1475 l_disc_class_mng_tbl ZX_EXTRACT_PKG.TRX_CLASS_MNG_TBL;
1476 l_hq_estb_ptp_id_tbl ZX_EXTRACT_PKG.SHIP_TO_PARTY_TAX_PROF_ID_TBL;
1477 l_hq_estb_reg_num_tbl ZX_EXTRACT_PKG.HQ_ESTB_REG_NUMBER_TBL;
1478 l_receivable_app_id_tbl ZX_EXTRACT_PKG.APPLIED_FROM_TRX_ID_TBL;
1479 l_disc_apply_date_tbl ZX_EXTRACT_PKG.TRX_DATE_TBL;
1480
1481 l_description VARCHAR2(240);
1482 l_meaning VARCHAR2(80);
1483
1484 BEGIN
1485
1486 -- IF PG_DEBUG = 'Y' THEN
1487 --
1488 -- arp_standard.debug('ZX.plsql.ZX_XX_EXTRACT_PKG.populate_tax_juris_for_usstr(+) ');
1489 -- END IF;
1490 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1491
1492 IF (g_level_procedure >= g_current_runtime_level ) THEN
1493 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP.BEGIN',
1494 'ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR(+)');
1495 END IF;
1496
1497
1498 -- Get necessary parameters from TRL Global Variables
1499
1500 P_REPORT_NAME := P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME;
1501 P_REQUEST_ID := P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1502 P_POSTING_STATUS := P_TRL_GLOBAL_VARIABLES_REC.POSTING_STATUS;
1503
1504 prev_event_class_mapping_id := 0;
1505
1506 IF (g_level_statement >= g_current_runtime_level ) THEN
1507 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1508 'p_report_name : '||P_REPORT_NAME);
1509 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1510 'p_request_id : '||p_request_id);
1511 END IF;
1512
1513 --Bug 9031051
1514
1515 IF P_REPORT_NAME = 'ZXXEUSL' THEN
1516 IF (g_level_statement >= g_current_runtime_level ) THEN
1517 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1518 'P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS: '||P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS );
1519 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1520 'P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES: '||P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES );
1521 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1522 'P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1: '||P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1 );
1523 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1524 'P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2: '||P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2 );
1525 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1526 'P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE: '||P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE);
1527 END IF;
1528
1529 IF P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS IS NULL
1530 AND P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES IS NULL
1531 AND P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1 IS NULL
1532 AND P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2 IS NULL
1533 THEN
1534
1535 IF (g_level_statement >= g_current_runtime_level ) THEN
1536 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1537 ' All Reporting codes parameters are NULL. Atleast one reporting code should be passed to '||
1538 ' TRL to filter data. Otherwise the extracted data will be deleted from TRL temp tables: ');
1539 END IF;
1540 ELSE
1541 BEGIN
1542 -- Directly populate the tax line id from Base Doc on which application is done.
1543 UPDATE zx_rep_trx_detail_t dtl
1544 set dtl.tax_line_id = (select min (tax_line_id)
1545 from zx_lines lines
1546 where lines.application_id = 222
1547 and lines.trx_id = dtl.ADJUSTED_DOC_TRX_ID
1548 and lines.trx_line_id = dtl.APPLIED_TO_TRX_LINE_ID
1549 and lines.tax_rate_id = dtl.tax_rate_id
1550 and nvl(lines.hq_estb_reg_number,fnd_api.g_miss_char) =
1551 P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
1552 )
1553 WHERE dtl.request_id = P_REQUEST_ID
1554 and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
1555 and dtl.APPLICATION_ID =222
1556 and dtl.APPLIED_FROM_ENTITY_CODE = 'APP';
1557
1558 -- Directly populate the tax line id from Adjustment Doc
1559 UPDATE zx_rep_trx_detail_t dtl
1560 set dtl.tax_line_id = (select min (tax_line_id)
1561 from zx_lines lines
1562 where lines.application_id = 222
1563 and lines.trx_id = dtl.ADJUSTED_DOC_TRX_ID
1564 and lines.trx_line_id = dtl.APPLIED_TO_TRX_LINE_ID
1565 AND lines.tax_rate_id = dtl.tax_rate_id
1566 AND nvl(lines.hq_estb_reg_number,fnd_api.g_miss_char) =
1567 P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
1568 )
1569 WHERE dtl.request_id = P_REQUEST_ID
1570 and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
1571 and dtl.APPLICATION_ID =222
1572 and dtl.EVENT_CLASS_CODE = 'ADJ';
1573
1574 /***
1575 UPDATE zx_rep_trx_detail_t dtl
1576 set tax_line_id = (select min (tax_line_id)
1577 from zx_lines lines
1578 where lines.application_id = 222
1579 and lines.trx_id = dtl.TRX_ID
1580 and lines.trx_line_id = dtl.TRX_LINE_ID)
1581 WHERE dtl.request_id = P_REQUEST_ID
1582 and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
1583 and dtl.APPLICATION_ID =222
1584 and dtl.EVENT_CLASS_CODE ='ADJ';
1585 ***/
1586 EXCEPTION WHEN OTHERS THEN
1587 IF ( g_level_statement>= g_current_runtime_level ) THEN
1588 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1589 'Error in updating tax line id zx_rep_trx_detail_t: Error Message : '||substrb(SQLERRM,1,120) );
1590 END IF;
1591 END;
1592
1593
1594 BEGIN
1595 SELECT dtl.detail_tax_line_id,
1596 dtl.trx_id,
1597 dtl.trx_line_id,
1598 dtl.event_class_code,
1599 --rep_code.reporting_code_name,
1600 --assoc.reporting_code_char_value,
1601 ZX_EXTRACT_PKG.get_vat_transaction_code_name(
1602 dtl.tax_line_id,
1603 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE,
1604 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS,
1605 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES,
1606 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1,
1607 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2,
1608 'NAME'),
1609 ZX_EXTRACT_PKG.get_vat_transaction_code_name(
1610 dtl.tax_line_id,
1611 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE,
1612 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS,
1613 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES,
1614 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1,
1615 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2,
1616 'CODE'),
1617 dtl.adjusted_doc_trx_id,
1618 NVL(dtl.applied_from_trx_id,NULL),
1619 NVL(dtl.applied_to_trx_line_id,NULL), -- Adjusted_doc_line_id
1620 dtl.adjusted_doc_application_id,
1621 dtl.adjusted_doc_event_class_code,
1622 dtl.adjusted_doc_entity_code,
1623 dtl.adjusted_doc_date,
1624 decode(P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SITE_REPORTED,'SHIP TO', dtl.shipping_tp_country,
1625 dtl.billing_tp_country),
1626 dtl.internal_organization_id,
1627 NVL(dtl.SHIPPING_TP_SITE_TAX_REG_NUM,NULL),
1628 NVL(dtl.BILLING_TP_SITE_TAX_REG_NUM,NULL),
1629 NVL(dtl.BILLING_TP_TAX_REG_NUM,NULL),
1630 dtl.billing_tp_name,
1631 dtl.shipping_tp_name,
1632 NULL,
1633 NULL,
1634 NULL,
1635 NULL,
1636 NULL,
1637 NULL,
1638 NULL,
1639 NULL,
1640 NULL,
1641 NULL,
1642 NULL
1643 BULK COLLECT
1644 INTO l_detail_tax_line_id_tbl,
1645 l_trx_id_tbl,
1646 l_trx_line_id_tbl,
1647 l_event_class_code_tbl,
1648 l_reporting_code_tbl,
1649 l_reporting_code_char_tbl,
1650 l_adj_trx_id_tbl,
1651 l_receivable_app_id_tbl,
1652 l_adj_trx_line_id_tbl,
1653 l_adj_application_id_tbl,
1654 l_adj_event_class_code_tbl,
1655 l_adj_entity_code_tbl,
1656 l_adj_doc_date_tbl,
1657 l_country_code_tbl,
1658 l_org_id_tbl,
1659 l_ship_to_site_tax_reg_num_tbl,
1660 l_bill_to_site_tax_reg_num_tbl,
1661 l_bill_to_tax_reg_num_tbl,
1662 l_billing_tp_name_tbl,
1663 l_shipping_tp_name_tbl,
1664 l_out_of_period_adj_tbl,
1665 l_func_curr_line_amt_tbl,
1666 l_country_code_reg_num_tbl,
1667 l_tax_reg_num_tbl,
1668 l_adj_tax_invoice_tbl,
1669 l_adj_gl_date_tbl,
1670 l_adj_trx_date_tbl,
1671 l_adj_trx_class_mng_tbl,
1672 l_hq_estb_reg_num_tbl,
1673 l_disc_class_mng_tbl,
1674 l_disc_apply_date_tbl
1675 FROM zx_rep_trx_detail_t dtl
1676 WHERE dtl.request_id = P_REQUEST_ID
1677 and ZX_EXTRACT_PKG.get_vat_transaction_code_name(
1678 dtl.tax_line_id,
1679 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE,
1680 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS,
1681 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES,
1682 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1,
1683 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2,
1684 'CODE') IS NOT NULL;
1685
1686 EXCEPTION
1687 WHEN OTHERS THEN
1688 IF ( g_level_statement>= g_current_runtime_level ) THEN
1689 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1690 'Error in fetching data from zx_rep_detail_t: Error Message : '||substrb(SQLERRM,1,120) );
1691 END IF;
1692 END;
1693
1694 IF ( g_level_statement>= g_current_runtime_level ) THEN
1695 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1696 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
1697 END IF;
1698
1699 FOR i in 1..Nvl(l_detail_tax_line_id_tbl.last,0) LOOP
1700
1701 IF ( g_level_statement>= g_current_runtime_level ) THEN
1702 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1703 'In l_detail_tax_line_id_tbl Loop'||to_char(l_detail_tax_line_id_tbl(i)));
1704 END IF;
1705
1706 IF l_adj_trx_id_tbl(i) IS NOT NULL THEN
1707
1708 IF ( g_level_statement>= g_current_runtime_level ) THEN
1709 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1710 'Debug: l_trx_id_tbl(i) IS NOT NULL '||to_char(l_trx_id_tbl(i)) );
1711 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1712 'Debug: l_org_id_tbl(i) IS NOT NULL '||to_char(l_org_id_tbl(i)) );
1713 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1714 'Debug: l_adj_trx_id_tbl(i) IS NOT NULL '||to_char(l_adj_trx_id_tbl(i)) );
1715 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1716 'Debug: l_adj_trx_line_id_tbl(i) IS NOT NULL '||to_char(l_adj_trx_line_id_tbl(i)) );
1717 END IF;
1718 -- To avoid repopulating the data for same item line
1719 IF (i = 1 OR l_trx_id_tbl(i) <> l_trx_id_tbl(i-1)
1720 OR nvl(l_adj_trx_line_id_tbl(i),l_trx_line_id_tbl(i))
1721 <> nvl(l_adj_trx_line_id_tbl(i-1),l_trx_line_id_tbl(i-1))) THEN
1722 BEGIN
1723 SELECT TAX_INVOICE_DATE,
1724 TRX_LINE_GL_DATE,
1725 TRX_DATE,
1726 LINE_CLASS,
1727 HQ_ESTB_PARTY_TAX_PROF_ID
1728 INTO l_adj_tax_invoice_tbl(i),
1729 l_adj_gl_date_tbl(i),
1730 l_adj_trx_date_tbl(i),
1731 l_adj_trx_class_tbl(i),
1732 l_hq_estb_ptp_id_tbl(i)
1733 FROM ZX_LINES_DET_FACTORS zx_det
1734 WHERE zx_det.application_id = 222
1735 AND zx_det.trx_id = l_adj_trx_id_tbl(i)
1736 and zx_det.trx_line_id = Nvl(l_adj_trx_line_id_tbl(i),zx_det.trx_line_id)
1737 AND ROWNUM = 1;
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 IF (g_level_statement>= g_current_runtime_level ) THEN
1741 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1742 'Error in fetching data from ZX_LINES_DET_FACTORS for adjusted doc tax invoice date and gl date. Error Message : '
1743 ||substrb(SQLERRM,1,120) );
1744 END IF;
1745 END;
1746
1747 IF ((l_adj_tax_invoice_tbl(i) IS NOT NULL
1748 AND (l_adj_tax_invoice_tbl(i) < P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_LOW
1749 OR l_adj_tax_invoice_tbl(i) > P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH))
1750 OR (P_TRL_GLOBAL_VARIABLES_REC.ESL_DEFAULT_TAX_DATE = 'GL DATE'
1751 AND (l_adj_gl_date_tbl(i) < P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_LOW
1752 OR l_adj_gl_date_tbl(i) > P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH))
1753 OR (P_TRL_GLOBAL_VARIABLES_REC.ESL_DEFAULT_TAX_DATE = 'TRX DATE'
1754 AND (l_adj_doc_date_tbl(i) < P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_LOW
1755 OR l_adj_doc_date_tbl(i) > P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH)))
1756 THEN
1757 l_out_of_period_adj_tbl(i) := 'OUT_PERIOD_ADJUSTMENT';
1758 ELSE
1759 l_out_of_period_adj_tbl(i) := 'IN_PERIOD_ADJUSTMENT';
1760 END IF;
1761
1762 IF l_adj_tax_invoice_tbl(i) IS NULL THEN
1763 IF P_TRL_GLOBAL_VARIABLES_REC.ESL_DEFAULT_TAX_DATE = 'GL DATE' THEN
1764 l_adj_tax_invoice_tbl(i) := l_adj_gl_date_tbl(i);
1765 ELSIF P_TRL_GLOBAL_VARIABLES_REC.ESL_DEFAULT_TAX_DATE = 'TRX DATE' THEN
1766 l_adj_tax_invoice_tbl(i) := l_adj_trx_date_tbl(i);
1767 END IF;
1768 END IF;
1769
1770 IF l_hq_estb_ptp_id_tbl(i) IS NOT NULL THEN
1771 SELECT registration_number
1772 INTO l_hq_estb_reg_num_tbl(i)
1773 FROM zx_registrations
1774 WHERE party_tax_profile_id = l_hq_estb_ptp_id_tbl(i)
1775 AND registration_number = P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
1776 AND rownum = 1 ;
1777 END IF;
1778
1779
1780 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_TRL_TAXABLE_TRX_TYPE',
1781 l_adj_trx_class_tbl(i),
1782 l_meaning,
1783 l_description);
1784
1785 l_adj_trx_class_mng_tbl(i) := l_meaning;
1786 ELSE -- Do not repopulate the existing data
1787 IF ( g_level_statement>= g_current_runtime_level ) THEN
1788 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1789 'Debug: Not repopulating the data');
1790 END IF;
1791 IF ( g_level_statement>= g_current_runtime_level ) THEN
1792 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1793 'Debug: l_adj_trx_id_tbl(i) IS NOT NULL '||to_char(l_adj_trx_id_tbl(i))||' '||to_char(l_adj_trx_line_id_tbl(i)) );
1794 END IF;
1795 l_adj_tax_invoice_tbl(i) := l_adj_tax_invoice_tbl(i-1);
1796 l_adj_gl_date_tbl(i) := l_adj_gl_date_tbl(i-1);
1797 l_out_of_period_adj_tbl(i) := l_out_of_period_adj_tbl(i-1);
1798 l_adj_trx_class_mng_tbl(i) := l_adj_trx_class_mng_tbl(i-1);
1799 END IF;
1800 ELSE
1801 l_out_of_period_adj_tbl(i) := 'IN_PERIOD_ADJUSTMENT';
1802 END IF; --adj_trx_id is not null
1803
1804 IF (i = 1 OR l_trx_id_tbl(i) <> l_trx_id_tbl(i-1)
1805 OR Nvl(l_adj_trx_line_id_tbl(i),l_trx_line_id_tbl(i)) <> Nvl(l_adj_trx_line_id_tbl(i-1),l_trx_line_id_tbl(i-1))) THEN
1806 --Code for populating the functional amount
1807 IF ( g_level_statement>= g_current_runtime_level ) THEN
1808 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1809 'Debug: Populating functional amount' );
1810 END IF;
1811
1812 BEGIN
1813
1814 IF l_event_class_code_tbl(i) = 'ADJ' THEN
1815 IF (g_level_statement>= g_current_runtime_level ) THEN
1816 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1817 'Debug: Populating functional amount ADJ - l_adj_trx_line_id_tbl : '||to_char(l_adj_trx_line_id_tbl(i) ));
1818 END IF;
1819
1820 SELECT sum(nvl(ACCTD_AMOUNT_CR,0) - nvl(ACCTD_AMOUNT_DR,0))
1821 INTO l_func_curr_line_amt_tbl(i)
1822 FROM ar_distributions_all
1823 WHERE source_id = l_trx_id_tbl(i)
1824 AND source_table = 'ADJ'
1825 AND ref_customer_trx_line_id = l_adj_trx_line_id_tbl(i)
1826 AND org_id = l_org_id_tbl(i);
1827
1828 IF (g_level_statement>= g_current_runtime_level ) THEN
1829 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1830 'Debug: After Populating functional amount ADJ : '||to_char(l_func_curr_line_amt_tbl(i) ));
1831 END IF;
1832 ELSIF l_event_class_code_tbl(i) IN ('EDISC', 'UNEDISC') THEN
1833 IF (g_level_statement>= g_current_runtime_level ) THEN
1834 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1835 'Debug: Populating functional amount Discounts - l_adj_trx_line_id_tbl : '||to_char(l_adj_trx_line_id_tbl(i) ));
1836 END IF;
1837
1838 SELECT sum(nvl(ACCTD_AMOUNT_CR,0) - nvl(ACCTD_AMOUNT_DR,0))
1839 INTO l_func_curr_line_amt_tbl(i)
1840 FROM ar_distributions_all
1841 WHERE source_id = l_receivable_app_id_tbl(i)
1842 AND source_table = 'RA'
1843 AND source_type IN ('EDISC', 'UNEDISC')
1844 AND ref_customer_trx_line_id = l_adj_trx_line_id_tbl(i)
1845 AND org_id = l_org_id_tbl(i);
1846
1847 IF (g_level_statement>= g_current_runtime_level ) THEN
1848 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1849 'Debug: After Populating functional amount for Discount :'||to_char(l_func_curr_line_amt_tbl(i) ));
1850 END IF;
1851
1852 ZX_AP_POPULATE_PKG.lookup_desc_meaning(
1853 'ZX_TRL_TAXABLE_TRX_TYPE',
1854 'DISC',
1855 l_meaning,
1856 l_description);
1857 l_disc_class_mng_tbl(i) := l_meaning;
1858
1859 IF (g_level_statement>= g_current_runtime_level ) THEN
1860 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1861 'Debug: After Populating class meaning for Discounts :'||to_char(l_disc_class_mng_tbl(i) ));
1862 END IF;
1863
1864 BEGIN
1865 SELECT apply_date
1866 INTO l_disc_apply_date_tbl(i)
1867 FROM ar_receivable_applications_all
1868 WHERE cash_receipt_id = l_trx_id_tbl(i)
1869 AND status ='APP'
1870 AND applied_customer_trx_id = l_adj_trx_id_tbl(i)
1871 AND org_id = l_org_id_tbl(i) ;
1872
1873 IF (g_level_statement>= g_current_runtime_level ) THEN
1874 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1875 'Debug: After Populating apply date for discount :'||l_disc_apply_date_tbl(i));
1876 END IF;
1877 EXCEPTION
1878 WHEN OTHERS THEN
1879 l_disc_apply_date_tbl(i) := NULL;
1880 IF (g_level_statement>= g_current_runtime_level ) THEN
1881 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1882 'Debug: In Exception setting Apply date to NULL');
1883 END IF;
1884 END;
1885 ELSE
1886 SELECT sum(nvl(ACCTD_AMOUNT,0))
1887 INTO l_func_curr_line_amt_tbl(i)
1888 FROM ra_cust_trx_line_gl_dist_all
1889 WHERE customer_trx_id = l_trx_id_tbl(i)
1890 AND customer_trx_line_id = l_trx_line_id_tbl(i)
1891 AND org_id = l_org_id_tbl(i);
1892
1893 IF (g_level_statement>= g_current_runtime_level ) THEN
1894 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1895 'Debug: Populating functional amount for Transactions :'||to_char(l_func_curr_line_amt_tbl(i) ));
1896 END IF;
1897 END IF;
1898
1899 EXCEPTION
1900 WHEN OTHERS THEN
1901 IF (g_level_statement>= g_current_runtime_level ) THEN
1902 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1903 'Error in fetching data from ra_cust_trx_line_gl_dist_all for functional amount. Error Message : '
1904 ||substrb(SQLERRM,1,120) );
1905 END IF;
1906 END;
1907
1908
1909 -- Changed code as per Amit's new document --
1910
1911 IF l_shipping_tp_name_tbl(i) = l_billing_tp_name_tbl(i) THEN
1912 IF l_ship_to_site_tax_reg_num_tbl(i) is not null THEN
1913 IF SubStr(l_ship_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
1914 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
1915 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
1916 THEN
1917 l_country_code_reg_num_tbl(i) := SubStr(l_ship_to_site_tax_reg_num_tbl(i),1,2);
1918 l_tax_reg_num_tbl(i) := SubStr(l_ship_to_site_tax_reg_num_tbl(i),3);
1919 ELSE -- no country at ship to site reg --- print always from site reported paramater
1920 /*IF SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
1921 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
1922 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB') THEN
1923
1924 l_country_code_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2);*/
1925 --ELSE
1926 l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
1927 --END IF;
1928
1929 l_tax_reg_num_tbl(i) := l_ship_to_site_tax_reg_num_tbl(i);
1930
1931 END IF;
1932 -- No ship to reg num
1933 ELSIF l_bill_to_site_tax_reg_num_tbl(i) is not null THEN
1934 IF SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
1935 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
1936 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
1937 THEN
1938 l_country_code_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2);
1939 l_tax_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),3);
1940 ELSE
1941 l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
1942 l_tax_reg_num_tbl(i) := l_bill_to_site_tax_reg_num_tbl(i);
1943 END IF;
1944 --l_tax_reg_num_tbl(i) := l_bill_to_site_tax_reg_num_tbl(i);
1945 END IF; -- End ship reg num check --
1946 ELSE -- shp to pty <> bil to pty --
1947 IF l_bill_to_site_tax_reg_num_tbl(i) is not null THEN
1948 IF SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
1949 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
1950 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
1951 THEN
1952 l_country_code_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2);
1953 l_tax_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),3);
1954 ELSE
1955 l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
1956 l_tax_reg_num_tbl(i) := l_bill_to_site_tax_reg_num_tbl(i);
1957 END IF;
1958 ELSE
1959 l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
1960 END IF;
1961 END IF; -- Party check --
1962
1963 -- get reg num from bill to party --
1964 IF l_ship_to_site_tax_reg_num_tbl(i) is null AND l_bill_to_site_tax_reg_num_tbl(i) is null
1965 THEN
1966 IF l_bill_to_tax_reg_num_tbl(i) IS NOT NULL
1967 THEN
1968 IF substr(l_bill_to_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
1969 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
1970 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
1971 THEN
1972 l_country_code_reg_num_tbl(i) := SubStr(l_bill_to_tax_reg_num_tbl(i),1,2);
1973 l_tax_reg_num_tbl(i) := SubStr(l_bill_to_tax_reg_num_tbl(i),3);
1974 ELSE
1975 l_tax_reg_num_tbl(i) := l_bill_to_tax_reg_num_tbl(i);
1976 l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
1977 END IF;
1978 ELSE
1979 l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
1980 END IF;
1981 END IF;
1982
1983 /*
1984 --Code for populating country code
1985 IF ( g_level_statement>= g_current_runtime_level ) THEN
1986 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1987 'Debug: Populating country code from tax reg num' );
1988 END IF;
1989
1990 IF l_ship_to_site_tax_reg_num_tbl(i) is not null THEN
1991 IF SubStr(l_ship_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
1992 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
1993 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
1994 THEN
1995 IF l_shipping_tp_name_tbl(i) = l_billing_tp_name_tbl(i) THEN
1996 l_country_code_reg_num_tbl(i) := SubStr(l_ship_to_site_tax_reg_num_tbl(i),1,2);
1997 ELSE
1998 IF SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
1999 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
2000 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB') THEN
2001 l_country_code_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2);
2002 END IF;
2003 END IF;
2004 ELSE
2005
2006 IF SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
2007 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
2008 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB') THEN
2009
2010 l_country_code_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2);
2011 ELSE
2012 l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
2013 END IF;
2014 END IF;
2015 ELSIF l_bill_to_site_tax_reg_num_tbl(i) is not null THEN
2016 IF SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
2017 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
2018 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
2019 THEN
2020 l_country_code_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2);
2021 ELSE
2022 l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
2023 END IF;
2024 ELSE */ -- New comments --
2025 /*ELSIF substr(l_bill_to_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
2026 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
2027 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
2028 THEN
2029 l_country_code_reg_num_tbl(i) := SubStr(l_bill_to_tax_reg_num_tbl(i),1,2);
2030 ELSE */
2031 -- l_country_code_reg_num_tbl(i) := l_country_code_tbl(i);
2032 -- END IF;
2033
2034 /* BEGIN
2035 SELECT rep_code.reporting_code_char_value
2036 INTO l_country_code_reg_num_tbl(i)
2037 FROM zx_reporting_types_b rep_type,
2038 zx_reporting_codes_b rep_code
2039 WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
2040 AND rep_type.reporting_type_code = 'MEMBER STATE'
2041 AND rep_code.reporting_code_char_value
2042 = (SubStr(Nvl(Nvl(l_ship_to_site_tax_reg_num_tbl(i),
2043 l_bill_to_site_tax_reg_num_tbl(i)),
2044 l_bill_to_tax_reg_num_tbl(i)),1,2));
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 IF (g_level_statement>= g_current_runtime_level ) THEN
2048 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2049 'Error in comparing the country code from the reg num. Error Message : '
2050 ||substrb(SQLERRM,1,120) );
2051 END IF;
2052 END;
2053 */
2054 /* IF l_ship_to_site_tax_reg_num_tbl(i) IS NOT NULL THEN
2055 IF l_shipping_tp_name_tbl(i) = l_billing_tp_name_tbl(i) THEN
2056 IF SubStr(l_ship_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
2057 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
2058 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
2059 THEN
2060 l_tax_reg_num_tbl(i) := SubStr(l_ship_to_site_tax_reg_num_tbl(i),3);
2061 ELSE
2062 l_tax_reg_num_tbl(i) := l_ship_to_site_tax_reg_num_tbl(i);
2063 END IF;
2064 ELSE
2065 IF SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
2066 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
2067 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
2068 THEN
2069 l_tax_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),3);
2070 ELSE
2071 l_tax_reg_num_tbl(i) := l_bill_to_site_tax_reg_num_tbl(i);
2072 END IF;
2073 END IF;
2074
2075 ELSIF l_bill_to_site_tax_reg_num_tbl(i) IS NOT NULL THEN
2076 IF SubStr(l_bill_to_site_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
2077 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
2078 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
2079 THEN
2080 l_tax_reg_num_tbl(i) := SubStr(l_bill_to_site_tax_reg_num_tbl(i),3);
2081 ELSE
2082 l_tax_reg_num_tbl(i) := l_bill_to_site_tax_reg_num_tbl(i);
2083 END IF;
2084
2085 ELSIF l_bill_to_tax_reg_num_tbl(i) IS NOT NULL THEN
2086 IF substr(l_bill_to_tax_reg_num_tbl(i),1,2) IN ('AT','BE','BG','CY','CZ','DK','EE','FI',
2087 'FR','DE','GR','HU','IE','IT', 'LV','LT','LU',
2088 'MT','NL','PL','PT','RO','SK','SI','ES','SE','GB')
2089 THEN
2090 l_tax_reg_num_tbl(i) := SubStr(l_bill_to_tax_reg_num_tbl(i),3);
2091 ELSE
2092 l_tax_reg_num_tbl(i) := l_bill_to_tax_reg_num_tbl(i);
2093 END IF;
2094 END IF; */
2095
2096 /* IF l_country_code_reg_num_tbl(i) IS NOT NULL THEN
2097 l_tax_reg_num_tbl(i) := SubStr(Nvl(Nvl(l_ship_to_site_tax_reg_num_tbl(i),
2098 l_bill_to_site_tax_reg_num_tbl(i)),
2099 l_bill_to_tax_reg_num_tbl(i)),3);
2100 END IF; */
2101 ELSE
2102 IF ( g_level_statement>= g_current_runtime_level ) THEN
2103 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2104 'Debug: Not repopulating country code' );
2105 END IF;
2106
2107
2108 IF l_event_class_code_tbl(i) <> 'ADJ' THEN
2109 l_func_curr_line_amt_tbl(i) := 0;
2110 END IF;
2111 l_tax_reg_num_tbl(i) := l_tax_reg_num_tbl(i-1);
2112 l_hq_estb_reg_num_tbl(i) := l_hq_estb_reg_num_tbl(i-1);
2113 l_country_code_reg_num_tbl(i) := l_country_code_reg_num_tbl(i-1);
2114 -- l_country_code_tbl(i) := l_country_code_tbl(i-1);
2115
2116
2117 END IF;
2118 END LOOP;
2119
2120 IF ( g_level_statement>= g_current_runtime_level ) THEN
2121 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2122 ' Inserting Data into ZX_REP_TRX_JX_EXT_T ' );
2123 END IF;
2124
2125 FORALL i in nvl(l_detail_tax_line_id_tbl.first,1)..nvl(l_detail_tax_line_id_tbl.last,0)
2126 INSERT INTO ZX_REP_TRX_JX_EXT_T
2127 (detail_tax_line_ext_id,
2128 detail_tax_line_id,
2129 attribute1, --County_code
2130 attribute2, --Reporting Code Name
2131 attribute10, --Reporting Code
2132 attribute3, --Adjusted doc GL Date
2133 attribute4, --Adjusted doc Tax date
2134 attribute5, --Out of period adjustments
2135 attribute6, --Tax reg num
2136 attribute7, -- adjusted doc trx type
2137 attribute8, -- adjustment hq estb reg num
2138 attribute9, -- Apply date for discounts
2139 attribute11, -- Disc trx class
2140 numeric1, --Functional currency line amount
2141 created_by,
2142 creation_date,
2143 last_updated_by,
2144 last_update_date,
2145 last_update_login,
2146 request_id)
2147 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
2148 l_detail_tax_line_id_tbl(i),
2149 l_country_code_reg_num_tbl(i),
2150 l_reporting_code_tbl(i),
2151 l_reporting_code_char_tbl(i),
2152 l_adj_gl_date_tbl(i),
2153 l_adj_tax_invoice_tbl(i),
2154 l_out_of_period_adj_tbl(i),
2155 l_tax_reg_num_tbl(i),
2156 l_adj_trx_class_mng_tbl(i),
2157 l_hq_estb_reg_num_tbl(i),
2158 l_disc_apply_date_tbl(i),
2159 l_disc_class_mng_tbl(i),
2160 l_func_curr_line_amt_tbl(i),
2161 fnd_global.user_id,
2162 sysdate,
2163 fnd_global.user_id,
2164 sysdate,
2165 fnd_global.login_id,
2166 P_REQUEST_ID);
2167
2168 IF ( g_level_statement>= g_current_runtime_level ) THEN
2169 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2170 'After insertion into zx_rep_trx_jx_ext_t ');
2171 END IF;
2172
2173
2174 DELETE FROM zx_rep_trx_detail_t dtl
2175 WHERE dtl.request_id = p_request_id
2176 AND NOT EXISTS ( SELECT 1
2177 FROM zx_rep_trx_jx_ext_t ext
2178 WHERE ext.detail_tax_line_id = dtl.detail_tax_line_id);
2179
2180 IF ( g_level_statement>= g_current_runtime_level ) THEN
2181 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2182 'After deleting data from zx_rep_trx_detail_t ' ||to_char(SQL%ROWCOUNT));
2183 END IF;
2184 END IF; -- Reporting codes parameters NULL check --
2185 END IF; -- report_name
2186 --End Bug 9031051
2187
2188 IF P_REPORT_NAME = 'RXZXPFTR' THEN
2189 IF ( g_level_statement>= g_current_runtime_level ) THEN
2190 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AR',
2191 'P_REPORT_NAME: '||P_REPORT_NAME );
2192 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AR',
2193 'P_POSTING_STATUS : '|| P_POSTING_STATUS );
2194 END IF;
2195
2196 IF P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
2197 IF P_POSTING_STATUS = 'TRANSFERRED' THEN
2198 DELETE FROM zx_rep_actg_ext_t
2199 WHERE request_id = p_request_id
2200 AND NVL(gl_transfer_flag,'N') <>'Y';
2201
2202 IF ( g_level_statement>= g_current_runtime_level ) THEN
2203 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AR',
2204 'No of Unposted transactions deleted from zx_rep_actg_ext_t: '|| sql%rowcount);
2205 END IF;
2206
2207 DELETE FROM zx_rep_trx_detail_t dtl
2208 WHERE dtl.request_id = p_request_id
2209 AND NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
2210 WHERE act.detail_tax_line_id = dtl.detail_tax_line_id) ;
2211
2212 IF ( g_level_statement>= g_current_runtime_level ) THEN
2213 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AR',
2214 'No of rows deleted from zx_rep_trx_detail_t: '|| sql%rowcount);
2215 END IF;
2216 ELSIF P_POSTING_STATUS = 'NOT_TRANSFERRED' THEN
2217 DELETE FROM zx_rep_actg_ext_t
2218 WHERE request_id = p_request_id
2219 AND NVL(gl_transfer_flag,'N') = 'Y';
2220 IF ( g_level_statement>= g_current_runtime_level ) THEN
2221 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AR',
2222 'No of posted transactions deleted from zx_rep_actg_ext_t: '|| sql%rowcount);
2223 END IF;
2224 DELETE FROM zx_rep_trx_detail_t dtl
2225 WHERE dtl.request_id = p_request_id
2226 AND NVL(dtl.posted_flag,'N') = 'Y'
2227 AND NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
2228 WHERE act.detail_tax_line_id = dtl.detail_tax_line_id);
2229
2230 IF ( g_level_statement>= g_current_runtime_level ) THEN
2231 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_CORE_AR',
2232 'No of rows deleted from zx_rep_trx_detail_t: '|| sql%rowcount);
2233 END IF;
2234 END IF;
2235 END IF; -- Summary Level --
2236 ELSIF P_REPORT_NAME = 'ARXSTR' THEN
2237
2238 BEGIN
2239 SELECT detail_tax_line_id,
2240 event_class_mapping_id,
2241 trx_id,
2242 trx_line_id,
2243 trx_level_type,
2244 place_of_supply_type_code,
2245 ship_to_location_id,
2246 ship_from_location_id,
2247 bill_to_location_id,
2248 bill_from_location_id,
2249 poa_location_id,
2250 poo_location_id,
2251 def_place_of_supply_type_code
2252 BULK COLLECT INTO l_detail_tax_line_id_tbl,
2253 l_event_class_mapping_id_tbl,
2254 l_trx_id_tbl,
2255 l_trx_line_id_tbl,
2256 l_trx_level_type_tbl,
2257 l_pos_type_code_tbl,
2258 l_ship_to_location_id_tbl,
2259 l_ship_from_location_id_tbl,
2260 l_bill_to_location_id_tbl,
2261 l_bill_from_location_id_tbl,
2262 l_poa_location_id_tbl,
2263 l_poo_location_id_tbl,
2264 l_def_pos_type_code_tbl
2265 FROM zx_rep_trx_detail_t itf
2266 WHERE itf.request_id = P_REQUEST_ID;
2267 EXCEPTION
2268 WHEN OTHERS THEN
2269 null;
2270 /*
2271 IF PG_DEBUG = 'Y' THEN
2272 l_err_msg := substrb(SQLERRM,1,120);
2273 arp_standard.debug('ZX_XX_EXTRACT_PKG.us_sales_tax_rep.'|| P_REPORT_NAME ||':'||l_err_msg);
2274 END IF;
2275 */
2276 END;
2277
2278 IF ( g_level_statement>= g_current_runtime_level ) THEN
2279 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2280 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
2281 END IF;
2282
2283 FOR i in 1..l_trx_id_tbl.last LOOP
2284
2285 l_place_of_supply := nvl(l_pos_type_code_tbl(i),nvl(l_def_pos_type_code_tbl(i),'SHIP_TO'));
2286 IF l_place_of_supply = 'SHIP_TO' THEN
2287 l_location_type_tbl(1):= 'SHIP_TO';
2288 l_location_id_tbl(1):= l_ship_to_location_id_tbl(i);
2289 ELSIF l_place_of_supply = 'SHIP_FROM' THEN
2290 l_location_type_tbl(1):= 'SHIP_FROM';
2291 l_location_id_tbl(1):= l_ship_from_location_id_tbl(i);
2292 ELSIF l_place_of_supply = 'BILL_TO' THEN
2293 l_location_type_tbl(1):= 'BILL_TO';
2294 l_location_id_tbl(1):= l_bill_to_location_id_tbl(i);
2295 ELSIF l_place_of_supply = 'BILL_FROM' THEN
2296 l_location_type_tbl(1):= 'BILL_FROM';
2297 l_location_id_tbl(1):= l_bill_from_location_id_tbl(i);
2298 ELSIF l_place_of_supply = 'POA' THEN
2299 l_location_type_tbl(1):= 'POA';
2300 l_location_id_tbl(1):= l_poa_location_id_tbl(i);
2301 ELSIF l_place_of_supply = 'POO' THEN
2302 l_location_type_tbl(1):= 'POO';
2303 l_location_id_tbl(1):= l_poo_location_id_tbl(i);
2304 END IF;
2305
2306
2307 /* Check existence of input combination in table ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl */
2308
2309 RETRIEVE_GEO_VALUE(l_event_class_mapping_id_tbl(i),
2310 l_trx_id_tbl(i),
2311 l_trx_line_id_tbl(i),
2312 l_trx_level_type_tbl(i),
2313 l_location_type_tbl(1),
2314 l_location_id_tbl(1),
2315 'STATE',
2316 l_state_tbl(i),
2317 l_geo_val_found);
2318
2319 IF ( g_level_statement>= g_current_runtime_level ) THEN
2320 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2321 'l_geo_val_found : '||arp_trx_util.boolean_to_varchar2(l_geo_val_found));
2322 END IF;
2323
2324 IF l_geo_val_found THEN
2325 null;
2326 ELSE
2327 IF prev_event_class_mapping_id <> l_event_class_mapping_id_tbl(i) THEN
2328 BEGIN
2329 SELECT
2330 zxevntclsmap.event_class_mapping_id,
2331 zxevntclsmap.ship_to_party_type,
2332 zxevntclsmap.ship_from_party_type,
2333 zxevntclsmap.poa_party_type,
2334 zxevntclsmap.poo_party_type,
2335 zxevntclsmap.bill_to_party_type,
2336 zxevntclsmap.bill_from_party_type
2337 INTO
2338 prev_event_class_mapping_id,
2339 zx_valid_init_params_pkg.source_rec.ship_to_party_type,
2340 zx_valid_init_params_pkg.source_rec.ship_from_party_type,
2341 zx_valid_init_params_pkg.source_rec.poa_party_type,
2342 zx_valid_init_params_pkg.source_rec.poo_party_type,
2343 zx_valid_init_params_pkg.source_rec.bill_to_party_type,
2344 zx_valid_init_params_pkg.source_rec.bill_from_party_type
2345 FROM ZX_EVNT_CLS_MAPPINGS zxevntclsmap
2346 WHERE zxevntclsmap.event_class_mapping_id = l_event_class_mapping_id_tbl(i);
2347
2348 IF ( g_level_statement>= g_current_runtime_level ) THEN
2349 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2350 'Rows fetched from ZX_EVNT_CLS_MAPPINGS : '||to_char(SQL%ROWCOUNT) );
2351 END IF;
2352 EXCEPTION
2353 WHEN OTHERS THEN
2354 null;
2355 IF ( g_level_statement>= g_current_runtime_level ) THEN
2356 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2357 'Error Message ZX_EVNT_CLS_MAPPINGS : '||substrb(SQLERRM,1,120) );
2358 END IF;
2359 /*
2360 IF PG_DEBUG = 'Y' THEN
2361 l_err_msg := substrb(SQLERRM,1,120);
2362 arp_standard.debug('ZX_XX_EXTRACT_PKG.us_sales_tax_rep.'|| P_REPORT_NAME ||':'||l_err_msg);
2363 END IF;
2364 */
2365 END;
2366 END IF;
2367
2368 BEGIN
2369
2370 IF ( g_level_statement>= g_current_runtime_level ) THEN
2371 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2372 'Before call to ZX_TCM_GEO_JUR_PKG.populate_loc_geography_info ');
2373 END IF;
2374
2375 ZX_TCM_GEO_JUR_PKG.populate_loc_geography_info
2376 (l_event_class_mapping_id_tbl(i),
2377 l_trx_id_tbl(i),
2378 l_trx_line_id_tbl(i),
2379 l_trx_level_type_tbl(i),
2380 l_location_type_tbl,
2381 l_location_id_tbl,
2382 x_return_status);
2383 EXCEPTION
2384 WHEN OTHERS THEN
2385 null;
2386 IF ( g_level_statement>= g_current_runtime_level ) THEN
2387 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2388 'Error Message : ZX_TCM_GEO_JUR_PKG.populate_loc_geography_info : '||substrb(SQLERRM,1,120) );
2389 END IF;
2390 /*
2391 IF PG_DEBUG = 'Y' THEN
2392 l_err_msg := substrb(SQLERRM,1,120);
2393 arp_standard.debug('ZX_XX_EXTRACT_PKG.us_sales_tax_rep.'|| P_REPORT_NAME ||':'||l_err_msg);
2394 END IF;
2395 */
2396 END;
2397 RETRIEVE_GEO_VALUE(l_event_class_mapping_id_tbl(i),
2398 l_trx_id_tbl(i),
2399 l_trx_line_id_tbl(i),
2400 l_trx_level_type_tbl(i),
2401 l_location_type_tbl(1),
2402 l_location_id_tbl(1),
2403 'STATE',
2404 l_state_tbl(i),
2405 l_geo_val_found);
2406
2407 IF ( g_level_statement>= g_current_runtime_level ) THEN
2408 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2409 'Retrieved geo_value for STATE : '||l_state_tbl(i) ||' l_geo_val_found : '||arp_trx_util.boolean_to_varchar2(l_geo_val_found));
2410 END IF;
2411
2412 END IF;
2413 RETRIEVE_GEO_VALUE(l_event_class_mapping_id_tbl(i),
2414 l_trx_id_tbl(i),
2415 l_trx_line_id_tbl(i),
2416 l_trx_level_type_tbl(i),
2417 l_location_type_tbl(1),
2418 l_location_id_tbl(1),
2419 'COUNTY',
2420 l_county_tbl(i),
2421 l_geo_val_found);
2422
2423 IF ( g_level_statement>= g_current_runtime_level ) THEN
2424 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2425 'Retrieved geo_value for COUNTY : '||l_county_tbl(i) ||' l_geo_val_found : '||arp_trx_util.boolean_to_varchar2(l_geo_val_found));
2426 END IF;
2427
2428 RETRIEVE_GEO_VALUE(l_event_class_mapping_id_tbl(i),
2429 l_trx_id_tbl(i),
2430 l_trx_line_id_tbl(i),
2431 l_trx_level_type_tbl(i),
2432 l_location_type_tbl(1),
2433 l_location_id_tbl(1),
2434 'CITY',
2435 l_city_tbl(i),
2436 l_geo_val_found);
2437
2438 IF ( g_level_statement>= g_current_runtime_level ) THEN
2439 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2440 'Retrieved geo_value for CITY : '||l_city_tbl(i) ||' l_geo_val_found : '||arp_trx_util.boolean_to_varchar2(l_geo_val_found));
2441 END IF;
2442
2443
2444 END LOOP;
2445
2446 -- Insert lines into JX EXT Table with Calculated amount --
2447
2448 FORALL i in l_detail_tax_line_id_tbl.first..l_detail_tax_line_id_tbl.last
2449
2450 INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
2451 detail_tax_line_id,
2452 attribute1,
2453 attribute2,
2454 attribute3,
2455 created_by,
2456 creation_date,
2457 last_updated_by,
2458 last_update_date,
2459 last_update_login,
2460 request_id)
2461 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
2462 l_detail_tax_line_id_tbl(i),
2463 l_state_tbl(i),
2464 l_county_tbl(i),
2465 l_city_tbl(i),
2466 fnd_global.user_id,
2467 sysdate,
2468 fnd_global.user_id,
2469 sysdate,
2470 fnd_global.login_id,
2471 p_request_id);
2472
2473 IF ( g_level_statement>= g_current_runtime_level ) THEN
2474 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2475 'After insertion into zx_rep_trx_jx_ext_t ');
2476 END IF;
2477
2478 -- Delete Unwanted lines from Detail ITF
2479
2480 BEGIN
2481 DELETE from zx_rep_trx_detail_t itf
2482 WHERE itf.request_id = p_request_id
2483 AND NOT EXISTS ( SELECT 1
2484 FROM zx_rep_trx_jx_ext_t ext
2485 WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
2486
2487 IF ( g_level_statement>= g_current_runtime_level ) THEN
2488 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2489 'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
2490 END IF;
2491
2492 EXCEPTION
2493 WHEN OTHERS THEN
2494 null;
2495 IF ( g_level_statement>= g_current_runtime_level ) THEN
2496 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2497 'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
2498 END IF;
2499 /*
2500 IF PG_DEBUG = 'Y' THEN
2501 l_err_msg := substrb(SQLERRM,1,120);
2502 arp_standard.debug('ZX_XX_EXTRACT_PKG.populate_us_sales_tax_rep_ext.'||p_report_name || '.'||l_err_msg);
2503 END IF;
2504 */
2505 END;
2506 END IF; -- End of P_REPORT_NAME = ..
2507
2508 --Bug 5251425 : To Populate General Ledger Activity(C_GL_ACTIVITY_DISP)
2509 --and C_BAL_SEGMENT_PROMPT into ZX_REP_TRX_JX_EXT_T.numeric1
2510 --and ZX_REP_TRX_JX_EXT_T.attribute1 respectively ..
2511
2512 IF ( p_report_name = 'ZXXVATRN' ) THEN
2513 -- get details for C_GL_ACTIVITY_DISP
2514 BEGIN
2515 /* select gl.set_of_books_id
2516 into l_set_of_books_id
2517 from gl_sets_of_books gl, ar_system_parameters ar
2518 where gl.set_of_books_id = ar.set_of_books_id;
2519 */
2520
2521 -- Introduced for the bug#7638536 ---
2522 IF ( g_level_statement>= g_current_runtime_level ) THEN
2523 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2524 'P_POSTING_STATUS: '||P_POSTING_STATUS);
2525 END IF;
2526
2527
2528 IF P_POSTING_STATUS = 'POSTED' THEN
2529 DELETE FROM zx_rep_actg_ext_t
2530 WHERE NVL(gl_transfer_flag,'N') <>'Y'
2531 AND request_id = p_request_id;
2532
2533 DELETE FROM zx_rep_trx_detail_t dtl
2534 WHERE NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
2535 WHERE act.detail_tax_line_id = dtl.detail_tax_line_id)
2536 AND dtl.request_id = p_request_id;
2537 END IF;
2538
2539 IF P_POSTING_STATUS = 'UNPOSTED' THEN
2540 DELETE FROM zx_rep_actg_ext_t
2541 WHERE NVL(gl_transfer_flag,'N') = 'Y'
2542 AND request_id = p_request_id;
2543
2544 DELETE FROM zx_rep_trx_detail_t dtl
2545 WHERE NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
2546 WHERE act.detail_tax_line_id = dtl.detail_tax_line_id)
2547 AND dtl.request_id = p_request_id;
2548 END IF;
2549
2550
2551
2552 IF ( g_level_statement>= g_current_runtime_level ) THEN
2553 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2554 'P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW : '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW);
2555 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2556 'P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH : '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH);
2557 END IF;
2558 select gl.period_name
2559 into l_period_from
2560 from gl_period_statuses gl
2561 --, ar_system_parameters ar
2562 where gl.start_date = P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW
2563 and gl.set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
2564 --l_set_of_books_id
2565 and gl.application_id = 222
2566 and rownum = 1;
2567
2568
2569 select gl.period_name
2570 bulk collect into l_end_period_tbl
2571 from gl_period_statuses gl
2572 where gl.end_date >= trunc(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH)
2573 and gl.set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
2574 and gl.application_id = 222
2575 order by gl.end_date ;
2576
2577
2578 /* select gl.period_name
2579 into l_period_to
2580 from gl_period_statuses gl
2581 --, ar_system_parameters ar
2582 where gl.end_date >= P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH
2583 and gl.set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
2584 ---l_set_of_books_id
2585 and gl.application_id = 222
2586 and rownum = 1;
2587 */
2588 IF ( g_level_statement>= g_current_runtime_level ) THEN
2589 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2590 'P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH : '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH);
2591 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2592 'P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW : '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW);
2593 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2594 'l_set_of_books_id : '||l_set_of_books_id);
2595 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2596 'l_period_from : '||l_period_from);
2597 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR', 'l_period_to : '||l_end_period_tbl(1));
2598 END IF;
2599 EXCEPTION WHEN OTHERS THEN
2600 NULL ;
2601 IF ( g_level_statement>= g_current_runtime_level ) THEN
2602 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2603 'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
2604 END IF;
2605 END ;
2606
2607
2608 DECLARE
2609 l_appcol_name varchar2(1000);
2610 l_seg_name varchar2(1000);
2611 l_prompt varchar2(1000) := '';
2612 l_value_set_name varchar2(1000);
2613 L_SEG_NUM NUMBER;
2614 L_CHART_OF_ACCOUNTS_ID gl_sets_of_books.chart_of_accounts_id%type;
2615 L_BALANCING_SEGMENT varchar2(100);
2616 l_bool boolean;
2617 BEGIN
2618 -- get details C_BAL_SEGMENT_PROMPT
2619 select to_char(chart_of_accounts_id)
2620 into l_CHART_OF_ACCOUNTS_ID
2621 from gl_ledgers where ledger_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID;
2622 -- from gl_sets_of_books gl, ar_system_parameters ar
2623 -- where gl.set_of_books_id = ar.set_of_books_id;
2624
2625 IF ( g_level_statement>= g_current_runtime_level ) THEN
2626 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2627 'Before calling fa_rx_flex_pkg.flex_sql to get balancing segment ' );
2628 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2629 'l_CHART_OF_ACCOUNTS_ID : '||l_CHART_OF_ACCOUNTS_ID);
2630 END IF;
2631
2632 l_balancing_segment := fa_rx_flex_pkg.flex_sql
2633 (
2634 p_application_id =>101,
2635 p_id_flex_code => 'GL#',
2636 p_id_flex_num => l_CHART_OF_ACCOUNTS_ID,
2637 p_table_alias => '',
2638 p_mode => 'SELECT',
2639 p_qualifier => 'GL_BALANCING'
2640 );
2641
2642 SELECT to_number(SubStr(l_balancing_segment,8)) INTO L_SEG_NUM FROM dual;
2643
2644 IF ( g_level_statement>= g_current_runtime_level ) THEN
2645 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2646 'l_balancing_segment : '||l_balancing_segment);
2647 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2648 'L_SEG_NUM : '||L_SEG_NUM);
2649 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2650 'Before calling fnd_flex_apis.get_segment_info to get the Balancing Seg prompt' );
2651 END IF;
2652
2653 l_bool := fnd_flex_apis.get_segment_info(
2654 x_application_id =>101,
2655 x_id_flex_code =>'GL#',
2656 x_id_flex_num =>l_CHART_OF_ACCOUNTS_ID,
2657 x_seg_num => L_SEG_NUM,
2658 x_appcol_name => l_appcol_name ,
2659 x_seg_name => l_seg_name ,
2660 x_prompt => l_prompt ,
2661 x_value_set_name => l_value_set_name
2662 );
2663
2664 IF ( g_level_statement>= g_current_runtime_level ) THEN
2665 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2666 'l_prompt : '||l_prompt);
2667 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2668 'l_seg_name : '||l_seg_name);
2669 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2670 'l_value_set_name : '||l_value_set_name);
2671 END IF;
2672
2673 SELECT
2674 det.detail_tax_line_id,
2675 det.trx_id,
2676 act.ACTG_LINE_CCID,
2677 det.ledger_id
2678 BULK COLLECT INTO
2679 l_detail_tax_line_id_tbl,
2680 l_trx_id_tbl,
2681 L_CCID_TBL,
2682 L_LEDGER_ID_TBL
2683 FROM
2684 zx_rep_trx_detail_t det ,
2685 ZX_REP_ACTG_EXT_T act
2686 WHERE det.request_id = p_request_id
2687 AND det.DETAIL_TAX_LINE_ID = ACT.DETAIL_TAX_LINE_ID(+);
2688
2689 IF ( g_level_statement>= g_current_runtime_level ) THEN
2690 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2691 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
2692 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2693 'Before calling ARP_STANDARD.gl_activity to get the gl_activity value.' );
2694 END IF;
2695
2696 FOR i IN 1..nvl(l_detail_tax_line_id_tbl.COUNT,0)
2697 LOOP
2698
2699
2700 IF t_gl_activity_tbl.EXISTS(L_CCID_TBL(i)) THEN
2701 null;
2702 ELSE
2703 t_gl_activity_tbl(L_CCID_TBL(i)) := null;
2704 END IF;
2705
2706 IF t_gl_activity_tbl(L_CCID_TBL(i)) is NULL THEN
2707
2708 ARP_STANDARD.gl_activity(
2709 l_period_from,
2710 l_end_period_tbl(1),
2711 L_CCID_TBL(i),
2712 L_LEDGER_ID_TBL(i),
2713 l_period_net_dr_tbl(i),
2714 l_period_net_cr_tbl(i));
2715
2716 t_gl_activity_tbl(L_CCID_TBL(i)) := l_period_net_dr_tbl(i) - l_period_net_cr_tbl(i);
2717 IF ( g_level_statement>= g_current_runtime_level ) THEN
2718 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2719 'l_period_net_dr_tbl : '||to_char(l_period_net_dr_tbl(i))||'-'||to_char(l_period_net_cr_tbl(i)));
2720 END IF;
2721
2722
2723 l_gl_activity_tbl(i) := t_gl_activity_tbl(L_CCID_TBL(i));
2724 ELSE
2725 l_gl_activity_tbl(i) := 0;
2726 END IF;
2727 l_bal_seg_prompt_tbl(i) := l_prompt ;
2728 END LOOP ;
2729
2730 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
2731 INSERT INTO ZX_REP_TRX_JX_EXT_T
2732 (
2733 detail_tax_line_ext_id,
2734 detail_tax_line_id,
2735 numeric1,
2736 attribute1,
2737 created_by,
2738 creation_date,
2739 last_updated_by,
2740 last_update_date,
2741 last_update_login,
2742 request_id
2743 )
2744 VALUES
2745 (
2746 zx_rep_trx_jx_ext_t_s.nextval,
2747 l_detail_tax_line_id_tbl(i),
2748 l_gl_activity_tbl(i),
2749 l_bal_seg_prompt_tbl(i),
2750 fnd_global.user_id,
2751 sysdate,
2752 fnd_global.user_id,
2753 sysdate,
2754 fnd_global.login_id,
2755 p_request_id
2756 );
2757
2758 IF ( g_level_statement>= g_current_runtime_level ) THEN
2759 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2760 'After insertion into zx_rep_trx_jx_ext_t ');
2761 END IF;
2762
2763 EXCEPTION WHEN OTHERS THEN
2764 -- NULL ;
2765 IF ( g_level_statement>= g_current_runtime_level ) THEN
2766 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2767 'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
2768 END IF;
2769 END;
2770
2771 END IF ; -- ZXXVATRN --
2772
2773
2774 --Bug 5251425 : To Derive Logic for C_AMOUNT_RECEIVED and C_TAX_AMOUNT_RECEIVED
2775 IF ( p_report_name = 'ZXXCDE' ) THEN
2776
2777 SELECT distinct dtl.detail_tax_line_id,
2778 dtl.trx_id ,
2779 dtl.internal_organization_id
2780 BULK COLLECT INTO
2781 l_detail_tax_line_id_tbl,
2782 l_trx_id_tbl,
2783 l_org_id_tbl
2784 FROM zx_rep_trx_detail_t dtl ,
2785 ar_receivable_applications_all cash
2786 WHERE dtl.request_id = p_request_id
2787 AND dtl.trx_id = cash.applied_customer_trx_id
2788 AND cash.status = 'APP'
2789 AND cash.application_type = 'CASH';
2790
2791
2792 IF ( g_level_statement>= g_current_runtime_level ) THEN
2793 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2794 'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
2795
2796 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2797 'Before Call to GET_RECEIVED_AMOUNTS procedure ' );
2798 END IF;
2799
2800
2801 GET_RECEIVED_AMOUNTS
2802 (
2803 l_detail_tax_line_id_tbl,
2804 l_trx_id_tbl ,
2805 l_org_id_tbl ,
2806 l_amount_received_tbl ,
2807 l_tax_received_tbl
2808 );
2809
2810 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
2811 INSERT INTO ZX_REP_TRX_JX_EXT_T
2812 (detail_tax_line_ext_id,
2813 detail_tax_line_id,
2814 numeric1, --C_TAX_AMOUNT_RECEIVED
2815 numeric2,--C_AMOUNT_RECEIVED
2816 created_by,
2817 creation_date,
2818 last_updated_by,
2819 last_update_date,
2820 last_update_login,
2821 request_id)
2822 VALUES (zx_rep_trx_jx_ext_t_s.nextval,
2823 l_detail_tax_line_id_tbl(i),
2824 l_tax_received_tbl(i),
2825 l_amount_received_tbl(i),
2826 fnd_global.user_id,
2827 sysdate,
2828 fnd_global.user_id,
2829 sysdate,
2830 fnd_global.login_id,
2831 p_request_id);
2832
2833 IF ( g_level_statement>= g_current_runtime_level ) THEN
2834 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2835 'After insertion into zx_rep_trx_jx_ext_t ');
2836 END IF;
2837
2838 DELETE FROM zx_rep_trx_detail_t dtl
2839 WHERE dtl.request_id = p_request_id
2840 AND NOT EXISTS ( SELECT 1
2841 FROM zx_rep_trx_jx_ext_t ext
2842 WHERE ext.detail_tax_line_id = dtl.detail_tax_line_id);
2843
2844 IF ( g_level_statement>= g_current_runtime_level ) THEN
2845 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2846 'After deleting data from zx_rep_trx_detail_t ' ||to_char(SQL%ROWCOUNT));
2847 END IF;
2848
2849 END IF ;
2850
2851 EXCEPTION
2852 WHEN OTHERS THEN
2853 -- null;
2854 IF ( g_level_statement>= g_current_runtime_level ) THEN
2855 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
2856 'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
2857 END IF;
2858 /*
2859 IF PG_DEBUG = 'Y' THEN
2860 l_err_msg := substrb(SQLERRM,1,120);
2861 arp_standard.debug('ZX_XX_EXTRACT_PKG.populate_us_sales_tax_rep_ext.'||p_report_name || '.'||l_err_msg);
2862 END IF;
2863 */
2864 IF (g_level_procedure >= g_current_runtime_level ) THEN
2865 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR.BEGIN',
2866 'ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR(-)');
2867 END IF;
2868
2869 END POPULATE_CORE_AR;
2870
2871 END ZX_CORE_REP_EXTRACT_PKG;