1 PACKAGE BODY OE_INVOICE_WF AS
2 /* $Header: OEXWINVB.pls 120.8.12020000.2 2013/01/07 13:53:20 sujithku 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 to_char(line_id) from oe_order_lines_all where header_id=l_header_id)--to_char() added for bug 10377384
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 to_char(line_id) from oe_order_lines_all -- to_char() added for bug#10377384
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 --sol_ord_er #16014165
181 ELSIF l_result_out = OE_GLOBALS.G_CONTRACTS_BILLED THEN
182 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_CONTRACTS_BILLED;
183 OE_STANDARD_WF.Clear_Msg_Context;
184 RETURN;
185 --sol_ord_er #16014165 end
186 END IF;
187 ELSIF l_return_status = 'DEFERRED' THEN
188 OE_STANDARD_WF.Clear_Msg_Context;
189 IF g_defer IS NOT NULL THEN -- 4343423
190 BEGIN
191 IF TO_NUMBER(g_defer) >= 0 THEN
192 resultout := 'DEFERRED:'||to_char(sysdate+(TO_NUMBER(g_defer)/1440),wf_engine.date_format);
193 ELSE
194 g_defer := NULL;
195 END IF;
196 EXCEPTION
197 WHEN OTHERS THEN
198 g_defer := NULL;
199 END;
200 END IF;
201
202 IF g_defer IS NULL THEN
203 resultout := 'DEFERRED:'||to_char(sysdate+((1+g_defer_min)/1440), wf_engine.date_format);
204 g_defer_min := mod(g_defer_min + 0.5, 4.5);
205 END IF;
206
207 IF l_debug_level > 0 THEN
208 oe_debug_pub.add(resultout, 5);
209 END IF;
210
211 RETURN;
212 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
213 -- start data fix project
214 -- UPDATE OE_ORDER_LINES_ALL /* Bug #3427029 */
215 -- SET INVOICE_INTERFACE_STATUS_CODE = 'INVOICE-UNEXPECTED-ERROR',
216 -- FLOW_STATUS_CODE = 'INVOICE_UNEXPECTED_ERROR',
217 -- CALCULATE_PRICE_FLAG = 'N',
218 -- LOCK_CONTROL = LOCK_CONTROL + 1
219 -- WHERE LINE_ID = l_line_id;
220 --OE_STANDARD_WF.Save_Messages(p_instance_id => actid);
221 --OE_STANDARD_WF.Clear_Msg_Context;
222 -- commit; -- messages were not saved without this
223 -- end data fix project
224 IF l_debug_level > 0 THEN
225 oe_debug_pub.add('OEXWINVB.pls - in unexpected error raise exception');
226 END IF;
227 --resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_INCOMPLETE;
228 --return;
229 app_exception.raise_exception;
230 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
231 -- For HOLDs and Validation errors
232 IF l_result_out = OE_GLOBALS.G_WFR_ON_HOLD THEN
233 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_ON_HOLD;
234 OE_STANDARD_WF.Save_Messages(p_instance_id => actid);
235 OE_STANDARD_WF.Clear_Msg_Context;
236 RETURN;
237 ELSIF l_result_out = OE_GLOBALS.G_WFR_INCOMPLETE THEN
238 resultout := OE_GLOBALS.G_WFR_COMPLETE || ':' || OE_GLOBALS.G_WFR_INCOMPLETE;
239 OE_STANDARD_WF.Save_Messages(p_instance_id => actid);
240 OE_STANDARD_WF.Clear_Msg_Context;
241 RETURN;
242 END IF;
243 END IF;
244
245 END IF;
246
247
248 --
249 -- CANCEL mode - activity 'compensation'
250 --
251 -- This is an event point is called with the effect of the activity must
252 -- be undone, for example when a process is reset to an earlier point
253 -- due to a loop back.
254 --
255 IF (funcmode = 'CANCEL') THEN
256
257 -- your cancel code goes here
258 null;
259
260 -- no result needed
261 resultout := 'COMPLETE';
262 return;
263 END IF;
264
265
266 --
267 -- Other execution modes may be created in the future. Your
268 -- activity will indicate that it does not implement a mode
269 -- by returning null
270 --
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 -- The line below records this function call in the error system
275 -- in the case of an exception.
276 wf_core.context('OE_Invoice_WF', 'Invoice_Interface',
277 itemtype, itemkey, to_char(actid), funcmode);
278 -- start data fix project
279 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
280 p_itemtype => itemtype,
281 p_itemkey => itemkey);
282 OE_STANDARD_WF.Save_Messages(p_instance_id => actid);
283 OE_STANDARD_WF.Clear_Msg_Context;
284 -- end data fix project
285 RAISE;
286
287 END Invoice_Interface;
288
289 END OE_INVOICE_WF;
[Home] [Help]