1 PACKAGE BODY oe_cogs_grp AS
2 /* $Header: OEXGCGSB.pls 120.2 2006/07/14 11:09:09 serla noship $ */
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_invoicable VARCHAR2(1);
16 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
17
18 BEGIN
19 x_return_status := FND_API.G_RET_STS_SUCCESS;
20 l_line_id := p_shippable_line_id;
21
22 WHILE x_revenue_event_line_id IS NULL LOOP
23
24 l_line_rec := oe_line_util.query_row(l_line_id);
25
26 IF oe_invoice_pub.line_invoiceable(l_line_rec) THEN
27 x_revenue_event_line_id := l_line_id;
28
29 ELSIF l_line_rec.link_to_line_id IS NULL THEN
30
31 -- for a standard line or a top model line,
32 -- the revenue event line is itself
33 -- for an included item, if non of its parent is invoicable,
34 -- the revenue event line will be the top model line
35
36 x_revenue_event_line_id := l_line_id;
37
38 ELSE
39
40 l_line_id := l_line_rec.link_to_line_id;
41
42 END IF;
43
44 END LOOP;
45
46 EXCEPTION WHEN NO_DATA_FOUND THEN
47
48 -- query order lines might return no data found
49 -- either because costing is passing an invalid order line
50 -- or because this line has an invalid link_to_line_id
51 FND_MESSAGE.SET_NAME('ONT', 'OE_COGS_INVALID_LINE_ID');
52 FND_MESSAGE.SET_TOKEN('LINE_ID', p_shippable_line_id);
53 OE_MSG_PUB.ADD;
54 x_return_status := FND_API.G_RET_STS_ERROR;
55
56 WHEN OTHERS THEN
57 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
58
59 END get_revenue_event_line;
60
61
62
63 FUNCTION is_revenue_event_line
64 (
65 p_line_id IN NUMBER
66 ) RETURN VARCHAR2 IS
67
68 l_child varchar2(1) := 'N';
69 l_master_org_id NUMBER;
70 l_master_org varchar2(30) := 'MASTER_ORGANIZATION_ID';
71 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
72 l_notify_costing VARCHAR2(1);
73 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
74
75 BEGIN
76
77 l_line_rec := oe_line_util.query_row(p_line_id);
78
79 IF (l_line_rec.retrobill_request_id IS NOT NULL
80 OR l_line_rec.line_category_code = 'RETURN'
81 OR l_line_rec.source_document_type_id = 10
82 OR l_line_rec.cancelled_flag = 'Y' ) THEN
83
84 IF l_debug_level > 0 THEN
85 oe_debug_pub.add( 'is revenue line: return/retrobill/internal/cancelled: returning N' , 1 ) ;
86 END IF;
87
88 RETURN 'N';
89
90 END IF;
91
92
93 l_master_org_id := oe_sys_parameters.value(l_master_org, l_line_rec.org_id);
94
95 IF l_line_rec.invoiced_quantity IS NULL OR l_line_rec.invoiced_quantity = 0 THEN
96
97 IF l_line_rec.top_model_line_id IS NULL
98 AND l_line_rec.item_type_code = 'STANDARD'
99 AND l_line_rec.shipped_quantity IS NOT NULL THEN
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add( 'is revenue line: standard shippable: returning Y' , 1 ) ;
102 END IF;
103
104 RETURN 'Y';
105
106 ELSIF l_line_rec.top_model_line_id IS NOT NULL THEN
107
108 -- model component
109
110 IF ( (p_line_id <> l_line_rec.top_model_line_id
111 AND oe_invoice_pub.line_invoiceable(l_line_rec))
112 OR (p_line_id = l_line_rec.top_model_line_id)) THEN
113
114 BEGIN
115 SELECT 'Y' INTO l_notify_costing
116 FROM DUAL WHERE EXISTS (SELECT l.line_id
117 FROM oe_order_lines_all l,
118 mtl_system_items msi
119 WHERE link_to_line_id = p_line_id
120 AND l.inventory_item_id=msi.inventory_item_id
121 AND msi.organization_id= l_master_org_id
122 AND (msi.invoice_enabled_flag = 'N'
123 OR msi.invoiceable_item_flag = 'N'
124 OR l.item_type_code='INCLUDED'
125 OR l.item_type_code='CONFIG'));
126 l_child := 'Y';
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 l_child:= 'N';
131 END;
132
133 IF l_child = 'Y' OR l_line_rec.shipped_quantity IS NOT NULL THEN
134 IF l_debug_level > 0 THEN
135 oe_debug_pub.add( 'is revenue line: model component: l_child='||l_child||' shipped_quantity='||l_line_rec.shipped_quantity||' returning Y' , 1 ) ;
136 END IF;
137
138 RETURN 'Y';
139
140 END IF;
141
142 END IF; -- top model line or invoiceable line
143 END IF; -- part of a model;
144 END IF;
145 IF l_debug_level > 0 THEN
146 oe_debug_pub.add( 'is revenue line: returning N', 1);
147 END IF;
148 RETURN 'N';
149
150 EXCEPTION WHEN OTHERS THEN
151
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153
154 END is_revenue_event_line;
155
156
157 END oe_cogs_grp;