eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ZX_CORE_REP_EXTRACT_PKG

Source


1 PACKAGE BODY ZX_CORE_REP_EXTRACT_PKG AS
2 /* $Header: zxricoreplugpvtb.pls 120.12.12010000.3 2008/11/12 12:43:21 spasala 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 --Bug 5251425
31 	TYPE TRX_CURRENCY_DESC_TBL IS TABLE OF
32 	ZX_REP_TRX_JX_EXT_T.ATTRIBUTE9%TYPE INDEX BY BINARY_INTEGER;
33 
34 	TYPE   batch_name_tbl IS TABLE OF
35 	ZX_REP_TRX_JX_EXT_T.ATTRIBUTE8%TYPE INDEX BY BINARY_INTEGER;
36 
37 	TYPE CCID_TBL IS TABLE OF
38 	ZX_REP_ACTG_EXT_T.ACTG_LINE_CCID%TYPE INDEX BY BINARY_INTEGER;
39 
40 	TYPE NUMBER_TBL is table of number index by binary_integer;
41 
42 	TYPE NUMERIC1_TBL IS TABLE OF
43 	ZX_REP_TRX_JX_EXT_T.NUMERIC1%TYPE INDEX BY BINARY_INTEGER;
44 
45 	TYPE NUMERIC2_TBL IS TABLE OF
46 	ZX_REP_TRX_JX_EXT_T.NUMERIC2%TYPE INDEX BY BINARY_INTEGER;
47 
48 	TYPE ACC_CCID_TBL IS TABLE OF ZX_ACCOUNTS.TAX_ACCOUNT_CCID%TYPE;
49 
50 -----------------------------------------
51 --Private Type
52 
53 ----------------------------------------
54 --
55 PROCEDURE get_org_vat_num (
56            --p_report_name            IN  VARCHAR2,
57                             p_detail_tax_line_id_tbl IN  ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
58                     --        p_request_id             IN  NUMBER,
59                             p_establishment_id       IN NUMBER,
60                             p_org_vat_num_tbl        OUT NOCOPY ATTRIBUTE2_TBL);
61 
62 PROCEDURE get_territory_info(
63              --p_report_name                  IN VARCHAR2,
64                               p_detail_tax_line_id_tbl       IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
65                               p_country_code_tbl             IN ZX_EXTRACT_PKG.billing_tp_country_tbl,
66                               p_territory_short_name_tbl     OUT NOCOPY ATTRIBUTE2_TBL,
67                               p_alternate_territory_name_tbl OUT NOCOPY ATTRIBUTE3_TBL);
68 
69 PROCEDURE adjustment_tax_code(
70                     p_detail_tax_line_id_tbl  IN  ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
71                     p_tax_rate_code_tbl       IN  ZX_EXTRACT_PKG.tax_rate_code_tbl,
72                     p_adj_tax_code_tbl        OUT NOCOPY ATTRIBUTE1_TBL);
73 
74 --Private Procedures Included for the Bug 5251425
75 PROCEDURE GET_CREATED_BY
76 (
77 	p_detail_tax_line_id_tbl       IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
78 	p_trx_id_tbl			  IN ZX_EXTRACT_PKG.trx_id_tbl,
79 	p_created_by_tbl		  OUT NOCOPY ATTRIBUTE6_TBL
80 ) ;
81 
82 PROCEDURE GET_OU_DESC
83 (
84 	p_detail_tax_line_id_tbl       IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
85 	p_internal_organization_id_tbl IN ZX_EXTRACT_PKG.internal_organization_id_tbl,
86 	p_ou_desc_tbl		  OUT NOCOPY ATTRIBUTE7_TBL
87 ) ;
88 
89 PROCEDURE GET_MATCH
90 (
91 	p_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
92 	p_acc_ccid_tbl ACC_CCID_TBL ,
93 	p_match_tbl OUT NOCOPY ATTRIBUTE5_TBL
94 );
95 
96 PROCEDURE GET_RECEIVED_AMOUNTS
97 (
98 	p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
99 	p_trx_id_tbl	         IN ZX_EXTRACT_PKG.trx_id_tbl,
100 	p_org_id_tbl		 IN zx_extract_pkg.INTERNAL_ORGANIZATION_ID_TBL ,
101 	p_amount_received_tbl	 OUT NOCOPY NUMERIC2_TBL,
102 	p_tax_received_tbl    OUT NOCOPY NUMERIC1_TBL
103 );
104 
105 -- Declare global varibles for FND log messages
106 
107    g_current_runtime_level           NUMBER;
108    g_level_statement       CONSTANT  NUMBER  := FND_LOG.LEVEL_STATEMENT;
109    g_level_procedure       CONSTANT  NUMBER  := FND_LOG.LEVEL_PROCEDURE;
110    g_level_event           CONSTANT  NUMBER  := FND_LOG.LEVEL_EVENT;
111    g_level_unexpected      CONSTANT  NUMBER  := FND_LOG.LEVEL_UNEXPECTED;
112    g_error_buffer                    VARCHAR2(100);
113 
114 -- Public APIs
115 
116 PROCEDURE populate_core_ap(
117           P_TRL_GLOBAL_VARIABLES_REC  IN  ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
118           )
119 IS
120 
121 p_request_id                    NUMBER;
122 p_report_name                   VARCHAR2(30);
123 p_legal_entity_id               NUMBER;
124 p_product                       VARCHAR2(30);
125 l_detail_tax_line_id_tbl        ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
126 l_trx_id_tbl                    ZX_EXTRACT_PKG.TRX_ID_TBL;
127 l_country_code_tbl              ZX_EXTRACT_PKG.BILLING_TP_COUNTRY_TBL;
128 l_org_vat_num_tbl               ATTRIBUTE2_TBL;
129 l_territory_short_name_tbl      ATTRIBUTE2_TBL;
130 l_alternate_territory_name_tbl  ATTRIBUTE3_TBL;
131 l_establishment_id              NUMBER;
132 l_tax_rate_code_tbl             ZX_EXTRACT_PKG.TAX_RATE_CODE_TBL;
133 l_adj_tax_code_tbl              ATTRIBUTE1_TBL;
134 
135 --Bug 5251425
136 l_trx_currency_code_tbl	zx_extract_pkg.trx_currency_code_tbl;
137 l_trx_currency_desc_tbl trx_currency_desc_tbl;
138 l_batch_name_tbl	batch_name_tbl;
139 l_err_msg               varchar2(120);
140 l_created_by_tbl attribute6_tbl;
141 l_ou_desc_tbl attribute7_tbl;
142 l_internal_organization_id_tbl zx_extract_pkg.internal_organization_id_tbl;
143 l_acc_ccid_tbl acc_ccid_tbl;
144 l_match_tbl attribute5_tbl;
145 
146 
147 
148    CURSOR establishment_id_csr(c_legal_entity_id number) IS
149    SELECT xle_etb.establishment_id
150      FROM zx_party_tax_profile ptp,
151           xle_etb_profiles xle_etb
152     WHERE ptp.party_id         = xle_etb.party_id
153       AND ptp.party_type_code  = 'LEGAL_ESTABLISHMENT'
154       AND xle_etb.legal_entity_id =  c_legal_entity_id
155       AND xle_etb.main_establishment_flag = 'Y';
156 
157 BEGIN
158 
159 	g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
160 --        g_level_statement  := FND_LOG.LEVEL_STATEMENT;
161 --        g_level_procedure  := FND_LOG.LEVEL_PROCEDURE;
162 
163 	IF (g_level_procedure >= g_current_runtime_level ) THEN
164 	FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP.BEGIN',
165 				      'ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP(+)');
166 	END IF;
167 
168     p_request_id         :=  P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
169     p_report_name        :=  P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME;
170     p_legal_entity_id    :=  P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID;
171     p_product            :=  P_TRL_GLOBAL_VARIABLES_REC.PRODUCT;
172 
173 	IF (g_level_statement >= g_current_runtime_level ) THEN
174 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
175 					      'p_report_name : '||P_REPORT_NAME);
176 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
177 					      'p_request_id : '||p_request_id);
178 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
179 					      'p_legal_entity_id : '||p_legal_entity_id);
180 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
181 					      'p_product : '||p_product);
182 	END IF;
183 
184 
185     IF P_REPORT_NAME = 'ZXXTATAT' THEN
186        BEGIN
187           INSERT INTO zx_rep_trx_jx_ext_t
188                            (detail_tax_line_ext_id,
189                             detail_tax_line_id,
190                             attribute1,
191                             created_by,
192                             creation_date,
193                             last_updated_by,
194                             last_update_date,
195                             last_update_login,
196                             request_id)
197                     (SELECT zx_rep_trx_jx_ext_t_s.nextval,
198                             dtl.detail_tax_line_id,
199                             'Yes', --fl.meaning,
200                             dtl.created_by,
201                             dtl.creation_date,
202                             dtl.last_updated_by,
203                             dtl.last_update_date,
204                             dtl.last_update_login,
205                             p_request_id
206                        FROM zx_rep_trx_detail_t dtl
207                          WHERE EXISTS (select distinct ah.invoice_id
208                             FROM ap_holds_all ah
209                             WHERE ah.invoice_id = dtl.trx_id
210                               AND ah.release_lookup_code IS NULL )
211                          AND dtl.request_id = p_request_id);
212 
213 	IF (g_level_statement >= g_current_runtime_level ) THEN
214 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
215 					      'Insertion for Hold , ext.attribute1 : '||to_char(SQL%ROWCOUNT) );
216 	END IF;
217 
218 --Bug 5251425 : To get the C_TRX_CURRENCY_DESC ( ext.attribute9) and the C_BATCH_NAME ( ext.attribute8 ) for the Invoice .
219 		SELECT
220 			dtl.detail_tax_line_id,
221 			dtl.trx_id,
222 			dtl.trx_currency_code,
223 			fcv.name,
224 			ab.batch_name,
225 			acc.TAX_ACCOUNT_CCID
226 		BULK COLLECT INTO
227 			l_detail_tax_line_id_tbl,
228 			l_trx_id_tbl,
229 			l_trx_currency_code_tbl,
230 			l_trx_currency_desc_tbl,
231 			l_batch_name_tbl,
232 			l_acc_ccid_tbl
233 		FROM
234 			zx_rep_trx_detail_t dtl,
235 			fnd_currencies_vl fcv,
236 			ap_invoices_all ai,
237 			ap_batches_all ab,
238 			zx_rates_b rates,
239 			zx_accounts acc
240 		WHERE
241 			dtl.request_id = p_request_id
242 			AND dtl.trx_currency_code = fcv.currency_code
243 			AND dtl.trx_id = ai.invoice_id
244 			AND ai.batch_id = ab.batch_id(+)
245 			AND dtl.tax_rate_id = rates.tax_rate_id(+)
246 			AND acc.TAX_ACCOUNT_ENTITY_ID(+) = rates.tax_rate_id;
247 
248 	IF (g_level_statement >= g_current_runtime_level ) THEN
249 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
250 					      'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
251 	END IF;
252 	--Get details for C_MATCH
253 
254 		GET_MATCH(
255 			l_detail_tax_line_id_tbl,
256 			l_acc_ccid_tbl,
257 			l_match_tbl
258 		);
259 
260 	IF (g_level_statement >= g_current_runtime_level ) THEN
261 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
262 					      'Before insertion into zx_rep_trx_jx_ext_t for report '||p_report_name  );
263 	END IF;
264 
265 	FORALL i IN nvl(l_detail_tax_line_id_tbl.FIRST,1)..nvl(l_detail_tax_line_id_tbl.LAST,0)
266 		MERGE INTO zx_rep_trx_jx_ext_t ext
267 		      USING ( SELECT 1 FROM dual ) T
268 		      ON ( ext.detail_tax_line_id = l_detail_tax_line_id_tbl(i))
269 		WHEN MATCHED THEN UPDATE SET ext.ATTRIBUTE9 = l_trx_currency_desc_tbl(i),
270 					     ext.attribute8 = l_batch_name_tbl(i),
271 					     ext.attribute5 = l_match_tbl(i)
272 		WHEN NOT MATCHED THEN
273 			INSERT (
274 				detail_tax_line_ext_id,
275 				detail_tax_line_id,
276 				attribute9,
277 				attribute8,
278 				attribute5,
279 				created_by,
280 				creation_date,
281 				last_updated_by,
282 				last_update_date,
283 				last_update_login,
284                                 request_id
285 			)
286 			VALUES ( ZX_MIGRATE_UTIL.get_next_seqid('ZX_REP_TRX_JX_EXT_T_S'),
287 				l_detail_tax_line_id_tbl(i),
288 				l_trx_currency_desc_tbl(i),
289 				l_batch_name_tbl(i),
290 				l_match_tbl(i),
291 				fnd_global.user_id,
292 				sysdate,
293 				fnd_global.user_id,
294 				sysdate,
295 				fnd_global.login_id,
296                                 p_request_id
297 			);
298 	EXCEPTION
299 	WHEN OTHERS THEN
300 --		NULL ;
301 		IF (g_level_statement >= g_current_runtime_level ) THEN
302 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
303 						      'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
304 		END IF;
305         END;
306     END IF;
307 
308     IF P_REPORT_NAME = 'ZXXTAVAR' THEN  --Bug 5251425
309        BEGIN
310           OPEN establishment_id_csr (p_legal_entity_id);
311          FETCH establishment_id_csr INTO l_establishment_id;
312 
313 	IF (g_level_statement >= g_current_runtime_level ) THEN
314 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
315 					      'l_establishment_id : '||l_establishment_id);
316 	END IF;
317 
318          -- ------------------------------------------------ --
319          -- Get filtered tax lines                           --
320          -- in this case, you need to group the lines by trx --
321          -- ------------------------------------------------ --
322 
323               SELECT dtl.detail_tax_line_id,
324                      dtl.trx_id,
325                      dtl.billing_tp_country,
326 		     dtl.internal_organization_id --Bug 5251425
327     BULK COLLECT INTO l_detail_tax_line_id_tbl,
328                       l_trx_id_tbl,
329                       l_country_code_tbl,
330 		      l_internal_organization_id_tbl --Bug 5251425
331                 FROM zx_reporting_types_b rep_type,
332                      zx_reporting_codes_b rep_code,
333                      zx_report_codes_assoc rep_ass,
334                      zx_party_tax_profile ptp,
335                      xle_etb_profiles  xle_pf ,
336                      zx_rep_trx_detail_t dtl
337               WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
338                 AND rep_type.reporting_type_code = 'MEMBER STATE'
339                 AND rep_code.reporting_code_id = rep_ass.reporting_code_id
340                 AND rep_ass.entity_code = 'ZX_PARTY_TAX_PROFILE'
341                 AND rep_ass.entity_id = ptp.party_tax_profile_id
342                 AND ptp.party_id = xle_pf.party_id
343                 AND ptp.Party_Type_Code = 'LEGAL_ESTABLISHMENT'
344                 AND xle_pf.establishment_id = l_establishment_id
345                 AND xle_pf.establishment_id = dtl.establishment_id
346                 AND  rep_code.reporting_code_char_value <> dtl.billing_tp_country
347                 AND dtl.request_id = p_request_id;
348 
349 	IF (g_level_statement >= g_current_runtime_level ) THEN
350 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
351 					      'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
352 	END IF;
353 
354 -- Note : Need to add xle_associations_v view to the above query for establishment_id join
355 
356          -- Get territory Info  --
357 IF ( nvl(l_detail_tax_line_id_tbl.count,0) > 0 ) THEN
358 
359 	GET_TERRITORY_INFO( l_detail_tax_line_id_tbl,
360 		l_country_code_tbl,
361 		l_territory_short_name_tbl,
362 		l_alternate_territory_name_tbl);
363 
364 	IF (g_level_statement >= g_current_runtime_level ) THEN
365 	FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
366 				      'After getting the territory info.' );
367 	END IF;
368 
369 	GET_ORG_VAT_NUM ( l_detail_tax_line_id_tbl,
370 	       l_establishment_id,
371 	       l_org_vat_num_tbl);
372 
373 	IF (g_level_statement >= g_current_runtime_level ) THEN
374 	FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
375 				      'After getting the org_vat_num' );
376 	END IF;
377 
378 
379 --Bug 5251425 : To derive the C_CREATED_BY(zx_rep_trx_detail_t.attribute6)
380 --		and C_ATTRIBUTE3(Operating Unit Desc)  into (zx_rep_trx_detail_t.attribute7)
381 
382 	GET_CREATED_BY(
383 		l_detail_tax_line_id_tbl,
384 		l_trx_id_tbl,
385 		l_created_by_tbl
386 	);
387 
388 	IF (g_level_statement >= g_current_runtime_level ) THEN
389 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
390 					      'After getting the created_by for invoice' );
391 	END IF;
392 
393 
394 	GET_OU_DESC(
395 		l_detail_tax_line_id_tbl,
396 		l_internal_organization_id_tbl,
397 		l_ou_desc_tbl
398 	);
399 
400 	IF (g_level_statement >= g_current_runtime_level ) THEN
401 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
402 					      'After getting the OU Description.' );
403 	END IF;
404 
405 
406          FORALL i in nvl(l_detail_tax_line_id_tbl.first,1)..nvl(l_detail_tax_line_id_tbl.last,0)
407 
408                 INSERT INTO zx_rep_trx_jx_ext_t
409                                   (detail_tax_line_ext_id,
410                                    detail_tax_line_id,
411                                    attribute1,
412                                    attribute2,
413                                    attribute3,
414 				   attribute6,--Bug 5251425
415 				   attribute7,--Bug 5251425
416                                    created_by,
417                                    creation_date,
418                                    last_updated_by,
419                                    last_update_date,
420                                    last_update_login)
421                            VALUES (zx_rep_trx_jx_ext_t_s.nextval,
422                                    l_detail_tax_line_id_tbl(i),
423                                    l_org_vat_num_tbl(i),
424                                    l_territory_short_name_tbl(i),
425                                    l_alternate_territory_name_tbl(i),
426 				   l_created_by_tbl(i),--Bug 5251425
427 				   l_ou_desc_tbl(i),--Bug 5251425
428                                    fnd_global.user_id,
429                                    sysdate,
430                                    fnd_global.user_id,
431                                    sysdate,
432                                    fnd_global.login_id);
433 
434 	IF (g_level_statement >= g_current_runtime_level ) THEN
435 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
436 					      'After insertion into zx_rep_trx_jx_ext_t for report '||p_report_name );
437 	END IF;
438 
439 		-- Delete Unwanted lines from Detail ITF
440 
441                 DELETE FROM zx_rep_trx_detail_t itf
442                  WHERE itf.request_id = p_request_id
443                    AND NOT EXISTS ( SELECT 1
444                                       FROM zx_rep_trx_jx_ext_t ext
445                                      WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
446 
447 	IF (g_level_statement >= g_current_runtime_level ) THEN
448 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
449 					      'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
450 	END IF;
451 END IF ;
452 
453     EXCEPTION
454        WHEN OTHERS THEN
455        NULL;
456 		IF (g_level_statement >= g_current_runtime_level ) THEN
457 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
458 						      'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
459 		END IF;
460 
461        END;
462    END IF;
463 
464 -- This plug-in call extracts adjustment tax code for Italy, Poland, Portugal countries requirement
465 
466       IF p_product = 'AR' or p_product = 'ALL' THEN
467 
468 	 BEGIN
469 
470                SELECT dtl.detail_tax_line_id,
471                       dtl.tax_rate_code
472     BULK COLLECT INTO l_detail_tax_line_id_tbl,
473                       l_tax_rate_code_tbl
474                  FROM zx_rep_trx_detail_t dtl
475                 WHERE dtl.request_id = p_request_id;
476 
477 	IF (g_level_statement >= g_current_runtime_level ) THEN
478 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
479 					      'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
480 	END IF;
481 
482         adjustment_tax_code(
483                   l_detail_tax_line_id_tbl,
484                   l_tax_rate_code_tbl,
485                   l_adj_tax_code_tbl);
486 
487 	IF (g_level_statement >= g_current_runtime_level ) THEN
488 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
489 					      'After getting adjustment tax code for Italy, Poland, Portugal countries');
490 	END IF;
491 
492          FORALL i in l_detail_tax_line_id_tbl.first..l_detail_tax_line_id_tbl.last
493                 INSERT INTO zx_rep_trx_jx_ext_t
494                                   (detail_tax_line_ext_id,
495                                    detail_tax_line_id,
496                                    attribute1,
497                                    created_by,
498                                    creation_date,
499                                    last_updated_by,
500                                    last_update_date,
501                                    last_update_login)
502                            VALUES (zx_rep_trx_jx_ext_t_s.nextval,
503                                    l_detail_tax_line_id_tbl(i),
504                                    l_adj_tax_code_tbl(i),
505                                    fnd_global.user_id,
506                                    sysdate,
507                                    fnd_global.user_id,
508                                    sysdate,
509                                    fnd_global.login_id);
510 
511 	IF (g_level_statement >= g_current_runtime_level ) THEN
512 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
513 					      'After insertion into zx_rep_trx_jx_ext_t ');
514 	END IF;
515 
516     EXCEPTION
517        WHEN OTHERS THEN
518        NULL;
519 		IF (g_level_statement >= g_current_runtime_level ) THEN
520 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
521 						      'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
522 		END IF;
523      END;
524   END IF;
525 
526 	g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
527 	IF (g_level_procedure >= g_current_runtime_level ) THEN
528 	FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP.BEGIN',
529 				      'ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP(-)');
530 	END IF;
531 	null;
532 
533 END populate_core_ap;
534 
535 
536 PROCEDURE get_territory_info
537           (
538 --p_report_name                  IN VARCHAR2,
539            p_detail_tax_line_id_tbl       IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
540            p_country_code_tbl             IN ZX_EXTRACT_PKG.billing_tp_country_tbl,
541            p_territory_short_name_tbl     OUT NOCOPY ATTRIBUTE2_TBL,
542            p_alternate_territory_name_tbl OUT NOCOPY ATTRIBUTE3_TBL
543           )
544 IS
545 
546 BEGIN
547 
548    FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
549        BEGIN
550           SELECT ft.territory_short_name,
551                  ft.alternate_territory_code
552             INTO p_territory_short_name_tbl(i),
553                  p_alternate_territory_name_tbl(i)
554             FROM fnd_territories_vl ft
555            WHERE ft.territory_code = p_country_code_tbl(i);
556 	EXCEPTION
557 	WHEN OTHERS THEN
558 		p_territory_short_name_tbl(i) := NULL ;
559 		p_alternate_territory_name_tbl(i) := NULL ;
560 		IF ( g_level_statement>= g_current_runtime_level ) THEN
561 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
562 			'Error Message : '||substrb(SQLERRM,1,120) );
563 		END IF;
564        END;
565    END LOOP;
566 
567 END get_territory_info;
568 
569 PROCEDURE get_org_vat_num (
570                     --p_report_name            IN  VARCHAR2,
571                              p_detail_tax_line_id_tbl IN  ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
572                        --      p_request_id             IN  NUMBER,
573                              p_establishment_id        IN NUMBER,
574                              p_org_vat_num_tbl        OUT NOCOPY ATTRIBUTE2_TBL)
575 IS
576 BEGIN
577 
578    FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
579       BEGIN
580         SELECT rep_code.reporting_code_char_value
581          INTO p_org_vat_num_tbl(i)
582          FROM zx_reporting_types_b rep_type,
583               zx_reporting_codes_b rep_code,
584               zx_report_codes_assoc rep_ass,
585               zx_party_tax_profile ptp,
586               xle_etb_profiles  xle_pf
587           --    zx_rep_trx_detail_t dtl
588         WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
589           AND rep_type.reporting_type_code = 'FSO_REG_NUM'
590           AND rep_code.reporting_code_id = rep_ass.reporting_code_id
591           AND rep_ass.entity_code = 'ZX_PARTY_TAX_PROFILE'
592           AND rep_ass.entity_id = ptp.party_tax_profile_id
593           AND ptp.party_id = xle_pf.party_id
594           AND ptp.Party_Type_Code = 'LEGAL_ESTABLISHMENT'
595           AND xle_pf.establishment_id = p_establishment_id;
596 --        AND  rep_code.reporting_code_char_value <> dtl.billing_tp_country;
597 	EXCEPTION
598 	WHEN OTHERS THEN
599 		p_org_vat_num_tbl(i) := NULL ;
600 		IF ( g_level_statement>= g_current_runtime_level ) THEN
601 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
602 			'Error Message : '||substrb(SQLERRM,1,120) );
603 		END IF;
604 	END ;
605       END LOOP;
606 END get_org_vat_num;
607 
608 
609 PROCEDURE adjustment_tax_code(
610                     p_detail_tax_line_id_tbl IN  ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
611                     p_tax_rate_code_tbl      IN  ZX_EXTRACT_PKG.tax_rate_code_tbl,
612                     p_adj_tax_code_tbl       OUT NOCOPY ATTRIBUTE1_TBL)
613 IS
614    BEGIN
615    FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
616        BEGIN
617        SELECT rep_code.reporting_code_char_value
618          INTO p_adj_tax_code_tbl(i)
619          FROM zx_reporting_types_b rep_type,
620               zx_reporting_codes_b rep_code,
621               zx_report_codes_assoc rep_ass,
622               zx_rates_b zx_rate
623         WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
624           AND rep_type.reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE'
625           AND rep_code.reporting_code_id = rep_ass.reporting_code_id
626           AND rep_ass.entity_code = 'ZX_RATES'
627           AND rep_ass.entity_id = zx_rate.tax_rate_id
628           AND zx_rate.tax_rate_code = p_tax_rate_code_tbl(i);
629 	EXCEPTION
630 	WHEN OTHERS THEN
631 		p_adj_tax_code_tbl(i) := NULL ;
632 		IF ( g_level_statement>= g_current_runtime_level ) THEN
633 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP',
634 			'Error Message : '||substrb(SQLERRM,1,120) );
635 		END IF;
636 	END ;
637     END LOOP;
638   END;
639 
640 PROCEDURE RETRIEVE_GEO_VALUE( p_event_class_mapping_id IN  ZX_LINES_DET_FACTORS.event_class_mapping_id%type,
641                               p_trx_id                 IN  ZX_LINES_DET_FACTORS.trx_id%type,
642                               p_trx_line_id            IN  ZX_LINES_DET_FACTORS.trx_line_id%type,
643                               p_trx_level_type         IN  ZX_LINES_DET_FACTORS.trx_level_type%type,
644                               p_location_type          IN  VARCHAR2,
645                               p_location_id            IN  ZX_LINES_DET_FACTORS.ship_to_location_id%type,
646                               p_geography_type         IN  VARCHAR2,
647                               x_geography_value        OUT NOCOPY VARCHAR2,
648                               x_geo_val_found          OUT NOCOPY BOOLEAN) IS
649 
650    hash_string                     varchar2(1000);
651    TABLE_SIZE              BINARY_INTEGER := 65636;
652    TABLEIDX                        binary_integer;
653    loc_info_idx                    binary_integer;
654    HASH_VALUE binary_integer;
655 
656    BEGIN
657 
658       hash_string := to_char(p_event_class_mapping_id)||'|'||
659                      to_char(p_trx_id)||'|'||
660                      to_char(p_trx_line_id)||'|'||
661                      p_trx_level_type||'|'||
662                      p_location_type||'|'||
663                      to_char(p_location_id)||'|'||
664                      p_geography_type;
665 
666        TABLEIDX := dbms_utility.get_hash_value(hash_string,1,TABLE_SIZE);
667 
668        IF (ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl.EXISTS(TABLEIDX)) THEN
669          loc_info_idx := ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl(TABLEIDX);
670          x_geography_value := ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.geography_value(loc_info_idx);
671          x_geo_val_found := TRUE;
672        ELSE
673          x_geo_val_found := FALSE;
674        END IF;
675 
676 end RETRIEVE_GEO_VALUE;
677 
678 --Bug 5251425
679 PROCEDURE GET_CREATED_BY
680           (
681            p_detail_tax_line_id_tbl       IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
682            p_trx_id_tbl			  IN ZX_EXTRACT_PKG.trx_id_tbl,
683            p_created_by_tbl		  OUT NOCOPY ATTRIBUTE6_TBL
684           )
685 IS
686 
687 BEGIN
688 
689    FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
690        BEGIN
691           SELECT fu.user_name
692 	     INTO p_created_by_tbl(i)
693             FROM ap_invoices_all ai,
694 		 fnd_user fu
695            WHERE ai.invoice_id = p_trx_id_tbl(i)
696 	   AND fu.user_id = ai.created_by ;
697 	EXCEPTION
698 		WHEN OTHERS THEN
699 		p_created_by_tbl(i) := NULL ;
700 		IF ( g_level_statement>= g_current_runtime_level ) THEN
701 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
702 			'Error Message : '||substrb(SQLERRM,1,120) );
703 		END IF;
704        END;
705    END LOOP;
706 
707 END GET_CREATED_BY;
708 
709 --Bug 5251425
710 PROCEDURE GET_OU_DESC
711           (
712            p_detail_tax_line_id_tbl       IN ZX_EXTRACT_PKG.detail_tax_line_id_tbl,
713            p_internal_organization_id_tbl IN ZX_EXTRACT_PKG.internal_organization_id_tbl,
714            p_ou_desc_tbl		  OUT NOCOPY ATTRIBUTE7_TBL
715           )
716 IS
717 
718 BEGIN
719 
720    FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
721        BEGIN
722 		SELECT hou.NAME
723 		INTO p_ou_desc_tbl(i)
724 		FROM hr_operating_units hou
725 		WHERE hou.organization_id = p_internal_organization_id_tbl(i);
726 	EXCEPTION
727 	WHEN OTHERS THEN
728 		p_ou_desc_tbl(i) := NULL ;
729 		IF ( g_level_statement>= g_current_runtime_level ) THEN
730 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
731 			'Error Message : '||substrb(SQLERRM,1,120) );
732 		END IF;
733        END;
734    END LOOP;
735 
736 END GET_OU_DESC;
737 --Bug 5251425
738 PROCEDURE GET_MATCH(
739 			p_detail_tax_line_id_tbl ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
740 			p_acc_ccid_tbl ACC_CCID_TBL ,
741 			p_match_tbl OUT NOCOPY ATTRIBUTE5_TBL
742 		    )
743 IS
744 	l_nls_no	VARCHAR(10);
745 BEGIN
746 	SELECT
747 		ln.meaning
748 		INTO     l_nls_no
749 	FROM
750 		fnd_lookups ln,  ap_lookup_codes la
751 	WHERE
752 		ln.lookup_type = 'YES_NO'
753 		AND   ln.lookup_code = 'N'
754 		AND   la.lookup_type = 'NLS REPORT PARAMETER'
755 		AND   la.lookup_code = 'ALL';
756 
757 	FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
758 		BEGIN
759 			SELECT Decode(p_ACC_CCID_TBL(i), act.ACTG_LINE_CCID,NULL,l_nls_no )
760 			INTO p_match_tbl(i)
761 			FROM   ZX_REP_ACTG_EXT_T act
762 			WHERE act.detail_tax_line_id = p_detail_tax_line_id_tbl(i);
763 		EXCEPTION
764 		WHEN OTHERS THEN
765 			p_match_tbl(i) := null;
766 			IF ( g_level_statement>= g_current_runtime_level ) THEN
767 				FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
768 				'Error Message : '||substrb(SQLERRM,1,120) );
769 			END IF;
770 		END;
771 	END LOOP;
772 END GET_MATCH;
773 
774 --To get the Received amounts for the report ZXXCDE
775 PROCEDURE GET_RECEIVED_AMOUNTS
776 (
777 	p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
778 	p_trx_id_tbl	         IN ZX_EXTRACT_PKG.trx_id_tbl,
779 	p_org_id_tbl		 IN zx_extract_pkg.INTERNAL_ORGANIZATION_ID_TBL ,
780 	p_amount_received_tbl	 OUT NOCOPY NUMERIC2_TBL,
781 	p_tax_received_tbl    OUT NOCOPY NUMERIC1_TBL
782 )
783 IS
784 
785 l_amount_recvd_tbl NUMERIC2_TBL;
786 l_tax_amount_rcvd_tbl NUMERIC1_TBL;
787 
788 BEGIN
789 
790  FOR i in 1..nvl(p_detail_tax_line_id_tbl.last,0) LOOP
791 
792          IF l_amount_recvd_tbl.EXISTS(p_trx_id_tbl(i)) THEN
793             null;
794          ELSE
795              l_amount_recvd_tbl(p_trx_id_tbl(i)) := null;
796          END IF;
797 
798          IF l_tax_amount_rcvd_tbl.EXISTS(p_trx_id_tbl(i)) THEN
799             null;
800          ELSE
801              l_tax_amount_rcvd_tbl(p_trx_id_tbl(i)) := null;
802          END IF;
803 
804 --get amount received for that trx without any proration done , populate the amount only once per trx_id
805 -- in the extension table
806 	 IF l_amount_recvd_tbl(p_trx_id_tbl(i)) is NULL THEN
807 
808 		BEGIN
809 			SELECT SUM(nvl(amount_applied,0)) ,sum(nvl(tax_applied,0))
810 			INTO l_amount_recvd_tbl(p_trx_id_tbl(i)),l_tax_amount_rcvd_tbl(p_trx_id_tbl(i))
811 			FROM AR_RECEIVABLE_APPLICATIONS_ALL
812 			WHERE applied_customer_trx_id = p_trx_id_tbl(i)
813 			AND org_id = p_org_id_tbl(i);
814 		EXCEPTION
815 		WHEN OTHERS THEN
816 			l_amount_recvd_tbl(p_trx_id_tbl(i)) := 0;
817 			l_tax_amount_rcvd_tbl(p_trx_id_tbl(i)) := 0;
818 
819 			IF ( g_level_statement>= g_current_runtime_level ) THEN
820 				FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
821 				'Error Message : '||substrb(SQLERRM,1,120) );
822 			END IF;
823 		END ;
824 		p_amount_received_tbl(i) := l_amount_recvd_tbl(p_trx_id_tbl(i));
825 		p_tax_received_tbl(i) := l_tax_amount_rcvd_tbl(p_trx_id_tbl(i)) ;
826 	  ELSE
827 		p_amount_received_tbl(i) := 0 ;
828 		p_tax_received_tbl(i) := 0 ;
829 	 END IF ;
830 END LOOP ;
831 
832 EXCEPTION
833 WHEN OTHERS THEN
834 NULL ;
835 END ;
836 
837 
838 /*======================================================================================+
839  | PROCEDURE                                                                            |
840  |   POPULATE_TAX_JURIS_FOR_USSTR                                                       |
841  |   Type       : Private                                                               |
842  |   Pre-req    : None                                                                  |
843  |   Function   :                                                                       |
844  |    This procedure extracts State, County, and City of taxing jurisdiction            |
845  |    for each tax line into table                                                      |
846  |                                                                                      |
847  |    Called from XX_XX_EXTRACT_PKG.XXXXXXXX                                            |
848  |                                                                                      |
849  |   Parameters :                                                                       |
850  |   IN         :  P_TRL_GLOBAL_VARIABLES_REC    IN   VARCHAR2                          |
851  |                                                                                      |
852  |   MODIFICATION HISTORY                                                               |
853  |     20-Jun-05  Santosh Vaze      created                                             |
854  |                                                                                      |
855  +======================================================================================*/
856 
857 
858 PROCEDURE POPULATE_CORE_AR(
859           P_TRL_GLOBAL_VARIABLES_REC     IN      ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
860           )
861 IS
862 
863 P_REPORT_NAME                   VARCHAR2(30);
864 P_REQUEST_ID                    NUMBER;
865 l_place_of_supply               ZX_LINES.place_of_supply_type_code%type;
866 l_location_type_tbl             ZX_TCM_GEO_JUR_PKG.location_type_tbl_type;
867 l_location_id_tbl               ZX_TCM_GEO_JUR_PKG.location_id_tbl_type;
868 l_geo_val_found                 BOOLEAN;
869 x_return_status  VARCHAR2(1);
870 prev_event_class_mapping_id       NUMBER;
871 
872 TYPE DETAIL_TAX_LINE_ID_TBL IS TABLE OF
873   ZX_REP_TRX_DETAIL_T.DETAIL_TAX_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
874 TYPE EVENT_CLASS_MAPPING_ID_TBL IS TABLE OF
875   ZX_REP_TRX_DETAIL_T.EVENT_CLASS_MAPPING_ID%TYPE INDEX BY BINARY_INTEGER;
876 TYPE TRX_ID_TBL IS TABLE OF
877   ZX_REP_TRX_DETAIL_T.TRX_ID%TYPE INDEX BY BINARY_INTEGER;
878 TYPE TRX_LINE_ID_TBL IS TABLE OF
879   ZX_REP_TRX_DETAIL_T.TRX_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
880 TYPE TRX_LEVEL_TYPE_TBL IS TABLE OF
881   ZX_REP_TRX_DETAIL_T.TRX_LEVEL_TYPE%TYPE INDEX BY BINARY_INTEGER;
882 TYPE POS_TYPE_CODE_TBL IS TABLE OF
883   ZX_REP_TRX_DETAIL_T.PLACE_OF_SUPPLY_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
884 TYPE SHIP_TO_LOCATION_ID_TBL IS TABLE OF
885   ZX_REP_TRX_DETAIL_T.SHIP_TO_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
886 TYPE SHIP_FROM_LOCATION_ID_TBL IS TABLE OF
887   ZX_REP_TRX_DETAIL_T.SHIP_FROM_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
888 TYPE BILL_TO_LOCATION_ID_TBL IS TABLE OF
889   ZX_REP_TRX_DETAIL_T.BILL_TO_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
890 TYPE BILL_FROM_LOCATION_ID_TBL IS TABLE OF
891   ZX_REP_TRX_DETAIL_T.BILL_FROM_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
892 TYPE POA_LOCATION_ID_TBL IS TABLE OF
893   ZX_REP_TRX_DETAIL_T.POA_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
894 TYPE POO_LOCATION_ID_TBL IS TABLE OF
895   ZX_REP_TRX_DETAIL_T.POO_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
896 TYPE DEF_POS_TYPE_CODE_TBL IS TABLE OF
897   ZX_REP_TRX_DETAIL_T.DEF_PLACE_OF_SUPPLY_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
898 TYPE GEO_VAL_TBL IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
899 
900 l_detail_tax_line_id_tbl        ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
901 l_event_class_mapping_id_tbl    EVENT_CLASS_MAPPING_ID_TBL;
902 l_trx_id_tbl                    ZX_EXTRACT_PKG.TRX_ID_TBL;
903 l_trx_line_id_tbl               TRX_LINE_ID_TBL;
904 l_trx_level_type_tbl            TRX_LEVEL_TYPE_TBL;
905 l_pos_type_code_tbl             POS_TYPE_CODE_TBL;
906 l_ship_to_location_id_tbl       SHIP_TO_LOCATION_ID_TBL;
907 l_ship_from_location_id_tbl     SHIP_FROM_LOCATION_ID_TBL;
908 l_bill_to_location_id_tbl       BILL_TO_LOCATION_ID_TBL;
909 l_bill_from_location_id_tbl     BILL_FROM_LOCATION_ID_TBL;
910 l_poa_location_id_tbl           POA_LOCATION_ID_TBL;
911 l_poo_location_id_tbl           POO_LOCATION_ID_TBL;
912 l_def_pos_type_code_tbl         DEF_POS_TYPE_CODE_TBL;
913 l_state_tbl                     GEO_VAL_TBL;
914 l_county_tbl                    GEO_VAL_TBL;
915 l_city_tbl                      GEO_VAL_TBL;
916 
917 --Bug 5251425 : Variable Declarations for the Bug.
918 l_ledger_id_tbl zx_extract_pkg.ledger_id_tbl;
919 l_period_net_dr_tbl number_tbl;
920 l_period_net_cr_tbl number_tbl;
921 l_gl_activity_tbl number_tbl;
922 t_gl_activity_tbl number_tbl;
923 l_ccid_tbl ccid_tbl ;
924 
925 l_amount_received_tbl NUMERIC2_TBL;
926 l_tax_received_tbl NUMERIC1_TBL;
927 l_org_id_tbl	zx_extract_pkg.INTERNAL_ORGANIZATION_ID_TBL;
928 
929 l_bal_seg_prompt_tbl ATTRIBUTE1_TBL;
930 
931 l_set_of_books_id apps.gl_sets_of_books.set_of_books_id%type;
932 l_period_from apps.gl_period_statuses.period_name%type;
933 l_period_to apps.gl_period_statuses.period_name%type;
934 
935 BEGIN
936 
937 -- IF PG_DEBUG = 'Y' THEN
938 --
939 --             arp_standard.debug('ZX.plsql.ZX_XX_EXTRACT_PKG.populate_tax_juris_for_usstr(+) ');
940 -- END IF;
941 	g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
942 
943 	IF (g_level_procedure >= g_current_runtime_level ) THEN
944 	FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AP.BEGIN',
945 				      'ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR(+)');
946 	END IF;
947 
948 
949  -- Get necessary parameters from TRL Global Variables
950 
951  P_REPORT_NAME       := P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME;
952  P_REQUEST_ID        := P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
953  prev_event_class_mapping_id := 0;
954 
955 	IF (g_level_statement >= g_current_runtime_level ) THEN
956 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
957 					      'p_report_name : '||P_REPORT_NAME);
958 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
959 					      'p_request_id : '||p_request_id);
960 	END IF;
961 
962  IF P_REPORT_NAME = 'ARXSTR' THEN
963 
964     BEGIN
965        SELECT  detail_tax_line_id,
966                event_class_mapping_id,
967                trx_id,
968                trx_line_id,
969                trx_level_type,
970                place_of_supply_type_code,
971                ship_to_location_id,
972                ship_from_location_id,
973                bill_to_location_id,
974                bill_from_location_id,
975                poa_location_id,
976                poo_location_id,
977                def_place_of_supply_type_code
978        BULK COLLECT INTO  l_detail_tax_line_id_tbl,
979                           l_event_class_mapping_id_tbl,
980                           l_trx_id_tbl,
981                           l_trx_line_id_tbl,
982                           l_trx_level_type_tbl,
983                           l_pos_type_code_tbl,
984                           l_ship_to_location_id_tbl,
985                           l_ship_from_location_id_tbl,
986                           l_bill_to_location_id_tbl,
987                           l_bill_from_location_id_tbl,
988                           l_poa_location_id_tbl,
989                           l_poo_location_id_tbl,
990                           l_def_pos_type_code_tbl
991        FROM  zx_rep_trx_detail_t itf
992        WHERE  itf.request_id = P_REQUEST_ID;
993     EXCEPTION
994        WHEN OTHERS THEN
995           null;
996 /*
997           IF PG_DEBUG = 'Y' THEN
998              l_err_msg := substrb(SQLERRM,1,120);
999              arp_standard.debug('ZX_XX_EXTRACT_PKG.us_sales_tax_rep.'|| P_REPORT_NAME ||':'||l_err_msg);
1000           END IF;
1001 */
1002     END;
1003 
1004 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1005 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1006 					      'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
1007 	END IF;
1008 
1009     FOR i in 1..l_trx_id_tbl.last LOOP
1010 
1011        l_place_of_supply := nvl(l_pos_type_code_tbl(i),nvl(l_def_pos_type_code_tbl(i),'SHIP_TO'));
1012        IF l_place_of_supply = 'SHIP_TO' THEN
1013           l_location_type_tbl(1):= 'SHIP_TO';
1014           l_location_id_tbl(1):= l_ship_to_location_id_tbl(i);
1015        ELSIF l_place_of_supply = 'SHIP_FROM' THEN
1016           l_location_type_tbl(1):= 'SHIP_FROM';
1017           l_location_id_tbl(1):= l_ship_from_location_id_tbl(i);
1018        ELSIF l_place_of_supply = 'BILL_TO' THEN
1019           l_location_type_tbl(1):= 'BILL_TO';
1020           l_location_id_tbl(1):= l_bill_to_location_id_tbl(i);
1021        ELSIF l_place_of_supply = 'BILL_FROM' THEN
1022           l_location_type_tbl(1):= 'BILL_FROM';
1023           l_location_id_tbl(1):= l_bill_from_location_id_tbl(i);
1024        ELSIF l_place_of_supply = 'POA' THEN
1025           l_location_type_tbl(1):= 'POA';
1026           l_location_id_tbl(1):= l_poa_location_id_tbl(i);
1027        ELSIF l_place_of_supply = 'POO' THEN
1028           l_location_type_tbl(1):= 'POO';
1029           l_location_id_tbl(1):= l_poo_location_id_tbl(i);
1030        END IF;
1031 
1032 
1033 /* Check existence of input combination in table ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl */
1034 
1035        RETRIEVE_GEO_VALUE(l_event_class_mapping_id_tbl(i),
1036                           l_trx_id_tbl(i),
1037                           l_trx_line_id_tbl(i),
1038                           l_trx_level_type_tbl(i),
1039                           l_location_type_tbl(1),
1040                           l_location_id_tbl(1),
1041                           'STATE',
1042                           l_state_tbl(i),
1043                           l_geo_val_found);
1044 
1045 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1046 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1047 					      'l_geo_val_found : '||arp_trx_util.boolean_to_varchar2(l_geo_val_found));
1048 	END IF;
1049 
1050        IF l_geo_val_found THEN
1051           null;
1052        ELSE
1053           IF prev_event_class_mapping_id <> l_event_class_mapping_id_tbl(i) THEN
1054              BEGIN
1055                 SELECT
1056                    zxevntclsmap.event_class_mapping_id,
1057                    zxevntclsmap.ship_to_party_type,
1058                    zxevntclsmap.ship_from_party_type,
1059                    zxevntclsmap.poa_party_type,
1060                    zxevntclsmap.poo_party_type,
1061                    zxevntclsmap.bill_to_party_type,
1062                    zxevntclsmap.bill_from_party_type
1063                 INTO
1064                    prev_event_class_mapping_id,
1065                    zx_valid_init_params_pkg.source_rec.ship_to_party_type,
1066                    zx_valid_init_params_pkg.source_rec.ship_from_party_type,
1067                    zx_valid_init_params_pkg.source_rec.poa_party_type,
1068                    zx_valid_init_params_pkg.source_rec.poo_party_type,
1069                    zx_valid_init_params_pkg.source_rec.bill_to_party_type,
1070                    zx_valid_init_params_pkg.source_rec.bill_from_party_type
1071                 FROM  ZX_EVNT_CLS_MAPPINGS zxevntclsmap
1072                 WHERE zxevntclsmap.event_class_mapping_id = l_event_class_mapping_id_tbl(i);
1073 
1074 		IF ( g_level_statement>= g_current_runtime_level ) THEN
1075 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1076 						      'Rows fetched from ZX_EVNT_CLS_MAPPINGS : '||to_char(SQL%ROWCOUNT) );
1077 		END IF;
1078              EXCEPTION
1079                 WHEN OTHERS THEN
1080                    null;
1081 		IF ( g_level_statement>= g_current_runtime_level ) THEN
1082 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1083 			'Error Message ZX_EVNT_CLS_MAPPINGS : '||substrb(SQLERRM,1,120) );
1084 		END IF;
1085 /*
1086                    IF PG_DEBUG = 'Y' THEN
1087                       l_err_msg := substrb(SQLERRM,1,120);
1088                       arp_standard.debug('ZX_XX_EXTRACT_PKG.us_sales_tax_rep.'|| P_REPORT_NAME ||':'||l_err_msg);
1089                    END IF;
1090 */
1091              END;
1092           END IF;
1093 
1094           BEGIN
1095 
1096 		IF ( g_level_statement>= g_current_runtime_level ) THEN
1097 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1098 						      'Before call to ZX_TCM_GEO_JUR_PKG.populate_loc_geography_info ');
1099 		END IF;
1100 
1101              ZX_TCM_GEO_JUR_PKG.populate_loc_geography_info
1102                 (l_event_class_mapping_id_tbl(i),
1103                  l_trx_id_tbl(i),
1104                  l_trx_line_id_tbl(i),
1105                  l_trx_level_type_tbl(i),
1106                  l_location_type_tbl,
1107                  l_location_id_tbl,
1108                  x_return_status);
1109           EXCEPTION
1110              WHEN OTHERS THEN
1111                 null;
1112 		IF ( g_level_statement>= g_current_runtime_level ) THEN
1113 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1114 			'Error Message : ZX_TCM_GEO_JUR_PKG.populate_loc_geography_info : '||substrb(SQLERRM,1,120) );
1115 		END IF;
1116 /*
1117                 IF PG_DEBUG = 'Y' THEN
1118                    l_err_msg := substrb(SQLERRM,1,120);
1119                    arp_standard.debug('ZX_XX_EXTRACT_PKG.us_sales_tax_rep.'|| P_REPORT_NAME ||':'||l_err_msg);
1120                 END IF;
1121 */
1122           END;
1123           RETRIEVE_GEO_VALUE(l_event_class_mapping_id_tbl(i),
1124                              l_trx_id_tbl(i),
1125                              l_trx_line_id_tbl(i),
1126                              l_trx_level_type_tbl(i),
1127                              l_location_type_tbl(1),
1128                              l_location_id_tbl(1),
1129                              'STATE',
1130                              l_state_tbl(i),
1131                              l_geo_val_found);
1132 
1133 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1134 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1135 					      'Retrieved geo_value for STATE : '||l_state_tbl(i) ||' l_geo_val_found : '||arp_trx_util.boolean_to_varchar2(l_geo_val_found));
1136 	END IF;
1137 
1138        END IF;
1139           RETRIEVE_GEO_VALUE(l_event_class_mapping_id_tbl(i),
1140                              l_trx_id_tbl(i),
1141                              l_trx_line_id_tbl(i),
1142                              l_trx_level_type_tbl(i),
1143                              l_location_type_tbl(1),
1144                              l_location_id_tbl(1),
1145                              'COUNTY',
1146                              l_county_tbl(i),
1147                              l_geo_val_found);
1148 
1149 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1150 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1151 					      'Retrieved geo_value for COUNTY : '||l_county_tbl(i) ||' l_geo_val_found : '||arp_trx_util.boolean_to_varchar2(l_geo_val_found));
1152 	END IF;
1153 
1154           RETRIEVE_GEO_VALUE(l_event_class_mapping_id_tbl(i),
1155                              l_trx_id_tbl(i),
1156                              l_trx_line_id_tbl(i),
1157                              l_trx_level_type_tbl(i),
1158                              l_location_type_tbl(1),
1159                              l_location_id_tbl(1),
1160                              'CITY',
1161                              l_city_tbl(i),
1162                              l_geo_val_found);
1163 
1164 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1165 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1166 					      'Retrieved geo_value for CITY : '||l_city_tbl(i) ||' l_geo_val_found : '||arp_trx_util.boolean_to_varchar2(l_geo_val_found));
1167 	END IF;
1168 
1169 
1170     END LOOP;
1171 
1172          -- Insert lines into JX EXT Table with Calculated amount --
1173 
1174     FORALL i in l_detail_tax_line_id_tbl.first..l_detail_tax_line_id_tbl.last
1175 
1176        INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
1177                                        detail_tax_line_id,
1178                                        attribute1,
1179                                        attribute2,
1180                                        attribute3,
1181                                        created_by,
1182                                        creation_date,
1183                                        last_updated_by,
1184                                        last_update_date,
1185                                        last_update_login)
1186                                VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1187                                        l_detail_tax_line_id_tbl(i),
1188                                        l_state_tbl(i),
1189                                        l_county_tbl(i),
1190                                        l_city_tbl(i),
1191                                        fnd_global.user_id,
1192                                        sysdate,
1193                                        fnd_global.user_id,
1194                                        sysdate,
1195                                        fnd_global.login_id);
1196 
1197 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1198 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1199 					      'After insertion into zx_rep_trx_jx_ext_t ');
1200 	END IF;
1201 
1202                 -- Delete Unwanted lines from Detail ITF
1203 
1204        BEGIN
1205                 DELETE from zx_rep_trx_detail_t itf
1206                  WHERE itf.request_id = p_request_id
1207                    AND NOT EXISTS ( SELECT 1
1208                                       FROM zx_rep_trx_jx_ext_t ext
1209                                      WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
1210 
1211 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1212 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1213 					      'After deletion from zx_rep_trx_detail_t : '||to_char(SQL%ROWCOUNT) );
1214 	END IF;
1215 
1216        EXCEPTION
1217           WHEN OTHERS THEN
1218              null;
1219 		IF ( g_level_statement>= g_current_runtime_level ) THEN
1220 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1221 			'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
1222 		END IF;
1223 /*
1224              IF PG_DEBUG = 'Y' THEN
1225                 l_err_msg := substrb(SQLERRM,1,120);
1226                 arp_standard.debug('ZX_XX_EXTRACT_PKG.populate_us_sales_tax_rep_ext.'||p_report_name || '.'||l_err_msg);
1227              END IF;
1228 */
1229        END;
1230  END IF;  -- End of P_REPORT_NAME = ..
1231 
1232 --Bug 5251425 : To Populate General Ledger Activity(C_GL_ACTIVITY_DISP) and C_BAL_SEGMENT_PROMPT into ZX_REP_TRX_JX_EXT_T.numeric1 and ZX_REP_TRX_JX_EXT_T.attribute1 respectively ..
1233  IF ( p_report_name = 'ZXXVATRN' ) THEN
1234 	-- get details for C_GL_ACTIVITY_DISP
1235 	BEGIN
1236 /*		select  gl.set_of_books_id
1237 		into l_set_of_books_id
1238 		from gl_sets_of_books gl, ar_system_parameters ar
1239 		where gl.set_of_books_id = ar.set_of_books_id;
1240 */
1241 
1242 IF ( g_level_statement>= g_current_runtime_level ) THEN
1243                 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1244                 'P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW : '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW);
1245                 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1246                 'P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH : '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH);
1247 END IF;
1248 		select gl.period_name
1249 		into   l_period_from
1250 		from   gl_period_statuses gl
1251 --, ar_system_parameters ar
1252 		where  gl.start_date = P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW
1253 		and    gl.set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
1254       --l_set_of_books_id
1255 		and    gl.application_id = 222
1256 		and    rownum = 1;
1257 
1258 		select gl.period_name
1259 		into   l_period_to
1260 		from   gl_period_statuses gl
1261 --, ar_system_parameters ar
1262 		where  gl.end_date >= P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH
1263 		and    gl.set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
1264           ---l_set_of_books_id
1265 		and    gl.application_id = 222
1266 		and    rownum = 1;
1267 
1268 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1269 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1270 					      'P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH : '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH);
1271 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1272 					      'P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW : '||P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW);
1273 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1274 					      'l_set_of_books_id : '||l_set_of_books_id);
1275 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1276 					      'l_period_from : '||l_period_from);
1277 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1278 					      'l_period_to : '||l_period_to);
1279 	END IF;
1280 	EXCEPTION WHEN OTHERS THEN
1281 		NULL ;
1282 		IF ( g_level_statement>= g_current_runtime_level ) THEN
1283 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1284 			'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
1285 		END IF;
1286 	END ;
1287 
1288 
1289 	DECLARE
1290 		l_appcol_name                  varchar2(1000);
1291 		l_seg_name                     varchar2(1000);
1292 		l_prompt                       varchar2(1000) := '';
1293 		l_value_set_name               varchar2(1000);
1294 		L_SEG_NUM			NUMBER;
1295 		L_CHART_OF_ACCOUNTS_ID		apps.gl_sets_of_books.chart_of_accounts_id%type;
1296 		L_BALANCING_SEGMENT		varchar2(100);
1297 		l_bool				boolean;
1298 	BEGIN
1299 		-- get details C_BAL_SEGMENT_PROMPT
1300 		select to_char(chart_of_accounts_id)
1301 		into l_CHART_OF_ACCOUNTS_ID
1302                 from gl_ledgers where ledger_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID;
1303 	--	from   gl_sets_of_books gl, ar_system_parameters ar
1304 	--	where  gl.set_of_books_id = ar.set_of_books_id;
1305 
1306 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1307 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1308 					      'Before calling fa_rx_flex_pkg.flex_sql to get balancing segment ' );
1309 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1310 					      'l_CHART_OF_ACCOUNTS_ID : '||l_CHART_OF_ACCOUNTS_ID);
1311 	END IF;
1312 
1313 		l_balancing_segment := fa_rx_flex_pkg.flex_sql
1314 				(
1315 					p_application_id =>101,
1316 					p_id_flex_code => 'GL#',
1317 					p_id_flex_num => l_CHART_OF_ACCOUNTS_ID,
1318 					p_table_alias => '',
1319 					p_mode => 'SELECT',
1320 					p_qualifier => 'GL_BALANCING'
1321 				);
1322 
1323 		SELECT to_number(SubStr(l_balancing_segment,8)) INTO L_SEG_NUM  FROM dual;
1324 
1325 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1326 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1327 					      'l_balancing_segment : '||l_balancing_segment);
1328 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1329 					      'L_SEG_NUM : '||L_SEG_NUM);
1330 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1331 					      'Before calling fnd_flex_apis.get_segment_info to get the Balancing Seg prompt' );
1332 	END IF;
1333 
1334 		l_bool := apps.fnd_flex_apis.get_segment_info(
1335 			x_application_id =>101,
1336 			x_id_flex_code =>'GL#',
1337 			x_id_flex_num =>l_CHART_OF_ACCOUNTS_ID,
1338 			x_seg_num => L_SEG_NUM,
1339 			x_appcol_name => l_appcol_name ,
1340 			x_seg_name => l_seg_name ,
1341 			x_prompt => l_prompt ,
1342 			x_value_set_name => l_value_set_name
1343 		);
1344 
1345 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1346 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1347 					      'l_prompt : '||l_prompt);
1348 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1349 					      'l_seg_name : '||l_seg_name);
1350 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1351 					      'l_value_set_name : '||l_value_set_name);
1352 	END IF;
1353 
1354 		SELECT
1355 			det.detail_tax_line_id,
1356 			det.trx_id,
1357 			act.ACTG_LINE_CCID,
1358 			det.ledger_id
1359 		BULK COLLECT INTO
1360 			l_detail_tax_line_id_tbl,
1361 			l_trx_id_tbl,
1362 			L_CCID_TBL,
1363 			L_LEDGER_ID_TBL
1364 		FROM
1365 			zx_rep_trx_detail_t det ,
1366 			ZX_REP_ACTG_EXT_T act
1367 		WHERE  det.request_id = p_request_id
1368 		AND det.DETAIL_TAX_LINE_ID = ACT.DETAIL_TAX_LINE_ID(+);
1369 
1370 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1371 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1372 					      'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
1373 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1374 			'Before calling ARP_STANDARD.gl_activity to get the gl_activity value.' );
1375 	END IF;
1376 
1377 		FOR i IN 1..nvl(l_detail_tax_line_id_tbl.COUNT,0)
1378 		LOOP
1379 
1380 
1381              IF t_gl_activity_tbl.EXISTS(L_CCID_TBL(i)) THEN
1382                 null;
1383              ELSE
1384                      t_gl_activity_tbl(L_CCID_TBL(i)) := null;
1385              END IF;
1386 
1387              IF t_gl_activity_tbl(L_CCID_TBL(i)) is NULL THEN
1388 
1389 			ARP_STANDARD.gl_activity(
1390 			l_period_from,
1391 			l_period_to,
1392 			L_CCID_TBL(i),
1393 			L_LEDGER_ID_TBL(i),
1394 			l_period_net_dr_tbl(i),
1395 			l_period_net_cr_tbl(i));
1396 
1397 			t_gl_activity_tbl(L_CCID_TBL(i)) := l_period_net_dr_tbl(i) - l_period_net_cr_tbl(i);
1398   IF ( g_level_statement>= g_current_runtime_level ) THEN
1399      FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1400                 'l_period_net_dr_tbl : '||to_char(l_period_net_dr_tbl(i))||'-'||to_char(l_period_net_cr_tbl(i)));
1401   END IF;
1402 
1403 
1404 	l_gl_activity_tbl(i) := t_gl_activity_tbl(L_CCID_TBL(i));
1405              ELSE
1406                 l_gl_activity_tbl(i) := 0;
1407              END IF;
1408 			l_bal_seg_prompt_tbl(i) := l_prompt ;
1409 		END LOOP ;
1410 
1411 		FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1412 			INSERT INTO ZX_REP_TRX_JX_EXT_T
1413 			(
1414 				detail_tax_line_ext_id,
1415 				detail_tax_line_id,
1416 				numeric1,
1417 				attribute1,
1418 				created_by,
1419 				creation_date,
1420 				last_updated_by,
1421 				last_update_date,
1422 				last_update_login
1423 			)
1424 			VALUES
1425 			(
1426 				zx_rep_trx_jx_ext_t_s.nextval,
1427 				l_detail_tax_line_id_tbl(i),
1428 				l_gl_activity_tbl(i),
1429 				l_bal_seg_prompt_tbl(i),
1430 				fnd_global.user_id,
1431 				sysdate,
1432 				fnd_global.user_id,
1433 				sysdate,
1434 				fnd_global.login_id
1435 			);
1436 
1437 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1438 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1439 					      'After insertion into zx_rep_trx_jx_ext_t ');
1440 	END IF;
1441 
1442 	EXCEPTION WHEN OTHERS THEN
1443 --		NULL ;
1444 		IF ( g_level_statement>= g_current_runtime_level ) THEN
1445 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1446 			'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
1447 		END IF;
1448 	END;
1449 
1450  END IF ;
1451 --Bug 5251425 : To Derive Logic for C_AMOUNT_RECEIVED and C_TAX_AMOUNT_RECEIVED
1452  IF ( p_report_name = 'ZXXCDE' ) THEN
1453 
1454 	SELECT dtl.detail_tax_line_id,
1455 	       dtl.trx_id ,
1456 	       dtl.internal_organization_id
1457 	BULK COLLECT INTO
1458 		l_detail_tax_line_id_tbl,
1459 		l_trx_id_tbl,
1460 		l_org_id_tbl
1461 	FROM zx_rep_trx_detail_t dtl ,
1462 	     ar_receivable_applications_all cash
1463 	WHERE
1464 		dtl.trx_id = cash.applied_customer_trx_id
1465 		AND dtl.request_id = p_request_id ;
1466 
1467 	IF ( g_level_statement>= g_current_runtime_level ) THEN
1468 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1469 					      'Count fetched : '||to_char(nvl(l_detail_tax_line_id_tbl.count,0)) );
1470 
1471 		FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1472 					      'Before Call to GET_RECEIVED_AMOUNTS procedure ' );
1473 	END IF;
1474 
1475 
1476 		GET_RECEIVED_AMOUNTS
1477 		(
1478 			l_detail_tax_line_id_tbl,
1479 			l_trx_id_tbl	        ,
1480 			l_org_id_tbl		,
1481 			l_amount_received_tbl	,
1482 			l_tax_received_tbl
1483 		);
1484 
1485 	 FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
1486 	   INSERT INTO ZX_REP_TRX_JX_EXT_T
1487 	       (detail_tax_line_ext_id,
1488 		detail_tax_line_id,
1489 		numeric1, --C_TAX_AMOUNT_RECEIVED
1490 		numeric2,--C_AMOUNT_RECEIVED
1491 		created_by,
1492 		creation_date,
1493 		last_updated_by,
1494 		last_update_date,
1495 		last_update_login)
1496 	   VALUES (zx_rep_trx_jx_ext_t_s.nextval,
1497 		l_detail_tax_line_id_tbl(i),
1498 		l_tax_received_tbl(i),
1499 		l_amount_received_tbl(i),
1500 		fnd_global.user_id,
1501 		sysdate,
1502 		fnd_global.user_id,
1503 		sysdate,
1504 		fnd_global.login_id);
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 					      'After insertion into zx_rep_trx_jx_ext_t ');
1509 	END IF;
1510 
1511 
1512  END IF ;
1513 
1514  EXCEPTION
1515     WHEN OTHERS THEN
1516 --       null;
1517 		IF ( g_level_statement>= g_current_runtime_level ) THEN
1518 			FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR',
1519 			'Error Message for report '||p_report_name ||' : '||substrb(SQLERRM,1,120) );
1520 		END IF;
1521 /*
1522        IF PG_DEBUG = 'Y' THEN
1523           l_err_msg := substrb(SQLERRM,1,120);
1524           arp_standard.debug('ZX_XX_EXTRACT_PKG.populate_us_sales_tax_rep_ext.'||p_report_name || '.'||l_err_msg);
1525        END IF;
1526 */
1527 	IF (g_level_procedure >= g_current_runtime_level ) THEN
1528 		FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR.BEGIN',
1529 					      'ZX_CORE_REP_EXTRACT_PKG.POPULATE_CORE_AR(-)');
1530 	END IF;
1531 
1532  END POPULATE_CORE_AR;
1533 
1534 END ZX_CORE_REP_EXTRACT_PKG;