[Home] [Help]
PACKAGE BODY: APPS.OE_ADV_ITEM_SEARCH_PVT
Source
1 PACKAGE BODY OE_ADV_ITEM_SEARCH_PVT AS
2 /* $Header: OEXVAISB.pls 120.1 2005/06/27 10:08:32 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_ADV_ITEM_SEARCH_PVT';
5
6 PROCEDURE Create_Items_Selected(p_session_id IN NUMBER,
7 p_header_id IN NUMBER,
8 x_ais_items_tbl OUT NOCOPY /* file.sql.39 change */ ais_item_tbl,
9 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
10 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
11 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
12 IS
13
14 CURSOR selected_items IS
15 SELECT inventory_item_id
16 FROM oe_selected_items
17 WHERE session_id = p_session_id;
18
19 l_line_tbl oe_order_pub.line_tbl_type;
20 l_old_line_tbl oe_order_pub.line_tbl_type;
21 l_line_rec oe_order_pub.line_rec_type;
22
23 l_control_rec oe_globals.control_rec_type;
24
25 -- l_booked_flag VARCHAR2(1) := 'N';
26
27 l_index NUMBER := 1;
28 l_return_status VARCHAR2(1);
29
30 BEGIN
31
32 l_control_rec.controlled_operation := TRUE;
33 l_control_rec.process_partial := TRUE;
34
35 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
36 l_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
37 l_line_rec.header_id := p_header_id;
38 l_line_rec.ordered_quantity := 1;
39
40 /* SELECT booked_flag
41 INTO l_booked_flag
42 FROM oe_order_headers
43 WHERE header_id = p_header_id;
44
45 IF l_booked_flag = 'Y' THEN
46 l_line_rec.ordered_quantity := 1;
47 END IF;*/
48
49 -- Loop on the cursor to load
50
51 FOR l_item IN selected_items LOOP
52
53 l_line_rec.inventory_item_id := l_item.inventory_item_id;
54 l_line_tbl(l_index) := l_line_rec;
55
56 l_index := l_index + 1;
57
58 END LOOP;
59
60 -- Call Process Order API to create the lines
61
62 oe_debug_pub.add('No of Items Selected in Advanced Search : ' || To_char(l_index - 1), 1);
63
64 IF l_index > 1 THEN
65
66 oe_order_pvt.lines
67 ( p_validation_level => fnd_api.g_valid_level_none
68 , p_control_rec => l_control_rec
69 , p_x_line_tbl => l_line_tbl
70 , p_x_old_line_tbl => l_old_line_tbl
71 , x_return_status => l_return_status );
72
73
74 oe_debug_pub.add('After call to Lines Procedure : ' || l_return_status, 1 );
75
76 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78 END IF;
79
80 OE_ORDER_PVT.Process_Requests_And_notify
81 ( p_process_requests => TRUE
82 , p_notify => TRUE
83 , x_return_status => l_return_status
84 , p_line_tbl => l_line_tbl
85 , p_old_line_tbl => l_old_line_tbl);
86
87
88 oe_debug_pub.add('After call to Process Request Notify : ' || l_return_status, 1 );
89
90 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
93 RAISE FND_API.G_EXC_ERROR;
94 END IF;
95
96 --Now the call goes back to the pld and needs to be handled.
97
98 l_index := l_line_tbl.first;
99 oe_debug_pub.add( 'Copying Items to AIS table ' || To_char(l_index) );
100
101 WHILE l_index IS NOT NULL LOOP
102 x_ais_items_tbl(l_index).inventory_item_id := l_line_tbl(l_index).inventory_item_id;
103 x_ais_items_tbl(l_index).return_status := l_line_tbl(l_index).return_status;
104 l_index := l_line_tbl.next(l_index);
105 END LOOP;
106
107 oe_msg_pub.count_and_get
108 ( p_count => x_msg_count
109 , p_data => x_msg_data );
110
111 x_return_status := l_return_status;
112
113 ELSE -- No items have been selected.
114
115 -- insert a row into OE_SELECTED_ITEMS with inventory_item_id as -1 for the passed session_id
116 insert_unused_session( p_session_id );
117
118 END IF;
119
120 oe_debug_pub.add('Leaving Create_Items_Selected : ' || l_return_status, 1 );
121
122 EXCEPTION
123
124 WHEN FND_API.G_EXC_ERROR THEN
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 oe_msg_pub.count_and_get
127 ( p_count => x_msg_count
128 , p_data => x_msg_data);
129
130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
132 oe_msg_pub.count_and_get
133 ( p_count => x_msg_count
134 , p_data => x_msg_data);
135
136 WHEN OTHERS THEN
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
138 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
139 oe_msg_pub.Add_Exc_Msg
140 ( G_PKG_NAME
141 , 'Create_Items_Selected' );
142 END IF;
143 oe_msg_pub.count_and_get
144 ( p_count => x_msg_count
145 , p_data => x_msg_data);
146
147 END create_items_selected;
148
149 PROCEDURE delete_selection ( p_session_id NUMBER ) IS
150
151 BEGIN
152
153 DELETE FROM oe_selected_items
154 WHERE session_id = p_session_id;
155
156 END delete_selection;
157
158 PROCEDURE Update_Used_Flag ( p_session_id NUMBER ) IS
159
160 BEGIN
161
162 UPDATE oe_selected_items
163 SET used_flag = 'Y'
164 WHERE session_id = p_session_id;
165
166 END Update_Used_Flag;
167
168 PROCEDURE Insert_unused_session ( p_session_id NUMBER ) IS
169
170 BEGIN
171
172 INSERT INTO oe_selected_items
173 (session_id, inventory_item_id, used_flag)
174 VALUES ( p_session_id, -1, 'Y' );
175
176 END insert_unused_session;
177
178 END OE_ADV_ITEM_SEARCH_PVT;