[Home] [Help]
PACKAGE BODY: APPS.PO_WF_PO_PRICAT_UPDATE
Source
1 PACKAGE BODY PO_WF_PO_PRICAT_UPDATE AS
2 /* $Header: POXWPCTB.pls 115.4 2002/11/21 03:17:48 sbull ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
7
8 /*=======================================================================+
9 | FILENAME
10 | POXWPCAB.pls
11 |
12 | DESCRIPTION
13 | PL/SQL body for package: PO_WF_PO_PRICAT_UPDATE
14 |
15 | NOTES
16 | MODIFIED IMRAN ALI (08/25/98) - Created
17 *=====================================================================*/
18
19 --
20 -- Process_line_items
21 --
22
23 procedure process_line_items ( itemtype in varchar2,
24 itemkey in varchar2,
25 actid in number,
26 funcmode in varchar2,
27 result out NOCOPY varchar2 )
28 is
29 x_progress varchar2(100);
30 x_interface_header_id number;
31 x_batch_id number;
32 x_buyer_id NUMBER;
33 x_document_type VARCHAR2(25);
34 x_document_subtype VARCHAR2(25);
35 x_create_items VARCHAR2(1) := 'N';
36 x_create_source_rule_flag VARCHAR2(1) := 'N';
37 x_approval_status VARCHAR2(25);
38 x_rel_gen_method VARCHAR2(25);
39 x_commit_interval NUMBER := 1;
40 X_process_code varchar2(25) := 'NOTIFIED';
41 x_current_line_accept_flag varchar2(1);
42 c_price_chg_accept_flag varchar2(1);
43 c_price_break_flag varchar2(1);
44 c_interface_header_id number;
45 c_interface_line_id number;
46
47 cursor C_temp_lines_interface IS
48 SELECT interface_header_id, interface_line_id, price_chg_accept_flag, price_break_flag
49 FROM po_lines_interface
50 WHERE interface_header_id = X_interface_header_id
51 AND NVL(process_code, 'PENDING') = X_process_code
52 ORDER By interface_line_id, unit_price desc;
53
54 begin
55
56 x_progress := 'PO_WF_PO_PRICAT_UPDATE.process_line_items: 01';
57 IF (g_po_wf_debug = 'Y') THEN
58 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
59 END IF;
60
61 -- Do nothing in cancel or timeout mode
62
63 if (funcmode <> wf_engine.eng_run) then
64
65 result := wf_engine.eng_null;
66 return;
67
68 end if;
69
70 x_interface_header_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
71 itemkey => itemkey,
72 aname => 'INTERFACE_HEADER_ID');
73
74 x_batch_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
75 itemkey => itemkey,
76 aname => 'BATCH_ID');
77
78 x_buyer_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
79 itemkey => itemkey,
80 aname => 'BUYER_ID');
81
82 x_document_type := wf_engine.GetItemAttrText ( itemtype => itemtype,
83 itemkey => itemkey,
84 aname => 'DOCUMENT_TYPE_CODE');
85
86 x_document_subtype := wf_engine.GetItemAttrText ( itemtype => itemtype,
87 itemkey => itemkey,
88 aname => 'DOCUMENT_SUBTYPE');
89
90 x_rel_gen_method := wf_engine.GetItemAttrText ( itemtype => itemtype,
91 itemkey => itemkey,
92 aname => 'RELEASE_GEN_METHOD');
93
94 x_approval_status := wf_engine.GetItemAttrText ( itemtype => itemtype,
95 itemkey => itemkey,
96 aname => 'APPROVAL_STATUS');
97
98
99 IF (g_po_pdoi_write_to_file = 'Y') THEN
100 po_debug.put_line ('Procedure PO_WF_PO_PRICAT_UPDATE.Process_line_items');
101 po_debug.put_line ('INTERFACE_HEADER_ID: ' || to_char(x_interface_header_id));
102 po_debug.put_line ('BATCH_ID: ' || to_char(x_batch_id));
103 END IF;
104
105 -- Call the Purchasing Open Interface to process the records. Use the Batch ID saved in
106 -- the WF attributes when the WF was initiated and interface_header_id to call the POI so
107 -- that only those rows are processed which have been pending for this document.
108
109 --
110 -- Update the price_chg_accept_flag for price breaks;
111 --
112
113 OPEN C_temp_lines_interface;
114 LOOP
115
116 FETCH C_temp_lines_interface
117 INTO c_interface_header_id, c_interface_line_id, c_price_chg_accept_flag, c_price_break_flag;
118
119 EXIT WHEN C_temp_lines_interface%NOTFOUND;
120
121 if ( NVL(c_price_break_flag, 'N') = 'N' ) then
122
123 -- process line and get the acceptance flag.
124
125 x_current_line_accept_flag := c_price_chg_accept_flag;
126 else
127
128 -- process price break - update acceptance flag.
129
130 update po_lines_interface
131 set price_chg_accept_flag = x_current_line_accept_flag
132 where interface_header_id = c_interface_header_id
133 and interface_line_id = c_interface_line_id;
134
135 end if;
136
137 END LOOP;
138
139 CLOSE C_temp_lines_interface;
140
141 --
142 -- Call POI here
143 --
144
145 po_docs_interface_sv5.process_po_headers_interface( X_batch_id,
146 X_buyer_id,
147 X_document_type,
148 X_document_subtype,
149 X_create_items,
150 X_create_source_rule_flag,
151 X_rel_gen_method,
152 X_approval_status,
153 X_commit_interval,
154 X_process_code );
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 wf_core.context('PO_WF_PO_PRICAT_UPDATE','process_line_items',x_progress);
159 raise;
160 end process_line_items;
161
162
163 -- ************************************************************************* --
164
165 procedure were_all_items_processed ( itemtype in varchar2,
166 itemkey in varchar2,
167 actid in number,
168 funcmode in varchar2,
169 result out NOCOPY varchar2 )
170 is
171 x_interface_header_id number;
172 x_progress varchar2(100);
173 begin
174
175 x_interface_header_id := wf_engine.GetItemAttrText ( itemtype => itemtype,
176 itemkey => itemkey,
177 aname => 'INTERFACE_HEADER_ID');
178
179 result := 'COMPLETE:Y';
180
181 begin
182 select 'COMPLETE:N' into result from sys.dual
183 where exists ( select 'un_processed_items_exist'
184 from PO_LINES_INTERFACE
185 where interface_header_id = x_interface_header_id
186 and NVL(process_code, 'PENDING') = 'NOTIFIED'
187 and NVL(price_chg_accept_flag,'NULL') = 'NULL' );
188 exception
189 when no_data_found then
190 result := 'COMPLETE:Y';
191 end;
192
193 return;
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 wf_core.context('PO_WF_PO_PRICAT_UPDATE','were_all_items_processed',x_progress);
198 raise;
199 end;
200
201 -- ************************************************************************* --
202
203 procedure were_any_items_rejected ( itemtype in varchar2,
204 itemkey in varchar2,
205 actid in number,
206 funcmode in varchar2,
207 result out NOCOPY varchar2 )
208 is
209 x_interface_header_id number;
210 x_progress varchar2(100);
211 begin
212
213 x_interface_header_id := wf_engine.GetItemAttrText ( itemtype => itemtype,
214 itemkey => itemkey,
215 aname => 'INTERFACE_HEADER_ID');
216
217 result := 'COMPLETE:N';
218
219 begin
220 select 'COMPLETE:Y' into result from sys.dual
221 where exists ( select 'items_were_rejected'
222 from PO_LINES_INTERFACE
223 where interface_header_id = x_interface_header_id
224 and NVL(process_code, 'PENDING') = 'NOTIFIED'
225 and NVL(price_chg_accept_flag,'NULL') = 'N');
226 exception
227 when no_data_found then
228 result := 'COMPLETE:N';
229 end;
230
231 return;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 wf_core.context('PO_WF_PO_PRICAT_UPDATE','were_any_items_rejected',x_progress);
236 raise;
237 end;
238
239 -- ************************************************************************* --
240
241 procedure cancel_buyer_notif ( itemtype in varchar2,
242 itemkey in varchar2,
243 actid in number,
244 funcmode in varchar2,
245 result out NOCOPY varchar2 )
246 is
247 x_progress varchar2(10) := '001';
248 x_nid number := NULL;
249 begin
250
251 -- Find all outstanding notifications to the buyer for this document and
252 -- cancel them.
253
254 -- As we loop back in the WF there can be only one outstanding notification.
255
256 begin
257 select notification_id into x_nid
258 from wf_item_activity_statuses_v
259 where item_type = itemtype
260 AND item_key = itemkey
261 AND ACTIVITY_NAME = 'BUYER_NOTIFICATION';
262
263 exception
264 when others then
265 null;
266 end;
267
268 x_progress := '002';
269
270 if x_nid is not null then
271
272 -- Need to trap the exception as the notification may not be open.
273 begin
274 wf_notification.cancel (x_nid, NULL);
275 exception
276 when others then
277 null;
278 end;
279 end if;
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 wf_core.context('PO_WF_PO_PRICAT_UPDATE','cancel_buyer_notif',x_progress);
284 raise;
285 end cancel_buyer_notif;
286
287 END PO_WF_PO_PRICAT_UPDATE;