[Home] [Help]
PACKAGE BODY: APPS.ZX_TDS_TAX_LINES_POPU_PKG
Source
1 package body ZX_TDS_TAX_LINES_POPU_PKG as
2 /* $Header: zxditaxlnpoppkgb.pls 120.68.12010000.8 2009/02/02 10:06:44 tsen ship $ */
3
4
5 g_current_runtime_level NUMBER;
6 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 -----------------------------------------------------------------------
11 -- PUBLIC PROCEDURE
12 -- cp_tsrm_val_to_zx_lines
13 --
14 -- DESCRIPTION
15 --
16 -- This procedure is called after determine tax applicability process
17 -- It copies TSRM parameter values to TDS global detail tax line structures
18 -- It is one of the entry point to Populate Tax lines process
19 --
20 -- CALLED BY
21 -- ZX_TDS_APPLICABILITY_DETM_PKG
22 --
23
24 PROCEDURE cp_tsrm_val_to_zx_lines(
25 p_trx_line_index IN BINARY_INTEGER,
26 p_begin_index IN BINARY_INTEGER,
27 p_end_index IN BINARY_INTEGER,
28 p_return_status OUT NOCOPY VARCHAR2,
29 p_error_buffer OUT NOCOPY VARCHAR2
30 )
31 IS
32 j BINARY_INTEGER;
33
34 BEGIN
35
36 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
37
38 IF (g_level_statement >= g_current_runtime_level ) THEN
39 FND_LOG.STRING(g_level_statement,
40 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.cp_tsrm_val_to_zx_lines.BEGIN',
41 'ZX_TDS_TAX_LINES_POPU_PKG: cp_tsrm_val_to_zx_lines(+)');
42 END IF;
43
44 --
45 -- init error buffer and return status
46 --
47 p_return_status := FND_API.G_RET_STS_SUCCESS;
48 p_error_buffer := NULL;
49
50 --
51 -- check if begin_index and end_index have values
52 --
53 IF (p_begin_index IS NULL OR p_end_index IS NULL) THEN
54
55 IF (g_level_statement >= g_current_runtime_level ) THEN
56 FND_LOG.STRING(g_level_statement,
57 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.cp_tsrm_val_to_zx_lines.END',
58 'ZX_TDS_TAX_LINES_POPU_PKG: cp_tsrm_val_to_zx_lines(-)'||'Begin index or End index is null');
59
60 END IF;
61 RETURN;
62 END IF;
63
64 --
65 -- get the index to transaction line table
66 --
67 j := p_trx_line_index;
68
69 FOR i IN p_begin_index ..p_end_index LOOP
70
71 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).account_string :=
72 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.account_string(j);
73 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).adjusted_doc_application_id :=
74 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(j);
75 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).adjusted_doc_entity_code :=
76 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(j);
77 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).adjusted_doc_event_class_code :=
78 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(j);
79 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).adjusted_doc_line_id :=
80 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(j);
81 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).adjusted_doc_trx_level_type :=
82 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(j);
83 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).adjusted_doc_trx_id :=
84 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(j);
85 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).adjusted_doc_date :=
86 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_date(j);
87 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).adjusted_doc_number :=
88 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_number(j);
89 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).application_id :=
90 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(j);
91 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_from_application_id :=
92 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(j);
93 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_from_entity_code :=
94 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(j);
95 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_from_event_class_code :=
96 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(j);
97 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_from_line_id :=
98 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(j);
99 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_from_trx_level_type :=
100 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(j);
101 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_from_trx_id :=
102 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(j);
103 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_from_trx_number :=
104 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_number;
105 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_to_application_id :=
106 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_to_application_id(j);
107 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_to_event_class_code :=
108 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_to_event_class_code(j);
109 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_to_entity_code :=
110 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_to_entity_code(j);
111 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_to_line_id :=
112 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_to_trx_line_id(j);
113 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_to_trx_level_type :=
114 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_to_trx_level_type(j);
115 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_to_trx_id :=
116 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_to_trx_id(j);
117 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).applied_to_trx_number :=
118 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_to_trx_number(j);
119 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_assessable_value :=
120 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.assessable_value(j);
121 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Asset_Flag :=
122 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.Asset_Flag(j);
123 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).asset_number :=
124 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.asset_number(j);
125 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).asset_accum_depreciation :=
126 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.asset_accum_depreciation(j);
127 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).asset_cost :=
128 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.asset_cost(j);
129 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).asset_type :=
130 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.asset_type(j);
131 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).banking_tp_taxpayer_id :=
132 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.banking_tp_taxpayer_id(j);
133 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).batch_source_id :=
134 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.batch_source_id(j);
135 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).batch_source_name :=
136 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.batch_source_name(j);
137 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).bill_from_location_id :=
138 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.bill_from_location_id(j);
139 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).bill_from_party_tax_prof_id :=
140 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.bill_from_party_tax_prof_id(j);
141 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).bill_from_site_tax_prof_id :=
142 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.bill_from_site_tax_prof_id(j);
143 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).bill_to_location_id :=
144 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.bill_to_location_id(j);
145 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).bill_to_party_tax_prof_id :=
146 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.bill_to_party_tax_prof_id(j);
147 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).bill_to_site_tax_prof_id :=
148 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.bill_to_site_tax_prof_id(j);
149 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).billing_tp_taxpayer_id :=
150 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.billing_tp_taxpayer_id(j);
151 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Billing_Tp_Tax_Reporting_Flag :=
152 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.Billing_Tp_Tax_Reporting_Flag(j);
153 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).billing_trading_partner_number :=
154 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.billing_trading_partner_number(j);
155 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).billing_trading_partner_name :=
156 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.billing_trading_partner_name(j);
157 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).cash_discount :=
158 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.cash_discount(j);
159 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char1 :=
160 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char1(j);
161 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char2 :=
162 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char2(j);
163 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char3 :=
164 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char3(j);
165 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char4 :=
166 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char4(j);
167 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char5 :=
168 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char5(j);
169 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char6 :=
170 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char6(j);
171 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char7 :=
172 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char7(j);
173 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char8 :=
174 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char8(j);
175 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char9 :=
176 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char9(j);
177 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).char10 :=
178 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.char10(j);
179 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).account_ccid :=
180 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.account_ccid(j);
181 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).currency_conversion_date :=
182 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.currency_conversion_date(j);
183 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).currency_conversion_rate :=
184 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.currency_conversion_rate(j);
185 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).currency_conversion_type :=
186 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.currency_conversion_type(j);
187 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date1 :=
188 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date1(j);
189 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date2 :=
190 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date2(j);
191 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date3 :=
192 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date3(j);
193 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date4 :=
194 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date4(j);
195 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date5 :=
196 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date5(j);
197 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date6 :=
198 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date6(j);
199 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date7 :=
200 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date7(j);
201 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date8 :=
202 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date8(j);
203 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date9 :=
204 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date9(j);
205 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).date10 :=
206 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.date10(j);
207 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).default_taxation_country :=
208 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.default_taxation_country(j);
209 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).doc_event_status :=
210 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.doc_event_status(j);
211 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).doc_seq_id :=
212 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.doc_seq_id(j);
213 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).doc_seq_name :=
214 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.doc_seq_name(j);
215 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).doc_seq_value :=
216 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.doc_seq_value(j);
217 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).document_sub_type :=
218 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.document_sub_type(j);
219 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).entity_code :=
220 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.entity_code(j);
221 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).establishment_id :=
222 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.establishment_id(j);
223 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).event_class_code :=
224 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.event_class_code(j);
225 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).event_type_code :=
226 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.event_type_code(j);
227 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).exempt_certificate_number :=
228 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.exempt_certificate_number(j);
229 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).fob_point :=
230 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.fob_point(j);
231 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_gl_date :=
232 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_gl_date(j);
233 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Historical_Flag
234 is NULL THEN
235 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Historical_Flag :=
236 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.Historical_Flag(j);
237 END IF;
238 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).hq_estb_party_tax_prof_id :=
239 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.hq_estb_party_tax_prof_id(j);
240 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).insurance_charge :=
241 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.insurance_charge(j);
242 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_intended_use :=
243 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_intended_use(j);
244 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).internal_organization_id :=
245 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.internal_organization_id(j);
246 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).legal_entity_id :=
247 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.legal_entity_id(j);
248 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_amt :=
249 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_amt(j);
250 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_level_action :=
251 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(j);
252 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).minimum_accountable_unit :=
253 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.minimum_accountable_unit(j);
254 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric1 :=
255 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric1(j);
256 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric2 :=
257 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric2(j);
258 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric3 :=
259 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric3(j);
260 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric4 :=
261 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric4(j);
262 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric5 :=
263 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric5(j);
264 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric6 :=
265 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric6(j);
266 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric7 :=
267 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric7(j);
268 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric8 :=
269 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric8(j);
270 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric9 :=
271 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric9(j);
272 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).numeric10 :=
273 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.numeric10(j);
274 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_charge :=
275 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.other_charge(j);
276 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).own_hq_location_id :=
277 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.own_hq_location_id(j);
278 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).own_hq_party_tax_prof_id :=
279 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.own_hq_party_tax_prof_id(j);
280 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).own_hq_site_tax_prof_id :=
281 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.own_hq_site_tax_prof_id(j);
282 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Line_Amt_Includes_Tax_Flag :=
283 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.Line_Amt_Includes_Tax_Flag(j);
284 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).paying_location_id :=
285 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.paying_location_id(j);
286 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).paying_party_tax_prof_id :=
287 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.paying_party_tax_prof_id(j);
288 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).paying_site_tax_prof_id :=
289 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.paying_site_tax_prof_id(j);
290 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poa_location_id :=
291 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_location_id(j);
292 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poa_party_tax_prof_id :=
293 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_party_tax_prof_id(j);
294 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poa_site_tax_prof_id :=
295 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_site_tax_prof_id(j);
296 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).pod_party_tax_prof_id :=
297 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.pod_party_tax_prof_id(j);
298 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).pod_site_tax_prof_id :=
299 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.pod_site_tax_prof_id(j);
300 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poi_party_tax_prof_id :=
301 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poi_party_tax_prof_id(j);
302 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poi_site_tax_prof_id :=
303 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poi_site_tax_prof_id(j);
304 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poc_location_id :=
305 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poc_location_id(j);
306 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).pod_location_id :=
307 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.pod_location_id(j);
308 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poi_location_id :=
309 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poi_location_id(j);
310 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poo_location_id :=
311 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poo_location_id(j);
312 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poo_party_tax_prof_id :=
313 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poo_party_tax_prof_id(j);
314 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).poo_site_tax_prof_id :=
315 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poo_site_tax_prof_id(j);
316 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).precision :=
317 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.precision(j);
318 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).product_code :=
319 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_code(j);
320 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).product_description :=
321 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_description(j);
322 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).product_id :=
323 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_id(j);
324 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).product_category :=
325 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_category(j);
326 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).product_fisc_classification :=
327 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_fisc_classification(j);
328 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).product_org_id :=
329 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_org_id(j);
330 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).product_type :=
331 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_type(j);
332 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).related_doc_application_id :=
333 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_application_id(j);
334 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).related_doc_entity_code :=
335 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_entity_code(j);
336 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).related_doc_event_class_code :=
337 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_event_class_code(j);
338 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).related_doc_trx_id :=
339 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_trx_id(j);
340 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).related_doc_number :=
341 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_number(j);
342 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).related_doc_date :=
343 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_date(j);
344 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ref_doc_line_quantity :=
345 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_line_quantity(j);
346 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ref_doc_application_id :=
347 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_application_id(j);
348 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ref_doc_entity_code :=
349 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_entity_code(j);
350 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ref_doc_event_class_code :=
351 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_event_class_code(j);
352 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ref_doc_line_id :=
353 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_line_id(j);
354 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ref_doc_trx_level_type :=
355 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_trx_level_type(j);
356 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ref_doc_trx_id :=
357 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_trx_id(j);
358 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ledger_id :=
359 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ledger_id(j);
360 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ship_from_location_id :=
361 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ship_from_location_id(j);
362 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ship_from_party_tax_prof_id :=
363 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ship_from_party_tax_prof_id(j);
364 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ship_from_site_tax_prof_id :=
365 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ship_from_site_tax_prof_id(j);
366 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ship_to_location_id :=
367 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ship_to_location_id(j);
368 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ship_to_party_tax_prof_id :=
369 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ship_to_party_tax_prof_id(j);
370 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ship_to_site_tax_prof_id :=
371 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ship_to_site_tax_prof_id(j);
372 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).content_owner_id :=
373 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.first_pty_org_id(j); -- subscriber_id(j): bug fix 3462583
374 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).supplier_tax_invoice_number :=
375 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.supplier_tax_invoice_number(j);
376 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).supplier_tax_invoice_date :=
377 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.supplier_tax_invoice_date(j);
378 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).supplier_exchange_rate :=
379 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.supplier_exchange_rate(j);
380 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_class_code :=
381 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_event_class_code(j);
382 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code :=
383 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_event_type_code(j);
384 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_invoice_date :=
385 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_invoice_date(j);
386 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_invoice_number :=
387 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_invoice_number(j);
388 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).merchant_party_country :=
389 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.merchant_party_country(j);
390 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).merchant_party_document_number :=
391 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.merchant_party_document_number(j);
392 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).merchant_party_name :=
393 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.merchant_party_name(j);
394 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).merchant_party_reference :=
395 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.merchant_party_reference(j);
396 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).merchant_party_tax_prof_id :=
397 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.merchant_party_tax_prof_id(j);
398 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).merchant_party_tax_reg_number :=
399 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.merchant_party_tax_reg_number(j);
400 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).merchant_party_taxpayer_id :=
401 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.merchant_party_taxpayer_id(j);
402 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).title_trans_party_tax_prof_id :=
403 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.title_trans_party_tax_prof_id(j);
404 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).title_trans_site_tax_prof_id :=
405 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.title_trans_site_tax_prof_id(j);
406 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).title_transfer_location_id :=
407 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.title_transfer_location_id(j);
408 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trading_discount :=
409 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trading_discount(j);
410 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trading_hq_location_id :=
411 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trading_hq_location_id(j);
412 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trading_hq_party_tax_prof_id :=
413 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trading_hq_party_tax_prof_id(j);
414 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trading_hq_site_tax_prof_id :=
415 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trading_hq_site_tax_prof_id(j);
416 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_number :=
417 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_number(j);
418 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_currency_code :=
419 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_currency_code(j);
420 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_date :=
421 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_date(j);
422 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_id :=
423 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id(j);
424 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_id_level2 :=
425 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id_level2(j);
426 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_id_level3 :=
427 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id_level3(j);
428 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_id_level4 :=
429 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id_level4(j);
430 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_id_level5 :=
431 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id_level5(j);
432 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_id_level6 :=
433 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id_level6(j);
434 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type :=
435 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(j);
436 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_description :=
437 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_description(j);
438 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id :=
439 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(j);
440 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_number :=
441 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_number(j);
442 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_type :=
443 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_type(j);
444 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).transfer_charge :=
445 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.transfer_charge(j);
446 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).transportation_charge :=
447 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.transportation_charge(j);
448 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_business_category :=
449 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_business_category(j);
450 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_communicated_date :=
451 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_communicated_date(j);
452 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_description :=
453 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_description(j);
454 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_doc_revision :=
455 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_doc_revision(j);
456 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_due_date :=
457 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_due_date(j);
458 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_date :=
459 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_date(j);
460 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_quantity :=
461 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_quantity(j);
462 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_receipt_date :=
463 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_receipt_date(j);
464 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_shipping_date :=
465 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_shipping_date(j);
466 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_sic_code :=
467 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_sic_code(j);
468 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_type_description :=
469 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_type_description(j);
470 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).receivables_trx_type_id :=
471 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.receivables_trx_type_id(j);
472 /***********************
473 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_user_key_level1 :=
474 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_trx_user_key1(j);
475 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_user_key_level2 :=
476 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_trx_user_key2(j);
477 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_user_key_level3 :=
478 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_trx_user_key3(j);
479 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_user_key_level4 :=
480 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_trx_user_key4(j);
481 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_user_key_level5 :=
482 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_trx_user_key5(j);
483 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_user_key_level6 :=
484 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_trx_user_key6(j);
485 *******************/
486 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_waybill_number :=
487 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_waybill_number(j);
488 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unit_price :=
489 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.unit_price(j);
490 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).internal_org_location_id :=
491 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.internal_org_location_id(j);
492
493 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ctrl_total_line_tx_amt :=
494 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ctrl_total_line_tx_amt(j);
495
496 -- bug fix 4630692
497 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_currency_code :=
498 NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_currency_code(j),
499 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_currency_code(j));
500
501 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).currency_conversion_date :=
502 NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.currency_conversion_date(j),
503 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_currency_conv_date(j));
504
505 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).currency_conversion_rate :=
506 NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.currency_conversion_rate(j),
507 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_currency_conv_rate(j));
508
509 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).currency_conversion_type :=
510 NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.currency_conversion_type(j),
511 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_currency_conv_type(j));
512
513
514 END LOOP;
515
516 IF (g_level_statement >= g_current_runtime_level ) THEN
517
518 FND_LOG.STRING(g_level_statement,
519 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.cp_tsrm_val_to_zx_lines.END',
520 'ZX_TDS_TAX_LINES_POPU_PKG: cp_tsrm_val_to_zx_lines(-)'||p_return_status);
521 END IF;
522
523 EXCEPTION
524 WHEN OTHERS THEN
525 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
526 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
527
528 IF (g_level_unexpected >= g_current_runtime_level ) THEN
529 FND_LOG.STRING(g_level_unexpected,
530 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.cp_tsrm_val_to_zx_lines',
531 p_error_buffer);
532 END IF;
533
534 END cp_tsrm_val_to_zx_lines;
535
536 -----------------------------------------------------------------------
537 -- PUBLIC PROCEDURE
538 -- populate_tax_line
539 --
540 -- DESCRIPTION
541 -- This procedure is called before tax lines summarization process
542 -- It is one of the entry point to populate Tax lines process
543 --
544 -- NOTE : currently not use
545
546 PROCEDURE populate_tax_line(
547 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
548 p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
549 p_return_status OUT NOCOPY VARCHAR2,
550 p_error_buffer OUT NOCOPY VARCHAR2)
551 IS
552 BEGIN
553
554 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
555
556 IF (g_level_statement >= g_current_runtime_level ) THEN
557 FND_LOG.STRING(g_level_statement,
558 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_tax_lines.BEGIN',
559 'ZX_TDS_TAX_LINES_POPU_PKG: populate_tax_lines(+)');
560 END IF;
561
562 p_return_status := FND_API.G_RET_STS_SUCCESS;
563 p_error_buffer := NULL;
564
565
566 /*
567 * populate orig columns in override case only
568 * and this should be done by UI
569 *
570 *IF ((p_event_class_rec.tax_event_type_code = 'CREATE' AND
571 * p_tax_line_rec.Manually_Entered_Flag = 'N') OR
572 * (p_event_class_rec.tax_event_type_code = 'UPDATE' AND
573 * p_tax_line_rec.line_level_action = 'CREATE') OR
574 * (p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX' AND
575 * p_tax_line_rec.Manually_Entered_Flag = 'Y') ) THEN
576 * populate_orig_columns(p_tax_line_rec);
577 * END IF;
578 */
579
580 p_tax_line_rec.line_assessable_value := p_tax_line_rec.taxable_amt;
581
582 populate_mandatory_columns(p_tax_line_rec,
583 p_return_status,
584 p_error_buffer);
585
586 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
587 RETURN;
588 END IF;
589
590 check_mandatory_columns(p_tax_line_rec,
591 p_return_status,
592 p_error_buffer);
593
594 IF (g_level_statement >= g_current_runtime_level ) THEN
595
596 FND_LOG.STRING(g_level_statement,
597 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_tax_lines.END',
598 'ZX_TDS_TAX_LINES_POPU_PKG: populate_tax_lines(-)'||p_return_status);
599 END IF;
600
601 EXCEPTION
602 WHEN OTHERS THEN
603 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
605
606 IF (g_level_unexpected >= g_current_runtime_level ) THEN
607 FND_LOG.STRING(g_level_unexpected,
608 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_tax_line',
609 p_error_buffer);
610 END IF;
611
612 END populate_tax_line;
613
614 -----------------------------------------------------------------------
615 -- PUBLIC PROCEDURE
616 -- populate_orig_columns
617 --
618 -- DESCRIPTION
619 -- This procedure populates original columns for a tax line record
620 --
621 -- NOTE : currently not use
622
623 PROCEDURE populate_orig_columns(
624 p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE)
625
626 IS
627 BEGIN
628
629 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
630
631 IF (g_level_statement >= g_current_runtime_level ) THEN
632 FND_LOG.STRING(g_level_statement,
633 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_orig_columns.BEGIN',
634 'ZX_TDS_TAX_LINES_POPU_PKG: populate_orig_columns(+)');
635 END IF;
636
637 IF p_tax_line_rec.orig_tax_status_id IS NULL THEN
638 p_tax_line_rec.orig_tax_status_id := p_tax_line_rec.tax_status_id;
639 END IF;
640 IF p_tax_line_rec.orig_tax_status_code IS NULL THEN
641 p_tax_line_rec.orig_tax_status_code := p_tax_line_rec.tax_status_code;
642 END IF;
643 IF p_tax_line_rec.orig_tax_rate_id IS NULL THEN
644 p_tax_line_rec.orig_tax_rate_id := p_tax_line_rec.tax_rate_id;
645 END IF;
646 IF p_tax_line_rec.orig_tax_rate_code IS NULL THEN
647 p_tax_line_rec.orig_tax_rate_code := p_tax_line_rec.tax_rate_code;
648 END IF;
649 IF p_tax_line_rec.orig_tax_rate IS NULL THEN
650 p_tax_line_rec.orig_tax_rate := p_tax_line_rec.tax_rate;
651 END IF;
652 IF p_tax_line_rec.orig_taxable_amt IS NULL THEN
653 p_tax_line_rec.orig_taxable_amt := p_tax_line_rec.taxable_amt;
654 END IF;
655 IF p_tax_line_rec.orig_taxable_amt_tax_curr IS NULL THEN
656 p_tax_line_rec.orig_taxable_amt_tax_curr :=
657 p_tax_line_rec.taxable_amt_tax_curr;
658 END IF;
659 IF p_tax_line_rec.orig_tax_amt IS NULL THEN
660 p_tax_line_rec.orig_tax_amt := p_tax_line_rec.tax_amt;
661 END IF;
662 IF p_tax_line_rec.orig_tax_amt_tax_curr IS NULL THEN
663 p_tax_line_rec.orig_tax_amt_tax_curr :=
664 p_tax_line_rec.tax_amt_tax_curr;
665 END IF;
666
667
668 IF (g_level_statement >= g_current_runtime_level ) THEN
669
670 FND_LOG.STRING(g_level_statement,
671 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_orig_columns.END',
672 'ZX_TDS_TAX_LINES_POPU_PKG: populate_orig_columns(-)');
673 END IF;
674
675 EXCEPTION
676 WHEN OTHERS THEN
677
678 IF (g_level_unexpected >= g_current_runtime_level ) THEN
679 FND_LOG.STRING(g_level_unexpected,
680 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_orig_columns',
681 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
682 END IF;
683
684 END populate_orig_columns;
685
686 -----------------------------------------------------------------------
687 -- PUBLIC PROCEDURE
688 -- populate_mandatory_columns
689 --
690 -- DESCRIPTION
691 -- This procedure populates mandatory columns such as tax line id, who
692 -- columns, etc for a tax line record
693 --
694 -- CALLED BY
695 -- ZX_TDS_OFFSET_TAX_DETM_PKG
696
697 PROCEDURE populate_mandatory_columns(
698 p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
699 p_return_status OUT NOCOPY VARCHAR2,
700 p_error_buffer OUT NOCOPY VARCHAR2)
701
702 IS
703
704 BEGIN
705
706 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
707
708 IF (g_level_statement >= g_current_runtime_level ) THEN
709 FND_LOG.STRING(g_level_statement,
710 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_mandatory_columns.BEGIN',
711 'ZX_TDS_TAX_LINES_POPU_PKG: populate_mandatory_columns(+)');
712 END IF;
713
714 p_return_status := FND_API.G_RET_STS_SUCCESS;
715
716 --
717 -- populate tax_line_id if it is null
718 --
719 IF p_tax_line_rec.tax_line_id IS NULL THEN
720 SELECT ZX_LINES_S.nextval
721 INTO p_tax_line_rec.tax_line_id
722 FROM dual;
723 END IF;
724
725
726 /******************************************
727 * move to set_detail_tax_line_def_val
728 * in ZX_TDS_CALC_SERVICES_PUB_PKG now
729 *
730 *
731 *
732 * -- populate Record_Type_Code
733 * IF p_tax_line_rec.Record_Type_Code IS NULL THEN
734 * p_tax_line_rec.Record_Type_Code := 'ETAX_CREATED';
735 * END IF;
736 *
737 * -- populate Historical_Flag
738 * IF p_tax_line_rec.Historical_Flag IS NULL THEN
739 * p_tax_line_rec.Historical_Flag := 'N';
740 * END IF;
741 **********************************************/
742
743 -- populate who columns
744
745
746 IF p_tax_line_rec.created_by IS NULL THEN
747 p_tax_line_rec.created_by := fnd_global.user_id;
748 END IF;
749
750 IF p_tax_line_rec.creation_date IS NULL THEN
751 p_tax_line_rec.creation_date := sysdate;
752 END IF;
753
754 p_tax_line_rec.last_updated_by := fnd_global.user_id;
755 p_tax_line_rec.last_update_login := fnd_global.login_id;
756 p_tax_line_rec.last_update_date := sysdate;
757
758 IF (g_level_statement >= g_current_runtime_level ) THEN
759
760 FND_LOG.STRING(g_level_statement,
761 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_mandatory_columns.END',
762 'ZX_TDS_TAX_LINES_POPU_PKG: populate_mandatory_columns(-)'||p_return_status);
763 END IF;
764
765 EXCEPTION
766 WHEN OTHERS THEN
767 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
768 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
769
770 IF (g_level_unexpected >= g_current_runtime_level ) THEN
771 FND_LOG.STRING(g_level_unexpected,
772 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_mandatory_columns',
773 p_error_buffer);
774 END IF;
775
776 END populate_mandatory_columns;
777 -----------------------------------------------------------------------
778 -- PUBLIC PROCEDURE
779 -- check_mandatory_columns
780 --
781 -- DESCRIPTION
782 -- This procedure ensures all mandatory columns are not NULL for a
783 -- tax line record
784 --
785 -- CALLED BY
786 -- populate_tax_lines
787
788 PROCEDURE check_mandatory_columns(
789 p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
790 p_return_status OUT NOCOPY VARCHAR2,
791 p_error_buffer OUT NOCOPY VARCHAR2)
792
793 IS
794 BEGIN
795
796 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
797
798 IF (g_level_statement >= g_current_runtime_level ) THEN
799 FND_LOG.STRING(g_level_statement,
800 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns.BEGIN',
801 'ZX_TDS_TAX_LINES_POPU_PKG: check_mandatory_columns(+)');
802 END IF;
803
804 p_return_status := FND_API.G_RET_STS_SUCCESS;
805
806 --
807 -- check mandatory columns for all tax lines
808 --
809 check_mandatory_columns_all(p_tax_line_rec,
810 p_return_status,
811 p_error_buffer);
812 IF (p_return_status = FND_API.G_RET_STS_SUCCESS AND
813 NVL(p_tax_line_rec.Manually_Entered_Flag,'N') = 'N') THEN
814 check_non_manual_tax_line(p_tax_line_rec,
815 p_return_status,
816 p_error_buffer);
817 END IF;
818
819 IF (g_level_statement >= g_current_runtime_level ) THEN
820 FND_LOG.STRING(g_level_statement,
821 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns',
822 'p_return_status = ' || p_return_status);
823 FND_LOG.STRING(g_level_statement,
824 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns',
825 'p_error_buffer = ' || p_error_buffer);
826 FND_LOG.STRING(g_level_statement,
827 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns.END',
828 'ZX_TDS_TAX_LINES_POPU_PKG: check_mandatory_columns(-)');
829 END IF;
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
835
836 IF (g_level_unexpected >= g_current_runtime_level ) THEN
837 FND_LOG.STRING(g_level_unexpected,
838 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns',
839 p_error_buffer);
840 END IF;
841
842 END check_mandatory_columns;
843 -----------------------------------------------------------------------
844 -- PUBLIC PROCEDURE
845 -- check_mandatory_columns_all
846 --
847 -- DESCRIPTION
848 -- This procedure ensures all mandatory columns belonged to a manual and
849 -- non manual tax line are not NULL for a tax line record
850 --
851 -- CALLED BY
852 -- check_mandatory_columns
853
854 PROCEDURE check_mandatory_columns_all(
855 p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
856 p_return_status OUT NOCOPY VARCHAR2,
857 p_error_buffer OUT NOCOPY VARCHAR2)
858
859 IS
860 BEGIN
861
862 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
863
864 IF (g_level_statement >= g_current_runtime_level ) THEN
865 FND_LOG.STRING(g_level_statement,
866 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns_all.BEGIN',
867 'ZX_TDS_TAX_LINES_POPU_PKG: check_mandatory_columns_all(+)');
868 END IF;
869
870 p_return_status := FND_API.G_RET_STS_SUCCESS;
871
872 IF p_tax_line_rec.application_id IS NULL THEN
873 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
874 p_error_buffer := 'application_id can not be NULL';
875 ELSIF p_tax_line_rec.entity_code IS NULL THEN
876 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
877 p_error_buffer := 'entity_code can not be NULL';
878 ELSIF p_tax_line_rec.event_class_code IS NULL THEN
879 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
880 p_error_buffer := 'event_class_code can not be NULL';
881 ELSIF p_tax_line_rec.event_type_code IS NULL THEN
882 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
883 p_error_buffer := 'event_type_code can not be NULL';
884 ELSIF p_tax_line_rec.trx_id IS NULL THEN
885 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
886 p_error_buffer := 'trx_id can not be NULL';
887 ELSIF p_tax_line_rec.trx_line_id IS NULL THEN
888 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
889 p_error_buffer := 'trx_line_id can not be NULL';
890 ELSIF p_tax_line_rec.trx_level_type IS NULL THEN
891 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
892 p_error_buffer := 'trx_level_type can not be NULL';
893
894 -- Bug#4572001- content_owner_id can be NULL
895 -- ELSIF p_tax_line_rec.content_owner_id IS NULL THEN
896 -- p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
897 -- p_error_buffer := 'content_owner_id can not be NULL';
898
899 ELSIF p_tax_line_rec.tax_regime_id IS NULL THEN
900 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
901 p_error_buffer := 'tax_regime_id can not be NULL';
902 ELSIF p_tax_line_rec.tax_regime_code IS NULL THEN
903 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904 p_error_buffer := 'tax_regime_code can not be NULL';
905 ELSIF p_tax_line_rec.tax_id IS NULL THEN
906 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
907 p_error_buffer := 'tax_id can not be NULL';
908 ELSIF p_tax_line_rec.tax IS NULL THEN
909 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910 p_error_buffer := 'tax can not be NULL';
911 ELSIF p_tax_line_rec.tax_status_id IS NULL THEN
912 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913 p_error_buffer := 'tax_status_id can not be NULL';
914 ELSIF p_tax_line_rec.tax_status_code IS NULL THEN
915 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
916 p_error_buffer := 'tax_status_code can not be NULL';
917 END IF;
918
919 IF (g_level_statement >= g_current_runtime_level ) THEN
920 FND_LOG.STRING(g_level_statement,
921 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns_all',
922 'p_return_status = ' || p_return_status);
923 FND_LOG.STRING(g_level_statement,
924 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns_all',
925 'p_error_buffer = ' || p_error_buffer);
926 FND_LOG.STRING(g_level_statement,
927 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns_all.END',
928 'ZX_TDS_TAX_LINES_POPU_PKG: check_mandatory_columns_all(-)');
929 END IF;
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
935
936 IF (g_level_unexpected >= g_current_runtime_level ) THEN
937 FND_LOG.STRING(g_level_unexpected,
938 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_mandatory_columns_all',
939 p_error_buffer);
940 END IF;
941
942 END check_mandatory_columns_all;
943
944 -----------------------------------------------------------------------
945 -- PUBLIC PROCEDURE
946 -- check_non_manual_tax_line
947 --
948 -- DESCRIPTION
949 -- This procedure ensures all mandatory columns belonged to a
950 -- non manually entered tax line are not NULL for a tax line record
951 --
952 -- CALLED BY
953 -- check_mandatory_columns
954
955
956 PROCEDURE check_non_manual_tax_line(
957 p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
958 p_return_status OUT NOCOPY VARCHAR2,
959 p_error_buffer OUT NOCOPY VARCHAR2)
960
961 IS
962 BEGIN
963
964 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
965
966 IF (g_level_statement >= g_current_runtime_level ) THEN
967 FND_LOG.STRING(g_level_statement,
968 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_non_manual_tax_line.BEGIN',
969 'ZX_TDS_TAX_LINES_POPU_PKG: check_non_manual_tax_line(+)');
970 END IF;
971
972 p_return_status := FND_API.G_RET_STS_SUCCESS;
973
974 IF p_tax_line_rec.trx_date IS NULL THEN
975 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
976 p_error_buffer := 'trx_date can not be NULL';
977 --ELSIF p_tax_line_rec.trx_line_date IS NULL THEN
978 -- p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
979 -- p_error_buffer := 'trx_line_date can not be NULL';
980 ELSIF p_tax_line_rec.ledger_id IS NULL THEN
981 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
982 p_error_buffer := 'ledger_id can not be NULL';
983
984 /* comment out only for AP uptake testing purpose. Change to reversed later. chku
985 ELSIF p_tax_line_rec.establishment_id IS NULL THEN
986 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
987 p_error_buffer := 'establishment_id can not be NULL';
988 */
989 ELSIF p_tax_line_rec.legal_entity_id IS NULL THEN
990 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
991 p_error_buffer := 'legal_entity_id can not be NULL';
992 ELSIF p_tax_line_rec.tax_rate_id IS NULL THEN
993 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
994 p_error_buffer := 'tax_rate_id can not be NULL';
995 ELSIF p_tax_line_rec.tax_rate_code IS NULL THEN
996 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
997 p_error_buffer := 'tax_rate_code can not be NULL';
998 ELSIF p_tax_line_rec.tax_rate IS NULL THEN
999 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1000 p_error_buffer := 'tax_rate can not be NULL';
1001
1002 /* comment out only for AP uptake testing purpose. Change to reversed later. chku
1003 ELSIF p_tax_line_rec.tax_apportionment_line_number IS NULL THEN
1004 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1005 p_error_buffer := 'tax_apportionment_line_number can not be NULL';
1006 */
1007 ELSIF p_tax_line_rec.tax_date IS NULL THEN
1008 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1009 p_error_buffer := 'tax_date can not be NULL';
1010 ELSIF p_tax_line_rec.tax_determine_date IS NULL THEN
1011 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1012 p_error_buffer := 'tax_determine_date can not be NULL';
1013 ELSIF p_tax_line_rec.tax_point_date IS NULL THEN
1014 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1015 p_error_buffer := 'tax_point_date can not be NULL';
1016
1017 END IF;
1018
1019 IF (g_level_statement >= g_current_runtime_level ) THEN
1020 FND_LOG.STRING(g_level_statement,
1021 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_non_manual_tax_line',
1022 'p_return_status = ' || p_return_status);
1023 FND_LOG.STRING(g_level_statement,
1024 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_non_manual_tax_line',
1025 'p_error_buffer = ' || p_error_buffer);
1026 FND_LOG.STRING(g_level_statement,
1027 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_non_manual_tax_line.END',
1028 'ZX_TDS_TAX_LINES_POPU_PKG: check_non_manual_tax_line(-)');
1029 END IF;
1030
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1034 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1035
1036 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1037 FND_LOG.STRING(g_level_unexpected,
1038 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.check_non_manual_tax_line',
1039 p_error_buffer);
1040 END IF;
1041
1042 END check_non_manual_tax_line;
1043
1044 -----------------------------------------------------------------------
1045 -- PUBLIC PROCEDURE
1046 -- pop_tax_line_for_trx_line
1047 --
1048 -- DESCRIPTION
1049 -- This procedure is called after tax calculation is done for
1050 -- all tax lines belonged to a transaction line. It populates
1051 -- missing columns and ensures all mandatory columns are not
1052 -- NULL for each tax line belonged to a transaction line.
1053 -- It is one of the entry point to populate Tax lines process
1054 --
1055 -- CALLED BY
1056 -- ZX_TDS_CALC_SERVICES_PUB_PKG
1057
1058
1059 PROCEDURE pop_tax_line_for_trx_line(
1060 p_begin_index IN BINARY_INTEGER,
1061 p_end_index IN BINARY_INTEGER,
1062 p_return_status OUT NOCOPY VARCHAR2,
1063 p_error_buffer OUT NOCOPY VARCHAR2)
1064 IS
1065
1066 BEGIN
1067
1068 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1069
1070 IF (g_level_statement >= g_current_runtime_level ) THEN
1071 FND_LOG.STRING(g_level_statement,
1072 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_tax_line_for_trx_line.BEGIN',
1073 'ZX_TDS_TAX_LINES_POPU_PKG: pop_tax_line_for_trx_line(+)');
1074 FND_LOG.STRING(g_level_statement,
1075 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_tax_line_for_trx_line',
1076 'p_begin_index = ' || to_char(p_begin_index));
1077 FND_LOG.STRING(g_level_statement,
1078 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_tax_line_for_trx_line',
1079 'p_end_index = ' || to_char(p_end_index));
1080 END IF;
1081
1082 p_return_status := FND_API.G_RET_STS_SUCCESS;
1083 p_error_buffer := NULL;
1084
1085 --
1086 -- check if begin_index and end_index have values
1087 --
1088 IF (p_begin_index IS NULL OR p_end_index IS NULL) THEN
1089
1090 IF (g_level_statement >= g_current_runtime_level ) THEN
1091 FND_LOG.STRING(g_level_statement,
1092 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_tax_line_for_trx_line',
1093 'Begin index or End index is null' );
1094 END IF;
1095
1096 RETURN;
1097 END IF;
1098
1099 pop_mandatory_col_for_trx_line(
1100 p_begin_index,
1101 p_end_index,
1102 p_return_status,
1103 p_error_buffer);
1104
1105 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1106 RETURN;
1107 END IF;
1108
1109 chk_mandatory_col_for_trx_line(
1110 p_begin_index,
1111 p_end_index,
1112 p_return_status,
1113 p_error_buffer);
1114
1115 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1116 RETURN;
1117 END IF;
1118
1119 IF (g_level_statement >= g_current_runtime_level ) THEN
1120 FND_LOG.STRING(g_level_statement,
1121 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_tax_line_for_trx_lines',
1122 'p_return_status = ' || p_return_status);
1123 FND_LOG.STRING(g_level_statement,
1124 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_tax_line_for_trx_lines',
1125 'p_error_buffer = ' || p_error_buffer);
1126 FND_LOG.STRING(g_level_statement,
1127 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_tax_line_for_trx_lines.END',
1128 'ZX_TDS_TAX_LINES_POPU_PKG: pop_tax_line_for_trx_lines(-)');
1129 END IF;
1130
1131 EXCEPTION
1132 WHEN OTHERS THEN
1133 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1134 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1135
1136 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1137 FND_LOG.STRING(g_level_unexpected,
1138 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_tax_line_for_trx_line',
1139 p_error_buffer);
1140 END IF;
1141
1142 END pop_tax_line_for_trx_line;
1143
1144 -----------------------------------------------------------------------
1145 -- PUBLIC PROCEDURE
1146 -- pop_mandatory_col_for_trx_line
1147 --
1148 -- DESCRIPTION
1149 -- This procedure populates mandatory columns such as tax line id, who
1150 -- columns, etc for all tax lines belonged to a transaction line
1151 --
1152 -- CALLED BY
1153 -- pop_tax_line_for_trx_lines
1154
1155 PROCEDURE pop_mandatory_col_for_trx_line(
1156 p_begin_index IN BINARY_INTEGER,
1157 p_end_index IN BINARY_INTEGER,
1158 p_return_status OUT NOCOPY VARCHAR2,
1159 p_error_buffer OUT NOCOPY VARCHAR2)
1160 IS
1161
1162 BEGIN
1163
1164 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1165
1166 IF (g_level_statement >= g_current_runtime_level ) THEN
1167 FND_LOG.STRING(g_level_statement,
1168 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line.BEGIN',
1169 'ZX_TDS_TAX_LINES_POPU_PKG: pop_mandatory_col_for_trx_line(+)');
1170 FND_LOG.STRING(g_level_statement,
1171 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line',
1172 'p_begin_index = ' || to_char(p_begin_index));
1173 FND_LOG.STRING(g_level_statement,
1174 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line',
1175 'p_end_index = ' || to_char(p_end_index));
1176 END IF;
1177
1178 p_return_status := FND_API.G_RET_STS_SUCCESS;
1179
1180 --
1181 -- check if begin_index and end_index have values
1182 --
1183 IF (p_begin_index IS NULL OR p_end_index IS NULL) THEN
1184
1185 IF (g_level_statement >= g_current_runtime_level ) THEN
1186 FND_LOG.STRING(g_level_statement,
1187 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line',
1188 'Begin index or End index is null' );
1189 END IF;
1190
1191 RETURN;
1192 END IF;
1193
1194 FOR i IN p_begin_index ..p_end_index LOOP
1195
1196 IF (g_level_statement >= g_current_runtime_level ) THEN
1197 FND_LOG.STRING(g_level_statement,
1198 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line',
1199 'processing detail line index = ' || to_char(i));
1200 END IF;
1201
1202 --
1203 -- populate tax_line_id if it is null
1204 --
1205 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_line_id IS NULL THEN
1206 SELECT ZX_LINES_S.nextval
1207 INTO ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_line_id
1208 FROM dual;
1209 END IF;
1210
1211 /*************************************
1212 * -- populate assessable_value
1213 * ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).assessable_value :=
1214 * ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).taxable_amt;
1215 *
1216 * -- populate Record_Type_Code
1217 * IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Record_Type_Code IS NULL THEN
1218 * ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Record_Type_Code :=
1219 * 'ETAX_CREATED';
1220 * END IF;
1221 *
1222 * -- populate Historical_Flag
1223 * IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Historical_Flag IS NULL THEN
1224 * ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Historical_Flag := 'N';
1225 * END IF;
1226 *
1227 ***************************************/
1228
1229 -- populate who columns
1230
1231 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).created_by IS NULL THEN
1232 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).created_by :=
1233 fnd_global.user_id;
1234 END IF;
1235
1236 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).creation_date IS NULL THEN
1237 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).creation_date :=
1238 sysdate;
1239 END IF;
1240
1241 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_updated_by :=
1242 fnd_global.user_id;
1243 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_update_login :=
1244 fnd_global.login_id;
1245 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_update_date :=
1246 sysdate;
1247
1248 IF (g_level_statement >= g_current_runtime_level ) THEN
1249 FND_LOG.STRING(g_level_statement,
1250 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line',
1251 'tax_line_id = ' ||
1252 to_char(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_line_id));
1253
1254 END IF;
1255 END LOOP;
1256
1257 IF (g_level_statement >= g_current_runtime_level ) THEN
1258 FND_LOG.STRING(g_level_statement,
1259 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line',
1260 'p_return_status = ' || p_return_status);
1261 FND_LOG.STRING(g_level_statement,
1262 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line',
1263 'p_error_buffer = ' || p_error_buffer);
1264 FND_LOG.STRING(g_level_statement,
1265 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line.END',
1266 'ZX_TDS_TAX_LINES_POPU_PKG: pop_mandatory_col_for_trx_line(-)');
1267 END IF;
1268
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1272 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1273
1274 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1275 FND_LOG.STRING(g_level_unexpected,
1276 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.pop_mandatory_col_for_trx_line',
1277 p_error_buffer);
1278 END IF;
1279
1280 END pop_mandatory_col_for_trx_line;
1281 -----------------------------------------------------------------------
1282 -- PUBLIC PROCEDURE
1283 -- chk_mandatory_col_for_trx_line
1284 --
1285 -- DESCRIPTION
1286 -- This procedure ensures all mandatory columns are not NULL
1287 -- for each tax lines belonged to a transaction line
1288 --
1289 -- CALLED BY
1290 -- pop_tax_line_for_trx_lines
1291
1292
1293 PROCEDURE chk_mandatory_col_for_trx_line(
1294 p_begin_index IN BINARY_INTEGER,
1295 p_end_index IN BINARY_INTEGER,
1296 p_return_status OUT NOCOPY VARCHAR2,
1297 p_error_buffer OUT NOCOPY VARCHAR2)
1298
1299 IS
1300 BEGIN
1301
1302 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1303
1304 IF (g_level_statement >= g_current_runtime_level ) THEN
1305 FND_LOG.STRING(g_level_statement,
1306 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mandatory_col_for_trx_line.BEGIN',
1307 'ZX_TDS_TAX_LINES_POPU_PKG: chk_mandatory_col_for_trx_line(+)');
1308 FND_LOG.STRING(g_level_statement,
1309 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mandatory_col_for_trx_line',
1310 'p_begin_index = ' || to_char(p_begin_index));
1311 FND_LOG.STRING(g_level_statement,
1312 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mandatory_col_for_trx_line',
1313 'p_end_index = ' || to_char(p_end_index));
1314 END IF;
1315
1316 p_return_status := FND_API.G_RET_STS_SUCCESS;
1317
1318 --
1319 -- check if begin_index and end_index have values
1320 --
1321 IF (p_begin_index IS NULL OR p_end_index IS NULL) THEN
1322
1323 IF (g_level_statement >= g_current_runtime_level ) THEN
1324 FND_LOG.STRING(g_level_statement,
1325 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mandatory_col_for_trx_line',
1326 'Begin index or End index is null' );
1327 END IF;
1328
1329 RETURN;
1330 END IF;
1331
1332 --
1333 -- check mandatory columns for all tax lines
1334 --
1335 chk_mand_col_all_for_trx_line(
1336 p_begin_index,
1337 p_end_index,
1338 p_return_status,
1339 p_error_buffer);
1340
1341 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1342 RETURN;
1343 END IF;
1344
1345 --
1346 -- check mandatory columns applied to non
1347 -- manually entered tax lines only
1348 --
1349 chk_non_manual_line_f_trx_line(
1350 p_begin_index,
1351 p_end_index,
1352 p_return_status,
1353 p_error_buffer);
1354 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1355 RETURN;
1356 END IF;
1357
1358 IF (g_level_statement >= g_current_runtime_level ) THEN
1359 FND_LOG.STRING(g_level_statement,
1360 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mandatory_col_for_trx_line',
1361 'p_return_status = ' || p_return_status);
1362 FND_LOG.STRING(g_level_statement,
1363 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mandatory_col_for_trx_line',
1364 'p_error_buffer = ' || p_error_buffer);
1365 FND_LOG.STRING(g_level_statement,
1366 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mandatory_col_for_trx_line.END',
1367 'ZX_TDS_TAX_LINES_POPU_PKG: chk_mandatory_col_for_trx_line(-)');
1368 END IF;
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1373 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1374
1375 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1376 FND_LOG.STRING(g_level_unexpected,
1377 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mandatory_col_for_trx_line',
1378 p_error_buffer);
1379 END IF;
1380
1381 END chk_mandatory_col_for_trx_line;
1382 -----------------------------------------------------------------------
1383 -- PUBLIC PROCEDURE
1384 -- chk_mand_col_all_for_trx_line
1385 --
1386 -- DESCRIPTION
1387 -- This procedure ensures all mandatory columns of a manual or non
1388 -- manual tax line are not NULL for each tax line belonged to
1389 -- a transaction line
1390 --
1391 -- CALLED BY
1392 -- chk_mandatory_col_for_trx_line
1393
1394 PROCEDURE chk_mand_col_all_for_trx_line(
1395 p_begin_index IN BINARY_INTEGER,
1396 p_end_index IN BINARY_INTEGER,
1397 p_return_status OUT NOCOPY VARCHAR2,
1398 p_error_buffer OUT NOCOPY VARCHAR2)
1399 IS
1400 BEGIN
1401
1402 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1403
1404 IF (g_level_statement >= g_current_runtime_level ) THEN
1405 FND_LOG.STRING(g_level_statement,
1406 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line.BEGIN',
1407 'ZX_TDS_TAX_LINES_POPU_PKG: chk_mand_col_all_for_trx_line(+)');
1408 FND_LOG.STRING(g_level_statement,
1409 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line',
1410 'p_begin_index = ' || to_char(p_begin_index));
1411 FND_LOG.STRING(g_level_statement,
1412 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line',
1413 'p_end_index = ' || to_char(p_end_index));
1414 END IF;
1415
1416 p_return_status := FND_API.G_RET_STS_SUCCESS;
1417
1418 --
1419 -- check if begin_index and end_index have values
1420 --
1421 IF (p_begin_index IS NULL OR p_end_index IS NULL) THEN
1422
1423 IF (g_level_statement >= g_current_runtime_level ) THEN
1424 FND_LOG.STRING(g_level_statement,
1425 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line',
1426 'Begin index or End index is null' );
1427 END IF;
1428
1429 RETURN;
1430 END IF;
1431
1432 FOR i IN p_begin_index ..p_end_index LOOP
1433
1434 IF (g_level_statement >= g_current_runtime_level ) THEN
1435 FND_LOG.STRING(g_level_statement,
1436 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line',
1437 'processing detail line index = ' || to_char(i));
1438 END IF;
1439
1440 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).application_id IS NULL THEN
1441 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1442 p_error_buffer := 'application_id can not be NULL';
1443 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).entity_code IS NULL THEN
1444 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1445 p_error_buffer := 'entity_code can not be NULL';
1446 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).event_class_code IS NULL THEN
1447 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1448 p_error_buffer := 'event_class_code can not be NULL';
1449 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).event_type_code IS NULL THEN
1450 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1451 p_error_buffer := 'event_type_code can not be NULL';
1452 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_id IS NULL THEN
1453 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1454 p_error_buffer := 'trx_id can not be NULL';
1455 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id IS NULL THEN
1456 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457 p_error_buffer := 'trx_line_id can not be NULL';
1458 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type IS NULL THEN
1459 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1460 p_error_buffer := 'trx_level_type can not be NULL';
1461 /*
1462 -- Bug#4572001- content_owner_id can be NULL
1463 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).content_owner_id IS NULL THEN
1464 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1465 p_error_buffer := 'content_owner_id can not be NULL';
1466 */
1467 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_regime_id IS NULL THEN
1468 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469 p_error_buffer := 'tax_regime_id can not be NULL';
1470 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_regime_code IS NULL THEN
1471 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1472 p_error_buffer := 'tax_regime_code can not be NULL';
1473 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_id IS NULL THEN
1474 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1475 p_error_buffer := 'tax_id can not be NULL';
1476 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax IS NULL THEN
1477 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1478 p_error_buffer := 'tax can not be NULL';
1479 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_status_id IS NULL THEN
1480 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481 p_error_buffer := 'tax_status_id can not be NULL';
1482 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_status_code IS NULL THEN
1483 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1484 p_error_buffer := 'tax_status_code can not be NULL';
1485 END IF;
1486
1487 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1488 EXIT;
1489 END IF;
1490
1491 END LOOP;
1492
1493 IF (g_level_statement >= g_current_runtime_level ) THEN
1494 FND_LOG.STRING(g_level_statement,
1495 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line',
1496 'p_return_status = ' || p_return_status);
1497 FND_LOG.STRING(g_level_statement,
1498 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line',
1499 'p_error_buffer = ' || p_error_buffer);
1500 FND_LOG.STRING(g_level_statement,
1501 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line.END',
1502 'ZX_TDS_TAX_LINES_POPU_PKG: chk_mand_col_all_for_trx_line(-)');
1503 END IF;
1504
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1508 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1509
1510 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1511 FND_LOG.STRING(g_level_unexpected,
1512 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_mand_col_all_for_trx_line',
1513 p_error_buffer);
1514 END IF;
1515
1516 END chk_mand_col_all_for_trx_line;
1517
1518 -----------------------------------------------------------------------
1519 -- PUBLIC PROCEDURE
1520 -- chk_non_manual_line_f_trx_line
1521 --
1522 -- DESCRIPTION
1523 -- This procedure ensures all mandatory columns of a non manually
1524 -- entered tax line are not NULL for each tax lines belonged to
1525 -- a transaction line
1526 --
1527 -- CALLED BY
1528 -- chk_mandatory_col_for_trx_line
1529
1530
1531 PROCEDURE chk_non_manual_line_f_trx_line(
1532 p_begin_index IN BINARY_INTEGER,
1533 p_end_index IN BINARY_INTEGER,
1534 p_return_status OUT NOCOPY VARCHAR2,
1535 p_error_buffer OUT NOCOPY VARCHAR2)
1536 IS
1537 BEGIN
1538
1539 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1540
1541 IF (g_level_statement >= g_current_runtime_level ) THEN
1542 FND_LOG.STRING(g_level_statement,
1543 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line.BEGIN',
1544 'ZX_TDS_TAX_LINES_POPU_PKG: chk_non_manual_line_f_trx_line(+)');
1545 FND_LOG.STRING(g_level_statement,
1546 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line',
1547 'p_begin_index = ' || to_char(p_begin_index));
1548 FND_LOG.STRING(g_level_statement,
1549 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line',
1550 'p_end_index = ' || to_char(p_end_index));
1551 END IF;
1552
1553 p_return_status := FND_API.G_RET_STS_SUCCESS;
1554
1555 --
1556 -- check if begin_index and end_index have values
1557 --
1558 IF (p_begin_index IS NULL OR p_end_index IS NULL) THEN
1559
1560 IF (g_level_statement >= g_current_runtime_level ) THEN
1561 FND_LOG.STRING(g_level_statement,
1562 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line',
1563 'Begin index or End index is null' );
1564 END IF;
1565
1566 RETURN;
1567 END IF;
1568
1569 FOR i IN p_begin_index ..p_end_index LOOP
1570
1571 IF (g_level_statement >= g_current_runtime_level ) THEN
1572 FND_LOG.STRING(g_level_statement,
1573 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line',
1574 'processing detail line index = ' || to_char(i));
1575 END IF;
1576
1577 IF (NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Manually_Entered_Flag,'N') = 'N') THEN
1578
1579 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_date IS NULL THEN
1580 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1581 p_error_buffer := 'trx_date can not be NULL';
1582 -- ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_date IS NULL THEN
1583 -- p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1584 -- p_error_buffer := 'trx_line_date can not be NULL';
1585 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).ledger_id IS NULL THEN
1586 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1587 p_error_buffer := 'ledger_id can not be NULL';
1588
1589 /* comment out only for AP uptake testing purpose. Change to reversed later. chku
1590 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).establishment_id IS NULL THEN
1591 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1592 p_error_buffer := 'establishment_id can not be NULL';
1593 */
1594 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).legal_entity_id IS NULL THEN
1595 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1596 p_error_buffer := 'legal_entity_id can not be NULL';
1597 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_id IS NULL THEN
1598 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1599 p_error_buffer := 'tax_rate_id can not be NULL';
1600 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code IS NULL THEN
1601 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1602 p_error_buffer := 'tax_rate_code can not be NULL';
1603 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate IS NULL THEN
1604 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1605 p_error_buffer := 'tax_rate can not be NULL';
1606
1607 /* comment out only for AP uptake testing purpose. Change to reversed later. chku
1608 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_apportionment_line_number IS NULL THEN
1609 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1610 p_error_buffer := 'tax_apportionment_line_number can not be NULL';
1611 */
1612 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_date IS NULL THEN
1613 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1614 p_error_buffer := 'tax_date can not be NULL';
1615 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date IS NULL THEN
1616 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1617 p_error_buffer := 'tax_determine_date can not be NULL';
1618 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_point_date IS NULL THEN
1619 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1620 p_error_buffer := 'tax_point_date can not be NULL';
1621 END IF;
1622
1623 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1624 EXIT;
1625 END IF;
1626 END IF; -- non manual tax line
1627 END LOOP;
1628
1629 IF (g_level_statement >= g_current_runtime_level ) THEN
1630 FND_LOG.STRING(g_level_statement,
1631 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line',
1632 'p_return_status = ' || p_return_status);
1633 FND_LOG.STRING(g_level_statement,
1634 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line',
1635 'p_error_buffer = ' || p_error_buffer);
1636 FND_LOG.STRING(g_level_statement,
1637 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line.END',
1638 'ZX_TDS_TAX_LINES_POPU_PKG: chk_non_manual_line_f_trx_line(-)');
1639 END IF;
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1644 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1645
1646 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1647 FND_LOG.STRING(g_level_unexpected,
1648 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.chk_non_manual_line_f_trx_line',
1649 p_error_buffer);
1650 END IF;
1651
1652 END chk_non_manual_line_f_trx_line;
1653
1654
1655 -----------------------------------------------------------------------
1656 -- PUBLIC PROCEDURE
1657 -- process_cancel_tax_lines
1658 --
1659 -- DESCRIPTION
1660 -- This procedure brings all canceled tax lines from zx_lines into
1661 -- detail tax lines global temp table and marks them as cancel
1662 --
1663 -- CALLED BY
1664 -- ZX_TDS_TAX_LINES_DETM_PKG
1665
1666 PROCEDURE process_cancel_tax_lines(
1667 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
1668 p_return_status OUT NOCOPY VARCHAR2,
1669 p_error_buffer OUT NOCOPY VARCHAR2)
1670
1671 IS
1672 BEGIN
1673
1674 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1675
1676 IF (g_level_procedure >= g_current_runtime_level ) THEN
1677 FND_LOG.STRING(g_level_procedure,
1678 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_tax_lines.BEGIN',
1679 'ZX_TDS_TAX_LINES_POPU_PKG: process_cancel_tax_lines(+)');
1680 END IF;
1681
1682 p_return_status := FND_API.G_RET_STS_SUCCESS;
1683
1684 INSERT INTO zx_detail_tax_lines_gt
1685 ( tax_line_id,
1686 internal_organization_id,
1687 application_id,
1688 entity_code,
1689 event_class_code,
1690 event_type_code,
1691 trx_id,
1692 trx_line_id,
1693 trx_level_type,
1694 trx_line_number,
1695 doc_event_status,
1696 -- line_event_status,
1697 tax_event_class_code,
1698 tax_event_type_code,
1699 tax_line_number,
1700 content_owner_id,
1701 tax_regime_id,
1702 tax_regime_code,
1703 tax_id,
1704 tax,
1705 tax_status_id,
1706 tax_status_code,
1707 tax_rate_id,
1708 tax_rate_code,
1709 tax_rate,
1710 tax_apportionment_line_number,
1711 trx_id_level2,
1712 trx_id_level3,
1713 trx_id_level4,
1714 trx_id_level5,
1715 trx_id_level6,
1716 trx_user_key_level1,
1717 trx_user_key_level2,
1718 trx_user_key_level3,
1719 trx_user_key_level4,
1720 trx_user_key_level5,
1721 trx_user_key_level6,
1722 mrc_tax_line_flag,
1723 ledger_id,
1724 establishment_id,
1725 legal_entity_id,
1726 legal_entity_tax_reg_number,
1727 hq_estb_reg_number,
1728 hq_estb_party_tax_prof_id,
1729 currency_conversion_date,
1730 currency_conversion_type,
1731 currency_conversion_rate,
1732 tax_currency_conversion_date,
1733 tax_currency_conversion_type,
1734 tax_currency_conversion_rate,
1735 trx_currency_code,
1736 minimum_accountable_unit,
1737 precision,
1738 trx_number,
1739 trx_date,
1740 unit_price,
1741 line_amt,
1742 trx_line_quantity,
1743 tax_base_modifier_rate,
1744 ref_doc_application_id,
1745 ref_doc_entity_code,
1746 ref_doc_event_class_code,
1747 ref_doc_trx_id,
1748 ref_doc_line_id,
1749 ref_doc_line_quantity,
1750 other_doc_line_amt,
1751 other_doc_line_tax_amt,
1752 other_doc_line_taxable_amt,
1753 unrounded_taxable_amt,
1754 unrounded_tax_amt,
1755 related_doc_application_id,
1756 related_doc_entity_code,
1757 related_doc_event_class_code,
1758 related_doc_trx_id,
1759 related_doc_number,
1760 related_doc_date,
1761 applied_from_application_id,
1762 applied_from_event_class_code,
1763 applied_from_entity_code,
1764 applied_from_trx_id,
1765 applied_from_line_id,
1766 applied_from_trx_level_type,
1767 applied_from_trx_number,
1768 adjusted_doc_application_id,
1769 adjusted_doc_entity_code,
1770 adjusted_doc_event_class_code,
1771 adjusted_doc_trx_id,
1772 adjusted_doc_line_id,
1773 adjusted_doc_trx_level_type,
1774 adjusted_doc_number,
1775 adjusted_doc_date,
1776 applied_to_application_id,
1777 applied_to_event_class_code,
1778 applied_to_entity_code,
1779 applied_to_trx_id,
1780 applied_to_line_id,
1781 applied_to_trx_number,
1782 summary_tax_line_id,
1783 offset_link_to_tax_line_id,
1784 offset_flag,
1785 process_for_recovery_flag,
1786 tax_jurisdiction_id,
1787 tax_jurisdiction_code,
1788 place_of_supply,
1789 place_of_supply_type_code,
1790 place_of_supply_result_id,
1791 tax_date_rule_id,
1792 tax_date,
1793 tax_determine_date,
1794 tax_point_date,
1795 trx_line_date,
1796 tax_type_code,
1797 tax_code,
1798 tax_registration_id,
1799 tax_registration_number,
1800 registration_party_type,
1801 rounding_level_code,
1802 rounding_rule_code,
1803 rounding_lvl_party_tax_prof_id,
1804 rounding_lvl_party_type,
1805 compounding_tax_flag,
1806 orig_tax_status_id,
1807 orig_tax_status_code,
1808 orig_tax_rate_id,
1809 orig_tax_rate_code,
1810 orig_tax_rate,
1811 orig_tax_jurisdiction_id,
1812 orig_tax_jurisdiction_code,
1813 orig_tax_amt_included_flag,
1814 orig_self_assessed_flag,
1815 tax_currency_code,
1816 tax_amt,
1817 tax_amt_tax_curr,
1818 tax_amt_funcl_curr,
1819 taxable_amt,
1820 taxable_amt_tax_curr,
1821 taxable_amt_funcl_curr,
1822 orig_taxable_amt,
1823 orig_taxable_amt_tax_curr,
1824 cal_tax_amt,
1825 cal_tax_amt_tax_curr,
1826 cal_tax_amt_funcl_curr,
1827 orig_tax_amt,
1828 orig_tax_amt_tax_curr,
1829 rec_tax_amt,
1830 rec_tax_amt_tax_curr,
1831 rec_tax_amt_funcl_curr,
1832 nrec_tax_amt,
1833 nrec_tax_amt_tax_curr,
1834 nrec_tax_amt_funcl_curr,
1835 tax_exemption_id,
1836 tax_rate_before_exemption,
1837 tax_rate_name_before_exemption,
1838 exempt_rate_modifier,
1839 exempt_certificate_number,
1840 exempt_reason,
1841 exempt_reason_code,
1842 tax_exception_id,
1843 tax_rate_before_exception,
1844 tax_rate_name_before_exception,
1845 exception_rate,
1846 tax_apportionment_flag,
1847 historical_flag,
1848 taxable_basis_formula,
1849 tax_calculation_formula,
1850 cancel_flag,
1851 purge_flag,
1852 delete_flag,
1853 tax_amt_included_flag,
1854 self_assessed_flag,
1855 overridden_flag,
1856 manually_entered_flag,
1857 freeze_until_overridden_flag,
1858 copied_from_other_doc_flag,
1859 recalc_required_flag,
1860 settlement_flag,
1861 item_dist_changed_flag,
1862 associated_child_frozen_flag,
1863 tax_only_line_flag,
1864 compounding_dep_tax_flag,
1865 last_manual_entry,
1866 tax_provider_id,
1867 record_type_code,
1868 reporting_period_id,
1869 legal_message_appl_2,
1870 legal_message_status,
1871 legal_message_rate,
1872 legal_message_basis,
1873 legal_message_calc,
1874 legal_message_threshold,
1875 legal_message_pos,
1876 legal_message_trn,
1877 legal_message_exmpt,
1878 legal_message_excpt,
1879 tax_regime_template_id,
1880 tax_applicability_result_id,
1881 direct_rate_result_id,
1882 status_result_id,
1883 rate_result_id,
1884 basis_result_id,
1885 thresh_result_id,
1886 calc_result_id,
1887 tax_reg_num_det_result_id,
1888 eval_exmpt_result_id,
1889 eval_excpt_result_id,
1890 enforce_from_natural_acct_flag,
1891 tax_hold_code,
1892 tax_hold_released_code,
1893 prd_total_tax_amt,
1894 prd_total_tax_amt_tax_curr,
1895 prd_total_tax_amt_funcl_curr,
1896 internal_org_location_id,
1897 attribute_category,
1898 attribute1,
1899 attribute2,
1900 attribute3,
1901 attribute4,
1902 attribute5,
1903 attribute6,
1904 attribute7,
1905 attribute8,
1906 attribute9,
1907 attribute10,
1908 attribute11,
1909 attribute12,
1910 attribute13,
1911 attribute14,
1912 attribute15,
1913 global_attribute_category,
1914 global_attribute1,
1915 global_attribute2,
1916 global_attribute3,
1917 global_attribute4,
1918 global_attribute5,
1919 global_attribute6,
1920 global_attribute7,
1921 global_attribute8,
1922 global_attribute9,
1923 global_attribute10,
1924 global_attribute11,
1925 global_attribute12,
1926 global_attribute13,
1927 global_attribute14,
1928 global_attribute15,
1929 numeric1,
1930 numeric2,
1931 numeric3,
1932 numeric4,
1933 numeric5,
1934 numeric6,
1935 numeric7,
1936 numeric8,
1937 numeric9,
1938 numeric10,
1939 char1,
1940 char2,
1941 char3,
1942 char4,
1943 char5,
1944 char6,
1945 char7,
1946 char8,
1947 char9,
1948 char10,
1949 date1,
1950 date2,
1951 date3,
1952 date4,
1953 date5,
1954 date6,
1955 date7,
1956 date8,
1957 date9,
1958 date10,
1959 tax_rate_type,
1960 created_by,
1961 creation_date,
1962 last_updated_by,
1963 last_update_date,
1964 last_update_login,
1965 line_assessable_value,
1966 legal_justification_text1,
1967 legal_justification_text2,
1968 legal_justification_text3,
1969 reporting_currency_code,
1970 trx_line_index,
1971 offset_tax_rate_code,
1972 proration_code,
1973 other_doc_source,
1974 reporting_only_flag,
1975 ctrl_total_line_tx_amt,
1976 sync_with_prvdr_flag,
1977 interface_entity_code,
1978 interface_tax_line_id,
1979 taxing_juris_geography_id,
1980 adjusted_doc_tax_line_id,
1981 object_version_number,
1982 legal_reporting_status,
1983 account_source_tax_rate_id
1984 )
1985 (SELECT
1986 L.tax_line_id,
1987 L.internal_organization_id,
1988 L.application_id,
1989 L.entity_code,
1990 L.event_class_code,
1991 L.event_type_code,
1992 L.trx_id,
1993 L.trx_line_id,
1994 L.trx_level_type,
1995 L.trx_line_number,
1996 L.doc_event_status,
1997 -- L.line_event_status,
1998 L.tax_event_class_code,
1999 L.tax_event_type_code,
2000 L.tax_line_number,
2001 L.content_owner_id,
2002 L.tax_regime_id,
2003 L.tax_regime_code,
2004 L.tax_id,
2005 L.tax,
2006 L.tax_status_id,
2007 L.tax_status_code,
2008 L.tax_rate_id,
2009 L.tax_rate_code,
2010 L.tax_rate,
2011 L.tax_apportionment_line_number,
2012 L.trx_id_level2,
2013 L.trx_id_level3,
2014 L.trx_id_level4,
2015 L.trx_id_level5,
2016 L.trx_id_level6,
2017 L.trx_user_key_level1,
2018 L.trx_user_key_level2,
2019 L.trx_user_key_level3,
2020 L.trx_user_key_level4,
2021 L.trx_user_key_level5,
2022 L.trx_user_key_level6,
2023 L.mrc_tax_line_flag,
2024 G.ledger_id,
2025 G.establishment_id,
2026 G.legal_entity_id,
2027 L.legal_entity_tax_reg_number,
2028 L.hq_estb_reg_number,
2029 L.hq_estb_party_tax_prof_id,
2030 G.currency_conversion_date,
2031 G.currency_conversion_type,
2032 G.currency_conversion_rate,
2033 L.tax_currency_conversion_date,
2034 L.tax_currency_conversion_type,
2035 L.tax_currency_conversion_rate,
2036 L.trx_currency_code,
2037 L.minimum_accountable_unit,
2038 L.precision,
2039 G.trx_number,
2040 G.trx_date,
2041 L.unit_price,
2042 L.line_amt,
2043 L.trx_line_quantity,
2044 L.tax_base_modifier_rate,
2045 L.ref_doc_application_id,
2046 L.ref_doc_entity_code,
2047 L.ref_doc_event_class_code,
2048 L.ref_doc_trx_id,
2049 L.ref_doc_line_id,
2050 L.ref_doc_line_quantity,
2051 L.other_doc_line_amt,
2052 L.other_doc_line_tax_amt,
2053 L.other_doc_line_taxable_amt,
2054 0,
2055 0, -- L.unrounded_tax_amt,
2056 L.related_doc_application_id,
2057 L.related_doc_entity_code,
2058 L.related_doc_event_class_code,
2059 L.related_doc_trx_id,
2060 L.related_doc_number,
2061 L.related_doc_date,
2062 L.applied_from_application_id,
2063 L.applied_from_event_class_code,
2064 L.applied_from_entity_code,
2065 L.applied_from_trx_id,
2066 L.applied_from_line_id,
2067 L.applied_from_trx_level_type,
2068 L.applied_from_trx_number,
2069 L.adjusted_doc_application_id,
2070 L.adjusted_doc_entity_code,
2071 L.adjusted_doc_event_class_code,
2072 L.adjusted_doc_trx_id,
2073 L.adjusted_doc_line_id,
2074 L.adjusted_doc_trx_level_type,
2075 L.adjusted_doc_number,
2076 L.adjusted_doc_date,
2077 L.applied_to_application_id,
2078 L.applied_to_event_class_code,
2079 L.applied_to_entity_code,
2080 L.applied_to_trx_id,
2081 L.applied_to_line_id,
2082 L.applied_to_trx_number,
2083 L.summary_tax_line_id,
2084 L.offset_link_to_tax_line_id,
2085 L.offset_flag,
2086 DECODE(L.historical_flag,'Y','Y',L.process_for_recovery_flag),
2087 L.tax_jurisdiction_id,
2088 L.tax_jurisdiction_code,
2089 L.place_of_supply,
2090 L.place_of_supply_type_code,
2091 L.place_of_supply_result_id,
2092 L.tax_date_rule_id,
2093 L.tax_date,
2094 L.tax_determine_date,
2095 L.tax_point_date,
2096 L.trx_line_date,
2097 L.tax_type_code,
2098 L.tax_code,
2099 L.tax_registration_id,
2100 L.tax_registration_number,
2101 L.registration_party_type,
2102 L.rounding_level_code,
2103 L.rounding_rule_code,
2104 L.rounding_lvl_party_tax_prof_id,
2105 L.rounding_lvl_party_type,
2106 L.compounding_tax_flag,
2107 L.orig_tax_status_id,
2108 L.orig_tax_status_code,
2109 L.orig_tax_rate_id,
2110 L.orig_tax_rate_code,
2111 L.orig_tax_rate,
2112 L.orig_tax_jurisdiction_id,
2113 L.orig_tax_jurisdiction_code,
2114 L.orig_tax_amt_included_flag,
2115 L.orig_self_assessed_flag,
2116 L.tax_currency_code,
2117 0, -- L.tax_amt,
2118 0, -- L.tax_amt_tax_curr,
2119 0, -- L.tax_amt_funcl_curr,
2120 0, -- L.taxable_amt
2121 0, -- L.taxable_amt_tax_curr,
2122 0, -- L.taxable_amt_funcl_curr,
2123 DECODE(L.orig_taxable_amt,NULL,L.taxable_amt,L.orig_taxable_amt),
2124 --orig_taxable_amt
2125 DECODE(L.orig_taxable_amt_tax_curr,NULL,L.taxable_amt_tax_curr,L.orig_taxable_amt_tax_curr),
2126 -- orig_taxable_amt_tax_curr
2127 0 , -- L.cal_tax_amt,
2128 0 , -- L.cal_tax_amt_tax_curr,
2129 0 , -- L.cal_tax_amt_funcl_curr,
2130 DECODE(L.orig_tax_amt,NULL,L.tax_amt,L.orig_tax_amt),
2131 --L.orig_tax_amt
2132 DECODE(L.orig_tax_amt_tax_curr,NULL, L.tax_amt_tax_curr,
2133 L.orig_tax_amt_tax_curr), -- L.orig_tax_amt_tax_curr
2134 0, --L.rec_tax_amt,
2135 0, --L.rec_tax_amt_tax_curr,
2136 0, --L.rec_tax_amt_funcl_curr,
2137 0, -- L.nrec_tax_amt,
2138 0, --L.nrec_tax_amt_tax_curr,
2139 0, -- L.nrec_tax_amt_funcl_curr,
2140 L.tax_exemption_id,
2141 L.tax_rate_before_exemption,
2142 L.tax_rate_name_before_exemption,
2143 L.exempt_rate_modifier,
2144 L.exempt_certificate_number,
2145 L.exempt_reason,
2146 L.exempt_reason_code,
2147 L.tax_exception_id,
2148 L.tax_rate_before_exception,
2149 L.tax_rate_name_before_exception,
2150 L.exception_rate,
2151 L.tax_apportionment_flag,
2152 L.historical_flag,
2153 L.taxable_basis_formula,
2154 L.tax_calculation_formula,
2155 L.cancel_flag,
2156 L.purge_flag,
2157 L.delete_flag,
2158 L.tax_amt_included_flag,
2159 L.self_assessed_flag,
2160 L.overridden_flag,
2161 L.manually_entered_flag,
2162 L.freeze_until_overridden_flag,
2163 L.copied_from_other_doc_flag,
2164 L.recalc_required_flag,
2165 L.settlement_flag,
2166 L.item_dist_changed_flag,
2167 L.associated_child_frozen_flag,
2168 L.tax_only_line_flag,
2169 L.compounding_dep_tax_flag,
2170 L.last_manual_entry,
2171 L.tax_provider_id,
2172 L.record_type_code,
2173 L.reporting_period_id,
2174 L.legal_message_appl_2,
2175 L.legal_message_status,
2176 L.legal_message_rate,
2177 L.legal_message_basis,
2178 L.legal_message_calc,
2179 L.legal_message_threshold,
2180 L.legal_message_pos,
2181 L.legal_message_trn,
2182 L.legal_message_exmpt,
2183 L.legal_message_excpt,
2184 L.tax_regime_template_id,
2185 L.tax_applicability_result_id,
2186 L.direct_rate_result_id,
2187 L.status_result_id,
2188 L.rate_result_id,
2189 L.basis_result_id,
2190 L.thresh_result_id,
2191 L.calc_result_id,
2192 L.tax_reg_num_det_result_id,
2193 L.eval_exmpt_result_id,
2194 L.eval_excpt_result_id,
2195 L.enforce_from_natural_acct_flag,
2196 NULL, --L.tax_hold_code,
2197 NULL, -- L.tax_hold_released_code,
2198 NULL, --L.prd_total_tax_amt,
2199 NULL, --L.prd_total_tax_amt_tax_curr,
2200 NULL, --L.prd_total_tax_amt_funcl_curr,
2201 L.internal_org_location_id,
2202 L.attribute_category,
2203 L.attribute1,
2204 L.attribute2,
2205 L.attribute3,
2206 L.attribute4,
2207 L.attribute5,
2208 L.attribute6,
2209 L.attribute7,
2210 L.attribute8,
2211 L.attribute9,
2212 L.attribute10,
2213 L.attribute11,
2214 L.attribute12,
2215 L.attribute13,
2216 L.attribute14,
2217 L.attribute15,
2218 L.global_attribute_category,
2219 L.global_attribute1,
2220 L.global_attribute2,
2221 L.global_attribute3,
2222 L.global_attribute4,
2223 L.global_attribute5,
2224 L.global_attribute6,
2225 L.global_attribute7,
2226 L.global_attribute8,
2227 L.global_attribute9,
2228 L.global_attribute10,
2229 L.global_attribute11,
2230 L.global_attribute12,
2231 L.global_attribute13,
2232 L.global_attribute14,
2233 L.global_attribute15,
2234 L.numeric1,
2235 L.numeric2,
2236 L.numeric3,
2237 L.numeric4,
2238 L.numeric5,
2239 L.numeric6,
2240 L.numeric7,
2241 L.numeric8,
2242 L.numeric9,
2243 L.numeric10,
2244 L.char1,
2245 L.char2,
2246 L.char3,
2247 L.char4,
2248 L.char5,
2249 L.char6,
2250 L.char7,
2251 L.char8,
2252 L.char9,
2253 L.char10,
2254 L.date1,
2255 L.date2,
2256 L.date3,
2257 L.date4,
2258 L.date5,
2259 L.date6,
2260 L.date7,
2261 L.date8,
2262 L.date9,
2263 L.date10,
2264 L.tax_rate_type,
2265 L.created_by,
2266 L.creation_date,
2267 L.last_updated_by,
2268 L.last_update_date,
2269 L.last_update_login,
2270 L.line_assessable_value,
2271 L.legal_justification_text1,
2272 L.legal_justification_text2,
2273 L.legal_justification_text3,
2274 L.reporting_currency_code,
2275 NULL, --L.trx_line_index,
2276 L.offset_tax_rate_code,
2277 NULL, -- L.proration_code,
2278 NULL, --L.other_doc_source,
2279 L.reporting_only_flag,
2280 L.ctrl_total_line_tx_amt,
2281 L.sync_with_prvdr_flag,
2282 L.interface_entity_code,
2283 L.interface_tax_line_id,
2284 L.taxing_juris_geography_id,
2285 L.adjusted_doc_tax_line_id,
2286 1,
2287 L.legal_reporting_status,
2288 L.account_source_tax_rate_id
2289 FROM ZX_LINES L,
2290 zx_lines_det_factors G
2291 WHERE G.application_id = p_event_class_rec.application_id AND
2292 G.entity_code = p_event_class_rec.entity_code AND
2293 G.event_class_code = p_event_class_rec.event_class_code AND
2294 -- G.trx_id = p_event_class_rec.trx_id AND
2295 G.event_id = p_event_class_rec.event_id AND
2296 L.trx_id = G.trx_id AND
2297 L.trx_line_id = G.trx_line_id AND
2298 L.trx_level_type = G.trx_level_type AND
2299 L.event_class_code = G.event_class_code
2300 AND L.entity_code = G.entity_code
2301 AND L.application_id = G.application_id
2302 -- AND L.subscriber_id = G.subscriber_id
2303 AND G.line_level_action NOT IN ('SYNCHRONIZE', 'CANCEL', 'NO_CHANGE','DISCARD')
2304 AND L.Cancel_Flag = 'Y'
2305 -- AND (L.tax_provider_id IS NOT NULL OR L.Cancel_Flag = 'Y')
2306 AND NOT EXISTS
2307 (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
2308 1
2309 FROM zx_detail_tax_lines_gt gt
2310 WHERE gt.application_id = L.application_id
2311 AND gt.entity_code = L.entity_code
2312 AND gt.event_class_code = L.event_class_code
2313 AND gt.trx_id = L.trx_id
2314 AND gt.trx_line_id = L.trx_line_id
2315 AND gt.trx_level_type = L.trx_level_type
2316 AND gt.tax_regime_code = L.tax_regime_code
2317 AND gt.tax = L.tax
2318 AND NVL(gt.tax_apportionment_line_number, -999999) =
2319 NVL(L.tax_apportionment_line_number, -999999)
2320 )
2321 );
2322
2323 IF (g_level_statement >= g_current_runtime_level ) THEN
2324 FND_LOG.STRING(g_level_statement,
2325 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_tax_lines',
2326 'Number of Rows Inserted: ' || SQL%ROWCOUNT);
2327 END IF;
2328
2329 IF (g_level_procedure >= g_current_runtime_level ) THEN
2330 FND_LOG.STRING(g_level_procedure,
2331 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_tax_lines',
2332 'p_return_status = ' || p_return_status);
2333 FND_LOG.STRING(g_level_procedure,
2334 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_tax_lines',
2335 'p_error_buffer = ' || p_error_buffer);
2336 FND_LOG.STRING(g_level_procedure,
2337 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_tax_lines.END',
2338 'ZX_TDS_TAX_LINES_POPU_PKG: process_cancel_tax_lines(-)');
2339 END IF;
2340
2341 EXCEPTION
2342 WHEN OTHERS THEN
2343 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2344 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2345
2346 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2347 FND_LOG.STRING(g_level_unexpected,
2348 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_tax_lines',
2349 p_error_buffer);
2350 END IF;
2351
2352 END process_cancel_tax_lines;
2353
2354 -----------------------------------------------------------------------
2355 -- PUBLIC PROCEDURE
2356 -- process_frozen_tax_lines
2357 --
2358 -- DESCRIPTION
2359 -- This procedure brings all frozen tax lines which found not
2360 -- applicable into detail tax lines global temp table and
2361 -- mark them as cancel
2362 --
2363 -- CALLED BY
2364 -- ZX_TDS_TAX_LINES_DETM_PKG
2365
2366 PROCEDURE process_frozen_tax_lines(
2367 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
2368 p_return_status OUT NOCOPY VARCHAR2,
2369 p_error_buffer OUT NOCOPY VARCHAR2)
2370
2371 IS
2372 BEGIN
2373
2374 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2375
2376 IF (g_level_procedure >= g_current_runtime_level ) THEN
2377 FND_LOG.STRING(g_level_procedure,
2378 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines.BEGIN',
2379 'ZX_TDS_TAX_LINES_POPU_PKG: process_frozen_tax_lines(+)');
2380 END IF;
2381
2382 p_return_status := FND_API.G_RET_STS_SUCCESS;
2383
2384 -- bug 6906427: 1. Add code to handle OVERRIDE_TAX
2385 -- 2. Make changes for UPDATE
2386 --
2387 IF p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX' THEN
2388
2389 -- Fetch back canceled tax lines with frozen tax distributions
2390 --
2391
2392 INSERT INTO zx_detail_tax_lines_gt
2393 ( tax_line_id,
2394 internal_organization_id,
2395 application_id,
2396 entity_code,
2397 event_class_code,
2398 event_type_code,
2399 trx_id,
2400 trx_line_id,
2401 trx_level_type,
2402 trx_line_number,
2403 doc_event_status,
2404 tax_event_class_code,
2405 tax_event_type_code,
2406 tax_line_number,
2407 content_owner_id,
2408 tax_regime_id,
2409 tax_regime_code,
2410 tax_id,
2411 tax,
2412 tax_status_id,
2413 tax_status_code,
2414 tax_rate_id,
2415 tax_rate_code,
2416 tax_rate,
2417 tax_apportionment_line_number,
2418 trx_id_level2,
2419 trx_id_level3,
2420 trx_id_level4,
2421 trx_id_level5,
2422 trx_id_level6,
2423 trx_user_key_level1,
2424 trx_user_key_level2,
2425 trx_user_key_level3,
2426 trx_user_key_level4,
2427 trx_user_key_level5,
2428 trx_user_key_level6,
2429 mrc_tax_line_flag,
2430 ledger_id,
2431 establishment_id,
2432 legal_entity_id,
2433 legal_entity_tax_reg_number,
2434 hq_estb_reg_number,
2435 hq_estb_party_tax_prof_id,
2436 currency_conversion_date,
2437 currency_conversion_type,
2438 currency_conversion_rate,
2439 tax_currency_conversion_date,
2440 tax_currency_conversion_type,
2441 tax_currency_conversion_rate,
2442 trx_currency_code,
2443 minimum_accountable_unit,
2444 precision,
2445 trx_number,
2446 trx_date,
2447 unit_price,
2448 line_amt,
2449 trx_line_quantity,
2450 tax_base_modifier_rate,
2451 ref_doc_application_id,
2452 ref_doc_entity_code,
2453 ref_doc_event_class_code,
2454 ref_doc_trx_id,
2455 ref_doc_line_id,
2456 ref_doc_line_quantity,
2457 other_doc_line_amt,
2458 other_doc_line_tax_amt,
2459 other_doc_line_taxable_amt,
2460 unrounded_taxable_amt,
2461 unrounded_tax_amt,
2462 related_doc_application_id,
2463 related_doc_entity_code,
2464 related_doc_event_class_code,
2465 related_doc_trx_id,
2466 related_doc_number,
2467 related_doc_date,
2468 applied_from_application_id,
2469 applied_from_event_class_code,
2470 applied_from_entity_code,
2471 applied_from_trx_id,
2472 applied_from_line_id,
2473 applied_from_trx_level_type,
2474 applied_from_trx_number,
2475 adjusted_doc_application_id,
2476 adjusted_doc_entity_code,
2477 adjusted_doc_event_class_code,
2478 adjusted_doc_trx_id,
2479 adjusted_doc_line_id,
2480 adjusted_doc_trx_level_type,
2481 adjusted_doc_number,
2482 adjusted_doc_date,
2483 applied_to_application_id,
2484 applied_to_event_class_code,
2485 applied_to_entity_code,
2486 applied_to_trx_id,
2487 applied_to_line_id,
2488 applied_to_trx_number,
2489 summary_tax_line_id,
2490 offset_link_to_tax_line_id,
2491 offset_flag,
2492 process_for_recovery_flag,
2493 tax_jurisdiction_id,
2494 tax_jurisdiction_code,
2495 place_of_supply,
2496 place_of_supply_type_code,
2497 place_of_supply_result_id,
2498 tax_date_rule_id,
2499 tax_date,
2500 tax_determine_date,
2501 tax_point_date,
2502 trx_line_date,
2503 tax_type_code,
2504 tax_code,
2505 tax_registration_id,
2506 tax_registration_number,
2507 registration_party_type,
2508 rounding_level_code,
2509 rounding_rule_code,
2510 rounding_lvl_party_tax_prof_id,
2511 rounding_lvl_party_type,
2512 compounding_tax_flag,
2513 orig_tax_status_id,
2514 orig_tax_status_code,
2515 orig_tax_rate_id,
2516 orig_tax_rate_code,
2517 orig_tax_rate,
2518 orig_tax_jurisdiction_id,
2519 orig_tax_jurisdiction_code,
2520 orig_tax_amt_included_flag,
2521 orig_self_assessed_flag,
2522 tax_currency_code,
2523 tax_amt,
2524 tax_amt_tax_curr,
2525 tax_amt_funcl_curr,
2526 taxable_amt,
2527 taxable_amt_tax_curr,
2528 taxable_amt_funcl_curr,
2529 orig_taxable_amt,
2530 orig_taxable_amt_tax_curr,
2531 cal_tax_amt,
2532 cal_tax_amt_tax_curr,
2533 cal_tax_amt_funcl_curr,
2534 orig_tax_amt,
2535 orig_tax_amt_tax_curr,
2536 rec_tax_amt,
2537 rec_tax_amt_tax_curr,
2538 rec_tax_amt_funcl_curr,
2539 nrec_tax_amt,
2540 nrec_tax_amt_tax_curr,
2541 nrec_tax_amt_funcl_curr,
2542 tax_exemption_id,
2543 tax_rate_before_exemption,
2544 tax_rate_name_before_exemption,
2545 exempt_rate_modifier,
2546 exempt_certificate_number,
2547 exempt_reason,
2548 exempt_reason_code,
2549 tax_exception_id,
2550 tax_rate_before_exception,
2551 tax_rate_name_before_exception,
2552 exception_rate,
2553 tax_apportionment_flag,
2554 historical_flag,
2555 taxable_basis_formula,
2556 tax_calculation_formula,
2557 cancel_flag,
2558 purge_flag,
2559 delete_flag,
2560 tax_amt_included_flag,
2561 self_assessed_flag,
2562 overridden_flag,
2563 manually_entered_flag,
2564 freeze_until_overridden_flag,
2565 copied_from_other_doc_flag,
2566 recalc_required_flag,
2567 settlement_flag,
2568 item_dist_changed_flag,
2569 associated_child_frozen_flag,
2570 tax_only_line_flag,
2571 compounding_dep_tax_flag,
2572 last_manual_entry,
2573 tax_provider_id,
2574 record_type_code,
2575 reporting_period_id,
2576 legal_message_appl_2,
2577 legal_message_status,
2578 legal_message_rate,
2579 legal_message_basis,
2580 legal_message_calc,
2581 legal_message_threshold,
2582 legal_message_pos,
2583 legal_message_trn,
2584 legal_message_exmpt,
2585 legal_message_excpt,
2586 tax_regime_template_id,
2587 tax_applicability_result_id,
2588 direct_rate_result_id,
2589 status_result_id,
2590 rate_result_id,
2591 basis_result_id,
2592 thresh_result_id,
2593 calc_result_id,
2594 tax_reg_num_det_result_id,
2595 eval_exmpt_result_id,
2596 eval_excpt_result_id,
2597 enforce_from_natural_acct_flag,
2598 tax_hold_code,
2599 tax_hold_released_code,
2600 prd_total_tax_amt,
2601 prd_total_tax_amt_tax_curr,
2602 prd_total_tax_amt_funcl_curr,
2603 internal_org_location_id,
2604 attribute_category,
2605 attribute1,
2606 attribute2,
2607 attribute3,
2608 attribute4,
2609 attribute5,
2610 attribute6,
2611 attribute7,
2612 attribute8,
2613 attribute9,
2614 attribute10,
2615 attribute11,
2616 attribute12,
2617 attribute13,
2618 attribute14,
2619 attribute15,
2620 global_attribute_category,
2621 global_attribute1,
2622 global_attribute2,
2623 global_attribute3,
2624 global_attribute4,
2625 global_attribute5,
2626 global_attribute6,
2627 global_attribute7,
2628 global_attribute8,
2629 global_attribute9,
2630 global_attribute10,
2631 global_attribute11,
2632 global_attribute12,
2633 global_attribute13,
2634 global_attribute14,
2635 global_attribute15,
2636 numeric1,
2637 numeric2,
2638 numeric3,
2639 numeric4,
2640 numeric5,
2641 numeric6,
2642 numeric7,
2643 numeric8,
2644 numeric9,
2645 numeric10,
2646 char1,
2647 char2,
2648 char3,
2649 char4,
2650 char5,
2651 char6,
2652 char7,
2653 char8,
2654 char9,
2655 char10,
2656 date1,
2657 date2,
2658 date3,
2659 date4,
2660 date5,
2661 date6,
2662 date7,
2663 date8,
2664 date9,
2665 date10,
2666 tax_rate_type,
2667 created_by,
2668 creation_date,
2669 last_updated_by,
2670 last_update_date,
2671 last_update_login,
2672 line_assessable_value,
2673 legal_justification_text1,
2674 legal_justification_text2,
2675 legal_justification_text3,
2676 reporting_currency_code,
2677 trx_line_index,
2678 offset_tax_rate_code,
2679 proration_code,
2680 other_doc_source,
2681 reporting_only_flag,
2682 ctrl_total_line_tx_amt,
2683 sync_with_prvdr_flag,
2684 interface_entity_code,
2685 interface_tax_line_id,
2686 taxing_juris_geography_id,
2687 adjusted_doc_tax_line_id,
2688 object_version_number,
2689 legal_reporting_status,
2690 account_source_tax_rate_id
2691 )
2692 (SELECT
2693 L.tax_line_id,
2694 L.internal_organization_id,
2695 L.application_id,
2696 L.entity_code,
2697 L.event_class_code,
2698 L.event_type_code,
2699 L.trx_id,
2700 L.trx_line_id,
2701 L.trx_level_type,
2702 L.trx_line_number,
2703 L.doc_event_status,
2704 L.tax_event_class_code,
2705 L.tax_event_type_code,
2706 L.tax_line_number,
2707 L.content_owner_id,
2708 L.tax_regime_id,
2709 L.tax_regime_code,
2710 L.tax_id,
2711 L.tax,
2712 L.tax_status_id,
2713 L.tax_status_code,
2714 L.tax_rate_id,
2715 L.tax_rate_code,
2716 L.tax_rate,
2717 L.tax_apportionment_line_number,
2718 L.trx_id_level2,
2719 L.trx_id_level3,
2720 L.trx_id_level4,
2721 L.trx_id_level5,
2722 L.trx_id_level6,
2723 L.trx_user_key_level1,
2724 L.trx_user_key_level2,
2725 L.trx_user_key_level3,
2726 L.trx_user_key_level4,
2727 L.trx_user_key_level5,
2728 L.trx_user_key_level6,
2729 L.mrc_tax_line_flag,
2730 L.ledger_id,
2731 L.establishment_id,
2732 L.legal_entity_id,
2733 L.legal_entity_tax_reg_number,
2734 L.hq_estb_reg_number,
2735 L.hq_estb_party_tax_prof_id,
2736 L.currency_conversion_date,
2737 L.currency_conversion_type,
2738 L.currency_conversion_rate,
2739 L.tax_currency_conversion_date,
2740 L.tax_currency_conversion_type,
2741 L.tax_currency_conversion_rate,
2742 L.trx_currency_code,
2743 L.minimum_accountable_unit,
2744 L.precision,
2745 G.trx_number,
2746 L.trx_date,
2747 L.unit_price,
2748 L.line_amt,
2749 L.trx_line_quantity,
2750 L.tax_base_modifier_rate,
2751 L.ref_doc_application_id,
2752 L.ref_doc_entity_code,
2753 L.ref_doc_event_class_code,
2754 L.ref_doc_trx_id,
2755 L.ref_doc_line_id,
2756 L.ref_doc_line_quantity,
2757 L.other_doc_line_amt,
2758 L.other_doc_line_tax_amt,
2759 L.other_doc_line_taxable_amt,
2760 0, -- L.unrounded_taxable_amt,
2761 0, -- L.unrounded_tax_amt,
2762 L.related_doc_application_id,
2763 L.related_doc_entity_code,
2764 L.related_doc_event_class_code,
2765 L.related_doc_trx_id,
2766 L.related_doc_number,
2767 L.related_doc_date,
2768 L.applied_from_application_id,
2769 L.applied_from_event_class_code,
2770 L.applied_from_entity_code,
2771 L.applied_from_trx_id,
2772 L.applied_from_line_id,
2773 L.applied_from_trx_level_type,
2774 L.applied_from_trx_number,
2775 L.adjusted_doc_application_id,
2776 L.adjusted_doc_entity_code,
2777 L.adjusted_doc_event_class_code,
2778 L.adjusted_doc_trx_id,
2779 L.adjusted_doc_line_id,
2780 L.adjusted_doc_trx_level_type,
2781 L.adjusted_doc_number,
2782 L.adjusted_doc_date,
2783 L.applied_to_application_id,
2784 L.applied_to_event_class_code,
2785 L.applied_to_entity_code,
2786 L.applied_to_trx_id,
2787 L.applied_to_line_id,
2788 L.applied_to_trx_number,
2789 L.summary_tax_line_id,
2790 L.offset_link_to_tax_line_id,
2791 L.offset_flag,
2792 DECODE(L.Reporting_Only_Flag, 'N', 'Y', 'N'), -- L.Process_For_Recovery_Flag,
2793 L.tax_jurisdiction_id,
2794 L.tax_jurisdiction_code,
2795 L.place_of_supply,
2796 L.place_of_supply_type_code,
2797 L.place_of_supply_result_id,
2798 L.tax_date_rule_id,
2799 L.tax_date,
2800 L.tax_determine_date,
2801 L.tax_point_date,
2802 L.trx_line_date,
2803 L.tax_type_code,
2804 L.tax_code,
2805 L.tax_registration_id,
2806 L.tax_registration_number,
2807 L.registration_party_type,
2808 L.rounding_level_code,
2809 L.rounding_rule_code,
2810 L.rounding_lvl_party_tax_prof_id,
2811 L.rounding_lvl_party_type,
2812 L.compounding_tax_flag,
2813 L.orig_tax_status_id,
2814 L.orig_tax_status_code,
2815 L.orig_tax_rate_id,
2816 L.orig_tax_rate_code,
2817 L.orig_tax_rate,
2818 L.orig_tax_jurisdiction_id,
2819 L.orig_tax_jurisdiction_code,
2820 L.orig_tax_amt_included_flag,
2821 L.orig_self_assessed_flag,
2822 L.tax_currency_code,
2823 0, -- L.tax_amt,
2824 0, -- L.tax_amt_tax_curr,
2825 0, -- L.tax_amt_funcl_curr,
2826 0, -- L.taxable_amt,
2827 0, -- L.taxable_amt_tax_curr,
2828 0, -- L.taxable_amt_funcl_curr,
2829 DECODE(L.orig_taxable_amt, NULL,L.taxable_amt,
2830 L.orig_taxable_amt), -- L.orig_taxable_amt
2831 DECODE(L.orig_taxable_amt_tax_curr, NULL, L.taxable_amt_funcl_curr,
2832 L.orig_taxable_amt_tax_curr), -- L.orig_taxable_amt_tax_curr
2833 0, -- L.cal_tax_amt,
2834 0, -- L.cal_tax_amt_tax_curr,
2835 0, -- L.cal_tax_amt_funcl_curr,
2836 DECODE(L.orig_tax_amt, NULL, L.tax_amt,
2837 L.orig_tax_amt), -- L.orig_tax_amt
2838 DECODE(L.orig_tax_amt_tax_curr, NULL, L.tax_amt,
2839 L.orig_tax_amt_tax_curr), -- L.orig_tax_amt_tax_curr
2840 0, -- L.rec_tax_amt,
2841 0, -- L.rec_tax_amt_tax_curr,
2842 0, -- L.rec_tax_amt_funcl_curr,
2843 0, -- L.nrec_tax_amt,
2844 0, -- L.nrec_tax_amt_tax_curr,
2845 0, -- L.nrec_tax_amt_funcl_curr,
2846 L.tax_exemption_id,
2847 L.tax_rate_before_exemption,
2848 L.tax_rate_name_before_exemption,
2849 L.exempt_rate_modifier,
2850 L.exempt_certificate_number,
2851 L.exempt_reason,
2852 L.exempt_reason_code,
2853 L.tax_exception_id,
2854 L.tax_rate_before_exception,
2855 L.tax_rate_name_before_exception,
2856 L.exception_rate,
2857 L.tax_apportionment_flag,
2858 L.historical_flag,
2859 L.taxable_basis_formula,
2860 L.tax_calculation_formula,
2861 'Y', -- L.cancel_flag
2862 L.purge_flag,
2863 L.delete_flag,
2864 L.tax_amt_included_flag,
2865 L.self_assessed_flag,
2866 L.overridden_flag,
2867 L.manually_entered_flag,
2868 L.freeze_until_overridden_flag,
2869 L.copied_from_other_doc_flag,
2870 L.recalc_required_flag,
2871 L.settlement_flag,
2872 L.item_dist_changed_flag,
2873 L.associated_child_frozen_flag,
2874 L.tax_only_line_flag,
2875 L.compounding_dep_tax_flag,
2876 L.last_manual_entry,
2877 L.tax_provider_id,
2878 L.record_type_code,
2879 L.reporting_period_id,
2880 L.legal_message_appl_2,
2881 L.legal_message_status,
2882 L.legal_message_rate,
2883 L.legal_message_basis,
2884 L.legal_message_calc,
2885 L.legal_message_threshold,
2886 L.legal_message_pos,
2887 L.legal_message_trn,
2888 L.legal_message_exmpt,
2889 L.legal_message_excpt,
2890 L.tax_regime_template_id,
2891 L.tax_applicability_result_id,
2892 L.direct_rate_result_id,
2893 L.status_result_id,
2894 L.rate_result_id,
2895 L.basis_result_id,
2896 L.thresh_result_id,
2897 L.calc_result_id,
2898 L.tax_reg_num_det_result_id,
2899 L.eval_exmpt_result_id,
2900 L.eval_excpt_result_id,
2901 L.enforce_from_natural_acct_flag,
2902 L.tax_hold_code,
2903 L.tax_hold_released_code,
2904 NULL, -- L.prd_total_tax_amt,
2905 NULL, -- L.prd_total_tax_amt_tax_curr,
2906 NULL, -- L.prd_total_tax_amt_funcl_curr,
2907 L.internal_org_location_id,
2908 L.attribute_category,
2909 L.attribute1,
2910 L.attribute2,
2911 L.attribute3,
2912 L.attribute4,
2913 L.attribute5,
2914 L.attribute6,
2915 L.attribute7,
2916 L.attribute8,
2917 L.attribute9,
2918 L.attribute10,
2919 L.attribute11,
2920 L.attribute12,
2921 L.attribute13,
2922 L.attribute14,
2923 L.attribute15,
2924 L.global_attribute_category,
2925 L.global_attribute1,
2926 L.global_attribute2,
2927 L.global_attribute3,
2928 L.global_attribute4,
2929 L.global_attribute5,
2930 L.global_attribute6,
2931 L.global_attribute7,
2932 L.global_attribute8,
2933 L.global_attribute9,
2934 L.global_attribute10,
2935 L.global_attribute11,
2936 L.global_attribute12,
2937 L.global_attribute13,
2938 L.global_attribute14,
2939 L.global_attribute15,
2940 L.numeric1,
2941 L.numeric2,
2942 L.numeric3,
2943 L.numeric4,
2944 L.numeric5,
2945 L.numeric6,
2946 L.numeric7,
2947 L.numeric8,
2948 L.numeric9,
2949 L.numeric10,
2950 L.char1,
2951 L.char2,
2952 L.char3,
2953 L.char4,
2954 L.char5,
2955 L.char6,
2956 L.char7,
2957 L.char8,
2958 L.char9,
2959 L.char10,
2960 L.date1,
2961 L.date2,
2962 L.date3,
2963 L.date4,
2964 L.date5,
2965 L.date6,
2966 L.date7,
2967 L.date8,
2968 L.date9,
2969 L.date10,
2970 L.tax_rate_type,
2971 L.created_by,
2972 L.creation_date,
2973 L.last_updated_by,
2974 L.last_update_date,
2975 L.last_update_login,
2976 L.line_assessable_value,
2977 L.legal_justification_text1,
2978 L.legal_justification_text2,
2979 L.legal_justification_text3,
2980 L.reporting_currency_code,
2981 NULL, -- L.trx_line_index
2982 NULL, -- L.offset_tax_rate_code
2983 NULL, -- L.proration_code
2984 NULL, -- L.other_doc_source
2985 L.reporting_only_flag,
2986 L.ctrl_total_line_tx_amt,
2987 DECODE(L.tax_provider_id, NULL, L.sync_with_prvdr_flag, 'Y'),
2988 L.interface_entity_code,
2989 L.interface_tax_line_id,
2990 L.taxing_juris_geography_id,
2991 L.adjusted_doc_tax_line_id,
2992 1,
2993 legal_reporting_status,
2994 L.account_source_tax_rate_id
2995 FROM zx_lines L,
2996 zx_lines_det_factors G
2997 WHERE G.application_id = p_event_class_rec.application_id
2998 AND G.entity_code = p_event_class_rec.entity_code
2999 AND G.event_class_code = p_event_class_rec.event_class_code
3000 AND G.trx_id = p_event_class_rec.trx_id
3001 AND G.event_id = p_event_class_rec.event_id
3002 AND L.trx_id = G.trx_id
3003 AND L.trx_line_id = G.trx_line_id
3004 AND L.trx_level_type = G.trx_level_type
3005 AND L.event_class_code = G.event_class_code
3006 AND L.entity_code = G.entity_code
3007 AND L.application_id = G.application_id
3008 AND L.associated_child_frozen_flag = 'Y'
3009 AND L.cancel_flag = 'Y'
3010 AND L.tax_apportionment_line_number < 0
3011 AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
3012 'X'
3013 FROM zx_detail_tax_lines_gt T
3014 WHERE T.trx_id = L.trx_id
3015 AND T.trx_line_id = L.trx_line_id
3016 AND T.event_class_code = L.event_class_code
3017 AND T.entity_code = L.entity_code
3018 AND T.application_id = L.application_id
3019 AND T.tax_line_id = L.tax_line_id
3020 AND T.trx_level_type = L.trx_level_type
3021 )
3022 );
3023
3024 ELSE
3025
3026 -- bug 6906427: update tax line id if no grouping criteria change
3027 --
3028 UPDATE zx_detail_tax_lines_gt zlgt
3029 SET (tax_line_id, associated_child_frozen_flag, summary_tax_line_id) =
3030 ( SELECT /*+ INDEX(zl ZX_LINES_U1) */
3031 tax_line_id, associated_child_frozen_flag, summary_tax_line_id
3032 FROM zx_lines zl
3033 WHERE zl.application_id = zlgt.application_id
3034 AND zl.entity_code = zlgt.entity_code
3035 AND zl.event_class_code = zlgt.event_class_code
3036 AND zl.trx_id = zlgt.trx_id
3037 and zl.trx_line_id = zlgt.trx_line_id
3038 AND zl.trx_level_type = zlgt.trx_level_type
3039 AND zl.internal_organization_id = zlgt.internal_organization_id
3040 AND NVL(zl.applied_from_trx_level_type, 'x') = NVL(zlgt.applied_from_trx_level_type, 'x')
3041 AND NVL(zl.adjusted_doc_trx_level_type, 'x') = NVL(zlgt.adjusted_doc_trx_level_type, 'x')
3042 AND NVL(zl.applied_from_application_id, 0) = NVL(zlgt.applied_from_application_id, 0)
3043 AND NVL(zl.applied_from_event_class_code, 'x') = NVL(zlgt.applied_from_event_class_code, 'x')
3044 AND NVL(zl.applied_from_entity_code, 'x') = NVL(zlgt.applied_from_entity_code, 'x')
3045 AND NVL(zl.applied_from_trx_id, 0) = NVL(zlgt.applied_from_trx_id, 0)
3046 AND NVL(zl.applied_from_line_id, 0) = NVL(zlgt.applied_from_line_id, 0)
3047 AND NVL(zl.adjusted_doc_application_id, 0) = NVL(zlgt.adjusted_doc_application_id, 0)
3048 AND NVL(zl.adjusted_doc_entity_code, 'x') = NVL(zlgt.adjusted_doc_entity_code, 'x')
3049 AND NVL(zl.adjusted_doc_event_class_code, 'x') = NVL(zlgt.adjusted_doc_event_class_code, 'x')
3050 AND NVL(zl.adjusted_doc_trx_id, 0) = NVL(zlgt.adjusted_doc_trx_id, 0)
3051 AND NVL(zl.tax_exemption_id, -999) = NVL(zlgt.tax_exemption_id, -999)
3052 --AND NVL(zl.tax_rate_before_exemption, -999) = NVL(zlgt.tax_rate_before_exemption, -999)
3053 --AND NVL(zl.tax_rate_name_before_exemption, 'x') = NVL(zlgt.tax_rate_name_before_exemption, 'x')
3054 --AND NVL(zl.exempt_rate_modifier, -999) = NVL(zlgt.exempt_rate_modifier, -999)
3055 AND NVL(zl.exempt_certificate_number, 'x') = NVL(zlgt.exempt_certificate_number, 'x')
3056 --AND NVL(zl.exempt_reason, 'x') = NVL(zlgt.exempt_reason, 'x')
3057 AND NVL(zl.exempt_reason_code, 'x') = NVL(zlgt.exempt_reason_code, 'x')
3058 AND NVL(zl.tax_exception_id, -999) = NVL(zlgt.tax_exception_id, -999)
3059 --AND NVL(zl.tax_rate_before_exception, -999) = NVL(zlgt.tax_rate_before_exception, -999)
3060 --AND NVL(zl.tax_rate_name_before_exception, 'x') = NVL(zlgt.tax_rate_name_before_exception, 'x')
3061 --AND NVL(zl.exception_rate, -999) = NVL(zlgt.exception_rate, -999)
3062 AND NVL(zl.content_owner_id, 0) = NVL(zlgt.content_owner_id, 0)
3063 AND zl.tax_regime_code = zlgt.tax_regime_code
3064 AND zl.tax = zlgt.tax
3065 AND NVL(zl.tax_status_code, 'x') = NVL(zlgt.tax_status_code, 'x')
3066 AND NVL(zl.tax_rate_id, 0) = NVL(zlgt.tax_rate_id, 0)
3067 AND NVL(zl.tax_rate_code, 'x') = NVL(zlgt.tax_rate_code, 'x')
3068 AND NVL(zl.tax_rate, -99) = NVL(zlgt.tax_rate, -99)
3069 AND NVL(zl.tax_jurisdiction_code, 'x') = NVL(zlgt.tax_jurisdiction_code, 'x')
3070 AND NVL(zl.ledger_id, 0) = NVL(zlgt.ledger_id, 0)
3071 AND NVL(zl.legal_entity_id, 0) = NVL(zlgt.legal_entity_id, 0)
3072 AND NVL(zl.establishment_id, 0) = NVL(zlgt.establishment_id, 0)
3073 AND NVL(TRUNC(zl.currency_conversion_date), SYSDATE) = NVL(TRUNC(zlgt.currency_conversion_date), SYSDATE)
3074 AND NVL(zl.currency_conversion_type,'x') = NVL(zlgt.currency_conversion_type,'x')
3075 AND NVL(zl.currency_conversion_rate, 1) = NVL(zlgt.currency_conversion_rate, 1)
3076 AND NVL(zl.taxable_basis_formula,'x') = NVL(zlgt.taxable_basis_formula,'x')
3077 AND NVL(zl.tax_calculation_formula,'x') = NVL(zlgt.tax_calculation_formula,'x')
3078 AND zl.tax_amt_included_flag = zlgt.tax_amt_included_flag
3079 AND zl.compounding_tax_flag = zlgt.compounding_tax_flag
3080 AND zl.self_assessed_flag = zlgt.self_assessed_flag
3081 AND zl.reporting_only_flag = zlgt.reporting_only_flag
3082 AND zl.copied_from_other_doc_flag = zlgt.copied_from_other_doc_flag
3083 AND NVL(zl.record_type_code,'x') = NVL(zlgt.record_type_code,'x')
3084 AND NVL(zl.tax_provider_id, 0) = NVL(zlgt.tax_provider_id, 0)
3085 AND zl.overridden_flag = zlgt.overridden_flag
3086 AND zl.manually_entered_flag = zlgt.manually_entered_flag
3087 AND zl.tax_only_line_flag = zlgt.tax_only_line_flag
3088 AND zl.mrc_tax_line_flag = zlgt.mrc_tax_line_flag
3089 AND zl.historical_flag = zlgt.historical_flag
3090 AND NVL(zl.tax_apportionment_line_number, 1) = NVL(zlgt.tax_apportionment_line_number, 1)
3091 AND zl.tax_line_id <> zlgt.tax_line_id
3092 AND zl.cancel_flag <> 'Y'
3093 AND ROWNUM = 1
3094 )
3095 WHERE NVL(associated_child_frozen_flag, 'N') ='N'
3096 AND summary_tax_line_id IS NULL
3097 AND NVL(tax_only_line_flag, 'N') = 'N'
3098 AND EXISTS (SELECT /*+ INDEX(zl ZX_LINES_U1) */
3099 tax_line_id, associated_child_frozen_flag
3100 FROM zx_lines zl
3101 WHERE zl.application_id = zlgt.application_id
3102 AND zl.entity_code = zlgt.entity_code
3103 AND zl.event_class_code = zlgt.event_class_code
3104 AND zl.trx_id = zlgt.trx_id
3105 and zl.trx_line_id = zlgt.trx_line_id
3106 AND zl.trx_level_type = zlgt.trx_level_type
3107 AND zl.internal_organization_id = zlgt.internal_organization_id
3108 AND NVL(zl.applied_from_trx_level_type, 'x') = NVL(zlgt.applied_from_trx_level_type, 'x')
3109 AND NVL(zl.adjusted_doc_trx_level_type, 'x') = NVL(zlgt.adjusted_doc_trx_level_type, 'x')
3110 AND NVL(zl.applied_from_application_id, 0) = NVL(zlgt.applied_from_application_id, 0)
3111 AND NVL(zl.applied_from_event_class_code, 'x') = NVL(zlgt.applied_from_event_class_code, 'x')
3112 AND NVL(zl.applied_from_entity_code, 'x') = NVL(zlgt.applied_from_entity_code, 'x')
3113 AND NVL(zl.applied_from_trx_id, 0) = NVL(zlgt.applied_from_trx_id, 0)
3114 AND NVL(zl.applied_from_line_id, 0) = NVL(zlgt.applied_from_line_id, 0)
3115 AND NVL(zl.adjusted_doc_application_id, 0) = NVL(zlgt.adjusted_doc_application_id, 0)
3116 AND NVL(zl.adjusted_doc_entity_code, 'x') = NVL(zlgt.adjusted_doc_entity_code, 'x')
3117 AND NVL(zl.adjusted_doc_event_class_code, 'x') = NVL(zlgt.adjusted_doc_event_class_code, 'x')
3118 AND NVL(zl.adjusted_doc_trx_id, 0) = NVL(zlgt.adjusted_doc_trx_id, 0)
3119 AND NVL(zl.tax_exemption_id, -999) = NVL(zlgt.tax_exemption_id, -999)
3120 -- AND NVL(zl.tax_rate_before_exemption, -999) = NVL(zlgt.tax_rate_before_exemption, -999)
3121 -- AND NVL(zl.tax_rate_name_before_exemption, 'x') = NVL(zlgt.tax_rate_name_before_exemption, 'x')
3122 -- AND NVL(zl.exempt_rate_modifier, -999) = NVL(zlgt.exempt_rate_modifier, -999)
3123 AND NVL(zl.exempt_certificate_number, 'x') = NVL(zlgt.exempt_certificate_number, 'x')
3124 -- AND NVL(zl.exempt_reason, 'x') = NVL(zlgt.exempt_reason, 'x')
3125 AND NVL(zl.exempt_reason_code, 'x') = NVL(zlgt.exempt_reason_code, 'x')
3126 AND NVL(zl.tax_exception_id, -999) = NVL(zlgt.tax_exception_id, -999)
3127 -- AND NVL(zl.tax_rate_before_exception, -999) = NVL(zlgt.tax_rate_before_exception, -999)
3128 -- AND NVL(zl.tax_rate_name_before_exception, 'x') = NVL(zlgt.tax_rate_name_before_exception, 'x')
3129 -- AND NVL(zl.exception_rate, -999) = NVL(zlgt.exception_rate, -999)
3130 AND NVL(zl.content_owner_id, 0) = NVL(zlgt.content_owner_id, 0)
3131 AND zl.tax_regime_code = zlgt.tax_regime_code
3132 AND zl.tax = zlgt.tax
3133 AND NVL(zl.tax_status_code, 'x') = NVL(zlgt.tax_status_code, 'x')
3134 AND NVL(zl.tax_rate_id, 0) = NVL(zlgt.tax_rate_id, 0)
3135 AND NVL(zl.tax_rate_code, 'x') = NVL(zlgt.tax_rate_code, 'x')
3136 AND NVL(zl.tax_rate, -99) = NVL(zlgt.tax_rate, -99)
3137 AND NVL(zl.tax_jurisdiction_code, 'x') = NVL(zlgt.tax_jurisdiction_code, 'x')
3138 AND NVL(zl.ledger_id, 0) = NVL(zlgt.ledger_id, 0)
3139 AND NVL(zl.legal_entity_id, 0) = NVL(zlgt.legal_entity_id, 0)
3140 AND NVL(zl.establishment_id, 0) = NVL(zlgt.establishment_id, 0)
3141 AND NVL(TRUNC(zl.currency_conversion_date), SYSDATE) = NVL(TRUNC(zlgt.currency_conversion_date), SYSDATE)
3142 AND NVL(zl.currency_conversion_type,'x') = NVL(zlgt.currency_conversion_type,'x')
3143 AND NVL(zl.currency_conversion_rate, 1) = NVL(zlgt.currency_conversion_rate, 1)
3144 AND NVL(zl.taxable_basis_formula,'x') = NVL(zlgt.taxable_basis_formula,'x')
3145 AND NVL(zl.tax_calculation_formula,'x') = NVL(zlgt.tax_calculation_formula,'x')
3146 AND zl.tax_amt_included_flag = zlgt.tax_amt_included_flag
3147 AND zl.compounding_tax_flag = zlgt.compounding_tax_flag
3148 AND zl.self_assessed_flag = zlgt.self_assessed_flag
3149 AND zl.reporting_only_flag = zlgt.reporting_only_flag
3150 AND zl.copied_from_other_doc_flag = zlgt.copied_from_other_doc_flag
3151 AND NVL(zl.record_type_code,'x') = NVL(zlgt.record_type_code,'x')
3152 AND NVL(zl.tax_provider_id, 0) = NVL(zlgt.tax_provider_id, 0)
3153 AND zl.overridden_flag = zlgt.overridden_flag
3154 AND zl.manually_entered_flag = zlgt.manually_entered_flag
3155 AND zl.tax_only_line_flag = zlgt.tax_only_line_flag
3156 AND zl.mrc_tax_line_flag = zlgt.mrc_tax_line_flag
3157 AND zl.historical_flag = zlgt.historical_flag
3158 AND NVL(zl.tax_apportionment_line_number, 1) = NVL(zlgt.tax_apportionment_line_number, 1)
3159 AND zl.tax_line_id <> zlgt.tax_line_id
3160 AND zl.cancel_flag <> 'Y'
3161 );
3162
3163 IF (g_level_statement >= g_current_runtime_level ) THEN
3164 FND_LOG.STRING(g_level_statement,
3165 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines',
3166 'Number of Rows Updated: ' || SQL%ROWCOUNT);
3167 END IF;
3168
3169 INSERT INTO zx_detail_tax_lines_gt
3170 ( tax_line_id,
3171 internal_organization_id,
3172 application_id,
3173 entity_code,
3174 event_class_code,
3175 event_type_code,
3176 trx_id,
3177 trx_line_id,
3178 trx_level_type,
3179 trx_line_number,
3180 doc_event_status,
3181 tax_event_class_code,
3182 tax_event_type_code,
3183 tax_line_number,
3184 content_owner_id,
3185 tax_regime_id,
3186 tax_regime_code,
3187 tax_id,
3188 tax,
3189 tax_status_id,
3190 tax_status_code,
3191 tax_rate_id,
3192 tax_rate_code,
3193 tax_rate,
3194 tax_apportionment_line_number,
3195 trx_id_level2,
3196 trx_id_level3,
3197 trx_id_level4,
3198 trx_id_level5,
3199 trx_id_level6,
3200 trx_user_key_level1,
3201 trx_user_key_level2,
3202 trx_user_key_level3,
3203 trx_user_key_level4,
3204 trx_user_key_level5,
3205 trx_user_key_level6,
3206 mrc_tax_line_flag,
3207 ledger_id,
3208 establishment_id,
3209 legal_entity_id,
3210 legal_entity_tax_reg_number,
3211 hq_estb_reg_number,
3212 hq_estb_party_tax_prof_id,
3213 currency_conversion_date,
3214 currency_conversion_type,
3215 currency_conversion_rate,
3216 tax_currency_conversion_date,
3217 tax_currency_conversion_type,
3218 tax_currency_conversion_rate,
3219 trx_currency_code,
3220 minimum_accountable_unit,
3221 precision,
3222 trx_number,
3223 trx_date,
3224 unit_price,
3225 line_amt,
3226 trx_line_quantity,
3227 tax_base_modifier_rate,
3228 ref_doc_application_id,
3229 ref_doc_entity_code,
3230 ref_doc_event_class_code,
3231 ref_doc_trx_id,
3232 ref_doc_line_id,
3233 ref_doc_line_quantity,
3234 other_doc_line_amt,
3235 other_doc_line_tax_amt,
3236 other_doc_line_taxable_amt,
3237 unrounded_taxable_amt,
3238 unrounded_tax_amt,
3239 related_doc_application_id,
3240 related_doc_entity_code,
3241 related_doc_event_class_code,
3242 related_doc_trx_id,
3243 related_doc_number,
3244 related_doc_date,
3245 applied_from_application_id,
3246 applied_from_event_class_code,
3247 applied_from_entity_code,
3248 applied_from_trx_id,
3249 applied_from_line_id,
3250 applied_from_trx_level_type,
3251 applied_from_trx_number,
3252 adjusted_doc_application_id,
3253 adjusted_doc_entity_code,
3254 adjusted_doc_event_class_code,
3255 adjusted_doc_trx_id,
3256 adjusted_doc_line_id,
3257 adjusted_doc_trx_level_type,
3258 adjusted_doc_number,
3259 adjusted_doc_date,
3260 applied_to_application_id,
3261 applied_to_event_class_code,
3262 applied_to_entity_code,
3263 applied_to_trx_id,
3264 applied_to_line_id,
3265 applied_to_trx_number,
3266 summary_tax_line_id,
3267 offset_link_to_tax_line_id,
3268 offset_flag,
3269 process_for_recovery_flag,
3270 tax_jurisdiction_id,
3271 tax_jurisdiction_code,
3272 place_of_supply,
3273 place_of_supply_type_code,
3274 place_of_supply_result_id,
3275 tax_date_rule_id,
3276 tax_date,
3277 tax_determine_date,
3278 tax_point_date,
3279 trx_line_date,
3280 tax_type_code,
3281 tax_code,
3282 tax_registration_id,
3283 tax_registration_number,
3284 registration_party_type,
3285 rounding_level_code,
3286 rounding_rule_code,
3287 rounding_lvl_party_tax_prof_id,
3288 rounding_lvl_party_type,
3289 compounding_tax_flag,
3290 orig_tax_status_id,
3291 orig_tax_status_code,
3292 orig_tax_rate_id,
3293 orig_tax_rate_code,
3294 orig_tax_rate,
3295 orig_tax_jurisdiction_id,
3296 orig_tax_jurisdiction_code,
3297 orig_tax_amt_included_flag,
3298 orig_self_assessed_flag,
3299 tax_currency_code,
3300 tax_amt,
3301 tax_amt_tax_curr,
3302 tax_amt_funcl_curr,
3303 taxable_amt,
3304 taxable_amt_tax_curr,
3305 taxable_amt_funcl_curr,
3306 orig_taxable_amt,
3307 orig_taxable_amt_tax_curr,
3308 cal_tax_amt,
3309 cal_tax_amt_tax_curr,
3310 cal_tax_amt_funcl_curr,
3311 orig_tax_amt,
3312 orig_tax_amt_tax_curr,
3313 rec_tax_amt,
3314 rec_tax_amt_tax_curr,
3315 rec_tax_amt_funcl_curr,
3316 nrec_tax_amt,
3317 nrec_tax_amt_tax_curr,
3318 nrec_tax_amt_funcl_curr,
3319 tax_exemption_id,
3320 tax_rate_before_exemption,
3321 tax_rate_name_before_exemption,
3322 exempt_rate_modifier,
3323 exempt_certificate_number,
3324 exempt_reason,
3325 exempt_reason_code,
3326 tax_exception_id,
3327 tax_rate_before_exception,
3328 tax_rate_name_before_exception,
3329 exception_rate,
3330 tax_apportionment_flag,
3331 historical_flag,
3332 taxable_basis_formula,
3333 tax_calculation_formula,
3334 cancel_flag,
3335 purge_flag,
3336 delete_flag,
3337 tax_amt_included_flag,
3338 self_assessed_flag,
3339 overridden_flag,
3340 manually_entered_flag,
3341 freeze_until_overridden_flag,
3342 copied_from_other_doc_flag,
3343 recalc_required_flag,
3344 settlement_flag,
3345 item_dist_changed_flag,
3346 associated_child_frozen_flag,
3347 tax_only_line_flag,
3348 compounding_dep_tax_flag,
3349 last_manual_entry,
3350 tax_provider_id,
3351 record_type_code,
3352 reporting_period_id,
3353 legal_message_appl_2,
3354 legal_message_status,
3355 legal_message_rate,
3356 legal_message_basis,
3357 legal_message_calc,
3358 legal_message_threshold,
3359 legal_message_pos,
3360 legal_message_trn,
3361 legal_message_exmpt,
3362 legal_message_excpt,
3363 tax_regime_template_id,
3364 tax_applicability_result_id,
3365 direct_rate_result_id,
3366 status_result_id,
3367 rate_result_id,
3368 basis_result_id,
3369 thresh_result_id,
3370 calc_result_id,
3371 tax_reg_num_det_result_id,
3372 eval_exmpt_result_id,
3373 eval_excpt_result_id,
3374 enforce_from_natural_acct_flag,
3375 tax_hold_code,
3376 tax_hold_released_code,
3377 prd_total_tax_amt,
3378 prd_total_tax_amt_tax_curr,
3379 prd_total_tax_amt_funcl_curr,
3380 internal_org_location_id,
3381 attribute_category,
3382 attribute1,
3383 attribute2,
3384 attribute3,
3385 attribute4,
3386 attribute5,
3387 attribute6,
3388 attribute7,
3389 attribute8,
3390 attribute9,
3391 attribute10,
3392 attribute11,
3393 attribute12,
3394 attribute13,
3395 attribute14,
3396 attribute15,
3397 global_attribute_category,
3398 global_attribute1,
3399 global_attribute2,
3400 global_attribute3,
3401 global_attribute4,
3402 global_attribute5,
3403 global_attribute6,
3404 global_attribute7,
3405 global_attribute8,
3406 global_attribute9,
3407 global_attribute10,
3408 global_attribute11,
3409 global_attribute12,
3410 global_attribute13,
3411 global_attribute14,
3412 global_attribute15,
3413 numeric1,
3414 numeric2,
3415 numeric3,
3416 numeric4,
3417 numeric5,
3418 numeric6,
3419 numeric7,
3420 numeric8,
3421 numeric9,
3422 numeric10,
3423 char1,
3424 char2,
3425 char3,
3426 char4,
3427 char5,
3428 char6,
3429 char7,
3430 char8,
3431 char9,
3432 char10,
3433 date1,
3434 date2,
3435 date3,
3436 date4,
3437 date5,
3438 date6,
3439 date7,
3440 date8,
3441 date9,
3442 date10,
3443 tax_rate_type,
3444 created_by,
3445 creation_date,
3446 last_updated_by,
3447 last_update_date,
3448 last_update_login,
3449 line_assessable_value,
3450 legal_justification_text1,
3451 legal_justification_text2,
3452 legal_justification_text3,
3453 reporting_currency_code,
3454 trx_line_index,
3455 offset_tax_rate_code,
3456 proration_code,
3457 other_doc_source,
3458 reporting_only_flag,
3459 ctrl_total_line_tx_amt,
3460 sync_with_prvdr_flag,
3461 interface_entity_code,
3462 interface_tax_line_id,
3463 taxing_juris_geography_id,
3464 adjusted_doc_tax_line_id,
3465 object_version_number,
3466 legal_reporting_status,
3467 account_source_tax_rate_id
3468 )
3469 (SELECT
3470 L.tax_line_id,
3471 L.internal_organization_id,
3472 L.application_id,
3473 L.entity_code,
3474 L.event_class_code,
3475 L.event_type_code,
3476 L.trx_id,
3477 L.trx_line_id,
3478 L.trx_level_type,
3479 L.trx_line_number,
3480 L.doc_event_status,
3481 L.tax_event_class_code,
3482 L.tax_event_type_code,
3483 L.tax_line_number,
3484 L.content_owner_id,
3485 L.tax_regime_id,
3486 L.tax_regime_code,
3487 L.tax_id,
3488 L.tax,
3489 L.tax_status_id,
3490 L.tax_status_code,
3491 L.tax_rate_id,
3492 L.tax_rate_code,
3493 L.tax_rate,
3494 -- bug 6906427
3495 NVL(-((SELECT max(ABS(tax_apportionment_line_number))
3496 FROM zx_detail_tax_lines_gt gt1
3497 WHERE gt1.application_id = L.application_id
3498 AND gt1.entity_code = L.entity_code
3499 AND gt1.event_class_code = L.event_class_code
3500 AND gt1.trx_id = L.trx_id
3501 AND gt1.trx_line_id = L.trx_line_id
3502 AND gt1.trx_level_type = L.trx_level_type
3503 AND gt1.tax_regime_code = L.tax_regime_code
3504 AND gt1.tax = L.tax
3505 -- ) + 1 -- Commented as a fix for Bug#7340317
3506 ) + L.tax_apportionment_line_number -- Added as a fix for Bug#7340317
3507 ),
3508 -L.tax_apportionment_line_number
3509 ),
3510 L.trx_id_level2,
3511 L.trx_id_level3,
3512 L.trx_id_level4,
3513 L.trx_id_level5,
3514 L.trx_id_level6,
3515 L.trx_user_key_level1,
3516 L.trx_user_key_level2,
3517 L.trx_user_key_level3,
3518 L.trx_user_key_level4,
3519 L.trx_user_key_level5,
3520 L.trx_user_key_level6,
3521 L.mrc_tax_line_flag,
3522 L.ledger_id,
3523 L.establishment_id,
3524 L.legal_entity_id,
3525 L.legal_entity_tax_reg_number,
3526 L.hq_estb_reg_number,
3527 L.hq_estb_party_tax_prof_id,
3528 L.currency_conversion_date,
3529 L.currency_conversion_type,
3530 L.currency_conversion_rate,
3531 L.tax_currency_conversion_date,
3532 L.tax_currency_conversion_type,
3533 L.tax_currency_conversion_rate,
3534 L.trx_currency_code,
3535 L.minimum_accountable_unit,
3536 L.precision,
3537 G.trx_number,
3538 L.trx_date,
3539 L.unit_price,
3540 L.line_amt,
3541 L.trx_line_quantity,
3542 L.tax_base_modifier_rate,
3543 L.ref_doc_application_id,
3544 L.ref_doc_entity_code,
3545 L.ref_doc_event_class_code,
3546 L.ref_doc_trx_id,
3547 L.ref_doc_line_id,
3548 L.ref_doc_line_quantity,
3549 L.other_doc_line_amt,
3550 L.other_doc_line_tax_amt,
3551 L.other_doc_line_taxable_amt,
3552 0, -- L.unrounded_taxable_amt,
3553 0, -- L.unrounded_tax_amt,
3554 L.related_doc_application_id,
3555 L.related_doc_entity_code,
3556 L.related_doc_event_class_code,
3557 L.related_doc_trx_id,
3558 L.related_doc_number,
3559 L.related_doc_date,
3560 L.applied_from_application_id,
3561 L.applied_from_event_class_code,
3562 L.applied_from_entity_code,
3563 L.applied_from_trx_id,
3564 L.applied_from_line_id,
3565 L.applied_from_trx_level_type,
3566 L.applied_from_trx_number,
3567 L.adjusted_doc_application_id,
3568 L.adjusted_doc_entity_code,
3569 L.adjusted_doc_event_class_code,
3570 L.adjusted_doc_trx_id,
3571 L.adjusted_doc_line_id,
3572 L.adjusted_doc_trx_level_type,
3573 L.adjusted_doc_number,
3574 L.adjusted_doc_date,
3575 L.applied_to_application_id,
3576 L.applied_to_event_class_code,
3577 L.applied_to_entity_code,
3578 L.applied_to_trx_id,
3579 L.applied_to_line_id,
3580 L.applied_to_trx_number,
3581 L.summary_tax_line_id,
3582 L.offset_link_to_tax_line_id,
3583 L.offset_flag,
3584 DECODE(L.Reporting_Only_Flag, 'N', 'Y', 'N'), -- L.Process_For_Recovery_Flag,
3585 L.tax_jurisdiction_id,
3586 L.tax_jurisdiction_code,
3587 L.place_of_supply,
3588 L.place_of_supply_type_code,
3589 L.place_of_supply_result_id,
3590 L.tax_date_rule_id,
3591 L.tax_date,
3592 L.tax_determine_date,
3593 L.tax_point_date,
3594 L.trx_line_date,
3595 L.tax_type_code,
3596 L.tax_code,
3597 L.tax_registration_id,
3598 L.tax_registration_number,
3599 L.registration_party_type,
3600 L.rounding_level_code,
3601 L.rounding_rule_code,
3602 L.rounding_lvl_party_tax_prof_id,
3603 L.rounding_lvl_party_type,
3604 L.compounding_tax_flag,
3605 L.orig_tax_status_id,
3606 L.orig_tax_status_code,
3607 L.orig_tax_rate_id,
3608 L.orig_tax_rate_code,
3609 L.orig_tax_rate,
3610 L.orig_tax_jurisdiction_id,
3611 L.orig_tax_jurisdiction_code,
3612 L.orig_tax_amt_included_flag,
3613 L.orig_self_assessed_flag,
3614 L.tax_currency_code,
3615 0, -- L.tax_amt,
3616 0, -- L.tax_amt_tax_curr,
3617 0, -- L.tax_amt_funcl_curr,
3618 0, -- L.taxable_amt,
3619 0, -- L.taxable_amt_tax_curr,
3620 0, -- L.taxable_amt_funcl_curr,
3621 DECODE(L.orig_taxable_amt, NULL,L.taxable_amt,
3622 L.orig_taxable_amt), -- L.orig_taxable_amt
3623 DECODE(L.orig_taxable_amt_tax_curr, NULL, L.taxable_amt_funcl_curr,
3624 L.orig_taxable_amt_tax_curr), -- L.orig_taxable_amt_tax_curr
3625 0, -- L.cal_tax_amt,
3626 0, -- L.cal_tax_amt_tax_curr,
3627 0, -- L.cal_tax_amt_funcl_curr,
3628 DECODE(L.orig_tax_amt, NULL, L.tax_amt,
3629 L.orig_tax_amt), -- L.orig_tax_amt
3630 DECODE(L.orig_tax_amt_tax_curr, NULL, L.tax_amt,
3631 L.orig_tax_amt_tax_curr), -- L.orig_tax_amt_tax_curr
3632 0, -- L.rec_tax_amt,
3633 0, -- L.rec_tax_amt_tax_curr,
3634 0, -- L.rec_tax_amt_funcl_curr,
3635 0, -- L.nrec_tax_amt,
3636 0, -- L.nrec_tax_amt_tax_curr,
3637 0, -- L.nrec_tax_amt_funcl_curr,
3638 L.tax_exemption_id,
3639 L.tax_rate_before_exemption,
3640 L.tax_rate_name_before_exemption,
3641 L.exempt_rate_modifier,
3642 L.exempt_certificate_number,
3643 L.exempt_reason,
3644 L.exempt_reason_code,
3645 L.tax_exception_id,
3646 L.tax_rate_before_exception,
3647 L.tax_rate_name_before_exception,
3648 L.exception_rate,
3649 L.tax_apportionment_flag,
3650 L.historical_flag,
3651 L.taxable_basis_formula,
3652 L.tax_calculation_formula,
3653 'Y', -- L.cancel_flag
3654 L.purge_flag,
3655 L.delete_flag,
3656 L.tax_amt_included_flag,
3657 L.self_assessed_flag,
3658 L.overridden_flag,
3659 L.manually_entered_flag,
3660 L.freeze_until_overridden_flag,
3661 L.copied_from_other_doc_flag,
3662 L.recalc_required_flag,
3663 L.settlement_flag,
3664 L.item_dist_changed_flag,
3665 L.associated_child_frozen_flag,
3666 L.tax_only_line_flag,
3667 L.compounding_dep_tax_flag,
3668 L.last_manual_entry,
3669 L.tax_provider_id,
3670 L.record_type_code,
3671 L.reporting_period_id,
3672 L.legal_message_appl_2,
3673 L.legal_message_status,
3674 L.legal_message_rate,
3675 L.legal_message_basis,
3676 L.legal_message_calc,
3677 L.legal_message_threshold,
3678 L.legal_message_pos,
3679 L.legal_message_trn,
3680 L.legal_message_exmpt,
3681 L.legal_message_excpt,
3682 L.tax_regime_template_id,
3683 L.tax_applicability_result_id,
3684 L.direct_rate_result_id,
3685 L.status_result_id,
3686 L.rate_result_id,
3687 L.basis_result_id,
3688 L.thresh_result_id,
3689 L.calc_result_id,
3690 L.tax_reg_num_det_result_id,
3691 L.eval_exmpt_result_id,
3692 L.eval_excpt_result_id,
3693 L.enforce_from_natural_acct_flag,
3694 L.tax_hold_code,
3695 L.tax_hold_released_code,
3696 NULL, -- L.prd_total_tax_amt,
3697 NULL, -- L.prd_total_tax_amt_tax_curr,
3698 NULL, -- L.prd_total_tax_amt_funcl_curr,
3699 L.internal_org_location_id,
3700 L.attribute_category,
3701 L.attribute1,
3702 L.attribute2,
3703 L.attribute3,
3704 L.attribute4,
3705 L.attribute5,
3706 L.attribute6,
3707 L.attribute7,
3708 L.attribute8,
3709 L.attribute9,
3710 L.attribute10,
3711 L.attribute11,
3712 L.attribute12,
3713 L.attribute13,
3714 L.attribute14,
3715 L.attribute15,
3716 L.global_attribute_category,
3717 L.global_attribute1,
3718 L.global_attribute2,
3719 L.global_attribute3,
3720 L.global_attribute4,
3721 L.global_attribute5,
3722 L.global_attribute6,
3723 L.global_attribute7,
3724 L.global_attribute8,
3725 L.global_attribute9,
3726 L.global_attribute10,
3727 L.global_attribute11,
3728 L.global_attribute12,
3729 L.global_attribute13,
3730 L.global_attribute14,
3731 L.global_attribute15,
3732 L.numeric1,
3733 L.numeric2,
3734 L.numeric3,
3735 L.numeric4,
3736 L.numeric5,
3737 L.numeric6,
3738 L.numeric7,
3739 L.numeric8,
3740 L.numeric9,
3741 L.numeric10,
3742 L.char1,
3743 L.char2,
3744 L.char3,
3745 L.char4,
3746 L.char5,
3747 L.char6,
3748 L.char7,
3749 L.char8,
3750 L.char9,
3751 L.char10,
3752 L.date1,
3753 L.date2,
3754 L.date3,
3755 L.date4,
3756 L.date5,
3757 L.date6,
3758 L.date7,
3759 L.date8,
3760 L.date9,
3761 L.date10,
3762 L.tax_rate_type,
3763 L.created_by,
3764 L.creation_date,
3765 L.last_updated_by,
3766 L.last_update_date,
3767 L.last_update_login,
3768 L.line_assessable_value,
3769 L.legal_justification_text1,
3770 L.legal_justification_text2,
3771 L.legal_justification_text3,
3772 L.reporting_currency_code,
3773 NULL, -- L.trx_line_index
3774 NULL, -- L.offset_tax_rate_code
3775 NULL, -- L.proration_code
3776 NULL, -- L.other_doc_source
3777 L.reporting_only_flag,
3778 L.ctrl_total_line_tx_amt,
3779 DECODE(L.tax_provider_id, NULL, L.sync_with_prvdr_flag, 'Y'),
3780 L.interface_entity_code,
3781 L.interface_tax_line_id,
3782 L.taxing_juris_geography_id,
3783 L.adjusted_doc_tax_line_id,
3784 1,
3785 legal_reporting_status,
3786 L.account_source_tax_rate_id
3787 FROM zx_lines L,
3788 zx_lines_det_factors G
3789 WHERE G.application_id = p_event_class_rec.application_id
3790 AND G.entity_code = p_event_class_rec.entity_code
3791 AND G.event_class_code = p_event_class_rec.event_class_code
3792 -- AND G.trx_id = p_event_class_rec.trx_id
3793 AND G.event_id = p_event_class_rec.event_id
3794 AND L.trx_id = G.trx_id
3795 AND L.trx_line_id = G.trx_line_id
3796 AND L.trx_level_type = G.trx_level_type
3797 AND L.event_class_code = G.event_class_code
3798 AND L.entity_code = G.entity_code
3799 AND L.application_id = G.application_id
3800 AND L.associated_child_frozen_flag = 'Y'
3801 AND L.cancel_flag <> 'Y'
3802 -- bug 6906427
3803 AND G.line_level_action = 'UPDATE'
3804 AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
3805 'X'
3806 FROM zx_detail_tax_lines_gt T
3807 WHERE T.trx_id = L.trx_id
3808 AND T.trx_line_id = L.trx_line_id
3809 AND T.event_class_code = L.event_class_code
3810 AND T.entity_code = L.entity_code
3811 AND T.application_id = L.application_id
3812 AND T.tax_line_id = L.tax_line_id
3813 AND T.trx_level_type = L.trx_level_type
3814 -- AND T.tax = L.tax
3815 -- AND T.tax_regime_code = L.tax_regime_code
3816 -- AND T.tax_status_code = L.tax_status_code
3817 -- AND T.tax_line_number = L.tax_line_number
3818 )
3819 );
3820
3821 IF (g_level_statement >= g_current_runtime_level ) THEN
3822 FND_LOG.STRING(g_level_statement,
3823 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines',
3824 'Number of Rows Inserted: ' || SQL%ROWCOUNT);
3825 END IF;
3826
3827 END IF; -- tax_event_type_code = OVERRIDE_TAX, or else
3828
3829 IF (g_level_procedure >= g_current_runtime_level ) THEN
3830 FND_LOG.STRING(g_level_procedure,
3831 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines',
3832 'p_return_status = ' || p_return_status);
3833 FND_LOG.STRING(g_level_procedure,
3834 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines',
3835 'p_error_buffer = ' || p_error_buffer);
3836 FND_LOG.STRING(g_level_procedure,
3837 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines.END',
3838 'ZX_TDS_TAX_LINES_POPU_PKG: process_frozen_tax_lines(-)');
3839 END IF;
3840
3841 EXCEPTION
3842 WHEN OTHERS THEN
3843 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3844 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3845
3846 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3847 FND_LOG.STRING(g_level_unexpected,
3848 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines',
3849 p_error_buffer);
3850 END IF;
3851
3852 END process_frozen_tax_lines;
3853
3854 -----------------------------------------------------------------------
3855 -- PUBLIC PROCEDURE
3856 -- process_discard_tax_lines
3857 --
3858 -- DESCRIPTION
3859 -- This procedure brings all discarded tax lines from zx_lines into
3860 -- detail tax lines global temp table
3861 --
3862 -- CALLED BY
3863 -- ZX_TDS_TAX_LINES_DETM_PKG
3864
3865 PROCEDURE process_discard_tax_lines(
3866 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
3867 p_return_status OUT NOCOPY VARCHAR2,
3868 p_error_buffer OUT NOCOPY VARCHAR2)
3869
3870 IS
3871 BEGIN
3872
3873 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3874
3875 IF (g_level_procedure >= g_current_runtime_level ) THEN
3876 FND_LOG.STRING(g_level_procedure,
3877 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_discard_tax_lines.BEGIN',
3878 'ZX_TDS_TAX_LINES_POPU_PKG: process_discard_tax_lines(+)');
3879 END IF;
3880
3881 p_return_status := FND_API.G_RET_STS_SUCCESS;
3882
3883 INSERT INTO zx_detail_tax_lines_gt
3884 ( tax_line_id,
3885 internal_organization_id,
3886 application_id,
3887 entity_code,
3888 event_class_code,
3889 event_type_code,
3890 trx_id,
3891 trx_line_id,
3892 trx_level_type,
3893 trx_line_number,
3894 doc_event_status,
3895 -- line_event_status,
3896 tax_event_class_code,
3897 tax_event_type_code,
3898 tax_line_number,
3899 content_owner_id,
3900 tax_regime_id,
3901 tax_regime_code,
3902 tax_id,
3903 tax,
3904 tax_status_id,
3905 tax_status_code,
3906 tax_rate_id,
3907 tax_rate_code,
3908 tax_rate,
3909 tax_apportionment_line_number,
3910 trx_id_level2,
3911 trx_id_level3,
3912 trx_id_level4,
3913 trx_id_level5,
3914 trx_id_level6,
3915 trx_user_key_level1,
3916 trx_user_key_level2,
3917 trx_user_key_level3,
3918 trx_user_key_level4,
3919 trx_user_key_level5,
3920 trx_user_key_level6,
3921 mrc_tax_line_flag,
3922 ledger_id,
3923 establishment_id,
3924 legal_entity_id,
3925 legal_entity_tax_reg_number,
3926 hq_estb_reg_number,
3927 hq_estb_party_tax_prof_id,
3928 currency_conversion_date,
3929 currency_conversion_type,
3930 currency_conversion_rate,
3931 tax_currency_conversion_date,
3932 tax_currency_conversion_type,
3933 tax_currency_conversion_rate,
3934 trx_currency_code,
3935 minimum_accountable_unit,
3936 precision,
3937 trx_number,
3938 trx_date,
3939 unit_price,
3940 line_amt,
3941 trx_line_quantity,
3942 tax_base_modifier_rate,
3943 ref_doc_application_id,
3944 ref_doc_entity_code,
3945 ref_doc_event_class_code,
3946 ref_doc_trx_id,
3947 ref_doc_line_id,
3948 ref_doc_line_quantity,
3949 other_doc_line_amt,
3950 other_doc_line_tax_amt,
3951 other_doc_line_taxable_amt,
3952 unrounded_taxable_amt,
3953 unrounded_tax_amt,
3954 related_doc_application_id,
3955 related_doc_entity_code,
3956 related_doc_event_class_code,
3957 related_doc_trx_id,
3958 related_doc_number,
3959 related_doc_date,
3960 applied_from_application_id,
3961 applied_from_event_class_code,
3962 applied_from_entity_code,
3963 applied_from_trx_id,
3964 applied_from_line_id,
3965 applied_from_trx_level_type,
3966 applied_from_trx_number,
3967 adjusted_doc_application_id,
3968 adjusted_doc_entity_code,
3969 adjusted_doc_event_class_code,
3970 adjusted_doc_trx_id,
3971 adjusted_doc_line_id,
3972 adjusted_doc_trx_level_type,
3973 adjusted_doc_number,
3974 adjusted_doc_date,
3975 applied_to_application_id,
3976 applied_to_event_class_code,
3977 applied_to_entity_code,
3978 applied_to_trx_id,
3979 applied_to_line_id,
3980 applied_to_trx_level_type,
3981 applied_to_trx_number,
3982 summary_tax_line_id,
3983 offset_link_to_tax_line_id,
3984 offset_flag,
3985 process_for_recovery_flag,
3986 tax_jurisdiction_id,
3987 tax_jurisdiction_code,
3988 place_of_supply,
3989 place_of_supply_type_code,
3990 place_of_supply_result_id,
3991 tax_date_rule_id,
3992 tax_date,
3993 tax_determine_date,
3994 tax_point_date,
3995 trx_line_date,
3996 tax_type_code,
3997 tax_code,
3998 tax_registration_id,
3999 tax_registration_number,
4000 registration_party_type,
4001 rounding_level_code,
4002 rounding_rule_code,
4003 rounding_lvl_party_tax_prof_id,
4004 rounding_lvl_party_type,
4005 compounding_tax_flag,
4006 orig_tax_status_id,
4007 orig_tax_status_code,
4008 orig_tax_rate_id,
4009 orig_tax_rate_code,
4010 orig_tax_rate,
4011 orig_tax_jurisdiction_id,
4012 orig_tax_jurisdiction_code,
4013 orig_tax_amt_included_flag,
4014 orig_self_assessed_flag,
4015 tax_currency_code,
4016 tax_amt,
4017 tax_amt_tax_curr,
4018 tax_amt_funcl_curr,
4019 taxable_amt,
4020 taxable_amt_tax_curr,
4021 taxable_amt_funcl_curr,
4022 orig_taxable_amt,
4023 orig_taxable_amt_tax_curr,
4024 cal_tax_amt,
4025 cal_tax_amt_tax_curr,
4026 cal_tax_amt_funcl_curr,
4027 orig_tax_amt,
4028 orig_tax_amt_tax_curr,
4029 rec_tax_amt,
4030 rec_tax_amt_tax_curr,
4031 rec_tax_amt_funcl_curr,
4032 nrec_tax_amt,
4033 nrec_tax_amt_tax_curr,
4034 nrec_tax_amt_funcl_curr,
4035 tax_exemption_id,
4036 tax_rate_before_exemption,
4037 tax_rate_name_before_exemption,
4038 exempt_rate_modifier,
4039 exempt_certificate_number,
4040 exempt_reason,
4041 exempt_reason_code,
4042 tax_exception_id,
4043 tax_rate_before_exception,
4044 tax_rate_name_before_exception,
4045 exception_rate,
4046 tax_apportionment_flag,
4047 historical_flag,
4048 taxable_basis_formula,
4049 tax_calculation_formula,
4050 cancel_flag,
4051 purge_flag,
4052 delete_flag,
4053 tax_amt_included_flag,
4054 self_assessed_flag,
4055 overridden_flag,
4056 manually_entered_flag,
4057 freeze_until_overridden_flag,
4058 copied_from_other_doc_flag,
4059 recalc_required_flag,
4060 settlement_flag,
4061 item_dist_changed_flag,
4062 associated_child_frozen_flag,
4063 tax_only_line_flag,
4064 compounding_dep_tax_flag,
4065 last_manual_entry,
4066 tax_provider_id,
4067 record_type_code,
4068 reporting_period_id,
4069 legal_message_appl_2,
4070 legal_message_status,
4071 legal_message_rate,
4072 legal_message_basis,
4073 legal_message_calc,
4074 legal_message_threshold,
4075 legal_message_pos,
4076 legal_message_trn,
4077 legal_message_exmpt,
4078 legal_message_excpt,
4079 tax_regime_template_id,
4080 tax_applicability_result_id,
4081 direct_rate_result_id,
4082 status_result_id,
4083 rate_result_id,
4084 basis_result_id,
4085 thresh_result_id,
4086 calc_result_id,
4087 tax_reg_num_det_result_id,
4088 eval_exmpt_result_id,
4089 eval_excpt_result_id,
4090 enforce_from_natural_acct_flag,
4091 tax_hold_code,
4092 tax_hold_released_code,
4093 prd_total_tax_amt,
4094 prd_total_tax_amt_tax_curr,
4095 prd_total_tax_amt_funcl_curr,
4096 internal_org_location_id,
4097 attribute_category,
4098 attribute1,
4099 attribute2,
4100 attribute3,
4101 attribute4,
4102 attribute5,
4103 attribute6,
4104 attribute7,
4105 attribute8,
4106 attribute9,
4107 attribute10,
4108 attribute11,
4109 attribute12,
4110 attribute13,
4111 attribute14,
4112 attribute15,
4113 global_attribute_category,
4114 global_attribute1,
4115 global_attribute2,
4116 global_attribute3,
4117 global_attribute4,
4118 global_attribute5,
4119 global_attribute6,
4120 global_attribute7,
4121 global_attribute8,
4122 global_attribute9,
4123 global_attribute10,
4124 global_attribute11,
4125 global_attribute12,
4126 global_attribute13,
4127 global_attribute14,
4128 global_attribute15,
4129 numeric1,
4130 numeric2,
4131 numeric3,
4132 numeric4,
4133 numeric5,
4134 numeric6,
4135 numeric7,
4136 numeric8,
4137 numeric9,
4138 numeric10,
4139 char1,
4140 char2,
4141 char3,
4142 char4,
4143 char5,
4144 char6,
4145 char7,
4146 char8,
4147 char9,
4148 char10,
4149 date1,
4150 date2,
4151 date3,
4152 date4,
4153 date5,
4154 date6,
4155 date7,
4156 date8,
4157 date9,
4158 date10,
4159 tax_rate_type,
4160 created_by,
4161 creation_date,
4162 last_updated_by,
4163 last_update_date,
4164 last_update_login,
4165 line_assessable_value,
4166 legal_justification_text1,
4167 legal_justification_text2,
4168 legal_justification_text3,
4169 reporting_currency_code,
4170 trx_line_index,
4171 offset_tax_rate_code,
4172 proration_code,
4173 other_doc_source,
4174 reporting_only_flag,
4175 ctrl_total_line_tx_amt,
4176 sync_with_prvdr_flag,
4177 interface_entity_code,
4178 interface_tax_line_id,
4179 taxing_juris_geography_id,
4180 adjusted_doc_tax_line_id,
4181 object_version_number,
4182 legal_reporting_status,
4183 account_source_tax_rate_id
4184 )
4185 (SELECT
4186 L.tax_line_id,
4187 L.internal_organization_id,
4188 L.application_id,
4189 L.entity_code,
4190 L.event_class_code,
4191 L.event_type_code,
4192 L.trx_id,
4193 L.trx_line_id,
4194 L.trx_level_type,
4195 L.trx_line_number,
4196 L.doc_event_status,
4197 -- L.line_event_status,
4198 L.tax_event_class_code,
4199 L.tax_event_type_code,
4200 L.tax_line_number,
4201 L.content_owner_id,
4202 L.tax_regime_id,
4203 L.tax_regime_code,
4204 L.tax_id,
4205 L.tax,
4206 L.tax_status_id,
4207 L.tax_status_code,
4208 L.tax_rate_id,
4209 L.tax_rate_code,
4210 L.tax_rate,
4211 L.tax_apportionment_line_number,
4212 L.trx_id_level2,
4213 L.trx_id_level3,
4214 L.trx_id_level4,
4215 L.trx_id_level5,
4216 L.trx_id_level6,
4217 L.trx_user_key_level1,
4218 L.trx_user_key_level2,
4219 L.trx_user_key_level3,
4220 L.trx_user_key_level4,
4221 L.trx_user_key_level5,
4222 L.trx_user_key_level6,
4223 L.mrc_tax_line_flag,
4224 G.ledger_id,
4225 G.establishment_id,
4226 G.legal_entity_id,
4227 L.legal_entity_tax_reg_number,
4228 L.hq_estb_reg_number,
4229 L.hq_estb_party_tax_prof_id,
4230 G.currency_conversion_date,
4231 G.currency_conversion_type,
4232 G.currency_conversion_rate,
4233 L.tax_currency_conversion_date,
4234 L.tax_currency_conversion_type,
4235 L.tax_currency_conversion_rate,
4236 L.trx_currency_code,
4237 L.minimum_accountable_unit,
4238 L.precision,
4239 G.trx_number,
4240 G.trx_date,
4241 L.unit_price,
4242 L.line_amt,
4243 L.trx_line_quantity,
4244 L.tax_base_modifier_rate,
4245 L.ref_doc_application_id,
4246 L.ref_doc_entity_code,
4247 L.ref_doc_event_class_code,
4248 L.ref_doc_trx_id,
4249 L.ref_doc_line_id,
4250 L.ref_doc_line_quantity,
4251 L.other_doc_line_amt,
4252 L.other_doc_line_tax_amt,
4253 L.other_doc_line_taxable_amt,
4254 0, -- L.unrounded_taxable_amt
4255 0, -- L.unrounded_tax_amt
4256 L.related_doc_application_id,
4257 L.related_doc_entity_code,
4258 L.related_doc_event_class_code,
4259 L.related_doc_trx_id,
4260 L.related_doc_number,
4261 L.related_doc_date,
4262 L.applied_from_application_id,
4263 L.applied_from_event_class_code,
4264 L.applied_from_entity_code,
4265 L.applied_from_trx_id,
4266 L.applied_from_line_id,
4267 L.applied_from_trx_level_type,
4268 L.applied_from_trx_number,
4269 L.adjusted_doc_application_id,
4270 L.adjusted_doc_entity_code,
4271 L.adjusted_doc_event_class_code,
4272 L.adjusted_doc_trx_id,
4273 L.adjusted_doc_line_id,
4274 L.adjusted_doc_trx_level_type,
4275 L.adjusted_doc_number,
4276 L.adjusted_doc_date,
4277 L.applied_to_application_id,
4278 L.applied_to_event_class_code,
4279 L.applied_to_entity_code,
4280 L.applied_to_trx_id,
4281 L.applied_to_line_id,
4282 L.applied_to_trx_level_type,
4283 L.applied_to_trx_number,
4284 L.summary_tax_line_id,
4285 L.offset_link_to_tax_line_id,
4286 L.offset_flag,
4287 DECODE(L.historical_flag,'Y','Y','N'), -- L.Process_For_Recovery_Flag
4288 L.tax_jurisdiction_id,
4289 L.tax_jurisdiction_code,
4290 L.place_of_supply,
4291 L.place_of_supply_type_code,
4292 L.place_of_supply_result_id,
4293 L.tax_date_rule_id,
4294 L.tax_date,
4295 L.tax_determine_date,
4296 L.tax_point_date,
4297 L.trx_line_date,
4298 L.tax_type_code,
4299 L.tax_code,
4300 L.tax_registration_id,
4301 L.tax_registration_number,
4302 L.registration_party_type,
4303 L.rounding_level_code,
4304 L.rounding_rule_code,
4305 L.rounding_lvl_party_tax_prof_id,
4306 L.rounding_lvl_party_type,
4307 L.compounding_tax_flag,
4308 L.orig_tax_status_id,
4309 L.orig_tax_status_code,
4310 L.orig_tax_rate_id,
4311 L.orig_tax_rate_code,
4312 L.orig_tax_rate,
4313 L.orig_tax_jurisdiction_id,
4314 L.orig_tax_jurisdiction_code,
4315 L.orig_tax_amt_included_flag,
4316 L.orig_self_assessed_flag,
4317 L.tax_currency_code,
4318 0, -- L.tax_amt
4319 0, -- L.tax_amt_tax_curr
4320 0, -- L.tax_amt_funcl_curr ??
4321 0, -- taxable_amt
4322 0, -- taxable_amt_tax_curr
4323 0, -- L.taxable_amt_funcl_curr ??
4324 DECODE(L.orig_taxable_amt, NULL, L.taxable_amt,
4325 L.orig_taxable_amt), -- orig_taxable_amt
4326 DECODE(L.orig_taxable_amt_tax_curr, NULL, L.taxable_amt_tax_curr,
4327 L.orig_taxable_amt_tax_curr), -- orig_taxable_amt_tax_curr
4328 0, -- L.cal_tax_amt
4329 0, -- L.cal_tax_amt_tax_curr
4330 0, -- L.cal_tax_amt_funcl_curr ??
4331 DECODE(L.orig_tax_amt, NULL, L.tax_amt,
4332 L.orig_tax_amt), -- L.orig_tax_amt,
4333 DECODE(L.orig_tax_amt_tax_curr, NULL, L.tax_amt_tax_curr,
4334 L.orig_tax_amt_tax_curr), -- L.orig_tax_amt_tax_curr
4335 0, -- L.rec_tax_amt
4336 0, -- L.rec_tax_amt_tax_curr
4337 0, -- L.rec_tax_amt_funcl_curr ??
4338 0, -- L.nrec_tax_amt
4339 0, -- L.nrec_tax_amt_tax_curr
4340 0, -- L.nrec_tax_amt_funcl_curr ??
4341 L.tax_exemption_id,
4342 L.tax_rate_before_exemption,
4343 L.tax_rate_name_before_exemption,
4344 L.exempt_rate_modifier,
4345 L.exempt_certificate_number,
4346 L.exempt_reason,
4347 L.exempt_reason_code,
4348 L.tax_exception_id,
4349 L.tax_rate_before_exception,
4350 L.tax_rate_name_before_exception,
4351 L.exception_rate,
4352 L.tax_apportionment_flag,
4353 L.historical_flag,
4354 L.taxable_basis_formula,
4355 L.tax_calculation_formula,
4356 'Y', -- DECODE(L.historical_flag, 'Y', 'Y', L.cancel_flag),
4357 L.purge_flag,
4358 L.delete_flag,
4359 L.tax_amt_included_flag,
4360 L.self_assessed_flag,
4361 L.overridden_flag,
4362 L.manually_entered_flag,
4363 L.freeze_until_overridden_flag,
4364 L.copied_from_other_doc_flag,
4365 L.recalc_required_flag,
4366 L.settlement_flag,
4367 L.item_dist_changed_flag,
4368 L.associated_child_frozen_flag,
4369 L.tax_only_line_flag,
4370 L.compounding_dep_tax_flag,
4371 L.last_manual_entry,
4372 L.tax_provider_id,
4373 L.record_type_code,
4374 L.reporting_period_id,
4375 L.legal_message_appl_2,
4376 L.legal_message_status,
4377 L.legal_message_rate,
4378 L.legal_message_basis,
4379 L.legal_message_calc,
4380 L.legal_message_threshold,
4381 L.legal_message_pos,
4382 L.legal_message_trn,
4383 L.legal_message_exmpt,
4384 L.legal_message_excpt,
4385 L.tax_regime_template_id,
4386 L.tax_applicability_result_id,
4387 L.direct_rate_result_id,
4388 L.status_result_id,
4389 L.rate_result_id,
4390 L.basis_result_id,
4391 L.thresh_result_id,
4392 L.calc_result_id,
4393 L.tax_reg_num_det_result_id,
4394 L.eval_exmpt_result_id,
4395 L.eval_excpt_result_id,
4396 L.enforce_from_natural_acct_flag,
4397 NULL, --L.tax_hold_code,
4398 NULL, --L.tax_hold_released_code,
4399 NULL, -- L.prd_total_tax_amt
4400 NULL, -- L.prd_total_tax_amt_tax_curr
4401 NULL, -- L.prd_total_tax_amt_funcl_curr
4402 L.internal_org_location_id,
4403 L.attribute_category,
4404 L.attribute1,
4405 L.attribute2,
4406 L.attribute3,
4407 L.attribute4,
4408 L.attribute5,
4409 L.attribute6,
4410 L.attribute7,
4411 L.attribute8,
4412 L.attribute9,
4413 L.attribute10,
4414 L.attribute11,
4415 L.attribute12,
4416 L.attribute13,
4417 L.attribute14,
4418 L.attribute15,
4419 L.global_attribute_category,
4420 L.global_attribute1,
4421 L.global_attribute2,
4422 L.global_attribute3,
4423 L.global_attribute4,
4424 L.global_attribute5,
4425 L.global_attribute6,
4426 L.global_attribute7,
4427 L.global_attribute8,
4428 L.global_attribute9,
4429 L.global_attribute10,
4430 L.global_attribute11,
4431 L.global_attribute12,
4432 L.global_attribute13,
4433 L.global_attribute14,
4434 L.global_attribute15,
4435 L.numeric1,
4436 L.numeric2,
4437 L.numeric3,
4438 L.numeric4,
4439 L.numeric5,
4440 L.numeric6,
4441 L.numeric7,
4442 L.numeric8,
4443 L.numeric9,
4444 L.numeric10,
4445 L.char1,
4446 L.char2,
4447 L.char3,
4448 L.char4,
4449 L.char5,
4450 L.char6,
4451 L.char7,
4452 L.char8,
4453 L.char9,
4454 L.char10,
4455 L.date1,
4456 L.date2,
4457 L.date3,
4458 L.date4,
4459 L.date5,
4460 L.date6,
4461 L.date7,
4462 L.date8,
4463 L.date9,
4464 L.date10,
4465 L.tax_rate_type,
4466 L.created_by,
4467 L.creation_date,
4468 L.last_updated_by,
4469 L.last_update_date,
4470 L.last_update_login,
4471 L.line_assessable_value,
4472 L.legal_justification_text1,
4473 L.legal_justification_text2,
4474 L.legal_justification_text3,
4475 L.reporting_currency_code,
4476 NULL, -- L.trx_line_index
4477 NULL, -- L.offset_tax_rate_code
4478 NULL, -- L.proration_code
4479 NULL, -- L.other_doc_source
4480 L.reporting_only_flag,
4481 L.ctrl_total_line_tx_amt,
4482 L.sync_with_prvdr_flag,
4483 L.interface_entity_code,
4484 L.interface_tax_line_id,
4485 L.taxing_juris_geography_id,
4486 L.adjusted_doc_tax_line_id,
4487 1,
4488 legal_reporting_status,
4489 L.account_source_tax_rate_id
4490 FROM ZX_LINES L,
4491 zx_lines_det_factors G
4492 WHERE G.application_id = p_event_class_rec.application_id AND
4493 G.entity_code = p_event_class_rec.entity_code AND
4494 G.event_class_code = p_event_class_rec.event_class_code AND
4495 -- G.trx_id = p_event_class_rec.trx_id AND
4496 G.event_id = p_event_class_rec.event_id AND
4497 L.trx_id = G.trx_id AND
4498 L.trx_line_id = G.trx_line_id AND
4499 L.trx_level_type = G.trx_level_type AND
4500 L.event_class_code = G.event_class_code AND
4501 -- L.event_type_code = G.event_type_code AND
4502 L.entity_code = G.entity_code AND
4503 L.application_id = G.application_id AND
4504 -- L.subscriber_id = G.subscriber_id AND
4505 G.line_level_action IN ('DISCARD', 'UNAPPLY_FROM') AND
4506 L.tax_provider_id IS NULL
4507 AND NOT EXISTS
4508 (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
4509 1
4510 FROM zx_detail_tax_lines_gt gt
4511 WHERE gt.application_id = L.application_id
4512 AND gt.entity_code = L.entity_code
4513 AND gt.event_class_code = L.event_class_code
4514 AND gt.trx_id = L.trx_id
4515 AND gt.trx_line_id = L.trx_line_id
4516 AND gt.trx_level_type = L.trx_level_type
4517 AND gt.tax_regime_code = L.tax_regime_code
4518 AND gt.tax = L.tax
4519 AND NVL(gt.tax_apportionment_line_number, -999999) =
4520 NVL(L.tax_apportionment_line_number, -999999)
4521 )
4522 );
4523
4524 IF (g_level_procedure >= g_current_runtime_level ) THEN
4525 FND_LOG.STRING(g_level_procedure,
4526 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_discard_tax_lines',
4527 'p_return_status = ' || p_return_status);
4528 FND_LOG.STRING(g_level_procedure,
4529 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_discard_tax_lines',
4530 'p_error_buffer = ' || p_error_buffer);
4531 FND_LOG.STRING(g_level_procedure,
4532 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_discard_tax_lines.END',
4533 'ZX_TDS_TAX_LINES_POPU_PKG: process_discard_tax_lines(-)');
4534 END IF;
4535
4536 EXCEPTION
4537 WHEN OTHERS THEN
4538 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4539 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4540
4541 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4542 FND_LOG.STRING(g_level_unexpected,
4543 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_discard_tax_lines',
4544 p_error_buffer);
4545 END IF;
4546
4547 END process_discard_tax_lines;
4548
4549 -----------------------------------------------------------------------
4550 -- PUBLIC PROCEDURE
4551 -- process_cancel_trx_lines
4552 --
4553 -- DESCRIPTION
4554 -- This procedure processes cancelled trx lines (line_level_action='CANCEL')
4555 -- It brings all tax lines from zx_lines into detail tax lines global temp table
4556 -- and mark them as cancelled
4557 --
4558 -- CALLED BY
4559 -- ZX_TDS_TAX_LINES_DETM_PKG
4560
4561 PROCEDURE process_cancel_trx_lines(
4562 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
4563 p_return_status OUT NOCOPY VARCHAR2,
4564 p_error_buffer OUT NOCOPY VARCHAR2)
4565
4566 IS
4567 BEGIN
4568
4569 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4570
4571 IF (g_level_procedure >= g_current_runtime_level ) THEN
4572 FND_LOG.STRING(g_level_procedure,
4573 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_trx_lines.BEGIN',
4574 'ZX_TDS_TAX_LINES_POPU_PKG: process_cancel_trx_lines(+)');
4575 END IF;
4576
4577 p_return_status := FND_API.G_RET_STS_SUCCESS;
4578
4579 INSERT INTO zx_detail_tax_lines_gt
4580 ( tax_line_id,
4581 internal_organization_id,
4582 application_id,
4583 entity_code,
4584 event_class_code,
4585 event_type_code,
4586 trx_id,
4587 trx_line_id,
4588 trx_level_type,
4589 trx_line_number,
4590 doc_event_status,
4591 tax_event_class_code,
4592 tax_event_type_code,
4593 tax_line_number,
4594 content_owner_id,
4595 tax_regime_id,
4596 tax_regime_code,
4597 tax_id,
4598 tax,
4599 tax_status_id,
4600 tax_status_code,
4601 tax_rate_id,
4602 tax_rate_code,
4603 tax_rate,
4604 tax_apportionment_line_number,
4605 trx_id_level2,
4606 trx_id_level3,
4607 trx_id_level4,
4608 trx_id_level5,
4609 trx_id_level6,
4610 trx_user_key_level1,
4611 trx_user_key_level2,
4612 trx_user_key_level3,
4613 trx_user_key_level4,
4614 trx_user_key_level5,
4615 trx_user_key_level6,
4616 mrc_tax_line_flag,
4617 ledger_id,
4618 establishment_id,
4619 legal_entity_id,
4620 legal_entity_tax_reg_number,
4621 hq_estb_reg_number,
4622 hq_estb_party_tax_prof_id,
4623 currency_conversion_date,
4624 currency_conversion_type,
4625 currency_conversion_rate,
4626 tax_currency_conversion_date,
4627 tax_currency_conversion_type,
4628 tax_currency_conversion_rate,
4629 trx_currency_code,
4630 minimum_accountable_unit,
4631 precision,
4632 trx_number,
4633 trx_date,
4634 unit_price,
4635 line_amt,
4636 trx_line_quantity,
4637 tax_base_modifier_rate,
4638 ref_doc_application_id,
4639 ref_doc_entity_code,
4640 ref_doc_event_class_code,
4641 ref_doc_trx_id,
4642 ref_doc_line_id,
4643 ref_doc_line_quantity,
4644 other_doc_line_amt,
4645 other_doc_line_tax_amt,
4646 other_doc_line_taxable_amt,
4647 unrounded_taxable_amt,
4648 unrounded_tax_amt,
4649 related_doc_application_id,
4650 related_doc_entity_code,
4651 related_doc_event_class_code,
4652 related_doc_trx_id,
4653 related_doc_number,
4654 related_doc_date,
4655 applied_from_application_id,
4656 applied_from_event_class_code,
4657 applied_from_entity_code,
4658 applied_from_trx_id,
4659 applied_from_line_id,
4660 applied_from_trx_level_type,
4661 applied_from_trx_number,
4662 adjusted_doc_application_id,
4663 adjusted_doc_entity_code,
4664 adjusted_doc_event_class_code,
4665 adjusted_doc_trx_id,
4666 adjusted_doc_line_id,
4667 adjusted_doc_trx_level_type,
4668 adjusted_doc_number,
4669 adjusted_doc_date,
4670 applied_to_application_id,
4671 applied_to_event_class_code,
4672 applied_to_entity_code,
4673 applied_to_trx_id,
4674 applied_to_line_id,
4675 applied_to_trx_number,
4676 summary_tax_line_id,
4677 offset_link_to_tax_line_id,
4678 offset_flag,
4679 process_for_recovery_flag,
4680 tax_jurisdiction_id,
4681 tax_jurisdiction_code,
4682 place_of_supply,
4683 place_of_supply_type_code,
4684 place_of_supply_result_id,
4685 tax_date_rule_id,
4686 tax_date,
4687 tax_determine_date,
4688 tax_point_date,
4689 trx_line_date,
4690 tax_type_code,
4691 tax_code,
4692 tax_registration_id,
4693 tax_registration_number,
4694 registration_party_type,
4695 rounding_level_code,
4696 rounding_rule_code,
4697 rounding_lvl_party_tax_prof_id,
4698 rounding_lvl_party_type,
4699 compounding_tax_flag,
4700 orig_tax_status_id,
4701 orig_tax_status_code,
4702 orig_tax_rate_id,
4703 orig_tax_rate_code,
4704 orig_tax_rate,
4705 orig_tax_jurisdiction_id,
4706 orig_tax_jurisdiction_code,
4707 orig_tax_amt_included_flag,
4708 orig_self_assessed_flag,
4709 tax_currency_code,
4710 tax_amt,
4711 tax_amt_tax_curr,
4712 tax_amt_funcl_curr,
4713 taxable_amt,
4714 taxable_amt_tax_curr,
4715 taxable_amt_funcl_curr,
4716 orig_taxable_amt,
4717 orig_taxable_amt_tax_curr,
4718 cal_tax_amt,
4719 cal_tax_amt_tax_curr,
4720 cal_tax_amt_funcl_curr,
4721 orig_tax_amt,
4722 orig_tax_amt_tax_curr,
4723 rec_tax_amt,
4724 rec_tax_amt_tax_curr,
4725 rec_tax_amt_funcl_curr,
4726 nrec_tax_amt,
4727 nrec_tax_amt_tax_curr,
4728 nrec_tax_amt_funcl_curr,
4729 tax_exemption_id,
4730 tax_rate_before_exemption,
4731 tax_rate_name_before_exemption,
4732 exempt_rate_modifier,
4733 exempt_certificate_number,
4734 exempt_reason,
4735 exempt_reason_code,
4736 tax_exception_id,
4737 tax_rate_before_exception,
4738 tax_rate_name_before_exception,
4739 exception_rate,
4740 tax_apportionment_flag,
4741 historical_flag,
4742 taxable_basis_formula,
4743 tax_calculation_formula,
4744 cancel_flag,
4745 purge_flag,
4746 delete_flag,
4747 tax_amt_included_flag,
4748 self_assessed_flag,
4749 overridden_flag,
4750 manually_entered_flag,
4751 freeze_until_overridden_flag,
4752 copied_from_other_doc_flag,
4753 recalc_required_flag,
4754 settlement_flag,
4755 item_dist_changed_flag,
4756 associated_child_frozen_flag,
4757 tax_only_line_flag,
4758 compounding_dep_tax_flag,
4759 last_manual_entry,
4760 tax_provider_id,
4761 record_type_code,
4762 reporting_period_id,
4763 legal_message_appl_2,
4764 legal_message_status,
4765 legal_message_rate,
4766 legal_message_basis,
4767 legal_message_calc,
4768 legal_message_threshold,
4769 legal_message_pos,
4770 legal_message_trn,
4771 legal_message_exmpt,
4772 legal_message_excpt,
4773 tax_regime_template_id,
4774 tax_applicability_result_id,
4775 direct_rate_result_id,
4776 status_result_id,
4777 rate_result_id,
4778 basis_result_id,
4779 thresh_result_id,
4780 calc_result_id,
4781 tax_reg_num_det_result_id,
4782 eval_exmpt_result_id,
4783 eval_excpt_result_id,
4784 enforce_from_natural_acct_flag,
4785 tax_hold_code,
4786 tax_hold_released_code,
4787 prd_total_tax_amt,
4788 prd_total_tax_amt_tax_curr,
4789 prd_total_tax_amt_funcl_curr,
4790 internal_org_location_id,
4791 attribute_category,
4792 attribute1,
4793 attribute2,
4794 attribute3,
4795 attribute4,
4796 attribute5,
4797 attribute6,
4798 attribute7,
4799 attribute8,
4800 attribute9,
4801 attribute10,
4802 attribute11,
4803 attribute12,
4804 attribute13,
4805 attribute14,
4806 attribute15,
4807 global_attribute_category,
4808 global_attribute1,
4809 global_attribute2,
4810 global_attribute3,
4811 global_attribute4,
4812 global_attribute5,
4813 global_attribute6,
4814 global_attribute7,
4815 global_attribute8,
4816 global_attribute9,
4817 global_attribute10,
4818 global_attribute11,
4819 global_attribute12,
4820 global_attribute13,
4821 global_attribute14,
4822 global_attribute15,
4823 numeric1,
4824 numeric2,
4825 numeric3,
4826 numeric4,
4827 numeric5,
4828 numeric6,
4829 numeric7,
4830 numeric8,
4831 numeric9,
4832 numeric10,
4833 char1,
4834 char2,
4835 char3,
4836 char4,
4837 char5,
4838 char6,
4839 char7,
4840 char8,
4841 char9,
4842 char10,
4843 date1,
4844 date2,
4845 date3,
4846 date4,
4847 date5,
4848 date6,
4849 date7,
4850 date8,
4851 date9,
4852 date10,
4853 tax_rate_type,
4854 created_by,
4855 creation_date,
4856 last_updated_by,
4857 last_update_date,
4858 last_update_login,
4859 line_assessable_value,
4860 legal_justification_text1,
4861 legal_justification_text2,
4862 legal_justification_text3,
4863 reporting_currency_code,
4864 trx_line_index,
4865 offset_tax_rate_code,
4866 proration_code,
4867 other_doc_source,
4868 reporting_only_flag,
4869 ctrl_total_line_tx_amt,
4870 sync_with_prvdr_flag,
4871 interface_entity_code,
4872 interface_tax_line_id,
4873 taxing_juris_geography_id,
4874 adjusted_doc_tax_line_id,
4875 object_version_number,
4876 legal_reporting_status,
4877 account_source_tax_rate_id
4878 )
4879 (SELECT
4880 L.tax_line_id,
4881 L.internal_organization_id,
4882 L.application_id,
4883 L.entity_code,
4884 L.event_class_code,
4885 L.event_type_code,
4886 L.trx_id,
4887 L.trx_line_id,
4888 L.trx_level_type,
4889 L.trx_line_number,
4890 L.doc_event_status,
4891 L.tax_event_class_code,
4892 L.tax_event_type_code,
4893 L.tax_line_number,
4894 L.content_owner_id,
4895 L.tax_regime_id,
4896 L.tax_regime_code,
4897 L.tax_id,
4898 L.tax,
4899 L.tax_status_id,
4900 L.tax_status_code,
4901 L.tax_rate_id,
4902 L.tax_rate_code,
4903 L.tax_rate,
4904 L.tax_apportionment_line_number,
4905 L.trx_id_level2,
4906 L.trx_id_level3,
4907 L.trx_id_level4,
4908 L.trx_id_level5,
4909 L.trx_id_level6,
4910 L.trx_user_key_level1,
4911 L.trx_user_key_level2,
4912 L.trx_user_key_level3,
4913 L.trx_user_key_level4,
4914 L.trx_user_key_level5,
4915 L.trx_user_key_level6,
4916 L.mrc_tax_line_flag,
4917 L.ledger_id,
4918 L.establishment_id,
4919 L.legal_entity_id,
4920 L.legal_entity_tax_reg_number,
4921 L.hq_estb_reg_number,
4922 L.hq_estb_party_tax_prof_id,
4923 L.currency_conversion_date,
4924 L.currency_conversion_type,
4925 L.currency_conversion_rate,
4926 L.tax_currency_conversion_date,
4927 L.tax_currency_conversion_type,
4928 L.tax_currency_conversion_rate,
4929 L.trx_currency_code,
4930 L.minimum_accountable_unit,
4931 L.precision,
4932 G.trx_number,
4933 L.trx_date,
4934 L.unit_price,
4935 L.line_amt,
4936 L.trx_line_quantity,
4937 L.tax_base_modifier_rate,
4938 L.ref_doc_application_id,
4939 L.ref_doc_entity_code,
4940 L.ref_doc_event_class_code,
4941 L.ref_doc_trx_id,
4942 L.ref_doc_line_id,
4943 L.ref_doc_line_quantity,
4944 L.other_doc_line_amt,
4945 L.other_doc_line_tax_amt,
4946 L.other_doc_line_taxable_amt,
4947 L.unrounded_taxable_amt,
4948 0, -- L.unrounded_tax_amt,
4949 L.related_doc_application_id,
4950 L.related_doc_entity_code,
4951 L.related_doc_event_class_code,
4952 L.related_doc_trx_id,
4953 L.related_doc_number,
4954 L.related_doc_date,
4955 L.applied_from_application_id,
4956 L.applied_from_event_class_code,
4957 L.applied_from_entity_code,
4958 L.applied_from_trx_id,
4959 L.applied_from_line_id,
4960 L.applied_from_trx_level_type,
4961 L.applied_from_trx_number,
4962 L.adjusted_doc_application_id,
4963 L.adjusted_doc_entity_code,
4964 L.adjusted_doc_event_class_code,
4965 L.adjusted_doc_trx_id,
4966 L.adjusted_doc_line_id,
4967 L.adjusted_doc_trx_level_type,
4968 L.adjusted_doc_number,
4969 L.adjusted_doc_date,
4970 L.applied_to_application_id,
4971 L.applied_to_event_class_code,
4972 L.applied_to_entity_code,
4973 L.applied_to_trx_id,
4974 L.applied_to_line_id,
4975 L.applied_to_trx_number,
4976 L.summary_tax_line_id,
4977 L.offset_link_to_tax_line_id,
4978 L.offset_flag,
4979 DECODE(L.reporting_only_flag, 'N', 'Y', 'N'), -- L.process_for_recovery_flag,
4980 L.tax_jurisdiction_id,
4981 L.tax_jurisdiction_code,
4982 L.place_of_supply,
4983 L.place_of_supply_type_code,
4984 L.place_of_supply_result_id,
4985 L.tax_date_rule_id,
4986 L.tax_date,
4987 L.tax_determine_date,
4988 L.tax_point_date,
4989 L.trx_line_date,
4990 L.tax_type_code,
4991 L.tax_code,
4992 L.tax_registration_id,
4993 L.tax_registration_number,
4994 L.registration_party_type,
4995 L.rounding_level_code,
4996 L.rounding_rule_code,
4997 L.rounding_lvl_party_tax_prof_id,
4998 L.rounding_lvl_party_type,
4999 L.compounding_tax_flag,
5000 L.orig_tax_status_id,
5001 L.orig_tax_status_code,
5002 L.orig_tax_rate_id,
5003 L.orig_tax_rate_code,
5004 L.orig_tax_rate,
5005 L.orig_tax_jurisdiction_id,
5006 L.orig_tax_jurisdiction_code,
5007 L.orig_tax_amt_included_flag,
5008 L.orig_self_assessed_flag,
5009 L.tax_currency_code,
5010 0, -- L.tax_amt,
5011 0, -- L.tax_amt_tax_curr,
5012 0, -- L.tax_amt_funcl_curr,
5013 taxable_amt,
5014 taxable_amt_tax_curr,
5015 L.taxable_amt_funcl_curr,
5016 L.orig_taxable_amt,
5017 L.orig_taxable_amt_tax_curr,
5018 L.cal_tax_amt,
5019 L.cal_tax_amt_tax_curr,
5020 L.cal_tax_amt_funcl_curr,
5021 L.orig_tax_amt,
5022 L.orig_tax_amt_tax_curr,
5023 L.rec_tax_amt,
5024 L.rec_tax_amt_tax_curr,
5025 L.rec_tax_amt_funcl_curr,
5026 L.nrec_tax_amt,
5027 L.nrec_tax_amt_tax_curr,
5028 L.nrec_tax_amt_funcl_curr,
5029 L.tax_exemption_id,
5030 L.tax_rate_before_exemption,
5031 L.tax_rate_name_before_exemption,
5032 L.exempt_rate_modifier,
5033 L.exempt_certificate_number,
5034 L.exempt_reason,
5035 L.exempt_reason_code,
5036 L.tax_exception_id,
5037 L.tax_rate_before_exception,
5038 L.tax_rate_name_before_exception,
5039 L.exception_rate,
5040 L.tax_apportionment_flag,
5041 L.historical_flag,
5042 L.taxable_basis_formula,
5043 L.tax_calculation_formula,
5044 'Y', -- L.cancel_flag,
5045 L.purge_flag,
5046 L.delete_flag,
5047 L.tax_amt_included_flag,
5048 L.self_assessed_flag,
5049 L.overridden_flag,
5050 L.manually_entered_flag,
5051 L.freeze_until_overridden_flag,
5052 L.copied_from_other_doc_flag,
5053 L.recalc_required_flag,
5054 L.settlement_flag,
5055 L.item_dist_changed_flag,
5056 L.associated_child_frozen_flag,
5057 L.tax_only_line_flag,
5058 L.compounding_dep_tax_flag,
5059 L.last_manual_entry,
5060 L.tax_provider_id,
5061 L.record_type_code,
5062 L.reporting_period_id,
5063 L.legal_message_appl_2,
5064 L.legal_message_status,
5065 L.legal_message_rate,
5066 L.legal_message_basis,
5067 L.legal_message_calc,
5068 L.legal_message_threshold,
5069 L.legal_message_pos,
5070 L.legal_message_trn,
5071 L.legal_message_exmpt,
5072 L.legal_message_excpt,
5073 L.tax_regime_template_id,
5074 L.tax_applicability_result_id,
5075 L.direct_rate_result_id,
5076 L.status_result_id,
5077 L.rate_result_id,
5078 L.basis_result_id,
5079 L.thresh_result_id,
5080 L.calc_result_id,
5081 L.tax_reg_num_det_result_id,
5082 L.eval_exmpt_result_id,
5083 L.eval_excpt_result_id,
5084 L.enforce_from_natural_acct_flag,
5085 L.tax_hold_code,
5086 L.tax_hold_released_code,
5087 L.prd_total_tax_amt,
5088 L.prd_total_tax_amt_tax_curr,
5089 L.prd_total_tax_amt_funcl_curr,
5090 L.internal_org_location_id,
5091 L.attribute_category,
5092 L.attribute1,
5093 L.attribute2,
5094 L.attribute3,
5095 L.attribute4,
5096 L.attribute5,
5097 L.attribute6,
5098 L.attribute7,
5099 L.attribute8,
5100 L.attribute9,
5101 L.attribute10,
5102 L.attribute11,
5103 L.attribute12,
5104 L.attribute13,
5105 L.attribute14,
5106 L.attribute15,
5107 L.global_attribute_category,
5108 L.global_attribute1,
5109 L.global_attribute2,
5110 L.global_attribute3,
5111 L.global_attribute4,
5112 L.global_attribute5,
5113 L.global_attribute6,
5114 L.global_attribute7,
5115 L.global_attribute8,
5116 L.global_attribute9,
5117 L.global_attribute10,
5118 L.global_attribute11,
5119 L.global_attribute12,
5120 L.global_attribute13,
5121 L.global_attribute14,
5122 L.global_attribute15,
5123 L.numeric1,
5124 L.numeric2,
5125 L.numeric3,
5126 L.numeric4,
5127 L.numeric5,
5128 L.numeric6,
5129 L.numeric7,
5130 L.numeric8,
5131 L.numeric9,
5132 L.numeric10,
5133 L.char1,
5134 L.char2,
5135 L.char3,
5136 L.char4,
5137 L.char5,
5138 L.char6,
5139 L.char7,
5140 L.char8,
5141 L.char9,
5142 L.char10,
5143 L.date1,
5144 L.date2,
5145 L.date3,
5146 L.date4,
5147 L.date5,
5148 L.date6,
5149 L.date7,
5150 L.date8,
5151 L.date9,
5152 L.date10,
5153 L.tax_rate_type,
5154 L.created_by,
5155 L.creation_date,
5156 L.last_updated_by,
5157 L.last_update_date,
5158 L.last_update_login,
5159 L.line_assessable_value,
5160 L.legal_justification_text1,
5161 L.legal_justification_text2,
5162 L.legal_justification_text3,
5163 L.reporting_currency_code,
5164 L.trx_line_index,
5165 L.offset_tax_rate_code,
5166 L.proration_code,
5167 L.other_doc_source,
5168 L.reporting_only_flag,
5169 L.ctrl_total_line_tx_amt,
5170 L.sync_with_prvdr_flag,
5171 L.interface_entity_code,
5172 L.interface_tax_line_id,
5173 L.taxing_juris_geography_id,
5174 L.adjusted_doc_tax_line_id,
5175 1,
5176 L.legal_reporting_status,
5177 L.account_source_tax_rate_id
5178 FROM ZX_LINES L,
5179 zx_lines_det_factors G
5180 WHERE G.application_id = p_event_class_rec.application_id AND
5181 G.entity_code = p_event_class_rec.entity_code AND
5182 G.event_class_code = p_event_class_rec.event_class_code AND
5183 -- G.trx_id = p_event_class_rec.trx_id AND
5184 G.event_id = p_event_class_rec.event_id AND
5185 L.trx_id = G.trx_id AND
5186 L.trx_line_id = G.trx_line_id AND
5187 L.trx_level_type = G.trx_level_type AND
5188 L.event_class_code = G.event_class_code AND
5189 -- L.event_type_code = G.event_type_code AND
5190 L.entity_code = G.entity_code AND
5191 L.application_id = G.application_id AND
5192 -- L.subscriber_id = G.subscriber_id AND
5193 G.line_level_action = 'CANCEL' AND
5194 L.tax_provider_id IS NULL
5195
5196 );
5197
5198 IF (g_level_procedure >= g_current_runtime_level ) THEN
5199 FND_LOG.STRING(g_level_procedure,
5200 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_trx_lines',
5201 'p_return_status = ' || p_return_status);
5202 FND_LOG.STRING(g_level_procedure,
5203 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_trx_lines',
5204 'p_error_buffer = ' || p_error_buffer);
5205 FND_LOG.STRING(g_level_procedure,
5206 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_trx_lines.END',
5207 'ZX_TDS_TAX_LINES_POPU_PKG: process_cancel_trx_lines(-)');
5208 END IF;
5209
5210 EXCEPTION
5211 WHEN OTHERS THEN
5212 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5213 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
5214
5215 IF (g_level_unexpected >= g_current_runtime_level ) THEN
5216 FND_LOG.STRING(g_level_unexpected,
5217 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_trx_lines',
5218 p_error_buffer);
5219 END IF;
5220
5221 END process_cancel_trx_lines;
5222
5223 -----------------------------------------------------------------------
5224 -- PUBLIC PROCEDURE
5225 -- process_tax_tolerance
5226 --
5227 -- DESCRIPTION
5228 -- This procedure determines tax hold code and tax hold released code for
5229 -- each tax line that tax amount tax currency has been overridden by
5230 -- the users
5231 --
5232 -- CALLED BY
5233 -- ZX_TDS_TAX_LINES_DETM_PKG
5234
5235 PROCEDURE process_tax_tolerance(
5236 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
5237 p_return_status OUT NOCOPY VARCHAR2,
5238 p_error_buffer OUT NOCOPY VARCHAR2)
5239
5240 IS
5241 l_tax_low_boundary NUMBER;
5242 l_tax_high_boundary NUMBER;
5243 l_count NUMBER;
5244 l_tax_id ZX_TAXES_B.TAX_ID%TYPE;
5245 l_tax_precision ZX_TAXES_B.TAX_PRECISION%TYPE;
5246 l_tax_min_acct_unit ZX_TAXES_B.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
5247 l_tax_line_id_tbl ZX_TDS_TAX_ROUNDING_PKG.TAX_ID_TBL;
5248 l_tax_amt_tax_curr_tbl ZX_TDS_TAX_ROUNDING_PKG.TAX_AMT_TAX_CURR_TBL;
5249 l_tax_id_tbl ZX_TDS_TAX_ROUNDING_PKG.TAX_ID_TBL;
5250 l_rounding_rule_tbl ZX_TDS_TAX_ROUNDING_PKG.ROUNDING_RULE_TBL;
5251 l_tax_hold_released_code_tbl TAX_HOLD_RELEASED_CODE_TBL;
5252 l_tax_hold_code_tbl TAX_HOLD_CODE_TBL;
5253 l_orig_tax_amt_tax_curr_tbl ORIG_TAX_AMT_TAX_CURR_TBL;
5254
5255 CURSOR get_tax_tolerance_csr
5256 IS
5257 SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5258 tax_line_id,
5259 orig_tax_amt_tax_curr,
5260 tax_amt_tax_curr,
5261 tax_id,
5262 tax_hold_released_code,
5263 Rounding_Rule_Code
5264 FROM ZX_DETAIL_TAX_LINES_GT
5265 WHERE application_id = p_event_class_rec.application_id
5266 AND entity_code = p_event_class_rec.entity_code
5267 AND event_class_code = p_event_class_rec.event_class_code
5268 AND trx_id = p_event_class_rec.trx_id
5269 AND tax_event_type_code = 'OVERRIDE_TAX'
5270 AND offset_link_to_tax_line_id IS NULL
5271 AND orig_tax_amt_tax_curr IS NOT NULL
5272 AND mrc_tax_line_flag = 'N';
5273
5274 -- Start Bugfix: 5617541
5275
5276 CURSOR get_tax_tol_ctrl_hdr_amt_csr
5277 IS
5278 SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5279 tax_line.tax_line_id,
5280 tax_line.orig_tax_amt_tax_curr,
5281 tax_line.tax_amt_tax_curr,
5282 tax_line.tax_id,
5283 tax_line.tax_hold_released_code,
5284 tax_line.Rounding_Rule_Code
5285 FROM ZX_DETAIL_TAX_LINES_GT tax_line,
5286 zx_lines_det_factors trx_line
5287 WHERE tax_line.application_id = p_event_class_rec.application_id
5288 AND tax_line.entity_code = p_event_class_rec.entity_code
5289 AND tax_line.event_class_code = p_event_class_rec.event_class_code
5290 AND tax_line.trx_id = p_event_class_rec.trx_id
5291 AND tax_line.application_id = trx_line.application_id
5292 AND tax_line.event_class_code = trx_line.event_class_code
5293 AND tax_line.entity_code = trx_line.entity_code
5294 AND tax_line.trx_id = trx_line.trx_id
5295 AND tax_line.trx_line_id = trx_line.trx_line_id
5296 AND tax_line.trx_level_type = trx_line.trx_level_type
5297 AND trx_line.ctrl_total_hdr_tx_amt IS NOT NULL
5298 AND tax_line.offset_link_to_tax_line_id IS NULL
5299 AND tax_line.orig_tax_amt IS NOT NULL
5300 AND tax_line.mrc_tax_line_flag = 'N';
5301
5302 CURSOR get_tax_tol_ctrl_line_amt_csr
5303 IS
5304 SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5305 tax_line_id,
5306 orig_tax_amt_tax_curr,
5307 tax_amt_tax_curr,
5308 tax_id,
5309 tax_hold_released_code,
5310 Rounding_Rule_Code
5311 FROM ZX_DETAIL_TAX_LINES_GT
5312 WHERE application_id = p_event_class_rec.application_id
5313 AND entity_code = p_event_class_rec.entity_code
5314 AND event_class_code = p_event_class_rec.event_class_code
5315 AND trx_id = p_event_class_rec.trx_id
5316 AND ctrl_total_line_tx_amt IS NOT NULL
5317 AND offset_link_to_tax_line_id IS NULL
5318 AND orig_tax_amt IS NOT NULL
5319 AND mrc_tax_line_flag = 'N';
5320
5321 -- End Bugfix: 5617541
5322
5323 -- bug 5684123
5324 CURSOR get_tax_tolerance_upd_csr
5325 IS
5326 SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5327 tax_line_id,
5328 orig_tax_amt_tax_curr,
5329 tax_amt_tax_curr,
5330 tax_id,
5331 tax_hold_released_code,
5332 Rounding_Rule_Code
5333 FROM zx_detail_tax_lines_gt gt
5334 WHERE gt.application_id = p_event_class_rec.application_id
5335 AND gt.entity_code = p_event_class_rec.entity_code
5336 AND gt.event_class_code = p_event_class_rec.event_class_code
5337 AND (gt.tax_event_type_code = 'UPDATE' AND
5338 gt.last_manual_entry IS NOT NULL AND
5339 NVL(gt.manually_entered_flag, 'N') <> 'Y'
5340 )
5341 AND gt.offset_link_to_tax_line_id IS NULL
5342 AND gt.orig_tax_amt_tax_curr IS NOT NULL
5343 AND gt.mrc_tax_line_flag = 'N'
5344 AND EXISTS
5345 (SELECT 1
5346 FROM zx_lines_det_factors line
5347 WHERE line.application_id = p_event_class_rec.application_id
5348 AND line.entity_code = p_event_class_rec.entity_code
5349 AND line.event_class_code = p_event_class_rec.event_class_code
5350 AND line.event_id = p_event_class_rec.event_id
5351 AND line.trx_id = gt.trx_id
5352 AND line.trx_line_id = gt.trx_line_id
5353 AND line.trx_level_type = gt.trx_level_type
5354
5355 );
5356
5357 -- bug 6634198
5358 --
5359 CURSOR get_tax_tolerance_import_csr
5360 IS
5361 SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5362 tax_line_id,
5363 orig_tax_amt_tax_curr,
5364 tax_amt_tax_curr,
5365 tax_id,
5366 tax_hold_released_code,
5367 Rounding_Rule_Code
5368 FROM zx_detail_tax_lines_gt gt
5369 WHERE gt.application_id = p_event_class_rec.application_id
5370 AND gt.entity_code = p_event_class_rec.entity_code
5371 AND gt.event_class_code = p_event_class_rec.event_class_code
5372 AND (gt.tax_event_type_code = 'CREATE' AND
5373 gt.last_manual_entry = 'TAX_AMOUNT' AND
5374 NVL(gt.manually_entered_flag, 'N') = 'Y'
5375 )
5376 AND gt.offset_link_to_tax_line_id IS NULL
5377 AND gt.orig_tax_amt_tax_curr IS NOT NULL
5378 AND gt.mrc_tax_line_flag = 'N'
5379 AND EXISTS
5380 (SELECT 1
5381 FROM zx_transaction_lines_gt lines_gt
5382 WHERE lines_gt.application_id = p_event_class_rec.application_id
5383 AND lines_gt.entity_code = p_event_class_rec.entity_code
5384 AND lines_gt.event_class_code = p_event_class_rec.event_class_code
5385 AND lines_gt.trx_id = gt.trx_id
5386 AND lines_gt.trx_line_id = gt.trx_line_id
5387 AND lines_gt.trx_level_type = gt.trx_level_type
5388 AND lines_gt.line_level_action IN ('CREATE_WITH_TAX', 'LINE_INFO_TAX_ONLY')
5389 );
5390
5391 BEGIN
5392
5393 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5394
5395 IF (g_level_statement >= g_current_runtime_level ) THEN
5396 FND_LOG.STRING(g_level_statement,
5397 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance.BEGIN',
5398 'ZX_TDS_TAX_LINES_POPU_PKG: process_tax_tolerance(+)');
5399 END IF;
5400
5401 p_return_status := FND_API.G_RET_STS_SUCCESS;
5402
5403 -- Start Bugfix: 5617541
5404 -- bug 5684123
5405 IF p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX'
5406 THEN
5407 OPEN get_tax_tolerance_csr;
5408 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_ctrl_total_line_tx_amt_flg = 'Y'
5409 THEN
5410 OPEN get_tax_tol_ctrl_line_amt_csr;
5411 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_ctrl_total_hdr_tx_amt_flg = 'Y'
5412 THEN
5413 OPEN get_tax_tol_ctrl_hdr_amt_csr;
5414 ELSIF p_event_class_rec.tax_event_type_code = 'UPDATE'
5415 THEN
5416 OPEN get_tax_tolerance_upd_csr;
5417 ELSIF p_event_class_rec.tax_event_type_code = 'CREATE'
5418 THEN
5419 OPEN get_tax_tolerance_import_csr;
5420 END IF;
5421
5422 LOOP
5423 IF p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX' THEN
5424 FETCH get_tax_tolerance_csr BULK COLLECT INTO
5425 l_tax_line_id_tbl,
5426 l_orig_tax_amt_tax_curr_tbl,
5427 l_tax_amt_tax_curr_tbl,
5428 l_tax_id_tbl,
5429 l_tax_hold_released_code_tbl,
5430 l_rounding_rule_tbl
5431 LIMIT C_LINES_PER_COMMIT;
5432 ELSE
5433 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_ctrl_total_line_tx_amt_flg = 'Y' THEN
5434 FETCH get_tax_tol_ctrl_line_amt_csr BULK COLLECT INTO
5435 l_tax_line_id_tbl,
5436 l_orig_tax_amt_tax_curr_tbl,
5437 l_tax_amt_tax_curr_tbl,
5438 l_tax_id_tbl,
5439 l_tax_hold_released_code_tbl,
5440 l_rounding_rule_tbl
5441 LIMIT C_LINES_PER_COMMIT;
5442 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_ctrl_total_hdr_tx_amt_flg = 'Y' THEN
5443 FETCH get_tax_tol_ctrl_hdr_amt_csr BULK COLLECT INTO
5444 l_tax_line_id_tbl,
5445 l_orig_tax_amt_tax_curr_tbl,
5446 l_tax_amt_tax_curr_tbl,
5447 l_tax_id_tbl,
5448 l_tax_hold_released_code_tbl,
5449 l_rounding_rule_tbl
5450 LIMIT C_LINES_PER_COMMIT;
5451 ELSIF p_event_class_rec.tax_event_type_code = 'UPDATE' THEN
5452 FETCH get_tax_tolerance_upd_csr BULK COLLECT INTO
5453 l_tax_line_id_tbl,
5454 l_orig_tax_amt_tax_curr_tbl,
5455 l_tax_amt_tax_curr_tbl,
5456 l_tax_id_tbl,
5457 l_tax_hold_released_code_tbl,
5458 l_rounding_rule_tbl
5459 LIMIT C_LINES_PER_COMMIT;
5460 ELSIF p_event_class_rec.tax_event_type_code = 'CREATE' THEN
5461 FETCH get_tax_tolerance_import_csr BULK COLLECT INTO
5462 l_tax_line_id_tbl,
5463 l_orig_tax_amt_tax_curr_tbl,
5464 l_tax_amt_tax_curr_tbl,
5465 l_tax_id_tbl,
5466 l_tax_hold_released_code_tbl,
5467 l_rounding_rule_tbl
5468 LIMIT C_LINES_PER_COMMIT;
5469 END IF;
5470 END IF;
5471
5472 -- End Bugfix: 5617541
5473
5474
5475 l_count := l_orig_tax_amt_tax_curr_tbl.COUNT;
5476
5477 IF (g_level_statement >= g_current_runtime_level ) THEN
5478 FND_LOG.STRING(g_level_statement,
5479 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5480 'number of rows fetched = ' || to_char(l_count));
5481 END IF;
5482
5483 IF l_count > 0 THEN
5484
5485 FOR i IN 1.. l_count LOOP
5486
5487 --
5488 -- determine tax hold code
5489 --
5490 l_tax_hold_code_tbl(i) := NULL;
5491 l_tax_id := l_tax_id_tbl(i);
5492 l_tax_precision := ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).tax_precision;
5493 l_tax_min_acct_unit := ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).minimum_accountable_unit;
5494
5495 --
5496 -- check tax tolerance
5497 --
5498 IF p_event_class_rec.tax_tolerance IS NOT NULL THEN
5499 l_tax_low_boundary:= ZX_TDS_TAX_ROUNDING_PKG.ROUND_TAX(
5500 (1 - p_event_class_rec.tax_tolerance / 100) * l_orig_tax_amt_tax_curr_tbl(i),
5501 l_rounding_rule_tbl(i),
5502 l_tax_min_acct_unit,
5503 l_tax_precision,
5504 p_return_status,
5505 p_error_buffer);
5506
5507 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5508 EXIT;
5509 END IF;
5510
5511 l_tax_high_boundary:= ZX_TDS_TAX_ROUNDING_PKG.ROUND_TAX(
5512 (1 + p_event_class_rec.tax_tolerance / 100) * l_orig_tax_amt_tax_curr_tbl(i),
5513 l_rounding_rule_tbl(i),
5514 l_tax_min_acct_unit,
5515 l_tax_precision,
5516 p_return_status,
5517 p_error_buffer);
5518 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5519 EXIT;
5520 END IF;
5521
5522 IF (ABS(l_tax_amt_tax_curr_tbl(i)) < ABS(l_tax_low_boundary) OR
5523 ABS(l_tax_amt_tax_curr_tbl(i)) > ABS(l_tax_high_boundary)) THEN
5524 l_tax_hold_code_tbl(i) := ZX_TDS_CALC_SERVICES_PUB_PKG.g_tax_variance_hold_val;
5525 END IF;
5526
5527 END IF;
5528
5529 --
5530 -- check tax tolerance amount range
5531 --
5532 -- bug fix 3495260
5533 IF p_event_class_rec.tax_tol_amt_range IS NOT NULL THEN
5534 IF ABS( l_tax_amt_tax_curr_tbl(i) -
5535 l_orig_tax_amt_tax_curr_tbl(i)) >
5536 p_event_class_rec.tax_tol_amt_range THEN
5537 IF l_tax_hold_code_tbl(i) IS NULL THEN
5538 l_tax_hold_code_tbl(i) := ZX_TDS_CALC_SERVICES_PUB_PKG.g_tax_amt_range_hold_val;
5539 ELSE
5540 l_tax_hold_code_tbl(i) := ZX_TDS_CALC_SERVICES_PUB_PKG.g_tax_variance_hold_val
5541 + ZX_TDS_CALC_SERVICES_PUB_PKG.g_tax_amt_range_hold_val;
5542 END IF;
5543 END IF; -- abs
5544 END IF; --tax_tol_amt_range
5545
5546 --
5547 -- determine tax hold released code
5548 --
5549 -- bug fix 3495260
5550 l_tax_hold_released_code_tbl(i) := BITAND( nvl(l_tax_hold_code_tbl(i), 0),
5551 nvl(l_tax_hold_released_code_tbl(i), 0) ) ;
5552
5553 END LOOP; -- end FOR LOOP
5554
5555 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5556 EXIT;
5557 END IF;
5558
5559 --
5560 -- bulk update the current rows processed
5561 -- before fetch the next set of rows
5562 --
5563 IF (g_level_statement >= g_current_runtime_level ) THEN
5564 FND_LOG.STRING(g_level_statement,
5565 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5566 'update ZX_DETAIL_TAX_LINES_GT with tax hold info');
5567 END IF;
5568
5569 FORALL i IN 1 .. l_count
5570 UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5571 ZX_DETAIL_TAX_LINES_GT
5572 SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), 0),
5573 tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), 0)
5574 WHERE tax_line_id = l_tax_line_id_tbl(i);
5575
5576 ELSE
5577 --
5578 -- no more records to process
5579 --
5580 -- Start Bugfix: 5617541
5581 IF p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX' THEN
5582 CLOSE get_tax_tolerance_csr;
5583 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_ctrl_total_line_tx_amt_flg = 'Y' THEN
5584 CLOSE get_tax_tol_ctrl_line_amt_csr;
5585 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_ctrl_total_hdr_tx_amt_flg = 'Y' THEN
5586 CLOSE get_tax_tol_ctrl_hdr_amt_csr;
5587 ELSIF p_event_class_rec.tax_event_type_code = 'UPDATE' THEN
5588 CLOSE get_tax_tolerance_upd_csr;
5589 ELSIF p_event_class_rec.tax_event_type_code = 'CREATE' THEN
5590 CLOSE get_tax_tolerance_import_csr;
5591 END IF;
5592 -- End Bugfix: 5617541
5593 EXIT;
5594 END IF;
5595 END LOOP;
5596
5597 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5598 -- Start Bugfix: 5617541
5599 IF p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX' THEN
5600 CLOSE get_tax_tolerance_csr;
5601 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_ctrl_total_line_tx_amt_flg = 'Y' THEN
5602 CLOSE get_tax_tol_ctrl_line_amt_csr;
5603 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_ctrl_total_hdr_tx_amt_flg = 'Y' THEN
5604 CLOSE get_tax_tol_ctrl_hdr_amt_csr;
5605 ELSIF p_event_class_rec.tax_event_type_code = 'UPDATE' THEN
5606 CLOSE get_tax_tolerance_upd_csr;
5607 ELSIF p_event_class_rec.tax_event_type_code = 'CREATE' THEN
5608 CLOSE get_tax_tolerance_import_csr;
5609 END IF;
5610 -- End Bugfix: 5617541
5611 RETURN;
5612 END IF;
5613
5614 IF (g_level_statement >= g_current_runtime_level ) THEN
5615 FND_LOG.STRING(g_level_statement,
5616 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5617 'p_return_status = ' || p_return_status);
5618 FND_LOG.STRING(g_level_statement,
5619 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5620 'p_error_buffer = ' || p_error_buffer);
5621 FND_LOG.STRING(g_level_statement,
5622 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance.END',
5623 'ZX_TDS_TAX_LINES_POPU_PKG: process_tax_tolerance(-)');
5624 END IF;
5625
5626 EXCEPTION
5627 WHEN OTHERS THEN
5628 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5629 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
5630
5631 IF (g_level_unexpected >= g_current_runtime_level ) THEN
5632 FND_LOG.STRING(g_level_unexpected,
5633 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5634 p_error_buffer);
5635 END IF;
5636
5637 END process_tax_tolerance;
5638
5639 -----------------------------------------------------------------------
5640 -- PUBLIC PROCEDURE
5641 -- populate_recovery_flg
5642 --
5643 -- DESCRIPTION
5644 -- This procedure populates Process_For_Recovery_Flag based on
5645 -- tax_recovery_flag in event class record, and
5646 -- Reporting_Only_Flag of a tax line
5647 --
5648
5649 PROCEDURE populate_recovery_flg(
5650 p_begin_index IN BINARY_INTEGER,
5651 p_end_index IN BINARY_INTEGER,
5652 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
5653 p_return_status OUT NOCOPY VARCHAR2,
5654 p_error_buffer OUT NOCOPY VARCHAR2)
5655
5656 IS
5657
5658 BEGIN
5659
5660 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5661
5662 IF (g_level_statement >= g_current_runtime_level ) THEN
5663 FND_LOG.STRING(g_level_statement,
5664 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_recovery_flg.BEGIN',
5665 'ZX_TDS_TAX_LINES_POPU_PKG: populate_recovery_flg(+)');
5666 FND_LOG.STRING(g_level_statement,
5667 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_recovery_flg',
5668 'begin_index = ' || to_char(p_begin_index) || ' end_index= '
5669 || to_char(p_end_index));
5670 END IF;
5671
5672 p_return_status := FND_API.G_RET_STS_SUCCESS;
5673
5674 --
5675 -- check if begin_index and end_index have values
5676 --
5677 IF (p_begin_index IS NULL OR p_end_index IS NULL) THEN
5678
5679 IF (g_level_statement >= g_current_runtime_level ) THEN
5680 FND_LOG.STRING(g_level_statement,
5681 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_recovery_flg',
5682 'Begin index or End index is null' );
5683 END IF;
5684
5685 RETURN;
5686 END IF;
5687
5688 -- If the value of p_event_class_rec.tax_recovery_flag is 'N',
5689 -- populate process_for_recovery_flag to 'N'. If it is 'Y', check
5690 -- reporting_only_flag to set tax_recovery_flag
5691 --
5692 IF NVL(p_event_class_rec.tax_recovery_flag, 'N') = 'N' THEN
5693 FOR i IN p_begin_index ..p_end_index LOOP
5694 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
5695 i).process_for_recovery_flag := 'N';
5696 END LOOP;
5697 ELSE
5698 FOR i IN p_begin_index ..p_end_index LOOP
5699 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
5700 i).reporting_only_flag = 'Y' THEN -- bugfix 5399549
5701 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
5702 i).process_for_recovery_flag := 'N';
5703 ELSE
5704 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
5705 i).process_for_recovery_flag := 'Y';
5706 END IF;
5707
5708 END LOOP;
5709 END IF;
5710
5711 IF (g_level_statement >= g_current_runtime_level ) THEN
5712 FND_LOG.STRING(g_level_statement,
5713 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_recovery_flg.END',
5714 'ZX_TDS_TAX_LINES_POPU_PKG: populate_recovery_flg(-)');
5715 END IF;
5716
5717 EXCEPTION
5718 WHEN OTHERS THEN
5719 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5720 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
5721 IF (g_level_unexpected >= g_current_runtime_level ) THEN
5722 FND_LOG.STRING(g_level_unexpected,
5723 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.populate_recovery_flg',
5724 p_error_buffer);
5725 END IF;
5726
5727 END populate_recovery_flg;
5728
5729 END ZX_TDS_TAX_LINES_POPU_PKG;