1 PACKAGE BODY OE_INVOICE_WF AS
2 /* $Header: OEXWINVB.pls 120.7 2008/03/10 09:34:51 amallik ship $ */
3
4 -- PROCEDURE XX_ACTIVITY_NAME
5 --
6 -- <describe the activity here>
7 --
8 -- IN
9 -- itemtype - type of the current item
10 -- itemkey - key of the current item
11 -- actid - process activity instance id
12 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
13 -- OUT
14 -- result
15 -- - COMPLETE[:<result>]
16 -- activity has completed with the indicated result
17 -- - WAITING
18 -- activity is waiting for additional transitions
19 -- - DEFERED
20 -- execution should be defered to background
21 -- - NOTIFIED[:<notification_id>:<assigned_user>]
22 -- activity has notified an external entity that this
23 -- step must be performed. A call to wf_engine.CompleteActivty
24 -- will signal when this step is complete. Optional
25 -- return of notification ID and assigned user.
26 -- - ERROR[:<error_code>]
27 -- function encountered an error.
28 g_defer varchar2(2000) := FND_PROFILE.value('ONT_DEFER_INV_MIN'); -- 4343423
29 g_defer_min NUMBER := 0;
30
31 PROCEDURE Invoice_Interface
32 ( itemtype IN VARCHAR2
33 , itemkey IN VARCHAR2
34 , actid IN NUMBER
35 , funcmode IN VARCHAR2
36 , resultout IN OUT NOCOPY VARCHAR2
37 ) IS
38 l_result_out VARCHAR2(30);
39 l_return_status VARCHAR2(30);
40 l_line_id NUMBER;
41 l_header_id NUMBER;
42 l_count NUMBER;
43 --
44 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
45 --
46
47 l_order_number number; -- Added for bug 6704643
48
49 BEGIN
50
51 --
52 -- RUN mode - normal process execution
53 --
54 IF (funcmode = 'RUN') THEN
55
56 OE_STANDARD_WF.Set_Msg_Context(actid);
57
58 IF itemtype = OE_GLOBALS.G_WFI_LIN THEN
59 l_line_id := to_number(itemkey);
60 ELSIF itemtype = OE_GLOBALS.G_WFI_HDR THEN
61 l_header_id := to_number(itemkey);
62 END IF;
63
64 IF itemtype = OE_GLOBALS.G_WFI_LIN THEN
65
66 -- Added below code for bug 6704643
67 select t1.order_number
68 into l_order_number
69 from oe_order_lines_all t2,
70 oe_order_headers_all t1
71 where t2.line_id = l_line_id
72 and t1.header_id = t2.header_id;
73
74 IF l_debug_level > 0 THEN
75 oe_debug_pub.add(' Sales Order : ' || l_order_number, 1);
76 END IF;
77 -- End of bug 6704643
78
79
80 --bug 6065302
81 SELECT count(1) into l_count
82 from RA_INTERFACE_LINES_ALL
83 where line_type = 'LINE'
84 and interface_line_context = 'ORDER ENTRY'
85 and interface_line_attribute6 = to_char(l_line_id)
86 and sales_order = to_char(l_order_number) -- Added for bug 6704643, Bug 6862908
87 and sales_order_line IS NOT NULL; -- Added for bug 6704643
88
89 IF( l_count = 0) THEN
90 SELECT count(1) into l_count from RA_CUSTOMER_TRX_LINES_ALL RCTL
91 where rctl.interface_line_context = 'ORDER ENTRY'
92 and rctl.line_type = 'LINE'
93 and RCTL.interface_line_attribute6 = to_char(l_line_id)
94 and rctl.sales_order = to_char(l_order_number) -- Added for bug 6704643, Bug 6862908
95 and rctl.sales_order_line is not null; -- Added for bug 6704643
96
97 END IF;
98
99 IF( l_count <> 0) THEN
100 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_COMPLETE ;
101 IF l_debug_level > 0 THEN
102 oe_debug_pub.add( 'Line is Invoiced, NOT invoicing any more ' ) ;
103 END IF;
104 OE_STANDARD_WF.Clear_Msg_Context;
105 RETURN;
106 END IF;
107 --END Bug 6065302
108 OE_Invoice_PUB.Interface_Line( l_line_id
109 , itemtype
110 , l_result_out
111 , l_return_status);
112 ELSIF itemtype = OE_GLOBALS.G_WFI_HDR THEN
113
114 -- Added below code for bug 6704643
115 select t1.order_number
116 into l_order_number
117 from oe_order_headers_all t1
118 where t1.header_id = l_header_id;
119
120 IF l_debug_level > 0 THEN
121 oe_debug_pub.add(' Sales Order : ' || l_order_number, 1);
122 END IF;
123 -- End of bug 6704643
124
125
126 -- bug 6065302
127 SELECT count(1) into l_count
128 from RA_INTERFACE_LINES_ALL
129 where line_type = 'LINE'
130 and interface_line_context = 'ORDER ENTRY'
131 and interface_line_attribute6 IN (select line_id from oe_order_lines_all where header_id=l_header_id)
132 and sales_order = to_char(l_order_number) -- Added for bug 6704643, Bug 6862908
133 and sales_order_line IS NOT NULL; -- Added for bug 6704643
134
135 IF( l_count = 0) THEN
136 SELECT count(1) into l_count from RA_CUSTOMER_TRX_LINES_ALL
137 where interface_line_context = 'ORDER ENTRY'
138 and line_type = 'LINE'
139 and interface_line_attribute6 IN (select line_id from oe_order_lines_all
140 where header_id=l_header_id)
141 and sales_order = to_char(l_order_number) -- Added for bug 6704643, Bug 6862908
142 and sales_order_line IS NOT NULL; -- Added for bug 6704643 ;
143 END IF;
144
145 IF( l_count <> 0) THEN
146 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_COMPLETE ;
147 IF l_debug_level > 0 THEN
148 oe_debug_pub.add( 'Lines in Header Invoiced, NOT invoicing any more ' ) ;
149 END IF;
150 OE_STANDARD_WF.Clear_Msg_Context;
151 RETURN;
152 END IF;
153 --END Bug 6065302
154 OE_Invoice_PUB.Interface_Header( l_header_id
155 , itemtype
156 , l_result_out
157 , l_return_status);
158 END IF;
159 IF l_debug_level > 0 THEN
160 oe_debug_pub.add( 'INFO-L_RETURN_STATUS: '||L_RETURN_STATUS ) ;
161 oe_debug_pub.add( 'INFO-L_RESULT_OUT: '||L_RESULT_OUT ) ;
162 END IF;
163 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
164 IF l_result_out = OE_GLOBALS.G_WFR_NOT_ELIGIBLE THEN
165 resultout := OE_GLOBALS.G_WFR_COMPLETE ||':' || OE_GLOBALS.G_WFR_NOT_ELIGIBLE ;
166 OE_STANDARD_WF.Clear_Msg_Context;
167 RETURN;
168 ELSIF l_result_out = OE_GLOBALS.G_WFR_COMPLETE THEN
169 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_COMPLETE ;
170 OE_STANDARD_WF.Clear_Msg_Context;
171 RETURN;
172 ELSIF l_result_out = OE_GLOBALS.G_WFR_PRTL_COMPLETE THEN
173 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_PRTL_COMPLETE;
174 OE_STANDARD_WF.Clear_Msg_Context;
175 RETURN;
176 ELSIF l_result_out = OE_GLOBALS.G_WFR_PENDING_ACCEPTANCE THEN
177 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_PENDING_ACCEPTANCE;
178 OE_STANDARD_WF.Clear_Msg_Context;
179 RETURN;
180 END IF;
181 ELSIF l_return_status = 'DEFERRED' THEN
182 OE_STANDARD_WF.Clear_Msg_Context;
183 IF g_defer IS NOT NULL THEN -- 4343423
184 BEGIN
185 IF TO_NUMBER(g_defer) >= 0 THEN
186 resultout := 'DEFERRED:'||to_char(sysdate+(TO_NUMBER(g_defer)/1440),wf_engine.date_format);
187 ELSE
188 g_defer := NULL;
189 END IF;
190 EXCEPTION
191 WHEN OTHERS THEN
192 g_defer := NULL;
193 END;
194 END IF;
195
196 IF g_defer IS NULL THEN
197 resultout := 'DEFERRED:'||to_char(sysdate+((1+g_defer_min)/1440), wf_engine.date_format);
198 g_defer_min := mod(g_defer_min + 0.5, 4.5);
199 END IF;
200
201 IF l_debug_level > 0 THEN
202 oe_debug_pub.add(resultout, 5);
203 END IF;
204
205 RETURN;
206 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
207 -- start data fix project
208 -- UPDATE OE_ORDER_LINES_ALL /* Bug #3427029 */
209 -- SET INVOICE_INTERFACE_STATUS_CODE = 'INVOICE-UNEXPECTED-ERROR',
210 -- FLOW_STATUS_CODE = 'INVOICE_UNEXPECTED_ERROR',
211 -- CALCULATE_PRICE_FLAG = 'N',
212 -- LOCK_CONTROL = LOCK_CONTROL + 1
213 -- WHERE LINE_ID = l_line_id;
214 --OE_STANDARD_WF.Save_Messages(p_instance_id => actid);
215 --OE_STANDARD_WF.Clear_Msg_Context;
216 -- commit; -- messages were not saved without this
217 -- end data fix project
218 IF l_debug_level > 0 THEN
219 oe_debug_pub.add('OEXWINVB.pls - in unexpected error raise exception');
220 END IF;
221 --resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_INCOMPLETE;
222 --return;
223 app_exception.raise_exception;
224 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
225 -- For HOLDs and Validation errors
226 IF l_result_out = OE_GLOBALS.G_WFR_ON_HOLD THEN
227 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_ON_HOLD;
228 OE_STANDARD_WF.Save_Messages(p_instance_id => actid);
229 OE_STANDARD_WF.Clear_Msg_Context;
230 RETURN;
231 ELSIF l_result_out = OE_GLOBALS.G_WFR_INCOMPLETE THEN
232 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_INCOMPLETE;
233 OE_STANDARD_WF.Save_Messages(p_instance_id => actid);
234 OE_STANDARD_WF.Clear_Msg_Context;
235 RETURN;
236 END IF;
237 END IF;
238
239 END IF;
240
241
242 --
243 -- CANCEL mode - activity 'compensation'
244 --
245 -- This is an event point is called with the effect of the activity must
246 -- be undone, for example when a process is reset to an earlier point
247 -- due to a loop back.
248 --
249 IF (funcmode = 'CANCEL') THEN
250
251 -- your cancel code goes here
252 null;
253
254 -- no result needed
255 resultout := 'COMPLETE';
256 return;
257 END IF;
258
259
260 --
261 -- Other execution modes may be created in the future. Your
262 -- activity will indicate that it does not implement a mode
263 -- by returning null
264 --
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 -- The line below records this function call in the error system
269 -- in the case of an exception.
270 wf_core.context('OE_Invoice_WF', 'Invoice_Interface',
271 itemtype, itemkey, to_char(actid), funcmode);
272 -- start data fix project
273 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
274 p_itemtype => itemtype,
275 p_itemkey => itemkey);
276 OE_STANDARD_WF.Save_Messages(p_instance_id => actid);
277 OE_STANDARD_WF.Clear_Msg_Context;
278 -- end data fix project
279 RAISE;
280
281 END Invoice_Interface;
282
283 END OE_INVOICE_WF;
[Home] [Help]