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