4
1 PACKAGE BODY WMS_REPL_CUSTOM_APIS_PUB AS
2 /* $Header: WMSREPCB.pls 120.2 2010/07/29 17:14:22 sahmahes ship $ */
3
5 PROCEDURE print_debug(p_err_msg VARCHAR2) IS
6 BEGIN
7 inv_mobile_helper_functions.tracelog(p_err_msg => p_err_msg,
8 p_module => 'WMS_REPL_CUSTOM_APIS_PUB',
9 p_level => 4);
10 END print_debug;
11
12 PROCEDURE POST_REPL_ALLOCATION_CUST
13 (x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2,
16 p_mol_id IN NUMBER) IS
17 l_progress VARCHAR2(10);
18 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
19
20
21
22 --***********************************************************************
23 --This API is to be used to process the demand lines after replenishment
24 --code has completed processing. One of the uses of this API can be to
25 --backorder the demand lines, that were assosciated with REPLENISHMENT
26 --Move order in case allocation of replenishment move orders was partial.
27 --************************************************************************
28
29 BEGIN
30
31 IF (l_debug = 1) THEN
32 print_debug('***Calling POST_REPL_ALLOCATION_CUST ***');
33 END IF;
34
35 -- Set the savepoint
36 SAVEPOINT POST_REPL_ALLOCATION_CUST_SP;
37 l_progress := '10';
38
39 -- Initialize message list to clear any existing messages
40 fnd_msg_pub.initialize;
41 l_progress := '20';
42
43 -- Initialize API return status to success
44 x_return_status := fnd_api.g_ret_sts_success;
45 l_progress := '30';
46
47 -- If the custom API is not implemented, return a value of FALSE for the output
48 -- variable 'x_api_is_implemented'. When custom logic is implemented, the line below
49 -- should be modified to return a TRUE value instead i.e.
50 g_is_api_implemented := TRUE;
51
52
53 --=======================================
54 --<Insert custom logic STARTS here>
55 -- Custom code to be written below this
56 --=======================================
57
58 -- The input to this API was move order line id.
59 -- Custom code can perform its operation using that move order line id.
60
61 -- For eg. if you have 3 demand lines of qty 10, 20 and 30.
62 -- Suppose you allocte 30 and for some reason you were not able to allocate the other 30
63 -- Thus, partial allocation of the lines happened.
64 -- Then you can backorder the line from here now that you have the move order line.
65
66
67
68
69
70 --=======================================
71 --<Insert custom logic ENDS here>
72 -- Custom code to be written below this
73 --=======================================
74 IF (l_debug = 1) THEN
75 print_debug('***End of POST_REPL_ALLOCATION_CUST ***');
76 END IF;
77
78 EXCEPTION
79 WHEN FND_API.G_EXC_ERROR THEN
80 ROLLBACK TO POST_REPL_ALLOCATION_CUST_SP;
81 x_return_status := fnd_api.g_ret_sts_error;
82 fnd_msg_pub.count_and_get(p_count => x_msg_count,
83 p_data => x_msg_data);
84 IF (l_debug = 1) THEN
85 print_debug('Exiting POST_REPL_ALLOCATION_CUST - Execution error: ' ||
86 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
87 END IF;
88
89 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
90 ROLLBACK TO POST_REPL_ALLOCATION_CUST_SP;
91 x_return_status := fnd_api.g_ret_sts_unexp_error;
92 fnd_msg_pub.count_and_get(p_count => x_msg_count,
93 p_data => x_msg_data);
94 IF (l_debug = 1) THEN
95 print_debug('Exiting POST_REPL_ALLOCATION_CUST - Unexpected error: ' ||
96 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
97 END IF;
98
99
100 WHEN OTHERS THEN
101 ROLLBACK TO POST_REPL_ALLOCATION_CUST_SP;
102 x_return_status := fnd_api.g_ret_sts_unexp_error;
103 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
104 -- fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
105 fnd_msg_pub.add_exc_msg('WMS_REPL_CUSTOM_APIS_PUB','POST_REPL_ALLOCATION_CUST');
106
107 END IF;
108 fnd_msg_pub.count_and_get(p_count => x_msg_count,
109 p_data => x_msg_data);
110 IF (l_debug = 1) THEN
111 print_debug('Exiting POST_REPL_ALLOCATION_CUST - Others exception: ' ||
112 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
113 END IF;
114
115
116 END POST_REPL_ALLOCATION_CUST;
117
118
119 PROCEDURE get_consol_repl_demand_cust
120 (x_return_status OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER,
122 x_msg_data OUT NOCOPY VARCHAR2,
123 x_consol_item_repl_tbl OUT NOCOPY WMS_REPLENISHMENT_PVT.consol_item_repl_tbl
124 ) IS
125 l_demand_line_detail_id NUMBER;
126 l_quantity NUMBER;
127 l_cnt NUMBER;
131 BEGIN
128 l_progress VARCHAR2(10);
129 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
130
132 IF (l_debug = 1) THEN
133 print_debug('***Calling GET_CONSOL_REPL_DEMAND_CUST ***');
134 END IF;
135
136 -- Set the savepoint
137 SAVEPOINT GET_CONSOL_REPL_DEMAND_CUST_SP;
138 l_progress := '10';
139
140 -- Initialize message list to clear any existing messages
141 fnd_msg_pub.initialize;
142 l_progress := '20';
143
144 -- Initialize API return status to success
145 x_return_status := fnd_api.g_ret_sts_success;
146 l_progress := '30';
147
148 -- If the custom API is not implemented, return a value of FALSE for the output
149 -- variable 'x_api_is_implemented'. When custom logic is implemented, the line below
150 -- should be modified to return a TRUE value instead.
151 g_is_api_implemented := TRUE;
152
153
154 --=======================================
155 --<Insert custom logic STARTS here>
156 --FOLLOW the 4 STEP process below:
157 --=======================================
158
159
160
161 -- STEP 1:
162 -- QUERY ALL DEMAND RECORDS INSERTED IN THE WMS_REPL_DEMAND_GTMP table
163 -- This set of records in the temp table already has filtered records
164 -- based ON criteria specified in the Push/Pull Replenishment Concurrent Program.
165
166 -- STEP 2:
167 -- ADD custom SQL logic to add/remove lines from WMS_REPL_DEMAND_GTMP table
171
168 -- Any line that gets deleted must be added to the Global PL/SQL table below.
169 -- The system will BACKORDER those demand lines at the end of the program
170
172 -- STEP 3:
173
174 --STEP 3.i)
175 l_cnt := wms_replenishment_pvt.g_backorder_deliv_tab.COUNT();
176
177
178 -- For each demand line that is removed from the table wms_repl_demand_gtmp, MUST do following:
179 -- *************************************************
180 -- delete this demand line from the GTMP
181 -- subtract the qty from the consolidation record for the item
182
183
184 -- we have not updated the qty l_prim_repl_qty yet
185 -- so no need to update the consol qty for this item
186
187 -- Add here to list of delivery_details to be backordered
188
189 l_cnt := l_cnt+1; -- Do not reset the cnt counter value.
190 wms_replenishment_pvt.g_backorder_deliv_tab(l_cnt):= l_demand_line_detail_id;
191 wms_replenishment_pvt.g_backorder_qty_tab(l_cnt) := l_quantity;
192
193 -- since we are backordering entire qty parameters
194 -- p_bo_qtys AND p_req_qtys will have same value
195
196 wms_replenishment_pvt.g_dummy_table(l_cnt) := 0;
197 -- *************************************************
198
199 --STEP 3.ii)
200 -- Decrement the quantity from x_consol_item_repl_tbl PL/SQL table based of inventory_item_id.
201 --Make sure that the records in x_consol_item_repl_tbl has correct values filled for fields marked '??' below.
202 --Those fields that have values assigned to 0 should always be assigned 0 to be calculated later in the program.
203
204 -- PROVIDE THESE VALUES:
205 --Organization_id = ??
206 --Item_id = ??
207 --total_demand_qty = ?? (in replenishment UOM)
208 --Repl_to_subinventory_code = ??
209 --Repl_UOM_code = ?? <<for the specified to_subinventory_code from subinventory set up>>
210 --date_required := ??; -- date on original demand lines
211
212 -- DO NOT CHANGE THE VALUE OF FOLLOWING PARAMETERS IN the x_consol_item_repl_tbl PL/SQL table:
213 --available_onhand_qty = 0
214 --open_mo_qty = 0
215 --final_replenishment_qty = 0
216
217 -- STEP 4:
218 -- RETURN consolidated demand lines in the x_consol_item_repl_tbl PL/SQL table
219
220
221 --=======================================
222 --<Insert custom logic ENDS here>
223 --=======================================
224
225 IF (l_debug = 1) THEN
226 print_debug('***End of GET_CONSOL_REPL_DEMAND_CUST ***');
227 END IF;
228
229 EXCEPTION
230 WHEN FND_API.G_EXC_ERROR THEN
231 ROLLBACK TO GET_CONSOL_REPL_DEMAND_CUST_SP;
232 x_return_status := fnd_api.g_ret_sts_error;
233 fnd_msg_pub.count_and_get(p_count => x_msg_count,
234 p_data => x_msg_data);
235 IF (l_debug = 1) THEN
236 print_debug('Exiting GET_CONSOL_REPL_DEMAND_CUST - Execution error: ' ||
240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
237 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
238 END IF;
239
241 ROLLBACK TO GET_CONSOL_REPL_DEMAND_CUST_SP;
242 x_return_status := fnd_api.g_ret_sts_unexp_error;
243 fnd_msg_pub.count_and_get(p_count => x_msg_count,
244 p_data => x_msg_data);
245 IF (l_debug = 1) THEN
246 print_debug('Exiting GET_CONSOL_REPL_DEMAND_CUST - Unexpected error: ' ||
247 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
248 END IF;
249
250 WHEN OTHERS THEN
251 ROLLBACK TO GET_CONSOL_REPL_DEMAND_CUST_SP;
252 x_return_status := fnd_api.g_ret_sts_unexp_error;
253 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
254 -- fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
255 fnd_msg_pub.add_exc_msg('WMS_REPL_CUSTOM_APIS_PUB','GET_CONSOL_REPL_DEMAND_CUST');
256
257 END IF;
258 fnd_msg_pub.count_and_get(p_count => x_msg_count,
259 p_data => x_msg_data);
260 IF (l_debug = 1) THEN
261 print_debug('Exiting GET_CONSOL_REPL_DEMAND_CUST - Others exception: ' ||
262 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
263 END IF;
264
265
266 END GET_CONSOL_REPL_DEMAND_CUST;
267
268 END WMS_REPL_CUSTOM_APIS_PUB;
[Home] [Help]