[Home] [Help]
PACKAGE BODY: APPS.OE_AR_ACCEPTANCE_GRP
Source
1 PACKAGE BODY OE_AR_Acceptance_GRP AS
2 -- $Header: OEXGAARB.pls 120.3 2006/09/20 09:21:05 serla noship $
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OEXGAARB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Package Spec of OE_AR_Acceptance_GRP |
13 --| |
14 --| PROCEDURE LIST |
15 --| Process_Acceptance_in_OM |
16 --| |
17 --| HISTORY |
18 --| MAY-05-2005 Initial creation |
19 --+=======================================================================+
20
21 PROCEDURE Process_Acceptance_in_OM(
22 p_Action_Request_tbl IN OUT NOCOPY OE_Order_PUB.Request_Tbl_Type,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2) IS
26
27 x_header_rec OE_Order_PUB.Header_Rec_Type;
28 x_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
29 x_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
30 x_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
31 x_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
32 x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
33 x_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
34 x_line_tbl OE_Order_PUB.Line_Tbl_Type;
35 x_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
36 x_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
37 x_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
38 x_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
39 x_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
40 x_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
41 x_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
42
43 BEGIN
44
45 -- call to process order
46 OE_Order_PVT.Process_Order(
47 p_api_version_number => 1.0
48 ,p_init_msg_list => FND_API.G_TRUE
49 ,x_return_status => x_return_status
50 ,x_msg_count => x_msg_count
51 ,x_msg_data => x_msg_data
52 ,p_x_action_request_tbl => p_action_request_tbl
53 ,p_x_header_rec => x_header_rec
54 ,p_x_Header_Adj_tbl => x_Header_Adj_tbl
55 ,p_x_Header_price_Att_tbl => x_Header_price_Att_tbl
56 ,p_x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
57 ,p_x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
58 ,p_x_Header_Scredit_tbl => x_Header_Scredit_tbl
59 ,p_x_Header_Payment_tbl => x_Header_Payment_tbl
60 ,p_x_line_tbl => x_line_tbl
61 ,p_x_Line_Adj_tbl => x_Line_Adj_tbl
62 ,p_x_Line_price_Att_tbl => x_Line_price_Att_tbl
63 ,p_x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
64 ,p_x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
65 ,p_x_Line_Scredit_tbl => x_Line_Scredit_tbl
66 ,p_x_Line_Payment_tbl => x_Line_Payment_tbl
67 ,p_x_Lot_Serial_tbl => x_Lot_Serial_tbl
68 );
69
70 END Process_Acceptance_in_OM;
71
72 PROCEDURE Get_interface_attributes
73 ( p_line_id IN NUMBER
74 , x_line_flex_rec OUT NOCOPY ar_deferral_reasons_grp.line_flex_rec
75 , x_return_status OUT NOCOPY VARCHAR2
76 , x_msg_count OUT NOCOPY NUMBER
77 , x_msg_data OUT NOCOPY VARCHAR2
78 )IS
79 l_delivery_line_id NUMBER := NULL;
80 l_line_rec OE_Order_Pub.Line_Rec_Type;
81 --
82 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
83 --
84 BEGIN
85 IF l_debug_level > 0 THEN
86 oe_debug_pub.add( 'ENTER OE_AR_Acceptance_GRP.GET_INTERFACE_ATTRIBUTES PROCEDURE ' , 5 ) ;
87 END IF;
88
89 IF (p_line_id is NULL OR p_line_id = FND_API.G_MISS_NUM) THEN
90 IF l_debug_level > 0 THEN
91 oe_debug_pub.add( 'Get_Interface_Attributes:Line id is null hence return' , 5 ) ;
92 END IF;
93 RETURN;
94 ELSE
95 BEGIN
96 OE_Line_Util.Query_Row(p_line_id => p_line_id,x_line_rec => l_line_rec);
97
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add( 'Get_Interface_Attributes: NO DATA FOUND FOR LINE ID:'||p_line_id , 5 ) ;
102 END IF;
103 x_line_flex_rec.INTERFACE_LINE_CONTEXT := NULL;
104 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE1 := NULL;
105 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE2 := NULL;
106 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := NULL;
107 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := NULL;
108 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE5 := NULL;
109 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE6 := NULL;
110 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE7 := NULL;
111 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE8 := NULL;
112 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE9 := NULL;
113 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE10 := NULL;
114 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE11 := NULL;
115 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE12 := NULL;
116 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE13 := NULL;
117 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE14 := NULL;
118 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE15 := NULL;
119
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 RETURN;
122 END;
123 END IF;
124
125 IF nvl(l_line_rec.invoiced_quantity, 0) = 0 THEN
126 IF l_debug_level > 0 THEN
127 oe_debug_pub.add( 'Get_Interface_Attributes: This line is not invoice interfaced, hence return. LINE ID:'||p_line_id , 5 ) ;
128
129 END IF;
130 x_line_flex_rec.INTERFACE_LINE_CONTEXT := NULL;
131 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE1 := NULL;
132 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE2 := NULL;
133 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := NULL;
134 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := NULL;
135 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE5 := NULL;
136 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE6 := NULL;
137 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE7 := NULL;
138 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE8 := NULL;
139 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE9 := NULL;
140 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE10 := NULL;
141 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE11 := NULL;
142 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE12 := NULL;
143 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE13 := NULL;
144 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE14 := NULL;
145 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE15 := NULL;
146
147 RETURN;
148
149 END IF;
150
151 x_line_flex_rec.INTERFACE_LINE_CONTEXT := 'ORDER ENTRY';
155 FROM oe_order_headers_all
152
153 SELECT order_number
154 INTO x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE1
156 WHERE header_id= l_line_rec.header_id;
157
158 SELECT tt.name
159 INTO x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE2
160 FROM oe_transaction_types_tl tt,
161 oe_order_headers oh
162 WHERE tt.language = ( select language_code
163 from fnd_languages
164 where installed_flag = 'B')
165 AND tt.transaction_type_id = oh.order_type_id
166 AND oh.header_id = l_line_rec.header_id;
167
168 IF l_debug_level > 0 THEN
169 oe_debug_pub.add( 'Get_Interface_Attributes: HEADER_ID :'||l_line_rec.header_id , 5 ) ;
170 END IF;
171
172 -- Populate delivery number and Waybill number
173 IF OE_Invoice_PUB.Shipping_info_Available(l_line_rec) THEN
174 IF l_debug_level > 0 THEN
175 oe_debug_pub.add( 'Get_Interface_Attributes:Shipping_info_Available: TRUE', 5 ) ;
176 END IF;
177 IF l_line_rec.item_type_code NOT In ('MODEL','CLASS','KIT') THEN
178 IF l_debug_level > 0 THEN
179 oe_debug_pub.add( 'Get_Interface_Attributes: ITEM NOT A MODEL/CLASS/KIT', 5 ) ;
180 END IF;
181 BEGIN
182 SELECT min(dl.delivery_id)
183 INTO l_delivery_line_id
184 FROM wsh_new_deliveries dl,
185 wsh_delivery_assignments da,
186 wsh_delivery_details dd
187 WHERE dd.delivery_detail_id = da.delivery_detail_id
188 AND da.delivery_id = dl.delivery_id
189 AND dd.source_code = 'OE'
190 AND dd.source_line_id = l_line_rec.line_id;
191
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 IF l_debug_level > 0 THEN
198 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := '0';
195 oe_debug_pub.add( 'Get_Interface_Attributes: DELIVERY DETAILS NOT FOUND FOR THIS LINE' , 1 ) ;
196 END IF;
197 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := '0';
199 END;
200 ELSE
201 -- IF l_line_rec.item_type_code In ('MODEL','CLASS','KIT') THEN
202 IF l_debug_level > 0 THEN
203 oe_debug_pub.add( 'Get_Interface_Attributes:ITEM IS A MODEL/CLASS/KIT' , 5 ) ;
204 END IF;
205 BEGIN
206 SELECT min(dl.delivery_id)
207 INTO l_delivery_line_id
208 FROM wsh_new_deliveries dl,
209 wsh_delivery_assignments da,
210 wsh_delivery_details dd
211 WHERE dd.delivery_detail_id = da.delivery_detail_id
212 AND da.delivery_id = dl.delivery_id
213 AND dd.source_code = 'OE'
214 AND dd.top_model_line_id = l_line_rec.line_id;
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 IF l_debug_level > 0 THEN
218 oe_debug_pub.add( 'Get_Interface_Attributes:DELIVERY DETAILS NOT FOUND FOR THIS LINE' , 1 ) ;
219 END IF;
220 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := '0';
221 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := '0';
222 END;
223 END IF;
224 END IF;
225
226 IF l_delivery_line_id Is Not Null Then
227 BEGIN
228 SELECT NVL(SUBSTR(dl.name, 1, 30), '0')
229 ,NVL(SUBSTR(dl.waybill, 1, 30), '0')
230 INTO x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3
231 ,x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4
232 FROM wsh_new_deliveries dl
233 WHERE dl.delivery_id = l_delivery_line_id;
234
235 IF l_debug_level > 0 THEN
236 oe_debug_pub.add('Get_Interface_Attributes:DELIVERY NUM:'||X_LINE_FLEX_REC.INTERFACE_LINE_ATTRIBUTE3,5);
237 oe_debug_pub.add('Get_Interface_Attributes:WAYBILL NUM:'||X_LINE_FLEX_REC.INTERFACE_LINE_ATTRIBUTE4 ,5);
238 END IF;
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 IF l_debug_level > 0 THEN
242 oe_debug_pub.add( 'Get_Interface_Attributes:NO DETAILS FOUND FOR DELIVERY AND WAYBILL NUMBER' , 5 );
243 END IF;
244 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := '0';
245 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := '0';
246 END;
247 ELSE -- for Returns and non shippable lines
248 IF l_debug_level > 0 THEN
249 oe_debug_pub.add( 'Get_Interface_Attributes:NON SHIPPABLE OR RETURN LINE ' , 5 ) ;
250 END IF;
251 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := '0';
252 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := '0';
253 END IF;
254
255
256 -- Line would not interface more than once if it has contingency attached to it.
257 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE5 :='0';
258 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE6 := to_char(p_line_id);
259 x_return_status := FND_API.G_RET_STS_SUCCESS;
260
261 IF l_debug_level > 0 THEN
262 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE1:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE1);
263 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE2:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE2);
264 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE3:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3);
265 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE4:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4);
266 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE5:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE5);
267 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE6:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE6);
268 oe_debug_pub.add( 'EXIT OE_AR_Acceptance_GRP.GET_INTERFACE_ATTRIBUTES PROCEDURE',5) ;
269 END IF;
270
271 END Get_Interface_Attributes;
272
273 END OE_AR_Acceptance_GRP;