[Home] [Help]
PACKAGE BODY: APPS.ZX_LINES_DET_FACTORS_PKG
Source
1 PACKAGE BODY ZX_LINES_DET_FACTORS_PKG AS
2 /* $Header: zxiflinedetfactb.pls 120.21 2006/06/28 16:56:04 lxzhang ship $ */
3
4 /* ======================================================================*
5 | Global Data Types |
6 * ======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_LINES_DET_FACTORS_PKG';
9 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
10 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
11 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
12 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
13 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
14 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
15 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
16
17 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
18 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
19 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
20 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
21 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
22 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
23 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
24 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'ZX.PLSQL.ZX_LINES_DET_FACTORS_PKG.';
25
26 CURSOR get_lines_det_factors(p_transaction_rec ZX_API_PUB.transaction_rec_type) IS
27 SELECT APPLICATION_ID,
28 ENTITY_CODE,
29 EVENT_CLASS_CODE,
30 EVENT_TYPE_CODE,
31 LINE_LEVEL_ACTION,
32 TRX_ID,
33 TRX_LINE_ID,
34 TRX_LEVEL_TYPE,
35 DEFAULT_TAXATION_COUNTRY,
36 DOCUMENT_SUB_TYPE,
37 TAX_INVOICE_DATE,
38 TAX_INVOICE_NUMBER,
39 LINE_INTENDED_USE ,
40 PRODUCT_FISC_CLASSIFICATION ,
41 PRODUCT_TYPE ,
42 PRODUCT_CATEGORY ,
43 USER_DEFINED_FISC_CLASS,
44 ASSESSABLE_VALUE,
45 INPUT_TAX_CLASSIFICATION_CODE,
46 OUTPUT_TAX_CLASSIFICATION_CODE,
47 USER_UPD_DET_FACTORS_FLAG,
48 TAX_EVENT_CLASS_CODE
49 FROM ZX_LINES_DET_FACTORS
50 WHERE application_id = p_transaction_rec.application_id
51 AND entity_code = p_transaction_rec.entity_code
52 AND event_class_code = p_transaction_rec.event_class_code
53 AND trx_id = p_transaction_rec.trx_id
54 FOR UPDATE NOWAIT;
55
56
57 /* ============================================================================*
58 | PROCEDURE update_line_det_attribs : Update only the determining applicable |
59 | at line level back to zx_lines_det_factors |
60 * ===========================================================================*/
61 PROCEDURE update_line_det_attribs (
62 p_trx_biz_category IN VARCHAR2,
63 p_line_intended_use IN VARCHAR2,
64 p_prod_fisc_class IN VARCHAR2,
65 p_prod_category IN VARCHAR2,
66 p_product_type IN VARCHAR2,
67 p_user_def_fisc_class IN VARCHAR2,
68 p_assessable_value IN NUMBER,
69 p_tax_classification_code IN VARCHAR2,
70 p_display_tax_classif_flag IN VARCHAR2,
71 p_transaction_line_rec IN ZX_API_PUB.transaction_line_rec_type,
72 x_return_status OUT NOCOPY VARCHAR2
73 ) IS
74 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LINE_DET_ATTRIBS';
75 l_return_status VARCHAR2(1);
76
77 BEGIN
78 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
79 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN','ZX_LINES_DET_FACTORS_PKG: '||l_api_name||'()+');
80 END IF;
81
82 /*-----------------------------------------+
83 | Initialize return status to SUCCESS |
84 +-----------------------------------------*/
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87
88 /*-----------------------------------------+
89 | Update zx_lines_det_factors |
90 +-----------------------------------------*/
91 UPDATE ZX_LINES_DET_FACTORS SET
92 trx_business_category = p_trx_biz_category,
93 line_intended_use = p_line_intended_use,
94 user_defined_fisc_class = p_user_def_fisc_class,
95 product_fisc_classification = p_prod_fisc_class,
96 product_category = p_prod_category,
97 product_type = p_product_type,
98 assessable_value = p_assessable_value,
99 input_tax_classification_code = decode(p_display_tax_classif_flag,'Y', p_tax_classification_code,
100 input_tax_classification_code),
101 user_upd_det_factors_flag = 'Y',
102 tax_processing_completed_flag = 'N',
103 object_version_number = object_version_number+1,
104 line_level_action = decode(line_level_action,'SYNCHRONIZE', 'UPDATE', line_level_action)
105 WHERE application_id = p_transaction_line_rec.application_id
106 AND entity_code = p_transaction_line_rec.entity_code
107 AND event_class_code = p_transaction_line_rec.event_class_code
108 AND trx_id = p_transaction_line_rec.trx_id
109 AND trx_line_id = p_transaction_line_rec.trx_line_id
110 AND trx_level_type = p_transaction_line_rec.trx_level_type;
111
112 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
113 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END','ZX_LINES_DET_FACTORS_PKG: '||l_api_name||'()-');
114 END IF;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
120 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
121 END IF;
122 END update_line_det_attribs;
123
124 /* ============================================================================*
125 | PROCEDURE update_header_det_attribs : Calls the defaulting API to redefault|
126 | tax determining attributes since the taxation country has changed |
127 | Also update the lines_det_factors with these values for UI to reflect the |
128 | changes. |
129 * ===========================================================================*/
130 PROCEDURE update_header_det_attribs (
131 p_taxation_country IN VARCHAR2,
132 p_document_subtype IN VARCHAR2,
133 p_tax_invoice_date IN DATE,
134 p_tax_invoice_number IN VARCHAR2,
135 p_display_tax_classif_flag IN VARCHAR2,
136 p_transaction_rec IN ZX_API_PUB.transaction_rec_type,
137 p_event_class_rec IN OUT NOCOPY ZX_API_PUB.event_class_rec_type,
138 x_return_status OUT NOCOPY VARCHAR2
139 ) IS
140 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HEADER_DET_ATTRIBS';
141 l_return_status VARCHAR2(1);
142 l_event_class_rec ZX_API_PUB.event_class_rec_type;
143
144 BEGIN
145 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN','ZX_LINES_DET_FACTORS_PKG: '||l_api_name||'()+');
147 END IF;
148
149
150 /*-----------------------------------------+
151 | Initialize return status to SUCCESS |
152 +-----------------------------------------*/
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155
156 OPEN get_lines_det_factors(p_transaction_rec);
157 LOOP
158 FETCH get_lines_det_factors BULK COLLECT INTO
159 zx_global_structures_pkg.trx_line_dist_tbl.APPLICATION_ID,
160 zx_global_structures_pkg.trx_line_dist_tbl.ENTITY_CODE,
161 zx_global_structures_pkg.trx_line_dist_tbl.EVENT_CLASS_CODE,
162 zx_global_structures_pkg.trx_line_dist_tbl.EVENT_TYPE_CODE,
163 zx_global_structures_pkg.trx_line_dist_tbl.LINE_LEVEL_ACTION,
164 zx_global_structures_pkg.trx_line_dist_tbl.TRX_ID,
165 zx_global_structures_pkg.trx_line_dist_tbl.TRX_LINE_ID,
166 zx_global_structures_pkg.trx_line_dist_tbl.TRX_LEVEL_TYPE,
167 zx_global_structures_pkg.trx_line_dist_tbl.DEFAULT_TAXATION_COUNTRY,
168 zx_global_structures_pkg.trx_line_dist_tbl.DOCUMENT_SUB_TYPE,
169 zx_global_structures_pkg.trx_line_dist_tbl.TAX_INVOICE_DATE,
170 zx_global_structures_pkg.trx_line_dist_tbl.TAX_INVOICE_NUMBER,
171 zx_global_structures_pkg.trx_line_dist_tbl.LINE_INTENDED_USE ,
172 zx_global_structures_pkg.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION ,
173 zx_global_structures_pkg.trx_line_dist_tbl.PRODUCT_TYPE ,
174 zx_global_structures_pkg.trx_line_dist_tbl.PRODUCT_CATEGORY ,
175 zx_global_structures_pkg.trx_line_dist_tbl.USER_DEFINED_FISC_CLASS,
176 zx_global_structures_pkg.trx_line_dist_tbl.ASSESSABLE_VALUE,
177 zx_global_structures_pkg.trx_line_dist_tbl.INPUT_TAX_CLASSIFICATION_CODE,
178 zx_global_structures_pkg.trx_line_dist_tbl.OUTPUT_TAX_CLASSIFICATION_CODE,
179 zx_global_structures_pkg.trx_line_dist_tbl.USER_UPD_DET_FACTORS_FLAG,
180 zx_global_structures_pkg.trx_line_dist_tbl.TAX_EVENT_CLASS_CODE
181 LIMIT G_LINES_PER_FETCH;
182
183 FOR l_trx_line_index IN 1 .. nvl(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.internal_organization_id.LAST,0)
184 LOOP
185 IF p_taxation_country <> ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DEFAULT_TAXATION_COUNTRY(l_trx_line_index) THEN
186
187 --Call TRD service to default the determining attributes again
188 ZX_DEFAULT_AUTOMATION_PKG.default_tax_attribs (p_trx_line_index => l_trx_line_index,
189 p_event_class_rec => p_event_class_rec,
190 p_taxation_country => p_taxation_country,
191 p_document_sub_type => p_document_subtype,
192 p_tax_invoice_number=> p_tax_invoice_number,
193 p_tax_invoice_date => p_tax_invoice_date,
194 x_return_status => l_return_status
195 );
196
197 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
198 x_return_status := l_return_status;
199 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
200 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_DEFAULT_AUTOMATION_PKG.redefault_intended_use returned errors');
201 END IF;
202 RETURN;
203 END IF;
204 ELSE
205 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DEFAULT_TAXATION_COUNTRY(l_trx_line_index) := p_taxation_country;
206 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOCUMENT_SUB_TYPE(l_trx_line_index) := p_document_subtype;
207 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_INVOICE_DATE(l_trx_line_index) := p_tax_invoice_date;
208 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_INVOICE_NUMBER(l_trx_line_index) := p_tax_invoice_number;
209 END IF;--taxation country is different
210 END LOOP;
211 EXIT WHEN get_lines_det_factors%NOTFOUND OR get_lines_det_factors%NOTFOUND IS NULL;
212 END LOOP;
213 CLOSE get_lines_det_factors;
214
215
216 FORALL i IN ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id.FIRST .. ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id.LAST
217 UPDATE ZX_LINES_DET_FACTORS SET
218 default_taxation_country = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.default_taxation_country(i),
219 document_sub_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.document_sub_type(i),
220 line_intended_use = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_intended_use(i),
221 user_defined_fisc_class = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.user_defined_fisc_class(i),
222 product_fisc_classification = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_fisc_classification(i),
223 product_category = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_category(i),
224 assessable_value = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.assessable_value(i),
225 input_tax_classification_code = decode(p_display_tax_classif_flag,'Y',ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.input_tax_classification_code(i),
226 input_tax_classification_code),
227 tax_invoice_date = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_invoice_date(i),
228 tax_invoice_number = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_invoice_number(i),
229 user_upd_det_factors_flag ='Y',
230 tax_processing_completed_flag ='N',
231 object_version_number = object_version_number+1,
232 line_level_action = decode(line_level_action,'SYNCHRONIZE', 'UPDATE', line_level_action)
233 WHERE application_id = p_transaction_rec.application_id
234 AND entity_code = p_transaction_rec.entity_code
235 AND event_class_code = p_transaction_rec.event_class_code
236 AND trx_id = p_transaction_rec.trx_id ;
237
238 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
239 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END','ZX_LINES_DET_FACTORS_PKG: '||l_api_name||'()-'||' RETURN_STATUS = ' || l_return_status);
240 END IF;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 IF (SQLCODE = 54) THEN
245 x_return_status := FND_API.G_RET_STS_ERROR;
246 FND_MESSAGE.SET_NAME('ZX','ZX_RESOURCE_BUSY');
247 ELSE
248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 END IF;
250 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
251 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
252 END IF;
253 END update_header_det_attribs;
254
255 /* =======================================================================*
256 | PROCEDURE lock_line_det_factors : Lock all the lines of a transaction |
257 | in zx_lines_det_factors |
258 * =======================================================================*/
259 PROCEDURE lock_line_det_factors (
260 p_transaction_rec IN ZX_API_PUB.transaction_rec_type,
261 x_return_status OUT NOCOPY VARCHAR2
262 ) IS
263 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_LINE_DET_FACTORS';
264 l_return_status VARCHAR2(1);
265
266 BEGIN
267 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
268 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN','ZX_LINE_DET_FACTORS_PKG: '||l_api_name||'()+');
269 END IF;
270
271 /*-----------------------------------------+
272 | Initialize return status to SUCCESS |
273 +-----------------------------------------*/
274 x_return_status := FND_API.G_RET_STS_SUCCESS;
275
276
277 OPEN get_lines_det_factors(p_transaction_rec);
278 CLOSE get_lines_det_factors;
279
280 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
281 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END','ZX_LINE_DET_FACTORS_PKG: LOCK_LINE_DET_FACTORS()-');
282 END IF;
283
284 EXCEPTION
285 WHEN OTHERS THEN
286
287 IF (SQLCODE = 54) THEN
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 FND_MESSAGE.SET_NAME('ZX','ZX_RESOURCE_BUSY');
290 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
291 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
292 END IF;
293 ELSE
294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
295 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
296 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
297 END IF;
298
299 END IF;
300 END lock_line_det_factors;
301
302 END ZX_LINES_DET_FACTORS_PKG;
303