1 PACKAGE BODY CLN_3A9_CANCELPO_PKG AS
2 /* $Header: CLN3A9PB.pls 115.6 2003/06/27 16:16:00 kkram noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4
5 /*
6 -- Name
7 -- RAISE_CANCEL_PO_EVENT
8 -- Purpose
9 -- Raise oracle.apps.cln.po.cancelpo event
10 -- Arguments
11 -- PO Header ID
12 -- PO Header Type
13 -- PO Header Sub Type
14 -- Notes
15 -- No specific notes
16
17
18 PROCEDURE RAISE_CANCEL_PO_EVENT(
19 p_document_id IN VARCHAR2,
20 p_hdr_type IN VARCHAR2,
21 p_hdr_sub_type IN VARCHAR2)
22 IS
23 l_cln_not_parameters wf_parameter_list_t;
24 l_authorization_status VARCHAR2(50);
25 l_cln_event_key NUMBER;
26 l_error_code NUMBER;
27 l_debug_mode VARCHAR2(255);
28 l_error_msg VARCHAR2(1000);
29 l_not_msg VARCHAR2(1000);
30 BEGIN
31 -- Sets the debug mode to FILE
32 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
33
34 cln_debug_pub.Add('CLN_3A9_CANCELPO_PKG.RAISE_CLN_EVENT CALLED', 2);
35
36 -- Raise event if it is a Standard PO or Release, else return
37 -- If the PO/Release is not approved then return
38 l_cln_not_parameters := wf_parameter_list_t();
39 IF (p_hdr_type = 'PO' AND p_hdr_sub_type = 'STANDARD') THEN -- Standard PO
40 SELECT authorization_status
41 INTO l_authorization_status
42 FROM po_headers_all
43 WHERE po_header_id = p_document_id;
44 IF l_authorization_status <> 'APPROVED' THEN
45 RETURN;
46 END IF;
47 WF_EVENT.AddParameterToList('POHEADERID', p_document_id, l_cln_not_parameters);
48 WF_EVENT.AddParameterToList('PORELEASEID', -1, l_cln_not_parameters);
49 ELSIF (p_hdr_type = 'RELEASE' AND (p_hdr_sub_type = 'BLANKET'
50 OR p_hdr_sub_type = 'SCHEDULED')) THEN -- Release
51 SELECT authorization_status
52 INTO l_authorization_status
53 FROM po_releases_all
54 WHERE po_release_id = p_document_id;
55 IF l_authorization_status <> 'APPROVED' THEN
56 RETURN;
57 END IF;
58 WF_EVENT.AddParameterToList('POHEADERID', -1, l_cln_not_parameters);
59 WF_EVENT.AddParameterToList('PORELEASEID', p_document_id, l_cln_not_parameters);
60 ELSE
61 RETURN;
62 END IF;
63
64 SELECT cln_generic_s.nextval INTO l_cln_event_key FROM dual;
65
66 -- Set Event Name, Event Key and Event Message Parameters.
67 WF_EVENT.AddParameterToList('oracle.apps.cln.po.cancelpo', 'EVENT_NAME', l_cln_not_parameters);
68 WF_EVENT.AddParameterToList(l_cln_event_key, 'EVNT_KEY', l_cln_not_parameters);
69 WF_EVENT.AddParameterToList('Cancel PO', 'ECX_EVENT_MESSAGE', l_cln_not_parameters);
70
71 -- Raise Cancel PO event
72 WF_EVENT.Raise('oracle.apps.cln.po.cancelpo', 'clncpo-' || l_cln_event_key, NULL, l_cln_not_parameters, NULL);
73
74 cln_debug_pub.Add('CLN_3A9_CANCELPO_PKG.RAISE_CLN_EVENT EXITED', 2);
75 EXCEPTION
76 WHEN OTHERS THEN
77 l_error_code := SQLCODE;
78 l_error_msg := SQLERRM;
79 insert into cln_test(test) values('Exception ' || ':' || l_error_code || ':' || l_error_msg);
80 FND_MESSAGE.SET_NAME('CLN','CLN_3A9_EVENT_RAISE_ERROR');
81 FND_MESSAGE.SET_TOKEN('POHEADERID', p_document_id);
82 FND_MESSAGE.SET_TOKEN('DBERRMSG', l_error_code || ':' || l_error_msg);
83 l_not_msg := FND_MESSAGE.GET;
84 cln_debug_pub.Add(l_not_msg, 6);
85 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_not_msg);
86 cln_debug_pub.Add('RAISE_CLN_EVENT EXITED', 2);
87 END RAISE_CANCEL_PO_EVENT;
88 */
89
90
91
92 -- Name
93 -- SETATTRIBUTES
94 -- Purpose
95 -- Based on the parameters passed, query PO base tables and populate item attribute values
96 -- Arguments
97 -- PO Header ID available as Item Attribute
98 -- Notes
99 -- No specific notes
100
101
102 PROCEDURE SETATTRIBUTES(
103 p_itemtype IN VARCHAR2,
104 p_itemkey IN VARCHAR2,
105 p_actid IN NUMBER,
106 p_funcmode IN VARCHAR2,
107 x_resultout IN OUT NOCOPY VARCHAR2)
108 IS
109 l_po_number VARCHAR2(20);
110 l_rel_number NUMBER;
111 l_rev_number NUMBER;
112 l_supp_ord_number VARCHAR2(25);
113 l_party_id VARCHAR2(30);
114 l_party_site_id VARCHAR2(40);
115 l_po_header_id NUMBER;
116 l_po_release_id NUMBER;
117 l_xmlg_doc_id NUMBER;
118 l_cln_event_key NUMBER;
119 l_debug_mode VARCHAR2(255);
120 l_error_code NUMBER;
121 l_error_msg VARCHAR2(1000);
122 l_not_msg VARCHAR2(1000);
123 BEGIN
124 -- Sets the debug mode to FILE
125 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
126
127 IF (l_Debug_Level <= 2) THEN
128 cln_debug_pub.Add('CLN_3A9_CANCELPO_PKG.SETATTRIBUTES CALLED', 2);
129 END IF;
130
131 l_po_header_id := TO_NUMBER(wf_engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'POHEADERID'));
132 l_po_release_id := TO_NUMBER(wf_engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'PORELEASEID'));
133
134 IF (l_Debug_Level <= 1) THEN
135 cln_debug_pub.Add('l_po_header_id:' || l_po_header_id, 1);
136 cln_debug_pub.Add('l_po_release_id:' || l_po_release_id, 1);
137 END IF;
138
139 -- Query PO base tables/views to get PO Number, release number, revision number,
140 -- and Sales Order Number based on PO Header ID or Po Release ID
141 IF l_po_release_id = -1 THEN
142 SELECT poh.segment1, por.release_num, poh.revision_num, poh.vendor_order_num
143 INTO l_po_number, l_rel_number, l_rev_number, l_supp_ord_number
144 FROM po_headers_all poh, po_releases_all por
145 WHERE poh.po_header_id = l_po_header_id
146 AND poh.po_header_id = por.po_header_id(+);
147 ELSE
148 SELECT poh.po_header_id, poh.segment1, por.release_num, por.revision_num, poh.vendor_order_num
149 INTO l_po_header_id, l_po_number, l_rel_number, l_rev_number, l_supp_ord_number
150 FROM po_headers_all poh, po_releases_all por
151 WHERE por.po_release_id = l_po_release_id
152 AND poh.po_header_id = por.po_header_id(+);
153 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'POHEADERID', l_po_header_id);
154 END IF;
155
156 IF (l_Debug_Level <= 1) THEN
157 cln_debug_pub.Add('l_po_header_id:' || l_po_header_id, 1);
158 cln_debug_pub.Add('l_po_release_id:' || l_po_release_id, 1);
159 cln_debug_pub.Add('l_po_number:' || l_po_number, 1);
160 cln_debug_pub.Add('l_rel_number:' || l_rel_number, 1);
161 cln_debug_pub.Add('l_rev_number:' || l_rev_number, 1);
162 cln_debug_pub.Add('l_supp_ord_number:' || l_supp_ord_number, 1);
163 END IF;
164
165 /* -- This is not required since it is taken care in XMG query
166 -- Upon PO cancellation, PO revision number is incremented by 1 in the PO Header table
167 -- but not in the PO Header Archive table
168 -- which is used in the XML Gateway Map to generate the Cancel PO XML document
169 IF l_po_rev_number > 0 THEN
170 l_po_rev_number := l_po_rev_number - 1;
171 END IF;
172 */
173
174
175 -- Set the PO details
176 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PONUMBER', l_po_number);
177 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PORELEASENO', l_rel_number);
178 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'POREVISIONNUMBER', l_rev_number);
179 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'SUPPLIERORDERNUM', l_supp_ord_number);
180
181 -- Query PO base tables/views to get Party ID and Party Site ID
182 -- based on PO Header ID
183 SELECT VENDOR_ID, VENDOR_SITE_ID
184 INTO l_party_id, l_party_site_id
185 FROM PO_HEADERS_ALL
186 WHERE PO_HEADER_ID = l_po_header_id;
187
188 IF (l_Debug_Level <= 1) THEN
189 cln_debug_pub.Add('l_party_id:' || l_party_id, 1);
190 cln_debug_pub.Add('l_party_site_id:' || l_party_site_id, 1);
191 END IF;
192
193 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARTYID', l_party_id);
194 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARTYSIDEID', l_party_site_id);
195
196 -- Set the event key
197 SELECT cln_generic_s.nextval INTO l_cln_event_key FROM dual;
198 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'CLNEVENTKEY', l_cln_event_key);
199
200 -- Set the XML Gateway Document ID
201 SELECT cln_generic_s.nextval INTO l_xmlg_doc_id FROM dual;
202 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'XMLGDOCUMENTID', l_xmlg_doc_id);
203
204 -- Set Transaction Type and Subtype
205 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'TRANSACTIONTYPE', 'CLN');
206 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'TRANSACTIONSUBTYPE', 'CANCELPO');
207
208 x_resultout:='Yes';
209 IF (l_Debug_Level <= 2) THEN
210 cln_debug_pub.Add('CLN_3A9_CANCELPO_PKG.SETATTRIBUTES EXITED', 2);
211 END IF;
212 EXCEPTION
213 WHEN OTHERS THEN
214 l_error_code := SQLCODE;
215 l_error_msg := SQLERRM;
216 FND_MESSAGE.SET_NAME('CLN','CLN_3A9_PO_QUERY_ERROR');
217 FND_MESSAGE.SET_TOKEN('POHEADERID', l_po_header_id);
218 FND_MESSAGE.SET_TOKEN('DBERRMSG', l_error_code || ':' || l_error_msg);
219 l_not_msg := FND_MESSAGE.GET;
220 IF (l_Debug_Level <= 6) THEN
221 cln_debug_pub.Add(l_not_msg, 6);
222 END IF;
223 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_not_msg);
224 END SETATTRIBUTES;
225
226
227 -- Name
228 -- IS_XML_CHOSEN
229 -- Purpose
230 -- Checks if XML transaction is set/enabled for this PO
231 -- Arguments
232 -- PO Header ID available as Item Attribute
233 -- PO Type available as Item Attribute
234 -- Notes
235 -- No specific notes
236
237 PROCEDURE IS_XML_CHOSEN(
238 p_itemtype IN VARCHAR2,
239 p_itemkey IN VARCHAR2,
240 p_actid IN NUMBER,
241 p_funcmode IN VARCHAR2,
242 x_resultout OUT NOCOPY VARCHAR2)
243 IS
244 l_po_header_id NUMBER;
245 l_po_release_id NUMBER;
246 l_xml_flag VARCHAR2(1);
247 l_error_code NUMBER;
248 l_error_msg VARCHAR2(1000);
249 l_not_msg VARCHAR2(1000);
250 BEGIN
251 IF (l_Debug_Level <= 2) THEN
252 cln_debug_pub.Add('CLN_3A9_CANCELPO_PKG.IS_XML_CHOSEN CALLED', 2);
253 END IF;
254
255 x_resultout := 'COMPLETE:F';
256
257 l_po_header_id := TO_NUMBER(wf_engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'POHEADERID'));
258 l_po_release_id := TO_NUMBER(wf_engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'PORELEASEID'));
259
260 -- Query for XML flag based on PO Header ID or PO Release ID
261 IF l_po_release_id = -1 THEN
262 SELECT poh.xml_flag
263 INTO l_xml_flag
264 FROM po_headers_all poh
265 WHERE po_header_id= l_po_header_id;
266 ELSE
267 SELECT por.xml_flag
268 INTO l_xml_flag
269 FROM po_headers_all poh, po_releases_all por
270 WHERE poh.po_header_id = por.po_header_id
271 AND por.po_release_id = l_po_release_id;
272 END IF;
273
274 IF l_xml_flag = 'Y' THEN
275 x_resultout := 'COMPLETE:T';
276 END IF;
277
278 IF (l_Debug_Level <= 1) THEN
279 cln_debug_pub.Add('l_xml_flag:' || l_xml_flag, 1);
280 END IF;
281 IF (l_Debug_Level <= 2) THEN
282 cln_debug_pub.Add('CLN_3A9_CANCELPO_PKG.IS_XML_CHOSEN EXITED', 2);
283 END IF;
284 EXCEPTION
285 WHEN OTHERS THEN
286 x_resultout := 'COMPLETE:F';
287 l_error_code := SQLCODE;
288 l_error_msg := SQLERRM;
289 FND_MESSAGE.SET_NAME('CLN','CLN_3A9_PO_QUERY_ERROR');
290 FND_MESSAGE.SET_TOKEN('POHEADERID', l_po_header_id);
291 FND_MESSAGE.SET_TOKEN('DBERRMSG', l_error_code || ':' || l_error_msg);
292 l_not_msg := FND_MESSAGE.GET;
293 IF (l_Debug_Level <= 6) THEN
294 cln_debug_pub.Add(l_not_msg, 6);
295 END IF;
296 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_not_msg);
297 NULL;
298 END IS_XML_CHOSEN;
299
300
301 END CLN_3A9_CANCELPO_PKG;
[Home] [Help]