PACKAGE BODY: APPS.AMS_CAMPAIGN_ITEMS_DENORM_PVT

Source

1 package body AMS_CAMPAIGN_ITEMS_DENORM_PVT as
2 /* $Header: amsvcpib.pls 115.3 2003/02/18 13:35:56 sikalyan ship $ */
3 
4 --=======================================================
5 -- script to populate AMS_IBA_CPN_ITEMS_DENORM table
6 --=======================================================
7 
8 procedure loadCampaignItemsDenormTable(
9 	errbuf OUT NOCOPY VARCHAR2,
10 	retcode OUT NOCOPY NUMBER
11 )
12 is
13 
14 CURSOR SchedCur IS
15 
16  SELECT inventory_item_id, organization_id, category_id,
17         category_set_id, act_product_used_by_id,
18         arc_act_product_used_by
19   FROM ams_act_products a,
20        ams_campaign_schedules_b b
21  WHERE a.act_product_used_by_id = b.schedule_id
22  AND b.activity_type_code = 'INTERNET'
23  AND b.status_code = 'ACTIVE'
24  AND b.active_flag = 'Y'
25  AND b.activity_id = 30
26  AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(b.start_date_time),TRUNC(SYSDATE)) AND NVL(TRUNC(b.end_date_time),TRUNC(SYSDATE));
27 
28  -- AND nvl(b.end_date_time,SYSDATE) >= SYSDATE
29 
30 BEGIN
31 
32  DELETE FROM ams_iba_cpn_items_denorm;
33 
34 -- commit;
35 
36  for SchedItem in SchedCur loop
37 
38    IF SchedItem.inventory_item_id is NOT NULL
39    THEN
40      insert into ams_iba_cpn_items_denorm
41      ( cpn_item_id
42       , item_id
43       , inventory_org_id
44       , object_used_by_type
45       , object_used_by_id
46       , OBJECT_VERSION_NUMBER
47       , CREATED_BY
48       , CREATION_DATE
49       , LAST_UPDATED_BY
50       , LAST_UPDATED_DATE
51       , LAST_UPDATE_LOGIN
52      )
53      select
54       ams_iba_cpn_items_denorm_s.nextval,
55       SchedItem.inventory_item_id,
56       SchedItem.organization_id,
57       SchedItem.arc_act_product_used_by,
58       SchedItem.act_product_used_by_id,
59       1,
60       FND_GLOBAL.user_id,
61       SYSDATE,
62       FND_GLOBAL.user_id,
63       SYSDATE,
64       FND_GLOBAL.conc_login_id
65       from DUAL;
66   END IF;
67 
68   IF SchedItem.category_id IS NOT NULL
69   THEN
70     INSERT INTO ams_iba_cpn_items_denorm
71     ( cpn_item_id
72       , item_id
73       , inventory_org_id
74       , object_used_by_type
75       , object_used_by_id
76       , OBJECT_VERSION_NUMBER
77       , CREATED_BY
78       , CREATION_DATE
79       , LAST_UPDATED_BY
80       , LAST_UPDATED_DATE
81       , LAST_UPDATE_LOGIN
82     )
83     SELECT
84       ams_iba_cpn_items_denorm_s.nextval,
85       inventory_item_id,
86       organization_id,
87       SchedItem.arc_act_product_used_by,
88       SchedItem.act_product_used_by_id,
89       1,
90       FND_GLOBAL.user_id,
91       SYSDATE,
92       FND_GLOBAL.user_id,
93       SYSDATE,
94       FND_GLOBAL.conc_login_id
95     FROM mtl_item_categories a
96     WHERE category_id = SchedItem.category_id
97     AND NOT EXISTS
98        (select 'x' from ams_iba_cpn_items_denorm
99         where item_id = a.inventory_item_id
100         AND inventory_org_id = a.organization_id);
101 
102   END IF;
103 
104 end loop;
105 
106 --commit;
107 
108 END loadCampaignItemsDenormTable;
109 
110 END AMS_CAMPAIGN_ITEMS_DENORM_PVT;