1 PACKAGE BODY PJM_UEFF_ONHAND AS
2 /* $Header: PJMUEOHB.pls 120.1 2006/02/10 14:44:38 exlin noship $ */
3 --
4 -- Name : Onhand_Quantity
5 -- Pre-reqs : None
6 -- Function : This function returns onhand quantity for a specific
7 -- unit number and item/org/rev/subinv/locator/lot that
8 -- matches the unit number on the OE line
9 --
10 --
11 -- Parameters :
12 -- IN : X_source_line NUMBER
13 -- X_item_id NUMBER
14 -- X_organization_id NUMBER
15 -- X_revision VARCHAR2
16 -- X_subinventory VARCHAR2
17 -- X_locator_id NUMBER
18 -- X_lot_number VARCHAR2
19 -- X_lpn_id NUMBER BUG fix 2752979
20 -- X_cost_group_id NUMBER BUG fix 2752979
21 --
22 --
23 -- Returns : NUMBER
24 --
25 FUNCTION Onhand_Quantity
26 ( X_source_line IN NUMBER
27 , X_item_id IN NUMBER
28 , X_organization_id IN NUMBER
29 , X_revision IN VARCHAR2
30 , X_subinventory IN VARCHAR2
31 , X_locator_id IN NUMBER
32 , X_lot_number IN VARCHAR2
33 , X_lpn_id IN NUMBER
34 , X_cost_group_id IN NUMBER
35 ) RETURN NUMBER IS
36
37 L_quantity NUMBER;
38 L_unit_number PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE;
39
40 BEGIN
41
42 L_quantity := 0; -- BUG fix 2752979
43
44 if ( pjm_unit_eff.enabled = 'N' ) then
45 return ( 0 );
46 end if;
47
48 if ( pjm_unit_eff.unit_effective_item
49 ( X_item_id, X_organization_id ) = 'N' ) then
50 return ( 0 );
51 end if;
52
53 L_unit_number := PJM_UNIT_EFF.OE_Line_Unit_Number_Cached(X_source_line);
54 --Bug #4726150
55 --For requisition move orders, unit number is mandatory for unit effective items
56 --and hence we need to use this table to derive l_unit_number
57 IF (l_unit_number IS NULL) THEN
58 BEGIN
59 SELECT unit_number
60 INTO l_unit_number
61 FROM mtl_txn_request_lines_v
62 WHERE line_id = x_source_line
63 AND move_order_type = 1;
64 EXCEPTION
65 WHEN OTHERS THEN
66 l_unit_number := NULL;
67 END;
68 END IF;
69 --- End Bug #4726150
70
71 SELECT count(*)
72 INTO L_quantity
73 FROM mtl_serial_numbers msn
74 WHERE msn.current_status = 3
75 AND msn.inventory_item_id = X_item_id
76 AND msn.current_organization_id = X_organization_id
77 AND msn.current_subinventory_code = X_subinventory
78 AND nvl(msn.current_locator_id,-3113) = nvl(X_locator_id,-3113)
79 AND nvl(msn.revision,'!@$') = nvl(X_revision,'!@$')
80 AND nvl(msn.lot_number,'!@$') = nvl(X_lot_number,'!@$')
81 AND nvl(msn.end_item_unit_number,'!@$') = nvl(L_unit_number,'!@$')
82 AND nvl(msn.lpn_id, -3113) = nvl(X_lpn_id, -3113) -- BUG fix 2752979
83 AND nvl(msn.cost_group_id, -3113) = nvl(X_cost_group_id, -3113); -- BUG fix 2752979
84
85 return ( L_quantity );
86
87 EXCEPTION
88 when others then
89 return ( 0 );
90
91 END Onhand_Quantity;
92
93
94 --
95 -- Name : Txn_Quantity
96 -- Pre-reqs : None
97 -- Function : This function returns transaction quantity for a specific
98 -- transaction that matches the unit number on the OE line
99 --
100 --
101 -- Parameters :
102 -- IN : X_source_line NUMBER
103 -- X_trx_temp_id NUMBER
104 -- X_lot_number NUMBER
105 --
106 -- Returns : NUMBER
107 --
108 FUNCTION Txn_Quantity
109 ( X_source_line IN NUMBER
110 , X_trx_temp_id IN NUMBER
111 , X_lot_number IN VARCHAR2
112 , X_Fetch_From_DB IN VARCHAR2
113 , X_item_id IN NUMBER
114 , X_organization_id IN NUMBER
115 , X_src_type_id IN NUMBER
116 , X_trx_src_id IN NUMBER
117 , X_rcv_trx_id IN NUMBER
118 , X_trx_sign IN NUMBER
119 , X_trx_src_line_id IN NUMBER
120 ) RETURN NUMBER IS
121
122 L_item_id NUMBER;
123 L_organization_id NUMBER;
124 L_src_type_id NUMBER;
125 L_trx_src_id NUMBER;
126 L_trx_src_line_id NUMBER;
127 L_rcv_trx_id NUMBER;
128 L_trx_qty NUMBER;
129 L_trx_sign NUMBER;
130 L_unit_number PJM_UNIT_NUMBERS.UNIT_NUMBER%TYPE;
131
132 BEGIN
133
134 if ( pjm_unit_eff.enabled = 'N' ) then
135 return ( NULL );
136 end if;
137
138 if ( X_Fetch_From_DB = 'Y' ) then
139
140 SELECT inventory_item_id
141 , organization_id
142 , transaction_source_type_id
143 , transaction_source_id
144 , trx_source_line_id
145 , rcv_transaction_id
146 , sign(primary_quantity)
147 INTO L_item_id
148 , L_organization_id
149 , L_src_type_id
150 , L_trx_src_id
151 , L_trx_src_line_id
152 , L_rcv_trx_id
153 , L_trx_sign
154 FROM mtl_material_transactions_temp
155 WHERE transaction_temp_id = X_trx_temp_id;
156
157 else
158
159 L_item_id := X_item_id;
160 L_organization_id := X_organization_id;
161 L_src_type_id := X_src_type_id;
162 L_trx_src_id := X_trx_src_id;
163 L_trx_src_line_id := X_trx_src_line_id;
164 L_rcv_trx_id := X_rcv_trx_id;
165 L_trx_sign := X_trx_sign;
166
167 end if;
168
169 if ( pjm_unit_eff.unit_effective_item
170 ( L_item_id, L_organization_id ) = 'N' ) then
171 return ( NULL );
172 end if;
173
174 L_unit_number := PJM_UNIT_EFF.OE_Line_Unit_Number_Cached(X_source_line);
175
176 if ( L_src_type_id = 5 ) then
177
178 --
179 -- Transaction Source is WIP; get the unit number from the WIP header
180 --
181 if ( L_unit_number =
182 PJM_UNIT_EFF.WIP_Unit_Number(L_trx_src_id, L_organization_id) ) then
183 return ( NULL ); -- NULL means quantity from transaction
184 else
185 return ( 0 );
186 end if;
187
188 elsif ( L_src_type_id in (1, 7) ) then
189
190 --
191 -- Transaction Source is PO/Internal Req; get the unit number from PO
192 -- distribution or PO req distribution through RCV transaction
193 --
194 if ( L_unit_number = PJM_UNIT_EFF.RCV_Unit_Number(L_rcv_trx_id) ) then
195 return ( NULL ); -- NULL means quantity from transaction
196 else
197 return ( 0 );
198 end if;
199
200 else
201
202 --
203 -- Bug 2752979
204 --
205 -- If the transaction source is a sales order line, there may or
206 -- may not be corresponding serial number records; if so, then
207 -- consider the entire transaction to be against a single unit
208 -- number (stamped on the sales order line)
209 --
210 if ( L_src_type_id = 2 and L_trx_src_line_id is not null ) then
211
212 SELECT count(fm_serial_number)
213 INTO L_trx_qty
214 FROM mtl_serial_numbers_temp msnt
215 WHERE msnt.transaction_temp_id in (
216 SELECT X_trx_temp_id FROM dual
217 UNION ALL
218 SELECT serial_transaction_temp_id
219 FROM mtl_transaction_lots_temp
220 WHERE transaction_temp_id = X_trx_temp_id )
221 AND rownum = 1;
222
223 if ( L_trx_qty = 0 ) then
224 if ( L_unit_number =
225 PJM_UNIT_EFF.OE_Line_Unit_Number_Cached(L_trx_src_line_id) ) then
226 return ( NULL ); -- NULL means quantity from transaction
227 else
228 return ( 0 );
229 end if;
230 end if;
231 end if;
232
233 --
234 -- Transaction Source is other; get the unit number quantity from the
235 -- transaction serials
236 --
237 SELECT count(*) * L_trx_sign
238 INTO L_trx_qty
239 FROM mtl_serial_numbers_temp msnt
240 , mtl_serial_numbers msn
241 WHERE msnt.transaction_temp_id in (
242 SELECT X_trx_temp_id FROM dual
243 UNION ALL
244 SELECT serial_transaction_temp_id
245 FROM mtl_transaction_lots_temp
246 WHERE transaction_temp_id = X_trx_temp_id )
247 AND msn.serial_number >= msnt.fm_serial_number
248 AND msn.serial_number <= nvl(msnt.to_serial_number , msnt.fm_serial_number)
249 AND length(msn.serial_number) = length(msnt.fm_serial_number)
250 AND msn.inventory_item_id = L_item_id
251 AND nvl(msn.lot_number,'!@$') = nvl(X_lot_number,'!@$')
252 AND msn.end_item_unit_number = L_unit_number;
253
254 return ( L_trx_qty );
255
256 end if;
257
258 EXCEPTION
259 when others then
260 return ( NULL );
261
262 END Txn_Quantity;
263
264 END;
[Home] [Help]