[Home] [Help]
PACKAGE BODY: APPS.PJM_SCHED_INT_WF
Source
1 PACKAGE BODY PJM_SCHED_INT_WF AS
2 /* $Header: PJMSIWFB.pls 120.4.12000000.2 2007/07/25 21:56:09 exlin ship $ */
3
4 -- ---------------------------------------------------------------------
5 -- Public Functions / Procedures
6 -- ---------------------------------------------------------------------
7
8 PROCEDURE start_wf
9 ( c_document_type varchar2
10 , n_tolerance_days number
11 , c_requestor varchar2
12 , c_ntf_proj_mgr varchar2
13 , c_ntf_task_mgr varchar2
14 , c_item_from varchar2
15 , c_item_to varchar2
16 , c_project_from varchar2
17 , c_project_to varchar2
18 , d_date_from varchar2
19 , d_date_to varchar2
20 , c_oe_or_ont varchar2
21 ) IS
22
23 c_item_type varchar2(80) :='PJMINTWF';
24 c_process varchar2(80) :='PJMINTWF_P';
25 c_exception_subject varchar2(2000) :='';
26 c_exception_body varchar2(2000) :='';
27 c_owner varchar2(80) :=c_requestor;
28
29 /*---------------------------------------------------------------------------
30 Define WIP,SO,MPS,MDS,FORECAST,RFQ,QTN,PO,PR cursors
31 ---------------------------------------------------------------------------*/
32
33 cursor cu_wip is
34 select 'WIP' document_type
35 , hou.name organization_name
36 , mif.item_number item_number
37 , mif.description description
38 , we.wip_entity_name wip_job_name
39 , wdj.wip_entity_id job_id
40 , ml1.meaning job_type
41 , ml2.meaning status
42 , wdj.scheduled_start_date job_start_date
43 , wdj.scheduled_completion_date job_end_date
44 , wdj.start_quantity start_quantity
45 , wdj.quantity_completed quantity_completed
46 , pp.segment1 project_number
47 , pp.name project_name
48 , pp.start_date project_start_date
49 , pp.completion_date project_end_date
50 , pt.task_number task_number
51 , pt.task_name task_name
52 , pt.start_date task_start_date
53 , pt.completion_date task_end_date
54 , wdj.project_id project_id
55 , wdj.task_id task_id
56 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(wdj.scheduled_start_date
57 ,'BETWEEN'
58 ,0
59 ,pp.start_date,pp.completion_date
60 ,pt.start_date,pt.completion_date
61 ) exception_days1
62 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(wdj.scheduled_completion_date
63 ,'BETWEEN'
64 ,0
65 ,pp.start_date,pp.completion_date
66 ,pt.start_date,pt.completion_date
67 ) exception_days2
68 from wip_discrete_jobs wdj
69 , wip_entities we
70 , fnd_lookup_values ml1
71 , fnd_lookup_values ml2
72 , pa_projects_all pp
73 , pa_tasks pt
74 , hr_all_organization_units_tl hou
75 , mtl_item_flexfields mif
76 where wdj.project_id > 0
77 and wdj.status_type not in (4,5,7,12)
78 and we.wip_entity_id = wdj.wip_entity_id
79 and ml1.view_application_id = 700
80 and ml1.language = userenv('LANG')
81 and ml1.lookup_type = 'WIP_DISCRETE_JOB'
82 and ml1.lookup_code = wdj.job_type
83 and ml2.view_application_id = 700
84 and ml2.language = userenv('LANG')
85 and ml2.lookup_type = 'WIP_JOB_STATUS'
86 and ml2.lookup_code = wdj.status_type
87 and pp.project_id = wdj.project_id
88 and ( c_project_from is null or pp.project_id >= c_project_from )
89 and ( c_project_to is null or pp.project_id <= c_project_to )
90 and ((
91 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(wdj.scheduled_start_date
92 ,'BETWEEN'
93 ,n_tolerance_days
94 ,pp.start_date
95 ,pp.completion_date
96 ,pt.start_date
97 ,pt.completion_date
98 ) <> 0
99 and wdj.scheduled_start_date
100 between nvl( fnd_date.canonical_to_date(d_date_from), wdj.scheduled_start_date - 1)
101 and nvl( fnd_date.canonical_to_date(d_date_to) , wdj.scheduled_start_date + 1)
102 ) or (
103 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(wdj.scheduled_completion_date
104 ,'BETWEEN'
105 ,n_tolerance_days
106 ,pp.start_date
107 ,pp.completion_date
108 ,pt.start_date
109 ,pt.completion_date
110 ) <>0
111 and wdj.scheduled_completion_date
112 between nvl( fnd_date.canonical_to_date(d_date_from), wdj.scheduled_completion_date - 1)
113 and nvl( fnd_date.canonical_to_date(d_date_to) , wdj.scheduled_completion_date + 1)
114 ))
115 and pt.task_id (+) = wdj.task_id
116 and hou.organization_id = wdj.organization_id
117 and hou.language = userenv('LANG')
118 and mif.organization_id = wdj.organization_id
119 and mif.inventory_item_id = wdj.primary_item_id
120 and ( c_item_from is null or mif.item_number >= c_item_from )
121 and ( c_item_to is null or mif.item_number <= c_item_to )
122 order by hou.name , we.wip_entity_name
123 ;
124 lr_wip cu_wip%rowtype;
125
126 cursor cu_so is
127 select 'SO' document_type
128 , ooh.order_number so_number
129 , ool.line_id so_line_id
130 , ool.line_number line_number
131 , hou.name warehouse
132 , mif.item_number item_number
133 , mif.description description
134 , nvl(ool.ordered_quantity,0) quantity
135 , ool.request_date requested_date
136 , ool.promise_date promised_date
137 , pp.segment1 project_number
138 , pp.name project_name
139 , pp.start_date project_start_date
140 , pp.completion_date project_end_date
141 , pt.task_number task_number
142 , pt.task_name task_name
143 , pt.start_date task_start_date
144 , pt.completion_date task_end_date
145 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ool.request_date
146 ,'BETWEEN'
147 ,0
148 ,pp.start_date
149 ,pp.completion_date
150 ,pt.start_date
151 ,pt.completion_date
152 ) exception_days1
153 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ool.promise_date
154 ,'BETWEEN'
155 ,0
156 ,pp.start_date
157 ,pp.completion_date
158 ,pt.start_date
159 ,pt.completion_date
160 ) exception_days2
161 , ool.project_id project_id
162 , ool.task_id task_id
163 from oe_order_lines_all ool
164 , hr_all_organization_units_tl hou
165 , oe_order_headers_all ooh
166 , mtl_item_flexfields mif
167 , pa_projects_all pp
168 , pa_tasks pt
169 where ool.project_id > 0
170 and nvl(ool.cancelled_flag,'N') <> 'Y'
171 and nvl(ool.open_flag,'Y') <> 'N'
172 and nvl(ool.shipped_quantity,0)+nvl(ool.cancelled_quantity,0) < ool.ordered_quantity
173 and ooh.header_id = ool.header_id
174 and hou.organization_id = ool.ship_from_org_id
175 and hou.language = userenv('LANG')
176 and mif.organization_id = ool.ship_from_org_id
177 and mif.inventory_item_id = ool.inventory_item_id
178 and ( c_item_from is null or mif.item_number >= c_item_from )
179 and ( c_item_to is null or mif.item_number <= c_item_to )
180 and pp.project_id = ool.project_id
181 and ( c_project_from is null or pp.project_id >= c_project_from )
182 and ( c_project_to is null or pp.project_id <= c_project_to )
183 and ((
184 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ool.request_date
185 ,'BETWEEN'
186 ,n_tolerance_days
187 ,pp.start_date
188 ,pp.completion_date
189 ,pt.start_date
190 ,pt.completion_date
191 ) <>0
192 and ool.request_date
193 between nvl( fnd_date.canonical_to_date(d_date_from), ool.request_date - 1)
194 and nvl( fnd_date.canonical_to_date(d_date_to) , ool.request_date + 1)
195 ) or (
196 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ool.promise_date
197 ,'BETWEEN'
198 ,n_tolerance_days
199 ,pp.start_date
200 ,pp.completion_date
201 ,pt.start_date
202 ,pt.completion_date
203 ) <>0
204 and ool.promise_date
205 between nvl( fnd_date.canonical_to_date(d_date_from), ool.promise_date - 1)
206 and nvl( fnd_date.canonical_to_date(d_date_to) , ool.promise_date + 1)
207 ))
208 and pt.task_id (+) = ool.task_id
209 order by 2
210 ;
211 lr_so cu_so%rowtype;
212
213 cursor cu_forecast is
214 select 'FORECAST' document_type
215 , mfdes.forecast_set forecast_set
216 , mfd.forecast_designator forecast_name
217 , hou.name organization_name
218 , mif.item_number item_number
219 , mif.description description
220 , mfd.forecast_date forecast_start_date
221 , mfd.rate_end_date forecast_end_date
222 , mfd.current_forecast_quantity quantity
223 , pp.segment1 project_number
224 , pp.name project_name
225 , pp.start_date project_start_date
226 , pp.completion_date project_end_date
227 , pt.task_number task_number
228 , pt.task_name task_name
229 , pt.start_date task_start_date
230 , pt.completion_date task_end_date
231 , mfd.transaction_id transaction_id
232 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(mfd.forecast_date
233 ,'BETWEEN'
234 ,0
235 ,pp.start_date
236 ,pp.completion_date
237 ,pt.start_date
238 ,pt.completion_date
239 ) exception_days1
240 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(mfd.rate_end_date
241 ,'BETWEEN'
242 ,0
243 ,pp.start_date
244 ,pp.completion_date
245 ,pt.start_date
246 ,pt.completion_date
247 ) exception_days2
248 , mfd.project_id project_id
249 , mfd.task_id task_id
250 from mrp_forecast_dates mfd
251 , hr_all_organization_units_tl hou
252 , mtl_item_flexfields mif
253 , pa_projects_all pp
254 , pa_tasks pt
255 , mrp_forecast_designators mfdes
256 where mfd.project_id > 0
257 and nvl(mfd.current_forecast_quantity,0) <>0
258 and mif.organization_id = mfd.organization_id
259 and mif.inventory_item_id = mfd.inventory_item_id
260 and ( c_item_from is null or mif.item_number >= c_item_from )
261 and ( c_item_to is null or mif.item_number <= c_item_to )
262 and pp.project_id = mfd.project_id
263 and ( c_project_from is null or pp.project_id >= c_project_from )
264 and ( c_project_to is null or pp.project_id <= c_project_to )
265 and ((
266 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(mfd.forecast_date
267 ,'BETWEEN'
268 ,n_tolerance_days
269 ,pp.start_date
270 ,pp.completion_date
271 ,pt.start_date
272 ,pt.completion_date
273 ) <>0
274 and mfd.forecast_date
275 between nvl( fnd_date.canonical_to_date(d_date_from), mfd.forecast_date - 1)
276 and nvl( fnd_date.canonical_to_date(d_date_to) , mfd.forecast_date + 1)
277 ) or (
278 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(mfd.rate_end_date
279 ,'BETWEEN'
280 ,n_tolerance_days
281 ,pp.start_date
282 ,pp.completion_date
283 ,pt.start_date
284 ,pt.completion_date
285 ) <>0
286 and mfd.rate_end_date
287 between nvl( fnd_date.canonical_to_date(d_date_from), mfd.rate_end_date - 1)
288 and nvl( fnd_date.canonical_to_date(d_date_to) , mfd.rate_end_date + 1)
289 ))
290 and pt.task_id (+) = mfd.task_id
291 and hou.organization_id = mfd.organization_id
292 and hou.language = userenv('LANG')
293 and mfdes.forecast_designator = mfd.forecast_designator
294 and mfdes.organization_Id = mfd.organization_id
295 order by mfd.forecast_designator
296 ;
297 lr_forecast cu_forecast%rowtype;
298
299 cursor cu_pr is
300 select 'PR' document_type
301 , prh.segment1 pr_number
302 , hou.name ship_to_location
303 , nvl(prh.closed_code,prh.authorization_status) status
304 , mif.item_number item_number
305 , mif.description description
306 , prd.req_line_quantity quantity
307 , prl.need_by_date need_by_date
308 , prd.distribution_id distribution_id
309 , pp.segment1 project_number
310 , pp.name project_name
311 , pp.start_date project_start_date
312 , pp.completion_date project_end_date
313 , pt.task_number task_number
314 , pt.task_name task_name
315 , pt.start_date task_start_date
316 , pt.completion_date task_end_date
317 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(prl.need_by_date
318 ,'BETWEEN'
319 ,0
320 ,pp.start_date
321 ,pp.completion_date
322 ,pt.start_date
323 ,pt.completion_date
324 ) exception_days1
325 , prd.project_id project_id
326 , prd.task_id task_id
327 from po_requisition_lines_all prl
328 , po_req_distributions_all prd
329 , po_requisition_headers_all prh
330 , mtl_item_flexfields mif
331 , pa_projects_all pp
332 , pa_tasks pt
333 , hr_all_organization_units_tl hou
334 , financials_system_params_all fsp
335 where prd.project_id > 0
336 and prd.requisition_line_id = prl.requisition_line_id
337 and prh.requisition_header_id = prl.requisition_header_id
338 and nvl(prh.closed_code,'OPEN') not like '%CLOSED%'
339 and nvl(prh.authorization_status,'NOT') not in ('CANCELLED','REJECTED','RETURNED')
340 and mif.organization_id = fsp.inventory_organization_id
341 and mif.inventory_item_id = prl.item_id
342 and ( c_item_from is null or mif.item_number >= c_item_from )
343 and ( c_item_to is null or mif.item_number <= c_item_to )
344 and pp.project_id = prd.project_id
345 and ( c_project_from is null or pp.project_id >= c_project_from )
346 and ( c_project_to is null or pp.project_id <= c_project_to )
347 and PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(prl.need_by_date
348 ,'BETWEEN'
349 ,n_tolerance_days
350 ,pp.start_date
351 ,pp.completion_date
352 ,pt.start_date
353 ,pt.completion_date
354 ) <>0
355 and prl.need_by_date
356 between nvl( fnd_date.canonical_to_date(d_date_from), prl.need_by_date - 1)
357 and nvl( fnd_date.canonical_to_date(d_date_to) , prl.need_by_date + 1)
358 and pt.task_id (+) = prd.task_id
359 and hou.organization_id (+) = prl.destination_organization_id
360 and hou.language (+) = userenv('LANG')
361 and fsp.org_id = prh.org_id
362 order by prh.segment1
363 ;
364
365 lr_pr cu_pr%rowtype;
366
367 cursor cu_rfq is
368 select 'RFQ' document_type
369 , ph.segment1 rfq_number
370 , hl.location_code ship_to_location
371 , mif.item_number item_number
372 , mif.description description
373 , ph.reply_date due_date
374 , pl.po_line_id po_line_id
375 , plc.displayed_field status
376 , pp.segment1 project_number
377 , pp.name project_name
378 , pp.start_date project_start_date
379 , pp.completion_date project_end_date
380 , pt.task_number task_number
381 , pt.task_name task_name
382 , pt.start_date task_start_date
383 , pt.completion_date task_end_date
384 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.reply_date
385 ,'BEFORE_END'
386 ,0
387 ,pp.start_date
388 ,pp.completion_date
389 ,pt.start_date
390 ,pt.completion_date
391 ) exception_days1
392 , pl.project_id project_id
393 , pl.task_id task_id
394 from po_lines_all pl
395 , po_headers_all ph
396 , po_lookup_codes plc
397 , hr_locations hl
398 , mtl_item_flexfields mif
399 , pa_projects_all pp
400 , pa_tasks pt
401 , financials_system_params_all fsp
402 where pl.project_id > 0
403 and ph.po_header_id = pl.po_header_id
404 and ph.type_lookup_code = 'RFQ'
405 and ph.status_lookup_code <> 'C'
406 and plc.lookup_type = 'RFQ/QUOTE STATUS'
407 and plc.lookup_code = ph.status_lookup_code
408 and hl.location_id (+) = ph.ship_to_location_id
409 and mif.organization_id = fsp.inventory_organization_id
410 and mif.inventory_item_id = pl.item_id
411 and ( c_item_from is null or mif.item_number >= c_item_from )
412 and ( c_item_to is null or mif.item_number <= c_item_to )
413 and pp.project_id = pl.project_id
414 and ( c_project_from is null or pp.project_id >= c_project_from )
415 and ( c_project_to is null or pp.project_id <= c_project_to )
416 and PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.reply_date
417 ,'BEFORE_END'
418 ,n_tolerance_days
419 ,pp.start_date
420 ,pp.completion_date
421 ,pt.start_date
422 ,pt.completion_date
423 ) <>0
424 and ph.reply_date
425 between nvl( fnd_date.canonical_to_date(d_date_from), ph.reply_date - 1)
426 and nvl( fnd_date.canonical_to_date(d_date_to) , ph.reply_date + 1)
427 and pt.task_id (+) = pl.task_id
428 and pl.org_id = ph.org_id
429 and fsp.org_id = pl.org_id
430 order by ph.segment1
431 ;
432
433 lr_rfq cu_rfq%rowtype;
434
435 cursor cu_quotation is
436 select 'QUOTATION' document_type
437 , ph.segment1 quotation_number
438 , hl.location_code ship_to_location
439 , mif.item_number item_number
440 , mif.description description
441 , ph.start_date eff_start_date
442 , ph.end_date eff_end_date
443 , pl.po_line_id po_line_id
444 , plc.displayed_field status
445 , pp.segment1 project_number
446 , pp.name project_name
447 , pp.start_date project_start_date
448 , pp.completion_date project_end_date
449 , pt.task_number task_number
450 , pt.task_name task_name
451 , pt.start_date task_start_date
452 , pt.completion_date task_end_date
453 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.start_date
454 ,'BEFORE_END'
455 ,0
456 ,pp.start_date
457 ,pp.completion_date
458 ,pt.start_date
459 ,pt.completion_date
460 ) exception_days1
461 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.end_date
462 ,'AFTER_END'
463 ,0
464 ,pp.start_date
465 ,pp.completion_date
466 ,pt.start_date
467 ,pt.completion_date
468 ) exception_days2
469 , pl.project_id project_id
470 , pl.task_id task_id
471 from po_lines_all pl
472 , po_headers_all ph
473 , po_lookup_codes plc
474 , hr_locations hl
475 , mtl_item_flexfields mif
476 , pa_projects_all pp
477 , pa_tasks pt
478 , financials_system_params_all fsp
479 where pl.project_id > 0
480 and ph.po_header_id = pl.po_header_id
481 and ph.type_lookup_code = 'QUOTATION'
482 and ph.status_lookup_code <> 'C'
483 and plc.lookup_type = 'RFQ/QUOTE STATUS'
484 and plc.lookup_code = ph.status_lookup_code
485 and hl.location_id (+) = ph.ship_to_location_id
486 and mif.organization_id = fsp.inventory_organization_id
487 and mif.inventory_item_id = pl.item_id
488 and ( c_item_from is null or mif.item_number >= c_item_from )
489 and ( c_item_to is null or mif.item_number <= c_item_to )
490 and pp.project_id = pl.project_id
491 and ( c_project_from is null or pp.project_id >= c_project_from )
492 and ( c_project_to is null or pp.project_id <= c_project_to )
493 and ((
494 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.start_date
495 ,'BEFORE_END'
496 ,n_tolerance_days
497 ,pp.start_date
498 ,pp.completion_date
499 ,pt.start_date
500 ,pt.completion_date
501 ) <>0
502 and ph.start_date
503 between nvl( fnd_date.canonical_to_date(d_date_from), ph.start_date - 1)
504 and nvl( fnd_date.canonical_to_date(d_date_to) , ph.start_date + 1)
505 ) or (
506 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.end_date
507 ,'AFTER_END'
508 ,n_tolerance_days
509 ,pp.start_date
510 ,pp.completion_date
511 ,pt.start_date
512 ,pt.completion_date
513 ) <>0
514 and ph.end_date
515 between nvl( fnd_date.canonical_to_date(d_date_from), ph.end_date - 1)
516 and nvl( fnd_date.canonical_to_date(d_date_to) , ph.end_date + 1)
517 ))
518 and pt.task_id (+) = pl.task_id
519 and pl.org_id = ph.org_id
520 and fsp.org_id = pl.org_id
521 order by ph.segment1
522 ;
523
524 lr_quotation cu_quotation%rowtype;
525
526 cursor cu_mds is
527 select 'MDS' document_type
528 , msd.schedule_designator mds_name
529 , msd.mps_transaction_id transaction_id
530 , msd.schedule_level schedule_level
531 , mif.item_number item_number
532 , mif.description description
533 , msd.schedule_date schedule_date
534 , msd.rate_end_date schedule_end_date
535 , msd.original_schedule_quantity quantity
536 , pp.segment1 project_number
537 , pp.name project_name
538 , pp.start_date project_start_date
539 , pp.completion_date project_end_date
540 , pt.task_number task_number
541 , pt.task_name task_name
542 , pt.start_date task_start_date
543 , pt.completion_date task_end_date
544 , hou.name organization_name
545 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.schedule_date
546 ,'BETWEEN'
547 ,0
548 ,pp.start_date
549 ,pp.completion_date
550 ,pt.start_date
551 ,pt.completion_date
552 ) exception_days1
553 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.rate_end_date
554 ,'BETWEEN'
555 ,0
556 ,pp.start_date
557 ,pp.completion_date
558 ,pt.start_date
559 ,pt.completion_date
560 ) exception_days2
561 , msd.project_id project_id
562 , msd.task_id task_id
563 from mrp_schedule_dates msd
564 , hr_all_organization_units_tl hou
565 , mtl_item_flexfields mif
566 , pa_projects_all pp
567 , pa_tasks pt
568 where msd.project_id > 0
569 and nvl(decode(mif.repetitive_planning_flag,
570 'Y',msd.repetitive_daily_rate
571 ,msd.schedule_quantity),0) <>0
572 and msd.supply_demand_type = 1
573 and msd.schedule_level = 2
574 and mif.organization_id = msd.organization_id
575 and mif.inventory_item_id = msd.inventory_item_id
576 and ( c_item_from is null or mif.item_number >= c_item_from )
577 and ( c_item_to is null or mif.item_number <= c_item_to )
578 and hou.organization_id = msd.organization_id
579 and hou.language = userenv('LANG')
580 and pp.project_id = msd.project_id
581 and ( c_project_from is null or pp.project_id >= c_project_from )
582 and ( c_project_to is null or pp.project_id <= c_project_to )
583 and ((
584 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.schedule_date
585 ,'BETWEEN'
586 ,n_tolerance_days
587 ,pp.start_date
588 ,pp.completion_date
589 ,pt.start_date
590 ,pt.completion_date
591 ) <>0
592 and msd.schedule_date
593 between nvl( fnd_date.canonical_to_date(d_date_from), msd.schedule_date - 1)
594 and nvl( fnd_date.canonical_to_date(d_date_to) , msd.schedule_date + 1)
595 ) or (
596 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.rate_end_date
597 ,'BETWEEN'
598 ,n_tolerance_days
599 ,pp.start_date
600 ,pp.completion_date
601 ,pt.start_date
602 ,pt.completion_date
603 ) <>0
604 and msd.rate_end_date
605 between nvl( fnd_date.canonical_to_date(d_date_from), msd.rate_end_date - 1)
606 and nvl( fnd_date.canonical_to_date(d_date_to) , msd.rate_end_date + 1)
607 ))
608 and pt.task_id (+) = msd.task_id
609 order by msd.schedule_designator
610 ;
611 lr_mds cu_mds%rowtype;
612
613 cursor cu_mps is
614 select 'MPS' document_type
615 , msd.schedule_designator mps_name
616 , msd.mps_transaction_id transaction_id
617 , msd.schedule_level schedule_level
618 , mif.item_number item_number
619 , mif.description description
620 , msd.schedule_date schedule_date
621 , msd.rate_end_date schedule_end_date
622 , msd.original_schedule_quantity quantity
623 , pp.segment1 project_number
624 , pp.name project_name
625 , pp.start_date project_start_date
626 , pp.completion_date project_end_date
627 , pt.task_number task_number
628 , pt.task_name task_name
629 , pt.start_date task_start_date
630 , pt.completion_date task_end_date
631 , hou.name organization_name
632 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.schedule_date
633 ,'BETWEEN'
634 ,0
635 ,pp.start_date
636 ,pp.completion_date
637 ,pt.start_date
638 ,pt.completion_date
639 ) exception_days1
640 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.rate_end_date
641 ,'BETWEEN'
642 ,0
643 ,pp.start_date
644 ,pp.completion_date
645 ,pt.start_date
646 ,pt.completion_date
647 ) exception_days2
648 , msd.project_id project_id
649 , msd.task_id task_id
650 from mrp_schedule_dates msd
651 , hr_all_organization_units_tl hou
652 , mtl_item_flexfields mif
653 , pa_projects_all pp
654 , pa_tasks pt
655 where msd.project_id > 0
656 and nvl(decode(mif.repetitive_planning_flag,
657 'Y',msd.repetitive_daily_rate
658 ,msd.schedule_quantity),0) <>0
659 and msd.supply_demand_type = 2
660 and msd.schedule_level = 2
661 and mif.organization_id = msd.organization_id
662 and mif.inventory_item_id = msd.inventory_item_id
663 and ( c_item_from is null or mif.item_number >= c_item_from )
664 and ( c_item_to is null or mif.item_number <= c_item_to )
665 and hou.organization_id = msd.organization_id
666 and hou.language = userenv('LANG')
667 and pp.project_id = msd.project_id
668 and ( c_project_from is null or pp.project_id >= c_project_from )
669 and ( c_project_to is null or pp.project_id <= c_project_to )
670 and ((
671 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.schedule_date
672 ,'BETWEEN'
673 ,n_tolerance_days
674 ,pp.start_date
675 ,pp.completion_date
676 ,pt.start_date
677 ,pt.completion_date
678 ) <>0
679 and msd.schedule_date
680 between nvl( fnd_date.canonical_to_date(d_date_from), msd.schedule_date - 1)
681 and nvl( fnd_date.canonical_to_date(d_date_to) , msd.schedule_date + 1)
682 ) or (
683 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.rate_end_date
684 ,'BETWEEN'
685 ,n_tolerance_days
686 ,pp.start_date
687 ,pp.completion_date
688 ,pt.start_date
689 ,pt.completion_date
690 ) <>0
691 and msd.rate_end_date
692 between nvl( fnd_date.canonical_to_date(d_date_from), msd.rate_end_date - 1)
693 and nvl( fnd_date.canonical_to_date(d_date_to) , msd.rate_end_date + 1)
694 ))
695 and pt.task_id (+) = msd.task_id
696 order by msd.schedule_designator
697 ;
698 lr_mps cu_mps%rowtype;
699
700 cursor cu_po is
701 select 'PO' document_type
702 , ph.segment1 po_number
703 , PO_HEADERS_SV3.GET_PO_STATUS(ph.po_header_id) status
704 , hou.name ship_to_location
705 , mif.item_number item_number
706 , mif.description description
707 , pd.quantity_ordered ordered_quantity
708 , pd.quantity_delivered delivered_quantity
709 , pd.po_distribution_id po_distribution_id
710 , pll.promised_date promised_date
711 , pll.need_by_date need_by_date
712 , pp.segment1 project_number
713 , pp.name project_name
714 , pp.start_date project_start_date
715 , pp.completion_date project_end_date
716 , pt.task_number task_number
717 , pt.task_name task_name
718 , pt.start_date task_start_date
719 , pt.completion_date task_end_date
720 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
721 ,'BETWEEN'
722 ,0
723 ,pp.start_date
724 ,pp.completion_date
725 ,pt.start_date
726 ,pt.completion_date
727 ) exception_days1
728 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
729 ,'BETWEEN'
730 ,0
731 ,pp.start_date
732 ,pp.completion_date
733 ,pt.start_date
734 ,pt.completion_date
735 ) exception_days2
736 , pd.project_id project_id
737 , pd.task_id task_id
738 from po_distributions_all pd
739 , po_line_locations_all pll
740 , po_lines_all pl
741 , po_headers_all ph
742 , mtl_item_flexfields mif
743 , pa_projects_all pp
744 , pa_tasks pt
745 , hr_all_organization_units_tl hou
746 , financials_system_params_all fsp
747 where pd.project_id > 0
748 and pd.po_release_id is null
749 and pll.line_location_id = pd.line_location_id
750 and pl.po_line_id = pll.po_line_id
751 and ph.po_header_id = pl.po_header_id
752 and nvl(pl.cancel_flag, 'N') <> 'Y' /* Bug 6262080; base bug 5757447 */
753 and nvl(ph.closed_code,'OPEN') not like '%CLOSED%'
754 and nvl(ph.authorization_status,'N') not in ('CANCELLED','REJECTED')
755 and mif.organization_id = fsp.inventory_organization_id
756 and mif.inventory_item_id = pl.item_id
757 and ( c_item_from is null or mif.item_number >= c_item_from )
758 and ( c_item_to is null or mif.item_number <= c_item_to )
759 and pp.project_id = pd.project_id
760 and ( c_project_from is null or pp.project_id >= c_project_from )
761 and ( c_project_to is null or pp.project_id <= c_project_to )
762 and ((
763 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
764 ,'BETWEEN'
765 ,n_tolerance_days
766 ,pp.start_date
767 ,pp.completion_date
768 ,pt.start_date
769 ,pt.completion_date
770 ) <>0
771 and pll.promised_date
772 between nvl( fnd_date.canonical_to_date(d_date_from), pll.promised_date - 1)
773 and nvl( fnd_date.canonical_to_date(d_date_to) , pll.promised_date + 1)
774 ) or (
775 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
776 ,'BETWEEN'
777 ,n_tolerance_days
778 ,pp.start_date
779 ,pp.completion_date
780 ,pt.start_date
781 ,pt.completion_date
782 ) <>0
783 and pll.need_by_date
784 between nvl( fnd_date.canonical_to_date(d_date_from), pll.need_by_date - 1)
785 and nvl( fnd_date.canonical_to_date(d_date_to) , pll.need_by_date + 1)
786 ))
787 and pt.task_id (+) = pd.task_id
788 and hou.organization_id (+) = pd.destination_organization_id
789 and hou.language (+) = userenv('LANG')
790 and ph.org_id = pl.org_id
791 and pl.org_id = pd.org_id
792 and pl.org_id = fsp.org_id
793 and pl.org_id = pll.org_id
794 order by ph.segment1
795 ;
796 lr_po cu_po%rowtype;
797
798 cursor cu_po_release is
799 select 'BLANKET_RELEASE' document_type
800 , ph.segment1 po_number
801 , PO_HEADERS_SV3.GET_PO_STATUS(ph.po_header_id) status
802 , hou.name ship_to_location
803 , pr.release_num release_number
804 , mif.item_number item_number
805 , mif.description description
806 , pd.quantity_ordered ordered_quantity
807 , pd.quantity_delivered delivered_quantity
808 , pd.po_distribution_id po_distribution_id
809 , pll.promised_date promised_date
810 , pll.need_by_date need_by_date
811 , pp.segment1 project_number
812 , pp.name project_name
813 , pp.start_date project_start_date
814 , pp.completion_date project_end_date
815 , pt.task_number task_number
816 , pt.task_name task_name
817 , pt.start_date task_start_date
818 , pt.completion_date task_end_date
819 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
820 ,'BETWEEN'
821 ,0
822 ,pp.start_date
823 ,pp.completion_date
824 ,pt.start_date
825 ,pt.completion_date
826 ) exception_days1
827 , PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
828 ,'BETWEEN'
829 ,0
830 ,pp.start_date
831 ,pp.completion_date
832 ,pt.start_date
833 ,pt.completion_date
834 ) exception_days2
835 , pd.project_id project_id
836 , pd.task_id task_id
837 from po_distributions_all pd
838 , po_releases_all pr
839 , po_line_locations_all pll
840 , po_lines_all pl
841 , po_headers_all ph
842 , mtl_item_flexfields mif
843 , pa_projects_all pp
844 , pa_tasks pt
845 , hr_all_organization_units_tl hou
846 , financials_system_params_all fsp
847 where pd.project_id > 0
848 and pd.po_release_id is not null
849 and pr.po_release_id = pd.po_release_id
850 and pll.line_location_id = pd.line_location_id
851 and pl.po_line_id = pll.po_line_id
852 and ph.po_header_id = pl.po_header_id
853 and nvl(ph.closed_code,'OPEN') not like '%CLOSED%'
854 and nvl(ph.authorization_status,'N') not in ('CANCELLED','REJECTED')
855 and nvl(pr.closed_code,'OPEN') not like '%CLOSED%'
856 and nvl(pr.authorization_status,'N') not in ('CANCELLED','REJECTED')
857 and nvl(pr.cancel_flag,'N') <> 'Y'
858 and mif.organization_id = fsp.inventory_organization_id
859 and mif.inventory_item_id = pl.item_id
860 and ( c_item_from is null or mif.item_number >= c_item_from )
861 and ( c_item_to is null or mif.item_number <= c_item_to )
862 and pp.project_id = pd.project_id
863 and ( c_project_from is null or pp.project_id >= c_project_from )
864 and ( c_project_to is null or pp.project_id <= c_project_to )
865 and ((
866 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
867 ,'BETWEEN'
868 ,n_tolerance_days
869 ,pp.start_date
870 ,pp.completion_date
871 ,pt.start_date
872 ,pt.completion_date
873 ) <>0
874 and pll.promised_date
875 between nvl( fnd_date.canonical_to_date(d_date_from), pll.promised_date - 1)
876 and nvl( fnd_date.canonical_to_date(d_date_to) , pll.promised_date + 1)
877 ) or (
878 PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
879 ,'BETWEEN'
880 ,n_tolerance_days
881 ,pp.start_date
882 ,pp.completion_date
883 ,pt.start_date
884 ,pt.completion_date
885 ) <>0
886 and pll.need_by_date
887 between nvl( fnd_date.canonical_to_date(d_date_from), pll.need_by_date - 1)
888 and nvl( fnd_date.canonical_to_date(d_date_to) , pll.need_by_date + 1)
889 ))
890 and pt.task_id (+) = pd.task_id
891 and hou.organization_id (+) = pd.destination_organization_id
892 and hou.language (+) = userenv('LANG')
893 and ph.org_id = pl.org_id
894 and pl.org_id = pd.org_id
895 and pl.org_id = fsp.org_id
896 and pl.org_id = pll.org_id
897 and pl.org_id = pr.org_id
898 order by ph.segment1
899 ;
900 lr_po_release cu_po_release%rowtype;
901
902 procedure timestamp ( mesg varchar2 ) is
903 begin
904 PJM_CONC.put_line( rpad( mesg || ' ' , 50 , '.' ) || ' ' ||
905 fnd_date.date_to_displaydt(sysdate) );
906 end timestamp;
907
908 BEGIN
909
910 IF upper(c_document_type) = 'WIP' or c_document_type is null then
911
912 timestamp('Processing Work in Process');
913
914 OPEN cu_wip;
915 LOOP
916 FETCH cu_wip INTO lr_wip;
917 EXIT WHEN cu_wip%NOTFOUND;
918
919 c_exception_subject :='';
920 c_exception_body :='';
921
922 IF lr_wip.exception_days1 <> 0 THEN
923 IF lr_wip.exception_days1 > 0 THEN
924 c_exception_subject:=
925 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ JOB SDATE EARLY');
926
927 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY JOB SDATE EARLY');
928 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_wip.exception_days1));
929 c_exception_body := FND_MESSAGE.GET;
930 ELSE
931 c_exception_subject:=
932 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ JOB SDATE LATE');
933
934 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY JOB SDATE LATE');
935 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_wip.exception_days1));
936 c_exception_body := FND_MESSAGE.GET;
937 END IF;
938 END IF;
939
940 IF lr_wip.exception_days2 <> 0 THEN
941 IF lr_wip.exception_days2 > 0 THEN
942 c_exception_subject:= c_exception_subject || ' / ' ||
943 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ JOB CDATE EARLY');
944
945 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY JOB CDATE EARLY');
946 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_wip.exception_days2));
947 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
948 ELSE
949 c_exception_subject:= c_exception_subject || ' / ' ||
950 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ JOB CDATE LATE');
951
952 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY JOB CDATE LATE');
953 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_wip.exception_days2));
954 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
955 END IF;
956 END IF;
957
958 PJM_SCHED_INT_WF_PRIV.launch_wip(c_item_type
959 ,c_requestor || '.WIP.' || to_char(lr_wip.job_id)
960 ,c_process
961 ,c_owner
962 ,c_requestor
963 ,n_tolerance_days
964 ,c_ntf_proj_mgr
965 ,c_ntf_task_mgr
966 ,lr_wip.project_number
967 ,lr_wip.project_name
968 ,lr_wip.project_start_date
969 ,lr_wip.project_end_date
970 ,lr_wip.task_number
971 ,lr_wip.task_name
972 ,lr_wip.task_start_date
973 ,lr_wip.task_end_date
974 ,c_exception_subject
975 ,c_exception_body
976 ,lr_wip.document_type
977 ,lr_wip.item_number
978 ,lr_wip.description
979 ,lr_wip.wip_job_name
980 ,lr_wip.organization_name
981 ,lr_wip.job_start_date
982 ,lr_wip.job_end_date
983 ,lr_wip.status
984 ,lr_wip.job_type
985 ,lr_wip.start_quantity
986 ,lr_wip.quantity_completed
987 );
988
989 END LOOP;
990
991 PJM_CONC.put_line(cu_wip%rowcount || ' exception(s) found.' || fnd_global.newline);
992
993 CLOSE cu_wip;
994 END IF;
995
996 IF upper(c_document_type) = 'SO' or c_document_type is null then
997
998 timestamp('Processing Sales Order');
999
1000 OPEN cu_so;
1001 LOOP
1002 FETCH cu_so INTO lr_so;
1003 EXIT WHEN cu_so%NOTFOUND;
1004
1005 c_exception_subject :='';
1006 c_exception_body :='';
1007
1008 IF lr_so.exception_days1 <> 0 THEN
1009 IF lr_so.exception_days1 > 0 THEN
1010 c_exception_subject:=
1011 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ REQ DATE EARLY');
1012
1013 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY REQ DATE EARLY');
1014 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_so.exception_days1));
1015 c_exception_body := FND_MESSAGE.GET;
1016 ELSE
1017 c_exception_subject:=
1018 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ REQ DATE LATE');
1019
1020 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY REQ DATE LATE');
1021 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_so.exception_days1));
1022 c_exception_body := FND_MESSAGE.GET;
1023 END IF;
1024 END IF;
1025
1026 IF lr_so.exception_days2 <> 0 THEN
1027 IF lr_so.exception_days2 > 0 THEN
1028 c_exception_subject:= c_exception_subject || ' / ' ||
1029 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ PROMISED DATE EARLY');
1030
1031 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY PROMISED DATE EARLY');
1032 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_so.exception_days2));
1033 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1034 ELSE
1035 c_exception_subject:= c_exception_subject || ' / ' ||
1036 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ PROMISED DATE LATE');
1037
1038 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY PROMISED DATE LATE');
1039 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_so.exception_days2));
1040 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1041 END IF;
1042 END IF;
1043
1044
1045 PJM_SCHED_INT_WF_PRIV.launch_so (c_item_type
1046 ,c_requestor || '.SO.' || to_char(lr_so.so_line_id)
1047 ,c_process
1048 ,c_owner
1049 ,c_requestor
1050 ,n_tolerance_days
1051 ,c_ntf_proj_mgr
1052 ,c_ntf_task_mgr
1053 ,lr_so.project_number
1054 ,lr_so.project_name
1055 ,lr_so.project_start_date
1056 ,lr_so.project_end_date
1057 ,lr_so.task_number
1058 ,lr_so.task_name
1059 ,lr_so.task_start_date
1060 ,lr_so.task_end_date
1061 ,c_exception_subject
1062 ,c_exception_body
1063 ,lr_so.document_type
1064 ,lr_so.item_number
1065 ,lr_so.description
1066 ,to_char(lr_so.so_number)
1067 ,to_char(lr_so.line_number)
1068 ,lr_so.warehouse
1069 ,lr_so.quantity
1070 ,lr_so.requested_date
1071 ,lr_so.promised_date
1072 );
1073
1074
1075 END LOOP;
1076
1077 PJM_CONC.put_line(cu_so%rowcount || ' exception(s) found.' || fnd_global.newline);
1078
1079 CLOSE cu_so;
1080 END IF;
1081
1082 IF upper(c_document_type) = 'FORECAST' or c_document_type is null then
1083
1084 timestamp('Processing Forecast');
1085
1086 OPEN cu_forecast;
1087 LOOP
1088 FETCH cu_forecast INTO lr_forecast;
1089 EXIT WHEN cu_forecast%NOTFOUND;
1090
1091 c_exception_subject :='';
1092 c_exception_body :='';
1093
1094 IF lr_forecast.exception_days1 <> 0 THEN
1095 IF lr_forecast.exception_days1 > 0 THEN
1096 c_exception_subject:=
1097 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ FC DATE EARLY');
1098
1099 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY FC DATE EARLY');
1100 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_forecast.exception_days1));
1101 c_exception_body := FND_MESSAGE.GET;
1102 ELSE
1103 c_exception_subject:=
1104 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ FC DATE LATE');
1105
1106 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY FC DATE LATE');
1107 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_forecast.exception_days1));
1108 c_exception_body := FND_MESSAGE.GET;
1109 END IF;
1110 END IF;
1111
1112 IF lr_forecast.exception_days2 <> 0 THEN
1113 IF lr_forecast.exception_days2 > 0 THEN
1114 c_exception_subject:= c_exception_subject || ' / ' ||
1115 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ FC EDATE EARLY');
1116
1117 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY FC EDATE EARLY');
1118 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_forecast.exception_days2));
1119 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1120 ELSE
1121 c_exception_subject:= c_exception_subject || ' / ' ||
1122 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ FC EDATE LATE');
1123
1124 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY FC EDATE LATE');
1125 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_forecast.exception_days2));
1126 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1127 END IF;
1128 END IF;
1129
1130 PJM_SCHED_INT_WF_PRIV.launch_forecast(c_item_type
1131 ,c_requestor || '.FCST.' || to_char(lr_forecast.transaction_id)
1132 ,c_process
1133 ,c_owner
1134 ,c_requestor
1135 ,n_tolerance_days
1136 ,c_ntf_proj_mgr
1137 ,c_ntf_task_mgr
1138 ,lr_forecast.project_number
1139 ,lr_forecast.project_name
1140 ,lr_forecast.project_start_date
1141 ,lr_forecast.project_end_date
1142 ,lr_forecast.task_number
1143 ,lr_forecast.task_name
1144 ,lr_forecast.task_start_date
1145 ,lr_forecast.task_end_date
1146 ,c_exception_subject
1147 ,c_exception_body
1148 ,lr_forecast.document_type
1149 ,lr_forecast.item_number
1150 ,lr_forecast.description
1151 ,lr_forecast.forecast_set
1152 ,lr_forecast.forecast_name
1153 ,lr_forecast.organization_name
1154 ,lr_forecast.quantity
1155 ,lr_forecast.forecast_start_date
1156 ,lr_forecast.forecast_end_date
1157 );
1158
1159 END LOOP;
1160
1161 PJM_CONC.put_line(cu_forecast%rowcount || ' exception(s) found.' || fnd_global.newline);
1162
1163 CLOSE cu_forecast;
1164 END IF;
1165
1166 IF upper(c_document_type) = 'PR' or c_document_type is null then
1167
1168 timestamp('Processing Purchase Requisition');
1169
1170 OPEN cu_pr;
1171 LOOP
1172 FETCH cu_pr INTO lr_pr;
1173 EXIT WHEN cu_pr%NOTFOUND;
1174
1175 c_exception_subject :='';
1176 c_exception_body :='';
1177
1178 IF lr_pr.exception_days1 <> 0 THEN
1179 IF lr_pr.exception_days1 > 0 THEN
1180 c_exception_subject:=
1181 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ NEEDBY DATE EARLY');
1182
1183 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY NEEDBY DATE EARLY');
1184 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_pr.exception_days1));
1185 c_exception_body := FND_MESSAGE.GET;
1186 ELSE
1187 c_exception_subject:=
1188 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ NEEDBY DATE LATE');
1189
1190 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY NEEDBY DATE LATE');
1191 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_pr.exception_days1));
1192 c_exception_body := FND_MESSAGE.GET;
1193 END IF;
1194 END IF;
1195
1196 PJM_SCHED_INT_WF_PRIV.launch_pr(c_item_type
1197 ,c_requestor || '.PR.' || to_char(lr_pr.distribution_id)
1198 ,c_process
1199 ,c_owner
1200 ,c_requestor
1201 ,n_tolerance_days
1202 ,c_ntf_proj_mgr
1203 ,c_ntf_task_mgr
1204 ,lr_pr.project_number
1205 ,lr_pr.project_name
1206 ,lr_pr.project_start_date
1207 ,lr_pr.project_end_date
1208 ,lr_pr.task_number
1209 ,lr_pr.task_name
1210 ,lr_pr.task_start_date
1211 ,lr_pr.task_end_date
1212 ,c_exception_subject
1213 ,c_exception_body
1214 ,lr_pr.document_type
1215 ,lr_pr.item_number
1216 ,lr_pr.description
1217 ,lr_pr.pr_number
1218 ,lr_pr.ship_to_location
1219 ,lr_pr.status
1220 ,lr_pr.quantity
1221 ,lr_pr.need_by_date
1222 );
1223 END LOOP;
1224
1225 PJM_CONC.put_line(cu_pr%rowcount || ' exception(s) found.' || fnd_global.newline);
1226
1227 CLOSE cu_pr;
1228 END IF;
1229
1230 IF upper(c_document_type) = 'RFQ' or c_document_type is null then
1231
1232 timestamp('Processing Request for Quotation');
1233
1234 OPEN cu_rfq;
1235 LOOP
1236 FETCH cu_rfq INTO lr_rfq;
1237 EXIT WHEN cu_rfq%NOTFOUND;
1238
1239 c_exception_subject :='';
1240 c_exception_body :='';
1241
1242 IF lr_rfq.exception_days1 <> 0 THEN
1243 c_exception_subject:=
1244 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ DUE DATE LATE');
1245
1246 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY DUE DATE LATE');
1247 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_rfq.exception_days1));
1248 c_exception_body := FND_MESSAGE.GET;
1249 END IF;
1250
1251 PJM_SCHED_INT_WF_PRIV.launch_rfq(c_item_type
1252 ,c_requestor || '.RFQ.' || to_char(lr_rfq.po_line_id)
1253 ,c_process
1254 ,c_owner
1255 ,c_requestor
1256 ,n_tolerance_days
1257 ,c_ntf_proj_mgr
1258 ,c_ntf_task_mgr
1259 ,lr_rfq.project_number
1260 ,lr_rfq.project_name
1261 ,lr_rfq.project_start_date
1262 ,lr_rfq.project_end_date
1263 ,lr_rfq.task_number
1264 ,lr_rfq.task_name
1265 ,lr_rfq.task_start_date
1266 ,lr_rfq.task_end_date
1267 ,c_exception_subject
1268 ,c_exception_body
1269 ,lr_rfq.document_type
1270 ,lr_rfq.item_number
1271 ,lr_rfq.description
1272 ,lr_rfq.rfq_number
1273 ,lr_rfq.ship_to_location
1274 ,lr_rfq.status
1275 ,lr_rfq.due_date
1276 );
1277
1278 END LOOP;
1279
1280 PJM_CONC.put_line(cu_rfq%rowcount || ' exception(s) found.' || fnd_global.newline);
1281
1282 CLOSE cu_rfq;
1283 END IF;
1284
1285 IF upper(c_document_type) = 'QUOTATION' or c_document_type is null then
1286
1287 timestamp('Processing Quotation');
1288
1289 OPEN cu_quotation;
1290 LOOP
1291 FETCH cu_quotation INTO lr_quotation;
1292 EXIT WHEN cu_quotation%NOTFOUND;
1293
1294 c_exception_subject :='';
1295 c_exception_body :='';
1296
1297 IF lr_quotation.exception_days1 <> 0 THEN
1298 c_exception_subject:=
1299 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ EFF DATE LATE');
1300
1301 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY EFF DATE LATE');
1302 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_quotation.exception_days1));
1303 c_exception_body := FND_MESSAGE.GET;
1304 END IF;
1305
1306 IF lr_quotation.exception_days2 <> 0 THEN
1307 c_exception_subject:= c_exception_subject || ' / ' ||
1308 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ EFF DATE EARLY');
1309
1310 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY EFF DATE EARLY');
1311 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_quotation.exception_days2));
1312 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1313 END IF;
1314
1315 PJM_SCHED_INT_WF_PRIV.launch_quotation(c_item_type
1316 ,c_requestor || '.QUOTE.' || to_char(lr_quotation.po_line_id)
1317 ,c_process
1318 ,c_owner
1319 ,c_requestor
1320 ,n_tolerance_days
1321 ,c_ntf_proj_mgr
1322 ,c_ntf_task_mgr
1323 ,lr_quotation.project_number
1324 ,lr_quotation.project_name
1325 ,lr_quotation.project_start_date
1326 ,lr_quotation.project_end_date
1327 ,lr_quotation.task_number
1328 ,lr_quotation.task_name
1329 ,lr_quotation.task_start_date
1330 ,lr_quotation.task_end_date
1331 ,c_exception_subject
1332 ,c_exception_body
1333 ,lr_quotation.document_type
1334 ,lr_quotation.item_number
1335 ,lr_quotation.description
1336 ,lr_quotation.quotation_number
1337 ,lr_quotation.ship_to_location
1338 ,lr_quotation.status
1339 ,lr_quotation.eff_start_date
1340 ,lr_quotation.eff_end_date
1341 );
1342
1343 END LOOP;
1344
1345 PJM_CONC.put_line(cu_quotation%rowcount || ' exception(s) found.' || fnd_global.newline);
1346
1347 CLOSE cu_quotation;
1348 END IF;
1349
1350 IF upper(c_document_type) = 'MDS' or c_document_type is null then
1351
1352 timestamp('Processing Master Demand Schedule');
1353
1354 OPEN cu_mds;
1355 LOOP
1356 FETCH cu_mds INTO lr_mds;
1357 EXIT WHEN cu_mds%NOTFOUND;
1358
1359 c_exception_subject :='';
1360 c_exception_body :='';
1361
1362 IF lr_mds.exception_days1 <> 0 THEN
1363 IF lr_mds.exception_days1 > 0 THEN
1364 c_exception_subject:=
1365 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ SCHED DATE EARLY');
1366
1367 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY SCHED DATE EARLY');
1368 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_mds.exception_days1));
1369 c_exception_body := FND_MESSAGE.GET;
1370 ELSE
1371 c_exception_subject:=
1372 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ SCHED DATE LATE');
1373
1374 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY SCHED DATE LATE');
1375 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_mds.exception_days1));
1376 c_exception_body := FND_MESSAGE.GET;
1377 END IF;
1378 END IF;
1379
1380 IF lr_mds.exception_days2 <> 0 THEN
1381 IF lr_mds.exception_days2 > 0 THEN
1382 c_exception_subject:= c_exception_subject || ' / ' ||
1383 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ SCHED EDATE EARLY');
1384
1385 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY SCHED EDATE EARLY');
1386 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_mds.exception_days2));
1387 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1388 ELSE
1389 c_exception_subject:= c_exception_subject || ' / ' ||
1390 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ SCHED EDATE LATE');
1391
1392 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY SCHED EDATE LATE');
1393 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_mds.exception_days2));
1394 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1395 END IF;
1396 END IF;
1397
1398 PJM_SCHED_INT_WF_PRIV.launch_mds(c_item_type
1399 ,c_requestor || '.MDS.' || to_char(lr_mds.transaction_id)
1400 ,c_process
1401 ,c_owner
1402 ,c_requestor
1403 ,n_tolerance_days
1404 ,c_ntf_proj_mgr
1405 ,c_ntf_task_mgr
1406 ,lr_mds.project_number
1407 ,lr_mds.project_name
1408 ,lr_mds.project_start_date
1409 ,lr_mds.project_end_date
1410 ,lr_mds.task_number
1411 ,lr_mds.task_name
1412 ,lr_mds.task_start_date
1413 ,lr_mds.task_end_date
1414 ,c_exception_subject
1415 ,c_exception_body
1416 ,lr_mds.document_type
1417 ,lr_mds.item_number
1418 ,lr_mds.description
1419 ,lr_mds.mds_name
1420 ,lr_mds.organization_name
1421 ,lr_mds.quantity
1422 ,lr_mds.schedule_date
1423 ,lr_mds.schedule_end_date
1424 );
1425
1426 END LOOP;
1427
1428 PJM_CONC.put_line(cu_mds%rowcount || ' exception(s) found.' || fnd_global.newline);
1429
1430 CLOSE cu_mds;
1431 END IF;
1432
1433 IF upper(c_document_type) = 'MPS' or c_document_type is null then
1434
1435 timestamp('Processing Master Production Schedule');
1436
1437 OPEN cu_mps;
1438 LOOP
1439 FETCH cu_mps INTO lr_mps;
1440 EXIT WHEN cu_mps%NOTFOUND;
1441
1442 c_exception_subject :='';
1443 c_exception_body :='';
1444
1445 IF lr_mps.exception_days1 <> 0 THEN
1446 IF lr_mps.exception_days1 > 0 THEN
1447 c_exception_subject:=
1448 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ SCHED DATE EARLY');
1449
1450 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY SCHED DATE EARLY');
1451 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_mps.exception_days1));
1452 c_exception_body := FND_MESSAGE.GET;
1453 ELSE
1454 c_exception_subject:=
1455 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ SCHED DATE LATE');
1456
1457 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY SCHED DATE LATE');
1458 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_mps.exception_days1));
1459 c_exception_body := FND_MESSAGE.GET;
1460 END IF;
1461 END IF;
1462
1463 IF lr_mps.exception_days2 <> 0 THEN
1464 IF lr_mps.exception_days2 > 0 THEN
1465 c_exception_subject:= c_exception_subject || ' / ' ||
1466 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ SCHED EDATE EARLY');
1467
1468 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY SCHED EDATE EARLY');
1469 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_mps.exception_days2));
1470 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1471 ELSE
1472 c_exception_subject:= c_exception_subject || ' / ' ||
1473 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ SCHED EDATE LATE');
1474
1475 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY SCHED EDATE LATE');
1476 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_mps.exception_days2));
1477 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1478 END IF;
1479 END IF;
1480
1481 PJM_SCHED_INT_WF_PRIV.launch_mps(c_item_type
1482 ,c_requestor || '.MPS.' || to_char(lr_mps.transaction_id)
1483 ,c_process
1484 ,c_owner
1485 ,c_requestor
1486 ,n_tolerance_days
1487 ,c_ntf_proj_mgr
1488 ,c_ntf_task_mgr
1489 ,lr_mps.project_number
1490 ,lr_mps.project_name
1491 ,lr_mps.project_start_date
1492 ,lr_mps.project_end_date
1493 ,lr_mps.task_number
1494 ,lr_mps.task_name
1495 ,lr_mps.task_start_date
1496 ,lr_mps.task_end_date
1497 ,c_exception_subject
1498 ,c_exception_body
1499 ,lr_mps.document_type
1500 ,lr_mps.item_number
1501 ,lr_mps.description
1502 ,lr_mps.mps_name
1503 ,lr_mps.organization_name
1504 ,lr_mps.quantity
1505 ,lr_mps.schedule_date
1506 ,lr_mps.schedule_end_date
1507 );
1508
1509 END LOOP;
1510
1511 PJM_CONC.put_line(cu_mps%rowcount || ' exception(s) found.' || fnd_global.newline);
1512
1513 CLOSE cu_mps;
1514 END IF;
1515
1516 IF upper(c_document_type) = 'PO' or c_document_type is null then
1517
1518 timestamp('Processing Purchase Order');
1519
1520 OPEN cu_po;
1521 LOOP
1522 FETCH cu_po INTO lr_po;
1523 EXIT WHEN cu_po%NOTFOUND;
1524
1525 c_exception_subject :='';
1526 c_exception_body :='';
1527
1528 IF lr_po.exception_days1 <> 0 THEN
1529 IF lr_po.exception_days1 > 0 THEN
1530 c_exception_subject:=
1531 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ PROMISED DATE EARLY');
1532
1533 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY PROMISED DATE EARLY');
1534 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_po.exception_days1));
1535 c_exception_body := FND_MESSAGE.GET;
1536 ELSE
1537 c_exception_subject:=
1538 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ PROMISED DATE LATE');
1539
1540 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY PROMISED DATE LATE');
1541 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_po.exception_days1));
1542 c_exception_body := FND_MESSAGE.GET;
1543 END IF;
1544 END IF;
1545
1546 IF lr_po.exception_days2 <> 0 THEN
1547 IF lr_po.exception_days2 > 0 THEN
1548 c_exception_subject:= c_exception_subject || ' / ' ||
1549 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ NEEDBY DATE EARLY');
1550
1551 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY NEEDBY DATE EARLY');
1552 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_po.exception_days2));
1553 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1554 ELSE
1555 c_exception_subject:= c_exception_subject || ' / ' ||
1556 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ NEEDBY DATE LATE');
1557
1558 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY NEEDBY DATE LATE');
1559 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_po.exception_days2));
1560 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1561 END IF;
1562 END IF;
1563
1564 PJM_SCHED_INT_WF_PRIV.launch_po (c_item_type
1565 ,c_requestor || '.PO.' || to_char(lr_po.po_distribution_id)
1566 ,c_process
1567 ,c_owner
1568 ,c_requestor
1569 ,n_tolerance_days
1570 ,c_ntf_proj_mgr
1571 ,c_ntf_task_mgr
1572 ,lr_po.project_number
1573 ,lr_po.project_name
1574 ,lr_po.project_start_date
1575 ,lr_po.project_end_date
1576 ,lr_po.task_number
1577 ,lr_po.task_name
1578 ,lr_po.task_start_date
1579 ,lr_po.task_end_date
1580 ,c_exception_subject
1581 ,c_exception_body
1582 ,lr_po.document_type
1583 ,lr_po.item_number
1584 ,lr_po.description
1585 ,lr_po.po_number
1586 ,lr_po.ship_to_location
1587 ,lr_po.status
1588 ,lr_po.ordered_quantity
1589 ,lr_po.delivered_quantity
1590 ,lr_po.promised_date
1591 ,lr_po.need_by_date
1592 );
1593 END LOOP;
1594
1595 PJM_CONC.put_line(cu_po%rowcount || ' exception(s) found.' || fnd_global.newline);
1596
1597 CLOSE cu_po;
1598 END IF;
1599
1600 IF upper(c_document_type) = 'BLANKET_RELEASE' or c_document_type is null then
1601
1602 timestamp('Processing Blanket Release');
1603
1604 OPEN cu_po_release;
1605 LOOP
1606 FETCH cu_po_release INTO lr_po_release;
1607 EXIT WHEN cu_po_release%NOTFOUND;
1608
1609 c_exception_subject :='';
1610 c_exception_body :='';
1611
1612 IF lr_po_release.exception_days1 <> 0 THEN
1613 IF lr_po_release.exception_days1 > 0 THEN
1614 c_exception_subject:=
1615 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ PROMISED DATE EARLY');
1616
1617 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY PROMISED DATE EARLY');
1618 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_po_release.exception_days1));
1619 c_exception_body := FND_MESSAGE.GET;
1620 ELSE
1621 c_exception_subject:=
1622 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ PROMISED DATE LATE');
1623
1624 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY PROMISED DATE LATE');
1625 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_po_release.exception_days1));
1626 c_exception_body := FND_MESSAGE.GET;
1627 END IF;
1628 END IF;
1629
1630 IF lr_po_release.exception_days2 <> 0 THEN
1631 IF lr_po_release.exception_days2 > 0 THEN
1632 c_exception_subject:= c_exception_subject || ' / ' ||
1633 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ NEEDBY DATE EARLY');
1634
1635 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY NEEDBY DATE EARLY');
1636 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_po_release.exception_days2));
1637 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1638 ELSE
1639 c_exception_subject:= c_exception_subject || ' / ' ||
1640 FND_MESSAGE.GET_STRING('PJM','SCHED-SUBJ NEEDBY DATE LATE');
1641
1642 FND_MESSAGE.SET_NAME('PJM','SCHED-BODY NEEDBY DATE LATE');
1643 FND_MESSAGE.SET_TOKEN('NUM', abs(lr_po_release.exception_days2));
1644 c_exception_body := c_exception_body || fnd_global.newline || FND_MESSAGE.GET;
1645 END IF;
1646 END IF;
1647
1648
1649 PJM_SCHED_INT_WF_PRIV.launch_po_release (c_item_type
1650 ,c_requestor || '.BR.' || to_char(lr_po_release.po_distribution_id)
1651 ,c_process
1652 ,c_owner
1653 ,c_requestor
1654 ,n_tolerance_days
1655 ,c_ntf_proj_mgr
1656 ,c_ntf_task_mgr
1657 ,lr_po_release.project_number
1658 ,lr_po_release.project_name
1659 ,lr_po_release.project_start_date
1660 ,lr_po_release.project_end_date
1661 ,lr_po_release.task_number
1662 ,lr_po_release.task_name
1663 ,lr_po_release.task_start_date
1664 ,lr_po_release.task_end_date
1665 ,c_exception_subject
1666 ,c_exception_body
1667 ,lr_po_release.document_type
1668 ,lr_po_release.item_number
1669 ,lr_po_release.description
1670 ,lr_po_release.po_number
1671 ,to_char(lr_po_release.release_number)
1672 ,lr_po_release.ship_to_location
1673 ,lr_po_release.status
1674 ,lr_po_release.ordered_quantity
1675 ,lr_po_release.delivered_quantity
1676 ,lr_po_release.promised_date
1677 ,lr_po_release.need_by_date
1678 );
1679
1680 END LOOP;
1681
1682 PJM_CONC.put_line(cu_po_release%rowcount || ' exception(s) found.' || fnd_global.newline);
1683
1684 CLOSE cu_po_release;
1685 END IF;
1686
1687 commit;
1688
1689 END start_wf;
1690
1691
1692 END PJM_SCHED_INT_WF;