1 PACKAGE BODY WSH_ITM_CUSTOM_PROCESS AS
2 /* $Header: WSHITPPB.pls 120.3 2011/01/14 20:35:12 rvarghes ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ITM_CUSTOM_PROCESS';
5
6 /*===========================================================================+
7 | PROCEDURE |
8 | PRE_PROCESS_REQUEST |
9 | |
10 | DESCRIPTION |
11 | This procedure is called from Submit Deleiveries for |
12 | Screening concurrent program which populates the interface |
13 | table. |
14 | Customers are given the flexibilty of adding additional |
15 | attributes to the Interface table/Additional Logic |
16 | in the code as per their Requirements through THIS procedure |
17 | |
18 +===========================================================================*/
19
20 PROCEDURE PRE_PROCESS_WSH_REQUEST (
21 p_request_control_id IN NUMBER
22 )IS
23
24 l_debug_on BOOLEAN;
25 --
26 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRE_PROCESS_WSH_REQUEST';
27 --
28 --
29 --
30 BEGIN
31 --
32 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
33 --
34 IF l_debug_on IS NULL
35 THEN
36 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
37 END IF;
38 --
39 -- Debug Statements
40 --
41 IF l_debug_on THEN
42 WSH_DEBUG_SV.push('Entering' || l_module_name);
43 END IF;
44
45 -- Debug Statements
46 --
47 IF l_debug_on THEN
48 WSH_DEBUG_SV.pop('Exiting' || l_module_name);
49 END IF;
50 --
51
52 END PRE_PROCESS_WSH_REQUEST;
53
54
55 /*===========================================================================+
56 | PROCEDURE |
57 | POST_PROCESS_REQUEST |
58 | |
59 | DESCRIPTION |
60 | This procedure is called when response is sent for Delivery |
61 | Screening request and is called via XML gateway. |
62 | Customers are given the flexibilty of adding Additional |
63 | Logic in the code as per their Requirements through THIS |
64 | procedure |
65 +===========================================================================*/
66
67 PROCEDURE POST_PROCESS_WSH_REQUEST (
68 p_request_control_id IN NUMBER
69 )IS
70
71 --
72 l_debug_on BOOLEAN;
73 --
74 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POST_PROCESS_WSH_REQUEST';
75 --
76 BEGIN
77 --
78 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
79 --
80 IF l_debug_on IS NULL
81 THEN
82 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
83 END IF;
84 --
85 -- Debug Statements
86 --
87 IF l_debug_on THEN
88 WSH_DEBUG_SV.push(' Entering ' || l_module_name);
89 END IF;
90
91 --
92 -- Debug Statements
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.pop('Exiting' || l_module_name);
96 END IF;
97 --
98
99 END POST_PROCESS_WSH_REQUEST;
100
101
102 /*===========================================================================+
103 | PROCEDURE |
104 | PRE_PROCESS_ONT_REQUEST |
105 | Parameters : IN p_request_control_id |
106 | IN p_line_id |
107 | DESCRIPTION |
108 | This procedure is called at the time of Booking Order |
109 | with ITM check included in the Workflow |
110 | table. |
111 | Customers are given the flexibilty of adding additional |
112 | attributes to the Interface table/Additional Logic |
113 | in the code as per their Requirements through THIS procedure |
114 | |
115 +===========================================================================*/
116
117 PROCEDURE PRE_PROCESS_ONT_REQUEST(
118 p_request_control_id IN NUMBER,
119 p_line_id IN NUMBER
120 ) IS
121
122 --
123 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
124 --
125 BEGIN
126
127 IF l_debug_level > 0 THEN
128 oe_debug_pub.add('Entering WSH_ITM_CUSTOM_PROCESS.PRE_PROCESS_ONT_REQUEST....' , 4 ) ;
129 END IF;
130
131
132 IF l_debug_level > 0 THEN
133 oe_debug_pub.add('Exiting WSH_ITM_CUSTOM_PROCESS.PRE_PROCESS_ONT_REQUEST....',4);
134 END IF;
135
136 Exception
137 WHEN NO_DATA_FOUND THEN
138 NULL;
139 END PRE_PROCESS_ONT_REQUEST;
140
141
142
143 /*===========================================================================+
144 | PROCEDURE |
145 | POST_PROCESS_ONT_REQUEST |
146 | Parameters : IN p_request_control_id |
147 | IN p_line_id |
148 | DESCRIPTION |
149 | This procedure is called when response is sent for |
150 | Order/DP screening |
151 | Customers are given the flexibilty of adding additional |
152 | attributes to the Interface table/Additional Logic |
153 | in the code as per their Requirements through THIS procedure |
154 | |
155 +===========================================================================*/
156
157 PROCEDURE POST_PROCESS_ONT_REQUEST(
158 p_request_control_id IN NUMBER
159 ) IS
160
161 --
162 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
163 --
164 BEGIN
165
166 IF l_debug_level > 0 THEN
167 oe_debug_pub.add('Entering WSH_ITM_CUSTOM_PROCESS.POST_PROCESS_ONT_REQUEST....' , 4 ) ;
168 END IF;
169
170 IF l_debug_level > 0 THEN
171 oe_debug_pub.add('Exiting WSH_ITM_CUSTOM_PROCESS.POST_PROCESS_ONT_REQUEST....',4);
172 END IF;
173
174 Exception
175 WHEN NO_DATA_FOUND THEN
176 NULL;
177 END POST_PROCESS_ONT_REQUEST;
178
179 -- Bug 7284454 - Added proc PRE_PROCESS_PTO, to provide option to customer
180 -- to send all components of PTO model in Single Request XML
181
182 /*==================================================================================+
183 | PROCEDURE : |
184 | GRP_MODEL_LINES_IN_SINGLE_REQ |
185 | |
186 | DESCRIPTION |
187 | This procedure is called before task is created, if Return parameter |
188 | is set to 'Y' then all Components of a PTO Model will be sent in |
189 | a single Request XML to avoid 'Locking Contention' problem. If it |
190 | set to 'N' then each component of a PTO Model will be sent in |
191 | separate Request XML. |
192 | |
193 | Customer can set it to 'Y', if the ITM Vendor supports Multiple |
194 | line screening (all Components of PTO Model) in Single Request XML. |
195 | |
196 +===================================================================================*/
197
198 FUNCTION GRP_MODEL_LINES_IN_SINGLE_REQ Return Varchar2
199
200 IS
201
202 l_debug_on BOOLEAN;
203 --
204 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRE_PROCESS_PTO';
205 --
206 BEGIN
207 --
208 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
209 --
210 IF l_debug_on IS NULL
211 THEN
212 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
213 END IF;
214 --
215 -- Debug Statements
216 --
217 IF l_debug_on THEN
218 WSH_DEBUG_SV.push('Entering' || l_module_name);
219 END IF;
220
221 --
222 -- Debug Statements
223 --
224 IF l_debug_on THEN
225 WSH_DEBUG_SV.pop('Exiting' || l_module_name);
226 END IF;
227 --
228 /* - By default Return variable is set to 'N', if needed customer
229 can set it to 'Y'
230 - Return variable should not be more than one character */
231
232 return 'N';
233
234 END GRP_MODEL_LINES_IN_SINGLE_REQ;
235
236
237 /*==================================================================================+
238 | FUNCTION : |
239 | GET_TOP_MODEL_INFO |
240 | |
241 | DESCRIPTION |
242 | This function returns the l_sql_str, which is a SQL select string. |
243 | This l_sql_str is by default set to return NULL. If desired this can |
244 | be changed, to return the SQL select statement like 'Select ....from |
245 | custom_table(s) ..'. |
246 | |
247 | NOTE : The SQL string that you return in l_sql_str should be compatible with the |
248 | one in the Example below. Also, the Max allowed length for l_sql_str is |
249 | 5000 Characters/VARCHAR2(5000). |
250 | |
251 | Example: Below the XXX_ITM_TOP_MODEL_INFO is an example of a Custom Table that |
252 | can be Pre-Populated during the Delivery Export Screening by adding |
253 | your required code in the above PROCEDURE PRE_PROCESS_WSH_REQUEST (); |
254 | The permissible columns (like INVENTORY_ITEM_ID ) are listed below. |
255 | |
256 | l_sql_str can be used to Return the Required SQL Statement like below : |
257 | l_sql_str := |
258 | ' SELECT |
259 | TMI.INVENTORY_ITEM_ID, TMI.ORGANIZATION_ID, TMI.ORGANIZATION_CODE, |
260 | TMI.OPERATING_UNIT, TMI.PRODUCT_CODE , TMI.ITEM_TYPE,TMI.UNIT_OF_MEASURE,|
261 | TMI.INCOMING_CURRENCY, |
262 | TMI.TOP_MODEL_LINE_ID, -- TopMost/GrandParent Source Line Id in the BOM |
263 | TMI.PARENT_LINE_ID, -- Source Line Id of the Parent |
264 | TMI.LINE_ID, -- Source Line Id of the Delivery Detail involved |
265 | TMI.LINE_NUMBER, -- Source Line Number of the Del.Detail involved |
266 | TMI.ORDERED_QUANTITY ORDER_QTY, -- Source Ordered Quantity |
267 | TMI.ATO_LINE_ID , -- Source Line id if the Source Line is an ATO |
268 | TMI.ATO_FLAG, -- 'Y' , if the line involved is an ATO |
269 | TMI.OPT_FLAG, -- 'Y', if it an Option |
270 | TMI.SHIPPABLE_FLAG, -- 'Y', if the line is Shippable |
271 | TMI.ITEM_TYPE_CODE, TMI.UNIT_LIST_PRICE, TMI.UNIT_SELLING_PRICE |
272 | FROM |
273 | XXX_ITM_TOP_MODEL_INFO TMI |
274 | WHERE |
275 | TMI.REQUEST_CONTROL_ID = RC.REQUEST_CONTROL_ID |
276 | AND TMI.ORIGINAL_SYSTEM_REFERENCE = SHPMNTITM.DELIVERY_ID |
277 | AND TMI.ORIGINAL_SYSTEM_LINE_REFERENCE = SHPMNTITM.DELIVERY_DETAIL_ID'; |
278 | |
279 | NOTE: The Query during runtime would be restricted by the Where Condition above |
280 | involving the REQUEST_CONTROL_ID (WSH_ITM_REQUEST_CONTROL table), |
281 | DELIVERY_ID (SHPMNTIM.DELIVERY_ID) AND |
282 | DELIVERY_DETAIL_ID (SHPMNTITM.DELIVERY_DETAIL_ID). | | |
283 | |
284 +===================================================================================*/
285
286 -- This Returns l_sql_str to MTCCAdapter.java pgm. to for formulate the SQL
287 -- Query to fetch the TopModel Releated Info. :
288 FUNCTION GET_TOP_MODEL_SQL Return Varchar2
289
290 IS
291
292 l_debug_on BOOLEAN;
293 --
294 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TOP_MODEL_SQL';
295 --
296 -- SQL String that needs to be Returned
297 l_sql_str VARCHAR2(5000);
298 --
299 BEGIN
300 --
301 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
302 --
303 IF l_debug_on IS NULL
304 THEN
305 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
306 END IF;
307 --
308 -- Debug Statements
309 --
310 IF l_debug_on THEN
311 WSH_DEBUG_SV.push('Entering' || l_module_name);
312 END IF;
313
314 /* - By default Return variable (l_sql_str) is set to Null, if needed customer
315 can set it to a SQL string */
316
317 l_sql_str := NULL;
318
319 return l_sql_str;
320
321 --
322 -- Debug Statements
323 --
324 IF l_debug_on THEN
325 WSH_DEBUG_SV.pop('Exiting' || l_module_name);
326 END IF;
327 --
328
329 END GET_TOP_MODEL_SQL;
330
331 END WSH_ITM_CUSTOM_PROCESS;
[Home] [Help]