[Home] [Help]
PACKAGE BODY: APPS.AMS_ADI_MEDIA_PLANNER_PVT
Source
1 PACKAGE BODY ams_adi_media_planner_pvt AS
2 /* $Header: amsvwmpb.pls 120.3 2005/10/13 11:08:52 dmvincen noship $ */
3 -- ===============================================================
4 -- Package name
5 -- Web ADI Media Planner
6 --
7 -- Purpose
8 --
9 -- History
10 -- 06-Jul-2005 dmvincen BUG4475733: Failing to create forumla metrics.
11 -- 13-Jul-2005 dmvincen BUG4477880: Incorrect commits.
12 -- 04-Oct-2005 dmvincen BUG4621065: Update error on import.
13 -- 12-Oct-2005 dmvincen BUG4667183: Correcting rollbacks.
14 --
15 -- NOTE
16 --
17 -- ===============================================================
18
19 /* ############ Private TYPE Declarations ############### */
20 TYPE actmetrics_tab_t IS TABLE OF AMS_ActMetric_PVT.act_metric_rec_type
21 INDEX BY BINARY_INTEGER;
22
23 /* ############ Global Variable Declarations ############### */
24 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
25 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
26 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
27
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_ADI_MEDIA_PLANNER_PVT';
29
30 /* ############# Forward Procedure Declarations ################ */
31 PROCEDURE resolve_metrics_from_rec (
32 p_interface_rec IN ams_adi_media_planner%ROWTYPE,
33 x_actmetric_table OUT NOCOPY actmetrics_tab_t
34 );
35
36 -- ==========================================================================
37 -- API Name
38 -- Load Metrics
39 -- Type
40 -- Private
41 -- Pre-Req
42 --
43 -- Parameters
44 --
45 -- IN
46 -- p_api_version
47 -- p_init_msg_list Default FND_API.g_false; indicate whether to
48 -- initialize the message buffer.
49 -- p_commit Default FND_API.g_false; indicate whether to
50 -- commit the transaction.
51 -- p_upload_batch_id Batch ID used to identify the records belonging
52 -- in the same upload batch.
53 -- p_object_type Default 'CSCH'; the type of object the metrics
54 -- are being loaded for.
55 -- p_object_name Name of the object the metrics are being loaded
56 -- for. The name and parent_id are used to uniquely
57 -- identify the record in the interface table
58 -- associated with the object; the object ID is not
59 -- in the interface table during import.
60 -- p_parent_type The type of object of the parent of the object.
61 -- p_parent_id The object ID of the parent object. Used with
62 -- object name to identify the record in the
63 -- interface table associated with the object; the
64 -- record in the interface table does not have the
65 -- object ID during import.
66 -- p_object_id Used with the metric API.
67 -- x_return_status Status of the API execution.
68 -- x_msg_count Number of messages in the message buffer.
69 -- x_msg_data Contents of the message buffer.
70 --
71 -- Version : Current version 1.0
72 --
73 -- History
74 --
75 -- NOTE
76 -- Invoked from the import concurrent program after the object gets
77 -- created and the objet ID is available.
78 --
79 -- upload_batch_id is intended to be used to identify a unique set of
80 -- working records for the user's session. Currently, there is an issue
81 -- with syncing a system (Web ADI) generated batch_id during upload to
82 -- multiple interfaces.
83 --
84 -- ==========================================================================
85 PROCEDURE load_metrics (
86 p_api_version IN NUMBER,
87 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
88 p_commit IN VARCHAR2 := FND_API.g_false,
89 p_upload_batch_id IN NUMBER,
90 p_object_type IN VARCHAR2 := 'CSCH',
91 p_object_name IN VARCHAR2,
92 -- p_parent_type IN VARCHAR2 := 'CAMP',
93 p_parent_type IN VARCHAR2,
94 p_parent_id IN NUMBER,
95 p_object_id IN NUMBER,
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2
99 )
100 IS
101 L_API_NAME CONSTANT VARCHAR2(30) := 'load_metrics';
102 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
103 l_interface_rec ams_adi_media_planner%ROWTYPE;
104 l_err_recs AMS_ADI_COMMON_PVT.ams_adi_error_rec_t :=
105 AMS_ADI_COMMON_PVT.ams_adi_error_rec_t();
106 BEGIN
107 IF fnd_api.to_boolean (p_init_msg_list) THEN
108 fnd_msg_pub.initialize;
109 END IF;
110
111 IF NOT FND_API.Compatible_API_Call(L_API_VERSION_NUMBER,
112 p_api_version, l_API_NAME, G_PKG_NAME)
113 THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116
117 x_return_status := FND_API.g_ret_sts_success;
118
119 BEGIN
120 SELECT *
121 INTO l_interface_rec
122 FROM ams_adi_media_planner
123 WHERE operation_type = 'IMPORT'
124 AND object_type = p_object_type
125 AND object_name = p_object_name
126 AND parent_type = p_parent_type
127 AND parent_id = p_parent_id;
128 EXCEPTION
129 WHEN TOO_MANY_ROWS THEN
130 x_return_status := FND_API.g_ret_sts_error;
131 FND_MESSAGE.set_name ('AMS', 'AMS_CSCH_NOT_UNIQUE');
132 FND_MSG_PUB.add;
133 RETURN;
134 WHEN NO_DATA_FOUND THEN
135 x_return_status := FND_API.g_ret_sts_error;
136 FND_MESSAGE.set_name ('AMS', 'AMS_CSCH_NOT_FOUND');
137 FND_MSG_PUB.add;
138 RETURN;
139 END;
140
141 IF (AMS_DEBUG_HIGH_ON) THEN
142 AMS_UTILITY_PVT.Write_Conc_Log('Public API: ' || l_api_name ||
143 ': upload batch id : '||p_upload_batch_id||
144 ', p_object_id : '||p_object_id);
145 END IF;
146
147 l_interface_rec.object_id := p_object_id;
148
149 load_metrics (
150 p_api_version => 1.0,
151 p_media_planner_rec => l_interface_rec,
152 p_err_recs => l_err_recs,
153 x_return_status => x_return_status,
154 x_msg_count => x_msg_count,
155 x_msg_data => x_msg_data
156 );
157
158 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
159 COMMIT;
160 END IF;
161
162 IF (AMS_DEBUG_HIGH_ON) THEN
163 AMS_UTILITY_PVT.Write_Conc_Log('Public API: ' || l_api_name ||
164 ': upload batch id : '||p_upload_batch_id||'. Done load');
165 END IF;
166
167 EXCEPTION
168 WHEN fnd_api.G_EXC_ERROR THEN
169 x_return_status := FND_API.G_RET_STS_ERROR;
170 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
171 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
174 WHEN OTHERS THEN
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
177 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, L_API_NAME);
178 END IF;
179 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
180
181 END;
182
183 -- ==========================================================================
184 -- API Name
185 -- Load Metrics
186 --
187 -- Parameters
188 --
189 -- IN
190 -- p_api_version
191 -- p_init_msg_list Default FND_API.g_false; indicate whether to
192 -- initialize the message buffer.
193 -- p_commit Default FND_API.g_false; indicate whether to
194 -- commit the transaction.
195 -- p_media_planner_rec ###### description here #######
196 -- x_return_status Status of the API execution.
197 -- x_msg_count Number of messages in the message buffer.
198 -- x_msg_data Contents of the message buffer.
199 --
200 -- Version : Current version 1.0
201 --
202 -- History
203 --
204 -- NOTE
205 -- Invoked from the concurrent program after the media planner record
206 -- is constructed from the interface table data.
207 --
208 -- Rely on the calling program to handle errors.
209 --
210 -- ==========================================================================
211 PROCEDURE load_metrics (
212 p_api_version IN NUMBER,
213 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
214 p_commit IN VARCHAR2 := FND_API.g_false,
215 p_media_planner_rec IN ams_adi_media_planner%ROWTYPE,
216 p_err_recs IN OUT NOCOPY AMS_ADI_COMMON_PVT.ams_adi_error_rec_t,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2
220 )
221 IS
222 L_API_VERSION_NUMBER CONSTANT NUMBER := 1;
223 L_API_NAME CONSTANT VARCHAR2(30) := 'LOAD_METRICS';
224 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
225 l_msg_count NUMBER;
226 l_msg_data VARCHAR2(4000);
227 l_table_of_metrics actmetrics_tab_t;
228
229 l_act_metric_rec AMS_ActMetric_PVT.act_metric_rec_type;
230 x_activity_metric_id NUMBER;
231
232 CURSOR c_met(p_metric_id IN NUMBER,
233 p_arc_act_metric_used_by IN VARCHAR2,
234 p_act_metric_used_by_id IN NUMBER) IS
235 SELECT activity_metric_id, object_version_number
236 FROM ams_act_metrics_all
237 WHERE metric_id = p_metric_id
238 AND act_metric_used_by_id = p_act_metric_used_by_id
239 AND arc_act_metric_used_by = p_arc_act_metric_used_by
240 ORDER BY activity_metric_id;
241
242 CURSOR c_met_display(p_metric_id IN NUMBER) IS
243 SELECT display_type FROM ams_metrics_all_b
244 WHERE metric_id = p_metric_id;
245
246 l_activity_metric_id NUMBER;
247 l_object_version_number NUMBER;
248 l_display_type VARCHAR2(30);
249
250 BEGIN
251 IF fnd_api.to_boolean (p_init_msg_list) THEN
252 fnd_msg_pub.initialize;
253 END IF;
254
255 IF NOT FND_API.Compatible_API_Call(L_API_VERSION_NUMBER,
256 p_api_version, L_API_NAME, G_PKG_NAME)
257 THEN
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259 END IF;
260
261 x_return_status := FND_API.g_ret_sts_success;
262
263 IF (AMS_DEBUG_HIGH_ON) THEN
264 AMS_UTILITY_PVT.Write_Conc_Log('Private API : load_metrics object_name:'||
265 p_media_planner_rec.object_name||
266 ' operation_type : '||p_media_planner_rec.operation_type);
267 END IF;
268
269 resolve_metrics_from_rec (
270 p_interface_rec => p_media_planner_rec,
271 x_actmetric_table => l_table_of_metrics
272 );
273
274 IF (AMS_DEBUG_HIGH_ON) THEN
275 AMS_UTILITY_PVT.Write_Conc_Log('Private API : load_metrics '||
276 'after resolve_metrics_from_rec table count '||l_table_of_metrics.COUNT);
277 END IF;
278
279 SAVEPOINT LOAD_METRICS_SP;
280
281 -- If there are any metrics loop through the table to do the processing.
282 IF l_table_of_metrics.COUNT > 0 THEN
283
284 FOR i IN l_table_of_metrics.FIRST..l_table_of_metrics.LAST LOOP
285
286 -- If the activity metric is already existing for the schedule,
287 -- update should take place.
288 IF p_media_planner_rec.operation_type = 'IMPORT' THEN
289 /*** BUG4621065: Reset values on each loop. ***/
290 l_activity_metric_id := null;
291 l_object_version_number := null;
292 OPEN c_met(l_table_of_metrics(i).metric_id,
293 p_media_planner_rec.object_type,
294 p_media_planner_rec.object_id);
295 FETCH c_met INTO l_activity_metric_id, l_object_version_number;
296 CLOSE c_met;
297
298 IF l_activity_metric_id IS NOT NULL AND
299 l_object_version_number = 1 THEN
300 l_table_of_metrics(i).activity_metric_id := l_activity_metric_id;
301 l_table_of_metrics(i).object_version_number := 1;
302 END IF;
303 END IF;
304
305 -- Set the currency code according to display type.
306 -- Default the currency if not set.
307
308 OPEN c_met_display(l_table_of_metrics(i).metric_id);
309 FETCH c_met_display INTO l_display_type;
310 CLOSE c_met_display;
311
312 IF l_display_type = 'CURRENCY' THEN
313 IF l_table_of_metrics(i).transaction_currency_code IS NULL THEN
314 l_act_metric_rec.transaction_currency_code :=
315 p_media_planner_rec.transaction_currency_code;
316 ELSE
317 l_act_metric_rec.transaction_currency_code :=
318 l_table_of_metrics(i).transaction_currency_code;
319 END IF;
320 ELSE
321 l_act_metric_rec.transaction_currency_code := null;
322 END IF;
323
324 IF l_table_of_metrics(i).activity_metric_id IS NULL THEN
325 -- invoke create metric API
326 l_act_metric_rec.metric_id := l_table_of_metrics(i).metric_id;
327
328 IF l_table_of_metrics(i).trans_forecasted_value <>
329 Fnd_Api.G_MISS_NUM THEN
330 l_act_metric_rec.trans_forecasted_value :=
331 l_table_of_metrics(i).trans_forecasted_value;
332 END IF;
333
334 IF l_table_of_metrics(i).trans_actual_value <>
335 Fnd_Api.G_MISS_NUM THEN
336 l_act_metric_rec.trans_actual_value :=
337 l_table_of_metrics(i).trans_actual_value;
338 END IF;
339
340 IF l_table_of_metrics(i).func_forecasted_value <>
341 Fnd_Api.G_MISS_NUM THEN
342 l_act_metric_rec.func_forecasted_value :=
343 l_table_of_metrics(i).func_forecasted_value;
344 END IF;
345
346 IF l_table_of_metrics(i).forecasted_variable_value <>
347 Fnd_Api.G_MISS_NUM THEN
348 l_act_metric_rec.forecasted_variable_value :=
349 l_table_of_metrics(i).forecasted_variable_value;
350 END IF;
351
352 l_act_metric_rec.act_metric_used_by_id :=
353 p_media_planner_rec.object_id;
354 l_act_metric_rec.arc_act_metric_used_by :=
355 p_media_planner_rec.object_type;
356 l_act_metric_rec.application_id := 530;
357 x_activity_metric_id := NULL;
358
359 AMS_ActMetric_PVT.Create_ActMetric (
360 p_api_version => 1.0,
361 p_init_msg_list => FND_API.G_FALSE,
362 p_commit => FND_API.G_FALSE,
363 p_validation_level => Fnd_Api.G_Valid_Level_Full,
364 x_return_status => l_return_status,
365 x_msg_count => l_msg_count,
366 x_msg_data => l_msg_data,
367 p_act_metric_rec => l_act_metric_rec,
368 x_activity_metric_id => x_activity_metric_id);
369
370 IF (AMS_DEBUG_HIGH_ON) THEN
371 AMS_UTILITY_PVT.debug_message('Private API : load_metric '||
372 'Return Status Create_ActMetric: '||l_return_status);
373 END IF;
374
375 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
376 AMS_ADI_COMMON_PVT.handle_error_row(
377 p_write_conc_log => FND_API.G_TRUE,
378 p_rollback => FND_API.G_FALSE,
379 p_error_code => NULL,
380 p_error_message => NULL,
381 p_object_id => p_media_planner_rec.object_id,
382 p_object_name => NULL,
383 p_parent_object_id => NULL,
384 p_error_records => p_err_recs
385 );
386 x_return_status := l_return_status;
387 END IF;
388
389 --Reset the metric record
390 l_act_metric_rec.metric_id := NULL;
391 l_act_metric_rec.transaction_currency_code := NULL;
392 l_act_metric_rec.trans_forecasted_value := NULL;
393 l_act_metric_rec.trans_actual_value := NULL;
394 l_act_metric_rec.func_forecasted_value := NULL;
395 l_act_metric_rec.forecasted_variable_value := NULL;
396 l_act_metric_rec.act_metric_used_by_id := NULL;
397 l_act_metric_rec.arc_act_metric_used_by := NULL;
398
399 ELSE
400 -- invoke update metric API
401 Ams_Actmetric_Pvt.update_actmetric (
402 p_api_version => 1.0,
403 p_init_msg_list => FND_API.G_FALSE,
404 p_commit => FND_API.G_FALSE,
405 p_act_metric_rec => l_table_of_metrics(i),
406 x_return_status => l_return_status,
407 x_msg_count => l_msg_count,
408 x_msg_data => l_msg_data);
409
410 -- Debug Message
411 IF (AMS_DEBUG_HIGH_ON) THEN
412 AMS_UTILITY_PVT.debug_message('Private API : load_metric '||
413 ': Return Status update_actmetric '||l_return_status);
414 AMS_UTILITY_PVT.debug_message('Private API : load_metric '||
415 ': activity_metric_id='||l_table_of_metrics(i).activity_metric_id);
416 AMS_UTILITY_PVT.debug_message('Private API : load_metric '||
417 ': metric_id='||l_table_of_metrics(i).metric_id);
418 AMS_UTILITY_PVT.debug_message('Private API : load_metric '||
419 ': OVN='||l_table_of_metrics(i).object_version_number);
420 END IF;
421
422 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
423 AMS_ADI_COMMON_PVT.handle_error_row(
424 p_write_conc_log => FND_API.G_TRUE,
425 p_rollback => FND_API.G_FALSE,
426 p_error_code => NULL,
427 p_error_message => NULL,
428 p_object_id => p_media_planner_rec.object_id,
429 p_object_name => NULL,
430 p_parent_object_id => NULL,
431 p_error_records => p_err_recs
432 );
433 x_return_status := l_return_status;
434 END IF;
435 END IF;
436 END LOOP;
437
438 END IF; -- COUNT >0
439
440 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
441 AMS_ADI_COMMON_PVT.handle_success_row(p_commit);
442 ELSE
443 ROLLBACK TO LOAD_METRICS_SP;
444 END IF;
445
446 IF (AMS_DEBUG_HIGH_ON) THEN
447 AMS_UTILITY_PVT.Write_Conc_Log('Private API :AMS_ADI_MEDIA_PLANNER_PVT.LOAD_METRICS. x_return_status = '||x_return_status);
448 END IF;
449
450 EXCEPTION
451 WHEN fnd_api.G_EXC_ERROR THEN
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 x_msg_count := l_msg_count;
454 x_msg_data := l_msg_data;
455 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
456 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
458 x_msg_count := l_msg_count;
459 x_msg_data := l_msg_data;
460 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
461 WHEN OTHERS THEN
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
463 x_msg_count := l_msg_count;
464 x_msg_data := l_msg_data;
465 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
466 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, L_API_NAME);
467 END IF;
468 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
469
470 END;
471
472 -- ==========================================================================
473 -- API Name
474 -- Load Request
475 --
476 -- Parameters
477 --
478 -- IN
479 -- errbuf Error message buffer for a concurrent program.
480 -- retcode Return code for a concurrent program.
481 -- p_upload_batch_id Batch ID used to identify the records belonging
482 -- in the same upload batch.
483 --
484 -- Version : Current version 1.0
485 --
486 -- History
487 --
488 -- NOTE
489 -- Conccurrent request entry point for processing Media Planner records.
490 --
491 -- ==========================================================================
492 PROCEDURE load_request (
493 x_errbuf OUT NOCOPY VARCHAR2,
494 x_retcode OUT NOCOPY NUMBER,
495 p_upload_batch_id IN NUMBER
496 )
497 IS
498 L_API_NAME CONSTANT VARCHAR2(30) := 'LOAD_REQUEST';
499 l_return_status VARCHAR2(1);
500 l_msg_count NUMBER;
501 l_msg_data VARCHAR2(4000);
502 l_err_recs AMS_ADI_COMMON_PVT.ams_adi_error_rec_t :=
503 AMS_ADI_COMMON_PVT.ams_adi_error_rec_t();
504
505 CURSOR c_interface IS
506 SELECT *
507 FROM ams_adi_media_planner
508 WHERE upload_batch_id = p_upload_batch_id;
509
510 BEGIN
511 x_retcode := 0; -- 0 is normal end of execution
512
513 -- due to a requirement to support 8i databases,
514 -- using bulk collect may not be efficient because
515 -- the number of columns fetched is great. See the
516 -- following for reference:
517 -- http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3561337894959
518
519 -- <TO DO>: bulk load metrics from interface table
520 -- bulk_interface_into_recs (l_interface_recs);
521
522 --Call init method to initialize
523 AMS_ADI_COMMON_PVT.init();
524
525 FOR l_interface_recs IN c_interface LOOP
526 load_metrics (
527 p_api_version => 1.0,
528 p_commit => FND_API.G_TRUE,
529 p_media_planner_rec => l_interface_recs,
530 p_err_recs => l_err_recs,
531 x_return_status => l_return_status,
532 x_msg_count => l_msg_count,
533 x_msg_data => l_msg_data
534 );
535 IF l_return_status <> FND_API.g_ret_sts_success THEN
536 -- Return error code should be success for the web adi to show the
537 -- messages on the Sheet.
538 -- retcode := 2; -- error code is 2
539 AMS_Utility_PVT.write_conc_log;
540 -- reset message buffer after contents
541 -- have been dumped into the log file
542 FND_MSG_PUB.initialize;
543 END IF;
544 END LOOP;
545
546 IF (AMS_DEBUG_HIGH_ON) THEN
547 AMS_UTILITY_PVT.Write_Conc_Log('Public API: load_request upload '||
548 'batch id : '||p_upload_batch_id||'. Done load');
549 END IF;
550
551 AMS_ADI_COMMON_PVT.complete_batch(
552 'AMS_ADI_MEDIA_PLANNER',
553 p_upload_batch_id,
554 FND_API.G_TRUE,
555 FND_API.G_TRUE,
556 l_err_recs
557 );
558
559 AMS_ADI_COMMON_PVT.complete_all(FND_API.G_TRUE,FND_API.G_TRUE,p_upload_batch_id);
560
561 IF (AMS_DEBUG_HIGH_ON) THEN
562 AMS_UTILITY_PVT.Write_Conc_Log('Public API: load_request retcode : '||x_retcode||' Done load');
563 END IF;
564
565 EXCEPTION
566 WHEN Fnd_Api.G_EXC_ERROR THEN
567 x_retcode := 1;
568 RAISE;
569 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
570 x_retcode := 1;
571 RAISE;
572 WHEN OTHERS THEN
573 x_errbuf := SQLERRM;
574 AMS_UTILITY_PVT.Write_Conc_Log(l_API_NAME||'SQLERROR: '||x_errbuf);
575 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
576 FND_MSG_PUB.Add_EXC_MSG(G_PKG_NAME, L_API_NAME);
577 END IF;
578 x_retcode := 1;
579 RAISE;
580
581 END;
582
583
584 -- ==========================================================================
585 -- API Name
586 -- Resolve Metrics From Record
587 --
588 -- Parameters
589 --
590 -- IN
591 -- p_interface_rec Complete Record from media planner table.
592 -- x_actmetric_table Activity metric records for update or insert.
593 --
594 -- Version : Current version 1.0
595 --
596 -- History
597 --
598 -- NOTE
599 -- Create an activity metric record for each metric found in media planner
600 -- interface.
601 --
602 -- ==========================================================================
603 PROCEDURE resolve_metrics_from_rec (
604 p_interface_rec IN ams_adi_media_planner%ROWTYPE,
605 x_actmetric_table OUT NOCOPY actmetrics_tab_t
606 )
607 IS
608 l_act_metric_rec AMS_ActMetric_PVT.act_metric_rec_type;
609 BEGIN
610
611 -- Clear the table.
612 x_actmetric_table.delete;
613
614 --Fixed Manual Metrics Start
615 IF p_interface_rec.ACT_METRIC_ID_101 IS NOT NULL OR
616 p_interface_rec.METRIC_ID_101 IS NOT NULL THEN
617
618 -- Records in this list are for updating. All fields are defaulted
619 -- to G_MISS... values.
620 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
621 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_101;
622 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_101;
623 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_101;
624 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_101;
625 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_101;
626 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_101;
627 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
628 END IF;
629
630 IF p_interface_rec.ACT_METRIC_ID_102 IS NOT NULL OR
631 p_interface_rec.METRIC_ID_102 IS NOT NULL THEN
632 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
633 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_102;
634 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_102;
635 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_102;
636 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_102;
637 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_102;
638 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_102;
639 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
640 END IF;
641
642 IF p_interface_rec.ACT_METRIC_ID_103 IS NOT NULL OR p_interface_rec.METRIC_ID_103 IS NOT NULL THEN
643 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
644 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_103;
645 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_103;
646 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_103;
647 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_103;
648 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_103;
649 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_103;
650 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
651 END IF;
652
653 IF p_interface_rec.ACT_METRIC_ID_104 IS NOT NULL OR p_interface_rec.METRIC_ID_104 IS NOT NULL THEN
654 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
655 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_104;
656 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_104;
657 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_104;
658 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_104;
659 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_104;
660 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_104;
661 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
662 END IF;
663
664 IF p_interface_rec.ACT_METRIC_ID_105 IS NOT NULL OR p_interface_rec.METRIC_ID_105 IS NOT NULL THEN
665 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
666 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_105;
667 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_105;
668 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_105;
669 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_105;
670 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_105;
671 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_105;
672 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
673 END IF;
674
675 IF p_interface_rec.ACT_METRIC_ID_106 IS NOT NULL OR p_interface_rec.METRIC_ID_106 IS NOT NULL THEN
676 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
677 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_106;
678 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_106;
679 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_106;
680 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_106;
681 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_106;
682 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_106;
683 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
684 END IF;
685
686 IF p_interface_rec.ACT_METRIC_ID_107 IS NOT NULL OR p_interface_rec.METRIC_ID_107 IS NOT NULL THEN
687 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
688 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_107;
689 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_107;
690 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_107;
691 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_107;
692 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_107;
693 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_107;
694 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
695 END IF;
696
697 IF p_interface_rec.ACT_METRIC_ID_108 IS NOT NULL OR p_interface_rec.METRIC_ID_108 IS NOT NULL THEN
698 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
699 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_108;
700 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_108;
701 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_108;
702 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_108;
703 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_108;
704 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_108;
705 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
706 END IF;
707
708 IF p_interface_rec.ACT_METRIC_ID_109 IS NOT NULL OR p_interface_rec.METRIC_ID_109 IS NOT NULL THEN
709 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
710 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_109;
711 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_109;
712 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_109;
713 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_109;
714 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_109;
715 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_109;
716 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
717 END IF;
718
719 IF p_interface_rec.ACT_METRIC_ID_110 IS NOT NULL OR p_interface_rec.METRIC_ID_110 IS NOT NULL THEN
720 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
721 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_110;
722 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_110;
723 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_110;
724 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_110;
725 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_110;
726 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_110;
727 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
728 END IF;
729 --Fixed Manual Metrics End
730
731 --Variable Manual Metrics Start
732 IF p_interface_rec.ACT_METRIC_ID_201 IS NOT NULL OR p_interface_rec.METRIC_ID_201 IS NOT NULL THEN
733 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
734 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_201;
735 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_201;
736 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_201;
737 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_201;
738 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_201;
739 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_201;
740 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
741 END IF;
742
743 IF p_interface_rec.ACT_METRIC_ID_202 IS NOT NULL OR p_interface_rec.METRIC_ID_202 IS NOT NULL THEN
744 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
745 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_202;
746 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_202;
747 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_202;
748 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_202;
749 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_202;
750 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_202;
751 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
752 END IF;
753
754 IF p_interface_rec.ACT_METRIC_ID_203 IS NOT NULL OR p_interface_rec.METRIC_ID_203 IS NOT NULL THEN
755 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
756 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_203;
757 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_203;
758 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_203;
759 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_203;
760 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_203;
761 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_203;
762 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
763 END IF;
764
765 IF p_interface_rec.ACT_METRIC_ID_204 IS NOT NULL OR p_interface_rec.METRIC_ID_204 IS NOT NULL THEN
766 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
767 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_204;
768 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_204;
769 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_204;
770 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_204;
771 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_204;
772 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_204;
773 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
774 END IF;
775
776 IF p_interface_rec.ACT_METRIC_ID_205 IS NOT NULL OR p_interface_rec.METRIC_ID_205 IS NOT NULL THEN
777 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
778 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_205;
779 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_205;
780 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_205;
781 l_act_metric_rec.trans_actual_value := p_interface_rec.METRIC_ACTUAL_205;
782 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_205;
783 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_205;
784 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
785 END IF;
786
787 --Variable Manual Metrics End
788
789 --Fixed Functional Metrics Start Only Forecast Value can be manually updated for Functional Metrics
790 IF p_interface_rec.ACT_METRIC_ID_301 IS NOT NULL OR p_interface_rec.METRIC_ID_301 IS NOT NULL THEN
791 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
792 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_301;
793 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_301;
794 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_301;
795 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_301;
796 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_301;
797 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
798 END IF;
799
800 IF p_interface_rec.ACT_METRIC_ID_302 IS NOT NULL OR p_interface_rec.METRIC_ID_302 IS NOT NULL THEN
801 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
802 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_302;
803 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_302;
804 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_302;
805 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_302;
806 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_302;
807 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
808 END IF;
809
810 IF p_interface_rec.ACT_METRIC_ID_303 IS NOT NULL OR p_interface_rec.METRIC_ID_303 IS NOT NULL THEN
811 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
812 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_303;
813 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_303;
814 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_303;
815 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_303;
816 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_303;
817 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
818 END IF;
819
820 IF p_interface_rec.ACT_METRIC_ID_304 IS NOT NULL OR p_interface_rec.METRIC_ID_304 IS NOT NULL THEN
821 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
822 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_304;
823 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_304;
824 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_304;
825 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_304;
826 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_304;
827 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
828 END IF;
829
830 IF p_interface_rec.ACT_METRIC_ID_305 IS NOT NULL OR p_interface_rec.METRIC_ID_305 IS NOT NULL THEN
831 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
832 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_305;
833 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_305;
834 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_305;
835 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_305;
836 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_305;
837 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
838 END IF;
839
840 IF p_interface_rec.ACT_METRIC_ID_306 IS NOT NULL OR p_interface_rec.METRIC_ID_306 IS NOT NULL THEN
841 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
842 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_306;
843 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_306;
844 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_306;
845 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_306;
846 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_306;
847 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
848 END IF;
849
850 IF p_interface_rec.ACT_METRIC_ID_307 IS NOT NULL OR p_interface_rec.METRIC_ID_307 IS NOT NULL THEN
851 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
852 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_307;
853 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_307;
854 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_307;
855 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_307;
856 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_307;
857 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
858 END IF;
859
860 IF p_interface_rec.ACT_METRIC_ID_308 IS NOT NULL OR p_interface_rec.METRIC_ID_308 IS NOT NULL THEN
861 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
862 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_308;
863 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_308;
864 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_308;
865 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_308;
866 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_308;
867 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
868 END IF;
869
870 IF p_interface_rec.ACT_METRIC_ID_309 IS NOT NULL OR p_interface_rec.METRIC_ID_309 IS NOT NULL THEN
871 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
872 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_309;
873 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_309;
874 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_309;
875 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_309;
876 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_309;
877 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
878 END IF;
879
880 IF p_interface_rec.ACT_METRIC_ID_310 IS NOT NULL OR p_interface_rec.METRIC_ID_310 IS NOT NULL THEN
881 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
882 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_310;
883 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_310;
884 l_act_metric_rec.trans_forecasted_value := p_interface_rec.METRIC_FORECAST_310;
885 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_310;
886 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_310;
887 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
888 END IF;
889 --Fixed Functional Metrics End
890
891 --Variable Functional Metrics Start
892 --For variable functional metrics, the user can update the unit forecast value only and the actual value is calculated by the system.
893 IF p_interface_rec.ACT_METRIC_ID_401 IS NOT NULL OR p_interface_rec.METRIC_ID_401 IS NOT NULL THEN
894 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
895 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_401;
896 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_401;
897 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_401;
898 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_401;
899 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_401;
900 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
901 END IF;
902
903 IF p_interface_rec.ACT_METRIC_ID_402 IS NOT NULL OR p_interface_rec.METRIC_ID_402 IS NOT NULL THEN
904 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
905 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_402;
906 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_402;
907 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_402;
908 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_402;
909 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_402;
910 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
911 END IF;
912
913 IF p_interface_rec.ACT_METRIC_ID_403 IS NOT NULL OR p_interface_rec.METRIC_ID_403 IS NOT NULL THEN
914 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
915 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_403;
916 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_403;
917 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_403;
918 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_403;
919 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_403;
920 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
921 END IF;
922
923 IF p_interface_rec.ACT_METRIC_ID_404 IS NOT NULL OR p_interface_rec.METRIC_ID_404 IS NOT NULL THEN
924 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
925 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_404;
926 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_404;
927 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_404;
928 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_404;
929 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_404;
930 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
931 END IF;
932
933 IF p_interface_rec.ACT_METRIC_ID_405 IS NOT NULL OR p_interface_rec.METRIC_ID_405 IS NOT NULL THEN
934 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
935 l_act_metric_rec.activity_metric_id := p_interface_rec.ACT_METRIC_ID_405;
936 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_405;
937 l_act_metric_rec.forecasted_variable_value := p_interface_rec.METRIC_FORECAST_UNIT_405;
938 l_act_metric_rec.object_version_number := p_interface_rec.OBJECT_VERSION_NUMBER_405;
939 l_act_metric_rec.transaction_currency_code := p_interface_rec.TRANS_CURRENCY_CODE_405;
940 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
941 END IF;
942 --Variable Functional Metrics End
943
944 --formula metrics start
945 -- BUG4475733: Set activity_metric_id to null for creation.
946 IF p_interface_rec.METRIC_ID_501 IS NOT NULL THEN
947 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
948 l_act_metric_rec.activity_metric_id := NULL;
949 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_501;
950 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
951 END IF;
952
953 IF p_interface_rec.METRIC_ID_502 IS NOT NULL THEN
954 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
955 l_act_metric_rec.activity_metric_id := NULL;
956 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_502;
957 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
958 END IF;
959
960 IF p_interface_rec.METRIC_ID_503 IS NOT NULL THEN
961 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
962 l_act_metric_rec.activity_metric_id := NULL;
963 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_503;
964 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
965 END IF;
966
967 IF p_interface_rec.METRIC_ID_504 IS NOT NULL THEN
968 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
969 l_act_metric_rec.activity_metric_id := NULL;
970 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_504;
971 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
972 END IF;
973
974 IF p_interface_rec.METRIC_ID_505 IS NOT NULL THEN
975 AMS_ActMetric_PVT.Init_ActMetric_Rec(l_act_metric_rec);
976 l_act_metric_rec.activity_metric_id := NULL;
977 l_act_metric_rec.metric_id := p_interface_rec.METRIC_ID_505;
978 x_actmetric_table(x_actmetric_table.COUNT) := l_act_metric_rec;
979 END IF;
980 --formula metrics end
981
982 END;
983
984 PROCEDURE purge_import_metrics(
985 p_object_type IN VARCHAR2,
986 p_object_name IN VARCHAR2,
987 p_parent_type IN VARCHAR2,
988 p_parent_id IN NUMBER
989 )
990 IS
991 BEGIN
992
993 IF (AMS_DEBUG_HIGH_ON) THEN
994 AMS_UTILITY_PVT.Write_Conc_Log('Public API: purge_import_metrics' ||
995 p_object_type|| '::'|| p_object_name|| '::' ||
996 p_parent_type ||'::'|| p_parent_id);
997 END IF;
998
999 --Clean up rows for import
1000 DELETE FROM ams_adi_media_planner
1001 WHERE operation_type = 'IMPORT'
1002 AND object_type = p_object_type
1003 AND object_name = p_object_name
1004 AND parent_type = p_parent_type
1005 AND parent_id = p_parent_id;
1006
1007 -- COMMIT;
1008
1009 END;
1010
1011 END ams_adi_media_planner_pvt;