[Home] [Help]
PACKAGE BODY: APPS.ENG_ROUTING_PKG
Source
1 PACKAGE BODY ENG_ROUTING_PKG AS
2 /* $Header: ENGPRTRB.pls 120.4 2006/05/05 02:13:32 prgopala noship $ */
3
4 -- +--------------------------- ROUTING_UPDATE -------------------------------+
5 -- NAME
6 -- ROUTING_UPDATE
7
8 -- DESCRIPTION
9 -- Update Routings: Flip routing_type to 1 (manufacturing)
10
11 -- REQUIRES
12 -- org_id: organization id
13 -- eng_item_id: routing that requires routing type to be set to 1
14 -- designator_option
15 -- 1. all
16 -- 2. primary only
17 -- 3. specific only
18 -- alt_rtg_designator
19
20 -- OUTPUT
21
22 -- NOTES
23
24 -- +--------------------------------------------------------------------------+
25
26 PROCEDURE ROUTING_UPDATE
27 (
28 X_org_id IN NUMBER,
29 X_eng_item_id IN NUMBER,
30 X_designator_option IN NUMBER,
31 X_transfer_option IN NUMBER,
32 X_alt_rtg_designator IN VARCHAR2,
33 X_effectivity_date IN DATE
34 )
35 IS
36 X_stmt_num NUMBER;
37 BEGIN
38
39 X_stmt_num := 500;
40 UPDATE BOM_OPERATIONAL_ROUTINGS
41 SET ROUTING_TYPE = 1
42 WHERE ORGANIZATION_ID = X_org_id
43 AND ASSEMBLY_ITEM_ID = X_eng_item_id
44 AND ((X_designator_option = 2 AND
45 ALTERNATE_ROUTING_DESIGNATOR IS NULL)
46 OR
47 (X_designator_option = 3 AND
48 ALTERNATE_ROUTING_DESIGNATOR = X_alt_rtg_designator)
49 OR
50 X_designator_option = 1);
51
52 IF ( X_transfer_option <> 1 ) THEN
53 X_stmt_num := 501;
54 DELETE FROM BOM_OPERATION_SEQUENCES BOS
55 WHERE ((X_transfer_option = 2
56 AND (BOS.EFFECTIVITY_DATE > X_effectivity_date
57 OR NVL(BOS.DISABLE_DATE, X_effectivity_date+1) <
58 X_effectivity_date))
59 OR (X_transfer_option = 3
60 AND NVL(BOS.DISABLE_DATE, X_effectivity_date + 1) <
61 X_effectivity_date))
62 AND EXISTS (SELECT 'X'
63 FROM BOM_OPERATIONAL_ROUTINGS BOR
64 WHERE ORGANIZATION_ID = X_org_id
65 AND ASSEMBLY_ITEM_ID = X_eng_item_id
66 AND ROUTING_TYPE = 1
67 AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
68 AND ((X_designator_option = 2 AND ALTERNATE_ROUTING_DESIGNATOR IS NULL)
69 OR (X_designator_option = 3 AND ALTERNATE_ROUTING_DESIGNATOR = X_alt_rtg_designator)
70 OR (X_designator_option = 1)));
71 END IF;
72
73 EXCEPTION
74 WHEN OTHERS THEN
75 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'ROUTING_UPDATE',
76 stmt_num => X_stmt_num,
77 message_name => 'ENG_ENUBRT_ERROR',
78 token => SQLERRM);
79
80 END ROUTING_UPDATE;
81
82 -- +--------------------------- ROUTING_TRANSFER -----------------------------+
83 -- NAME
84 -- ROUTING_TRANSFER
85
86 -- DESCRIPTION
87 -- Transfer Routings
88
89 -- REQUIRES
90 -- org_id: organization id
91 -- eng_item_id
92 -- mfg_item_id
93 -- designator_option
94 -- 1. all
95 -- 2. primary only
96 -- 3. specific only
97 -- transfer option
98 -- 1. all rows
99 -- 2. current only
100 -- 3. current and pending
101 -- alt_rtg_designator
102 -- effectivity_date
103 -- last_login_id not used internally just kept to support already existing usage
104 -- ecn_name
105
106 -- OUTPUT
107
108 -- RETURNS
109 -- 1 SUCCESS
110 -- 2 FAILURE
111
112 -- NOTES
113
114 -- +--------------------------------------------------------------------------+
115
116 PROCEDURE ROUTING_TRANSFER
117 (
118 X_org_id IN NUMBER,
119 X_eng_item_id IN NUMBER,
120 X_mfg_item_id IN NUMBER,
121 X_designator_option IN NUMBER,
122 X_transfer_option IN NUMBER,
123 X_alt_rtg_designator IN VARCHAR2,
124 X_effectivity_date IN DATE,
125 X_last_login_id IN NUMBER,
126 X_ecn_name IN VARCHAR2
127 )
128 IS
129 X_stmt_num NUMBER;
130 X_from_rtg_sequence_id NUMBER;
131
132 CURSOR RTG_CURSOR IS
133 SELECT ROUTING_SEQUENCE_ID, ALTERNATE_ROUTING_DESIGNATOR
134 FROM BOM_OPERATIONAL_ROUTINGS
135 WHERE ORGANIZATION_ID = X_org_id
136 AND ASSEMBLY_ITEM_ID = X_mfg_item_id
137 AND COMMON_ROUTING_SEQUENCE_ID = ROUTING_SEQUENCE_ID;
138
139 -- BUG 3503220
140 CURSOR RTG_COPIES IS
141 SELECT ROUTING_SEQUENCE_ID, ALTERNATE_ROUTING_DESIGNATOR
142 FROM BOM_OPERATIONAL_ROUTINGS BOR
143 WHERE ORGANIZATION_ID = X_org_id
144 AND ASSEMBLY_ITEM_ID = X_mfg_item_id
145 AND COMMON_ROUTING_SEQUENCE_ID = ROUTING_SEQUENCE_ID
146 AND ((X_designator_option = 2 AND
147 BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
148 OR
149 (X_designator_option = 3 AND
150 BOR.ALTERNATE_ROUTING_DESIGNATOR = X_alt_rtg_designator)
151 OR
152 X_designator_option = 1);
153 BEGIN
154
155 X_stmt_num := 800;
156
157 --- BOM_OPERATIONAL_ROUTINGS
158 -- Bug 5104285 Added all columns in the insert. Otherwise flow routing was being
159 -- copied as a normal routing.
160 BEGIN
161 INSERT INTO BOM_OPERATIONAL_ROUTINGS(
162 ROUTING_SEQUENCE_ID,
163 ASSEMBLY_ITEM_ID,
164 ORGANIZATION_ID,
165 ALTERNATE_ROUTING_DESIGNATOR,
166 LAST_UPDATE_DATE,
167 LAST_UPDATED_BY,
168 CREATION_DATE,
169 CREATED_BY,
170 LAST_UPDATE_LOGIN,
171 ROUTING_TYPE,
172 COMMON_ASSEMBLY_ITEM_ID,
173 COMMON_ROUTING_SEQUENCE_ID,
174 ROUTING_COMMENT,
175 COMPLETION_SUBINVENTORY,
176 COMPLETION_LOCATOR_ID,
177 ATTRIBUTE_CATEGORY,
178 ATTRIBUTE1,
179 ATTRIBUTE2,
180 ATTRIBUTE3,
181 ATTRIBUTE4,
182 ATTRIBUTE5,
183 ATTRIBUTE6,
184 ATTRIBUTE7,
185 ATTRIBUTE8,
186 ATTRIBUTE9,
187 ATTRIBUTE10,
188 ATTRIBUTE11,
189 ATTRIBUTE12,
190 ATTRIBUTE13,
191 ATTRIBUTE14,
192 ATTRIBUTE15,
193 LINE_ID,
194 CFM_ROUTING_FLAG,
195 MIXED_MODEL_MAP_FLAG,
196 PRIORITY,
197 TOTAL_PRODUCT_CYCLE_TIME,
198 CTP_FLAG,
199 PROJECT_ID,
200 TASK_ID,
201 PENDING_FROM_ECN,
202 ORIGINAL_SYSTEM_REFERENCE,
203 SERIALIZATION_START_OP)
204 SELECT
205 BOM_OPERATIONAL_ROUTINGS_S.NEXTVAL,
206 X_mfg_item_id,
207 BOR.ORGANIZATION_ID,
208 BOR.ALTERNATE_ROUTING_DESIGNATOR,
209 SYSDATE,
210 to_number(Fnd_Profile.Value('USER_ID')),
211 SYSDATE,
212 to_number(Fnd_Profile.Value('USER_ID')),
213 to_number(Fnd_Profile.Value('LOGIN_ID')),
214 1,
215 BOR.COMMON_ASSEMBLY_ITEM_ID,
216 DECODE(BOR.COMMON_ROUTING_SEQUENCE_ID,BOR.ROUTING_SEQUENCE_ID,BOM_OPERATIONAL_ROUTINGS_S.CURRVAL,BOR.COMMON_ROUTING_SEQUENCE_ID),
217 BOR.ROUTING_COMMENT,
218 BOR.COMPLETION_SUBINVENTORY,
219 BOR.COMPLETION_LOCATOR_ID,
220 BOR.ATTRIBUTE_CATEGORY,
221 BOR.ATTRIBUTE1,
222 BOR.ATTRIBUTE2,
223 BOR.ATTRIBUTE3,
224 BOR.ATTRIBUTE4,
225 BOR.ATTRIBUTE5,
226 BOR.ATTRIBUTE6,
227 BOR.ATTRIBUTE7,
228 BOR.ATTRIBUTE8,
229 BOR.ATTRIBUTE9,
230 BOR.ATTRIBUTE10,
231 BOR.ATTRIBUTE11,
232 BOR.ATTRIBUTE12,
233 BOR.ATTRIBUTE13,
234 BOR.ATTRIBUTE14,
235 BOR.ATTRIBUTE15,
236 LINE_ID,
237 CFM_ROUTING_FLAG,
238 MIXED_MODEL_MAP_FLAG,
239 PRIORITY,
240 TOTAL_PRODUCT_CYCLE_TIME,
241 CTP_FLAG,
242 PROJECT_ID,
243 TASK_ID,
244 PENDING_FROM_ECN,
245 ORIGINAL_SYSTEM_REFERENCE,
246 SERIALIZATION_START_OP
247 FROM BOM_OPERATIONAL_ROUTINGS BOR
248 WHERE ORGANIZATION_ID = X_org_id
249 AND ASSEMBLY_ITEM_ID = X_eng_item_id
250 AND ((X_designator_option = 2 AND
251 BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
252 OR
253 (X_designator_option = 3 AND
254 BOR.ALTERNATE_ROUTING_DESIGNATOR = X_alt_rtg_designator)
255 OR
256 X_designator_option = 1);
257 EXCEPTION
258 WHEN OTHERS THEN
259 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'ROUTING_TRANSFER',
260 stmt_num => X_stmt_num,
261 message_name => 'ENG_ENUBRT_ERROR',
262 token => SQLERRM);
263 END;
264
265 -- bug 3780577 : odaboval moved the ERES call after the LOOP
266 -- in order to see the routing revisions:
267 /* THIS ERES CALL IS NOT MOVED TO A PLACE BELOW.
268 -- ERES BEGIN
269 -- If there is a parent eRecord, log a child record to accompany it
270 -- ================================================================
271 IF ENG_BOM_RTG_TRANSFER_PKG.G_PARENT_ERECORD_ID is NOT NULL THEN
272 FOR ROUTING IN RTG_COPIES LOOP
273 ENG_BOM_RTG_TRANSFER_PKG.Process_Erecord
274 ( p_event_name =>'oracle.apps.bom.routingCreate'
275 , p_event_key =>to_char(ROUTING.routing_sequence_id)
276 , p_user_key =>ENG_BOM_RTG_TRANSFER_PKG.G_ITEM_NAME
277 ||'-'||ENG_BOM_RTG_TRANSFER_PKG.G_ORG_CODE||'-'||ROUTING.alternate_routing_designator
278 , p_parent_event_key =>to_char(X_eng_item_id)||'-'||to_char(X_org_id)
279 ||'-'||to_char(X_mfg_item_id)
280 );
281 END LOOP;
282 END IF;
283 NOT USED ANYMORE. PLEASE SEE CALL BELOW. */
284 -- ERES END
285 -- ========
286
287 FOR RTG IN RTG_CURSOR LOOP
288
289 X_stmt_num := 801;
290 BEGIN
291 SELECT ROUTING_SEQUENCE_ID
292 INTO X_from_rtg_sequence_id
293 FROM BOM_OPERATIONAL_ROUTINGS
294 WHERE ORGANIZATION_ID = X_org_id
295 AND ASSEMBLY_ITEM_ID = X_eng_item_id
296 AND NVL(ALTERNATE_ROUTING_DESIGNATOR,'NONE') = NVL(RTG.ALTERNATE_ROUTING_DESIGNATOR,'NONE');
297 EXCEPTION
298 WHEN OTHERS THEN
299 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'ROUTING_TRANSFER',
300 stmt_num => X_stmt_num,
301 message_name => 'ENG_ENUBRT_ERROR',
302 token => SQLERRM);
303 END;
304
305 BOM_COPY_ROUTING.COPY_ROUTING(from_sequence_id => X_from_rtg_sequence_id,
306 to_sequence_id => RTG.ROUTING_SEQUENCE_ID,
307 from_org_id => X_org_id,
308 to_org_id => X_org_id,
309 display_option => X_transfer_option,
310 user_id => to_number(Fnd_Profile.Value('USER_ID')),
311 to_item_id => X_mfg_item_id,
312 direction => 4,
313 to_alternate => RTG.ALTERNATE_ROUTING_DESIGNATOR,
314 rev_date => X_effectivity_date);
315
316 IF (X_designator_option = 3 AND X_alt_rtg_designator IS NOT NULL) THEN -- bug 3570053
317 UPDATE BOM_OPERATIONAL_ROUTINGS
318 SET ALTERNATE_ROUTING_DESIGNATOR = NULL
319 WHERE ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID;
320 END IF;
321 END LOOP;
322
323 -- bug 3780577 : odaboval moved the ERES RoutingCreate here
324 -- in order to get the routing revisions.
325 -- ERES BEGIN
326 -- If there is a parent eRecord, log a child record to accompany it
327 -- ================================================================
328 IF ENG_BOM_RTG_TRANSFER_PKG.G_PARENT_ERECORD_ID is NOT NULL THEN
329 FOR ROUTING IN RTG_COPIES LOOP
330 ENG_BOM_RTG_TRANSFER_PKG.Process_Erecord
331 ( p_event_name =>'oracle.apps.bom.routingCreate'
332 , p_event_key =>to_char(ROUTING.routing_sequence_id)
333 , p_user_key =>ENG_BOM_RTG_TRANSFER_PKG.G_ITEM_NAME
334 ||'-'||ENG_BOM_RTG_TRANSFER_PKG.G_ORG_CODE||'-'||ROUTING.alternate_routing_designator
335 , p_parent_event_key =>to_char(X_eng_item_id)||'-'||to_char(X_org_id)
336 ||'-'||to_char(X_mfg_item_id)
337 );
338 END LOOP;
339 END IF;
340 -- ERES END
341 -- ========
342 END ROUTING_TRANSFER;
343
344 END ENG_ROUTING_PKG;