[Home] [Help]
PACKAGE BODY: APPS.OE_AR_ACCEPTANCE_GRP
Source
1 PACKAGE BODY OE_AR_Acceptance_GRP AS
2 -- $Header: OEXGAARB.pls 120.5 2012/01/21 20:19:22 vmachett ship $
6 --| All rights reserved. |
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
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;
114 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE11 := 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;
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 x_line_flex_rec.acceptance_date := NULL; -- bug 8293484
120
121 x_return_status := FND_API.G_RET_STS_ERROR;
122 RETURN;
123 END;
124 END IF;
125
126 IF nvl(l_line_rec.invoiced_quantity, 0) = 0 THEN
127 IF l_debug_level > 0 THEN
128 oe_debug_pub.add( 'Get_Interface_Attributes: This line is not invoice interfaced, hence return. LINE ID:'||p_line_id , 5 ) ;
129
130 END IF;
131 x_line_flex_rec.INTERFACE_LINE_CONTEXT := NULL;
132 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE1 := NULL;
133 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE2 := NULL;
134 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := NULL;
135 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := NULL;
136 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE5 := NULL;
137 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE6 := NULL;
138 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE7 := NULL;
139 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE8 := NULL;
140 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE9 := NULL;
141 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE10 := NULL;
142 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE11 := NULL;
143 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE12 := NULL;
144 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE13 := NULL;
145 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE14 := NULL;
146 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE15 := NULL;
147 x_line_flex_rec.acceptance_date := NULL; -- bug 8293484
148
149 RETURN;
150
151 END IF;
152
153 x_line_flex_rec.INTERFACE_LINE_CONTEXT := 'ORDER ENTRY';
154
155 SELECT order_number
156 INTO x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE1
157 FROM oe_order_headers_all
158 WHERE header_id= l_line_rec.header_id;
159
160 SELECT tt.name
161 INTO x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE2
162 FROM oe_transaction_types_tl tt,
163 oe_order_headers oh
164 WHERE tt.language = ( select language_code
165 from fnd_languages
166 where installed_flag = 'B')
167 AND tt.transaction_type_id = oh.order_type_id
168 AND oh.header_id = l_line_rec.header_id;
169
170 IF l_debug_level > 0 THEN
171 oe_debug_pub.add( 'Get_Interface_Attributes: HEADER_ID :'||l_line_rec.header_id , 5 ) ;
172 END IF;
173
174 -- Populate delivery number and Waybill number
175 IF OE_Invoice_PUB.Shipping_info_Available(l_line_rec) THEN
176 IF l_debug_level > 0 THEN
177 oe_debug_pub.add( 'Get_Interface_Attributes:Shipping_info_Available: TRUE', 5 ) ;
178 END IF;
179 IF l_line_rec.item_type_code NOT In ('MODEL','CLASS','KIT') THEN
180 IF l_debug_level > 0 THEN
181 oe_debug_pub.add( 'Get_Interface_Attributes: ITEM NOT A MODEL/CLASS/KIT', 5 ) ;
182 END IF;
183 BEGIN
184 SELECT min(dl.delivery_id)
185 INTO l_delivery_line_id
186 FROM wsh_new_deliveries dl,
187 wsh_delivery_assignments da,
188 wsh_delivery_details dd
189 WHERE dd.delivery_detail_id = da.delivery_detail_id
190 AND da.delivery_id = dl.delivery_id
191 AND dd.source_code = 'OE'
192 AND dd.source_line_id = l_line_rec.line_id;
193
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 IF l_debug_level > 0 THEN
197 oe_debug_pub.add( 'Get_Interface_Attributes: DELIVERY DETAILS NOT FOUND FOR THIS LINE' , 1 ) ;
198 END IF;
199 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := '0';
200 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := '0';
201 END;
202 ELSE
203 -- IF l_line_rec.item_type_code In ('MODEL','CLASS','KIT') THEN
204 IF l_debug_level > 0 THEN
205 oe_debug_pub.add( 'Get_Interface_Attributes:ITEM IS A MODEL/CLASS/KIT' , 5 ) ;
206 END IF;
207 BEGIN
208 SELECT min(dl.delivery_id)
209 INTO l_delivery_line_id
210 FROM wsh_new_deliveries dl,
211 wsh_delivery_assignments da,
212 wsh_delivery_details dd
213 WHERE dd.delivery_detail_id = da.delivery_detail_id
214 AND da.delivery_id = dl.delivery_id
215 AND dd.source_code = 'OE'
216 AND dd.top_model_line_id = l_line_rec.line_id;
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 IF l_debug_level > 0 THEN
220 oe_debug_pub.add( 'Get_Interface_Attributes:DELIVERY DETAILS NOT FOUND FOR THIS LINE' , 1 ) ;
221 END IF;
222 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := '0';
223 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := '0';
224 END;
225 END IF;
226 END IF;
227
228 IF l_delivery_line_id Is Not Null Then
229 BEGIN
230 SELECT NVL(SUBSTR(dl.name, 1, 30), '0')
231 ,NVL(SUBSTR(dl.waybill, 1, 30), '0')
232 INTO x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3
233 ,x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4
234 FROM wsh_new_deliveries dl
235 WHERE dl.delivery_id = l_delivery_line_id;
236
237 IF l_debug_level > 0 THEN
238 oe_debug_pub.add('Get_Interface_Attributes:DELIVERY NUM:'||X_LINE_FLEX_REC.INTERFACE_LINE_ATTRIBUTE3,5);
239 oe_debug_pub.add('Get_Interface_Attributes:WAYBILL NUM:'||X_LINE_FLEX_REC.INTERFACE_LINE_ATTRIBUTE4 ,5);
240 END IF;
241 EXCEPTION
242 WHEN NO_DATA_FOUND THEN
243 IF l_debug_level > 0 THEN
244 oe_debug_pub.add( 'Get_Interface_Attributes:NO DETAILS FOUND FOR DELIVERY AND WAYBILL NUMBER' , 5 );
245 END IF;
246 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := '0';
247 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := '0';
248 END;
249 ELSE -- for Returns and non shippable lines
250 IF l_debug_level > 0 THEN
251 oe_debug_pub.add( 'Get_Interface_Attributes:NON SHIPPABLE OR RETURN LINE ' , 5 ) ;
252 END IF;
253 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3 := '0';
254 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4 := '0';
255 END IF;
256
257
258 -- Line would not interface more than once if it has contingency attached to it.
259 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE5 :='0';
260 x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE6 := to_char(p_line_id);
261
262 -- Bug 8293484 Start
263 x_line_flex_rec.acceptance_date:=l_line_rec.REVREC_SIGNATURE_DATE;
264
265 oe_debug_pub.add('l_line_rec.REVREC_SIGNATURE_DATE' || l_line_rec.REVREC_SIGNATURE_DATE , 5 );
266 oe_debug_pub.add('x_line_flex_rec.acceptance_date' || x_line_flex_rec.acceptance_date , 5 );
267 -- Bug 8293484 End
268
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270
271 IF l_debug_level > 0 THEN
272 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE1:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE1);
273 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE2:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE2);
274 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE3:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE3);
275 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE4:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE4);
276 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE5:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE5);
277 oe_debug_pub.add('INTERFACE_LINE_ATTRIBUTE6:'||x_line_flex_rec.INTERFACE_LINE_ATTRIBUTE6);
278 oe_debug_pub.add( 'EXIT OE_AR_Acceptance_GRP.GET_INTERFACE_ATTRIBUTES PROCEDURE',5) ;
279 END IF;
280
281 END Get_Interface_Attributes;
282
283 END OE_AR_Acceptance_GRP;