[Home] [Help]
PACKAGE BODY: APPS.ZX_AR_ACTG_POPULATE_PKG
Source
1 PACKAGE BODY ZX_AR_ACTG_POPULATE_PKG AS
2 /* $Header: zxriractgpoppvtb.pls 120.7 2006/02/08 14:34:09 rjreddy ship $ */
3
4
5 --Populate party info into global variables
6 GT_BILLING_TP_NUMBER ZX_EXTRACT_PKG.BILLING_TP_NUMBER_TBL;
7 GT_BILLING_TP_TAX_REG_NUM ZX_EXTRACT_PKG.BILLING_TP_TAX_REG_NUM_TBL;
8 GT_BILLING_TP_TAXPAYER_ID ZX_EXTRACT_PKG.BILLING_TP_TAXPAYER_ID_TBL;
9 GT_BILLING_TP_SITE_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_SITE_NAME_ALT_TBL;
10 GT_BILLING_TP_NAME ZX_EXTRACT_PKG.BILLING_TP_NAME_TBL;
11 GT_BILLING_TP_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_NAME_ALT_TBL;
15 GT_BILLING_TP_STATE ZX_EXTRACT_PKG.BILLING_TP_STATE_TBL;
12 GT_BILLING_TP_SIC_CODE ZX_EXTRACT_PKG.BILLING_TP_SIC_CODE_TBL;
13 GT_BILLING_TP_CITY ZX_EXTRACT_PKG.BILLING_TP_CITY_TBL;
14 GT_BILLING_TP_COUNTY ZX_EXTRACT_PKG.BILLING_TP_COUNTY_TBL;
16 GT_BILLING_TP_PROVINCE ZX_EXTRACT_PKG.BILLING_TP_PROVINCE_TBL;
17 GT_BILLING_TP_ADDRESS1 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS1_TBL;
18 GT_BILLING_TP_ADDRESS2 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS2_TBL;
19 GT_BILLING_TP_ADDRESS3 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS3_TBL;
20 GT_BILLING_TP_ADDR_LINES_ALT ZX_EXTRACT_PKG.BILLING_TP_ADDR_LINES_ALT_TBL;
21 GT_BILLING_TP_COUNTRY ZX_EXTRACT_PKG.BILLING_TP_COUNTRY_TBL;
22 GT_BILLING_TP_POSTAL_CODE ZX_EXTRACT_PKG.BILLING_TP_POSTAL_CODE_TBL;
23 GT_BILLING_TP_PARTY_NUMBER ZX_EXTRACT_PKG.BILLING_TP_PARTY_NUMBER_TBL;
24 GT_BILLING_TP_ID ZX_EXTRACT_PKG.BILLING_TP_ID_TBL;
25 GT_BILLING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
26 GT_BILLING_TP_ADDRESS_ID ZX_EXTRACT_PKG.BILLING_TP_ADDRESS_ID_TBL;
27 -- GT_SHIPPING_TP_ID ZX_EXTRACT_PKG.BILLING_TP_ID_TBL;
28 -- GT_SHIPPING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
29 -- GT_SHIPPING_TP_ADDRESS_ID ZX_EXTRACT_PKG.BILLING_TP_ADDRESS_ID_TBL;
30 GT_BILLING_TP_TAX_REP_FLAG ZX_EXTRACT_PKG.BILLING_TP_TAX_REP_FLAG_TBL;
31 GT_BILLING_TP_SITE_NAME ZX_EXTRACT_PKG.BILLING_TP_SITE_NAME_TBL;
32 GT_GDF_RA_ADDRESSES_BILL_ATT9 ZX_EXTRACT_PKG.GDF_RA_ADDRESSES_BILL_ATT9_TBL;
33 GT_GDF_PARTY_SITES_BILL_ATT8 ZX_EXTRACT_PKG.GDF_PARTY_SITES_BILL_ATT8_TBL;
34 GT_GDF_RA_CUST_BILL_ATT10 ZX_EXTRACT_PKG.GDF_RA_CUST_BILL_ATT10_TBL;
35 GT_GDF_RA_CUST_BILL_ATT12 ZX_EXTRACT_PKG.GDF_RA_CUST_BILL_ATT12_TBL;
36 GT_GDF_RA_ADDRESSES_BILL_ATT8 ZX_EXTRACT_PKG.GDF_RA_ADDRESSES_BILL_ATT8_TBL;
37
38 GT_SHIPPING_TP_NUMBER ZX_EXTRACT_PKG.SHIPPING_TP_NUMBER_TBL;
39 GT_SHIPPING_TP_TAX_REG_NUM ZX_EXTRACT_PKG.SHIPPING_TP_TAX_REG_NUM_TBL;
40 GT_SHIPPING_TP_TAXPAYER_ID ZX_EXTRACT_PKG.SHIPPING_TP_TAXPAYER_ID_TBL;
41 -- GT_SHIPPING_TP_SITE_NAME_ALT ZX_EXTRACT_PKG.SHIPPING_TP_SITE_NAME_ALT_TBL;
42 GT_SHIPPING_TP_NAME ZX_EXTRACT_PKG.SHIPPING_TP_NAME_TBL;
43 GT_SHIPPING_TP_NAME_ALT ZX_EXTRACT_PKG.SHIPPING_TP_NAME_ALT_TBL;
44 GT_SHIPPING_TP_SIC_CODE ZX_EXTRACT_PKG.SHIPPING_TP_SIC_CODE_TBL;
45 GT_SHIPPING_TP_CITY ZX_EXTRACT_PKG.SHIPPING_TP_CITY_TBL;
46 GT_SHIPPING_TP_COUNTY ZX_EXTRACT_PKG.SHIPPING_TP_COUNTY_TBL;
47 GT_SHIPPING_TP_STATE ZX_EXTRACT_PKG.SHIPPING_TP_STATE_TBL;
48 GT_SHIPPING_TP_PROVINCE ZX_EXTRACT_PKG.SHIPPING_TP_PROVINCE_TBL;
49 GT_SHIPPING_TP_ADDRESS1 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS1_TBL;
50 GT_SHIPPING_TP_ADDRESS2 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS2_TBL;
51 GT_SHIPPING_TP_ADDRESS3 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS3_TBL;
52 GT_SHIPPING_TP_ADDR_LINES_ALT ZX_EXTRACT_PKG.SHIPPING_TP_ADDR_LINES_ALT_TBL;
53 GT_SHIPPING_TP_COUNTRY ZX_EXTRACT_PKG.SHIPPING_TP_COUNTRY_TBL;
54 GT_SHIPPING_TP_POSTAL_CODE ZX_EXTRACT_PKG.SHIPPING_TP_POSTAL_CODE_TBL;
55 -- GT_SHIPPING_TP_PARTY_NUMBER ZX_EXTRACT_PKG.SHIPPING_TP_PARTY_NUMBER_TBL;
56 GT_SHIPPING_TP_ID ZX_EXTRACT_PKG.SHIPPING_TP_ID_TBL;
57 GT_SHIPPING_TP_SITE_ID ZX_EXTRACT_PKG.SHIPPING_TP_SITE_ID_TBL;
58 GT_SHIPPING_TP_ADDRESS_ID ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS_ID_TBL;
59 -- GT_SHIPPING_TP_TAX_REP_FLAG ZX_EXTRACT_PKG.SHIPPING_TP_TAX_REP_FLAG_TBL;
60 GT_SHIPPING_TP_SITE_NAME ZX_EXTRACT_PKG.SHIPPING_TP_SITE_NAME_TBL;
61 GT_GDF_RA_ADDRESSES_SHIP_ATT9 ZX_EXTRACT_PKG.GDF_RA_ADDRESSES_SHIP_ATT9_TBL;
62 GT_GDF_PARTY_SITES_SHIP_ATT8 ZX_EXTRACT_PKG.GDF_PARTY_SITES_SHIP_ATT8_TBL;
63 GT_GDF_RA_CUST_SHIP_ATT10 ZX_EXTRACT_PKG.GDF_RA_CUST_SHIP_ATT10_TBL;
64 GT_GDF_RA_CUST_SHIP_ATT12 ZX_EXTRACT_PKG.GDF_RA_CUST_SHIP_ATT12_TBL;
65 GT_GDF_RA_ADDRESSES_SHIP_ATT8 ZX_EXTRACT_PKG.GDF_RA_ADDRESSES_SHIP_ATT8_TBL;
66 GT_TAX_RATE_VAT_TRX_TYPE_DESC ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_DESC_TBL;
67 GT_TAX_RATE_CODE_REG_TYPE_MNG ZX_EXTRACT_PKG.TAX_RATE_CODE_REG_TYPE_MNG_TBL;
68 GT_TRX_CLASS_MNG ZX_EXTRACT_PKG.TRX_CLASS_MNG_TBL;
69 GT_TAX_EXCEPTION_REASON_MNG ZX_EXTRACT_PKG.TAX_EXCEPTION_REASON_MNG_TBL;
70 GT_TAX_EXEMPT_REASON_MNG ZX_EXTRACT_PKG.TAX_EXEMPT_REASON_MNG_TBL;
71
72 GT_DETAIL_TAX_LINE_ID ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
73 GT_LEDGER_ID ZX_EXTRACT_PKG.LEDGER_ID_TBL;
74 GT_TRX_ID ZX_EXTRACT_PKG.TRX_ID_TBL;
75 GT_TRX_TYPE_ID ZX_EXTRACT_PKG.TRX_TYPE_ID_TBL;
76 GT_TRX_CLASS ZX_EXTRACT_PKG.TRX_LINE_CLASS_TBL;
77 GT_TRX_BATCH_SOURCE_ID ZX_EXTRACT_PKG.BATCH_SOURCE_ID_TBL;
78 GT_TAX_RATE_ID ZX_EXTRACT_PKG.TAX_RATE_ID_TBL;
79 GT_TAX_RATE_VAT_TRX_TYPE_CODE ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_CODE_TBL;
80 GT_TAX_RATE_REG_TYPE_CODE ZX_EXTRACT_PKG.TAX_RATE_REG_TYPE_CODE_TBL;
81 GT_TAX_EXEMPTION_ID ZX_EXTRACT_PKG.TAX_EXEMPTION_ID_TBL;
82 GT_TAX_EXCEPTION_ID ZX_EXTRACT_PKG.TAX_EXCEPTION_ID_TBL;
83 GT_TAX_LINE_ID ZX_EXTRACT_PKG.TAX_LINE_ID_TBL;
84 GT_TAX_AMT ZX_EXTRACT_PKG.TAX_AMT_TBL;
85 GT_TAX_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL;
86 GT_TAX_LINE_NUMBER ZX_EXTRACT_PKG.TAX_LINE_NUMBER_TBL;
87 GT_TAXABLE_AMT ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
88 GT_TAXABLE_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL;
89 GT_TRX_LINE_ID ZX_EXTRACT_PKG.TRX_LINE_ID_TBL;
90 GT_TAX_EXCEPTION_REASON_CODE ZX_EXTRACT_PKG.TAX_EXCEPTION_REASON_CODE_TBL;
91 GT_EXEMPT_REASON_CODE ZX_EXTRACT_PKG.EXEMPT_REASON_CODE_TBL;
92 GT_RECONCILIATION_FLAG ZX_EXTRACT_PKG.RECONCILIATION_FLAG_TBL;
93 GT_INTERNAL_ORGANIZATION_ID ZX_EXTRACT_PKG.INTERNAL_ORGANIZATION_ID_TBL;
94 GT_BR_REF_CUSTOMER_TRX_ID ZX_EXTRACT_PKG.BR_REF_CUSTOMER_TRX_ID_TBL;
95 GT_REVERSE_FLAG ZX_EXTRACT_PKG.REVERSE_FLAG_TBL;
96 GT_AMOUNT_APPLIED ZX_EXTRACT_PKG.AMOUNT_APPLIED_TBL;
97 GT_TAX_RATE ZX_EXTRACT_PKG.TAX_RATE_TBL;
98 GT_TAX_RATE_CODE ZX_EXTRACT_PKG.TAX_RATE_CODE_TBL;
99 GT_TAX_TYPE_CODE ZX_EXTRACT_PKG.TAX_TYPE_CODE_TBL;
100 GT_TRX_DATE ZX_EXTRACT_PKG.TRX_DATE_TBL;
101 GT_TRX_CURRENCY_CODE ZX_EXTRACT_PKG.TRX_CURRENCY_CODE_TBL;
102 GT_CURRENCY_CONVERSION_RATE ZX_EXTRACT_PKG.CURRENCY_CONVERSION_RATE_TBL;
103 GT_APPLICATION_ID ZX_EXTRACT_PKG.APPLICATION_ID_TBL;
104 GT_DOC_EVENT_STATUS ZX_EXTRACT_PKG.DOC_EVENT_STATUS_TBL;
105 GT_EXTRACT_SOURCE_LEDGER ZX_EXTRACT_PKG.EXTRACT_SOURCE_LEDGER_TBL;
106 GT_FUNCTIONAL_CURRENCY_CODE ZX_EXTRACT_PKG.FUNCTIONAL_CURRENCY_CODE_TBL;
107 GT_MINIMUM_ACCOUNTABLE_UNIT ZX_EXTRACT_PKG.MINIMUM_ACCOUNTABLE_UNIT_TBL;
108 GT_PRECISION ZX_EXTRACT_PKG.PRECISION_TBL;
109 GT_RECEIPT_CLASS_ID ZX_EXTRACT_PKG.RECEIPT_CLASS_ID_TBL;
110 GT_EXCEPTION_RATE ZX_EXTRACT_PKG.EXCEPTION_RATE_TBL;
111 GT_SHIP_FROM_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_FROM_PTY_TAX_PROF_ID_TBL;
112 GT_SHIP_FROM_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_FROM_SITE_TAX_PROF_ID_TBL;
113 GT_SHIP_TO_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_TO_PARTY_TAX_PROF_ID_TBL;
114 GT_SHIP_TO_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_TO_SITE_TAX_PROF_ID_TBL;
115 GT_BILL_TO_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_TO_PARTY_TAX_PROF_ID_TBL;
116 GT_BILL_TO_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_TO_SITE_TAX_PROF_ID_TBL;
117 GT_BILL_FROM_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_FROM_PTY_TAX_PROF_ID_TBL;
118 GT_BILL_FROM_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_FROM_SITE_TAX_PROF_ID_TBL;
119 -- GT_BILLING_TP_ID ZX_EXTRACT_PKG.BILLING_TP_ID_TBL;
120 -- GT_BILLING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
121 --GT_BILLING_TP_ADDRESS_ID ZX_EXTRACT_PKG.BILLING_TP_ADDRESS_ID_TBL;
122 GT_BILL_TO_PARTY_ID ZX_EXTRACT_PKG.BILL_TO_PARTY_ID_TBL;
123 GT_BILL_TO_PARTY_SITE_ID ZX_EXTRACT_PKG.BILL_TO_PARTY_SITE_ID_TBL;
124 GT_SHIP_TO_PARTY_ID ZX_EXTRACT_PKG.SHIP_TO_PARTY_ID_TBL;
125 GT_SHIP_TO_PARTY_SITE_ID ZX_EXTRACT_PKG.SHIP_TO_PARTY_SITE_ID_TBL;
126 GT_HISTORICAL_FLAG ZX_EXTRACT_PKG.HISTORICAL_FLAG_TBL;
127 GT_ACTG_SOURCE_ID ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL;
128 GT_AE_HEADER_ID ZX_EXTRACT_PKG.ACTG_HEADER_ID_TBL;
129 GT_EVENT_ID ZX_EXTRACT_PKG.ACTG_EVENT_ID_TBL;
130 -- GT_ENTITY_ID ZX_EXTRACT_PKG.ACTG_ENTITY_ID_TBL;
131 GT_LINE_CCID ZX_EXTRACT_PKG.ACTG_LINE_CCID_TBL;
132 GT_TRX_ARAP_BALANCING_SEGMENT ZX_EXTRACT_PKG.TRX_ARAP_BALANCING_SEG_TBL;
133 GT_TRX_ARAP_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_ARAP_NATURAL_ACCOUNT_TBL;
134 GT_TRX_TAXABLE_BAL_SEG ZX_EXTRACT_PKG.TRX_TAXABLE_BALANCING_SEG_TBL;
135 GT_TRX_TAXABLE_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_TAXABLE_NATURAL_ACCT_TBL;
136 GT_TRX_TAX_BALANCING_SEGMENT ZX_EXTRACT_PKG.TRX_TAX_BALANCING_SEG_TBL;
137 GT_TRX_TAX_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_TAX_NATURAL_ACCOUNT_TBL;
138 --GT_INTERNAL_ORGANIZATION_ID ZX_EXTRACT_PKG.INTERNAL_ORGANIZATION_ID_TBL;
139 -- apai GT_REP_CONTEXT_ID ZX_EXTRACT_PKG.REP_CONTEXT_ID_TBL;
140
141
142 G_RETCODE NUMBER :=0;
143
144 C_LINES_PER_COMMIT CONSTANT NUMBER := 5000;
145 L_MSG VARCHAR2(500);
146 G_REP_CONTEXT_ID NUMBER;
147 g_current_runtime_level NUMBER;
148 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
149 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
150 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
151 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
152 g_error_buffer VARCHAR2(100);
153
154 PROCEDURE convert_amounts(P_CURRENCY_CODE IN VARCHAR2,
155 P_EXCHANGE_RATE IN NUMBER,
156 P_PRECISION IN NUMBER,
157 P_MIN_ACCT_UNIT IN NUMBER,
158 P_INPUT_TAX_AMOUNT IN NUMBER,
159 P_INPUT_TAXABLE_AMOUNT IN NUMBER,
160 P_INPUT_EXEMPT_AMOUNT IN NUMBER,
161 i IN binary_integer);
162
163
164
165 PROCEDURE APP_FUNCTIONAL_AMOUNTS(
166 P_TRX_ID IN NUMBER,
167 P_TAX_CODE_ID IN NUMBER,
168 P_CURRENCY_CODE IN VARCHAR2,
169 P_EXCHANGE_RATE IN NUMBER,
170 P_PRECISION IN NUMBER,
171 P_MIN_ACCT_UNIT IN NUMBER,
172 P_INPUT_TAX_AMOUNT IN OUT NOCOPY NUMBER,
173 P_INPUT_TAXABLE_AMOUNT IN OUT NOCOPY NUMBER,
174 P_SUMMARY_LEVEL IN VARCHAR2,
175 P_REGISTER_TYPE IN VARCHAR2,
176 i IN BINARY_INTEGER);
177
178 PROCEDURE get_accounting_info(P_TRX_ID IN NUMBER,
179 P_TRX_LINE_ID IN NUMBER,
180 P_TAX_LINE_ID IN NUMBER,
181 P_EVENT_ID IN NUMBER,
182 P_AE_HEADER_ID IN NUMBER,
183 P_ACTG_SOURCE_ID IN NUMBER,
184 P_BALANCING_SEGMENT IN VARCHAR2,
185 P_ACCOUNTING_SEGMENT IN VARCHAR2,
186 P_SUMMARY_LEVEL IN VARCHAR2,
187 P_TRX_CLASS IN VARCHAR2,
188 i IN binary_integer);
189
190 PROCEDURE get_accounting_amounts(P_TRX_ID IN NUMBER,
191 P_TRX_LINE_ID IN NUMBER,
192 P_TAX_LINE_ID IN NUMBER,
193 -- P_ENTITY_ID IN NUMBER,
194 P_EVENT_ID IN NUMBER,
195 P_AE_HEADER_ID IN NUMBER,
196 P_ACTG_SOURCE_ID IN NUMBER,
197 P_SUMMARY_LEVEL IN VARCHAR2,
198 P_TRX_CLASS IN VARCHAR2,
199 P_LEDGER_ID IN NUMBER,
200 i IN binary_integer);
201
202 PROCEDURE other_trx_segment_info(P_TRX_ID IN NUMBER,
203 P_TRX_LINE_ID IN NUMBER,
204 P_TAX_LINE_ID IN NUMBER,
205 -- P_ENTITY_ID IN NUMBER,
206 P_EVENT_ID IN NUMBER,
207 P_AE_HEADER_ID IN NUMBER,
208 P_ACTG_SOURCE_ID IN NUMBER,
209 P_BALANCING_SEGMENT IN VARCHAR2,
210 P_ACCOUNTING_SEGMENT IN VARCHAR2,
211 P_SUMMARY_LEVEL IN VARCHAR2,
212 P_TRX_CLASS IN VARCHAR2,
213 i IN binary_integer);
214
215 PROCEDURE other_trx_actg_amounts(P_TRX_ID IN NUMBER,
216 P_TRX_LINE_ID IN NUMBER,
217 P_TAX_LINE_ID IN NUMBER,
218 -- P_ENTITY_ID IN NUMBER,
219 P_EVENT_ID IN NUMBER,
220 P_AE_HEADER_ID IN NUMBER,
221 P_ACTG_SOURCE_ID IN NUMBER,
222 P_SUMMARY_LEVEL IN VARCHAR2,
223 P_TRX_CLASS IN VARCHAR2,
224 P_LEDGER_ID IN NUMBER,
225 i IN binary_integer);
226
227 PROCEDURE inv_segment_info (P_TRX_ID IN NUMBER,
228 P_TRX_LINE_ID IN NUMBER,
229 P_TAX_LINE_ID IN NUMBER,
230 -- P_ENTITY_ID IN NUMBER,
234 P_BALANCING_SEGMENT IN VARCHAR2,
231 P_EVENT_ID IN NUMBER,
232 P_AE_HEADER_ID IN NUMBER,
233 P_ACTG_SOURCE_ID IN NUMBER,
235 P_ACCOUNTING_SEGMENT IN VARCHAR2,
236 P_SUMMARY_LEVEL IN VARCHAR2,
237 P_TRX_CLASS IN VARCHAR2,
238 i IN binary_integer);
239
240
241 PROCEDURE inv_actg_amounts(P_TRX_ID IN NUMBER,
242 P_TRX_LINE_ID IN NUMBER,
243 P_TAX_LINE_ID IN NUMBER,
244 -- P_ENTITY_ID IN NUMBER,
245 P_EVENT_ID IN NUMBER,
246 P_AE_HEADER_ID IN NUMBER,
247 P_ACTG_SOURCE_ID IN NUMBER,
248 P_SUMMARY_LEVEL IN VARCHAR2,
249 P_TRX_CLASS IN VARCHAR2,
250 P_LEDGER_ID IN NUMBER,
251 i IN binary_integer);
252
253 PROCEDURE EXTRACT_PARTY_INFO( i IN BINARY_INTEGER);
254
255 PROCEDURE initialize_variables (
256 p_count IN NUMBER);
257
258 PROCEDURE populate_meaning(
259 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
260 i BINARY_INTEGER);
261
262 PROCEDURE UPDATE_REP_DETAIL_T(p_count IN NUMBER);
263
264 PROCEDURE UPDATE_REP_ACTG_T(p_count IN NUMBER);
265
266 /*===========================================================================+
267 | PROCEDURE |
268 | UPDATE_ADDITIONAL_INFO |
269 | |
270 | DESCRIPTION |
271 | This procedure populates additional extract information |
272 | AR_TAX_EXTRACT_SUB_ITF |
273 | |
274 | Called from |
275 | |
276 | SCOPE - Public |
277 | |
278 | NOTES |
279 | |
280 | MODIFICATION HISTORY |
281 | |
282 +===========================================================================*/
283 PROCEDURE UPDATE_ADDITIONAL_INFO(
284 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
285 P_MRC_SOB_TYPE IN VARCHAR2)
286 IS
287
288 CURSOR detail_t_cur(c_request_id IN NUMBER) IS
289 SELECT ZX_DTL.DETAIL_TAX_LINE_ID,
290 ZX_DTL.LEDGER_ID,
291 ZX_DTL.INTERNAL_ORGANIZATION_ID,
292 ZX_DTL.TRX_ID ,
293 ZX_DTL.TRX_TYPE_ID ,
294 ZX_DTL.TRX_LINE_CLASS,
295 ZX_DTL.TRX_BATCH_SOURCE_ID,
296 ZX_DTL.TAX_RATE_ID ,
297 ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
298 ZX_DTL.TAX_RATE_REGISTER_TYPE_CODE,
299 ZX_DTL.TAX_EXEMPTION_ID ,
300 ZX_DTL.TAX_EXCEPTION_ID ,
301 ZX_DTL.TAX_LINE_ID ,
302 ZX_DTL.TAX_AMT ,
303 ZX_DTL.TAX_AMT_FUNCL_CURR ,
304 ZX_DTL.TAX_LINE_NUMBER ,
305 ZX_DTL.TAXABLE_AMT ,
306 ZX_DTL.TAXABLE_AMT_FUNCL_CURR ,
307 ZX_DTL.TRX_LINE_ID ,
308 ZX_DTL.TAX_EXCEPTION_REASON_CODE ,
309 ZX_DTL.EXEMPT_REASON_CODE,
310 ZX_DTL.RECONCILIATION_FLAG ,
311 ZX_DTL.INTERNAL_ORGANIZATION_ID,
312 ZX_DTL.BR_REF_CUSTOMER_TRX_ID,
313 ZX_DTL.REVERSE_FLAG,
314 ZX_DTL.AMOUNT_APPLIED,
315 ZX_DTL.TAX_RATE,
316 ZX_DTL.TAX_RATE_CODE,
317 ZX_DTL.TAX_TYPE_CODE,
318 ZX_DTL.TRX_DATE,
319 ZX_DTL.TRX_CURRENCY_CODE,
320 ZX_DTL.CURRENCY_CONVERSION_RATE,
321 ZX_DTL.APPLICATION_ID,
322 ZX_DTL.DOC_EVENT_STATUS,
323 ZX_DTL.EXTRACT_SOURCE_LEDGER ,
324 ZX_DTL.FUNCTIONAL_CURRENCY_CODE,
325 ZX_DTL.MINIMUM_ACCOUNTABLE_UNIT,
326 ZX_DTL.PRECISION,
327 ZX_DTL.RECEIPT_CLASS_ID ,
328 ZX_DTL.EXCEPTION_RATE,
329 ZX_DTL.SHIP_FROM_PARTY_TAX_PROF_ID,
330 ZX_DTL.SHIP_FROM_SITE_TAX_PROF_ID,
331 ZX_DTL.SHIP_TO_PARTY_TAX_PROF_ID ,
332 ZX_DTL.SHIP_TO_SITE_TAX_PROF_ID ,
333 ZX_DTL.BILL_TO_PARTY_TAX_PROF_ID,
334 ZX_DTL.BILL_TO_SITE_TAX_PROF_ID,
335 ZX_DTL.BILL_FROM_PARTY_TAX_PROF_ID,
336 ZX_DTL.BILL_FROM_SITE_TAX_PROF_ID,
337 ZX_DTL.BILLING_TRADING_PARTNER_ID,
338 ZX_DTL.BILLING_TP_SITE_ID,
339 ZX_DTL.BILLING_TP_ADDRESS_ID,
340 ZX_DTL.SHIPPING_TRADING_PARTNER_ID,
341 ZX_DTL.SHIPPING_TP_SITE_ID,
342 ZX_DTL.SHIPPING_TP_ADDRESS_ID,
343 ZX_DTL.BILL_TO_PARTY_ID,
344 ZX_DTL.BILL_TO_PARTY_SITE_ID,
345 ZX_DTL.SHIP_TO_PARTY_ID,
346 ZX_DTL.SHIP_TO_PARTY_SITE_ID,
347 ZX_DTL.HISTORICAL_FLAG,
348 ZX_ACTG.ACTG_SOURCE_ID,
349 ZX_ACTG.ACTG_HEADER_ID,
353 FROM zx_rep_trx_detail_t zx_dtl,
350 ZX_ACTG.ACTG_EVENT_ID,
351 -- ZX_ACTG.ACTG_ENTITY_ID,
352 ZX_ACTG.ACTG_LINE_CCID
354 zx_rep_actg_ext_t zx_actg
355 WHERE EXTRACT_SOURCE_LEDGER = 'AR'
356 AND zx_dtl.detail_tax_line_id = zx_actg.detail_tax_line_id
357 AND zx_dtl.request_id = c_request_id;
358
359 CURSOR chart_of_acc_id IS
360 SELECT chart_of_accounts_id
361 FROM gl_sets_of_books
362 WHERE set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.ledger_id;
363
364 L_TRX_CLASS VARCHAR2(30);
365 L_TAXABLE_AMOUNT NUMBER;
366 L_TAXABLE_ACCOUNTED_AMOUNT NUMBER;
367
368 -- L_BANKING_TP_NAME AR_TAX_EXTRACT_SUB_ITF.BANKING_TP_NAME%TYPE;
369 -- L_BANKING_TP_TAXPAYER_ID AR_TAX_EXTRACT_SUB_ITF.BANKING_TP_TAXPAYER_ID%type;
370 -- L_MATRIX_REPORT VARCHAR2(1);
371
372 -- L_TRX_APPLIED_TO_TRX_ID NUMBER; -- where it is used, AP
373 -- L_ACCOUNTING_DATE DATE; -- where is this being used AP
374 -- L_TRX_CURRENCY_CODE VARCHAR2(15);
375 -- RA_SUB_ITF_TABLE_REC AR_TAX_EXTRACT_SUB_ITF%ROWTYPE;
376 l_count NUMBER;
377 l_balancing_segment VARCHAR2(25);
378 l_accounting_segment VARCHAR2(25);
379 l_ledger_id NUMBER(15);
380 BEGIN
381
382 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
383
384 IF (g_level_procedure >= g_current_runtime_level ) THEN
385 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO.BEGIN',
386 'ZX_AR_ACTG_POPULATE_PKG: UPDATE_ADDITIONAL_INFO(+)');
387 END IF;
388 l_ledger_id := NVL(P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEDGER_ID, P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID);
389 -- L_MATRIX_REPORT := P_MATRIX_REPORT;
390 -- l_request_id is global param, assigned value in initialize
391
392
393 -- Accounting Flex Field Information --
394
395 OPEN chart_of_acc_id;
396 FETCH chart_of_acc_id
397 INTO P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id;
398
399 -- Determine which segment is balancing segment for the given
400 -- chart of accounts (Set of books)
401
402
403 l_balancing_segment := fa_rx_flex_pkg.flex_sql(
404 p_application_id =>101,
405 p_id_flex_code => 'GL#',
406 p_id_flex_num => P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
407 p_table_alias => '',
408 p_mode => 'SELECT',
409 p_qualifier => 'GL_BALANCING');
410
411
412 l_accounting_segment := fa_rx_flex_pkg.flex_sql(
413 p_application_id =>101,
414 p_id_flex_code => 'GL#',
415 p_id_flex_num => P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
416 p_table_alias => '',
417 p_mode => 'SELECT',
418 p_qualifier => 'GL_ACCOUNT');
419
420 -- The above function will return balancing segment in the form CC.SEGMENT1
421 -- we need to drop CC. to get the actual balancing segment.
422
423 l_balancing_segment := substrb(l_balancing_segment,
424 instrb(l_balancing_segment,'.')+1);
425
426 OPEN detail_t_cur(P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID);
427 LOOP
428 FETCH detail_t_cur BULK COLLECT INTO
429 GT_DETAIL_TAX_LINE_ID,
430 GT_LEDGER_ID,
431 GT_INTERNAL_ORGANIZATION_ID,
432 GT_TRX_ID,
433 GT_TRX_TYPE_ID,
434 GT_TRX_CLASS,
435 GT_TRX_BATCH_SOURCE_ID,
436 GT_TAX_RATE_ID,
437 GT_TAX_RATE_VAT_TRX_TYPE_CODE,
438 GT_TAX_RATE_REG_TYPE_CODE,
439 GT_TAX_EXEMPTION_ID,
440 GT_TAX_EXCEPTION_ID,
441 GT_TAX_LINE_ID,
442 GT_TAX_AMT,
443 GT_TAX_AMT_FUNCL_CURR,
444 GT_TAX_LINE_NUMBER,
445 GT_TAXABLE_AMT,
446 GT_TAXABLE_AMT_FUNCL_CURR,
447 GT_TRX_LINE_ID,
448 GT_TAX_EXCEPTION_REASON_CODE,
449 GT_EXEMPT_REASON_CODE,
450 GT_RECONCILIATION_FLAG,
451 GT_INTERNAL_ORGANIZATION_ID,
452 GT_BR_REF_CUSTOMER_TRX_ID,
453 GT_REVERSE_FLAG,
454 GT_AMOUNT_APPLIED,
455 GT_TAX_RATE,
456 GT_TAX_RATE_CODE,
457 GT_TAX_TYPE_CODE,
458 GT_TRX_DATE,
459 GT_TRX_CURRENCY_CODE,
460 GT_CURRENCY_CONVERSION_RATE,
461 GT_APPLICATION_ID,
462 GT_DOC_EVENT_STATUS,
463 GT_EXTRACT_SOURCE_LEDGER,
464 GT_FUNCTIONAL_CURRENCY_CODE,
465 GT_MINIMUM_ACCOUNTABLE_UNIT,
466 GT_PRECISION,
467 GT_RECEIPT_CLASS_ID,
468 GT_EXCEPTION_RATE,
469 GT_SHIP_FROM_PARTY_TAX_PROF_ID,
470 GT_SHIP_FROM_SITE_TAX_PROF_ID,
471 GT_SHIP_TO_PARTY_TAX_PROF_ID,
472 GT_SHIP_TO_SITE_TAX_PROF_ID,
473 GT_BILL_TO_PARTY_TAX_PROF_ID,
474 GT_BILL_TO_SITE_TAX_PROF_ID,
475 GT_BILL_FROM_PARTY_TAX_PROF_ID,
476 GT_BILL_FROM_SITE_TAX_PROF_ID,
477 GT_BILLING_TP_ID,
478 GT_BILLING_TP_SITE_ID,
479 GT_BILLING_TP_ADDRESS_ID,
480 GT_SHIPPING_TP_ID,
481 GT_SHIPPING_TP_SITE_ID,
482 GT_SHIPPING_TP_ADDRESS_ID,
483 GT_BILL_TO_PARTY_ID,
484 GT_BILL_TO_PARTY_SITE_ID,
485 GT_SHIP_TO_PARTY_ID,
486 GT_SHIP_TO_PARTY_SITE_ID,
487 GT_HISTORICAL_FLAG,
488 GT_ACTG_SOURCE_ID,
489 GT_AE_HEADER_ID,
490 GT_EVENT_ID,
491 -- GT_ENTITY_ID,
492 GT_LINE_CCID
493 LIMIT C_LINES_PER_COMMIT;
494
495 l_count := nvl(GT_DETAIL_TAX_LINE_ID.COUNT,0);
496
497 IF l_count >0 THEN
498
499 initialize_variables(l_count);
500 G_REP_CONTEXT_ID := ZX_EXTRACT_PKG.GET_REP_CONTEXT_ID(P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID,
501 P_TRL_GLOBAL_VARIABLES_REC.request_id);
502
503
504 FOR i IN 1..l_count
505 LOOP
506
507 L_TRX_CLASS := GT_TRX_CLASS(i);
508
509 IF P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION'
510 OR ( UPPER(L_TRX_CLASS) IN
511 ('APP','EDISC','UNEDISC','ADJ','FINCHRG','MISC_CASH_RECEIPT','BR') )
512 THEN
513 -- Pass the taxable amount columns for rounding
514 L_TAXABLE_AMOUNT := GT_TAXABLE_AMT(i);
515 L_TAXABLE_ACCOUNTED_AMOUNT := GT_TAXABLE_AMT_FUNCL_CURR(i);
516 END IF;
517 /* apai
518 GT_REP_CONTEXT_ID(i) := ZX_EXTRACT_PKG.GET_REP_CONTEXT_ID(GT_INTERNAL_ORGANIZATION_ID(i),
519 P_TRL_GLOBAL_VARIABLES_REC.legal_entity_level,
520 P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID,
521 P_TRL_GLOBAL_VARIABLES_REC.request_id);
522 */
523
524 IF (g_level_procedure >= g_current_runtime_level ) THEN
525 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
526 'G_REP_CONTEXT_ID :' ||to_char(G_REP_CONTEXT_ID)||'---'
527 ||to_char(GT_INTERNAL_ORGANIZATION_ID(i)));
528 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
529 'GT_TRX_ID :' ||to_char(GT_TRX_ID(i)));
530 END IF;
531
532 -- Replacement to populate_inv()
533
534 IF L_TRX_CLASS IN ('APP','EDISC','UNEDISC','ADJ','FINCHRG','MISC_CASH_RECEIPT','BR')
535 THEN
536 APP_FUNCTIONAL_AMOUNTS(
537 GT_TRX_ID(i),
538 GT_TAX_RATE_ID(i),
539 GT_TRX_CURRENCY_CODE(i),
540 GT_CURRENCY_CONVERSION_RATE(i),
541 GT_PRECISION(i),
542 GT_MINIMUM_ACCOUNTABLE_UNIT(i),
543 GT_TAX_AMT(i),
544 GT_TAXABLE_AMT(i),
545 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
546 P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE,
547 i);
548 END IF;
549
550 get_accounting_info(GT_TRX_ID(i),
551 GT_TRX_LINE_ID(i),
552 GT_TAX_LINE_ID(i),
553 GT_EVENT_ID(i),
554 GT_AE_HEADER_ID(i),
555 GT_ACTG_SOURCE_ID(i),
556 l_balancing_segment,
557 l_accounting_segment,
558 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
559 L_TRX_CLASS,
560 i) ;
561
562 get_accounting_amounts(GT_TRX_ID(i),
563 GT_TRX_LINE_ID(i),
564 GT_TAX_LINE_ID(i),
565 -- GT_ENTITY_ID(i),
566 GT_EVENT_ID(i),
567 GT_AE_HEADER_ID(i),
568 GT_ACTG_SOURCE_ID(i),
569 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
570 L_TRX_CLASS,
571 l_ledger_id,
572 i) ;
573
574
575 --Check This Code
576 -- IF UPPER(L_TRX_CLASS) IN ('APP','EDISC','UNEDISC','ADJ','FINCHRG',
577 -- 'MISC_CASH_RECEIPT','BR')
578 -- AND GT_TAX_CODE_ID_TAB(i) IS NULL
579 -- AND GT_TAX_OFFSET_TAX_CODE_ID_TAB(i) IS NOT NULL
580 -- THEN
581 --
582 -- PG_TAX_CODE_ID_TAB(i) := PG_TAX_OFFSET_TAX_CODE_ID_TAB(i);
583 --
584 -- END If;
585 --
586 -- POPULATE_EXT_COM_EXT_COLUMNS( p_index => i );
587
588 EXTRACT_PARTY_INFO(i);
589
590 END LOOP; -- end loop of each extract line
591
592 -- IF G_AR_RETCODE <>2 THEN
593 -- UPDATE_AR_SUB_ITF(L_MATRIX_REPORT,P_SUMMARY_LEVEL );
594 -- END IF;
595 --
596 -- IF G_AR_RETCODE <>2 THEN
597 -- UPDATE_AR_EXTENSION(l_count);
598 -- END IF;
599 --
600 -- IF G_AR_RETCODE <>2 THEN
601 -- UPDATE_COM_EXTENSION(l_count);
602 --END IF;
603
604 -- ELSE
605 --
606 -- EXIT;
607
608 END IF;
609 UPDATE_REP_DETAIL_T(l_count);
610 UPDATE_REP_ACTG_T(l_count);
611
612 EXIT WHEN detail_t_cur%NOTFOUND
613 OR detail_t_cur%NOTFOUND IS NULL;
614
615 END LOOP;
616
617 CLOSE detail_t_cur;
618
619 IF (g_level_procedure >= g_current_runtime_level ) THEN
620 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO.END',
621 'ZX_AR_ACTG_POPULATE_PKG: UPDATE_ADDITIONAL_INFO(-)');
622 END IF;
623
624 EXCEPTION
625 WHEN OTHERS THEN
626 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
627 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
628 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
629 FND_MSG_PUB.Add;
630 IF (g_level_unexpected >= g_current_runtime_level ) THEN
631 FND_LOG.STRING(g_level_unexpected,
632 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
633 g_error_buffer);
634 END IF;
635
636 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := G_RETCODE;
637
638 END UPDATE_ADDITIONAL_INFO;
639
640
641 PROCEDURE APP_FUNCTIONAL_AMOUNTS(
642 P_TRX_ID IN NUMBER,
643 P_TAX_CODE_ID IN NUMBER,
644 P_CURRENCY_CODE IN VARCHAR2,
645 P_EXCHANGE_RATE IN NUMBER,
646 P_PRECISION IN NUMBER,
647 P_MIN_ACCT_UNIT IN NUMBER,
648 P_INPUT_TAX_AMOUNT IN OUT NOCOPY NUMBER,
649 P_INPUT_TAXABLE_AMOUNT IN OUT NOCOPY NUMBER,
650 P_SUMMARY_LEVEL IN VARCHAR2,
651 P_REGISTER_TYPE IN VARCHAR2,
652 i IN binary_integer)
653 IS
654
655
656 CURSOR ROUNDING_AMTS_CURSOR (
657 C_TRX_ID IN NUMBER,
658 C_REGISTER_TYPE IN VARCHAR2,
659 C_TAX_ID IN NUMBER ) IS
660 SELECT SUM(NVL(ARDTAX.AMOUNT_CR,0) - NVL(ARDTAX.AMOUNT_DR,0)),
661 SUM(NVL(ARDTAX.TAXABLE_ENTERED_CR,0) -
662 NVL(ARDTAX.TAXABLE_ENTERED_DR,0))
663 FROM AR_DISTRIBUTIONS_ALL ARDTAX,
664 AR_RECEIVABLE_APPLICATIONS_ALL APP,
665 RA_CUSTOMER_TRX_ALL TRXCM
666 WHERE TRXCM.CUSTOMER_TRX_ID = C_TRX_ID
667 AND APP.APPLIED_CUSTOMER_TRX_ID = TRXCM.CUSTOMER_TRX_ID
668 AND APP.RECEIVABLE_APPLICATION_ID = ARDTAX.SOURCE_ID
669 AND ARDTAX.SOURCE_TABLE = 'RA'
670 AND ARDTAX.SOURCE_TYPE = DECODE(C_REGISTER_TYPE,'TAX','TAX',
671 'INTERIM','DEFERRED_TAX',NULL)
672 AND ARDTAX.TAX_CODE_ID = C_TAX_ID
673 AND ARDTAX.SOURCE_TABLE_SECONDARY = 'CT'
674 AND ARDTAX.SOURCE_TYPE_SECONDARY = 'RECONCILE'
675 GROUP BY C_TRX_ID, C_TAX_ID ;
676
677
678 L_CURRENCY_CODE VARCHAR2(15);
679 L_EXCHANGE_RATE NUMBER;
680 L_PRECISION NUMBER;
681 L_MIN_ACCT_UNIT NUMBER;
682 L_TAXABLE_AMOUNT NUMBER;
683 L_TAX_AMOUNT NUMBER;
684 L_EXEMPT_AMOUNT NUMBER;
685 L_TAXABLE_ACCTD_AMT NUMBER;
686 L_TAX_ACCTD_AMT NUMBER;
687 L_ORG_ID NUMBER;
688 L_MATRIX_STATEMENT VARCHAR2(5000);
689 L_CONTROL_ACCOUNT_CCID NUMBER;
690 L_SET_OF_BOOKS_ID NUMBER;
694 l_al_third_party_id NUMBER;
691 L_AH_ACCOUNTING_DATE DATE;
692 L_ROUNDING_TAXABLE_AMT NUMBER;
693 L_ROUNDING_TAX_AMT NUMBER;
695 l_al_third_party_sub_id NUMBER;
696 l_gl_posted_date DATE;
697
698 L_TRX_CLASS VARCHAR2(30);
699 L_DIST_ID NUMBER;
700 L_BAL_SEG_STATEMENT VARCHAR2(1000);
701 L_BR_PARENT_TRX_ID NUMBER;
702
703 BEGIN
704
705 IF (g_level_procedure >= g_current_runtime_level ) THEN
706 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.APP_FUNCTIONAL_AMOUNTS.BEGIN',
707 'ZX_AR_ACTG_POPULATE_PKG: APP_FUNCTIONAL_AMOUNTS(+)');
708 END IF;
709
710 -- L_ORG_ID := P_RA_SUB_ITF_TABLE_REC.org_id;
711 -- L_CONTROL_ACCOUNT_CCID := P_RA_SUB_ITF_TABLE_REC.al_account_ccid;
712 -- L_SET_OF_BOOKS_ID := P_RA_SUB_ITF_TABLE_REC.set_of_books_id;
713 -- L_AH_ACCOUNTING_DATE := P_RA_SUB_ITF_TABLE_REC.accounting_date;
714 -- L_GL_POSTED_DATE := P_RA_SUB_ITF_TABLE_REC.gl_posted_date;
715 -- L_CURRENCY_CODE := P_RA_SUB_ITF_TABLE_REC.currency_code;
716 -- L_EXCHANGE_RATE := P_RA_SUB_ITF_TABLE_REC.exchange_rate;
717 -- L_PRECISION := P_RA_SUB_ITF_TABLE_REC.precision;
718 -- L_TAX_AMOUNT := P_RA_SUB_ITF_TABLE_REC.tax_entered_amount;
719 -- L_TAXABLE_AMOUNT := P_RA_SUB_ITF_TABLE_REC.taxable_amount;
720 -- L_EXEMPT_AMOUNT := P_RA_SUB_ITF_TABLE_REC.exempt_entered_amount;
721
722
723 --L_TRX_CLASS := P_RA_SUB_ITF_TABLE_REC.TRX_CLASS_CODE;
724 -- L_DIST_ID := P_RA_SUB_ITF_TABLE_REC.ACCTG_DIST_ID;
725
726 -- L_AL_THIRD_PARTY_ID := P_RA_SUB_ITF_TABLE_REC.BILLING_TRADING_PARTNER_ID;
727 -- L_AL_THIRD_PARTY_SUB_ID := P_RA_SUB_ITF_TABLE_REC.BILLING_TP_SITE_ID;
728
729
730 -- get ah_period_name
731 -- get_acctg_period_name(L_SET_OF_BOOKS_ID,
732 -- L_AH_ACCOUNTING_DATE,
733 -- P_AH_PERIOD_NAME);
734 --
735 -- P_TAX_EXTRACT_DECLARER_ID := ARP_TAX_EXTRACT.GET_DECLARER_ID
736 -- ( L_ORG_ID) ;
737
738 IF UPPER(P_SUMMARY_LEVEL) = 'TRANSACTION' THEN
739
740 -- IF P_RECONCILIATION_FLAG = 'Y' THEN --rm input parameter
741 -- IF P_RA_SUB_ITF_TABLE_REC.RECONCILIATION_FLAG = 'Y' THEN
742 -- Fetch the reconciliation amounts and add to the tax/taxable amounts
743 OPEN ROUNDING_AMTS_CURSOR (
744 P_TRX_ID,
745 P_REGISTER_TYPE,
746 P_TAX_CODE_ID );
747
748 FETCH ROUNDING_AMTS_CURSOR INTO
749 L_ROUNDING_TAX_AMT, L_ROUNDING_TAXABLE_AMT ;
750 CLOSE ROUNDING_AMTS_CURSOR;
751
752 P_INPUT_TAX_AMOUNT := P_INPUT_TAX_AMOUNT + nvl(L_ROUNDING_TAX_AMT,0);
753 P_INPUT_TAXABLE_AMOUNT:= P_INPUT_TAXABLE_AMOUNT +
754 nvl(L_ROUNDING_TAXABLE_AMT,0);
755
756 -- END IF;
757
758 L_TAXABLE_AMOUNT := L_TAXABLE_AMOUNT - nvl(L_EXEMPT_AMOUNT,0);
759
760 convert_amounts( P_CURRENCY_CODE,
761 P_EXCHANGE_RATE,
762 P_PRECISION,
763 P_MIN_ACCT_UNIT,
764 P_INPUT_TAX_AMOUNT,
765 P_INPUT_TAXABLE_AMOUNT,
766 0, i); --P_INPUT_EXEMPT_AMOUNT
767
768 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
769
770 L_TAXABLE_AMOUNT := L_TAXABLE_AMOUNT - nvl(L_EXEMPT_AMOUNT,0);
771
772 convert_amounts(
773 P_CURRENCY_CODE,
774 P_EXCHANGE_RATE,
775 P_PRECISION,
776 P_MIN_ACCT_UNIT,
777 P_INPUT_TAX_AMOUNT,
778 P_INPUT_TAXABLE_AMOUNT,
779 0,i); --P_INPUT_EXEMPT_AMOUNT,
780
781
782 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
783 /*
784 IF L_EXEMPT_AMOUNT IS NOT NULL THEN
785 P_EXEMPT_ENTERED_AMOUNT :=
786 arpcurr.CurrRound(L_EXEMPT_AMOUNT,L_CURRENCY_CODE);
787
788 IF P_TAXABLE_AMOUNT IS NOT NULL THEN
789 P_TAXABLE_EXEMPT_ENTERED_AMT :=
790 arpcurr.CurrRound((P_TAXABLE_AMOUNT + L_EXEMPT_AMOUNT),
791 L_CURRENCY_CODE);
792 END IF;
793
794 ELSE
795 P_EXEMPT_ENTERED_AMOUNT := 0;
796 P_TAXABLE_EXEMPT_ENTERED_AMT := P_TAXABLE_AMOUNT;
797 END IF;
798
799 IF P_EXEMPT_ENTERED_AMOUNT IS NOT NULL THEN
800 P_EXEMPT_ACCTD_AMOUNT :=
801 arpcurr.FUNCTIONAL_AMOUNT(
802 P_EXEMPT_ENTERED_AMOUNT,
803 L_CURRENCY_CODE,
804 L_EXCHANGE_RATE,
805 L_PRECISION,
806 L_MIN_ACCT_UNIT);
807 END IF;
808
809 IF P_TAXABLE_EXEMPT_ENTERED_AMT IS NOT NULL THEN
810 P_TAXABLE_EXEMPT_ACCTD_AMT :=
811 arpcurr.FUNCTIONAL_AMOUNT(
812 P_TAXABLE_EXEMPT_ENTERED_AMT,
813 L_CURRENCY_CODE,
814 L_EXCHANGE_RATE,
815 L_PRECISION,
816 L_MIN_ACCT_UNIT);
817 END IF;
818
819 -- Round off the amounts to the precision for the functional currency.
820 -- Modified the code such that taxable_accounted_amount is rounded
821 -- before taxable amount is rounded (BUG3123264).
822
823 IF P_TAXABLE_AMOUNT IS NOT NULL THEN
827 L_EXCHANGE_RATE,
824 P_TAXABLE_ACCOUNTED_AMOUNT :=
825 arpcurr.FUNCTIONAL_AMOUNT(P_TAXABLE_AMOUNT,
826 L_CURRENCY_CODE,
828 L_PRECISION,
829 L_MIN_ACCT_UNIT);
830 END IF;
831
832
833 IF P_TAXABLE_AMOUNT IS NOT NULL THEN
834 P_TAXABLE_AMOUNT :=
835 arpcurr.CurrRound(P_TAXABLE_AMOUNT,L_CURRENCY_CODE);
836 END IF;
837 */
838 NULL;
839
840 END IF; -- P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION'
841
842 IF (g_level_procedure >= g_current_runtime_level ) THEN
843 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.APP_FUNCTIONAL_AMOUNTS.END',
844 'ZX_AR_ACTG_POPULATE_PKG: APP_FUNCTIONAL_AMOUNTS(-)');
845 END IF;
846
847 EXCEPTION
848 WHEN OTHERS THEN
849 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
850 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
851 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
852 FND_MSG_PUB.Add;
853 IF (g_level_unexpected >= g_current_runtime_level ) THEN
854 FND_LOG.STRING(g_level_unexpected,
855 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.APP_FUNCTIONAL_AMOUNTS',
856 g_error_buffer);
857 END IF;
858
859 G_RETCODE := 2;
860
861 END APP_FUNCTIONAL_AMOUNTS;
862
863 PROCEDURE get_accounting_info (P_TRX_ID IN NUMBER,
864 P_TRX_LINE_ID IN NUMBER,
865 P_TAX_LINE_ID IN NUMBER,
866 -- P_ENTITY_ID IN NUMBER,
867 P_EVENT_ID IN NUMBER,
868 P_AE_HEADER_ID IN NUMBER,
869 P_ACTG_SOURCE_ID IN NUMBER,
870 P_BALANCING_SEGMENT IN VARCHAR2,
871 P_ACCOUNTING_SEGMENT IN VARCHAR2,
872 P_SUMMARY_LEVEL IN VARCHAR2,
873 P_TRX_CLASS IN VARCHAR2,
874 i IN binary_integer) IS
875
876 BEGIN
877
878 IF p_trx_class in ('INV','CM','DM') THEN
879 inv_segment_info (P_TRX_ID,
880 P_TRX_LINE_ID,
881 P_TAX_LINE_ID,
882 -- P_ENTITY_ID,
883 P_EVENT_ID,
884 P_AE_HEADER_ID,
885 P_ACTG_SOURCE_ID,
886 P_BALANCING_SEGMENT,
887 P_ACCOUNTING_SEGMENT,
888 P_SUMMARY_LEVEL,
889 P_TRX_CLASS,
890 i);
891
892 ELSIF p_trx_class IN ('APP','EDISC','UNEDISC','ADJ','FINCHRG',
893 'MISC_CASH_RECEIPT') THEN
894 other_trx_segment_info(P_TRX_ID,
895 P_TRX_LINE_ID,
896 P_TAX_LINE_ID,
897 -- P_ENTITY_ID,
898 P_EVENT_ID,
899 P_AE_HEADER_ID,
900 P_ACTG_SOURCE_ID,
901 P_BALANCING_SEGMENT,
902 P_ACCOUNTING_SEGMENT,
903 P_SUMMARY_LEVEL,
904 P_TRX_CLASS,
905 i);
906 END IF;
907 END get_accounting_info;
908
909 PROCEDURE get_accounting_amounts(P_TRX_ID IN NUMBER,
910 P_TRX_LINE_ID IN NUMBER,
911 P_TAX_LINE_ID IN NUMBER,
912 -- P_ENTITY_ID IN NUMBER,
913 P_EVENT_ID IN NUMBER,
914 P_AE_HEADER_ID IN NUMBER,
915 P_ACTG_SOURCE_ID IN NUMBER,
916 P_SUMMARY_LEVEL IN VARCHAR2,
917 P_TRX_CLASS IN VARCHAR2,
918 P_LEDGER_ID IN NUMBER,
919 i IN binary_integer) IS
920 BEGIN
921 IF p_trx_class in ('INV','CM','DM') THEN
922 inv_actg_amounts(P_TRX_ID,
923 P_TRX_LINE_ID,
924 P_TAX_LINE_ID,
925 -- P_ENTITY_ID,
926 P_EVENT_ID,
927 P_AE_HEADER_ID,
928 P_ACTG_SOURCE_ID,
929 P_SUMMARY_LEVEL,
930 P_TRX_CLASS,
931 P_LEDGER_ID,
932 i);
933 ELSIF p_trx_class IN ('APP','EDISC','UNEDISC','ADJ','FINCHRG',
934 'MISC_CASH_RECEIPT') THEN
935 other_trx_actg_amounts(P_TRX_ID,
936 P_TRX_LINE_ID,
937 P_TAX_LINE_ID,
938 -- P_ENTITY_ID,
939 P_EVENT_ID,
940 P_AE_HEADER_ID,
941 P_ACTG_SOURCE_ID,
942 P_SUMMARY_LEVEL,
943 P_TRX_CLASS,
944 P_LEDGER_ID,
948 END get_accounting_amounts;
945 i);
946 END IF;
947
949
950 /*PROCEDURE inv_segment_info (P_TRX_ID IN NUMBER,
951 P_TRX_LINE_ID IN NUMBER,
952 P_TAX_LINE_ID IN NUMBER,
953 -- P_ENTITY_ID IN NUMBER,
954 P_EVENT_ID IN NUMBER,
955 P_AE_HEADER_ID IN NUMBER,
956 P_TAX_DIST_ID IN NUMBER,
957 P_BALANCING_SEGMENT IN VARCHAR2,
958 P_ACCOUNTING_SEGMENT IN VARCHAR2,
959 P_SUMMARY_LEVEL IN VARCHAR2,
960 P_TRX_CLASS IN VARCHAR2,
961 i IN binary_integer) IS */
962 PROCEDURE inv_segment_info (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_ACTG_SOURCE_ID IN NUMBER,
969 P_BALANCING_SEGMENT IN VARCHAR2,
970 P_ACCOUNTING_SEGMENT IN VARCHAR2,
971 P_SUMMARY_LEVEL IN VARCHAR2,
972 P_TRX_CLASS IN VARCHAR2,
973 i IN binary_integer) IS
974 CURSOR trx_ccid (c_trx_id number, c_event_id number, c_ae_header_id number) IS
975 SELECT
976 ael.code_combination_id
977 FROM ra_cust_trx_line_gl_dist_all gl_dist,
978 xla_distribution_links lnk,
979 xla_ae_lines ael
980 WHERE gl_dist.customer_trx_id = c_trx_id
981 AND gl_dist.account_class = 'LINE'
982 AND lnk.application_id = 222
983 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
984 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
985 AND lnk.ae_header_id = ael.ae_header_id
986 AND lnk.ae_line_num = ael.ae_line_num
987 AND lnk.event_id = c_event_id
988 AND lnk.ae_header_id = c_ae_header_id
989 AND rownum =1;
990
991 CURSOR trx_line_ccid (c_trx_id number, c_trx_line_id number, c_event_id number, c_ae_header_id NUMBER) IS
992 SELECT
993 ael.code_combination_id
994 FROM ra_cust_trx_line_gl_dist_all gl_dist,
995 xla_distribution_links lnk,
996 xla_ae_lines ael
997 WHERE gl_dist.customer_trx_id = c_trx_id
998 AND gl_dist.customer_trx_line_id = c_trx_line_id
999 AND gl_dist.account_class = 'LINE'
1000 AND lnk.application_id = 222
1001 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1002 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1003 AND lnk.ae_header_id = ael.ae_header_id
1004 AND lnk.ae_line_num = ael.ae_line_num
1005 AND lnk.event_id = c_event_id
1006 AND lnk.ae_header_id = c_ae_header_id
1007 AND rownum =1;
1008
1009
1010 -- For transavtion distribution level code combination id select in the build SQL
1011 -- The following query can be removed ----
1012
1013 CURSOR trx_dist_ccid (c_trx_id NUMBER, c_trx_line_id NUMBER, c_event_id NUMBER, c_ae_header_id NUMBER) IS
1014 SELECT
1015 ael.code_combination_id
1016 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1017 xla_distribution_links lnk,
1018 xla_ae_lines ael
1019 WHERE gl_dist.customer_trx_id = c_trx_id
1020 AND gl_dist.customer_trx_line_id = c_trx_line_id
1021 AND gl_dist.account_class = 'LINE'
1022 AND lnk.application_id = 222
1023 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1024 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1025 AND lnk.ae_header_id = ael.ae_header_id
1026 AND lnk.ae_line_num = ael.ae_line_num
1027 AND lnk.event_id = c_event_id
1028 AND lnk.ae_header_id = c_ae_header_id
1029 AND rownum =1;
1030
1031
1032 CURSOR tax_ccid (c_trx_id number, c_event_id number, c_ae_header_id number) IS
1033 SELECT
1034 ael.code_combination_id
1035 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1036 xla_distribution_links lnk,
1037 xla_ae_lines ael
1038 WHERE gl_dist.customer_trx_id = c_trx_id
1039 AND gl_dist.account_class = 'TAX'
1040 AND lnk.application_id = 222
1041 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1042 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1043 AND lnk.ae_header_id = ael.ae_header_id
1044 AND lnk.ae_line_num = ael.ae_line_num
1045 AND lnk.event_id = c_event_id
1046 AND lnk.ae_header_id = c_ae_header_id
1047 AND rownum =1;
1048
1049 CURSOR tax_line_ccid (c_trx_id number, c_tax_line_id NUMBER, c_event_id number, c_ae_header_id number) IS
1050 SELECT
1051 ael.code_combination_id
1052 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1053 xla_distribution_links lnk,
1054 xla_ae_lines ael
1055 WHERE gl_dist.customer_trx_id = c_trx_id
1056 AND gl_dist.customer_trx_line_id = c_tax_line_id
1057 AND gl_dist.account_class = 'TAX'
1058 AND lnk.application_id = 222
1059 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1060 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1061 AND lnk.ae_header_id = ael.ae_header_id
1062 AND lnk.ae_line_num = ael.ae_line_num
1063 AND lnk.event_id = c_event_id
1064 AND lnk.ae_header_id = c_ae_header_id
1065 AND rownum =1;
1066
1067
1068 -- For transavtion distribution level code combination id select in the build SQL
1069 -- The following query can be removed ----
1070
1071 CURSOR tax_dist_ccid (c_trx_id NUMBER, c_tax_line_id NUMBER, c_tax_line_dist_id NUMBER,
1072 c_event_id number, c_ae_header_id number) IS
1073 SELECT
1074 ael.code_combination_id
1075 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1076 xla_distribution_links lnk,
1077 xla_ae_lines ael
1078 WHERE gl_dist.customer_trx_id = c_trx_id
1079 AND gl_dist.customer_trx_line_id = c_tax_line_id
1080 AND gl_dist.cust_trx_line_gl_dist_id = c_tax_line_dist_id
1081 AND gl_dist.account_class = 'TAX'
1082 AND lnk.application_id = 222
1083 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1084 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1085 AND lnk.ae_header_id = ael.ae_header_id
1086 AND lnk.ae_line_num = ael.ae_line_num
1087 AND lnk.event_id = c_event_id
1088 AND lnk.ae_header_id = c_ae_header_id
1089 AND rownum =1;
1090
1091 L_BAL_SEG_VAL VARCHAR2(240);
1092 L_ACCT_SEG_VAL VARCHAR2(240);
1093 L_SQL_STATEMENT1 VARCHAR2(1000);
1094 L_SQL_STATEMENT2 VARCHAR2(1000);
1095 l_ccid number;
1096 BEGIN
1097
1098 GT_TRX_ARAP_BALANCING_SEGMENT(i) := NULL;
1099 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := NULL;
1100 GT_TRX_TAXABLE_BAL_SEG(i) := NULL;
1101 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := NULL;
1102 GT_TRX_TAX_BALANCING_SEGMENT(i) := NULL;
1103 GT_TRX_TAX_NATURAL_ACCOUNT(i) := NULL;
1104
1105
1106 L_BAL_SEG_VAL := '';
1107 L_ACCT_SEG_VAL := '';
1108
1109 L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||
1110 ' FROM GL_CODE_COMBINATIONS '||
1111 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
1112
1113 L_SQL_STATEMENT2 := ' SELECT '||P_ACCOUNTING_SEGMENT ||
1114 ' FROM GL_CODE_COMBINATIONS '||
1115 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
1116
1117
1118 IF P_SUMMARY_LEVEL = 'TRANSACTION' THEN
1119 OPEN trx_ccid (p_trx_id, p_event_id, p_ae_header_id);
1120 LOOP
1121 FETCH trx_ccid INTO l_ccid;
1122 EXIT WHEN trx_ccid%NOTFOUND;
1123
1124 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1125 USING l_ccid;
1126
1127 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1128 USING l_ccid;
1129
1130 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
1131 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
1132 ELSE
1133 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
1134 NULL;
1135 ELSE
1136 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
1137 ||','||L_BAL_SEG_VAL;
1138 END IF;
1139 END IF;
1140
1141
1142 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
1143 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1144 ELSE
1145 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1146 NULL;
1147 ELSE
1148 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
1149 ||','||L_ACCT_SEG_VAL;
1150 END IF;
1151 END IF;
1152
1153 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
1154 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
1155 END LOOP;
1156
1157
1158 OPEN tax_ccid (p_trx_id, p_event_id, p_ae_header_id);
1159 LOOP
1160 FETCH tax_ccid INTO l_ccid;
1161 EXIT WHEN tax_ccid%NOTFOUND;
1162
1163 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1164 USING l_ccid;
1165
1166 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1167 USING l_ccid;
1168
1169 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
1170 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
1171 ELSE
1172 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
1173 NULL;
1174 ELSE
1175 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
1176 ||','||L_BAL_SEG_VAL;
1177 END IF;
1178 END IF;
1179
1180
1181 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
1182 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1183 ELSE
1184 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1185 NULL;
1186 ELSE
1187 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
1188 ||','||L_ACCT_SEG_VAL;
1189 END IF;
1190 END IF;
1191
1192 END LOOP;
1193
1194 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
1195 OPEN trx_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
1196 LOOP
1197 FETCH trx_line_ccid INTO l_ccid;
1198 EXIT WHEN trx_line_ccid%NOTFOUND;
1199
1200 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1201 USING l_ccid;
1202
1203 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1204 USING l_ccid;
1205
1206 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
1207 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
1208 ELSE
1209 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
1210 NULL;
1211 ELSE
1212 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
1213 ||','||L_BAL_SEG_VAL;
1214 END IF;
1215 END IF;
1216
1217
1218 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
1219 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1220 ELSE
1221 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1222 NULL;
1223 ELSE
1224 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
1225 ||','||L_ACCT_SEG_VAL;
1226 END IF;
1227 END IF;
1228
1229 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
1230 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
1231 END LOOP;
1232
1233
1234 OPEN tax_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
1235 LOOP
1236 FETCH tax_line_ccid INTO l_ccid;
1237 EXIT WHEN tax_line_ccid%NOTFOUND;
1238
1239 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1240 USING l_ccid;
1241
1242 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1243 USING l_ccid;
1244
1245 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
1246 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
1247 ELSE
1248 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
1249 NULL;
1250 ELSE
1251 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
1252 ||','||L_BAL_SEG_VAL;
1253 END IF;
1254 END IF;
1255
1256
1257 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
1258 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1259 ELSE
1260 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1261 NULL;
1262 ELSE
1263 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
1264 ||','||L_ACCT_SEG_VAL;
1265 END IF;
1266 END IF;
1267
1268 END LOOP;
1269
1270
1271 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
1272 OPEN trx_dist_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
1273 LOOP
1274 FETCH trx_dist_ccid INTO l_ccid;
1275 EXIT WHEN trx_dist_ccid%NOTFOUND;
1276
1277 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1278 USING l_ccid;
1279
1280 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1281 USING l_ccid;
1282
1283 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
1284 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
1285 ELSE
1286 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
1287 NULL;
1288 ELSE
1289 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
1290 ||','||L_BAL_SEG_VAL;
1291 END IF;
1292 END IF;
1293
1294
1295 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
1296 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1297 ELSE
1298 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1299 NULL;
1300 ELSE
1301 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
1302 ||','||L_ACCT_SEG_VAL;
1303 END IF;
1304 END IF;
1305
1306 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
1307 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
1308 END LOOP;
1309
1310
1311 OPEN tax_dist_ccid (p_trx_id, p_tax_line_id, P_ACTG_SOURCE_ID, p_event_id, p_ae_header_id);
1312 LOOP
1313 FETCH tax_ccid INTO l_ccid;
1314 EXIT WHEN tax_ccid%NOTFOUND;
1315
1316 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1317 USING l_ccid;
1318
1319 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1320 USING l_ccid;
1321
1322 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
1323 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
1324 ELSE
1325 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
1326 NULL;
1327 ELSE
1328 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
1329 ||','||L_BAL_SEG_VAL;
1330 END IF;
1331 END IF;
1332
1333
1334 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
1335 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1336 ELSE
1337 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1338 NULL;
1339 ELSE
1340 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
1341 ||','||L_ACCT_SEG_VAL;
1342 END IF;
1343 END IF;
1344
1345 END LOOP;
1346 END IF; -- Summary Level
1347 END inv_segment_info;
1348
1349
1350
1351 /*PROCEDURE inv_actg_amounts(P_TRX_ID IN NUMBER,
1352 P_TRX_LINE_ID IN NUMBER,
1353 P_TAX_LINE_ID IN NUMBER,
1354 -- P_ENTITY_ID IN NUMBER,
1355 P_EVENT_ID IN NUMBER,
1356 P_AE_HEADER_ID IN NUMBER,
1357 P_TAX_DIST_ID IN NUMBER,
1358 P_SUMMARY_LEVEL IN VARCHAR2,
1359 P_TRX_CLASS IN VARCHAR2,
1360 i IN binary_integer) IS */
1361 PROCEDURE inv_actg_amounts(P_TRX_ID IN NUMBER,
1362 P_TRX_LINE_ID IN NUMBER,
1363 P_TAX_LINE_ID IN NUMBER,
1364 -- P_ENTITY_ID IN NUMBER,
1365 P_EVENT_ID IN NUMBER,
1366 P_AE_HEADER_ID IN NUMBER,
1367 P_ACTG_SOURCE_ID IN NUMBER,
1368 P_SUMMARY_LEVEL IN VARCHAR2,
1369 P_TRX_CLASS IN VARCHAR2,
1370 P_LEDGER_ID IN NUMBER,
1371 i IN binary_integer) IS
1372 -- Transaction Header Level
1373
1374 CURSOR taxable_amount_hdr (c_trx_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER, c_ledger_id NUMBER) IS
1375 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1376 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1377 xla_distribution_links lnk,
1378 xla_ae_headers aeh,
1379 xla_ae_lines ael
1380 WHERE gl_dist.customer_trx_id = c_trx_id
1381 AND lnk.application_id = 222
1382 AND gl_dist.account_class = 'REV'
1383 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1384 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1385 AND lnk.ae_header_id = c_ae_header_id
1386 AND lnk.event_id = c_event_id
1387 AND lnk.ae_line_num = ael.ae_line_num
1388 AND aeh.ae_header_id = ael.ae_header_id
1389 AND aeh.ledger_id = c_ledger_id;
1390
1391
1392
1393 CURSOR tax_amount_hdr (c_trx_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER,c_ledger_id NUMBER) IS
1394 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1395 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1396 xla_distribution_links lnk,
1397 xla_ae_headers aeh,
1398 xla_ae_lines ael
1399 WHERE gl_dist.customer_trx_id = c_trx_id
1400 AND gl_dist.account_class = 'TAX'
1401 AND lnk.application_id = 222
1402 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1403 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1404 AND lnk.ae_header_id = c_ae_header_id
1405 AND lnk.event_id = c_event_id
1406 AND lnk.ae_line_num = ael.ae_line_num
1407 AND aeh.ae_header_id = ael.ae_header_id
1408 AND aeh.ledger_id = c_ledger_id;
1409
1410
1411
1412 -- Transaction Line Level
1413
1414 CURSOR taxable_amount_line (c_trx_id NUMBER,c_trx_line_id NUMBER, c_ae_header_id NUMBER,
1415 c_event_id NUMBER, c_ledger_id NUMBER) IS
1416 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1417 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1418 xla_distribution_links lnk,
1419 xla_ae_headers aeh,
1420 xla_ae_lines ael
1421 WHERE gl_dist.customer_trx_id = c_trx_id
1422 AND gl_dist.customer_trx_line_id = c_trx_line_id
1423 AND gl_dist.account_class = 'REV'
1424 AND lnk.application_id = 222
1425 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1426 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1427 AND lnk.ae_header_id = c_ae_header_id
1428 AND lnk.event_id = c_event_id
1429 AND lnk.ae_line_num = ael.ae_line_num
1430 AND aeh.ae_header_id = ael.ae_header_id
1431 AND aeh.ledger_id = c_ledger_id;
1432
1433
1434
1435 CURSOR tax_amount_line (c_trx_id NUMBER,c_tax_line_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER, c_ledger_id NUMBER) IS
1436 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1437 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1438 xla_distribution_links lnk,
1439 xla_ae_headers aeh,
1440 xla_ae_lines ael
1441 WHERE gl_dist.customer_trx_id = c_trx_id
1442 AND gl_dist.customer_trx_line_id = c_tax_line_id
1443 AND gl_dist.account_class = 'TAX'
1444 AND lnk.application_id = 222
1445 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1446 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1447 AND lnk.event_id = c_event_id
1448 AND lnk.ae_header_id = c_ae_header_id
1449 AND lnk.ae_line_num = ael.ae_line_num
1450 AND aeh.ae_header_id = ael.ae_header_id
1451 AND aeh.ledger_id = c_ledger_id;
1452
1453
1454 -- Transaction Distribution Level
1455
1456
1457
1458 CURSOR tax_amount_dist ( c_trx_id NUMBER,c_tax_line_id NUMBER, c_tax_dist_id NUMBER, c_ae_header_id NUMBER,
1459 c_event_id NUMBER, c_ledger_id NUMBER) IS
1460 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1461 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1462 xla_distribution_links lnk,
1463 xla_ae_headers aeh,
1464 xla_ae_lines ael
1465 WHERE gl_dist.customer_trx_id = c_trx_id
1466 AND gl_dist.customer_trx_line_id = c_tax_line_id
1467 AND gl_dist.cust_trx_line_gl_dist_id = c_tax_dist_id
1468 AND gl_dist.account_class = 'TAX'
1469 AND lnk.application_id = 222
1470 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1471 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1472 AND lnk.ae_header_id = ael.ae_header_id
1473 AND lnk.ae_line_num = ael.ae_line_num
1474 AND lnk.event_id = c_event_id
1475 AND lnk.ae_header_id = c_ae_header_id
1476 AND aeh.ae_header_id = ael.ae_header_id
1477 AND aeh.ledger_id = c_ledger_id;
1478
1479
1480
1481 CURSOR taxable_amount_dist (c_trx_id NUMBER,c_trx_line_id NUMBER, c_ae_header_id NUMBER,
1482 c_event_id NUMBER, c_ledger_id NUMBER) IS
1483 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1484 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1485 xla_distribution_links lnk,
1486 xla_ae_headers aeh,
1487 xla_ae_lines ael
1488 WHERE gl_dist.customer_trx_id = c_trx_id
1489 AND gl_dist.customer_trx_line_id = c_trx_line_id
1490 AND gl_dist.account_class = 'LINE'
1491 AND lnk.application_id = 222
1492 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1493 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1494 AND lnk.ae_header_id = c_ae_header_id
1495 AND lnk.event_id = c_event_id
1496 AND lnk.ae_line_num = ael.ae_line_num
1497 AND aeh.ae_header_id = ael.ae_header_id
1498 AND aeh.ledger_id = c_ledger_id;
1499
1500
1501
1502
1503 BEGIN
1504
1505 IF p_summary_level = 'TRANSACTION' THEN
1506 OPEN taxable_amount_hdr(p_trx_id , p_ae_header_id , p_event_id,p_ledger_id );
1507 FETCH taxable_amount_hdr INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
1508 -- EXIT WHEN taxable_amount_hdr%NOTFOUND;
1509 CLOSE taxable_amount_hdr;
1510
1511 OPEN tax_amount_hdr(p_trx_id , p_ae_header_id , p_event_id,p_ledger_id);
1512 FETCH tax_amount_hdr INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
1513 -- EXIT WHEN tax_amount_hdr%NOTFOUND;
1514 CLOSE tax_amount_hdr;
1515 ELSIF p_summary_level = 'TRANSACTION_LINE' THEN
1516 OPEN taxable_amount_line(p_trx_id ,p_trx_line_id, p_ae_header_id , p_event_id,p_ledger_id);
1517 FETCH taxable_amount_line INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
1518 -- EXIT WHEN taxable_amount_line%NOTFOUND;
1519 CLOSE taxable_amount_line;
1520
1521 OPEN tax_amount_line(p_trx_id , p_trx_line_id, p_ae_header_id , p_event_id,p_ledger_id);
1522 FETCH tax_amount_line INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
1523 -- EXIT WHEN tax_amount_line%NOTFOUND;
1524 CLOSE tax_amount_line;
1525
1526 ELSIF p_summary_level = 'TRANSACTION_DISTRIBUTION' THEN
1527 OPEN taxable_amount_dist(P_ACTG_SOURCE_ID ,p_trx_line_id,p_ae_header_id , p_event_id,p_ledger_id);
1528 FETCH taxable_amount_dist INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
1529 -- EXIT WHEN taxable_amount_dist%NOTFOUND;
1530 CLOSE taxable_amount_dist;
1531
1532 OPEN tax_amount_dist(p_trx_id ,p_tax_line_id,P_ACTG_SOURCE_ID, p_ae_header_id , p_event_id,p_ledger_id);
1533 FETCH tax_amount_dist INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
1534 -- EXIT WHEN tax_amount_dist%NOTFOUND;
1535 CLOSE tax_amount_dist;
1536 END IF;
1537
1538 END inv_actg_amounts;
1539
1540
1541 PROCEDURE other_trx_segment_info(P_TRX_ID IN NUMBER,
1542 P_TRX_LINE_ID IN NUMBER,
1543 P_TAX_LINE_ID IN NUMBER,
1544 -- P_ENTITY_ID IN NUMBER,
1545 P_EVENT_ID IN NUMBER,
1546 P_AE_HEADER_ID IN NUMBER,
1547 P_ACTG_SOURCE_ID IN NUMBER,
1548 P_BALANCING_SEGMENT IN VARCHAR2,
1549 P_ACCOUNTING_SEGMENT IN VARCHAR2,
1550 P_SUMMARY_LEVEL IN VARCHAR2,
1551 P_TRX_CLASS IN VARCHAR2,
1552 i IN binary_integer) IS
1553
1554 CURSOR trx_ccid (c_actg_source_id number, c_event_id number, c_ae_header_id number) IS
1555 SELECT
1556 ael.code_combination_id
1557 FROM ar_distributions_all dist,
1558 xla_distribution_links lnk,
1559 xla_ae_lines ael
1560 WHERE dist.line_id = p_actg_source_id
1561 AND lnk.application_id = 222
1562 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1563 AND lnk.source_distribution_id_num_1 = dist.line_id
1564 AND lnk.ae_header_id = ael.ae_header_id
1565 AND lnk.ae_line_num = ael.ae_line_num
1566 AND lnk.event_id = c_event_id
1567 AND lnk.ae_header_id = c_ae_header_id
1568 AND rownum =1;
1569
1570 CURSOR trx_dist_ccid (c_actg_source_id number, c_event_id number, c_ae_header_id number) IS
1571 SELECT ael.code_combination_id
1572 FROM ar_distributions_all dist,
1573 ar_distributions_all taxdist,
1574 xla_distribution_links lnk,
1575 xla_ae_lines ael
1576 WHERE taxdist.line_id = p_actg_source_id
1577 AND NVL(dist.source_table,'X') = NVL(taxdist.source_table_secondary,'X')
1578 AND dist.tax_link_id = taxdist.tax_link_id
1579 AND dist.source_id = taxdist.source_id
1580 AND lnk.source_distribution_id_num_1 = dist.line_id
1581 AND lnk.application_id = 222
1582 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1583 AND lnk.ae_header_id = ael.ae_header_id
1584 AND lnk.ae_line_num = ael.ae_line_num
1585 AND lnk.event_id = c_event_id
1586 AND lnk.ae_header_id = c_ae_header_id
1587 AND rownum =1;
1588
1589 CURSOR tax_ccid (c_actg_source_id number, c_event_id number, c_ae_header_id number) IS
1590 SELECT ael.code_combination_id
1591 FROM ar_distributions_all dist,
1592 ar_distributions_all taxdist,
1593 xla_distribution_links lnk,
1594 xla_ae_lines ael
1595 WHERE dist.line_id = p_actg_source_id
1596 AND NVL(dist.source_table,'X') = NVL(taxdist.source_table_secondary,'X')
1597 AND dist.tax_link_id = taxdist.tax_link_id
1598 AND dist.source_id = taxdist.source_id
1599 AND lnk.source_distribution_id_num_1 = taxdist.line_id
1600 AND lnk.application_id = 222
1604 AND lnk.event_id = c_event_id
1601 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1602 AND lnk.ae_header_id = ael.ae_header_id
1603 AND lnk.ae_line_num = ael.ae_line_num
1605 AND lnk.ae_header_id = c_ae_header_id
1606 AND rownum =1;
1607
1608 CURSOR tax_dist_ccid (c_actg_source_id number, c_event_id number, c_ae_header_id number) IS
1609 SELECT
1610 ael.code_combination_id
1611 FROM ar_distributions_all taxdist,
1612 xla_distribution_links lnk,
1613 xla_ae_lines ael
1614 WHERE taxdist.line_id = p_actg_source_id
1615 AND lnk.application_id = 222
1616 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1617 AND lnk.source_distribution_id_num_1 = taxdist.line_id
1618 AND lnk.ae_header_id = ael.ae_header_id
1619 AND lnk.ae_line_num = ael.ae_line_num
1620 AND lnk.event_id = c_event_id
1621 AND lnk.ae_header_id = c_ae_header_id
1622 AND rownum =1;
1623
1624
1625
1626 L_BAL_SEG_VAL VARCHAR2(240);
1627 L_ACCT_SEG_VAL VARCHAR2(240);
1628 L_SQL_STATEMENT1 VARCHAR2(1000);
1629 L_SQL_STATEMENT2 VARCHAR2(1000);
1630 l_ccid number;
1631 BEGIN
1632
1633 GT_TRX_ARAP_BALANCING_SEGMENT(i) := NULL;
1634 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := NULL;
1635 GT_TRX_TAXABLE_BAL_SEG(i) := NULL;
1636 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := NULL;
1637 GT_TRX_TAX_BALANCING_SEGMENT(i) := NULL;
1638 GT_TRX_TAX_NATURAL_ACCOUNT(i) := NULL;
1639
1640
1641 L_BAL_SEG_VAL := '';
1642 L_ACCT_SEG_VAL := '';
1643
1644 L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||
1645 ' FROM GL_CODE_COMBINATIONS '||
1646 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
1647
1648 L_SQL_STATEMENT2 := ' SELECT '||P_ACCOUNTING_SEGMENT ||
1649 ' FROM GL_CODE_COMBINATIONS '||
1650 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
1651
1652
1653 IF P_SUMMARY_LEVEL = 'TRANSACTION' OR P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
1654
1655 OPEN trx_ccid (p_actg_source_id, p_event_id, p_ae_header_id);
1656 LOOP
1657 FETCH trx_ccid INTO l_ccid;
1658 EXIT WHEN trx_ccid%NOTFOUND;
1659
1660 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1661 USING l_ccid;
1662
1663 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1664 USING l_ccid;
1665
1666 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
1667 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
1668 ELSE
1669 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
1670 NULL;
1671 ELSE
1672 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
1673 ||','||L_BAL_SEG_VAL;
1674 END IF;
1675 END IF;
1676
1677
1678 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
1679 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1680 ELSE
1681 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1682 NULL;
1683 ELSE
1684 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
1685 ||','||L_ACCT_SEG_VAL;
1686 END IF;
1687 END IF;
1688
1689 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
1690 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
1691 END LOOP;
1692
1693
1694 OPEN tax_ccid (p_actg_source_id, p_event_id, p_ae_header_id);
1695 LOOP
1696 FETCH tax_ccid INTO l_ccid;
1697 EXIT WHEN tax_ccid%NOTFOUND;
1698
1699 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1700 USING l_ccid;
1701
1702 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1703 USING l_ccid;
1704
1705 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
1706 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
1707 ELSE
1708 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
1709 NULL;
1710 ELSE
1711 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
1712 ||','||L_BAL_SEG_VAL;
1713 END IF;
1714 END IF;
1715
1716
1717 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
1718 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1719 ELSE
1720 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1721 NULL;
1722 ELSE
1723 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
1724 ||','||L_ACCT_SEG_VAL;
1725 END IF;
1726 END IF;
1727
1728 END LOOP;
1729 /*
1730 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
1731 OPEN trx_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
1732 LOOP
1733 FETCH trx_line_ccid INTO l_ccid;
1734 EXIT WHEN trx_line_ccid%NOTFOUND;
1735
1736 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1737 USING l_ccid;
1738
1739 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1740 USING l_ccid;
1741
1742 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
1743 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
1744 ELSE
1745 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
1746 NULL;
1747 ELSE
1748 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
1749 ||','||L_BAL_SEG_VAL;
1750 END IF;
1751 END IF;
1752
1753
1754 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
1755 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1756 ELSE
1757 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1758 NULL;
1759 ELSE
1760 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
1761 ||','||L_ACCT_SEG_VAL;
1762 END IF;
1763 END IF;
1764
1765 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
1766 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
1767 END LOOP;
1768
1769
1770 OPEN tax_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
1771 LOOP
1772 FETCH tax_line_ccid INTO l_ccid;
1773 EXIT WHEN tax_line_ccid%NOTFOUND;
1774
1775 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1776 USING l_ccid;
1777
1778 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1779 USING l_ccid;
1780
1781 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
1782 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
1783 ELSE
1784 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
1785 NULL;
1786 ELSE
1787 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
1788 ||','||L_BAL_SEG_VAL;
1789 END IF;
1790 END IF;
1791
1792
1793 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
1794 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1795 ELSE
1796 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1797 NULL;
1798 ELSE
1799 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
1800 ||','||L_ACCT_SEG_VAL;
1801 END IF;
1802 END IF;
1803
1804 END LOOP;
1805 */
1806
1807 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
1808 OPEN trx_dist_ccid (p_actg_source_id, p_event_id, p_ae_header_id);
1809 LOOP
1810 FETCH trx_dist_ccid INTO l_ccid;
1811 EXIT WHEN trx_dist_ccid%NOTFOUND;
1812
1813 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1814 USING l_ccid;
1815
1816 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1817 USING l_ccid;
1818
1819 IF GT_TRX_TAXABLE_BAL_SEG(i) IS NULL then
1820 GT_TRX_TAXABLE_BAL_SEG(i) := L_BAL_SEG_VAL;
1821 ELSE
1822 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(i),L_BAL_SEG_VAL) > 0 THEN
1823 NULL;
1824 ELSE
1825 GT_TRX_TAXABLE_BAL_SEG(i) := GT_TRX_TAXABLE_BAL_SEG(i)
1826 ||','||L_BAL_SEG_VAL;
1827 END IF;
1828 END IF;
1829
1830
1831 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) IS NULL then
1832 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1833 ELSE
1834 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1835 NULL;
1836 ELSE
1837 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
1838 ||','||L_ACCT_SEG_VAL;
1839 END IF;
1840 END IF;
1841
1842 GT_TRX_ARAP_BALANCING_SEGMENT(i) := GT_TRX_TAXABLE_BAL_SEG(i);
1843 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(i);
1844 END LOOP;
1845
1846
1847 OPEN tax_dist_ccid (p_actg_source_id, p_event_id, p_ae_header_id);
1848 LOOP
1849 FETCH tax_ccid INTO l_ccid;
1850 EXIT WHEN tax_ccid%NOTFOUND;
1851
1852 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1853 USING l_ccid;
1854
1855 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1856 USING l_ccid;
1857
1858 IF GT_TRX_TAX_BALANCING_SEGMENT(i) IS NULL then
1859 GT_TRX_TAX_BALANCING_SEGMENT(i) := L_BAL_SEG_VAL;
1860 ELSE
1861 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(i),L_BAL_SEG_VAL) > 0 THEN
1862 NULL;
1863 ELSE
1864 GT_TRX_TAX_BALANCING_SEGMENT(i) := GT_TRX_TAX_BALANCING_SEGMENT(i)
1865 ||','||L_BAL_SEG_VAL;
1866 END IF;
1867 END IF;
1868
1869
1870 IF GT_TRX_TAX_NATURAL_ACCOUNT(i) IS NULL then
1871 GT_TRX_TAX_NATURAL_ACCOUNT(i) := L_ACCT_SEG_VAL;
1872 ELSE
1873 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(i),L_BAL_SEG_VAL) > 0 THEN
1874 NULL;
1875 ELSE
1876 GT_TRX_TAX_NATURAL_ACCOUNT(i) := GT_TRX_TAX_NATURAL_ACCOUNT(i)
1877 ||','||L_ACCT_SEG_VAL;
1878 END IF;
1879 END IF;
1880
1881 END LOOP;
1882 END IF; -- Summary Level
1883 END other_trx_segment_info;
1884
1885
1886
1887 PROCEDURE other_trx_actg_amounts(P_TRX_ID IN NUMBER,
1888 P_TRX_LINE_ID IN NUMBER,
1889 P_TAX_LINE_ID IN NUMBER,
1890 -- P_ENTITY_ID IN NUMBER,
1891 P_EVENT_ID IN NUMBER,
1892 P_AE_HEADER_ID IN NUMBER,
1893 P_ACTG_SOURCE_ID IN NUMBER,
1894 P_SUMMARY_LEVEL IN VARCHAR2,
1895 P_TRX_CLASS IN VARCHAR2,
1896 P_LEDGER_ID IN NUMBER,
1897 i IN binary_integer) IS
1898 -- Transaction Header Level
1899 CURSOR taxable_amount_hdr (c_actg_source_id NUMBER, c_ae_header_id NUMBER,
1900 c_event_id NUMBER,c_ledger_id NUMBER) IS
1901 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1902 FROM ar_distributions_all dist,
1903 xla_distribution_links lnk,
1904 xla_ae_headers aeh,
1905 xla_ae_lines ael
1906 WHERE dist.line_id = c_actg_source_id
1907 AND lnk.application_id = 222
1908 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1909 AND lnk.source_distribution_id_num_1 = dist.line_id
1910 AND lnk.ae_header_id = c_ae_header_id
1911 AND lnk.event_id = c_event_id
1912 AND lnk.ae_line_num = ael.ae_line_num
1913 AND aeh.ae_header_id = ael.ae_header_id
1914 AND aeh.ledger_id = c_ledger_id;
1915
1916 CURSOR tax_amount_hdr (c_actg_source_id NUMBER, c_ae_header_id NUMBER,
1917 c_event_id NUMBER,c_ledger_id NUMBER) IS
1918 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1919 FROM AR_DISTRIBUTIONS_ALL dist,
1920 AR_DISTRIBUTIONS_ALL taxdist,
1921 xla_distribution_links lnk,
1922 xla_ae_headers aeh,
1923 xla_ae_lines ael
1924 WHERE dist.line_id = c_actg_source_id
1925 AND taxdist.tax_link_id = dist.tax_link_id
1926 AND NVL(taxdist.source_type_secondary,'X') = NVL(dist.source_type,'X')
1927 AND taxdist.source_id = dist.source_id
1928 AND lnk.source_distribution_id_num_1 = taxdist.line_id
1929 AND lnk.application_id = 222
1930 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1931 AND lnk.ae_header_id = c_ae_header_id
1932 AND lnk.event_id = c_event_id
1933 AND lnk.ae_line_num = ael.ae_line_num
1934 AND aeh.ae_header_id = ael.ae_header_id
1935 AND aeh.ledger_id = c_ledger_id;
1936
1937
1938 -- Transaction Distribution Level
1939
1940 CURSOR taxable_amount_dist (c_actg_source_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER,c_ledger_id NUMBER) IS
1941 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1942 FROM AR_DISTRIBUTIONS_ALL dist,
1943 AR_DISTRIBUTIONS_ALL taxdist,
1944 xla_distribution_links lnk,
1945 xla_ae_headers aeh,
1946 xla_ae_lines ael
1947 WHERE taxdist.line_id = c_actg_source_id
1951 AND lnk.source_distribution_id_num_1 = dist.line_id
1948 AND taxdist.tax_link_id = dist.tax_link_id
1949 AND NVL(taxdist.source_type_secondary,'X') = NVL(dist.source_type,'X')
1950 AND taxdist.source_id = dist.source_id
1952 AND lnk.application_id = 222
1953 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1954 AND lnk.ae_header_id = c_ae_header_id
1955 AND lnk.event_id = c_event_id
1956 AND lnk.ae_line_num = ael.ae_line_num
1957 AND aeh.ae_header_id = ael.ae_header_id
1958 AND aeh.ledger_id = c_ledger_id;
1959
1960 CURSOR tax_amount_dist (c_actg_source_id NUMBER, c_ae_header_id NUMBER,
1961 c_event_id NUMBER,c_ledger_id NUMBER) IS
1962 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
1963 FROM ar_distributions_all taxdist,
1964 xla_distribution_links lnk,
1965 xla_ae_headers aeh,
1966 xla_ae_lines ael
1967 WHERE taxdist.line_id = c_actg_source_id
1968 AND lnk.application_id = 222
1969 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1970 AND lnk.source_distribution_id_num_1 = taxdist.line_id
1971 AND lnk.ae_header_id = c_ae_header_id
1972 AND lnk.event_id = c_event_id
1973 AND lnk.ae_line_num = ael.ae_line_num
1974 AND aeh.ae_header_id = ael.ae_header_id
1975 AND aeh.ledger_id = c_ledger_id;
1976
1977 BEGIN
1978
1979 IF p_summary_level = 'TRANSACTION' THEN
1980 OPEN taxable_amount_hdr(p_actg_source_id , p_ae_header_id , p_event_id,p_ledger_id );
1981 FETCH taxable_amount_hdr INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
1982 -- EXIT WHEN taxable_amount_hdr%NOTFOUND;
1983 CLOSE taxable_amount_hdr;
1984
1985 OPEN tax_amount_hdr(p_actg_source_id , p_ae_header_id , p_event_id,p_ledger_id);
1986 FETCH tax_amount_hdr INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
1987 -- EXIT WHEN tax_amount_hdr%NOTFOUND;
1988 CLOSE tax_amount_hdr;
1989 /* ELSIF p_summary_level = 'TRANSACTION_LINE' THEN
1990 OPEN taxable_amount_line(p_trx_id ,p_trx_line_id, p_ae_header_id , p_event_id);
1991 FETCH taxable_amount_line INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
1992 -- EXIT WHEN taxable_amount_line%NOTFOUND;
1993 CLOSE taxable_amount_line;
1994
1995 OPEN tax_amount_line(p_trx_id , p_trx_line_id, p_ae_header_id , p_event_id);
1996 FETCH tax_amount_line INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
1997 -- EXIT WHEN tax_amount_line%NOTFOUND;
1998 CLOSE tax_amount_line;
1999 */
2000 ELSIF p_summary_level = 'TRANSACTION_DISTRIBUTION' THEN
2001 OPEN taxable_amount_dist(p_actg_source_id ,p_ae_header_id , p_event_id,p_ledger_id);
2002 FETCH taxable_amount_dist INTO GT_TAXABLE_AMT(i),GT_TAXABLE_AMT_FUNCL_CURR(i);
2003 -- EXIT WHEN taxable_amount_dist%NOTFOUND;
2004 CLOSE taxable_amount_dist;
2005
2006 OPEN tax_amount_dist(p_actg_source_id, p_ae_header_id , p_event_id,p_ledger_id);
2007 FETCH tax_amount_dist INTO GT_TAX_AMT(i),GT_TAX_AMT_FUNCL_CURR(i);
2008 -- EXIT WHEN tax_amount_dist%NOTFOUND;
2009 CLOSE tax_amount_dist;
2010 END IF;
2011
2012 END other_trx_actg_amounts;
2013
2014
2015 /*===========================================================================+
2016 | PROCEDURE |
2017 | convert_amounts |
2018 | |
2019 | DESCRIPTION |
2020 | This procedure converts tax and taxable amounts into functional amounts|
2021 | |
2022 | |
2023 | SCOPE - Private |
2024 | |
2025 | NOTES |
2026 | |
2027 | MODIFICATION HISTORY |
2028 | |
2029 +===========================================================================*/
2030
2031
2032 PROCEDURE convert_amounts(P_CURRENCY_CODE IN VARCHAR2,
2033 P_EXCHANGE_RATE IN NUMBER,
2034 P_PRECISION IN NUMBER,
2035 P_MIN_ACCT_UNIT IN NUMBER,
2036 P_INPUT_TAX_AMOUNT IN NUMBER,
2037 P_INPUT_TAXABLE_AMOUNT IN NUMBER,
2038 P_INPUT_EXEMPT_AMOUNT IN NUMBER,
2039 i IN BINARY_INTEGER) IS
2040
2041 l_taxable_amount NUMBER;
2042 l_TAXABLE_ACCOUNTED_AMOUNT number;
2043 l_TAX_ACCOUNTED_AMOUNT number;
2044 BEGIN
2045
2046 IF (g_level_procedure >= g_current_runtime_level ) THEN
2047 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.convert_amounts.BEGIN',
2048 'ZX_AR_ACTG_POPULATE_PKG: convert_amounts(+)');
2049 END IF;
2050 /*
2051 IF P_INPUT_EXEMPT_AMOUNT IS NOT NULL THEN
2052 P_EXEMPT_ENTERED_AMOUNT := P_INPUT_EXEMPT_AMOUNT;
2053 P_TAXABLE_EXEMPT_ENTERED_AMT :=
2054 P_INPUT_TAXABLE_AMOUNT + P_INPUT_EXEMPT_AMOUNT;
2055 l_taxable_amount := P_INPUT_TAXABLE_AMOUNT ;
2056
2057 ELSE
2058 P_EXEMPT_ENTERED_AMOUNT := 0;
2059 P_TAXABLE_EXEMPT_ENTERED_AMT := P_INPUT_TAXABLE_AMOUNT;
2060 l_taxable_amount := P_INPUT_TAXABLE_AMOUNT;
2061 END IF;
2062
2063 IF P_EXEMPT_ENTERED_AMOUNT IS NOT NULL THEN
2064 P_EXEMPT_ACCTD_AMOUNT := arpcurr.FUNCTIONAL_AMOUNT(
2065 P_EXEMPT_ENTERED_AMOUNT,
2066 P_CURRENCY_CODE,
2067 P_EXCHANGE_RATE,
2068 P_PRECISION,
2069 P_MIN_ACCT_UNIT);
2070 END IF;
2071
2072 IF P_TAXABLE_EXEMPT_ENTERED_AMT IS NOT NULL THEN
2073 P_TAXABLE_EXEMPT_ACCTD_AMT := arpcurr.FUNCTIONAL_AMOUNT(
2074 P_TAXABLE_EXEMPT_ENTERED_AMT,
2075 P_CURRENCY_CODE,
2076 P_EXCHANGE_RATE,
2077 P_PRECISION,
2078 P_MIN_ACCT_UNIT);
2079 END IF;
2080
2081 P_TAX_ENTERED_AMOUNT := P_INPUT_TAX_AMOUNT;
2082
2083
2084 P_TAXABLE_AMOUNT := l_taxable_amount;
2085 */
2086 IF P_INPUT_TAX_AMOUNT IS NOT NULL THEN
2087 l_TAX_ACCOUNTED_AMOUNT := arpcurr.FUNCTIONAL_AMOUNT(
2088 P_INPUT_TAX_AMOUNT,
2089 P_CURRENCY_CODE,
2090 P_EXCHANGE_RATE,
2091 P_PRECISION,
2092 P_MIN_ACCT_UNIT);
2093 END IF;
2094
2095 IF p_input_taxable_amount IS NOT NULL THEN
2096 l_TAXABLE_ACCOUNTED_AMOUNT := arpcurr.FUNCTIONAL_AMOUNT(
2097 p_input_taxable_amount,
2098 P_CURRENCY_CODE,
2099 P_EXCHANGE_RATE,
2100 P_PRECISION,
2101 P_MIN_ACCT_UNIT);
2102 END IF;
2103 GT_TAX_AMT_FUNCL_CURR(i) := l_TAX_ACCOUNTED_AMOUNT;
2104 GT_TAXABLE_AMT_FUNCL_CURR(i) := l_TAXABLE_ACCOUNTED_AMOUNT;
2105
2106
2107 IF (g_level_procedure >= g_current_runtime_level ) THEN
2108 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.convert_amounts.END',
2109 'ZX_AR_ACTG_POPULATE_PKG: convert_amounts(-)');
2110 END IF;
2111
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2115 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2116 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
2117 FND_MSG_PUB.Add;
2118 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2119 FND_LOG.STRING(g_level_unexpected,
2120 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.convert_amounts',
2121 g_error_buffer);
2122 END IF;
2123
2124 G_RETCODE := 2;
2125 END convert_amounts;
2126
2127 -- This API populates accounting segment details---
2128 ---------------------------------------------------
2129
2130
2131 -- This API populates Accounting Amounts ---
2132 ---------------------------------------------------
2133
2134
2135 /*PROCEDURE EXTRACT_PARTY_INFO( i IN BINARY_INTEGER) IS
2136
2137 l_bill_to_party_id zx_rep_trx_detail_t.BILL_TO_PARTY_ID%TYPE;
2138 l_bill_to_site_id zx_rep_trx_detail_t.BILL_TO_PARTY_SITE_ID%TYPE;
2139 l_bill_to_ptp_id zx_rep_trx_detail_t.BILL_FROM_PARTY_TAX_PROF_ID%TYPE;
2140 l_bill_to_stp_id zx_rep_trx_detail_t.BILL_FROM_SITE_TAX_PROF_ID%TYPE;
2141
2142 l_ship_to_party_id zx_rep_trx_detail_t.SHIP_TO_PARTY_ID%TYPE;
2143 l_ship_to_site_id zx_rep_trx_detail_t.SHIP_TO_PARTY_SITE_ID%TYPE;
2144 l_ship_to_ptp_id zx_rep_trx_detail_t.SHIP_FROM_PARTY_TAX_PROF_ID%TYPE;
2145 l_ship_to_stp_id zx_rep_trx_detail_t.SHIP_FROM_SITE_TAX_PROF_ID%TYPE;
2146
2147 l_bill_ship varchar2(30);
2148
2149 l_tbl_index_party BINARY_INTEGER;
2150 l_tbl_index_site BINARY_INTEGER;
2151 l_tbl_index_cust BINARY_INTEGER;
2152
2153 CURSOR party_id_cur
2154 (c_ptp_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_TAX_PROF_ID%TYPE) IS
2155 SELECT party_id
2156 FROM zx_party_tax_profile
2157 WHERE PARTY_TAX_PROFILE_ID = c_ptp_id
2158 AND party_type_code = 'THIRD_PARTY';
2159
2160 CURSOR party_site_id_cur
2161 (c_ptp_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_SITE_TAX_PROF_ID%TYPE) IS
2162 SELECT party_id
2163 FROM zx_party_tax_profile
2164 WHERE PARTY_TAX_PROFILE_ID = c_ptp_site_id
2165 AND party_type_code = 'THIRD_PARTY_SITE';
2166
2167 -- If party_id is NOT NULL and Historical flag 'Y' then get the party tax profile ID from zx_party_tax_profile
2168
2169 CURSOR party_profile_id_cur
2170 (c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE) IS
2171 SELECT party_tax_profile_id
2172 FROM zx_party_tax_profile
2173 WHERE party_id = c_party_id
2174 AND party_type_code = 'THIRD_PARTY';
2175
2176
2177 CURSOR site_profile_id_cur
2178 (c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE) IS
2179 SELECT party_tax_profile_id
2180 FROM zx_party_tax_profile
2181 WHERE party_id = c_party_site_id
2182 AND party_type_code = 'THIRD_PARTY_SITE';
2183
2184 CURSOR party_cur (c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE) IS
2185 select SUBSTRB(PARTY.PARTY_NAME,1,240) ,
2186 DECODE(PARTY.PARTY_TYPE,
2187 'ORGANIZATION',
2188 PARTY.ORGANIZATION_NAME_PHONETIC,
2189 NULL) ,
2190 DECODE(PARTY.PARTY_TYPE,
2191 'ORGANIZATION',
2192 PARTY.SIC_CODE,
2193 NULL) ,
2194 PARTY.PARTY_NUMBER
2195 FROM HZ_PARTIES PARTY
2196 WHERE PARTY.PARTY_ID = c_party_id;
2197
2198 CURSOR party_site_cur ( c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE) IS
2199 select LOC.CITY,
2200 LOC.COUNTY,
2201 LOC.STATE,
2202 LOC.PROVINCE,
2203 LOC.ADDRESS1,
2204 LOC.ADDRESS2,
2205 LOC.ADDRESS3,
2206 LOC.ADDRESS_LINES_PHONETIC,
2207 LOC.COUNTRY,
2208 LOC.POSTAL_CODE
2209 FROM HZ_PARTY_SITES PARTY_SITE,
2210 HZ_LOCATIONS LOC
2211 WHERE party_site.party_site_id = c_party_site_id
2212 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
2213
2214 CURSOR cust_acct_cur (c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE,
2215 c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE,
2216 c_ship_bill varchar2) IS
2217 SELECT acct.account_number,
2218 acct.global_attribute10,
2219 acct.global_attribute12,
2220 acct_site.global_attribute8,
2221 acct_site.global_attribute9,
2222 site_use.location,
2223 site_use.tax_reference
2224 FROM hz_cust_accounts acct,
2225 hz_cust_site_uses_all site_use ,
2226 hz_cust_acct_sites_all acct_site
2227 WHERE acct.CUST_ACCOUNT_ID = acct_site.CUST_ACCOUNT_ID
2228 and acct_site.CUST_ACCT_SITE_ID = site_use.CUST_ACCT_SITE_ID
2229 and acct_site.PARTY_SITE_ID = c_party_site_id
2230 and ACCT.PARTY_ID = c_party_id
2231 and site_use.site_use_code = c_ship_bill;
2232
2233 CURSOR migrated_party_cur (c_cust_acct_id ZX_REP_TRX_DETAIL_T.BILLING_TRADING_PARTNER_ID%TYPE) IS
2234 SELECT party.party_id
2235 FROM HZ_PARTIES PARTY,
2236 hz_cust_accounts acct
2237 WHERE PARTY.PARTY_ID = acct.party_id
2238 And acct.cust_account_id = c_cust_acct_id;
2239
2240
2241 CURSOR migrated_party_site_cur ( c_cust_acct_site_id ZX_REP_TRX_DETAIL_T.BILLING_TP_ADDRESS_ID%TYPE) IS
2242 SELECT PARTY_SITE.party_site_id
2243 FROM HZ_PARTY_SITES PARTY_SITE,
2244 hz_cust_acct_sites_all acct_site
2245 WHERE acct_site.cust_acct_site_id = c_cust_acct_site_id
2246 AND party_site.party_site_id = acct_site. party_site_id;
2247
2248 BEGIN
2249
2250
2251 IF (g_level_procedure >= g_current_runtime_level ) THEN
2252 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO.BEGIN',
2253 'ZX_AR_ACTG_POPULATE_PKG: EXTRACT_PARTY_INFO(+)');
2254 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2255 'gt_historical_flag :' ||gt_historical_flag(i));
2256 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2257 'GT_BILL_TO_PARTY_TAX_PROF_ID :' ||to_char(GT_BILL_TO_PARTY_TAX_PROF_ID(i)));
2258 END IF;
2259
2260 IF gt_historical_flag(i) IS NULL AND GT_BILL_TO_PARTY_TAX_PROF_ID(i) IS NOT NULL THEN
2261 OPEN party_id_cur(GT_BILL_TO_PARTY_TAX_PROF_ID(i));
2262 FETCH party_id_cur INTO l_bill_to_party_id;
2263 CLOSE party_id_cur;
2264
2265 OPEN party_site_id_cur(GT_BILL_TO_SITE_TAX_PROF_ID(i));
2266 FETCH party_site_id_cur INTO l_bill_to_site_id;
2267 CLOSE party_site_id_cur;
2268
2269 l_bill_to_ptp_id := GT_BILL_TO_PARTY_TAX_PROF_ID(i);
2270 l_bill_to_stp_id := GT_BILL_TO_SITE_TAX_PROF_ID(i);
2271
2272 ELSE
2276
2273 OPEN migrated_party_cur (GT_BILLING_TP_ID(i));
2274 FETCH migrated_party_cur INTO GT_BILL_TO_PARTY_ID(i);
2275 CLOSE migrated_party_cur;
2277 IF (g_level_procedure >= g_current_runtime_level ) THEN
2278 FND_LOG.STRING(g_level_procedure,
2279 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2280 'migrated_party_cur'||to_char(GT_BILL_TO_PARTY_ID(i))||'-'||to_char(GT_BILLING_TP_ID(i)));
2281 END IF;
2282
2283
2284 OPEN party_profile_id_cur (GT_BILL_TO_PARTY_ID(i));
2285 FETCH party_profile_id_cur into l_bill_to_ptp_id;
2286 CLOSE party_profile_id_cur;
2287
2288 OPEN migrated_party_site_cur (GT_BILLING_TP_ADDRESS_ID(i));
2289 FETCH migrated_party_site_cur INTO GT_BILL_TO_PARTY_SITE_ID(i);
2290 CLOSE migrated_party_site_cur;
2291
2292 OPEN site_profile_id_cur(GT_BILL_TO_PARTY_SITE_ID(i));
2293 FETCH site_profile_id_cur INTO l_bill_to_stp_id;
2294 CLOSE site_profile_id_cur;
2295
2296 IF (g_level_procedure >= g_current_runtime_level ) THEN
2297 FND_LOG.STRING(g_level_procedure,
2298 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2299 'migrated_party_site_cur :'||to_char(GT_BILL_TO_PARTY_SITE_ID(i))||'-'||to_char(GT_BILLING_TP_ADDRESS_ID(i)));
2300 END IF;
2301
2302 l_bill_to_party_id := GT_BILL_TO_PARTY_ID(i);
2303 l_bill_to_site_id := GT_BILL_TO_PARTY_SITE_ID(i);
2304 l_bill_ship := 'BILL_TO';
2305 END IF;
2306
2307
2308 -- IF GT_BILLING_TP_ID(i) IS NOT NULL AND GT_BILLING_TP_ADDRESS_ID(i) IS NOT NULL THEN
2309
2310 IF l_bill_to_site_id is not null and l_bill_to_party_id is not null THEN
2311 l_tbl_index_cust := dbms_utility.get_hash_value(to_char(l_bill_to_site_id)||to_char(l_bill_to_party_id)||
2312 l_bill_ship, 1,8192);
2313
2314 IF (g_level_procedure >= g_current_runtime_level ) THEN
2315 FND_LOG.STRING(g_level_procedure,
2316 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2317 'Before Open cust_acct_cur :'||to_char(l_bill_to_party_id)||'-'||to_char(l_bill_to_site_id));
2318 END IF;
2319
2320 IF g_cust_bill_ar_tbl.EXISTS(l_tbl_index_cust) THEN
2321 GT_BILLING_TP_NUMBER(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_NUMBER ;
2322 GT_GDF_RA_CUST_BILL_ATT10(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT10;
2323 GT_GDF_RA_CUST_BILL_ATT12(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT12;
2324 GT_GDF_RA_ADDRESSES_BILL_ATT8(i) :=g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT8;
2325 GT_GDF_RA_ADDRESSES_BILL_ATT9(i) :=g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT9;
2326 GT_BILLING_TP_SITE_NAME(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_SITE_NAME;
2327 GT_BILLING_TP_TAX_REG_NUM(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_TAX_REG_NUM;
2328 ELSE
2329 OPEN cust_acct_cur (l_bill_to_site_id,
2330 l_bill_to_party_id,
2331 l_bill_ship);
2332 FETCH cust_acct_cur INTO GT_BILLING_TP_NUMBER(i),
2333 GT_GDF_RA_CUST_BILL_ATT10(i),
2334 GT_GDF_RA_CUST_BILL_ATT12(i),
2335 GT_GDF_RA_ADDRESSES_BILL_ATT8(i),
2336 GT_GDF_RA_ADDRESSES_BILL_ATT9(i),
2337 GT_BILLING_TP_SITE_NAME(i),
2338 GT_BILLING_TP_TAX_REG_NUM(i);
2339
2340 IF (g_level_procedure >= g_current_runtime_level ) THEN
2341 FND_LOG.STRING(g_level_procedure,
2342 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2343 'After fetch of cust_acct_cur'||GT_BILLING_TP_NUMBER(i)||'-'||GT_BILLING_TP_TAX_REG_NUM(i));
2344 END IF;
2345
2346 g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_NUMBER := GT_BILLING_TP_NUMBER(i);
2347 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT10 := GT_GDF_RA_CUST_BILL_ATT10(i);
2348 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT12 := GT_GDF_RA_CUST_BILL_ATT12(i);
2349 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT8 := GT_GDF_RA_ADDRESSES_BILL_ATT8(i);
2350 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT9 := GT_GDF_RA_ADDRESSES_BILL_ATT9(i);
2351 g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_SITE_NAME := GT_BILLING_TP_SITE_NAME(i);
2352 g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_TAX_REG_NUM := GT_BILLING_TP_TAX_REG_NUM(i);
2353
2354 CLOSE cust_acct_cur;
2355 END IF;
2356
2357 IF (g_level_procedure >= g_current_runtime_level ) THEN
2358 FND_LOG.STRING(g_level_procedure,
2359 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2360 'After assign to g_cust_bill_ar_tbl ');
2361 END IF;
2362
2363 l_tbl_index_party := dbms_utility.get_hash_value(to_char(l_bill_to_party_id)||
2364 l_bill_ship, 1,8192);
2365 IF g_party_bill_ar_tbl.EXISTS(l_tbl_index_party) THEN
2366
2367 GT_BILLING_TP_NAME_ALT(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT;
2368 GT_BILLING_TP_NAME(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME;
2369 GT_BILLING_TP_SIC_CODE(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE;
2370 GT_BILLING_TP_NUMBER(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER;
2371 ELSE
2372 OPEN party_cur (l_bill_to_party_id);
2373 FETCH party_cur INTO GT_BILLING_TP_NAME(i),
2374 GT_BILLING_TP_NAME_ALT(i),
2375 GT_BILLING_TP_SIC_CODE(i),
2376 GT_BILLING_TP_NUMBER(i);
2377
2378 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT := GT_BILLING_TP_NAME_ALT(i);
2379 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME := GT_BILLING_TP_NAME(i);
2380 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE := GT_BILLING_TP_SIC_CODE(i);
2381 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER := GT_BILLING_TP_NUMBER(i);
2382 CLOSE party_cur;
2383 END IF;
2384
2385
2386
2387 IF (g_level_procedure >= g_current_runtime_level ) THEN
2388 FND_LOG.STRING(g_level_procedure,
2389 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2390 'After assign to g_party_bill_ar_tbl '||g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER);
2391 END IF;
2392 l_tbl_index_site := dbms_utility.get_hash_value(to_char(l_bill_to_site_id)||
2393 l_bill_ship, 1,8192);
2394
2395 IF g_site_bill_ar_tbl.EXISTS(l_tbl_index_site) THEN
2396 GT_BILLING_TP_CITY(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_CITY;
2397 GT_BILLING_TP_COUNTY(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_COUNTY;
2398 GT_BILLING_TP_STATE(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_STATE;
2399 GT_BILLING_TP_PROVINCE(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_PROVINCE;
2400 GT_BILLING_TP_ADDRESS1(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS1;
2401 GT_BILLING_TP_ADDRESS2(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS2;
2402 GT_BILLING_TP_ADDRESS3(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS3;
2403 GT_BILLING_TP_ADDR_LINES_ALT(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDR_LINES_ALT;
2404 GT_BILLING_TP_COUNTRY(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_COUNTRY;
2405 GT_BILLING_TP_POSTAL_CODE(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_POSTAL_CODE;
2406 ELSE
2407 OPEN party_site_cur (l_bill_to_site_id);
2408 FETCH party_site_cur INTO GT_BILLING_TP_CITY(i),
2409 GT_BILLING_TP_COUNTY(i),
2410 GT_BILLING_TP_STATE(i),
2411 GT_BILLING_TP_PROVINCE(i),
2412 GT_BILLING_TP_ADDRESS1(i),
2413 GT_BILLING_TP_ADDRESS2(i),
2414 GT_BILLING_TP_ADDRESS3(i),
2415 GT_BILLING_TP_ADDR_LINES_ALT(i),
2416 GT_BILLING_TP_COUNTRY(i),
2417 GT_BILLING_TP_POSTAL_CODE(i);
2418
2419 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_CITY := GT_BILLING_TP_CITY(i);
2420 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_COUNTY := GT_BILLING_TP_COUNTY(i);
2421 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_STATE := GT_BILLING_TP_STATE(i);
2422 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_PROVINCE := GT_BILLING_TP_PROVINCE(i);
2423 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS1 := GT_BILLING_TP_ADDRESS1(i);
2424 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS2 := GT_BILLING_TP_ADDRESS2(i);
2425 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS3 := GT_BILLING_TP_ADDRESS3(i);
2426 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDR_LINES_ALT := GT_BILLING_TP_ADDR_LINES_ALT(i);
2427 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_COUNTRY := GT_BILLING_TP_COUNTRY(i);
2428 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_POSTAL_CODE := GT_BILLING_TP_POSTAL_CODE(i);
2429 CLOSE party_site_cur;
2430 END IF;
2431
2432
2433
2434 IF (g_level_procedure >= g_current_runtime_level ) THEN
2435 FND_LOG.STRING(g_level_procedure,
2436 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2437 'After assign to g_site_bill_ar_tbl '||g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_CITY);
2438 END IF;
2439 END IF;
2440
2441
2442 IF gt_historical_flag(i) IS NULL AND GT_SHIP_TO_PARTY_TAX_PROF_ID(i) IS NOT NULL THEN
2443 OPEN party_id_cur(GT_SHIP_TO_PARTY_TAX_PROF_ID(i));
2444 FETCH party_id_cur INTO l_bill_to_party_id;
2445 CLOSE party_id_cur;
2446
2447 OPEN party_site_id_cur(GT_SHIP_TO_SITE_TAX_PROF_ID(i));
2448 FETCH party_site_id_cur INTO l_bill_to_site_id;
2449 CLOSE party_site_id_cur;
2450
2451 l_ship_to_ptp_id := GT_SHIP_TO_PARTY_TAX_PROF_ID(i);
2452 l_ship_to_stp_id := GT_SHIP_TO_SITE_TAX_PROF_ID(i);
2453
2454 ELSE
2455
2456
2457 OPEN migrated_party_cur (GT_SHIPPING_TP_ID(i));
2458 FETCH migrated_party_cur INTO GT_SHIP_TO_PARTY_ID(i);
2459 CLOSE migrated_party_cur;
2460
2461 OPEN migrated_party_site_cur (GT_SHIPPING_TP_ADDRESS_ID(i));
2462 FETCH migrated_party_site_cur INTO GT_SHIP_TO_PARTY_SITE_ID(i);
2463 CLOSE migrated_party_site_cur;
2464
2465
2466 OPEN party_profile_id_cur (GT_SHIP_TO_PARTY_ID(i));
2467 FETCH party_profile_id_cur into l_bill_to_ptp_id;
2468 CLOSE party_profile_id_cur;
2469
2470 OPEN site_profile_id_cur(GT_SHIP_TO_PARTY_SITE_ID(i));
2471 FETCH site_profile_id_cur INTO l_bill_to_stp_id;
2472 CLOSE site_profile_id_cur;
2473
2474 l_ship_to_party_id := GT_SHIP_TO_PARTY_ID(i);
2475 l_ship_to_site_id := GT_SHIP_TO_PARTY_SITE_ID(i);
2476 l_bill_ship := 'SHIP_TO';
2477
2478 END IF;
2479
2480 -- IF GT_SHIPPING_TP_ID(i) IS NOT NULL AND GT_SHIPPING_TP_ADDRESS_ID(i) IS NOT NULL THEN
2481
2482
2483 IF l_ship_to_site_id is not null and l_ship_to_party_id is not null THEN
2484 l_tbl_index_cust := dbms_utility.get_hash_value(to_char(l_ship_to_site_id)||(l_ship_to_party_id)||
2485 l_bill_ship, 1,8192);
2486
2487 IF g_cust_ship_ar_tbl.EXISTS(l_tbl_index_cust) THEN
2488 GT_SHIPPING_TP_NUMBER(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER ;
2489 GT_GDF_RA_CUST_SHIP_ATT10(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT10;
2490 GT_GDF_RA_CUST_SHIP_ATT12(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT12;
2491 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i) :=g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT8;
2492 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i) :=g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT9;
2493 GT_SHIPPING_TP_SITE_NAME(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_SITE_NAME;
2494 GT_SHIPPING_TP_TAX_REG_NUM(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_TAX_REG_NUM;
2495 ELSE
2496 OPEN cust_acct_cur (l_ship_to_site_id,
2497 l_ship_to_party_id,
2498 l_bill_ship);
2499 FETCH cust_acct_cur INTO GT_SHIPPING_TP_NUMBER(i),
2500 GT_GDF_RA_CUST_SHIP_ATT10(i),
2501 GT_GDF_RA_CUST_SHIP_ATT12(i),
2502 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i),
2503 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i),
2504 GT_SHIPPING_TP_SITE_NAME(i),
2505 GT_SHIPPING_TP_TAX_REG_NUM(i);
2506 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER := GT_SHIPPING_TP_NUMBER(i);
2507 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT10 := GT_GDF_RA_CUST_SHIP_ATT10(i);
2508 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT12 := GT_GDF_RA_CUST_SHIP_ATT12(i);
2509 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT8 := GT_GDF_RA_ADDRESSES_SHIP_ATT8(i);
2510 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT9 := GT_GDF_RA_ADDRESSES_SHIP_ATT9(i);
2511 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_SITE_NAME := GT_SHIPPING_TP_SITE_NAME(i);
2512 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_TAX_REG_NUM := GT_SHIPPING_TP_TAX_REG_NUM(i);
2513
2514 CLOSE cust_acct_cur;
2515 END IF;
2516
2517 l_tbl_index_party := dbms_utility.get_hash_value(to_char(l_ship_to_party_id)||
2518 l_bill_ship, 1,8192);
2519 IF g_party_ship_ar_tbl.EXISTS(l_tbl_index_party) THEN
2520
2521 GT_SHIPPING_TP_NAME_ALT(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME_ALT;
2522 GT_SHIPPING_TP_NAME(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME;
2523 GT_SHIPPING_TP_SIC_CODE(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_SIC_CODE;
2524 GT_SHIPPING_TP_NUMBER(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NUMBER;
2525 ELSE
2526 OPEN party_cur (l_ship_to_party_id);
2527 FETCH party_cur INTO GT_SHIPPING_TP_NAME(i),
2528 GT_SHIPPING_TP_NAME_ALT(i),
2529 GT_SHIPPING_TP_SIC_CODE(i),
2530 GT_SHIPPING_TP_NUMBER(i);
2531
2532 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME_ALT := GT_SHIPPING_TP_NAME_ALT(i);
2533 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME := GT_SHIPPING_TP_NAME(i);
2534 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_SIC_CODE := GT_SHIPPING_TP_SIC_CODE(i);
2535 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NUMBER := GT_SHIPPING_TP_NUMBER(i);
2536 CLOSE party_cur;
2537 END IF;
2538
2539 l_tbl_index_site := dbms_utility.get_hash_value(to_char(l_ship_to_site_id)||
2540 l_bill_ship, 1,8192);
2541 IF g_site_ship_ar_tbl.EXISTS(l_tbl_index_site) THEN
2542 GT_SHIPPING_TP_CITY(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_CITY;
2546 GT_SHIPPING_TP_ADDRESS1(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS1;
2543 GT_SHIPPING_TP_COUNTY(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_COUNTY;
2544 GT_SHIPPING_TP_STATE(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_STATE;
2545 GT_SHIPPING_TP_PROVINCE(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_PROVINCE;
2547 GT_SHIPPING_TP_ADDRESS2(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS2;
2548 GT_SHIPPING_TP_ADDRESS3(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS3;
2549 GT_SHIPPING_TP_ADDR_LINES_ALT(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDR_LINES_ALT;
2550 GT_SHIPPING_TP_COUNTRY(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_COUNTRY;
2551 GT_SHIPPING_TP_POSTAL_CODE(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_POSTAL_CODE;
2552 ELSE
2553 OPEN party_site_cur (l_ship_to_site_id);
2554 FETCH party_site_cur INTO GT_SHIPPING_TP_CITY(i),
2555 GT_SHIPPING_TP_COUNTY(i),
2556 GT_SHIPPING_TP_STATE(i),
2557 GT_SHIPPING_TP_PROVINCE(i),
2558 GT_SHIPPING_TP_ADDRESS1(i),
2559 GT_SHIPPING_TP_ADDRESS2(i),
2560 GT_SHIPPING_TP_ADDRESS3(i),
2561 GT_SHIPPING_TP_ADDR_LINES_ALT(i),
2562 GT_SHIPPING_TP_COUNTRY(i),
2563 GT_SHIPPING_TP_POSTAL_CODE(i);
2564 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_CITY := GT_SHIPPING_TP_CITY(i);
2565 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_COUNTY := GT_SHIPPING_TP_COUNTY(i);
2566 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_STATE := GT_SHIPPING_TP_STATE(i);
2567 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_PROVINCE := GT_SHIPPING_TP_PROVINCE(i);
2568 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS1 := GT_SHIPPING_TP_ADDRESS1(i);
2569 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS2 := GT_SHIPPING_TP_ADDRESS2(i);
2570 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS3 := GT_SHIPPING_TP_ADDRESS3(i);
2571 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDR_LINES_ALT := GT_SHIPPING_TP_ADDR_LINES_ALT(i);
2572 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_COUNTRY := GT_SHIPPING_TP_COUNTRY(i);
2573 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_POSTAL_CODE := GT_SHIPPING_TP_POSTAL_CODE(i);
2574 CLOSE party_site_cur;
2575 END IF;
2576
2577 END IF;
2578
2579
2580 IF (g_level_procedure >= g_current_runtime_level ) THEN
2581 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO.END',
2582 'ZX_AR_ACTG_POPULATE_PKG: EXTRACT_PARTY_INFO(-)');
2583 END IF;
2584
2585 EXCEPTION
2586 WHEN OTHERS THEN
2587 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2588 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2589 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
2590 FND_MSG_PUB.Add;
2591 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2592 FND_LOG.STRING(g_level_unexpected,
2593 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.EXTRACT_PARTY_INFO',
2594 g_error_buffer);
2595 END IF;
2596
2597 G_RETCODE := 2;
2598
2599
2600 END EXTRACT_PARTY_INFO;
2601 */
2602
2603
2604 PROCEDURE EXTRACT_PARTY_INFO( i IN BINARY_INTEGER) IS
2605
2606 l_bill_to_party_id zx_rep_trx_detail_t.BILL_TO_PARTY_ID%TYPE;
2607 l_bill_to_site_id zx_rep_trx_detail_t.BILL_TO_PARTY_SITE_ID%TYPE;
2608 l_bill_to_ptp_id zx_rep_trx_detail_t.BILL_FROM_PARTY_TAX_PROF_ID%TYPE;
2609 l_bill_to_stp_id zx_rep_trx_detail_t.BILL_FROM_SITE_TAX_PROF_ID%TYPE;
2610
2611 l_ship_to_party_id zx_rep_trx_detail_t.SHIP_TO_PARTY_ID%TYPE;
2612 l_ship_to_site_id zx_rep_trx_detail_t.SHIP_TO_PARTY_SITE_ID%TYPE;
2613 l_ship_to_ptp_id zx_rep_trx_detail_t.SHIP_FROM_PARTY_TAX_PROF_ID%TYPE;
2614 l_ship_to_stp_id zx_rep_trx_detail_t.SHIP_FROM_SITE_TAX_PROF_ID%TYPE;
2615
2616 l_bill_ship varchar2(30);
2617
2618 l_tbl_index_party BINARY_INTEGER;
2619 l_tbl_index_site BINARY_INTEGER;
2620 l_tbl_index_cust BINARY_INTEGER;
2621
2622 CURSOR party_id_from_ptp_cur
2623 (c_ptp_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_TAX_PROF_ID%TYPE) IS
2624 SELECT party_id
2625 FROM zx_party_tax_profile
2626 WHERE PARTY_TAX_PROFILE_ID = c_ptp_id
2627 AND party_type_code = 'THIRD_PARTY';
2628
2629 CURSOR party_site_id_from_ptp_cur
2630 (c_ptp_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_SITE_TAX_PROF_ID%TYPE) IS
2631 SELECT party_id
2632 FROM zx_party_tax_profile
2633 WHERE PARTY_TAX_PROFILE_ID = c_ptp_site_id
2634 AND party_type_code = 'THIRD_PARTY_SITE';
2635
2636 -- If party_id is NOT NULL and Historical flag 'Y' then get the party tax profile ID from zx_party_tax_profile
2637
2638 CURSOR party_profile_id_cur
2639 (c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE) IS
2640 SELECT party_tax_profile_id
2641 FROM zx_party_tax_profile
2642 WHERE party_id = c_party_id
2643 AND party_type_code = 'THIRD_PARTY';
2644
2645
2646 CURSOR site_profile_id_cur
2647 (c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE) IS
2648 SELECT party_tax_profile_id
2649 FROM zx_party_tax_profile
2650 WHERE party_id = c_party_site_id
2651 AND party_type_code = 'THIRD_PARTY_SITE';
2652
2653 CURSOR party_cur (c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE) IS
2654 select SUBSTRB(PARTY.PARTY_NAME,1,240) ,
2655 DECODE(PARTY.PARTY_TYPE,
2656 'ORGANIZATION',
2657 PARTY.ORGANIZATION_NAME_PHONETIC,
2658 NULL) ,
2659 DECODE(PARTY.PARTY_TYPE,
2660 'ORGANIZATION',
2661 PARTY.SIC_CODE,
2662 NULL) ,
2663 PARTY.PARTY_NUMBER,
2664 PARTY.JGZZ_FISCAL_CODE,
2665 PARTY.TAX_REFERENCE
2666 FROM HZ_PARTIES PARTY
2667 WHERE PARTY.PARTY_ID = c_party_id;
2668
2669 CURSOR party_site_cur ( c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE) IS
2670 select LOC.CITY,
2671 LOC.COUNTY,
2672 LOC.STATE,
2673 LOC.PROVINCE,
2674 LOC.ADDRESS1,
2675 LOC.ADDRESS2,
2676 LOC.ADDRESS3,
2677 LOC.ADDRESS_LINES_PHONETIC,
2678 LOC.COUNTRY,
2679 LOC.POSTAL_CODE
2680 FROM HZ_PARTY_SITES PARTY_SITE,
2681 HZ_LOCATIONS LOC
2682 WHERE party_site.party_site_id = c_party_site_id
2683 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
2684
2685 CURSOR cust_acct_cur (c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE,
2686 c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE,
2687 c_ship_bill varchar2) IS
2688 SELECT acct.account_number,
2689 acct.global_attribute10,
2690 acct.global_attribute12,
2691 acct_site.global_attribute8,
2692 acct_site.global_attribute9,
2693 site_use.location,
2694 site_use.tax_reference
2695 FROM hz_cust_accounts acct,
2696 hz_cust_site_uses_all site_use ,
2697 hz_cust_acct_sites_all acct_site
2698 WHERE acct.CUST_ACCOUNT_ID = acct_site.CUST_ACCOUNT_ID
2699 and acct_site.CUST_ACCT_SITE_ID = site_use.CUST_ACCT_SITE_ID
2700 and acct_site.PARTY_SITE_ID = c_party_site_id
2701 and ACCT.PARTY_ID = c_party_id
2702 and site_use.site_use_code = c_ship_bill;
2703
2704 CURSOR party_id_cur (c_cust_acct_id ZX_REP_TRX_DETAIL_T.BILLING_TRADING_PARTNER_ID%TYPE) IS
2705 SELECT acct.party_id
2706 FROM hz_cust_accounts acct
2707 WHERE acct.cust_account_id = c_cust_acct_id;
2708
2709 CURSOR party_site_id_cur ( c_cust_site_use_id ZX_REP_TRX_DETAIL_T.BILLING_TP_SITE_ID%TYPE) IS
2710 SELECT acct_site.party_site_id
2711 FROM hz_cust_acct_sites_all acct_site,
2712 hz_cust_site_uses_all site_use
2713 WHERE acct_site.cust_acct_site_id = site_use.cust_acct_site_id
2714 AND site_use.site_use_id = c_cust_site_use_id;
2715
2716
2717 BEGIN
2718
2719
2720 IF (g_level_procedure >= g_current_runtime_level ) THEN
2721 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO.BEGIN',
2722 'ZX_AR_POPULATE_PKG: EXTRACT_PARTY_INFO(+)');
2723 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2724 'gt_historical_flag :' ||gt_historical_flag(i));
2725 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2726 'GT_BILL_TO_PARTY_TAX_PROF_ID :' ||to_char(GT_BILL_TO_PARTY_TAX_PROF_ID(i)));
2727 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2728 'GT_BILLING_TP_SITE_ID :' ||to_char(GT_BILLING_TP_SITE_ID(i)));
2729 END IF;
2730
2731 IF GT_BILL_TO_PARTY_TAX_PROF_ID(i) IS NOT NULL THEN
2732 OPEN party_id_from_ptp_cur(GT_BILL_TO_PARTY_TAX_PROF_ID(i));
2733 FETCH party_id_from_ptp_cur INTO l_bill_to_party_id;
2734 CLOSE party_id_from_ptp_cur;
2735 l_bill_to_ptp_id := GT_BILL_TO_PARTY_TAX_PROF_ID(i);
2736 ELSE
2737 OPEN party_id_cur (GT_BILLING_TP_ID(i));
2738 FETCH party_id_cur INTO GT_BILL_TO_PARTY_ID(i);
2739 CLOSE party_id_cur;
2740
2741 OPEN party_profile_id_cur (GT_BILL_TO_PARTY_ID(i));
2742 FETCH party_profile_id_cur into GT_BILL_TO_PARTY_TAX_PROF_ID(i);
2743 CLOSE party_profile_id_cur;
2744 --l_bill_to_party_id := GT_BILLING_TP_ID(i);
2745 l_bill_to_party_id := GT_BILL_TO_PARTY_ID(i);
2746 END IF;
2747 IF (g_level_procedure >= g_current_runtime_level ) THEN
2748 FND_LOG.STRING(g_level_procedure,
2749 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2750 'party_id_cur : l_bill_to_party_id '||to_char(l_bill_to_party_id));
2751 END IF;
2752
2753 IF GT_BILL_TO_SITE_TAX_PROF_ID(i) IS NOT NULL THEN
2754 OPEN party_site_id_from_ptp_cur(GT_BILL_TO_SITE_TAX_PROF_ID(i));
2755 FETCH party_site_id_from_ptp_cur INTO l_bill_to_site_id;
2756 CLOSE party_site_id_from_ptp_cur;
2757 l_bill_to_stp_id := GT_BILL_TO_SITE_TAX_PROF_ID(i);
2758 ELSE
2759 OPEN party_site_id_cur (GT_BILLING_TP_SITE_ID(i));
2763 OPEN site_profile_id_cur(GT_BILL_TO_PARTY_SITE_ID(i));
2760 FETCH party_site_id_cur INTO GT_BILL_TO_PARTY_SITE_ID(i);
2761 CLOSE party_site_id_cur;
2762
2764 FETCH site_profile_id_cur INTO GT_BILL_TO_SITE_TAX_PROF_ID(i);
2765 CLOSE site_profile_id_cur;
2766 l_bill_to_site_id := GT_BILL_TO_PARTY_SITE_ID(i);
2767 END IF;
2768
2769 IF (g_level_procedure >= g_current_runtime_level ) THEN
2770 FND_LOG.STRING(g_level_procedure,
2771 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2772 'party_site_id_cur : l_bill_to_site_id '||to_char(l_bill_to_site_id));
2773 END IF;
2774
2775 l_bill_ship := 'BILL_TO';
2776
2777
2778 -- IF GT_BILLING_TP_ID(i) IS NOT NULL AND GT_BILLING_TP_ADDRESS_ID(i) IS NOT NULL THEN
2779
2780 IF l_bill_to_site_id is not null and l_bill_to_party_id is not null THEN
2781 l_tbl_index_cust := dbms_utility.get_hash_value(to_char(l_bill_to_site_id)||to_char(l_bill_to_party_id)||
2782 l_bill_ship, 1,8192);
2783
2784 IF (g_level_procedure >= g_current_runtime_level ) THEN
2785 FND_LOG.STRING(g_level_procedure,
2786 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2787 'Before Open cust_acct_cur :'||to_char(l_bill_to_party_id)||'-'||to_char(l_bill_to_site_id));
2788 END IF;
2789
2790 IF g_cust_bill_ar_tbl.EXISTS(l_tbl_index_cust) THEN
2791 GT_BILLING_TP_NUMBER(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_NUMBER ;
2792 GT_GDF_RA_CUST_BILL_ATT10(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT10;
2793 GT_GDF_RA_CUST_BILL_ATT12(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT12;
2794 GT_GDF_RA_ADDRESSES_BILL_ATT8(i) :=g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT8;
2795 GT_GDF_RA_ADDRESSES_BILL_ATT9(i) :=g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT9;
2796 GT_BILLING_TP_SITE_NAME(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_SITE_NAME;
2797 GT_BILLING_TP_TAX_REG_NUM(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_TAX_REG_NUM;
2798 ELSE
2799 OPEN cust_acct_cur (l_bill_to_site_id,
2800 l_bill_to_party_id,
2801 l_bill_ship);
2802 FETCH cust_acct_cur INTO GT_BILLING_TP_NUMBER(i),
2803 GT_GDF_RA_CUST_BILL_ATT10(i),
2804 GT_GDF_RA_CUST_BILL_ATT12(i),
2805 GT_GDF_RA_ADDRESSES_BILL_ATT8(i),
2806 GT_GDF_RA_ADDRESSES_BILL_ATT9(i),
2807 GT_BILLING_TP_SITE_NAME(i),
2808 GT_BILLING_TP_TAX_REG_NUM(i);
2809
2810 IF (g_level_procedure >= g_current_runtime_level ) THEN
2811 FND_LOG.STRING(g_level_procedure,
2812 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2813 'After fetch of cust_acct_cur'||GT_BILLING_TP_NUMBER(i)||'-'||GT_BILLING_TP_TAX_REG_NUM(i));
2814 END IF;
2815
2816 g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_NUMBER := GT_BILLING_TP_NUMBER(i);
2817 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT10 := GT_GDF_RA_CUST_BILL_ATT10(i);
2818 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT12 := GT_GDF_RA_CUST_BILL_ATT12(i);
2819 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT8 := GT_GDF_RA_ADDRESSES_BILL_ATT8(i);
2820 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT9 := GT_GDF_RA_ADDRESSES_BILL_ATT9(i);
2821 g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_SITE_NAME := GT_BILLING_TP_SITE_NAME(i);
2822 g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_TAX_REG_NUM := GT_BILLING_TP_TAX_REG_NUM(i);
2823
2824 CLOSE cust_acct_cur;
2825 END IF;
2826
2827 IF (g_level_procedure >= g_current_runtime_level ) THEN
2828 FND_LOG.STRING(g_level_procedure,
2829 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2830 'After assign to g_cust_bill_ar_tbl ');
2831 END IF;
2832
2833 l_tbl_index_party := dbms_utility.get_hash_value(to_char(l_bill_to_party_id)||
2834 l_bill_ship, 1,8192);
2835 IF g_party_bill_ar_tbl.EXISTS(l_tbl_index_party) THEN
2836
2837 GT_BILLING_TP_NAME_ALT(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT;
2838 GT_BILLING_TP_NAME(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME;
2839 GT_BILLING_TP_SIC_CODE(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE;
2840 GT_BILLING_TP_NUMBER(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER;
2841 ELSE
2842 OPEN party_cur (l_bill_to_party_id);
2843 FETCH party_cur INTO GT_BILLING_TP_NAME(i),
2844 GT_BILLING_TP_NAME_ALT(i),
2845 GT_BILLING_TP_SIC_CODE(i),
2846 GT_BILLING_TP_NUMBER(i),
2847 GT_BILLING_TP_TAXPAYER_ID(i),
2848 GT_BILLING_TP_TAX_REG_NUM(i);
2849
2850 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT := GT_BILLING_TP_NAME_ALT(i);
2851 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME := GT_BILLING_TP_NAME(i);
2852 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE := GT_BILLING_TP_SIC_CODE(i);
2853 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER := GT_BILLING_TP_NUMBER(i);
2854 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_TAXPAYER_ID := GT_BILLING_TP_TAXPAYER_ID(i);
2855 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_TAX_REG_NUM := GT_BILLING_TP_TAX_REG_NUM(i);
2856
2857 CLOSE party_cur;
2861
2858 END IF;
2859
2860
2862 IF (g_level_procedure >= g_current_runtime_level ) THEN
2863 FND_LOG.STRING(g_level_procedure,
2864 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2865 'After assign to g_party_bill_ar_tbl '||g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER);
2866 END IF;
2867 l_tbl_index_site := dbms_utility.get_hash_value(to_char(l_bill_to_site_id)||
2868 l_bill_ship, 1,8192);
2869
2870 IF g_site_bill_ar_tbl.EXISTS(l_tbl_index_site) THEN
2871 GT_BILLING_TP_CITY(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_CITY;
2872 GT_BILLING_TP_COUNTY(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_COUNTY;
2873 GT_BILLING_TP_STATE(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_STATE;
2874 GT_BILLING_TP_PROVINCE(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_PROVINCE;
2875 GT_BILLING_TP_ADDRESS1(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS1;
2876 GT_BILLING_TP_ADDRESS2(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS2;
2877 GT_BILLING_TP_ADDRESS3(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS3;
2878 GT_BILLING_TP_ADDR_LINES_ALT(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDR_LINES_ALT;
2879 GT_BILLING_TP_COUNTRY(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_COUNTRY;
2880 GT_BILLING_TP_POSTAL_CODE(i) := g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_POSTAL_CODE;
2881 ELSE
2882 OPEN party_site_cur (l_bill_to_site_id);
2883 FETCH party_site_cur INTO GT_BILLING_TP_CITY(i),
2884 GT_BILLING_TP_COUNTY(i),
2885 GT_BILLING_TP_STATE(i),
2886 GT_BILLING_TP_PROVINCE(i),
2887 GT_BILLING_TP_ADDRESS1(i),
2888 GT_BILLING_TP_ADDRESS2(i),
2889 GT_BILLING_TP_ADDRESS3(i),
2890 GT_BILLING_TP_ADDR_LINES_ALT(i),
2891 GT_BILLING_TP_COUNTRY(i),
2892 GT_BILLING_TP_POSTAL_CODE(i);
2893
2894 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_CITY := GT_BILLING_TP_CITY(i);
2895 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_COUNTY := GT_BILLING_TP_COUNTY(i);
2896 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_STATE := GT_BILLING_TP_STATE(i);
2897 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_PROVINCE := GT_BILLING_TP_PROVINCE(i);
2898 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS1 := GT_BILLING_TP_ADDRESS1(i);
2899 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS2 := GT_BILLING_TP_ADDRESS2(i);
2900 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDRESS3 := GT_BILLING_TP_ADDRESS3(i);
2901 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_ADDR_LINES_ALT := GT_BILLING_TP_ADDR_LINES_ALT(i);
2902 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_COUNTRY := GT_BILLING_TP_COUNTRY(i);
2903 g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_POSTAL_CODE := GT_BILLING_TP_POSTAL_CODE(i);
2904 CLOSE party_site_cur;
2905 END IF;
2906
2907
2908
2909 IF (g_level_procedure >= g_current_runtime_level ) THEN
2910 FND_LOG.STRING(g_level_procedure,
2911 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2912 'After assign to g_site_bill_ar_tbl '||g_site_bill_ar_tbl(l_tbl_index_site).BILLING_TP_CITY);
2913 END IF;
2914 END IF;
2915
2916 -- Ship to party information ----
2917 ---------------------------------
2918 IF GT_SHIP_TO_PARTY_TAX_PROF_ID(i) IS NOT NULL THEN
2919 OPEN party_id_from_ptp_cur(GT_SHIP_TO_PARTY_TAX_PROF_ID(i));
2920 FETCH party_id_from_ptp_cur INTO l_ship_to_party_id;
2921 CLOSE party_id_from_ptp_cur;
2922 l_ship_to_ptp_id := GT_SHIP_TO_PARTY_TAX_PROF_ID(i);
2923 ELSE
2924 OPEN party_id_cur (GT_SHIPPING_TP_ID(i));
2925 FETCH party_id_cur INTO GT_SHIP_TO_PARTY_ID(i);
2926 CLOSE party_id_cur;
2927
2928 OPEN party_profile_id_cur (GT_SHIP_TO_PARTY_ID(i));
2929 FETCH party_profile_id_cur into l_ship_to_ptp_id;
2930 CLOSE party_profile_id_cur;
2931 --l_ship_to_party_id := GT_SHIPPING_TP_ID(i);
2932 l_ship_to_party_id := GT_SHIP_TO_PARTY_ID(i);
2933 END IF;
2934
2935 IF (g_level_procedure >= g_current_runtime_level ) THEN
2936 FND_LOG.STRING(g_level_procedure,
2937 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2938 'party_id_cur : l_ship_to_party_id '||to_char(l_ship_to_party_id));
2939 END IF;
2940
2941 IF GT_SHIP_TO_SITE_TAX_PROF_ID(i) IS NOT NULL THEN
2942 OPEN party_site_id_from_ptp_cur(GT_SHIP_TO_SITE_TAX_PROF_ID(i));
2943 FETCH party_site_id_from_ptp_cur INTO l_ship_to_site_id;
2944 CLOSE party_site_id_from_ptp_cur;
2945 l_ship_to_stp_id := GT_SHIP_TO_SITE_TAX_PROF_ID(i);
2946 ELSE
2947 OPEN party_site_id_cur (GT_SHIPPING_TP_SITE_ID(i));
2948 FETCH party_site_id_cur INTO GT_SHIP_TO_PARTY_SITE_ID(i);
2949 CLOSE party_site_id_cur;
2950
2951 OPEN site_profile_id_cur(GT_SHIP_TO_PARTY_SITE_ID(i));
2952 FETCH site_profile_id_cur INTO l_ship_to_stp_id;
2953 CLOSE site_profile_id_cur;
2954 --l_ship_to_site_id := GT_SHIPPING_TP_SITE_ID(i);
2955 l_ship_to_site_id := GT_SHIP_TO_PARTY_SITE_ID(i);
2956 END IF;
2957
2958 IF (g_level_procedure >= g_current_runtime_level ) THEN
2959 FND_LOG.STRING(g_level_procedure,
2960 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2961 'party_site_id_cur : l_ship_to_site_id '||to_char(l_ship_to_site_id));
2962 END IF;
2963
2964
2965
2966
2967 l_bill_ship := 'SHIP_TO';
2968
2969
2970 -- IF GT_SHIPPING_TP_ID(i) IS NOT NULL AND GT_SHIPPING_TP_ADDRESS_ID(i) IS NOT NULL THEN
2971
2972
2973 IF l_ship_to_site_id is not null and l_ship_to_party_id is not null THEN
2974 l_tbl_index_cust := dbms_utility.get_hash_value(to_char(l_ship_to_site_id)||(l_ship_to_party_id)||
2975 l_bill_ship, 1,8192);
2976
2977 IF g_cust_ship_ar_tbl.EXISTS(l_tbl_index_cust) THEN
2978 GT_SHIPPING_TP_NUMBER(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER ;
2979 GT_GDF_RA_CUST_SHIP_ATT10(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT10;
2980 GT_GDF_RA_CUST_SHIP_ATT12(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT12;
2981 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i) :=g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT8;
2982 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i) :=g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT9;
2983 GT_SHIPPING_TP_SITE_NAME(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_SITE_NAME;
2984 GT_SHIPPING_TP_TAX_REG_NUM(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_TAX_REG_NUM;
2985
2986 IF (g_level_procedure >= g_current_runtime_level ) THEN
2987 FND_LOG.STRING(g_level_procedure,
2988 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
2989 'Exists in the cache g_cust_ship_ar_tbl '||g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER);
2990 END IF;
2991
2992 ELSE
2993 OPEN cust_acct_cur (l_ship_to_site_id,
2994 l_ship_to_party_id,
2995 l_bill_ship);
2996 FETCH cust_acct_cur INTO GT_SHIPPING_TP_NUMBER(i),
2997 GT_GDF_RA_CUST_SHIP_ATT10(i),
2998 GT_GDF_RA_CUST_SHIP_ATT12(i),
2999 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i),
3000 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i),
3001 GT_SHIPPING_TP_SITE_NAME(i),
3002 GT_SHIPPING_TP_TAX_REG_NUM(i);
3003 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER := GT_SHIPPING_TP_NUMBER(i);
3004 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT10 := GT_GDF_RA_CUST_SHIP_ATT10(i);
3005 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT12 := GT_GDF_RA_CUST_SHIP_ATT12(i);
3006 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT8 := GT_GDF_RA_ADDRESSES_SHIP_ATT8(i);
3007 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT9 := GT_GDF_RA_ADDRESSES_SHIP_ATT9(i);
3008 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_SITE_NAME := GT_SHIPPING_TP_SITE_NAME(i);
3009 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_TAX_REG_NUM := GT_SHIPPING_TP_TAX_REG_NUM(i);
3010
3011 CLOSE cust_acct_cur;
3012 END IF;
3013 IF (g_level_procedure >= g_current_runtime_level ) THEN
3014 FND_LOG.STRING(g_level_procedure,
3015 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3016 'After assign to g_cust_ship_ar_tbl '||g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER);
3017 END IF;
3018
3019 l_tbl_index_party := dbms_utility.get_hash_value(to_char(l_ship_to_party_id)||
3020 l_bill_ship, 1,8192);
3021 IF g_party_ship_ar_tbl.EXISTS(l_tbl_index_party) THEN
3022
3023 GT_SHIPPING_TP_NAME_ALT(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME_ALT;
3024 GT_SHIPPING_TP_NAME(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME;
3025 GT_SHIPPING_TP_SIC_CODE(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_SIC_CODE;
3026 GT_SHIPPING_TP_NUMBER(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NUMBER;
3027 ELSE
3028 OPEN party_cur (l_ship_to_party_id);
3029 FETCH party_cur INTO GT_SHIPPING_TP_NAME(i),
3030 GT_SHIPPING_TP_NAME_ALT(i),
3031 GT_SHIPPING_TP_SIC_CODE(i),
3032 GT_SHIPPING_TP_NUMBER(i),
3033 GT_SHIPPING_TP_TAXPAYER_ID(i),
3034 GT_SHIPPING_TP_TAX_REG_NUM(i);
3035
3036 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME_ALT := GT_SHIPPING_TP_NAME_ALT(i);
3037 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME := GT_SHIPPING_TP_NAME(i);
3038 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_SIC_CODE := GT_SHIPPING_TP_SIC_CODE(i);
3039 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NUMBER := GT_SHIPPING_TP_NUMBER(i);
3040 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_TAXPAYER_ID := GT_SHIPPING_TP_TAXPAYER_ID(i);
3041 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_TAX_REG_NUM := GT_SHIPPING_TP_TAX_REG_NUM(i);
3042
3043 CLOSE party_cur;
3044 END IF;
3045 IF (g_level_procedure >= g_current_runtime_level ) THEN
3046 FND_LOG.STRING(g_level_procedure,
3047 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3048 'After assign to g_party_ship_ar_tbl '||g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME);
3049 END IF;
3050
3051 l_tbl_index_site := dbms_utility.get_hash_value(to_char(l_ship_to_site_id)||
3052 l_bill_ship, 1,8192);
3053 IF g_site_ship_ar_tbl.EXISTS(l_tbl_index_site) THEN
3054 GT_SHIPPING_TP_CITY(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_CITY;
3055 GT_SHIPPING_TP_COUNTY(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_COUNTY;
3056 GT_SHIPPING_TP_STATE(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_STATE;
3057 GT_SHIPPING_TP_PROVINCE(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_PROVINCE;
3058 GT_SHIPPING_TP_ADDRESS1(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS1;
3059 GT_SHIPPING_TP_ADDRESS2(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS2;
3060 GT_SHIPPING_TP_ADDRESS3(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS3;
3061 GT_SHIPPING_TP_ADDR_LINES_ALT(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDR_LINES_ALT;
3062 GT_SHIPPING_TP_COUNTRY(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_COUNTRY;
3063 GT_SHIPPING_TP_POSTAL_CODE(i) := g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_POSTAL_CODE;
3064 ELSE
3065 OPEN party_site_cur (l_ship_to_site_id);
3066 FETCH party_site_cur INTO GT_SHIPPING_TP_CITY(i),
3067 GT_SHIPPING_TP_COUNTY(i),
3068 GT_SHIPPING_TP_STATE(i),
3069 GT_SHIPPING_TP_PROVINCE(i),
3070 GT_SHIPPING_TP_ADDRESS1(i),
3071 GT_SHIPPING_TP_ADDRESS2(i),
3072 GT_SHIPPING_TP_ADDRESS3(i),
3073 GT_SHIPPING_TP_ADDR_LINES_ALT(i),
3074 GT_SHIPPING_TP_COUNTRY(i),
3075 GT_SHIPPING_TP_POSTAL_CODE(i);
3076 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_CITY := GT_SHIPPING_TP_CITY(i);
3077 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_COUNTY := GT_SHIPPING_TP_COUNTY(i);
3078 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_STATE := GT_SHIPPING_TP_STATE(i);
3079 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_PROVINCE := GT_SHIPPING_TP_PROVINCE(i);
3080 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS1 := GT_SHIPPING_TP_ADDRESS1(i);
3081 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS2 := GT_SHIPPING_TP_ADDRESS2(i);
3082 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDRESS3 := GT_SHIPPING_TP_ADDRESS3(i);
3083 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_ADDR_LINES_ALT := GT_SHIPPING_TP_ADDR_LINES_ALT(i);
3084 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_COUNTRY := GT_SHIPPING_TP_COUNTRY(i);
3085 g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_POSTAL_CODE := GT_SHIPPING_TP_POSTAL_CODE(i);
3086 CLOSE party_site_cur;
3087 END IF;
3088 IF (g_level_procedure >= g_current_runtime_level ) THEN
3089 FND_LOG.STRING(g_level_procedure,
3090 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3091 'After assign to g_site_ship_ar_tbl '||g_site_ship_ar_tbl(l_tbl_index_site).SHIPPING_TP_CITY);
3092 END IF;
3093
3094 END IF;
3095
3096
3097 IF (g_level_procedure >= g_current_runtime_level ) THEN
3098 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO.END',
3099 'ZX_AR_POPULATE_PKG: EXTRACT_PARTY_INFO(-)');
3100 END IF;
3101
3102 EXCEPTION
3103 WHEN OTHERS THEN
3104 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3105 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3106 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3107 FND_MSG_PUB.Add;
3108 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3109 FND_LOG.STRING(g_level_unexpected,
3110 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3111 g_error_buffer);
3112 END IF;
3113
3114 G_RETCODE := 2;
3115
3116
3117 END EXTRACT_PARTY_INFO;
3118
3119 PROCEDURE populate_meaning(
3120 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
3121 i BINARY_INTEGER)
3122 IS
3123 l_description VARCHAR2(240);
3124 l_meaning VARCHAR2(80);
3125 BEGIN
3126
3127 IF GT_TRX_CLASS(i) IS NOT NULL THEN
3128 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_TRANSACTION_CLASS_TYPE',
3129 GT_TRX_CLASS(i),
3130 l_meaning,
3131 l_description);
3132 GT_TRX_CLASS_MNG(i) := l_meaning;
3133 END IF;
3134
3135 IF P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE IS NOT NULL THEN
3136 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_REGISTER_TYPE',
3137 P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE,
3138 l_meaning,
3139 l_description);
3140
3141 GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := l_meaning;
3142 END IF;
3143
3144 IF GT_TAX_RATE_VAT_TRX_TYPE_CODE(i) IS NOT NULL THEN
3145 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_VAT_TRANSACTION_TYPE',
3146 GT_TAX_RATE_VAT_TRX_TYPE_CODE(i),
3147 l_meaning,
3148 l_description);
3149 GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) := l_description;
3150 END IF;
3151
3152 IF GT_TAX_EXCEPTION_REASON_CODE(i) IS NOT NULL THEN
3153 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_EXCEPTION_REASON',
3154 GT_TAX_EXCEPTION_REASON_CODE(i),
3155 l_meaning,
3156 l_description);
3157
3158 GT_TAX_EXCEPTION_REASON_MNG(i) := l_meaning;
3159 END IF;
3160
3161 IF GT_EXEMPT_REASON_CODE(i) IS NOT NULL THEN
3162 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_EXEMPTION_REASON',
3163 GT_EXEMPT_REASON_CODE(i),
3164 l_meaning,
3165 l_description);
3166
3167 GT_TAX_EXEMPT_REASON_MNG(i) := l_meaning;
3168 END IF;
3169
3170 END populate_meaning;
3171
3172 PROCEDURE UPDATE_REP_DETAIL_T(p_count IN NUMBER) IS
3173 i number;
3174 BEGIN
3175
3176 IF (g_level_procedure >= g_current_runtime_level ) THEN
3177 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_DETAIL_T.BEGIN',
3178 'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_DETAIL_T(+)');
3179 END IF;
3180
3181 FORALL i in 1..p_count
3182 UPDATE ZX_REP_TRX_DETAIL_T SET
3183 REP_CONTEXT_ID = G_REP_CONTEXT_ID,
3184 BILLING_TP_NUMBER = GT_BILLING_TP_NUMBER(i),
3185 BILLING_TP_TAX_REG_NUM = GT_BILLING_TP_TAX_REG_NUM(i),
3186 BILLING_TP_TAXPAYER_ID = GT_BILLING_TP_TAXPAYER_ID(i),
3187 BILLING_TP_SITE_NAME_ALT = GT_BILLING_TP_SITE_NAME_ALT(i),
3188 BILLING_TP_NAME = GT_BILLING_TP_NAME(i),
3189 BILLING_TP_NAME_ALT = GT_BILLING_TP_NAME_ALT(i),
3190 BILLING_TP_SIC_CODE = GT_BILLING_TP_SIC_CODE(i),
3191 BILLING_TP_CITY = GT_BILLING_TP_CITY(i),
3192 BILLING_TP_COUNTY = GT_BILLING_TP_COUNTY(i),
3193 BILLING_TP_STATE = GT_BILLING_TP_STATE(i),
3194 BILLING_TP_PROVINCE = GT_BILLING_TP_PROVINCE(i),
3195 BILLING_TP_ADDRESS1 = GT_BILLING_TP_ADDRESS1(i),
3196 BILLING_TP_ADDRESS2 = GT_BILLING_TP_ADDRESS2(i),
3197 BILLING_TP_ADDRESS3 = GT_BILLING_TP_ADDRESS3(i),
3198 BILLING_TP_ADDRESS_LINES_ALT = GT_BILLING_TP_ADDR_LINES_ALT(i),
3199 BILLING_TP_COUNTRY = GT_BILLING_TP_COUNTRY(i),
3200 BILLING_TP_POSTAL_CODE = GT_BILLING_TP_POSTAL_CODE(i),
3201 BILLING_TP_PARTY_NUMBER = GT_BILLING_TP_PARTY_NUMBER(i),
3202 BILLING_TRADING_PARTNER_ID = GT_BILLING_TP_ID(i),
3203 BILLING_TP_SITE_ID = GT_BILLING_TP_SITE_ID(i),
3204 BILLING_TP_ADDRESS_ID = GT_BILLING_TP_ADDRESS_ID(i),
3205 -- BILLING_TP_TAX_REP_FLAG = GT_BILLING_TP_TAX_REP_FLAG(i),
3206 BILLING_TP_SITE_NAME = GT_BILLING_TP_SITE_NAME(i),
3207 GDF_RA_ADDRESSES_BILL_ATT9 = GT_GDF_RA_ADDRESSES_BILL_ATT9(i),
3208 GDF_PARTY_SITES_BILL_ATT8 = GT_GDF_PARTY_SITES_BILL_ATT8(i),
3209 GDF_RA_CUST_BILL_ATT10 = GT_GDF_RA_CUST_BILL_ATT10(i),
3210 GDF_RA_CUST_BILL_ATT12 = GT_GDF_RA_CUST_BILL_ATT12(i),
3211 GDF_RA_ADDRESSES_BILL_ATT8 = GT_GDF_RA_ADDRESSES_BILL_ATT8(i),
3212 SHIPPING_TP_NUMBER = GT_SHIPPING_TP_NUMBER(i),
3213 SHIPPING_TP_TAX_REG_NUM = GT_SHIPPING_TP_TAX_REG_NUM(i),
3214 SHIPPING_TP_TAXPAYER_ID = GT_SHIPPING_TP_TAXPAYER_ID(i),
3215 -- SHIPPING_TP_SITE_NAME_ALT = GT_SHIPPING_TP_SITE_NAME_ALT(i),
3216 SHIPPING_TP_NAME = GT_SHIPPING_TP_NAME(i),
3217 SHIPPING_TP_NAME_ALT = GT_SHIPPING_TP_NAME_ALT(i),
3218 SHIPPING_TP_SIC_CODE = GT_SHIPPING_TP_SIC_CODE(i),
3219 SHIPPING_TP_CITY = GT_SHIPPING_TP_CITY(i),
3220 SHIPPING_TP_COUNTY = GT_SHIPPING_TP_COUNTY(i),
3221 SHIPPING_TP_STATE = GT_SHIPPING_TP_STATE(i),
3222 SHIPPING_TP_PROVINCE = GT_SHIPPING_TP_PROVINCE(i),
3223 SHIPPING_TP_ADDRESS1 = GT_SHIPPING_TP_ADDRESS1(i),
3224 SHIPPING_TP_ADDRESS2 = GT_SHIPPING_TP_ADDRESS2(i),
3225 SHIPPING_TP_ADDRESS3 = GT_SHIPPING_TP_ADDRESS3(i),
3226 -- SHIPPING_TP_ADDR_LINES_ALT = GT_SHIPPING_TP_ADDR_LINES_ALT(i),
3227 SHIPPING_TP_COUNTRY = GT_SHIPPING_TP_COUNTRY(i),
3228 SHIPPING_TP_POSTAL_CODE = GT_SHIPPING_TP_POSTAL_CODE(i),
3229 -- SHIPPING_TP_PARTY_NUMBER = GT_SHIPPING_TP_PARTY_NUMBER(i),
3230 -- SHIPPING_TRADING_PARTNER_ID = GT_SHIPPING_TRADING_PARTNER_ID(i),
3231 SHIPPING_TP_SITE_ID = GT_SHIPPING_TP_SITE_ID(i),
3232 SHIPPING_TP_ADDRESS_ID = GT_SHIPPING_TP_ADDRESS_ID(i),
3233 -- SHIPPING_TP_TAX_REP_FLAG = GT_SHIPPING_TP_TAX_REP_FLAG(i),
3234 SHIPPING_TP_SITE_NAME = GT_SHIPPING_TP_SITE_NAME(i),
3235 GDF_RA_ADDRESSES_SHIP_ATT9 = GT_GDF_RA_ADDRESSES_SHIP_ATT9(i),
3236 GDF_PARTY_SITES_SHIP_ATT8 = GT_GDF_PARTY_SITES_SHIP_ATT8(i),
3237 GDF_RA_CUST_SHIP_ATT10 = GT_GDF_RA_CUST_SHIP_ATT10(i),
3238 GDF_RA_CUST_SHIP_ATT12 = GT_GDF_RA_CUST_SHIP_ATT12(i),
3239 GDF_RA_ADDRESSES_SHIP_ATT8 = GT_GDF_RA_ADDRESSES_SHIP_ATT8(i),
3240 TRX_CLASS_MNG = GT_TRX_CLASS_MNG(i),
3241 TAX_RATE_CODE_REG_TYPE_MNG = GT_TAX_RATE_CODE_REG_TYPE_MNG(i),
3242 TAX_RATE_VAT_TRX_TYPE_DESC = GT_TAX_RATE_VAT_TRX_TYPE_DESC(i),
3243 TAXABLE_AMT = GT_TAXABLE_AMT(i),
3244 TAXABLE_AMT_FUNCL_CURR = GT_TAXABLE_AMT_FUNCL_CURR(i),
3245 TAX_AMT = GT_TAX_AMT(i),
3246 TAX_AMT_FUNCL_CURR = GT_TAX_AMT_FUNCL_CURR(i)
3247 WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
3248
3249 IF (g_level_procedure >= g_current_runtime_level ) THEN
3250 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_DETAIL_T.END',
3251 'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_DETAIL_T(-)');
3252 END IF;
3253
3254
3255 EXCEPTION
3256 WHEN OTHERS THEN
3257 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3258 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3259 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3260 FND_MSG_PUB.Add;
3261 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3262 FND_LOG.STRING(g_level_unexpected,
3263 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_DETAIL_T',
3264 g_error_buffer);
3265 END IF;
3266
3267 G_RETCODE := 2;
3268
3269 END UPDATE_REP_DETAIL_T;
3270
3271 PROCEDURE UPDATE_REP_ACTG_T(p_count IN NUMBER) IS
3272 i number;
3273 BEGIN
3274
3275 IF (g_level_procedure >= g_current_runtime_level ) THEN
3276 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG_T.BEGIN',
3277 'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_ACTG_T(+)');
3278 END IF;
3279
3280 FORALL i in 1..p_count
3281 UPDATE zx_rep_actg_ext_t SET
3282 TRX_ARAP_BALANCING_SEGMENT = GT_TRX_ARAP_BALANCING_SEGMENT(i),
3283 TRX_ARAP_NATURAL_ACCOUNT = GT_TRX_ARAP_NATURAL_ACCOUNT(i),
3284 TRX_TAXABLE_BALANCING_SEGMENT = GT_TRX_TAXABLE_BAL_SEG(i),
3285 TRX_TAXABLE_NATURAL_ACCOUNT = GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),
3286 TRX_TAX_BALANCING_SEGMENT = GT_TRX_TAX_BALANCING_SEGMENT(i),
3287 TRX_TAX_NATURAL_ACCOUNT = GT_TRX_TAX_NATURAL_ACCOUNT(i)
3288 WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
3289
3290 IF (g_level_procedure >= g_current_runtime_level ) THEN
3291 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG__T.END',
3292 'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_ACTG_T(-)');
3293 END IF;
3294
3295
3296 EXCEPTION
3297 WHEN OTHERS THEN
3298 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3299 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3300 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3301 FND_MSG_PUB.Add;
3302 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3303 FND_LOG.STRING(g_level_unexpected,
3304 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG_T',
3305 g_error_buffer);
3306 END IF;
3307
3308 G_RETCODE := 2;
3309
3310 END UPDATE_REP_ACTG_T;
3311
3312 PROCEDURE initialize_variables (
3313 p_count IN NUMBER) IS
3314 i number;
3315
3316 BEGIN
3317
3318 IF (g_level_procedure >= g_current_runtime_level ) THEN
3319 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.initialize_variables.BEGIN',
3320 'ZX_AR_ACTG_POPULATE_PKG: initialize_variables(+)');
3321 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.initialize_variables',
3322 'p_count : '||to_char(p_count));
3323 END IF;
3324
3325 FOR i IN 1.. p_count LOOP
3326 -- apai GT_REP_CONTEXT_ID(i) := NULL;
3327 GT_BILLING_TP_NUMBER(i) := NULL;
3328 GT_BILLING_TP_TAX_REG_NUM(i) := NULL;
3329 GT_BILLING_TP_TAXPAYER_ID(i) := NULL;
3330 GT_BILLING_TP_SITE_NAME_ALT(i) := NULL;
3331 GT_BILLING_TP_NAME(i) := NULL;
3332 GT_BILLING_TP_NAME_ALT(i) := NULL;
3333 GT_BILLING_TP_SIC_CODE(i) := NULL;
3334 GT_BILLING_TP_CITY(i) := NULL;
3335 GT_BILLING_TP_COUNTY(i) := NULL;
3336 GT_BILLING_TP_STATE(i) := NULL;
3337 GT_BILLING_TP_PROVINCE(i) := NULL;
3338 GT_BILLING_TP_ADDRESS1(i) := NULL;
3339 GT_BILLING_TP_ADDRESS2(i) := NULL;
3340 GT_BILLING_TP_ADDRESS3(i) := NULL;
3341 GT_BILLING_TP_ADDR_LINES_ALT(i) := NULL;
3342 GT_BILLING_TP_COUNTRY(i) := NULL;
3343 GT_BILLING_TP_POSTAL_CODE(i) := NULL;
3344 GT_BILLING_TP_PARTY_NUMBER(i) := NULL;
3345 -- GT_BILLING_TP_ID(i) := NULL;
3346 -- GT_BILLING_TP_SITE_ID(i) := NULL;
3347 -- GT_BILLING_TP_ADDRESS_ID(i) := NULL;
3348 -- GT_BILLING_TP_TAX_REP_FLAG(i) := NULL;
3349 GT_BILLING_TP_SITE_NAME(i) := NULL;
3350 GT_GDF_RA_ADDRESSES_BILL_ATT9(i) := NULL;
3351 GT_GDF_PARTY_SITES_BILL_ATT8(i) := NULL;
3352 GT_GDF_RA_CUST_BILL_ATT10(i) := NULL;
3353 GT_GDF_RA_CUST_BILL_ATT12(i) := NULL;
3354 GT_GDF_RA_ADDRESSES_BILL_ATT8(i) := NULL;
3355 GT_SHIPPING_TP_NUMBER(i) := NULL;
3356 GT_SHIPPING_TP_TAX_REG_NUM(i) := NULL;
3357 GT_SHIPPING_TP_TAXPAYER_ID(i) := NULL;
3358 -- GT_SHIPPING_TP_SITE_NAME_ALT(i) := NULL;
3359 GT_SHIPPING_TP_NAME(i) := NULL;
3360 GT_SHIPPING_TP_NAME_ALT(i) := NULL;
3361 GT_SHIPPING_TP_SIC_CODE(i) := NULL;
3362 GT_SHIPPING_TP_CITY(i) := NULL;
3363 GT_SHIPPING_TP_COUNTY(i) := NULL;
3364 GT_SHIPPING_TP_STATE(i) := NULL;
3365 GT_SHIPPING_TP_PROVINCE(i) := NULL;
3366 GT_SHIPPING_TP_ADDRESS1(i) := NULL;
3367 GT_SHIPPING_TP_ADDRESS2(i) := NULL;
3368 GT_SHIPPING_TP_ADDRESS3(i) := NULL;
3369 -- GT_SHIPPING_TP_ADDR_LINES_ALT(i) := NULL;
3370 GT_SHIPPING_TP_COUNTRY(i) := NULL;
3371 GT_SHIPPING_TP_POSTAL_CODE(i) := NULL;
3372 -- GT_SHIPPING_TP_PARTY_NUMBER(i) := NULL;
3373 -- GT_SHIPPING_TRADING_PARTNER_ID(i) := NULL;
3374 -- GT_SHIPPING_TP_SITE_ID(i) := NULL;
3375 -- GT_SHIPPING_TP_ADDRESS_ID(i) := NULL;
3376 -- GT_SHIPPING_TP_TAX_REP_FLAG(i) := NULL;
3377 GT_SHIPPING_TP_SITE_NAME(i) := NULL;
3378 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i) := NULL;
3379 GT_GDF_PARTY_SITES_SHIP_ATT8(i) := NULL;
3380 GT_GDF_RA_CUST_SHIP_ATT10(i) := NULL;
3381 GT_GDF_RA_CUST_SHIP_ATT12(i) := NULL;
3382 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i) := NULL;
3383 GT_TRX_CLASS_MNG(i) := NULL;
3384 GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := NULL;
3385 GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) := NULL;
3386 -- New --
3387 GT_BILLING_TP_NUMBER(i) := NULL;
3388 GT_GDF_RA_CUST_BILL_ATT10(i) := NULL;
3389 GT_GDF_RA_CUST_BILL_ATT12(i) := NULL;
3390 GT_GDF_RA_ADDRESSES_BILL_ATT8(i) := NULL;
3391 GT_GDF_RA_ADDRESSES_BILL_ATT9(i) := NULL;
3392 GT_BILLING_TP_SITE_NAME(i) := NULL;
3393 GT_BILLING_TP_TAX_REG_NUM(i) := NULL;
3394 GT_BILLING_TP_NAME(i) := NULL;
3395 GT_BILLING_TP_NAME_ALT(i) := NULL;
3396 GT_BILLING_TP_SIC_CODE(i) := NULL;
3397 GT_BILLING_TP_NUMBER(i) := NULL;
3398 GT_BILLING_TP_CITY(i) := NULL;
3399 GT_BILLING_TP_COUNTY(i) := NULL;
3400 GT_BILLING_TP_STATE(i) := NULL;
3401 GT_BILLING_TP_PROVINCE(i) := NULL;
3402 GT_BILLING_TP_ADDRESS1(i) := NULL;
3403 GT_BILLING_TP_ADDRESS2(i) := NULL;
3404 GT_BILLING_TP_ADDRESS3(i) := NULL;
3405 GT_BILLING_TP_ADDR_LINES_ALT(i) := NULL;
3406 GT_BILLING_TP_COUNTRY(i) := NULL;
3407 GT_BILLING_TP_POSTAL_CODE(i) := NULL;
3408 GT_SHIPPING_TP_NUMBER(i) := NULL;
3409 GT_GDF_RA_CUST_SHIP_ATT10(i) := NULL;
3410 GT_GDF_RA_CUST_SHIP_ATT12(i) := NULL;
3411 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i) := NULL;
3412 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i) := NULL;
3413 GT_SHIPPING_TP_SITE_NAME(i) := NULL;
3414 GT_SHIPPING_TP_TAX_REG_NUM(i) := NULL;
3415 GT_SHIPPING_TP_NAME(i) := NULL;
3416 GT_SHIPPING_TP_NAME_ALT(i) := NULL;
3417 GT_SHIPPING_TP_SIC_CODE(i) := NULL;
3418 GT_SHIPPING_TP_NUMBER(i) := NULL;
3419 GT_SHIPPING_TP_CITY(i) := NULL;
3420 GT_SHIPPING_TP_COUNTY(i) := NULL;
3421 GT_SHIPPING_TP_STATE(i) := NULL;
3422 GT_SHIPPING_TP_PROVINCE(i) := NULL;
3423 GT_SHIPPING_TP_ADDRESS1(i) := NULL;
3424 GT_SHIPPING_TP_ADDRESS2(i) := NULL;
3425 GT_SHIPPING_TP_ADDRESS3(i) := NULL;
3426 GT_SHIPPING_TP_ADDR_LINES_ALT(i) := NULL;
3427 GT_SHIPPING_TP_COUNTRY(i) := NULL;
3428 GT_SHIPPING_TP_POSTAL_CODE(i) := NULL;
3429
3430 END LOOP;
3431
3432 IF (g_level_procedure >= g_current_runtime_level ) THEN
3433 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.initialize_variables.END',
3434 'ZX_AR_ACTG_POPULATE_PKG: initialize_variables(-)');
3435 END IF;
3436
3437 EXCEPTION
3438 WHEN OTHERS THEN
3439 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3440 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3441 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3442 FND_MSG_PUB.Add;
3443 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3444 FND_LOG.STRING(g_level_unexpected,
3445 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.initialize_variables',
3446 g_error_buffer);
3447 END IF;
3448
3449 END initialize_variables ;
3450
3451 END ZX_AR_ACTG_POPULATE_PKG;