1 PACKAGE BODY PON_PO_INTEGRATION_GRP AS
2 /* $Header: PONGPOIB.pls 120.2 2006/03/24 09:38:32 smhanda noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PON_PO_INTEGRATION_GRP';
5
6 -----------------------------------------------------------------------
7 --Start of Comments
8 --Name: validate_po_purge
9 --Pre-reqs:
10 --Modifies:
11 --Locks:
12 -- None
13 --Function:
14 -- Given the header ids of PO documents, determine whether they are allowed
15 -- to be purged from PON's perspective
16 --Parameters:
17 --IN:
18 --p_api_version
19 -- Version of the api the caller is assuming
20 --p_init_msg_list
21 -- FND_API.G_TRUE: initialize the message list
22 -- FND_API.G_FALSE: do not initialize the message list
23 --p_commit
24 -- FND_API.G_TRUE: procedure should commit
25 -- FND_API.G_FALSE: procedure should not commit
26 --p_in_rec
27 -- A structure that holds PO information
28 -- p_in_rec.entity_name will expect 'PO_HEADERS', while p_in_rec.entity_ids
29 -- will be a table of all document header ids that PO are about to be purged
30 --IN OUT:
31 --OUT:
32 --x_return_status
33 -- status of the procedure
34 --x_msg_count
35 -- Number of messages in message stack
36 --x_msg_data
37 -- If there is only 1 message in message stack, this out variable should
38 -- be populated with that message
39 --x_out_rec
40 -- A structure indicating whether PO documents can be purged or not
41 -- For each entry in p_in_rec.entity_ids, the corresponding entry in
42 -- x_out_rec.purge_allowed will indicate whether the document is purgable
43 -- or not. e.g., If x_out_rec.purge_allowed(i) is 'Y', it means that
44 -- p_in_rec.entity_ids(i) can be purged. If x_out_rec.purge_allowed(i) is 'N',
45 -- the document specified in p_in_rec.entity_ids(i) will not be purged.
46 -- The number of records in x_out_rec.purge_allowed should always be the
47 -- same as that for p_in_rec.entity_ids
48 --Returns:
49 --Notes:
50 --Testing:
51 --End of Comments
52 ------------------------------------------------------------------------
53
54 PROCEDURE validate_po_purge
55 ( p_api_version IN NUMBER,
56 p_init_msg_list IN VARCHAR2,
57 p_commit IN VARCHAR2,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2,
61 p_in_rec IN PURGE_IN_RECTYPE,
62 x_out_rec OUT NOCOPY PURGE_OUT_RECTYPE
63 ) IS
64
65 l_api_version NUMBER := 1.0;
66 l_api_name VARCHAR2(50) := 'validate_po_purge';
67 l_progress NUMBER;
68 l_idx NUMBER;
69 l_references NUMBER;
70
71 BEGIN
72 -- initialize return for unexpected error
73 x_return_status := fnd_api.g_ret_sts_unexp_error;
74
75 l_progress := 100;
76
77 -- Standard call to check for call compatibility.
78 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
79 l_api_name, l_api_name) THEN
80 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81 END IF;
82
83 l_progress := 150;
84
85 -- Initialize message list if p_init_msg_list is set to TRUE.
86 IF FND_API.to_Boolean(p_init_msg_list) THEN
87 FND_MSG_PUB.initialize();
88 END IF;
89
90 l_progress := 200;
91
92 if (p_in_rec.entity_name = 'PO_HEADERS') then
93 for l_idx in 1..p_in_rec.entity_ids.count loop
94 begin
95 select 1
96 into l_references
97 from dual
98 where exists (select 1
99 from pon_bid_headers
100 where po_header_id = p_in_rec.entity_ids(l_idx)
101 );
102 exception when no_data_found then
103 l_references := 0;
104 end;
105 l_progress := 300;
106
107 if (l_references = 0) then
108 begin
109 select 1
110 into l_references
111 from dual
112 where exists ( select 1
113 from pon_auction_headers_all
114 where source_doc_msg in ('PO_POTYPE_BLKT', 'PO_POTYPE_CNTR')
115 and source_doc_id = p_in_rec.entity_ids(l_idx)
116 );
117 exception when no_data_found then
118 l_references := 0;
119 end;
120
121 l_progress := 400;
122
123 if (l_references = 0) then
124 x_out_rec.purge_allowed(l_idx) := 'Y';
125 else
126 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
127 fnd_log.string(fnd_log.level_statement,
128 'pon.pon_po_integration_grp',
129 'po_header ' || p_in_rec.entity_ids(l_idx) || ' failed in pon_auction_headers_all');
130 end if;
131
132 x_out_rec.purge_allowed(l_idx) := 'N';
133 end if;
134 else
135 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
136 fnd_log.string(fnd_log.level_statement,
137 'pon.pon_po_integration_grp',
138 'po_header ' || p_in_rec.entity_ids(l_idx) || ' failed in pon_bid_headers');
139 end if;
140
141 x_out_rec.purge_allowed(l_idx) := 'N';
142 end if;
143 end loop;
144 else -- p_in_rec.entity_name = 'PO_REQUISITION_HEADERS'
145 for l_idx in 1..p_in_rec.entity_ids.count loop
146 begin
147 select 1
148 into l_references
149 from dual
150 where exists ( select 1
151 from pon_backing_requisitions
152 where requisition_header_id = p_in_rec.entity_ids(l_idx)
153 );
154 exception when no_data_found then
155 l_references := 0;
156 end;
157
158 l_progress := 350;
159
160 if (l_references = 0) then
161 x_out_rec.purge_allowed(l_idx) := 'Y';
162 else
163 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
164 fnd_log.string(fnd_log.level_statement,
165 'pon.pon_po_integration_grp',
166 'po_requisition_headers ' || p_in_rec.entity_ids(l_idx) || ' pon_backing_requisitions');
167 end if;
168
169 x_out_rec.purge_allowed(l_idx) := 'N';
170 end if;
171 end loop;
172 end if; -- entity_name
173
174 l_progress := 500;
175
176 if (p_commit = fnd_api.g_true) then
177 commit;
178 end if;
179
180 x_return_status := fnd_api.g_ret_sts_success;
181 x_msg_count := 0;
182 x_msg_data := null;
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
187 FND_MSG_PUB.add_exc_msg
188 ( p_pkg_name => g_pkg_name,
189 p_procedure_name => l_api_name || '.' || l_progress
190 );
191
192 FND_MSG_PUB.count_and_get
193 ( p_encoded => 'F',
194 p_count => x_msg_count,
195 p_data => x_msg_data
196 );
197
198 END validate_po_purge;
199
200 -----------------------------------------------------------------------
201 --Start of Comments
202 --Name: po_purge
203 --Pre-reqs:
204 --Modifies:
205 --Locks:
206 -- None
207 --Function:
208 -- Perform necessary actions for PON records when PO documents are purged
209 --Parameters:
210 --IN:
211 --p_api_version
212 -- Version of the api the caller is assuming
213 --p_init_msg_list
214 -- FND_API.G_TRUE: initialize the message list
215 -- FND_API.G_FALSE: do not initialize the message list
216 --p_commit
217 -- FND_API.G_TRUE: procedure should commit
218 -- FND_API.G_FALSE: procedure should not commit
219 --p_in_rec
220 -- A structure that holds PO information
221 -- p_in_rec.entity_name will expect 'PO_HEADERS', while p_in_rec.entity_ids
222 -- will be a table of all document header ids that PO are about to be purged
223 --IN OUT:
224 --OUT:
225 --x_return_status
226 -- status of the procedure
227 --x_msg_count
228 -- Number of messages in message stack
229 --x_msg_data
230 -- If there is only 1 message in message stack, this out variable should
231 -- be populated with that message
232 --Returns:
233 --Notes:
234 --Testing:
235 --End of Comments
236 ------------------------------------------------------------------------
237
238 PROCEDURE po_purge
239 ( p_api_version IN NUMBER,
240 p_init_msg_list IN VARCHAR2,
241 p_commit IN VARCHAR2,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_msg_count OUT NOCOPY NUMBER,
244 x_msg_data OUT NOCOPY VARCHAR2,
245 p_in_rec IN PURGE_IN_RECTYPE
246 ) IS
247
248 l_api_version NUMBER := 1.0;
249 l_api_name VARCHAR2(50) := 'po_purge';
250 l_progress NUMBER;
251
252 BEGIN
253 -- initialize return for unexpected error
254 x_return_status := fnd_api.g_ret_sts_unexp_error;
255
256 l_progress := 100;
257
258 -- Standard call to check for call compatibility.
259 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
260 l_api_name, l_api_name) THEN
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 END IF;
263
264 l_progress := 150;
265
266 -- Initialize message list if p_init_msg_list is set to TRUE.
267 IF FND_API.to_Boolean(p_init_msg_list) THEN
268 FND_MSG_PUB.initialize();
269 END IF;
270
271 l_progress := 200;
272
273 if (p_commit = fnd_api.g_true) then
274 commit;
275 end if;
276
277 x_return_status := FND_API.G_RET_STS_SUCCESS;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282 FND_MSG_PUB.add_exc_msg
283 ( p_pkg_name => g_pkg_name,
284 p_procedure_name => l_api_name || '.' || l_progress
285 );
286
287 FND_MSG_PUB.count_and_get
288 ( p_encoded => 'F',
289 p_count => x_msg_count,
290 p_data => x_msg_data
291 );
292
293 END po_purge;
294
295 END PON_PO_INTEGRATION_GRP;