1 PACKAGE BODY WMS_REPL_CUSTOM_APIS_PUB AS
2 /* $Header: WMSREPCB.pls 120.0 2007/12/30 22:42:44 satkumar noship $ */
3
4
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
13 PROCEDURE get_consol_repl_demand_cust
14 (x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 x_consol_item_repl_tbl OUT NOCOPY WMS_REPLENISHMENT_PVT.consol_item_repl_tbl
18 ) IS
19
20 l_progress VARCHAR2(10);
21 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
22
23 BEGIN
24 IF (l_debug = 1) THEN
25 print_debug('***Calling GET_CONSOL_REPL_DEMAND_CUST ***');
26 END IF;
27
28 -- Set the savepoint
29 SAVEPOINT GET_CONSOL_REPL_DEMAND_CUST_SP;
30 l_progress := '10';
31
32 -- Initialize message list to clear any existing messages
33 fnd_msg_pub.initialize;
34 l_progress := '20';
35
36 -- Initialize API return status to success
37 x_return_status := fnd_api.g_ret_sts_success;
38 l_progress := '30';
39
40 -- If the custom API is not implemented, return a value of FALSE for the output
41 -- variable 'x_api_is_implemented'. When custom logic is implemented, the line below
42 -- should be modified to return a TRUE value instead.
43 g_is_api_implemented := FALSE;
44
45
46 --=======================================
47 --<Insert custom logic STARTS here>
48 --=======================================
49 -- QUERY ALL DEMAND RECORDS INSERTED IN THE WMS_REPL_DEMAND_GTMP table
50 -- This set of records in the temp table already has filtered records
51 -- based ON criteria specified in the Push Replenishment Concurrent Program
52
53 -- Apply any custom logic to add/remove demad lines from this table
54 -- RETURN consolidated demand lines in the x_consol_item_repl_tbl PL/SQL table
55
56 --Make sure that the records in x_consol_item_repl_tbl has correct values filled for fields marked '??' below.
57 --Those fields that have values assigned to 0 should always be assigned 0 to be calculated later in the program.
58
59 -- PROVIDE THESE VALUES:
60 --Organization_id = ??
61 --Item_id = ??
62 --total_demand_qty = ?? (in replenishment UOM)
63 --Repl_to_subinventory_code = ??
64 --Repl_UOM_code = ?? <<for the specified to_subinventory_code from subinventory set up>>
65 --date_required := ??; -- date on original demand lines
66
67
68 -- NO VALUE SHOULD BE CHANGED BELOW THIS LINE:
69 --available_onhand_qty = 0
70 --open_mo_qty = 0
71 --final_replenishment_qty = 0
72
73
74
75
76 --=======================================
77 --<Insert custom logic ENDS here>
78 --=======================================
79
80 IF (l_debug = 1) THEN
81 print_debug('***End of GET_CONSOL_REPL_DEMAND_CUST ***');
82 END IF;
83
84 EXCEPTION
85 WHEN FND_API.G_EXC_ERROR THEN
86 ROLLBACK TO GET_CONSOL_REPL_DEMAND_CUST_SP;
87 x_return_status := fnd_api.g_ret_sts_error;
88 fnd_msg_pub.count_and_get(p_count => x_msg_count,
89 p_data => x_msg_data);
90 IF (l_debug = 1) THEN
91 print_debug('Exiting GET_CONSOL_REPL_DEMAND_CUST - Execution error: ' ||
92 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
93 END IF;
94
95 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
96 ROLLBACK TO GET_CONSOL_REPL_DEMAND_CUST_SP;
97 x_return_status := fnd_api.g_ret_sts_unexp_error;
98 fnd_msg_pub.count_and_get(p_count => x_msg_count,
99 p_data => x_msg_data);
100 IF (l_debug = 1) THEN
101 print_debug('Exiting GET_CONSOL_REPL_DEMAND_CUST - Unexpected error: ' ||
102 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
103 END IF;
104
105 WHEN OTHERS THEN
106 ROLLBACK TO GET_CONSOL_REPL_DEMAND_CUST_SP;
107 x_return_status := fnd_api.g_ret_sts_unexp_error;
108 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
109 -- fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
110 fnd_msg_pub.add_exc_msg('WMS_REPL_CUSTOM_APIS_PUB','GET_CONSOL_REPL_DEMAND_CUST');
111
112 END IF;
113 fnd_msg_pub.count_and_get(p_count => x_msg_count,
114 p_data => x_msg_data);
115 IF (l_debug = 1) THEN
116 print_debug('Exiting GET_CONSOL_REPL_DEMAND_CUST - Others exception: ' ||
117 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
118 END IF;
119
120
121 END GET_CONSOL_REPL_DEMAND_CUST;
122
123 END WMS_REPL_CUSTOM_APIS_PUB;