1 PACKAGE BODY WSM_WSMPNTXN_XMLP_PKG AS
2 /* $Header: WSMPNTXNB.pls 120.0 2007/12/24 14:54:21 krreddy noship $ */
3 FUNCTION QR_PTXN_JOBREFCURDS RETURN number IS
4 PTXN_JOB PTXN_JOB_TYPE;
5 BEGIN
6 -- OPEN PTXN_JOB FOR
7 SELECT
8 WDJ.WIP_ENTITY_ID
9 BULK COLLECT INTO G_WIP_ENTITY_ID_PL_TBL
10 FROM
11 WIP_DISCRETE_JOBS WDJ,
12 WIP_ENTITIES WE
13 WHERE WDJ.ORGANIZATION_ID = P_ORGANIZATION_ID
14 AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
15 AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
16 AND WE.ENTITY_TYPE = 5
17 AND WDJ.WIP_ENTITY_ID in (
18 SELECT
19 WIP_ENTITY_ID
20 FROM
21 WSM_LOT_MOVE_TXN_INTERFACE
22 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
23 AND STATUS <> 4
24 UNION ALL
25 SELECT
26 WIP_ENTITY_ID
27 FROM
28 WIP_MOVE_TXN_INTERFACE
29 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
30 UNION ALL
31 SELECT
32 WIP_ENTITY_ID
33 FROM
34 WIP_COST_TXN_INTERFACE
35 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
36 UNION ALL
37 SELECT
38 TRANSACTION_SOURCE_ID
39 FROM
40 MTL_MATERIAL_TRANSACTIONS_TEMP
41 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
42 AND TRANSACTION_SOURCE_TYPE_ID = 5
43 UNION ALL
44 SELECT
45 TRANSACTION_SOURCE_ID
46 FROM
47 MTL_MATERIAL_TRANSACTIONS
48 WHERE COSTED_FLAG in ( 'N' , 'E' )
49 AND TRANSACTION_SOURCE_TYPE_ID = 5
50 AND ORGANIZATION_ID = P_ORGANIZATION_ID
51 UNION ALL
52 SELECT
53 WIP_ENTITY_ID
54 FROM
55 WIP_OPERATION_YIELDS
56 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
57 AND STATUS in ( 1 , 3 )
58 UNION ALL
59 SELECT
60 SJ.WIP_ENTITY_ID
61 FROM
62 WSM_SM_STARTING_JOBS SJ,
63 WSM_SPLIT_MERGE_TRANSACTIONS WMT
64 WHERE SJ.TRANSACTION_ID = WMT.TRANSACTION_ID
65 AND ( WMT.STATUS <> 4
66 OR NVL(WMT.COSTED
67 ,1) <> 4 )
68 UNION ALL
69 SELECT
70 RJ.WIP_ENTITY_ID
71 FROM
72 WSM_SM_RESULTING_JOBS RJ,
73 WSM_SPLIT_MERGE_TRANSACTIONS WMT
74 WHERE RJ.TRANSACTION_ID = WMT.TRANSACTION_ID
75 AND ( WMT.STATUS <> 4
76 OR NVL(WMT.COSTED
77 ,1) <> 4 )
78 UNION ALL
79 SELECT
80 PD.WIP_ENTITY_ID
81 FROM
82 PO_RELEASES_ALL PR,
83 PO_HEADERS_ALL PH,
84 PO_DISTRIBUTIONS_ALL PD,
85 PO_LINE_LOCATIONS_ALL PL
86 WHERE PD.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
87 AND PD.PO_LINE_ID is not null
88 AND PD.LINE_LOCATION_ID is not null
89 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
90 AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
91 AND pr.po_release_id (+) = PD.PO_RELEASE_ID
92 AND NVL(PR.CANCEL_FLAG
93 ,'N') = 'N'
94 AND ( PL.QUANTITY_RECEIVED < ( PL.QUANTITY - PL.QUANTITY_CANCELLED ) )
95 UNION ALL
96 SELECT
97 PRL.WIP_ENTITY_ID
98 FROM
99 PO_REQUISITION_LINES_ALL PRL
100 WHERE PRL.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
101 AND NVL(PRL.CANCEL_FLAG
102 ,'N') = 'N'
103 AND PRL.LINE_LOCATION_ID is null
104 UNION ALL
105 SELECT
106 PRI.WIP_ENTITY_ID
107 FROM
108 PO_REQUISITIONS_INTERFACE_ALL PRI
109 WHERE PRI.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID );
110 RETURN 1;
111 END QR_PTXN_JOBREFCURDS;
112
113 /*FUNCTION QR_PTXN_CTREFCURDS(WIP_ENTITY_ID_1 IN NUMBER) RETURN number IS
114 JOB_PTXN_CT PTXN_CT_TYPE;
115 L_COUNT NUMBER := 0;
116 L_CTMP NUMBER := 0;
117 BEGIN
118
119 BEGIN
120
121 SELECT
122 1
123 INTO L_COUNT
124 FROM
125 DUAL
126 WHERE exists (
127 SELECT
128 1
129 FROM
130 WIP_MOVE_TXN_INTERFACE
131 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
132 AND WIP_ENTITY_ID = WIP_ENTITY_ID_1
133 UNION ALL
134 SELECT
135 1
136 FROM
137 WSM_LOT_MOVE_TXN_INTERFACE
138 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
139 AND WIP_ENTITY_ID = WIP_ENTITY_ID_1
140 AND STATUS <> 4 );
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 L_COUNT := 0;
144 WHEN TOO_MANY_ROWS THEN
145 L_COUNT := 1;
146 END;
147 IF L_COUNT = 1 THEN
148 INSERT INTO WSM_PEND_TXN_REP_TMP
149 VALUES (WIP_ENTITY_ID_1
150 ,'Pending Move Transactions');
151 END IF;
152 BEGIN
153 SELECT
154 1
155 INTO L_COUNT
156 FROM
157 DUAL
158 WHERE exists (
159 SELECT
160 1
161 FROM
162 WIP_COST_TXN_INTERFACE
163 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
164 AND WIP_ENTITY_ID = WIP_ENTITY_ID_1 );
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 L_COUNT := 0;
168 WHEN TOO_MANY_ROWS THEN
169 L_COUNT := 1;
170 END;
171 IF L_COUNT = 1 THEN
172 INSERT INTO WSM_PEND_TXN_REP_TMP
173 VALUES (WIP_ENTITY_ID_1
174 ,'Pending Resource Transactions');
175 END IF;
176 BEGIN
177 SELECT
178 1
179 INTO L_COUNT
180 FROM
181 DUAL
182 WHERE exists (
183 SELECT
184 1
185 FROM
186 MTL_MATERIAL_TRANSACTIONS_TEMP
187 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
188 AND TRANSACTION_SOURCE_ID = WIP_ENTITY_ID_1
189 AND TRANSACTION_SOURCE_TYPE_ID = 5 );
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 L_COUNT := 0;
193 WHEN TOO_MANY_ROWS THEN
194 L_COUNT := 1;
195 END;
196 IF L_COUNT = 1 THEN
197 INSERT INTO WSM_PEND_TXN_REP_TMP
198 VALUES (WIP_ENTITY_ID_1
199 ,'Pending Material Transactions');
200 END IF;
201 BEGIN
202 SELECT
203 1
204 INTO L_COUNT
205 FROM
206 DUAL
207 WHERE exists (
208 SELECT
209 1
210 FROM
211 MTL_MATERIAL_TRANSACTIONS
212 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
213 AND TRANSACTION_SOURCE_ID = WIP_ENTITY_ID_1
214 AND COSTED_FLAG IN ( 'N' , 'E' )
215 AND TRANSACTION_SOURCE_TYPE_ID = 5 );
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 L_COUNT := 0;
219 WHEN TOO_MANY_ROWS THEN
220 L_COUNT := 1;
221 END;
222 IF L_COUNT = 1 THEN
223 INSERT INTO WSM_PEND_TXN_REP_TMP
224 VALUES (WIP_ENTITY_ID_1
225 ,'Uncosted Material Transactions');
226 END IF;
227 BEGIN
228 SELECT
229 1
230 INTO L_COUNT
231 FROM
232 DUAL
233 WHERE exists (
234 SELECT
235 1
236 FROM
237 WIP_OPERATION_YIELDS
238 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
239 AND WIP_ENTITY_ID = WIP_ENTITY_ID_1
240 AND STATUS in ( 1 , 3 ) );
241 EXCEPTION
242 WHEN NO_DATA_FOUND THEN
243 L_COUNT := 0;
244 WHEN TOO_MANY_ROWS THEN
245 L_COUNT := 1;
246 END;
247 IF L_COUNT = 1 THEN
248 INSERT INTO WSM_PEND_TXN_REP_TMP
249 VALUES (WIP_ENTITY_ID_1
250 ,'Pending Operation Yield');
251 END IF;
252 BEGIN
253 SELECT
254 1
255 INTO L_COUNT
256 FROM
257 DUAL
258 WHERE exists (
259 SELECT
260 1
261 FROM
262 WSM_SM_STARTING_JOBS SJ,
263 WSM_SPLIT_MERGE_TRANSACTIONS WMT
264 WHERE SJ.WIP_ENTITY_ID = WIP_ENTITY_ID_1
265 AND SJ.TRANSACTION_ID = WMT.TRANSACTION_ID
266 AND WMT.ORGANIZATION_ID = P_ORGANIZATION_ID
267 AND WMT.STATUS <> 4
268 UNION
269 SELECT
270 1
271 FROM
272 WSM_SM_RESULTING_JOBS RJ,
273 WSM_SPLIT_MERGE_TRANSACTIONS WMT
274 WHERE RJ.WIP_ENTITY_ID = WIP_ENTITY_ID_1
275 AND RJ.TRANSACTION_ID = WMT.TRANSACTION_ID
276 AND WMT.ORGANIZATION_ID = P_ORGANIZATION_ID
277 AND WMT.STATUS <> 4 );
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN
280 L_COUNT := 0;
281 WHEN TOO_MANY_ROWS THEN
282 L_COUNT := 1;
283 END;
284 IF L_COUNT = 1 THEN
285 INSERT INTO WSM_PEND_TXN_REP_TMP
286 VALUES (WIP_ENTITY_ID_1
287 ,'Pending WIP Lot Transactions');
288 END IF;
289 BEGIN
290 SELECT
291 1
292 INTO L_COUNT
293 FROM
294 DUAL
295 WHERE exists (
296 SELECT
297 1
298 FROM
299 WSM_SM_STARTING_JOBS SJ,
300 WSM_SPLIT_MERGE_TRANSACTIONS WMT
301 WHERE SJ.WIP_ENTITY_ID = WIP_ENTITY_ID_1
302 AND SJ.TRANSACTION_ID = WMT.TRANSACTION_ID
303 AND WMT.ORGANIZATION_ID = P_ORGANIZATION_ID
304 AND NVL(WMT.COSTED
305 ,1) <> 4
306 UNION
307 SELECT
308 1
309 FROM
310 WSM_SM_RESULTING_JOBS RJ,
311 WSM_SPLIT_MERGE_TRANSACTIONS WMT
312 WHERE RJ.WIP_ENTITY_ID = WIP_ENTITY_ID_1
313 AND RJ.TRANSACTION_ID = WMT.TRANSACTION_ID
314 AND WMT.ORGANIZATION_ID = P_ORGANIZATION_ID
315 AND NVL(WMT.COSTED
316 ,1) <> 4 );
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 L_COUNT := 0;
320 WHEN TOO_MANY_ROWS THEN
321 L_COUNT := 1;
322 END;
323 IF L_COUNT = 1 THEN
324 INSERT INTO WSM_PEND_TXN_REP_TMP
325 VALUES (WIP_ENTITY_ID_1
326 ,'Uncosted WIP Lot Transactions ');
327 END IF;
328 BEGIN
329 SELECT
330 1
331 INTO L_COUNT
332 FROM
333 DUAL
334 WHERE exists (
335 SELECT
336 1
337 FROM
338 PO_RELEASES_ALL PR,
339 PO_HEADERS_ALL PH,
340 PO_DISTRIBUTIONS_ALL PD,
341 PO_LINE_LOCATIONS_ALL PL
342 WHERE PD.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
343 AND PD.WIP_ENTITY_ID = WIP_ENTITY_ID_1
344 AND PD.PO_LINE_ID is not null
345 AND PD.LINE_LOCATION_ID is not null
346 AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
347 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
348 AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
349 AND pr.po_release_id (+) = PD.PO_RELEASE_ID
350 AND NVL(PR.CANCEL_FLAG
351 ,'N') = 'N'
352 AND ( PL.QUANTITY_RECEIVED < ( PL.QUANTITY - PL.QUANTITY_CANCELLED ) ) )
353 OR exists (
354 SELECT
355 1
356 FROM
357 PO_REQUISITION_LINES_ALL PRL
358 WHERE PRL.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
359 AND PRL.WIP_ENTITY_ID = WIP_ENTITY_ID_1
360 AND NVL(PRL.CANCEL_FLAG
361 ,'N') = 'N'
362 AND PRL.LINE_LOCATION_ID is null )
363 OR exists (
364 SELECT
365 1
366 FROM
367 PO_REQUISITIONS_INTERFACE_ALL PRI
368 WHERE PRI.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
369 AND PRI.WIP_ENTITY_ID = WIP_ENTITY_ID_1 );
370 EXCEPTION
371 WHEN NO_DATA_FOUND THEN
372 L_COUNT := 0;
373 WHEN TOO_MANY_ROWS THEN
374 L_COUNT := 1;
375 END;
376 IF L_COUNT = 1 THEN
377 INSERT INTO WSM_PEND_TXN_REP_TMP
378 VALUES (WIP_ENTITY_ID_1
379 ,'Pending PO Requisitions');
380 END IF;
381 \*OPEN JOB_PTXN_CT
382 FOR SELECT
383 *
384 FROM
385 WSM_PEND_TXN_REP_TMP
386 WHERE WIP_ENTITY_ID = WIP_ENTITY_ID_1;*\
387 \*SELECT wip_entity_id , ptxn_table
388 BULK COLLECT INTO G_WSM_PEND_TXN_REP_PL_TBL
389 FROM WSM_PEND_TXN_REP_TMP
390 WHERE WIP_ENTITY_ID = WIP_ENTITY_ID_1;*\
391 commit;
392 RETURN (1);
393 END QR_PTXN_CTREFCURDS;*/
394
395 FUNCTION CF_EXCLUDE_RESERVEFORMULA RETURN CHAR IS
396 BEGIN
397 IF (P_EXCLUDE_RESERVED_JOBS = 1) THEN
398 RETURN ('and not exists (select 1 from WIP_RESERVATIONS_V wrv where wrv.wip_entity_id = wdj.wip_entity_id)');
399 ELSE
400 RETURN (' ');
401 END IF;
402 RETURN NULL;
403 END CF_EXCLUDE_RESERVEFORMULA;
404
405 FUNCTION CF_EXCLUDE_UNCOMPFORMULA RETURN CHAR IS
406 BEGIN
407 IF (P_EXCLUDE_UNCOMPLETE_JOBS = 1) THEN
408 RETURN ('and wdj.status_type in (4,5,14,15)');
409 ELSE
410 RETURN (' ');
411 END IF;
412 RETURN NULL;
413 END CF_EXCLUDE_UNCOMPFORMULA;
414
415 FUNCTION CF_ORG_CODEFORMULA RETURN CHAR IS
416 ORG_NAME VARCHAR2(240);
417 BEGIN
418 SELECT
419 ORGANIZATION_NAME
420 INTO ORG_NAME
421 FROM
422 ORG_ORGANIZATION_DEFINITIONS
423 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
424 RETURN (ORG_NAME);
425 END CF_ORG_CODEFORMULA;
426
427 FUNCTION CF_JOB_TYPEFORMULA RETURN CHAR IS
428 ENTITY_TYPE VARCHAR2(80);
429 BEGIN
430 SELECT
431 MEANING
432 INTO ENTITY_TYPE
433 FROM
434 MFG_LOOKUPS
435 WHERE LOOKUP_TYPE = 'WIP_ENTITY'
436 AND LOOKUP_CODE = P_ENTITY_TYPE;
437 RETURN (ENTITY_TYPE);
438 END CF_JOB_TYPEFORMULA;
439
440 FUNCTION AFTERPFORM RETURN BOOLEAN IS
441 BEGIN
442 IF (P_FROM_CLASS IS NOT NULL) THEN
443 IF (P_TO_CLASS IS NOT NULL) THEN
444 P_CLASS_RANGE := 'and wdj.class_code between
445 :p_from_class and :p_to_class';
446 ELSE
447 P_CLASS_RANGE := 'and wdj.class_code >= :p_from_class';
448 END IF;
449 ELSE
450 IF (P_TO_CLASS IS NOT NULL) THEN
451 P_CLASS_RANGE := 'and wdj.class_code <= :p_to_class';
452 ELSE
453 P_CLASS_RANGE := ' ';
454 END IF;
455 END IF;
456 IF (P_ACCT_CLASS_TYPE IS NULL) THEN
457 P_CLASS_TYPE := ' ';
458 ELSE
459 P_CLASS_TYPE := 'and wac.class_type = to_char(:P_ACCT_CLASS_TYPE)';
460 END IF;
461 IF (P_FROM_COMPLETE_DATE IS NOT NULL) THEN
462 IF (P_TO_COMPLETE_DATE IS NOT NULL) THEN
463 P_DATE_COMPLETE := 'and trunc(wdj.date_completed) between to_date(to_char(:p_from_complete_date,''YYYY/MM/DD'')
464 ,''YYYY/MM/DD'') and to_date(to_char(:p_to_complete_date,''YYYY/MM/DD''),''YYYY/MM/DD'')';
465 ELSE
466 P_DATE_COMPLETE := 'and trunc(wdj.date_completed) >= to_date(to_char(:p_from_complete_date,''YYYY/MM/DD'')
467 ,''YYYY/MM/DD'')';
468 END IF;
469 ELSE
470 IF (P_TO_COMPLETE_DATE IS NOT NULL) THEN
471 P_DATE_COMPLETE := 'and trunc(wdj.date_completed) <= to_date(to_char(:p_to_complete_date,''YYYY/MM/DD'')
472 ,''YYYY/MM/DD'')';
473 ELSE
474 P_DATE_COMPLETE := ' ';
475 END IF;
476 END IF;
477 IF (P_FROM_JOB IS NOT NULL) THEN
478 IF (P_TO_JOB IS NOT NULL) THEN
479 P_JOB_NAME := 'and we.wip_entity_name between :p_from_job and :p_to_job';
480 ELSE
481 P_JOB_NAME := 'and we.wip_entity_name >= :p_from_job';
482 END IF;
483 ELSE
484 IF (P_TO_JOB IS NOT NULL) THEN
485 P_JOB_NAME := 'and we.wip_entity_name <= :p_to_job';
486 ELSE
487 P_JOB_NAME := ' ';
488 END IF;
489 END IF;
490 IF (P_FROM_RELEASE_DATE IS NOT NULL) THEN
491 IF (P_TO_RELEASE_DATE IS NOT NULL) THEN
492 P_RELEASE_DATE := 'and trunc(wdj.date_released) between to_date(to_char(:p_from_release_date,''YYYY/MM/DD'')
493 ,''YYYY/MM/DD'') and to_date(to_char(:p_to_release_date,''YYYY/MM/DD'')
494 ,''YYYY/MM/DD'')';
495 ELSE
496 P_RELEASE_DATE := 'and trunc(wdj.date_released) >= to_date(to_char(:p_from_release_date,''YYYY/MM/DD'')
497 ,''YYYY/MM/DD'')';
498 END IF;
499 ELSE
500 IF (P_TO_RELEASE_DATE IS NOT NULL) THEN
501 P_RELEASE_DATE := 'and trunc(wdj.date_released) <= to_date(to_char(:p_to_release_date,''YYYY/MM/DD'')
502 ,''YYYY/MM/DD'')';
503 ELSE
504 P_RELEASE_DATE := ' ';
505 END IF;
506 END IF;
507 IF (P_FROM_START_DATE IS NOT NULL) THEN
508 IF (P_TO_START_DATE IS NOT NULL) THEN
509 P_START_DATE := 'and trunc(wdj.scheduled_start_date) between to_date(to_char(:p_from_start_date,''YYYY/MM/DD'')
510 ,''YYYY/MM/DD'') and to_date(to_char(:p_to_start_date,''YYYY/MM/DD''),''YYYY/MM/DD'')';
511 ELSE
512 P_START_DATE := 'and trunc(wdj.scheduled_start_date) >= to_date(to_char(:p_from_start_date,''YYYY/MM/DD'')
513 ,''YYYY/MM/DD'')';
514 END IF;
515 ELSE
516 IF (P_TO_START_DATE IS NOT NULL) THEN
517 P_START_DATE := 'and trunc(wdj.scheduled_start_date) <= to_date(to_char(:p_to_start_date,''YYYY/MM/DD'')
518 ,''YYYY/MM/DD'')';
519 ELSE
520 P_START_DATE := ' ';
521 END IF;
522 END IF;
523 RETURN (TRUE);
524 END AFTERPFORM;
525
526 FUNCTION P_DATE_COMPLETEVALIDTRIGGER RETURN BOOLEAN IS
527 BEGIN
528 RETURN (TRUE);
529 END P_DATE_COMPLETEVALIDTRIGGER;
530
531 FUNCTION CF_TIMEZONEFORMULA RETURN CHAR IS
532 BEGIN
533 RETURN FND_TIMEZONES.GET_NAME(FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
534 END CF_TIMEZONEFORMULA;
535
536 FUNCTION AFTERREPORT RETURN BOOLEAN IS
537 BEGIN
538 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
539 RETURN (TRUE);
540 END AFTERREPORT;
541
542 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
543 BEGIN
544 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
545 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
546 RETURN (TRUE);
547 END BEFOREREPORT;
548
549 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
550 BEGIN
551 RETURN (TRUE);
552 END BEFOREPFORM;
553
554 END WSM_WSMPNTXN_XMLP_PKG;
555
556
[Home] [Help]