[Home] [Help]
PACKAGE BODY: APPS.AMS_MINISITE_DENORM_PVT
Source
1 package body AMS_MINISITE_DENORM_PVT as
2 /* $Header: amsvmsib.pls 120.2 2005/12/30 00:33:00 sikalyan ship $ */
3
4 --=======================================================
5 -- script used to populate AMS_IBA_MS_ITEMS_DENORM table
6 -- with data required for section search
7 --=======================================================
8
9 procedure loadMsitesDenormTable(
10 errbuf OUT NOCOPY VARCHAR2,
11 retcode OUT NOCOPY NUMBER
12 )
13 is
14
15 dd varchar2(20);
16 l_out_AppInfo boolean;
17 l_status_AppInfo varchar2(300) ;
18 l_industry_AppInfo varchar2(300) ;
19 l_oracle_schema_AppInfo varchar2(300) ;
20 l_application_short_name varchar2(300) ;
21
22 CURSOR compCur IS
23 select MSITE_ID, MSITE_ROOT_SECTION_ID, START_DATE_ACTIVE from ibe_msites_b
24 where msite_id <> 1;
25 -- where END_DATE_ACTIVE > sysdate ;
26
27 cursor allTopLevelSections(l_minisite_id in number, l_msite_root_section_id in number) IS
28 SELECT distinct s.section_id
29 FROM ibe_dsp_msite_sct_sects mss, ibe_dsp_sections_b s
30 WHERE mss.parent_section_id = l_msite_root_section_id
31 AND mss.mini_site_id = l_minisite_id
32 AND s.section_id = mss.child_section_id
33 AND s.section_type_code = 'N'
34 --and not exists (select 1
35 -- from ibe_msites_b cc
36 -- where s.section_id = cc.MSITE_ROOT_SECTION_ID
37 -- )
38 -- AND NVL(mss.start_date_active, SYSDATE) <= SYSDATE
39 -- AND NVL(mss.end_date_active, SYSDATE) >= SYSDATE
40 -- AND NVL(s.start_date_active, SYSDATE) <= SYSDATE
41 -- AND NVL(s.end_date_active, SYSDATE) >= SYSDATE
42 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(MSS.START_DATE_ACTIVE),TRUNC(SYSDATE)) AND NVL(TRUNC(MSS.END_DATE_ACTIVE),TRUNC(SYSDATE))
43 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(S.START_DATE_ACTIVE),TRUNC(SYSDATE)) AND NVL(TRUNC(S.END_DATE_ACTIVE),TRUNC(SYSDATE))
44 ORDER BY s.section_id;
45
46 CURSOR ItemCur(p_sectionId in NUMBER, p_msite_id in NUMBER)
47 IS
48 SELECT DISTINCT f.inventory_item_id, f.organization_id
49 FROM ibe_dsp_section_items f
50 WHERE f.section_id in
51 (
52 select cs.child_section_id from
53 (
54 SELECT mss.child_section_id child_section_id
55 FROM ibe_DSP_MSITE_SCT_SECTS mss
56 START WITH mss.child_section_id = p_sectionId
57 and mss.mini_site_id = p_msite_id
58 CONNECT BY PRIOR mss.child_section_id = mss.parent_section_id
59 and mss.mini_site_id = p_msite_id
60 ) cs
61 where cs.child_section_id not in
62 (
63 select mss2.parent_section_id
64 from ibe_DSP_MSITE_SCT_SECTS mss2
65 where mini_site_id = p_msite_id
66 )
67 and NOT EXISTS
68 (
69 select 1
70 from ibe_dsp_sections_b s
71 where s.section_id = cs.child_section_id
72 and s.status_code = 'UNPUBLISHED'
73 )
74 );
75
76 -- SELECT DISTINCT f.inventory_item_id, f.organization_id
77 --p_sectionId, p_msite_id
78 -- FROM ibe_dsp_section_items f
79 -- WHERE f.section_id in
80 -- (
81 -- SELECT mss.child_section_id
82 --SELECT mss.parent_section_id
83 -- FROM ibe_DSP_MSITE_SCT_SECTS mss
84 -- START WITH mss.child_section_id = p_sectionId
85 --START WITH mss.parent_section_id = p_sectionId
86 -- and mss.mini_site_id = p_msite_id
87 -- CONNECT BY PRIOR mss.child_section_id = mss.parent_section_id
88 -- AND mss.mini_site_id = p_msite_id
89 -- MINUS
90 -- (SELECT mss2.parent_section_id
91 -- FROM ibe_DSP_MSITE_SCT_SECTS mss2
92 -- )
93 -- MINUS
94 -- (
95 -- select s.section_id
96 -- from ibe_DSP_MSITE_SCT_SECTS mss3 , ibe_dsp_sections_b s
97 -- where mss3.child_section_id = s.section_id
98 -- and s.status_code = 'UNPUBLISHED'
99 -- and mss3.mini_site_id = p_msite_id
100 -- )
101 -- );
102
103
104 BEGIN
105
106 DELETE FROM ams_iba_ms_items_denorm;
107
108 --commit;
109
110 for v_CompoundData in compCur loop
111
112 for sectionIdData in allTopLevelSections(v_CompoundData.MSITE_ID, v_CompoundData.MSITE_ROOT_SECTION_ID ) loop
113
114 for ItemData in ItemCur(sectionIdData.section_id, v_CompoundData.MSITE_ID)
115 loop
116 insert into ams_iba_ms_items_denorm
117 ( item_id --inventory_item_id
118 , inventory_org_id --organization_id
119 , top_section_id --section_id
120 , minisite_id
121 , minisite_item_id
122 , start_date_active
123 , OBJECT_VERSION_NUMBER
124 , CREATED_BY
125 , CREATION_DATE
126 , LAST_UPDATED_BY
127 , LAST_UPDATED_DATE
128 , LAST_UPDATE_LOGIN
129 )
130 VALUES
131 (
132 ItemData.inventory_item_id,
133 ItemData.organization_id,
134 sectionIdData.section_id,
135 v_CompoundData.MSITE_ID,
136 ams_iba_ms_items_denorm_s.nextval,
137 v_CompoundData.start_date_active,
138 1,
139 FND_GLOBAL.user_id,
140 SYSDATE,
141 FND_GLOBAL.user_id,
142 SYSDATE,
143 FND_GLOBAL.conc_login_id
144 );
145
146 end loop;
147 /*
148 select distinct f.inventory_item_id,f.organization_id, sectionIdData.section_id,
149 v_CompoundData.MSITE_ID,
150 -- NextMinisiteItemId, --ams_iba_ms_items_denorm_s.nextval,
151 ams_iba_ms_items_denorm_s.nextval,
152 v_CompoundData.start_date_active,
153 1,
154 FND_GLOBAL.user_id,
155 SYSDATE,
156 FND_GLOBAL.user_id,
157 SYSDATE,
158 FND_GLOBAL.conc_login_id
159 from ibe_dsp_section_items f
160 where f.section_id in
161 (
162 SELECT mss.child_section_id
163 --SELECT mss.parent_section_id
164 FROM ibe_DSP_MSITE_SCT_SECTS mss
165 START WITH mss.child_section_id = sectionIdData.section_id
166 --START WITH mss.parent_section_id = sectionIdData.section_id
167 and mss.mini_site_id = v_CompoundData.MSITE_ID
168 CONNECT BY PRIOR mss.child_section_id = mss.parent_section_id
169 AND mss.mini_site_id =v_CompoundData.MSITE_ID
170 MINUS
171 (SELECT mss2.parent_section_id
172 FROM ibe_DSP_MSITE_SCT_SECTS mss2
173 )
174 MINUS
175 (
176 select s.section_id
177 from ibe_DSP_MSITE_SCT_SECTS mss3 , ibe_dsp_sections_b s
178 where mss3.child_section_id = s.section_id
179 and s.status_code = 'UNPUBLISHED'
180 and mss3.mini_site_id = v_CompoundData.MSITE_ID
181 )
182 );
183 */
184 end loop;
185
186 end loop;
187
188 --commit;
189
190 END loadMsitesDenormTable;
191
192 END AMS_MINISITE_DENORM_PVT;