1 PACKAGE MSD_DEM_COMMON_UTILITIES AUTHID DEFINER AS
2 /* $Header: msddemcus.pls 120.25.12020000.3 2013/01/10 07:21:51 kkhatri ship $ */
3
4 /*** CONSTANTS ***/
5
6 /* ENTITY TYPES */
7 C_HIERARCHY NUMBER := 1;
8 C_SERIES NUMBER := 2;
9
10 /* ENTITIES - Hierarchy */
11 C_ITEM NUMBER := 1;
12 C_SHIP_FROM NUMBER := 2;
13 C_TRADING_PARTNER NUMBER := 3;
14 C_DEMAND_CLASS NUMBER := 4;
15 C_SALES_CHANNEL NUMBER := 5;
16 C_TIME NUMBER := 6;
17
18 /* ENTITIES - Series */
19 C_RETURN_HISTORY NUMBER := 1;
20 C_BH_BI_BD NUMBER := 2;
21 C_BH_BI_RD NUMBER := 3;
22 C_BH_RI_BD NUMBER := 4;
23 C_BH_RI_RD NUMBER := 5;
24 C_SH_SI_SD NUMBER := 6;
25 C_SH_SI_RD NUMBER := 7;
26 C_SH_RI_SD NUMBER := 8;
27 C_SH_RI_RD NUMBER := 9;
28 C_SRP_RETURN_HISTORY NUMBER := 10; --jarora
29 C_SRP_USG_HISTORY_DR NUMBER := 11; --jarora
30 C_SRP_USG_HISTORY_FS NUMBER := 12; --jarora
31 C_INSTALL_BASE_HISTORY NUMBER := 13; --jarora
32 C_TOTAL_BACKLOG NUMBER := 14; --sopjarora
33 C_PAST_DUE_BACKLOG NUMBER := 15; --sopjarora
34 C_ON_HAND_INVENTORY NUMBER := 16; --sopjarora
35 C_ACTUAL_PRODUCTION NUMBER := 17; --sopjarora
36
37 /* LEVEL TYPES */
38 C_ITEM_LEVEL_TYPE NUMBER := 1;
39 C_LOCATION_LEVEL_TYPE NUMBER := 2;
40 C_TIME_LEVEL_TYPE NUMBER := 3;
41
42 /* YES/NO */
43 C_YES NUMBER := 1;
44 C_NO NUMBER := 2;
45
46 /* COLLECTION TYPES */
47 C_REFRESH NUMBER := 1;
48 C_NET_CHANGE NUMBER := 2;
49
50 /* MSD DEM Debug Profile Value */
51 C_MSD_DEM_DEBUG VARCHAR2(1) := nvl( fnd_profile.value( 'MSD_DEM_DEBUG_MODE'), 'N');
52
53 /* Demantra Schema Name */
54 C_MSD_DEM_SCHEMA VARCHAR2(100) := nvl( fnd_profile.value( 'MSD_DEM_SCHEMA' ) , 'DMTRA_TEMPLATE');
55
56 /* Demantra Sys_Params */
57 C_DEM_MIN_SALES_DATE_D DATE := NULL;
58 C_DEM_MAX_FORE_SALES_DATE_D DATE := NULL;
59 C_DEM_MAX_SALES_DATE_D DATE := NULL;
60 C_DEM_HISTORY_PERIODS NUMBER := NULL;
61 C_DEM_LEAD NUMBER := NULL;
62
63
64 /*** PUBLIC PROCEDURES ***
65 * LOG_MESSAGE
66 * LOG_DEBUG
67 * GET_DBLINK
68 * GET_INSTANCE_INFO
69 * GET_SCHEMA_NAME
70 */
71
72 /*
73 * This procedure logs a given message text in the concurrent request log file.
74 * param: p_buff - message text to be logged.
75 */
76 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2);
77
78 /*
79 * This procedure logs a given debug message text in the concurrent request log file
80 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
81 * param: p_buff - debug message text to be logged.
82 */
83 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2);
84
85 /*
86 * This procedure gets the db link to the given source instance
87 */
88 PROCEDURE GET_DBLINK (
89 errbuf OUT NOCOPY VARCHAR2,
90 retcode OUT NOCOPY VARCHAR2,
91 p_sr_instance_id IN NUMBER,
92 p_dblink OUT NOCOPY VARCHAR2);
93
94 /*
95 * This procedure gets the db link to the destination from given source instance
96 */
97 PROCEDURE GET_DEST_DBLINK (
98 errbuf OUT NOCOPY VARCHAR2,
99 retcode OUT NOCOPY VARCHAR2,
100 p_sr_instance_id IN NUMBER,
101 p_dblink OUT NOCOPY VARCHAR2);
102
103 /*
104 * This procedure gets the instance info given the source instance id
105 */
106 PROCEDURE GET_INSTANCE_INFO (
107 errbuf OUT NOCOPY VARCHAR2,
108 retcode OUT NOCOPY VARCHAR2,
109 p_instance_code OUT NOCOPY VARCHAR2,
110 p_apps_ver OUT NOCOPY NUMBER,
111 p_dgmt OUT NOCOPY NUMBER,
112 p_instance_type OUT NOCOPY NUMBER,
113 p_sr_instance_id IN NUMBER);
114
115 /* This procedure gives schema name for a application */
116
117 PROCEDURE GET_SCHEMA_NAME (
118 errbuf OUT NOCOPY VARCHAR2,
119 retcode OUT NOCOPY VARCHAR2,
120 schema_name OUT NOCOPY VARCHAR2,
121 appl_short_name IN VARCHAR2 );
122
123 /*
124 * This procedure will refresh Purge Series Data data profile to its defualt value
125 * i.e. it will set the data profile option to No Load and No Purge for all series
126 * included in the profile.
127 */
128
129 PROCEDURE REFRESH_PURGE_SERIES (
130 errbuf OUT NOCOPY VARCHAR2,
131 retcode OUT NOCOPY VARCHAR2,
132 p_profile_id IN NUMBER,
133 p_schema IN VARCHAR2);
134
135 /*
136 * Update the synonyms MSD_DEM_TRANSFER_LIST and MSD_DEM_TRANSFER_QUERY
137 * to point to the Demantra's tables TRANSFER_LIST and TRANSFER_QUERY
138 * if Demantra is installed.
139 * Sets the profile MSD_DEM_SCHEMA to the Demantra Schema Name
140 * The checks if the table MDP_MATRIX exists in the Demantra Schema
141 */
142
143 PROCEDURE UPDATE_SYNONYMS (
144 errbuf OUT NOCOPY VARCHAR2,
145 retcode OUT NOCOPY VARCHAR2,
146 p_demantra_schema IN VARCHAR2 DEFAULT NULL);
147
148 /* Deletes the msd_dem_entities_inuse table if the new demantra schema is intstalled
149 this will ensure that there will be no mapping between the seeded units in APPS and
150 the (display uints,exchange rate,indexes) in Demantra */
151
152 PROCEDURE CLEANUP_ENTITIES_INUSE(
153 errbuf out nocopy varchar2,
154 retcode out nocopy varchar2);
155
156
157 /*** FUNCTIONS ***
158 * GET_ALL_ORGS
159 * DM_TIME_LEVEL
160 * GET_PARAMETER_VALUE
161 * GET_LOOKUP_VALUE
162 * GET_UOM_CODE
163 * GET_SR_INSTANCE_ID_FOR_ZONE
164 * UOM_CONVERT
165 * IS_PF_FCSTABLE_FOR_ITEM
166 * IS_PRODUCT_FAMILY_FORECASTABLE
167 * GET_SUPPLIER_CALENDAR
168 * GET_SAFETY_STOCK_ENDDATE
169 * GET_PERIOD_DATE_FOR_DUMMY
170 * GET_SITE_FOR_CSF
171 * IS_LAST_DATE_IN_BUCKET
172 * GET_SNO_PLAN_CUTOFF_DATE
173 * IS_SUPPLIER_CALENDAR_PRESENT
174 * UOM_CONV
175 * GET_LOOKUP_CODE
176 * GET_LEVEL_NAME
177 * GET_DEMANTRA_DATE
178 * IS_USE_NEW_SITE_FORMAT
179 * GET_DEMANTRA_VERSION
180 * GET_APP_ID_TEXT
181 * UPDATE_DEM_APCC_SYNONYM
182 * GET_CTO_EFFECTIVE_DATE
183 * GET_SPF_SR_CAT_SET_ID
184 * GET_DEM_SYSDATE
185 * GET_DEM_CTO_BASE_MODEL
186 * GET_DEM_CTO_OPTION_CLASS
187 * GET_DEM_CTO_OPTION
188 * GET_DEM_SPF_BASE_MODEL
189 * GET_DEM_SPF_OPTION_CLASS
190 * GET_DEM_SPF_OPTION
191 * GET_ITEM_LABEL
192 * GET_ORG_LABEL
193 * GET_SITE_LABEL
194 * GET_DC_LABEL
195 * GET_SC_LABEL
196 * GET_ITEM_ID
197 * GET_ORG_ID
198 * GET_SITE_ID
199 * GET_DC_ID
200 * GET_SC_ID
201 * GET_ASSET_GROUP_LABEL
202 * GET_CLASS_CODE_LABEL
203 * GET_WORKORDER_ITEM
204 * GET_ASSET_GROUP_ID
205 * GET_CLASS_CODE_ID
206 * CHECK_WF_STATUS
207 */
208
209
210
211 /*
212 * This function returns the comma(,) separated list of demand management enabled orgs
213 * belonging to the given org group.
214 */
215 FUNCTION GET_ALL_ORGS (
216 p_org_group IN VARCHAR2,
217 p_sr_instance_id IN NUMBER)
218 RETURN VARCHAR2;
219
220 /* This function returns the Active Demantra Data Model time level (Day/Month/week) */
221 FUNCTION DM_TIME_LEVEL RETURN VARCHAR2;
222
223 /* This function returns the parameter_value in msd_dem_setup_parameters given the parameter_name */
224 FUNCTION GET_PARAMETER_VALUE (
225 p_sr_instance_id NUMBER,
226 p_parameter_name VARCHAR2)
227 RETURN VARCHAR2;
228
229 /* This function returns the lookup_value given the lookup_type and lookup_code */
230 FUNCTION GET_LOOKUP_VALUE (
231 p_lookup_type IN VARCHAR2,
232 p_lookup_code IN VARCHAR2)
233 RETURN VARCHAR2;
234
235 /* This function returns the UOM code given the display unit id */
236 FUNCTION GET_UOM_CODE (
237 p_unit_id IN NUMBER)
238 RETURN VARCHAR2;
239
240 /* This function returns a sr_instance_id in which the zone is defined */
241 FUNCTION GET_SR_INSTANCE_ID_FOR_ZONE (
242 p_zone IN VARCHAR2)
243 RETURN NUMBER;
244
245 /* This function returns the conversion rate for the given item, From UOM and To UOM */
246 FUNCTION UOM_CONVERT (
247 p_inventory_item_id IN NUMBER,
248 p_precision IN NUMBER,
249 p_from_unit IN VARCHAR2,
250 p_to_unit IN VARCHAR2)
251 RETURN NUMBER;
252
253 /* This function returns 1 if the product family's forecast control is set for the given
254 item in the master org, else returns 2 */
255 FUNCTION IS_PF_FCSTABLE_FOR_ITEM (
256 p_sr_inventory_item_id IN NUMBER,
257 p_sr_instance_id IN NUMBER,
258 p_master_org_id IN NUMBER)
259 RETURN NUMBER;
260
261 /* This function returns 1 if the product family forecast control flag is set,
262 * else returns 2
263 */
264 FUNCTION IS_PRODUCT_FAMILY_FORECASTABLE (
265 p_inventory_item_id IN NUMBER,
266 p_sr_instance_id IN NUMBER)
267 RETURN NUMBER;
268
269 /*
270 * This function gets the calendar code
271 */
272 FUNCTION GET_SUPPLIER_CALENDAR (
273 p_plan_id IN NUMBER,
274 p_sr_instance_id IN NUMBER,
275 p_organization_id IN NUMBER,
276 p_inventory_item_id IN NUMBER,
277 p_supplier_id IN NUMBER,
278 p_supplier_site_id IN NUMBER,
279 p_using_organization_id IN NUMBER)
280 RETURN VARCHAR2;
281
282 /*
283 * This function gets the period end date
284 */
285 FUNCTION GET_SAFETY_STOCK_ENDDATE (
286 p_plan_id IN NUMBER,
287 p_sr_instance_id IN NUMBER,
288 p_organization_id IN NUMBER,
289 p_inventory_item_id IN NUMBER,
290 p_period_start_date IN DATE)
291 RETURN DATE;
292
293 /*
294 * Returns a valid date from the table INPUTS in Demantra
295 */
296 FUNCTION GET_PERIOD_DATE_FOR_DUMMY
297 RETURN DATE;
298
299 /*
300 * Given, the instance, customer and/or site, this function returns
301 * the site level member name. If only the customer is specified then
302 * then any arbit site belonging to the customer is returned.
303 */
304 FUNCTION GET_SITE_FOR_CSF (
305 p_sr_instance_id IN NUMBER,
306 p_customer_id IN NUMBER,
307 p_customer_site_id IN NUMBER)
308 RETURN VARCHAR2;
309
310 /*
311 * Given, the instance, calendar_code, calendar_date, this function
312 * returns 1 if the date is the last date in its demantra bucket,
313 * else returns 2.
314 * Note: This function requires the table msd_dem_dates to be
315 * populated.
316 */
317 FUNCTION IS_LAST_DATE_IN_BUCKET (
318 p_sr_instance_id IN NUMBER,
319 p_calendar_code IN VARCHAR2,
320 p_calendar_date IN DATE)
321 RETURN NUMBER;
322
323 /*
324 * Given the plan id of a SNO plan, this function returns
325 * the cutoff date for the plan.
326 */
327 FUNCTION GET_SNO_PLAN_CUTOFF_DATE (
328 p_plan_id IN NUMBER)
329 RETURN DATE;
330
331 /*
332 * This function returns 1 if a supplier calendar is present else returns 2.
333 */
334 FUNCTION IS_SUPPLIER_CALENDAR_PRESENT (
335 p_plan_id IN NUMBER,
336 p_sr_instance_id IN NUMBER,
337 p_organization_id IN NUMBER,
338 p_inventory_item_id IN NUMBER,
339 p_supplier_id IN NUMBER,
340 p_supplier_site_id IN NUMBER,
341 p_using_organization_id IN NUMBER)
342 RETURN NUMBER;
343
344 /*
345 * Given the item and the uom code, this function gives the conversion factor
346 * to the base uom of the item.
347 */
348 FUNCTION UOM_CONV (
349 p_sr_instance_id IN NUMBER,
350 p_uom_code IN VARCHAR2,
351 p_inventory_item_id IN NUMBER DEFAULT NULL)
352 RETURN NUMBER;
353
354 /*
355 * This function given the Demantra lookup table name and lookup ID
356 * returns the lookup Code
357 */
358 FUNCTION GET_LOOKUP_CODE (
359 p_lookup_table_name IN VARCHAR2,
360 p_lookup_id IN NUMBER)
361 RETURN VARCHAR2;
362
363 /*
364 * This function given the Demantra lookup table name and lookup ID
365 * returns the lookup Code
366 */
367 FUNCTION GET_LEVEL_NAME (
368 p_it_level_code IN NUMBER)
369 RETURN VARCHAR2;
370
371 /*
372 * Given a date, the function returns the the bucket date to which the date belongs.
373 * If p_date is null, p_from is 1, the the function returns
374 * max of (min_sales_date, sysdate - 2 years )
375 * If p_date is null, p_from is 2, the the function returns
376 * min of (max_fore_sales_date, sysdate + 2 years )
377 */
378 FUNCTION GET_DEMANTRA_DATE (
379 p_date IN DATE,
380 p_from IN NUMBER)
381 RETURN DATE;
382
383 /*
384 * The function is used to determine whether to use the new site format or not.
385 * Returns -
386 * 1 - use new site format, from 7.3.x onwards
387 * 0 - use old site format, for 7.2.x release
388 */
389 FUNCTION IS_USE_NEW_SITE_FORMAT
390 RETURN NUMBER;
391
392 /*
393 * The function returns the Demantra release version.
394 */
395 FUNCTION GET_DEMANTRA_VERSION
396 RETURN VARCHAR2;
397
398
399 /*
400 * The function returns the request Demantra value or the join condition
401 * given the lookup code. This function uses APP ID for Demantra 7.3 release
402 * and internal ids for Demantra 7.2 release.
403 */
404 FUNCTION GET_APP_ID_TEXT (
405 p_lookup_type IN VARCHAR2,
406 p_lookup_code IN VARCHAR2,
407 p_is_select IN NUMBER,
408 p_column_name IN VARCHAR2)
409 RETURN VARCHAR2;
410
411 /*
412 * Procedure Name - UPDATE_DEM_APCC_SYNONYM
413 * This procedure creates the required dummy objets for APCC
414 * 1) Checks if demantra is installed and the mview created
415 * 1.1.a) If mview is available, drop it.
416 * 1.1.b) Create a new mview with the same name - BIEO_OBI_MV
417 * 1.2) If demantra is not installed, and dummy table available
418 * 1.2.a) Drop the dummy table
419 * 1.2.b) Create the dummy table - MSD_DEM_BIEO_OBI_MV_DUMMY
420 * 2) Create synonym MSD_DEM_BIEO_OBI_MV_SYN accordingly.
421 *
422 */
423 PROCEDURE UPDATE_DEM_APCC_SYNONYM(
424 errbuf out NOCOPY varchar2,
425 retcode out NOCOPY varchar2);
426
427 /*
428 * Use this function to determine start/end date of a CTO item
429 * Dates calculated, to be closer to max sales date in demantra, as follows :
430 * (If 'max_sales_date' sys_param is used the value will be used, else the max date from sales staging table will be considered)
431 * Start date - bom_effective_date or (max_sales_date - cto_history_periods) whichever is higher
432 * End date - bom_inactive_date or (max_sales_date + lead) whichever is closer to lower
433 *
434 * params :
435 * p_bom_date - bom_effective_date or bom_inactive_date
436 * p_min_max - if 1 (date passed is bom_effective_date) else (date passed is bom_inactive_date)
437 */
438 FUNCTION GET_CTO_EFFECTIVE_DATE (
439 p_bom_date IN DATE,
440 p_min_max IN NUMBER DEFAULT 1)
441 RETURN DATE;
442
443 /*
444 * This function returns the value of the constant CS_DEM_SYSDATE
445 */
446 FUNCTION GET_DEM_SYSDATE
447 RETURN DATE;
448
449 /*
450 * This function returns the value of the constant CS_DEM_CTO_BASE_MODEL
451 */
452 FUNCTION GET_DEM_CTO_BASE_MODEL
453 RETURN VARCHAR2;
454
455 /*
456 * This function returns the value of the constant CS_DEM_CTO_OPTION_CLASS
457 */
458 FUNCTION GET_DEM_CTO_OPTION_CLASS
459 RETURN VARCHAR2;
460
461 /*
462 * This function returns the value of the constant CS_DEM_CTO_OPTION
463 */
464 FUNCTION GET_DEM_CTO_OPTION
465 RETURN VARCHAR2;
466
467 /*
468 * This function returns the value of the constant CS_DEM_SPF_BASE_MODEL
469 */
470 FUNCTION GET_DEM_SPF_BASE_MODEL
471 RETURN VARCHAR2;
472
473 /*
474 * This function returns the value of the constant CS_DEM_SPF_OPTION_CLASS
475 */
476 FUNCTION GET_DEM_SPF_OPTION_CLASS
477 RETURN VARCHAR2;
478
479 /*
480 * This function returns the value of the constant CS_DEM_SPF_OPTION
481 */
482 FUNCTION GET_DEM_SPF_OPTION
483 RETURN VARCHAR2;
484
485 /*
486 * This function returns the value of the constant CS_DEM_LEVEL_ITEM_LABEL
487 */
488 FUNCTION GET_ITEM_LABEL
489 RETURN VARCHAR2;
490
491 /*
492 * This function returns the value of the constant CS_DEM_LEVEL_ORG_LABEL
493 */
494 FUNCTION GET_ORG_LABEL
495 RETURN VARCHAR2;
496
497 /*
498 * This function returns the value of the constant CS_DEM_LEVEL_SITE_LABEL
499 */
500 FUNCTION GET_SITE_LABEL
501 RETURN VARCHAR2;
502
503 /*
504 * This function returns the value of the constant CS_DEM_LEVEL_DC_LABEL
505 */
506 FUNCTION GET_DC_LABEL
507 RETURN VARCHAR2;
508
509 /*
510 * This function returns the value of the constant CS_DEM_LEVEL_SC_LABEL
511 */
512 FUNCTION GET_SC_LABEL
513 RETURN VARCHAR2;
514
515 /*
516 * This function returns the value of the constant CS_DEM_LEVEL_ASSET_GROUP_LABEL
517 */
518 FUNCTION GET_ASSET_GROUP_LABEL
519 RETURN VARCHAR2;
520
521 /*
522 * This function returns the value of the constant CS_DEM_LEVEL_CLASS_CODE_LABEL
523 */
524 FUNCTION GET_CLASS_CODE_LABEL
525 RETURN VARCHAR2;
526
527 /*
528 * This function returns the value of the constant CS_DEM_WORKORDER_ITEM
529 */
530 FUNCTION GET_WORKORDER_ITEM
531 RETURN VARCHAR2;
532
533 /*
534 * This function returns the value of the constant CS_DEM_LEVEL_ITEM_ID
535 */
536 FUNCTION GET_ITEM_ID
537 RETURN NUMBER;
538
539 /*
540 * This function returns the value of the constant CS_DEM_LEVEL_ORG_ID
541 */
542 FUNCTION GET_ORG_ID
543 RETURN NUMBER;
544
545 /*
546 * This function returns the value of the constant CS_DEM_LEVEL_SITE_ID
547 */
548 FUNCTION GET_SITE_ID
549 RETURN NUMBER;
550
551 /*
552 * This function returns the value of the constant CS_DEM_LEVEL_DC_ID
553 */
554 FUNCTION GET_DC_ID
555 RETURN NUMBER;
556
557 /*
558 * This function returns the value of the constant CS_DEM_LEVEL_SC_ID
559 */
560 FUNCTION GET_SC_ID
561 RETURN NUMBER;
562
563 /*
564 * This function returns the value of the constant CS_DEM_LEVEL_ASSET_GROUP_ID
565 */
566 FUNCTION GET_ASSET_GROUP_ID
567 RETURN NUMBER;
568
569 /*
570 * This function returns the value of the constant CS_DEM_LEVEL_CLASS_CODE_ID
571 */
572 FUNCTION GET_CLASS_CODE_ID
573 RETURN NUMBER;
574
575 /*
576 * This function given a source instance, will get the source category set id for the category
577 * set specified in the profile MSC_SERVICE_ITEMS_CATSET
578 */
579 FUNCTION GET_SPF_SR_CAT_SET_ID (
580 p_sr_instance_id IN NUMBER)
581 RETURN VARCHAR2;
582
583
584 /* Procedure to launch (remote) request on an instance from a different instance.
585 * To be called over dblink, with parameter string (all parameters to be passed to fnd_request.submit_request including product name, program name etc)
586 * Will initialize apps session if required, and launch the request
587 */
588 PROCEDURE LAUNCH_REMOTE_REQUEST (
589 errbuf OUT NOCOPY VARCHAR2,
590 retcode OUT NOCOPY VARCHAR2,
591 p_user_name IN VARCHAR2,
592 p_resp_name IN VARCHAR2,
593 p_appl_name IN VARCHAR2,
594 p_params_string IN VARCHAR2,
595 p_request_id OUT NOCOPY NUMBER
596 );
597
598 /*
599 * This procedure will launch demantra workflow when provided the workflow lookup code.
600 *
601 * ------------ PARAMETERS LIST ----------------
602 * p_workflow_lookup_code : Workflow lookup code
603 * p_synchronous : Whether CP to in sync with workflow or not
604 * p_check_interval : Check the status of the workflow for every (in seconds)
605 * p_time_out : Stop the status check of the workflow after (in minutes)
606 * --------------------------------------------
607 *
608 */
609 PROCEDURE Launch_Dem_Workflow (
610 errbuf OUT NOCOPY VARCHAR2,
611 retcode OUT NOCOPY VARCHAR2,
612 p_workflow_lookup_code IN VARCHAR2,
613 p_synchronous IN NUMBER DEFAULT C_NO,
614 p_check_interval IN NUMBER DEFAULT 60,
615 p_time_out IN NUMBER DEFAULT 1440 );
616
617 /*
618 *
619 * This procedure is used to check/validate the values given
620 * for parameters in UI while submitting concurrent programs.
621 * After successful validation the procedures returns 'from_date' and 'to_date'
622 *
623 * ------------ PARAMETERS LIST ----------------
624 * p_sr_instance_id : Instance Id
625 * p_collection_group : Organization Group
626 * p_collection_method : Complete or Net Change
627 * p_date_range_type : Absolute or Rolling
628 * p_collection_window : Noof Days in the past for collecting data
629 * p_future_window : Noof Days in the future for collecting data
630 * p_from_date : Start date for collecting data
631 * p_to_date : End date for collecting data
632 * p_collect_product : Name of the product for which the collection is done
633 * (EAM/CMRO/NMP/FLEET)
634 * ---------------------------------------------
635 */
636 PROCEDURE UI_params_validation (
637 errbuf OUT NOCOPY VARCHAR2,
638 retcode OUT NOCOPY VARCHAR2,
639 o_from_date OUT NOCOPY DATE,
640 o_to_date OUT NOCOPY DATE,
641 p_sr_instance_id IN NUMBER,
642 p_collection_group IN VARCHAR2,
643 p_collection_method IN NUMBER,
644 p_hidden_param1 IN VARCHAR2,
645 p_date_range_type IN NUMBER,
646 p_collection_window IN NUMBER,
647 p_from_date IN VARCHAR2,
648 p_to_date IN VARCHAR2,
649 p_collect_product IN VARCHAR2 Default NULL,
650 p_future_window IN NUMBER Default NULL);
651
652
653 /*
654 * This procedure will update parameter's value in demantra
655 * Can be used only for updating paramters in sys_params table
656 *
657 * ------------ PARAMETERS LIST ----------------
658 * p_start_param : parameter name of start_date
659 * p_start_bucket : offset value for start_date
660 * p_end_param : parameter name of end_date
661 * p_end_bucket : offset value for end_date
662 * p_pivot_param : base date parameter name which is used for
663 * setting the above start & end params.
664 * Above offsets are applied against this param.
665 * p_param_name : parameter name to be updated
666 * p_param_value : parameter value to be loaded
667 *
668 */
669 PROCEDURE update_dem_params(
670 p_start_param IN VARCHAR2 DEFAULT NULL,
671 p_start_bucket IN VARCHAR2 DEFAULT NULL,
672 p_end_param IN VARCHAR2 DEFAULT NULL,
673 p_end_bucket IN VARCHAR2 DEFAULT NULL,
674 p_pivot_param IN VARCHAR2 DEFAULT NULL,
675 p_param_name IN VARCHAR2 DEFAULT NULL,
676 p_param_value IN VARCHAR2 DEFAULT NULL ) ;
677
678 /* This function is used to get the correct organization name
679 * for the site which is used for booking internal sales order
680 * p_sr_instance_id - instance id
681 * p_tp_site_id - trading partner site id
682 * p_partner_id - trading partner id
683 * p_location_id - location id (not used)
684 *
685 */
686 FUNCTION get_org_for_internal_site(
687 p_sr_instance_id number,
688 p_tp_site_id number,
689 p_partner_id number,
690 p_location_id number default null)
691
692 RETURN VARCHAR2 ;
693
694 /*
695 * This procedure updates the series load & purge option for the given data profile.
696 * p_schema : demantra schema name
697 * p_dataprofile : Data profile lookup value
698 * p_series : Series lookup value
699 * p_load : Load option ( 0 - OVERRIDE / 1 - ACCUMULATE / 2 - No Load )
700 * p_purge: purge option (0 - No Purge / 1 - Purge All dates without new data / 2 - Purge All dates without new data, within DP time range )
701 * p_notify : notify application server ( 1 - Yes / 2 - No )
702 */
703
704 PROCEDURE SET_SERIES_OPTIONS(
705 errbuf OUT NOCOPY VARCHAR2,
706 retcode OUT NOCOPY VARCHAR2,
707 p_schema IN VARCHAR2,
708 p_dataprofile IN VARCHAR2,
709 p_series IN VARCHAR2,
710 p_load IN NUMBER,
711 p_purge IN NUMBER,
712 p_notify IN NUMBER DEFAULT 2);
713
714 --BUG16070532
715 /*
716 * This function checks for the demantra workflow status
717 * whether it is running (or) completed (or) failed.
718 * Arguments
719 * p_ret_process_id :- process id of the workflow launched
720 * p_check_finish :- check status for every specified time in seconds
721 * p_time_out :- check status till the given time in minutes
722 * p_dem_schema :- demantra schema name
723 * Returns
724 * -1 - workflow failed
725 * 0 - workflow completed
726 * 1 - workflow running
727 */
728 FUNCTION CHECK_WF_STATUS(
729 p_ret_process_id IN NUMBER,
730 p_check_finish IN NUMBER,
731 p_time_out IN NUMBER,
732 p_dem_schema IN varchar2)
733 RETURN NUMBER;
734
735
736 END MSD_DEM_COMMON_UTILITIES;
[Home] [Help]