[Home] [Help]
PACKAGE BODY: APPS.WIP_WIPREVAL_XMLP_PKG
Source
1 PACKAGE BODY WIP_WIPREVAL_XMLP_PKG AS
2 /* $Header: WIPREVALB.pls 120.1 2008/01/09 10:08:02 dwkrishn noship $ */
3 FUNCTION DISP_CURRENCYFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 RETURN (REPORT_OPTION || '(' || CURRENCY_CODE || ')');
6 END DISP_CURRENCYFORMULA;
7
8 FUNCTION ORG_NAME_HDRFORMULA RETURN VARCHAR2 IS
9 BEGIN
10 RETURN (ORG_NAME);
11 END ORG_NAME_HDRFORMULA;
12
13 FUNCTION TOT_CST_INC_APP_CSTFORMULA(TOT_ACT_ISS_STD IN NUMBER
14 ,TOT_RES_APP_COST IN NUMBER
15 ,TOT_RES_OVR_APP_COST IN NUMBER
16 ,TOT_MV_OVR_APP_COST IN NUMBER) RETURN NUMBER IS
17 BEGIN
18 /*SRW.REFERENCE(TOT_ACT_ISS_STD)*/NULL;
19 /*SRW.REFERENCE(TOT_RES_APP_COST)*/NULL;
20 /*SRW.REFERENCE(TOT_RES_OVR_APP_COST)*/NULL;
21 /*SRW.REFERENCE(TOT_MV_OVR_APP_COST)*/NULL;
22 RETURN (NVL(TOT_ACT_ISS_STD
23 ,0) + NVL(TOT_RES_APP_COST
24 ,0) + NVL(TOT_RES_OVR_APP_COST
25 ,0) + NVL(TOT_MV_OVR_APP_COST
26 ,0));
27 END TOT_CST_INC_APP_CSTFORMULA;
28
29 FUNCTION TOT_CST_INC_EFF_VARFORMULA(TOT_USG_VAR IN NUMBER
30 ,TOT_EFF_VAR IN NUMBER
31 ,TOT_RES_OVR_EFF_VAR IN NUMBER
32 ,TOT_MV_OVR_EFF_VAR IN NUMBER) RETURN NUMBER IS
33 BEGIN
34 /*SRW.REFERENCE(TOT_USG_VAR)*/NULL;
35 /*SRW.REFERENCE(TOT_EFF_VAR)*/NULL;
36 /*SRW.REFERENCE(TOT_RES_OVR_EFF_VAR)*/NULL;
37 /*SRW.REFERENCE(TOT_MV_OVR_EFF_VAR)*/NULL;
38 RETURN (NVL(TOT_USG_VAR
39 ,0) + NVL(TOT_EFF_VAR
40 ,0) + NVL(TOT_RES_OVR_EFF_VAR
41 ,0) + NVL(TOT_MV_OVR_EFF_VAR
42 ,0));
43 END TOT_CST_INC_EFF_VARFORMULA;
44
45 FUNCTION TOT_JOB_BALANCE_CSTFORMULA(TOT_CST_INC_APP_CST IN NUMBER
46 ,TOT_SCP_AND_COMP_CST IN NUMBER
47 ,TOT_CLOSE_TRX_CST IN NUMBER) RETURN NUMBER IS
48 BEGIN
49 /*SRW.REFERENCE(TOT_CST_INC_APP_CST)*/NULL;
50 /*SRW.REFERENCE(TOT_SCP_AND_COMP_CST)*/NULL;
51 /*SRW.REFERENCE(TOT_CLOSE_TRX_CST)*/NULL;
52 RETURN (NVL(TOT_CST_INC_APP_CST
53 ,0) - NVL(TOT_SCP_AND_COMP_CST
54 ,0) - NVL(TOT_CLOSE_TRX_CST
55 ,0));
56 END TOT_JOB_BALANCE_CSTFORMULA;
57
58 FUNCTION TOT_CST_INC_STD_CSTFORMULA(TOT_REQ_JOB_STD IN NUMBER
59 ,TOT_RES_STD_COST IN NUMBER
60 ,TOT_RES_OVR_STD_COST IN NUMBER
61 ,TOT_MV_OVR_STD_COST IN NUMBER) RETURN NUMBER IS
62 BEGIN
63 /*SRW.REFERENCE(TOT_REQ_JOB_STD)*/NULL;
64 /*SRW.REFERENCE(TOT_RES_STD_COST)*/NULL;
65 /*SRW.REFERENCE(TOT_RES_OVR_STD_COST)*/NULL;
66 /*SRW.REFERENCE(TOT_MV_OVR_STD_COST)*/NULL;
67 RETURN (NVL(TOT_REQ_JOB_STD
68 ,0) + NVL(TOT_RES_STD_COST
69 ,0) + NVL(TOT_RES_OVR_STD_COST
70 ,0) + NVL(TOT_MV_OVR_STD_COST
71 ,0));
72 END TOT_CST_INC_STD_CSTFORMULA;
73
74 FUNCTION AFTERREPORT RETURN BOOLEAN IS
75 BEGIN
76 BEGIN
77 EXECUTE IMMEDIATE
78 'DELETE FROM WIP_TEMP_REPORTS
79 WHERE PROGRAM_SOURCE = ''WIPREVAL''';
80 EXECUTE IMMEDIATE
81 'COMMIT';
82 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
83 END;
84 RETURN (TRUE);
85 END AFTERREPORT;
86
87 FUNCTION AFTERPFORM RETURN BOOLEAN IS
88 BEGIN
89 RETURN (TRUE);
90 END AFTERPFORM;
91
92 FUNCTION MAT_DISPFORMULA(QTY_REQ_SUM IN NUMBER
93 ,REQ_JOB_STD_SUM IN NUMBER
94 ,QTY_ISS_PER_OP_SUM IN NUMBER
95 ,ACT_ISS_STD_SUM IN NUMBER
96 ,SUPPLY_TYPE_CODE IN NUMBER) RETURN NUMBER IS
97 BEGIN
98 IF ((QTY_REQ_SUM <> 0) OR (REQ_JOB_STD_SUM <> 0) OR (QTY_ISS_PER_OP_SUM <> 0) OR (ACT_ISS_STD_SUM <> 0)) THEN
99 IF (SUPPLY_TYPE_CODE <> 4 AND SUPPLY_TYPE_CODE <> 5) THEN
100 RETURN (1);
101 ELSIF (QTY_ISS_PER_OP_SUM <> 0) THEN
102 RETURN (1);
103 ELSIF (SUPPLY_TYPE_CODE = 4) THEN
104 IF (P_INCLUDE_BULK = 1) THEN
105 RETURN (1);
106 ELSE
107 RETURN (0);
108 END IF;
109 ELSIF (SUPPLY_TYPE_CODE = 5) THEN
110 IF (P_INCLUDE_VENDOR = 1) THEN
111 RETURN (1);
112 ELSE
113 RETURN (0);
114 END IF;
115 END IF;
116 ELSE
117 RETURN (0);
118 END IF;
119 RETURN NULL;
120 END MAT_DISPFORMULA;
121
122 FUNCTION FROM_ASSY_DISP_P RETURN VARCHAR2 IS
123 BEGIN
124 RETURN FROM_ASSY_DISP;
125 END FROM_ASSY_DISP_P;
126
127 FUNCTION TO_LINE_DISP_P RETURN VARCHAR2 IS
128 BEGIN
129 RETURN TO_LINE_DISP;
130 END TO_LINE_DISP_P;
131
132 FUNCTION TO_CLASS_DISP_P RETURN VARCHAR2 IS
133 BEGIN
134 RETURN TO_CLASS_DISP;
135 END TO_CLASS_DISP_P;
136
137 FUNCTION TO_ASSY_DISP_P RETURN VARCHAR2 IS
138 BEGIN
139 RETURN TO_ASSY_DISP;
140 END TO_ASSY_DISP_P;
141
142 FUNCTION FROM_LINE_DISP_P RETURN VARCHAR2 IS
143 BEGIN
144 RETURN FROM_LINE_DISP;
145 END FROM_LINE_DISP_P;
146
147 FUNCTION FROM_CLASS_DISP_P RETURN VARCHAR2 IS
148 BEGIN
149 RETURN FROM_CLASS_DISP;
150 END FROM_CLASS_DISP_P;
151
152 FUNCTION CHART_OF_ACCTS_ID_P RETURN NUMBER IS
153 BEGIN
154 RETURN CHART_OF_ACCTS_ID;
155 END CHART_OF_ACCTS_ID_P;
156
157 FUNCTION C_COUNTER_P RETURN NUMBER IS
158 BEGIN
159 RETURN C_COUNTER;
160 END C_COUNTER_P;
161
162 FUNCTION CALENDAR_CODE_P RETURN VARCHAR2 IS
163 BEGIN
164 RETURN CALENDAR_CODE;
165 END CALENDAR_CODE_P;
166
167 FUNCTION CURRENCY_CODE_P RETURN VARCHAR2 IS
168 BEGIN
169 RETURN CURRENCY_CODE;
170 END CURRENCY_CODE_P;
171
172 FUNCTION WHERE_PERIOD_P RETURN VARCHAR2 IS
173 BEGIN
174 RETURN WHERE_PERIOD;
175 END WHERE_PERIOD_P;
176
177 FUNCTION ORG_NAME_P RETURN VARCHAR2 IS
178 BEGIN
179 RETURN ORG_NAME;
180 END ORG_NAME_P;
181
182 FUNCTION REPORT_SORT_BY_AFT_P RETURN VARCHAR2 IS
183 BEGIN
184 RETURN REPORT_SORT_BY_AFT;
185 END REPORT_SORT_BY_AFT_P;
186
187 FUNCTION EXCEPTION_SET_ID_P RETURN NUMBER IS
188 BEGIN
189 RETURN EXCEPTION_SET_ID;
190 END EXCEPTION_SET_ID_P;
191
192 FUNCTION REPORT_OPTION_P RETURN VARCHAR2 IS
193 BEGIN
194 RETURN REPORT_OPTION;
195 END REPORT_OPTION_P;
196
197 FUNCTION WHERE_CLASS_P RETURN VARCHAR2 IS
198 BEGIN
199 RETURN WHERE_CLASS;
200 END WHERE_CLASS_P;
201
202 FUNCTION WHERE_ASSEMBLY_P RETURN VARCHAR2 IS
203 BEGIN
204 RETURN WHERE_ASSEMBLY;
205 END WHERE_ASSEMBLY_P;
206
207 FUNCTION REPORT_SORT_P RETURN VARCHAR2 IS
208 BEGIN
209 RETURN REPORT_SORT;
210 END REPORT_SORT_P;
211
212 FUNCTION WHERE_LINE_P RETURN VARCHAR2 IS
213 BEGIN
214 RETURN WHERE_LINE;
215 END WHERE_LINE_P;
216
217 FUNCTION PRECISION_P RETURN NUMBER IS
218 BEGIN
219 RETURN PRECISION;
220 END PRECISION_P;
221
222 FUNCTION EXT_PRECISION_P RETURN NUMBER IS
223 BEGIN
224 RETURN EXT_PRECISION;
225 END EXT_PRECISION_P;
226
227 FUNCTION C_INCLUDE_VENDOR_P RETURN VARCHAR2 IS
228 BEGIN
229 RETURN C_INCLUDE_VENDOR;
230 END C_INCLUDE_VENDOR_P;
231
232 FUNCTION C_INCLUDE_BULK_P RETURN VARCHAR2 IS
233 BEGIN
234 RETURN C_INCLUDE_BULK;
235 END C_INCLUDE_BULK_P;
236
237 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
238 BEGIN
239
240 DECLARE
241 canonical varchar2(10) := 'DDMMYYYY';
242 BEGIN
243
244 C_FROM_PERIOD_START_DATE := to_date(to_char(FROM_PERIOD_START_DATE,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS');
245 C_TO_PERIOD_END_DATE := to_date(to_char(TO_PERIOD_END_DATE,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS');
246
247 --SRW.MESSAGE(0, 'IN BEFORE REPORT TRIGGER');
248
249 SELECT OOD.ORGANIZATION_NAME,
250 OOD.CHART_OF_ACCOUNTS_ID,
251 SOB.CURRENCY_CODE,
252 FC.EXTENDED_PRECISION,
253 FC.PRECISION,
254 RPT_RUN_OPT.MEANING,
255 MP.CALENDAR_CODE,
256 MP.CALENDAR_EXCEPTION_SET_ID,
257 ML1.MEANING,
258 ML2.MEANING
259 INTO ORG_NAME,
260 CHART_OF_ACCTS_ID,
261 CURRENCY_CODE,
262 EXT_PRECISION,
263 PRECISION,
264 REPORT_OPTION,
265 CALENDAR_CODE,
266 EXCEPTION_SET_ID,
267 C_Include_Bulk,
268 C_Include_Vendor
269 FROM FND_CURRENCIES FC,
270 GL_SETS_OF_BOOKS SOB,
271 ORG_ORGANIZATION_DEFINITIONS OOD,
272 MFG_LOOKUPS RPT_RUN_OPT,
273 MTL_PARAMETERS MP,
274 MFG_LOOKUPS ML1,
275 MFG_LOOKUPS ML2
276 WHERE OOD.ORGANIZATION_ID = ORG_ID
277 AND OOD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
278 AND MP.ORGANIZATION_ID = ORG_ID
279 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
280 AND FC.ENABLED_FLAG = 'Y'
281 AND RPT_RUN_OPT.LOOKUP_TYPE = 'CST_WIP_VALUE_REPORT_TYPE'
282 AND RPT_RUN_OPT.LOOKUP_CODE = REPORT_RUN_OPT
283 AND ML1.LOOKUP_CODE = NVL(P_Include_Bulk,2)
284 AND ML1.LOOKUP_TYPE = 'SYS_YES_NO'
285 AND ML2.LOOKUP_CODE = NVL(P_Include_Vendor,2)
286 AND ML2.LOOKUP_TYPE = 'SYS_YES_NO';
287
288
289 -- if user entered null, make sure we display null, i.e. use _disp
290
291 FROM_ASSY_DISP := FROM_ASSEMBLY;
292 TO_ASSY_DISP := TO_ASSEMBLY;
293 FROM_CLASS_DISP := FROM_CLASS;
294 TO_CLASS_DISP := TO_CLASS;
295 FROM_LINE_DISP :=FROM_LINE;
296 TO_LINE_DISP := TO_LINE;
297
298 IF FROM_ASSEMBLY IS NULL THEN
299 SELECT NVL(MIN(WE.WIP_ENTITY_NAME),'X')
300 INTO FROM_ASSEMBLY
301 FROM WIP_ENTITIES WE
302 WHERE WE.ORGANIZATION_ID = ORG_ID
303 AND WE.ENTITY_TYPE = 2;
304 END IF;
305
306 IF TO_ASSEMBLY IS NULL THEN
307 SELECT NVL(MAX(WE.WIP_ENTITY_NAME),'X')
308 INTO TO_ASSEMBLY
309 FROM WIP_ENTITIES WE
310 WHERE WE.ORGANIZATION_ID = ORG_ID
311 AND WE.ENTITY_TYPE = 2;
312 END IF;
313
314 IF FROM_CLASS IS NULL THEN
315 SELECT NVL(MIN(WRI.CLASS_CODE),'X')
316 INTO FROM_CLASS
317 FROM WIP_REPETITIVE_ITEMS WRI
318 WHERE WRI.ORGANIZATION_ID = ORG_ID;
319 END IF;
320
321 IF TO_CLASS IS NULL THEN
322 SELECT NVL(MAX(WRI.CLASS_CODE),'X')
323 INTO TO_CLASS
324 FROM WIP_REPETITIVE_ITEMS WRI
325 WHERE WRI.ORGANIZATION_ID = ORG_ID;
326 END IF;
327
328 IF FROM_LINE IS NULL THEN
329 SELECT NVL(MIN(WL.LINE_CODE),'X')
330 INTO FROM_LINE
331 FROM WIP_LINES WL
332 WHERE WL.ORGANIZATION_ID = ORG_ID;
333 END IF;
334
335 IF TO_LINE IS NULL THEN
336 SELECT NVL(MAX(WL.LINE_CODE),'X')
337 INTO TO_LINE
338 FROM WIP_LINES WL
339 WHERE WL.ORGANIZATION_ID = ORG_ID;
340 END IF;
341
342 --SRW.USER_EXIT('FND SRWINIT');
343
344 /*SRW.USER_EXIT('FND FLEXSQL CODE="GL#" NUM=":CHART_OF_ACCTS_ID"
345 APPL_SHORT_NAME="SQLGL" OUTPUT=":P_FLEXDATA_ACCT"
346 MODE="SELECT" DISPLAY="ALL"
347 TABLEALIAS="GCC"');*/
348
349 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK"
350 APPL_SHORT_NAME="INV" OUTPUT=":P_FLEXDATA_ITEM"
351 MODE="SELECT" DISPLAY="ALL"
352 TABLEALIAS="MSI"');*/
353
354 EXECUTE IMMEDIATE'delete from wip_temp_reports
355 where program_source = ''WIPREVAL''';
356
357
358 --SRW.MESSAGE(444, 'DONE WITH test2 INSERT');
359 TO_PERIOD_END_DATE_char:=to_char(TO_PERIOD_END_DATE,'DD-MON-YY');
360
361 FROM_PERIOD_START_DATE_char:=to_char(FROM_PERIOD_START_DATE,'DD-MON-YY');
362
363
364
365 EXECUTE IMMEDIATE 'insert into wip_temp_reports
366 (organization_id,
367 program_source,
368 last_updated_by,
369 wip_entity_id,
370 key1,
371 key2,
372 key3,
373 key4,
374 key5,
375 attribute1,
376 date1,
377 date2)
378 select /*+ RULE */
379 wrs.organization_id,
380 ''WIPREVAL'',
381 31,
382 wrs.wip_entity_id,
383 wrs.line_id,
384 wrs.repetitive_schedule_id,
385 decode(sign(trunc(wrs.last_unit_start_date)-
386 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
387 -1, (bcd1.prior_seq_num - bcd2.next_seq_num +
388 decode(mod(wrs.processing_work_days,1),0,1,
389 mod(wrs.processing_work_days,1))),
390 1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
391 (bcd1.prior_seq_num - bcd2.next_seq_num +
392 decode(mod(wrs.processing_work_days,1),0,1,
393 mod(wrs.processing_work_days,1)))),
394 0,
395 0,
396 ''N'',to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
397 ,
398 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
399 from
400 bom_calendar_dates bcd1,
401 bom_calendar_dates bcd2,
402 wip_entities we,
403 wip_lines wl,
404 wip_repetitive_items wri,
405 wip_repetitive_schedules wrs
406 where
407 bcd1.calendar_date =
408 decode(sign(trunc(wrs.last_unit_start_date) -
409 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
410 -1, trunc(wrs.last_unit_start_date),
411 1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
412 trunc(wrs.last_unit_start_date))
413 and bcd2.calendar_date =
414 decode(sign(
415 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
416 trunc(wrs.first_unit_start_date)),
417 -1, trunc(wrs.first_unit_start_date),
418 1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
419 trunc(wrs.first_unit_start_date))
420 and bcd1.calendar_code = :CALENDAR_CODE
421 and bcd2.calendar_code = :CALENDAR_CODE
422 and bcd1.exception_set_id = :EXCEPTION_SET_ID
423 and bcd2.exception_set_id = :EXCEPTION_SET_ID
424 and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
425 and we.entity_type = 2
426 and we.organization_id = :ORG_ID
427 and wrs.wip_entity_id = we.wip_entity_id
428 and wl.line_code between :FROM_LINE and :TO_LINE
429 and wl.organization_id = :ORG_ID
430 and wrs.line_id = wl.line_id
431 and wri.line_id = wrs.line_id
432 and wri.class_code between :FROM_CLASS and :TO_CLASS
433 and wri.organization_id = :ORG_ID
434 and wri.wip_entity_id = wrs.wip_entity_id
435 and wrs.organization_id = :ORG_ID
436 and ((trunc(wrs.first_unit_start_date) between
437 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
438 and to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''))
439 or (trunc(wrs.last_unit_start_date) between
440 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
441 and to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''))
442 or ((trunc(wrs.first_unit_start_date) <
443 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''))
444 and (trunc(wrs.last_unit_start_date) >
445 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''))))
446 and not exists
447 (select mmta.repetitive_schedule_id
448 from mtl_material_txn_allocations mmta,
449 mtl_material_transactions mmt
450 where mmt.transaction_date >=
451 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
452 and mmt.transaction_date < to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
453 and mmt.transaction_id = mmta.transaction_id
454 and mmta.organization_id = mmt.organization_id
455 and mmt.organization_id = :ORG_ID
456 and mmt.transaction_source_id = we.wip_entity_id
457 and mmt.transaction_source_type_id + 0 = 5
458 and mmta.repetitive_schedule_id = wrs.repetitive_schedule_id)
459 and wrs.repetitive_schedule_id in
460 (select repetitive_schedule_id
461 from wip_period_balances
462 where repetitive_schedule_id = wrs.repetitive_schedule_id
463 and organization_id = wrs.organization_id
464 and wip_entity_id = wrs.wip_entity_id)
465 group by
466 wrs.organization_id,
467 wrs.wip_entity_id,
468 wrs.line_id,
469 wrs.repetitive_schedule_id,
470 wrs.last_unit_start_date,
471 wrs.processing_work_days,
472 (bcd1.prior_seq_num - bcd2.next_seq_num)'
473 USING
474
475 CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID , FROM_ASSEMBLY , TO_ASSEMBLY, ORG_ID
476 , FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS , TO_CLASS , ORG_ID , ORG_ID , ORG_ID ;
477
478 --SRW.MESSAGE(5, 'DONE WITH 1 INSERT');
479
480 EXECUTE IMMEDIATE 'insert into wip_temp_reports
481 (organization_id,
482 program_source,
483 last_updated_by,
484 wip_entity_id,
485 key1,
486 key2,
487 key3,
488 key4,
489 key5,
490 attribute1,
491 date1,
492 date2)
493 select /*+ RULE */
494 wrs.organization_id,
495 ''WIPREVAL'',
496 31,
497 wrs.wip_entity_id,
498 wrs.line_id,
499 wrs.repetitive_schedule_id,
500 decode(sign(trunc(wrs.last_unit_start_date)-
501 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
502 -1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
503 -1, 0,
504 1, (bcd1.prior_seq_num - bcd2.next_seq_num +
505 decode(mod(wrs.processing_work_days,1),0,1,
506 mod(wrs.processing_work_days,1))),
507 (bcd1.prior_seq_num - bcd2.next_seq_num +
508 decode(mod(wrs.processing_work_days,1),0,1,
509 mod(wrs.processing_work_days,1))))),
510 1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
511 -1, 0,
512 1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
513 (bcd1.prior_seq_num - bcd2.next_seq_num + 1))),
514 (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
515 -1, 0,
516 1, (bcd1.prior_seq_num - bcd2.next_seq_num +
517 decode(mod(wrs.processing_work_days,1),0,1,
518 mod(wrs.processing_work_days,1))),
519 (bcd1.prior_seq_num - bcd2.next_seq_num +
520 decode(mod(wrs.processing_work_days,1),0,1,
521 mod(wrs.processing_work_days,1)))))),
522 sum(mmta1.primary_quantity),
523 sum(mmta2.primary_quantity),
524 ''N'',
525 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
526 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
527 from
528 mtl_material_transactions mmt1,
529 mtl_material_txn_allocations mmta1,
530 mtl_material_transactions mmt2,
531 mtl_material_txn_allocations mmta2,
532 bom_calendar_dates bcd1,
533 bom_calendar_dates bcd2,
534 wip_lines wl,
535 wip_repetitive_items wri,
536 wip_repetitive_schedules wrs,
537 wip_entities we
538 where
539 bcd1.calendar_date =
540 decode(sign(trunc(wrs.last_unit_start_date) -
541 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
542 -1, trunc(wrs.last_unit_start_date),
543 1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
544 trunc(wrs.last_unit_start_date))
545 and bcd2.calendar_date =
546 decode(sign(
547 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
548 trunc(wrs.first_unit_start_date)),
549 -1, trunc(wrs.first_unit_start_date),
550 1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
551 trunc(wrs.first_unit_start_date))
552 and bcd1.calendar_code = :CALENDAR_CODE
553 and bcd2.calendar_code = :CALENDAR_CODE
554 and bcd1.exception_set_id = :EXCEPTION_SET_ID
555 and bcd2.exception_set_id = :EXCEPTION_SET_ID
556 and mmt1.transaction_action_id in (31,32)
557 and mmt1.transaction_date >=
558 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
559 and mmt1.transaction_date <
560 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
561 and mmt1.transaction_id = mmta1.transaction_id
562 and mmta1.organization_id = mmt1.organization_id
563 and mmt1.organization_id = :ORG_ID
564 and mmt1.transaction_source_id = we.wip_entity_id
565 and mmt1.transaction_source_type_id + 0 = 5
566 and mmt2.transaction_source_id = we.wip_entity_id
567 and mmt2.transaction_source_type_id + 0 = 5
568 and mmt2.transaction_action_id = 30
569 and mmt2.transaction_date >=
570 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
571 and mmt2.transaction_date <
572 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
573 and mmt2.transaction_id = mmta2.transaction_id
574 and mmta2.organization_id = mmt2.organization_id
575 and mmt2.organization_id = :ORG_ID
576 and mmta2.repetitive_schedule_id = wrs.repetitive_schedule_id
577 and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
578 and we.entity_type = 2
579 and we.organization_id = :ORG_ID
580 and wrs.wip_entity_id = we.wip_entity_id
581 and wl.line_code between :FROM_LINE and :TO_LINE
582 and wl.organization_id = :ORG_ID
583 and wrs.line_id = wl.line_id
584 and wri.line_id = wrs.line_id
585 and wri.class_code between :FROM_CLASS and :TO_CLASS
586 and wri.organization_id = :ORG_ID
587 and wri.wip_entity_id = wrs.wip_entity_id
588 and wrs.organization_id = :ORG_ID
589 and mmta1.repetitive_schedule_id = wrs.repetitive_schedule_id
590 group by mmta1.repetitive_schedule_id,
591 mmta2.repetitive_schedule_id,
592 wrs.organization_id,
593 wrs.wip_entity_id,
594 wrs.line_id,
595 wrs.repetitive_schedule_id,
596 wrs.last_unit_start_date,
597 wrs.processing_work_days,
598 (bcd1.prior_seq_num - bcd2.next_seq_num)'
599 USING
600 CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID , ORG_ID , ORG_ID , FROM_ASSEMBLY , TO_ASSEMBLY , ORG_ID
601 , FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS, TO_CLASS , ORG_ID , ORG_ID ;
602
603 --SRW.MESSAGE(6, 'DONE WITH 2 INSERTS');
607 (select sum(mmta1.primary_quantity)
604
605 EXECUTE IMMEDIATE 'update wip_temp_reports wtr
606 set key4 =
608 from mtl_material_transactions mmt1,
609 mtl_material_txn_allocations mmta1
610 where mmt1.transaction_action_id in (31,32)
611 and mmt1.transaction_date >=
612 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
613 and mmt1.transaction_date <
614 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
615 and mmt1.transaction_id = mmta1.transaction_id
616 and mmta1.organization_id = mmt1.organization_id
617 and mmt1.organization_id = :ORG_ID
618 and mmt1.transaction_source_id = wtr.wip_entity_id
619 and mmt1.transaction_source_type_id + 0 = 5
620 and mmta1.repetitive_schedule_id = wtr.key2)
621 where wtr.key4 <> 0
622 and wtr.key5 <> 0
623 and wtr.program_source = ''WIPREVAL'''
624 USING
625 ORG_ID;
626
627 --SRW.MESSAGE(4, 'DONE WIT');
628
629 EXECUTE IMMEDIATE 'update wip_temp_reports wtr
630 set key5 =
631 (select sum(mmta2.primary_quantity)
632 from mtl_material_transactions mmt2,
633 mtl_material_txn_allocations mmta2
634 where mmt2.transaction_action_id = 30
635 and mmt2.transaction_date >=
636 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
637 and mmt2.transaction_date <
638 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
639 and mmt2.transaction_id = mmta2.transaction_id
640 and mmta2.organization_id = mmt2.organization_id
641 and mmt2.organization_id = :ORG_ID
642 and mmt2.transaction_source_id = wtr.wip_entity_id
643 and mmt2.transaction_source_type_id + 0 = 5
644 and mmta2.repetitive_schedule_id = wtr.key2)
645 where wtr.key4 <> 0
646 and wtr.key5 <> 0
647 and wtr.program_source = ''WIPREVAL'''
648 USING
649 ORG_ID;
650
651
652 EXECUTE IMMEDIATE 'insert into wip_temp_reports
653 (organization_id,
654 program_source,
655 last_updated_by,
656 wip_entity_id,
657 key1,
658 key2,
659 key3,
660 key4,
661 key5,
662 attribute1,
663 date1,
664 date2)
665 select /*+ RULE */
666 wrs.organization_id,
667 ''WIPREVAL'',
668 31,
669 wrs.wip_entity_id,
670 wrs.line_id,
671 wrs.repetitive_schedule_id,
672 decode(sign(trunc(wrs.last_unit_start_date)-
673 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
674 -1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
675 -1, 0,
676 1, (bcd1.prior_seq_num - bcd2.next_seq_num +
677 decode(mod(wrs.processing_work_days,1),0,1,
678 mod(wrs.processing_work_days,1))),
679 (bcd1.prior_seq_num - bcd2.next_seq_num +
680 decode(mod(wrs.processing_work_days,1),0,1,
681 mod(wrs.processing_work_days,1))))),
682 1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
683 -1, 0,
684 1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
685 (bcd1.prior_seq_num - bcd2.next_seq_num + 1))),
686 (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
687 -1, 0,
688 1, (bcd1.prior_seq_num - bcd2.next_seq_num +
689 decode(mod(wrs.processing_work_days,1),0,1,
690 mod(wrs.processing_work_days,1))),
691 (bcd1.prior_seq_num - bcd2.next_seq_num +
692 decode(mod(wrs.processing_work_days,1),0,1,
693 mod(wrs.processing_work_days,1)))))),
694 sum(mmta1.primary_quantity),
695 0,
696 ''N'',
697 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
698 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
699 from
700 mtl_material_transactions mmt1,
701 mtl_material_txn_allocations mmta1,
702 bom_calendar_dates bcd1,
703 bom_calendar_dates bcd2,
704 wip_lines wl,
705 wip_repetitive_schedules wrs,
706 wip_repetitive_items wri,
707 wip_entities we
708 where
709 bcd1.calendar_date =
710 decode(sign(trunc(wrs.last_unit_start_date) -
711 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
712 -1, trunc(wrs.last_unit_start_date),
713 1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
714 trunc(wrs.last_unit_start_date))
715 and bcd2.calendar_date =
716 decode(sign(
717 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
718 trunc(wrs.first_unit_start_date)),
719 -1, trunc(wrs.first_unit_start_date),
720 1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
721 trunc(wrs.first_unit_start_date))
722 and bcd1.calendar_code = :CALENDAR_CODE
723 and bcd2.calendar_code = :CALENDAR_CODE
724 and bcd1.exception_set_id = :EXCEPTION_SET_ID
725 and bcd2.exception_set_id = :EXCEPTION_SET_ID
726 and mmt1.transaction_action_id in (31,32)
727 and mmt1.transaction_date >=
728 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
729 and mmt1.transaction_date <
730 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')+ 1
731 and mmt1.transaction_id = mmta1.transaction_id
732 and mmta1.organization_id = mmt1.organization_id
733 and mmt1.organization_id = :ORG_ID
734 and mmt1.transaction_source_id = we.wip_entity_id
735 and mmt1.transaction_source_type_id + 0 = 5
736 and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
737 and we.entity_type = 2
738 and we.organization_id = :ORG_ID
739 and wrs.wip_entity_id = we.wip_entity_id
740 and wl.line_code between :FROM_LINE and :TO_LINE
741 and wl.organization_id = :ORG_ID
742 and wrs.line_id = wl.line_id
743 and wri.line_id = wrs.line_id
744 and wri.class_code between :FROM_CLASS and :TO_CLASS
745 and wri.organization_id = :ORG_ID
746 and wri.wip_entity_id = wrs.wip_entity_id
747 and wrs.organization_id = :ORG_ID
748 and mmta1.repetitive_schedule_id = wrs.repetitive_schedule_id
749 and mmta1.repetitive_schedule_id not in
750 (select key2
751 from wip_temp_reports
752 where program_source = ''WIPREVAL'')
753 group by mmta1.repetitive_schedule_id,
754 wrs.organization_id,
755 wrs.wip_entity_id,
756 wrs.line_id,
757 wrs.repetitive_schedule_id,
758 wrs.last_unit_start_date,
759 wrs.processing_work_days,
760 (bcd1.prior_seq_num - bcd2.next_seq_num)'
761 USING
762 CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID ,
763 ORG_ID , FROM_ASSEMBLY , TO_ASSEMBLY, ORG_ID , FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS , TO_CLASS , ORG_ID , ORG_ID ;
764
765 --SRW.MESSAGE(7, 'DONE WITH 3 INSERTS');
766
767 EXECUTE IMMEDIATE 'insert into wip_temp_reports
768 (organization_id,
769 program_source,
770 last_updated_by,
771 wip_entity_id,
772 key1,
773 key2,
774 key3,
775 key4,
776 key5,
777 attribute1,
778 date1,
779 date2)
780 select /*+ RULE */
781 wrs.organization_id,
782 ''WIPREVAL'',
783 31,
784 wrs.wip_entity_id,
785 wrs.line_id,
786 wrs.repetitive_schedule_id,
787 decode(sign(trunc(wrs.last_unit_start_date)-
788 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
789 -1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
790 -1, 0,
791 1, (bcd1.prior_seq_num - bcd2.next_seq_num +
792 decode(mod(wrs.processing_work_days,1),0,1,
793 mod(wrs.processing_work_days,1))),
794 (bcd1.prior_seq_num - bcd2.next_seq_num +
795 decode(mod(wrs.processing_work_days,1),0,1,
796 mod(wrs.processing_work_days,1))))),
797 1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
798 -1, 0,
799 1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
800 (bcd1.prior_seq_num - bcd2.next_seq_num + 1))),
801 (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
802 -1, 0,
803 1, (bcd1.prior_seq_num - bcd2.next_seq_num +
804 decode(mod(wrs.processing_work_days,1),0,1,
805 mod(wrs.processing_work_days,1))),
806 (bcd1.prior_seq_num - bcd2.next_seq_num +
807 decode(mod(wrs.processing_work_days,1),0,1,
808 mod(wrs.processing_work_days,1)))))),
809 0,
810 sum(mmta2.primary_quantity),
811 ''N'',
812 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
813 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
814 from
815 mtl_material_transactions mmt2,
816 mtl_material_txn_allocations mmta2,
817 bom_calendar_dates bcd1,
818 bom_calendar_dates bcd2,
819 wip_lines wl,
820 wip_repetitive_schedules wrs,
821 wip_repetitive_items wri,
822 wip_entities we
823 where
824 bcd1.calendar_date =
825 decode(sign(trunc(wrs.last_unit_start_date) -
826 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
827 -1, trunc(wrs.last_unit_start_date),
828 1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
829 trunc(wrs.last_unit_start_date))
830 and bcd2.calendar_date =
831 decode(sign(
832 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
833 trunc(wrs.first_unit_start_date)),
834 -1, trunc(wrs.first_unit_start_date),
835 1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
836 trunc(wrs.first_unit_start_date))
837 and bcd1.calendar_code = :CALENDAR_CODE
838 and bcd2.calendar_code = :CALENDAR_CODE
839 and bcd1.exception_set_id = :EXCEPTION_SET_ID
840 and bcd2.exception_set_id = :EXCEPTION_SET_ID
841 and mmt2.transaction_action_id = 30
842 and mmt2.transaction_date >=
843 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
847 and mmta2.organization_id = mmt2.organization_id
844 and mmt2.transaction_date <
845 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
846 and mmt2.transaction_id = mmta2.transaction_id
848 and mmt2.organization_id = :ORG_ID
849 and mmt2.transaction_source_id = we.wip_entity_id
850 and mmt2.transaction_source_type_id + 0 = 5
851 and mmta2.repetitive_schedule_id = wrs.repetitive_schedule_id
852 and mmta2.repetitive_schedule_id not in
853 (select key2
854 from wip_temp_reports
855 where program_source = ''WIPREVAL'')
856 and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
857 and we.entity_type = 2
858 and we.organization_id = :ORG_ID
859 and wrs.wip_entity_id = we.wip_entity_id
860 and wl.line_code between :FROM_LINE and :TO_LINE
861 and wl.organization_id = :ORG_ID
862 and wrs.line_id = wl.line_id
863 and wri.line_id = wrs.line_id
864 and wri.class_code between :FROM_CLASS and :TO_CLASS
865 and wri.organization_id = :ORG_ID
866 and wri.wip_entity_id = wrs.wip_entity_id
867 and wrs.organization_id = :ORG_ID
868 group by mmta2.repetitive_schedule_id,
869 wrs.organization_id,
870 wrs.wip_entity_id,
871 wrs.line_id,
872 wrs.repetitive_schedule_id,
873 wrs.last_unit_start_date,
874 wrs.processing_work_days,
875 (bcd1.prior_seq_num - bcd2.next_seq_num)'
876 USING
877 CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID ,
878 ORG_ID , FROM_ASSEMBLY , TO_ASSEMBLY, ORG_ID , FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS , TO_CLASS , ORG_ID , ORG_ID;
879
880 --SRW.MESSAGE(8, 'DONE WITH 4 INSERTS');
881
882 EXECUTE IMMEDIATE 'insert into wip_temp_reports
883 (organization_id,
884 program_source,
885 last_updated_by,
886 wip_entity_id,
887 key1,
888 key2,
889 key3,
890 key4,
891 key5,
892 attribute1,
893 date1,
894 date2)
895 select /*+ RULE */
896 wrs.organization_id,
897 ''WIPREVAL'',
898 31,
899 wrs.wip_entity_id,
900 wrs.line_id,
901 wrs.repetitive_schedule_id,
902 decode(sign(trunc(wrs.last_unit_start_date)-
903 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
904 -1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
905 -1, 0,
906 1, (bcd1.prior_seq_num - bcd2.next_seq_num +
907 decode(mod(wrs.processing_work_days,1),0,1,
908 mod(wrs.processing_work_days,1))),
909 (bcd1.prior_seq_num - bcd2.next_seq_num +
910 decode(mod(wrs.processing_work_days,1),0,1,
911 mod(wrs.processing_work_days,1))))),
912 1, (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
913 -1, 0,
914 1, (bcd1.prior_seq_num - bcd2.next_seq_num + 1),
915 (bcd1.prior_seq_num - bcd2.next_seq_num + 1))),
916 (decode(sign(bcd1.prior_seq_num - bcd2.next_seq_num + 1),
917 -1, 0,
918 1, (bcd1.prior_seq_num - bcd2.next_seq_num +
919 decode(mod(wrs.processing_work_days,1),0,1,
920 mod(wrs.processing_work_days,1))),
921 (bcd1.prior_seq_num - bcd2.next_seq_num +
922 decode(mod(wrs.processing_work_days,1),0,1,
923 mod(wrs.processing_work_days,1)))))),
924 0,
925 0,
926 ''Y'',
927 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
928 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
929 from
930 mtl_material_transactions mmt3,
931 mtl_material_txn_allocations mmta3,
932 bom_calendar_dates bcd1,
933 bom_calendar_dates bcd2,
934 wip_lines wl,
935 wip_repetitive_schedules wrs,
936 wip_repetitive_items wri,
937 wip_entities we
938 where
939 bcd1.calendar_date =
940 decode(sign(trunc(wrs.last_unit_start_date) -
941 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')),
942 -1, trunc(wrs.last_unit_start_date),
943 1, to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
944 trunc(wrs.last_unit_start_date))
945 and bcd2.calendar_date =
946 decode(sign(
947 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') -
948 trunc(wrs.first_unit_start_date)),
949 -1, trunc(wrs.first_unit_start_date),
950 1, to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||'''),
951 trunc(wrs.first_unit_start_date))
952 and bcd1.calendar_code = :CALENDAR_CODE
953 and bcd2.calendar_code = :CALENDAR_CODE
954 and bcd1.exception_set_id = :EXCEPTION_SET_ID
955 and bcd2.exception_set_id = :EXCEPTION_SET_ID
956 and mmt3.transaction_action_id in (1,27,33,34)
957 and mmt3.transaction_date >=
958 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
959 and mmt3.transaction_date <
960 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
961 and mmt3.transaction_id = mmta3.transaction_id
962 and mmta3.organization_id = mmt3.organization_id
963 and mmt3.organization_id = :ORG_ID
964 and mmt3.transaction_source_id = we.wip_entity_id
965 and mmt3.transaction_source_type_id + 0 = 5
966 and mmta3.repetitive_schedule_id = wrs.repetitive_schedule_id
967 and mmta3.repetitive_schedule_id not in
968 (select key2
969 from wip_temp_reports
970 where program_source = ''WIPREVAL'')
971 and we.wip_entity_name between :FROM_ASSEMBLY and :TO_ASSEMBLY
972 and we.entity_type = 2
973 and we.organization_id = :ORG_ID
974 and wrs.wip_entity_id = we.wip_entity_id
975 and wl.line_code between :FROM_LINE and :TO_LINE
976 and wl.organization_id = :ORG_ID
977 and wrs.line_id = wl.line_id
978 and wri.line_id = wrs.line_id
979 and wri.class_code between :FROM_CLASS and :TO_CLASS
980 and wri.organization_id = :ORG_ID
981 and wri.wip_entity_id = wrs.wip_entity_id
982 and wrs.organization_id = :ORG_ID
983 and mmta3.repetitive_schedule_id = wrs.repetitive_schedule_id
984 group by mmta3.repetitive_schedule_id,
985 wrs.organization_id,
986 wrs.wip_entity_id,
987 wrs.line_id,
988 wrs.repetitive_schedule_id,
989 wrs.last_unit_start_date,
990 wrs.processing_work_days,
991 (bcd1.prior_seq_num - bcd2.next_seq_num)'
992
993 USING
994 CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID ,
995 ORG_ID , FROM_ASSEMBLY , TO_ASSEMBLY , ORG_ID , FROM_LINE , TO_LINE , ORG_ID , FROM_CLASS , TO_CLASS , ORG_ID , ORG_ID;
996
997 --SRW.MESSAGE(9, 'DONE WITH 5 INSERTS');
998
999 EXECUTE IMMEDIATE 'update wip_temp_reports wtr
1000 set attribute1 = ''Y''
1001 where key2 in
1002 (select /*+ RULE */
1003 wrs.repetitive_schedule_id
1004 from
1005 wip_repetitive_schedules wrs,
1006 mtl_material_transactions mmt3,
1007 mtl_material_txn_allocations mmta3
1008 where
1009 mmt3.transaction_action_id in (1,27,33,34)
1010 and mmt3.transaction_date >=
1011 to_date('||''''|| C_FROM_PERIOD_START_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''')
1012 and mmt3.transaction_date <
1013 to_date('||''''|| C_TO_PERIOD_END_DATE || ''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''') + 1
1014 and mmt3.transaction_id = mmta3.transaction_id
1015 and mmta3.organization_id = mmt3.organization_id
1016 and mmt3.organization_id = :ORG_ID
1017 and mmt3.transaction_source_id = wrs.wip_entity_id
1018 and mmt3.transaction_source_type_id + 0 = 5
1019 and mmta3.repetitive_schedule_id = wrs.repetitive_schedule_id
1020 and wrs.repetitive_schedule_id = wtr.key2
1021 and wrs.line_id = wtr.key1
1022 and wrs.organization_id = :ORG_ID
1023 group by mmta3.repetitive_schedule_id,
1024 wrs.organization_id,
1025 wrs.wip_entity_id,
1026 wrs.line_id,
1027 wrs.repetitive_schedule_id)
1028 and attribute1 = ''N'''
1029 USING
1030 ORG_ID , ORG_ID;
1031
1032 --SRW.MESSAGE(10, 'DONE WITH MATERIAL UPDATES');
1033
1034
1035 -- This update is necessary, because it's possible for lusd - fusd to be
1036 -- > processing_days.
1037
1038 EXECUTE IMMEDIATE 'update wip_temp_reports wtr
1039 set wtr.key3 = (wtr.key3 - 1)
1040 where wtr.program_source = ''WIPREVAL''
1041 and wtr.key2 =
1042 (select wrs.repetitive_schedule_id
1043 from wip_repetitive_schedules wrs,
1044 bom_calendar_dates bcd1,
1045 bom_calendar_dates bcd2
1046 where wrs.repetitive_schedule_id = wtr.key2
1047 and wrs.organization_id = wtr.organization_id
1048 and bcd1.calendar_date = trunc(wrs.last_unit_start_date)
1049 and bcd2.calendar_date = trunc(wrs.first_unit_start_date)
1050 and bcd1.calendar_code = :CALENDAR_CODE
1051 and bcd2.calendar_code = :CALENDAR_CODE
1052 and bcd1.exception_set_id = :EXCEPTION_SET_ID
1053 and bcd2.exception_set_id = :EXCEPTION_SET_ID
1054 and ((bcd1.prior_seq_num - bcd2.next_seq_num + 1)
1055 > ceil(wrs.processing_work_days)))'
1056 USING
1057 CALENDAR_CODE , CALENDAR_CODE , EXCEPTION_SET_ID , EXCEPTION_SET_ID;
1058
1059 EXECUTE IMMEDIATE 'update wip_temp_reports wtr
1060 set wtr.key6 =
1061 (select wtr.key3 * wrs.daily_production_rate
1062 from wip_repetitive_schedules wrs
1063 where wrs.repetitive_schedule_id = wtr.key2)
1064 where wtr.program_source = ''WIPREVAL''';
1065
1066 --SRW.MESSAGE(11, 'DONE WITH INSERTS');
1067
1068 END;
1069 return (TRUE);
1070 END;
1071
1072 FUNCTION GET_PRECISION RETURN VARCHAR2 IS
1073 begin
1074
1075 if p_qty_precision = 0 then return('999G999G999G990');
1076
1077 elsif p_qty_precision = 1 then return('999G999G999G990D0');
1078
1079 elsif p_qty_precision = 3 then return('999G999G999G990D000');
1080
1081 elsif p_qty_precision = 4 then return('999G999G999G990D0000');
1082
1083 elsif p_qty_precision = 5 then return('999G999G999G990D00000');
1084
1085 elsif p_qty_precision = 6 then return('999G999G999G990D000000');
1086
1087 else return('999G999G999G990D00');
1088
1089 end if;
1090
1091 END;
1092 END WIP_WIPREVAL_XMLP_PKG;
1093