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