eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.WSH_SC_DETAILS_REQUIRED_PKG

Source


1 PACKAGE BODY WSH_SC_DETAILS_REQUIRED_PKG as
2 /* $Header: WSHSCDRB.pls 115.4 99/07/16 08:20:41 porting ship $ */
3 
4 --
5 -- Package
6 --   WSH_SC_DETAILS_REQUIRED_PKG
7 -- Purpose
8 --   Inventory Details Required evaluations for line/delivery/departure in SC
9 --   Called for line level in post-query of form or Open Interface
10 --   or from form at departure or delivery level
11 -- History
12 --      04-mar-97 troveda	Created
13 --
14 
15   --
16   -- PUBLIC FUNCTIONS/PROCEDURES
17   --
18 
19 
20   --
21   -- Name
22   --   Details Required
23   -- Purpose
24   --   Evaluates if a particular batch/header/line requires details or not
25   -- Arguments
26   -- X_Entity_id	either Picking_line_detail_id, Delivery_id or Departure_id
27   -- X_Mode		either LINE, DELIVERY, DEPARTURE
28   -- X_Action		BACKORDER (ignores lines with 0 SQ): CONFIRM (all lines)
29   -- X_Reservations 	Y or N
30   -- X_Warehouse_id     required - use profile mfg_organization_id
31   -- X_Order_Category	either P for internal order or anything else
32   -- X_Details_Req      Y or N
33   --
34   -- Notes
35   -- we no longer check if the picking_header status = OPEN. We assume that
36   -- this routine is only called if it is OPEN and even if its isnt, there
37   -- is no harm in returning the details_required flag.
38 
39 
40 
41   PROCEDURE Details_Required(X_Entity_id		IN     NUMBER,
42 			     X_Mode			IN     VARCHAR2,
43 			     X_Action			IN     VARCHAR2,
44 			     X_Reservations 	    	IN     VARCHAR2,
45 			     X_Warehouse_id          	IN     NUMBER,
46 			     X_Order_Category		IN     VARCHAR2,
47 			     X_Details_Req              IN OUT VARCHAR2
48                              )
49   IS
50     Sql_Stmt		VARCHAR2(5000);
51     X_Cursor 		NUMBER;
52     X_Rows 		NUMBER;
53     X_Dummy		NUMBER;
54     X_Stmt_Num		NUMBER;
55 
56   BEGIN
57 -- Initialize the Details Required Flag here
58     X_Details_Req := 'N';
59 
60 /*
61    There is at least one detail requiring a subinventory and
62    reservations are OFF or the detail has been autoscheduled
63    and for a line with some shipped quantity and no primary
64    subinventories exist.
65 */
66 
67 
68 
69 SQL_Stmt :=           'SELECT ''Y'' ';
70 SQL_Stmt := SQL_Stmt||'FROM so_picking_lines_all sopls,';
71 SQL_Stmt := SQL_Stmt||'     so_picking_line_details sopld ';
72 SQL_Stmt := SQL_Stmt||'WHERE sopls.picking_line_id  = sopld.picking_line_id';
73 if X_MODE = 'LINE' then
74 SQL_Stmt := SQL_Stmt||' AND  sopld.picking_line_detail_id =:X_ENTITY_ID';
75 elsif X_MODE = 'DELIVERY'  then
76 SQL_Stmt := SQL_Stmt||' AND  sopld.delivery_id     = :X_ENTITY_ID';
77 elsif X_MODE = 'DEPARTURE' then
78 SQL_Stmt := SQL_Stmt||' AND  sopld.departure_id    = :X_ENTITY_ID';
79 else
80   return;
81 end if;
82 SQL_Stmt := SQL_Stmt||' AND nvl(sopld.warehouse_id,sopls.warehouse_id) = :X_WAREHOUSE_ID';
83 SQL_Stmt := SQL_Stmt||' AND nvl(sopld.shipped_quantity, decode(:X_Action, ';
84 SQL_Stmt := SQL_Stmt||'		   ''CONFIRM'', sopld.requested_quantity, ';
85 SQL_Stmt := SQL_Stmt||'		   ''BACKORDER'', 0)) > 0 ';
86 SQL_Stmt := SQL_Stmt||' AND (nvl(sopld.autoscheduled_flag,''Y'')=''Y''';
87 SQL_Stmt := SQL_Stmt||'	 OR :X_Reservations = ''N'')';
88 SQL_Stmt := SQL_Stmt||' AND sopld.subinventory is NULL';
89 SQL_Stmt := SQL_Stmt||' AND sopls.picking_header_id >0 ';
90 SQL_Stmt := SQL_Stmt||' AND NOT EXISTS';
91 SQL_Stmt := SQL_Stmt||'  (SELECT ''Default Sub Exists'' ';
92 SQL_Stmt := SQL_Stmt||'    FROM mtl_item_sub_defaults mtlisd, ';
93 SQL_Stmt := SQL_Stmt||'	 mtl_secondary_inventories mtlsub ';
94 SQL_Stmt := SQL_Stmt||'    WHERE mtlisd.organization_id = :X_WAREHOUSE_ID ';
95 SQL_Stmt := SQL_Stmt||'    AND mtlisd.inventory_item_id = sopls.inventory_item_id ';
96 SQL_Stmt := SQL_Stmt||'    AND mtlisd.default_type = 1 ';
97 SQL_Stmt := SQL_Stmt||'    AND mtlsub.organization_id   = :X_WAREHOUSE_ID ';
98 SQL_Stmt := SQL_Stmt||'    AND mtlsub.secondary_inventory_name = mtlisd.subinventory_code ';
99 SQL_Stmt := SQL_Stmt||'    AND mtlsub.quantity_tracked = 1 ';
100 SQL_Stmt := SQL_Stmt||'    AND sysdate <= nvl(mtlsub.disable_date,sysdate))';
101 
102 	X_Stmt_Num := 40;
103      X_Cursor := dbms_sql.open_cursor;
104 
105 	X_Stmt_Num := 50;
106      dbms_sql.parse(X_Cursor,SQL_Stmt,dbms_sql.v7);
107 
108 	X_Stmt_Num := 60;
109      dbms_sql.bind_variable(X_Cursor,'X_Entity_Id',X_Entity_Id);
110      dbms_sql.bind_variable(X_Cursor,'X_Warehouse_id',X_Warehouse_id);
111      dbms_sql.bind_variable(X_Cursor,'X_Reservations',X_Reservations);
112      dbms_sql.bind_variable(X_Cursor,'X_Action',X_Action);
113 
114 	X_Stmt_Num := 80;
115      X_Rows := dbms_sql.execute(X_Cursor);
116 
117 	X_Stmt_Num := 90;
118      X_Rows := dbms_sql.fetch_rows(X_Cursor);
119 
120         X_Stmt_Num := 110;
121      IF X_Rows <> 0 THEN
122 	X_Details_Req := 'Y';
123 	X_Stmt_Num := 100;
124         IF dbms_sql.is_open(X_Cursor) THEN
125 	   dbms_sql.close_cursor(X_Cursor);
126         END IF;
127      ELSE
128 	X_Stmt_Num := 100;
129         IF dbms_sql.is_open(X_Cursor) THEN
130 	   dbms_sql.close_cursor(X_Cursor);
131         END IF;
132 
133 /*
134    Location is required or missing for one detail that is
135    under locator control and no default locator is defined
136    for the item in inventory.
137    Join sub to default sub to get correct loc.
138 */
139 
140 
141 SQL_Stmt :=           'SELECT ''Y'' ';
142 SQL_Stmt := SQL_Stmt||'FROM mtl_parameters mtlpar';
143 SQL_Stmt := SQL_Stmt||'    ,mtl_secondary_inventories mtlsin ';
144 SQL_Stmt := SQL_Stmt||',mtl_item_sub_defaults mtlisd ';
145 SQL_Stmt := SQL_Stmt||',mtl_secondary_inventories mtlsub ';
146 SQL_Stmt := SQL_Stmt||',mtl_system_items mtlsis ';
147 SQL_Stmt := SQL_Stmt||',so_picking_line_details sopld ';
148 SQL_Stmt := SQL_Stmt||',so_picking_lines_all sopls ';
149 SQL_Stmt := SQL_Stmt||'WHERE sopls.picking_line_id  = sopld.picking_line_id';
150 if X_MODE = 'LINE' then
151 SQL_Stmt := SQL_Stmt||' AND  sopld.picking_line_detail_id =:X_ENTITY_ID';
152 elsif X_MODE = 'DELIVERY'  then
153 SQL_Stmt := SQL_Stmt||' AND  sopld.delivery_id     = :X_ENTITY_ID';
154 elsif X_MODE = 'DEPARTURE' then
155 SQL_Stmt := SQL_Stmt||' AND  sopld.departure_id    = :X_ENTITY_ID';
156 else
157   return;
158 end if;
159 SQL_Stmt := SQL_Stmt||' AND nvl(sopld.shipped_quantity, decode(:X_Action, ';
160 SQL_Stmt := SQL_Stmt||'	   ''CONFIRM'',  sopld.requested_quantity, ';
161 SQL_Stmt := SQL_Stmt||'	   ''BACKORDER'', 0)) > 0 ';
162 SQL_Stmt := SQL_Stmt||' AND (:X_Reservations = ''N'' ';
163 SQL_Stmt := SQL_Stmt||'     OR   NVL(sopld.autoscheduled_flag,''Y'')= ''Y'')';
164 SQL_Stmt := SQL_Stmt||' AND mtlpar.organization_id  = :X_WAREHOUSE_ID';
165 SQL_Stmt := SQL_Stmt||' AND sopls.picking_header_id >0 ';
166 SQL_Stmt := SQL_Stmt||' AND mtlsin.organization_id  = mtlpar.organization_id';
167 SQL_Stmt := SQL_Stmt||' AND mtlsin.secondary_inventory_name = NVL(sopld.subinventory,mtlisd.subinventory_code)';
168 SQL_Stmt := SQL_Stmt||' AND mtlisd.organization_id   = :X_WAREHOUSE_ID';
169 SQL_Stmt := SQL_Stmt||' AND mtlisd.inventory_item_id (+)   = sopls.inventory_item_id';
170 SQL_Stmt := SQL_Stmt||' AND mtlisd.default_type  = 1';
171 SQL_Stmt := SQL_Stmt||' AND mtlsub.secondary_inventory_name  = ';
172 SQL_Stmt := SQL_Stmt||'                       nvl(mtlisd.subinventory_code, mtlsub.secondary_inventory_name)';
173 SQL_Stmt := SQL_Stmt||' AND mtlsub.organization_id  = :X_WAREHOUSE_ID';
174 SQL_Stmt := SQL_Stmt||' AND mtlsub.quantity_tracked = 1';
175 SQL_Stmt := SQL_Stmt||' AND trunc(sysdate) <= nvl(mtlsub.disable_date, trunc(sysdate))';
176 SQL_Stmt := SQL_Stmt||' AND mtlsis.organization_id + 0  = mtlpar.organization_id';
177 SQL_Stmt := SQL_Stmt||' AND mtlsis.inventory_item_id = sopls.inventory_item_id + 0';
178 SQL_Stmt := SQL_Stmt||' AND decode(NVL( mtlpar.stock_locator_control_code,1), ';
179 SQL_Stmt := SQL_Stmt||'   	    1,''N'', 2,''Y'', 3,''Y'', 4,';
180 SQL_Stmt := SQL_Stmt||'            decode(NVL(mtlsin.locator_type,1), 1,''N'', 2,''Y'', 3, ''Y'', 4,''N'',5,';
181 SQL_Stmt := SQL_Stmt||'            decode(NVL( mtlsis.location_control_code,1), ';
182 SQL_Stmt := SQL_Stmt||'                        1,''N'', 2,''Y'', 3,''Y'',''N''),''N'')) = ''Y''';
183 SQL_Stmt := SQL_Stmt||' AND sopld.inventory_location_id is NULL';
184 SQL_Stmt := SQL_Stmt||' AND not exists';
185 SQL_Stmt := SQL_Stmt||'  (SELECT ''default loc for this sub exists''';
186 SQL_Stmt := SQL_Stmt||'   FROM   mtl_item_loc_defaults mtldl,';
187 SQL_Stmt := SQL_Stmt||'          mtl_secondary_inventories mtlsub,';
188 SQL_Stmt := SQL_Stmt||'          mtl_item_sub_defaults mtlisd';
189 SQL_Stmt := SQL_Stmt||'   WHERE  mtldl.inventory_item_id =  sopls.inventory_item_id';
190 SQL_Stmt := SQL_Stmt||'   AND    mtldl.organization_id =  :X_WAREHOUSE_ID';
191 SQL_Stmt := SQL_Stmt||'   AND    mtldl.default_type = 1';
192 SQL_Stmt := SQL_Stmt||'   AND    mtldl.subinventory_code = mtlsub.secondary_inventory_name';
193 SQL_Stmt := SQL_Stmt||'   AND    mtlisd.organization_id =  :X_WAREHOUSE_ID';
194 SQL_Stmt := SQL_Stmt||'   AND    mtlisd.inventory_item_id = sopls.inventory_item_id';
195 SQL_Stmt := SQL_Stmt||'   AND    mtlisd.default_type = 1';
196 SQL_Stmt := SQL_Stmt||'   AND    mtlsub.organization_id = mtlisd.organization_id';
197 SQL_Stmt := SQL_Stmt||'   AND    mtlsub.secondary_inventory_name = mtlisd.subinventory_code';
198 SQL_Stmt := SQL_Stmt||'   AND    mtlsub.quantity_tracked = 1';
199 SQL_Stmt := SQL_Stmt||'   AND    trunc(sysdate) <= nvl( mtlsub.disable_date, trunc(sysdate)))';
200 
201 
202      X_Stmt_Num := 120;
203      X_Cursor := dbms_sql.open_cursor;
204 
205 	X_Stmt_Num := 130;
206      dbms_sql.parse(X_Cursor,SQL_Stmt,dbms_sql.v7);
207 
208 	X_Stmt_Num := 140;
209      dbms_sql.bind_variable(X_Cursor,'X_Entity_Id',X_Entity_Id);
210      dbms_sql.bind_variable(X_Cursor,'X_Reservations',X_Reservations);
211      dbms_sql.bind_variable(X_Cursor,'X_Warehouse_id',X_Warehouse_id);
212      dbms_sql.bind_variable(X_Cursor,'X_Action',X_Action);
213 
214 	X_Stmt_Num := 160;
215      X_Rows := dbms_sql.execute(X_Cursor);
216 
217 	X_Stmt_Num := 170;
218      X_Rows := dbms_sql.fetch_rows(X_Cursor);
219 
220         X_Stmt_Num := 180;
221      IF X_Rows <> 0 THEN
222 	X_Details_Req := 'Y';
223 	X_Stmt_Num := 190;
224         IF dbms_sql.is_open(X_Cursor) THEN
225 	   dbms_sql.close_cursor(X_Cursor);
226         END IF;
227      ELSE
228 	X_Stmt_Num := 200;
229         IF dbms_sql.is_open(X_Cursor) THEN
230 	   dbms_sql.close_cursor(X_Cursor);
231         END IF;
232 
233 
234 /*
235    Revision, Lot or serial number information is required by at least
236    one detail and is missing
237 */
238 
239 
240 SQL_Stmt :=           'SELECT ''Y''';
241 if X_MODE = 'DELIVERY'  then
242 SQL_Stmt := SQL_Stmt||'  FROM (select  pl.picking_line_id, pld.warehouse_id, pld.shipped_quantity, ';
243 SQL_Stmt := SQL_Stmt||'                pld.requested_quantity, pld.autoscheduled_flag, pld.revision, ';
244 SQL_Stmt := SQL_Stmt||'                pld.lot_number, h.order_category, pld.serial_number, pld.transaction_temp_id ';
245 SQL_Stmt := SQL_Stmt||'        from so_headers_all h, so_lines_all l, so_picking_lines_all pl, ';
246 SQL_Stmt := SQL_Stmt||'             so_picking_line_details pld ';
247 SQL_Stmt := SQL_Stmt||'        where pld.picking_line_id = pl.picking_line_id ';
248 SQL_Stmt := SQL_Stmt||'        and pl.order_line_id = l.line_id ';
249 SQL_Stmt := SQL_Stmt||'        and l.header_id = h.header_id ';
250 SQL_Stmt := SQL_Stmt||'        and pld.delivery_id = :X_ENTITY_ID) sopld';
251 elsif X_MODE = 'DEPARTURE' then
252 SQL_Stmt := SQL_Stmt||'  FROM (select pl.picking_line_id, pld.warehouse_id, pld.shipped_quantity, ';
253 SQL_Stmt := SQL_Stmt||'               pld.requested_quantity, pld.autoscheduled_flag, pld.revision, ';
254 SQL_Stmt := SQL_Stmt||'               pld.lot_number, h.order_category, pld.serial_number, pld.transaction_temp_id ';
255 SQL_Stmt := SQL_Stmt||'        from so_headers_all h, so_lines_all l, so_picking_lines_all pl, ';
256 SQL_Stmt := SQL_Stmt||'             so_picking_line_details pld ';
257 SQL_Stmt := SQL_Stmt||'        where pld.picking_line_id = pl.picking_line_id ';
258 SQL_Stmt := SQL_Stmt||'        and pl.order_line_id = l.line_id ';
259 SQL_Stmt := SQL_Stmt||'        and l.header_id = h.header_id ';
260 SQL_Stmt := SQL_Stmt||'        and pld.departure_id = :X_ENTITY_ID) sopld';
261 else
262 SQL_Stmt := SQL_Stmt||'  FROM so_picking_line_details sopld';
263 end if;
264 SQL_Stmt := SQL_Stmt||'      ,mtl_system_items mtlsis';
265 SQL_Stmt := SQL_Stmt||'      ,so_picking_lines_all sopls ';
266 SQL_Stmt := SQL_Stmt||'WHERE sopls.picking_line_id  = sopld.picking_line_id';
267 if X_MODE = 'LINE' then
268 SQL_Stmt := SQL_Stmt||' AND  sopld.picking_line_detail_id =:X_ENTITY_ID';
269 elsif X_MODE IN ('DEPARTURE', 'DELIVERY')  then
270 null;
271 else
272   return;
273 end if;
274 SQL_Stmt := SQL_Stmt||'  AND  NVL(sopld.warehouse_id, sopls.warehouse_id)   = :X_WAREHOUSE_ID';
275 SQL_Stmt := SQL_Stmt||'  AND  nvl(sopld.shipped_quantity, decode(:X_Action, ';
276 SQL_Stmt := SQL_Stmt||'			      ''CONFIRM'', sopld.requested_quantity, ';
277 SQL_Stmt := SQL_Stmt||'			      ''BACKORDER'', 0)) > 0 ';
278 SQL_Stmt := SQL_Stmt||'  AND (:X_Reservations = ''N'' ';
279 SQL_Stmt := SQL_Stmt||'       OR  NVL(sopld.autoscheduled_flag,''Y'')= ''Y'')';
280 SQL_Stmt := SQL_Stmt||'  AND mtlsis.organization_id + 0  = :X_WAREHOUSE_ID';
281 SQL_Stmt := SQL_Stmt||' AND sopls.picking_header_id >0 ';
282 SQL_Stmt := SQL_Stmt||'  AND mtlsis.inventory_item_id   = sopls.inventory_item_id + 0';
283 SQL_Stmt := SQL_Stmt||'  AND (  (decode( mtlsis.revision_qty_control_code,2,''Y'',''N'') = ''Y'' ';
284 SQL_Stmt := SQL_Stmt||'          AND sopld.revision is NULL)';
285 SQL_Stmt := SQL_Stmt||'       OR(decode(mtlsis.lot_control_code,2,''Y'',3,''Y'',''N'') = ''Y''';
286 SQL_Stmt := SQL_Stmt||'	         AND sopld.lot_number is NULL)';
287 SQL_Stmt := SQL_Stmt||'       OR(decode(mtlsis.serial_number_control_code,2,''Y'',5,''Y'',';
288 if X_MODE IN ('DELIVERY','DEPARTURE')  then
289 SQL_Stmt := SQL_Stmt||'	       6,decode(sopld.order_category,''P'',''N'',''Y''), ''N'') = ''Y''';
290 else
291 SQL_Stmt := SQL_Stmt||'	       6,decode(:X_Order_Category,''P'',''N'',''Y''), ''N'') = ''Y''';
292 end if;
293 
294 --if serial number is null also ensure transaction_temp_id is null
295 --if transaction_temp_id exists, make sure quantity in msnt is the shipped quantity
296 SQL_Stmt := SQL_Stmt||'	         AND (  (sopld.serial_number is NULL ';
297 SQL_Stmt := SQL_Stmt||'                         and (sopld.transaction_temp_id is NULL';
298 SQL_Stmt := SQL_Stmt||'                              or (not exists (select null from mtl_serial_numbers_temp';
299 SQL_Stmt := SQL_Stmt||'                                              where transaction_temp_id = sopld.transaction_temp_id))))';
300 
301 SQL_Stmt := SQL_Stmt||'               or( (sopld.serial_number is NULL or sopld.shipped_quantity>1)';
302 SQL_Stmt := SQL_Stmt||'                   and sopld.shipped_quantity <> ';
303 
304 -- here we select the ranges of serial numbers ie: select sum(nvl(to_sn,from_sn) - from_sn) +1
305 -- it is a little more complex because sn are varchars so we have to strip of the leading prefixes.
306 -- Bug 848584 : Added nvl before substr (2 places)
307 SQL_Stmt := SQL_Stmt||'                    (select nvl(sum(to_number(nvl(substr(nvl(to_serial_number,fm_serial_number),';
308 SQL_Stmt := SQL_Stmt||'				nvl(length(rtrim(nvl(to_serial_number,fm_serial_number), ''0123456789'')),0) + 1),0)) -';
309 SQL_Stmt := SQL_Stmt||'	to_number(nvl(substr(fm_serial_number,';
310 SQL_Stmt := SQL_Stmt||'				nvl(length(rtrim(fm_serial_number, ''0123456789'')),0) + 1),0)) + 1),0) ';
311 SQL_Stmt := SQL_Stmt||'                     from mtl_serial_numbers_temp msnt';
312 SQL_Stmt := SQL_Stmt||'                     where msnt.transaction_temp_id = sopld.transaction_temp_id)))))';
313 
314 
315 
316 	X_Stmt_Num := 210;
317      X_Cursor := dbms_sql.open_cursor;
318 
319 	X_Stmt_Num := 220;
320      dbms_sql.parse(X_Cursor,SQL_Stmt,dbms_sql.v7);
321 
322 	X_Stmt_Num := 230;
323      dbms_sql.bind_variable(X_Cursor,'X_Entity_Id',X_Entity_Id);
324      dbms_sql.bind_variable(X_Cursor,'X_Reservations',X_Reservations);
325      dbms_sql.bind_variable(X_Cursor,'X_Warehouse_id',X_Warehouse_id);
326      dbms_sql.bind_variable(X_Cursor,'X_Action',X_Action);
327      if X_MODE not in ('DEPARTURE','DELIVERY') then
328        dbms_sql.bind_variable(X_Cursor,'X_Order_Category',X_Order_Category);
329      end if;
330 
331 	X_Stmt_Num := 250;
332      X_Rows := dbms_sql.execute(X_Cursor);
333 
334 	X_Stmt_Num := 260;
335      X_Rows := dbms_sql.fetch_rows(X_Cursor);
336 
337         X_Stmt_Num := 270;
338      IF X_Rows <> 0 THEN
339 	X_Details_Req := 'Y';
340 	X_Stmt_Num := 280;
341         IF dbms_sql.is_open(X_Cursor) THEN
342 	   dbms_sql.close_cursor(X_Cursor);
343         END IF;
344      ELSE
345 	X_Details_Req := 'N';
346      END IF;
347     END IF;
348    END IF;
349 
350      IF dbms_sql.is_open(X_Cursor) THEN
351 	dbms_sql.close_cursor(X_Cursor);
352      END IF;
353 
354   EXCEPTION
355     WHEN OTHERS THEN
356 	FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
360  	APP_EXCEPTION.Raise_Exception;
357 	FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_DETAILS_REQUIRED_PKG.Details_Required');
358 	FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
359 	FND_MESSAGE.Set_Token('ORA_TEXT',X_Stmt_Num ||':'|| SQL_Stmt);
361   END Details_Required;
362 
363 END WSH_SC_DETAILS_REQUIRED_PKG;