1 PACKAGE BODY WIP_COMPONENT as
2 /* $Header: wipfnmtb.pls 120.2 2005/06/17 13:07:49 appldev $ */
3
4 /* Returns 1 if a component should be picked up in
5 populate temp based on the action, supply type, and quantities required
6 and issued.
7 Otherwise returns 2
8 */
9 FUNCTION IS_VALID
10 (p_transaction_action_id NUMBER,
11 p_wip_supply_type NUMBER,
12 p_required_quantity NUMBER,
13 p_quantity_issued NUMBER,
14 p_assembly_quantity NUMBER,
15 p_entity_type NUMBER) RETURN NUMBER IS
16 BEGIN
17
18 -- Apply to discrete and repetitive
19
20 -- No negative components for issue or replenish sub
21 IF p_transaction_action_id IN (1,2)
22 AND p_required_quantity <= 0 THEN
23 return(2);
24 END IF;
25
26 -- No positive components for negative issue, neg return
27 IF p_transaction_action_id in (33,34)
28 AND p_required_quantity >= 0 THEN
29 return(2);
30 END IF;
31
32 -- Only return positive components
33 -- We allow if it = 0 so you can return components added on
34 -- the fly.
35 IF p_transaction_action_id = 27
36 AND p_required_quantity < 0 THEN
37 return(2);
38 END IF;
39
40 -- For sub transfers, looking for pull components
41 IF p_wip_supply_type NOT IN (2,3)
42 AND p_transaction_action_id = 2 THEN
43 return(2);
44 END IF;
45
46 -- For everything else looking for push components
47 IF p_wip_supply_type <> 1
48 AND p_transaction_action_id <> 2 THEN
49 return(2);
50 END IF;
51
52 -- Discrete and lot based Only
53 /* Fix for bug 3115507: The following checks are to be applied for
54 EAM work orders also. */
55
56 IF p_entity_type in (1,5,6) THEN
57 -- Don't allow overissue for components
58 IF p_transaction_action_id = 1 AND p_assembly_quantity IS NULL
59 AND p_required_quantity <= p_quantity_issued THEN
60 return(2);
61 END IF;
62
63 -- Don't allow overissue for negative components
64 IF p_transaction_action_id = 33 AND p_assembly_quantity IS NULL
65 AND p_required_quantity >= p_quantity_issued THEN
66 return(2);
67 END IF;
68
69 -- Only return components that were issued
70 IF p_transaction_action_id = 27
71 AND p_quantity_issued <= 0 THEN
72 return(2);
73 END IF;
74
75 -- Only return negative components that were issued
76 -- Only return negative components that were issued
77 IF p_transaction_action_id = 34
78 AND p_quantity_issued >= 0 THEN
79 return(2);
80 END IF;
81
82 END IF;
83
84 return(1);
85
86 END IS_VALID;
87
88 FUNCTION MEETS_CRITERIA
89 (req_op_seq NUMBER,
90 crit_op_seq NUMBER,
91 req_dept_id NUMBER,
92 crit_dept_id NUMBER,
93 req_sub VARCHAR2,
94 crit_sub VARCHAR2) RETURN NUMBER IS
95 BEGIN
96 IF crit_op_seq IS NOT NULL AND
97 req_op_seq <> crit_op_seq THEN
98 return(2);
99
100 ELSIF crit_dept_id IS NOT NULL AND
101 nvl(req_dept_id, -1) <> crit_dept_id THEN
102 return(2);
103
104 ELSIF crit_sub IS NOT NULL AND
105 nvl(req_sub, '@@@@') <> crit_sub THEN
106 return(2);
107
108 END IF;
109
110 return(1);
111
112 END MEETS_CRITERIA;
113
114 /* ER 4369064: Component Yield Enhancement */
115 /* Added two new paramters include_yield and component_yield_factor. These will
116 be used to compute transaction quantity based on yield consideration */
117 FUNCTION Determine_Txn_Quantity
118 (transaction_action_id IN NUMBER,
119 qty_per_assembly IN NUMBER,
120 required_qty IN NUMBER,
121 qty_issued IN NUMBER,
122 assembly_qty IN NUMBER,
123 include_yield IN NUMBER,
124 component_yield_factor IN NUMBER,
125 basis_type IN NUMBER DEFAULT NULL ) RETURN NUMBER IS /* LBM Project */
126 l_effective_yield NUMBER; /* ER 4369064 */
127 l_remove_yield_effect NUMBER;
128 BEGIN
129
130 if (include_yield = 2) then
131 /* This means that the paramter is set such that yield is NOT considered for
132 transaction. Then, setting effective_yield to 1 for calculating
133 quantity to be transacted. When calculation is based on required_quantity,
134 we use remove_yield_effect because required_quantity already includes yield */
135 l_remove_yield_effect := component_yield_factor;
136 l_effective_yield := 1;
137 else
138 /* Do not strip the required quantity off its yield factor and when
139 calculating transaction quantity based on QPA, include yield */
140 l_remove_yield_effect := 1;
141 l_effective_yield := component_yield_factor;
142 end if;
143
144 IF transaction_action_id in ( 1,
145 2,
146 33) THEN
147 IF assembly_qty IS NOT NULL THEN
148 if basis_type = WIP_CONSTANTS.LOT_BASED_MTL then /* LBM Project */
149 return (-1 * qty_per_assembly / l_effective_yield);
150 else
151 return(-1 * qty_per_assembly * assembly_qty / l_effective_yield);
152 end if;
153 ELSIF transaction_action_id = 33 THEN
154 return(GREATEST(qty_issued - required_qty * l_remove_yield_effect,0));
155 ELSE
156 return(LEAST(qty_issued - required_qty * l_remove_yield_effect,0));
157 END IF;
158
159 ELSIF assembly_qty IS NOT NULL THEN
160 IF transaction_action_id = 34 THEN
161 return(GREATEST(LEAST(qty_issued,0),
162 (qty_per_assembly * assembly_qty / l_effective_yield)));
163 ELSE
164 return(LEAST(GREATEST(qty_issued,0),
165 (qty_per_assembly * assembly_qty / l_effective_yield)));
166 END IF;
167 ELSIF transaction_action_id = 34 THEN
168 return(LEAST(qty_issued,0));
169 ELSE
170 return(GREATEST(qty_issued,0));
171 END IF;
172
173 END Determine_Txn_Quantity;
174
175 FUNCTION Valid_Subinventory
176 (p_subinventory IN VARCHAR2,
177 p_item_id IN NUMBER,
178 p_org_id IN NUMBER) RETURN VARCHAR2 IS
179 valid_flag VARCHAR2(2) := 'Y';
180 BEGIN
181
182 IF p_subinventory IS NULL THEN
183 valid_flag := 'N';
184 END IF;
185
186 -- If restricted sub, non-asset item, must be in MTL_ITEM_SUB_VAL_V
187
188 SELECT decode(count(*),0,valid_flag,'N')
189 INTO VALID_FLAG
190 FROM DUAL
191 WHERE
192 (EXISTS
193 (SELECT 1
194 FROM MTL_SYSTEM_ITEMS MSI
195 WHERE MSI.INVENTORY_ITEM_ID = p_item_id
196 AND MSI.ORGANIZATION_ID = p_org_id
197 AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
198 AND MSI.INVENTORY_ASSET_FLAG = 'N')
199 AND NOT EXISTS
200 (SELECT 1
201 FROM MTL_ITEM_SUB_VAL_V MSVV
202 WHERE MSVV.ORGANIZATION_ID = p_org_id
203 AND MSVV.INVENTORY_ITEM_ID = p_item_id
204 AND MSVV.SECONDARY_INVENTORY_NAME = p_subinventory));
205
206 -- If restricted sub, asset item, must be in MTL_ITEM_AST_TRK_SUB_VAL_V
207
208 SELECT decode(count(*),0,valid_flag,'N')
209 INTO VALID_FLAG
210 FROM DUAL
211 WHERE
212 (EXISTS
213 (SELECT 1
214 FROM MTL_SYSTEM_ITEMS MSI
215 WHERE MSI.INVENTORY_ITEM_ID = p_item_id
216 AND MSI.ORGANIZATION_ID = p_org_id
217 AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
218 AND MSI.INVENTORY_ASSET_FLAG = 'Y')
219 AND NOT EXISTS
220 (SELECT 1
221 FROM MTL_ITEM_SUB_AST_TRK_VAL_V MSVV
222 WHERE MSVV.ORGANIZATION_ID = p_org_id
223 AND MSVV.INVENTORY_ITEM_ID = p_item_id
224 AND MSVV.SECONDARY_INVENTORY_NAME = p_subinventory));
225
226 -- Test non-restricted items
227
228 SELECT decode(count(*),0,valid_flag,'N')
229 INTO VALID_FLAG
230 FROM DUAL
231 WHERE
232 NOT EXISTS
233 (SELECT 1
234 FROM MTL_SUBINVENTORIES_VAL_V MSVV,
235 MTL_SYSTEM_ITEMS MSI
236 WHERE MSVV.ORGANIZATION_ID = p_org_id
237 AND MSVV.SECONDARY_INVENTORY_NAME = p_subinventory
238 AND MSI.INVENTORY_ITEM_ID = p_item_id
239 AND MSI.ORGANIZATION_ID = p_org_id
240 AND MSI.INVENTORY_ASSET_FLAG = 'N'
241 UNION
242 SELECT 1
243 FROM MTL_SUB_AST_TRK_VAL_V MSVV,
244 MTL_SYSTEM_ITEMS MSI
245 WHERE MSVV.ORGANIZATION_ID = p_org_id
246 AND MSVV.SECONDARY_INVENTORY_NAME = p_subinventory
247 AND MSI.INVENTORY_ITEM_ID = p_item_id
248 AND MSI.ORGANIZATION_ID = p_org_id
249 AND MSI.INVENTORY_ASSET_FLAG = 'Y');
250
251 return(VALID_FLAG);
252
253 END Valid_Subinventory;
254
255 FUNCTION Valid_Locator
256 (p_locator_id IN OUT NOCOPY NUMBER,
257 p_item_id IN NUMBER,
258 p_org_id IN NUMBER,
259 p_org_control IN NUMBER,
260 p_sub_control IN NUMBER,
261 p_item_control IN NUMBER,
262 p_restrict_locators_code IN NUMBER,
263 p_loc_disable_date IN DATE,
264 p_locator_control OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
265 valid_flag VARCHAR2(2) := 'Y';
266 x_locator_control NUMBER;
267 BEGIN
268
269 x_locator_control := QLTINVCB.Control
270 (p_org_control,
271 p_sub_control,
272 p_item_control);
273 p_locator_control := x_locator_control;
274
275 IF p_locator_id IS NOT NULL AND x_locator_control = 1 THEN
276 p_locator_id := NULL;
277 return('Y');
278 END IF;
279
280 IF p_locator_id IS NULL THEN
281 IF x_locator_control <> 1 THEN
282 return('N');
283 ELSE
284 return('Y');
285 END IF;
286 END IF;
287
288 IF nvl(p_loc_disable_date, SYSDATE+1) < SYSDATE THEN
289 return('N');
290 END IF;
291
292 IF p_restrict_locators_code = 1 THEN
293 SELECT decode(count(*),0,valid_flag,'N') INTO valid_flag
294 FROM DUAL
295 WHERE NOT EXISTS
296 (SELECT 1
297 FROM MTL_SECONDARY_LOCATORS msl
298 WHERE msl.inventory_item_id = p_item_id
299 AND msl.secondary_locator = p_locator_id
300 AND msl.organization_id = p_org_id);
301 END IF;
302
303 return(valid_flag);
304
305 END Valid_Locator;
306
307 END WIP_COMPONENT;
[Home] [Help]