1 PACKAGE BODY QPR_REPORT_ENTITIES_PVT AS
2 /* $Header: QPRRPTEB.pls 120.0 2007/10/11 13:20:22 agbennet noship $ */
3
4 --===================
5 -- PROCEDURES
6 --===================
7
8 --========================================================================
9 -- PROCEDURE : DELETE_REPORT_HEADER
10 --
11 -- PARAMETERS:
12 -- p_report_id Id for the Report Header to be deleted
13 -- x_return_status Return status
14 --
15 -- COMMENT : This procedure deletes the record from the Report Header tables
16 --========================================================================
17 PROCEDURE DELETE_REPORT_HEADER(
18 p_report_id IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2)
20 IS
21 BEGIN
22
23 DELETE FROM QPR_REPORT_HDRS_B
24 WHERE REPORT_HEADER_ID = p_report_id;
25
26 DELETE FROM QPR_REPORT_HDRS_TL
27 WHERE REPORT_HEADER_ID = p_report_id;
28
29 x_return_status := FND_API.G_RET_STS_SUCCESS;
30 EXCEPTION
31 WHEN OTHERS
32 THEN
33 x_return_status := FND_API.G_RET_STS_ERROR;
34 END DELETE_REPORT_HEADER;
35
36
37 --========================================================================
38 -- PROCEDURE : DELETE_REPORT
39 --
40 -- PARAMETERS:
41 -- p_report_id Report Header Id for which Report needs
42 -- to be deleted
43 --
44 -- x_return_status Return status
45 --
46 -- COMMENT : This procedure deletes the report header, lines, relations
47 -- for a given report id
48 --========================================================================
49
50 PROCEDURE DELETE_REPORT(
51 p_report_id IN NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2)
53 IS
54 l_report_line_id NUMBER;
55 l_related_report_id NUMBER;
56
57 CURSOR Get_Report_Lines(p_report_id NUMBER)
58 IS
59 SELECT REPORT_LINE_ID
60 FROM QPR_REPORT_LINES
61 WHERE REPORT_HEADER_ID = p_report_id;
62
63 BEGIN
64
65 DELETE_RELATED_REPORTS(p_report_id,x_return_status);
66
67 OPEN Get_Report_Lines(p_report_id);
68 LOOP
69 FETCH Get_Report_Lines INTO l_report_line_id;
70 IF Get_Report_Lines%NOTFOUND
71 THEN
72 EXIT;
73 END IF;
74 DELETE FROM BISM_OBJECTS
75 WHERE OBJECT_NAME LIKE 'qpr'||l_report_line_id||'q%';
76 END LOOP;
77 CLOSE Get_Report_Lines;
78
79 DELETE FROM QPR_REPORT_LINES
80 WHERE REPORT_HEADER_ID = p_report_id;
81
82 DELETE FROM QPR_REPORT_RELNS
83 WHERE PARENT_REPORT_ID = p_report_id;
84
85 DELETE FROM QPR_REPORT_HDRS_TL
86 WHERE REPORT_HEADER_ID = p_report_id;
87
88 DELETE FROM QPR_REPORT_HDRS_B
89 WHERE REPORT_HEADER_ID = p_report_id;
90
91
92 EXCEPTION
93 WHEN OTHERS
94 THEN
95 x_return_status := FND_API.G_RET_STS_ERROR;
96
97 END DELETE_REPORT;
98
99 --===========================================================
100 -- PROCEDURE : DELETE_RELATED_REPORTS
101 --
102 -- PARAMETERS:
103 -- p_report_id Parent report id for whom related reports
104 -- should be deleted
105 -- x_return_status Return status
106 --
107 -- COMMENT : This procedure deletes all the related report headers, lines
108 -- for a given report id
109 --========================================================================
110
111 PROCEDURE DELETE_RELATED_REPORTS(
112 p_parent_report_id IN NUMBER,
113 x_return_status OUT NOCOPY VARCHAR2)
114
115 IS
116
117 l_related_report_id NUMBER;
118 l_report_line_id NUMBER;
119
120 CURSOR Get_Report_Lines(p_parent_report_id NUMBER)
121 IS
122 SELECT REPORT_LINE_ID
123 FROM QPR_REPORT_LINES
124 WHERE REPORT_HEADER_ID = p_parent_report_id;
125
126 CURSOR Get_Related_Reports(p_parent_report_id NUMBER)
127 IS
128 SELECT TARGET_REPORT_ID
129 FROM QPR_REPORT_RELNS
130 WHERE PARENT_REPORT_ID = p_parent_report_id;
131
132 BEGIN
133
134 OPEN Get_Related_Reports(p_parent_report_id);
135 LOOP
136 FETCH Get_Related_Reports INTO l_related_report_id;
137 IF Get_Related_Reports%NOTFOUND
138 THEN
139 EXIT;
140 END IF;
141 OPEN Get_Report_Lines(l_related_report_id);
142 LOOP
143 FETCH Get_Report_Lines INTO l_report_line_id;
144 IF Get_Report_Lines%NOTFOUND
145 THEN
146 EXIT;
147 END IF;
148 DELETE FROM BISM_OBJECTS
149 WHERE OBJECT_NAME LIKE 'qpr'||l_report_line_id||'q%';
150 END LOOP;
151 CLOSE Get_Report_Lines;
152 END LOOP;
153 CLOSE Get_Related_Reports;
154
155 DELETE FROM QPR_REPORT_HDRS_B WHERE REPORT_HEADER_ID IN
156 (SELECT TARGET_REPORT_ID FROM QPR_REPORT_RELNS WHERE PARENT_REPORT_ID = p_parent_report_id);
157
158 DELETE FROM QPR_REPORT_HDRS_TL WHERE REPORT_HEADER_ID IN
159 (SELECT TARGET_REPORT_ID FROM QPR_REPORT_RELNS WHERE PARENT_REPORT_ID = p_parent_report_id);
160
161 DELETE FROM QPR_REPORT_LINES WHERE REPORT_HEADER_ID IN
162 (SELECT TARGET_REPORT_ID FROM QPR_REPORT_RELNS WHERE PARENT_REPORT_ID = p_parent_report_id);
163
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165
166 EXCEPTION
167 WHEN OTHERS
168 THEN
169 x_return_status := FND_API.G_RET_STS_ERROR;
170
171 END DELETE_RELATED_REPORTS;
172
173
174 --===========================================================
175 -- PROCEDURE : DELETE_REPORTS
176 --
177 -- PARAMETERS:
178 -- p_price_plan_id Price plan ID for which reports needs
179 -- to be deleted
180 -- x_return_status Return status
181 --
182 -- COMMENT : This procedure deletes all the report headers, lines, relations
183 -- for a given price plan id
184 --========================================================================
185
186 PROCEDURE DELETE_REPORTS(
187 p_price_plan_id IN NUMBER,
188 x_return_status OUT NOCOPY VARCHAR2)
189 IS
190
191 l_report_line_id NUMBER;
192
193 CURSOR Get_Report_Lines(c_price_plan_id NUMBER)
194 IS
195 SELECT QRL.REPORT_LINE_ID
196 FROM QPR_REPORT_HDRS_B QRH,
197 QPR_REPORT_LINES QRL
198 WHERE QRH.REPORT_HEADER_ID = QRL.REPORT_HEADER_ID
199 AND QRH.PLAN_ID = c_price_plan_id;
200 BEGIN
201
202 OPEN Get_Report_lines(p_price_plan_id);
203 LOOP
204
205 FETCH Get_Report_Lines INTO l_report_line_id;
206 IF Get_Report_Lines%NOTFOUND
207 THEN
208 EXIT;
209 END IF;
210
211 DELETE FROM BISM_OBJECTS
212 WHERE OBJECT_NAME LIKE 'qpr'||l_report_line_id||'q%';
213
214 END LOOP;
215 CLOSE Get_Report_Lines;
216
217 DELETE FROM QPR_REPORT_LINES
218 WHERE REPORT_HEADER_ID IN (
219 SELECT REPORT_HEADER_ID
220 FROM QPR_REPORT_HDRS_B
221 WHERE PLAN_ID = p_price_plan_id
222 );
223
224 DELETE FROM QPR_REPORT_RELNS
225 WHERE PARENT_REPORT_ID IN (
226 SELECT REPORT_HEADER_ID
227 FROM QPR_REPORT_HDRS_B
228 WHERE PLAN_ID = p_price_plan_id
229 );
230
231 DELETE FROM QPR_REPORT_HDRS_TL
232 WHERE REPORT_HEADER_ID IN (
233 SELECT REPORT_HEADER_ID
234 FROM QPR_REPORT_HDRS_B
235 WHERE PLAN_ID = p_price_plan_id
236 );
237 DELETE FROM QPR_REPORT_HDRS_B
238 WHERE PLAN_ID = p_price_plan_id;
239
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241 EXCEPTION
242 WHEN OTHERS
243 THEN
244 x_return_status := FND_API.G_RET_STS_ERROR;
245 END DELETE_REPORTS;
246
247 END QPR_REPORT_ENTITIES_PVT;
248
[Home] [Help]