[Home] [Help]
PACKAGE BODY: APPS.WSMPLBJT
Source
1 PACKAGE BODY WSMPLBJT AS
2 /* $Header: WSMLBJTB.pls 120.3 2006/02/10 19:02:57 nlal noship $ */
3
4 x_exp_date DATE;
5
6
7 FUNCTION Material_Issue (
8 X_Wip_Entity_Id IN NUMBER,
9 X_Inventory_Item_Id IN NUMBER,
10 X_Organization_id IN NUMBER,
11 X_Quantity IN NUMBER,
12 X_Acct_Period_Id IN NUMBER,
13 X_Lot_Creation_Id IN NUMBER,
14 X_Lot_Number IN VARCHAR2,
15 X_Subinventory IN VARCHAR2,
16 X_Locator_Id IN NUMBER,
17 X_Revision IN VARCHAR2,
18 X_err_code OUT NOCOPY NUMBER,
19 X_err_msg OUT NOCOPY VARCHAR2,
20 X_passed_header_id IN NUMBER DEFAULT null,
21 -- ST : Serial Support Project --
22 -- Return the transaction temp id also... --
23 X_Temp_id OUT NOCOPY NUMBER
24 -- ST : Serial Support Project --
25 )
26 RETURN NUMBER IS
27
28 X_header_id NUMBER;
29 -- ST : Serial Support Project : Commenting the below declaration --
30 -- X_Temp_Id NUMBER;
31 -- ST : Serial Support Project --
32 X_User_Id NUMBER := FND_GLOBAL.USER_ID;
33 X_Login_Id NUMBER := FND_GLOBAL.LOGIN_ID;
34 X_Op_Seq NUMBER;
35 X_Date DATE := SYSDATE;
36 X_Uom VARCHAR2(3);
37 X_type_id NUMBER;
38
39
40 BEGIN
41
42 IF ( X_passed_header_id is null ) THEN
43
44 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
45 INTO X_Header_Id
46 FROM DUAL;
47
48 ELSE
49 X_Header_id := X_passed_header_id;
50
51 END IF;
52
53 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
54 INTO X_Temp_Id
55 FROM DUAL;
56
57 -- Select the Primary UOM for this item
58
59 SELECT primary_uom_code
60 INTO X_UOM
61 FROM MTL_SYSTEM_ITEMS
62 WHERE INVENTORY_ITEM_ID = X_Inventory_Item_Id
63 AND ORGANIZATION_ID = X_Organization_Id;
64
65 -- Select the first operation from the WIP routing.
66 -- x_op_seq = 10 always in the changed solution.
67 /*
68 SELECT min(operation_seq_num)
69 INTO x_op_seq
70 FROM wip_operations
71 where wip_entity_id = X_Wip_Entity_Id
72 and organization_id = X-organization_Id;
73 */
74
75 -- We use the user-defined transaction type
76 -- Changed to miscellaneous transaction types
77
78 /*
79 SELECT transaction_type_id
80 INTO x_type_id
81 FROM MTL_TRANSACTION_TYPES
82 WHERE TRANSACTION_TYPE_NAME = 'WSM WIP Issue';
83 */
84
85 insert into mtl_material_transactions_temp (
86 last_update_date,
87 creation_date,
88 last_updated_by,
89 created_by,
90 last_update_login,
91 transaction_header_id,
92 transaction_source_id,
93 inventory_item_id,
94 organization_id,
95 revision,
96 subinventory_code,
97 locator_id,
98 transaction_quantity,
99 primary_quantity,
100 transaction_uom,
101 transaction_type_id,
102 transaction_action_id,
103 transaction_source_type_id,
104 transaction_date,
105 acct_period_id,
106 source_code,
107 source_line_id,
108 wip_entity_type,
109 negative_req_flag,
110 operation_seq_num,
111 wip_supply_type,
112 wip_commit_flag,
113 process_flag,
114 posting_flag,
115 transaction_temp_id)
116 values(
117 X_date, /* LAST_UPDATE_DATE */
118 X_date, /* CREATION_DATE */
119 X_User_Id, /* LAST_UPDATED_BY */
120 X_User_Id, /* CREATED_BY */
121 X_Login_Id,
122 X_Header_Id, /* TRANSACTION_HEADER_ID */
123 X_Wip_Entity_Id, /* TRANSACTION_SOURCE_ID */
124 X_Inventory_Item_Id, /* INVENTORY_ITEM_ID */
125 X_Organization_Id, /* ORGANIZATION_ID */
126 X_Revision, /* REVISION */
127 X_Subinventory, /* SUBINVENTORY_CODE */
128 X_Locator_Id,
129 -1 * X_Quantity, /* TRANSACTION_QUANTITY */
130 -1 * X_Quantity, /* PRIMARY_QUANTITY */
131 X_Uom, /* UNIT_OF_MEASURE */
132 35, /* TRANSACTION_TYPE_ID */
133 1, /* TRANSACTION_ACTION_ID */
134 5, /* TRANSACTION_SOURCE_TYPE_ID */
135 X_date, /* TRANSACTION_DATE */
136 X_Acct_Period_Id, /* ACCT_PERIOD_ID */
137 'WSM',
138 to_char(X_Lot_Creation_Id), /* SOURCE_LINE_ID */
139 5, /* WIP_ENTITY_TYPE */
140 1, /* neg req flag */
141 10, /* op seq */
142 '', /* supply type */
143 'N', /* WIP_COMMIT_FLAG */
144 'Y', /* PROCESS_FLAG */
145 'Y', /* POSTING_FLAG */
146 X_temp_id /* Transaction Temp Id */
147 );
148
149 INSERT INTO MTL_TRANSACTION_LOTS_TEMP (
150 transaction_temp_id,
151 -- ST : Serial Support Project --
152 SERIAL_TRANSACTION_TEMP_ID,
153 -- ST : Serial Support Project --
154 last_update_date,
155 creation_date,
156 last_updated_by,
157 created_by,
158 last_update_login,
159 transaction_quantity,
160 primary_quantity,
161 lot_number
162 ) values (
163 X_temp_id,
164 -- ST : Serial Support Project --
165 x_temp_id,
166 -- ST : Serial Support Project --
167 X_date,
168 X_date,
169 X_User_Id,
170 X_User_Id,
171 X_Login_Id,
172 -1 * X_quantity,
173 -1 * X_quantity,
174 X_lot_number
175 );
176
177 return(X_Header_Id);
178
179 EXCEPTION WHEN OTHERS THEN
180
181 X_err_code := SQLCODE;
182 X_err_msg := 'WSMPLBJT.Material_Issue '|| SUBSTR(SQLERRM,1,60);
183 return(0);
184
185 END Material_Issue;
186
187
188 FUNCTION get_assembly(
189 X_component_item_id IN NUMBER,
190 X_organization_id IN NUMBER,
191 X_err_code OUT NOCOPY NUMBER,
192 X_err_msg OUT NOCOPY VARCHAR2)
193 return NUMBER IS
194
195 x_assembly_id NUMBER := 0;
196
197 BEGIN
198
199 select unique wcv.assembly_item_id
200 into x_assembly_id
201 from wsm_components_v wcv,
202 mtl_system_items msi
203 where wcv.component_item_id = X_component_item_id
204 and wcv.organization_id = X_organization_id
205 and msi.organization_id = X_organization_id
206 and msi.inventory_item_id = wcv.assembly_item_id
207 and msi.lot_control_code = 2 ;
208 return(x_assembly_id);
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 X_err_code := SQLCODE;
213 X_err_msg := 'WSMPLBJT.GET_ASSEMBLY '|| SUBSTR(SQLERRM,1,60);
214 RETURN (0);
215 END get_assembly;
216
217
218 FUNCTION get_id(
219 X_item_name IN VARCHAR2,
220 X_organization_id IN NUMBER,
221 X_err_code OUT NOCOPY NUMBER,
222 X_err_msg OUT NOCOPY VARCHAR2 )
223 return NUMBER IS
224
225 x_temp_id NUMBER := 0;
226 BEGIN
227
228 -- abedajna 10/11/00
229 /* select inventory_item_id
230 ** into x_temp_id
231 ** from mtl_system_items
232 ** where segment1=X_item_name
233 ** and organization_id = X_organization_id;
234 */
235
236 -- modification by abedajna 10/11/00
237
238 select inventory_item_id
239 into x_temp_id
240 from mtl_system_items_kfv
241 where concatenated_segments = X_item_name
242 and organization_id = X_organization_id;
243
244 return(x_temp_id);
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 X_err_code := SQLCODE;
249 X_err_msg := 'WSMPLBJT.GET_ID '|| SUBSTR(SQLERRM,1,60);
250 RETURN(0);
251
252 END get_id;
253
254
255 FUNCTION next_job_name (
256 X_Job_Name IN VARCHAR2,
257 X_Organization_Id IN NUMBER,
258 X_Item_Id IN NUMBER,
259 X_Count IN NUMBER,
260 X_err_code OUT NOCOPY NUMBER,
261 X_err_msg OUT NOCOPY VARCHAR2)
262 RETURN NUMBER IS
263
264 temp_name VARCHAR2(240);
265 dummy NUMBER;
266 ct NUMBER := X_Count;
267 x_sep VARCHAR2(30);
268
269 BEGIN
270
271 SELECT nvl(NEW_LOT_SEPARATOR,'-')
272 INTO x_sep
273 FROM WSM_PARAMETERS
274 WHERE ORGANIZATION_ID = X_Organization_Id;
275
276 LOOP
277 temp_name := X_Job_Name || x_sep || to_char(ct);
278 -- abb modification
279 if ct = 0 then
280 temp_name := X_Job_Name;
281 end if; -- ct = 0
282
283 SELECT 1
284 INTO dummy
285 FROM DUAL
286 WHERE (EXISTS(
287 SELECT 1
288 FROM wip_entities
289 where wip_entity_name = temp_name
290 and organization_id = X_Organization_id)
291 /* Bugfix 4317714: Add second where clause to also check for mtl_lot_numbers if uniqueness constraint is across items */
292 OR EXISTS(
293 SELECT 1
294 FROM mtl_lot_numbers
295 where lot_number = temp_name
296 and organization_id = X_Organization_id
297 and inventory_item_id = X_Item_Id));
298 /* End bugfix 4317714 */
299 ct := ct + 1;
300
301 END LOOP;
302
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 return(ct);
306
307 WHEN OTHERS THEN
308 X_err_code := SQLCODE;
309 X_err_msg := 'WSMPLBJT.NEXT_JOB_NAME '|| SUBSTR(SQLERRM,1,60);
310 return(ct);
311
312 END next_job_name;
313
314 END WSMPLBJT;