[Home] [Help]
PACKAGE BODY: APPS.ZX_AP_ACTG_POPULATE_PKG
Source
1 PACKAGE BODY ZX_AP_ACTG_POPULATE_PKG AS
2 /* $Header: zxripactgpoppvtb.pls 120.6 2006/01/20 18:50:43 apai ship $ */
3 --Populate variables
4 GT_TRX_CLASS_MNG ZX_EXTRACT_PKG.TRX_CLASS_MNG_TBL;
5 GT_TAX_RATE_CODE_REG_TYPE_MNG ZX_EXTRACT_PKG.TAX_RATE_CODE_REG_TYPE_MNG_TBL;
6 GT_TRX_QUANTITY_UOM_MNG ZX_EXTRACT_PKG.TRX_QUANTITY_UOM_MNG_TBL;
7 GT_TAXABLE_DISC_AMT ZX_EXTRACT_PKG.TAXABLE_DISC_AMT_TBL;
8 GT_TAXABLE_DISC_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAXABLE_DISC_AMT_FUN_CURR_TBL;
9 GT_TAX_DISC_AMT ZX_EXTRACT_PKG.TAX_DISC_AMT_TBL;
10 GT_TAX_DISC_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAX_DISC_AMT_FUN_CURR_TBL;
11 GT_TAX_RATE_VAT_TRX_TYPE_DESC ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_DESC_TBL;
12 GT_BILLING_TP_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_NAME_ALT_TBL;
13 GT_BILLING_TP_SIC_CODE ZX_EXTRACT_PKG.BILLING_TP_SIC_CODE_TBL;
14 GT_BILLING_TP_CITY ZX_EXTRACT_PKG.BILLING_TP_CITY_TBL;
15 GT_BILLING_TP_COUNTY ZX_EXTRACT_PKG.BILLING_TP_COUNTY_TBL;
16 GT_BILLING_TP_STATE ZX_EXTRACT_PKG.BILLING_TP_STATE_TBL;
17 GT_BILLING_TP_PROVINCE ZX_EXTRACT_PKG.BILLING_TP_PROVINCE_TBL;
18 GT_BILLING_TP_ADDRESS1 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS1_TBL;
19 GT_BILLING_TP_ADDRESS2 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS2_TBL;
20 GT_BILLING_TP_ADDRESS3 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS3_TBL;
21 GT_BILLING_TP_ADDR_LINES_ALT ZX_EXTRACT_PKG.BILLING_TP_ADDR_LINES_ALT_TBL;
22 GT_BILLING_TP_COUNTRY ZX_EXTRACT_PKG.BILLING_TP_COUNTRY_TBL;
23 GT_BILLING_TP_POSTAL_CODE ZX_EXTRACT_PKG.BILLING_TP_POSTAL_CODE_TBL;
24 GT_SHIPPING_TP_CITY ZX_EXTRACT_PKG.SHIPPING_TP_CITY_TBL;
25 GT_SHIPPING_TP_COUNTY ZX_EXTRACT_PKG.SHIPPING_TP_COUNTY_TBL;
26 GT_SHIPPING_TP_STATE ZX_EXTRACT_PKG.SHIPPING_TP_STATE_TBL;
27 GT_SHIPPING_TP_ADDRESS1 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS1_TBL;
28 GT_SHIPPING_TP_ADDRESS2 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS2_TBL;
29 GT_SHIPPING_TP_ADDRESS3 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS3_TBL;
30 GT_SHIPPING_TP_COUNTRY ZX_EXTRACT_PKG.SHIPPING_TP_COUNTRY_TBL;
31 GT_SHIPPING_TP_POSTAL_CODE ZX_EXTRACT_PKG.SHIPPING_TP_POSTAL_CODE_TBL;
32 GT_BILLING_TRADING_PARTNER_ID ZX_EXTRACT_PKG.BILLING_TRADING_PARTNER_ID_TBL;
33 GT_BILLING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
34 GT_BILLING_TP_TAX_REP_FLAG ZX_EXTRACT_PKG.BILLING_TP_TAX_REP_FLAG_TBL;
35 GT_OFFICE_SITE_FLAG ZX_EXTRACT_PKG.OFFICE_SITE_FLAG_TBL;
36 GT_REGISTRATION_STATUS_CODE ZX_EXTRACT_PKG.REGISTRATION_STATUS_CODE_TBL;
37 GT_BILLING_TP_NUMBER ZX_EXTRACT_PKG.BILLING_TP_NUMBER_TBL;
38 GT_BILLING_TP_TAX_REG_NUM ZX_EXTRACT_PKG.BILLING_TP_TAX_REG_NUM_TBL;
39 GT_BILLING_TP_TAXPAYER_ID ZX_EXTRACT_PKG.BILLING_TP_TAXPAYER_ID_TBL;
40 GT_BILLING_TP_SITE_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_SITE_NAME_ALT_TBL;
41 GT_BILLING_TP_NAME ZX_EXTRACT_PKG.BILLING_TP_NAME_TBL;
42 GT_SHIPPING_TP_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_NAME_ALT_TBL;
43 GT_SHIPPING_TP_SIC_CODE ZX_EXTRACT_PKG.BILLING_TP_SIC_CODE_TBL;
44 GT_GDF_PO_VENDOR_SITE_ATT17 ZX_EXTRACT_PKG.GDF_PO_VENDOR_SITE_ATT17_TBL;
45
46 --Gloabl variables to fetch detail cursor
47 GT_DETAIL_TAX_LINE_ID ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
48 GT_APPLICATION_ID ZX_EXTRACT_PKG.APPLICATION_ID_TBL;
49 GT_INTERNAL_ORGANIZATION_ID ZX_EXTRACT_PKG.INTERNAL_ORGANIZATION_ID_TBL;
50 GT_TRX_ID ZX_EXTRACT_PKG.TRX_ID_TBL;
51 GT_TRX_LINE_ID ZX_EXTRACT_PKG.TRX_LINE_ID_TBL;
52 GT_TAX_LINE_ID ZX_EXTRACT_PKG.TAX_LINE_ID_TBL;
53 GT_TRX_LINE_TYPE ZX_EXTRACT_PKG.TRX_LINE_TYPE_TBL;
54 GT_TRX_LINE_CLASS ZX_EXTRACT_PKG.TRX_LINE_CLASS_TBL;
55 GT_SHIP_TO_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_TO_PARTY_TAX_PROF_ID_TBL;
56 GT_SHIP_FROM_PTY_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_FROM_PTY_TAX_PROF_ID_TBL;
57 GT_BILL_TO_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_TO_PARTY_TAX_PROF_ID_TBL;
58 GT_BILL_FROM_PTY_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_FROM_PTY_TAX_PROF_ID_TBL;
59 GT_SHIP_TO_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_TO_SITE_TAX_PROF_ID_TBL;
60 GT_BILL_TO_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_TO_SITE_TAX_PROF_ID_TBL;
61 GT_SHIP_FROM_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_FROM_SITE_TAX_PROF_ID_TBL;
62 GT_BILL_FROM_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_FROM_SITE_TAX_PROF_ID_TBL;
63 GT_BILL_FROM_PARTY_ID ZX_EXTRACT_PKG.BILL_FROM_PARTY_ID_TBL;
64 GT_BILL_FROM_PARTY_SITE_ID ZX_EXTRACT_PKG.BILL_FROM_PARTY_SITE_ID_TBL;
65 GT_HISTORICAL_FLAG ZX_EXTRACT_PKG.HISTORICAL_FLAG_TBL;
66 GT_ACTG_SOURCE_ID ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL;
67 GT_AE_HEADER_ID ZX_EXTRACT_PKG.ACTG_HEADER_ID_TBL;
68 GT_EVENT_ID ZX_EXTRACT_PKG.ACTG_EVENT_ID_TBL;
69 -- GT_ENTITY_ID ZX_EXTRACT_PKG.ACTG_ENTITY_ID_TBL;
70 GT_LINE_CCID ZX_EXTRACT_PKG.ACTG_LINE_CCID_TBL;
71 GT_TRX_ARAP_BALANCING_SEGMENT ZX_EXTRACT_PKG.TRX_ARAP_BALANCING_SEG_TBL;
72 GT_TRX_ARAP_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_ARAP_NATURAL_ACCOUNT_TBL;
73 GT_TRX_TAXABLE_BAL_SEG ZX_EXTRACT_PKG.TRX_TAXABLE_BALANCING_SEG_TBL;
74 GT_TRX_TAXABLE_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_TAXABLE_NATURAL_ACCT_TBL;
75 GT_TRX_TAX_BALANCING_SEGMENT ZX_EXTRACT_PKG.TRX_TAX_BALANCING_SEG_TBL;
76 GT_TRX_TAX_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_TAX_NATURAL_ACCOUNT_TBL;
77 GT_TAX_AMT ZX_EXTRACT_PKG.TAX_AMT_TBL;
78 GT_TAX_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL;
79 GT_TAXABLE_AMT ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
80 GT_TAXABLE_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL;
81 -- apai GT_REP_CONTEXT_ID ZX_EXTRACT_PKG.REP_CONTEXT_ID_TBL;
82 C_LINES_PER_COMMIT constant number := 5000;
83
84 G_REP_CONTEXT_ID NUMBER;
85 g_retcode NUMBER := 0;
86 g_current_runtime_level NUMBER;
87 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
88 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
89 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
90 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
91 g_error_buffer VARCHAR2(100);
92
93
94 PROCEDURE extract_party_info(i IN BINARY_INTEGER);
95
96 PROCEDURE get_accounting_info(P_TRX_ID IN NUMBER,
97 P_TRX_LINE_ID IN NUMBER,
98 P_TAX_LINE_ID IN NUMBER,
99 P_EVENT_ID IN NUMBER,
100 P_AE_HEADER_ID IN NUMBER,
101 P_TAX_DIST_ID IN NUMBER,
102 P_BALANCING_SEGMENT IN VARCHAR2,
103 P_ACCOUNTING_SEGMENT IN VARCHAR2,
104 P_SUMMARY_LEVEL IN VARCHAR2,
105 P_INCLUDE_DISCOUNTS IN VARCHAR2,
106 P_ORG_ID IN NUMBER,
107 i IN binary_integer);
108
109 PROCEDURE get_accounting_amounts(P_TRX_ID IN NUMBER,
110 P_TRX_LINE_ID IN NUMBER,
111 P_TAX_LINE_ID IN NUMBER,
112 -- P_ENTITY_ID IN NUMBER,
113 P_EVENT_ID IN NUMBER,
114 P_AE_HEADER_ID IN NUMBER,
115 P_TAX_DIST_ID IN NUMBER,
116 P_SUMMARY_LEVEL IN VARCHAR2,
117 P_LEDGER_ID IN NUMBER,
118 i IN binary_integer);
119
120 PROCEDURE get_discount_info
121 ( j IN BINARY_INTEGER,
122 P_TRX_ID IN NUMBER,
123 P_TAX_LINE_ID IN NUMBER,
124 P_SUMMARY_LEVEL IN VARCHAR2,
125 P_DIST_ID IN NUMBER,
126 P_TRX_LINE_ID IN NUMBER,
127 P_DISC_DISTRIBUTION_METHOD IN VARCHAR2,
128 P_LIABILITY_POST_LOOKUP_CODE IN VARCHAR2
129 );
130
131 PROCEDURE populate_meaning(
132 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
133 i IN BINARY_INTEGER);
134
135 PROCEDURE initialize_variables (
136 p_count IN NUMBER);
137
138 PROCEDURE update_zx_rep_detail_t(
139 P_COUNT IN BINARY_INTEGER);
140
141 PROCEDURE update_rep_actg_t(p_count IN NUMBER);
142
143 PROCEDURE update_additional_info(
144 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
145 P_MRC_SOB_TYPE IN VARCHAR2) IS
146
147 l_count number;
148 l_reporting_ledger_id NUMBER(15);
149 l_primary_ledger_id NUMBER(15);
150
151
152 CURSOR rep_detail_cursor(c_request_id IN number) IS
153 SELECT zx_dtl.DETAIL_TAX_LINE_ID,
154 zx_dtl.APPLICATION_ID,
155 zx_dtl.INTERNAL_ORGANIZATION_ID,
156 zx_dtl.TRX_ID,
157 zx_dtl.TRX_LINE_ID ,
158 zx_dtl.TAX_LINE_ID ,
159 zx_dtl.TRX_LINE_TYPE,
160 zx_dtl.TRX_LINE_CLASS,
161 zx_dtl.BILL_FROM_PARTY_TAX_PROF_ID,
162 zx_dtl.BILL_FROM_SITE_TAX_PROF_ID,
163 zx_dtl.SHIP_TO_SITE_TAX_PROF_ID,
164 zx_dtl.SHIP_FROM_SITE_TAX_PROF_ID,
165 zx_dtl.SHIP_TO_PARTY_TAX_PROF_ID,
166 zx_dtl.SHIP_FROM_PARTY_TAX_PROF_ID,
167 zx_dtl.BILL_FROM_PARTY_ID,
168 zx_dtl.BILL_FROM_PARTY_SITE_ID,
169 zx_dtl.HISTORICAL_FLAG,
170 ZX_ACTG.ACTG_SOURCE_ID,
171 ZX_ACTG.ACTG_HEADER_ID,
172 ZX_ACTG.ACTG_EVENT_ID,
173 -- ZX_ACTG.ACTG_ENTITY_ID,
174 ZX_ACTG.ACTG_LINE_CCID
175 FROM zx_rep_trx_detail_t zx_dtl,
176 zx_rep_actg_ext_t zx_actg
177 WHERE zx_dtl.request_id = c_request_id
178 AND zx_dtl.extract_source_ledger = 'AP'
179 AND zx_dtl.detail_tax_line_id = zx_actg.detail_tax_line_id;
180
181 l_balancing_segment VARCHAR2(25);
182 l_accounting_segment VARCHAR2(25);
183 l_ledger_id NUMBER(15);
184 BEGIN
185
186 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
187
188 IF (g_level_procedure >= g_current_runtime_level ) THEN
189 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.update_additional_info.BEGIN',
190 'update_additional_info(+)');
191 END IF;
192
193 l_ledger_id := NVL(P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEDGER_ID, P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID);
194
195 OPEN rep_detail_cursor(P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID);
196 LOOP
197 FETCH rep_detail_cursor BULK COLLECT INTO
198 GT_DETAIL_TAX_LINE_ID,
199 GT_APPLICATION_ID,
200 GT_INTERNAL_ORGANIZATION_ID,
201 GT_TRX_ID,
202 GT_TRX_LINE_ID,
203 GT_TAX_LINE_ID,
204 GT_TRX_LINE_TYPE,
205 GT_TRX_LINE_CLASS,
206 GT_BILL_FROM_PTY_TAX_PROF_ID,
207 GT_BILL_FROM_SITE_TAX_PROF_ID,
208 GT_SHIP_TO_SITE_TAX_PROF_ID,
209 GT_SHIP_FROM_SITE_TAX_PROF_ID,
210 GT_SHIP_TO_PARTY_TAX_PROF_ID,
211 GT_SHIP_FROM_PTY_TAX_PROF_ID,
212 GT_BILL_FROM_PARTY_ID,
213 GT_BILL_FROM_PARTY_SITE_ID,
214 GT_HISTORICAL_FLAG,
215 GT_ACTG_SOURCE_ID,
216 GT_AE_HEADER_ID,
217 GT_EVENT_ID,
218 -- GT_ENTITY_ID,
219 GT_LINE_CCID
220 LIMIT C_LINES_PER_COMMIT;
221
222 l_count := GT_DETAIL_TAX_LINE_ID.count;
223 IF (g_level_procedure >= g_current_runtime_level ) THEN
224 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.update_additional_info',
225 'Rows fetched by rep_detail_cursor :'||to_char(l_count));
226 END IF;
227
228 IF l_count > 0 THEN
229 initialize_variables(l_count);
230 G_REP_CONTEXT_ID := ZX_EXTRACT_PKG.GET_REP_CONTEXT_ID(P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID,
231 P_TRL_GLOBAL_VARIABLES_REC.request_id);
232
233 FOR i in 1..l_count
234 LOOP
235 /* apai
236 GT_REP_CONTEXT_ID(i) := ZX_EXTRACT_PKG.GET_REP_CONTEXT_ID(GT_INTERNAL_ORGANIZATION_ID(i),
237 P_TRL_GLOBAL_VARIABLES_REC.legal_entity_level,
238 P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID,
239 P_TRL_GLOBAL_VARIABLES_REC.request_id);
240 */
241 IF (g_level_procedure >= g_current_runtime_level ) THEN
242 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.update_additional_info',
243 ' GT_BILL_FROM_PTY_TAX_PROF_ID(i) :'||to_char(GT_DETAIL_TAX_LINE_ID(i)));
244 END IF;
245
246 extract_party_info(i);
247 populate_meaning(P_TRL_GLOBAL_VARIABLES_REC,i);
248
249 get_accounting_info(GT_TRX_ID(i),
250 GT_TRX_LINE_ID(i),
251 GT_TAX_LINE_ID(i),
252 GT_EVENT_ID(i),
253 GT_AE_HEADER_ID(i),
254 GT_ACTG_SOURCE_ID(i),
255 l_balancing_segment,
256 l_accounting_segment,
257 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
258 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_DISCOUNTS,
259 GT_INTERNAL_ORGANIZATION_ID(i),
260 -- l_ledger_id,
261 i) ;
262
263 get_accounting_amounts(GT_TRX_ID(i),
264 GT_TRX_LINE_ID(i),
265 GT_TAX_LINE_ID(i),
266 -- GT_ENTITY_ID(i),
267 GT_EVENT_ID(i),
268 GT_AE_HEADER_ID(i),
269 GT_ACTG_SOURCE_ID(i),
270 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
271 l_ledger_id,
272 i) ;
273 END LOOP;
274 ELSE
275 EXIT;
276 END IF;
277
278 EXIT WHEN rep_detail_cursor%NOTFOUND
279 OR rep_detail_cursor%NOTFOUND IS NULL;
280
281 END LOOP;
282
283 update_zx_rep_detail_t(l_count);
284 UPDATE_REP_ACTG_T(l_count);
285
286 IF (g_level_procedure >= g_current_runtime_level ) THEN
287 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.update_additional_info.END',
288 'update_additional_info(-)');
289 END IF;
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
294 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
295 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
296 FND_MSG_PUB.Add;
297 IF (g_level_unexpected >= g_current_runtime_level ) THEN
298 FND_LOG.STRING(g_level_unexpected,
299 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.update_additional_info',
300 g_error_buffer);
301 END IF;
302
303 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := g_retcode;
304
305 END update_additional_info;
306
307
308 PROCEDURE extract_party_info( i IN BINARY_INTEGER) IS
309
310 l_party_id zx_rep_trx_detail_t.bill_from_party_id%TYPE;
311 l_party_site_id zx_rep_trx_detail_t.bill_from_party_site_id%TYPE;
312 l_party_profile_id zx_rep_trx_detail_t.BILL_FROM_PARTY_TAX_PROF_ID%TYPE;
313 l_site_profile_id zx_rep_trx_detail_t.BILL_FROM_SITE_TAX_PROF_ID%TYPE;
314
315 l_tbl_index_party BINARY_INTEGER;
316 l_tbl_index_site BINARY_INTEGER;
317
318 -- If party_id is NULL and Historical flag 'N' then get the party ID from zx_party_tax_profile
319
320 CURSOR party_id_cur
321 (c_bill_from_ptp_id zx_rep_trx_detail_t.BILL_FROM_PARTY_TAX_PROF_ID%TYPE) IS
322 SELECT party_id
323 FROM zx_party_tax_profile
324 WHERE PARTY_TAX_PROFILE_ID = c_bill_from_ptp_id
325 AND party_type_code = 'THIRD_PARTY';
326
327 CURSOR party_site_id_cur
328 (c_bill_from_stp_id zx_rep_trx_detail_t.BILL_FROM_SITE_TAX_PROF_ID%TYPE) IS
329 SELECT party_id
330 FROM zx_party_tax_profile
331 WHERE PARTY_TAX_PROFILE_ID = c_bill_from_stp_id
332 AND party_type_code = 'THIRD_PARTY_SITE';
333 -- If party_id is NOT NULL and Historical flag 'Y' then get the party tax profile ID from zx_party_tax_profile
334
335 CURSOR party_profile_id_cur
336 (c_bill_from_party_id zx_rep_trx_detail_t.bill_from_party_id%TYPE) IS
337 SELECT party_tax_profile_id
338 FROM zx_party_tax_profile
339 WHERE party_id = c_bill_from_party_id
340 AND party_type_code = 'THIRD_PARTY';
341
342 CURSOR site_profile_id_cur
343 (c_bill_from_site_id zx_rep_trx_detail_t.bill_from_party_site_id%TYPE) IS
344 SELECT party_tax_profile_id
345 FROM zx_party_tax_profile
346 WHERE party_id = c_bill_from_site_id
347 AND party_type_code = 'THIRD_PARTY_SITE';
348
349 CURSOR party_cur
350 (c_bill_from_party_id zx_rep_trx_detail_t.bill_from_party_id%TYPE) IS
351 SELECT SEGMENT1,
352 VAT_REGISTRATION_NUM,
353 NUM_1099,
354 VENDOR_NAME,
355 VENDOR_NAME_ALT,
356 STANDARD_INDUSTRY_CLASS
357 FROM ap_suppliers
358 WHERE party_id = c_bill_from_party_id;
359
360 CURSOR party_site_cur
361 (c_bill_from_site_id zx_rep_trx_detail_t.bill_from_party_site_id%TYPE) IS
362 SELECT CITY,
363 COUNTY,
364 STATE,
365 PROVINCE,
366 ADDRESS_LINE1,
367 ADDRESS_LINE2,
368 ADDRESS_LINE3,
369 ADDRESS_LINES_ALT,
370 COUNTRY,
371 ZIP,
372 -- VENDOR_ID,
373 -- VENDOR_SITE_ID,
374 -- TAX_REPORTING_SITE_FLAG,
375 GLOBAL_ATTRIBUTE17,
376 VENDOR_SITE_CODE_ALT
377 FROM ap_supplier_sites_all
378 WHERE party_site_id = c_bill_from_site_id;
379
380
381 BEGIN
382
383 IF (g_level_procedure >= g_current_runtime_level ) THEN
384 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.extract_party_info.BEGIN',
385 'extract_party_info(+) '||to_char(i));
386 END IF;
387
388 IF NVL(gt_historical_flag(i),'N') = 'N' AND GT_BILL_FROM_PTY_TAX_PROF_ID(i) IS NOT NULL THEN
389 OPEN party_id_cur(GT_BILL_FROM_PTY_TAX_PROF_ID(i));
390 FETCH party_id_cur INTO l_party_id;
391
392 OPEN party_site_id_cur(GT_BILL_FROM_SITE_TAX_PROF_ID(i));
393 FETCH party_site_id_cur INTO l_party_site_id;
394 ELSE
395 OPEN party_profile_id_cur (GT_BILL_FROM_PARTY_ID(i));
396 FETCH party_profile_id_cur into l_party_profile_id;
397
398 OPEN site_profile_id_cur(GT_BILL_FROM_PARTY_SITE_ID(i));
399 FETCH site_profile_id_cur INTO l_site_profile_id;
400
401 l_party_id := GT_BILL_FROM_PARTY_ID(i);
402 l_party_site_id := GT_BILL_FROM_PARTY_SITE_ID(i);
403
404 END IF;
405 IF (g_level_procedure >= g_current_runtime_level ) THEN
406 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.extract_party_info',
407 ' l_party_id :'||to_char(l_party_id)||' '||to_char(GT_BILL_FROM_PTY_TAX_PROF_ID(i)));
408 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.extract_party_info',
409 ' GT_DETAIL_TAX_LINE_ID :'||to_char(l_party_id)||' '||to_char(GT_DETAIL_TAX_LINE_ID(i)));
410 END IF;
411
412 IF l_party_id IS NOT NULL THEN
413 l_tbl_index_party := dbms_utility.get_hash_value(to_char(l_party_id), 1,8192);
414
415 IF g_party_info_ap_tbl.EXISTS(l_tbl_index_party) THEN
416
417 GT_BILLING_TP_NUMBER(i) := g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NUMBER ;
418 GT_BILLING_TP_TAX_REG_NUM(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_TAX_REG_NUM;
419 GT_BILLING_TP_TAXPAYER_ID(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_TAXPAYER_ID;
420 GT_BILLING_TP_NAME(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NAME;
421 GT_BILLING_TP_NAME_ALT(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT;
422 GT_BILLING_TP_SIC_CODE(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE;
423
424 ELSE
425
426 OPEN party_cur (l_party_id);
427 FETCH party_cur INTO
428 GT_BILLING_TP_NUMBER(i),
429 GT_BILLING_TP_TAX_REG_NUM(i),
430 GT_BILLING_TP_TAXPAYER_ID(i),
431 GT_BILLING_TP_NAME(i),
432 GT_BILLING_TP_NAME_ALT(i),
433 GT_BILLING_TP_SIC_CODE(i);
434
435 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NUMBER := GT_BILLING_TP_NUMBER(i);
436 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_TAX_REG_NUM := GT_BILLING_TP_TAX_REG_NUM(i);
437 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_TAXPAYER_ID := GT_BILLING_TP_TAXPAYER_ID(i);
438 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NAME := GT_BILLING_TP_NAME(i);
439 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT := GT_BILLING_TP_NAME_ALT(i);
440 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE := GT_BILLING_TP_SIC_CODE(i);
441
442 IF (g_level_procedure >= g_current_runtime_level ) THEN
443 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.extract_party_info',
444 ' GT_BILLING_TP_NUMBER(i) :'||GT_BILLING_TP_NUMBER(i));
445 END IF;
446
447 END IF;
448 END IF;
449
450 IF l_party_site_id IS NOT NULL THEN
451 l_tbl_index_site := dbms_utility.get_hash_value(to_char(l_party_site_id), 1,8192);
452
453 IF g_party_site_tbl.EXISTS(l_tbl_index_site) THEN
454
455 GT_BILLING_TP_CITY(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_CITY;
456 GT_BILLING_TP_COUNTY(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTY;
457 GT_BILLING_TP_COUNTY(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTY;
458 GT_BILLING_TP_STATE(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_STATE;
459 GT_BILLING_TP_PROVINCE(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_PROVINCE;
460 GT_BILLING_TP_ADDRESS1(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS1;
461 GT_BILLING_TP_ADDRESS2(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS2;
462 GT_BILLING_TP_ADDRESS3(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS3;
463 GT_BILLING_TP_ADDR_LINES_ALT(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDR_LINES_ALT;
464 GT_BILLING_TP_COUNTRY(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTRY;
465 GT_BILLING_TP_POSTAL_CODE(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_POSTAL_CODE;
466 GT_GDF_PO_VENDOR_SITE_ATT17(i) := g_party_site_tbl(l_tbl_index_site).GDF_PO_VENDOR_SITE_ATT17;
467 GT_BILLING_TP_SITE_NAME_ALT(i) :=g_party_site_tbl(l_tbl_index_site).BILLING_TP_SITE_NAME_ALT;
468
469 ELSE
470
471 OPEN party_site_cur (l_party_site_id);
472 FETCH party_site_cur INTO
473 GT_BILLING_TP_CITY(i),
474 GT_BILLING_TP_COUNTY(i),
475 GT_BILLING_TP_STATE(i),
476 GT_BILLING_TP_PROVINCE(i),
477 GT_BILLING_TP_ADDRESS1(i),
478 GT_BILLING_TP_ADDRESS2(i),
479 GT_BILLING_TP_ADDRESS3(i),
480 GT_BILLING_TP_ADDR_LINES_ALT(i),
481 GT_BILLING_TP_COUNTRY(i),
482 GT_BILLING_TP_POSTAL_CODE(i),
483 GT_GDF_PO_VENDOR_SITE_ATT17(i),
484 GT_BILLING_TP_SITE_NAME_ALT(i);
485
486 g_party_site_tbl(l_tbl_index_site).BILLING_TP_CITY := GT_BILLING_TP_CITY(i);
487 g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTY := GT_BILLING_TP_COUNTY(i);
488 g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTY := GT_BILLING_TP_COUNTY(i);
489 g_party_site_tbl(l_tbl_index_site).BILLING_TP_PROVINCE := GT_BILLING_TP_STATE(i);
490 g_party_site_tbl(l_tbl_index_site).BILLING_TP_PROVINCE := GT_BILLING_TP_PROVINCE(i);
491 g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS1 := GT_BILLING_TP_ADDRESS1(i);
492 g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS2 := GT_BILLING_TP_ADDRESS2(i);
493 g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS3 := GT_BILLING_TP_ADDRESS3(i);
494 g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDR_LINES_ALT := GT_BILLING_TP_ADDR_LINES_ALT(i);
495 g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTRY := GT_BILLING_TP_COUNTRY(i);
496 g_party_site_tbl(l_tbl_index_site).BILLING_TP_POSTAL_CODE := GT_BILLING_TP_POSTAL_CODE(i);
497 g_party_site_tbl(l_tbl_index_site).GDF_PO_VENDOR_SITE_ATT17 := GT_GDF_PO_VENDOR_SITE_ATT17(i);
498 g_party_site_tbl(l_tbl_index_site).BILLING_TP_SITE_NAME_ALT := GT_BILLING_TP_SITE_NAME_ALT(i);
499
500 END IF;
501 END IF;
502
503 IF (g_level_procedure >= g_current_runtime_level ) THEN
504 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.extract_party_info.END',
505 'extract_party_info(-)');
506 END IF;
507
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
512 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
513 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
514 FND_MSG_PUB.Add;
515 IF (g_level_unexpected >= g_current_runtime_level ) THEN
516 FND_LOG.STRING(g_level_unexpected,
517 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.extract_party_info',
518 g_error_buffer);
519 END IF;
520 g_retcode := 2;
521 END extract_party_info;
522
523
524 PROCEDURE get_accounting_info(P_TRX_ID IN NUMBER,
525 P_TRX_LINE_ID IN NUMBER,
526 P_TAX_LINE_ID IN NUMBER,
527 -- P_ENTITY_ID IN NUMBER,
528 P_EVENT_ID IN NUMBER,
529 P_AE_HEADER_ID IN NUMBER,
530 P_TAX_DIST_ID IN NUMBER,
531 P_BALANCING_SEGMENT IN VARCHAR2,
532 P_ACCOUNTING_SEGMENT IN VARCHAR2,
533 P_SUMMARY_LEVEL IN VARCHAR2,
534 P_INCLUDE_DISCOUNTS IN VARCHAR2,
535 P_ORG_ID IN NUMBER,
536 -- P_LEDGER_ID IN NUMBER,
537 i IN BINARY_INTEGER) IS
538
539 CURSOR get_system_info_cur(c_org_id NUMBER) IS
540 SELECT discount_distribution_method,
541 disc_is_inv_less_tax_flag,
542 liability_post_lookup_code
543 FROM ap_system_parameters_all
544 WHERE org_id = c_org_id;
545
546 CURSOR trx_ccid (c_trx_id number, c_event_id number, c_ae_header_id number) IS
547 SELECT
548 ael.code_combination_id
549 FROM zx_rec_nrec_dist zx_dist,
550 xla_distribution_links lnk,
551 xla_ae_lines ael
552 WHERE zx_dist.trx_id = c_trx_id
553 AND lnk.application_id = 200
554 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
555 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
556 AND lnk.ae_header_id = ael.ae_header_id
557 AND lnk.ae_line_num = ael.ae_line_num
558 AND lnk.event_id = c_event_id
559 AND lnk.ae_header_id = c_ae_header_id
560 AND rownum =1;
561
562
563 CURSOR trx_line_ccid (c_trx_id NUMBER,
564 c_trx_line_id NUMBER,
565 c_event_id NUMBER,
566 c_ae_header_id NUMBER) IS
567 SELECT
568 ael.code_combination_id
569 FROM zx_rec_nrec_dist zx_dist,
570 xla_distribution_links lnk,
571 xla_ae_lines ael
572 WHERE zx_dist.trx_id = c_trx_id
573 AND zx_dist.trx_line_id = c_trx_line_id
574 AND lnk.application_id = 200
575 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
576 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
577 AND lnk.ae_header_id = ael.ae_header_id
578 AND lnk.ae_line_num = ael.ae_line_num
579 AND lnk.event_id = c_event_id
580 AND lnk.ae_header_id = c_ae_header_id
581 AND rownum =1;
582
583
584 -- For transavtion distribution level code combination id select in the build SQL
585 -- The following query can be removed ----
586
587 CURSOR trx_dist_ccid (c_trx_id NUMBER, c_trx_line_id NUMBER, c_event_id NUMBER, c_ae_header_id NUMBER) IS
588 SELECT
589 ael.code_combination_id
590 FROM zx_rec_nrec_dist zx_dist,
591 xla_distribution_links lnk,
592 xla_ae_lines ael
593 WHERE zx_dist.trx_id = c_trx_id
594 AND zx_dist.trx_line_id = c_trx_line_id
595 AND lnk.application_id = 200
596 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
597 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
598 AND lnk.ae_header_id = ael.ae_header_id
599 AND lnk.ae_line_num = ael.ae_line_num
600 AND lnk.event_id = c_event_id
601 AND lnk.ae_header_id = c_ae_header_id
602 AND rownum =1;
603
604
605
606 CURSOR tax_ccid (c_trx_id number, c_event_id number, c_ae_header_id number) IS
607 SELECT
608 ael.code_combination_id
609 FROM zx_rec_nrec_dist zx_dist,
610 xla_distribution_links lnk,
611 xla_ae_lines ael
612 WHERE zx_dist.trx_id = c_trx_id
613 AND lnk.application_id = 200
614 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
615 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
616 AND lnk.ae_header_id = ael.ae_header_id
617 AND lnk.ae_line_num = ael.ae_line_num
618 AND lnk.event_id = c_event_id
619 AND lnk.ae_header_id = c_ae_header_id
620 AND rownum =1;
621
622
623 CURSOR tax_line_ccid (c_trx_id number, c_tax_line_id NUMBER, c_event_id number, c_ae_header_id number) IS
624 SELECT
625 ael.code_combination_id
626 FROM zx_rec_nrec_dist zx_dist,
627 xla_distribution_links lnk,
628 xla_ae_lines ael
629 WHERE zx_dist.trx_id = c_trx_id
630 AND zx_dist.tax_line_id = c_tax_line_id
631 AND lnk.application_id = 200
632 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
633 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
634 AND lnk.ae_header_id = ael.ae_header_id
635 AND lnk.ae_line_num = ael.ae_line_num
636 AND lnk.event_id = c_event_id
637 AND lnk.ae_header_id = c_ae_header_id
638 AND rownum =1;
639
640
641 -- For transaction distribution level code combination id select in the build SQL
642 -- The following query can be removed ----
643
644 CURSOR tax_dist_ccid (c_trx_id NUMBER, c_tax_line_id NUMBER, c_tax_line_dist_id NUMBER,
645 c_event_id number, c_ae_header_id number) IS
646 SELECT
647 ael.code_combination_id
648 FROM zx_rec_nrec_dist zx_dist,
649 xla_distribution_links lnk,
650 xla_ae_lines ael
651 WHERE zx_dist.trx_id = c_trx_id
652 AND zx_dist.tax_line_id = c_tax_line_id
653 AND zx_dist.REC_NREC_TAX_DIST_ID = c_tax_line_dist_id
654 AND lnk.application_id = 200
655 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
656 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
657 AND lnk.ae_header_id = ael.ae_header_id
658 AND lnk.ae_line_num = ael.ae_line_num
659 AND lnk.event_id = c_event_id
660 AND lnk.ae_header_id = c_ae_header_id
661 AND rownum =1;
662
663
664 l_disc_is_inv_less_tax_flag VARCHAR2(1);
665 l_disc_distribution_method VARCHAR2(30);
666 l_liability_post_lookup_code VARCHAR2(30);
667
668 L_BAL_SEG_VAL VARCHAR2(240);
669 L_ACCT_SEG_VAL VARCHAR2(240);
670 L_SQL_STATEMENT1 VARCHAR2(1000);
671 L_SQL_STATEMENT2 VARCHAR2(1000);
672 l_ccid number;
673
674 BEGIN
675
676 IF (g_level_procedure >= g_current_runtime_level ) THEN
677 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.get_accounting_info.BEGIN',
678 'get_accounting_info(+)');
679 END IF;
680
681 GT_TRX_ARAP_BALANCING_SEGMENT(i) := NULL;
682 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := NULL;
683 GT_TRX_TAXABLE_BAL_SEG(i) := NULL;
684 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := NULL;
685 GT_TRX_TAX_BALANCING_SEGMENT(i) := NULL;
686 GT_TRX_TAX_NATURAL_ACCOUNT(i) := NULL;
687
688
689 L_BAL_SEG_VAL := '';
690 L_ACCT_SEG_VAL := '';
691
692 L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||
693 ' FROM GL_CODE_COMBINATIONS '||
694 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
695
696 L_SQL_STATEMENT2 := ' SELECT '||P_ACCOUNTING_SEGMENT ||
697 ' FROM GL_CODE_COMBINATIONS '||
698 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
699
700 OPEN get_system_info_cur(p_org_id);
701 FETCH get_system_info_cur
702 INTO l_disc_distribution_method,
703 l_disc_is_inv_less_tax_flag,
704 l_liability_post_lookup_code;
705 CLOSE get_system_info_cur;
706
707
708 IF NVL(l_disc_is_inv_less_tax_flag, 'N') = 'N' AND
709 NVL(l_disc_distribution_method, 'SYSTEM') <> 'SYSTEM' THEN
710
711 IF P_INCLUDE_DISCOUNTS = 'Y' THEN
712 get_discount_info(i,
713 P_TRX_ID,
714 P_TAX_LINE_ID,
715 P_SUMMARY_LEVEL,
716 P_TAX_DIST_ID,
717 P_TRX_LINE_ID,
718 l_disc_distribution_method,
719 l_liability_post_lookup_code);
720 END IF;
721 END IF;
722
723
724 IF P_SUMMARY_LEVEL = 'TRANSACTION' THEN
725 OPEN trx_ccid (p_trx_id, p_event_id, p_ae_header_id);
726 LOOP
727 FETCH trx_ccid INTO l_ccid;
728 EXIT WHEN trx_ccid%NOTFOUND;
729
730 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
731 USING l_ccid;
732
733 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
734 USING l_ccid;
735
736 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
737 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
738 ELSE
739 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
740 NULL;
741 ELSE
742 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
743 ||','||L_BAL_SEG_VAL;
744 END IF;
745 END IF;
746
747
748 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
749 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
750 ELSE
751 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
752 NULL;
753 ELSE
754 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
755 ||','||L_ACCT_SEG_VAL;
756 END IF;
757 END IF;
758
759 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
760 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
761 END LOOP;
762
763
764 OPEN tax_ccid (p_trx_id, p_event_id, p_ae_header_id);
765 LOOP
766 FETCH tax_ccid INTO l_ccid;
767 EXIT WHEN tax_ccid%NOTFOUND;
768
769 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
770 USING l_ccid;
771
772 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
773 USING l_ccid;
774
775 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
776 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
777 ELSE
778 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
779 NULL;
780 ELSE
781 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
782 ||','||L_BAL_SEG_VAL;
783 END IF;
784 END IF;
785
786
787 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
788 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
789 ELSE
790 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
791 NULL;
792 ELSE
793 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
794 ||','||L_ACCT_SEG_VAL;
795 END IF;
796 END IF;
797
798 END LOOP;
799
800 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
801 OPEN trx_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
802 LOOP
803 FETCH trx_line_ccid INTO l_ccid;
804 EXIT WHEN trx_line_ccid%NOTFOUND;
805
806 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
807 USING l_ccid;
808
809 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
810 USING l_ccid;
811
812 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
813 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
814 ELSE
815 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
816 NULL;
817 ELSE
818 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
819 ||','||L_BAL_SEG_VAL;
820 END IF;
821 END IF;
822
823
824 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
825 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
826 ELSE
827 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
828 NULL;
829 ELSE
830 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
831 ||','||L_ACCT_SEG_VAL;
832 END IF;
833 END IF;
834
835 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
836 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
837 END LOOP;
838
839
840 OPEN tax_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
841 LOOP
842 FETCH tax_line_ccid INTO l_ccid;
843 EXIT WHEN tax_line_ccid%NOTFOUND;
844
845 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
846 USING l_ccid;
847
848 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
849 USING l_ccid;
850
851 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
852 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
853 ELSE
854 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
855 NULL;
856 ELSE
857 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
858 ||','||L_BAL_SEG_VAL;
859 END IF;
860 END IF;
861
862
863 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
864 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
865 ELSE
866 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
867 NULL;
868 ELSE
869 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
870 ||','||L_ACCT_SEG_VAL;
871 END IF;
872 END IF;
873
874 END LOOP;
875
876
877 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
878 OPEN trx_dist_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
879 LOOP
880 FETCH trx_dist_ccid INTO l_ccid;
881 EXIT WHEN trx_dist_ccid%NOTFOUND;
882
883 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
884 USING l_ccid;
885
886 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
887 USING l_ccid;
888
889 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
890 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
891 ELSE
892 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
893 NULL;
894 ELSE
895 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
896 ||','||L_BAL_SEG_VAL;
897 END IF;
898 END IF;
899
900
901 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
902 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
903 ELSE
904 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
905 NULL;
906 ELSE
907 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
908 ||','||L_ACCT_SEG_VAL;
909 END IF;
910 END IF;
911
912 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
913 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
914 END LOOP;
915
916
917 OPEN tax_dist_ccid (p_trx_id, p_tax_line_id, p_tax_dist_id, p_event_id, p_ae_header_id);
918 LOOP
919 FETCH tax_ccid INTO l_ccid;
920 EXIT WHEN tax_ccid%NOTFOUND;
921
922 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
923 USING l_ccid;
924
925 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
926 USING l_ccid;
927
928 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
929 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
930 ELSE
931 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
932 NULL;
933 ELSE
934 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
935 ||','||L_BAL_SEG_VAL;
936 END IF;
937 END IF;
938
939
940 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
941 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
942 ELSE
943 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
944 NULL;
945 ELSE
946 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
947 ||','||L_ACCT_SEG_VAL;
948 END IF;
949 END IF;
950
951 END LOOP;
952 END IF; -- Summary Level
953
954 IF (g_level_procedure >= g_current_runtime_level ) THEN
955 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.get_accounting_info.END',
956 'get_accounting_info(-)');
957 END IF;
958
959 END get_accounting_info;
960
961
962 PROCEDURE get_accounting_amounts(P_TRX_ID IN NUMBER,
963 P_TRX_LINE_ID IN NUMBER,
964 P_TAX_LINE_ID IN NUMBER,
965 -- P_ENTITY_ID IN NUMBER,
966 P_EVENT_ID IN NUMBER,
967 P_AE_HEADER_ID IN NUMBER,
968 P_TAX_DIST_ID IN NUMBER,
969 P_SUMMARY_LEVEL IN VARCHAR2,
970 P_LEDGER_ID IN NUMBER,
971 i IN binary_integer) IS
972 -- Transaction Header Level
973
974 CURSOR taxable_amount_hdr (c_trx_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER, c_ledger_id NUMBER) IS
975 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
976 FROM zx_rec_nrec_dist zx_dist,
977 xla_distribution_links lnk,
978 xla_ae_headers aeh,
979 xla_ae_lines ael
980 WHERE zx_dist.trx_id = c_trx_id
981 AND lnk.application_id = 200
982 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
983 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
984 AND lnk.ae_header_id = c_ae_header_id
985 AND lnk.event_id = c_event_id
986 AND lnk.ae_line_num = ael.ae_line_num
987 AND lnk.ae_header_id = ael.ae_header_id
988 AND aeh.ae_header_id = ael.ae_header_id
989 AND aeh.ledger_id = c_ledger_id;
990
991
992 CURSOR tax_amount_hdr (c_trx_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER,c_ledger_id NUMBER) IS
993 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
994 FROM zx_rec_nrec_dist zx_dist,
995 xla_distribution_links lnk,
996 xla_ae_headers aeh,
997 xla_ae_lines ael
998 WHERE zx_dist.trx_id = c_trx_id
999 AND lnk.application_id = 200
1000 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
1001 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
1002 AND lnk.ae_header_id = c_ae_header_id
1003 AND lnk.event_id = c_event_id
1004 AND lnk.ae_line_num = ael.ae_line_num
1005 AND aeh.ae_header_id = ael.ae_header_id
1006 AND aeh.ledger_id = c_ledger_id;
1007
1008
1009 -- Transaction Line Level
1010
1011 CURSOR taxable_amount_line (c_trx_id NUMBER,c_trx_line_id NUMBER, c_ae_header_id NUMBER,
1012 c_event_id NUMBER, c_ledger_id NUMBER) IS
1013 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1014 FROM zx_rec_nrec_dist zx_dist,
1015 xla_distribution_links lnk,
1016 xla_ae_headers aeh,
1017 xla_ae_lines ael
1018 WHERE zx_dist.trx_id = c_trx_id
1019 AND zx_dist.trx_line_id = c_trx_line_id
1020 AND lnk.application_id = 200
1021 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
1022 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
1023 AND lnk.ae_header_id = c_ae_header_id
1024 AND lnk.event_id = c_event_id
1025 AND lnk.ae_line_num = ael.ae_line_num
1026 AND aeh.ae_header_id = ael.ae_header_id
1027 AND aeh.ledger_id = c_ledger_id;
1028
1029
1030 CURSOR tax_amount_line (c_trx_id NUMBER,c_tax_line_id NUMBER, c_ae_header_id NUMBER,
1031 c_event_id NUMBER, c_ledger_id NUMBER) IS
1032 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1033 FROM zx_rec_nrec_dist zx_dist,
1034 xla_distribution_links lnk,
1035 xla_ae_headers aeh,
1036 xla_ae_lines ael
1037 WHERE zx_dist.trx_id = c_trx_id
1038 AND zx_dist.tax_line_id = c_tax_line_id
1039 AND lnk.application_id = 200
1040 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
1041 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
1042 AND lnk.ae_header_id = c_ae_header_id
1043 AND lnk.event_id = c_event_id
1044 AND lnk.ae_line_num = ael.ae_line_num
1045 AND aeh.ae_header_id = ael.ae_header_id
1046 AND aeh.ledger_id = c_ledger_id;
1047
1048
1049 -- Transaction Distribution Level
1050
1051
1052
1053 CURSOR tax_amount_dist ( c_trx_id NUMBER,c_tax_line_id NUMBER, c_tax_dist_id NUMBER, c_ae_header_id NUMBER,
1054 c_event_id NUMBER, c_ledger_id NUMBER) IS
1055 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1056 FROM zx_rec_nrec_dist zx_dist,
1057 xla_distribution_links lnk,
1058 xla_ae_headers aeh,
1059 xla_ae_lines ael
1060 WHERE zx_dist.trx_id = c_trx_id
1061 AND zx_dist.tax_line_id = c_tax_line_id
1062 AND zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
1063 AND lnk.application_id = 200
1064 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
1065 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
1066 AND lnk.ae_header_id = c_ae_header_id
1067 AND lnk.event_id = c_event_id
1068 AND lnk.ae_line_num = ael.ae_line_num
1069 AND aeh.ae_header_id = ael.ae_header_id
1070 AND aeh.ledger_id = c_ledger_id;
1071
1072
1073 CURSOR taxable_amount_dist (c_trx_id NUMBER,c_trx_line_id NUMBER, c_ae_header_id NUMBER,
1074 c_event_id NUMBER, c_ledger_id NUMBER) IS
1075 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1076 FROM zx_rec_nrec_dist zx_dist,
1077 xla_distribution_links lnk,
1078 xla_ae_headers aeh,
1079 xla_ae_lines ael
1080 WHERE zx_dist.trx_id = c_trx_id
1081 AND zx_dist.trx_line_id = c_trx_line_id
1082 -- AND zx_dist.trx_line_dist_id = c_trx_line_dist_id
1083 AND lnk.application_id = 200
1084 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
1085 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
1086 AND lnk.ae_header_id = c_ae_header_id
1087 AND lnk.event_id = c_event_id
1088 AND lnk.ae_line_num = ael.ae_line_num
1089 AND aeh.ae_header_id = ael.ae_header_id
1090 AND aeh.ledger_id = c_ledger_id;
1091
1092
1093
1094 BEGIN
1095
1096 IF (g_level_procedure >= g_current_runtime_level ) THEN
1097 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.get_accounting_amounts.BEGIN',
1098 'get_accounting_amounts(+)');
1099 END IF;
1100
1101 IF p_summary_level = 'TRANSACTION' THEN
1102 OPEN taxable_amount_hdr(p_trx_id , p_ae_header_id , p_event_id, p_ledger_id);
1103 FETCH taxable_amount_hdr INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
1104 -- EXIT WHEN taxable_amount_hdr%NOTFOUND;
1105 CLOSE taxable_amount_hdr;
1106
1107 OPEN tax_amount_hdr(p_trx_id , p_ae_header_id , p_event_id, p_ledger_id);
1108 FETCH tax_amount_hdr INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
1109 -- EXIT WHEN tax_amount_hdr%NOTFOUND;
1110 CLOSE tax_amount_hdr;
1111 ELSIF p_summary_level = 'TRANSACTION_LINE' THEN
1112 OPEN taxable_amount_line(p_trx_id ,p_trx_line_id, p_ae_header_id , p_event_id, p_ledger_id);
1113 FETCH taxable_amount_line INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
1114 -- EXIT WHEN taxable_amount_line%NOTFOUND;
1115 CLOSE taxable_amount_line;
1116
1117 OPEN tax_amount_line(p_trx_id , p_trx_line_id, p_ae_header_id , p_event_id, p_ledger_id);
1118 FETCH tax_amount_line INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
1119 -- EXIT WHEN tax_amount_line%NOTFOUND;
1120 CLOSE tax_amount_line;
1121
1122 ELSIF p_summary_level = 'TRANSACTION_DISTRIBUTION' THEN
1123 OPEN taxable_amount_dist(p_tax_dist_id ,p_trx_line_id,p_ae_header_id , p_event_id, p_ledger_id);
1124 FETCH taxable_amount_dist INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
1125 -- EXIT WHEN taxable_amount_dist%NOTFOUND;
1126 CLOSE taxable_amount_dist;
1127
1128 OPEN tax_amount_dist(p_trx_id,p_tax_line_id, p_tax_dist_id, p_ae_header_id , p_event_id, p_ledger_id);
1129 FETCH tax_amount_dist INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
1130 -- EXIT WHEN tax_amount_dist%NOTFOUND;
1131 CLOSE tax_amount_dist;
1132 END IF;
1133
1134 IF (g_level_procedure >= g_current_runtime_level ) THEN
1135 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.get_accounting_amounts.END',
1136 'get_accounting_amounts(-)');
1137 END IF;
1138
1139 END get_accounting_amounts;
1140
1141
1142 /*PROCEDURE get_discount_info
1143 ( i IN BINARY_INTEGER,
1144 P_TRX_ID IN NUMBER,
1145 -- P_TAX_ID IN NUMBER,
1146 P_SUMMARY_LEVEL IN VARCHAR2,
1147 P_DIST_ID IN NUMBER,
1148 P_TRX_LINE_ID IN NUMBER,
1149 P_DISC_DISTRIBUTION_METHOD IN VARCHAR2,
1150 P_LIABILITY_POST_LOOKUP_CODE IN VARCHAR2
1151 )
1152 */
1153 PROCEDURE get_discount_info
1154 ( j IN BINARY_INTEGER,
1155 P_TRX_ID IN NUMBER,
1156 P_TAX_LINE_ID IN NUMBER,
1157 P_SUMMARY_LEVEL IN VARCHAR2,
1158 P_DIST_ID IN NUMBER,
1159 P_TRX_LINE_ID IN NUMBER,
1160 P_DISC_DISTRIBUTION_METHOD IN VARCHAR2,
1161 P_LIABILITY_POST_LOOKUP_CODE IN VARCHAR2
1162 )
1163 IS
1164
1165 CURSOR taxable_hdr_csr IS
1166 SELECT sum(aphd.amount), -- discount amount (entered)
1167 sum(aphd.paid_base_amount) -- discount amount (accounted)
1168 FROM ap_invoice_distributions_all aid,
1169 ap_invoices_all ai,
1170 ap_invoice_payments_all aip,
1171 ap_payment_hist_dists aphd,
1172 ap_payment_history_all aph
1173 WHERE aid.invoice_id = ai.invoice_id
1174 AND aid.invoice_id = aip.invoice_id
1175 AND aid.distribution_line_number
1176 IN (SELECT distribution_line_number
1177 FROM ap_invoice_distributions_all
1178 WHERE invoice_id = p_trx_id
1179 AND line_type_lookup_code = 'ITEM')
1180 AND aip.invoice_payment_id = aphd.invoice_payment_id
1181 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
1182 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
1183 AND nvl(aph.historical_flag, 'N') = 'N'
1184 AND aph.check_id = aip.check_id
1185 UNION
1186 SELECT xal.entered_dr - xal.entered_cr ,
1187 -- discount entered amount (replace this with new xla colum names)
1188 xal.accounted_dr -xal.entered_cr
1189 -- discount entered amount (replace this with new xla colum names)
1190 FROM ap_invoice_distributions_all aid,
1191 ap_invoices_all ai,
1192 ap_invoice_payments_all aip,
1193 ap_payment_history_all aph,
1194 xla_ae_lines xal
1195 WHERE aid.invoice_id = ai.invoice_id
1196 AND aid.invoice_id = aip.invoice_id
1197 AND aid.distribution_line_number
1198 IN (SELECT distribution_line_number
1199 FROM ap_invoice_distributions_all
1200 WHERE invoice_id = p_trx_id
1201 AND line_type_lookup_code = 'ITEM')
1202 AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
1203 AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
1204 AND xal.accounting_class_code = 'DISCOUNT'
1205 AND aph.check_id = aip.check_id
1206 AND nvl(aph.historical_flag, 'N') = 'Y';
1207
1208 CURSOR tax_hdr_csr IS
1209 SELECT sum(aphd.amount), -- discount amount (entered)
1210 sum(aphd.paid_base_amount) -- discount amount (accounted)
1211 FROM ap_invoice_distributions_all aid,
1212 ap_invoices_all ai,
1213 ap_invoice_payments_all aip,
1214 ap_payment_hist_dists aphd,
1215 ap_payment_history_all aph
1216 WHERE aid.invoice_id = ai.invoice_id
1217 AND aid.invoice_id = aip.invoice_id
1218 AND aid.distribution_line_number
1219 IN (SELECT distribution_line_number
1220 FROM ap_invoice_distributions_all
1221 WHERE invoice_id = p_trx_id
1222 AND line_type_lookup_code = 'TAX')
1223 AND aip.invoice_payment_id = aphd.invoice_payment_id
1224 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
1225 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
1226 AND nvl(aph.historical_flag, 'N') = 'N'
1227 AND aph.check_id = aip.check_id
1228 UNION
1229 SELECT xal.entered_dr - xal.entered_cr ,
1230 -- discount entered amount (replace this with new xla colum names)
1231 xal.accounted_dr -xal.entered_cr
1232 -- discount entered amount (replace this with new xla colum names)
1233 FROM ap_invoice_distributions_all aid,
1234 ap_invoices_all ai,
1235 ap_invoice_payments_all aip,
1236 ap_payment_history_all aph,
1237 xla_ae_lines xal
1238 WHERE aid.invoice_id = ai.invoice_id
1239 AND aid.invoice_id = aip.invoice_id
1240 AND aid.distribution_line_number
1241 IN (SELECT distribution_line_number
1242 FROM ap_invoice_distributions_all
1243 WHERE invoice_id = p_trx_id
1244 AND line_type_lookup_code = 'TAX')
1245 AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
1246 AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
1247 AND xal.accounting_class_code = 'DISCOUNT'
1248 AND aph.check_id = aip.check_id
1249 AND nvl(aph.historical_flag, 'N') = 'Y';
1250
1251 CURSOR taxable_line_csr IS
1252 SELECT sum(aphd.amount), -- discount amount (entered)
1253 sum(aphd.paid_base_amount) -- discount amount (accounted)
1254 FROM ap_invoice_distributions_all aid,
1255 ap_invoices_all ai,
1256 ap_invoice_payments_all aip,
1257 ap_payment_hist_dists aphd,
1258 ap_payment_history_all aph
1259 WHERE aid.invoice_id = ai.invoice_id
1260 AND aid.invoice_id = aip.invoice_id
1261 -- AND aid.distribution_line_number
1262 AND aid.invoice_distribution_id = p_trx_line_id
1263 AND aip.invoice_payment_id = aphd.invoice_payment_id
1264 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
1265 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
1266 AND nvl(aph.historical_flag, 'N') = 'N'
1267 AND aph.check_id = aip.check_id
1268 UNION
1269 SELECT xal.entered_dr - xal.entered_cr ,
1270 -- discount entered amount (replace this with new xla colum names)
1271 xal.accounted_dr -xal.entered_cr
1272 -- discount entered amount (replace this with new xla colum names)
1273 FROM ap_invoice_distributions_all aid,
1274 ap_invoices_all ai,
1275 ap_invoice_payments_all aip,
1276 ap_payment_history_all aph,
1277 xla_ae_lines xal
1278 WHERE aid.invoice_id = ai.invoice_id
1279 AND aid.invoice_id = aip.invoice_id
1280 -- AND aid.distribution_line_number
1281 AND aid.invoice_distribution_id = p_trx_line_id
1282 AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
1283 AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
1284 AND xal.accounting_class_code = 'DISCOUNT'
1285 AND aph.check_id = aip.check_id
1286 AND nvl(aph.historical_flag, 'N') = 'Y';
1287
1288
1289 CURSOR tax_line_csr IS
1290 SELECT sum(aphd.amount), -- discount amount (entered)
1291 sum(aphd.paid_base_amount) -- discount amount (accounted)
1292 FROM ap_invoice_distributions_all aid,
1293 ap_invoices_all ai,
1294 ap_invoice_payments_all aip,
1295 ap_payment_hist_dists aphd,
1296 ap_payment_history_all aph
1297 WHERE aid.invoice_id = ai.invoice_id
1298 AND aid.invoice_id = aip.invoice_id
1299 -- AND aid.distribution_line_number
1300 AND aid.invoice_distribution_id = p_tax_line_id
1301 AND aip.invoice_payment_id = aphd.invoice_payment_id
1302 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
1303 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
1304 AND nvl(aph.historical_flag, 'N') = 'N'
1305 AND aph.check_id = aip.check_id
1306 UNION
1307 SELECT xal.entered_dr - xal.entered_cr ,
1308 -- discount entered amount (replace this with new xla colum names)
1309 xal.accounted_dr -xal.entered_cr
1310 -- discount entered amount (replace this with new xla colum names)
1311 FROM ap_invoice_distributions_all aid,
1312 ap_invoices_all ai,
1313 ap_invoice_payments_all aip,
1314 ap_payment_history_all aph,
1315 xla_ae_lines xal
1316 WHERE aid.invoice_id = ai.invoice_id
1317 AND aid.invoice_id = aip.invoice_id
1318 -- AND aid.distribution_line_number
1319 AND aid.invoice_distribution_id = p_tax_line_id
1320 AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
1321 AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
1322 AND xal.accounting_class_code = 'DISCOUNT'
1323 AND aph.check_id = aip.check_id
1324 AND nvl(aph.historical_flag, 'N') = 'Y';
1325
1326
1327 CURSOR taxable_dist_csr IS
1328 SELECT sum(aphd.amount), -- discount amount (entered)
1329 sum(aphd.paid_base_amount) -- discount amount (accounted)
1330 FROM ap_invoice_distributions_all aid,
1331 ap_invoices_all ai,
1332 ap_invoice_payments_all aip,
1333 ap_payment_hist_dists aphd,
1334 ap_payment_history_all aph
1335 WHERE aid.invoice_id = ai.invoice_id
1336 AND aid.invoice_id = aip.invoice_id
1337 -- AND aid.distribution_line_number
1338 AND aid.invoice_distribution_id = p_trx_line_id
1339 AND aip.invoice_payment_id = aphd.invoice_payment_id
1340 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
1341 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
1342 AND nvl(aph.historical_flag, 'N') = 'N'
1343 AND aph.check_id = aip.check_id
1344 UNION
1345 SELECT xal.entered_dr - xal.entered_cr ,
1346 -- discount entered amount (replace this with new xla colum names)
1347 xal.accounted_dr -xal.entered_cr
1348 -- discount entered amount (replace this with new xla colum names)
1349 FROM ap_invoice_distributions_all aid,
1350 ap_invoices_all ai,
1351 ap_invoice_payments_all aip,
1352 ap_payment_history_all aph,
1353 xla_ae_lines xal
1354 WHERE aid.invoice_id = ai.invoice_id
1355 AND aid.invoice_id = aip.invoice_id
1356 -- AND aid.distribution_line_number
1357 AND aid.invoice_distribution_id = p_trx_line_id
1358 AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
1359 AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
1360 AND xal.accounting_class_code = 'DISCOUNT'
1361 AND aph.check_id = aip.check_id
1362 AND nvl(aph.historical_flag, 'N') = 'Y';
1363
1364
1365 CURSOR tax_dist_csr IS
1366 SELECT sum(aphd.amount), -- discount amount (entered)
1367 sum(aphd.paid_base_amount) -- discount amount (accounted)
1368 FROM ap_invoice_distributions_all aid,
1369 ap_invoices_all ai,
1370 ap_invoice_payments_all aip,
1371 ap_payment_hist_dists aphd,
1372 ap_payment_history_all aph
1373 WHERE aid.invoice_id = ai.invoice_id
1374 AND aid.invoice_id = aip.invoice_id
1375 -- AND aid.distribution_line_number
1376 AND aid.invoice_distribution_id = p_tax_line_id
1377 AND aip.invoice_payment_id = aphd.invoice_payment_id
1378 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
1379 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
1380 AND nvl(aph.historical_flag, 'N') = 'N'
1381 AND aph.check_id = aip.check_id
1382 UNION
1383 SELECT xal.entered_dr - xal.entered_cr ,
1384 -- discount entered amount (replace this with new xla colum names)
1385 xal.accounted_dr -xal.entered_cr
1386 -- discount entered amount (replace this with new xla colum names)
1387 FROM ap_invoice_distributions_all aid,
1388 ap_invoices_all ai,
1389 ap_invoice_payments_all aip,
1390 ap_payment_history_all aph,
1391 xla_ae_lines xal
1392 WHERE aid.invoice_id = ai.invoice_id
1393 AND aid.invoice_id = aip.invoice_id
1394 -- AND aid.distribution_line_number
1395 AND aid.invoice_distribution_id = p_tax_line_id
1396 AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
1397 AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
1398 AND xal.accounting_class_code = 'DISCOUNT'
1399 AND aph.check_id = aip.check_id
1400 AND nvl(aph.historical_flag, 'N') = 'Y';
1401
1402
1403
1404 l_tax_entered_disc_amt NUMBER;
1405 l_tax_acct_disc_amt NUMBER;
1406 -- l_tax1_entered_disc_amt NUMBER;
1407 -- l_tax1_accounted_disc_amt NUMBER;
1408 -- l_tax2_entered_disc_amt NUMBER;
1409 -- l_tax2_accounted_disc_amt:= 0;
1410 -- l_tax3_entered_disc_amt:= 0;
1411 -- l_tax3_accounted_disc_amt:= 0;
1412 -- l_tax4_entered_disc_amt:= 0;
1413 -- l_tax4_accounted_disc_amt:= 0;
1414 l_0_taxable_entered_disc_amt NUMBER;
1415 l_0_taxable_accounted_disc_amt NUMBER;
1416 l_taxable_entered_disc_amt NUMBER;
1417 l_taxable_acct_disc_amt NUMBER;
1418 i BINARY_INTEGER;
1419
1420 BEGIN
1421
1422 /* IF PG_DEBUG = 'Y' THEN
1423 arp_util_tax.debug('AP_TAX_POPULATE.get_discount_info_ap(+) ');
1424 arp_util_tax.debug('P_TRX_ID = ' || P_TRX_ID);
1425 arp_util_tax.debug('P_TAX_ID = ' || P_TAX_ID);
1426 arp_util_tax.debug('P_SUMMARY_LEVEL = ' || P_SUMMARY_LEVEL);
1427 arp_util_tax.debug('P_DISC_DISTRIBUTION_METHOD = ' || P_DISC_DISTRIBUTION_METHOD);
1428 arp_util_tax.debug('P_LIABILITY_POST_LOOKUP_CODE = ' || P_LIABILITY_POST_LOOKUP_CODE);
1429 END IF;
1430 */
1431 i := j;
1432 --P_INDEX_TO_GLOBAL_TABLES;
1433 --l_tax_type := P_SUB_ITF_REC.tax_code_type_code;
1434
1435 -- get discount tax amount;
1436
1437
1438 l_tax_entered_disc_amt:= 0;
1439 l_tax_acct_disc_amt:= 0;
1440 -- l_tax1_entered_disc_amt:= 0;
1441 -- l_tax1_accounted_disc_amt:= 0;
1442 -- l_tax2_entered_disc_amt:= 0;
1443 -- l_tax2_accounted_disc_amt:= 0;
1444 -- l_tax3_entered_disc_amt:= 0;
1445 -- l_tax3_accounted_disc_amt:= 0;
1446 -- l_tax4_entered_disc_amt:= 0;
1447 -- l_tax4_accounted_disc_amt:= 0;
1448 l_0_taxable_entered_disc_amt:= 0;
1449 l_0_taxable_accounted_disc_amt:= 0;
1450 l_taxable_entered_disc_amt:= 0;
1451 l_taxable_acct_disc_amt:= 0;
1452
1453
1454 IF P_DISC_DISTRIBUTION_METHOD = 'EXPENSE' OR
1455 P_LIABILITY_POST_LOOKUP_CODE IS NOT NULL THEN
1456
1457 IF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
1458
1459
1460 OPEN taxable_dist_csr;
1461 --(p_trx_line_id);
1462 FETCH taxable_line_csr INTO l_taxable_entered_disc_amt, l_taxable_acct_disc_amt;
1463
1464 IF taxable_dist_csr%NOTFOUND THEN
1465 -- Message
1466 NULL;
1467 END IF; -- tax_discount_cur
1468
1469
1470 CLOSE taxable_dist_csr;
1471
1472 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
1473
1474 OPEN taxable_line_csr;
1475 --(p_trx_line_id);
1476 FETCH taxable_line_csr INTO l_taxable_entered_disc_amt, l_taxable_acct_disc_amt;
1477
1478 IF taxable_line_csr%NOTFOUND THEN
1479 NULL;
1480 -- Message
1481 END IF; -- tax_discount_cur
1482
1483 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION' THEN
1484
1485 OPEN taxable_hdr_csr;
1486 --(p_trx_line_id);
1487 FETCH taxable_hdr_csr INTO l_taxable_entered_disc_amt, l_taxable_acct_disc_amt;
1488
1489 IF taxable_hdr_csr%NOTFOUND THEN
1490 NULL;
1491 -- Message
1492 END IF; -- tax_discount_cur
1493
1494 END IF; --summary level
1495
1496
1497 -- GT_TAXABLE_ENT_DISC_AMT_TBL(i):= l_taxable_entered_disc_amt;
1498 -- G_TAXABLE_ACCT_DISC_AMT_TBL(i):= l_taxable_acct_disc_amt;
1499
1500 GT_TAXABLE_DISC_AMT(i) := l_taxable_entered_disc_amt;
1501 GT_TAXABLE_DISC_AMT_FUNCL_CURR(i) := l_taxable_acct_disc_amt;
1502
1503 ELSE -- P_DISC_DISTRIBUTION_METHOD = 'TAX' AND P_LIABILITY_POST_LOOKUP_CODE IS NULL
1504 NULL;
1505 -- arp_util_tax.debug('Taxable discount amount are stored in one account in accounting table ...');
1506 END IF;
1507
1508 IF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
1509
1510
1511 OPEN tax_dist_csr;
1512 --(p_trx_line_id);
1513 FETCH tax_line_csr INTO l_tax_entered_disc_amt, l_tax_acct_disc_amt;
1514
1515 IF tax_dist_csr%NOTFOUND THEN
1516 -- Message
1517 NULL;
1518 END IF; -- tax_discount_cur
1519
1520
1521 CLOSE tax_dist_csr;
1522
1523 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
1524
1525 OPEN tax_line_csr;
1526 --(p_trx_line_id);
1527 FETCH tax_line_csr INTO l_tax_entered_disc_amt, l_tax_acct_disc_amt;
1528
1529 IF tax_line_csr%NOTFOUND THEN
1530 NULL; -- Message
1531 END IF; -- tax_discount_cur
1532
1533 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION' THEN
1534
1535 OPEN tax_hdr_csr;
1536 --(p_trx_line_id);
1537 FETCH tax_hdr_csr INTO l_tax_entered_disc_amt, l_tax_acct_disc_amt;
1538
1539 IF tax_hdr_csr%NOTFOUND THEN
1540 NULL; -- Message
1541 END IF; -- tax_discount_cur
1542
1543 END IF; --summary level
1544
1545
1546 GT_TAX_DISC_AMT(i) := l_tax_entered_disc_amt;
1547 GT_TAX_DISC_AMT_FUNCL_CURR(i) := l_tax_acct_disc_amt;
1548
1549 EXCEPTION
1550
1551 WHEN NO_DATA_FOUND THEN
1552 -- arp_util_tax.debug('Exception No data found exception in GET_DISCOUNT_INFO_AP..'||
1553 -- SQLCODE||' ; '||SQLERRM);
1554 NULL;
1555
1556 WHEN OTHERS THEN
1557
1558 --arp_util_tax.debug('When others Exception in GET_DISCOUNT_INFO_AP..'||
1559 -- SQLCODE||' ; '||SQLERRM);
1560 NULL;
1561
1562
1563 END get_discount_info;
1564
1565 PROCEDURE update_zx_rep_detail_t(
1566 P_COUNT IN BINARY_INTEGER)
1567 IS
1568
1569 BEGIN
1570
1571 IF (g_level_procedure >= g_current_runtime_level ) THEN
1572 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.update_zx_rep_detail_t.BEGIN',
1573 'update_zx_rep_detail_t(+)');
1574 END IF;
1575
1576 FORALL i IN 1 .. p_count
1577 UPDATE zx_rep_trx_detail_t SET
1578 REP_CONTEXT_ID = G_REP_CONTEXT_ID,
1579 BILLING_TP_NUMBER = GT_BILLING_TP_NUMBER(i),
1580 BILLING_TP_TAX_REG_NUM = GT_BILLING_TP_TAX_REG_NUM(i),
1581 BILLING_TP_TAXPAYER_ID = GT_BILLING_TP_TAXPAYER_ID(i),
1582 BILLING_TP_SITE_NAME_ALT = GT_BILLING_TP_SITE_NAME_ALT(i),
1583 BILLING_TP_NAME = GT_BILLING_TP_NAME(i),
1584 BILLING_TP_NAME_ALT = GT_BILLING_TP_NAME_ALT(i),
1585 BILLING_TP_SIC_CODE = GT_BILLING_TP_SIC_CODE(i),
1586 BILLING_TP_CITY = GT_BILLING_TP_CITY(i),
1587 BILLING_TP_COUNTY = GT_BILLING_TP_COUNTY(i),
1588 BILLING_TP_STATE = GT_BILLING_TP_STATE(i),
1589 BILLING_TP_PROVINCE = GT_BILLING_TP_PROVINCE(i),
1590 BILLING_TP_ADDRESS1 = GT_BILLING_TP_ADDRESS1(i),
1591 BILLING_TP_ADDRESS2 = GT_BILLING_TP_ADDRESS2(i),
1592 BILLING_TP_ADDRESS3 = GT_BILLING_TP_ADDRESS3(i),
1593 BILLING_TP_ADDRESS_LINES_ALT = GT_BILLING_TP_ADDR_LINES_ALT(i),
1594 BILLING_TP_COUNTRY = GT_BILLING_TP_COUNTRY(i),
1595 BILLING_TP_POSTAL_CODE = GT_BILLING_TP_POSTAL_CODE(i),
1596 GDF_PO_VENDOR_SITE_ATT17 = GT_GDF_PO_VENDOR_SITE_ATT17(i),
1597 TRX_CLASS_MNG = GT_TRX_CLASS_MNG(i),
1598 TAX_RATE_CODE_REG_TYPE_MNG = GT_TAX_RATE_CODE_REG_TYPE_MNG(i),
1599 TAXABLE_DISC_AMT = GT_TAXABLE_DISC_AMT(i),
1600 TAXABLE_DISC_AMT_FUNCL_CURR = GT_TAXABLE_DISC_AMT_FUNCL_CURR(i),
1601 TAX_DISC_AMT = GT_TAX_DISC_AMT(i),
1602 TAX_DISC_AMT_FUNCL_CURR = GT_TAX_DISC_AMT_FUNCL_CURR(i)
1603 WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
1604
1605 IF (g_level_procedure >= g_current_runtime_level ) THEN
1606 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.update_zx_rep_detail_t.END',
1607 'update_zx_rep_detail_t(-)');
1608 END IF;
1609
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1613 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1614 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1615 FND_MSG_PUB.Add;
1616 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1617 FND_LOG.STRING(g_level_unexpected,
1618 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.update_zx_rep_detail_t',
1619 g_error_buffer);
1620 END IF;
1621 g_retcode := 2;
1622
1623 END update_zx_rep_detail_t;
1624
1625 PROCEDURE update_rep_actg_t(p_count IN NUMBER) IS
1626 i number;
1627 BEGIN
1628
1629 IF (g_level_procedure >= g_current_runtime_level ) THEN
1630 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG_T.BEGIN',
1631 'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_ACTG_T(+)');
1632 END IF;
1633
1634 FORALL i in 1..p_count
1635 UPDATE zx_rep_actg_ext_t SET
1636 TRX_ARAP_BALANCING_SEGMENT = GT_TRX_ARAP_BALANCING_SEGMENT(i),
1637 TRX_ARAP_NATURAL_ACCOUNT = GT_TRX_ARAP_NATURAL_ACCOUNT(i),
1638 TRX_TAXABLE_BALANCING_SEGMENT = GT_TRX_TAXABLE_BAL_SEG(i),
1639 TRX_TAXABLE_NATURAL_ACCOUNT = GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),
1640 TRX_TAX_BALANCING_SEGMENT = GT_TRX_TAX_BALANCING_SEGMENT(i),
1641 TRX_TAX_NATURAL_ACCOUNT = GT_TRX_TAX_NATURAL_ACCOUNT(i)
1642 WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
1643
1644 IF (g_level_procedure >= g_current_runtime_level ) THEN
1645 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG__T.END',
1646 'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_ACTG_T(-)');
1647 END IF;
1648
1649
1650 EXCEPTION
1651 WHEN OTHERS THEN
1652 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1653 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1654 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1655 FND_MSG_PUB.Add;
1656 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1657 FND_LOG.STRING(g_level_unexpected,
1658 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG_T',
1659 g_error_buffer);
1660 END IF;
1661
1662 G_RETCODE := 2;
1663
1664 END UPDATE_REP_ACTG_T;
1665
1666
1667 PROCEDURE initialize_variables (
1668 p_count IN NUMBER) IS
1669 i number;
1670
1671 BEGIN
1672
1673 IF (g_level_procedure >= g_current_runtime_level ) THEN
1674 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.initialize_variables.BEGIN',
1675 'initialize_variables(+)');
1676 END IF;
1677
1678 FOR i IN 1.. p_count LOOP
1679 /*
1680 GT_DETAIL_TAX_LINE_ID(i) := NULL;
1681 GT_APPLICATION_ID(i) := NULL;
1682 GT_INTERNAL_ORGANIZATION_ID(i) := NULL;
1683 GT_TRX_ID(i) := NULL;
1684 GT_TRX_LINE_TYPE(i) := NULL;
1685 GT_TRX_LINE_CLASS(i) := NULL;
1686 GT_SHIP_TO_PARTY_TAX_PROF_ID(i) := NULL;
1687 GT_SHIP_FROM_PTY_TAX_PROF_ID(i) := NULL;
1688 GT_BILL_TO_PARTY_TAX_PROF_ID(i) := NULL;
1689 GT_BILL_FROM_PTY_TAX_PROF_ID(i) := NULL;
1690 GT_SHIP_TO_SITE_TAX_PROF_ID(i) := NULL;
1691 GT_BILL_TO_SITE_TAX_PROF_ID(i) := NULL;
1692 GT_SHIP_FROM_SITE_TAX_PROF_ID(i) := NULL;
1693 GT_BILL_FROM_SITE_TAX_PROF_ID(i) := NULL;
1694 GT_BILL_FROM_PARTY_ID(i) := NULL;
1695 GT_BILL_FROM_PARTY_SITE_ID(i) := NULL;
1696 GT_HISTORICAL_FLAG(i) := NULL;
1697 GT_REP_CONTEXT_ID(i) := NULL;
1698 GT_TRX_CLASS_MNG(i) := NULL;
1699 GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := NULL;
1700 */
1701 -- apai GT_REP_CONTEXT_ID(i) := NULL;
1702 GT_BILLING_TP_NUMBER(i) := NULL;
1703 GT_BILLING_TP_TAX_REG_NUM(i) := NULL;
1704 GT_BILLING_TP_TAXPAYER_ID(i) := NULL;
1705 GT_BILLING_TP_SITE_NAME_ALT(i) := NULL;
1706 GT_BILLING_TP_NAME(i) := NULL;
1707 GT_BILLING_TP_NAME_ALT(i) := NULL;
1708 GT_BILLING_TP_SIC_CODE(i) := NULL;
1709 GT_BILLING_TP_CITY(i) := NULL;
1710 GT_BILLING_TP_COUNTY(i) := NULL;
1711 GT_BILLING_TP_STATE(i) := NULL;
1712 GT_BILLING_TP_PROVINCE(i) := NULL;
1713 GT_BILLING_TP_ADDRESS1(i) := NULL;
1714 GT_BILLING_TP_ADDRESS2(i) := NULL;
1715 GT_BILLING_TP_ADDRESS3(i) := NULL;
1716 GT_BILLING_TP_ADDR_LINES_ALT(i) := NULL;
1717 GT_BILLING_TP_COUNTRY(i) := NULL;
1718 GT_BILLING_TP_POSTAL_CODE(i) := NULL;
1719 GT_GDF_PO_VENDOR_SITE_ATT17(i) := NULL;
1720 GT_TRX_CLASS_MNG(i) := NULL;
1721 GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := NULL;
1722 GT_TAXABLE_DISC_AMT(i) := NULL;
1723 GT_TAXABLE_DISC_AMT_FUNCL_CURR(i) := NULL;
1724 GT_TAX_DISC_AMT(i) := NULL;
1725 GT_TAX_DISC_AMT_FUNCL_CURR(i) := NULL;
1726 END LOOP;
1727
1728 IF (g_level_procedure >= g_current_runtime_level ) THEN
1729 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.initialize_variables.END',
1730 'initialize_variables(-)');
1731 END IF;
1732
1733
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1737 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1738 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1739 FND_MSG_PUB.Add;
1740 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1741 FND_LOG.STRING(g_level_unexpected,
1742 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.initialize_variables',
1743 g_error_buffer);
1744 END IF;
1745 g_retcode := 2;
1746
1747 END initialize_variables ;
1748
1749
1750 PROCEDURE populate_meaning(
1751 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
1752 i BINARY_INTEGER)
1753 IS
1754 l_description VARCHAR2(240);
1755 l_meaning VARCHAR2(80);
1756 BEGIN
1757
1758 IF (g_level_procedure >= g_current_runtime_level ) THEN
1759 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.populate_meaning.BEGIN',
1760 'populate_meaning(+)');
1761 END IF;
1762
1763 IF GT_TRX_LINE_CLASS(i) IS NOT NULL THEN
1764 lookup_desc_meaning('ZX_TRANSACTION_CLASS_TYPE',
1765 GT_TRX_LINE_CLASS(i),
1766 l_meaning,
1767 l_description);
1768 GT_TRX_CLASS_MNG(i) := l_meaning;
1769 END IF;
1770
1771 IF P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE IS NOT NULL THEN
1772 lookup_desc_meaning('ZX_REGISTER_TYPE',
1773 P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE,
1774 l_meaning,
1775 l_description);
1776
1777 GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := l_meaning;
1778 END IF;
1779
1780 IF (g_level_procedure >= g_current_runtime_level ) THEN
1781 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.populate_meaning.END',
1782 'populate_meaning(-)' ||GT_TAX_RATE_CODE_REG_TYPE_MNG(i));
1783 END IF;
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1787 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1788 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_meaning- '|| g_error_buffer);
1789 FND_MSG_PUB.Add;
1790 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1791 FND_LOG.STRING(g_level_unexpected,
1792 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.populate_meaning',
1793 g_error_buffer);
1794 END IF;
1795
1796
1797 END populate_meaning;
1798
1799
1800
1801 PROCEDURE lookup_desc_meaning(p_lookup_type IN VARCHAR2,
1802 P_LOOKUP_CODE IN VARCHAR2,
1803 p_meaning OUT NOCOPY VARCHAR2,
1804 p_description OUT NOCOPY VARCHAR2) IS
1805
1806 CURSOR lookup_cur (c_lookup_type VARCHAR2,
1807 c_lookup_code VARCHAR2) IS
1808 SELECT meaning, description
1809 FROM fnd_lookups
1810 WHERE lookup_type = c_lookup_type
1811 AND lookup_code = c_lookup_code;
1812
1813 l_tbl_index_lookup BINARY_INTEGER;
1814 BEGIN
1815
1816 IF (g_level_procedure >= g_current_runtime_level ) THEN
1817 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.lookup_desc_meaning.BEGIN',
1818 'lookup_desc_meaning(+)');
1819 END IF;
1820
1821 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL THEN
1822 IF (g_level_procedure >= g_current_runtime_level ) THEN
1823 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.lookup_desc_meaning',
1824 'Lookup Type and Lookup code are not null '||p_lookup_type||'-'||P_LOOKUP_CODE);
1825 END IF;
1826
1827 l_tbl_index_lookup := dbms_utility.get_hash_value(p_lookup_type||p_lookup_code, 1,8192);
1828
1829 IF (g_level_procedure >= g_current_runtime_level ) THEN
1830 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.lookup_desc_meaning',
1831 'Meaning Alredy existed in the Cache');
1832 END IF;
1833
1834 IF g_lookup_info_tbl.EXISTS(l_tbl_index_lookup) THEN
1835
1836 p_meaning := g_lookup_info_tbl(l_tbl_index_lookup).lookup_meaning;
1837 p_description := g_lookup_info_tbl(l_tbl_index_lookup).lookup_description;
1838
1839 ELSE
1840
1841 IF (g_level_procedure >= g_current_runtime_level ) THEN
1842 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.lookup_desc_meaning',
1843 'Before Open lookup_cur');
1844 END IF;
1845
1846 OPEN lookup_cur (p_lookup_type, p_lookup_code);
1847 FETCH lookup_cur
1848 INTO p_meaning,
1849 p_description;
1850
1851 IF (g_level_procedure >= g_current_runtime_level ) THEN
1852 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.lookup_desc_meaning',
1853 'p_meaning p_description'||p_meaning||' '||p_description);
1854 END IF;
1855
1856 g_lookup_info_tbl(l_tbl_index_lookup).lookup_meaning := p_meaning;
1857 g_lookup_info_tbl(l_tbl_index_lookup).lookup_description := p_description;
1858 END IF;
1859 END IF;
1860
1861 IF lookup_cur%ISOPEN THEN
1862 CLOSE lookup_cur;
1863 END IF;
1864 IF (g_level_procedure >= g_current_runtime_level ) THEN
1865 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.lookup_desc_meaning.END',
1866 'lookup_desc_meaning(-)');
1867 END IF;
1868
1869 EXCEPTION
1870 WHEN OTHERS THEN
1871 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1872 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1873 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1874 FND_MSG_PUB.Add;
1875 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1876 FND_LOG.STRING(g_level_unexpected,
1877 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.lookup_desc_meaning',
1878 g_error_buffer);
1879 END IF;
1880 g_retcode := 2;
1881
1882 END lookup_desc_meaning;
1883
1884 END ZX_AP_ACTG_POPULATE_PKG;