[Home] [Help]
PACKAGE BODY: APPS.AMS_DMEXTRACT_PVT
Source
1 PACKAGE BODY ams_DMExtract_pvt AS
2 /* $Header: amsvdxtb.pls 120.2 2006/01/27 05:52:03 kbasavar noship $ */
3
4 G_MODE_UPDATE CONSTANT VARCHAR2(30) := 'U';
5 -- start of changes by amisingh for campaign ScheduleLOV
6 G_MEDIA_EMAIL CONSTANT NUMBER := 20;
7 G_MEDIA_TELEMARKETING CONSTANT NUMBER := 460;
8 G_MEDIA_DIRECTMAIL CONSTANT NUMBER := 480;
9 -- end of changes by amisingh
10
11 --
12 -- Foreward Declarations of Procedures
13 --
14 PROCEDURE analyze_mining_tables;
15
16 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
17 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
18 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
19
20 PROCEDURE InsertDrvStgIns (
21 p_object_id IN NUMBER,
22 p_object_type IN VARCHAR2,
23 x_return_status OUT NOCOPY VARCHAR2
24 );
25
26 PROCEDURE InsertGenStg(
27 p_is_b2b IN BOOLEAN,
28 p_model_type IN VARCHAR2,
29 p_is_org_prod IN BOOLEAN
30 );
31
32 PROCEDURE InsertExpStg(
33 p_is_b2b IN BOOLEAN,
34 p_model_type IN VARCHAR2,
35 p_is_org_prod IN BOOLEAN
36 );
37
38 PROCEDURE InsertAggStg(
39 p_is_b2b IN BOOLEAN
40 );
41
42 PROCEDURE InsertAggStgOrg;
43
44 PROCEDURE InsertBICStg(
45 p_is_b2b IN BOOLEAN,
46 p_model_type IN VARCHAR2,
47 p_is_org_prod IN BOOLEAN
48 );
49
50 PROCEDURE InsertPartyDetails (x_return_status OUT NOCOPY VARCHAR2);
51
52 PROCEDURE InsertPartyDetailsTime (x_return_status OUT NOCOPY VARCHAR2);
53
54 PROCEDURE InsertDrvStgUpd (
55 p_party_type IN VARCHAR2,
56 x_return_status OUT NOCOPY VARCHAR2
57 );
58
59 PROCEDURE UpdatePartyDetails;
60
61 PROCEDURE UpdatePartyDetailsTime;
62
63 -- kbasavar migrated chi's changes for bug 3089951
64 -- Synchronizes the records of both ams_dm_party_details
65 -- and ams_dm_party_details_time. The commits done at
66 -- the end of each procedure to release rollback space
67 -- could cause the records to go out of sync if one table's
68 -- insert completes while the other fails.
69 PROCEDURE sync_party_tables;
70
71
72 --
73 -- Procedure Bodies
74 --
75 -- ******** OBSOLETED ***********
76 -- kbasavar migrated chi's changes for bug 3089951
77 -- nyostos - Sept 10, 2003 - Commented out procedure
78 --
79 -- Procedure DeleteStg
80 -- This has been added temporarily till we figure out how to
81 -- execute Truncate Table. This proc must be removed after
82 -- a truncate table procedure is resolved.
83
84 --PROCEDURE DeleteStg
85 --IS
86 -- l_api_name CONSTANT VARCHAR2(30) := 'Delete_Stg';
87 -- l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
88 -- l_return_status VARCHAR2(1);
89 --BEGIN
90
91 --------------------- initialize -----------------------
92 -- SAVEPOINT Delete_Stg;
93
94 -- IF (AMS_DEBUG_HIGH_ON) THEN
95 -- AMS_Utility_PVT.debug_message (l_full_name || ': Start');
96 -- END IF;
97
98 -- Delete all staging tables
99
100 -- DELETE FROM ams_dm_drv_stg;
101 -- DELETE FROM ams_dm_gen_stg;
102 -- DELETE FROM ams_dm_agg_stg;
103 -- DELETE FROM ams_dm_bic_stg;
104 -- DELETE FROM ams_dm_perint_stg;
105 -- DELETE FROM ams_dm_finnum_stg;
106 -- DELETE FROM ams_dm_party_profile_stg;
107
108 -------------------- finish --------------------------
109 -- IF (AMS_DEBUG_HIGH_ON) THEN
110 -- AMS_Utility_PVT.debug_message (l_full_name || ': End');
111 -- END IF;
112
113 --END DeleteStg;
114
115
116 PROCEDURE TruncateTable (
117 p_table IN VARCHAR2
118 )
119 IS
120 -- Does the DDL for each table
121
122 l_api_name CONSTANT VARCHAR2(30) := 'TruncateTable';
123 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
124
125 l_result BOOLEAN;
126 l_status VARCHAR2(10);
127 l_industry VARCHAR2(10);
128 l_ams_schema VARCHAR2(30);
129 BEGIN
130 --------------------perform truncate table---------------
131 l_result := fnd_installation.get_app_info(
132 'AMS',
133 l_status,
134 l_industry,
135 l_ams_schema
136 );
137
138 IF (AMS_DEBUG_HIGH_ON) THEN
139 AMS_Utility_PVT.debug_message (l_full_name || ':: table : ' || l_ams_schema || '.' || p_table);
140 END IF;
141
142 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || l_ams_schema || '.' || p_table);
143
144 END TruncateTable;
145
146 PROCEDURE TruncateStgTables
147 IS
148 -- TruncateStgTables
149 -- This procedure cleans up the staging area prior to performing any data
150 -- extraction. Run this proc before all data loads. Do not purge the
151 -- data after the load is complete. Purge just prior to next load.
152 l_api_name CONSTANT VARCHAR2(30) := 'TruncateStgTables';
153 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
154 BEGIN
155
156 --------------------- initialize -----------------------
157 IF (AMS_DEBUG_HIGH_ON) THEN
158 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
159 END IF;
160
161 -------------truncate all stage tables---------------------
162 -- nyostos - Sep 11, 2002
163 -- Changes related to parallel mining processes using Global Temporary Tables
164 --TruncateTable ('AMS_DM_DRV_STG');
165 --TruncateTable ('AMS_DM_GEN_STG');
166 --TruncateTable ('AMS_DM_PERINT_STG');
167 --TruncateTable ('AMS_DM_FINNUM_STG');
168 --TruncateTable ('AMS_DM_PARTY_PROFILE_STG');
169 --TruncateTable ('AMS_DM_AGG_STG');
170 --TruncateTable ('AMS_DM_BIC_STG');
171
172 TruncateTable ('AMS_DM_DRV_STG_GT');
173 TruncateTable ('AMS_DM_GEN_STG_GT');
174 TruncateTable ('AMS_DM_PERINT_STG_GT');
175 TruncateTable ('AMS_DM_FINNUM_STG_GT');
176 TruncateTable ('AMS_DM_PROFILE_STG_GT');
177 TruncateTable ('AMS_DM_AGG_STG_GT');
178 TruncateTable ('AMS_DM_BIC_STG_GT');
179
180 -------------------- finish --------------------------
181 IF (AMS_DEBUG_HIGH_ON) THEN
182 AMS_Utility_PVT.debug_message (l_full_name || ': End');
183 END IF;
184
185 END TruncateStgTables;
186 -- End of Purge of the staging area
187
188 PROCEDURE InsertDrvStgIns (
189 p_object_id IN NUMBER,
190 p_object_type IN VARCHAR2,
191 x_return_status OUT NOCOPY VARCHAR2
192 )
193 IS
194 l_api_name CONSTANT VARCHAR2(30) := 'InsertDrvStgIns';
195 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
196 BEGIN
197 --------------------- initialize -----------------------
198 SAVEPOINT Insert_Drv_Stg_Ins;
199
200 IF (AMS_DEBUG_HIGH_ON) THEN
201 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
202 END IF;
203
204 ----------------------- Insert ----------------------
205 -- Insert new records in Driving Stage
206
207 -- nyostos - Sept 11, 2003
208 -- changes for parallel mining processes using global temporary tables
209 --INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG,DEFAULT,DEFAULT)*/
210 --INTO ams_dm_drv_stg (
211 INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG_GT,DEFAULT,DEFAULT)*/
212 INTO ams_dm_drv_stg_gt (party_id)
213 SELECT ads.party_id party_id
214 FROM ams_dm_source ads
215 WHERE ads.used_for_object_id = p_object_id
216 AND ads.arc_used_for_object = p_object_type
217 AND NOT EXISTS (
218 SELECT pdt.party_id party_id
219 FROM ams_dm_party_details pdt
220 WHERE ads.party_id = pdt.party_id
221 );
222
223 -------------------- finish --------------------------
224 COMMIT;
225
226 IF (AMS_DEBUG_HIGH_ON) THEN
227 AMS_Utility_PVT.debug_message (l_full_name || ': End');
228 END IF;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 ROLLBACK TO Insert_Drv_Stg_Ins;
233 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
234 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
235 END IF;
236
237 END InsertDrvStgIns;
238 -- End inserting into Driving Stage for the Insert Process.
239
240 PROCEDURE InsertDrvStgUpd (
241 p_party_type IN VARCHAR2,
242 x_return_status OUT NOCOPY VARCHAR2
243 )
244 IS
245 l_api_name CONSTANT VARCHAR2(30) := 'InsertDrvStgUpd';
246 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
247 BEGIN
248 --------------------- initialize -----------------------
249 SAVEPOINT Insert_Drv_Stg_Upd;
250
251 IF (AMS_DEBUG_HIGH_ON) THEN
252 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
253 END IF;
254
255 ----------------------- Insert ----------------------
256 -- Insert party_ids in Driving Stage that have been updated since last load.
257
258 -- nyostos - Sept 11, 2003
259 -- changes for parallel mining processes using global temporary tables
260
261 --INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG,DEFAULT,DEFAULT)*/
262 --INTO ams_dm_drv_stg (
263 -- kbasavar 6/11/2004 Commented out the exists condition for bug 3278796
264 -- This could be a candidate for a performance issue in the future. As we will go against all parties in party details
265 -- The table could grow significantly in time
266 INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG_GT,DEFAULT,DEFAULT)*/
267 INTO ams_dm_drv_stg_gt (
268 party_id)
269 SELECT x.party_id
270 FROM ams_dm_party_details x
271 WHERE x.party_type = p_party_type
272 /* AND EXISTS (
273 (SELECT a.party_id
274 FROM hz_parties a
275 WHERE a.last_update_date > x.last_update_date
276 AND a.party_id = x.party_id
277 AND a.status = 'A')
278 UNION ALL
279 (SELECT b.party_id
280 FROM hz_person_profiles b
281 WHERE b.last_update_date > x.last_update_date
282 AND b.party_id = x.party_id
283 AND SYSDATE BETWEEN b.effective_start_date AND NVL(b.effective_end_date,SYSDATE))
284 UNION ALL
285 (SELECT c.party_id
286 FROM hz_relationships c
287 WHERE c.last_update_date > x.last_update_date
288 AND c.subject_table_name = 'HZ_PARTIES'
289 AND c.object_table_name = 'HZ_PARTIES'
290 AND c.directional_flag = 'F'
291 AND c.party_id = x.party_id
292 AND c.status = 'A' AND SYSDATE BETWEEN c.start_date AND NVL(c.end_date,SYSDATE))
293 UNION ALL
294 (SELECT d.party_id
295 FROM hz_organization_profiles d
296 WHERE d.last_update_date > x.last_update_date
297 AND d.party_id = x.party_id
298 AND SYSDATE BETWEEN d.effective_start_date AND NVL(d.effective_end_date,SYSDATE))
299 UNION ALL
300 (SELECT e.party_id
301 FROM hz_employment_history e
302 WHERE e.last_update_date > x.last_update_date
303 AND e.party_id = x.party_id
304 AND e.status = 'A')
305 UNION ALL
306 (SELECT f.party_id
307 FROM hz_person_interest f
308 WHERE f.last_update_date > x.last_update_date
309 AND f.party_id = x.party_id
310 AND f.status = 'A')
311 )
312 */
313 ;
314 -------------------- finish --------------------------
315 COMMIT;
316
317 IF (AMS_DEBUG_HIGH_ON) THEN
318 AMS_Utility_PVT.debug_message (l_full_name || ': End');
319 END IF;
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 ROLLBACK TO Insert_Drv_Stg_Upd;
324 -- kbasavar migrated chi's changes for 3089951
325 x_return_status := FND_API.g_ret_sts_error;
326
327 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
328 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
329 END IF;
330 END InsertDrvStgUpd;
331 -- End of identifying changed party ids.
332
333 PROCEDURE InsertGenStg(
334 p_is_b2b IN BOOLEAN,
335 p_model_type IN VARCHAR2,
336 p_is_org_prod IN BOOLEAN
337 )
338 IS
339 -- Insert Data in staging area for simple 1-1 mapping..
340 l_api_name CONSTANT VARCHAR2(30) := 'InsertGenStg';
341 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
342 l_is_b2b BOOLEAN;
343 BEGIN
344 --------------------- initialize -----------------------
345 SAVEPOINT Insert_Gen_Stg;
346
347 l_is_b2b:=p_is_b2b; --kbasavar 07/14/2003
348
349 IF (AMS_DEBUG_HIGH_ON) THEN
350 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
351 END IF;
352
353 ----------------------- Insert ----------------------
354 /*********** Correction made in code krmukher 03/19/2001 ********************/
355 IF l_is_b2b THEN
356 -- Insert for B2B
357 IF p_model_type = 'CUSTOMER_PROFITABILITY' OR p_is_org_prod THEN
358 INSERT /*+ first_rows*/
359 -- INTO ams_dm_gen_stg (
360 INTO ams_dm_gen_stg_gt (
361 party_id,
362 party_type,
363 country,
364 state,
365 province,
366 county,
367 zip_code,
368 paydex_score_year,
369 paydex_score_3_month_ago,
370 industry_paydex_median,
371 global_failure_score,
372 dnb_score,
373 out_of_business_flag,
374 customer_quality_rank,
375 fortune_500_rank,
376 num_of_employees,
377 legal_status,
378 year_established,
379 sic_code1,
380 minority_business_flag,
381 small_business_flag,
382 women_owned_bus_flag,
383 gov_org_flag,
384 hq_subsidiary_flag,
385 foreign_owned_flag,
386 import_export_bus_flag,
387 email_address,
388 address1,
389 address2,
390 competitor_flag,
391 third_party_flag,
392 control_yr,
393 line_of_business,
394 cong_dist_code,
395 labor_surplus_flag,
396 debarment_flag,
397 disadv_8a_flag,
398 debarments_count,
399 months_since_last_debarment,
400 gsa_indicator_flag,
401 analysis_fy,
402 fiscal_yearend_month,
403 curr_fy_potential_revenue,
404 next_fy_potential_revenue,
405 organization_type,
406 business_scope,
407 corporation_class,
408 registration_type,
409 incorp_year,
410 public_private_ownership_flag,
411 internal_flag,
412 high_credit,
413 avg_high_credit,
414 total_payments,
415 credit_score_class,
416 credit_score_natl_percentile,
417 credit_score_incd_default,
418 credit_score_age,
422 maximum_credit_recommendation,
419 failure_score_class,
420 failure_score_incd_default,
421 failure_score_age,
423 maximum_credit_currency_code,
424 party_name,
425 city
426 )
427 SELECT
428 drv.party_id party_id,
429 hzp.party_type party_type,
430 hzp.country country,
431 hzp.state state,
432 hzp.province province,
433 hzp.county county,
434 hzp.postal_code zip_code,
435 hop.paydex_score paydex_score_year,
436 hop.paydex_three_months_ago paydex_score_3_month_avg,
437 hop.paydex_norm industry_paydex_median,
438 hop.global_failure_score global_failure_score,
439 hop.db_rating dnb_score,
440 hop.oob_ind out_of_business_flag,
441 NULL customer_quality_rank,
442 NULL fortune_500_rank,
443 hop.employees_total num_of_employees,
444 hop.legal_status legal_status,
445 hop.year_established year_established,
446 hop.sic_code sic_code1,
447 hop.minority_owned_ind minority_business_flag,
448 hop.small_bus_ind small_business_flag,
449 hop.woman_owned_ind women_owned_bus_flag,
450 NULL gov_org_flag,
451 NULL hq_subsidiary_flag,
452 NULL foreign_owned_flag,
453 DECODE (hop.import_ind || hop.export_ind, 'YY', 'Y', 'YN', 'Y', 'NY', 'Y', 'Y', 'Y', NULL, NULL, 'N') import_export_bus_flag,
454 hzp.email_address,
455 hzp.address1,
456 hzp.address2,
457 hzp.competitor_flag,
458 hzp.third_party_flag,
459 hop.control_yr,
460 hop.line_of_business,
461 hop.cong_dist_code,
462 hop.labor_surplus_ind,
463 hop.debarment_ind,
464 hop.disadv_8a_ind,
465 hop.debarments_count,
466 ABS (MONTHS_BETWEEN (SYSDATE, hop.debarments_date)),
467 hop.gsa_indicator_flag,
468 hop.analysis_fy,
469 hop.fiscal_yearend_month,
470 hop.curr_fy_potential_revenue,
471 hop.next_fy_potential_revenue,
472 hop.organization_type,
473 hop.business_scope,
474 hop.corporation_class,
475 hop.registration_type,
476 hop.incorp_year,
477 hop.public_private_ownership_flag,
478 hop.internal_flag,
479 hop.high_credit,
480 hop.avg_high_credit,
481 hop.total_payments,
482 hop.credit_score_class,
483 hop.credit_score_natl_percentile,
484 hop.credit_score_incd_default,
485 hop.credit_score_age,
486 hop.failure_score_class,
487 hop.failure_score_incd_default,
488 hop.failure_score_age,
489 hop.maximum_credit_recommendation,
490 hop.maximum_credit_currency_code,
491 hzp.party_name,
492 hzp.city
493 FROM
494 -- ams_dm_drv_stg drv,
495 ams_dm_drv_stg_gt drv, -- nysotos - Sep 15, 2003 - Global Temp Table
496 hz_organization_profiles hop,
497 hz_parties hzp
498 WHERE
499 drv.party_id = hzp.party_id
500 AND hzp.status = 'A'
501 AND hop.party_id(+) = hzp.party_id
502 AND hop.status(+) = 'A'
503 AND (SYSDATE BETWEEN hop.effective_start_date(+) and NVL(hop.effective_end_date(+),SYSDATE));
504 ELSE
505 INSERT /*+ first_rows*/
506 -- INTO ams_dm_gen_stg (
507 INTO ams_dm_gen_stg_gt ( -- nysotos - Sep 15, 2003 - Global Temp Table
508 party_id,
509 party_type,
510 gender,
511 ethnicity,
512 marital_status,
513 personal_income,
514 hoh_flag,
515 household_income,
516 household_size,
517 rent_flag,
518 degree_received,
519 school_type,
520 employed_flag,
521 years_employed,
522 occupation,
523 military_branch,
524 presence_of_children,
525 country,
526 state,
527 province,
528 county,
529 zip_code,
530 reference_use_flag,
531 paydex_score_year,
532 paydex_score_3_month_ago,
533 industry_paydex_median,
534 global_failure_score,
535 dnb_score,
539 num_of_employees,
536 out_of_business_flag,
537 customer_quality_rank,
538 fortune_500_rank,
540 legal_status,
541 year_established,
542 sic_code1,
543 minority_business_flag,
544 small_business_flag,
545 women_owned_bus_flag,
546 gov_org_flag,
547 hq_subsidiary_flag,
548 foreign_owned_flag,
549 import_export_bus_flag,
550 email_address,
551 address1,
552 address2,
553 competitor_flag,
554 third_party_flag,
555 person_first_name,
556 person_middle_name,
557 person_last_name,
558 person_name_suffix,
559 person_title,
560 person_academic_title,
561 person_pre_name_adjunct,
562 control_yr,
563 line_of_business,
564 cong_dist_code,
565 labor_surplus_flag,
566 debarment_flag,
567 disadv_8a_flag,
568 debarments_count,
569 months_since_last_debarment,
570 gsa_indicator_flag,
571 analysis_fy,
572 fiscal_yearend_month,
573 curr_fy_potential_revenue,
574 next_fy_potential_revenue,
575 organization_type,
576 business_scope,
577 corporation_class,
578 registration_type,
579 incorp_year,
580 public_private_ownership_flag,
581 internal_flag,
582 high_credit,
583 avg_high_credit,
584 total_payments,
585 credit_score_class,
586 credit_score_natl_percentile,
587 credit_score_incd_default,
588 credit_score_age,
589 failure_score_class,
590 failure_score_incd_default,
591 failure_score_age,
592 maximum_credit_recommendation,
593 maximum_credit_currency_code,
594 party_name,
595 city
596 )
597 SELECT
598 drv.party_id party_id,
599 hzp.party_type party_type,
600 hpp.gender gender,
601 hpp.declared_ethnicity ethnicity,
602 hpp.marital_status marital_status,
603 hpp.personal_income personal_income,
604 hpp.head_of_household_flag hoh_flag,
605 hpp.household_income household_income,
606 hpp.household_size household_size,
607 DECODE(hpp.rent_own_ind, 'RENT', 1, 0) rent_flag,
608 NULL degree_received,
609 NULL school_type,
610 DECODE(heh.end_date, NULL, 1, 0) employed_flag,
611 DECODE(heh.end_date, NULL, (SYSDATE - heh.begin_date)/365 , (heh.end_date - heh.begin_date)/365) years_employed,
612 DECODE(heh.end_date, NULL, heh.employed_as_title, 'UNEMPLOYED') occupation,
613 heh.branch military_branch,
614 NULL num_of_children,
615 hzp.country country,
616 hzp.state state,
617 hzp.province province,
618 hzp.county county,
619 hzp.postal_code zip_code,
620 hoc.reference_use_flag reference_use_flag,
621 hop.paydex_score paydex_score_year,
622 hop.paydex_three_months_ago paydex_score_3_month_avg,
623 hop.paydex_norm industry_paydex_median,
624 hop.global_failure_score global_failure_score,
625 hop.db_rating dnb_score,
626 hop.oob_ind out_of_business_flag,
627 NULL customer_quality_rank,
628 NULL fortune_500_rank,
629 hop.employees_total num_of_employees,
630 hop.legal_status legal_status,
634 hop.small_bus_ind small_business_flag,
631 hop.year_established year_established,
632 hop.sic_code sic_code1,
633 hop.minority_owned_ind minority_business_flag,
635 hop.woman_owned_ind women_owned_bus_flag,
636 NULL gov_org_flag,
637 NULL hq_subsidiary_flag,
638 NULL foreign_owned_flag,
639 DECODE (hop.import_ind || hop.export_ind, 'YY', 'Y', 'YN', 'Y', 'NY', 'Y', 'Y', 'Y', NULL, NULL, 'N') import_export_bus_flag,
640 hzp.email_address,
641 hzp.address1,
642 hzp.address2,
643 hzp.competitor_flag,
644 hzp.third_party_flag,
645 hpp.person_first_name,
646 hpp.person_middle_name,
647 hpp.person_last_name,
648 hpp.person_name_suffix,
649 hpp.person_title,
650 hpp.person_academic_title,
651 hpp.person_pre_name_adjunct,
652 hop.control_yr,
653 hop.line_of_business,
654 hop.cong_dist_code,
655 hop.labor_surplus_ind,
656 hop.debarment_ind,
657 hop.disadv_8a_ind,
658 hop.debarments_count,
659 ABS (MONTHS_BETWEEN (SYSDATE, hop.debarments_date)),
660 hop.gsa_indicator_flag,
661 hop.analysis_fy,
662 hop.fiscal_yearend_month,
663 hop.curr_fy_potential_revenue,
664 hop.next_fy_potential_revenue,
665 hop.organization_type,
666 hop.business_scope,
667 hop.corporation_class,
668 hop.registration_type,
669 hop.incorp_year,
670 hop.public_private_ownership_flag,
671 hop.internal_flag,
672 hop.high_credit,
673 hop.avg_high_credit,
674 hop.total_payments,
675 hop.credit_score_class,
676 hop.credit_score_natl_percentile,
677 hop.credit_score_incd_default,
678 hop.credit_score_age,
679 hop.failure_score_class,
680 hop.failure_score_incd_default,
681 hop.failure_score_age,
682 hop.maximum_credit_recommendation,
683 hop.maximum_credit_currency_code,
684 hzp.party_name,
685 hzp.city
686 FROM
687 -- ams_dm_drv_stg drv,
688 ams_dm_drv_stg_gt drv, -- nysotos - Sep 15, 2003 - Global Temp Table
689 hz_person_profiles hpp,
690 hz_organization_profiles hop,
691 hz_org_contacts hoc,
692 hz_employment_history heh,
693 hz_relationships hpr,
694 hz_parties hzp
695 WHERE
696 drv.party_id = hzp.party_id
697 AND hzp.status = 'A'
698 AND drv.party_id = hpr.party_id
699 AND hpr.status = 'A'
700 AND hpr.subject_table_name = 'HZ_PARTIES'
701 AND hpr.object_table_name = 'HZ_PARTIES'
702 AND hpr.directional_flag = 'F'
703 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
704 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
705 AND hpp.party_id(+) = hpr.subject_id
706 AND (SYSDATE BETWEEN hpp.effective_start_date(+) and NVL(hpp.effective_end_date(+),SYSDATE))
707 AND hop.party_id(+) = hpr.object_id
708 AND hop.status(+) = 'A'
709 AND (SYSDATE BETWEEN hop.effective_start_date(+) and NVL(hop.effective_end_date(+),SYSDATE))
710 AND hpr.relationship_id = hoc.party_relationship_id(+)
711 AND heh.party_id(+) = hpr.subject_id
712 AND heh.status(+) = 'A';
713 END IF;
714 ELSE
715 INSERT /*+ first_rows*/
716 -- INTO ams_dm_gen_stg (
717 INTO ams_dm_gen_stg_gt ( -- nysotos - Sep 15, 2003 - Global Temp Table
718 party_id,
719 party_type,
720 gender,
721 ethnicity,
722 marital_status,
723 personal_income,
724 hoh_flag,
725 household_income,
726 household_size,
727 rent_flag,
728 degree_received,
729 school_type,
730 employed_flag,
731 years_employed,
732 occupation,
733 military_branch,
734 presence_of_children,
735 country,
736 state,
737 province,
738 county,
739 zip_code,
740 email_address,
741 address1,
742 address2,
743 competitor_flag,
744 third_party_flag,
745 person_first_name,
746 person_middle_name,
747 person_last_name,
748 person_name_suffix,
749 person_title,
750 person_academic_title,
751 person_pre_name_adjunct,
752 party_name,
753 city
754 )
755 SELECT
756 drv.party_id party_id,
757 hzp.party_type party_type,
761 hpp.personal_income personal_income,
758 hpp.gender gender,
759 hpp.declared_ethnicity ethnicity,
760 hpp.marital_status marital_status,
762 hpp.head_of_household_flag hoh_flag,
763 hpp.household_income household_income,
764 hpp.household_size household_size,
765 DECODE(hpp.rent_own_ind, 'RENT', 1, 0) rent_flag,
766 NULL degree_received,
767 NULL school_type,
768 DECODE(heh.end_date, NULL, 1, 0) employed_flag,
769 DECODE(heh.end_date, NULL, (SYSDATE - heh.begin_date)/365 , (heh.end_date - heh.begin_date)/365) years_employed,
770 DECODE(heh.end_date, NULL, heh.employed_as_title, 'UNEMPLOYED') occupation,
771 heh.branch military_branch,
772 NULL num_of_children,
773 hzp.country country,
774 hzp.state state,
775 hzp.province province,
776 hzp.county county,
777 hzp.postal_code zip_code,
778 hzp.email_address,
779 hzp.address1,
780 hzp.address2,
781 hzp.competitor_flag,
782 hzp.third_party_flag,
783 hzp.person_first_name,
784 hzp.person_middle_name,
785 hzp.person_last_name,
786 hzp.person_name_suffix,
787 hzp.person_title,
788 hzp.person_academic_title,
789 hzp.person_pre_name_adjunct,
790 hzp.party_name,
791 hzp.city
792 FROM
793 -- ams_dm_drv_stg drv,
794 ams_dm_drv_stg_gt drv, -- nysotos - Sep 15, 2003 - Global Temp Table
795 hz_person_profiles hpp,
796 hz_employment_history heh,
797 hz_parties hzp
798 WHERE
799 drv.party_id = hzp.party_id
800 AND hzp.status = 'A'
801 AND hpp.party_id(+) = hzp.party_id
802 AND heh.party_id(+) = hzp.party_id
803 AND heh.status(+) = 'A'
804 AND (SYSDATE BETWEEN hpp.effective_start_date(+) and NVL(hpp.effective_end_date(+),SYSDATE));
805
806 END IF;
807
808 COMMIT;
809
810 IF (AMS_DEBUG_HIGH_ON) THEN
811 AMS_Utility_PVT.debug_message (l_full_name || ': End');
812 END IF;
813
814 EXCEPTION
815 WHEN OTHERS THEN
816 ROLLBACK TO Insert_Gen_Stg;
817
818 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
819 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
820 END IF;
821 END InsertGenStg;
822 -- End of 1-1 mapping
823
824 PROCEDURE InsertExpStg(
825 p_is_b2b IN BOOLEAN,
826 p_model_type IN VARCHAR2,
827 p_is_org_prod IN BOOLEAN
828 )
829 IS
830
831 -- Proc to swap multiple rows to multiple columns
832 l_api_name CONSTANT VARCHAR2(30) := 'InsertExpStg';
833 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
834 l_is_b2b BOOLEAN;
835
836 BEGIN
840
837 --------------------- initialize -----------------------
838 SAVEPOINT Insert_Exp_Stg;
839 l_is_b2b:=p_is_b2b; -- kbasavar 7/14/2003
841 IF (AMS_DEBUG_HIGH_ON) THEN
842 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
843 AMS_Utility_PVT.debug_message (l_full_name || ': Insert');
844 END IF;
845
846 IF l_is_b2b THEN
847 IF p_model_type<>'CUSTOMER_PROFITABILITY' OR p_is_org_prod THEN
848 -- INSERT INTO ams_dm_perint_stg (
849 INSERT INTO ams_dm_perint_stg_gt ( -- nysotos - Sep 15, 2003 - Global Temp Table
850 party_id,
851 -- interest related attributes
852 interest_art_flag,
853 interest_books_flag,
854 interest_movies_flag,
855 interest_music_flag,
856 interest_theater_flag,
857 interest_travel_flag,
858 interest_drink_flag,
859 interest_smoke_flag,
860 interest_other_flag)
861 SELECT
862 drv.party_id party_id,
863 -- interest attributes
864 MAX (DECODE (hpi.interest_type_code, 'ART', 1, 0)) interest_art_flag,
865 MAX (DECODE (hpi.interest_type_code, 'BOOKS',1,0)) interest_books_flag,
866 MAX (DECODE (hpi.interest_type_code, 'MOVIES',1,0)) interest_movies_flag,
867 MAX (DECODE (hpi.interest_type_code, 'MUSIC',1,0)) interest_music_flag,
868 MAX (DECODE (hpi.interest_type_code, 'THEATER',1,0)) interest_theater_flag,
869 MAX (DECODE (hpi.interest_type_code, 'TRAVEL',1,0)) interest_travel_flag,
870 MAX (DECODE (hpi.interest_type_code, 'DRINK',1,0)) interest_drink_flag,
871 MAX (DECODE (hpi.interest_type_code, 'SMOKE',1,0)) interest_smoke_flag,
872 MAX (DECODE (hpi.interest_type_code, 'ART',0,
873 'BOOKS',0,
874 'MOVIES',0,
875 'MUSIC',0,
876 'THEATER',0,
877 'TRAVEL',0,
878 'DRINK',0,
879 'SMOKE',0, 1)) interest_other_flag
880 -- FROM ams_dm_drv_stg drv,
881 FROM ams_dm_drv_stg_gt drv, -- nyostos - Sept 15, 2003 - Global Temp Table
882 hz_person_interest hpi,
883 hz_relationships hpr
884 WHERE drv.party_id = hpr.party_id --it's the party of type relationship
885 AND hpr.status = 'A'
886 AND hpr.subject_table_name = 'HZ_PARTIES'
887 AND hpr.object_table_name = 'HZ_PARTIES'
888 AND hpr.directional_flag = 'F'
889 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
890 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
891 AND hpi.party_id(+) = hpr.subject_id
892 AND hpi.status(+) = 'A'
893 GROUP BY drv.party_id
894 ;
895 END IF;
896 ELSE
897 -- INSERT INTO ams_dm_perint_stg (
898 INSERT INTO ams_dm_perint_stg_gt ( -- nyostos - Sept 15, 2003 - Global Temp Table
899 party_id,
900 -- interest related attributes
901 interest_art_flag,
902 interest_books_flag,
903 interest_movies_flag,
904 interest_music_flag,
905 interest_theater_flag,
906 interest_travel_flag,
907 interest_drink_flag,
908 interest_smoke_flag,
909 interest_other_flag)
910 SELECT
911 drv.party_id party_id,
912 -- interest attributes
913 MAX (DECODE (hpi.interest_type_code, 'ART', 1, 0)) interest_art_flag,
914 MAX (DECODE (hpi.interest_type_code, 'BOOKS',1,0)) interest_books_flag,
915 MAX (DECODE (hpi.interest_type_code, 'MOVIES',1,0)) interest_movies_flag,
916 MAX (DECODE (hpi.interest_type_code, 'MUSIC',1,0)) interest_music_flag,
917 MAX (DECODE (hpi.interest_type_code, 'THEATER',1,0)) interest_theater_flag,
918 MAX (DECODE (hpi.interest_type_code, 'TRAVEL',1,0)) interest_travel_flag,
919 MAX (DECODE (hpi.interest_type_code, 'DRINK',1,0)) interest_drink_flag,
920 MAX (DECODE (hpi.interest_type_code, 'SMOKE',1,0)) interest_smoke_flag,
921 MAX (DECODE (hpi.interest_type_code, 'ART',0,
922 'BOOKS',0,
923 'MOVIES',0,
924 'MUSIC',0,
925 'THEATER',0,
926 'TRAVEL',0,
927 'DRINK',0,
928 'SMOKE',0, 1)) interest_other_flag
932 WHERE drv.party_id = hpi.party_id(+)
929 -- FROM ams_dm_drv_stg drv,
930 FROM ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Table
931 hz_person_interest hpi
933 AND hpi.status(+) = 'A'
934 GROUP BY drv.party_id
935 ;
936
937 END IF;
938
939 -- financial number attributes
940 IF l_is_b2b THEN --kbasavar
941 IF p_model_type='CUSTOMER_PROFITABILITY' OR p_is_org_prod THEN
942 -- INSERT INTO ams_dm_finnum_stg (
943 INSERT INTO ams_dm_finnum_stg_gt ( -- nyostos - Sep 15, 2003 -
944 party_id,
945 gross_annual_income,
946 debt_to_income_ratio,
947 net_worth,
948 total_assets,
949 tot_debt_outstanding,
950 gross_annual_sales,
951 current_assets,
952 current_liabilities,
953 net_profit,
954 accounts_receivable,
955 retained_earnings)
956 SELECT
957 drv.party_id,
958 SUM(DECODE (hzf.financial_number_name,'GROSS_INCOME',hzf.financial_number,0)) gross_annual_income ,
959 AVG(DECODE (hzf.financial_number_name,'LONG_TERM_DEBT',
960 hzf.financial_number,0) / DECODE (hzf.financial_number_name,'GROSS_INCOME',
961 hzf.financial_number,1)) debt_to_income_ratio ,
962 SUM(DECODE (hzf.financial_number_name,'NET_WORTH',hzf.financial_number,0)) net_worth ,
963 SUM(DECODE (hzf.financial_number_name,'TOTAL_ASSETS',hzf.financial_number,0)) total_assets ,
964 SUM(DECODE (hzf.financial_number_name,'LONG_TERM_DEBT',hzf.financial_number,0)) tot_debt_outstanding ,
965 SUM(DECODE (hzf.financial_number_name,'SALES',hzf.financial_number,0)) gross_annual_sales ,
966 SUM(DECODE (hzf.financial_number_name,'TOTAL_CURRENT_ASSETS',hzf.financial_number,0)) current_assets ,
967 SUM(DECODE (hzf.financial_number_name,'TOTAL_CURR_LIABILITIES',hzf.financial_number,0)) current_liabilities ,
968 SUM(DECODE (hzf.financial_number_name,'PROFIT_BEFORE_TAX',hzf.financial_number,0)) net_profit ,
969 SUM(DECODE (hzf.financial_number_name,'ACCOUNTS_RECEIVABLE',hzf.financial_number,0)) accounts_receivable ,
970 SUM(DECODE (hzf.financial_number_name,'RETAINED_EARNINGS',hzf.financial_number,0)) retained_earnings
974 WHERE drv.party_id = hfr.party_id(+)
971 -- nyostos - Sep 15, 2003 - Global Temp Table
972 -- FROM ams_dm_drv_stg drv, hz_financial_numbers hzf, hz_financial_reports hfr
973 FROM ams_dm_drv_stg_gt drv, hz_financial_numbers hzf, hz_financial_reports hfr
975 AND hfr.status(+) = 'A'
976 AND hfr.consolidated_ind(+) = 'C' -- wen only want consolidated reports
977 AND hfr.financial_report_id = hzf.financial_report_id(+)
978 AND hzf.status(+) = 'A'
979 GROUP BY drv.party_id
980 ;
981 ELSE
982 -- nyostos - Sep 15, 2003 - Global Temp Table
983 -- INSERT INTO ams_dm_finnum_stg (
984 INSERT INTO ams_dm_finnum_stg_gt (
985 party_id,
986 gross_annual_income,
987 debt_to_income_ratio,
988 -- num_credit_lines,
989 -- num_trade_lines,
990 net_worth,
991 total_assets,
992 tot_debt_outstanding,
993 -- bankruptcy_flag,
994 -- high_risk_fraud_flag,
995 gross_annual_sales,
996 -- growth_rate_sales_year,
997 -- growth_rate_net_prof_year,
998 current_assets,
999 current_liabilities,
1000 -- total_debts,
1001 net_profit,
1002 -- tangible_net_profit,
1003 -- capital_amount,
1004 -- capital_type_indicator,
1005 accounts_receivable,
1006 retained_earnings)
1007 SELECT
1008 drv.party_id, -- rectify this
1009 SUM(DECODE (hzf.financial_number_name,'GROSS_INCOME',hzf.financial_number,0)) gross_annual_income ,
1010 AVG(DECODE (hzf.financial_number_name,'LONG_TERM_DEBT',
1011 hzf.financial_number,0) / DECODE (hzf.financial_number_name,'GROSS_INCOME',
1012 hzf.financial_number,1)) debt_to_income_ratio ,
1013 -- NULL num_credit_lines , --**CHECK!
1014 -- NULL num_trade_lines , --**CHECK!
1015 SUM(DECODE (hzf.financial_number_name,'NET_WORTH',hzf.financial_number,0)) net_worth ,
1016 SUM(DECODE (hzf.financial_number_name,'TOTAL_ASSETS',hzf.financial_number,0)) total_assets ,
1017 SUM(DECODE (hzf.financial_number_name,'LONG_TERM_DEBT',hzf.financial_number,0)) tot_debt_outstanding ,
1018 -- NULL bankruptcy_flag , --**CHECK!
1019 -- NULL high_risk_fraud_flag , --**CHECK!
1020 SUM(DECODE (hzf.financial_number_name,'SALES',hzf.financial_number,0)) gross_annual_sales ,
1021 -- NULL growth_rate_sales_year , --**CHECK!
1022 -- NULL growth_rate_net_prof_year , --**CHECK!
1023 SUM(DECODE (hzf.financial_number_name,'TOTAL_CURRENT_ASSETS',hzf.financial_number,0)) current_assets ,
1024 SUM(DECODE (hzf.financial_number_name,'TOTAL_CURR_LIABILITIES',hzf.financial_number,0)) current_liabilities ,
1025 -- NULL total_debts ,
1026 SUM(DECODE (hzf.financial_number_name,'PROFIT_BEFORE_TAX',hzf.financial_number,0)) net_profit ,
1027 -- NULL tangible_net_profit , --**CHECK!
1028 -- NULL capital_amt , --**CHECK!
1029 -- NULL capital_type_indicator ,
1030 SUM(DECODE (hzf.financial_number_name,'ACCOUNTS_RECEIVABLE',hzf.financial_number,0)) accounts_receivable ,
1031 SUM(DECODE (hzf.financial_number_name,'RETAINED_EARNINGS',hzf.financial_number,0)) retained_earnings
1032 -- nyostos - Sep 15, 2003 - Global Temp Table
1033 -- FROM ams_dm_drv_stg drv, hz_financial_numbers hzf, hz_financial_reports hfr, hz_relationships hpr
1037 AND hpr.subject_table_name = 'HZ_PARTIES'
1034 FROM ams_dm_drv_stg_gt drv, hz_financial_numbers hzf, hz_financial_reports hfr, hz_relationships hpr
1035 WHERE drv.party_id = hpr.party_id
1036 AND hpr.status = 'A'
1038 AND hpr.object_table_name = 'HZ_PARTIES'
1039 AND hpr.directional_flag = 'F'
1040 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1041 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1042 AND hfr.party_id(+) = hpr.object_id --the org's party id
1043 AND hfr.status(+) = 'A'
1044 AND hfr.consolidated_ind(+) = 'C' -- wen only want consolidated reports
1045 AND hfr.financial_report_id = hzf.financial_report_id(+)
1046 AND hzf.status(+) = 'A'
1047 GROUP BY drv.party_id
1048 ;
1049 END IF;
1050 END IF;
1051
1052
1053 /******** OPEN: which account record do we use? ********/
1054
1055 IF l_is_b2b THEN
1056 IF p_model_type='CUSTOMER_PROFITABILITY' OR p_is_org_prod THEN
1057 -- INSERT INTO ams_dm_party_profile_stg (
1058 INSERT INTO ams_dm_profile_stg_gt ( -- nyostos - Sep 15, 2003 - Global Temp Table
1059 party_id,
1060 credit_check_flag,
1061 tolerance,
1062 discount_terms_flag,
1063 dunning_letters_flag,
1064 interest_charges_flag,
1065 send_statements_flag,
1066 credit_hold_flag,
1067 credit_rating,
1068 risk_code,
1069 interest_period_days,
1070 payment_grace_days)
1071 SELECT
1072 drv.party_id party_id,
1073 MAX(hcp.credit_checking) credit_check_flag,
1074 MAX(hcp.tolerance) tolerance,
1075 MAX(hcp.discount_terms) discount_terms_flag,
1076 MAX(hcp.dunning_letters) dunning_letters_flag,
1077 MAX(hcp.interest_charges) interest_charges_flag,
1078 MAX(hcp.send_statements) send_statements_flag,
1079 MAX(hcp.credit_hold) credit_hold_flag,
1080 MAX(hcp.credit_rating) credit_rating, -- may give erroneous results
1081 MAX(hcp.risk_code) risk_code, --may give erroneous results
1082 MAX(hcp.interest_period_days) interest_period_days,
1083 MAX(hcp.payment_grace_days) payment_grace_days
1084 FROM
1085 -- ams_dm_drv_stg drv,
1086 ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Tables
1087 hz_cust_accounts hca,
1088 hz_customer_profiles hcp
1089 WHERE drv.party_id = hca.party_id(+)
1090 AND hca.status(+) = 'A'
1091 AND hcp.cust_account_id(+) = hca.cust_account_id
1092 AND hcp.status(+) = 'A'
1093 GROUP BY drv.party_id
1094 ;
1095 ELSE
1096 -- INSERT INTO ams_dm_party_profile_stg (
1097 INSERT INTO ams_dm_profile_stg_gt ( -- nyostos - Sep 15, 2003 - Global Temp Table
1098 party_id,
1099 credit_check_flag,
1100 tolerance,
1101 discount_terms_flag,
1102 dunning_letters_flag,
1103 interest_charges_flag,
1104 send_statements_flag,
1105 -- send_credit_balance_flag,
1106 credit_hold_flag,
1107 -- profile_class_code,
1108 credit_rating,
1109 risk_code,
1110 interest_period_days,
1111 payment_grace_days)
1112 SELECT
1113 drv.party_id party_id,
1114 MAX(hcp.credit_checking) credit_check_flag,
1115 MAX(hcp.tolerance) tolerance,
1116 MAX(hcp.discount_terms) discount_terms_flag,
1117 MAX(hcp.dunning_letters) dunning_letters_flag,
1118 MAX(hcp.interest_charges) interest_charges_flag,
1119 MAX(hcp.send_statements) send_statements_flag,
1120 MAX(hcp.credit_hold) credit_hold_flag,
1121 MAX(hcp.credit_rating) credit_rating, -- may give erroneous results
1122 MAX(hcp.risk_code) risk_code, --may give erroneous results
1123 MAX(hcp.interest_period_days) interest_period_days,
1124 MAX(hcp.payment_grace_days) payment_grace_days
1125 FROM
1126 -- ams_dm_drv_stg drv,
1127 ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Tables
1128 hz_cust_accounts hca,
1129 hz_customer_profiles hcp,
1130 hz_relationships hpr
1131 WHERE drv.party_id = hpr.party_id
1132 AND hpr.status = 'A'
1133 AND hpr.subject_table_name = 'HZ_PARTIES'
1134 AND hpr.object_table_name = 'HZ_PARTIES'
1135 AND hpr.directional_flag = 'F'
1136 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1137 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1138 AND hca.party_id = hpr.object_id --the org's party id
1139 AND hca.status = 'A'
1140 AND hcp.cust_account_id(+) = hca.cust_account_id
1141 AND hcp.status(+) = 'A'
1142 GROUP BY drv.party_id
1143 ;
1147 INSERT INTO ams_dm_profile_stg_gt ( -- nyostos - Sep 15, 2003 - Global Temp Table
1144 END IF;
1145 ELSE
1146 -- INSERT INTO ams_dm_party_profile_stg (
1148 party_id,
1149 credit_check_flag,
1150 tolerance,
1151 discount_terms_flag,
1152 dunning_letters_flag,
1153 interest_charges_flag,
1154 send_statements_flag,
1155 -- send_credit_balance_flag,
1156 credit_hold_flag,
1157 -- profile_class_code,
1158 credit_rating,
1159 risk_code,
1160 interest_period_days,
1161 payment_grace_days)
1162 SELECT
1163 drv.party_id party_id,
1164 MAX(hcp.credit_checking) credit_check_flag,
1165 MAX(hcp.tolerance) tolerance,
1166 MAX(hcp.discount_terms) discount_terms_flag,
1167 MAX(hcp.dunning_letters) dunning_letters_flag,
1168 MAX(hcp.interest_charges) interest_charges_flag,
1169 MAX(hcp.send_statements) send_statements_flag,
1170 MAX(hcp.credit_hold) credit_hold_flag,
1171 MAX(hcp.credit_rating) credit_rating, -- may give erroneous results
1172 MAX(hcp.risk_code) risk_code, --may give erroneous results
1173 MAX(hcp.interest_period_days) interest_period_days,
1174 MAX(hcp.payment_grace_days) payment_grace_days
1175 FROM
1176 -- ams_dm_drv_stg drv,
1177 ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Table
1178 hz_cust_accounts hca,
1179 hz_customer_profiles hcp
1180 WHERE hcp.cust_account_id(+) = hca.cust_account_id
1181 AND hcp.status(+) = 'A'
1182 AND drv.party_id = hca.party_id
1183 AND hca.status = 'A'
1184 GROUP BY drv.party_id
1185 ;
1186 END IF;
1187
1188 COMMIT;
1189
1190 IF (AMS_DEBUG_HIGH_ON) THEN
1191 AMS_Utility_PVT.debug_message (l_full_name || ': End');
1192 END IF;
1193
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196 ROLLBACK TO Insert_Exp_Stg;
1197
1198 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1199 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1200 END IF;
1201 END InsertExpStg;
1202
1203 PROCEDURE InsertAggStg(
1204 p_is_b2b IN BOOLEAN
1205 )
1206 IS
1207 l_api_name CONSTANT VARCHAR2(30) := 'InsertAggStg';
1208 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1209
1210 TYPE AMS_DM_AGG_STG_REC_TYPE IS RECORD
1211 (
1212 PARTY_ID NUMBER(15),
1213 AGE NUMBER,
1214 DAYS_SINCE_LAST_SCHOOL NUMBER,
1215 DAYS_SINCE_LAST_EVENT NUMBER,
1216 NUM_TIMES_TARGETED NUMBER,
1217 LAST_TARGETED_CHANNEL_CODE VARCHAR2(30),
1218 TIMES_TARGETED_MONTH NUMBER,
1219 TIMES_TARGETED_3_MONTHS NUMBER,
1220 TIMES_TARGETED_6_MONTHS NUMBER,
1221 TIMES_TARGETED_12_MONTHS NUMBER,
1222 DAYS_SINCE_LAST_TARGETED NUMBER,
1223 AVG_DISC_OFFERED NUMBER,
1224 NUM_TYPES_DISC_OFFERED NUMBER,
1225 DAYS_SINCE_FIRST_CONTACT NUMBER,
1226 DAYS_SINCE_ACCT_ESTABLISHED NUMBER,
1227 DAYS_SINCE_ACCT_TERM NUMBER,
1228 DAYS_SINCE_ACCT_ACTIVATION NUMBER,
1229 DAYS_SINCE_ACCT_SUSPENDED NUMBER,
1230 NUM_TIMES_TARGETED_EMAIL NUMBER,
1231 NUM_TIMES_TARGETED_TELEMKT NUMBER,
1232 NUM_TIMES_TARGETED_DIRECT NUMBER,
1233 NUM_TGT_BY_OFFR_TYP1 NUMBER,
1234 NUM_TGT_BY_OFFR_TYP2 NUMBER,
1235 NUM_TGT_BY_OFFR_TYP3 NUMBER,
1236 NUM_TGT_BY_OFFR_TYP4 NUMBER
1237 );
1238
1239 -- age is in number of days
1240 CURSOR c_age IS
1241 SELECT SYSDATE - hpp.date_of_birth, drv.party_id
1242 FROM hz_person_profiles hpp, ams_dm_drv_stg_gt drv
1243 WHERE hpp.party_id = drv.party_id
1244 AND (SYSDATE BETWEEN hpp.effective_start_date AND NVL(hpp.effective_end_date,SYSDATE))
1245 GROUP BY drv.party_id,hpp.date_of_birth;
1246
1247 CURSOR c_age_b2b IS
1248 SELECT SYSDATE - hpp.date_of_birth,drv.party_id
1249 FROM hz_person_profiles hpp,hz_relationships hpr,ams_dm_drv_stg_gt drv
1250 WHERE hpp.party_id = hpr.subject_id
1251 AND drv.party_id = hpr.party_id
1252 AND hpr.status = 'A'
1253 AND hpr.subject_table_name = 'HZ_PARTIES'
1254 AND hpr.object_table_name = 'HZ_PARTIES'
1255 AND hpr.directional_flag = 'F'
1256 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1257 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1258 AND (SYSDATE BETWEEN hpp.effective_start_date AND NVL(hpp.effective_end_date,SYSDATE))
1259 GROUP BY drv.party_id,hpp.date_of_birth;
1260
1261 CURSOR c_days_since_last_school IS
1262 SELECT SYSDATE - MAX(hze.last_date_attended), drv.party_id
1263 FROM hz_education hze, ams_dm_drv_stg_gt drv
1264 WHERE hze.party_id = drv.party_id
1265 AND hze.status = 'A'
1266 GROUP BY drv.party_id;
1267
1268 CURSOR c_days_since_last_school_b2b IS
1272 AND drv.party_id = hpr.party_id
1269 SELECT SYSDATE - MAX(hze.last_date_attended), drv.party_id
1270 FROM hz_education hze, ams_dm_drv_stg_gt drv, hz_relationships hpr
1271 WHERE hze.party_id = hpr.subject_id
1273 AND hpr.status = 'A'
1274 AND hpr.subject_table_name = 'HZ_PARTIES'
1275 AND hpr.object_table_name = 'HZ_PARTIES'
1276 AND hpr.directional_flag = 'F'
1277 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1278 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1279 AND hze.status = 'A'
1280 GROUP BY drv.party_id;
1281
1282 CURSOR c_days_since_last_event IS
1283 SELECT (SYSDATE - MAX(aeo.event_start_date)), drv.party_id
1284 FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
1285 WHERE aeo.event_offer_id IN (SELECT aer.event_offer_id
1286 FROM ams_event_registrations aer
1287 -- WHERE aer.event_offer_id = aeo.event_offer_id
1288 WHERE aer.attendant_party_id = drv.party_id)
1289 GROUP BY drv.party_id;
1290
1291 CURSOR c_num_times_targeted IS
1292 SELECT COUNT(DISTINCT ale.list_header_id), drv.party_id
1293 FROM ams_list_entries ale, ams_dm_drv_stg_gt drv,ams_list_headers_all hdr
1294 WHERE ale.party_id = drv.party_id
1295 AND hdr.list_header_id = ale.list_header_id
1296 AND hdr.list_type = 'TARGET'
1297 GROUP BY drv.party_id;
1298
1299 -- change to use ams_act_lists
1300 CURSOR c_last_targeted_channel_code IS
1301 SELECT aal.list_used_by_id, drv.party_id
1302 FROM ams_act_lists aal, ams_list_headers_all alh, ams_dm_drv_stg_gt drv
1303 WHERE aal.list_used_by = 'CSCH'
1304 AND aal.list_act_type = 'TARGET'
1305 AND alh.list_header_id = aal.list_header_id
1306 AND alh.sent_out_date = (SELECT MAX(l.sent_out_date)
1307 FROM ams_list_headers_all l
1308 WHERE l.list_header_id IN (SELECT ale.list_header_id
1309 FROM ams_list_entries ale
1310 WHERE ale.party_id = drv.party_id))
1311 GROUP BY drv.party_id, aal.list_used_by_id ;
1312 -- modified krmukher 03/20/2001 per Chi's suggestion
1313 -- all references to party_id in ams_list_entries
1314 -- changed to list_entry_source_system_id
1315 --l_last_targeted_channel_code VARCHAR2(30);
1316 CURSOR c_times_targeted IS
1317 SELECT SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -1),1,0,1)),
1318 SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -3),1,0,1)),
1319 SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -9),1,0,1)),
1320 SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -12),1,0,1)), drv.party_id
1321 FROM ams_list_headers_all alh, ams_dm_drv_stg_gt drv
1322 WHERE alh.sent_out_date IS NOT NULL
1323 AND MONTHS_BETWEEN(sysdate, alh.sent_out_date) <= 12
1324 AND alh.list_header_id IN (SELECT ale.list_header_id
1325 FROM ams_list_entries ale
1326 -- WHERE ale.list_header_id = alh.list_header_id
1327 WHERE ale.party_id = drv.party_id)
1328 GROUP BY drv.party_id;
1329 -- AND ale.party_id = drv.party_id);
1330 -- modified krmukher 03/20/2001 per Chi's suggestion
1331 -- all references to party_id in ams_list_entries
1332 -- changed to list_entry_source_system_id
1333 CURSOR c_days_since_last_targeted IS
1334 SELECT (SYSDATE - MAX(aeo.event_start_date)), drv.party_id
1335 FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
1336 WHERE aeo.event_offer_id IN (SELECT aer.event_offer_id
1337 FROM ams_event_registrations aer
1338 -- WHERE aer.event_offer_id = aeo.event_offer_id
1339 WHERE aer.attendant_party_id = drv.party_id)
1340 GROUP BY drv.party_id;
1341
1342 CURSOR c_avg_disc_offered IS
1343 SELECT AVG(aao.offer_amount), drv.party_id
1344 FROM ams_act_offers aao, ams_campaign_schedules acs, ams_dm_drv_stg_gt drv
1345 WHERE aao.arc_act_offer_used_by = 'CSCH'
1346 AND aao.activity_offer_id = acs.activity_offer_id
1347 AND acs.campaign_schedule_id IN (SELECT aal.list_used_by_id
1348 FROM ams_act_lists aal
1349 WHERE aal.list_used_by = 'CSCH'
1350 AND aal.list_act_type = 'TARGET'
1351 AND aal.list_header_id IN (SELECT ale.list_header_id
1352 FROM ams_list_entries ale
1353 -- WHERE ale.list_header_id = alh.list_header_id
1354 WHERE ale.party_id = drv.party_id))
1355 GROUP BY drv.party_id;
1356 -- AND ale.party_id = drv.party_id));
1357 -- modified krmukher 03/20/2001 per Chi's suggestion
1358 -- all references to party_id in ams_list_entries
1359 -- changed to list_entry_source_system_id
1360
1361 CURSOR c_num_types_disc_offered IS
1362 SELECT COUNT(aao.offer_type), drv.party_id
1363 FROM ams_act_offers aao, ams_campaign_schedules acs, ams_dm_drv_stg_gt drv
1364 WHERE aao.arc_act_offer_used_by = 'CSCH'
1365 AND aao.activity_offer_id = acs.activity_offer_id
1366 AND acs.campaign_schedule_id IN (SELECT aal.list_used_by_id
1370 AND aal.list_act_type = 'TARGET'
1367 FROM ams_list_entries ale, ams_act_lists aal
1368 WHERE aal.list_header_id = ale.list_header_id
1369 AND aal.list_used_by = 'CSCH'
1371 AND ale.party_id = drv.party_id)
1372 GROUP BY drv.party_id;
1373
1374 CURSOR c_days_since_first_contact IS
1375 SELECT (SYSDATE - MIN(aeo.event_start_date)), drv.party_id
1376 FROM ams_event_offers_all_b aeo,ams_dm_drv_stg_gt drv
1377 WHERE aeo.event_offer_id IN (SELECT 1
1378 FROM ams_event_registrations aer
1379 -- WHERE aer.event_offer_id = aeo.event_offer_id
1380 WHERE aer.attendant_party_id = drv.party_id)
1381 GROUP BY drv.party_id;
1382
1383 CURSOR c_days_since_account IS
1384 SELECT SYSDATE - MAX(hca.account_established_date),
1385 0,--SYSDATE - MAX(hca.account_termination_date),
1386 0 --SYSDATE - MAX(hca.account_activation_date)
1387 , drv.party_id
1388 -- SYSDATE - MAX(hca.account_suspension_date)
1389 FROM hz_cust_accounts hca, ams_dm_drv_stg_gt drv
1390 WHERE hca.party_id = drv.party_id
1391 AND hca.status = 'A'
1392 GROUP BY drv.party_id;
1393
1394 CURSOR c_days_since_account_b2b IS
1395 SELECT SYSDATE - MAX(hca.account_established_date),
1396 0,--SYSDATE - MAX(hca.account_termination_date),
1397 0 --SYSDATE - MAX(hca.account_activation_date)
1398 , drv.party_id
1399 -- SYSDATE - MAX(hca.account_suspension_date)
1400 FROM hz_cust_accounts hca, ams_dm_drv_stg_gt drv, hz_relationships hpr
1401 WHERE hca.party_id = hpr.object_id
1402 AND drv.party_id = hpr.party_id
1403 AND hpr.status = 'A'
1404 AND hpr.subject_table_name = 'HZ_PARTIES'
1405 AND hpr.object_table_name = 'HZ_PARTIES'
1406 AND hpr.directional_flag = 'F'
1407 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1408 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1409 AND hca.status = 'A'
1410 GROUP BY drv.party_id;
1411
1412 CURSOR c_num_times_tgt_chnl IS
1413 /* commented out by amisingh
1414 SELECT SUM(DECODE(UPPER(ame.media_name), 'EMAIL',1,0)),
1415 SUM(DECODE(UPPER(ame.media_name), 'TELEMARKETING',1,0)),
1416 SUM(DECODE(UPPER(ame.media_name), 'DIRECT MAIL',1,0))
1417 */
1418 SELECT SUM(DECODE(acs.activity_id, G_MEDIA_EMAIL,1,0)),
1419 SUM(DECODE(acs.activity_id, G_MEDIA_TELEMARKETING ,1,0)),
1420 SUM(DECODE(acs.activity_id, G_MEDIA_DIRECTMAIL ,1,0)), drv.party_id
1421 FROM ams_list_entries ale,
1422 ams_act_lists aal,
1423 ams_campaign_schedules_b acs,
1424 --ams_media_b ame,
1425 ams_dm_drv_stg_gt drv
1426 WHERE aal.list_used_by = 'CSCH'
1427 AND aal.list_used_by_id = acs.schedule_id
1428 AND aal.list_act_type = 'TARGET'
1429 AND acs.activity_type_code = 'DIRECT_MARKETING'
1430 --AND acs.activity_id = ame.media_id
1431 --AND UPPER(ame.media_name) = 'TELEMARKETING' commented out by amisingh
1432 AND aal.list_header_id = ale.list_header_id
1433 AND ale.party_id = drv.party_id
1434 GROUP BY drv.party_id;
1435
1436 CURSOR c_num_tgt_offr_typ IS
1437 SELECT SUM(DECODE(UPPER(offer_type),'ACCRUAL',1,0)),
1438 SUM(DECODE(UPPER(offer_type),'LUMPSUM',1,0)),
1439 SUM(DECODE(UPPER(offer_type),'ORDER',1,0)),
1440 SUM(DECODE(UPPER(offer_type),'OFF_INVOICE',1,0)), drv.party_id
1441 FROM ams_list_entries ale,
1442 ams_act_lists aal,
1443 ams_campaign_schedules_b acs,
1444 ams_act_offers aao, ams_dm_drv_stg_gt drv
1445 WHERE aal.list_used_by = 'CSCH'
1446 AND aal.list_used_by_id = acs.schedule_id
1447 AND aal.list_act_type = 'TARGET'
1448 AND acs.end_date_time <= SYSDATE
1449 AND acs.activity_id = aao.activity_offer_id
1450 AND aal.list_header_id = ale.list_header_id
1451 AND ale. party_id = drv.party_id
1452 GROUP BY drv.party_id;
1453
1454 -- nyostos - Sep 15, 2003 - Use Global Temporary Table
1455 --CURSOR cur_party IS SELECT party_id FROM ams_dm_drv_stg;
1456 CURSOR c_all_parties IS SELECT party_id FROM ams_dm_drv_stg_gt;
1457
1458 c_party NUMBER;
1459 l_person_party_id NUMBER;
1460 l_org_party_id NUMBER;
1461 l_is_b2b BOOLEAN;
1462 l_bs_rows NUMBER;
1463
1464 -- choang - 05-aug-2004 - bug 3816612 - changing to use index by binary integer
1465 TYPE l_master_table_type IS
1466 TABLE OF AMS_DM_AGG_STG_REC_TYPE INDEX BY BINARY_INTEGER;
1467 -- TABLE OF AMS_DM_AGG_STG_REC_TYPE INDEX BY VARCHAR2(15);
1468
1469 TYPE t_number_table IS TABLE OF NUMBER(15)
1470 INDEX BY BINARY_INTEGER;
1471
1472 l_all_party_id_list t_number_table;
1473 l_master_party_id_list t_number_table;
1474 l_master_rec AMS_DM_AGG_STG_REC_TYPE;
1475
1476 l_master_table l_master_table_type;
1477 l_party_list t_number_table;
1478
1479 l_age_list dbms_sql.NUMBER_table;
1480 l_days_since_last_school_list dbms_sql.NUMBER_table;
1481 l_days_since_last_event_list dbms_sql.NUMBER_table;
1482 l_num_times_tgt_list dbms_sql.NUMBER_table;
1483 l_last_targeted_ch_code_list dbms_sql.VARCHAR2_table;
1484 l_times_tgt_month_list dbms_sql.NUMBER_table;
1488 l_days_since_last_tgt_list dbms_sql.NUMBER_table;
1485 l_times_tgt_3months_list dbms_sql.NUMBER_table;
1486 l_times_tgt_6months_list dbms_sql.NUMBER_table;
1487 l_times_tgt_12months_list dbms_sql.NUMBER_table;
1489 l_avg_disc_offered_list dbms_sql.NUMBER_table;
1490 l_num_types_disc_offered_list dbms_sql.NUMBER_table;
1491 l_days_since_first_cnt_list dbms_sql.NUMBER_table;
1492 l_days_since_acct_estb_list dbms_sql.NUMBER_table;
1493 l_days_since_acct_term_list dbms_sql.NUMBER_table;
1494 l_days_since_acct_act_list dbms_sql.NUMBER_table;
1495 l_num_times_tgt_email_list dbms_sql.NUMBER_table;
1496 l_num_times_tgt_telemkt_list dbms_sql.NUMBER_table;
1497 l_num_times_tgt_direct_list dbms_sql.NUMBER_table;
1498 l_num_tgt_by_offr_typ1_list dbms_sql.NUMBER_table;
1499 l_num_tgt_by_offr_typ2_list dbms_sql.NUMBER_table;
1500 l_num_tgt_by_offr_typ3_list dbms_sql.NUMBER_table;
1501 l_num_tgt_by_offr_typ4_list dbms_sql.NUMBER_table;
1502
1503 l_count NUMBER := 1;
1504
1505 l_return_status VARCHAR2(1);
1506 BEGIN
1507 --------------------- initialize -----------------------
1508 SAVEPOINT Insert_Agg_Stg;
1509 l_is_b2b:=p_is_b2b;
1510 IF (AMS_DEBUG_HIGH_ON) THEN
1511 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1512 END IF;
1513
1514 l_bs_rows:= fnd_profile.value_specific('AMS_BATCH_SIZE');
1515 IF (l_bs_rows IS NULL OR l_bs_rows < 1) THEN
1516 l_bs_rows :=1000;
1517 END IF;
1518
1519 --get all the parties into the mater table.
1520 OPEN c_all_parties;
1521 LOOP
1522 FETCH c_all_parties BULK COLLECT INTO l_all_party_id_list LIMIT l_bs_rows;
1523 FOR i IN 1..l_all_party_id_list.COUNT LOOP
1524 l_master_party_id_list(l_count) := l_all_party_id_list(i);
1525 l_count := l_count+1;
1526 l_master_rec.PARTY_ID := l_all_party_id_list(i);
1527 l_master_table(l_all_party_id_list(i)) := l_master_rec;
1528 END LOOP;
1529 EXIT WHEN c_all_parties%NOTFOUND;
1530 END LOOP;
1531 CLOSE c_all_parties;
1532
1533 IF p_is_b2b THEN
1534 OPEN c_age_b2b;
1535 LOOP
1536 FETCH c_age_b2b BULK COLLECT INTO l_age_list,l_party_list LIMIT l_bs_rows;
1537 FOR i IN 1..l_party_list.COUNT LOOP
1538 l_master_table(l_party_list(i)).AGE := l_age_list(i);
1539 END LOOP;
1540 EXIT WHEN c_age_b2b%NOTFOUND;
1541 END LOOP;
1542 CLOSE c_age_b2b;
1543 ELSE
1544 OPEN c_age;
1545 LOOP
1546 FETCH c_age BULK COLLECT INTO l_age_list,l_party_list LIMIT l_bs_rows;
1547 FOR i IN 1..l_party_list.COUNT LOOP
1548 l_master_table(l_party_list(i)).AGE := l_age_list(i);
1549 END LOOP;
1550 EXIT WHEN c_age%NOTFOUND;
1551 END LOOP;
1552 CLOSE c_age;
1553 END IF ;
1554
1555 l_party_list.delete;
1556
1557 IF p_is_b2b THEN
1558 OPEN c_days_since_last_school_b2b;
1559 LOOP
1560 FETCH c_days_since_last_school_b2b BULK COLLECT INTO l_days_since_last_school_list,l_party_list LIMIT l_bs_rows;
1561 FOR i IN 1..l_party_list.COUNT LOOP
1562 l_master_table(l_party_list(i)).DAYS_SINCE_LAST_SCHOOL := l_days_since_last_school_list(i);
1563 END LOOP;
1564 EXIT WHEN c_days_since_last_school_b2b%NOTFOUND;
1565 END LOOP;
1566 CLOSE c_days_since_last_school_b2b;
1567 ELSE
1568 OPEN c_days_since_last_school;
1569 LOOP
1570 FETCH c_days_since_last_school BULK COLLECT INTO l_days_since_last_school_list,l_party_list LIMIT l_bs_rows;
1571 FOR i IN 1..l_party_list.COUNT LOOP
1572 l_master_table(l_party_list(i)).DAYS_SINCE_LAST_SCHOOL := l_days_since_last_school_list(i);
1573 END LOOP;
1574 EXIT WHEN c_days_since_last_school%NOTFOUND;
1575 END LOOP;
1576 CLOSE c_days_since_last_school;
1577 END IF;
1578
1579 l_party_list.delete;
1580
1581 OPEN c_days_since_last_event;
1582 LOOP
1583 FETCH c_days_since_last_event BULK COLLECT INTO l_days_since_last_event_list,l_party_list LIMIT l_bs_rows;
1584 FOR i IN 1..l_party_list.COUNT LOOP
1585 l_master_table(l_party_list(i)).DAYS_SINCE_LAST_EVENT := l_days_since_last_event_list(i);
1586 END LOOP;
1587 EXIT WHEN c_days_since_last_event%NOTFOUND;
1588 END LOOP;
1589 CLOSE c_days_since_last_event;
1590
1591 l_party_list.delete;
1592
1593 OPEN c_num_times_targeted;
1594 LOOP
1595 FETCH c_num_times_targeted BULK COLLECT INTO l_num_times_tgt_list,l_party_list LIMIT l_bs_rows;
1596 FOR i IN 1..l_party_list.COUNT LOOP
1597 l_master_table(l_party_list(i)).NUM_TIMES_TARGETED := l_num_times_tgt_list(i);
1598 END LOOP;
1599 EXIT WHEN c_num_times_targeted%NOTFOUND;
1600 END LOOP;
1601 CLOSE c_num_times_targeted;
1602
1603 l_party_list.delete;
1604
1605 /* --commented due to perf issue
1606 OPEN c_last_targeted_channel_code;
1607 LOOP
1608 FETCH c_last_targeted_channel_code BULK COLLECT INTO l_last_targeted_ch_code_list,l_party_list LIMIT l_bs_rows;
1609 FOR i IN 1..l_party_list.COUNT LOOP
1610 l_master_table(l_party_list(i)).LAST_TARGETED_CHANNEL_CODE := l_last_targeted_ch_code_list(i);
1614 CLOSE c_last_targeted_channel_code;
1611 END LOOP;
1612 EXIT WHEN c_last_targeted_channel_code%NOTFOUND;
1613 END LOOP;
1615
1616 l_party_list.delete;
1617 */
1618
1619 OPEN c_times_targeted;
1620
1621 LOOP
1622 FETCH c_times_targeted
1623 BULK COLLECT
1624 INTO l_times_tgt_month_list,
1625 l_times_tgt_3months_list,
1626 l_times_tgt_6months_list,
1627 l_times_tgt_12months_list,
1628 l_party_list
1629 LIMIT l_bs_rows;
1630 FOR i IN 1..l_party_list.COUNT LOOP
1631 l_master_table(l_party_list(i)).TIMES_TARGETED_MONTH := l_times_tgt_month_list(i);
1632 l_master_table(l_party_list(i)).TIMES_TARGETED_3_MONTHS := l_times_tgt_3months_list(i);
1633 l_master_table(l_party_list(i)).TIMES_TARGETED_6_MONTHS := l_times_tgt_6months_list(i);
1634 l_master_table(l_party_list(i)).TIMES_TARGETED_12_MONTHS := l_times_tgt_12months_list(i);
1635 END LOOP;
1636 EXIT WHEN c_times_targeted%NOTFOUND;
1637 END LOOP;
1638
1639 l_party_list.delete;
1640
1641 OPEN c_days_since_last_targeted;
1642 LOOP
1643 FETCH c_days_since_last_targeted BULK COLLECT INTO l_days_since_last_tgt_list,l_party_list LIMIT l_bs_rows;
1644 FOR i IN 1..l_party_list.COUNT LOOP
1645 l_master_table(l_party_list(i)).DAYS_SINCE_LAST_TARGETED := l_days_since_last_tgt_list(i);
1646 END LOOP;
1647 EXIT WHEN c_days_since_last_targeted%NOTFOUND;
1648 END LOOP;
1649 CLOSE c_days_since_last_targeted;
1650
1651 l_party_list.delete;
1652
1653 OPEN c_avg_disc_offered;
1654 LOOP
1655 FETCH c_avg_disc_offered BULK COLLECT INTO l_avg_disc_offered_list,l_party_list LIMIT l_bs_rows;
1656 FOR i IN 1..l_party_list.COUNT LOOP
1657 l_master_table(l_party_list(i)).AVG_DISC_OFFERED := l_avg_disc_offered_list(i);
1658 END LOOP;
1659 EXIT WHEN c_avg_disc_offered%NOTFOUND;
1660 END LOOP;
1661 CLOSE c_avg_disc_offered;
1662
1663 l_party_list.delete;
1664
1665 OPEN c_num_types_disc_offered;
1666 LOOP
1667 FETCH c_num_types_disc_offered BULK COLLECT INTO l_num_types_disc_offered_list,l_party_list LIMIT l_bs_rows;
1668 FOR i IN 1..l_party_list.COUNT LOOP
1669 l_master_table(l_party_list(i)).NUM_TYPES_DISC_OFFERED := l_num_types_disc_offered_list(i);
1670 END LOOP;
1671 EXIT WHEN c_num_types_disc_offered%NOTFOUND;
1672 END LOOP;
1673 CLOSE c_num_types_disc_offered;
1674
1675 l_party_list.delete;
1676
1677 OPEN c_days_since_first_contact;
1678 LOOP
1679 FETCH c_days_since_first_contact BULK COLLECT INTO l_days_since_first_cnt_list,l_party_list LIMIT l_bs_rows;
1680 FOR i IN 1..l_party_list.COUNT LOOP
1681 l_master_table(l_party_list(i)).DAYS_SINCE_FIRST_CONTACT := l_days_since_first_cnt_list(i);
1682 END LOOP;
1683 EXIT WHEN c_days_since_first_contact%NOTFOUND;
1684 END LOOP;
1685 CLOSE c_days_since_first_contact;
1686
1687 l_party_list.delete;
1688
1689 IF p_is_b2b THEN
1690 OPEN c_days_since_account_b2b;
1691 LOOP
1692 FETCH c_days_since_account_b2b BULK COLLECT
1693 INTO l_days_since_acct_estb_list,
1694 l_days_since_acct_term_list,
1695 l_days_since_acct_act_list,
1696 l_party_list
1697 LIMIT l_bs_rows;
1698 FOR i IN 1..l_party_list.COUNT LOOP
1699 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_ESTABLISHED := l_days_since_acct_estb_list(i);
1700 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_TERM := l_days_since_acct_term_list(i);
1701 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_ACTIVATION := l_days_since_acct_act_list(i);
1702 END LOOP;
1703 EXIT WHEN c_days_since_account_b2b%NOTFOUND;
1704 END LOOP;
1705 CLOSE c_days_since_account_b2b;
1706 ELSE
1707 OPEN c_days_since_account;
1708 LOOP
1709 FETCH c_days_since_account BULK COLLECT
1710 INTO l_days_since_acct_estb_list,
1711 l_days_since_acct_term_list,
1712 l_days_since_acct_act_list,
1713 l_party_list
1714 LIMIT l_bs_rows;
1715 FOR i IN 1..l_party_list.COUNT LOOP
1716 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_ESTABLISHED := l_days_since_acct_estb_list(i);
1717 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_TERM := l_days_since_acct_term_list(i);
1718 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_ACTIVATION := l_days_since_acct_act_list(i);
1719 END LOOP;
1720 EXIT WHEN c_days_since_account%NOTFOUND;
1721 END LOOP;
1722 CLOSE c_days_since_account;
1723
1724 END IF;
1725
1726 l_party_list.delete;
1727
1728 OPEN c_num_times_tgt_chnl;
1729 LOOP
1730 FETCH c_num_times_tgt_chnl BULK COLLECT
1731 INTO l_num_times_tgt_email_list,
1732 l_num_times_tgt_telemkt_list,
1733 l_num_times_tgt_direct_list,
1734 l_party_list
1735 LIMIT l_bs_rows;
1736 FOR i IN 1..l_party_list.COUNT LOOP
1740 END LOOP;
1737 l_master_table(l_party_list(i)).NUM_TIMES_TARGETED_EMAIL := l_num_times_tgt_email_list(i);
1738 l_master_table(l_party_list(i)).NUM_TIMES_TARGETED_TELEMKT := l_num_times_tgt_telemkt_list(i);
1739 l_master_table(l_party_list(i)).NUM_TIMES_TARGETED_DIRECT := l_num_times_tgt_direct_list(i);
1741 EXIT WHEN c_num_times_tgt_chnl%NOTFOUND;
1742 END LOOP;
1743 CLOSE c_num_times_tgt_chnl;
1744
1745 l_party_list.delete;
1746
1747 OPEN c_num_tgt_offr_typ;
1748 LOOP
1749 FETCH c_num_tgt_offr_typ BULK COLLECT
1750 INTO l_num_tgt_by_offr_typ1_list,
1751 l_num_tgt_by_offr_typ2_list,
1752 l_num_tgt_by_offr_typ3_list,
1753 l_num_tgt_by_offr_typ4_list,
1754 l_party_list
1755 LIMIT l_bs_rows;
1756 FOR i IN 1..l_party_list.COUNT LOOP
1757 l_master_table(l_party_list(i)).NUM_TGT_BY_OFFR_TYP1 := l_num_tgt_by_offr_typ1_list(i);
1758 l_master_table(l_party_list(i)).NUM_TGT_BY_OFFR_TYP2 := l_num_tgt_by_offr_typ1_list(i);
1759 l_master_table(l_party_list(i)).NUM_TGT_BY_OFFR_TYP3 := l_num_tgt_by_offr_typ1_list(i);
1760 l_master_table(l_party_list(i)).NUM_TGT_BY_OFFR_TYP4 := l_num_tgt_by_offr_typ1_list(i);
1761 END LOOP;
1762 EXIT WHEN c_num_tgt_offr_typ%NOTFOUND;
1763 END LOOP;
1764 CLOSE c_num_tgt_offr_typ;
1765
1766 l_party_list.delete;
1767
1768 FOR k IN 1..l_master_party_id_list.COUNT LOOP
1769 INSERT INTO ams_dm_agg_stg_gt(
1770 party_id,
1771 age,
1772 days_since_last_school,
1773 days_since_last_event,
1774 num_times_targeted,
1775 last_targeted_channel_code,
1776 times_targeted_month,
1777 times_targeted_3_months,
1778 times_targeted_6_months,
1779 times_targeted_12_months,
1780 days_since_last_targeted,
1781 avg_disc_offered,
1782 num_types_disc_offered,
1783 days_since_first_contact,
1784 days_since_acct_established,
1785 days_since_acct_term,
1786 days_since_acct_activation,
1787 days_since_acct_suspended,
1788 num_times_targeted_email,
1789 num_times_targeted_telemkt,
1790 num_times_targeted_direct,
1791 num_tgt_by_offr_typ1,
1792 num_tgt_by_offr_typ2,
1793 num_tgt_by_offr_typ3,
1794 num_tgt_by_offr_typ4)
1795 VALUES
1796 (
1797 l_master_table(l_master_party_id_list(k)).PARTY_ID,
1798 l_master_table(l_master_party_id_list(k)).AGE,
1799 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_LAST_SCHOOL,
1800 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_LAST_EVENT,
1801 l_master_table(l_master_party_id_list(k)).NUM_TIMES_TARGETED,
1802 l_master_table(l_master_party_id_list(k)).LAST_TARGETED_CHANNEL_CODE,
1803 l_master_table(l_master_party_id_list(k)).TIMES_TARGETED_MONTH,
1804 l_master_table(l_master_party_id_list(k)).TIMES_TARGETED_3_MONTHS,
1805 l_master_table(l_master_party_id_list(k)).TIMES_TARGETED_6_MONTHS,
1806 l_master_table(l_master_party_id_list(k)).TIMES_TARGETED_12_MONTHS,
1807 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_LAST_TARGETED,
1808 l_master_table(l_master_party_id_list(k)).AVG_DISC_OFFERED,
1812 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_ACCT_TERM,
1809 l_master_table(l_master_party_id_list(k)).NUM_TYPES_DISC_OFFERED,
1810 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_FIRST_CONTACT,
1811 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_ACCT_ESTABLISHED,
1813 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_ACCT_ACTIVATION,
1814 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_ACCT_SUSPENDED,
1815 l_master_table(l_master_party_id_list(k)).NUM_TIMES_TARGETED_EMAIL,
1816 l_master_table(l_master_party_id_list(k)).NUM_TIMES_TARGETED_TELEMKT,
1817 l_master_table(l_master_party_id_list(k)).NUM_TIMES_TARGETED_DIRECT,
1818 l_master_table(l_master_party_id_list(k)).NUM_TGT_BY_OFFR_TYP1,
1819 l_master_table(l_master_party_id_list(k)).NUM_TGT_BY_OFFR_TYP2,
1820 l_master_table(l_master_party_id_list(k)).NUM_TGT_BY_OFFR_TYP3,
1821 l_master_table(l_master_party_id_list(k)).NUM_TGT_BY_OFFR_TYP4);
1822 END LOOP;
1823 l_master_table.delete;
1824 l_master_party_id_list.delete;
1825
1826 -------------------- finish --------------------------
1827 COMMIT;
1828
1829 IF (AMS_DEBUG_HIGH_ON) THEN
1830 AMS_Utility_PVT.debug_message (l_full_name || ': End');
1831 END IF;
1832
1833 EXCEPTION
1834 WHEN OTHERS THEN
1835 ROLLBACK TO Insert_Agg_Stg;
1836 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1837 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1838 END IF;
1839 END InsertAggStg;
1840
1841 --kbasavar Added a new procedure for customer profitability
1842 PROCEDURE InsertAggStgOrg
1843 IS
1844 l_api_name CONSTANT VARCHAR2(30) := 'InsertAggStgOrg';
1845 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1846
1847 TYPE AMS_DM_AGG_STG_ORG_REC_TYPE IS RECORD
1848 (
1849 PARTY_ID NUMBER(15),
1850 DAYS_SINCE_LAST_EVENT NUMBER,
1851 NUM_TIMES_TARGETED NUMBER,
1852 LAST_TARGETED_CHANNEL_CODE VARCHAR2(30),
1853 TIMES_TARGETED_MONTH NUMBER,
1854 TIMES_TARGETED_3_MONTHS NUMBER,
1855 TIMES_TARGETED_6_MONTHS NUMBER,
1856 TIMES_TARGETED_12_MONTHS NUMBER,
1857 DAYS_SINCE_LAST_TARGETED NUMBER,
1858 AVG_DISC_OFFERED NUMBER,
1859 NUM_TYPES_DISC_OFFERED NUMBER,
1860 DAYS_SINCE_FIRST_CONTACT NUMBER,
1861 DAYS_SINCE_ACCT_ESTABLISHED NUMBER,
1862 DAYS_SINCE_ACCT_TERM NUMBER,
1863 DAYS_SINCE_ACCT_ACTIVATION NUMBER,
1864 DAYS_SINCE_ACCT_SUSPENDED NUMBER,
1865 NUM_TIMES_TARGETED_EMAIL NUMBER,
1866 NUM_TIMES_TARGETED_TELEMKT NUMBER,
1867 NUM_TIMES_TARGETED_DIRECT NUMBER,
1868 NUM_TGT_BY_OFFR_TYP1 NUMBER,
1869 NUM_TGT_BY_OFFR_TYP2 NUMBER,
1870 NUM_TGT_BY_OFFR_TYP3 NUMBER,
1871 NUM_TGT_BY_OFFR_TYP4 NUMBER
1872 );
1873
1874 CURSOR c_days_since_last_event IS
1875 SELECT (SYSDATE - MAX(aeo.event_start_date)), drv.party_id
1876 FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
1877 WHERE aeo.event_offer_id IN (SELECT aer.event_offer_id
1878 FROM ams_event_registrations aer
1879 WHERE aer.attendant_party_id in
1880 (select party_id
1881 from hz_relationships hpr
1882 where object_id=drv.party_id
1883 AND hpr.status = 'A'
1884 AND hpr.subject_table_name = 'HZ_PARTIES'
1885 AND hpr.object_table_name = 'HZ_PARTIES'
1886 AND hpr.directional_flag = 'F'
1887 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1888 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1889 )
1890 )
1891 group by drv.party_id;
1892
1893 CURSOR c_num_times_targeted IS
1894 SELECT COUNT(DISTINCT ale.list_header_id),drv.party_id
1895 FROM ams_list_entries ale, ams_dm_drv_stg_gt drv,ams_list_headers_all hdr
1896 WHERE ale.party_id in
1897 (select party_id
1898 from hz_relationships hpr
1899 where object_id=drv.party_id
1900 AND hpr.status = 'A'
1901 AND hpr.subject_table_name = 'HZ_PARTIES'
1902 AND hpr.object_table_name = 'HZ_PARTIES'
1903 AND hpr.directional_flag = 'F'
1904 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1905 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1906 )
1907 AND hdr.list_header_id = ale.list_header_id
1908 AND hdr.list_type = 'TARGET'
1909 GROUP BY drv.party_id;
1910
1911 CURSOR c_last_targeted_channel_code IS
1912 SELECT aal.list_used_by_id, drv.party_id
1913 FROM ams_act_lists aal, ams_list_headers_all alh, ams_dm_drv_stg_gt drv
1914 WHERE aal.list_used_by = 'CSCH'
1915 AND aal.list_act_type = 'TARGET'
1916 AND alh.list_header_id = aal.list_header_id
1917 AND alh.sent_out_date = (SELECT MAX(l.sent_out_date)
1918 FROM ams_list_headers_all l
1919 WHERE l.list_header_id IN
1920 (SELECT ale.list_header_id
1924 from hz_relationships hpr
1921 FROM ams_list_entries ale
1922 WHERE ale.party_id in
1923 (select party_id
1925 where object_id=drv.party_id
1926 AND hpr.status = 'A'
1927 AND hpr.subject_table_name = 'HZ_PARTIES'
1928 AND hpr.object_table_name = 'HZ_PARTIES'
1929 AND hpr.directional_flag = 'F'
1930 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1931 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1932 )
1933 ))
1934 group by drv.party_id,aal.list_used_by_id;
1935
1936 CURSOR c_times_targeted IS
1937 SELECT SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -1),1,0,1)),
1938 SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -3),1,0,1)),
1939 SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -9),1,0,1)),
1940 SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -12),1,0,1)), drv.party_id
1941 FROM ams_list_headers_all alh,ams_dm_drv_stg_gt drv
1942 WHERE alh.sent_out_date IS NOT NULL
1943 AND MONTHS_BETWEEN(sysdate, alh.sent_out_date) <= 12
1944 AND alh.list_header_id IN (SELECT ale.list_header_id
1945 FROM ams_list_entries ale
1946 WHERE ale.party_id in
1947 (select party_id
1948 from hz_relationships hpr
1949 where object_id=drv.party_id
1950 AND hpr.status = 'A'
1951 AND hpr.subject_table_name = 'HZ_PARTIES'
1952 AND hpr.object_table_name = 'HZ_PARTIES'
1953 AND hpr.directional_flag = 'F'
1954 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1955 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1956 ))
1957 group by drv.party_id;
1958
1959 CURSOR c_days_since_last_targeted IS
1960 SELECT (SYSDATE - MAX(aeo.event_start_date)), drv.party_id
1961 FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
1962 WHERE aeo.event_offer_id IN (SELECT aer.event_offer_id
1963 FROM ams_event_registrations aer
1964 WHERE aer.attendant_party_id in
1965 (select party_id
1966 from hz_relationships hpr
1967 where object_id=drv.party_id
1968 AND hpr.status = 'A'
1969 AND hpr.subject_table_name = 'HZ_PARTIES'
1970 AND hpr.object_table_name = 'HZ_PARTIES'
1971 AND hpr.directional_flag = 'F'
1972 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1973 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1974 )
1975 )
1976 group by drv.party_id;
1977
1978 CURSOR c_avg_disc_offered IS
1979 SELECT AVG(aao.offer_amount),drv.party_id
1980 FROM ams_act_offers aao, ams_campaign_schedules acs, ams_dm_drv_stg_gt drv
1981 WHERE aao.arc_act_offer_used_by = 'CSCH'
1982 AND aao.activity_offer_id = acs.activity_offer_id
1983 AND acs.campaign_schedule_id IN (SELECT aal.list_used_by_id
1984 FROM ams_act_lists aal
1985 WHERE aal.list_used_by = 'CSCH'
1986 AND aal.list_act_type = 'TARGET'
1987 AND aal.list_header_id IN (SELECT ale.list_header_id
1988 FROM ams_list_entries ale
1989 WHERE ale.party_id in
1990 (select party_id
1991 from hz_relationships hpr
1992 where object_id=drv.party_id
1993 AND hpr.status = 'A'
1994 AND hpr.subject_table_name = 'HZ_PARTIES'
1995 AND hpr.object_table_name = 'HZ_PARTIES'
1996 AND hpr.directional_flag = 'F'
1997 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
1998 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
1999 )
2000 ))
2001 group by drv.party_id;
2002
2003
2004 CURSOR c_num_types_disc_offered IS
2005 SELECT COUNT(aao.offer_type),drv.party_id
2006 FROM ams_act_offers aao, ams_campaign_schedules acs,ams_dm_drv_stg_gt drv
2007 WHERE aao.arc_act_offer_used_by = 'CSCH'
2008 AND aao.activity_offer_id = acs.activity_offer_id
2009 AND acs.campaign_schedule_id IN (SELECT aal.list_used_by_id
2010 FROM ams_list_entries ale, ams_act_lists aal
2011 WHERE aal.list_header_id = ale.list_header_id
2012 AND aal.list_used_by = 'CSCH'
2013 AND aal.list_act_type = 'TARGET'
2014 AND ale.party_id in
2015 (select party_id
2016 from hz_relationships hpr
2017 where object_id=drv.party_id
2018 AND hpr.status = 'A'
2019 AND hpr.subject_table_name = 'HZ_PARTIES'
2020 AND hpr.object_table_name = 'HZ_PARTIES'
2021 AND hpr.directional_flag = 'F'
2022 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
2023 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
2024 )
2025 )
2026 group by drv.party_id;
2027
2028 CURSOR c_days_since_first_contact IS
2029 SELECT (SYSDATE - MIN(aeo.event_start_date)),drv.party_id
2033 WHERE aer.attendant_party_id in
2030 FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
2031 WHERE aeo.event_offer_id IN (SELECT 1
2032 FROM ams_event_registrations aer
2034 (select party_id
2035 from hz_relationships hpr
2036 where object_id=drv.party_id
2037 AND hpr.status = 'A'
2038 AND hpr.subject_table_name = 'HZ_PARTIES'
2039 AND hpr.object_table_name = 'HZ_PARTIES'
2040 AND hpr.directional_flag = 'F'
2041 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
2042 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
2043 )
2044 )
2045 group by drv.party_id;
2046
2047 CURSOR c_days_since_account IS
2048 SELECT SYSDATE - MAX(hca.account_established_date),
2049 0,--SYSDATE - MAX(hca.account_termination_date),
2050 0 --SYSDATE - MAX(hca.account_activation_date)
2051 ,drv.party_id
2052 FROM hz_cust_accounts hca, ams_dm_drv_stg_gt drv
2053 WHERE hca.party_id = drv.party_id
2054 AND hca.status = 'A'
2055 group by drv.party_id;
2056
2057 CURSOR c_num_times_tgt_chnl IS
2058 SELECT SUM(DECODE(acs.activity_id, G_MEDIA_EMAIL,1,0)),
2059 SUM(DECODE(acs.activity_id, G_MEDIA_TELEMARKETING ,1,0)),
2060 SUM(DECODE(acs.activity_id, G_MEDIA_DIRECTMAIL ,1,0)), drv.party_id
2061 FROM ams_list_entries ale,
2062 ams_act_lists aal,
2063 ams_campaign_schedules_b acs,
2064 --ams_media_b ame,
2065 ams_dm_drv_stg_gt drv
2066 WHERE aal.list_used_by = 'CSCH'
2067 AND aal.list_used_by_id = acs.schedule_id
2068 AND aal.list_act_type = 'TARGET'
2069 AND acs.activity_type_code = 'DIRECT_MARKETING'
2070 --AND acs.activity_id = ame.media_id
2071 AND aal.list_header_id = ale.list_header_id
2072 AND ale.party_id in
2073 (select party_id
2074 from hz_relationships hpr
2075 where object_id=drv.party_id
2076 AND hpr.status = 'A'
2077 AND hpr.subject_table_name = 'HZ_PARTIES'
2078 AND hpr.object_table_name = 'HZ_PARTIES'
2079 AND hpr.directional_flag = 'F'
2080 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
2081 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
2082 )
2083 group by drv.party_id;
2084
2085
2086 CURSOR c_num_tgt_offr_typ IS
2087 SELECT SUM(DECODE(UPPER(offer_type),'ACCRUAL',1,0)),
2088 SUM(DECODE(UPPER(offer_type),'LUMPSUM',1,0)),
2089 SUM(DECODE(UPPER(offer_type),'ORDER',1,0)),
2090 SUM(DECODE(UPPER(offer_type),'OFF_INVOICE',1,0)), drv.party_id
2091 FROM ams_list_entries ale,
2092 ams_act_lists aal,
2093 ams_campaign_schedules_b acs,
2094 ams_act_offers aao, ams_dm_drv_stg_gt drv
2095 WHERE aal.list_used_by = 'CSCH'
2096 AND aal.list_used_by_id = acs.schedule_id
2097 AND aal.list_act_type = 'TARGET'
2098 AND acs.end_date_time <= SYSDATE
2099 AND acs.activity_id = aao.activity_offer_id
2100 AND aal.list_header_id = ale.list_header_id
2101 AND ale. party_id in
2102 (select party_id
2103 from hz_relationships hpr
2104 where object_id=drv.party_id
2105 AND hpr.status = 'A'
2106 AND hpr.subject_table_name = 'HZ_PARTIES'
2107 AND hpr.object_table_name = 'HZ_PARTIES'
2108 AND hpr.directional_flag = 'F'
2109 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
2110 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
2111 )
2112 group by drv.party_id;
2113
2114 -- nyostos - Sep 15, 2003 - Use Global Temporary Table
2115 -- CURSOR cur_party IS SELECT party_id FROM AMS_DM_DRV_stg_gt;
2116 CURSOR c_all_parties IS SELECT party_id FROM AMS_DM_DRV_stg_gt;
2117
2118 -- choang - 05-aug-2004 - bug 3816612 - changing to use index by binary integer
2119 TYPE l_master_table_type IS
2120 TABLE OF AMS_DM_AGG_STG_ORG_REC_TYPE INDEX BY BINARY_INTEGER;
2121 -- TABLE OF AMS_DM_AGG_STG_ORG_REC_TYPE INDEX BY VARCHAR2(15);
2122
2123 TYPE t_number_table IS TABLE OF NUMBER(15)
2124 INDEX BY BINARY_INTEGER;
2125
2126 l_bs_rows NUMBER;
2127 l_all_party_id_list dbms_sql.NUMBER_table;
2128
2129 l_master_party_id_list t_number_table;
2130 l_master_rec AMS_DM_AGG_STG_ORG_REC_TYPE;
2131 l_master_table l_master_table_type;
2132
2133 l_party_list t_number_table;
2134 l_days_since_last_event_list dbms_sql.NUMBER_table;
2135 l_num_times_tgt_list dbms_sql.NUMBER_table;
2136 l_last_targeted_ch_code_list dbms_sql.VARCHAR2_table;
2137 l_times_tgt_month_list dbms_sql.NUMBER_table;
2138 l_times_tgt_3months_list dbms_sql.NUMBER_table;
2139 l_times_tgt_6months_list dbms_sql.NUMBER_table;
2140 l_times_tgt_12months_list dbms_sql.NUMBER_table;
2141 l_days_since_last_tgt_list dbms_sql.NUMBER_table;
2142 l_avg_disc_offered_list dbms_sql.NUMBER_table;
2143 l_num_types_disc_offered_list dbms_sql.NUMBER_table;
2144 l_days_since_first_cnt_list dbms_sql.NUMBER_table;
2145 l_days_since_acct_estb_list dbms_sql.NUMBER_table;
2146 l_days_since_acct_term_list dbms_sql.NUMBER_table;
2147 l_days_since_acct_act_list dbms_sql.NUMBER_table;
2148 l_num_times_tgt_email_list dbms_sql.NUMBER_table;
2149 l_num_times_tgt_telemkt_list dbms_sql.NUMBER_table;
2153 l_num_tgt_by_offr_typ3_list dbms_sql.NUMBER_table;
2150 l_num_times_tgt_direct_list dbms_sql.NUMBER_table;
2151 l_num_tgt_by_offr_typ1_list dbms_sql.NUMBER_table;
2152 l_num_tgt_by_offr_typ2_list dbms_sql.NUMBER_table;
2154 l_num_tgt_by_offr_typ4_list dbms_sql.NUMBER_table;
2155
2156 l_count NUMBER := 1;
2157 BEGIN
2158 --------------------- initialize -----------------------
2159 SAVEPOINT Insert_Agg_stg_Org;
2160
2161 IF (AMS_DEBUG_HIGH_ON) THEN
2162 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
2163 END IF;
2164
2165 l_bs_rows:= fnd_profile.value_specific('AMS_BATCH_SIZE');
2166 IF (l_bs_rows IS NULL OR l_bs_rows < 1) THEN
2167 l_bs_rows :=1000;
2168 END IF;
2169
2170 --get all the parties into the mater table.
2171 OPEN c_all_parties;
2172 LOOP
2173 FETCH c_all_parties BULK COLLECT INTO l_all_party_id_list LIMIT l_bs_rows;
2174 FOR i IN 1..l_all_party_id_list.COUNT LOOP
2175 l_master_party_id_list(l_count) := l_all_party_id_list(i);
2176 l_count := l_count+1;
2177 l_master_rec.PARTY_ID := l_all_party_id_list(i);
2178 l_master_table(l_all_party_id_list(i)) := l_master_rec;
2179 END LOOP;
2180 EXIT WHEN c_all_parties%NOTFOUND;
2181 END LOOP;
2182 CLOSE c_all_parties;
2183
2184 OPEN c_days_since_last_event;
2185 LOOP
2186 FETCH c_days_since_last_event BULK COLLECT INTO l_days_since_last_event_list,l_party_list LIMIT l_bs_rows;
2187 FOR i IN 1..l_party_list.COUNT LOOP
2188 l_master_table(l_party_list(i)).DAYS_SINCE_LAST_EVENT := l_days_since_last_event_list(i);
2189 END LOOP;
2190 EXIT WHEN c_days_since_last_event%NOTFOUND;
2191 END LOOP;
2192 CLOSE c_days_since_last_event;
2193
2194 l_party_list.delete;
2195
2196 OPEN c_num_times_targeted;
2197 LOOP
2198 FETCH c_num_times_targeted BULK COLLECT INTO l_num_times_tgt_list,l_party_list LIMIT l_bs_rows;
2199 FOR i IN 1..l_party_list.COUNT LOOP
2200 l_master_table(l_party_list(i)).NUM_TIMES_TARGETED := l_num_times_tgt_list(i);
2201 END LOOP;
2202 EXIT WHEN c_num_times_targeted%NOTFOUND;
2203 END LOOP;
2204 CLOSE c_num_times_targeted;
2205
2206 l_party_list.delete;
2207
2208 /*--commented due to perf issue
2209 OPEN c_last_targeted_channel_code;
2210 LOOP
2211 FETCH c_last_targeted_channel_code BULK COLLECT INTO l_last_targeted_ch_code_list,l_party_list LIMIT l_bs_rows;
2212 FOR i IN 1..l_party_list.COUNT LOOP
2213 l_master_table(l_party_list(i)).LAST_TARGETED_CHANNEL_CODE := l_last_targeted_ch_code_list(i);
2214 END LOOP;
2215 EXIT WHEN c_last_targeted_channel_code%NOTFOUND;
2216 END LOOP;
2217 CLOSE c_last_targeted_channel_code;
2218
2219 l_party_list.delete;
2220 */
2221
2222 OPEN c_times_targeted;
2223
2224 LOOP
2225 FETCH c_times_targeted
2226 BULK COLLECT
2227 INTO l_times_tgt_month_list,
2228 l_times_tgt_3months_list,
2229 l_times_tgt_6months_list,
2230 l_times_tgt_12months_list,
2231 l_party_list
2232 LIMIT l_bs_rows;
2233 FOR i IN 1..l_party_list.COUNT LOOP
2234 l_master_table(l_party_list(i)).TIMES_TARGETED_MONTH := l_times_tgt_month_list(i);
2235 l_master_table(l_party_list(i)).TIMES_TARGETED_3_MONTHS := l_times_tgt_3months_list(i);
2236 l_master_table(l_party_list(i)).TIMES_TARGETED_6_MONTHS := l_times_tgt_6months_list(i);
2237 l_master_table(l_party_list(i)).TIMES_TARGETED_12_MONTHS := l_times_tgt_12months_list(i);
2238 END LOOP;
2239 EXIT WHEN c_times_targeted%NOTFOUND;
2240 END LOOP;
2241
2242 l_party_list.delete;
2243
2244 OPEN c_days_since_last_targeted;
2245 LOOP
2246 FETCH c_days_since_last_targeted BULK COLLECT INTO l_days_since_last_tgt_list,l_party_list LIMIT l_bs_rows;
2247 FOR i IN 1..l_party_list.COUNT LOOP
2248 l_master_table(l_party_list(i)).DAYS_SINCE_LAST_TARGETED := l_days_since_last_tgt_list(i);
2249 END LOOP;
2250 EXIT WHEN c_days_since_last_targeted%NOTFOUND;
2251 END LOOP;
2252 CLOSE c_days_since_last_targeted;
2253
2254 l_party_list.delete;
2255
2256 OPEN c_avg_disc_offered;
2257 LOOP
2258 FETCH c_avg_disc_offered BULK COLLECT INTO l_avg_disc_offered_list,l_party_list LIMIT l_bs_rows;
2259 FOR i IN 1..l_party_list.COUNT LOOP
2260 l_master_table(l_party_list(i)).AVG_DISC_OFFERED := l_avg_disc_offered_list(i);
2261 END LOOP;
2262 EXIT WHEN c_avg_disc_offered%NOTFOUND;
2263 END LOOP;
2264 CLOSE c_avg_disc_offered;
2265
2266 l_party_list.delete;
2267
2268 OPEN c_num_types_disc_offered;
2269 LOOP
2270 FETCH c_num_types_disc_offered BULK COLLECT INTO l_num_types_disc_offered_list,l_party_list LIMIT l_bs_rows;
2271 FOR i IN 1..l_party_list.COUNT LOOP
2272 l_master_table(l_party_list(i)).NUM_TYPES_DISC_OFFERED := l_num_types_disc_offered_list(i);
2273 END LOOP;
2274 EXIT WHEN c_num_types_disc_offered%NOTFOUND;
2275 END LOOP;
2276 CLOSE c_num_types_disc_offered;
2277
2281 LOOP
2278 l_party_list.delete;
2279
2280 OPEN c_days_since_first_contact;
2282 FETCH c_days_since_first_contact BULK COLLECT INTO l_days_since_first_cnt_list,l_party_list LIMIT l_bs_rows;
2283 FOR i IN 1..l_party_list.COUNT LOOP
2284 l_master_table(l_party_list(i)).DAYS_SINCE_FIRST_CONTACT := l_days_since_first_cnt_list(i);
2285 END LOOP;
2286 EXIT WHEN c_days_since_first_contact%NOTFOUND;
2287 END LOOP;
2288 CLOSE c_days_since_first_contact;
2289
2290 l_party_list.delete;
2291
2292 OPEN c_days_since_account;
2293 LOOP
2294 FETCH c_days_since_account BULK COLLECT
2295 INTO l_days_since_acct_estb_list,
2296 l_days_since_acct_term_list,
2297 l_days_since_acct_act_list,
2298 l_party_list
2299 LIMIT l_bs_rows;
2300 FOR i IN 1..l_party_list.COUNT LOOP
2301 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_ESTABLISHED := l_days_since_acct_estb_list(i);
2302 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_TERM := l_days_since_acct_term_list(i);
2303 l_master_table(l_party_list(i)).DAYS_SINCE_ACCT_ACTIVATION := l_days_since_acct_act_list(i);
2304 END LOOP;
2305 EXIT WHEN c_days_since_account%NOTFOUND;
2306 END LOOP;
2307 CLOSE c_days_since_account;
2308
2309 l_party_list.delete;
2310
2311 OPEN c_num_times_tgt_chnl;
2312 LOOP
2313 FETCH c_num_times_tgt_chnl BULK COLLECT
2314 INTO l_num_times_tgt_email_list,
2315 l_num_times_tgt_telemkt_list,
2316 l_num_times_tgt_direct_list,
2317 l_party_list
2318 LIMIT l_bs_rows;
2319 FOR i IN 1..l_party_list.COUNT LOOP
2320 l_master_table(l_party_list(i)).NUM_TIMES_TARGETED_EMAIL := l_num_times_tgt_email_list(i);
2321 l_master_table(l_party_list(i)).NUM_TIMES_TARGETED_TELEMKT := l_num_times_tgt_telemkt_list(i);
2322 l_master_table(l_party_list(i)).NUM_TIMES_TARGETED_DIRECT := l_num_times_tgt_direct_list(i);
2323 END LOOP;
2324 EXIT WHEN c_num_times_tgt_chnl%NOTFOUND;
2325 END LOOP;
2326 CLOSE c_num_times_tgt_chnl;
2327
2328 l_party_list.delete;
2329
2330 OPEN c_num_tgt_offr_typ;
2331 LOOP
2332 FETCH c_num_tgt_offr_typ BULK COLLECT
2333 INTO l_num_tgt_by_offr_typ1_list,
2334 l_num_tgt_by_offr_typ2_list,
2335 l_num_tgt_by_offr_typ3_list,
2336 l_num_tgt_by_offr_typ4_list,
2337 l_party_list
2338 LIMIT l_bs_rows;
2339 FOR i IN 1..l_party_list.COUNT LOOP
2340 l_master_table(l_party_list(i)).NUM_TGT_BY_OFFR_TYP1 := l_num_tgt_by_offr_typ1_list(i);
2341 l_master_table(l_party_list(i)).NUM_TGT_BY_OFFR_TYP2 := l_num_tgt_by_offr_typ1_list(i);
2342 l_master_table(l_party_list(i)).NUM_TGT_BY_OFFR_TYP3 := l_num_tgt_by_offr_typ1_list(i);
2343 l_master_table(l_party_list(i)).NUM_TGT_BY_OFFR_TYP4 := l_num_tgt_by_offr_typ1_list(i);
2344 END LOOP;
2345 EXIT WHEN c_num_tgt_offr_typ%NOTFOUND;
2346 END LOOP;
2347 CLOSE c_num_tgt_offr_typ;
2348
2349 l_party_list.delete;
2350
2351 FOR k IN 1..l_master_party_id_list.COUNT LOOP
2352 INSERT INTO ams_dm_agg_stg_gt(
2353 party_id,
2354 days_since_last_event,
2355 num_times_targeted,
2356 last_targeted_channel_code,
2357 times_targeted_month,
2358 times_targeted_3_months,
2359 times_targeted_6_months,
2360 times_targeted_12_months,
2361 days_since_last_targeted,
2362 avg_disc_offered,
2363 num_types_disc_offered,
2364 days_since_first_contact,
2365 days_since_acct_established,
2366 days_since_acct_term,
2367 days_since_acct_activation,
2368 days_since_acct_suspended,
2369 num_times_targeted_email,
2370 num_times_targeted_telemkt,
2371 num_times_targeted_direct,
2372 num_tgt_by_offr_typ1,
2373 num_tgt_by_offr_typ2,
2374 num_tgt_by_offr_typ3,
2375 num_tgt_by_offr_typ4)
2376 VALUES
2377 (
2378 l_master_table(l_master_party_id_list(k)).PARTY_ID,
2379 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_LAST_EVENT,
2380 l_master_table(l_master_party_id_list(k)).NUM_TIMES_TARGETED,
2381 l_master_table(l_master_party_id_list(k)).LAST_TARGETED_CHANNEL_CODE,
2382 l_master_table(l_master_party_id_list(k)).TIMES_TARGETED_MONTH,
2383 l_master_table(l_master_party_id_list(k)).TIMES_TARGETED_3_MONTHS,
2384 l_master_table(l_master_party_id_list(k)).TIMES_TARGETED_6_MONTHS,
2385 l_master_table(l_master_party_id_list(k)).TIMES_TARGETED_12_MONTHS,
2386 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_LAST_TARGETED,
2387 l_master_table(l_master_party_id_list(k)).AVG_DISC_OFFERED,
2388 l_master_table(l_master_party_id_list(k)).NUM_TYPES_DISC_OFFERED,
2389 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_FIRST_CONTACT,
2390 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_ACCT_ESTABLISHED,
2391 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_ACCT_TERM,
2392 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_ACCT_ACTIVATION,
2396 l_master_table(l_master_party_id_list(k)).NUM_TIMES_TARGETED_DIRECT,
2393 l_master_table(l_master_party_id_list(k)).DAYS_SINCE_ACCT_SUSPENDED,
2394 l_master_table(l_master_party_id_list(k)).NUM_TIMES_TARGETED_EMAIL,
2395 l_master_table(l_master_party_id_list(k)).NUM_TIMES_TARGETED_TELEMKT,
2397 l_master_table(l_master_party_id_list(k)).NUM_TGT_BY_OFFR_TYP1,
2398 l_master_table(l_master_party_id_list(k)).NUM_TGT_BY_OFFR_TYP2,
2399 l_master_table(l_master_party_id_list(k)).NUM_TGT_BY_OFFR_TYP3,
2400 l_master_table(l_master_party_id_list(k)).NUM_TGT_BY_OFFR_TYP4);
2401 END LOOP;
2402 l_master_table.delete;
2403 l_master_party_id_list.delete;
2404
2405 -------------------- finish --------------------------
2406 COMMIT;
2407
2408 IF (AMS_DEBUG_HIGH_ON) THEN
2409 AMS_Utility_PVT.debug_message (l_full_name || ': End');
2410 END IF;
2411
2412 EXCEPTION
2413 WHEN OTHERS THEN
2414 ROLLBACK TO Insert_Agg_stg_Org;
2415
2416 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2417 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
2418 END IF;
2419
2420 END InsertAggStgOrg;
2421
2422
2423 -- End of complex transformations
2424
2425 PROCEDURE InsertBICStg(
2426 p_is_b2b IN BOOLEAN,
2427 p_model_type IN VARCHAR2,
2428 p_is_org_prod IN BOOLEAN
2429 )
2430 IS
2431 l_api_name CONSTANT VARCHAR2(30) := 'InsertBICStg';
2432 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2433
2434 l_date DATE := TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, 1),'DD-MM-YYYY'), 'DD-MM-YYYY'), 'MONTH');
2435 l_is_b2b BOOLEAN;
2436
2437 BEGIN
2438 --------------------- initialize -----------------------
2439 SAVEPOINT Insert_BIC_Stg;
2440 l_is_b2b:=p_is_b2b;
2441
2442 IF (AMS_DEBUG_HIGH_ON) THEN
2443 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
2444 END IF;
2445
2446 ----------------------- Insert ----------------------
2447 IF l_is_b2b THEN
2448 IF p_model_type='CUSTOMER_PROFITABILITY' OR p_is_org_prod THEN
2449 -- nyostos - Sep 15, 2003 - Use Global Temporary Table
2450 -- INSERT -- /*+ APPEND PARALLEL(AMS_DM_BIC_STG,DEFAULT,DEFAULT)*/
2451 -- INTO ams_dm_BIC_stg (
2452 INSERT -- /*+ APPEND PARALLEL(AMS_DM_BIC_STG_GT,DEFAULT,DEFAULT)*/
2453 INTO ams_dm_BIC_stg_GT (
2454 party_id,
2455 avg_talk_time,
2456 avg_order_amount,
2457 avg_units_per_order,
2458 tot_order_amount_year,
2459 tot_order_amount_9_months,
2460 tot_order_amount_6_months,
2461 tot_order_amount_3_months,
2462 tot_num_orders_year,
2463 tot_num_order_9_months,
2464 tot_num_order_6_months,
2465 tot_num_order_3_months,
2466 num_of_sr_year,
2467 num_of_sr_6_months,
2468 num_of_sr_3_months,
2469 num_of_sr_1_month,
2470 avg_resolve_days_year,
2471 avg_resolve_days_6_months,
2472 avg_resolve_days_3_months,
2473 avg_resolve_days_1_month,
2474 order_lines_delivered,
2475 order_lines_ontime,
2476 order_qty_cumul,
2477 order_recency,
2478 payments,
2479 returns,
2480 return_by_value,
2481 return_by_value_pct,
2482 ontime_payments,
2483 ontime_ship_pct,
2484 closed_srs,
2485 COGS,
2486 contracts_cuml,
2487 contract_amt,
2488 contract_duration,
2489 inactive_contracts,
2490 open_contracts,
2491 new_contracts,
2492 renewed_contracts,
2493 escalated_srs,
2494 first_call_cl_rate,
2495 num_of_complaints,
2496 num_of_interactions,
2497 num_of_transfers,
2498 open_srs,
2499 pct_call_rework,
2500 products,
2501 referals,
2502 reopened_srs,
2503 sales,
2504 total_sr_response_time,
2505 pct_first_closed_srs,
2506 avg_complaints,
2507 avg_hold_time,
2508 avg_len_of_emp,
2509 avg_transfers_per_sr,
2510 avg_workload,
2511 tot_calls,
2512 call_length,
2513 profitability)
2514 SELECT
2515 drv.party_id party_id,
2516 AVG(bps.avg_talk_time) avg_talk_time, -- Can we do avg of avg.. this may be wrong.
2517 SUM(bps.order_amt)/SUM(bps.order_num) avg_order_amount,
2518 SUM(bps.order_qty)/SUM(bps.order_num) avg_units_per_order,
2519 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,order_amt)) tot_order_amount_year,
2520 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 9),1,0,order_amt)) tot_order_amount_9_months,
2521 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,order_amt)) tot_order_amount_6_months,
2522 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,order_amt)) tot_order_amount_3_months,
2526 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,order_num)) tot_num_order_3_months,
2523 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,order_num)) tot_num_orders_year,
2524 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 9),1,0,order_num)) tot_num_order_9_months,
2525 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,order_num)) tot_num_order_6_months,
2527 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,srs_logged)) num_of_sr_year,
2528 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,srs_logged)) num_of_sr_6_months,
2529 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,srs_logged)) num_of_sr_3_months,
2530 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 1),1,0,srs_logged)) num_of_sr_1_month,
2531 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,avg_sr_resl_time)) avg_resolve_days_year,
2532 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,avg_sr_resl_time)) avg_resolve_days_6_months,
2533 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,avg_sr_resl_time)) avg_resolve_days_3_months,
2534 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 1),1,0,avg_sr_resl_time)) avg_resolve_days_1_month,
2535 SUM(bps.order_lines_delivered) order_lines_delivered,
2536 SUM(bps.order_lines_ontime) order_lines_ontime,
2537 SUM(bps.order_qty_cuml) order_qty_cuml,
2538 SUM(bps.order_recency) order_recency,
2539 SUM(bps.payments) payments,
2540 SUM(bps.returns) returns,
2541 SUM(bps.return_by_value) return_by_value,
2542 SUM(bps.return_by_value_pct) return_by_value_pct,
2543 SUM(bps.ontime_payments) ontime_payments,
2544 SUM(bps.ontime_ship_pct) ontime_ship_pct,
2545 SUM(bps.closed_srs) closed_srs,
2546 SUM(bps.COGS) COGS,
2547 SUM(bps.contracts_cuml) contracts_cuml,
2548 SUM(bps.contract_amt) contract_amt,
2549 SUM(bps.contract_duration) contract_duration,
2550 SUM(bps.inactive_contracts) inactive_contracts,
2551 SUM(bps.open_contracts) open_contracts,
2552 SUM(bps.new_contracts) new_contracts,
2553 SUM(bps.renewed_contracts) renewed_contracts,
2554 SUM(bps.esc_srs) escalated_srs,
2555 AVG(bps.first_call_cl_rate) first_call_cl_rate,
2556 SUM(bps.no_of_complaints) num_of_complaints,
2557 SUM(bps.no_of_interactions) num_of_interactions,
2558 SUM(bps.no_of_transfers) num_of_transfers,
2559 SUM(bps.open_srs) open_srs,
2560 AVG(bps.perct_call_rework) pct_call_rework,
2561 SUM(bps.products) products,
2562 SUM(bps.referals) referals,
2563 SUM(bps.reopened_srs) reopened_srs,
2564 SUM(bps.sales) sales,
2565 SUM(bps.total_sr_response_time) total_sr_response_time,
2566 AVG(bps.avg_closed_srs) pct_first_closed_srs, --note
2567 AVG(bps.avg_complaints) avg_complaints,
2568 AVG(bps.avg_hold_time) avg_hold_time,
2569 AVG(bps.avg_len_of_emp) avg_len_of_emp,
2570 AVG(bps.avg_transfers_per_sr) avg_transfers_per_sr,
2571 AVG(bps.avg_workload) avg_workload,
2572 SUM(bps.calls) tot_calls, --note
2573 AVG(bps.call_length) call_length,
2574 AVG(bps.profitability) profitability
2575 FROM bic_party_summ bps,
2576 -- ams_dm_drv_stg drv
2577 ams_dm_drv_stg_gt drv -- nyostos - Sep 15, 2003 - Global Temp Table
2578 WHERE drv.party_id = bps.party_id(+)
2579 AND bps.period_start_date(+) > l_date - 365
2580 GROUP BY drv.party_id
2581 ;
2582 ELSE
2583 -- nyostos - Sep 15, 2003 - Use Global Temporary Table
2584 -- INSERT -- /*+ APPEND PARALLEL(AMS_DM_BIC_STG,DEFAULT,DEFAULT)*/
2585 -- INTO ams_dm_BIC_stg (
2586 INSERT -- /*+ APPEND PARALLEL(AMS_DM_BIC_STG_GT,DEFAULT,DEFAULT)*/
2587 INTO ams_dm_BIC_stg_GT (
2588 party_id,
2589 avg_talk_time,
2590 avg_order_amount,
2591 avg_units_per_order,
2592 tot_order_amount_year,
2593 tot_order_amount_9_months,
2594 tot_order_amount_6_months,
2595 tot_order_amount_3_months,
2596 tot_num_orders_year,
2597 tot_num_order_9_months,
2598 tot_num_order_6_months,
2599 tot_num_order_3_months,
2600 num_of_sr_year,
2601 num_of_sr_6_months,
2602 num_of_sr_3_months,
2603 num_of_sr_1_month,
2604 avg_resolve_days_year,
2605 avg_resolve_days_6_months,
2606 avg_resolve_days_3_months,
2607 avg_resolve_days_1_month,
2608 order_lines_delivered,
2609 order_lines_ontime,
2610 order_qty_cumul,
2611 order_recency,
2612 payments,
2613 returns,
2614 return_by_value,
2615 return_by_value_pct,
2616 ontime_payments,
2617 ontime_ship_pct,
2618 closed_srs,
2619 COGS,
2620 contracts_cuml,
2621 contract_amt,
2622 contract_duration,
2626 renewed_contracts,
2623 inactive_contracts,
2624 open_contracts,
2625 new_contracts,
2627 escalated_srs,
2628 first_call_cl_rate,
2629 num_of_complaints,
2630 num_of_interactions,
2631 num_of_transfers,
2632 open_srs,
2633 pct_call_rework,
2634 products,
2635 referals,
2636 reopened_srs,
2637 sales,
2638 total_sr_response_time,
2639 pct_first_closed_srs,
2640 avg_complaints,
2641 avg_hold_time,
2642 avg_len_of_emp,
2643 avg_transfers_per_sr,
2644 avg_workload,
2645 tot_calls,
2646 call_length,
2647 profitability)
2648 SELECT
2649 drv.party_id party_id,
2650 AVG(bps.avg_talk_time) avg_talk_time, -- Can we do avg of avg.. this may be wrong.
2651 SUM(bps.order_amt)/SUM(bps.order_num) avg_order_amount,
2652 SUM(bps.order_qty)/SUM(bps.order_num) avg_units_per_order,
2653 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,order_amt)) tot_order_amount_year,
2654 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 9),1,0,order_amt)) tot_order_amount_9_months,
2655 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,order_amt)) tot_order_amount_6_months,
2656 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,order_amt)) tot_order_amount_3_months,
2657 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,order_num)) tot_num_orders_year,
2658 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 9),1,0,order_num)) tot_num_order_9_months,
2659 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,order_num)) tot_num_order_6_months,
2660 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,order_num)) tot_num_order_3_months,
2661 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,srs_logged)) num_of_sr_year,
2662 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,srs_logged)) num_of_sr_6_months,
2663 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,srs_logged)) num_of_sr_3_months,
2664 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 1),1,0,srs_logged)) num_of_sr_1_month,
2665 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,avg_sr_resl_time)) avg_resolve_days_year,
2666 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,avg_sr_resl_time)) avg_resolve_days_6_months,
2667 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,avg_sr_resl_time)) avg_resolve_days_3_months,
2668 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 1),1,0,avg_sr_resl_time)) avg_resolve_days_1_month,
2669 SUM(bps.order_lines_delivered) order_lines_delivered,
2670 SUM(bps.order_lines_ontime) order_lines_ontime,
2671 SUM(bps.order_qty_cuml) order_qty_cuml,
2672 SUM(bps.order_recency) order_recency,
2673 SUM(bps.payments) payments,
2674 SUM(bps.returns) returns,
2675 SUM(bps.return_by_value) return_by_value,
2676 SUM(bps.return_by_value_pct) return_by_value_pct,
2677 SUM(bps.ontime_payments) ontime_payments,
2678 SUM(bps.ontime_ship_pct) ontime_ship_pct,
2679 SUM(bps.closed_srs) closed_srs,
2680 SUM(bps.COGS) COGS,
2681 SUM(bps.contracts_cuml) contracts_cuml,
2682 SUM(bps.contract_amt) contract_amt,
2683 SUM(bps.contract_duration) contract_duration,
2684 SUM(bps.inactive_contracts) inactive_contracts,
2685 SUM(bps.open_contracts) open_contracts,
2686 SUM(bps.new_contracts) new_contracts,
2687 SUM(bps.renewed_contracts) renewed_contracts,
2688 SUM(bps.esc_srs) escalated_srs,
2689 AVG(bps.first_call_cl_rate) first_call_cl_rate,
2690 SUM(bps.no_of_complaints) num_of_complaints,
2691 SUM(bps.no_of_interactions) num_of_interactions,
2692 SUM(bps.no_of_transfers) num_of_transfers,
2693 SUM(bps.open_srs) open_srs,
2694 AVG(bps.perct_call_rework) pct_call_rework,
2695 SUM(bps.products) products,
2696 SUM(bps.referals) referals,
2697 SUM(bps.reopened_srs) reopened_srs,
2698 SUM(bps.sales) sales,
2699 SUM(bps.total_sr_response_time) total_sr_response_time,
2700 AVG(bps.avg_closed_srs) pct_first_closed_srs, --note
2701 AVG(bps.avg_complaints) avg_complaints,
2702 AVG(bps.avg_hold_time) avg_hold_time,
2703 AVG(bps.avg_len_of_emp) avg_len_of_emp,
2704 AVG(bps.avg_transfers_per_sr) avg_transfers_per_sr,
2705 AVG(bps.avg_workload) avg_workload,
2706 SUM(bps.calls) tot_calls, --note
2707 AVG(bps.call_length) call_length,
2708 AVG(bps.profitability) profitability
2709 FROM bic_party_summ bps,
2710 -- ams_dm_drv_stg drv,
2711 ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Table
2712 hz_relationships hpr
2713 WHERE drv.party_id = hpr.party_id
2714 AND hpr.status = 'A'
2715 AND hpr.subject_table_name = 'HZ_PARTIES'
2716 AND hpr.object_table_name = 'HZ_PARTIES'
2717 AND hpr.directional_flag = 'F'
2721 AND bps.period_start_date(+) > l_date - 365
2718 AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
2719 AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
2720 AND bps.party_id(+) = hpr.object_id --the org's party id
2722 GROUP BY drv.party_id
2723 ;
2724 END IF;
2725 ELSE
2726 -- nyostos - Sep 15, 2003 - Use Global Temporary Table
2727 -- INSERT -- /*+ APPEND PARALLEL(AMS_DM_BIC_STG,DEFAULT,DEFAULT)*/
2728 -- INTO ams_dm_BIC_stg (
2729 INSERT -- /*+ APPEND PARALLEL(AMS_DM_BIC_STG_GT,DEFAULT,DEFAULT)*/
2730 INTO ams_dm_BIC_stg_GT (
2731 party_id,
2732 avg_talk_time,
2733 avg_order_amount,
2734 avg_units_per_order,
2735 tot_order_amount_year,
2736 tot_order_amount_9_months,
2737 tot_order_amount_6_months,
2738 tot_order_amount_3_months,
2739 tot_num_orders_year,
2740 tot_num_order_9_months,
2741 tot_num_order_6_months,
2742 tot_num_order_3_months,
2743 num_of_sr_year,
2744 num_of_sr_6_months,
2745 num_of_sr_3_months,
2746 num_of_sr_1_month,
2747 avg_resolve_days_year,
2748 avg_resolve_days_6_months,
2749 avg_resolve_days_3_months,
2750 avg_resolve_days_1_month,
2751 order_lines_delivered,
2752 order_lines_ontime,
2753 order_qty_cumul,
2754 order_recency,
2755 payments,
2756 returns,
2757 return_by_value,
2758 return_by_value_pct,
2759 ontime_payments,
2760 ontime_ship_pct,
2761 closed_srs,
2762 COGS,
2763 contracts_cuml,
2764 contract_amt,
2765 contract_duration,
2766 inactive_contracts,
2767 open_contracts,
2768 new_contracts,
2769 renewed_contracts,
2770 escalated_srs,
2771 first_call_cl_rate,
2772 num_of_complaints,
2773 num_of_interactions,
2774 num_of_transfers,
2775 open_srs,
2776 pct_call_rework,
2777 products,
2778 referals,
2779 reopened_srs,
2780 sales,
2781 total_sr_response_time,
2782 pct_first_closed_srs,
2783 avg_complaints,
2784 avg_hold_time,
2785 avg_len_of_emp,
2786 avg_transfers_per_sr,
2787 avg_workload,
2788 tot_calls,
2789 call_length,
2790 profitability)
2791 SELECT
2792 drv.party_id party_id,
2793 AVG(bps.avg_talk_time) avg_talk_time, -- Can we do avg of avg.. this may be wrong.
2794 SUM(bps.order_amt)/SUM(bps.order_num) avg_order_amount,
2795 SUM(bps.order_qty)/SUM(bps.order_num) avg_units_per_order,
2796 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,order_amt)) tot_order_amount_year,
2797 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 9),1,0,order_amt)) tot_order_amount_9_months,
2798 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,order_amt)) tot_order_amount_6_months,
2799 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,order_amt)) tot_order_amount_3_months,
2800 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,order_num)) tot_num_orders_year,
2801 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 9),1,0,order_num)) tot_num_order_9_months,
2802 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,order_num)) tot_num_order_6_months,
2803 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,order_num)) tot_num_order_3_months,
2804 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,srs_logged)) num_of_sr_year,
2805 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,srs_logged)) num_of_sr_6_months,
2806 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,srs_logged)) num_of_sr_3_months,
2807 SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 1),1,0,srs_logged)) num_of_sr_1_month,
2808 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) -12),1,0,avg_sr_resl_time)) avg_resolve_days_year,
2809 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 6),1,0,avg_sr_resl_time)) avg_resolve_days_6_months,
2810 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 3),1,0,avg_sr_resl_time)) avg_resolve_days_3_months,
2811 AVG(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, period_start_date)) - 1),1,0,avg_sr_resl_time)) avg_resolve_days_1_month,
2812 SUM(bps.order_lines_delivered) order_lines_delivered,
2813 SUM(bps.order_lines_ontime) order_lines_ontime,
2814 SUM(bps.order_qty_cuml) order_qty_cuml,
2815 SUM(bps.order_recency) order_recency,
2816 SUM(bps.payments) payments,
2817 SUM(bps.returns) returns,
2818 SUM(bps.return_by_value) return_by_value,
2819 SUM(bps.return_by_value_pct) return_by_value_pct,
2820 SUM(bps.ontime_payments) ontime_payments,
2821 SUM(bps.ontime_ship_pct) ontime_ship_pct,
2822 SUM(bps.closed_srs) closed_srs,
2823 SUM(bps.COGS) COGS,
2824 SUM(bps.contracts_cuml) contracts_cuml,
2825 SUM(bps.contract_amt) contract_amt,
2826 SUM(bps.contract_duration) contract_duration,
2827 SUM(bps.inactive_contracts) inactive_contracts,
2828 SUM(bps.open_contracts) open_contracts,
2829 SUM(bps.new_contracts) new_contracts,
2830 SUM(bps.renewed_contracts) renewed_contracts,
2831 SUM(bps.esc_srs) escalated_srs,
2832 AVG(bps.first_call_cl_rate) first_call_cl_rate,
2833 SUM(bps.no_of_complaints) num_of_complaints,
2837 AVG(bps.perct_call_rework) pct_call_rework,
2834 SUM(bps.no_of_interactions) num_of_interactions,
2835 SUM(bps.no_of_transfers) num_of_transfers,
2836 SUM(bps.open_srs) open_srs,
2838 SUM(bps.products) products,
2839 SUM(bps.referals) referals,
2840 SUM(bps.reopened_srs) reopened_srs,
2841 SUM(bps.sales) sales,
2842 SUM(bps.total_sr_response_time) total_sr_response_time,
2843 AVG(bps.avg_closed_srs) pct_first_closed_srs, --note
2844 AVG(bps.avg_complaints) avg_complaints,
2845 AVG(bps.avg_hold_time) avg_hold_time,
2846 AVG(bps.avg_len_of_emp) avg_len_of_emp,
2847 AVG(bps.avg_transfers_per_sr) avg_transfers_per_sr,
2848 AVG(bps.avg_workload) avg_workload,
2849 SUM(bps.calls) tot_calls, --note
2850 AVG(bps.call_length) call_length,
2851 AVG(bps.profitability) profitability
2852 FROM bic_party_summ bps,
2853 -- ams_dm_drv_stg drv
2854 ams_dm_drv_stg_gt drv -- nyostos - Sep 15, 2003 - Global Temp Table
2855 WHERE bps.period_start_date(+) > l_date - 365
2856 AND bps.party_id(+) = drv.party_id
2857 GROUP BY drv.party_id
2858 ;
2859
2860 END IF;
2861 -------------------- finish --------------------------
2862 COMMIT;
2863
2864 IF (AMS_DEBUG_HIGH_ON) THEN
2865 AMS_Utility_PVT.debug_message (l_full_name || ': End');
2866 END IF;
2867
2868 EXCEPTION
2869 WHEN OTHERS THEN
2870 ROLLBACK TO Insert_BIC_Stg;
2871
2872 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2873 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
2874 END IF;
2875 END InsertBICStg;
2876 -- End of staging Cust Intelligence data
2877
2878 -- End of all Staging PRocedures
2879
2880 PROCEDURE InsertPartyDetails(x_return_status OUT NOCOPY VARCHAR2)
2881 IS
2882 l_api_name CONSTANT VARCHAR2(30) := 'InsertPartyDetails';
2883 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2884 BEGIN
2885 --------------------- initialize -----------------------
2886 SAVEPOINT Insert_Party_Details;
2887
2888 x_return_status := FND_API.g_ret_sts_success;
2889
2890 IF (AMS_DEBUG_HIGH_ON) THEN
2891 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
2892 END IF;
2893
2894 ----------------------- Insert ----------------------
2895 -- Move Data from Staging to Party Details (Insert Process)
2896 INSERT -- /*+ APPEND PARALLEL(AMS_DM_PARTY_DETAILS,DEFAULT,DEFAULT)*/
2897 INTO ams_dm_party_details (
2898 party_id,
2899 created_by,
2900 creation_date,
2901 last_updated_by,
2902 last_update_date,
2903 last_update_login,
2904 object_version_number,
2905 party_type,
2906 gender,
2907 ethnicity,
2908 marital_status,
2909 personal_income,
2910 hoh_flag,
2911 household_income,
2912 household_size,
2913 apartment_flag,
2914 rent_flag,
2915 degree_received,
2916 school_type,
2917 interest_art_flag,
2918 interest_books_flag,
2919 interest_movies_flag,
2920 interest_music_flag,
2921 interest_theater_flag,
2922 interest_travel_flag,
2923 interest_drink_flag,
2924 interest_smoke_flag,
2925 interest_other_flag,
2926 employed_flag,
2927 years_employed,
2928 occupation,
2929 military_branch,
2930 residence_type,
2931 resident_length,
2932 presence_of_children, -- num_of_children?
2933 country,
2934 state,
2935 province,
2936 county,
2937 zip_code,
2938 Reference_use_flag,
2939 gross_annual_income,
2940 debt_to_income_ratio,
2941 num_credit_lines,
2942 num_trade_lines,
2943 net_worth,
2944 total_assets,
2945 tot_debt_outstanding,
2946 bankruptcy_flag,
2947 high_risk_fraud_flag,
2948 gross_annual_sales,
2949 growth_rate_sales_year,
2950 growth_rate_net_prof_year,
2951 current_assets,
2952 current_liabilities,
2953 total_debts,
2954 net_profit,
2955 tangible_net_profit,
2956 capital_amount,
2957 capital_type_indicator,
2958 accounts_receivable,
2959 retained_earnings,
2960 paydex_score_year,
2961 paydex_score_3_month_ago,
2962 industry_paydex_median,
2963 global_failure_score,
2964 dnb_score,
2965 out_of_business_flag,
2966 customer_quality_rank,
2967 fortune_500_rank,
2968 num_of_employees,
2969 legal_status,
2970 year_established,
2971 sic_code1,
2972 minority_business_flag,
2973 small_business_flag,
2974 women_owned_bus_flag,
2975 gov_org_flag,
2976 hq_subsidiary_flag,
2977 foreign_owned_flag,
2978 import_export_bus_flag,
2979 credit_check_flag,
2980 tolerance,
2981 discount_terms_flag,
2982 dunning_letters_flag,
2983 interest_charges_flag,
2984 send_statements_flag,
2985 -- send_credit_balance_flag,
2986 credit_hold_flag,
2987 -- profile_class_code,
2988 credit_rating,
2989 risk_code,
2990 -- standard_terms,
2994 business_scope,
2991 -- override_terms,
2992 interest_period_days,
2993 payment_grace_days,
2995 email_address,
2996 address1,
2997 address2,
2998 competitor_flag,
2999 third_party_flag,
3000 person_first_name,
3001 person_middle_name,
3002 person_last_name,
3003 person_name_suffix,
3004 person_title,
3005 person_academic_title,
3006 person_pre_name_adjunct,
3007 control_yr,
3008 line_of_business,
3009 cong_dist_code,
3010 labor_surplus_flag,
3011 debarment_flag,
3012 disadv_8a_flag,
3013 debarments_count,
3014 months_since_last_debarment,
3015 gsa_indicator_flag,
3016 analysis_fy,
3017 fiscal_yearend_month,
3018 curr_fy_potential_revenue,
3019 next_fy_potential_revenue,
3020 organization_type,
3021 corporation_class,
3022 registration_type,
3023 incorp_year,
3024 public_private_ownership_flag,
3025 internal_flag,
3026 high_credit,
3027 avg_high_credit,
3028 total_payments,
3029 credit_score_class,
3030 credit_score_natl_percentile,
3031 credit_score_incd_default,
3032 credit_score_age,
3033 failure_score_class,
3034 failure_score_incd_default,
3035 failure_score_age,
3036 maximum_credit_recommendation,
3037 maximum_credit_currency_code,
3038 party_name,
3039 city
3040 )
3041 SELECT
3042 drv.party_id party_id,
3043 FND_GLOBAL.USER_ID created_by, ---------------> FND_GLOBAL
3044 SYSDATE creation_date,
3045 fnd_global.user_id last_updated_by,
3046 SYSDATE last_update_date,
3047 fnd_global.conc_login_id last_update_login,
3048 1 object_version_number,
3049 gen.party_type party_type, ---------------> 1-1
3050 gen.gender gender,
3051 gen.ethnicity ethnicity,
3052 gen.marital_status marital_status,
3053 gen.personal_income personal_income,
3054 gen.hoh_flag hoh_flag,
3055 gen.household_income household_income,
3056 gen.household_size household_size,
3057 gen.apartment_flag apartment_flag,
3058 gen.rent_flag rent_flag,
3059 gen.degree_received degree_received,
3060 gen.school_type school_type, ------------> 1-1
3061 int.interest_art_flag interest_art_flag,
3062 int.interest_books_flag interest_books_flag,
3063 int.interest_movies_flag interest_movies_flag,
3064 int.interest_music_flag interest_music_flag,
3065 int.interest_theater_flag interest_theater_flag,
3066 int.interest_travel_flag interest_travel_flag,
3067 int.interest_drink_flag interest_drink_flag,
3068 int.interest_smoke_flag interest_smoke_flag,
3069 int.interest_other_flag interest_other_flag, -- person_interest
3070 gen.employed_flag employed_flag, --------------------------1-
3071 gen.years_employed years_employed,
3072 gen.occupation occupation,
3073 gen.military_branch military_branch,
3074 gen.residence_type residence_type,
3075 gen.resident_length resident_length,
3076 gen.presence_of_children presence_of_children, -- num_of_children?
3077 gen.country country,
3078 gen.state state,
3079 gen.province province,
3080 gen.county county,
3081 gen.zip_code zip_code,
3082 gen.reference_use_flag Reference_use_flag, --------------------------1-1
3083 fin.gross_annual_income gross_annual_income, ----------> Financial Number
3084 fin.debt_to_income_ratio debt_to_income_ratio,
3085 fin.num_credit_lines num_credit_lines,
3086 fin.num_trade_lines num_trade_lines,
3087 fin.net_worth net_worth,
3088 fin.total_assets total_assets,
3089 fin.tot_debt_outstanding tot_debt_outstanding,
3090 fin.bankruptcy_flag bankruptcy_flag,
3091 fin.high_risk_fraud_flag high_risk_fraud_flag,
3092 fin.gross_annual_sales gross_annual_sales,
3093 fin.growth_rate_sales_year growth_rate_sales_year,
3094 fin.growth_rate_net_prof_year growth_rate_net_prof_year,
3095 fin.current_assets current_assets,
3096 fin.current_liabilities current_liabilities,
3097 fin.total_debts total_debts,
3098 fin.net_profit net_profit,
3099 fin.tangible_net_profit tangible_net_profit,
3100 fin.capital_amount capital_amount,
3101 fin.capital_type_indicator capital_type_indicator,
3102 fin.accounts_receivable accounts_receivable,
3103 fin.retained_earnings retained_earnings, ----------> Financial Number
3104 gen.paydex_score_year paydex_score_year, -----------> 1-1
3105 gen.paydex_score_3_month_ago paydex_score_3_month_ago,
3106 gen.industry_paydex_median industry_paydex_median,
3110 gen.customer_quality_rank customer_quality_rank,
3107 gen.global_failure_score global_failure_score,
3108 gen.dnb_score dnb_score,
3109 gen.out_of_business_flag out_of_business_flag,
3111 gen.fortune_500_rank fortune_500_rank,
3112 gen.num_of_employees num_of_employees,
3113 gen.legal_status legal_status,
3114 gen.year_established year_established,
3115 gen.sic_code1 sic_code1,
3116 gen.minority_business_flag minority_business_flag,
3117 gen.small_business_flag small_business_flag,
3118 gen.women_owned_bus_flag women_owned_bus_flag,
3119 gen.gov_org_flag gov_org_flag,
3120 gen.hq_subsidiary_flag hq_subsidiary_flag,
3121 gen.foreign_owned_flag foreign_owned_flag,
3122 gen.import_export_bus_flag import_export_bus_flag,
3123 ppf.credit_check_flag credit_check_flag,
3124 ppf.tolerance tolerance,
3125 ppf.discount_terms_flag discount_terms_flag,
3126 ppf.dunning_letters_flag dunning_letters_flag,
3127 ppf.interest_charges_flag interest_charges_flag,
3128 ppf.send_statements_flag send_statements_flag,
3129 -- ppf.send_credit_balance_flag send_credit_balance_flag,
3130 ppf.credit_hold_flag credit_hold_flag,
3131 -- ppf.profile_class_code profile_class_code,
3132 ppf.credit_rating credit_rating,
3133 ppf.risk_code risk_code,
3134 -- ppf.standard_terms standard_terms,
3135 -- ppf.override_terms override_terms,
3136 ppf.interest_period_days interest_period_days,
3137 ppf.payment_grace_days payment_grace_days,
3138 gen.business_scope,
3139 gen.email_address,
3140 gen.address1,
3141 gen.address2,
3142 gen.competitor_flag,
3143 gen.third_party_flag,
3144 gen.person_first_name,
3145 gen.person_middle_name,
3146 gen.person_last_name,
3147 gen.person_name_suffix,
3148 gen.person_title,
3149 gen.person_academic_title,
3150 gen.person_pre_name_adjunct,
3151 gen.control_yr,
3152 gen.line_of_business,
3153 gen.cong_dist_code,
3154 gen.labor_surplus_flag,
3155 gen.debarment_flag,
3156 gen.disadv_8a_flag,
3157 gen.debarments_count,
3158 gen.months_since_last_debarment,
3159 gen.gsa_indicator_flag,
3160 gen.analysis_fy,
3161 gen.fiscal_yearend_month,
3162 gen.curr_fy_potential_revenue,
3163 gen.next_fy_potential_revenue,
3164 gen.organization_type,
3165 gen.corporation_class,
3166 gen.registration_type,
3167 gen.incorp_year,
3168 gen.public_private_ownership_flag,
3169 gen.internal_flag,
3170 gen.high_credit,
3171 gen.avg_high_credit,
3172 gen.total_payments,
3173 gen.credit_score_class,
3174 gen.credit_score_natl_percentile,
3175 gen.credit_score_incd_default,
3176 gen.credit_score_age,
3177 gen.failure_score_class,
3178 gen.failure_score_incd_default,
3179 gen.failure_score_age,
3180 gen.maximum_credit_recommendation,
3181 gen.maximum_credit_currency_code,
3182 gen.party_name,
3183 gen.city
3184 FROM
3185 -- nyostos - Sep 15, 2003 - Use Global Temporary Tables
3186 -- ams_dm_drv_stg drv,
3187 -- ams_dm_gen_stg gen,
3188 -- ams_dm_perint_stg int,
3189 -- ams_dm_finnum_stg fin,
3190 -- ams_dm_party_profile_stg ppf
3191 ams_dm_drv_stg_gt drv,
3192 ams_dm_gen_stg_gt gen,
3193 ams_dm_perint_stg_gt int,
3194 ams_dm_finnum_stg_gt fin,
3195 ams_dm_profile_stg_gt ppf
3196 WHERE
3197 drv.party_id = gen.party_id (+)
3198 AND drv.party_id = int.party_id (+)
3199 AND drv.party_id = fin.party_id (+)
3200 AND drv.party_id = ppf.party_id (+)
3201 ;
3202 /* IF (SQL%NOTFOUND) THEN
3203 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
3204 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
3205 FND_MSG_PUB.add;
3206 END IF;
3207 RAISE FND_API.g_exc_error;
3208 END IF;
3209 */
3210 -------------------- finish --------------------------
3211 COMMIT;
3212
3213 IF (AMS_DEBUG_HIGH_ON) THEN
3214 AMS_Utility_PVT.debug_message (l_full_name || ': End');
3215 END IF;
3216
3217 EXCEPTION
3218 WHEN OTHERS THEN
3219 ROLLBACK TO Insert_Party_Details;
3220
3221 x_return_status := FND_API.g_ret_sts_error;
3222
3223 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3224 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3225 END IF;
3226 END InsertPartyDetails;
3227 -- End of moving data from Staging to Party Details
3228
3229 PROCEDURE InsertPartyDetailsTime(x_return_status OUT NOCOPY VARCHAR2)
3230 IS
3231 l_api_name CONSTANT VARCHAR2(30) := 'InsertPartyDetailsTime';
3232 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3233 BEGIN
3234 --------------------- initialize -----------------------
3235 SAVEPOINT Insert_Party_Details_Time;
3236
3237 x_return_status := FND_API.g_ret_sts_success;
3238
3239 IF (AMS_DEBUG_HIGH_ON) THEN
3243 ----------------------- Insert ----------------------
3240 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
3241 END IF;
3242
3244 -- Move Data from Staging to Party Details Time (Insert Process)
3245
3246 INSERT -- /*+ APPEND PARALLEL(AMS_DM_PARTY_DETAILS_TIME,DEFAULT,DEFAULT)*/
3247 INTO ams_dm_party_details_time (
3248 party_id,
3249 created_by,
3250 creation_date,
3251 last_updated_by,
3252 last_update_date,
3253 last_update_login,
3254 object_version_number,
3255 age, ------------------------------ to be mapped into agg staging
3256 days_since_last_school,
3257 days_since_last_event,
3258 num_times_targeted,
3259 last_targeted_channel_code,
3260 times_targeted_month,
3261 times_targeted_3_months,
3262 times_targeted_6_months,
3263 times_targeted_12_months,
3264 days_since_last_targeted,
3265 avg_disc_offered,
3266 num_types_disc_offered,
3267 days_since_first_contact,
3268 days_since_acct_established,
3269 days_since_acct_term,
3270 days_since_acct_activation,
3271 days_since_acct_suspended,
3272 num_times_targeted_email,
3273 num_times_targeted_telemkt,
3274 num_times_targeted_direct,
3275 num_tgt_by_offr_typ1,
3276 num_tgt_by_offr_typ2,
3277 num_tgt_by_offr_typ3,
3278 num_tgt_by_offr_typ4, --------------------- agg
3279 avg_talk_time, ------------------------------ bic
3280 avg_order_amount,
3281 avg_units_per_order,
3282 tot_order_amount_year,
3283 tot_order_amount_9_months,
3284 tot_order_amount_6_months,
3285 tot_order_amount_3_months,
3286 tot_num_orders_year,
3287 tot_num_order_9_months,
3288 tot_num_order_6_months,
3289 tot_num_order_3_months,
3290 num_of_sr_year,
3291 num_of_sr_6_months,
3292 num_of_sr_3_months,
3293 num_of_sr_1_month,
3294 avg_resolve_days_year,
3295 avg_resolve_days_6_months,
3296 avg_resolve_days_3_months,
3297 avg_resolve_days_1_month,
3298 order_lines_delivered,
3299 order_lines_ontime,
3300 order_qty_cumul,
3301 order_recency,
3302 payments,
3303 returns,
3304 return_by_value,
3305 return_by_value_pct,
3306 ontime_payments,
3307 ontime_ship_pct,
3308 closed_srs,
3309 COGS,
3310 contracts_cuml,
3311 contract_amt,
3312 contract_duration,
3313 inactive_contracts,
3314 open_contracts,
3315 new_contracts,
3316 renewed_contracts,
3317 escalated_srs,
3318 first_call_cl_rate,
3319 num_of_complaints,
3320 num_of_interactions,
3321 num_of_transfers,
3322 open_srs,
3323 pct_call_rework,
3324 products,
3325 referals,
3326 reopened_srs,
3327 sales,
3328 total_sr_response_time,
3329 pct_first_closed_srs,
3330 avg_complaints,
3331 avg_hold_time,
3332 avg_len_of_emp,
3333 avg_transfers_per_sr,
3334 avg_workload,
3335 tot_calls,
3336 call_length,
3337 profitability)
3338 SELECT
3339 drv.party_id party_id,
3340 FND_GLOBAL.USER_ID created_by,
3341 SYSDATE creation_date,
3342 fnd_global.user_id last_updated_by,
3343 SYSDATE last_update_date,
3344 fnd_global.conc_login_id last_update_login,
3345 1 object_version_number,
3346 agg.age age, --------------- to be mapped
3347 agg.days_since_last_school days_since_last_school,
3348 agg.days_since_last_event days_since_last_event,
3349 agg.num_times_targeted num_times_targeted,
3350 agg.last_targeted_channel_code last_targeted_channel_code,
3351 agg.times_targeted_month times_targeted_month,
3352 agg.times_targeted_3_months times_targeted_3_months,
3353 agg.times_targeted_6_months times_targeted_6_months,
3354 agg.times_targeted_12_months times_targeted_12_months,
3355 agg.days_since_last_targeted days_since_last_targeted,
3356 agg.avg_disc_offered avg_disc_offered,
3357 agg.num_types_disc_offered num_types_disc_offered,
3358 agg.days_since_first_contact days_since_first_contact,
3359 agg.days_since_acct_established days_since_acct_established,
3360 agg.days_since_acct_term days_since_acct_term,
3361 agg.days_since_acct_activation days_since_acct_activation,
3362 agg.days_since_acct_suspended days_since_acct_suspended,
3363 agg.num_times_targeted_email num_times_targeted_email,
3364 agg.num_times_targeted_telemkt num_times_targeted_telemkt,
3365 agg.num_times_targeted_direct num_times_targeted_direct ,
3366 agg.num_tgt_by_offr_typ1 num_tgt_by_offr_typ1,
3367 agg.num_tgt_by_offr_typ2 num_tgt_by_offr_typ2,
3368 agg.num_tgt_by_offr_typ3 num_tgt_by_offr_typ3,
3369 agg.num_tgt_by_offr_typ4 num_tgt_by_offr_typ4, -------- agg
3370 bic.avg_talk_time avg_talk_time, ---------------- bic
3371 bic.avg_order_amount avg_order_amount,
3372 bic.avg_units_per_order avg_units_per_order,
3376 bic.tot_order_amount_3_months tot_order_amount_3_months,
3373 bic.tot_order_amount_year tot_order_amount_year,
3374 bic.tot_order_amount_9_months tot_order_amount_9_months,
3375 bic.tot_order_amount_6_months tot_order_amount_6_months,
3377 bic.tot_num_orders_year tot_num_orders_year,
3378 bic.tot_num_order_9_months tot_num_order_9_months,
3379 bic.tot_num_order_6_months tot_num_order_6_months,
3380 bic.tot_num_order_3_months tot_num_order_3_months,
3381 bic.num_of_sr_year num_of_sr_year,
3382 bic.num_of_sr_6_months num_of_sr_6_months,
3383 bic.num_of_sr_3_months num_of_sr_3_months,
3384 bic.num_of_sr_1_month num_of_sr_1_month,
3385 bic.avg_resolve_days_year avg_resolve_days_year,
3386 bic.avg_resolve_days_6_months avg_resolve_days_6_months,
3387 bic.avg_resolve_days_3_months avg_resolve_days_3_months,
3388 bic.avg_resolve_days_1_month avg_resolve_days_1_month,
3389 bic.order_lines_delivered order_lines_delivered,
3390 bic.order_lines_ontime order_lines_ontime,
3391 bic.order_qty_cumul order_qty_cumul,
3392 bic.order_recency order_recency,
3393 bic.payments payments,
3394 bic.returns returns,
3395 bic.return_by_value return_by_value,
3396 bic.return_by_value_pct return_by_value_pct,
3397 bic.ontime_payments ontime_payments,
3398 bic.ontime_ship_pct ontime_ship_pct,
3399 bic.closed_srs closed_srs,
3400 bic.COGS COGS,
3401 bic.contracts_cuml contracts_cuml,
3402 bic.contract_amt contract_amt,
3403 bic.contract_duration contract_duration,
3404 bic.inactive_contracts inactive_contracts,
3405 bic.open_contracts open_contracts,
3406 bic.new_contracts new_contracts,
3407 bic.renewed_contracts renewed_contracts,
3408 bic.escalated_srs escalated_srs,
3409 bic.first_call_cl_rate first_call_cl_rate,
3410 bic.num_of_complaints num_of_complaints,
3411 bic.num_of_interactions num_of_interactions,
3412 bic.num_of_transfers num_of_transfers,
3413 bic.open_srs open_srs,
3414 bic.pct_call_rework pct_call_rework,
3415 bic.products products,
3416 bic.referals referals,
3417 bic.reopened_srs reopened_srs,
3418 bic.sales sales,
3419 bic.total_sr_response_time total_sr_response_time,
3420 bic.pct_first_closed_srs pct_first_closed_srs,
3421 bic.avg_complaints avg_complaints,
3422 bic.avg_hold_time avg_hold_time,
3423 bic.avg_len_of_emp avg_len_of_emp,
3424 bic.avg_transfers_per_sr avg_transfers_per_sr,
3425 bic.avg_workload avg_workload,
3426 bic.tot_calls tot_calls,
3427 bic.call_length call_length,
3428 bic.profitability profitability
3429 FROM
3430 -- nyostos - Sep 15, 2003 - Global Temp Tables
3431 -- ams_dm_drv_stg drv,
3432 -- ams_dm_agg_stg agg,
3433 -- ams_dm_bic_stg bic
3434 ams_dm_drv_stg_gt drv,
3435 ams_dm_agg_stg_gt agg,
3436 ams_dm_bic_stg_gt bic
3437 WHERE drv.party_id = agg.party_id (+)
3438 AND drv.party_id = bic.party_id (+)
3439 ;
3440 -------------------- finish --------------------------
3441 COMMIT;
3442
3443 IF (AMS_DEBUG_HIGH_ON) THEN
3444 AMS_Utility_PVT.debug_message (l_full_name || ': End');
3445 END IF;
3446
3447 EXCEPTION
3448 WHEN OTHERS THEN
3449 ROLLBACK TO Insert_Party_Details_Time;
3450
3451 x_return_status := FND_API.g_ret_sts_error;
3452
3453 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3454 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3455 END IF;
3456 END InsertPartyDetailsTime;
3457 -- End of moving data from Staging to Party Details
3458
3459 PROCEDURE UpdatePartyDetails
3460 IS
3461 l_api_name CONSTANT VARCHAR2(30) := 'UpdatePartyDetails';
3462 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3463 BEGIN
3464 --------------------- initialize -----------------------
3465 SAVEPOINT Update_Party_Details;
3466
3467 IF (AMS_DEBUG_HIGH_ON) THEN
3468 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
3469 END IF;
3470
3471 ----------------------- Update ----------------------
3472 -- Update Party Details with changed data from Staging Area
3473 UPDATE /*+ PARALLEL(AMS_DM_PARTY_DETAILS)*/
3474 ams_dm_party_details pdt SET (
3475 last_updated_by,
3476 last_update_date,
3477 last_update_login,
3478 gender,
3479 ethnicity,
3480 marital_status,
3481 personal_income,
3482 hoh_flag,
3483 household_income,
3484 household_size,
3485 apartment_flag,
3486 rent_flag,
3487 degree_received,
3488 school_type,
3492 interest_music_flag,
3489 interest_art_flag,
3490 interest_books_flag,
3491 interest_movies_flag,
3493 interest_theater_flag,
3494 interest_travel_flag,
3495 interest_drink_flag,
3496 interest_smoke_flag,
3497 interest_other_flag,
3498 employed_flag,
3499 years_employed,
3500 occupation,
3501 military_branch,
3502 residence_type,
3503 resident_length,
3504 presence_of_children,
3505 country,
3506 state,
3507 province,
3508 county,
3509 zip_code,
3510 Reference_use_flag,
3511 gross_annual_income,
3512 debt_to_income_ratio,
3513 num_credit_lines,
3514 num_trade_lines,
3515 net_worth,
3516 total_assets,
3517 tot_debt_outstanding,
3518 bankruptcy_flag,
3519 high_risk_fraud_flag,
3520 gross_annual_sales,
3521 growth_rate_sales_year,
3522 growth_rate_net_prof_year,
3523 current_assets,
3524 current_liabilities,
3525 total_debts,
3526 net_profit,
3527 tangible_net_profit,
3528 capital_amount,
3529 capital_type_indicator,
3530 accounts_receivable,
3531 retained_earnings,
3532 paydex_score_year,
3533 paydex_score_3_month_ago,
3534 industry_paydex_median,
3535 global_failure_score,
3536 dnb_score,
3537 out_of_business_flag,
3538 customer_quality_rank,
3539 fortune_500_rank,
3540 num_of_employees,
3541 legal_status,
3542 year_established,
3543 sic_code1,
3544 minority_business_flag,
3545 small_business_flag,
3546 women_owned_bus_flag,
3547 gov_org_flag,
3548 hq_subsidiary_flag,
3549 foreign_owned_flag,
3550 import_export_bus_flag,
3551 credit_check_flag,
3552 tolerance,
3553 discount_terms_flag,
3554 dunning_letters_flag,
3555 interest_charges_flag,
3556 send_statements_flag,
3557 credit_hold_flag,
3558 credit_rating,
3559 risk_code,
3560 interest_period_days,
3561 payment_grace_days
3562 ) = ( SELECT
3563 fnd_global.user_id last_updated_by,
3564 SYSDATE last_update_date,
3565 fnd_global.conc_login_id last_update_login,
3566 gen.gender gender,
3567 gen.ethnicity ethnicity,
3568 gen.marital_status marital_status,
3569 gen.personal_income personal_income,
3570 gen.hoh_flag hoh_flag,
3571 gen.household_income household_income,
3572 gen.household_size household_size,
3573 gen.apartment_flag apartment_flag,
3574 gen.rent_flag rent_flag,
3575 gen.degree_received degree_received,
3576 gen.school_type school_type,
3577 int.interest_art_flag interest_art_flag,
3578 int.interest_books_flag interest_books_flag,
3579 int.interest_movies_flag interest_movies_flag,
3580 int.interest_music_flag interest_music_flag,
3581 int.interest_theater_flag interest_theater_flag,
3582 int.interest_travel_flag interest_travel_flag,
3583 int.interest_drink_flag interest_drink_flag,
3584 int.interest_smoke_flag interest_smoke_flag,
3585 int.interest_other_flag interest_other_flag,
3586 gen.employed_flag employed_flag,
3587 gen.years_employed years_employed,
3588 gen.occupation occupation,
3589 gen.military_branch military_branch,
3590 gen.residence_type residence_type,
3591 gen.resident_length resident_length,
3592 gen.presence_of_children presence_of_children,
3593 gen.country country,
3594 gen.state state,
3595 gen.province province,
3596 gen.county county,
3597 gen.zip_code zip_code,
3598 gen.reference_use_flag Reference_use_flag,
3599 fin.gross_annual_income gross_annual_income,
3600 fin.debt_to_income_ratio debt_to_income_ratio,
3601 fin.num_credit_lines num_credit_lines,
3602 fin.num_trade_lines num_trade_lines,
3603 fin.net_worth net_worth,
3604 fin.total_assets total_assets,
3605 fin.tot_debt_outstanding tot_debt_outstanding,
3606 fin.bankruptcy_flag bankruptcy_flag,
3607 fin.high_risk_fraud_flag high_risk_fraud_flag,
3608 fin.gross_annual_sales gross_annual_sales,
3609 fin.growth_rate_sales_year growth_rate_sales_year,
3610 fin.growth_rate_net_prof_year growth_rate_net_prof_year,
3611 fin.current_assets current_assets,
3612 fin.current_liabilities current_liabilities,
3613 fin.total_debts total_debts,
3614 fin.net_profit net_profit,
3615 fin.tangible_net_profit tangible_net_profit,
3616 fin.capital_amount capital_amount,
3617 fin.capital_type_indicator capital_type_indicator,
3618 fin.accounts_receivable accounts_receivable,
3622 gen.industry_paydex_median industry_paydex_median,
3619 fin.retained_earnings retained_earnings,
3620 gen.paydex_score_year paydex_score_year,
3621 gen.paydex_score_3_month_ago paydex_score_3_month_ago,
3623 gen.global_failure_score global_failure_score,
3624 gen.dnb_score dnb_score,
3625 gen.out_of_business_flag out_of_business_flag,
3626 gen.customer_quality_rank customer_quality_rank,
3627 gen.fortune_500_rank fortune_500_rank,
3628 gen.num_of_employees num_of_employees,
3629 gen.legal_status legal_status,
3630 gen.year_established year_established,
3631 gen.sic_code1 sic_code1,
3632 gen.minority_business_flag minority_business_flag,
3633 gen.small_business_flag small_business_flag,
3634 gen.women_owned_bus_flag women_owned_bus_flag,
3635 gen.gov_org_flag gov_org_flag,
3636 gen.hq_subsidiary_flag hq_subsidiary_flag,
3637 gen.foreign_owned_flag foreign_owned_flag,
3638 gen.import_export_bus_flag import_export_bus_flag,
3639 ppf.credit_check_flag credit_check_flag,
3640 ppf.tolerance tolerance,
3641 ppf.discount_terms_flag discount_terms_flag,
3642 ppf.dunning_letters_flag dunning_letters_flag,
3643 ppf.interest_charges_flag interest_charges_flag,
3644 ppf.send_statements_flag send_statements_flag,
3645 ppf.credit_hold_flag credit_hold_flag,
3646 ppf.credit_rating credit_rating,
3647 ppf.risk_code risk_code,
3648 ppf.interest_period_days interest_period_days,
3649 ppf.payment_grace_days payment_grace_days
3650 FROM
3651 ams_dm_drv_stg_gt drv,
3652 ams_dm_gen_stg_gt gen,
3653 ams_dm_perint_stg_gt int,
3654 ams_dm_finnum_stg_gt fin,
3655 ams_dm_profile_stg_gt ppf
3656 WHERE
3657 drv.party_id = pdt.party_id
3658 AND drv.party_id = gen.party_id (+)
3659 AND drv.party_id = int.party_id (+)
3660 AND drv.party_id = fin.party_id (+)
3661 AND drv.party_id = ppf.party_id (+)
3662 )
3663 WHERE pdt.party_id IN (SELECT /*+ INDEX_FFS(AMS_DM_DRV_STG_GT_U1)*/ party_id FROM ams_dm_drv_stg_gt)
3664 ;
3665
3666 -------------------- finish --------------------------
3667 COMMIT;
3668
3669 IF (AMS_DEBUG_HIGH_ON) THEN
3670 AMS_Utility_PVT.debug_message (l_full_name || ': End');
3671 END IF;
3672
3673 EXCEPTION
3674 WHEN OTHERS THEN
3675 ROLLBACK TO Update_Party_Details;
3676
3677 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3678 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3679 END IF;
3680
3681 RAISE FND_API.g_exc_unexpected_error;
3682
3683 END UpdatePartyDetails;
3684 -- End of Updating Party Details
3685
3686 PROCEDURE UpdatePartyDetailsTime
3687 IS
3688 l_api_name CONSTANT VARCHAR2(30) := 'UpdatePartyDetailsTime';
3689 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3690 BEGIN
3691 --------------------- initialize -----------------------
3692 SAVEPOINT Update_Party_Details_Time;
3693
3694 IF (AMS_DEBUG_HIGH_ON) THEN
3695 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
3696 END IF;
3697
3698 ----------------------- Insert ----------------------
3699 -- Update Party Details Time with changed data from Staging Area
3700
3701 UPDATE /*+ PARALLEL(AMS_DM_PARTY_DETAILS_TIME)*/
3702 ams_dm_party_details_time pdtt SET (
3703 last_updated_by,
3704 last_update_date,
3705 last_update_login,
3706 age,
3707 days_since_last_school,
3708 days_since_last_event,
3709 num_times_targeted,
3710 last_targeted_channel_code,
3711 times_targeted_month,
3712 times_targeted_3_months,
3713 times_targeted_6_months,
3714 times_targeted_12_months,
3715 days_since_last_targeted,
3716 avg_disc_offered,
3717 num_types_disc_offered,
3718 days_since_first_contact,
3719 days_since_acct_established,
3720 days_since_acct_term,
3721 days_since_acct_activation,
3722 days_since_acct_suspended,
3723 num_times_targeted_email,
3724 num_times_targeted_telemkt,
3725 num_times_targeted_direct,
3726 num_tgt_by_offr_typ1,
3727 num_tgt_by_offr_typ2,
3728 num_tgt_by_offr_typ3,
3729 num_tgt_by_offr_typ4,
3730 avg_talk_time,
3731 avg_order_amount,
3732 avg_units_per_order,
3733 tot_order_amount_year,
3734 tot_order_amount_9_months,
3735 tot_order_amount_6_months,
3736 tot_order_amount_3_months,
3737 tot_num_orders_year,
3738 tot_num_order_9_months,
3739 tot_num_order_6_months,
3740 tot_num_order_3_months,
3741 num_of_sr_year,
3742 num_of_sr_6_months,
3743 num_of_sr_3_months,
3744 num_of_sr_1_month,
3745 avg_resolve_days_year,
3746 avg_resolve_days_6_months,
3747 avg_resolve_days_3_months,
3748 avg_resolve_days_1_month,
3749 order_lines_delivered,
3750 order_lines_ontime,
3754 returns,
3751 order_qty_cumul,
3752 order_recency,
3753 payments,
3755 return_by_value,
3756 return_by_value_pct,
3757 ontime_payments,
3758 ontime_ship_pct,
3759 closed_srs,
3760 COGS,
3761 contracts_cuml,
3762 contract_amt,
3763 contract_duration,
3764 inactive_contracts,
3765 open_contracts,
3766 new_contracts,
3767 renewed_contracts,
3768 escalated_srs,
3769 first_call_cl_rate,
3770 num_of_complaints,
3771 num_of_interactions,
3772 num_of_transfers,
3773 open_srs,
3774 pct_call_rework,
3775 products,
3776 referals,
3777 reopened_srs,
3778 sales,
3779 total_sr_response_time,
3780 pct_first_closed_srs,
3781 avg_complaints,
3782 avg_hold_time,
3783 avg_len_of_emp,
3784 avg_transfers_per_sr,
3785 avg_workload,
3786 tot_calls,
3787 call_length,
3788 profitability
3789 ) = ( SELECT
3790 fnd_global.user_id last_updated_by,
3791 SYSDATE last_update_date,
3792 fnd_global.conc_login_id last_update_login,
3793 agg.age age,
3794 agg.days_since_last_school days_since_last_school,
3795 agg.days_since_last_event days_since_last_event,
3796 agg.num_times_targeted num_times_targeted,
3797 agg.last_targeted_channel_code last_targeted_channel_code,
3798 agg.times_targeted_month times_targeted_month,
3799 agg.times_targeted_3_months times_targeted_3_months,
3800 agg.times_targeted_6_months times_targeted_6_months,
3801 agg.times_targeted_12_months times_targeted_12_months,
3802 agg.days_since_last_targeted days_since_last_targeted,
3803 agg.avg_disc_offered avg_disc_offered,
3804 agg.num_types_disc_offered num_types_disc_offered,
3805 agg.days_since_first_contact days_since_first_contact,
3806 agg.days_since_acct_established days_since_acct_established,
3807 agg.days_since_acct_term days_since_acct_term,
3808 agg.days_since_acct_activation days_since_acct_activation,
3809 agg.days_since_acct_suspended days_since_acct_suspended,
3810 agg.num_times_targeted_email num_times_targeted_email,
3811 agg.num_times_targeted_telemkt num_times_targeted_telemkt,
3812 agg.num_times_targeted_direct num_times_targeted_direct ,
3813 agg.num_tgt_by_offr_typ1 num_tgt_by_offr_typ1,
3814 agg.num_tgt_by_offr_typ2 num_tgt_by_offr_typ2,
3815 agg.num_tgt_by_offr_typ3 num_tgt_by_offr_typ3,
3816 agg.num_tgt_by_offr_typ4 num_tgt_by_offr_typ4,
3817 bic.avg_talk_time avg_talk_time,
3818 bic.avg_order_amount avg_order_amount,
3819 bic.avg_units_per_order avg_units_per_order,
3820 bic.tot_order_amount_year tot_order_amount_year,
3821 bic.tot_order_amount_9_months tot_order_amount_9_months,
3822 bic.tot_order_amount_6_months tot_order_amount_6_months,
3823 bic.tot_order_amount_3_months tot_order_amount_3_months,
3824 bic.tot_num_orders_year tot_num_orders_year,
3825 bic.tot_num_order_9_months tot_num_order_9_months,
3826 bic.tot_num_order_6_months tot_num_order_6_months,
3827 bic.tot_num_order_3_months tot_num_order_3_months,
3828 bic.num_of_sr_year num_of_sr_year,
3829 bic.num_of_sr_6_months num_of_sr_6_months,
3830 bic.num_of_sr_3_months num_of_sr_3_months,
3831 bic.num_of_sr_1_month num_of_sr_1_month,
3832 bic.avg_resolve_days_year avg_resolve_days_year,
3833 bic.avg_resolve_days_6_months avg_resolve_days_6_months,
3834 bic.avg_resolve_days_3_months avg_resolve_days_3_months,
3835 bic.avg_resolve_days_1_month avg_resolve_days_1_month,
3836 bic.order_lines_delivered order_lines_delivered,
3837 bic.order_lines_ontime order_lines_ontime,
3838 bic.order_qty_cumul order_qty_cumul,
3839 bic.order_recency order_recency,
3840 bic.payments payments,
3841 bic.returns returns,
3842 bic.return_by_value return_by_value,
3843 bic.return_by_value_pct return_by_value_pct,
3844 bic.ontime_payments ontime_payments,
3845 bic.ontime_ship_pct ontime_ship_pct,
3846 bic.closed_srs closed_srs,
3847 bic.COGS COGS,
3848 bic.contracts_cuml contracts_cuml,
3849 bic.contract_amt contract_amt,
3850 bic.contract_duration contract_duration,
3851 bic.inactive_contracts inactive_contracts,
3852 bic.open_contracts open_contracts,
3853 bic.new_contracts new_contracts,
3854 bic.renewed_contracts renewed_contracts,
3855 bic.escalated_srs escalated_srs,
3856 bic.first_call_cl_rate first_call_cl_rate,
3857 bic.num_of_complaints num_of_complaints,
3858 bic.num_of_interactions num_of_interactions,
3862 bic.products products,
3859 bic.num_of_transfers num_of_transfers,
3860 bic.open_srs open_srs,
3861 bic.pct_call_rework pct_call_rework,
3863 bic.referals referals,
3864 bic.reopened_srs reopened_srs,
3865 bic.sales sales,
3866 bic.total_sr_response_time total_sr_response_time,
3867 bic.pct_first_closed_srs pct_first_closed_srs,
3868 bic.avg_complaints avg_complaints,
3869 bic.avg_hold_time avg_hold_time,
3870 bic.avg_len_of_emp avg_len_of_emp,
3871 bic.avg_transfers_per_sr avg_transfers_per_sr,
3872 bic.avg_workload avg_workload,
3873 bic.tot_calls tot_calls,
3874 bic.call_length call_length,
3875 bic.profitability profitability
3876 FROM
3877 ams_dm_drv_stg_gt drv,
3878 ams_dm_agg_stg_gt agg,
3879 ams_dm_bic_stg_gt bic
3880 WHERE drv.party_id = pdtt.party_id
3881 AND drv.party_id = agg.party_id (+)
3882 AND drv.party_id = bic.party_id (+)
3883 )
3884 WHERE pdtt.party_id IN (SELECT /*+ INDEX_FFS(AMS_DM_DRV_STG_GT_U1)*/ party_id FROM ams_dm_drv_stg_gt)
3885 ;
3886
3887 -------------------- finish --------------------------
3888 COMMIT;
3889
3890 IF (AMS_DEBUG_HIGH_ON) THEN
3891 AMS_Utility_PVT.debug_message (l_full_name || ': End');
3892 END IF;
3893
3894 EXCEPTION
3895 WHEN OTHERS THEN
3896 ROLLBACK TO Update_Party_Details_Time;
3897
3898 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3899 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3900 END IF;
3901
3902 RAISE FND_API.g_exc_unexpected_error;
3903
3904 END UpdatePartyDetailsTime;
3905 -- End of Updating Party Details Time
3906
3907 PROCEDURE ExtractMain (
3908 p_api_version IN NUMBER,
3909 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3910 p_commit IN VARCHAR2 := FND_API.g_false,
3911
3912 x_return_status OUT NOCOPY VARCHAR2,
3913 x_msg_count OUT NOCOPY NUMBER,
3914 x_msg_data OUT NOCOPY VARCHAR2,
3915
3916 p_job IN VARCHAR2 DEFAULT 'NULL',
3917 p_mode IN VARCHAR2,
3918 p_model_id IN NUMBER DEFAULT NULL,
3919 p_model_type IN VARCHAR2 DEFAULT 'NULL'
3920 )
3921 IS
3922 -- This is the main driving procedure for the extraction process. It calls all
3923 -- the other procedures as required.
3924 -- p_mode: Parameter specifying whether this is an Insert or an Update process
3925 -- Values: 'I' --> Insert Process
3926 -- 'U' --> Update Process
3927 -- (the following are valid only if p_mode is 'I')
3928 -- p_model_id: Model ID for the data mining model to be built or scored
3929 -- p_model_type: Whether this extraction process is for model building ('MODL')
3930 -- or scoring the model ('SCOR')
3931 --
3932
3933 --kbasavar 8/4/2003
3934 CURSOR c_model_type(p_model_id IN NUMBER) is
3935 SELECT model_type
3936 FROM ams_dm_models_vl
3937 WHERE model_id=p_model_id
3938 ;
3939
3940 -- kbasavar 12/29/2003 get model_id if it is Score
3941 CURSOR c_model_id (p_score_id IN NUMBER) IS
3942 SELECT model_id
3943 FROM ams_dm_scores_all_b
3944 WHERE score_id = p_score_id
3945 ;
3946
3947 l_api_version CONSTANT NUMBER := 1.0;
3948 l_api_name CONSTANT VARCHAR2(30) := 'ExtractMain';
3949 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3950 l_return_status VARCHAR2(1);
3951
3952 l_mode VARCHAR2(1);
3953 l_object_id NUMBER ;
3954 l_object_type VARCHAR2(30);
3955 l_is_b2b BOOLEAN;
3956
3957 l_model_type VARCHAR2(30);
3958 l_is_org_prod BOOLEAN;
3959 l_model_id NUMBER;
3960 l_party_type VARCHAR2(30);
3961 l_index NUMBER;
3962 BEGIN
3963 --------------------- initialize -----------------------
3964 SAVEPOINT Extract_Main;
3965
3966 IF (AMS_DEBUG_HIGH_ON) THEN
3967 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
3968 END IF;
3969
3970 IF FND_API.to_boolean (p_init_msg_list) THEN
3971 FND_MSG_PUB.initialize;
3972 END IF;
3973
3974 IF NOT FND_API.compatible_api_call(
3975 l_api_version,
3976 p_api_version,
3977 l_api_name,
3978 g_pkg_name
3979 ) THEN
3980 RAISE FND_API.g_exc_unexpected_error;
3981 END IF;
3982
3983 x_return_status := FND_API.g_ret_sts_success;
3984
3985 -- truncate staging tables
3986 TruncateStgTables;
3987
3988 l_mode := p_mode;
3989 l_object_id := p_model_id;
3990 l_object_type := p_model_type;
3991
3992 IF l_object_type = 'SCOR' THEN
3993 OPEN c_model_id(l_object_id);
3994 FETCH c_model_id INTO l_model_id;
3995 CLOSE c_model_id;
3996 ELSE
3997 l_model_id := l_object_id;
3998 END IF;
3999
4000
4001 AMS_DMSelection_PVT.is_org_prod_affn(
4002 p_model_id => l_model_id,
4006 AMS_DMSelection_PVT.is_b2b_data_source(
4003 x_is_org_prod => l_is_org_prod
4004 );
4005
4007 p_model_id => l_model_id,
4008 x_is_b2b => l_is_b2b
4009 );
4010
4011 OPEN c_model_type(l_model_id);
4012 FETCH c_model_type into l_model_type;
4013 CLOSE c_model_type;
4014
4015 IF l_mode = 'I' THEN -- insert mode
4016 -- insert new records into driving table
4017 InsertDrvStgIns (
4018 p_object_id => l_object_id,
4019 p_object_type => l_object_type,
4020 x_return_status => x_return_status
4021 );
4022
4023 IF x_return_status <> FND_API.g_ret_sts_success THEN
4024 RAISE FND_API.g_exc_error;
4025 END IF;
4026
4027 -- load into staging tables
4028 InsertGenStg (
4029 p_is_b2b => l_is_b2b,
4030 p_model_type => l_model_type,
4031 p_is_org_prod => l_is_org_prod
4032 );
4033 InsertExpStg(
4034 p_is_b2b => l_is_b2b,
4035 p_model_type => l_model_type,
4036 p_is_org_prod => l_is_org_prod
4037 );
4038
4039 IF l_is_b2b AND ( l_model_type='CUSTOMER_PROFITABILITY' OR l_is_org_prod)THEN
4040 InsertAggStgOrg;
4041 ELSE
4042 InsertAggStg(
4043 p_is_b2b => l_is_b2b
4044 );
4045 END IF;
4046
4047 InsertBICStg(
4048 p_is_b2b => l_is_b2b,
4049 p_model_type => l_model_type,
4050 p_is_org_prod => l_is_org_prod
4051 );
4052
4053 -- load into targets
4054 InsertPartyDetails(x_return_status);
4055
4056 IF x_return_status <> FND_API.g_ret_sts_success THEN
4057 RAISE FND_API.g_exc_error;
4058 END IF;
4059
4060 InsertPartyDetailsTime(x_return_status);
4061
4062 IF x_return_status <> FND_API.g_ret_sts_success THEN
4063 -- kbasavar migrated chi's changes for bug 3089951
4064 sync_party_tables;
4065 RAISE FND_API.g_exc_error;
4066 END IF;
4067
4068 /*
4069 The extraction process is driven by various criteria like if its a B2B model or is it a customer profitability etc.
4070 If the mode is update then the model id will be null and hence l_is_b2b and the model type will have invalid values.
4071 In order to tackle this situation, if the mode is update, perform the extraction process separately for each party type.
4072 In future if there is any new party_type introduced then it should be handled explicitly.
4073 */
4074 ELSIF l_mode = G_MODE_UPDATE THEN -- update mode
4075 -- insert changed records into driving table
4076
4077 FOR l_index IN 1..3 LOOP
4078 IF l_index = 1 then
4079 l_party_type := 'ORGANIZATION';
4080 l_is_b2b := TRUE ;
4081 l_model_type := 'CUSTOMER_PROFITABILITY';
4082 l_is_org_prod := FALSE;
4083 ELSIF l_index = 2 THEN
4084 l_party_type := 'PARTY_RELATIONSHIP';
4085 l_is_b2b := TRUE;
4086 l_model_type := 'EMAIL';
4087 l_is_org_prod := FALSE;
4088 ELSE
4089 l_party_type := 'PERSON';
4090 l_is_b2b := FALSE;
4091 l_model_type := 'EMAIL';
4092 l_is_org_prod := FALSE;
4093 END IF;
4094
4095 AMS_Utility_PVT.create_log (
4096 x_return_status => l_return_status,
4097 p_arc_log_used_by => 'MODL',
4098 p_log_used_by_id => -1, -- party update is for all of party details, so no specific id
4099 p_msg_data =>l_full_name || '::' || l_party_type,
4100 p_msg_type => ''
4101 );
4102
4103 InsertDrvStgUpd(
4104 p_party_type => l_party_type,
4105 x_return_status => x_return_status
4106 );
4107
4108 IF x_return_status <> FND_API.g_ret_sts_success THEN
4109 RAISE FND_API.g_exc_error;
4110 END IF;
4111
4112 -- insert into staging tables
4113 InsertGenStg (
4114 p_is_b2b => l_is_b2b,
4115 p_model_type => l_model_type,
4116 p_is_org_prod => l_is_org_prod
4117 );
4118 InsertExpStg(
4119 p_is_b2b => l_is_b2b,
4120 p_model_type => l_model_type,
4121 p_is_org_prod => l_is_org_prod
4122 );
4123
4124 IF l_is_b2b AND ( l_model_type='CUSTOMER_PROFITABILITY' OR l_is_org_prod) THEN
4125 InsertAggStgOrg;
4126 ELSE
4127 InsertAggStg(
4128 p_is_b2b => l_is_b2b
4129 );
4130 END IF;
4131
4132
4133 InsertBICStg(
4134 p_is_b2b => l_is_b2b,
4135 p_model_type => l_model_type,
4136 p_is_org_prod => l_is_org_prod
4137 );
4138
4139 -- load into targets
4140 UpdatePartyDetails;
4141 UpdatePartyDetailsTime;
4142
4143 TruncateTable ('AMS_DM_DRV_STG_GT');
4144
4145 END LOOP;
4146
4147 END IF;
4148
4149 -- analyze_mining_tables;
4150
4151 -------------------- finish --------------------------
4152 IF FND_API.to_boolean (p_commit) THEN
4153 COMMIT;
4154 END IF;
4155
4156 FND_MSG_PUB.count_and_get (
4157 p_encoded => FND_API.g_false,
4158 p_count => x_msg_count,
4159 p_data => x_msg_data
4160 );
4161
4162 IF (AMS_DEBUG_HIGH_ON) THEN
4163 AMS_Utility_PVT.debug_message (l_full_name || ': End');
4164 END IF;
4165
4166 EXCEPTION
4167 WHEN FND_API.g_exc_error THEN
4168 ROLLBACK TO Extract_Main;
4169 x_return_status := FND_API.g_ret_sts_error;
4170 FND_MSG_PUB.count_and_get (
4171 p_encoded => FND_API.g_false,
4172 p_count => x_msg_count,
4173 p_data => x_msg_data
4174 );
4175 WHEN FND_API.g_exc_unexpected_error THEN
4176 ROLLBACK TO Extract_Main;
4177 x_return_status := FND_API.g_ret_sts_unexp_error ;
4178 FND_MSG_PUB.count_and_get (
4179 p_encoded => FND_API.g_false,
4180 p_count => x_msg_count,
4181 p_data => x_msg_data
4182 );
4183 WHEN OTHERS THEN
4184 ROLLBACK TO Extract_Main;
4185 x_return_status := FND_API.g_ret_sts_unexp_error ;
4186
4187 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
4188 THEN
4189 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
4190 END IF;
4191
4192 FND_MSG_PUB.count_and_get (
4193 p_encoded => FND_API.g_false,
4194 p_count => x_msg_count,
4195 p_data => x_msg_data
4196 );
4197 END ExtractMain;
4198 -- End of Main Extract Procedure
4199
4200
4201 -- History
4202 -- 06-Mar-2001 choang Created.
4203 --
4204 PROCEDURE schedule_update_parties (
4205 errbuf OUT NOCOPY VARCHAR2,
4206 retcode OUT NOCOPY VARCHAR2
4207 )
4208 IS
4209 l_return_status VARCHAR2(1);
4210 l_msg_count NUMBER;
4211 l_msg_data VARCHAR2(4000);
4212 BEGIN
4213 retcode := 0;
4214
4215 ExtractMain (
4216 p_api_version => 1.0,
4217 p_init_msg_list => FND_API.G_TRUE,
4218 p_commit => FND_API.G_TRUE,
4219
4220 x_return_status => l_return_status,
4221 x_msg_count => l_msg_count,
4222 x_msg_data => l_msg_data,
4223 p_mode => G_MODE_UPDATE
4224 );
4225 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4226 FOR i IN 1 .. l_msg_count LOOP
4227 AMS_Utility_PVT.create_log (
4228 x_return_status => l_return_status,
4229 p_arc_log_used_by => 'MODL',
4230 p_log_used_by_id => -1, -- party update is for all of party details, so no specific id
4231 p_msg_data => FND_MSG_PUB.get(i, FND_API.g_false),
4232 p_msg_type => 'ERROR'
4233 );
4234 END LOOP;
4235 retcode := 2;
4236 END IF;
4237 END schedule_update_parties;
4238
4239
4240 PROCEDURE analyze_mining_tables
4241 IS
4242 -- for table analyzing
4243 l_result BOOLEAN;
4244 l_status VARCHAR2(10);
4245 l_industry VARCHAR2(10);
4246 l_ams_schema VARCHAR2(30);
4247 BEGIN
4248 -- gather table statistics
4249 -- this is needed for the ODM build
4250 -- process to complete successfully
4251 l_result := fnd_installation.get_app_info(
4252 'AMS',
4253 l_status,
4254 l_industry,
4255 l_ams_schema
4256 );
4257
4258 DBMS_STATS.gather_table_stats (
4259 ownname => l_ams_schema,
4260 tabname => 'AMS_DM_PARTY_DETAILS',
4261 estimate_percent => 99,
4262 cascade => TRUE
4263 );
4264
4265 DBMS_STATS.gather_table_stats (
4266 ownname => l_ams_schema,
4267 tabname => 'AMS_DM_PARTY_DETAILS_TIME',
4268 estimate_percent => 99,
4269 cascade => TRUE
4270 );
4271
4272 DBMS_STATS.gather_table_stats (
4273 ownname => l_ams_schema,
4274 tabname => 'AMS_DM_SOURCE',
4275 estimate_percent => 99,
4276 cascade => TRUE
4277 );
4278 END analyze_mining_tables;
4279
4280 --
4281 -- PROCEDURE
4282 -- sync_party_tables
4283 --
4284 -- DESCRIPTION
4285 -- Synchronizes the party records of ams_dm_party_details
4286 -- and ams_dm_party_details_time by deleting from party
4287 -- details. This procedure is meant to be called if the
4288 -- insert for party details time fails.
4289 --
4290 -- HISTORY
4291 -- 08-aug-2003 choang Created for bug 3089951
4292 -- 10-Sep-2003 kbasavar migrated to mainline
4293 PROCEDURE sync_party_tables
4294 IS
4295 BEGIN
4296 DELETE FROM ams_dm_party_details d
4297 WHERE NOT EXISTS (SELECT 1 FROM ams_dm_party_details_time t
4298 WHERE t.party_id = d.party_id)
4299 ;
4300 END;
4301
4302
4303 END ams_DMExtract_pvt ; -- END OF PACKAGE BODY