[Home] [Help]
PACKAGE BODY: APPS.ZX_TPI_SERVICES_PKG
Source
1 PACKAGE BODY ZX_TPI_SERVICES_PKG AS
2 /* $Header: zxiftpisrvcpkgb.pls 120.106.12020000.5 2013/03/12 07:22:02 ssohal ship $ */
3 /* ======================================================================*
4 | Global Data Types |
5 * ======================================================================*/
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_TPI_SERVICES_PKG';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_TPI_SERVICES_PKG.';
16
17 NUMBER_DUMMY CONSTANT NUMBER(15):= -999999999999999;
18
19
20 -- Bug 5417887: Tables to hold zx_lines_det_factor_attributes to avoid any further fetch on it.
21 TYPE varchar_30_idx_bi_tbl_type is table of VARCHAR2(30) index by BINARY_INTEGER;
22 record_type_code_tbl varchar_30_idx_bi_tbl_type;
23 line_level_action_tbl varchar_30_idx_bi_tbl_type;
24
25 TYPE varchar_1_idx_bi_tbl_type is table of VARCHAR2(1) index by BINARY_INTEGER;
26 partner_migrated_flag_tbl varchar_1_idx_bi_tbl_type;
27
28 -----------------------------------------------------------------------
29 -- PRIVATE PROCEDURE
30 -- get_migrated_doc_info
31 --
32 -- DESCRIPTION
33 -- If the source document is migrated for an incoming adjusted document,
34 -- then applicable regimes may not have been populated in ZX_TRX_LINE_APP_REGIMES.
35 -- In this scenario, return the standard regime to be populated in
36 -- ZX_TRX_LINE_APP_REGIMES.
37 --
38 -- CALLED BY
39 -- popl_pvrdr_info_tax_reg_tbl
40 -----------------------------------------------------------------------
41
42 PROCEDURE get_migrated_doc_info (
43 p_trx_line_index IN NUMBER,
44 x_migrated_tax_provider_id OUT NOCOPY NUMBER,
45 x_migrated_tax_regime_code OUT NOCOPY VARCHAR2,
46 x_migrated_tax_regime_id OUT NOCOPY NUMBER,
47 x_migrated_effective_from OUT NOCOPY DATE, -- Bug 5557565
48 x_migrated_effective_to OUT NOCOPY DATE, -- Bug 5557565
49 x_migrated_country_code OUT NOCOPY VARCHAR2,
50 x_return_status OUT NOCOPY VARCHAR2
51 ) IS
52 l_api_name CONSTANT VARCHAR2(30) := 'GET_MIGRATED_DOC_INFO';
53 l_context_info_rec ZX_API_PUB.context_info_rec_type;
54 l_mig_trx_date zx_lines.trx_date%type;
55 l_mig_first_pty_id number;
56 l_mig_exists number;
57 l_mig_glb_att_cat zx_lines.global_attribute_category%type;
58 BEGIN
59 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
60 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
61 END IF;
62
63 /*Set the return status to Success */
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66
67 IF ZX_API_PUB.G_PUB_SRVC in ('CALCULATE_TAX','IMPORT_DOCUMENT_WITH_TAX') THEN
68
69 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index) is not NULL THEN
70 BEGIN --- bug6024643
71 SELECT tax_provider_id,
72 tax_regime_code
73 INTO x_migrated_tax_provider_id,
74 x_migrated_tax_regime_code
75 FROM ZX_LINES
76 WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
77 AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
78 AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
79 AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
80 AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
81 AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index)
82 AND rownum = 1;
83
84 --- bug6024643
85 EXCEPTION
86 WHEN OTHERS THEN
87 null;
88 END;
89 ELSIF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index) is not NULL THEN
90 BEGIN --- bug6024643
91 SELECT tax_provider_id,
92 tax_regime_code,
93 trx_date,
94 content_owner_id,
95 global_attribute_category
96 INTO x_migrated_tax_provider_id,
97 x_migrated_tax_regime_code,
98 l_mig_trx_date,
99 l_mig_first_pty_id,
100 l_mig_glb_att_cat
101 FROM ZX_LINES
102 WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
103 AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
104 AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
105 AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_trx_line_index)
106 AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(p_trx_line_index)
107 AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_trx_line_index)
108 AND rownum = 1;
109 --- bug6024643
110 EXCEPTION
111 WHEN OTHERS THEN
112 null;
113 END;
114 IF x_migrated_tax_regime_code IS NOT NULL AND x_migrated_tax_provider_id > 0 THEN
115 BEGIN
116 SELECT 1
117 INTO l_mig_exists
118 FROM zx_srvc_subscriptions srvc,
119 zx_regimes_usages usg
120 WHERE srvc.regime_usage_id = usg.regime_usage_id
121 AND l_mig_trx_date between srvc.effective_from and nvl(srvc.effective_to,l_mig_trx_date)
122 AND srvc.srvc_provider_id = x_migrated_tax_provider_id
123 AND usg.first_pty_org_id = l_mig_first_pty_id
124 AND usg.tax_regime_code = x_migrated_tax_regime_code
125 AND rownum = 1;
126 EXCEPTION
127 WHEN OTHERS THEN
128 l_mig_exists := 0;
129 END;
130 IF Nvl(l_mig_exists,0) = 0 AND l_mig_glb_att_cat IS NOT NULL THEN
131 BEGIN
132 SELECT usg.tax_regime_code
133 into x_migrated_tax_regime_code
134 from zx_srvc_subscriptions srvc,
135 zx_regimes_usages usg
136 where srvc.regime_usage_id = usg.regime_usage_id
137 and l_mig_trx_date between srvc.effective_from and nvl(srvc.effective_to,l_mig_trx_date)
138 and srvc.enabled_flag = 'Y'
139 and srvc_provider_id = x_migrated_tax_provider_id
140 and usg.first_pty_org_id = l_mig_first_pty_id
141 and rownum = 1;
142 EXCEPTION
143 WHEN OTHERS THEN NULL;
144 END;
145 END IF;
146 END IF;
147 END IF;
148 IF x_migrated_tax_regime_code IS NOT NULL THEN --- bug6024643
149 SELECT tax_regime_id
150 , effective_from
151 , effective_to
152 , country_code
153 INTO x_migrated_tax_regime_id
154 , x_migrated_effective_from -- Bug 5557565
155 , x_migrated_effective_to -- Bug 5557565
156 , x_migrated_country_code
157 FROM ZX_REGIMES_B
158 WHERE tax_regime_code = x_migrated_tax_regime_code;
159 END IF; --- bug6024643
160 END IF;
161
162 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
163 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.END',
164 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
165 END IF;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
170 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
171 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
172 END IF;
173 RETURN;
174 END get_migrated_doc_info;
175
176
177 -----------------------------------------------------------------------
178 -- PRIVATE PROCEDURE
179 -- popl_all_regimes_tbl
180 --
181 -- DESCRIPTION
182 -- This procedure temporarily copies all applicable regimes for a line
183 -- into trx_line_app_regimes_tbl structure before bulk inserting into db
184 -- table ZX_TRX_LINE_APP_REGIMES
185 --
186 -- CALLED BY
187 -- popl_pvrdr_info_tax_reg_tbl
188 -----------------------------------------------------------------------
189 PROCEDURE popl_all_regimes_tbl (
190 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
191 p_trx_line_id IN NUMBER,
192 p_trx_level_type IN VARCHAR2,
193 p_tax_regime_id IN NUMBER,
194 p_tax_regime_code IN VARCHAR2,
195 p_tax_provider_id IN NUMBER,
196 x_return_status OUT NOCOPY VARCHAR2
197 ) IS
198 l_api_name CONSTANT VARCHAR2(30) := 'POPL_ALL_REGIMES_TBL';
199 l_count NUMBER;
200 BEGIN
201 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
202 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
203 END IF;
204
205 /*Set the return status to Success */
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207
208 /* Storing of eBTax regimes in zx_trx_line_app_regimes table is expensive from performance and storage perspective
209 By not storing these regimes, we will not be able to handle scenario of eBTax to tax provider switch scenario */
210
211 IF p_tax_provider_id IS NOT NULL THEN
212 l_count :=ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id.COUNT+1 ;
213 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
214 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
215 ' l_count = ' || l_count);
216 END IF;
217 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id(l_count) :=
218 p_event_class_rec.application_id;
219 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.entity_code(l_count) :=
220 p_event_class_rec.entity_code;
221 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.event_class_code(l_count) :=
222 p_event_class_rec.event_class_code;
223 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_id(l_count) := p_event_class_rec.trx_id;
224 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_line_id(l_count) := p_trx_line_id;
225 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_level_type(l_count) := p_trx_level_type;
226 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_id(l_count) := p_tax_regime_id;
227 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_code(l_count) := p_tax_regime_code;
228 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_provider_id(l_count) := p_tax_provider_id;
229 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.allow_tax_calculation_flag(l_count) :=
230 p_event_class_rec.process_for_applicability_flag;
231 ZX_GLOBAL_STRUCTURES_PKG.G_PTNR_SRVC_SUBSCR_FLAG := 'Y';
232 END IF;
233 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
234 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
235 END IF;
236 END popl_all_regimes_tbl;
237
238 -----------------------------------------------------------------------
239 -- PRIVATE PROCEDURE
240 -- set_detail_tax_line_values
241 --
242 -- DESCRIPTION
243 -- This procedure assigns the values that need to be populated to
244 -- zx_detail_tax_lines_gt
245 --
246 -- CALLED BY
247 -- ptnr_post_processing_calc_tax
248 -----------------------------------------------------------------------
249 PROCEDURE set_detail_tax_line_values (
250 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
251 p_ptnr_tax_line_ind IN NUMBER,
252 p_tax_provider_id IN NUMBER,
253 x_return_status OUT NOCOPY VARCHAR2
254 ) IS
255 l_api_name CONSTANT VARCHAR2(30) := 'SET_DETAIL_TAX_LINE_VALUES';
256 l_count NUMBER;
257
258 prev_tax_regime_code zx_regimes_b.tax_regime_code%type;
259 l_tax_regime_id zx_regimes_b.tax_regime_id%type;
260
261 prev_application_id zx_lines_det_factors.application_id%type;
262 prev_entity_code zx_lines_det_factors.entity_code%type;
263 prev_event_class_code zx_lines_det_factors.event_class_code%type;
264 prev_trx_id zx_lines_det_factors.trx_id%type;
265 prev_trx_line_id zx_lines_det_factors.trx_line_id%type;
266 prev_trx_level_type zx_lines_det_factors.trx_level_type%type;
267 l_lines_det_fact_rec zx_lines_det_factors%rowtype;
268 l_historical_flag VARCHAR2(1);
269 l_ptnr_exemption_indx VARCHAR2(4000);
270 l_tax_account_source_tax ZX_TAXES_B.TAX_ACCOUNT_SOURCE_TAX%TYPE;
271
272 BEGIN
273 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
274 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
275 END IF;
276
277 /*Set the return status to Success */
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279
280 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.internal_organization_id(p_ptnr_tax_line_ind) := p_event_class_rec.internal_organization_id;
281 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.application_id(p_ptnr_tax_line_ind) := p_event_class_rec.application_id;
282 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.entity_code(p_ptnr_tax_line_ind) := p_event_class_rec.entity_code;
283 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.event_class_code(p_ptnr_tax_line_ind) := p_event_class_rec.event_class_code;
284 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.event_type_code(p_ptnr_tax_line_ind) := p_event_class_rec.event_type_code;
285 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.doc_event_status(p_ptnr_tax_line_ind) := p_event_class_rec.doc_status_code;
286 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_event_class_code(p_ptnr_tax_line_ind) := p_event_class_rec.tax_event_class_code;
287 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_event_type_code(p_ptnr_tax_line_ind) := p_event_class_rec.tax_event_type_code;
288 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.ledger_id(p_ptnr_tax_line_ind) := p_event_class_rec.ledger_id;
289 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_entity_id(p_ptnr_tax_line_ind) := p_event_class_rec.legal_entity_id;
290 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_provider_id(p_ptnr_tax_line_ind) := p_tax_provider_id;
291 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.cancel_flag(p_ptnr_tax_line_ind) := 'N';
292 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.compounding_tax_flag(p_ptnr_tax_line_ind) := 'N';
293 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.reporting_only_flag(p_ptnr_tax_line_ind) := 'N';
294 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.copied_from_other_doc_flag(p_ptnr_tax_line_ind) := 'N';
295 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.historical_flag(p_ptnr_tax_line_ind) := 'N';
296 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.offset_flag(p_ptnr_tax_line_ind) := 'N';
297 -- Bug 8298174
298 -- ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.process_for_recovery_flag(p_ptnr_tax_line_ind) := 'N';
299 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.process_for_recovery_flag(p_ptnr_tax_line_ind) := NVL(p_event_class_rec.tax_recovery_flag, 'N');
300 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.purge_flag(p_ptnr_tax_line_ind) := 'N';
301 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.freeze_until_overridden_flag(p_ptnr_tax_line_ind) := 'N';
302 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.mrc_tax_line_flag(p_ptnr_tax_line_ind) := 'N'; -- Bug 5162537
303 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_flag(p_ptnr_tax_line_ind) := 'N';
304 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_line_number(p_ptnr_tax_line_ind) := NUMBER_DUMMY;
305 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.taxable_amt_tax_curr(p_ptnr_tax_line_ind) := ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.taxable_amount(p_ptnr_tax_line_ind);
306 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_determine_date(p_ptnr_tax_line_ind) := ZX_SECURITY.G_EFFECTIVE_DATE;
307
308 IF prev_trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(p_ptnr_tax_line_ind)
309 AND prev_trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(p_ptnr_tax_line_ind)
310 AND prev_application_id = p_event_class_rec.application_id
311 AND prev_entity_code = p_event_class_rec.entity_code
312 AND prev_event_class_code = p_event_class_rec.event_class_code
313 AND prev_trx_id = p_event_class_rec.trx_id
314 THEN
315 null;
316 ELSE
317 BEGIN
318 SELECT *
319 INTO l_lines_det_fact_rec
320 FROM zx_lines_det_factors
321 WHERE application_id = p_event_class_rec.application_id
322 AND entity_code = p_event_class_rec.entity_code
323 AND event_class_code = p_event_class_rec.event_class_code
324 AND trx_id = p_event_class_rec.trx_id
325 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(p_ptnr_tax_line_ind)
326 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(p_ptnr_tax_line_ind);
327 END;
328 prev_trx_line_id := ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(p_ptnr_tax_line_ind);
329 prev_trx_level_type := ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(p_ptnr_tax_line_ind);
330 prev_application_id := p_event_class_rec.application_id;
331 prev_entity_code := p_event_class_rec.entity_code;
332 prev_event_class_code := p_event_class_rec.event_class_code;
333 prev_trx_id := p_event_class_rec.trx_id;
334 END IF;
335
336 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_number(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.trx_number;
337 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_number(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.trx_line_number;
338 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_currency_code(p_ptnr_tax_line_ind) := nvl(l_lines_det_fact_rec.trx_currency_code, l_lines_det_fact_rec.trx_line_currency_code);
339 -- Begin 12946349
340 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.currency_conversion_date(p_ptnr_tax_line_ind) := nvl(l_lines_det_fact_rec.currency_conversion_date,l_lines_det_fact_rec.trx_line_currency_conv_date);
341 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.currency_conversion_rate(p_ptnr_tax_line_ind) := nvl(l_lines_det_fact_rec.currency_conversion_rate,l_lines_det_fact_rec.trx_line_currency_conv_rate);
342 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.currency_conversion_type(p_ptnr_tax_line_ind) := nvl(l_lines_det_fact_rec.currency_conversion_type,l_lines_det_fact_rec.trx_line_currency_conv_type);
343 -- End 12946349
344 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_date(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.trx_date;
345 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.unit_price(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.unit_price;
346 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.line_amt(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.line_amt;
347 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_quantity(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.trx_line_quantity;
348 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_date(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.trx_date;
349 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_date(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.trx_line_date;
350 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_date(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.trx_date;
351 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_application_id(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.applied_from_application_id;
352 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_entity_code(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.applied_from_entity_code;
353 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_event_class_code(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.applied_from_event_class_code;
354 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_id(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.applied_from_trx_id;
355 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_line_id(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.applied_from_line_id;
356 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_level_type(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.applied_from_trx_level_type;
357 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_number(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.applied_from_trx_number;
358 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.adjusted_doc_application_id;
359 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.adjusted_doc_entity_code;
360 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.adjusted_doc_event_class_code;
361 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.adjusted_doc_trx_id;
362 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.adjusted_doc_line_id;
363 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.adjusted_doc_trx_level_type;
364 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_number(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.adjusted_doc_number;
365 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_date(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.adjusted_doc_date;
366 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.establishment_id(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.establishment_id;
367 record_type_code_tbl(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.record_type_code;
368 line_level_action_tbl(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.line_level_action;
369 partner_migrated_flag_tbl(p_ptnr_tax_line_ind) := l_lines_det_fact_rec.partner_migrated_flag;
370
371 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind) IS NOT NULL THEN
372 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
373 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
374 ' adjusted_doc_application_id = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind) ||
375 ' adjusted_doc_entity_code = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind) ||
376 ' adjusted_doc_event_class_code = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind) ||
377 ' adjusted_doc_trx_id = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind) ||
378 ' adjusted_doc_line_id = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind) ||
379 ' trx_level_type = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(p_ptnr_tax_line_ind));
380 END IF;
381
382 BEGIN
383 SELECT historical_flag INTO l_historical_flag
384 FROM ZX_LINES_DET_FACTORS
385 WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
386 AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
387 AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
388 AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
389 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
390 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind);
391 EXCEPTION
392 WHEN OTHERS THEN
393 l_historical_flag := NULL;
394 END;
395
396 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
397 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
398 ' l_historical_flag = ' || l_historical_flag);
399 END IF;
400
401 IF NVL(l_historical_flag,'N') = 'Y' THEN
402 BEGIN
403 SELECT tax_line_id
404 INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind)
405 FROM zx_lines
406 WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
407 AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
408 AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
409 AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
410 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
411 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind);
412 EXCEPTION
413 WHEN TOO_MANY_ROWS THEN
414 BEGIN
415 SELECT tax_line_id
416 INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind)
417 FROM zx_lines
418 WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
419 AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
420 AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
421 AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
422 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
423 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind)
424 AND INSTR(upper(tax),ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind)) > 0;
425 EXCEPTION
426 WHEN OTHERS THEN
427 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind) := NULL;
428 END;
429 WHEN OTHERS THEN
430 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind) := NULL;
431 END;
432 ELSE -- Not a migrated invoice
433 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind) := NULL;
434 END IF;
435
436 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind) IS NULL THEN
437 BEGIN
438 SELECT tax_line_id
439 INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind)
440 FROM zx_lines
441 WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
442 AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
443 AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
444 AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
445 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
446 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind)
447 AND tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind)
448 AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind)
449 AND tax_rate_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(p_ptnr_tax_line_ind);
450 EXCEPTION
451 WHEN TOO_MANY_ROWS THEN
452 BEGIN
453 SELECT tax_line_id
454 INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind)
455 FROM zx_lines
456 WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
457 AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
458 AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
459 AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
460 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
461 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind)
462 AND tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind)
463 AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind)
464 AND tax_rate_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(p_ptnr_tax_line_ind)
465 AND tax_rate = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_percentage(p_ptnr_tax_line_ind);
466 EXCEPTION
467 WHEN TOO_MANY_ROWS THEN
468 BEGIN
469 SELECT tax_line_id
470 INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind)
471 FROM zx_lines
472 WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
473 AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
474 AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
475 AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
476 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
477 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind)
478 AND tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind)
479 AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind)
480 AND tax_rate_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(p_ptnr_tax_line_ind)
481 AND tax_rate = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_percentage(p_ptnr_tax_line_ind)
482 AND tax_apportionment_line_number = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_line_number(p_ptnr_tax_line_ind);
483 EXCEPTION
484 WHEN OTHERS THEN
485 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind) := NULL;
486 END;
487 WHEN OTHERS THEN
488 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind) := NULL;
489 END;
490 WHEN OTHERS THEN
491 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind) := NULL;
492 END;
493 END IF;
494 ELSE -- Not an applied credit memo
495 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind) := NULL;
496 END IF;
497
498 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
499 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
500 ' adjusted_doc_tax_line_id = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind));
501 END IF;
502 IF prev_tax_regime_code IS NULL
503 OR prev_tax_regime_code <> ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind) THEN
504 BEGIN
505 SELECT tax_regime_id
506 INTO l_tax_regime_id
507 FROM zx_regimes_b
508 WHERE tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind);
509 END;
510 prev_tax_regime_code := ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind);
511 END IF;
512 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_regime_id(p_ptnr_tax_line_ind) := l_tax_regime_id;
513
514 BEGIN
515 SELECT tax_jurisdiction_id
516 INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction_id(p_ptnr_tax_line_ind)
517 FROM ZX_JURISDICTIONS_B
518 WHERE tax_jurisdiction_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction(p_ptnr_tax_line_ind);
519 EXCEPTION WHEN OTHERS THEN
520 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction_id(p_ptnr_tax_line_ind) := NULL;
521 END;
522
523 -- adding code to populate exemption details in partner calculated tax lines
524 BEGIN
525 SELECT TAX_ACCOUNT_SOURCE_TAX
526 INTO l_tax_account_source_tax
527 FROM ZX_SCO_TAXES_B_V
528 WHERE tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind)
529 AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind)
530 AND ( ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_date(p_ptnr_tax_line_ind) >= effective_from
531 AND (ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_date(p_ptnr_tax_line_ind) <= effective_to
532 OR effective_to IS NULL));
533 EXCEPTION
534 WHEN OTHERS THEN
535 NULL;
536 END;
537 l_ptnr_exemption_indx := to_char(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_id(p_ptnr_tax_line_ind)) || '$' ||
538 to_char(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(p_ptnr_tax_line_ind)) || '$' ||
539 NVL(l_tax_account_source_tax, ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind)) || '$' ||
540 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind) || '$' ||
541 to_char(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_provider_id(p_ptnr_tax_line_ind));
542
543 IF ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl.EXISTS(l_ptnr_exemption_indx)
544 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_id
545 = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_id(p_ptnr_tax_line_ind)
546 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_line_id
547 = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(p_ptnr_tax_line_ind)
548 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax
549 = NVL(l_tax_account_source_tax, ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind))
550 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_regime_code
551 = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind)
552 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_provider_id
553 = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_provider_id(p_ptnr_tax_line_ind)
554 THEN
555
556 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_exemption_id(p_ptnr_tax_line_ind) := ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_exemption_id;
557 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_certificate_number(p_ptnr_tax_line_ind) := ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).exempt_certificate_number;
558
559 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind) = 'STATE' THEN
560 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(p_ptnr_tax_line_ind) :=
561 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason_code;
562 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(p_ptnr_tax_line_ind) :=
563 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason;
564 ELSIF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind) = 'COUNTY' THEN
565 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(p_ptnr_tax_line_ind) :=
566 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason_code;
567 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(p_ptnr_tax_line_ind) :=
568 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason;
569 ELSIF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind) = 'CITY' THEN
570 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(p_ptnr_tax_line_ind) :=
571 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason_code;
572 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(p_ptnr_tax_line_ind) :=
573 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason;
574 ELSIF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind) = 'DISTRICT' THEN
575 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(p_ptnr_tax_line_ind) :=
576 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).di_exempt_reason_code;
577 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(p_ptnr_tax_line_ind) :=
578 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).di_exempt_reason;
579 ELSE
580 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(p_ptnr_tax_line_ind) :=
581 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason_code;
582 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(p_ptnr_tax_line_ind) :=
583 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason;
584 END IF;
585
586 ELSE
587 IF NOT ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_exemption_id.exists(p_ptnr_tax_line_ind) THEN
588 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_exemption_id(p_ptnr_tax_line_ind) := NULL;
589 END IF;
590 IF NOT ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code.exists(p_ptnr_tax_line_ind) THEN
591 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(p_ptnr_tax_line_ind) := NULL;
592 END IF;
593 IF NOT ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason.exists(p_ptnr_tax_line_ind) THEN
594 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(p_ptnr_tax_line_ind) := NULL;
595 END IF;
596 END IF;
597
598 IF NOT ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text1.exists(p_ptnr_tax_line_ind) THEN
599 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text1(p_ptnr_tax_line_ind) := NULL;
600 END IF;
601 IF NOT ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text2.exists(p_ptnr_tax_line_ind) THEN
602 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text2(p_ptnr_tax_line_ind) := NULL;
603 END IF;
604 IF NOT ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text3.exists(p_ptnr_tax_line_ind) THEN
605 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text3(p_ptnr_tax_line_ind) := NULL;
606 END IF;
607
608 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
609 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
610 ' tax_exemption_id = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_exemption_id(p_ptnr_tax_line_ind));
611 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
612 ' exempt_reason_code = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(p_ptnr_tax_line_ind));
613 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
614 ' exempt_reason = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(p_ptnr_tax_line_ind));
615 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
616 ' exempt_certificate_number = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_certificate_number(p_ptnr_tax_line_ind));
617 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
618 ' establishment_id = ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.establishment_id(p_ptnr_tax_line_ind));
619 END IF;
620
621 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
622 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||': '||l_api_name||'(-)');
623 END IF;
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
628 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
629 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
630 END IF;
631 RETURN;
632 END set_detail_tax_line_values;
633
634 -----------------------------------------------------------------------
635 -- PRIVATE PROCEDURE
636 -- exemption_handling
637 --
638 -- DESCRIPTION
639 --
640 -- CALLED BY
641 -----------------------------------------------------------------------
642 /*****************TBD*************************************************
643 PROCEDURE exemption_handling (
644 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
645 p_trx_line_id IN NUMBER,
646 p_trx_level_type IN VARCHAR2,
647 p_tax_regime_id IN NUMBER,
648 p_tax_regime_code IN VARCHAR2,
649 p_tax_provider_id IN NUMBER,
650 x_return_status OUT NOCOPY VARCHAR2
651 ) IS
652 l_api_name CONSTANT VARCHAR2(30) := 'EXEMPTION_HANDLING';
653 l_count NUMBER;
654 BEGIN
655 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
656 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
657 END IF;
658
659 --Set the return status to Success
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 FOR exemption_index IN ZX_PTNR_SRVC_INTGRTN_PKG.g_exemption_tbl.TAX.FIRST..
663 nvl(ZX_PTNR_SRVC_INTGRTN_PKG.g_exemption_tbl.TAX.LAST,0)
664 LOOP
665 SELECT exempt_certificate_number
666 FROM ZX_EXEMPTIONS
667 WHERE content_owner_id = p_event_class_rec.first_pty_org_id
668 AND product_id = ??
669 AND inventory_org_id = ??
670 AND exemption_status_code = ??
671 AND exempt_certificate_number = ZX_PTNR_SRVC_INTGRTN_PKG.g_exemption_tbl(exemption_index).EXEMPT_CERTIFICATE_NUMBER
672 AND exempt_reason_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_exemption_tbl(exemption_index).EXEMPT_REASON_CODE
673 AND duplicate_exemption = ??
674 AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_exemption_tbl(exemption_index).TAX
675 AND tax_status_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl(p_tax_line_index).TAX_STATUS_CODE
676 AND tax_jurisdiction_id = ??
677 AND tax_rate_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl(p_tax_line_index).TAX_STATUS_CODE
678 AND effective_from = G_EFFECTIVE_DATE
679 AND tax_regime_code = ??
680 AND party_tax_profile_id = ??
681
682 ZX_TCM_GET_EXEMPT_PKG.get_tax_exemptions(
683 p_bill_to_cust_site_use_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_cust_acct_site_use_id(p_structure_index),
684 p_bill_to_cust_acct_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_third_pty_acct_id(p_structure_index),
685 p_bill_to_party_site_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_site_tax_prof_id(p_structure_index),
686 p_bill_to_party_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_party_tax_prof_id(p_structure_index),
687 p_sold_to_party_site_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trading_hq_site_tax_prof_id(p_structure_index),
688 p_sold_to_party_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trading_hq_party_tax_prof_id(p_structure_index),
689 p_inventory_org_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(p_structure_index),
690 p_inventory_item_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_id(p_structure_index),
691 p_exempt_certificate_number => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_certificate_number(p_structure_index),
692 p_reason_code => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_reason_code(p_structure_index),
693 p_exempt_control_flag => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exemption_control_flag(p_structure_index),
694 p_tax_date => l_tax_date,
695 p_tax_regime_code => l_tax_regime_code,
696 p_tax => l_tax,
697 p_tax_status_code => l_tax_status_code,
698 p_tax_rate_code => l_tax_rate_code,
699 p_tax_jurisdiction_id => l_tax_jurisdiction_id,
700 p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
701 p_event_class_rec => p_event_class_rec,
702 x_return_status => p_return_status,
703 x_exemption_rec => l_exemption_rec);
704 END LOOP;
705
706 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
707 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||': '||l_api_name||'(-)');
708 END IF;
709 END exemption_handling;
710 */
711
712 -----------------------------------------------------------------------
713 -- PUBLIC PROCEDURE
714 -- trx_line_app_regimes_tbl_hdl
715 --
716 -- DESCRIPTION
717 -- This is a table handler on ZX_TRX_LINE_APP_REGIMES
718 --
719 -- Argument of interest
720 -- p_event - takes following values
721 -- INSERT inserts at end of post calculation
722 -- SET_FLAG sets update_flag to indicate which transaction lines
723 -- are being updated;this will ease out the setting of partner
724 -- processing flag later
725 -- RESET_FLAG resets update_flag at the end of processing
726 -- DELETE Deletes all non-applicable regimes if provider not applicable
727 --
728 -- CALLED BY
729 -- ptnr_post_processing_calc_tax
730 -- ZX_SRVC_TYPS_PKG.calculate_tax_pvt
731 -- ZX_SRVC_TYPS_PKG.calculate_tax
732 -----------------------------------------------------------------------
733
734
735 PROCEDURE trx_line_app_regimes_tbl_hdl(
736 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
737 p_event IN VARCHAR2,
738 p_tax_regime_code IN VARCHAR2,
739 p_provider_id IN NUMBER,
740 p_trx_line_id IN NUMBER,
741 p_trx_level_type IN VARCHAR2,
742 x_return_status OUT NOCOPY VARCHAR2
743 )IS
744 l_api_name CONSTANT VARCHAR2(30):= 'TRX_LINE_APP_REGIMES_TBL_HDL';
745 l_dummy NUMBER;
746 l_context_info_rec ZX_API_PUB.context_info_rec_type;
747
748 BEGIN
749 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
750 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
751 END IF;
752
753 /*Set the return status to Success */
754 x_return_status := FND_API.G_RET_STS_SUCCESS;
755
756 IF p_event = 'INSERT' THEN
757 FOR i IN nvl(ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id.FIRST,0) .. nvl(ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id.LAST,-1)
758 LOOP
759 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id(i) = p_event_class_rec.application_id
760 AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.entity_code(i) = p_event_class_rec.entity_code
761 AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.event_class_code(i) = p_event_class_rec.event_class_code
762 AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_id(i) = p_event_class_rec.trx_id THEN
763 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
764 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
765 ' Application Id = ' || p_event_class_rec.application_id ||
766 ' Entity code = ' || p_event_class_rec.entity_code ||
767 ' Event Class code = ' || p_event_class_rec.event_class_code ||
768 ' Trx Id = ' || p_event_class_rec.trx_id ||
769 ' Trx Line Id = ' || ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_line_id(i) ||
770 ' Tax Regime code = ' || ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_code(i));
771 END IF;
772
773 BEGIN
774 SELECT 1
775 INTO l_dummy
776 FROM zx_trx_line_app_regimes
777 WHERE application_id = p_event_class_rec.application_id
778 AND entity_code = p_event_class_rec.entity_code
779 AND event_class_code = p_event_class_rec.event_class_code
780 AND trx_id = p_event_class_rec.trx_id
781 AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_line_id(i)
782 AND trx_level_type =ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_level_type(i)
783 AND tax_regime_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_code(i);
784 EXCEPTION
785 WHEN OTHERS THEN
786 BEGIN
787 INSERT INTO ZX_TRX_LINE_APP_REGIMES(APPLICATION_ID,
788 ENTITY_CODE,
789 EVENT_CLASS_CODE,
790 TRX_ID,
791 TRX_LINE_ID,
792 TRX_LEVEL_TYPE,
793 TAX_REGIME_ID,
794 TAX_REGIME_CODE,
795 TAX_PROVIDER_ID,
796 ALLOW_TAX_CALCULATION_FLAG,
797 PSEUDO_TAX_ONLY_LINE_FLAG,
798 CREATION_DATE,
799 CREATED_BY,
800 LAST_UPDATE_DATE,
801 LAST_UPDATED_BY,
802 LAST_UPDATE_LOGIN
803 )
804 VALUES (ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id(i),
805 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.entity_code(i),
806 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.event_class_code(i),
807 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_id(i),
808 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_line_id(i),
809 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_level_type(i),
810 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_id(i),
811 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_code(i),
812 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_provider_id(i),
813 ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.allow_tax_calculation_flag(i),
814 'N',
815 sysdate,
816 fnd_global.user_id,
817 sysdate,
818 fnd_global.user_id,
819 fnd_global.conc_login_id
820 ) ;
821 EXCEPTION
822 WHEN OTHERS THEN
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
824 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
825 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
826 END IF;
827 END;
828 END;
829 END IF;
830 END LOOP;
831 ELSIF p_event = 'DELETE' THEN
832 DELETE from ZX_TRX_LINE_APP_REGIMES
833 WHERE application_id = p_event_class_rec.application_id
834 AND entity_code = p_event_class_rec.entity_code
835 AND event_class_code = p_event_class_rec.event_class_code
836 AND trx_id = p_event_class_rec.trx_id
837 AND tax_provider_id = p_provider_id
838 AND tax_regime_code = p_tax_regime_code;
839 ELSIF p_event = 'SET_FLAG' THEN
840 UPDATE zx_trx_line_app_regimes
841 SET update_flag ='Y'
842 WHERE application_id = p_event_class_rec.application_id
843 AND entity_code = p_event_class_rec.entity_code
844 AND event_class_code = p_event_class_rec.event_class_code
845 AND trx_id = p_event_class_rec.trx_id
846 AND trx_line_id = p_trx_line_id
847 AND trx_level_type = p_trx_level_type;
848 ELSIF p_event = 'RESET_FLAG' THEN
849 UPDATE zx_trx_line_app_regimes
850 SET update_flag = null
851 WHERE application_id = p_event_class_rec.application_id
852 AND entity_code = p_event_class_rec.entity_code
853 AND event_class_code = p_event_class_rec.event_class_code
854 AND trx_id = p_event_class_rec.trx_id
855 AND update_flag = 'Y';
856 END IF;
857
858 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
859 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
860 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
861 END IF;
862
863 EXCEPTION
864 WHEN OTHERS THEN
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
866 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
867 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
868 END IF;
869 END trx_line_app_regimes_tbl_hdl;
870
871 -----------------------------------------------------------------------
872 -- PUBLIC PROCEDURE
873 -- popl_pvrdr_info_tax_reg_tbl
874 --
875 -- DESCRIPTION
876 -- This procedure loops over the returned detail tax regimes table to
877 -- determine if provider is applicable for the given regime for a given line.
878 -- On determining that a provider is applicable for a regime, it stamps the
879 -- provider id on the tax regime table so that TDS calculate tax process
880 -- may ignore it.
881 -- This procedure also invokes call to populate all the applicable regimes
882 -- for a line in trx_line_app_regime_tbl
883 --
884 -- CALLED BY
885 -- ZX_SRVC_TYPS_PKG.calculate_tax_pvt
886 -- ZX_SRVC_TYPS_PKG.import
887 -----------------------------------------------------------------------
888
889
890 PROCEDURE popl_pvrdr_info_tax_reg_tbl (
891 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
892 p_trx_line_index IN BINARY_INTEGER,
893 x_return_status OUT NOCOPY VARCHAR2
894 ) IS
895 l_api_name CONSTANT VARCHAR2(30) := 'POPL_PVRDR_INFO_TAX_REG_TBL';
896 l_provider_id NUMBER;
897 l_ptnr_migrated_flag VARCHAR2(1);
898 l_migrated_tax_provider_id NUMBER;
899 l_migrated_tax_regime_id NUMBER;
900 l_migrated_tax_regime_code VARCHAR2(30);
901 l_migrated_effective_from DATE;
902 l_migrated_effective_to DATE;
903 l_return_status VARCHAR2(1);
904 l_tax_regime_id NUMBER;
905 l_migrated_country_code ZX_REGIMES_B.country_code%TYPE;
906 l_context_info_rec ZX_API_PUB.context_info_rec_type;
907
908 /* Bug 5557565 */
909
910 CURSOR app_doc_regime_csr(l_index NUMBER) IS
911 SELECT ztlar.tax_regime_id,
912 ztlar.tax_regime_code,
913 ztlar.tax_provider_id,
914 regimes.effective_from,
915 regimes.effective_to,
916 regimes.country_code
917 FROM ZX_TRX_LINE_APP_REGIMES ztlar
918 , zx_regimes_b regimes
919 WHERE ztlar.application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(l_index)
920 AND ztlar.entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(l_index)
921 AND ztlar.event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(l_index)
922 AND ztlar.trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(l_index)
923 AND ztlar.trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(l_index)
924 AND ztlar.trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(l_index)
925 AND ztlar.tax_regime_id = regimes.tax_regime_id;
926
927 CURSOR adj_doc_regime_csr(l_index NUMBER) IS
928 SELECT ztlar.tax_regime_id,
929 ztlar.tax_regime_code,
930 ztlar.tax_provider_id,
931 regimes.effective_from,
932 regimes.effective_to,
933 regimes.country_code
934 FROM ZX_TRX_LINE_APP_REGIMES ztlar
935 , zx_regimes_b regimes
936 WHERE ztlar.application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(l_index)
937 AND ztlar.entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(l_index)
938 AND ztlar.event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(l_index)
939 AND ztlar.trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(l_index)
940 AND ztlar.trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(l_index)
941 AND ztlar.trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(l_index)
942 AND ztlar.tax_regime_id = regimes.tax_regime_id;
943
944 /* Bug 5557565 */
945
946 app_docs app_doc_regime_csr%ROWTYPE;
947 adj_docs adj_doc_regime_csr%ROWTYPE;
948
949
950 BEGIN
951 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
952 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
953 END IF;
954
955 --Set the return status to Success
956 x_return_status := FND_API.G_RET_STS_SUCCESS;
957
958 --Populate the product business group in the global variable - needed to determine the service provider
959 ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW := p_event_class_rec.prod_family_grp_code;
960
961 --ZX_TDS_CALC_SERVICES_PUB_PKG.get_tax_regimes does not return any regimes for the transaction lines in the following conditions.
962 -- applied_from_application_id is not null
963 -- adjusted_doc_application_id is not null
964 -- For these lines, fetch the applicable regimes from ZX_TRX_LINE_APP_REGIMES and
965 -- Populate ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl if regime does not already exist
966
967 --If applied from document
968
969 IF ZX_GLOBAL_STRUCTURES_PKG.detail_tax_regime_tbl.COUNT=0
970 AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index) is not NULL THEN
971 --APPLIED_FROM_DOCUMENT - check if original document is migrated
972 BEGIN
973 SELECT nvl(partner_migrated_flag, 'N') -- Bug 5007293
974 INTO l_ptnr_migrated_flag
975 FROM ZX_LINES_DET_FACTORS
976 WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
977 AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
978 AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
979 AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
980 AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
981 AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index);
982
983 EXCEPTION
984 WHEN NO_DATA_FOUND THEN
985 l_ptnr_migrated_flag := 'N';
986 END;
987
988 IF l_ptnr_migrated_flag = 'Y' THEN
989 --APPLIED_FROM_DOCUMENT - migrated: get information of regimes from ZX_LINES
990 get_migrated_doc_info (p_trx_line_index,
991 l_migrated_tax_provider_id,
992 l_migrated_tax_regime_code,
993 l_migrated_tax_regime_id,
994 l_migrated_effective_from, -- Bug 5557565
995 l_migrated_effective_to, -- Bug 5557565
996 l_migrated_country_code,
997 l_return_status
998 );
999 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1000 x_return_status := l_return_status;
1001 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1002 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1003 ' RETURN_STATUS = ' || x_return_status);
1004 END IF;
1005 RETURN;
1006 END IF;
1007 IF NOT ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.EXISTS(l_migrated_tax_regime_id) THEN
1008 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).tax_regime_id := l_migrated_tax_regime_id;
1009 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).tax_regime_code := l_migrated_tax_regime_code;
1010 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).tax_provider_id := l_migrated_tax_provider_id;
1011 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).country_code := l_migrated_country_code;
1012 /* Bug 5557565 */
1013 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).effective_from := l_migrated_effective_from;
1014 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).effective_to := l_migrated_effective_to;
1015 IF l_migrated_tax_provider_id is not null THEN
1016 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).partner_processing_flag := 'C';
1017 END IF;
1018 END IF;
1019 popl_all_regimes_tbl (p_event_class_rec,
1020 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index),
1021 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index),
1022 l_migrated_tax_regime_id,
1023 l_migrated_tax_regime_code,
1024 l_migrated_tax_provider_id,
1025 l_return_status
1026 );
1027
1028 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1029 x_return_status := l_return_status;
1030 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1031 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1032 ' RETURN_STATUS = ' || x_return_status);
1033 END IF;
1034 RETURN;
1035 END IF;
1036 ELSIF l_ptnr_migrated_flag ='N' THEN
1037 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1038 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' APPLIED_FROM_DOCUMENT :not migrated- retrieve the applicable regimes from zx_trx_line_app_regimes for original transaction');
1039 END IF;
1040 FOR app_docs IN app_doc_regime_csr(p_trx_line_index) LOOP
1041 IF NOT ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.EXISTS(app_docs.tax_regime_id) THEN
1042 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).tax_regime_id :=app_docs.tax_regime_id;
1043 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).tax_regime_code := app_docs.tax_regime_code;
1044 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).tax_provider_id := app_docs.tax_provider_id;
1045 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).country_code := app_docs.country_code;
1046
1047 /* Bug 5557565 */
1048 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).effective_from := app_docs.effective_from;
1049 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).effective_to := app_docs.effective_to;
1050 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).partner_processing_flag := 'C';
1051 END IF; --tax_regime_tbl exists
1052 popl_all_regimes_tbl (p_event_class_rec,
1053 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index),
1054 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index),
1055 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).tax_regime_id,
1056 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).tax_regime_code,
1057 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(app_docs.tax_regime_id).tax_provider_id,
1058 l_return_status
1059 );
1060 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1061 x_return_status := l_return_status;
1062 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1063 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1064 ' RETURN_STATUS = ' || x_return_status);
1065 END IF;
1066 RETURN;
1067 END IF;
1068 END LOOP; --loop on app_docs
1069 END IF; --l_ptnr_migrated_flag
1070 --If adjusted document
1071 ELSIF ZX_GLOBAL_STRUCTURES_PKG.detail_tax_regime_tbl.COUNT=0
1072 AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index) is not NULL THEN
1073 --ADJUSTED_DOC - check if original document is migrated
1074 BEGIN
1075 SELECT nvl(partner_migrated_flag, 'N') -- Bug 5007293
1076 INTO l_ptnr_migrated_flag
1077 FROM ZX_LINES_DET_FACTORS
1078 WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
1079 AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
1080 AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
1081 AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_trx_line_index)
1082 AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(p_trx_line_index)
1083 AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_trx_line_index);
1084 EXCEPTION
1085 WHEN no_data_found THEN
1086 l_ptnr_migrated_flag := 'N';
1087 END;
1088 IF l_ptnr_migrated_flag = 'Y' THEN
1089 --ADJUSTED DOC - migrated: get information of regimes from ZX_LINES
1090 get_migrated_doc_info (p_trx_line_index,
1091 l_migrated_tax_provider_id,
1092 l_migrated_tax_regime_code,
1093 l_migrated_tax_regime_id,
1094 l_migrated_effective_from, -- Bug 5557565
1095 l_migrated_effective_to, -- Bug 5557565
1096 l_migrated_country_code,
1097 l_return_status
1098 );
1099
1100 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1101 x_return_status := l_return_status;
1102 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1103 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1104 ' RETURN_STATUS = ' || x_return_status);
1105 END IF;
1106 RETURN;
1107 END IF;
1108
1109 IF l_migrated_tax_regime_id IS NOT NULL THEN --- bug6024643
1110 IF NOT ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.EXISTS(l_migrated_tax_regime_id) THEN
1111 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).tax_regime_id := l_migrated_tax_regime_id;
1112 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).tax_regime_code := l_migrated_tax_regime_code;
1113 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).tax_provider_id := l_migrated_tax_provider_id;
1114 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).country_code := l_migrated_country_code;
1115 /* Bug 5557565 */
1116 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).effective_from := l_migrated_effective_from;
1117 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).effective_to := l_migrated_effective_to;
1118 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_migrated_tax_regime_id).partner_processing_flag := 'C';
1119 END IF;
1120 popl_all_regimes_tbl (p_event_class_rec,
1121 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index),
1122 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index),
1123 l_migrated_tax_regime_id,
1124 l_migrated_tax_regime_code,
1125 l_migrated_tax_provider_id,
1126 l_return_status
1127 );
1128
1129 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1130 x_return_status := l_return_status;
1131 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1132 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1133 ' RETURN_STATUS = ' || x_return_status);
1134 END IF;
1135 RETURN;
1136 END IF;
1137 END IF; --- bug6024643
1138 ELSIF l_ptnr_migrated_flag ='N' THEN
1139 FOR adj_docs IN adj_doc_regime_csr(p_trx_line_index) LOOP
1140 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1141 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' ADJ_DOC:not migrated - $' || adj_docs.tax_regime_code || '$' || adj_docs.effective_from);
1142 END IF;
1143 IF NOT ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.EXISTS(adj_docs.tax_regime_id) THEN
1144 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).tax_regime_id :=adj_docs.tax_regime_id;
1145 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).tax_regime_code := adj_docs.tax_regime_code;
1146 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).tax_provider_id := adj_docs.tax_provider_id;
1147 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).country_code := adj_docs.country_code;
1148 /* Bug 5557565 */
1149 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).effective_from := adj_docs.effective_from;
1150 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).effective_to := adj_docs.effective_to;
1151 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).partner_processing_flag := 'C';
1152 END IF;--tax regime tbl exists
1153 popl_all_regimes_tbl (p_event_class_rec,
1154 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index),
1155 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index),
1156 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).tax_regime_id,
1157 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).tax_regime_code,
1158 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(adj_docs.tax_regime_id).tax_provider_id,
1159 l_return_status
1160 );
1161 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1162 x_return_status := l_return_status;
1163 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1164 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1165 ' RETURN_STATUS = ' || x_return_status);
1166 END IF;
1167 RETURN;
1168 END IF;
1169 END LOOP; --loop on adj_docs
1170 END IF; --l_ptnr_migrated_flag
1171 END IF;--adjusted/applied
1172
1173 -- The following logic will run only if detail_tax_regime_tbl structure.count <> 0
1174 IF ZX_GLOBAL_STRUCTURES_PKG.detail_tax_regime_tbl.count <> 0 THEN
1175
1176 FOR l_detail_regime_index IN nvl(ZX_GLOBAL_STRUCTURES_PKG.detail_tax_regime_tbl.FIRST,0)..nvl(ZX_GLOBAL_STRUCTURES_PKG.detail_tax_regime_tbl.LAST,-1)
1177 LOOP
1178 IF (ZX_GLOBAL_STRUCTURES_PKG.detail_tax_regime_tbl(l_detail_regime_index).trx_line_index=p_trx_line_index) THEN --Bug 4941881
1179 --In order to avoid unwanted multiple calls to the get_service_provider for same regime cache the hit regimes in a temporary structure
1180 l_tax_regime_id := ZX_GLOBAL_STRUCTURES_PKG.detail_tax_regime_tbl(l_detail_regime_index).tax_regime_id;
1181
1182 --Call routine to check if provider is applicable
1183
1184 get_service_provider (p_event_class_rec.application_id
1185 ,p_event_class_rec.entity_code
1186 ,p_event_class_rec.event_class_code
1187 ,ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_tax_regime_id).tax_regime_code
1188 ,l_provider_id
1189 ,l_return_status
1190 );
1191
1192 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1193 x_return_status := l_return_status;
1194 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1195 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1196 ' RETURN_STATUS = ' || x_return_status);
1197 END IF;
1198 RETURN;
1199 END IF;
1200
1201 IF l_provider_id <> 0 THEN
1202 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_tax_regime_id).tax_provider_id := l_provider_id;
1203 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_tax_regime_id).partner_processing_flag := 'C';
1204 END IF;
1205
1206 --Populate the table for all regimes applicable to transaction line
1207 --Call routine to populate all applicable regimes for line in ZX_TRX_LINE_APP_REGIMES
1208 popl_all_regimes_tbl (p_event_class_rec,
1209 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index),
1210 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index),
1211 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_tax_regime_id).tax_regime_id,
1212 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_tax_regime_id).tax_regime_code,
1213 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_tax_regime_id).tax_provider_id,
1214 l_return_status
1215 );
1216 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1217 x_return_status := l_return_status;
1218 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1219 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1220 ' RETURN_STATUS = ' || x_return_status);
1221 END IF;
1222 RETURN;
1223 END IF;
1224 END IF;
1225 END LOOP; --on detail tax regimes
1226
1227 ELSE
1228 FOR l_trx_regime_index IN nvl(ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.FIRST,0)..nvl(ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.LAST,-1)
1229 LOOP
1230 IF ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.EXISTS(l_trx_regime_index) THEN
1231 get_service_provider (p_event_class_rec.application_id
1232 ,p_event_class_rec.entity_code
1233 ,p_event_class_rec.event_class_code
1234 ,ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_trx_regime_index).tax_regime_code
1235 ,l_provider_id
1236 ,l_return_status
1237 );
1238
1239 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1240 x_return_status := l_return_status;
1241 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1242 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1243 ' RETURN_STATUS = ' || x_return_status);
1244 END IF;
1245 RETURN;
1246 END IF;
1247
1248 IF l_provider_id <> 0 THEN
1249 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_trx_regime_index).tax_provider_id := l_provider_id;
1250 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_trx_regime_index).partner_processing_flag := 'C';
1251 END IF;
1252 END IF;
1253 END LOOP; --on doc tax regimes
1254
1255 END IF;
1256
1257 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1258 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1259 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
1260 END IF;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1265 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1266 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
1267 END IF;
1268 END popl_pvrdr_info_tax_reg_tbl;
1269
1270 -----------------------------------------------------------------------
1271 -- PUBLIC PROCEDURE
1272 -- get_service_provider
1273 --
1274 -- DESCRIPTION
1275 -- This procedure returns the service provider for a given regime
1276 -- based on the subscription setup from ZX_SRVC_SUBSCRIPTIONS table
1277 --
1278 -- CALLED BY
1279 -- popl_pvrdr_info_tax_reg_tbl
1280 -- Tax forms
1281 -----------------------------------------------------------------------
1282
1283 PROCEDURE get_service_provider (
1284 p_application_id IN NUMBER,
1285 p_entity_code IN VARCHAR2,
1286 p_event_class_code IN VARCHAR2,
1287 p_tax_regime_code IN VARCHAR2,
1288 x_provider_id OUT NOCOPY NUMBER,
1289 x_return_status OUT NOCOPY VARCHAR2
1290 ) IS
1291
1292 l_api_name CONSTANT VARCHAR2(30) := 'GET_SERVICE_PROVIDER';
1293 l_provider_name VARCHAR2(50);
1294 -- l_context_info_rec ZX_API_PUB.context_info_rec_type;
1295 l_tbl_index BINARY_INTEGER;
1296
1297 BEGIN
1298 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1299 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1300 END IF;
1301
1302 --Set the return status to Success
1303 x_return_status := FND_API.G_RET_STS_SUCCESS;
1304
1305 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1306 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1307 ' Application Id = ' || to_char(p_application_id) ||
1308 ' Entity code = ' || p_entity_code ||
1309 ' Event Class code = ' || p_event_class_code ||
1310 ' ZX_SECURITY.G_FIRST_PARTY_ORG_ID = ' || ZX_SECURITY.G_FIRST_PARTY_ORG_ID ||
1311 ' Tax Regime code = ' || p_tax_regime_code ||
1312 ' ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW =' || ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW);
1313 END IF;
1314
1315 l_tbl_index := dbms_utility.get_hash_value(to_char(p_application_id)||'$$'||p_entity_code||'$$'||p_event_class_code||'$$'||to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID)||'$$'||p_tax_regime_code, 1, 8192);
1316
1317 IF tax_regime_tmp_tbl.exists(l_tbl_index)
1318 AND tax_regime_tmp_tbl(l_tbl_index).application_id = p_application_id
1319 AND tax_regime_tmp_tbl(l_tbl_index).entity_code = p_entity_code
1320 AND tax_regime_tmp_tbl(l_tbl_index).event_class_code = p_event_class_code
1321 AND tax_regime_tmp_tbl(l_tbl_index).first_pty_org_id = ZX_SECURITY.G_FIRST_PARTY_ORG_ID
1322 AND tax_regime_tmp_tbl(l_tbl_index).tax_regime_code = p_tax_regime_code THEN
1323 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1324 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1325 ' Using tax_regime_tmp_tbl cache');
1326 END IF;
1327
1328 x_provider_id := tax_regime_tmp_tbl(l_tbl_index).srvc_provider_id;
1329 ELSE
1330 --Check the service provider to call for calculating tax
1331 BEGIN
1332 SELECT srvc.srvc_provider_id
1333 INTO x_provider_id
1334 FROM ZX_SRVC_SUBSCRIPTIONS srvc,
1335 ZX_REGIMES_USAGES reg
1336 WHERE reg.tax_regime_code = p_tax_regime_code
1337 AND srvc.regime_usage_id = reg.regime_usage_id
1338 AND srvc.enabled_flag = 'Y'
1339 AND srvc.prod_family_grp_code = ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW
1340 AND ZX_SECURITY.G_EFFECTIVE_DATE between
1341 (srvc.effective_from) AND nvl( srvc.effective_to,ZX_SECURITY.G_EFFECTIVE_DATE)
1342 AND reg.first_pty_org_id = ZX_SECURITY.G_FIRST_PARTY_ORG_ID
1343 AND NOT EXISTS (SELECT 1
1344 FROM ZX_SRVC_SBSCRPTN_EXCLS excl
1345 WHERE excl.application_id = p_application_id
1346 AND excl.entity_code = p_entity_code
1347 AND excl.event_class_code = p_event_class_code
1348 AND excl.srvc_subscription_id = srvc.srvc_subscription_id
1349 );
1350 EXCEPTION
1351 WHEN NO_DATA_FOUND THEN
1352 x_provider_id := 0; --eBTax is the tax provider
1353 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1354 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1355 ' Tax Provider = eBTax' );
1356 END IF;
1357 END;
1358 tax_regime_tmp_tbl(l_tbl_index).srvc_provider_id := x_provider_id;
1359 tax_regime_tmp_tbl(l_tbl_index).application_id := p_application_id;
1360 tax_regime_tmp_tbl(l_tbl_index).entity_code := p_entity_code;
1361 tax_regime_tmp_tbl(l_tbl_index).event_class_code := p_event_class_code;
1362 tax_regime_tmp_tbl(l_tbl_index).first_pty_org_id := ZX_SECURITY.G_FIRST_PARTY_ORG_ID;
1363 tax_regime_tmp_tbl(l_tbl_index).tax_regime_code := p_tax_regime_code;
1364
1365 END IF;
1366
1367 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1368 IF x_provider_id <> 0 THEN
1369 BEGIN
1370 SELECT party_name
1371 INTO l_provider_name
1372 FROM HZ_PARTIES pty,
1373 ZX_PARTY_TAX_PROFILE ptp
1374 WHERE pty.party_id = ptp.party_id
1375 AND ptp.party_tax_profile_id = x_provider_id;
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 l_provider_name := NULL;
1379 END;
1380 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1381 ' Tax Provider = ' || l_provider_name);
1382 END IF;
1383 END IF;
1384
1385 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1386 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1387 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
1388 END IF;
1389
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1393 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1394 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
1395 END IF;
1396 END get_service_provider;
1397
1398
1399 -----------------------------------------------------------------------
1400 -- PUBLIC PROCEDURE
1401 -- Overloaded get_service_provider
1402 --
1403 -- DESCRIPTION
1404 -- This is an overloaded procedure which returns the service provider
1405 -- for a given regime based on the subscription setup from
1406 -- ZX_SRVC_SUBSCRIPTIONS table. It is called from the import exemptions
1407 -- which does not have information of the document
1408 --
1409 -- CALLED BY
1410 -- import_exemptions
1411
1412 -----------------------------------------------------------------------
1413 PROCEDURE get_service_provider (
1414 p_tax_regime_code IN VARCHAR2,
1415 x_provider_id OUT NOCOPY NUMBER,
1416 x_return_status OUT NOCOPY VARCHAR2
1417 ) IS
1418
1419 l_api_name CONSTANT VARCHAR2(30) := 'GET_SERVICE_PROVIDER';
1420 l_context_info_rec ZX_API_PUB.context_info_rec_type;
1421 l_product_family VARCHAR2(30);
1422 l_provider_name VARCHAR2(50);
1423
1424 BEGIN
1425 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1426 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1427 END IF;
1428
1429 --Set the return status to Success
1430 x_return_status := FND_API.G_RET_STS_SUCCESS;
1431
1432 --Check service provider to call for import exemptions
1433 SELECT srvc.srvc_provider_id
1434 INTO x_provider_id
1435 FROM ZX_SRVC_SUBSCRIPTIONS srvc,
1436 ZX_REGIMES_USAGES reg
1437 WHERE reg.tax_regime_code = p_tax_regime_code
1438 AND srvc.regime_usage_id = reg.regime_usage_id
1439 AND srvc.enabled_flag = 'Y'
1440 AND (ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW is null OR
1441 srvc.prod_family_grp_code = ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW)
1442 AND ZX_SECURITY.G_EFFECTIVE_DATE between
1443 (srvc.effective_from) AND nvl( srvc.effective_to,ZX_SECURITY.G_EFFECTIVE_DATE)
1444 AND reg.first_pty_org_id = ZX_SECURITY.G_FIRST_PARTY_ORG_ID;
1445
1446 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1447 SELECT party_name
1448 INTO l_provider_name
1449 FROM hz_parties pty,
1450 zx_party_tax_profile ptp
1451 WHERE pty.party_id = ptp.party_id
1452 AND ptp.party_tax_profile_id = x_provider_id;
1453
1454 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1455 ' Tax Provider = ' || l_provider_name);
1456 END IF;
1457
1458 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1459 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1460 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
1461 END IF;
1462
1463 EXCEPTION
1464 WHEN NO_DATA_FOUND THEN
1465 x_provider_id := 0; --eBTax is the tax provider
1466 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1467 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1468 ' Tax Provider = eBTax' );
1469 END IF;
1470 WHEN OTHERS THEN
1471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1472 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1473 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
1474 END IF;
1475 END get_service_provider;
1476
1477
1478 -----------------------------------------------------------------------
1479 -- PUBLIC PROCEDURE
1480 -- derive_ext_attrs
1481 --
1482 -- DESCRIPTION
1483 -- This procedure calls the main wrapper code (generated) for the user
1484 -- registered extensible procedures.
1485 --
1486 -- CALLED BY
1487 -- ZX_SRVC_TYPS_PKG.calculate_tax
1488 -- ZX_SRVC_TYPS_PKG.import
1489 -- ZX_SRVC_TYPS_PKG.override_tax_lines
1490 -- ZX_SRVC_TYPS_PKG.reverse_document
1491 -- ZX_SRVC_TYPS_PKG.discard_tax_only_lines
1492 -- ZX_SRVC_TYPS_PKG.synchronize_tax
1493 -- ZX_SRVC_TYPS_PKG.partner_inclusive_tax_override
1494 -----------------------------------------------------------------------
1495
1496 PROCEDURE derive_ext_attrs (
1497 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
1498 p_tax_regime_code IN VARCHAR2,
1499 p_provider_id IN NUMBER,
1500 p_service_type_code IN VARCHAR2,
1501 x_return_status OUT NOCOPY VARCHAR2
1502 ) IS
1503 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_EXT_ATTRS';
1504 l_context_info_rec ZX_API_PUB.context_info_rec_type;
1505 l_regime_index NUMBER;
1506 l_service_type_id NUMBER;
1507 l_context_ccid NUMBER;
1508 l_data_transfer_mode VARCHAR2(30);
1509 l_return_status VARCHAR2(1);
1510 l_user_extns BOOLEAN;
1511 l_dummy NUMBER;
1512 l_service_provider VARCHAR2(360);
1513
1514 CURSOR get_service_provider_csr (c_provider_id NUMBER) IS
1515 SELECT pty.party_name
1516 FROM zx_party_tax_profile ptp,
1517 hz_parties pty
1518 WHERE ptp.party_tax_profile_id = p_provider_id
1519 AND ptp.party_id = pty.party_id
1520 AND rownum = 1;
1521
1522 BEGIN
1523 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1524 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1525 END IF;
1526
1527 --Set the return status to Success
1528 x_return_status := FND_API.G_RET_STS_SUCCESS;
1529
1530 --Set the global variables
1531 ZX_TAX_PARTNER_PKG.G_TAX_REGIME_CODE := p_tax_regime_code;
1532 ZX_TAX_PARTNER_PKG.G_EVENT_CLASS_REC := p_event_class_rec;
1533
1534 IF p_service_type_code = 'DERIVE_LINE_ATTRS' AND ZX_API_PUB.G_PUB_SRVC = 'DISCARD_TAX_ONLY_LINES' THEN
1535 null;
1536 ELSE
1537 BEGIN
1538 l_user_extns := TRUE;
1539 SELECT reg.service_type_id,
1540 reg.context_ccid,
1541 srvc.data_transfer_code
1542 INTO l_service_type_id,
1543 l_context_ccid,
1544 l_data_transfer_mode
1545 FROM ZX_API_REGISTRATIONS reg,
1546 ZX_SERVICE_TYPES srvc,
1547 ZX_API_CODE_COMBINATIONS api
1548 WHERE api.code_combination_id = reg.context_ccid
1549 AND api.segment_attribute1 = p_tax_regime_code
1550 AND reg.api_owner_id = p_event_class_rec.first_pty_org_id
1551 AND srvc.service_type_id = reg.service_type_id
1552 AND srvc.service_type_code = p_service_type_code;
1553
1554 EXCEPTION
1555 WHEN NO_DATA_FOUND THEN
1556 l_user_extns := FALSE;
1557 END;
1558 END IF;
1559
1560 IF l_user_extns THEN
1561 --Populate the global attributes
1562 -- ZX_USER_EXT_PKG.G_EXT_ATTRS_INPUT_REC.business_flow := p_event_class_rec.prod_family_grp_code;
1563 -- ZX_USER_EXT_PKG.G_EXT_ATTRS_INPUT_REC.country_code := p_tax_regime_code;
1564 -- ZX_USER_EXT_PKG.G_EXT_ATTRS_INPUT_REC.transaction_service_type := ZX_API_PUB.G_PUB_SRVC;
1565 -- ZX_USER_EXT_PKG.G_EXT_ATTRS_INPUT_REC.derivation_level := p_service_type_code;
1566 -- ZX_USER_EXT_PKG.G_EXT_ATTRS_INPUT_REC.transaction_id := p_event_class_rec.trx_id;
1567 -- ZX_USER_EXT_PKG.G_EXT_ATTRS_INPUT_REC.event_id := p_event_class_rec.event_id;
1568
1569 --Call the procedure to derive user extensible parameters
1570 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1571 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1572 ' Call the registered User extensibe procedure ' || x_return_status);
1573 END IF;
1574
1575 -- Added Begin-Exception-End Block around ZX_USER_EXT_PKG.invoke_third_party_interface for Error Handling
1576 BEGIN
1577 ZX_USER_EXT_PKG.invoke_third_party_interface (p_event_class_rec.first_pty_org_id
1578 ,l_service_type_id
1579 ,l_context_ccid
1580 ,l_data_transfer_mode
1581 ,l_return_status
1582 );
1583
1584 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1585 x_return_status := l_return_status;
1586 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1587 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1588 ' RETURN_STATUS = ' || x_return_status);
1589 END IF;
1590 RETURN;
1591 END IF;
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 x_return_status := FND_API.G_RET_STS_ERROR ;
1595 IF p_provider_id = 1 THEN
1596 l_service_provider := 'VERTEX';
1597 ELSIF p_provider_id = 2 THEN
1598 l_service_provider := 'TAXWARE';
1599 ELSIF p_provider_id IS NOT NULL THEN
1600 OPEN get_service_provider_csr(p_provider_id);
1601 FETCH get_service_provider_csr INTO l_service_provider;
1602 CLOSE get_service_provider_csr;
1603 END IF;
1604
1605 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1606 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
1607 END IF;
1608 FND_MESSAGE.SET_NAME('ZX','ZX_PTNR_SERVICE_REQD');
1609 FND_MESSAGE.SET_TOKEN('SERVICE_PROVIDER',l_service_provider);
1610 ZX_API_PUB.add_msg(p_context_info_rec => ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
1611 RETURN;
1612 END;
1613 END IF;--no user extensible procedures defined
1614
1615 --Insert dummy records so that partner views do not fail in case the extensible
1616 --tables are not populated by user/extensible procedures are not registered.
1617 --Based on service type code we need to insert data in header,line GT tables.
1618 IF(p_service_type_code = 'DERIVE_HDR_ATTRS') THEN
1619 BEGIN
1620 SELECT 1
1621 INTO l_dummy
1622 FROM ZX_PRVDR_HDR_EXTNS_GT
1623 WHERE application_id = p_event_class_rec.application_id
1624 AND entity_code = p_event_class_rec.entity_code
1625 AND event_class_code = p_event_class_rec.event_class_code
1626 AND trx_id = p_event_class_rec.trx_id;
1627
1628 EXCEPTION
1629 WHEN NO_DATA_FOUND THEN
1630 INSERT INTO ZX_PRVDR_HDR_EXTNS_GT(event_class_code,
1631 application_id,
1632 entity_code,
1633 trx_id,
1634 provider_id,
1635 tax_regime_code,
1636 creation_date,
1637 created_by,
1638 last_update_date,
1639 last_updated_by
1640 )
1641 values (p_event_class_rec.EVENT_CLASS_CODE,
1642 p_event_class_rec.APPLICATION_ID,
1643 p_event_class_rec.ENTITY_CODE,
1644 p_event_class_rec.TRX_ID,
1645 p_provider_id,
1646 p_tax_regime_code,
1647 sysdate,
1648 fnd_global.user_id,
1649 sysdate,
1650 fnd_global.user_id
1651 );
1652 END;
1653 ELSIF(p_service_type_code = 'DERIVE_LINE_ATTRS') THEN
1654 BEGIN
1655 INSERT INTO ZX_PRVDR_LINE_EXTNS_GT (event_class_code,
1656 application_id,
1657 entity_code,
1658 trx_id,
1659 trx_line_id,
1660 trx_level_type,
1661 provider_id,
1662 tax_regime_code,
1663 creation_date,
1664 created_by,
1665 last_update_date,
1666 last_updated_by
1667 )
1668 SELECT lines.event_class_code,
1669 lines.application_id,
1670 lines.entity_code,
1671 lines.trx_id,
1672 lines.trx_line_id,
1673 lines.trx_level_type,
1674 p_provider_id,
1675 p_tax_regime_code,
1676 sysdate,
1677 fnd_global.user_id,
1678 sysdate,
1679 fnd_global.user_id
1680 FROM ZX_LINES_DET_FACTORS lines
1681 WHERE application_id = p_event_class_rec.application_id
1682 AND entity_code = p_event_class_rec.entity_code
1683 AND event_class_code = p_event_class_rec.event_class_code
1684 AND trx_id = p_event_class_rec.trx_id
1685 AND NOT EXISTS (SELECT 1
1686 FROM ZX_PRVDR_LINE_EXTNS_GT ext
1687 WHERE ext.application_id = lines.application_id
1688 AND ext.entity_code = lines.entity_code
1689 AND ext.event_class_code = lines.event_class_code
1690 AND ext.trx_id = lines.trx_id
1691 AND ext.trx_line_id = lines.trx_line_id
1692 AND ext.trx_level_type = lines.trx_level_type);
1693
1694 END;
1695 END IF; --End of p_service_type_code
1696
1697 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1698 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1699 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
1700 END IF;
1701
1702 EXCEPTION
1703 WHEN OTHERS THEN
1704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1705 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1706 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
1707 END IF;
1708 END derive_ext_attrs;
1709
1710 -----------------------------------------------------------------------
1711 -- PUBLIC PROCEDURE
1712 -- partner_pre_processing
1713 --
1714 -- DESCRIPTION
1715 -- This procedure is used to prepare the information to be passed to the tax
1716 -- partner services. It sets attributes in ZX_TRX_PRE_PROC_OPTIONS_GT so that
1717 -- partner interface views pick the correct data for the transaction and
1718 -- partner under consideration
1719 --
1720 -- CALLED BY
1721 -- ZX_SRVC_TYPS_PKG.calculate_tax
1722 -- ZX_SRVC_TYPS_PKG.import
1723 -- ZX_SRVC_TYPS_PKG.reverse_document
1724 -- ZX_SRVC_TYPS_PKG.synchronize_tax
1725 -- ZX_SRVC_TYPS_PKG.partner_inclusive_tax_override
1726 -----------------------------------------------------------------------
1727
1728 PROCEDURE partner_pre_processing(
1729 p_tax_regime_id IN NUMBER,
1730 p_tax_regime_code IN VARCHAR2,
1731 p_tax_provider_id IN NUMBER,
1732 p_ptnr_processing_flag IN VARCHAR2,
1733 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
1734 x_return_status OUT NOCOPY VARCHAR2
1735 ) IS
1736
1737 l_api_name CONSTANT VARCHAR2(30) := 'PARTNER_PRE_PROCESSING';
1738 l_provider_found NUMBER;
1739 l_return_status VARCHAR2(1);
1740 l_line_level_action VARCHAR2(30);
1741 l_context_info_rec ZX_API_PUB.context_info_rec_type;
1742 -- Bug# 4769082
1743 l_legal_entity_number ZX_TRX_PRE_PROC_OPTIONS_GT.legal_entity_number%type;
1744 l_establishment_number ZX_TRX_PRE_PROC_OPTIONS_GT.establishment_number%type; -- Bug 5139731
1745 l_application_short_name FND_APPLICATION.application_short_name%type;
1746 -- Bug# 4769082
1747 l_cnt_of_options_gt NUMBER;
1748 l_hq_estb_ptp_id ZX_LINES_DET_FACTORS.hq_estb_party_tax_prof_id%type; -- Bug 5090593
1749 l_party_id ZX_PARTY_TAX_PROFILE.party_id%type; -- Bug 5090593
1750 l_tax_provider_id NUMBER; -- Bug 5090593
1751
1752 BEGIN
1753 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1754 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1755 END IF;
1756
1757 --Set the return status to Success
1758 x_return_status := FND_API.G_RET_STS_SUCCESS;
1759
1760 IF p_tax_provider_id is NULL THEN
1761
1762 get_service_provider (p_event_class_rec.application_id
1763 ,p_event_class_rec.entity_code
1764 ,p_event_class_rec.event_class_code
1765 ,p_tax_regime_code
1766 ,l_tax_provider_id
1767 ,l_return_status
1768 );
1769
1770 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1771 x_return_status := l_return_status;
1772 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1773 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1774 ' RETURN_STATUS = ' || x_return_status);
1775 END IF;
1776 RETURN;
1777 END IF;
1778
1779 IF l_tax_provider_id > 0 THEN
1780
1781 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(p_tax_regime_id).tax_provider_id := l_tax_provider_id;
1782
1783 END IF;
1784
1785 ELSE
1786
1787 l_tax_provider_id := p_tax_provider_id;
1788
1789 END IF;
1790
1791 DELETE from ZX_TRX_PRE_PROC_OPTIONS_GT;
1792
1793 --Set the context for the partner interface views
1794 IF ZX_API_PUB.G_PUB_SRVC = 'SYNCHRONIZE_TAX_REPOSITORY' THEN
1795 BEGIN
1796 INSERT into ZX_TRX_PRE_PROC_OPTIONS_GT (INTERNAL_ORGANIZATION_ID,
1797 APPLICATION_ID,
1798 ENTITY_CODE,
1799 EVENT_CLASS_CODE,
1800 EVNT_CLS_MAPPING_ID,
1801 TAX_EVENT_TYPE_CODE,
1802 PROD_FAMILY_GRP_CODE,
1803 TRX_ID,
1804 TAX_REGIME_CODE,
1805 PARTNER_PROCESSING_FLAG,
1806 TAX_PROVIDER_ID,
1807 EVENT_ID,
1808 CREATION_DATE,
1809 CREATED_BY,
1810 LAST_UPDATE_DATE,
1811 LAST_UPDATED_BY,
1812 LAST_UPDATE_LOGIN
1813 )
1814 SELECT p_event_class_rec.internal_organization_id,
1815 p_event_class_rec.application_id,
1816 p_event_class_rec.entity_code,
1817 p_event_class_rec.event_class_code,
1818 clsmap.event_class_mapping_id,
1819 p_event_class_rec.tax_event_type_code,
1820 clsmap.prod_family_grp_code,
1821 p_event_class_rec.trx_id,
1822 p_tax_regime_code,
1823 p_ptnr_processing_flag,
1824 p_tax_provider_id,
1825 p_event_class_rec.event_id,
1826 sysdate,
1827 fnd_global.user_id,
1828 sysdate,
1829 fnd_global.user_id,
1830 fnd_global.conc_login_id
1831 FROM ZX_EVNT_CLS_MAPPINGS clsmap
1832 WHERE clsmap.application_id = p_event_class_rec.application_id
1833 AND clsmap.entity_code = p_event_class_rec.entity_code
1834 AND clsmap.event_class_code = p_event_class_rec.event_class_code;
1835 EXCEPTION WHEN OTHERS THEN
1836 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1837 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1838 ' SYNCHRONIZE_TAX_REPOSITORY: Exception while inserting into ZX_TRX_PRE_PROC_OPTIONS_GT');
1839 END IF;
1840 END;
1841 ELSE
1842 BEGIN
1843 SELECT line_level_action, hq_estb_party_tax_prof_id
1844 INTO l_line_level_action, l_hq_estb_ptp_id
1845 FROM zx_lines_det_factors
1846 WHERE application_id = p_event_class_rec.application_id
1847 AND entity_code = p_event_class_rec.entity_code
1848 AND event_class_code = p_event_class_rec.event_class_code
1849 AND trx_id = p_event_class_rec.trx_id
1850 AND line_level_action not in ('ALLOCATE_TAX_ONLY_ADJUSTMENT','ALLOCATE_LINE_ONLY_ADJUSTMENT','LINE_INFO_TAX_ONLY')
1851 AND rownum = 1;
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 SELECT line_level_action, hq_estb_party_tax_prof_id
1855 INTO l_line_level_action, l_hq_estb_ptp_id
1856 FROM zx_lines_det_factors
1857 WHERE application_id = p_event_class_rec.application_id
1858 AND entity_code = p_event_class_rec.entity_code
1859 AND event_class_code = p_event_class_rec.event_class_code
1860 AND trx_id = p_event_class_rec.trx_id
1861 AND rownum = 1;
1862 END;
1863
1864 -- Bug# 4769082
1865 BEGIN
1866 SELECT app.application_short_name
1867 INTO l_application_short_name
1868 FROM FND_APPLICATION app
1869 WHERE app.application_id = p_event_class_rec.application_id;
1870 END;
1871
1872 /* Bug 5090593: Products may or may not pass establishment_id.
1873 In the case where product does not pass the establishment_id, the PTP of hq establishment is internally derived.
1874 So, we need to use this PTP to get the establishment info.
1875 IF p_event_class_rec.establishment_id IS NOT NULL THEN
1876 BEGIN
1877 SELECT pty.party_number,
1878 xletb.name
1879 INTO l_legal_entity_number,
1880 l_establishment_name
1881 FROM XLE_ETB_PROFILES xletb,
1882 XLE_ENTITY_PROFILES xlent,
1883 HZ_PARTIES pty
1884 WHERE xletb.establishment_id = p_event_class_rec.establishment_id
1885 AND xlent.legal_entity_id = xletb.legal_entity_id
1886 AND pty.party_id = xlent.party_id
1887 AND xletb.main_establishment_flag = 'Y';
1888 EXCEPTION WHEN OTHERS THEN
1889 l_legal_entity_number := NULL;
1890 l_establishment_name := NULL;
1891 END;
1892 ELSE
1893 l_legal_entity_number := NULL;
1894 l_establishment_name := NULL;
1895 END IF;
1896 */
1897 /* Bug 5139731: Derivation of Establishment number and Legal entity number */
1898 IF l_hq_estb_ptp_id IS NOT NULL THEN
1899 BEGIN
1900 SELECT pty.party_number
1901 , pty.party_id
1902 INTO l_establishment_number
1903 , l_party_id
1904 FROM HZ_PARTIES pty,
1905 ZX_PARTY_TAX_PROFILE ptp
1906 WHERE ptp.party_tax_profile_id = l_hq_estb_ptp_id
1907 AND pty.party_id = ptp.party_id;
1908 EXCEPTION WHEN OTHERS THEN
1909 l_establishment_number := NULL;
1910 END;
1911 BEGIN
1912 SELECT pty.party_number
1913 INTO l_legal_entity_number
1914 FROM XLE_ETB_PROFILES xletb,
1915 HZ_PARTIES pty,
1916 XLE_ENTITY_PROFILES xep
1917 WHERE xletb.party_id = l_party_id
1918 AND xletb.legal_entity_id = xep.legal_entity_id
1919 AND pty.party_id = xep.party_id;
1920 EXCEPTION WHEN OTHERS THEN
1921 l_legal_entity_number := NULL;
1922 END;
1923 ELSE
1924 l_legal_entity_number := NULL;
1925 l_establishment_number := NULL;
1926 END IF;
1927 -- Bug# 4769082
1928
1929 BEGIN
1930 INSERT into ZX_TRX_PRE_PROC_OPTIONS_GT (INTERNAL_ORGANIZATION_ID,
1931 APPLICATION_ID,
1932 ENTITY_CODE,
1933 EVENT_CLASS_CODE,
1934 EVNT_CLS_MAPPING_ID,
1935 TAX_EVENT_TYPE_CODE,
1936 PROD_FAMILY_GRP_CODE,
1937 TRX_ID,
1938 TAX_REGIME_CODE,
1939 PARTNER_PROCESSING_FLAG,
1940 TAX_PROVIDER_ID,
1941 EVENT_ID,
1942 QUOTE_FLAG,
1943 RECORD_FLAG,
1944 RECORD_FOR_PARTNERS_FLAG,
1945 APPLICATION_SHORT_NAME,
1946 LEGAL_ENTITY_NUMBER,
1947 ESTABLISHMENT_NUMBER, -- Bug 5139731
1948 ALLOW_TAX_CALCULATION_FLAG,
1949 CREATION_DATE,
1950 CREATED_BY,
1951 LAST_UPDATE_DATE,
1952 LAST_UPDATED_BY,
1953 LAST_UPDATE_LOGIN
1954 )
1955 VALUES (p_event_class_rec.internal_organization_id,
1956 p_event_class_rec.application_id,
1957 p_event_class_rec.entity_code,
1958 p_event_class_rec.event_class_code,
1959 p_event_class_rec.event_class_mapping_id,
1960 p_event_class_rec.tax_event_type_code,
1961 p_event_class_rec.prod_family_grp_code,
1962 p_event_class_rec.trx_id,
1963 p_tax_regime_code,
1964 p_ptnr_processing_flag,
1965 p_tax_provider_id,
1966 p_event_class_rec.event_id,
1967 nvl(p_event_class_rec.quote_flag,'N'),
1968 p_event_class_rec.record_flag,
1969 p_event_class_rec.record_for_partners_flag,
1970 l_application_short_name,
1971 l_legal_entity_number,
1972 l_establishment_number, -- Bug 5139731
1973 decode(l_line_level_action,
1974 'ALLOCATE_TAX_ONLY_ADJUSTMENT', 'N',
1975 'ALLOCATE_LINE_ONLY_ADJUSTMENT', 'N', -- Bug 5007293
1976 'LINE_INFO_TAX_ONLY', 'N', -- Bug
1977 p_event_class_rec.process_for_applicability_flag),
1978 sysdate,
1979 fnd_global.user_id,
1980 sysdate,
1981 fnd_global.user_id,
1982 fnd_global.conc_login_id);
1983 EXCEPTION WHEN OTHERS THEN
1984 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1985 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1986 ' Exception while inserting into ZX_TRX_PRE_PROC_OPTIONS_GT');
1987 END IF;
1988 END;
1989
1990 END IF; -- API is synchronize_tax
1991
1992 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1993 BEGIN
1994 SELECT count(*)
1995 INTO l_cnt_of_options_gt
1996 FROM ZX_TRX_PRE_PROC_OPTIONS_GT;
1997 EXCEPTION WHEN OTHERS THEN
1998 l_cnt_of_options_gt := 0;
1999 END;
2000 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2001 'The record is inserted in ZX_TRX_PRE_PROC_OPTIONS_GT = ' || l_cnt_of_options_gt);
2002 END IF;
2003 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2004 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
2005 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
2006 END IF;
2007
2008 EXCEPTION
2009 WHEN NO_DATA_FOUND THEN
2010 ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(p_tax_regime_id).partner_processing_flag := 'F';
2011 WHEN OTHERS THEN
2012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2013 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
2014 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
2015 END IF;
2016 END partner_pre_processing;
2017
2018
2019 -----------------------------------------------------------------------
2020 -- PUBLIC PROCEDURE
2021 -- call_partner_service
2022 --
2023 -- DESCRIPTION
2024 -- This procedure is used to prepare the call to the appropriate tax
2025 -- partner service
2026 --
2027 -- CALLED BY
2028 -- ZX_SRVC_TYPS_PKG.calculate_tax
2029 -- ZX_SRVC_TYPS_PKG.import
2030 -- ZX_SRVC_TYPS_PKG.override_tax_lines
2031 -- ZX_SRVC_TYPS_PKG.document_level_changes
2032 -- ZX_SRVC_TYPS_PKG.reverse_document
2033 -- ZX_SRVC_TYPS_PKG.synchronize_tax
2034 -- ZX_SRVC_TYPS_PKG.discard_tax_only_lines
2035 -- ZX_SRVC_TYPS_PKG.partner_inclusive_tax_override
2036 -----------------------------------------------------------------------
2037
2038
2039 PROCEDURE call_partner_service(
2040 p_tax_regime_code IN VARCHAR2,
2041 p_tax_provider_id IN NUMBER,
2042 p_service_type_code IN VARCHAR2,
2043 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
2044 x_return_status OUT NOCOPY VARCHAR2
2045 ) IS
2046
2047 l_api_name CONSTANT VARCHAR2(30) := 'CALL_PARTNER_SERVICE';
2048 l_context_info_rec ZX_API_PUB.context_info_rec_type;
2049 l_service_type_id NUMBER;
2050 l_context_ccid NUMBER;
2051 l_return_status VARCHAR2(1);
2052 l_counter NUMBER;
2053 l_to_amt NUMBER;
2054 l_prev_trx_id NUMBER; -- Added for Bug#12922605
2055 l_error_buffer VARCHAR2(1000);
2056
2057 -- Added for Bug#16460613
2058 l_tax_currencies_tbl ZX_TAX_PARTNER_PKG.tax_currencies_tbl_type;
2059 l_curr_tbl_idx NUMBER;
2060 l_exist_in_tbl BOOLEAN;
2061 l_exchange_rate NUMBER;
2062
2063 CURSOR currency_csr IS
2064 SELECT DISTINCT tax, -- Bug#5395227
2065 tax_currency_code,
2066 exchange_rate_type
2067 FROM ZX_SCO_TAXES_B_V -- Bug#5395227
2068 WHERE tax_regime_code = p_tax_regime_code;
2069
2070 --Currencies can be passed at either the header level/line level
2071 CURSOR document_currency_csr IS
2072 SELECT DISTINCT -- Bug#16460613
2073 NVL(trx_line_currency_code,trx_currency_code) trx_currency_code,
2074 trx_date
2075 FROM ZX_LINES_DET_FACTORS
2076 WHERE application_id = p_event_class_rec.application_id
2077 AND entity_code = p_event_class_rec.entity_code
2078 AND event_class_code = p_event_class_rec.event_class_code
2079 AND trx_id = p_event_class_rec.trx_id;
2080
2081 BEGIN
2082 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2083 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2084 END IF;
2085
2086 --Set the return status to Success
2087 x_return_status := FND_API.G_RET_STS_SUCCESS;
2088
2089 BEGIN
2090 SELECT reg.service_type_id,
2091 reg.context_ccid
2092 INTO l_service_type_id,
2093 l_context_ccid
2094 FROM ZX_API_REGISTRATIONS reg,
2095 ZX_SERVICE_TYPES srvc,
2096 ZX_API_CODE_COMBINATIONS api
2097 WHERE api.code_combination_id = reg.context_ccid
2098 AND api.segment_attribute1 = p_tax_regime_code
2099 AND (api.segment_attribute2 is null
2100 OR api.segment_attribute2 = p_event_class_rec.prod_family_grp_code )
2101 AND reg.api_owner_id = p_tax_provider_id
2102 AND srvc.service_type_id = reg.service_type_id
2103 AND srvc.service_type_code = p_service_type_code;
2104 EXCEPTION WHEN OTHERS THEN
2105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; --tax returned by partner is not amongst candidate taxes
2106 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2107 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2108 ' The Partner Service '|| p_service_type_code || ' is not registered for ' ||
2109 p_event_class_rec.prod_family_grp_code || ',' || p_tax_regime_code ||
2110 ', and provider ' || p_tax_provider_id);
2111 END IF;
2112 RETURN; -- Bug 5200373
2113 END;
2114
2115 IF p_service_type_code = 'CALCULATE_TAX' THEN
2116 --Populate the tax currencies structure
2117 l_counter := 0;
2118 FOR currencies IN currency_csr LOOP
2119 FOR doc_curr IN document_currency_csr LOOP
2120 -- If tax currency and trx currency is same, exchange rate will be 1
2121 IF currencies.tax_currency_code = doc_curr.trx_currency_code THEN
2122 l_exchange_rate := 1;
2123
2124 -- If tax currency and trx currency is not same, get the exchange rate from API
2125 ELSE
2126 l_exchange_rate := TO_NUMBER(NULL);
2127 ZX_TDS_TAX_ROUNDING_PKG.convert_to_currency (p_from_currency => doc_curr.trx_currency_code,
2128 p_to_currency => currencies.tax_currency_code,
2129 p_conversion_date => ZX_SECURITY.G_EFFECTIVE_DATE,
2130 p_tax_conversion_type => currencies.exchange_rate_type,
2131 p_trx_conversion_type => p_event_class_rec.currency_conversion_type,
2132 p_to_curr_conv_rate => l_exchange_rate,
2133 p_from_amt => 1,
2134 p_to_amt => l_to_amt,
2135 p_return_status => l_return_status,
2136 p_error_buffer => l_error_buffer,
2137 p_trx_conversion_date => doc_curr.trx_date); --Bug7183884
2138
2139 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2140 x_return_status := l_return_status;
2141 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2142 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2143 ' RETURN_STATUS = ' || x_return_status);
2144 END IF;
2145 RETURN;
2146 END IF;
2147 END IF;
2148
2149 -- search for similar record in table
2150 l_curr_tbl_idx := l_tax_currencies_tbl.FIRST;
2151 l_exist_in_tbl := FALSE;
2152 WHILE l_curr_tbl_idx IS NOT NULL LOOP
2153 IF (l_tax_currencies_tbl(l_curr_tbl_idx).tax = currencies.tax AND
2154 l_tax_currencies_tbl(l_curr_tbl_idx).tax_currency_code = currencies.tax_currency_code AND
2155 l_tax_currencies_tbl(l_curr_tbl_idx).trx_line_currency_code = doc_curr.trx_currency_code AND
2156 l_tax_currencies_tbl(l_curr_tbl_idx).exchange_rate = l_exchange_rate)
2157 THEN
2158 l_exist_in_tbl := TRUE;
2159 EXIT;
2160 END IF;
2161 l_curr_tbl_idx := l_tax_currencies_tbl.NEXT(l_curr_tbl_idx);
2162 END LOOP;
2163
2164 -- insert the record in plsql table only if it does not exist
2165 IF NOT l_exist_in_tbl THEN
2166 l_counter := l_counter+1;
2167 l_tax_currencies_tbl(l_counter).tax := currencies.tax;
2168 l_tax_currencies_tbl(l_counter).tax_currency_code := currencies.tax_currency_code;
2169 l_tax_currencies_tbl(l_counter).trx_line_currency_code := doc_curr.trx_currency_code;
2170 l_tax_currencies_tbl(l_counter).exchange_rate := l_exchange_rate;
2171 END IF;
2172
2173 END LOOP; --doc_curr
2174 END LOOP;--currencies
2175
2176 -- Assign tax currencies table data to Global Tax Currencies table
2177 ZX_PTNR_SRVC_INTGRTN_PKG.G_TAX_CURRENCIES_TBL := l_tax_currencies_tbl;
2178
2179 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2180 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2181 'No. of records in ZX_PTNR_SRVC_INTGRTN_PKG.G_TAX_CURRENCIES_TBL : '||
2182 TO_CHAR(ZX_PTNR_SRVC_INTGRTN_PKG.G_TAX_CURRENCIES_TBL.COUNT));
2183 END IF;
2184
2185 ELSIF p_service_type_code = 'DOCUMENT_LEVEL_CHANGES' THEN
2186 --Populate trx_rec
2187 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2188 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2189 ' p_event_class_rec.event_class_mapping_id ' || p_event_class_rec.event_class_mapping_id ||
2190 ' ZX_API_PUB.G_PUB_SRVC ' || ZX_API_PUB.G_PUB_SRVC ||
2191 ' p_event_class_rec.event_type_code ' || p_event_class_rec.event_type_code ||
2192 ' p_event_class_rec.tax_event_type_code ' || p_event_class_rec.tax_event_type_code);
2193 END IF;
2194
2195 ZX_PTNR_SRVC_INTGRTN_PKG.G_TRX_REC.document_type_id := p_event_class_rec.event_class_mapping_id;
2196 ZX_PTNR_SRVC_INTGRTN_PKG.G_TRX_REC.transaction_id := p_event_class_rec.trx_id;
2197 IF ZX_API_PUB.G_PUB_SRVC = 'CALCULATE_TAX' THEN --called for partner processing flag = 'N'
2198 ZX_PTNR_SRVC_INTGRTN_PKG.G_TRX_REC.document_level_action := 'DELETE';
2199 ELSE
2200 ZX_PTNR_SRVC_INTGRTN_PKG.G_TRX_REC.document_level_action := p_event_class_rec.event_type_code;
2201 END IF;
2202 END IF;
2203
2204 -- Call the partner service
2205 ZX_PTNR_SRVC_INTGRTN_PKG.invoke_third_party_interface (p_tax_provider_id,
2206 l_service_type_id,
2207 l_context_ccid ,
2208 'PLS',
2209 l_return_status
2210 );
2211 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2212 x_return_status := l_return_status;
2213 l_prev_trx_id := -99;
2214
2215 FOR er_index IN NVL(ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_STRING.FIRST,1) ..
2216 NVL(ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_STRING.LAST,0)
2217 LOOP
2218
2219 -- Added code for Bug#12922605
2220 l_error_buffer := ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_STRING(er_index);
2221
2222 -- Added IF condition to ensure that only 1 error message
2223 -- per trx_id should be added to error table
2224 IF l_prev_trx_id <> p_event_class_rec.trx_id THEN
2225 -- Add message to FND
2226 FND_MESSAGE.SET_NAME('ZX', 'GENERIC_MESSAGE');
2227 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_error_buffer);
2228 -- Set the context info
2229 l_context_info_rec.APPLICATION_ID := p_event_class_rec.application_id;
2230 l_context_info_rec.ENTITY_CODE := p_event_class_rec.entity_code;
2231 l_context_info_rec.EVENT_CLASS_CODE := p_event_class_rec.event_class_code;
2232 l_context_info_rec.TRX_ID := p_event_class_rec.trx_id;
2233 -- Add error message to Error table
2234 ZX_API_PUB.add_msg( p_context_info_rec => l_context_info_rec );
2235 -- Assign the value of Trx_id to l_prev_trx_id
2236 l_prev_trx_id := p_event_class_rec.trx_id;
2237 END IF;
2238
2239 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2240 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2241 'Error Type: '||ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_TYPE(er_index));
2242 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2243 'Error Message: '||l_error_buffer);
2244 END IF;
2245
2246 END LOOP;
2247
2248 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2249 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2250 ' RETURN_STATUS = ' || x_return_status);
2251 END IF;
2252 RETURN;
2253 END IF;
2254
2255 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2256 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
2257 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
2258 END IF;
2259
2260 EXCEPTION
2261 WHEN OTHERS THEN
2262 x_return_status := FND_API.G_RET_STS_ERROR;
2263 FOR er_index IN NVL(ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_STRING.FIRST,1) ..
2264 NVL(ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_STRING.LAST,0)
2265 LOOP
2266 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2267 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2268 'Error Type: '||ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_TYPE(er_index));
2269 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2270 'Error Message: '||ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_STRING(er_index));
2271 END IF;
2272 FND_MESSAGE.SET_NAME('ZX', 'GENERIC_MESSAGE');
2273 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', ZX_PTNR_SRVC_INTGRTN_PKG.G_MESSAGES_TBL.ERROR_MESSAGE_STRING(er_index));
2274 l_context_info_rec.APPLICATION_ID := p_event_class_rec.application_id;
2275 l_context_info_rec.ENTITY_CODE := p_event_class_rec.entity_code;
2276 l_context_info_rec.EVENT_CLASS_CODE := p_event_class_rec.event_class_code;
2277 l_context_info_rec.TRX_ID := p_event_class_rec.trx_id;
2278 ZX_API_PUB.add_msg( p_context_info_rec =>l_context_info_rec );
2279 -- ZX_API_PUB.dump_msg;
2280 END LOOP;
2281 RETURN;
2282 END call_partner_service;
2283
2284 -----------------------------------------------------------------------
2285 -- PUBLIC PROCEDURE
2286 -- ptnr_post_processing_calc_tax
2287 --
2288 -- DESCRIPTION
2289 -- This procedure is used to massage the tax results returned by the partner's
2290 -- calculate tax service. The processing of tax results involve validation
2291 -- of the data returned, mapping, further processing, and/or recording.
2292 --
2293 -- CALLED BY
2294 -- ZX_SRVC_TYPS_PKG.calculate_tax
2295 -- ZX_SRVC_TYPS_PKG.import
2296 -- ZX_SRVC_TYPS_PKG.partner_inclusive_tax_override
2297 -----------------------------------------------------------------------
2298
2299 PROCEDURE ptnr_post_processing_calc_tax(
2300 p_tax_regime_code IN VARCHAR2,
2301 p_tax_provider_id IN NUMBER,
2302 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
2303 x_return_status OUT NOCOPY VARCHAR2
2304 ) IS
2305 l_api_name CONSTANT VARCHAR2(30) := 'PTNR_POST_PROCESSING_CALC_TAX';
2306 l_context_info_rec ZX_API_PUB.context_info_rec_type;
2307 l_app_regimes NUMBER;
2308 l_tax_found NUMBER;
2309 l_tax_index VARCHAR2(100);
2310 l_prepay_tax_rate NUMBER;
2311 l_prepay_tax_amt NUMBER;
2312 l_prepay_line_amt NUMBER;
2313 l_line_level_action VARCHAR2(30);
2314 l_tax VARCHAR2(30);
2315 l_prorated_amt NUMBER;
2316 l_sync_with_prvdr_flag VARCHAR2(1);
2317 l_delete_flag VARCHAR2(1);
2318 l_self_assessment_flag VARCHAR2(1);
2319 l_return_status VARCHAR2(1);
2320 l_tax_precision NUMBER;
2321 l_threshold_indicator_flag VARCHAR2(1);
2322 l_partner_migrated_flag VARCHAR2(1);
2323 l_allow_tax_calculation_flag ZX_TRX_PRE_PROC_OPTIONS_GT.allow_tax_calculation_flag%type;
2324
2325 --Table to derive the apportionment number in case the transaction line has same taxes
2326 TYPE tax_tbl_type is table of NUMBER index by VARCHAR2(240);
2327
2328 tax_tbl tax_tbl_type;
2329 /* Bug 5162537 */
2330 l_tax_rec ZX_TDS_UTILITIES_PKG.zx_tax_info_cache_rec;
2331 l_error_buffer VARCHAR2(1000);
2332
2333 l_tax_class ZX_RATES_B.tax_class%TYPE;
2334 l_ptnr_calc_flag VARCHAR2(2);
2335 --
2336 -- Bug#5417753
2337 --
2338 CURSOR get_def_tax_rate_csr
2339 (c_tax_status_code ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
2340 c_tax ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
2341 c_tax_regime_code ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
2342 c_tax_class ZX_RATES_B.TAX_CLASS%TYPE,
2343 c_tax_determine_date ZX_LINES.TAX_DETERMINE_DATE%TYPE)
2344 IS
2345 SELECT rate.tax_rate_code,
2346 rate.rate_type_code,
2347 rate.tax_rate_id
2348 FROM ZX_SCO_RATES_B_V rate
2349 WHERE rate.tax_regime_code = c_tax_regime_code
2350 -- AND rate.default_rate_flag = 'Y'
2351 AND rate.active_flag = 'Y'
2352 AND rate.tax = c_tax
2353 AND rate.tax_status_code = c_tax_status_code
2354 AND rate.tax_jurisdiction_code is null
2355 AND (rate.tax_class = c_tax_class or rate.tax_class IS NULL)
2356 -- AND rate.default_flg_effective_from <= c_tax_determine_date
2357 -- AND (rate.default_flg_effective_to >= c_tax_determine_date OR
2358 -- rate.default_flg_effective_to IS NULL)
2359 AND rate.effective_from <= c_tax_determine_date
2360 AND (rate.effective_to >= c_tax_determine_date OR
2361 rate.effective_to IS NULL)
2362 ORDER BY rate.tax_class NULLS LAST, rate.subscription_level_code;
2363
2364 BEGIN
2365 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2366 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2367 END IF;
2368
2369 --Set the return status to Success
2370 x_return_status := FND_API.G_RET_STS_SUCCESS;
2371
2372 -- Bug#5417753- determine tax_class value
2373 IF p_event_class_rec.prod_family_grp_code = 'O2C' THEN
2374 l_tax_class := 'OUTPUT';
2375
2376 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2377 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Checking whether any item lines processed for this Transaction');
2378 END IF;
2379
2380 BEGIN
2381 SELECT 'Y' INTO l_ptnr_calc_flag
2382 from ZX_O2C_CALC_TXN_INPUT_V ptnr,
2383 zx_evnt_cls_mappings evnt
2384 where evnt.application_id = p_event_class_rec.application_id
2385 and evnt.entity_code = p_event_class_rec.entity_code
2386 and evnt.event_class_code = p_event_class_rec.event_class_code
2387 and ptnr.transaction_id = p_event_class_rec.trx_id
2388 and ptnr.document_type_id = evnt.event_class_mapping_id
2389 and rownum = 1;
2390 EXCEPTION
2391 WHEN OTHERS THEN
2392 l_ptnr_calc_flag := 'N';
2393 END;
2394 ELSIF p_event_class_rec.prod_family_grp_code = 'P2P' THEN
2395 l_tax_class := 'INPUT';
2396 END IF;
2397
2398 IF NVL(l_ptnr_calc_flag,'Y') = 'Y' THEN
2399 --Call routine to bulk insert all Applicable regimes for line in db table ZX_TRX_LINE_APP_REGIMES
2400 --Insert into zx_trx_line_app_regimes(Point 5 in DLD)
2401 trx_line_app_regimes_tbl_hdl(p_event_class_rec => p_event_class_rec,
2402 p_event => 'INSERT',
2403 p_provider_id => null,
2404 p_tax_regime_code => null,
2405 p_trx_line_id => null,
2406 p_trx_level_type => null,
2407 x_return_status => l_return_status
2408 );
2409
2410 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2411 x_return_status := l_return_status;
2412 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2413 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2414 ' RETURN_STATUS = ' || x_return_status);
2415 END IF;
2416 RETURN;
2417 END IF;
2418 END IF;
2419
2420 /* Bug 5131206: For tax only documents, partner tax calculation service is called
2421 for synchronization of the document information. Partner is
2422 expected NOT to calculate the tax.
2423 Hence, ptnr_post_processing_calc_tax should be skipped in this case.
2424 The tax lines are created by eBTax and are later synchronized with
2425 the partner.
2426 */
2427 BEGIN
2428 SELECT allow_tax_calculation_flag
2429 INTO l_allow_tax_calculation_flag
2430 FROM ZX_TRX_PRE_PROC_OPTIONS_GT
2431 WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
2432 AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
2433 AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
2434 AND TRX_ID = p_event_class_rec.TRX_ID;
2435 END;
2436
2437 IF l_allow_tax_calculation_flag = 'N' THEN
2438 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2439 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.END',
2440 G_PKG_NAME ||': '||l_api_name||'(-)'||' ptnr_post_processing_calc_tax is skipped for tax only documents');
2441 END IF;
2442 RETURN;
2443 END IF;
2444
2445 --Point 3A in DLD
2446 IF ZX_API_PUB.G_PUB_SRVC = 'IMPORT_DOCUMENT_WITH_TAX' THEN
2447 IF p_event_class_rec.record_for_partners_flag = 'Y' THEN
2448 IF p_event_class_rec.process_for_applicability_flag = 'N' THEN
2449 l_sync_with_prvdr_flag := 'Y';
2450 l_delete_flag := 'Y';
2451 ELSIF p_event_class_rec.perf_addnl_appl_for_imprt_flag ='Y' THEN
2452 l_self_assessment_flag := 'Y';
2453 l_delete_flag := 'Y';
2454 END IF;
2455 END IF;
2456 END IF;
2457
2458 FOR ptnr_tax_line_index IN nvl(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.document_type_id.FIRST, 1) .. nvl(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.document_type_id.LAST,0)
2459 LOOP
2460 --Check to see if partner returned tax is a valid tax from the list of candidate taxes for the regime (Point 3b in DLD)
2461 /* Bug#5395227- use cache structure
2462 BEGIN
2463 SELECT tax_precision,
2464 minimum_accountable_unit,
2465 rounding_rule_code,
2466 tax_id,
2467 tax_type_code,
2468 exchange_rate_type
2469 INTO l_tax_precision,
2470 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.minimum_accountable_unit(ptnr_tax_line_index),
2471 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rounding_rule_code(ptnr_tax_line_index),
2472 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_id(ptnr_tax_line_index),
2473 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_type_code(ptnr_tax_line_index),
2474 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_type(ptnr_tax_line_index)
2475 -- FROM ZX_SCO_TAXES -- Bug#5395227
2476 FROM ZX_SCO_TAXES_B_V
2477 WHERE tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(ptnr_tax_line_index)
2478 AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
2479 AND rownum = 1
2480 ORDER BY subscription_level_code; -- Bug#5395227
2481
2482 EXCEPTION
2483 WHEN NO_DATA_FOUND THEN
2484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ; --tax returned by partner is not amongst candidate taxes
2485 IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
2486 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'Tax returned by the tax partner is invalid');
2487 END IF;
2488 RETURN;
2489 END;
2490
2491 */
2492
2493 -- Bug#5395227- replace above code
2494
2495 l_tax_rec := NULL;
2496 l_return_status := FND_API.G_RET_STS_SUCCESS;
2497
2498 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
2499 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(ptnr_tax_line_index),
2500 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index),
2501 ZX_SECURITY.G_EFFECTIVE_DATE,
2502 l_tax_rec,
2503 l_return_status,
2504 l_error_buffer);
2505
2506 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2507 l_tax_precision := l_tax_rec.tax_precision;
2508 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.minimum_accountable_unit(ptnr_tax_line_index) := l_tax_rec.minimum_accountable_unit;
2509 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rounding_rule_code(ptnr_tax_line_index) := l_tax_rec.rounding_rule_code;
2510 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_id(ptnr_tax_line_index) := l_tax_rec.tax_id;
2511 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_type_code(ptnr_tax_line_index) := l_tax_rec.tax_type_code;
2512 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_type(ptnr_tax_line_index) := l_tax_rec.exchange_rate_type;
2513
2514 ELSE
2515 IF (g_level_statement >= g_current_runtime_level ) THEN
2516 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2517 'Incorrect return_status after calling ZX_TDS_UTILITIES_PKG.get_tax_cache_info for tax '
2518 || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
2519 || 'RETURN_STATUS = ' || l_return_status);
2520 END IF;
2521 RETURN;
2522 END IF;
2523
2524 --PRECISION CHECK
2525 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2526 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2527 ' Checking the preceision returned on tax line' || to_char(l_tax_precision));
2528 END IF;
2529
2530 FOR i IN ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_currencies_tbl.FIRST.. nvl(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_currencies_tbl.LAST,0)
2531 LOOP
2532 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_currencies_tbl(i).tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index) THEN
2533 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_currencies_tbl(i).tax_currency_precision > l_tax_precision THEN -- Bug 5288518
2534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; --tax returned by partner is not amongst candidate taxes
2535 IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
2536 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,
2537 ' Precision returned by the tax partner is invalid ');
2538 END IF;
2539 RETURN; -- Bug 4769082
2540 END IF;
2541 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_code(ptnr_tax_line_index) := ZX_PTNR_SRVC_INTGRTN_PKG.G_TAX_CURRENCIES_TBL(i).tax_currency_code;
2542 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_precision(ptnr_tax_line_index) := ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_currencies_tbl(i).tax_currency_precision; -- Bug 5288518
2543 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_rate(ptnr_tax_line_index) := ZX_PTNR_SRVC_INTGRTN_PKG.G_TAX_CURRENCIES_TBL(i).exchange_rate;
2544 EXIT; -- Bug 4769082
2545 END IF;
2546 END LOOP;
2547
2548
2549 --Check if multiple regimes applicable for transaction line and if inclusive flag set for tax line (Point 3d)
2550 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2551 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2552 ' Inclusive Tax Line Flag: ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.inclusive_tax_line_flag(ptnr_tax_line_index));
2553 END IF;
2554 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.inclusive_tax_line_flag(ptnr_tax_line_index) = 'Y' THEN
2555 BEGIN
2556 SELECT count(*)
2557 INTO l_app_regimes
2558 FROM ZX_TRX_LINE_APP_REGIMES
2559 WHERE application_id = p_event_class_rec.application_id
2560 AND entity_code = p_event_class_rec.entity_code
2561 AND event_class_code = p_event_class_rec.event_class_code
2562 AND trx_id = p_event_class_rec.trx_id
2563 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(ptnr_tax_line_index)
2564 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(ptnr_tax_line_index);
2565 END;
2566
2567 IF l_app_regimes > 1 THEN
2568 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; --tax returned by partner cannot be inclusive
2569 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2570 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2571 ' Returned tax line for multiple regimes by the tax partner is invalid ' || x_return_status);
2572 END IF;
2573 RETURN;
2574 END IF;
2575 END IF;
2576
2577 /*Derivation of tax apportionment number (Point 3l in DLD) */
2578 --Store the taxes for line in a temp structure indexed by line id and tax to make comparisions easier
2579
2580 l_tax_index := ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index) || '$' ||
2581 to_char(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(ptnr_tax_line_index));
2582 IF (tax_tbl.EXISTS(l_tax_index)) THEN
2583 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_line_number(ptnr_tax_line_index) := tax_tbl(l_tax_index)+1;
2584 ELSE
2585 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_line_number(ptnr_tax_line_index):=1;
2586 END IF;
2587 tax_tbl(l_tax_index) := ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_line_number(ptnr_tax_line_index);
2588
2589 --Points 3j-3k in DLD
2590 -- Determine default tax status
2591
2592 BEGIN
2593 SELECT status.tax_status_code,
2594 status.tax_status_id
2595 INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(ptnr_tax_line_index),
2596 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_id(ptnr_tax_line_index)
2597 -- FROM ZX_SCO_STATUS status -- Bug#5395227
2598 FROM ZX_SCO_STATUS_B_V status
2599 WHERE status.tax_regime_code = p_tax_regime_code
2600 AND status.default_status_flag = 'Y'
2601 AND status.tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
2602 AND (ZX_SECURITY.G_EFFECTIVE_DATE between status.default_flg_effective_from AND nvl(status.default_flg_effective_to,ZX_SECURITY.G_EFFECTIVE_DATE))
2603 AND rownum = 1
2604 ORDER BY subscription_level_code; -- Bug#5395227
2605 EXCEPTION WHEN OTHERS THEN
2606 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2607 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' In exception of zx_sco_status');
2608 END IF;
2609 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(ptnr_tax_line_index) := NULL;
2610 END;
2611
2612 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2613 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2614 ' Determine default tax rate for status ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(ptnr_tax_line_index));
2615 END IF;
2616
2617 /* Bug#5417753- replace by the cursor below
2618 BEGIN
2619 SELECT rate.tax_rate_code,
2620 rate.rate_type_code,
2621 rate.tax_rate_id
2622 INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(ptnr_tax_line_index),
2623 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rate_type_code(ptnr_tax_line_index),
2624 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_id(ptnr_tax_line_index)
2625 -- FROM ZX_SCO_RATES rate -- Bug#5395227
2626 FROM ZX_SCO_RATES_B_V rate
2627 WHERE rate.tax_regime_code = p_tax_regime_code
2628 AND rate.default_rate_flag = 'Y'
2629 AND rate.active_flag = 'Y'
2630 AND rate.tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
2631 AND rate.tax_status_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(ptnr_tax_line_index)
2632 AND rate.tax_jurisdiction_code is null
2633 AND (rate.tax_class = l_tax_class or rate.tax_class IS NULL)
2634 AND (ZX_SECURITY.G_EFFECTIVE_DATE between rate.default_flg_effective_from AND nvl(rate.default_flg_effective_to,ZX_SECURITY.G_EFFECTIVE_DATE))
2635 AND rownum = 1
2636 ORDER BY rate.tax_class NULLS LAST, rate.subscription_level_code; -- Bug#5395227
2637 EXCEPTION WHEN OTHERS THEN
2638 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2639 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' In exception of zx_sco_rates');
2640 END IF;
2641 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(ptnr_tax_line_index) := NULL;
2642 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rate_type_code(ptnr_tax_line_index) := NULL;
2643 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_id(ptnr_tax_line_index) := NULL;
2644 END;
2645 */
2646
2647 -- Bug#5417753
2648 OPEN get_def_tax_rate_csr(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(ptnr_tax_line_index),
2649 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index),
2650 p_tax_regime_code,
2651 l_tax_class,
2652 ZX_SECURITY.G_EFFECTIVE_DATE);
2653
2654 FETCH get_def_tax_rate_csr INTO
2655 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(ptnr_tax_line_index),
2656 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rate_type_code(ptnr_tax_line_index),
2657 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_id(ptnr_tax_line_index);
2658 IF get_def_tax_rate_csr%notfound THEN
2659 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(ptnr_tax_line_index) := NULL;
2660 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rate_type_code(ptnr_tax_line_index) := NULL;
2661 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_id(ptnr_tax_line_index) := NULL;
2662 END IF;
2663 CLOSE get_def_tax_rate_csr;
2664
2665 set_detail_tax_line_values(p_event_class_rec,
2666 ptnr_tax_line_index,
2667 p_tax_provider_id,
2668 l_return_status);
2669
2670 --Max Tax condition handling (point 3C in DLD)
2671 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2672 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2673 ' Threshold Indicator Flag: ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.threshold_indicator_flag(ptnr_tax_line_index));
2674 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2675 ' Exempt Certificate Number: ' || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_certificate_number(ptnr_tax_line_index));
2676 END IF;
2677
2678 -- Added the following code to check whether Global Attributes are initialized.
2679 -- Added the below 11 lines as part of vertex O series certification.
2680 BEGIN
2681 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute_category(ptnr_tax_line_index)
2682 := ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute_category(ptnr_tax_line_index);
2683 EXCEPTION
2684 WHEN OTHERS THEN
2685 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute2(ptnr_tax_line_index) := NULL;
2686 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute4(ptnr_tax_line_index) := NULL;
2687 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute6(ptnr_tax_line_index) := NULL;
2688 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute_category(ptnr_tax_line_index) := NULL;
2689 END;
2690
2691 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.threshold_indicator_flag(ptnr_tax_line_index) = 'Y'
2692 AND l_threshold_indicator_flag is null THEN
2693 l_threshold_indicator_flag := 'Y';
2694 END IF;
2695
2696 --Resetting the partner migrated flag to indicate that, for subsequent updates on the same
2697 --document, we need not pass entire document information to the partner
2698 IF p_event_class_rec.tax_event_type_code = 'UPDATE'
2699 AND l_partner_migrated_flag is null
2700 AND record_type_code_tbl(ptnr_tax_line_index) = 'MIGRATED'
2701 AND partner_migrated_flag_tbl(ptnr_tax_line_index) = 'Y' THEN
2702 l_partner_migrated_flag := 'N';
2703 END IF;
2704
2705 -- Bug 5162537:
2706 -- The tax lines created for partners did not have correct tax line numbering.
2707 -- The TDS procedure ZX_TDS_TAX_LINES_DETM_PKG: populate_tax_line_numbers
2708 -- expects the cache ZX_TDS_UTILITIES_PKG.zx_tax_info_cache_rec to be populated
2709 -- for the tax.
2710 -- Since, the taxes are determined by the partner, it is necessary that partner
2711 -- integration infrastructure populates this cache.
2712 --
2713 /* bug#5395227 don't need to call again here
2714 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
2715 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(ptnr_tax_line_index),
2716 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index),
2717 ZX_SECURITY.G_EFFECTIVE_DATE,
2718 l_tax_rec,
2719 l_return_status,
2720 l_error_buffer);
2721
2722 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2723 IF (g_level_statement >= g_current_runtime_level ) THEN
2724 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2725 'Incorrect return_status after calling ZX_TDS_UTILITIES_PKG.get_tax_cache_info for tax '
2726 || ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
2727 || 'RETURN_STATUS = ' || l_return_status);
2728 END IF;
2729 RETURN;
2730 END IF;
2731 */
2732
2733 --Populate other attributes of output structure(Points 3h and 3m)
2734 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.sync_with_prvdr_flag(ptnr_tax_line_index):= l_sync_with_prvdr_flag;
2735 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.delete_flag(ptnr_tax_line_index) := l_delete_flag ;
2736 IF l_self_assessment_flag is not NULL THEN
2737 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.use_tax_flag(ptnr_tax_line_index) := l_self_assessment_flag ;
2738 END IF;
2739
2740 /*Pre-Payment Processing handling*/
2741 --Point 3e-3f in DLD
2742 BEGIN
2743 -- Need to check if pre-payment processing applicable for the line and this need to be done only once for every transaction line.
2744 -- although tax lines for that transaction lines may be many
2745 IF ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW = 'P2P' THEN
2746 IF ptnr_tax_line_index > 1 AND
2747 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(ptnr_tax_line_index-1) =
2748 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(ptnr_tax_line_index) THEN
2749 SELECT tax.tax_rate,
2750 tax.tax_amt,
2751 tax.line_amt
2752 INTO l_prepay_tax_rate,
2753 l_prepay_tax_amt,
2754 l_prepay_line_amt
2755 FROM ZX_LINES tax
2756 WHERE tax.application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_application_id(ptnr_tax_line_index)
2757 AND tax.entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_entity_code(ptnr_tax_line_index)
2758 AND tax.event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_event_class_code(ptnr_tax_line_index)
2759 AND tax.trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_id(ptnr_tax_line_index)
2760 AND tax.trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_line_id(ptnr_tax_line_index)
2761 AND tax.trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_level_type(ptnr_tax_line_index)
2762 AND tax.tax_regime_code = p_tax_regime_code
2763 AND tax.tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
2764 AND tax.tax_apportionment_line_number = 1;
2765
2766 -- If difference in partner returned tax rate and tax rate on the pre-payment document,
2767 -- call TDM process to prorate the tax amount
2768 IF l_prepay_tax_rate <> ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_percentage(ptnr_tax_line_index) THEN
2769 --Call TRD routine to prorate the tax amount
2770 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_amount(ptnr_tax_line_index) :=
2771 ZX_TRD_SERVICES_PUB_PKG.get_prod_total_tax_amt(l_prepay_tax_amt,
2772 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.line_amt(ptnr_tax_line_index),
2773 l_prepay_line_amt
2774 );
2775 END IF;
2776 END IF; --ptnr_tax_line_index >1
2777 END IF; -- P2P flow
2778 EXCEPTION
2779 WHEN NO_DATA_FOUND THEN
2780 null;
2781 END;
2782
2783 -- Mark the sync with provider flag(Point 3n in DLD)
2784 -- Retrieve line level action
2785
2786 IF p_event_class_rec.record_for_partners_flag ='Y' AND
2787 p_event_class_rec.record_flag = 'Y' AND
2788 p_event_class_rec.quote_flag = 'N' THEN
2789
2790 l_line_level_action := line_level_action_tbl(ptnr_tax_line_index);
2791
2792 IF l_line_level_action ='CREATE_TAX_ONLY' THEN
2793 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.sync_with_prvdr_flag(ptnr_tax_line_index) := 'Y';
2794 END IF;
2795
2796 END IF;
2797
2798 -- Get the own_hq_tax_reg_number (Point 3i in DLD) - TBD
2799 /*
2800 --Call TCM routine to get first party tax registration number
2801 ZX_TCM_CONTROL_PKG.get_tax_registration (p_parent_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.hq_estb_ptp_id(1),
2802 p_site_ptp_id => null,
2803 p_account_type_code => null,
2804 p_tax_determine_date => ZX_SECURITY.G_EFFECTIVE_DATE,
2805 p_tax => ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index),
2806 p_tax_regime_code => p_tax_regime_code,
2807 p_jurisdiction_code => null,
2808 p_account_id => null,
2809 p_account_site_id => null,
2810 p_site_use_id => null,
2811 p_zx_registration_rec => l_registration_rec,
2812 p_ret_record_level => l_ret_record_level,
2813 p_return_status => l_return_status
2814 );
2815
2816 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.party_tax_reg_number(ptnr_tax_line_index) := l_registration_rec.registration_number;
2817
2818 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2819 null; --ok if not first party tax registration registration number is not found
2820 END IF;
2821 */
2822 END LOOP; --looping over the tax lines
2823
2824 --Populate zx_detail_tax_lines_gt with the taxes retured from partner*/
2825
2826 BEGIN
2827 FORALL i IN ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.application_id.FIRST.. nvl(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.application_id.LAST,0)
2828 INSERT INTO ZX_DETAIL_TAX_LINES_GT (tax_line_id,
2829 internal_organization_id,
2830 application_id,
2831 entity_code,
2832 event_class_code,
2833 event_type_code,
2834 content_owner_id,
2835 trx_id,
2836 trx_line_id,
2837 trx_level_type,
2838 tax_regime_code,
2839 tax_line_number,
2840 tax,
2841 tax_status_code,
2842 tax_rate_code,
2843 tax_rate_type,
2844 tax_apportionment_line_number,
2845 place_of_supply_type_code,
2846 tax_jurisdiction_code,
2847 tax_currency_code,
2848 --precision,
2849 --minimum_accountable_unit,
2850 rounding_rule_code,
2851 tax_amt,
2852 unrounded_tax_amt,
2853 unrounded_taxable_amt,
2854 tax_amt_tax_curr,
2855 tax_rate,
2856 taxable_amt,
2857 --exempt_amt,
2858 exempt_certificate_number,
2859 exempt_rate_modifier,
2860 exempt_reason,
2861 exempt_reason_code,
2862 tax_exemption_id,
2863 applied_from_application_id, -- Bug 5468010
2864 applied_from_entity_code, -- Bug 5468010
2865 applied_from_event_class_code, -- Bug 5468010
2866 applied_from_trx_id, -- Bug 5468010
2867 applied_from_line_id, -- Bug 5468010
2868 applied_from_trx_level_type, -- Bug 5468010
2869 applied_from_trx_number, -- Bug 5468010
2870 adjusted_doc_application_id, -- Bug 5468010
2871 adjusted_doc_entity_code, -- Bug 5468010
2872 adjusted_doc_event_class_code, -- Bug 5468010
2873 adjusted_doc_trx_id, -- Bug 5468010
2874 adjusted_doc_line_id, -- Bug 5468010
2875 adjusted_doc_trx_level_type, -- Bug 5468010
2876 adjusted_doc_number, -- Bug 5468010
2877 adjusted_doc_date, -- Bug 5468010
2878 adjusted_doc_tax_line_id, -- Bug 6130978
2879 sync_with_prvdr_flag,
2880 tax_only_line_flag,
2881 tax_amt_included_flag,
2882 self_assessed_flag,
2883 overridden_flag,
2884 last_manual_entry,
2885 tax_provider_id,
2886 manually_entered_flag,
2887 registration_party_type, -- Bug 5288518
2888 tax_registration_number, -- Bug 5288518
2889 cancel_flag,
2890 delete_flag,
2891 trx_line_number,
2892 trx_number,
2893 doc_event_status,
2894 tax_event_class_code,
2895 tax_event_type_code,
2896 tax_regime_id,
2897 tax_id,
2898 tax_status_id,
2899 tax_rate_id,
2900 mrc_tax_line_flag, -- Bug 5162537
2901 ledger_id,
2902 legal_entity_id,
2903 tax_currency_conversion_date,
2904 tax_currency_conversion_type,
2905 tax_currency_conversion_rate,
2906 trx_currency_code,
2907 currency_conversion_date,
2908 currency_conversion_type,
2909 currency_conversion_rate,
2910 trx_date,
2911 unit_price,
2912 line_amt,
2913 trx_line_quantity,
2914 offset_flag,
2915 process_for_recovery_flag,
2916 reporting_only_flag, -- Bug 8298174
2917 tax_jurisdiction_id,
2918 tax_date,
2919 tax_determine_date,
2920 trx_line_date,
2921 tax_type_code,
2922 compounding_tax_flag,
2923 taxable_amt_tax_curr,
2924 tax_apportionment_flag,
2925 historical_flag,
2926 purge_flag,
2927 freeze_until_overridden_flag,
2928 copied_from_other_doc_flag,
2929 global_attribute2,
2930 global_attribute4,
2931 global_attribute6,
2932 global_attribute_category,
2933 record_type_code,
2934 legal_justification_text1,
2935 legal_justification_text2,
2936 legal_justification_text3,
2937 object_version_number,
2938 creation_date,
2939 created_by,
2940 last_update_date,
2941 last_updated_by,
2942 last_update_login,
2943 establishment_id
2944 )
2945 -- VALUES (--ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_line_id(i),
2946 select ZX_LINES_S.nextval,
2947 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.internal_organization_id(i),
2948 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.application_id(i),
2949 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.entity_code(i),
2950 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.event_class_code(i),
2951 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.event_type_code(i),
2952 p_event_class_rec.first_pty_org_id,
2953 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_id(i),
2954 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(i),
2955 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(i),
2956 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(i),
2957 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_line_number(i),
2958 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(i),
2959 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(i),
2960 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(i),
2961 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rate_type_code(i),
2962 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_line_number(i),
2963 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.situs(i),
2964 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction(i),
2965 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_code(i),
2966 --ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_precision(i),
2967 --ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.minimum_accountable_unit(i),
2968 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rounding_rule_code(i),
2969 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_amount(i),
2970 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.unrounded_tax_amount(i),
2971 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.taxable_amount(i),
2972 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_curr_tax_amount(i),
2973 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_percentage(i),
2974 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.taxable_amount(i),
2975 --ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_amt(i),
2976 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_certificate_number(i),
2977 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_rate_modifier(i),
2978 rtrim(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(i)), --Bug 11896754
2979 rtrim(ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(i)), --Bug 11896754
2980 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_exemption_id(i),
2981 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_application_id(i),
2982 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_entity_code(i),
2983 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_event_class_code(i),
2984 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_id(i),
2985 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_line_id(i),
2986 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_level_type(i),
2987 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_number(i),
2988 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(i), -- Bug 5468010
2989 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(i), -- Bug 5468010
2990 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(i), -- Bug 5468010
2991 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(i), -- Bug 5468010
2992 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(i), -- Bug 5468010
2993 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(i), -- Bug 5468010
2994 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_number(i), -- Bug 5468010
2995 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_date(i), -- Bug 5468010
2996 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(i), -- Bug 6130978
2997 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.sync_with_prvdr_flag(i),
2998 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_only_line_flag(i),
2999 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.inclusive_tax_line_flag(i),
3000 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.use_tax_flag(i),
3001 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.user_override_flag(i),
3002 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.last_manual_entry(i),
3003 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_provider_id(i),
3004 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.manually_entered_flag(i),
3005 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.registration_party_type(i), -- Bug 5288518
3006 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.party_tax_reg_number(i), -- Bug 5288518
3007 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.cancel_flag(i),
3008 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.delete_flag(i),
3009 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_number(i),
3010 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_number(i),
3011 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.doc_event_status(i),
3012 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_event_class_code(i),
3013 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_event_type_code(i),
3014 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_regime_id(i),
3015 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_id(i),
3016 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_id(i),
3017 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_id(i),
3018 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.mrc_tax_line_flag(i), -- Bug 5162537
3019 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.ledger_id(i),
3020 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_entity_id(i),
3021 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_date(i),
3022 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_type(i),
3023 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_rate(i),
3024 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_currency_code(i),
3025 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.currency_conversion_date(i),
3026 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.currency_conversion_type(i),
3027 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.currency_conversion_rate(i),
3028 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_date(i),
3029 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.unit_price(i),
3030 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.line_amt(i),
3031 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_quantity(i),
3032 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.offset_flag(i),
3033 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.process_for_recovery_flag(i),
3034 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.reporting_only_flag(i), -- Bug 8298174
3035 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction_id(i),
3036 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_date(i),
3037 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_determine_date(i),
3038 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_date(i),
3039 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_type_code(i),
3040 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.compounding_tax_flag(i),
3041 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.taxable_amt_tax_curr(i),
3042 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_flag(i),
3043 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.historical_flag(i),
3044 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.purge_flag(i),
3045 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.freeze_until_overridden_flag(i),
3046 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.copied_from_other_doc_flag(i),
3047 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute2(i),
3048 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute4(i),
3049 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute6(i),
3050 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute_category(i),
3051 'ETAX_CREATED',
3052 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text1(i),
3053 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text2(i),
3054 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_justification_text3(i),
3055 1,
3056 sysdate,
3057 fnd_global.user_id,
3058 sysdate,
3059 fnd_global.user_id,
3060 fnd_global.conc_login_id,
3061 ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.establishment_id(i)
3062 from dual where line_level_action_tbl(i) <> 'DELETE';
3063 EXCEPTION WHEN OTHERS THEN
3064 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3065 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
3066 ' Exception while Populate zx_detail_tax_lines_gt: ' || sqlerrm);
3067 END IF;
3068 END;
3069
3070 -- Update THRESHOLD_INDICATOR_FLAG, partner migrated_flag on ZX_LINES_DET_FACTORS for the whole document.
3071 IF l_threshold_indicator_flag = 'Y' OR l_partner_migrated_flag = 'N' THEN
3072 UPDATE zx_lines_det_factors
3073 SET threshold_indicator_flag = nvl(l_threshold_indicator_flag, threshold_indicator_flag),
3074 partner_migrated_flag = nvl(l_partner_migrated_flag, partner_migrated_flag),
3075 line_amt_includes_tax_flag = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.line_amt_includes_tax_flag(1)
3076 WHERE application_id = p_event_class_rec.application_id
3077 AND entity_code = p_event_class_rec.entity_code
3078 AND event_class_code = p_event_class_rec.event_class_code
3079 AND trx_id = p_event_class_rec.trx_id;
3080 END IF;
3081
3082 --Reset any flags that may have been set during tax calculation for ease of coding in identifying the updated lines
3083 trx_line_app_regimes_tbl_hdl(p_event_class_rec => p_event_class_rec,
3084 p_event => 'RESET_FLAG',
3085 p_provider_id => null,
3086 p_tax_regime_code => null,
3087 p_trx_line_id => null,
3088 p_trx_level_type => null,
3089 x_return_status => l_return_status
3090 );
3091
3092 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3093 x_return_status := l_return_status;
3094 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3095 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
3096 ' RETURN_STATUS = ' || x_return_status);
3097 END IF;
3098 RETURN;
3099 END IF;
3100
3101 --Flush this table for subsequent calls
3102 tax_tbl.DELETE;
3103 record_type_code_tbl.DELETE;
3104 line_level_action_tbl.DELETE;
3105 partner_migrated_flag_tbl.DELETE;
3106 ZX_PTNR_SRVC_INTGRTN_PKG.G_TAX_CURRENCIES_TBL.DELETE;
3107
3108 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3109 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
3110 END IF;
3111
3112 EXCEPTION
3113 WHEN OTHERS THEN
3114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3115 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3116 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
3117 END IF;
3118 END ptnr_post_processing_calc_tax;
3119
3120
3121 -----------------------------------------------------------------------
3122 -- PUBLIC PROCEDURE
3123 -- ptnr_post_proc_sync_tax
3124 --
3125 -- DESCRIPTION
3126 -- This procedure is used to process the tax results returned by the
3127 -- partner's tax synchronization service. The processing of
3128 -- tax results involves validation of the data returned, and/or recording.
3129 --
3130 -- CALLED BY
3131 -- ZX_SRVC_TYPS_PKG.calculate_tax
3132 -- ZX_SRVC_TYPS_PKG.import
3133 -- ZX_SRVC_TYPS_PKG.partner_inclusive_tax_override
3134 -----------------------------------------------------------------------
3135
3136 PROCEDURE ptnr_post_proc_sync_tax(
3137 p_tax_regime_code IN VARCHAR2,
3138 p_tax_provider_id IN NUMBER,
3139 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
3140 x_return_status OUT NOCOPY VARCHAR2
3141 ) IS
3142
3143 l_api_name CONSTANT VARCHAR2(30) := 'PTNR_POST_PROC_SYNC_TAX';
3144 l_context_info_rec ZX_API_PUB.context_info_rec_type;
3145 l_last_manual_entry zx_lines.last_manual_entry%type;
3146 l_tax_line_id zx_lines.tax_line_id%type; -- Bug 4908196
3147 l_return_status VARCHAR2(1);
3148
3149 BEGIN
3150 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3151 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
3152 END IF;
3153
3154 --Set the return status to Success
3155 x_return_status := FND_API.G_RET_STS_SUCCESS;
3156
3157 /* Bug 4908196: Following assumption is true for tax partner integration.
3158 If partner needs to return more than one tax line of the same tax, the tax line with different situs will be sent. */
3159
3160 FOR i IN nvl(ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl.FIRST,0) .. nvl(ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl.LAST,-1)
3161 LOOP
3162 IF ZX_API_PUB.G_PUB_SRVC = 'OVERRIDE_TAX' THEN
3163 SELECT tax.last_manual_entry
3164 , tax.tax_line_id
3165 INTO l_last_manual_entry
3166 , l_tax_line_id
3167 FROM ZX_LINES tax
3168 WHERE tax.application_id = p_event_class_rec.application_id
3169 AND tax.entity_code = p_event_class_rec.entity_code
3170 AND tax.event_class_code = p_event_class_rec.event_class_code
3171 AND tax.trx_id = p_event_class_rec.trx_id
3172 AND tax.trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).transaction_line_id
3173 AND tax.trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).trx_level_type
3174 AND tax.tax_regime_code = p_tax_regime_code
3175 AND tax.tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax
3176 AND tax.place_of_supply_type_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).situs;
3177
3178 IF l_last_manual_entry = 'TAX_AMOUNT' THEN
3179 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax_rate_percentage is not null OR
3180 ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).taxable_amount is not null THEN
3181 UPDATE ZX_LINES
3182 SET tax_rate = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax_rate_percentage,
3183 taxable_amt = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).taxable_amount
3184 WHERE tax_line_id = l_tax_line_id;
3185 END IF;
3186 ELSIF l_last_manual_entry = 'TAX_RATE' THEN
3187 IF ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax_rate_percentage is not null THEN
3188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; --tax rate cannot be modified
3189 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3190 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'The tax partner cannot modify an overridden tax rate');
3191 END IF;
3192 ELSIF ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).taxable_amount is not null THEN
3193 UPDATE ZX_LINES
3194 SET taxable_amt = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).taxable_amount
3195 WHERE tax_line_id = l_tax_line_id;
3196 END IF;
3197 END IF; --last_manual_entry
3198 ELSE
3199 UPDATE ZX_LINES
3200 SET tax_rate = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax_rate_percentage,
3201 taxable_amt = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).taxable_amount
3202 WHERE application_id = p_event_class_rec.application_id
3203 AND entity_code = p_event_class_rec.entity_code
3204 AND event_class_code = p_event_class_rec.event_class_code
3205 AND trx_id = p_event_class_rec.trx_id
3206 AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).transaction_line_id
3207 AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).trx_level_type
3208 AND tax_regime_code = p_tax_regime_code
3209 AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax
3210 AND place_of_supply_type_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).situs;
3211 END IF; -- G_PUB_SRVC = OVERRIDE_TAX
3212 END LOOP;
3213
3214 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3215 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
3216 G_PKG_NAME ||': '||l_api_name||'(-)'||' RETURN_STATUS = ' || x_return_status);
3217 END IF;
3218
3219 EXCEPTION
3220 WHEN OTHERS THEN
3221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3222 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3223 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
3224 END IF;
3225 END ptnr_post_proc_sync_tax;
3226
3227 FUNCTION get_incl_tax_amt (
3228 p_application_id IN NUMBER,
3229 p_entity_code IN VARCHAR2,
3230 p_event_class_code IN VARCHAR2,
3231 p_trx_id IN NUMBER,
3232 p_trx_line_id IN NUMBER,
3233 p_trx_level_type IN VARCHAR2,
3234 p_tax_provider_id IN NUMBER
3235 )RETURN NUMBER
3236 IS
3237 l_api_name CONSTANT VARCHAR2(30) := 'GET_INCL_TAX_AMT';
3238 l_tax_amount NUMBER;
3239 BEGIN
3240 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3241 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
3242 END IF;
3243
3244 SELECT /*+ INDEX (TAX ZX_LINES_U1) */
3245 sum(nvl(tax.tax_amt,0))
3246 INTO l_tax_amount
3247 FROM ZX_LINES tax
3248 WHERE tax.application_id = p_application_id
3249 AND tax.entity_code = p_entity_code
3250 AND tax.event_class_code = p_event_class_code
3251 AND tax.trx_id = p_trx_id
3252 AND tax.trx_line_id = p_trx_line_id
3253 AND tax.trx_level_type = p_trx_level_type
3254 AND tax_amt_included_flag = 'Y'
3255 AND tax_provider_id <> p_tax_provider_id;
3256
3257 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3258 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||': '||l_api_name||'(-)');
3259 END IF;
3260
3261 RETURN l_tax_amount;
3262
3263 EXCEPTION
3264 WHEN OTHERS THEN
3265 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3266 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||': '||l_api_name||'(-)');
3267 END IF;
3268 RETURN 0;
3269 END get_incl_tax_amt;
3270
3271
3272 END ZX_TPI_SERVICES_PKG;