[Home] [Help]
PACKAGE BODY: APPS.MSC_WS_COLLECTIONS
Source
1 PACKAGE BODY MSC_WS_COLLECTIONS AS
2 /* $Header: MSCWCOLLB.pls 120.18 2008/03/25 19:33:22 bnaghi noship $ */
3
4
5 FUNCTION GetCollectionMethodAsNumber( CollectionMethod IN VARCHAR2) RETURN NUMBER;
6 FUNCTION GetRH_CollectionMethodAsNumber( CollectionMethod IN VARCHAR2) RETURN NUMBER;
7 FUNCTION GetRH_DateRangeTypeAsNumber(DateRangeType IN Varchar2) RETURN NUMBER;
8 FUNCTION GetLookupCodeForAppSuppList(ApprovedSupplierList IN VARCHAR2) RETURN NUMBER;
9 FUNCTION GetUserCompAssoc(UserCompanyAssoc IN VARCHAR2) RETURN NUMBER;
10 FUNCTION GetResAvail(ResourceAvailability IN VARCHAR2) RETURN NUMBER;
11 FUNCTION Dem_ULC_GetDestTableName RETURN VARCHAR2;
12 FUNCTION isValid_COLLECTION_GROUP( orgGroup IN VARCHAR2, InstanceId IN Number) RETURN BOOLEAN;
13 FUNCTION isValid_DEM_COLLECTION_GROUP( orgGroup IN VARCHAR2, InstanceId IN Number ) RETURN BOOLEAN;
14 FUNCTION isValid_INSTANCE_ID( INST_ID IN NUMBER) RETURN BOOLEAN;
15 FUNCTION isValid_DEM_COLLECTION_METHOD( methodCode IN NUMBER) RETURN BOOLEAN ;
16 FUNCTION isValid_DATE_RANGE_TYPE(CollectionMethod IN NUMBER, dateRangeType IN NUMBER) RETURN BOOLEAN;
17 FUNCTION isValid_Dates( DateRangeType in NUMBER, DateFrom in DATE, DateTo IN DATE) RETURN BOOLEAN;
18 FUNCTION isValid_Sel_Of_OrderTypes( CollectAllOrderTypes in Varchar2, IncludeOrderTypes in VARCHAR2, ExcludeOrderTypes in VARCHAR2) RETURN BOOLEAN;
19 FUNCTION isValid_RMA_Types( types IN MscChar255Arr, InstanceId IN Number) RETURN BOOLEAN;
20 FUNCTION SaveRMATypesIntoTable(RMATypes IN MscChar255Arr) RETURN BOOLEAN;
21
22
23
24 -- =============================================================
25 -- Desc: Please see package spec file for description
26 -- =============================================================
27 PROCEDURE RunASCPCollections(
28 status OUT NOCOPY VARCHAR2,
29 processId OUT NOCOPY VARCHAR2,
30 UserID IN NUMBER,
31 ResponsibilityID IN NUMBER,
32 InstanceID IN NUMBER,
33 CollectionGroup IN VARCHAR2,
34 TotalWorkerNum IN NUMBER,
35 Timeout IN NUMBER,
36 OdsPURGEoption IN VARCHAR2,
37 CollectionMethod IN VARCHAR2,
38 AnalyzeTablesEnabled IN VARCHAR2,
39 ApprovedSupplierList IN VARCHAR2, -- changed from varchar2(popList) to Number
40 AtpRulesEnabled IN VARCHAR2,
41 BomEnabled IN VARCHAR2,
42 BorEnabled IN VARCHAR2,
43 CalendarEnabled IN VARCHAR2,
44 DemandClassEnabled IN VARCHAR2,
45 ItemSubstEnabled IN VARCHAR2,
46 ForecastEnabled IN VARCHAR2,
47 ItemEnabled IN VARCHAR2,
48 KpiBisEnabled IN VARCHAR2,
49 MdsEnabled IN VARCHAR2,
50 MpsEnabled IN VARCHAR2,
51 OnHandEnabled IN VARCHAR2,
52 ParameterEnabled IN VARCHAR2,
53 PlannerEnabled IN VARCHAR2,
54 PoReceiptsEnabled IN VARCHAR2,
55 ProjectEnabled IN VARCHAR2,
56 PurReqPoEnabled IN VARCHAR2,
57 ReservesHardEnabled IN VARCHAR2,
58 ResourceAvailability IN VARCHAR2,
59 SafestockEnabled IN VARCHAR2,
60 SalesorderRtype IN VARCHAR2,
61 SourcingHistoryEnabled IN VARCHAR2,
62 SourcingEnabled IN VARCHAR2,
63 SubInvEnabled IN VARCHAR2,
64
65 SupplierResponseEnabled IN VARCHAR2,
66 TpCustomerEnabled IN VARCHAR2,
67 TripEnabled IN VARCHAR2,
68 UnitNoEnabled IN VARCHAR2,
69 UomEnabled IN VARCHAR2,
70 UserCompanyAssoc IN VARCHAR2,
71 UserSupplyDemand IN VARCHAR2,
72 WipEnabled IN VARCHAR2,
73 SalesChannelEnabled IN VARCHAR2,
74 FiscalCalendarEnabled IN VARCHAR2,
75 InternalRepairEnabled IN VARCHAR2,
76 ExternalRepairEnabled IN VARCHAR2,
77 PaybackDemandSupplyEnabled IN VARCHAR2,
78 CurrencyConversionEnabled IN VARCHAR2,
79 DeliveryDetailsEnabled IN VARCHAR2,
80
81 Odstotalworkernum IN NUMBER,
82 RecalcResAvailability IN VARCHAR2,
83 PurgeSourcingHistory IN VARCHAR2
84 ) AS
85 result BOOLEAN := false;
86 L_VAL_RESULT VARCHAR2(30);
87 code NUMBER;
88 req_id NUMBER;
89 submit_failed EXCEPTION;
90 error_tracking_num NUMBER;
91 passedCollectionGroup varchar2(100);
92 BEGIN
93
94
95 --dbms_output.put_line('ApprovedSupplierList: ' || ApprovedSupplierList);
96
97 /* Language and Trading Partners are hidden*/
98 /* validate InstanceId, CollectionGroup, WorkOrderNum, Timeout, Odstotalworkernum, all others are yes/no flags*/
99
100 error_tracking_num := 100;
101 MSC_WS_COMMON.VALIDATE_USER_RESP(L_VAL_RESULT, UserId, ResponsibilityID);
102 IF (L_VAL_RESULT <> 'OK') THEN
103 PROCESSID := -1;
104 STATUS := L_VAL_RESULT;
105 RETURN;
106 END IF;
107
108 error_tracking_num := 110;
109 result := isValid_INSTANCE_ID(InstanceId);
110 IF (result = false) THEN
111 PROCESSID := -1;
112 STATUS := 'INVALID_INSTANCE_ID';
113 RETURN;
114 END IF;
115
116 error_tracking_num := 120;
117 result := isValid_COLLECTION_GROUP( CollectionGroup, InstanceId) ;
118 IF (result = false) THEN
119 PROCESSID := -1;
120 STATUS := 'INVALID_COLLECTION_GROUP';
121 RETURN;
122 END IF;
123
124 error_tracking_num := 130;
125 IF (TotalWorkerNum < 1)THEN
126 PROCESSID := -1;
127 STATUS := 'INVALID_WORKER_NUMBER';
128 RETURN;
129 END IF;
130
131 IF (Odstotalworkernum < 1)THEN
132 PROCESSID := -1;
133 STATUS := 'INVALID_ODS_WORKER_NUMBER';
134 RETURN;
135 END IF;
136
137 IF (Timeout < 0)THEN
138 PROCESSID := -1;
139 STATUS := 'INVALID_TIMEOUT';
140 RETURN;
141 END IF;
142
143 IF (OdsPURGEoption = 'N' and CollectionMethod ='COMPLETE_REFRESH') THEN
144 PROCESSID := -1;
145 STATUS := 'INVALID_COLLECTION_METHOD_FOR_NO_PURGE';
146 RETURN;
147 END IF;
148 --bnaghi bug 6861953
149
150
151
152 IF (GetLookupCodeForAppSuppList(ApprovedSupplierList) =-1) then
153 PROCESSID := -1;
154 STATUS := 'INVALID_APPROVE_SUPPLIER_LIST';
155 RETURN;
156 END IF;
157 IF (GetResAvail(ResourceAvailability) =-1) then
158 PROCESSID := -1;
159 STATUS := 'INVALID_RESOURCE_AVAILABILITY';
160 RETURN;
161 END IF;
162 IF (GetUserCompAssoc(UserCompanyAssoc) =-1) then
163 PROCESSID := -1;
164 STATUS := 'INVALID_USER_COMPANY_ASSOC';
165 RETURN;
166 END IF;
167
168
169 -- initiating request set
170 error_tracking_num := 140;
171 result := fnd_submit.set_request_set('MSC','MSCPDX');
172 IF(result = false) THEN
173 RAISE submit_failed ;
174 END IF ;
175
176 passedCollectionGroup := CollectionGroup;
177 -- bug 6837675
178 --if ( CollectionGroup = 'All') then
179 -- passedCollectionGroup := '-999';
180 --end if;
181 -- register Planning Data Pull
182 error_tracking_num := 150;
183 result := fnd_submit.submit_program('MSC','MSCPDP','MSCPDP',InstanceId, passedCollectionGroup,
184 TotalWorkerNum,Timeout,'US', -- language
185 MSC_WS_COMMON.Bool_to_Number( OdsPURGEoption),
186 GetCollectionMethodAsNumber(CollectionMethod ),
187 MSC_WS_COMMON.Bool_to_Number(AnalyzeTablesEnabled),
188 GetLookupCodeForAppSuppList(ApprovedSupplierList),
189 MSC_WS_COMMON.Bool_to_Number(AtpRulesEnabled),
190 MSC_WS_COMMON.Bool_to_Number(BomEnabled),
191 MSC_WS_COMMON.Bool_to_Number(BorEnabled) ,
192 MSC_WS_COMMON.Bool_to_Number(CalendarEnabled),
193 MSC_WS_COMMON.Bool_to_Number(DemandClassEnabled),
194 MSC_WS_COMMON.Bool_to_Number(ItemSubstEnabled) ,
195 MSC_WS_COMMON.Bool_to_Number(ForecastEnabled),
196 MSC_WS_COMMON.Bool_to_Number(ItemEnabled),
197 MSC_WS_COMMON.Bool_to_Number(KpiBisEnabled),
198 MSC_WS_COMMON.Bool_to_Number(MdsEnabled),
199 MSC_WS_COMMON.Bool_to_Number(MpsEnabled),
200 MSC_WS_COMMON.Bool_to_Number(OnHandEnabled),
201 MSC_WS_COMMON.Bool_to_Number(ParameterEnabled),
202 MSC_WS_COMMON.Bool_to_Number(PlannerEnabled),
203 MSC_WS_COMMON.Bool_to_Number(PoReceiptsEnabled),
204 MSC_WS_COMMON.Bool_to_Number(ProjectEnabled),
205 MSC_WS_COMMON.Bool_to_Number(PurReqPoEnabled),
206 MSC_WS_COMMON.Bool_to_Number(ReservesHardEnabled),
207 GetResAvail(ResourceAvailability) ,
208 MSC_WS_COMMON.Bool_to_Number(SafestockEnabled),
209 MSC_WS_COMMON.Bool_to_Number(SalesorderRtype),
210 MSC_WS_COMMON.Bool_to_Number(SourcingHistoryEnabled),
211 MSC_WS_COMMON.Bool_to_Number(SourcingEnabled),
212 MSC_WS_COMMON.Bool_to_Number(SubInvEnabled),
213
214 MSC_WS_COMMON.Bool_to_Number(SupplierResponseEnabled),
215 MSC_WS_COMMON.Bool_to_Number(TpCustomerEnabled),
216 MSC_WS_COMMON.Bool_to_Number('N'),
217 MSC_WS_COMMON.Bool_to_Number(TripEnabled),
218 MSC_WS_COMMON.Bool_to_Number(UnitNoEnabled),
219 MSC_WS_COMMON.Bool_to_Number(UomEnabled),
220 GetUserCompAssoc(UserCompanyAssoc),
221 MSC_WS_COMMON.Bool_to_Number(UserSupplyDemand),
222 MSC_WS_COMMON.Bool_to_Number(WipEnabled),
223 MSC_WS_COMMON.Bool_to_Number(SalesChannelEnabled),
224 MSC_WS_COMMON.Bool_to_Number(FiscalCalendarEnabled),
225 MSC_WS_COMMON.Bool_to_Number(InternalRepairEnabled),
226 MSC_WS_COMMON.Bool_to_Number(ExternalRepairEnabled ),
227 MSC_WS_COMMON.Bool_to_Number(PaybackDemandSupplyEnabled ),
228 MSC_WS_COMMON.Bool_to_Number(CurrencyConversionEnabled ),
229 MSC_WS_COMMON.Bool_to_Number(DeliveryDetailsEnabled )
230 );
231
232 IF(result = false) THEN
233 RAISE submit_failed ;
234 END IF ;
235
236 -- register Planning ODS Load
237 error_tracking_num := 160;
238 result := fnd_submit.submit_program('MSC','MSCPDC','MSCPDC',InstanceId,Timeout,Odstotalworkernum,
242
239 MSC_WS_COMMON.Bool_to_Number(RecalcResAvailability),
240 MSC_WS_COMMON.Bool_to_Number(SourcingHistoryEnabled),
241 MSC_WS_COMMON.Bool_to_Number(PurgeSourcingHistory));
243 IF(result = false) THEN
244 RAISE submit_failed ;
245 END IF ;
246
247 -- submitting the request set
248 error_tracking_num := 170;
249 req_id := fnd_submit.submit_set(NULL,FALSE);
250 IF(req_id = 0) THEN
251 RAISE submit_failed ;
252 END IF ;
253
254 status := 'SUCCESS';
255 processId := req_id;
256
257 EXCEPTION
258 WHEN submit_failed THEN
259 status := 'ERROR_SUBMIT';
260 processId := -1;
261 RETURN;
262 WHEN others THEN
263 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
264 processId := -1;
265 RETURN;
266
267 END RunASCPCollections;
268
269 PROCEDURE RunASCPCollections_Pub(
270 processId OUT NOCOPY NUMBER,
271 status OUT NOCOPY VARCHAR2,
272 UserName IN VARCHAR2,
273 RespName IN VARCHAR2,
274 RespApplName IN VARCHAR2,
275 SecurityGroupName IN VARCHAR2,
276 Language IN VARCHAR2,
277 InstanceID IN Number,
278 CollectionGroup IN VARCHAR2,
279 TotalWorkerNum IN NUMBER,
280 Timeout IN NUMBER,
281 OdsPURGEoption IN VARCHAR2,
282 CollectionMethod IN VARCHAR2,
283 AnalyzeTablesEnabled IN VARCHAR2,
284 ApprovedSupplierList IN VARCHAR2,
285 AtpRulesEnabled IN VARCHAR2,
286 BomEnabled IN VARCHAR2,
287 BorEnabled IN VARCHAR2,
288 CalendarEnabled IN VARCHAR2,
289 DemandClassEnabled IN VARCHAR2,
290 ItemSubstEnabled IN VARCHAR2,
291 ForecastEnabled IN VARCHAR2,
292 ItemEnabled IN VARCHAR2,
293 KpiBisEnabled IN VARCHAR2,
294 MdsEnabled IN VARCHAR2,
295 MpsEnabled IN VARCHAR2,
296 OnHandEnabled IN VARCHAR2,
297 ParameterEnabled IN VARCHAR2,
298 PlannerEnabled IN VARCHAR2,
299 PoReceiptsEnabled IN VARCHAR2,
300 ProjectEnabled IN VARCHAR2,
301 PurReqPoEnabled IN VARCHAR2,
302 ReservesHardEnabled IN VARCHAR2,
303 ResourceAvailability IN VARCHAR2,
304 SafestockEnabled IN VARCHAR2,
305 SalesorderRtype IN VARCHAR2,
306 SourcingHistoryEnabled IN VARCHAR2,
307 SourcingEnabled IN VARCHAR2,
308 SubInvEnabled IN VARCHAR2,
309
310 SupplierResponseEnabled IN VARCHAR2,
311 TpCustomerEnabled IN VARCHAR2,
312 TripEnabled IN VARCHAR2,
313 UnitNoEnabled IN VARCHAR2,
314 UomEnabled IN VARCHAR2,
315 UserCompanyAssoc IN VARCHAR2,
316 UserSupplyDemand IN VARCHAR2,
317 WipEnabled IN VARCHAR2,
318 SalesChannelEnabled IN VARCHAR2,
319 FiscalCalendarEnabled IN VARCHAR2,
320 InternalRepairEnabled IN VARCHAR2,
321 ExternalRepairEnabled IN VARCHAR2,
322 PaybackDemandSupplyEnabled IN VARCHAR2,
323 CurrencyConversionEnabled IN VARCHAR2,
324 DeliveryDetailsEnabled IN VARCHAR2,
325
326 Odstotalworkernum IN NUMBER,
327 RecalcResAvailability IN VARCHAR2,
328 PurgeSourcingHistory IN VARCHAR2
329 ) AS
330 userid number;
331 respid number;
332 l_String VARCHAR2(30);
333 error_tracking_num number;
334 l_SecutirtGroupId NUMBER;
335 BEGIN
336 error_tracking_num :=2010;
337 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
338 IF (l_String <> 'OK') THEN
339 Status := l_String;
340 RETURN;
341 END IF;
342 error_tracking_num :=2020;
343 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid,'MSC_FNDRSRUN_COLL', l_SecutirtGroupId);
344 IF (l_String <> 'OK') THEN
345 Status := l_String;
346 RETURN;
347 END IF;
348
349 error_tracking_num :=2040;
350
351
352 RunASCPCollections ( Status,
353 processId,
354 userid,
355 respid,
356 InstanceID,
357 CollectionGroup,
358 TotalWorkerNum,
359 Timeout,
360 OdsPURGEoption ,
361 CollectionMethod ,
362 AnalyzeTablesEnabled ,
363 ApprovedSupplierList ,
364 AtpRulesEnabled ,
365 BomEnabled ,
366 BorEnabled ,
367 CalendarEnabled ,
368 DemandClassEnabled ,
369 ItemSubstEnabled ,
370 ForecastEnabled ,
371 ItemEnabled ,
372 KpiBisEnabled ,
373 MdsEnabled ,
374 MpsEnabled ,
375 OnHandEnabled ,
376 ParameterEnabled ,
377 PlannerEnabled ,
378 PoReceiptsEnabled ,
379 ProjectEnabled ,
380 PurReqPoEnabled ,
381 ReservesHardEnabled ,
382 ResourceAvailability ,
383 SafestockEnabled ,
384 SalesorderRtype ,
385 SourcingHistoryEnabled ,
386 SourcingEnabled ,
387 SubInvEnabled ,
388
389 SupplierResponseEnabled ,
390 TpCustomerEnabled ,
391 TripEnabled ,
392 UnitNoEnabled ,
393 UomEnabled ,
394 UserCompanyAssoc ,
395 UserSupplyDemand ,
396 WipEnabled ,
397 SalesChannelEnabled ,
398 FiscalCalendarEnabled ,
399 InternalRepairEnabled ,
400 ExternalRepairEnabled ,
401 PaybackDemandSupplyEnabled ,
402 CurrencyConversionEnabled ,
403 DeliveryDetailsEnabled ,
404
405 Odstotalworkernum ,
406 RecalcResAvailability ,
407 PurgeSourcingHistory );
408 -- dbms_output.put_line('USERID=' || userid);
409
410
411 EXCEPTION
412 WHEN others THEN
413 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
414
415 return;
416
417
418 END RunASCPCollections_Pub;
419
420
421
422
423 -- =============================================================
424 -- Desc: Please see package spec file for description
425 -- =============================================================
426 PROCEDURE RunODSLoad(
427 status OUT NOCOPY VARCHAR2,
428 processId OUT NOCOPY VARCHAR2,
429 UserID IN NUMBER ,
430 ResponsibilityID IN NUMBER ,
431 InstanceId IN NUMBER ,
432 Timeout IN NUMBER ,
433 TotalWorkerNum IN NUMBER ,
434 RecalcResAvailability IN VARCHAR2 ,
435 RecalcSourcingHistory IN VARCHAR2 ,
436 PurgeSourcingHistory IN VARCHAR2
437 ) as
438 L_VAL_RESULT VARCHAR2(30);
439 result BOOLEAN := false;
440 req_id NUMBER:=0;
441 submit_failed EXCEPTION;
442 error_tracking_num NUMBER;
443 BEGIN
444 error_tracking_num :=100;
445 MSC_WS_COMMON.VALIDATE_USER_RESP (L_VAL_RESULT, UserId, ResponsibilityID);
446 IF (L_VAL_RESULT <> 'OK') THEN
447 PROCESSID := -1;
448 STATUS := L_VAL_RESULT;
449 RETURN;
450 END IF;
451
452 error_tracking_num :=120;
453 result := isValid_INSTANCE_ID( InstanceId);
454 IF (result = false) THEN
455 PROCESSID := -1;
456 STATUS := 'INVALID_INSTANCE_ID';
457 RETURN;
458 END IF;
459
460 error_tracking_num :=130;
461 IF (TotalWorkerNum < 1)THEN
462 PROCESSID := -1;
463 STATUS := 'INVALID_WORKER_NUMBER';
464 RETURN;
465 END IF;
466
467 error_tracking_num :=140;
468 IF (Timeout < 0)THEN
469 PROCESSID := -1;
470 STATUS := 'INVALID_TIMEOUT';
471 RETURN;
472 END IF;
473
474 -- register Planning ODS Load
475 error_tracking_num :=150;
476 req_id := fnd_request.submit_request('MSC','MSCPDC','Planning ODS Load',NULL, false,
477 InstanceId, Timeout, TotalWorkerNum,
478 MSC_WS_COMMON.Bool_to_Number(RecalcResAvailability),
479 MSC_WS_COMMON.Bool_to_Number(RecalcSourcingHistory),
480 MSC_WS_COMMON.Bool_to_Number(PurgeSourcingHistory));
481
482 IF(req_id = 0) THEN
483 raise submit_failed ;
484 END IF ;
485
486 status := 'SUCCESS';
487 processId := req_id;
488
489 EXCEPTION
490 WHEN submit_failed THEN
491 status := 'ERROR_SUBMIT';
492 processId := -1;
493 RETURN;
494 WHEN others THEN
495 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
496 processId := -1;
497 RETURN;
498
499 END RunODSLoad;
500
501 PROCEDURE RunODSLoad_Pub(
502 processId OUT NOCOPY NUMBER,
503 status OUT NOCOPY VARCHAR2,
504 UserName IN VARCHAR2,
505 RespName IN VARCHAR2,
506 RespApplName IN VARCHAR2,
507 SecurityGroupName IN VARCHAR2,
508 Language IN VARCHAR2,
509 InstanceId IN NUMBER ,
510 Timeout IN NUMBER ,
511 TotalWorkerNum IN NUMBER ,
512 RecalcResAvailability IN VARCHAR2 ,
513 RecalcSourcingHistory IN VARCHAR2 ,
514 PurgeSourcingHistory IN VARCHAR2
515 ) AS
516 userid number;
517 respid number;
518 l_String VARCHAR2(30);
519 error_tracking_num number;
520 l_SecutirtGroupId NUMBER;
521 BEGIN
522 error_tracking_num :=2010;
523 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
524 IF (l_String <> 'OK') THEN
525 Status := l_String;
526 RETURN;
527 END IF;
528 error_tracking_num :=2020;
529 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid,'MSC_FNDRSRUN_COLL', l_SecutirtGroupId);
530 IF (l_String <> 'OK') THEN
531 Status := l_String;
532 RETURN;
533 END IF;
534
535 error_tracking_num :=2040;
536
537
538 RunODSLoad (
539 status,
540 processId,
541 userid,
542 respid,
543 InstanceId,
544 Timeout,
545 TotalWorkerNum,
546 RecalcResAvailability,
547 RecalcSourcingHistory,
548 PurgeSourcingHistory );
549
550
551 -- dbms_output.put_line('USERID=' || userid);
552
553
554 EXCEPTION
555 WHEN others THEN
556 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
557
558 return;
559
560
561 END RunODSLoad_Pub;
562
563
564 -- =============================================================
565 -- Desc: Please see package spec file for description
566 -- =============================================================
567 PROCEDURE RunDemantraShipmentBooking(
568 status OUT nocopy VARCHAR2,
569 processid OUT nocopy VARCHAR2,
570 UserID IN NUMBER,
571 ResponsibilityID IN NUMBER,
572 InstanceId IN NUMBER,
573 CollectionGroup IN VARCHAR2,
574 CollectionMethod IN NUMBER,
575 DateRangeType IN NUMBER,
576 HistoryCollectionWindow IN NUMBER DEFAULT NULL,
577 DateFrom IN DATE DEFAULT NULL,
578 DateTo IN DATE DEFAULT NULL,
579 BHBookedItemsBookedDate IN VARCHAR2,
580 BHBookedItemsRequestedDate IN VARCHAR2,
581 BHRequestedItemsBookedDate IN VARCHAR2,
582 BHRequestedItemsRequestedDate IN VARCHAR2,
583 SHShippedItemsShippedDate IN VARCHAR2,
584 SHShippedItemsRequestedDate IN VARCHAR2,
585 SHRequestedItemsShippedDate IN VARCHAR2,
586 SHRequestedItemsRequestedDate IN VARCHAR2,
587 CollectISO IN VARCHAR2,
588 CollectAllOrderTypes IN VARCHAR2,
589 IncludeOrderTypes IN VARCHAR2 DEFAULT NULL,
590 ExcludeOrderTypes IN VARCHAR2 DEFAULT NULL,
591 LaunchDownload IN VARCHAR2
592 ) IS
596 submit_failed EXCEPTION;
593 L_VAL_RESULT VARCHAR2(30);
594 result BOOLEAN := false;
595 req_id NUMBER:=0;
597 hiddenParam VARCHAR2(80);
598 error_tracking_num NUMBER;
599 passedCollectionGroup varchar2(100);
600 BEGIN
601 /* VALIDATE: InstanceId, CollectionGroup , CollectionMethod,DateRangeType*/
602 /* for CollectAllOrderTypes = no , EITHER IncludeOrderTypes non empty, or ExcludeOrderTypes not empty, but not both*/
603
604 error_tracking_num := 100;
605 MSC_WS_COMMON.VALIDATE_USER_RESP (L_VAL_RESULT, UserId, ResponsibilityID);
606 IF (L_VAL_RESULT <> 'OK') THEN
607 PROCESSID := -1;
608 STATUS := L_VAL_RESULT;
609 RETURN;
610 END IF;
611
612 error_tracking_num := 110;
613 result := isValid_INSTANCE_ID( InstanceId);
614 IF (result = false) THEN
615 PROCESSID := -1;
616 STATUS := 'INVALID_INSTANCE_ID';
617 RETURN;
618 END IF;
619
620 error_tracking_num := 120;
621 result := isValid_DEM_COLLECTION_GROUP( CollectionGroup, InstanceId) ;
622 IF (result = false) THEN
623 PROCESSID := -1;
624 STATUS := 'INVALID_COLLECTION_GROUP';
625 RETURN;
626 END IF;
627
628 error_tracking_num := 130;
629 result := isValid_DEM_COLLECTION_METHOD( CollectionMethod) ;
630 IF (result = false) THEN
631 PROCESSID := -1;
632 STATUS := 'INVALID_COLLECTION_METHOD';
633 RETURN;
634 END IF;
635
636
637 hiddenParam := '1';
638
639 error_tracking_num := 140;
640 result := isValid_DATE_RANGE_TYPE(CollectionMethod, DateRangeType );
641 IF (result = false) THEN
642 PROCESSID := -1;
643 STATUS := 'INVALID_DATE_RANGE_TYPE';
644 RETURN;
645 END IF;
646
647 error_tracking_num := 145;
648 result := isValid_Dates( DateRangeType, DateFrom, DateTo );
649 IF (result = false) THEN
650 PROCESSID := -1;
651 STATUS := 'INVALID_DATES';
652 RETURN;
653 END IF;
654
655 error_tracking_num := 149;
656 -- for 'rolling' dateRange Type, HistoryCollectionWindow cannot be null
657 IF ( DateRangeType = 2) THEN
658 IF ( HistoryCollectionWindow is NULL ) THEN
659 PROCESSID := -1;
660 STATUS := 'INVALID_HISTORY_COLLECTION_WINDOW';
661 RETURN;
662 END IF;
663 END IF;
664
665 error_tracking_num := 150;
666 result := isValid_Sel_Of_OrderTypes( CollectAllOrderTypes, IncludeOrderTypes, ExcludeOrderTypes );
667 IF (result = false) THEN
668 PROCESSID := -1;
669 STATUS := 'INVALID_ORDER_TYPE_SELECTION';
670 RETURN;
671 END IF;
672
673
674 -- initiating request set
675 error_tracking_num := 160;
676 result := fnd_submit.set_request_set('MSD','MSDDEMRSCH');
677 IF(result = false) THEN
678 RAISE submit_failed ;
679 END IF ;
680
681 passedCollectionGroup := CollectionGroup;
682 -- fixed done dor bug 6837675
683 --if ( CollectionGroup = 'All') then
684 -- passedCollectionGroup := '-999';
685 --end if;
686 -- Stage 1 Collect Shipment and booking History
687 error_tracking_num := 170;
688 result := fnd_submit.submit_program('MSD','MSDDEMCHD','MSDDEMRSCHD',
689 InstanceId,passedCollectionGroup, CollectionMethod,hiddenParam,
690 DateRangeType, HistoryCollectionWindow,
691 to_char(DateFrom, 'YYYY/MM/DD HH24:MI:SS'), to_char(DateTo, 'YYYY/MM/DD HH24:MI:SS'),
692 MSC_WS_COMMON.Bool_to_Number(BHBookedItemsBookedDate),
693 MSC_WS_COMMON.Bool_to_Number(BHBookedItemsRequestedDate),
694 MSC_WS_COMMON.Bool_to_Number(BHRequestedItemsBookedDate),
695 MSC_WS_COMMON.Bool_to_Number(BHRequestedItemsRequestedDate),
696 MSC_WS_COMMON.Bool_to_Number(SHShippedItemsShippedDate),
697 MSC_WS_COMMON.Bool_to_Number(SHShippedItemsRequestedDate),
698 MSC_WS_COMMON.Bool_to_Number(SHRequestedItemsShippedDate),
699 MSC_WS_COMMON.Bool_to_Number(SHRequestedItemsRequestedDate),
700 MSC_WS_COMMON.Bool_to_Number(CollectISO),
701 MSC_WS_COMMON.Bool_to_Number(CollectAllOrderTypes ),
702 IncludeOrderTypes,
703 ExcludeOrderTypes,
704 MSC_WS_COMMON.Bool_to_Number(LaunchDownload )
705 );
706
707 IF(result = false) THEN
708 RAISE submit_failed ;
709 END IF ;
710
711 -- Stage 2 Push Setup Params
712 error_tracking_num := 180;
713 result := fnd_submit.submit_program('MSD','MSDDEMPSP','MSDDEMRSPSP',
714 InstanceId,passedCollectionGroup);
715
716 IF(result = false) THEN
717 RAISE submit_failed ;
718 END IF ;
719
720 -- Stage 3 Populate Staging Tables
721 -- Stage 3.1 Collect Level Type
722 error_tracking_num := 190;
723 result := fnd_submit.submit_program('MSD','MSDDEMCLT','MSDDEMRSPST',
724 InstanceId, 2);
725
726 IF(result = false) THEN
727 RAISE submit_failed ;
728 END IF ;
729
730 -- Stage 3.2 Collect Level Type
731 error_tracking_num := 200;
732 result := fnd_submit.submit_program('MSD','MSDDEMCLT','MSDDEMRSPST',
733 InstanceId, 1);
734
735 IF(result = false) THEN
736 RAISE submit_failed ;
737 END IF ;
738
739 -- Stage 3.3 Update Level Codes
740 error_tracking_num := 210;
741 result := fnd_submit.submit_program('MSD','MSDDEMULC','MSDDEMRSPST',
742 InstanceId, 'SITE', Dem_ULC_GetDestTableName(), 'DM_SITE_CODE', 'EBS_SITE_SR_PK');
743
744 IF(result = false) THEN
745 RAISE submit_failed ;
746 END IF ;
747
748 -- Stage 3.4 Collect Time
749 error_tracking_num := 220;
750 result := fnd_submit.submit_program('MSD','MSDDEMCTD','MSDDEMRSPST',
751 MSC_WS_COMMON.Bool_to_Number(LaunchDownload ));
752
753 IF(result = false) THEN
754 RAISE submit_failed ;
755 END IF ;
756
757 -- Stage 4 Launch EP Load
758 error_tracking_num := 230;
759 result := fnd_submit.submit_program('MSD','MSDDEMARD','MSDDEMRSLH',
760 MSC_WS_COMMON.Bool_to_Number(LaunchDownload ));
761
762 IF(result = false) THEN
763 RAISE submit_failed ;
764 END IF ;
765
766 -- submitting the request set
767 error_tracking_num := 240;
768 req_id := fnd_submit.submit_set(NULL,FALSE);
769 IF(req_id = 0) THEN
770 RAISE submit_failed ;
771 END IF ;
772
773 status := 'SUCCESS';
774 processId := req_id;
775
776 EXCEPTION
777 WHEN submit_failed THEN
778 status := 'ERROR_SUBMIT';
779 processId := -1;
780 RETURN;
781 WHEN others THEN
782 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
783 processId := -1;
784 RETURN;
785
786 END RunDemantraShipmentBooking;
787
788
789 PROCEDURE RunDemantraShipmentBooking_Pub(
790 processId OUT NOCOPY NUMBER,
791 status OUT NOCOPY VARCHAR2,
792 UserName IN VARCHAR2,
793 RespName IN VARCHAR2,
794 RespApplName IN VARCHAR2,
795 SecurityGroupName IN VARCHAR2,
796 Language IN VARCHAR2,
797 InstanceId IN NUMBER,
798 CollectionGroup IN VARCHAR2,
799 CollectionMethod IN NUMBER,
800 DateRangeType IN NUMBER,
801 HistoryCollectionWindow IN NUMBER DEFAULT NULL,
802 DateFrom IN DATE DEFAULT NULL,
803 DateTo IN DATE DEFAULT NULL,
804 BHBookedItemsBookedDate IN VARCHAR2,
805 BHBookedItemsRequestedDate IN VARCHAR2,
806 BHRequestedItemsBookedDate IN VARCHAR2,
807 BHRequestedItemsRequestedDate IN VARCHAR2,
808 SHShippedItemsShippedDate IN VARCHAR2,
809 SHShippedItemsRequestedDate IN VARCHAR2,
810 SHRequestedItemsShippedDate IN VARCHAR2,
811 SHRequestedItemsRequestedDate IN VARCHAR2,
812 CollectISO IN VARCHAR2,
813 CollectAllOrderTypes IN VARCHAR2,
814 IncludeOrderTypes IN VARCHAR2 DEFAULT NULL,
815 ExcludeOrderTypes IN VARCHAR2 DEFAULT NULL,
816 LaunchDownload IN VARCHAR2
817 ) AS
818 userid number;
819 respid number;
820 l_String VARCHAR2(30);
821 error_tracking_num number;
822 l_SecutirtGroupId NUMBER;
823 BEGIN
824 error_tracking_num :=2010;
825 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
826 IF (l_String <> 'OK') THEN
827 Status := l_String;
828 RETURN;
829 END IF;
830 error_tracking_num :=2020;
831 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid,'MSD_DEM_FNDRSRUN_CHD', l_SecutirtGroupId);
832 IF (l_String <> 'OK') THEN
833 Status := l_String;
834 RETURN;
835 END IF;
836
837 error_tracking_num :=2040;
838
839
840 RunDemantraShipmentBooking( Status,
841 processid,
842 userid,
843 respid,
844 InstanceId,
845 CollectionGroup,
846 CollectionMethod,
847 DateRangeType,
848 HistoryCollectionWindow,
849 DateFrom,
850 DateTo ,
851 BHBookedItemsBookedDate,
852 BHBookedItemsRequestedDate,
853 BHRequestedItemsBookedDate,
854 BHRequestedItemsRequestedDate,
855 SHShippedItemsShippedDate,
856 SHShippedItemsRequestedDate,
857 SHRequestedItemsShippedDate,
858 SHRequestedItemsRequestedDate,
859 CollectISO,
860 CollectAllOrderTypes,
861 IncludeOrderTypes,
862 ExcludeOrderTypes,
863 LaunchDownload);
864
865
866 EXCEPTION
867 WHEN others THEN
868 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
869
870 return;
871
872
873 END RunDemantraShipmentBooking_Pub;
874
875 -- =============================================================
876 -- Desc: Please see package spec file for description
877 -- =============================================================
878 PROCEDURE RunDemantraSCIData (
879 status OUT NOCOPY VARCHAR2,
880 processid OUT NOCOPY VARCHAR2,
881 UserID IN NUMBER,
882 ResponsibilityID IN NUMBER,
883 InstanceId IN NUMBER,
884 CollectionGroup IN VARCHAR2,
885 CollectionMethod IN NUMBER,
886 DateRangeType IN NUMBER,
887 HistoryCollectionWindow IN NUMBER DEFAULT NULL,
888 DateFrom IN DATE DEFAULT NULL,
889 DateTo IN DATE DEFAULT NULL
890 ) is
891 L_VAL_RESULT VARCHAR2(30);
892 result BOOLEAN := false;
893 req_id NUMBER:=0;
894 submit_failed EXCEPTION;
895 hiddenParam VARCHAR2(80);
896 error_tracking_number NUMBER;
897 passedCollectionGroup varchar2(100);
898 BEGIN
899 /* validate: Instanceid, CollectionGroup, CollectionMenthod, DateRangeType*/
900
901 error_tracking_number := 100;
902 MSC_WS_COMMON.VALIDATE_USER_RESP (L_VAL_RESULT, UserId, ResponsibilityID);
903 IF (L_VAL_RESULT <> 'OK') THEN
904 PROCESSID := -1;
905 STATUS := L_VAL_RESULT;
906 RETURN;
907 END IF;
908
909 error_tracking_number := 110;
910 result := isValid_INSTANCE_ID( InstanceId);
911 IF (result = false) THEN
912 PROCESSID := -1;
913 STATUS := 'INVALID_INSTANCE_ID';
914 RETURN;
915 END IF;
916
917 error_tracking_number := 120;
918 result := isValid_DEM_COLLECTION_GROUP( CollectionGroup, InstanceId) ;
919 IF (result = false) THEN
920 PROCESSID := -1;
921 STATUS := 'INVALID_COLLECTION_GROUP';
922 RETURN;
923 END IF;
924
925 error_tracking_number := 130;
926 result := isValid_DEM_COLLECTION_METHOD( CollectionMethod) ;
927 IF (result = false) THEN
928 PROCESSID := -1;
929 STATUS := 'INVALID_COLLECTION_METHOD';
930 RETURN;
931 END IF;
932
933 hiddenParam := '1';
934
935 error_tracking_number := 140;
936 result := isValid_DATE_RANGE_TYPE(CollectionMethod, DateRangeType );
937 IF (result = false) THEN
938 PROCESSID := -1;
939 STATUS := 'INVALID_DATE_RANGE_TYPE';
940 RETURN;
941 END IF;
942
943 error_tracking_number := 145;
944 result := isValid_Dates( DateRangeType, DateFrom, DateTo );
945 IF (result = false) THEN
946 PROCESSID := -1;
947 STATUS := 'INVALID_DATES';
948 RETURN;
949 END IF;
950
951 error_tracking_number := 149;
952 -- for 'rolling' dateRange Type, HistoryCollectionWindow cannot be null
953 IF ( DateRangeType = 2) THEN
954 IF ( HistoryCollectionWindow is NULL ) THEN
955 PROCESSID := -1;
956 STATUS := 'INVALID_HISTORY_COLLECTION_WINDOW';
957 RETURN;
958 END IF;
959 END IF;
960
961 passedCollectionGroup := CollectionGroup;
962 -- bug 6837675
963 --if ( CollectionGroup = 'All') then
964 -- passedCollectionGroup := '-999';
965 --end if;
966 -- register Collect SCI Data
967 error_tracking_number := 150;
968 req_id := fnd_request.submit_request('MSD','MSDDEMCSD','Collect SCI Data',NULL, false,
969 InstanceId, passedCollectionGroup, CollectionMethod,
970 hiddenParam, DateRangeType, HistoryCollectionWindow,
971 to_char(DateFrom, 'YYYY/MM/DD HH24:MI:SS'), to_char(DateTo, 'YYYY/MM/DD HH24:MI:SS'));
972
973
974 IF(req_id = 0) THEN
975 RAISE submit_failed ;
976 END IF ;
977
978 status := 'SUCCESS';
979 processId := req_id;
980
981 EXCEPTION
982 WHEN submit_failed THEN
983 status := 'ERROR_SUBMIT';
984 processId := -1;
985 RETURN;
986 WHEN others THEN
987 status := 'ERROR_UNEXPECTED_'||error_tracking_number;
988 processId := -1;
989 RETURN;
990
991 END RunDemantraSCIData;
992
993 PROCEDURE RunDemantraSCIData_Pub(
994 processId OUT NOCOPY NUMBER,
995 status OUT NOCOPY VARCHAR2,
996 UserName IN VARCHAR2,
997 RespName IN VARCHAR2,
998 RespApplName IN VARCHAR2,
999 SecurityGroupName IN VARCHAR2,
1000 Language IN VARCHAR2,
1001 InstanceId IN NUMBER,
1002 CollectionGroup IN VARCHAR2,
1003 CollectionMethod IN NUMBER,
1004 DateRangeType IN NUMBER,
1005 HistoryCollectionWindow IN NUMBER DEFAULT NULL,
1006 DateFrom IN DATE DEFAULT NULL,
1007 DateTo IN DATE DEFAULT NULL
1008 ) AS
1009 userid number;
1010 respid number;
1011 l_String VARCHAR2(30);
1012 error_tracking_num number;
1013 l_SecutirtGroupId NUMBER;
1014 BEGIN
1015 error_tracking_num :=2010;
1016 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1017 IF (l_String <> 'OK') THEN
1018 Status := l_String;
1019 RETURN;
1020 END IF;
1021 error_tracking_num :=2020;
1022 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid,respid,'MSD_DEM_FNDRSRUN_SCI', l_SecutirtGroupId);
1023 IF (l_String <> 'OK') THEN
1024 Status := l_String;
1025 RETURN;
1026 END IF;
1027
1028 error_tracking_num :=2040;
1029
1030
1031 RunDemantraSCIData( Status,
1032 processid,
1033 userid,
1034 respid,
1035 InstanceId,
1036 CollectionGroup,
1037 CollectionMethod,
1038 DateRangeType,
1039 HistoryCollectionWindow,
1040 DateFrom ,
1041 DateTo );
1042
1043
1044
1045 EXCEPTION
1046 WHEN others THEN
1047 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
1048
1049 return;
1050
1051
1052 END RunDemantraSCIData_Pub;
1053
1054
1055
1056 -- =============================================================
1057 -- Desc: Please see package spec file for description
1058 -- =============================================================
1059 PROCEDURE RunDemantraCurrConversion(
1060 status OUT NOCOPY VARCHAR2,
1061 processid OUT NOCOPY VARCHAR2,
1062 UserID IN NUMBER,
1063 ResponsibilityID IN NUMBER,
1064 InstanceId IN NUMBER,
1065 DateFrom IN DATE DEFAULT NULL,
1066 DateTo IN DATE DEFAULT NULL,
1067 CollectAllCurrencies IN VARCHAR2,
1068 IncludeCurrencyList IN VARCHAR2 DEFAULT NULL,
1069 ExcludeCurrencyList IN VARCHAR2 DEFAULT NULL
1070 ) is
1071 L_VAL_RESULT VARCHAR2(30);
1072 result BOOLEAN := false;
1073 req_id NUMBER:=0;
1074 submit_failed EXCEPTION;
1075 hiddenParam VARCHAR2(80);
1076 error_tracking_number NUMBER;
1077 BEGIN
1078
1079 error_tracking_number:= 100;
1080 MSC_WS_COMMON.VALIDATE_USER_RESP (L_VAL_RESULT, UserId, ResponsibilityID);
1081 IF (L_VAL_RESULT <> 'OK') THEN
1082 PROCESSID := -1;
1083 STATUS := L_VAL_RESULT;
1084 RETURN;
1085 END IF;
1086
1087 error_tracking_number:= 110;
1088 result := isValid_INSTANCE_ID( InstanceId);
1089 IF (result = false) THEN
1090 PROCESSID := -1;
1091 STATUS := 'INVALID_INSTANCE_ID';
1092 RETURN;
1093 END IF;
1094
1095 error_tracking_number := 111;
1096 IF (DateFrom > DateTo) THEN
1097 PROCESSID := -1;
1098 STATUS := 'INVALID_DATES';
1099 RETURN;
1100 END IF;
1101
1102
1103 error_tracking_number:= 120;
1104 result:= isValid_Sel_Of_OrderTypes( CollectAllCurrencies , IncludeCurrencyList, ExcludeCurrencyList );
1105 IF (result = false) THEN
1106 PROCESSID := -1;
1107 STATUS := 'INVALID_SELECTION_CURRENCY_LIST';
1108 RETURN;
1109 END IF;
1110
1111
1112 -- register Collect Currency
1113 error_tracking_number:= 130;
1114 req_id := fnd_request.submit_request('MSD','MSDDEMCCONV','Collect Currency',NULL, false,
1115 InstanceId,
1116 to_char(DateFrom, 'YYYY/MM/DD HH24:MI:SS'), to_char(DateTo, 'YYYY/MM/DD HH24:MI:SS'),
1117 MSC_WS_COMMON.Bool_to_Number(CollectAllCurrencies),
1118 IncludeCurrencyList,
1119 ExcludeCurrencyList
1120 );
1121
1122
1123 IF(req_id = 0) THEN
1124 RAISE submit_failed ;
1125 END IF ;
1126
1127 status := 'SUCCESS';
1128 processId := req_id;
1129
1130 EXCEPTION
1131 WHEN submit_failed THEN
1132 status := 'ERROR_SUBMIT';
1133 processId := -1;
1134 RETURN;
1135 WHEN others THEN
1136 status := 'ERROR_UNEXPECTED_'||error_tracking_number;
1137 processId := -1;
1138 RETURN;
1139
1140 END RunDemantraCurrConversion;
1141
1142
1143 PROCEDURE RunDemantraCurrConversion_Pub(
1144 processId OUT NOCOPY NUMBER,
1145 status OUT NOCOPY VARCHAR2,
1146 UserName IN VARCHAR2,
1147 RespName IN VARCHAR2,
1148 RespApplName IN VARCHAR2,
1149 SecurityGroupName IN VARCHAR2,
1150 Language IN VARCHAR2,
1151 InstanceId IN NUMBER,
1152 DateFrom IN DATE DEFAULT NULL,
1153 DateTo IN DATE DEFAULT NULL,
1154 CollectAllCurrencies IN VARCHAR2,
1155 IncludeCurrencyList IN VARCHAR2 DEFAULT NULL,
1156 ExcludeCurrencyList IN VARCHAR2 DEFAULT NULL
1157 ) AS
1158 userid number;
1159 respid number;
1160 l_String VARCHAR2(30);
1161 error_tracking_num number;
1162 l_SecutirtGroupId NUMBER;
1163 BEGIN
1164 error_tracking_num :=2010;
1165 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1166 IF (l_String <> 'OK') THEN
1167 Status := l_String;
1168 RETURN;
1169 END IF;
1170 error_tracking_num :=2020;
1171 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSD_DEM_FNDRSRUN_CURCONV',l_SecutirtGroupId);
1172 IF (l_String <> 'OK') THEN
1173 Status := l_String;
1174 RETURN;
1175 END IF;
1176
1177 error_tracking_num :=2040;
1178
1179
1180 RunDemantraCurrConversion( Status,
1181 processid,
1182 userid,
1183 respid,
1184 InstanceId,
1185 DateFrom ,
1186 DateTo ,
1187 CollectAllCurrencies,
1188 IncludeCurrencyList,
1189 ExcludeCurrencyList );
1190
1191
1192 EXCEPTION
1193 WHEN others THEN
1194 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
1195
1196 return;
1197
1198
1199 END RunDemantraCurrConversion_Pub;
1200 -- =============================================================
1201 -- Desc: Please see package spec file for description
1202 -- =============================================================
1203 PROCEDURE RunDemantraUOMConversion(
1204 status OUT NOCOPY VARCHAR2,
1205 processid OUT NOCOPY VARCHAR2,
1206 UserID IN NUMBER,
1207 ResponsibilityID IN NUMBER,
1208 InstanceId IN NUMBER,
1209 IncludeAll IN VARCHAR2,
1210 IncludeUomList IN VARCHAR2 DEFAULT NULL,
1211 ExcludeUomList IN VARCHAR2 DEFAULT NULL
1212 ) is
1213 L_VAL_RESULT VARCHAR2(30);
1214 result BOOLEAN := false;
1215 req_id NUMBER:=0;
1216 submit_failed EXCEPTION;
1217 error_tracking_number NUMBER;
1218 BEGIN
1219
1220 error_tracking_number := 100;
1221 MSC_WS_COMMON.VALIDATE_USER_RESP (L_VAL_RESULT, UserId, ResponsibilityID);
1222 IF (L_VAL_RESULT <> 'OK') THEN
1223 PROCESSID := -1;
1224 STATUS := L_VAL_RESULT;
1225 RETURN;
1226 END IF;
1227
1228 error_tracking_number := 110;
1229 result := isValid_INSTANCE_ID( InstanceId);
1230 IF (result = false) THEN
1231 PROCESSID := -1;
1232 STATUS := 'INVALID_INSTANCE_ID';
1233 RETURN;
1234 END IF;
1235
1236 error_tracking_number := 120;
1237 result:= isValid_Sel_Of_OrderTypes( IncludeAll , IncludeUomList, ExcludeUomList );
1238 IF (result = false) THEN
1239 PROCESSID := -1;
1240 STATUS := 'INVALID_SELECTION_UOM_LIST';
1241 RETURN;
1242 END IF;
1243
1244
1245 -- register Collect UOM Conversions
1246 error_tracking_number := 130;
1247 req_id := fnd_request.submit_request('MSD','MSDDEMUOM','UOM Conversions',NULL, false,
1248 InstanceId,
1249 MSC_WS_COMMON.Bool_to_Number(IncludeAll),
1250 IncludeUomList,
1251 ExcludeUomList
1252 );
1253
1254
1255 IF(req_id = 0) THEN
1256 raise submit_failed ;
1257 END IF ;
1258
1259 status := 'SUCCESS';
1260 processId := req_id;
1261
1262 EXCEPTION
1263 WHEN submit_failed THEN
1264 status := 'ERROR_SUBMIT';
1265 processId := -1;
1266 RETURN;
1267 WHEN others THEN
1268 status := 'ERROR_UNEXPECTED_'||error_tracking_number;
1269 processId := -1;
1270 RETURN;
1271
1272 END RunDemantraUOMConversion;
1273
1274 PROCEDURE RunDemantraUOMConversion_Pub(
1275 processId OUT NOCOPY NUMBER,
1276 status OUT NOCOPY VARCHAR2,
1277 UserName IN VARCHAR2,
1278 RespName IN VARCHAR2,
1279 RespApplName IN VARCHAR2,
1280 SecurityGroupName IN VARCHAR2,
1281 Language IN VARCHAR2,
1282 InstanceId IN NUMBER,
1283 IncludeAll IN VARCHAR2,
1284 IncludeUomList IN VARCHAR2 DEFAULT NULL,
1285 ExcludeUomList IN VARCHAR2 DEFAULT NULL
1286 ) AS
1287 userid number;
1288 respid number;
1289 l_String VARCHAR2(30);
1290 error_tracking_num number;
1291 l_SecutirtGroupId NUMBER;
1292 BEGIN
1293 error_tracking_num :=2010;
1294 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1295 IF (l_String <> 'OK') THEN
1296 status := l_String;
1297 RETURN;
1298 END IF;
1299 error_tracking_num :=2020;
1300 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid,'MSD_DEM_FNDRSRUN_UOM', l_SecutirtGroupId);
1301 IF (l_String <> 'OK') THEN
1302 status := l_String;
1303 RETURN;
1304 END IF;
1305
1306 error_tracking_num :=2040;
1307
1308
1309 RunDemantraUOMConversion(
1310 status,
1311 processid,
1312 userid,
1313 respid,
1314 InstanceId ,
1315 IncludeAll,
1316 IncludeUomList ,
1317 ExcludeUomList );
1318
1319
1320
1321 EXCEPTION
1322 WHEN others THEN
1323 status := 'ERROR_UNEXPECTED_'||error_tracking_num||'_'||sqlerrm;
1324
1325 return;
1326
1327
1328 END RunDemantraUOMConversion_Pub;
1329
1330 -- =============================================================
1331 -- Desc: Please see package spec file for description
1332 -- =============================================================
1333 PROCEDURE RunDemantraPricingData(
1334 status OUT NOCOPY VARCHAR2,
1335 processid OUT NOCOPY VARCHAR2,
1336 UserID IN NUMBER,
1337 ResponsibilityID IN NUMBER,
1338 InstanceId IN NUMBER,
1339 DateFrom IN DATE,
1340 DateTo IN DATE,
1341 IncludeAllLists IN VARCHAR2,
1342 IncludePriceList IN VARCHAR2 DEFAULT NULL,
1343 ExcludePriceList IN VARCHAR2 DEFAULT NULL
1344 ) is
1345 L_VAL_RESULT VARCHAR2(30);
1346 result BOOLEAN := false;
1347 req_id NUMBER:=0;
1348 submit_failed EXCEPTION;
1349 error_tracking_number NUMBER;
1350 BEGIN
1351
1352 error_tracking_number := 100;
1353 MSC_WS_COMMON.VALIDATE_USER_RESP (L_VAL_RESULT, UserId, ResponsibilityID);
1354 IF (L_VAL_RESULT <> 'OK') THEN
1355 PROCESSID := -1;
1356 STATUS := L_VAL_RESULT;
1357 RETURN;
1358 END IF;
1359
1360 error_tracking_number := 110;
1361 result := isValid_INSTANCE_ID( InstanceId);
1362 IF (result = false) THEN
1363 PROCESSID := -1;
1364 STATUS := 'INVALID_INSTANCE_ID';
1365 RETURN;
1366 END IF;
1367
1368 IF (DateFrom is NULL) THEN
1369 PROCESSID := -1;
1370 STATUS := 'INVALID_DATEFROM_CANNOT_BE_NULL';
1371 RETURN;
1372 END IF;
1373
1374 IF (DateTo is NULL) THEN
1375 PROCESSID := -1;
1376 STATUS := 'INVALID_DATETO_CANNOT_BE_NULL';
1377 RETURN;
1378 END IF;
1379
1380 IF (DateFrom > DateTo) THEN
1381 PROCESSID := -1;
1382 STATUS := 'INVALID_DATES';
1383 RETURN;
1384 END IF;
1385
1386 error_tracking_number := 120;
1387 result:= isValid_Sel_Of_OrderTypes( IncludeAllLists , IncludePriceList, ExcludePriceList );
1388 IF (result = false) THEN
1389 PROCESSID := -1;
1390 STATUS := 'INVALID_SELECTION_PRICE_LIST';
1391 RETURN;
1392 END IF;
1393
1394
1395 -- register Collect Price Lists
1396 error_tracking_number := 130;
1397 req_id := fnd_request.submit_request('MSD','MSDDEMPRL','Collect Price Lists',NULL, false,
1398 InstanceId,
1399 to_char(DateFrom, 'YYYY/MM/DD HH24:MI:SS'),
1400 to_char(DateTo, 'YYYY/MM/DD HH24:MI:SS'),
1401 MSC_WS_COMMON.Bool_to_Number(IncludeAllLists),
1402 IncludePriceList,
1403 ExcludePriceList
1404 );
1405
1406
1407 IF(req_id = 0) THEN
1408 RAISE submit_failed ;
1409 END IF ;
1410
1411 status := 'SUCCESS';
1412 processId := req_id;
1413
1414 EXCEPTION
1415 WHEN submit_failed THEN
1416 status := 'ERROR_SUBMIT';
1417 processId := -1;
1418 RETURN;
1419 WHEN others THEN
1420 status := 'ERROR_UNEXPECTED_'||error_tracking_number;
1421 processId := -1;
1422 RETURN;
1423
1424
1425 END RunDemantraPricingData;
1426
1427 PROCEDURE RunDemantraPricingData_Pub(
1428 processId OUT NOCOPY NUMBER,
1429 status OUT NOCOPY VARCHAR2,
1430 UserName IN VARCHAR2,
1431 RespName IN VARCHAR2,
1432 RespApplName IN VARCHAR2,
1433 SecurityGroupName IN VARCHAR2,
1434 Language IN VARCHAR2,
1435 InstanceId IN NUMBER,
1436 DateFrom IN DATE,
1437 DateTo IN DATE,
1438 IncludeAllLists IN VARCHAR2,
1439 IncludePriceList IN VARCHAR2 DEFAULT NULL,
1440 ExcludePriceList IN VARCHAR2 DEFAULT NULL
1441 )AS
1442 userid number;
1443 respid number;
1444 l_String VARCHAR2(30);
1445 error_tracking_num number;
1446 l_SecutirtGroupId NUMBER;
1447 BEGIN
1448 error_tracking_num :=2010;
1449 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1450 IF (l_String <> 'OK') THEN
1451 Status := l_String;
1452 RETURN;
1453 END IF;
1454 error_tracking_num :=2020;
1455 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid,'MSD_DEM_FNDRSRUN_PRL', l_SecutirtGroupId);
1456 IF (l_String <> 'OK') THEN
1457 Status := l_String;
1458 RETURN;
1459 END IF;
1460
1461 error_tracking_num :=2040;
1462
1463
1464 RunDemantraPricingData( Status,
1465 processid ,
1466 userid,
1467 respid,
1468 InstanceId,
1469 DateFrom,
1470 DateTo,
1471 IncludeAllLists ,
1472 IncludePriceList,
1473 ExcludePriceList );
1474 -- dbms_output.put_line('USERID=' || userid);
1475
1476
1477 EXCEPTION
1478 WHEN others THEN
1479 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
1480
1481 return;
1482
1483
1484 END RunDemantraPricingData_Pub;
1485 -- =============================================================
1486 -- Desc: Please see package spec file for description
1487 -- =============================================================
1488 PROCEDURE RunDemantraReturnsHistory(
1489 status OUT NOCOPY VARCHAR2,
1490 processid OUT NOCOPY VARCHAR2,
1491 UserID IN NUMBER,
1492 ResponsibilityID IN NUMBER,
1493 InstanceId IN NUMBER,
1494 CollectionGroup IN VARCHAR2,
1495 CollectionMethod IN VARCHAR2,
1496 DateRangeType IN VARCHAR2 DEFAULT NULL,
1497 HistoryCollectionWindow IN NUMBER DEFAULT NULL,
1498 DateFrom IN DATE DEFAULT NULL,
1499 DateTo IN DATE DEFAULT NULL,
1500 RMATypes IN MscChar255Arr
1501 ) is
1502 L_VAL_RESULT VARCHAR2(30);
1503 result BOOLEAN := false;
1504 req_id NUMBER:=0;
1505 submit_failed EXCEPTION;
1506 error_tracking_number NUMBER;
1507 passedCollectionGroup varchar2(100);
1508
1509 BEGIN
1510 error_tracking_number:=100;
1511 MSC_WS_COMMON.VALIDATE_USER_RESP (L_VAL_RESULT, UserId, ResponsibilityID);
1512 IF (L_VAL_RESULT <> 'OK') THEN
1513 PROCESSID := -1;
1514 STATUS := L_VAL_RESULT;
1515 RETURN;
1516 END IF;
1517
1518 error_tracking_number:=120;
1519 result := isValid_INSTANCE_ID( InstanceId);
1520 IF (result = false) THEN
1521 PROCESSID := -1;
1522 STATUS := 'INVALID_INSTANCE_ID';
1523 RETURN;
1524 END IF;
1525
1526 error_tracking_number:=130;
1527 result := isValid_DEM_COLLECTION_GROUP( CollectionGroup, InstanceId) ;
1528 IF (result = false) THEN
1529 PROCESSID := -1;
1530 STATUS := 'INVALID_COLLECTION_GROUP';
1531 RETURN;
1532 END IF;
1533
1534 error_tracking_number:=140;
1535
1536 result := isValid_RMA_Types(RMATypes, InstanceId);
1537 IF (result = false) THEN
1538 PROCESSID := -1;
1539 STATUS := 'INVALID_RMA_TYPES';
1540 RETURN;
1541 END IF;
1542
1543 error_tracking_number:=145;
1544 --save the RMA types into MSD_DEM_RMA_TYPE. Conc program will read data from here, and wipe out table.
1545
1546 result := SaveRMATypesIntoTable(RMATypes);
1547 IF (result = false) THEN
1548 PROCESSID := -1;
1549 STATUS := 'ERROR_SAVING_RMA_TYPES';
1550 RETURN;
1551 END IF;
1552
1553 -- register Collect Returns History
1554 error_tracking_number:=150;
1555
1556 passedCollectionGroup := CollectionGroup;
1557 -- bug 6837675
1558 --if ( CollectionGroup = 'All') then
1559 -- passedCollectionGroup := '-999';
1560 --end if;
1561 req_id := fnd_request.submit_request('MSD','MSDDEMRH','Returns History',NULL, false,
1562 InstanceId, passedCollectionGroup,
1563 GetRH_CollectionMethodAsNumber( CollectionMethod),
1564 GetRH_DateRangeTypeAsNumber(DateRangeType),
1565 HistoryCollectionWindow,
1566 to_char(DateFrom, 'YYYY/MM/DD HH24:MI:SS'), to_char(DateTo, 'YYYY/MM/DD HH24:MI:SS'),
1567 'MSD_DEM_RETURN_HISTORY'
1568 );
1569
1570
1571 IF(req_id = 0) THEN
1572 raise submit_failed ;
1573 END IF ;
1574
1575 status := 'SUCCESS';
1576 processId := req_id;
1577
1578 EXCEPTION
1579 WHEN submit_failed THEN
1580 status := 'ERROR_SUBMIT';
1581 processId := -1;
1582 RETURN;
1583 WHEN others THEN
1584 status := 'ERROR_UNEXPECTED_'||error_tracking_number;
1585 processId := -1;
1586 RETURN;
1587
1588
1589 END RunDemantraReturnsHistory;
1590
1591 PROCEDURE RunDemantraReturnsHistory_Pub(
1592 processId OUT NOCOPY NUMBER,
1593 status OUT NOCOPY VARCHAR2,
1594 UserName IN VARCHAR2,
1595 RespName IN VARCHAR2,
1596 RespApplName IN VARCHAR2,
1597 SecurityGroupName IN VARCHAR2,
1598 Language IN VARCHAR2,
1599 InstanceId IN NUMBER,
1600 CollectionGroup IN VARCHAR2,
1601 CollectionMethod IN VARCHAR2,
1602 DateRangeType IN VARCHAR2 DEFAULT NULL,
1603 HistoryCollectionWindow IN NUMBER DEFAULT NULL,
1604 DateFrom IN DATE DEFAULT NULL,
1605 DateTo IN DATE DEFAULT NULL,
1606 RMATypes IN MscChar255Arr
1607 ) AS
1608 userid number;
1609 respid number;
1610 l_String VARCHAR2(30);
1611 error_tracking_num number;
1612 l_SecutirtGroupId NUMBER;
1613 BEGIN
1614 error_tracking_num :=2010;
1615 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1616 IF (l_String <> 'OK') THEN
1617 Status := l_String;
1618 RETURN;
1619 END IF;
1620 error_tracking_num :=2020;
1621 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid,'MSD_DEM_CRH', l_SecutirtGroupId);
1622 IF (l_String <> 'OK') THEN
1623 Status := l_String;
1624 RETURN;
1625 END IF;
1626
1627 error_tracking_num :=2040;
1628
1629 RunDemantraReturnsHistory ( status,
1630 processId,
1631 userId ,
1632 respid,
1633 InstanceId,
1634 CollectionGroup ,
1635 CollectionMethod,
1636 DateRangeType,
1637 HistoryCollectionWindow,
1638 DateFrom ,
1639 DateTo,
1640 RMATypes );
1641
1642
1643
1644 EXCEPTION
1645 WHEN others THEN
1646 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
1647
1648 return;
1649 END RunDemantraReturnsHistory_Pub;
1650
1651 FUNCTION GetCollectionMethodAsNumber( CollectionMethod IN VARCHAR2) RETURN NUMBER IS
1652 cCode NUMBER;
1653 BEGIN
1654 --SELECT lookup_code INTO cCode FROM mfg_lookups WHERE lookup_type = 'PARTIAL_YES_NO' AND meaning = CollectionMethod;
1655
1656 SELECT to_number(decode(CollectionMethod,'COMPLETE_REFRESH',1,'NET_CHANGE_REFRESH',2,'TARGETED_REFRESH',3, -1))
1657 INTO cCode FROM dual;
1658
1659 RETURN cCode;
1660
1661
1662 RETURN 3;
1663 END GetCollectionMethodAsNumber;
1664
1665 FUNCTION GetLookupCodeForAppSuppList(ApprovedSupplierList IN VARCHAR2) RETURN NUMBER IS
1666 cCode NUMBER;
1667 BEGIN
1668 --SELECT lookup_code INTO cCode FROM mfg_lookupsWHERE lookup_type = 'MSC_X_ASL_SYS_YES_NO' AND meaning = ApprovedSupplierList;
1669 -- bnaghi bug 6861953
1670 SELECT to_number(decode(ApprovedSupplierList,'YES_REPLACE',1,'NO',2,'YES_BUT_RETAIN_CP', 3,-1))
1671 INTO cCode FROM dual;
1672
1673 RETURN cCode;
1674
1675 END GetLookupCodeForAppSuppList;
1676
1677
1678 FUNCTION GetUserCompAssoc(UserCompanyAssoc IN VARCHAR2) RETURN NUMBER IS
1679 cCode NUMBER;
1680 BEGIN
1681 --SELECT lookup_code INTO cCode FROM fnd_lookups WHERE lookup_type = 'MSC_X_USER_COMPANY' AND meaning = UserCompanyAssoc;
1682
1683 SELECT to_number(decode(UserCompanyAssoc,'CREATE_USERS_ENABLE_UCA',3,'NO',1,'ENABLE_UCA', 2,-1))
1684 INTO cCode FROM dual;
1685
1686 RETURN cCode;
1687
1688 END GetUserCompAssoc;
1689
1690
1691 FUNCTION GetResAvail(ResourceAvailability IN VARCHAR2) RETURN NUMBER IS
1692 cCode NUMBER;
1693 BEGIN
1694 --SELECT lookup_type, meaning, lookup_code FROM mfg_lookups WHERE lookup_type = 'MSC_NRA_ENABLED'
1695 -- bnaghi bug 6861953
1699 RETURN cCode;
1696 SELECT to_number(decode(ResourceAvailability,'COLLECT_DATA',1,'DO_NOT_COLLECT_DATA',2,'REGENERATE_DATA', 3,-1))
1697 INTO cCode FROM dual;
1698
1700
1701 END GetResAvail;
1702 -- =============================================================
1703 -- Desc: Private function to validate instance id. The logic
1704 -- mirrors the value set MSC_SRS_INSTANCE_CODE
1705 -- =============================================================
1706 FUNCTION isValid_INSTANCE_ID( INST_ID IN NUMBER) RETURN BOOLEAN IS
1707 l_val_instance_id NUMBER;
1708 BEGIN
1709 BEGIN
1710 SELECT instance_id INTO l_val_instance_id
1711 FROM msc_apps_instances
1712 WHERE instance_id = INST_ID
1713 AND instance_type IN (1,2,4) and enable_flag=1;
1714 END;
1715
1716 RETURN TRUE;
1717
1718 EXCEPTION WHEN no_data_found THEN
1719 RETURN false;
1720 END isValid_INSTANCE_ID;
1721
1722
1723 -- =============================================================
1724 -- Desc: Private function to validate instance id. The logic
1725 -- mirrors the value set MSC_ORG_STRINGS
1726 -- =============================================================
1727 FUNCTION isValid_COLLECTION_GROUP( orgGroup IN VARCHAR2, InstanceId IN Number) RETURN booleaN IS
1728 cCode VARCHAR2(80);
1729 BEGIN
1730 SELECT org_group into cCode
1731 from MSC_ORG_GROUPS_V
1732 WHERE code = orgGroup
1733 and (instance_id IS NULL OR instance_id = InstanceId);
1734
1735 RETURN true;
1736
1737 EXCEPTION
1738 WHEN no_data_found THEN
1739 RETURN false;
1740
1741 END isValid_COLLECTION_GROUP;
1742
1743 -- =============================================================
1744 -- Desc: Private function to validate instance id. The logic
1745 -- mirrors the value set MSD_DEM_ORG_STRINGS
1746 -- =============================================================
1747 FUNCTION isValid_DEM_COLLECTION_GROUP( orgGroup IN VARCHAR2, InstanceId IN Number) RETURN booleaN IS
1748 cCode VARCHAR2(80);
1749 BEGIN
1750 -- changed for bug 6837675 , orgGroup/collection group input value for All is -999
1751 SELECT org_group into cCode
1752 from MSD_DEM_ORG_GROUPS_V
1753 WHERE code = orgGroup
1754 and (instance_id IS NULL OR instance_id = InstanceId);
1755 RETURN true;
1756
1757 EXCEPTION
1758 WHEN no_data_found THEN
1759 RETURN false;
1760
1761 END isValid_DEM_COLLECTION_GROUP;
1762
1763
1764 -- =============================================================
1765 -- Desc: Private function to validate instance id. The logic
1766 -- mirrors the value set MSD_DEM_COLL_METHODS
1767 -- =============================================================
1768 FUNCTION isValid_DEM_COLLECTION_METHOD( methodCode IN NUMBER) RETURN booleaN IS
1769 cCode NUMBER;
1770 BEGIN
1771
1772 SELECT lookup_code INTO cCode
1773 FROM fnd_lookup_values_vl flv
1774 WHERE flv.lookup_type = 'MSD_DEM_COLL_METHODS'
1775 AND flv.lookup_code = methodCode;
1776
1777 RETURN true;
1778
1779 EXCEPTION
1780 WHEN no_data_found THEN
1781 RETURN false;
1782
1783 END isValid_DEM_COLLECTION_METHOD;
1784
1785
1786
1787 -- =============================================================
1788 -- Desc: Private function to validate instance id. The logic
1789 -- mirrors the value set MSD_DEM_DATE_RANGE_TYPES
1790 -- =============================================================
1791 FUNCTION isValid_DATE_RANGE_TYPE(CollectionMethod IN NUMBER,
1792 dateRangeType IN NUMBER) RETURN booleaN IS
1793 cCode NUMBER;
1794 BEGIN
1795 -- If collection method is 2 (Netchange), then the dateRangeType
1796 -- Is required. Otherwise, dateRangeType need to be NULL
1797 IF (CollectionMethod = 2) THEN
1798 SELECT lookup_code into cCode from fnd_lookup_values_vl flv
1799 WHERE flv.lookup_type = 'MSD_DEM_DATE_RANGE_TYPES'
1800 --AND '1' = hiddenParam
1801 AND lookup_code= dateRangeType;
1802
1803 RETURN true;
1804 ELSE
1805 IF (dateRangeType is NULL) THEN
1806 RETURN true;
1807 ELSE
1808 RETURN false;
1809 END IF;
1810 END IF;
1811 EXCEPTION WHEN no_data_found THEN
1812 RETURN false;
1813
1814 END isValid_DATE_RANGE_TYPE;
1815
1816
1817 -- =============================================================
1818 -- Desc: Private function to validate instance id. The logic
1819 -- mirrors the value set MSD_DEM_DATE_RANGE_TYPES
1820 -- =============================================================
1821 FUNCTION isValid_Sel_Of_OrderTypes( CollectAllOrderTypes in Varchar2,
1822 IncludeOrderTypes in VARCHAR2,
1823 ExcludeOrderTypes in VARCHAR2) RETURN BOOLEAN is
1824 BEGIN
1825 IF (CollectAllOrderTypes = 'Y') THEN
1826 RETURN true;
1827 END IF;
1828
1829 IF ((IncludeOrderTypes is NULL) AND (ExcludeOrderTypes is NULL)) THEN
1830 RETURN FALSE;
1831 END IF;
1832
1833 IF ( (IncludeOrderTypes is NOT NULL) AND (ExcludeOrderTypes is NOT NULL )) THEN
1834 RETURN false;
1835 END IF;
1836
1837 RETURN true;
1838 END isValid_Sel_Of_OrderTypes;
1839
1840 -- =============================================================
1841 -- Desc: Private function to validate dates based on DateRangeType.
1842 -- =============================================================
1843 FUNCTION isValid_Dates( DateRangeType in NUMBER,
1844 DateFrom in DATE,
1848 IF ( DateRangeType = 1) THEN
1845 DateTo IN DATE) RETURN BOOLEAN is
1846 BEGIN
1847 -- for 'absolute' dateRange Type, dates cannot be null
1849 IF ( DateFrom is NULL or DateTo is NULL ) THEN
1850 RETURN FALSE;
1851 END IF;
1852 END IF;
1853
1854 -- dateFrom < dateTo
1855 IF ( DateFrom is NOT NULL and DateTo is NOT NULL ) THEN
1856 IF ( DateFrom > DateTo) THEN
1857 RETURN FALSE;
1858 END IF;
1859 END IF;
1860
1861 RETURN TRUE;
1862
1863 END isValid_Dates;
1864
1865
1866 -- =============================================================
1867 -- Desc: Private function to get destination table name for
1868 -- "Demantra Update Level Codes" conc program
1869 -- =============================================================
1870
1871 FUNCTION Dem_ULC_GetDestTableName RETURN VARCHAR2 is
1872 value varchar(80);
1873 BEGIN
1874 SELECT msd_dem_common_utilities.get_lookup_value ('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE') into value from dual;
1875 RETURN value;
1876 END Dem_ULC_GetDestTableName;
1877
1878 FUNCTION GetRH_CollectionMethodAsNumber( CollectionMethod IN VARCHAR2) RETURN NUMBER IS
1879 cCode NUMBER;
1880 BEGIN
1881
1882 SELECT to_number(decode(CollectionMethod,'COMPLETE',1,'NET_CHANGE',2, -1))
1883 INTO cCode FROM dual;
1884 RETURN cCode;
1885 END GetRH_CollectionMethodAsNumber;
1886
1887 FUNCTION GetRH_DateRangeTypeAsNumber(DateRangeType IN Varchar2) RETURN NUMBER IS
1888 cCode NUMBER;
1889 BEGIN
1890
1891 SELECT to_number(decode(DateRangeType,'ABSOLUTE',1,'ROLLING',2, -1))
1892 INTO cCode FROM dual;
1893 RETURN cCode;
1894 END GetRH_DateRangeTypeAsNumber;
1895
1896 FUNCTION isValid_RMA_Types( types IN MscChar255Arr, InstanceId IN Number) RETURN BOOLEAN is
1897 v_number NUMBER :=0;
1898 dbLink varchar2(128);
1899 dbLink2 varchar2(129);
1900 tableName varchar2(180);
1901 tableNameAll varchar2(180);
1902 selectStr varchar2(1555);
1903 i NUMBER :=0;
1904 begin
1905
1906 if types is NULL or types.COUNT = 0 then
1907 return TRUE;
1908 end if;
1909
1910 select a2m_dblink into dbLink
1911 from msc_apps_instances
1912 where instance_id = InstanceId;
1913
1914 select DECODE(dbLink, NULL,'', '@' || dbLink) into dbLink2 from dual;
1915 tableName := 'oe_transaction_types_tl' || dbLink2;
1916 tableNameAll := 'oe_transaction_types_all' || dbLink2;
1917
1918
1919 for i in 1 .. types.LAST LOOP
1920 v_number := 0;
1921 selectStr := 'SELECT count(1)
1922 FROM ' || tableName || ' a, ' || tableNameAll || ' b
1923 WHERE b.transaction_type_code =''LINE''
1924 AND b.order_category_code = ''RETURN''
1925 AND a.LANGUAGE = userenv(''LANG'')
1926 AND a.transaction_type_id = b.transaction_type_id
1927 AND a.name = ''' ||types(i) || '''';
1928
1929
1930 --dbms_output.put_line(selectStr);
1931 execute immediate selectStr into v_number;
1932
1933 --dbms_output.put_line('number = ' || v_number);
1934
1935 if v_number = 0 then
1936 return false;
1937 end if;
1938
1939 end loop;
1940
1941 return true;
1942
1943 end isValid_RMA_Types;
1944
1945 FUNCTION SaveRMATypesIntoTable(RMATypes IN MscChar255Arr) RETURN BOOLEAN IS
1946 i NUMBER;
1947 begin
1948 if ( RMATypes is NULL or RMATypes.COUNT =0) then
1949 return true;
1950 end if;
1951
1952 for i in 1 .. RMATypes.LAST LOOP
1953 BEGIN
1954 INSERT INTO MSD_DEM_RMA_TYPE
1955 ( RMA_TYPES )
1956 VALUES
1957 (
1958 RMATypes(i)
1959 );
1960 EXCEPTION WHEN others THEN
1961 RETURN FALSE;
1962 END ;
1963 end loop;
1964
1965 return TRUE;
1966
1967 end SaveRMATypesIntoTable;
1968
1969 END MSC_WS_COLLECTIONS;