1 PACKAGE BODY oe_cogs_grp AS
2 /* $Header: OEXGCGSB.pls 120.6 2011/02/17 06:25:41 slagiset ship $ */
3
4
5 PROCEDURE get_revenue_event_line
6 (
7 p_shippable_line_id IN NUMBER,
8 x_revenue_event_line_id OUT NOCOPY NUMBER,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2
12 ) IS
13
14 l_line_id NUMBER;
15 l_ato_line_id_cogs NUMBER; --9811795
16 l_ato_line_id_rev NUMBER; --9811795
17 l_invoicable VARCHAR2(1);
18 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
19 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level; --9811795
20
21 BEGIN
22 x_return_status := FND_API.G_RET_STS_SUCCESS;
23 x_revenue_event_line_id := -1; --9811795
24
25 --9811795 start
26 IF l_debug_level > 0
27 THEN
28 OE_DEBUG_PUB.ADD('Entering oe_cogs_grp.get_revenue_event_line ');
29 END IF;
30 --9811795 end
31
32 l_line_id := p_shippable_line_id;
33
34 --9811795 start
35 SELECT ato_line_id
36 INTO l_ato_line_id_cogs
37 FROM oe_order_lines_all
38 WHERE line_id=l_line_id;
39
40 IF l_ato_line_id_cogs IS NOT NULL THEN
41
42 IF l_debug_level > 0
43 THEN
44 OE_DEBUG_PUB.ADD('Below calling extension API');
45 END IF;
46
47 oe_cogs_extension.get_revenue_event_line
48 (
49 p_shippable_line_id => l_line_id,
50 x_revenue_event_line_id => x_revenue_event_line_id,
51 x_return_status => x_return_status,
52 x_msg_count => x_msg_count,
53 x_msg_data => x_msg_data
54 );
55
56 IF l_debug_level > 0
57 THEN
58 OE_DEBUG_PUB.ADD('After calling extension API , revenue event line id :'||x_revenue_event_line_id);
59 END IF;
60
61 IF x_revenue_event_line_id <> -1 THEN
62
63 SELECT ato_line_id
64 INTO l_ato_line_id_rev
65 FROM oe_order_lines_all
66 WHERE line_id=x_revenue_event_line_id;
67
68 IF l_ato_line_id_rev <> l_ato_line_id_cogs THEN
69
70 FND_MESSAGE.SET_NAME('ONT', 'OE_REV_INVALID_LINE_ID');
71 FND_MESSAGE.SET_TOKEN('LINE_ID', x_revenue_event_line_id);
72 OE_MSG_PUB.ADD;
73
74 IF l_debug_level > 0
75 THEN
76 OE_DEBUG_PUB.ADD('Revenue Line ID and COGS Line ID are not a part of same ATO Model');
77 END IF;
78
79 x_return_status := FND_API.G_RET_STS_ERROR;
80 RETURN;
81
82 END IF;
83
84 END IF;
85
86
87 END IF;
88
89 IF x_revenue_event_line_id <> -1 THEN
90 RETURN;
91 END IF;
92
93 x_revenue_event_line_id := NULL;
94 --9811795 end
95
96 WHILE x_revenue_event_line_id IS NULL LOOP
97
98 l_line_rec := oe_line_util.query_row(l_line_id);
99
100 IF oe_invoice_pub.line_invoiceable(l_line_rec) THEN
101 x_revenue_event_line_id := l_line_id;
102
103 ELSIF l_line_rec.link_to_line_id IS NULL THEN
104
105 -- for a standard line or a top model line,
106 -- the revenue event line is itself
107 -- for an included item, if non of its parent is invoicable,
108 -- the revenue event line will be the top model line
109
110 x_revenue_event_line_id := l_line_id;
111
112 ELSE
113
114 l_line_id := l_line_rec.link_to_line_id;
115
116 END IF;
117
118 END LOOP;
119
120
121 --9811795 start
122 IF l_debug_level > 0
123 THEN
124 OE_DEBUG_PUB.ADD('Exiting oe_cogs_grp.get_revenue_event_line ');
125 END IF;
126 --9811795 end
127
128 EXCEPTION WHEN NO_DATA_FOUND THEN
129
130 -- query order lines might return no data found
131 -- either because costing is passing an invalid order line
132 -- or because this line has an invalid link_to_line_id
133 FND_MESSAGE.SET_NAME('ONT', 'OE_COGS_INVALID_LINE_ID');
134 FND_MESSAGE.SET_TOKEN('LINE_ID', p_shippable_line_id);
135 OE_MSG_PUB.ADD;
136 x_return_status := FND_API.G_RET_STS_ERROR;
137
138 --9811795 start
139 IF l_debug_level > 0
140 THEN
141 OE_DEBUG_PUB.ADD('Error When no data found ');
142 END IF;
143 --9811795 end
144
145 WHEN OTHERS THEN
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147
148 --9811795 start
149 IF l_debug_level > 0
150 THEN
151 OE_DEBUG_PUB.ADD('When others:'||substr(SQLCODE,1,250));
152 END IF;
153 --9811795 end
154
155 END get_revenue_event_line;
156
157
158
159 FUNCTION is_revenue_event_line
160 (
161 p_line_id IN NUMBER
162 ) RETURN VARCHAR2 IS
163
164 l_child varchar2(1) := 'N';
165 l_master_org_id NUMBER;
166 l_master_org varchar2(30) := 'MASTER_ORGANIZATION_ID';
167 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
168 l_notify_costing VARCHAR2(1);
169 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
170
171 -- bug#11717692
172 l_inventory_interfaced VARCHAR2(1) := 'N';
173
174
175 BEGIN
176
177 l_line_rec := oe_line_util.query_row(p_line_id);
178
179 IF (l_line_rec.retrobill_request_id IS NOT NULL
180 OR l_line_rec.line_category_code = 'RETURN'
181 OR l_line_rec.source_document_type_id = 10
182 OR l_line_rec.cancelled_flag = 'Y' ) THEN
183
184 IF l_debug_level > 0 THEN
185 oe_debug_pub.add( 'is revenue line: return/retrobill/internal/cancelled: returning N' , 1 ) ;
186 END IF;
187
188 RETURN 'N';
189
190 END IF;
191
192
193 l_master_org_id := oe_sys_parameters.value(l_master_org, l_line_rec.org_id);
194
195 IF l_line_rec.invoiced_quantity IS NULL OR l_line_rec.invoiced_quantity = 0 THEN
196
197 IF l_line_rec.top_model_line_id IS NULL
198 AND l_line_rec.item_type_code = 'STANDARD'
199 AND l_line_rec.shipped_quantity IS NOT NULL THEN
200 IF l_debug_level > 0 THEN
201 oe_debug_pub.add( 'is revenue line: standard shippable: returning Y' , 1 ) ;
202 END IF;
203
204 RETURN 'Y';
205
206 ELSIF l_line_rec.top_model_line_id IS NOT NULL THEN
207
208 -- model component
209
210 IF ( (p_line_id <> l_line_rec.top_model_line_id
211 AND oe_invoice_pub.line_invoiceable(l_line_rec))
212 OR (p_line_id = l_line_rec.top_model_line_id)) THEN
213
214 BEGIN
215 SELECT 'Y' INTO l_notify_costing
216 FROM DUAL WHERE EXISTS (SELECT l.line_id
217 FROM oe_order_lines_all l,
218 mtl_system_items msi
219 WHERE link_to_line_id = p_line_id
220 AND l.inventory_item_id=msi.inventory_item_id
221 AND msi.organization_id= l_master_org_id
222 AND (msi.invoice_enabled_flag = 'N'
223 OR msi.invoiceable_item_flag = 'N'
224 OR l.item_type_code='INCLUDED'
225 OR l.item_type_code='CONFIG'));
226 l_child := 'Y';
227
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 l_child:= 'N';
231 END;
232
233 IF l_child = 'Y' OR l_line_rec.shipped_quantity IS NOT NULL THEN
234 IF l_debug_level > 0 THEN
235 oe_debug_pub.add( 'is revenue line: model component: l_child='||l_child||' shipped_quantity='||l_line_rec.shipped_quantity||' returning Y' , 1 ) ;
236 END IF;
237
238 RETURN 'Y';
239
240 END IF;
241
242 END IF; -- top model line or invoiceable line
243 -- Check if there's a SO Issue on the line
244 --
245 -- Bug #11717692
246 -- is_revenue_event_line is modified to return 'Y' to move the COGS account from
247 -- deferred to actual account for the order lines that has a sales order issue.
248 -- For the lines with 'Bill Only (Consume)' Line flow, there exists a SO Issue
249 -- when the Lines INVENTORY_INTERFACE WF activity is COMPLETE
250
251 ELSE
252 IF l_debug_level > 0 THEN
253 oe_debug_pub.add( 'Checking if the Line is inventory interfaced', 1);
254 END IF;
255
256 BEGIN
257 SELECT 'Y' INTO l_inventory_interfaced
258 FROM WF_ITEM_ACTIVITY_STATUSES WAS, WF_PROCESS_ACTIVITIES WPA
259 WHERE WAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
260 AND WAS.ITEM_TYPE = 'OEOL'
261 AND WAS.ITEM_KEY = to_char(p_line_id)
262 AND WPA.ACTIVITY_NAME = 'INVENTORY_INTERFACE'
263 AND WAS.ACTIVITY_STATUS = 'COMPLETE'
264 AND WAS.ACTIVITY_RESULT_CODE = 'COMPLETE';
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 IF l_debug_level > 0 THEN
268 oe_debug_pub.add( 'No data returned from the Workflow status query. ', 1);
269 END IF;
270 l_inventory_interfaced := 'N';
271 END;
272
273 IF l_inventory_interfaced = 'Y' THEN
274 IF l_debug_level > 0 THEN
275 oe_debug_pub.add( 'Line is inventory interfaced.', 1);
276 oe_debug_pub.add( 'is revenue line: Sales Order Issue: returning Y.', 1);
277 END IF;
278 RETURN 'Y';
279 END IF;
280 END IF; -- SO Issue Exists
281 -- End of Changes for Bug #11717692
282
283 END IF;
284 IF l_debug_level > 0 THEN
285 oe_debug_pub.add( 'is revenue line: returning N', 1);
286 END IF;
287 RETURN 'N';
288
289 EXCEPTION WHEN OTHERS THEN
290
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292
293 END is_revenue_event_line;
294
295
296 END oe_cogs_grp;
[Home] [Help]